Transition plan for consumers of the new flat format `parameter_value`

[suvayu]

During the last dev call, the question of how to transition the internal consumers of parameter_value came up. While my initial response was to manage it by documenting compatibile versions of the spine packages, after some thought, I think we can do better in terms of easing development effort.

Background

DB / Client API	old	new
old	✅	❓
new	❌	✅

As it is implemented in #539, code to read the old DB can coexist with the new. So I think if we can make the case of using new client API with old DB possible, the development effort of updating all client APIs, can happen incrementally, without it, it will have to be all-or-nothing.

Proposal

We can add an optional flag to arrow_value.from_database, say, force_new=False; when set to true, an old DB parameter_value will be processed with the migration code from compat (make_records + convert_records_to_columns + to_array) before being converted to pyarrow as it would be for a new format DB. There will be a constant performance penalty.

This should change the ❓ above to ✅, and the development effort can be incremental. Once all client APIs have been migrated, we can force a DB migration. Until then advanced users can manually migrate the DB and test it by using spinedb_api from scripts.

Additional remark

• Using the new DB with old client API is not possible.
• We also need to implement a Julia client API in SpineInterface. Based on this discussion, it should be relatively straightforward to parse the new flat format to existing structures defined by SpineInterface.

[suvayu]

Thoughts and comments? @soininen @ptsavol @jkiviluo @OleMussmann

[OleMussmann]

I personally like the idea and I think we should try to estimate the performance hit with some test benchmarks.

Throwing another idea into the ring: The compat code can be used to check if manual intervention is necessary for the DB transition. That might be less invasive than transitioning the DB and looking out for potential errors. It can give users a heads-up, like "Your DB is safe to be transitioned to the new, high-performance data structure."

[soininen]

In my view, once we have the Alembic migration, all JSON values in all databases get migrated to the new format and we only need to care about providing backward compatible access to the old values (and in case of SpineOpt, to the old JSON, too).

I have outline my "vision" below. I am sure I have overlooked something important.

Note, that JSON/JSON blob below may refer to both the binary JSON blob that we store in the database or the Python structure we get when we load that blob (using json.loads).

The parameter_value model stays as our compatibility layer. parameter_value.from_database() and parameter_value.to_database() are responsible for the value <-> JSON transformation so those need to be modified to deal with the new JSON format:

parameter_value	Input	Output
.from_database()	new JSON	old values
.to_database()	old values	new JSON

With this, current Python code should "just work" with minimal changes. Fingers crossed. Really.

The new Arrow tables will be available via the arrow_value module. arrow_value.from_database() and arrow_value.to_database() deal with new JSON and new types exclusively:

arrow_value	Input	Output
.from_database()	new JSON	new Arrow values
.to_database()	new Arrow values	new JSON

Code that wants to use the Arrow arrays uses arrow_value instead of parameter_value. I think this would allow us to transition to the new type in Toolbox in as incremental way as possible, though I am afraid we are still forced to make large, sweeping changes.

The tougher part is SpineInterface/SpineOpt which get the parameter values as raw JSON blobs through PyCall or Spine DB server. To my understanding, regardless of whether PyCall or the DB server is used, most of that data goes through the import_functions/export_functions interfaces. Luckily, both interfaces support injecting "parse" and "unparse" functions into the code which default to from_database() and to_database from parameter_value module. DB server uses this injection to actually keep the JSON as JSON - we can extend that to transform between the new and old style JSON:

spine_db_server	Input	Output
._parse_value()	new JSON	old JSON
._unparse_value()	old JSON	new JSON

I think this should make most of current SpineInterface/SpineOpt work out-of-the-box with migrated databases if run under Toolbox (which means DB server is being used) but I confess I do not know how to deal with PyCall. I hope some similar option would be available there, too.

Last, but not least, we have ParameterDefinitionItem, ParameterValueItem and ListValueItem which give access to the JSON blob and the value. Here, I think we can employ similar tactic as with from_database and to_database above:

Key	Value
"value" / "default_value"	New JSON
"parsed_value	old value
"arrow_value"	new Arrow value

The radical change here is that item["value"] would provide the new JSON blob instead of the old one. This should not have any effect on the Toolbox code I know of (except some unit tests that can be fixed) since you newer use the blob as-id but pass it to from_database(). I would be surprised if this really changed anything to anyone.

The old parsed value is available behind the "parsed_value" key and the Arrow equivalent behind "arrow_value", no changes here.

There is probably a lot of devils in the details, but that is basically it. Let me know if you have any questions or comments.

[soininen]

A rough draft of next steps from the top of my mind:

• Implement arrow_value.to_database() that writes the new JSON (@soininen is currently working on this)
• Modify arrow_value.from_database() to read the new JSON
• Modify parsed_value.to_database() to write the new JSON
• Modify parsed_value.from_database() to read the new JSON

This would allow one to test migrating a database and using it in Toolbox.

All this should be done in a development branch.

[suvayu]

I'll respond to your detailed comment later (after some thinking)

All this should be done in a development branch.

Definitely. I don't know how you want to do this. Right now the draft PR is on WIP-data-transition, we can treat that branch as our target branch for the 4 tasks you list above (and any subsequent related PRs).

[soininen]

In my view, once we have the Alembic migration, all JSON values in all databases get migrated to the new format and we only need to care about providing backward compatible access to the old values (and in case of SpineOpt, to the old JSON, too).

This is revision 1 of my comment above. In the original, I discussed about two sets of from_database()/to_database() functions but I overlooked the fact that we need just a single to_database().

I have outline my "vision" below. I am sure I have overlooked something important.

Note, that JSON/JSON blob below may refer to both the binary JSON blob that we store in the database or the Python structure we get when we load that blob (using json.loads).

After migration, the value and default_value columns contain JSON only in the new format. parameter_value.to_database() need to be changed such that it outputs the new format. It should keep on accepting the legacy value types for backwards compatibility while also accept the new Arrow types.

For reading the new JSON format, we need two versions of from_database(). parameter_value.from_database() is the logical backwards compatible function taking the new JSON as input and returning the legacy types. Current code that uses the legacy types would not need any changes. For code that uses the new Arrow types, I prefer to have a separate function arrow_value.from_database() that takes the new JSON and returns the new types.

If we implement things as outlined above, current code using from_database()/to_database() should work as before even though the data is stored in the new JSON format. When we want to migrate code to use the new Arrow types, one needs to switch from parameter_value.from_database() to arrow_value.from_database() and modify other parts of the code accordingly.

SpineInterface/SpineOpt get the parameter values as raw JSON through Spine DB server or PyCall. I think it is best if we modify SpineInterface to work with the new JSON as part of the transition process. In case this proves infeasible, we can fall back to doing new JSON <-> old JSON conversions in Spine DB server. There are some callback readily available for that.

Last, but not least, we have ParameterDefinitionItem, ParameterValueItem and ListValueItem which give access to the JSON blob and the value. Here, I think we can employ similar tactic as with from_database and to_database above:

Key	Value
"value" / "default_value"	New JSON
"parsed_value	old value
"arrow_value"	new Arrow value

The radical change here is that item["value"] would provide the new JSON blob instead of the old one. This should not have any effect on the Toolbox code I know of (except some unit tests that can be fixed) since you newer use the blob as-id but pass it to from_database().

The old parsed value is available behind the "parsed_value" key and the Arrow equivalent behind "arrow_value". This is actually what we already have.

There is probably a lot of devils in the details, but that is basically it. Let me know if you have any questions or comments.