DEPRECATED - HAPI database schemas

SQL schemas for the Humanitarian API (HAPI) from the Centre for Humanitarian Data.

In this release, the schemas are designed for SQLite for early development and testing, but may/should also work with PostgreSQL.

Prerequisites

• Docker
• Unix make command

Usage

Download the PostgreSQL docker image:

$ docker pull postgres

To create a test database with simple sample data:

$ make

It is also possible to make an empty database for testing:

$ make empty

Note that the database username and password are hard-coded directly in the docker-compose file, as it's only meant for testing. Also, the database will be mounted to $HOME/postgres_data.

To explore the database:

$ docker exec -it hapi-schema-db psql -U postgres -d hapi

Design notes

There is a denormalised view in sql/views corresponding to every table type in sql/schema. On the API side, we will always reference the views rather than the tables; that way, if the underlying table structures change, the views can insulate our API code.

Any changes to the tables in sql/schema/hapi-core-tables.sql need to be reflected in the data-type-specific tables and in the views in sql/views/; otherwise, the database may break.

Files

File	Description
README.md	You are here.
UNLICENSE.md	Public-domain non-license.
Makefile	Build file for the test data.
./sql/schema/	Definitions for the database tables, relational-dependencies, and indices.
./sql/schema/hapi-core-tables.sql	Definitions for the core tables (used by more than one type of data)
./sql/schema/hapi-op-tables.sql	Definition for the operational-presence table. Depends on hapi-core-tables.sql
./sql/schema/hapi-pop-tables.sql	Definition for the baseline-population table. Depends on hapi-core-tables.sql
./sql/views/	SQL view definitions. The API application layer code should always use these, never the tables themselves.
./sql/views/hapi-core-views.sql	SQL views for the core tables. You must create the tables in ./sql/schema/ first
./sql/views/hapi-op-views.sql	SQL view for the operation-presence table.
./sql/views/hapi-pop-views.sql	SQL view for the baseline-population table.
./sql/samples/	Very simple sample data to test the database and views. You must create the tables in ./sql/schema/ first.
./sql/samples/core-data.sql	Sample data for the core tables.
./sql/samples/op-data.sql	Sample data for the operational-presence table.
./sql/samples/pop-data.sql	Sample data for the baseline-population table.
./databases/	SQLite3 test databases.
./databases/hapi-test.sql	Test database build by the Makefile

As we progress, we can import larger sample datasets from CSV instead of declaring them in SQL.

Automated tests

Coming soon.

Unlicense

This code and sample data is in the PUBLIC DOMAIN (see UNLICENSE.md).