Local SQLite database¶
A single database file as specified in config.ini is used.
The database includes three tables which gets only populated when something went wrong.
person: All details regarding a person recordfailure: Categorization of import errors. It is automatically sourced from the code (seeexceptionsfolder) upon database creation.person_failure: The linking table between a person and a failure category.
Check smartvadhis2/core/models.py for the database schema.
If there is ever a need to move to a full-blown DBMS (e.g. Postgres, Redshift) it is hypothetically easy to switch since it relies on an ORM (Object-relational mapping) - namely SQLAlchemy.
Querying and exporting¶
A command-line tool to query and export from the local database is included, intended for report-style exports.
Use the records command (check kennethreitz/records for details)
pipenv run records 'select first_name, surname, cause_of_death from person' --url=sqlite:///db/smartva-dhis2.db
would for example yield:
first_name|surname |cause_of_death
----------|---------|-----------------------
A amin |Skywalker|Homicide
Joan |Ark |Diabetes
Han |Solo |Congenital malformation
To export it as a CSV file:
pipenv run records 'select sid, surname, cause_of_death from person' csv --url=sqlite:///db/smartva-dhis2.db > export.csv
SQL snippets¶
Get the different error code count since a certain date:
SELECT count(pf.failureid), f.failuredescription
FROM person_failure pf
JOIN failure f
ON f.failureid = pf.failureid
WHERE pf.created >= '2018-04-24'
GROUP BY pf.failureid;
Get the names and SIDs of attempted imports but were already in DHIS2 (duplicates):
SELECT p.first_name, p.first_name_2nd, p.surname, p.sid
FROM person p
JOIN person_failure pf
ON pf.personid = p.personid
WHERE pf.failureid = 704;
Supported export formats¶
csv, tsv, json, yaml, html, xls, xlsx, dbf, latex, ods
Backup¶
It is advised to automate a backup of the local database (which is just a file) to a secure remote location, preferably keeping old versions (instead of replacing it every time).
Standard free and open source command-line tools for backing up files remotely: