genologics-sql’s documentation

Contents:

Tables

This is the main module. It maps the different tables in the Genologics Postgres Database into SQLAlchemy’s objects, allowing them to be queried in python fashion.

class genologics_sql.tables.Analyte(**kwargs)[source]

Table mapping Analytes

Parameters:
  • artifactid (INTEGER) – artifact id of the analyte. Primary key
  • analyteid (INTEGER) – internal analyte id
  • iscalibrant (BOOLEAN) – unknown
  • sequencenumber (INTEGER) – unknown
  • isvisible (BOOLEAN) – unknown

The following attributes are not found in the table, but are available through mapping

Parameters:artifact (Artifact) – artifact row corresponding to the analyte row.
class genologics_sql.tables.Artifact(**kwargs)[source]

Table mapping process objects

Parameters:
  • artifactid (INTEGER) – the (short) artifact ID. Primary key.
  • name (STRING) – the artifact given name
  • luid (STRING) – the (long) process id
  • concentration (FLOAT) – unknown
  • origvolume (FLOAT) – unknown
  • origconcentration (FLOAT) – unknown
  • datastoreid (INTEGER) – id of the associated datastore
  • isworking (BOOLEAN) – API working flag
  • isoriginal (BOOLEAN) – unknown
  • isglobal (BOOLEAN) – unknown
  • isgenealogyartifact (BOOLEAN) – unknown
  • ownerid (INTEGER) – researcher id of the artifact creator
  • createddate (TIMESTAMP) – date of creation of the artifact
  • lastmodifieddate (TIMESTAMP) – date of last modification
  • lastmodifiedby (INTEGER) – researcher id of the last modifier
  • artifacttypeid (INTEGER) – unknown
  • processoutputid (INTEGER) – unknown
  • currentstateid (INTEGER) – unknown
  • originalstateid (INTEGER) – unknown
  • compoundartifactid (INTEGER) – unknown
  • outputindex (INTEGER) – unknown

The following attributes are not found in the table, but are available through mapping

Parameters:
  • samples (Sample) – Sample rows associated with the Artifact row.
  • ancestors (Artifact) – Artifact rows associated with this row through artifact_ancestor_map.
  • udfs (ArtifactUdfView) – ArtifactUdfView row associated the Artifact row.
  • containerplacement (ContainerPlacement) – ContainerPlacement row associated the Artifact row.
  • reagentlabels (ReagentLabel) – reagentlabel rows associated with the Artifact row.
class genologics_sql.tables.ArtifactUdfView(**kwargs)[source]

View mapping udfs with artifacts through the datastores.

Parameters:
  • artifactid (INTEGER) – the (short) artifact id
  • udtname (STRING) – name of the user defined type
  • udfname (STRING) – name of the user defined field
  • udftype (STRING) – type of the user defined field
  • udfvalue (STRING) – value of the user defined field
  • udfunitlabel (STRING) – unit of the user defined field
class genologics_sql.tables.Container(**kwargs)[source]

Table mapping containers

Parameters:
  • containerid (INTEGER) – The (short) container id. Primary Key.
  • subtype (STRING) – The container type
  • luid (STRING) – The (long) container id
  • isvisible (BOOLEAN) – unkown
  • name (STRING) – The container name
  • ownerid (INTEGER) – Researcher ID of the container creator
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • createddate (TIMESTAMP) – The date of creation
  • lastmodifieddate (TIMESTAMP) – The date of last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier
  • stateid (INTEGER) – placeholders for empty, populated, depleted, discarded
  • typeid (INTEGER) – container type id from containertype (not mapped)
  • lotnumber (STRING) – unknown
  • expirydate (TIMESTAMP) – unknown

The following attributes are not found in the table, but are available through mapping

Parameters:udfs (EntityUdfView) – EntityUdfView row associated with the Container row.
class genologics_sql.tables.ContainerPlacement(**kwargs)[source]

Table mapping sample placement in the containers

Parameters:
  • placementid (INTEGER) – internal placement ID. Primary key.
  • containerid (INTEGER) – the associated container id
  • wellxposition (INTEGER) – the horizontal position in the container of the sample
  • wellyposition (INTEGER) – the vertical position in the container of the sample
  • dateplaced (TIMESTAMP) – timestamp of the placement creation
  • ownerid (INTEGER) – researcherid of the user who made the placement
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unkown
  • createddate (TIMESTAMP) – timestamp of the placement creation
  • lastmodifieddate (TIMESTAMP) – timestamp of the last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier
  • reagentid (INTEGER) – Reagent ID used in that placement
  • processartifactid (INTEGER) – artifact id of the artifact involved in that placement

The following attributes are not found in the table, but are available through mapping

Parameters:container (Container) – Container row associated with the ContainerPlacement row.
class genologics_sql.tables.EntityUdfView(**kwargs)[source]

Table used to access project and container udfs

Parameters:
  • attachtoid (INTEGER) – the ID of the entity to attach the row to.
  • attachtoclassid (INTEGER) – the ID of the class of the entity to attach the row to.
  • udtname (STRING) – the name of the User Defined Type.
  • udfname (STRING) – the name of the User Defined Field.
  • udttype (STRING) – the type of the User Defined Type.
  • udfvalue (STRING) – the value of the User Defined Field.
  • udfunitlabel (STRING) – the type of the User Defined Field if preset.

All of these are mapped as primary keys.

class genologics_sql.tables.EscalatedSample(**kwargs)[source]

Table mapping the escalated samples

Parameters:
  • escalatedsampleid (INTEGER) – the escalated sample internal id. Primary key.
  • escalationeventid (INTEGER) – the associated escalation event id
  • artifactid (INTEGER) – the associated artifact id.
  • ownerid (INTEGER) – Researcher ID of the container creator
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • createddate (TIMESTAMP) – The date of creation
  • lastmodifieddate (TIMESTAMP) – The date of last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier
class genologics_sql.tables.EscalationEvent(**kwargs)[source]

Table mapping Escalation events

Parameters:
  • eventid (INTEGER) – escalation event internal id. Primary Key.
  • processid (INTEGER) – process ID where the escalation took place
  • originarorid (INTEGER) – researcher id of the user requesting a review
  • reviewerid (INTEGER) – researcher id of the user having to perform the review
  • escalationdate (TIMESTAMP) – timestamp of the review request
  • reviewdate (TIMESTAMP) – timestamp of the review completion
  • escalationcomment (STRING) – comment of the review request
  • reviewcomment (STRING) – comment of the review completion
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • ownerid (INTEGER) – Researcher ID of the container creator
  • createddate (TIMESTAMP) – The date of creation
  • lastmodifieddate (TIMESTAMP) – The date of last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier
class genologics_sql.tables.GlsFile(**kwargs)[source]

Table mapping Glsfiles

Parameters:
  • fileid (INTEGER) – internal file id of corresponding ResultFile. Primary key.
  • server (STRING) – ftp location
  • contenturi (STRING) – URI to the file
  • luid (STRING) – long file id
  • originallocation (STRING) – original path of the file on the uploader’s computer.
  • ispublished (BOOLEAN) – unknown
  • ownerid (INTEGER) – Researcher ID of the file creator
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • createddate (TIMESTAMP) – The date of creation
  • lastmodifieddate (TIMESTAMP) – The date of last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier
  • attachtoid (INTEGER) – unknown
  • attachtoclassid (INTEGER) – unknown

The following attributes are not found in the table, but are available through mapping

Parameters:
  • artifact (Artifact) – artifact row corresponding to the ResultFile row.
  • glsfile (GlsFile) – glsfile row corresponding to the ResultFile row.
class genologics_sql.tables.Process(**kwargs)[source]

Table mapping process objects

Parameters:
  • processid (INTEGER) – the (short) process ID. Primary key.
  • daterun (TIMESTAMP) – date where the process was closed
  • luid (STRING) – the (long) process id
  • isprotocol (BOOLEAN) – unknown
  • protocolnameused (STRING) – unknown
  • programstarted (BOOLEAN) – probably stores EPP status
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • ownerid (INTEGER) – researcher id of the process creator
  • createddate (TIMESTAMP) – date of creation of the process
  • lastmodifieddate (TIMESTAMP) – date of last modification
  • lastmodifiedby (INTEGER) – researcher id of the last modifier
  • installationid (INTEGER) – unknown
  • techid (INTEGER) – unknown
  • typeid (INTEGER) – id of the process type associated
  • stringparameterid (INTEGER) – parameterid from processparameter. Contains information about EPPs.
  • fileparameterid (INTEGER) – unknown often empty
  • protocolstepid (INTEGER) – id of the associated protocol step
  • workstatus (STRING) – status of the process. values : COMPLETE, RECORD_DETAILS, STARTED, UNDER_REVIEW, MOVE_SAMPLES_ON
  • reagentcategoryid (INTEGER) – id of the assocated reagent category
  • signedbyid (INTEGER) – unknown
  • signeddate (TIMESTAMP) – unknown
  • nextstepslocked (BOOLEAN) – unknown

The following attributes are not found in the table, but are available through mapping

Parameters:
  • type (ProcessType) – ProcessType row associated with the Process row.
  • udfs (ProcessUdfView) – ProcessUdfView row associated with the Process row.
class genologics_sql.tables.ProcessType(**kwargs)[source]

Table mapping the Process Types

Parameters:
  • typeid (INTEGER) – The Process Type ID. Primary Key
  • displayname (STRING) – The name of the process type as shown everywhere.
  • typename (STRING) – The name of the _category_ of the process type.
  • isenabled (BOOLEAN) – Probably related to the tickbox in the Operations interface
  • contextcode (STRING) – The short code (usually 3 letters) that represents the type
  • isvisible (BOOLEAN) – unknown
  • style (INTEGER) – unknown
  • BOOLEANshowinexplorer – unknown
  • showinbuttonbar (BOOLEAN) – unknown
  • openpostprocess (BOOLEAN) – unknown
  • iconconstant (STRING) – unknown
  • outputcontextcode (STRING) – unknown. Apparently, a two-letter code.
  • useprotocol (BOOLEAN) – unknown
  • ownerid (INTEGER) – Researcher ID of the owner of the type. Should correlate to the Researcher table.
  • datastoreid (INTEGER) – likely related to the udf storage
  • isglobal (BOOLEAN) – unknown
  • createddate (TIMESTAMP) – creation date
  • lastmodifieddate (TIMESTAMP) – timestamp of the last modification
  • lastmodifiedby (INTEGER) – ID of the last modifier
  • behaviourname (STRING) – unknown
  • pmetadata (STRING) – html string likely containing display data. The actual column name is metadata, but that causes namespace conflicts.
  • canedit (BOOLEAN) – is that type editable
  • modulename (STRING) – Java module tied to this type
  • expertname (STRING) – Java class tied to this type
class genologics_sql.tables.ProcessUdfView(**kwargs)[source]

View mapping udfs with processes through the datastores.

Parameters:
  • processid (INTEGER) – the (short) process id
  • typeid (INTEGER) – the process type id
  • udtname (STRING) – name of the user defined type
  • udfname (STRING) – name of the user defined field
  • udftype (STRING) – type of the user defined field
  • udfvalue (STRING) – value of the user defined field
  • udfunitlabel (STRING) – unit of the user defined field
class genologics_sql.tables.Project(**kwargs)[source]

Table storing project objects

Parameters:
  • projectid (INTEGER) – the _internal_ project ID. Primary key.
  • name (STRING) – the project name.
  • opendate (TIMESTAMP) – the opening date of the project as a timestamp.
  • closedate (TIMESTAMP) – the closing date of the project as a timestamp.
  • invoicedate (TIMESTAMP) – the invoicing date of the project as a timestamp.
  • luid (STRING) – the external project id.
  • maximumsampleid (STRING) – the id of the last sample. usually, nb of samples-1, as it’s 0 indexed.
  • ownerid (INTEGER) – researcherID of the project owner.
  • datastoreid (INTEGER) – probably used to map the udfs
  • isglobal (INTEGER) – unkown
  • createddate (TIMESTAMP) – the creation date of the project as a timestamp.
  • lastmodifieddate (TIMESTAMP) – the last modification date of the project as a timestamp.
  • lastmodifiedby (INTEGER) – the id of the last modifier of the project.
  • researcherid (INTEGER) – the id of the researcher associated to the project.
  • priority (INTEGER) – unknown

The following attributes are not found in the table, but are available through mapping

Parameters:
  • udfs (UDFS) – list of project udf rows for the given projectid
  • researcher (RESEARCHER) – direct researcher mapping
class genologics_sql.tables.ReagentLabel(**kwargs)[source]

Table mapping reagent labels

Parameters:
  • labelid (INTEGER) – The reagent label id. Primary Key.
  • name (STRING) – The reagent label name
  • ownerid (INTEGER) – Researcher ID of the reagent label creator
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • createddate (TIMESTAMP) – The date of creation
  • lastmodifieddate (TIMESTAMP) – The date of last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier

The following attributes are not found in the table, but are available through mapping

Parameters:artifacts (Artifact) – list of artifacts linked through the artifact_label junction table.
class genologics_sql.tables.Researcher(**kwargs)[source]

Table mapping Researchers

Parameters:
  • researcherid (INTEGER) – internal researcher id. Primary key.
  • roleid (INTEGER) – internal role id
  • firstname (STRING) – First name of the researcher
  • lastname (STRING) – Last name of the researcher
  • title (STRING) – researcher’s title, if any
  • initials (STRING) – researcher’s initials
  • ownerid (INTEGER) – id of the row creator
  • datastoreid (INTEGER) – id of the associated datastore
  • isglobal (BOOLEAN) – unknown
  • createddate (TIMESTAMP) – The date of creation
  • lastmodifieddate (TIMESTAMP) – The date of last modification
  • lastmodifiedby (INTEGER) – researcherid of the last modifier
  • phone (STRING) – researcher’s phone number
  • email (STRING) – researcher’s email address
  • fax (STRING) – researcher’s fax number
  • addressid (INTEGER) – id of the associated Address row. (Not mapped)
  • labid (INTEGER) – id of the associated Lab row. (Not mapped)
  • supervisorid (INTEGER) – researcher id of the researcher’s supervisor
  • isapproved (BOOLEAN) – has been validated as a user
  • requestedsupervisorfirstname (STRING) – unknown
  • requestedsupervsodlastname (STRING) – unknown
  • requestedusername (STRING) – unknown
  • requestedpassword (STRING) – unknown
  • requestedlabname (STRING) – unknown
  • avatar (LARGEBINARY) – base64 encoding of the avatar image
  • avatarcontenttype (STRING) – mime type of the avatar image
class genologics_sql.tables.ResultFile(**kwargs)[source]

Table mapping ResultFiles

Parameters:
  • artifactid (INTEGER) – artifact id of the ResultFile. Primary key
  • fileid (INTEGER) – internal file id
  • typeid (STRING) – unknown
  • parsestatus (INTEGER) – unknown
  • status (INTEGER) – unknown
  • commandid (INTEGER) – unknown
  • glsfileid (BOOLEAN) – id of the corresponding row in glsfile

The following attributes are not found in the table, but are available through mapping

Parameters:
  • artifact (Artifact) – artifact row corresponding to the ResultFile row.
  • glsfile (GlsFile) – glsfile row corresponding to the ResultFile row.
class genologics_sql.tables.Sample(**kwargs)[source]

Table mapping the samples

Parameters:
  • processid (INTEGER) – The ID of the process that spawned the sample. Primary key.
  • sampleid (INTEGER) – Internal sample ID.
  • name (STRING) – the sample name.
  • datereceived (TIMESTAMP) – timestamp of the sample import.
  • datecompleted (TIMESTAMP) – timestamp of the project closure / sample completion.
  • maximumanalyteid (INTEGER) – unknown
  • uniqueid (INTEGER) – unknown. Not unique.
  • bisourceid (INTEGER) – unknown.
  • projectid (INTEGER) – projet ID associated to the sample.
  • controltypeid (INTEGER) – unknown.

The following attributes are not found in the table, but are available through mapping

Parameters:project (Project) – project object associated to the sample through the projectid foreign key.
genologics_sql.tables.artifact_ancestor_map = Table('artifact_ancestor_map', MetaData(bind=None), Column('artifactid', Integer(), ForeignKey('artifact.artifactid'), table=<artifact_ancestor_map>), Column('ancestorartifactid', Integer(), ForeignKey('artifact.artifactid'), table=<artifact_ancestor_map>), schema=None)

Junction table between artifact and artifact (as an ancestor)

genologics_sql.tables.artifact_label_map = Table('artifact_label_map', MetaData(bind=None), Column('artifactid', Integer(), ForeignKey('artifact.artifactid'), table=<artifact_label_map>), Column('labelid', Integer(), ForeignKey('reagentlabel.labelid'), table=<artifact_label_map>), schema=None)

Junction table between artifact and reagentlabel)

genologics_sql.tables.artifact_sample_map = Table('artifact_sample_map', MetaData(bind=None), Column('artifactid', Integer(), ForeignKey('artifact.artifactid'), table=<artifact_sample_map>), Column('processid', Integer(), ForeignKey('sample.processid'), table=<artifact_sample_map>), schema=None)

Junction table between artifact and sample

Queries

This is a collection of queries that were found useful. Most of them aim to get entities based on their latest modification date, especially UDF modification date, as the API does not provide this.

genologics_sql.queries.get_last_modified_project_artifact_udfs(session, interval='2 hours')[source]

gets the project objects that have artifact udfs last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_project_artifacts(session, interval='2 hours')[source]

gets the project objects that have artifacts last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_project_containers(session, interval='2 hours')[source]

gets the project objects that have containers last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_project_process_udfs(session, interval='2 hours')[source]

gets the project objects that have containers last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_project_processes(session, interval='2 hours')[source]

gets the project objects that have containers last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_project_sample_udfs(session, interval='2 hours')[source]

gets the project objects that have sample udfs last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_project_udfs(session, interval='2 hours')[source]

gets the project objects that have a udf last modified in the last <interval>

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_projectids(session, interval='2 hours')[source]

gets all the projectids for which any part has been modified in the last interval

Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records
genologics_sql.queries.get_last_modified_projects(session, interval='2 hours')[source]

gets the project objects last modified in the last <interval>

Query:

select * from project where age(lastmodifieddate)< ‘1 hour’::interval;
Parameters:
  • session – the current SQLAlchemy session to the database
  • interval – str Postgres-compliant time string
Returns:

List of Project records

Indices and tables