generate_sql_schema - enum clashes

[rokf]

Hello,

I've recently found out about the generate_sql_schema method that is implemented in this repository. I suppose that the mentioned method is used to generate schema.sql files which are present across the various model-specific repositories.

We've discussed the fact that one currently can't simply take all the schema.sql files and apply them to the same database because some of them have enums that have the same name because some of the columns in the various models have the same name - smart-data-models/dataModel.Device#35.

I believe that this should be fixed and I (or one of my colleagues) can help with a PR if that'd be desired. We'd obviously follow the contribution guidelines - we've already looked at those.

In the linked issue multiple potential solutions were identified:

• addition of a table prefix for the enums, i.e. Distribution_status_type and DeviceOperation_status_type instead of status_type
• YAML enums could be encoded as plain text columns (potentially acceptable, information loss)
• enums could be encoded as check constraints instead, i.e. status TEXT NOT NULL CHECK (status IN ('cancelled','finished','ongoing','planned','scheduled')),

Which solution would you propose that we go forward with?

Thank you!

[rokf]

I suppose that since these enums aren't meant to be reused across tables and each schema.sql file is considered something that should work standalone using check constraints could be considered the best approach?

I also assume that the idea is that we'll keep it at a single schema.sql file for each model, which means that whenever the schema changes one would have to drop the table and recreate it or apply patches to the schema.sql file (make it idempotent - use alter table ... instead of altering the create table ... statement and use checks like if not exists) 🤔

https://stackoverflow.com/questions/10923213/postgres-enum-data-type-or-check-constraint