Opened 4 years ago

Closed 3 years ago

Last modified 3 years ago

#507 closed enhancement (fixed)

Bash scripts for petascopedb new schema creation and migration

Reported by: pcampalani Owned by: pcampalani
Priority: major Milestone: Future
Component: undecided Version: development
Keywords: petascopedb script bash Cc: dmisev, pbaumann, abeccati
Complexity: Medium

Description

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.

Change History (13)

comment:1 Changed 4 years ago by pcampalani

  • Owner changed from dmisev to pcampalani
  • Status changed from new to accepted

comment:2 Changed 4 years ago by pcampalani

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.

comment:3 Changed 4 years ago by pcampalani

  • Cc dmisev pbaumann abeccati added

comment:4 Changed 4 years ago by pcampalani

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.

comment:5 Changed 4 years ago by pcampalani

  • Resolution set to fixed
  • Status changed from accepted to closed

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.

comment:6 Changed 4 years ago by 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?

comment:7 Changed 4 years ago by pcampalani

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.

comment:8 Changed 4 years ago by 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.

comment:9 Changed 4 years ago by pbaumann

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.

comment:10 Changed 3 years ago by pcampalani

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

comment:11 Changed 3 years ago by pcampalani

  • Resolution fixed deleted
  • Status changed from closed to reopened

comment:12 Changed 3 years ago by pcampalani

  • Resolution set to fixed
  • Status changed from reopened to closed

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

comment:13 Changed 3 years ago by dmisev

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

Note: See TracTickets for help on using tickets.