Database Properties
Database Type: PostgreSQL - 10.15 (Debian 10.15-1.pgdg100+1)
Schema pgmetadata
PgMetadata - contains tables for the QGIS plugin pg_metadata
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| dataset_contact | 0 | 2 | 4 | 0 | Table | Pivot table between dataset and contacts. |
| contact | 1 | 0 | 5 | 0 | Table | List of contacts related to the published datasets. |
| theme | 0 | 0 | 4 | 0 | Table | List of themes related to the published datasets. |
| qgis_plugin | 0 | 0 | 4 | 0 | Table | Version and date of the database structure. Useful for database structure and glossary data migrations between the plugin versions by the QGIS plugin pg_metadata |
| link | 0 | 1 | 9 | 0 | Table | List of links related to the published datasets. |
| dataset | 2 | 0 | 25 | 0 | Table | Main table for storing dataset about PostgreSQL vector layers. |
| glossary | 0 | 0 | 14 | 136 | Table | List of labels and words used as labels for stored data |
| html_template | 0 | 0 | 3 | 0 | Table | This table contains the HTML templates for the main metadata sheet, and one for the contacts and links. Contacts and links templates are used to compute a unique contact or link HTML representation. |
| v_glossary | 0 | 0 | 1 | 0 | View | View transforming the glossary content into a JSON helping to localize a label or description by fetching directly the corresponding item. Ex: SET SESSION “pgmetadata.locale” = ‘fr’; WITH glossary AS (SELECT dict FROM pgmetadata.v_glossary) SELECT (dict->‘contact.contact_role’->‘OW’->‘label’->‘fr’)::text AS label FROM glossary; |
| v_dataset_as_dcat | 0 | 0 | 4 | 0 | View | DCAT - View which formats the datasets AS DCAT XML record objects |
| v_valid_dataset | 0 | 0 | 3 | 0 | View | Gives a list of lines from pgmetadata.dataset with corresponding (existing) tables and views. |
| v_dataset | 0 | 0 | 24 | 0 | View | Formatted version of dataset data, with all the codes replaced by corresponding labels taken from pgmetadata.glossary. Used in the function in charge of building the HTML metadata content. |
| v_table_list | 0 | 0 | 3 | 0 | View | View containing list of all tables in this database with schema name |
| v_export_table | 0 | 0 | 25 | 0 | View | Generate a flat representation of the datasets. Links and contacts are grouped in one column each |
| v_locales | 0 | 0 | 1 | 0 | View | Lists the locales available in the glossary, by listing the columns label_xx of the table pgmetadata.glossary |
| v_contact | 0 | 0 | 8 | 0 | View | Formatted version of contact data, with all the codes replaced by corresponding labels taken from pgmetadata.glossary. Used in the function in charge of building the HTML metadata content. The localized version of labels and descriptions are taken considering the session setting ‘pgmetadata.locale’. For example with: SET SESSION “pgmetadata.locale” = ‘fr’; |
| v_orphan_dataset_items | 0 | 0 | 3 | 0 | View | View containing the tables referenced in dataset but not existing in the database itself. |
| v_schema_list | 0 | 0 | 2 | 0 | View | View containing list of all schema in this database |
| v_link | 0 | 0 | 12 | 0 | View | Formatted version of link data, with all the codes replaced by corresponding labels taken from pgmetadata.glossary. Used in the function in charge of building the HTML metadata content. |
| v_orphan_tables | 0 | 0 | 3 | 0 | View | View containing the existing tables but not referenced in dataset |
| v_table_comment_from_metadata | 0 | 0 | 5 | 0 | View | View containing the desired formatted comment for the tables listed in the pgmetadata.dataset table. This view is used by the trigger to update the table comment when the dataset item is added or modified |