Create SQLAlchemy ORM models for CliDE CDMS

[isedwards]

This issue will begin to add support for CliDE, a Climate Data Management System (CDMS) used in the Pacific.

You will need PostgreSQL installed and then to restore the CliDE SQL DDL file here.

I don’t think it is possible to use automap to write the model classes to a .py file. However, it is possible to do this with sqlacodegen. This will create all of the classes automatically and will also create code to represent all of the relationships between tables.

The final step for the first task is to create a fork of pyopencdms, add the generated models to /opencdms/models/clide.py and then submit a pull request.

I rough outline of the commands is below:

# Use psql to create a database called clideDB (this can be owened by the `postgres` user
$ git clone https://github.com/opencdms/opencdms-test-data.git
$ psql clideDB < opencdms-test-data/schemas/clide/clidedb_schema.sql

You’ll need to fork https://github.com/opencdms/pyopencdms so that you can push your changes

$ git clone https://github.com/<your_username>/pyopencdms.git   # (your fork)
$ cd pyopencdms
$ pip3 install sqlacodegen psycopg2
$ sqlacodegen postgresql+psycopg2://localhost/clideDB > opencdms/models/clide.py 
$ git add .
$ git commit -m "SQLAlchemy models for CliDE"
$ git push origin
Then a pull request…

In addition, verify the code it generates:

We have 44 tables and therefore at least 44 models to be generated. Try to validate the generated code against alembic and perform some manual checks for at least 3 hours.

[Shaibujnr]

Check: Create SQLAlchemy models for ClideDB PR

• Created a postgresql db locally called clideDB owned by my user (can as well be owned by postgres)
• Cloned the test data repo ($ git clone https://github.com/opencdms/opencdms-test-data.git)
• Executed ($ psql clideDB < opencdms-test-data/schemas/clide/clidedb_schema.sql) to update the database schema with the clideDB schema.
• Setup python virtual environment using pyenv and activated the environment
• Ran pip install sqlacodegen psycopg2 alembic pandas
• Ran sqlacodegen postgresql+psycopg2://localhost/clideDB > opencdms/models/clide.py

Sqlacodegen was able to generate all 44 models however on validating the generated code.

• Sqlacodegen detected sql views in the clideDB database and output those views as SQLAlchemy table classes
• This issue doesn't seem to originate from sqlacodegen as alembic also detects sql views as tables. Therefore it is assumed that this has something to do with the Core SQLAlchemy Library.
• Currently, SQLAlchemy has no direct support for views (there are workarounds and extension libraries like https://github.com/jklukas/sqlalchemy-views).
• Sqlacodegen didn't generate the model indexes correclty, clideDB had named indexes for a lot of it's columns and I had to fix this manually.

N.B.: SQLAlchemy Utils also has a solution for SQL views (https://github.com/kvesteri/sqlalchemy-utils/blob/master/sqlalchemy_utils/view.py, https://github.com/kvesteri/sqlalchemy-utils/blob/master/tests/test_views.py) and https://github.com/olirice/alembic_utils has a solution for alembic to detect sql views as views, however, this extension library is tailored specifically for postgresql.

System Environment: MacOs Bigsur
IDE: VSCode
Python Version: 3.8.1

[isedwards]

@Shaibujnr - in the comments above, you wrote:

• Sqlacodegen didn't generate the model indexes correclty, clideDB had named indexes for a lot of it's columns and I had to fix this manually.

Could you provide the alembic command that you used to create the code that you then used to manually fix the models (in this commit)?

Also, do you know that the consequences woiuld have been if we had not made the manual changes and just used the original output of sqlacodegen? Would the models still have worked correctly?

[Shaibujnr]

@isedwards alembic revision --autogenerate generates a migration file for the difference between the current database schema and the defined SQLAlchemy models.

As regarding the consequences of not fixing the indexes, I doubt we would have any problems. The db engine automatically uses its indexes to optimize queries regardless of what's defined on SQLAlchemy. I only see an issue when SQLAlchemy tries to explicitly specify an index for the db engine to use for a query and then there'll be a conflict.