SQLAlchemy models and DDL and ERD generation for chop-dbhi/data-models style JSON endpoints.

Web service available at http://dmsa.a0b.io/

In your shell, hopefully within a virtualenv:

pip install dmsa

In python:

from dmsa.omop.v5.models import Base

for tbl in Base.metadata.sorted_tables():
    print tbl.name

Or:

from dmsa.pedsnet.v2.models import Person, VisitPayer

print VisitPayer.columns

These models are dynamically generated at runtime from JSON endpoints provided by chop-dbhi/data-models-service, which reads data stored in chop-dbhi/data-models. It should be simple to add modules for any additional data models that become available, but the currently provided ones are:

• OMOP V4 at omop.v4.models
• OMOP V5 at omop.v5.models
• PEDSnet V1 at pedsnet.v1.models
• PEDSnet V2 at pedsnet.v2.models
• i2b2 V1.7 at i2b2.v1_7.models
• i2b2 PEDSnet V2 at i2b2.pedsnet.v2.models
• PCORnet V1 at pcornet.v1.models
• PCORnet V2 at pcornet.v2.models
• PCORnet V3 at pcornet.v3.models

Use of the included Dockerfile is highly recommended to avoid installing DBMS and graphing specific system requirements.

The following DBMS dialects are supported when generating DDL:

• PostgreSQL called as postgresql
• MySQL called as mysql
• MS SQL Server called as mssql
• Oracle called as oracle

Retrieve the image:

docker pull dbhi/data-models-sqlalchemy

Usage for DDL generation:

docker run --rm dbhi/data-models-sqlalchemy ddl -h

Generate OMOP V5 creation DDL for Oracle:

docker run --rm dbhi/data-models-sqlalchemy ddl omop v5 oracle

Generate OMOP V5 drop DDL for Oracle:

docker run --rm dbhi/data-models-sqlalchemy ddl -d omop v5 oracle

Generate OMOP V5 data deletion DML for Oracle:

docker run --rm dbhi/data-models-sqlalchemy ddl -x omop v5 oracle

Usage for ERD generation:

docker run --rm dbhi/data-models-sqlalchemy erd -h

Generate i2b2 PEDSnet V2 ERD (the image will land at ./erd/i2b2_pedsnet_v2_erd.png):

docker run --rm -v $(pwd)/erd:/erd dbhi/data-models-sqlalchemy erd i2b2_pedsnet v2 /erd/i2b2_pedsnet_v2_erd.png

The graphviz graphing package supports a number of other output formats, listed here (link pending), which are interpreted from the passed extension.

Install the system requirements (see Dockerfile for details):

• Python 2.7
• graphviz for ERD generation
• Oracle instantclient-basic and -sdk and libaio1 for Oracle DDL generation
• libpq-dev for PostgreSQL DDL generation
• unixodbc-dev for MS SQL Server DDL generation

Install the python requirements, hopefully within a virtualenv (see Dockerfile for details):

pip install cx-Oracle            # for Oracle DDL generation
pip install psycopg2             # for PostgreSQL DDL generation
pip install PyMySQL              # for MySQL DDL generation
pip install pyodbc               # for MS SQL Server DDL generation

Install the data-models-sqlalchemy python package:

pip install dmsa

Usage for DDL generation:

dmsa ddl -h

Generate OMOP V5 creation DDL for Oracle:

dmsa ddl omop v5 oracle

Generate OMOP V5 drop DDL for Oracle:

dmsa ddl -d omop v5 oracle

Generate OMOP V5 data deletion DML for Oracle:

dmsa ddl -x omop v5 oracle

Usage for ERD generation:

dmsa erd -h

Generate i2b2 PEDSnet V2 ERD (the image will land at ./erd/i2b2_pedsnet_v2_erd.png):

mkdir erd
dmsa erd i2b2_pedsnet v2 ./erd/i2b2_pedsnet_v2_erd.png

The web service uses a simple Flask debug server for now. It exposes the following endpoints:

• Creation DDL at /<model>/<version>/ddl/<dialect>/
• Creation DDL for only table, constraint, or index elements at /<model>/<version>/ddl/<dialect>/<elements>
• Drop DDL at /<model>/<version>/drop/<dialect>/
• Drop DDL for only table, constraint, or index elements at /<model>/<version>/drop/<dialect>/<elements>
• Data deletion DML at /<model>/<version>/delete/<dialect>/
• ERDs at /<model>/<version>/erd/

Usage:

docker run  dbhi/data-models-sqlalchemy start -h

Run:

docker run dbhi/data-models-sqlalchemy  # Uses Dockerfile defaults of 0.0.0.0:80

Install Flask:

pip install Flask

Usage:

dmsa start -h

Run:

dmsa start                              # Uses Flask defaults of 127.0.0.1:5000