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');
CONNEC 3.20.1 update scripts¶
Remove actors from move events and add them to source
BEGIN;
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 = 939 AND e.class_code = 'E7');
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');
INSERT INTO model.link (domain_id, range_id, property_code)
SELECT el.domain_id, l.range_id, 'P67' FROM model.link l
JOIN model.entity e ON l.domain_id = e.id AND l.type_id IN (862, 1091, 943, 1046, 1045)
JOIN model.link el ON e.id = el.range_id AND el.property_code = 'P67'
JOIN model.entity s ON el.domain_id = s.id AND s.class_code = 'E33';
DELETE FROM model.link WHERE id in (
SELECT l.id FROM model.link l
JOIN model.entity e ON l.domain_id = e.id AND l.type_id IN (862, 1091, 943, 1046, 1045)
JOIN model.link el ON e.id = el.range_id AND el.property_code = 'P67'
JOIN model.entity s ON el.domain_id = s.id AND s.class_code = 'E33'
);
COMMIT;
Remove description form move events and add it to source description.
BEGIN;
UPDATE model.entity s SET description = description || E'\r\n----\r\n' || (
SELECT e.description
FROM model.entity e
JOIN model.link l ON e.id = l.range_id AND l.property_code = 'P67' and e.class_code = 'E9' AND l.domain_id = s.id AND e.id NOT IN (1672, 1617, 1612, 1673, 1674, 1433, 1421, 1663, 1619, 1435, 1444, 1443, 1596, 1603, 826, 1496, 1493))
WHERE id IN (
SELECT l.domain_id
FROM model.entity e
JOIN model.link l ON e.id = l.range_id AND l.property_code = 'P67' and e.class_code = 'E9' AND e.id NOT IN (1672, 1617, 1612, 1673, 1674, 1433, 1421, 1663, 1619, 1435, 1444, 1443, 1596, 1603, 826, 1496, 1493));
UPDATE model.entity SET description = ''
WHERE id IN (
SELECT l.range_id
FROM model.entity e
JOIN model.link l ON e.id = l.range_id AND l.property_code = 'P67' and e.class_code = 'E9' AND e.id NOT IN (1672, 1617, 1612, 1673, 1674, 1433, 1421, 1663, 1619, 1435, 1444, 1443, 1596, 1603, 826, 1496, 1493));
COMMIT;