• Exports
  • Database Structure
  • Model Data
  • Single Schema
• Recursive Events

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

Exports¶

Database Structure¶

pg_dump -sc --if-exists -n model -n gis -n log -n web -n import openatlas > 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);