class ResultsTaxoncount(db.Model):
__tablename__ = 'results_taxoncount'
id = db.Column(BIGINT, primary_key=True)
sample = db.Column(BIGINT, db.ForeignKey('samples.id'), nullable=False)
ssr = db.Column(BIGINT, default=True, nullable=True)
taxon = db.Column(BIGINT, db.ForeignKey('taxa.id'), nullable=False)
count = db.Column(BIGINT, default=True, nullable=True)
avg = db.Column(BIGINT, default=True, nullable=True)
created = db.Column(TIMESTAMP, default=func.current_timestamp())
count_norm = db.Column(BIGINT, default=True, nullable=True)
disabled_sample = db.Column(BIGINT, default=True, nullable=True)
terminal_count = db.Column(BIGINT, nullable=False)
def __init__(self, sample, ssr, taxon, count, avg, count_norm,
disabled_sample, terminal_count):
self.sample = sample
self.ssr = ssr
self.taxon = taxon
self.count = count
self.avg = avg
self.count_norm = count_norm
self.disabled_sample = disabled_sample
self.terminal_count = terminal_count
def __repr__(self):
return '<id {}>'.format(self.id)
class KitTypesQuestions(db.Model):
__tablename__ = 'kit_types_questions'
id = db.Column(BIGINT, primary_key=True)
kit_type = db.Column(BIGINT, db.ForeignKey('kit_types.id'), nullable=False)
question_text = db.Column(TEXT, nullable=False)
question_type = db.Column(INTEGER, default=True, nullable=True)
question_order = db.Column(INTEGER, nullable=False)
page = db.Column(INTEGER, default=True, nullable=True)
show_number = db.Column(SMALLINT, default=1, nullable=False)
requiredFilterPermission = db.Column(INTEGER, default=15, nullable=False)
kit_types_questions_answer_options = db.relationship(
'KitTypesQuestionsAnswerOptions', backref='kit_types_questions')
def __init__(self, kit_type, question_text, question_type, question_order,
page, show_number, requiredFilterPermission):
self.kit_type = kit_type
self.question_text = question_text
self.question_type = question_type
self.question_order = question_order
self.page = page
self.show_number = show_number
self.requiredFilterPermission = requiredFilterPermission
def __repr__(self):
return '<id {}>'.format(self.id)
class Experiments(db.Model):
__tablename__ = 'experiments'
id = db.Column(BIGINT, primary_key = True)
name = db.Column(VARCHAR(64), default = True, nullable = False)
color = db.Column(VARCHAR(6), default = True, nullable = False)
description = db.Column(TEXT, default = True, nullable = False)
instructions = db.Column(TEXT)
spare_answer_id = db.Column(BIGINT, default = True, nullable = True)
tag = db.Column(VARCHAR(10), default = True, nullable = True)
deliver_data = db.Column(SMALLINT, default = 0, nullable = False)
samples = db.relationship('Samples', backref='experiments')
kit_types_questions = db.relationship('KitTypesQuestions', secondary=kit_types_questions_X_experiments,
backref=db.backref('experiments', lazy='dynamic'))
def __init__(self, name, color, description, instructions, spare_answer_id, tag, deliver_data):
self.name = name
self.color = color
self.description = description
self.instructions = instructions
self.spare_answer_id = spare_answer_id
self.tag = tag
self.deliver_data = deliver_data
def __repr__(self):
return '<id {}>'.format(self.id)
class KitTypes(db.Model):
__tablename__ = 'kit_types'
id = db.Column(BIGINT, primary_key=True)
name = db.Column(VARCHAR(128), nullable=False)
description = db.Column(TEXT, nullable=False)
welcome_screen = db.Column(TEXT, nullable=False)
finish_screen = db.Column(TEXT)
def __init__(self, name, description, welcome_screen, finish_screen):
self.name = name
self.description = description
self.welcome_screen = welcome_screen
self.finish_screen = finish_screen
def __repr__(self):
return '<id {}>'.format(self.id)
class TaxonInfo(db.Model):
__tablename__ = 'taxon_info'
id = db.Column(BIGINT, primary_key = True)
taxon = db.Column(BIGINT, db.ForeignKey('taxa.id'), nullable = False)
description_1 = db.Column(VARCHAR(256), default = True, nullable = True)
description_2 = db.Column(VARCHAR(4096), default = True, nullable = True)
description_3 = db.Column(TEXT)
def __init__(self, taxon, description_1, description_2, description_3):
self.taxon = taxon
self.description_1 = description_1
self.description_2 = description_2
self.description_3 = description_3
def __repr__(self):
return '<id {}>'.format(self.id)
class Taxa(db.Model):
__tablename__ = 'taxa'
id = db.Column(BIGINT, primary_key=True)
tax_rank = db.Column(VARCHAR(128), default=True, nullable=True)
tax_name = db.Column(VARCHAR(128), default=True, nullable=True)
tax_color = db.Column(VARCHAR(12), default=True, nullable=True)
parent = db.Column(BIGINT, default=True, nullable=True)
superkingdom = db.Column(BIGINT, default=True, nullable=True)
reads = db.Column(BIGINT, default=0, nullable=False)
results_taxoncount = db.relationship('ResultsTaxoncount', backref='taxa')
taxon_infos = db.relationship('TaxonInfo', uselist=False, backref='taxa')
def __init__(self, tax_rank, tax_name, tax_color, parent, superkingdom,
reads):
self.tax_rank = tax_rank
self.tax_name = tax_name
self.tax_color = tax_color
self.parent = parent
self.superkingdom = superkingdom
self.reads = reads
def __repr__(self):
return '<id {}>'.format(self.id)
class KitTypesQuestionsAnswerOptions(db.Model):
__tablename__ = 'kit_types_questions_answer_options'
id = db.Column(BIGINT, primary_key = True)
kit_type_question = db.Column(BIGINT, db.ForeignKey('kit_types_questions.id'), nullable = False)
answer_text = db.Column(VARCHAR(256), nullable = False)
answer_tooltip = db.Column(VARCHAR(256), nullable = False)
answer_order = db.Column(INTEGER, default = True, nullable = True)
answer_validation = db.Column(TEXT)
kit_types_questions_answers = db.relationship('KitTypesQuestionsAnswers', backref='kit_types_questions_answer_options')
def __init__(self, kit_type_question, answer_text, answer_tooltip, answer_order,
answer_validation):
self.kit_type_question = kit_type_question
self.answer_text = answer_text
self.answer_tooltip = answer_tooltip
self.answer_order = answer_order
self.answer_validation = answer_validation
def __repr__(self):
return '<id {}>'.format(self.id)
class KitTypesQuestionsAnswers(db.Model):
__tablename__ = 'kit_types_questions_answers'
id = db.Column(BIGINT, primary_key=True)
kit = db.Column(BIGINT, db.ForeignKey('kits.id'), nullable=False)
kit_types_questions_answer_option = db.Column(
BIGINT,
db.ForeignKey('kit_types_questions_answer_options.id'),
nullable=False)
answer_int = db.Column(BIGINT, default=True, nullable=True)
answer_decimal = db.Column(BIGINT, default=True, nullable=True)
answer_text = db.Column(TEXT)
def __init__(self, kit_types_questions_answer_option, kit, answer_int,
answer_decimal, answer_text):
self.kit_types_questions_answer_option = kit_types_questions_answer_option
self.kit = kit
self.answer_int = answer_int
self.answer_decimal = answer_decimal
self.answer_text = answer_text
def __repr__(self):
return '<id {}>'.format(self.id)
class Role(db.Model, RoleMixin):
__tablename__ = 'role'
id = db.Column(BIGINT, primary_key=True)
name = db.Column(VARCHAR(128), unique=True)
description = db.Column(TEXT)
self.name_last = name_last
self.induction_completed = induction_completed
self.adminlevel = adminlevel
self.outbound_last_identify = outbound_last_identify
self.serviceConsent = serviceConsent
self.sitever = sitever
self.firstrunwarning = firstrunwarning
self.displayname = displayname
self.consentonfile = consentonfile
self.mailinglist_invite = mailinglist_invite
def __repr__(self):
return '<id {}>'.format(self.id)
roles_users = db.Table('roles_users',
db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
db.Column('role_id', db.Integer(), db.ForeignKey('role.id')))
class Role(db.Model, RoleMixin):
__tablename__ = 'role'
id = db.Column(BIGINT, primary_key=True)
name = db.Column(VARCHAR(128), unique=True)
description = db.Column(TEXT)
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(BIGINT, primary_key=True)
email = db.Column(VARCHAR(255), unique=True)
password = db.Column(VARCHAR(255))
active = db.Column(BOOLEAN)
confirmed_at = db.Column(TIMESTAMP, default = func.current_timestamp())
import datetime
from web_api import app, db
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.security import Security, SQLAlchemyUserDatastore
from flask.ext.security import UserMixin, RoleMixin
from sqlalchemy.sql import func
from sqlalchemy.dialects.postgresql import \
ARRAY, BIGINT, BIT, BOOLEAN, BYTEA, CHAR, CIDR, DATE, \
DOUBLE_PRECISION, ENUM, FLOAT, HSTORE, INET, INTEGER, \
INTERVAL, JSON, JSONB, MACADDR, NUMERIC, OID, REAL, SMALLINT, TEXT, \
TIME, TIMESTAMP, UUID, VARCHAR, INT4RANGE, INT8RANGE, NUMRANGE, \
DATERANGE, TSRANGE, TSTZRANGE, TSVECTOR
kit_types_questions_X_experiments = db.Table(
'kit_types_questions_X_experiments',
db.Column('kit_types_question', BIGINT,
db.ForeignKey('kit_types_questions.id')),
db.Column('experiment', BIGINT, db.ForeignKey('experiments.id')))
class KitTypesQuestions(db.Model):
__tablename__ = 'kit_types_questions'
id = db.Column(BIGINT, primary_key=True)
kit_type = db.Column(BIGINT, db.ForeignKey('kit_types.id'), nullable=False)
question_text = db.Column(TEXT, nullable=False)
question_type = db.Column(INTEGER, default=True, nullable=True)
question_order = db.Column(INTEGER, nullable=False)
page = db.Column(INTEGER, default=True, nullable=True)
show_number = db.Column(SMALLINT, default=1, nullable=False)
requiredFilterPermission = db.Column(INTEGER, default=15, nullable=False)
kit_types_questions_answer_options = db.relationship(
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(BIGINT, primary_key=True)
email = db.Column(VARCHAR(255), unique=True)
password = db.Column(VARCHAR(255))
active = db.Column(BOOLEAN)
confirmed_at = db.Column(TIMESTAMP, default = func.current_timestamp())
#Optional/Additional fields for Registration
first_name = db.Column(VARCHAR(255))
last_name = db.Column(VARCHAR(255))
admin = db.Column(BOOLEAN, default = False)
last_login_at = db.Column(TIMESTAMP)
current_login_at = db.Column(TIMESTAMP)
last_login_ip = db.Column(TEXT)
current_login_ip = db.Column(TEXT)
login_count = db.Column(BIGINT)
user_survey = db.relationship('UserSurvey', backref='users')
roles = db.relationship('Role', secondary=roles_users,
backref=db.backref('users', lazy='dynamic'))
class Kits(db.Model):
__tablename__ = 'kits'
id = db.Column(BIGINT, primary_key=True)
user = db.Column(BIGINT, db.ForeignKey('users.id'), nullable=False)
mailinglabel = db.Column(BIGINT, default=0, nullable=True)
record_created = db.Column(TIMESTAMP,
default=func.current_timestamp(),
nullable=False)
assembled = db.Column(TIMESTAMP)
registered = db.Column(TIMESTAMP)
barcode = db.Column(VARCHAR(9), default=True, nullable=True)
order_id = db.Column(BIGINT, default=0, nullable=True)
kit_type = db.Column(BIGINT, db.ForeignKey('kit_types.id'), nullable=False)
survey_page = db.Column(INTEGER, default=0, nullable=False)
survey_completed = db.Column(TIMESTAMP)
registration_override = db.Column(INTEGER, default=0, nullable=False)
tubesReceived = db.Column(TIMESTAMP)
processed = db.Column(TIMESTAMP)
analyzed = db.Column(TIMESTAMP)
last_reminder = db.Column(TIMESTAMP)
samples = db.relationship('Samples', backref='kits')
kit_types_questions_answers = db.relationship('KitTypesQuestionsAnswers',
backref='kits')
def __init__(self, user, mailinglabel, assembled, registered, barcode,
order_id, kit_type, survey_page, survey_completed,
registration_override, tubesReceived, processed, analyzed,
last_reminder):
self.user = user
self.mailinglabel = mailinglabel
self.assembled = assembled
self.registered = registered
self.barcode = barcode
self.order_id = order_id
self.kit_type = kit_type
self.survey_page = survey_page
self.survey_completed = survey_completed
self.registration_override = registration_override
self.tubesReceived = tubesReceived
self.processed = processed
self.analyzed = analyzed
self.last_reminder = last_reminder
def __repr__(self):
return '<id {}>'.format(self.id)
class UserSurvey(db.Model):
__tablename__ = 'user_survey'
id = db.Column(BIGINT, primary_key=True)
user = db.Column(BIGINT, db.ForeignKey('user.id'), nullable=False)
dob = db.Column(VARCHAR(128), nullable=False)
city_and_country = db.Column(VARCHAR(128), nullable=False)
gender = db.Column(VARCHAR(128), nullable=False)
race = db.Column(VARCHAR(128), nullable=False)
allow_data_comparison = db.Column(BOOLEAN, default=False)
term_of_service = db.Column(BOOLEAN, default=False)
privacy_policy = db.Column(BOOLEAN, default=False)
induction_completed = db.Column(BOOLEAN, default=False)
service_consent = db.Column(BOOLEAN, default=False)
consent_on_file = db.Column(BOOLEAN, default=False)
record_created = db.Column(TIMESTAMP,
default=func.current_timestamp(),
nullable=False)
def __init__(self, user, dob, city_and_country, gender, race,
allow_data_comparison, term_of_service, privacy_policy,
induction_completed, service_consent):
self.user = user
self.dob = dob
self.city_and_country = city_and_country
self.gender = gender
self.race = race
self.allow_data_comparison = allow_data_comparison
self.privacy_policy = privacy_policy
self.induction_completed = induction_completed
self.service_consent = service_consent
def __repr__(self):
return '<id {}>'.format(self.id)
class Samples(db.Model):
__tablename__ = 'samples'
id = db.Column(BIGINT, primary_key = True)
vial_barcode = db.Column(VARCHAR(32), default = True, nullable = True)
experiment = db.Column(BIGINT, db.ForeignKey('experiments.id'), nullable = False)
assigned_kit = db.Column(TIMESTAMP)
kit = db.Column(BIGINT, db.ForeignKey('kits.id'), nullable = False)
assigned_vial = db.Column(TIMESTAMP)
created = db.Column(TIMESTAMP, default = func.current_timestamp())
processed = db.Column(TIMESTAMP)
received = db.Column(TIMESTAMP)
analyzed = db.Column(TIMESTAMP)
query_exclude = db.Column(INTEGER, default = 0, nullable = False)
collected = db.Column(TIMESTAMP)
sequencing_revision = db.Column(BIGINT, nullable = False)
lab_sample_loading = db.relationship('LabSampleLoading', backref='samples')
results_taxoncount = db.relationship('ResultsTaxoncount', uselist=False, backref='samples')
def __init__(self, vial_barcode, experiment, assigned_kit, kit, assigned_vial,
processed, received, analyzed, query_exclude, collected,
sequencing_revision):
self.vial_barcode = vial_barcode
self.experiment = experiment
self.assigned_kit = assigned_kit
self.kit = kit
self.assigned_vial = assigned_vial
self.processed = processed
self.received = received
self.analyzed = analyzed
self.query_exclude = query_exclude
self.collected = collected
self.sequencing_revision = sequencing_revision
def __repr__(self):
return '<id {}>'.format(self.id)
class LabSampleLoading(db.Model):
__tablename__ = 'lab_sample_loading'
id = db.Column(BIGINT, primary_key=True)
tubeId = db.Column(VARCHAR(255), nullable=False)
PRID = db.Column(VARCHAR(255), nullable=False)
Extraction_Rack_Number = db.Column(VARCHAR(255), nullable=False)
Extraction_Rack_Loaction = db.Column(VARCHAR(255), nullable=False)
Extraction_Rack_Scan_Time = db.Column(VARCHAR(255), nullable=False)
pipeline_tracking_record = db.Column(
BIGINT, db.ForeignKey('lab_pipeline_tracking.id'), nullable=False)
originFileName = db.Column(VARCHAR(128), nullable=False)
sample = db.Column(BIGINT, db.ForeignKey('samples.id'), nullable=False)
taxondata_loaded = db.Column(TIMESTAMP)
created = db.Column(TIMESTAMP, default=func.current_timestamp())
pipeline_rev = db.Column(INTEGER, default=1, nullable=False)
latestRev2 = db.Column(INTEGER, default=0, nullable=False)
def __init__(self, tubeId, PRID, Extraction_Rack_Number,
Extraction_Rack_Loaction, Extraction_Rack_Scan_Time,
pipeline_tracking_record, originFileName, sample,
taxondata_loaded, pipeline_rev, latestRev2):
self.tubeId = tubeId
self.PRID = PRID
self.Extraction_Rack_Number = Extraction_Rack_Number
self.Extraction_Rack_Loaction = Extraction_Rack_Loaction
self.Extraction_Rack_Scan_Time = Extraction_Rack_Scan_Time
self.pipeline_tracking_record = pipeline_tracking_record
self.originFileName = originFileName
self.sample = sample
self.taxondata_loaded = taxondata_loaded
self.pipeline_rev = pipeline_rev
self.latestRev2 = latestRev2
def __repr__(self):
return '<id {}>'.format(self.id)
class Users(db.Model):
__tablename__ = 'users'
id = db.Column(BIGINT, primary_key=True)
facebook_id = db.Column(BIGINT, default = True, nullable = True)
username = db.Column(VARCHAR(128), default = True, nullable = True)
email = db.Column(VARCHAR(196), default = True, nullable = True)
password = db.Column(VARCHAR(256), default = True, nullable = True)
confirmed_at = db.Column(TIMESTAMP, default = func.current_timestamp())
name_first = db.Column(VARCHAR(128), default = True, nullable = False)
name_last = db.Column(VARCHAR(128), default = True, nullable = False)
induction_completed = db.Column(INTEGER, default = 0)
adminlevel = db.Column(INTEGER, default = 0)
outbound_last_identify = db.Column(TIMESTAMP)
serviceConsent = db.Column(INTEGER, default = True, nullable = True)
sitever = db.Column(BIGINT, default = 0)
firstrunwarning = db.Column(INTEGER, default = 0)
displayname = db.Column(VARCHAR(64), default = True, nullable = True)
consentonfile = db.Column(BOOLEAN, default = 0)
mailinglist_invite = db.Column(TIMESTAMP)
active = db.Column(BOOLEAN)
kits = db.relationship('Kits', backref='users')
def __init__(self, facebook_id, username, email, password, name_first, name_last, induction_completed,
adminlevel, outbound_last_identify, serviceConsent, sitever, firstrunwarning, displayname,
consentonfile, mailinglist_invite):
self.facebook_id = facebook_id
self.username = username
self.email = email
self.password = password
self.name_first = name_first
self.name_last = name_last
self.induction_completed = induction_completed
self.adminlevel = adminlevel
self.outbound_last_identify = outbound_last_identify
self.serviceConsent = serviceConsent
self.sitever = sitever
self.firstrunwarning = firstrunwarning
self.displayname = displayname
self.consentonfile = consentonfile
self.mailinglist_invite = mailinglist_invite
def __repr__(self):
return '<id {}>'.format(self.id)