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

Replace the database with a backup

It is possible (for users with the role admin or manager) to make SQL dumps via the backend anytime: admin -> data -> SQL export. Also, every time the script, which is used at an upgrade to a new OpenAtlas version, is called before doing database changes. These backups are located at files/export and because the date of creation is included in the filename it is easy to see when they were made. Of course it is good practice to store these SQL backups on another machine too, e.g. in case of a hard disk failure, but this is outside the scope of OpenAtlas.

In case these backups are needed, lets say for example an import went wrong, the workflow on a Debian machine would be as follows. The commands are executed as postgres user and it is assumed the database and user are called openatlas.

  • Delete the current database
    dropdb openatlas
  • Create a new, empty database with the owner openaltas
    createdb openatlas -O openatlas
  • Import the (unpacked) backup into the new database
    psql openatlas < path/to/the/unpacked/backup.sql


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 --rows-per-insert 10000 -a -t model.cidoc_class -t model.cidoc_class_i18n -t model.cidoc_class_inheritance -t -t model.property_i18n -t model.property_inheritance > install/2_data_model.sql

Single Schema

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

Add update trigger for modified field

Replace schema.table and execute:

CREATE TRIGGER update_modified BEFORE UPDATE ON schema.table FOR EACH ROW EXECUTE PROCEDURE model.update_modified();

Export database for Windows

pg_dump -Fc openatlas > openatlas.dump

to ingest in windows

C:\Program Files\PostgreSQL\12\bin\pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "openatlas" --clean --verbose "path to dump file" 

Reset Demo


Find used but missing places

e.g. after a case study separation (not sure if this statement is now showing really missing or just needed locations)

SELECT,, e.system_type FROM model.entity e
WHERE e.class_code = 'E53' AND e.system_type = 'place location' AND IN (
   SELECT FROM lr JOIN model.entity r ON lr.range_id = AND lr.property_code IN ('P74', 'OA8', 'OA9', 'P7'))
   AND NOT IN (SELECT range_id FROM WHERE property_code = 'P53');

Delete orphaned locations

DELETE FROM model.entity WHERE id IN (
    SELECT id FROM model.entity WHERE system_type = 'place location' AND id NOT IN (
        SELECT FROM model.entity e JOIN l ON = l.range_id AND l.property_code = 'P53' 
        WHERE e.class_code = 'E53' AND e.system_type = 'place location'));

Import database to Windows

Make a dump with inserts

pg_dump --attribute-inserts openatlas > openatlas.sql

In the pgadmin tool, select the corresponding database, run the query tool and select the open file option.
Load the provided dump file and execute (F5) it.
Login and adapt user as needed.

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.

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

Updated by Alexander Watzinger 10 months ago · 69 revisions

Also available in: PDF HTML TXT