Bash scripts for petascopedb new schema creation and migration

[Piero Campalani]

A new schema for petascopedb is being developed, and currently SQL files are implemented for the schema definition (+ triggers and inserts) and coverages migration.

See:

$ find ./applications/petascope/src/main/db/ -name "*9*" 
./applications/petascope/src/main/db/README9
./applications/petascope/src/main/db/petascope9
./applications/petascope/src/main/db/petascope9/schema9.sql
./applications/petascope/src/main/db/petascope9/populate9.sql
./applications/petascope/src/main/db/petascope9/migrate9.sql
./applications/petascope/src/main/db/petascope9/triggers9.sql

update_petascopedb.sh must be updated itself for wrapping such SQL scripts and inhibit the user to run psql commands.

[Piero Campalani]

Scripts updated in changeset:4ef41da.
Before closing the ticket an other patch is due to restore the updateX.sql mechanism which might come in the next future.

[Piero Campalani]

changeset:4f9cbd8 : add ps9_dbupdates for future updates, and CREATE IF NOT EXISTS the tables for WMS service.

TODO restore updateN.sql (N≥1) support in update_petascopedb.sh.

[Piero Campalani]

DB updates support restored in changeset:263cb9e.

Things work now for WC*S purposes, but WMS init utilities are not yet updated to PS9 tables and are broken now. I opened a new ticket for this issue: #530.

[abeccati]

As far as I know WMS should not be affected at all by PS9_ tables or did I miss something? I mean, you can still publish rasdaman collections directly if you know the geo parameters for it, right?

[Piero Campalani]

Hi Alan,
look at #530: they are affected.
The scripts know the geo-bounds because they fetch them from WC*S tables in petascopedb.
In future we can add a parameter to the utilities to let a user set the bounds and publish collections which are not registered in petascopedb, but that's currently not possible.

[abeccati]

I see, still I talk about the WMS service itself, if one inputs the geo parameters directly in WMS tables (not taking them from the coverage ones), a rasdaman collection can still be served as map layer. It is fundamental for existing WMS import tools that directly load their maps into the WMS.

[Peter Baumann]

Maybe I am misunderstanding something, so I'm awaiting to stand corrected.

Naively, a WMS should use the coverage directly, ie: a layer represents a physical coverage or some derivative (via the rasql code fragments, which currently are confined to induced expressions).
However, one might think about decoupling WMS layers from coverages; in this case, we should establish terminology and use that for documentation in the WMS part.

I suggest to discuss this and come up with a design decision; hope we can make this decision before final 9.0 roll-out, as it will affect future WMS behavior.

[Piero Campalani]

Re-opening: see ​this discussion in the dev list.

[Piero Campalani]

Fixed in changeset:8cf4169.
Now petascope tables will continue having PS_ prefix also after upgrade.
Documentation for devs upcoming on the wiki.

[Dimitar Misev]

Note it's also documentation for users, to ingest data they need to know the schema.