OpenAtlas

CONNEC find move event with missing from/to location¶

SELECT move.id, from_link.range_id, to_link.range_id FROM model.entity move 
LEFT JOIN model.link from_link ON move.id = from_link.domain_id AND from_link.property_code = 'P27'
LEFT JOIN model.link to_link ON move.id = to_link.domain_id AND to_link.property_code = 'P26'
WHERE move.openatlas_class_name = 'move' AND from_link.range_id IS NULL AND to_link.range_id IS NOT NULL
ORDER BY move.id;

CONNEC 6.1.0 event update¶

UPDATE model.entity SET class_code = 'E9', system_class = 'move' WHERE id IN (
SELECT id FROM model.entity WHERE id in (7511, 8215, 8420, 8422, 8770) AND class_code = 'E7');

UPDATE model.entity SET class_code = 'E9', system_class = 'move' WHERE id IN (
SELECT id FROM model.entity WHERE id in (1124, 1418, 950, 1409) AND class_code = 'E7');

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;

Join references

UPDATE model.link SET domain_id = 3204
WHERE property_code = 'P67' AND domain_id IN 
(3216, 3218, 3222, 3230, 3234, 3238, 3285, 3287, 3705, 3713, 3715, 3719, 3745, 3815, 3845, 3849, 3853, 4272, 4276, 4281, 4286, 4293, 4297, 4301, 4367, 4369, 4374, 4494, 4731, 4735, 4742, 4926, 5130, 5132, 5137, 5141, 5148, 5149, 5154, 5158, 5232, 5236, 5240, 5245, 5264, 5269, 5273, 5277, 5281, 5286, 5290, 5294, 5295, 5299, 5303, 5307, 5311, 5315, 5319);

DELETE FROM model.entity WHERE id IN
(3216, 3218, 3222, 3230, 3234, 3238, 3285, 3287, 3705, 3713, 3715, 3719, 3745, 3815, 3845, 3849, 3853, 4272, 4276, 4281, 4286, 4293, 4297, 4301, 4367, 4369, 4374, 4494, 4731, 4735, 4742, 4926, 5130, 5132, 5137, 5141, 5148, 5149, 5154, 5158, 5232, 5236, 5240, 5245, 5264, 5269, 5273, 5277, 5281, 5286, 5290, 5294, 5295, 5299, 5303, 5307, 5311, 5315, 5319);