The SQL builder output requires manual editing for sqlite3 (a Python package), because sqlite3 does not allow foreign keys to be added via ALTER TABLE.
Describe the solution you'd like:
I would like the option to have the SQL builder write foreign key definitions into each table upon creation, even though this will create order dependencies. The order dependencies can be minimized via:
- Write all the enumeration tables first.
- Allow the user to specify an ordering of tables to be written early, e.g., IdentifiedObject, BaseVoltage, PowerSystemResource, ConnectivityNodeContainer, ConnectivityNode, EquipmentContainer, Equipment, TransformerEnd, GeneratingUnit, ConductingEquipment, etc.
Of course it would be nice to have the tables automatically sorted so every foreign key reference has already been defined, but that might not be worth the effort. If the builder can refrain from writing ALTER TABLE statements and write the enumerations first, that would save significant editing time for use with sqlite3.
Describe alternatives you've considered:
Internet search highlights a code-based method of creating a new table in sqlite3, copying the column definitions, adding the foreign keys, dropping the old table, finally renaming the new table to the old name. This has to be done for every table that has an ancestor class and/or a regular foreign key, so it seemed like a lot of work. Instead, I manually edited the CIMTool output sql file, which took a couple of hours. That's not so bad, but it will have to be redone whenever the profile changes and especially when it expands.
The SQL builder output requires manual editing for sqlite3 (a Python package), because sqlite3 does not allow foreign keys to be added via ALTER TABLE.
Describe the solution you'd like:
I would like the option to have the SQL builder write foreign key definitions into each table upon creation, even though this will create order dependencies. The order dependencies can be minimized via:
Of course it would be nice to have the tables automatically sorted so every foreign key reference has already been defined, but that might not be worth the effort. If the builder can refrain from writing ALTER TABLE statements and write the enumerations first, that would save significant editing time for use with sqlite3.
Describe alternatives you've considered:
Internet search highlights a code-based method of creating a new table in sqlite3, copying the column definitions, adding the foreign keys, dropping the old table, finally renaming the new table to the old name. This has to be done for every table that has an ancestor class and/or a regular foreign key, so it seemed like a lot of work. Instead, I manually edited the CIMTool output sql file, which took a couple of hours. That's not so bad, but it will have to be redone whenever the profile changes and especially when it expands.