class Experiment(Base):
"""ORM for 'Simulation' model in the database."""
__tablename__ = 'experiment'
# PK (with autoincrementing value)
id = Column(Integer,
Sequence('experiment_id_seq', start=1, increment=1),
primary_key=True)
# Linking with FK: experiment_id
simulation_rel = relationship('Simulation')
# Experiment name
name = Column(String(32))
# Experiment date
date = Column(DateTime, default=func.now())
def __init__(self, name):
self.name = name
def make_bigint_autoincrement_column(column_name: str,
dialect: Dialect) -> Column:
"""
Returns an instance of :class:`Column` representing a :class:`BigInteger`
``AUTOINCREMENT`` column in the specified :class:`Dialect`.
"""
# noinspection PyUnresolvedReferences
if dialect.name == SqlaDialectName.MSSQL:
return Column(column_name, BigInteger,
Sequence('dummy_name', start=1, increment=1))
else:
# return Column(column_name, BigInteger, autoincrement=True)
# noinspection PyUnresolvedReferences
raise AssertionError(
f"SQLAlchemy doesn't support non-PK autoincrement fields yet for "
f"dialect {dialect.name!r}")
class UacQidLink(Base):
__tablename__ = 'uacqidlink'
uacqidlinkseq = Sequence('uacqidlinkseq', metadata=Base.metadata)
id = Column(UUID, primary_key=True)
unique_number = Column('uacqidlinkseq',
Integer,
server_default=uacqidlinkseq.next_value())
qid = Column('qid', BigInteger)
uac = Column('uac', String)
caseref = Column(String(16), ForeignKey('case.caseref'))
case = relationship("Case", back_populates="uac_qid_links")
events = relationship("CaseEvent")
class AuthManager(Base):
__tablename__ = 'auth_manager'
id_seq = Sequence('auth_manager_id_seq', metadata=Base.metadata)
id = Column(Integer, id_seq, primary_key=True)
route_name = Column(String(80), unique=True, nullable=True)
permission = Column(String(80), unique=False, nullable=True)
page_url = Column(String(80), unique=True, nullable=True)
num = Column(Integer, unique=True, nullable=True)
func_name = Column(String(80), unique=True, nullable=True)
def to_dict(self):
"""將數據轉為字典"""
dictionary = self.__dict__
if "_sa_instance_state" in dictionary:
del dictionary["_sa_instance_state"]
return dictionary
class Contents(Base):
__tablename__ = 'contents'
id = Column(Integer, Sequence('contents_id_seq'), primary_key=True)
title = Column(VARCHAR(128))
tags = Column(ARRAY(VARCHAR(32)))
content = Column(Text)
users_id = Column(VARCHAR(32))
upload_at = Column(TIMESTAMP)
update_at = Column(TIMESTAMP)
version = Column(Integer())
def __repr__(self):
return "<CONTENTS(id='%r', title='%r', users_id='%r', version='%r')>" % (
self.id, self.title, self.users_id, self.version)
def add_archived_url_table(self):
self.op.create_table(
'archived_urls',
Column('id',
Integer,
Sequence('archived_url_id_seq'),
primary_key=True),
Column('contact_id',
Integer,
ForeignKey('contacts.id'),
nullable=False),
Column('actor_id', String(USER_ID_LENGTH), nullable=False),
Column('created', DateTime(timezone=True)),
Column('label', String(CONTENT_TITLE_LENGTH)),
Column('url', String(CONTENT_TITLE_LENGTH)),
)
class ParkingLocation(Base):
__tablename__ = f"importer_parkinglocation"
id = Column(Integer, Sequence("grl_seq"), primary_key=True, index=True)
api_id = Column(String, index=True)
name = Column(String, index=True)
type = Column(String, index=True)
geometrie = Column(Geometry('POINT', srid=4326), index=True)
state = Column(String, index=True)
free_space_short = Column(Integer, index=True)
free_space_long = Column(Integer, index=True)
short_capacity = Column(Integer, index=True)
long_capacity = Column(Integer, index=True)
pub_date = Column(TIMESTAMP, index=True)
stadsdeel = Column(String, index=True)
buurt_code = Column(String, index=True)
scraped_at = Column(TIMESTAMP, index=True)
class Model(Base):
__tablename__ = 'model'
id = Column(Integer, Sequence('wids'), primary_key=True)
bigg_id = Column(String)
first_created = Column(DateTime)
genome_id = Column(Integer, ForeignKey('genome.id'))
genome = relationship('Genome', backref='model')
UniqueConstraint('name', 'firstcreated')
notes = Column(String)
__table_args__ = (UniqueConstraint('bigg_id'), {})
def __repr__(self):
return "Model (#%d) %s %s" % (self.id, self.bigg_id,
self.first_created)
def downgrade():
op.drop_constraint('vote_pkey', 'vote', type_='primary')
op.execute(CreateSequence(Sequence('vote_id_seq')))
op.add_column(
'vote',
sa.Column(
'id',
sa.INTEGER(),
nullable=False,
server_default=sa.text("nextval('vote_id_seq'::regclass)"),
),
)
op.create_primary_key('vote_pkey', 'vote', ['id'])
op.create_unique_constraint('vote_user_id_voteset_id_key', 'vote',
['user_id', 'voteset_id'])
op.drop_index(op.f('ix_vote_user_id'), table_name='vote')
def migrate_temporal(table): # pragma: no cover
# imports here so we don't have a dependency on alembic
from alembic import op
import sqlalchemy as sa
from sqlalchemy.schema import Sequence, CreateSequence
op.execute(CreateSequence(Sequence(table + "_id_seq")))
op.add_column(
table,
sa.Column('id',
sa.Integer(),
nullable=False,
server_default=sa.text("nextval('" + table +
"_id_seq'::regclass)")))
op.drop_constraint(table + '_pkey', table_name=table)
op.create_primary_key(table + '_pkey', table, ['id'])
class Genome(Base):
__tablename__ = 'genome'
id = Column(Integer, Sequence('wids'), primary_key=True)
bioproject_id = Column(String(200))
organism = Column(String(200))
__table_args__ = (UniqueConstraint('bioproject_id'),{})
def __repr__(self):
return "Genome (#%d) %s %s" % (self.id, self.bioproject_id, self.organism)
def __init__(self, bioproject_id, organism):
self.bioproject_id = bioproject_id
self.organism = organism
def nextval(self):
if not self._db_seq:
self._db_seq = Sequence(self._seq_name,
start=self._start_num,
maxvalue=self._end_num,
cycle=True)
try:
self._current_num = db.execute(self._db_seq)
except Exception as e:
app.logger.debug(str(e))
self._db_seq.create(db.engine)
db.session.execute(self._db_seq)
self._current_num = db.session.execute(self._db_seq)
else:
self._current_num = db.session.execute(self._db_seq)
return self.id_format()
class ModelGene(Base):
__tablename__ = 'model_gene'
id = Column(Integer, Sequence('wids'), primary_key=True)
model_id = Column(Integer,
ForeignKey('model.id',
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
gene_id = Column(Integer,
ForeignKey('gene.id',
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
__table_args__ = (UniqueConstraint('model_id', 'gene_id'), )
class ModelCompartmentalizedComponent(Base):
__tablename__ = 'model_compartmentalized_component'
id = Column(Integer, Sequence('wids'), primary_key=True)
model_id = Column(Integer,
ForeignKey('model.id',
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
compartmentalized_component_id = Column(
Integer, ForeignKey('compartmentalized_component.id'), nullable=False)
formula = Column(String, nullable=True)
charge = Column(Integer, nullable=True)
__table_args__ = (UniqueConstraint('compartmentalized_component_id',
'model_id'), )
class OldIDSynonym(Base):
__tablename__ = "old_id_model_synonym"
id = Column(Integer, Sequence('wids'), primary_key=True)
type = Column(custom_enums['old_id_synonym_type'])
synonym_id = Column(Integer,
ForeignKey('synonym.id', ondelete='CASCADE'),
nullable=False)
ome_id = Column(Integer, nullable=False)
__table_args__ = (UniqueConstraint('synonym_id', 'ome_id'), )
def __repr__(self):
return (
'<ome OldIDSynonym(id=%d, type="%s", ome_id=%d, synonym_id=%d)>' %
(self.id, self.type, self.ome_id, self.synonym_id))
def add_archived_contact_table(self):
self.op.create_table(
'archived_contacts',
Column('id',
Integer,
Sequence('archived_contact_id_seq'),
primary_key=True),
Column('contact_id',
Integer,
ForeignKey('contacts.id'),
nullable=False),
Column('actor_id', String(USER_ID_LENGTH), nullable=False),
Column('created', DateTime(timezone=True)),
Column('description', Text),
Column('archived_contact_type', String(30), nullable=False),
)
class Genome(Base):
__tablename__ = 'genome'
id = Column(Integer, Sequence('wids'), primary_key=True)
accession_type = Column(String(200), nullable=False)
accession_value = Column(String(200), nullable=False)
organism = Column(String(200), nullable=True)
taxon_id = Column(String(200), nullable=True)
ncbi_assembly_id = Column(String(200), nullable=True)
__table_args__ = (UniqueConstraint('accession_type', 'accession_value'), )
def __repr__(self):
return (
'<ome Genome(id={self.id}, accession_type={self.accession_type}, '
'accession_value={self.accession_value})>'.format(self=self))
class Events(Base, Utility):
__tablename__ = 'events'
id = Column(Integer, Sequence('events_id_seq', start=1, increment=1), primary_key=True) # noqa
event_id = Column(String, nullable=False)
room_id = Column(Integer, ForeignKey('rooms.id', ondelete="CASCADE"))
event_title = Column(String, nullable=True)
start_time = Column(String, nullable=False)
end_time = Column(String, nullable=False)
checked_in = Column(Boolean, nullable=True)
cancelled = Column(Boolean, nullable=True)
state = Column(Enum(StateType), default="active")
room = relationship('Room')
recurring_event_id = Column(String, nullable=True)
number_of_participants = Column(Integer, nullable=False)
check_in_time = Column(String, nullable=True)
meeting_end_time = Column(String, nullable=True)
class Role(Base):
__tablename__ = 'role'
id = Column(Integer, Sequence('role_id_seq'), primary_key=True)
role = Column(String(80), unique=True, nullable=False)
chi_name = Column(String(80), unique=False, nullable=True)
def to_dict(self):
"""將數據轉為字典"""
dictionary = self.__dict__
if "_sa_instance_state" in dictionary:
del dictionary["_sa_instance_state"]
return dictionary
def __repr__(self):
"""讓print這個物件的時候，看起來好看"""
return '<Role %r>' % self.role
class PhoneNumber(Base):
__tablename__ = 'phonenumbers'
phone_number_id = Column('id',
Integer,
Sequence('phonenumber_id_seq'),
primary_key=True)
contact_id = Column('contact_id', Integer, ForeignKey('contacts.id'))
contact = relationship("Contact", back_populates="phonenumbers")
label = Column(String(CONTENT_TITLE_LENGTH))
phone_number = Column(String(CONTENT_TITLE_LENGTH), nullable=False)
def get_doc_property_provider(self, prefix):
return PhoneNumberDocPropertyProvider(self, prefix)
class ActivityTable(self.Base):
__tablename__ = activitytable
uniquerowid = Column(Integer,
Sequence(f'{activitytable}_id_seq', start=1, increment=1),
primary_key=True)
trackid = Column(String)
match = Column(String)
#object_value = Column(String) #new addition for object_value;
starting = Column(TIMESTAMP(timezone=False))
totaltime= Column(Float)
ending = Column(TIMESTAMP(timezone=False))
#### adding columns for multiple json usecase
relativestarting = Column(TIMESTAMP(timezone=False))
relativending = Column(TIMESTAMP(timezone=False))
endingsource = Column(String) #ending source for timestamp
startingsource = Column(String) #starting source for timestamp
class GeneratedDocument(Base):
"""Base class to track a document that was generated at some point.
Keeps a reference to the created document by storing it's oguid.
"""
__tablename__ = 'generateddocuments'
__table_args__ = (
UniqueConstraint('admin_unit_id', 'int_id',
name='ix_generated_document_unique'),
{})
document_id = Column("id", Integer, Sequence("generateddocument_id_seq"),
primary_key=True)
admin_unit_id = Column(String(UNIT_ID_LENGTH), nullable=False)
int_id = Column(Integer, nullable=False)
oguid = composite(Oguid, admin_unit_id, int_id)
generated_version = Column(Integer, nullable=False)
generated_document_type = Column(String(100), nullable=False)
__mapper_args__ = {'polymorphic_on': generated_document_type}
def __repr__(self):
return "<GeneratedDocument {}@{}>".format(
self.int_id, self.admin_unit_id)
def is_up_to_date(self, document):
assert Oguid.for_object(document) == self.oguid, 'invalid document'
return self.generated_version == document.get_current_version()
def resolve_document(self):
return self.oguid.resolve_object()
def unlock_document(self):
document = self.resolve_document()
lockable = ILockable(document)
lockable.unlock(SYS_LOCK)
assert not lockable.locked(), 'unexpected: could not remove lock'
def is_locked(self):
document = self.resolve_document()
return ILockable(document).locked()
def get_download_url(self):
return '{}/download'.format(self.resolve_document().absolute_url())
class PepedFlow(Base):
__tablename__ = 'peped_flows'
flow_record_id = Column(Integer,
Sequence('peped_flows_flow_record_id_seq'),
primary_key=True)
start_time = Column(Integer) #, nullable=False) / DateTime?
end_time = Column(Integer)
p_src_ip = Column(LargeBinary)
p_dst_ip = Column(LargeBinary)
src_port = Column(Integer)
dst_port = Column(Integer)
protocol = Column(Integer)
packets = Column(Integer)
bytes = Column(Integer)
def to_pb(self):
r = pep3_pb2.FlowRecord()
r.anonymous_part.start_time = self.start_time
r.anonymous_part.end_time = self.end_time
r.source_ip.data = self.p_src_ip
r.source_ip.state = pep3_pb2.Pseudonymizable.UNENCRYPTED_PSEUDONYM
r.destination_ip.data = self.p_dst_ip
r.destination_ip.state = pep3_pb2.Pseudonymizable.UNENCRYPTED_PSEUDONYM
r.anonymous_part.source_port = self.src_port
r.anonymous_part.destination_port = self.dst_port
r.anonymous_part.protocol = self.protocol
r.anonymous_part.number_of_packets = self.packets
r.anonymous_part.number_of_bytes = self.bytes
return r
def validate(self):
assert (self.packets > 0), "Flow can not have 0 packets"
assert (self.bytes > 0), "Flow can not have 0 bytes"
#TODO: are there any assertions we can do on the format/size of the triples?
def print(self):
print(self.__header__())
print(self)
def __header__(self):
return "{: >39} -> {: <39} {: <5} {: <5} -> {: <5} {: <10} {: <10}".format(
"src", "dst", "proto", "src_p", "dst_p", "bytes", "packets")
def __str__(self):
return "{!s: >39} -> {!s: <39} {: <5} {: <5} -> {: <5} {: <10} {: <10}".format(
hextail(self.p_src_ip), hextail(self.p_dst_ip), self.protocol,
self.src_port, self.dst_port, self.bytes, self.packets)
class CollectionContent(BASE, ModelBase):
"""Represents files"""
__tablename__ = 'ess_coll_content'
content_id = Column(BigInteger, Sequence('ESS_CONTENT_ID_SEQ'))
coll_id = Column(BigInteger)
scope = Column(String(SCOPE_LENGTH))
name = Column(String(NAME_LENGTH))
min_id = Column(BigInteger)
max_id = Column(BigInteger)
content_type = Column(ContentType.db_type(name='ESS_CONTENT_TYPE'),
default=ContentType.FILE)
# size = Column(BigInteger)
# md5 = Column(String(32))
# adler32 = Column(String(8))
edge_id = Column(Integer)
status = Column(ContentStatus.db_type(name='ESS_CONTENT_STATUS'),
default=ContentStatus.NEW)
priority = Column(Integer())
num_success = Column(Integer())
num_failure = Column(Integer())
last_failed_at = Column(DateTime)
pfn_size = Column(BigInteger)
pfn = Column(String(1024))
object_metadata = Column(JSON())
_table_args = (
PrimaryKeyConstraint('content_id', name='ESS_COLL_CONTENT_PK'),
# PrimaryKeyConstraint('scope', 'name', 'coll_id', 'content_type', 'min_id', 'max_id', 'edge_id', 'content_id', name='ESS_COLL_CONTENT_PK'),
ForeignKeyConstraint(['edge_id'], ['ess_edges.edge_id'],
name='ESS_CONTENT_EDGE_ID_FK'),
ForeignKeyConstraint(['coll_id'], ['ess_coll.coll_id'],
name='ESS_CONTENT_COLL_ID_FK'),
CheckConstraint('status IS NOT NULL', name='ESS_CONTENT_STATUS_NN'),
UniqueConstraint('scope',
'name',
'coll_id',
'content_type',
'min_id',
'max_id',
'edge_id',
name='ESS_CONTENT_UQ'),
Index('ESS_CONTENT_SCOPE_NAME_IDX', 'scope', 'name', 'edge_id',
'status'),
Index('ESS_CONTENT_SCOPE_NAME_MINMAX_IDX', 'scope', 'name',
'content_type', 'min_id', 'max_id', 'edge_id', 'status'),
Index('ESS_CONTENT_COLLECTION_ID_IDX', 'coll_id', 'status'),
Index('ESS_CONTENT_EDGE_ID_IDX', 'edge_id', 'status'),
Index('ESS_CONTENT_STATUS_PRIORITY_IDX', 'status', 'priority'))
class Notification(Base):
__tablename__ = 'notifications'
notification_id = Column('id',
Integer,
Sequence('notifications_id_seq'),
primary_key=True)
userid = Column(String(USER_ID_LENGTH), nullable=False)
activity_id = Column(Integer, ForeignKey('activities.id'))
activity = relationship("Activity", backref="notifications")
is_read = Column(Boolean, default=False, nullable=False)
is_badge = Column(Boolean, default=False, nullable=False)
is_digest = Column(Boolean, default=False, nullable=False)
# Flag if the notification has already been part of a digest mail
sent_in_digest = Column(Boolean, default=False, nullable=False)
def __repr__(self):
return u'<Notification {} for {} on {} >'.format(
self.notification_id, repr(self.userid),
repr(self.activity.resource))
def serialize(self, portal_url):
actor = Actor.lookup(self.activity.actor_id)
return {
'@id': self._api_url(portal_url),
'notification_id': self.notification_id,
'actor_id': self.activity.actor_id,
'actor_label': actor.get_label(with_principal=False),
'created': json_compatible(self.activity.created),
'read': self.is_read,
'title': self.activity.title,
'label': self.activity.label,
'link': self._resolve_notification_link(portal_url),
'summary': self.activity.summary,
}
def _resolve_notification_link(self, portal_url):
return '{}/@@resolve_notification?notification_id={}'.format(
portal_url, self.notification_id)
def _api_url(self, portal_url):
return '{}/@notifications/{}/{}'.format(portal_url, self.userid,
self.notification_id)
class Message(BASE, ModelBase):
"""Represents the event messages"""
__tablename__ = 'messages'
msg_id = Column(BigInteger().with_variant(Integer, "sqlite"),
Sequence('MESSAGE_ID_SEQ', schema=DEFAULT_SCHEMA_NAME),
primary_key=True)
msg_type = Column(EnumWithValue(MessageType))
status = Column(EnumWithValue(MessageStatus))
locking = Column(EnumWithValue(MessageLocking))
source = Column(EnumWithValue(MessageSource))
transform_id = Column(Integer())
num_contents = Column(Integer())
created_at = Column("created_at", DateTime, default=datetime.datetime.utcnow)
updated_at = Column("updated_at", DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
msg_content = Column(JSON())
_table_args = (PrimaryKeyConstraint('msg_id', name='MESSAGES_PK'))
class Output(Base):
__tablename__ = 'OUTPUT'
__table_args__ = table_args()
id = Column(Integer,
Sequence('OUTPUT_ID_SEQ', schema=schema()),
primary_key=True)
datasetname = Column(String(400))
nlumis = Column(Integer)
expectedlumis = Column(Integer)
nevents = Column(Integer)
nblocks = Column(Integer)
dsb_status = Column(String(30)) ## in DBS ?
status = Column(String(30))
## workflow it belongs to
workfow_id = Column(Integer, ForeignKey(prefix() + 'WORKFLOW.id'))
workflow = relationship(Workflow)
date = Column(Integer)
def insert_notification_defaults(self):
seq = Sequence('notification_defaults_id_seq')
for item in DEFAULT_SETTINGS:
setting = self.execute(defaults_table
.select()
.where(defaults_table.columns.kind == item.get('kind')))
if setting.rowcount:
# We don't want to reset already inserted settings
continue
self.execute(defaults_table
.insert()
.values(id=self.execute(seq),
kind=item['kind'],
badge_notification_roles=json.dumps(item['badge_notification_roles']),
digest_notification_roles=json.dumps(item['digest_notification_roles'])))
class TbUser(UserMixin, db.Model):
user_seq_id = Sequence('user_seq_id')
user_id = db.Column(db.Integer, user_seq_id, server_default=user_seq_id.next_value())
user_name = db.Column(db.String(255))
user_email = db.Column(db.String(255), primary_key=True)
user_password = db.Column(db.String(255))
about_me = db.Column(db.String(140))
last_seen = db.Column(db.DateTime, default=datetime.utcnow)
posts = db.relationship('Post', backref='author', lazy='dynamic')
def set_password(self, password):
self.user_password = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.user_password, password)
def set_userid(self):
pass
def __repr__(self):
return '<TbUser {}>'.format(self.user_name)
def get_id(self):
return self.user_email
def avatar(self, size):
digest = md5(self.user_email.lower().encode('utf-8')).hexdigest()
return 'https://www.gravatar.com/avatar/{}?d=identicon&s={}'.format(digest, size)
def follow(self, user):
if not self.is_following(user):
self.followed.append(user)
def unfollow(self, user):
if self.is_following(user):
self.followed.remove(user)
def is_following(self, user):
return self.followed.filter(followers.c.followed_email == user.user_email).count() > 0
followed = db.relationship(
'TbUser', secondary=followers,
primaryjoin=(followers.c.follower_email == user_email),
secondaryjoin=(followers.c.followed_email == user_email),
backref=db.backref('followers', lazy='dynamic'), lazy='dynamic')
class FirstPartRecord(Base):
__tablename__ = 'first_part_record'
id = Column(Integer, Sequence('first_part_record_id_seq'), primary_key=True)
send_time = Column(DateTime, unique=False, nullable=False)
finish_time = Column(DateTime, unique=False, nullable=True)
pnlist_id = Column(Integer, unique=False, nullable=False)
part_number = Column(String(80), unique=False, nullable=False)
machine_id = Column(Integer, unique=False, nullable=False)
machine_name = Column(String(80), unique=False, nullable=False)
mold_id = Column(Integer, unique=False, nullable=False)
mold_number = Column(String(80), unique=False, nullable=False)
mold_number_f = Column(String(80), unique=False, nullable=False)
material_id = Column(Integer, unique=False, nullable=True)
material_part_number = Column(String(80), unique=False, nullable=True)
grn_no = Column(String(80), unique=False, nullable=True)
batch_number = Column(String(80), unique=False, nullable=True)
examine_dependence = Column(String(80), unique=False, nullable=True)
type = Column(String(80), unique=False, nullable=True)
type_id = Column(Integer, unique=False, nullable=True)
all_determination = Column(String(80), unique=False, nullable=True)
dimension_id = Column(Integer, unique=False, nullable=True)
ps = Column(Text, unique=False, nullable=True)
building = Column(String(80), unique=False, nullable=True)
dimension_state = Column(Integer, unique=False, nullable=True)
examine_state = Column(Integer, unique=False, nullable=True)
def to_dict(self):
"""將數據轉為字典"""
dictionary = self.__dict__
if "_sa_instance_state" in dictionary:
del dictionary["_sa_instance_state"]
if "send_time" in dictionary:
try:
dictionary["send_time"] = dictionary["send_time"].strftime('%Y-%m-%d %H:%M:%S')
except:
pass
else:
pass
if "finish_time" in dictionary:
try:
dictionary["finish_time"] = dictionary["finish_time"].strftime('%Y-%m-%d %H:%M:%S')
except:
pass
else:
pass
return dictionary
def next_value(self, func):
return func.lpad(func.cast(Sequence.next_value(self, func), String),
8, '0')
