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.
- Docker
- Unix make command
Download the PostgreSQL docker image:
$ docker pull postgresTo 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
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.
| 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.
Coming soon.
This code and sample data is in the PUBLIC DOMAIN (see UNLICENSE.md).
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent