Version the CQ database schema and have a utility to upgrade the database.

[dbrnz]

The CQ database’s schema needs to be versioned in order to detect when changes are made to it.

One solution is to add a metadata table (with name and value columns) to the database and use it to store the current schema version, and provide a tools/cq_upgrade.py utility to then check versions and upgrade when needed. This is similar to what is done in mapknowledge/init.py for knowledgebase.db.

The schema version should also be a constant in the map server's competency_query.py so that it can detect and warn when a schema upgrade is needed, instead of giving missing table errors.

[dbrnz]

@napakalas thanks for this.

A few improvements to make things more robust:

• For consistency with flatmap servers, the environment settings need to be COMPETENCY_USER and COMPETENCY_DATABASE (see mapserver/competency/__init__.py)
• The default mode of operation should be to check if an upgrade is needed, with an --upgrade option set to actually upgrade (i.e. to be fail safe)
• To show the name of the database checked when reporting on its status.
• To prompt the user that they should have backed up the database before upgrading and then get confirmation before proceeding with the upgrade. You could even suggest the backup command, e.g. pg_dump -s -d "DATABASE_NAME" > DATABASE_NAME.backup.sql with DATABASE_NAME substituted by the name of the actual database.

[dbrnz]

@napakalas a crash when trying to actually do an upgrade...

$ python tools/cq_upgrade.py --check-only
INFO:root:Current competency schema version: None
INFO:root:Required competency schema version: 1.1
WARNING:root:Competency schema version 1.1 is required but database has version None. Run `python tools/cq_upgrade.py` to upgrade the schema.

$ pg_dump -s -d "map-knowledge" > cq_staging_backup.sql

$ python tools/cq_upgrade.py
INFO:root:Current competency schema version: None
INFO:root:Required competency schema version: 1.1
INFO:root:Upgrading competency schema...
Traceback (most recent call last):
  File "/home/ubuntu/flatmap-devel/map-knowledge/tools/cq_upgrade.py", line 177, in <module>
    main()
  File "/home/ubuntu/flatmap-devel/map-knowledge/tools/cq_upgrade.py", line 167, in main
    upgrade_schema(db)
  File "/home/ubuntu/flatmap-devel/map-knowledge/tools/cq_upgrade.py", line 123, in upgrade_schema
    cursor.execute(statement)
  File "/home/ubuntu/flatmap-devel/map-knowledge/.venv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.SyntaxError: syntax error at or near ":"
LINE 1: ALTER TABLE metadata OWNER TO abi:knowledge

[napakalas]

Thanks Dave for point this out.
I am updating and checking this now.

[dbrnz]

The crash is because KNOWLEDGE_USER is needed for the Postgresql connection yet the database owner is just abi (i.e. KNOWLEDGE_USER.split(':')[0].

I have patched things locally and successfully updated the devel/staging CQ database but will wait for your tweaks before creating package releases.

[napakalas]

yeah, abi:knowledge is the combination of username and password. I should handle that case.