npgmlwarehouse-python

ORM and access layer for the existing MySQL multi-lims warehouse

This package contains an ORM for an existing multi-lims warehouse database schema, which hosts information about runs, samples and studies. A Perl ORM for the same schema is defined in ml_warehouse. Migrations for that schema are also tracked in the Perl package.

The code in this package was tested for read and write operations. Most of the business logic for create and update operation for different database tables is implemented in the Perl package. We advise against performing write operations using this ORM.

Currently, record creation from this code is performed only on the iRODS location table seq_product_irods_locations. We allow for vendor-specific sqlalchemy code where trying to be vendor-agnostic would result in implementing a large volume of custom code and tests.

This ORM has been auto-generated with sqlacodegen 4.0.2 with post-generation manual modification to deal with awkward bits. Only a selection of practically useful tables is represented in the ORM.

sqlacodegen --generator declarative --tables 'aliquot,bmap_flowcell,...' mysql+pymysql://user:pass@host:port/dbname > src/npgmlwarehouse/db/schema.py

A list of tables can be generated as follows:

echo 'show tables' | mlwh-livero | grep -v Tables_in | grep -v gsu | grep -v tol_sample | grep -v lighthouse | grep -v cgap| grep -v iseq_external | grep -v iseq_heron | grep -v schema_migrations | grep -v ar_internal_metadata | xargs echo | perl -nle 's/\s/,/g;print $_'

Development

The project follows Google code and documentation style guide. Linting should be performed by ruff.

Unit tests are performed against an instance of MySQL server.

Installation and testing:

Either create a MySQL instance (via container mysql:8.0.35) or use a shared NPG MySQL testing instance. If using using a container, set up a test DB for the units tests to write to. Credentials should match those found in ./tests/config/testdb.json

set global sql_mode = 'STRICT_TRANS_TABLES';
CREATE DATABASE test;
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;

pip install .[test]
pytest