Changes between Version 68 and Version 69 of PetascopeDevGuide

Dec 30, 2015 3:21:53 PM (21 months ago)



  • PetascopeDevGuide

    v68 v69  
    216216A final note: to avoid clashes with old existing tables during the upgrade while not breaking running services, new tables have a temporary `ps9` prefix while co-existing with the other pre-upgrade tables: at the end of a successful [browser:applications/petascope/src/main/db/petascope/ upgrade], such tables (+ primary indexes) are renamed back to the usual `ps` prefix. Some non-primary indexes and constraints might still harmlessly have `ps9` prefix though. Check the [ discussion] in the mailing list for further insights.
     219=== Updating the database schema: is this below more than duplicate? ===
     221The update process for the database schema of `petascopedb` is organized by means of a set of `update<N>.*` files, which are excecuted in a loop by [browser:applications/petascope/src/main/db/].
     223Not all of them represent actual ''updates'': there can be ''upgrades'' as well. The major 9.0 [browser:applications/petascope/src/main/db/petascope/ upgrade script] for instance creates a completely new set of tables: to avoid the deletion of important data, existing tables are moved to a backup [ schema], whose name is `'ps_pre_update<N>'`, being `update<N>` the basename (no suffix) of the latest ''pre-upgrade'' db status. The upgrade then proceeds with i) the creation of new tables and db objects (triggers, procedures, indexes) inside an interim schema, ii) the migration of pre-existing coverages' metadata to these new tables and iii) moving the old tables to a backup schema. The new upgraded schema is finally published to the default `public` schema of Postgres. WMS tables, whose [browser:applications/petascope/src/main/db/petascope/update5.sql schema] has not changed ever since, are simply moved to the `public` schema.
     225Here is the synopsis of  ``:
     227usage: [--revert] [--cleanup] [--help]
     230    Restore tables to a pre-upgrade backed-up schema (ps_pre_updateX).
     232    Drops all schemas in petascopedb, but the public one.
     234    Show this message.
     237We see that we have two options:
     239   i. '''`--revert`''' : moves WMS tables back to the backup schema; drops the new tables and restores the old tables: the final db status will be exactly the same as before running the last upgrade.
     240   i. '''`--cleanup`''' : drops the backup schema in `petascopedb`.
     242Calling the script on an already synchronized database will have no effect.
     244The database of geo-metadata is generally a small database, which hardly reaches relevant sizes.
     245While one can restore a pre-upgrade snapshot with the `--revert` option (if the snapshot has not been cleaned up), it is then suggested to keep a backup dump of the database: an additional layer of backup at almost no cost.
     247Before starting the upgrade, you might want to understand more in detail what is done by the migration script. The following list explains the flow of operations what are done by the [browser:applications/petascope/src/main/db/petascope/update8/migrate.sql migration script] (pre-upgrade and post-upgrade tables will be prefixed `'ps8'` and `'ps9'` respectively):
     249   * main : `db_migration()`
     250      * `migrate_uoms()` : migrate the catalog of '''Unit of Measures''' (UoM) from `ps8_uom` to `ps9_uom`/`ps9_quantity`; a UoM in the previous schema can indeed be seen as a minimal informational basis for a SWE field.
     251      * `migrate_crss()` : migrate the catalog of '''Coordinate Reference Systems''' (CRSs) from `ps8_crs` to `ps9_crs`; mind here that all CRSs are migrated but from version 9.0 the CRSs shall compulsorily be an actionable HTTP URI resolving to a valid GML definition of CRS. Additionally, in order to let the migrator understand whenever a CRS defines latitude first it is suggested to either turn all CRS in `ps8_crs` to URIs ''`.../def/crs/ESPG/0/<code>`''or to ''`EPSG:<code>`'' format (indeed only ESPG codes can be recognized to define latitude first: see this [browser:applications/petascope/src/main/db/petascope/update8/north_first_crss.sql table]); `CRS:1` should be left as is instead: it will be turned to an appropriate [IndexCrss index] CRS.
     252      * forall '''coverages''' in `ps8_coverage` do `migrate_coverage()`:
     253         * fetch GMLCOV coverage type from `ps8_coverage` fill in `ps9_coverage` table  .. GMLCOV types need to appear to be ''grid'' coverages (~"*Grid*") and must be a legal grid coverage value (see ''" GML coverage types"'' in [browser:applications/petascope/src/main/db/petascope/update8/populate.sql this] file), otherwise the coverage is not migrated;
     254         * `'application/x-octet-stream'` MIME type is assigned by default (`rasdaman` data source);
     255         * migrate optionally stored GMLCOV extra metadata annotations from `ps8_metadata` to `ps9_extra_metadata`;
     256         * build the native CRS for `ps9_domain_set` by concatenating the CRSs associated to each coverage axis in `ps8_crsset` and filling URIs in `ps9_crs` (then referenced by `ps9_domain_set`): axes with __type__ `'t'` (for ''temporal'', see `ps8_axistype`) are assigned the `OGC:AnsiDate` CRS URI (see time handling [wiki:PetascopeTimeHandling_8_5 prior to] and [PetascopeTimeHandling from] 9.0) while axes with `CRS:1` assignment are given an appropriate [IndexCrss index] CRS, depending on how many consecutive `CRS:1` axes are found (see `translate_crs()` function in the [browser:applications/petascope/src/main/db/petascope/update8/ utilities]);
     257         * determine the '''origin''' of the coverage for `ps9_gridded_domain_set`: axis footprint/resolution is firstly recovered from coverage extent in `ps8_domain` and number of cells in `ps8_celldomain` -- `(dom_max-dom_min)/(cdom_max-cdom_min+1)`) -- then origin is placed in the upper-left corner (bottom of cube in case of 3D, and so on) in the centre of the sample space (e.g. pixel-centre); the order of the axes defined in the CRS is strictly followed, hence e.g. in case of [ WGS84] geographic CRS, latitudes will appear first in the tuple of coordinates (see [PetascopeSubsets this] page for a more thorough description of coverage geometries in v9.0);
     258            * for `CRS:1` indexed axis, the `+1` term in the denominator is removed: the domain is like the cell domain.
     259         * '''offset vectors''' are computed for `ps9_grid_axis` and `ps9_rectilinear_axis`: again the axis order of the CRS is kept into account and `CRS:1` indexed axes will not treat the domain extent as dense but indexed (no `+1` in the denominator of the resolution formula); as origin is now in the upper-left corner in the 2D horizontal space, the vector associated with northings will point South (negative norm);
     260         * the '''range set''' of the coverage is collected for `ps9_range_set` and `ps9_rasdaman_collection`: using the [ dblink] module, the OID of the collection marray is fetched from `RASBASE` (if more than 1 marray is found in the collection, then a warning will be thrown in the final log, and only the first will be selected: a coverage is now associated to one-and-only-one marray);
     261         * the '''range type''' is built for `ps9_range_type_component` and [PetascopeDevGuide SWE-related tables]: !UoMs/Quantities migrated in the first stage of migration (see above) are here linked to their correspondent coverages; data type from `ps8_datatype` is moved to `ps9_range_data_type`.
     263A final report of coverage migrations is then printed: name and general information on coverage domain and range will be displayed for every migrated coverage. If some problem arose while migrating a coverage, an additional ''log'' column will describe what did not work. In case the mistake can be fixed (maybe changing the pre-upgrade database?), then one can just repeat the operation by reverting back (`--revert`) and re-migrating.
    218267== Limitations ==
    219268The current schema supports gridded and multipoint coverages. However, it is designed to be extensible to further types in the [ coverage hierarchy].
    223272Finally, just SWE ''quantities'' are currently supported: other kinds of SWE data fields (''category'', ''count'', etc.) can be easily extended though. SWE metadata is partially configurable (#582) and also currently partially supported by Petascope (#573).
    225 === legacy (v8.x) ===
    227 Release 9.0 of rasdaman included a complete re-design of the Petascope database schema.
    228 For v8.x devs, documentation of the old database schema can be downloaded from [ here].
    230274== WCPS grammar ==