Connect to CliDE database and check schema matches ORM models (for CliDE/Postgres)

[isedwards]

This issue requires an instance of PostgreSQL to be running with CliDE database restored (instructions for restoring the CliDE schema are given in #9).

The aim is to make one or more small changes to the CliDE database in Postgres, e.g.

ALTER TABLE stations ALTER COLUMN start_date int;
ALTER TABLE stations DROP COLUMN end_date;

When pyopencdms connects to the database we would like to have a method that can detect whether the schema is the same as our models. It is important that we:

a) detect if the schema is not exactly what we expected it to be
b) report the exact difference
c) do not change the database to match our model

Before beginning an implementation, the approach should be discussed in the comments below. It's possible that we may use alembic to detect the differences, e.g. by getting diffs

[Shaibujnr]

Hello @isedwards

when do the alter table instructions come into play?
Should those be executed before the schema detection is implemented?

[isedwards]

Step 1

The idea is to implement schema detection and then to compare a standard CliDE database schema (that doesn't have any alterations) to the ORM models that we have previously created.

Hopefully this first check will detect no differences (although this may not be the case because of the manual changes that were made to improve the representation of the indexes - we'll have to discuss what to do in this case).

Step 2

Once we have a check that is reporting the database schema is equivalent to our current ORM models then we manually apply minor schema changes to the database schema using SQL ALTER TABLE commands.

The desired outcome is that when we next run the Python schema detection it reports these new differences correctly.

Let me know if it would help to talk this through
more - and it would be good to know how things are going at each stage

[isedwards]

The current suggestion is to use compare_metadata() (see getting-diffs) to report any differences as JSON since we don't want to create a new migration file or to migrate their database - we just want to be aware that there are differences and to generate a (JSON) report of what the differences are.

[Shaibujnr]

The manual changes to the indexes were made to conform to the changes alembic detected. What I mean is, ClideDB had named indexes however sqlacodegen generated the models without the named indexes. I was able to catch this mismatch using the alembic autogenerate feature. After manually updating the indexes alembic no longer complained about the indexes.

In short, I don't think we would have an issue with indexes when comparing ClideDB database to our model. We would have an issue with the SQL Views tho. That's the only part sqlacodegen couldn't generate properly (rather it generated these as tables) and I couldn't define them manually either because SQLAlchemy has no out of the box support for defining SQL Views

[Shaibujnr]

If there is a way to perform the comparison while excluding the views, no difference should be detected. I don't think compare_metadata can do that at the moment but I came across these

• https://alembic-verify.readthedocs.io/en/latest/
• https://sqlalchemy-diff.readthedocs.io/en/latest/

The documentations for both libraries isn't detailed.
Alembic verify pretty much does what we want to do using SQLAlchemy Diff internally, but SQLAlchemy Diff is used mainly for comparing the differences between two database schemas, there must have been a way alembic-verify used sqlalchemy-diff to detect changes between SQLAlchemy Model and Database rather than Database and Database I'll have to look deeper to really understand it.

What I am interested in is the SQLAlchemy Diff's compare function that takes ignores argument, which can be a list of tables, or columns.

Since SQLAlchemy detects views as tables and we don't want to work with views at the moment, we can explicitly ignore those views when performing the comparison.

[Shaibujnr]

The approach I'd suggest is to first to use alembic's compare_metadata and compare the results with SQLAlchemy Diff's compare to ensure they both output the same result (or hopefully SQLAlchemy Diff's compare outperforms alembic's compare_metadata) and then I can go ahead with SQLAlchemy Diff to compare while ignoring the view tables as I described above.

What do you think @isedwards ?

[isedwards]

I think it's valuable to document that alembic-verify and sqlalchemy-diff exist and that they may be able to help us, especially if we need even more advanced capabilities in the future.

In this case it looks like compare_metadata is sufficient because we are in control of how we return the output, for example:

• models/clide.py:

  CLIDE_VIEWS = ['ext_equipment', 'ext_obs_aero', 'ext_obs_aws', ... ] 
  

• models/__init__.py:

  from alembic.autogenerate import compare_metadata
  
  def schema_check(ignore=None):
       ...
       diff = compare_metadata(mc, metadata)
       if ignore:
           # TODO: remove known/acceptable differences (e.g. `view in CLIDE_VIEWS`) from output
  
       return diff
  

The aim is for schema_check to be generic so that it can be reused with other supported systems in addition to CliDE. At some point we'll move it to a method of a base class.

[Shaibujnr]

alright, this makes sense. Will get on to it right away.

[Shaibujnr]

Hello Ian,

I have defined a check_schema function with the signature check_schema(metadata: Metadata, database_url: str, ignore_tables: Optional[Sequence[str]] = None). This function raises an error if the schema doesn't match the models.

The next thing to do is have a script that calls this function and then executes the alter table statements if there are no exceptions correct?

[isedwards]

Nope..!

Imagine that the SQLAlchemy models that you created are for CliDE version 1.0.

However, there is a user out there who is using CliDE version 2 - and we have no idea what the CliDE v2 schema looks like. Therefore pyopencdms is unable to work with CliDE v2.

What we want is to produce an error telling the user that pyopencdms dosn't recognise their CliDE database and the error will contain the details letting them/us know what is different to what we were expecting.

So... let's rename check_schema as get_schema_diff. Let's make it return [] if there are no differences and [(...), (...), ...] if there are differences (and not raise an error).

---

The next step is to manually alter the PostgreSQL CliDE database so that the schema is different to what we're expecting. You can do this with psql: psql -d clide_db

We can execute some SQL statements to manually mess up the database a bit:

ALTER TABLE stations ALTER COLUMN start_date int;
ALTER TABLE stations DROP COLUMN end_date;

Now, the next time you run get_schema_diff you should get a report of the differences instead of just an empty list.

Later we can do things like:

diff = get_schema_diff(Metadata, "connection_str")
if diff:
    raise RunTimeError(f"OpenCDMS is unable to support your CliDE database version. Please send the following report to info@opencdms.org: {diff}")

Let me know if it still doesn't make sense.

[isedwards]

I'm happy to skip the last step - I think it will become more clear when we're writing unit tests.

[Shaibujnr]

I perfectly understand what needs to be done now. No worries.