Project

General

Profile

Below are some unsorted database related code snippets used in development.

Exports

Database Structure

Export the database structure from the test database (used to avoid specialties in production databases) into install/1_structure.sql

pg_dump -sc --if-exists -n model -n gis -n log -n web -n import openatlas_test > install/1_structure.sql

Model Data

pg_dump openatlas --inserts -a -t model.class -t model.class_i18n -t model.class_inheritance -t model.property -t model.property_i18n -t model.property_inheritance > data_model.sql

Single Schema

pg_dump -n web openatlas > /tmp/openatlas_web.sql
pg_dump -n model openatlas > /tmp/openatlas_web.sql

Recursive Events

To get all child events of a given event the SQL below (replace ROOT_EVENT_ID at bottom). It works but is slow and could be improved.

WITH RECURSIVE tree AS (
  SELECT e.id, ARRAY[]::INTEGER[] AS ancestors
  FROM model.entity e
  WHERE (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117')) IS NULL 
  UNION ALL 
  SELECT e.id, tree.ancestors || 
    (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117'))
  FROM model.entity e, tree
  WHERE (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117')) = tree.id
)
SELECT * FROM tree WHERE ROOT_EVENT_ID = ANY(tree.ancestors);

CONNEC 3.20.0 update scripts

Transform all events with type letter exchange to move event:

UPDATE model.entity SET class_code = 'E9' WHERE id IN (
    SELECT e.id FROM model.entity e
    JOIN model.link l ON e.id = l.domain_id AND l.range_id = 639 AND e.class_code = 'E7');

Update all move locations to start locations:

UPDATE model.link SET property_code = 'P27' WHERE id IN (
    SELECT l.id FROM model.link l
    JOIN model.entity e ON l.domain_id = e.id AND l.property_code = 'P7' AND e.class_code = 'E9');