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