def init_user_and_trip_db(): """ Drops the user and trip tables and then instantiates them :return: None """ users = Table('users', metadata, Column('user_id', Integer, primary_key=True), Column('email', String(50)), Column('password', LargeBinary()), Column('is_admin', Integer, default=0)) trips = Table('trips', metadata, Column('user_id', Integer, primary_key=True), Column('timestamp', DateTime, primary_key=True), Column('start', String(100)), Column('stop', String(100)), Column('time', Integer)) metadata.drop_all() metadata.create_all()
class EntitySnapshotRecord(Base): __tablename__ = "entity_snapshots" # Application ID. application_name = Column(String(length=32), primary_key=True) # Originator ID (e.g. an entity or aggregate ID). originator_id = Column(UUIDType(), primary_key=True) # Originator version of item in sequence. originator_version = Column( BigInteger().with_variant(Integer, "sqlite"), primary_key=True ) # Topic of the item (e.g. path to domain entity class). topic = Column(Text(), nullable=False) # State of the item (serialized dict, possibly encrypted). state = Column(LargeBinary())
class Pathway(Base): """Represents a pathway network in BEL format harmonized by ComPath""" __tablename__ = NETWORK_TABLE_NAME id = Column(Integer, primary_key=True) name = Column(String(255), nullable=False, index=True, doc='Pathway name') resource_name = Column(String(255), nullable=False, index=True, doc='Database of origin') pathway_id = Column(String(255), nullable=False, index=True, doc='Pathway identifier in database of origin') number_of_nodes = Column(Integer(), doc='Number of nodes') number_of_edges = Column(Integer(), doc='Number of edges') version = Column(String(16), nullable=False, doc='Version of the BEL file') authors = Column(Text, nullable=True, doc='Authors of the underlying BEL file') contact = Column(String(255), nullable=True, doc='Contact email from the underlying pathway') description = Column(Text, nullable=True, doc='Descriptive text from the pathway') pybel_version = Column(String(16), nullable=False, doc='Version of PyBEL') created = Column(DateTime, nullable=False, default=datetime.datetime.utcnow) blob = Column(LargeBinary(LONGBLOB), doc='A pickled version of this pathway') def __str__(self): """Return pathway name.""" return '{} ({})'.format( self.name, DATABASE_STYLE_DICT.get(self.resource_name, self.resource_name) ) @property def display_name(self): """Return pathway name.""" return '{} ({})'.format( self.name, DATABASE_STYLE_DICT.get(self.resource_name, self.resource_name) ) def as_bel(self): """Get this network and loads it into a :class:`BELGraph`. :rtype: pybel.BELGraph """ return from_bytes(self.blob)
class Unit(Base): """Object that represents transfer units in the automation tools database. """ __tablename__ = "unit" id = Column(Integer, Sequence("user_id_seq"), primary_key=True) uuid = Column(String(36)) path = Column(LargeBinary()) unit_type = Column(String(10)) # ingest or transfer status = Column(String(20), nullable=True) microservice = Column(String(50)) current = Column(Boolean(create_constraint=False)) def __repr__(self): return ( "<Unit(id={s.id}, uuid={s.uuid}, unit_type={s.unit_type}, " "path={s.path}, status={s.status}, current={s.current})>".format( s=self))
def addColumnToTable(metadata, oeclass, fieldname, fieldtype): #~ print metadata, oeclass , fieldname, fieldtype table = metadata.tables[oeclass.tablename] names = [col.name for col in table.columns] if fieldname in names: print oeclass.tablename, 'add already ', fieldname return if fieldtype == numpy.ndarray: if fieldname + '_shape' not in names: col = Column(fieldname + '_shape', String(128)) col.create(table) col = Column(fieldname + '_dtype', String(128)) col.create(table) col = Column(fieldname + '_blob', LargeBinary(MAX_BINARY_SIZE)) col.create(table) else: col = Column(fieldname, fieldtype) col.create(table)
class LogTopic(Base): query = Session.query_property() __tablename__ = "logtopic" __table_args__ = ( UniqueConstraint("idx", "log_id", name="ix_idx_log_id"), Index("ix_idx_topic_topic_log_id", "idx", "topic_topic", "log_id"), ) id = Column(Integer, primary_key=True) idx = Column(Integer, nullable=False) topic_topic = Column(LargeBinary(32), ForeignKey("topic.topic"), index=True, nullable=False) log_id = Column(Integer, ForeignKey("log.id"), index=True, nullable=False) topic = relationship("Topic") log = relationship("Log")
class Attachment(Object): """An attachment for a message.""" message_id = Column(Integer, ForeignKey('message.id'), nullable=False) message = sqlalchemy.orm.relationship('Message', uselist=False, back_populates='attachments') filename = Column(String(100), default=u'') mimetype = Column(String(100), default=u'') size = Column(String(20), default=u'') data = Column(LargeBinary(MAX_FILE_SIZE)) white_page_before = Column(Boolean, default=False, nullable=False) date = Column(DateTime, nullable=True, default=datetime.datetime.now) user_id = Column(Integer, ForeignKey('user.id'), nullable=True, default=get_current_user) user = sqlalchemy.orm.relation('User', uselist=False, backref='attachments')
class TimestampSequencedNoIDRecord(Base): __tablename__ = "timestamp_sequenced_items_noid" # Sequence ID (e.g. an entity or aggregate ID). sequence_id = Column(UUIDType(), primary_key=True) # Position (timestamp) of item in sequence. position = Column(DECIMAL(24, 6, 6), primary_key=True) # Topic of the item (e.g. path to domain event class). topic = Column(Text(), nullable=False) # State of the item (serialized dict, possibly encrypted). state = Column(LargeBinary()) __table_args__ = ( Index( "timestamp_sequenced_items_noid_position_index", "position", unique=False ), )
class User(db.Model): id = Column(Integer, primary_key=True) email = Column(String(120), index=True, nullable=True, unique=True) icloud = Column(String(120), index=True, nullable=True, unique=True) password_hash = Column(LargeBinary(), nullable=True) date_joined = Column(DateTime(True), default=func.now()) date_last_activity = Column(DateTime(True), default=func.now()) date_v2_import = Column(DateTime(True), nullable=True) album = Column(Boolean(), default=True) single = Column(Boolean(), default=True) ep = Column(Boolean(), default=True) live = Column(Boolean(), default=False) soundtrack = Column(Boolean(), default=False) remix = Column(Boolean(), default=False) other = Column(Boolean(), default=False) artists = relationship("Artist", secondary="user_artist", lazy=True) releases = relationship("Release", secondary="user_release", lazy=True) @property def filters(self): filters = [] if self.album: filters.append("Album") if self.single: filters.append("Single") if self.ep: filters.append("EP") if self.live: filters.append("Live") if self.soundtrack: filters.append("Soundtrack") if self.remix: filters.append("Remix") if self.other: filters.append("Other") return filters def __repr__(self): return "<User {}>".format(self.id)
class File(Content): """File adds some attributes to :class:`~kotti.resources.Content` that are useful for storing binary data. """ implements(IFile) #: Primary key column in the DB #: (:class:`sqlalchemy.types.Integer`) id = Column(Integer(), ForeignKey('contents.id'), primary_key=True) #: The binary data itself #: (:class:`sqlalchemy.types.LargeBinary`) data = deferred(Column(LargeBinary())) #: The filename is used in the attachment view to give downloads #: the original filename it had when it was uploaded. #: (:class:`sqlalchemy.types.Unicode`) filename = Column(Unicode(100)) #: MIME type of the file #: (:class:`sqlalchemy.types.String`) mimetype = Column(String(100)) #: Size of the file in bytes #: (:class:`sqlalchemy.types.Integer`) size = Column(Integer()) type_info = Content.type_info.copy( name=u'File', title=_(u'File'), add_view=u'add_file', addable_to=[u'Document'], selectable_default_views=[], ) def __init__(self, data=None, filename=None, mimetype=None, size=None, **kwargs): super(File, self).__init__(**kwargs) self.data = data self.filename = filename self.mimetype = mimetype self.size = size
class Topic(Base): query = Session.query_property() __tablename__ = "topic" topic = Column(LargeBinary(32), primary_key=True) logs = relationship( "Log", secondary="logtopic", order_by=LogTopic.idx, primaryjoin=(LogTopic.topic_topic == topic), secondaryjoin=and_(LogTopic.log_idx == Log.idx, LogTopic.log_receipt_hash == Log.receipt_hash), ) def __repr__(self) -> str: return f"Topic(topic={self.topic!r})" def __str__(self) -> str: return f"Topic[{humanize_hash(self.topic)}]" # type: ignore
class ModeratorOrmModel(OrmModelBase): __tablename__ = 'moderator' id = Column(Integer(), primary_key=True) username = Column(String(), nullable=False, unique=True) password = deferred(Column(LargeBinary(), nullable=False)) roles = Column(MutableList.as_mutable(ARRAY(String)), nullable=False, index=True) # Bans given by this moderator given_bans = relationship('BanOrmModel', backref='moderator') posts = relationship('PostOrmModel', backref='moderator') boards = association_proxy('board_moderators', 'board', creator=create_board_for_proxy) logs = relationship('ModeratorLogOrmModel', backref='moderator')
class PageObject(Base): __tablename__ = 'page_objects' id = Column(Integer, primary_key=True) page_id = Column(Integer, ForeignKey('pages.id')) context_id = Column(Integer, ForeignKey('object_contexts.id')) bytes = Column(LargeBinary(length=(2**32)-1)) content = Column(String(10000)) bounding_box = Column(JSON) init_cls_confidences = Column(JSON) cls = Column(String(200)) pp_rule_cls = Column(String(200)) annotated_cls = Column(String(200)) confidence = Column(Numeric(precision=9, scale=6)) classification_success = Column(Boolean, unique=False, default=None) proposal_success = Column(Boolean, unique=False, default=None) summary = Column(Text()) keywords = Column(Text()) def as_dict(self): return {c.name: getattr(self, c.name) for c in self.__table__.columns}
def upgrade(): op.execute(CreateSequence(Sequence('object_id_seq'))) op.execute(CreateSequence(Sequence('blockchain_index_seq'))) op.create_table( 'blockchain', Column('id', Integer(), Sequence('object_id_seq'), primary_key=True), Column('object_id', String(), nullable=False), Column('creation_timestamp', Integer(), nullable=False), Column('data', LargeBinary(), nullable=False), Column('blockchain_index', Integer(), Sequence('blockchain_index_seq'), nullable=False, unique=True), Index('idx_blockchain', 'id', 'object_id', 'blockchain_index', 'creation_timestamp', unique=True))
class File(Content): id = Column(Integer(), ForeignKey('contents.id'), primary_key=True) data = Column(LargeBinary()) filename = Column(Unicode(100)) mimetype = Column(String(100)) size = Column(Integer()) type_info = Content.type_info.copy( name=u'File', title=_(u'File'), add_view=u'add_file', addable_to=[u'Document'], ) def __init__(self, data=None, filename=None, mimetype=None, size=None, **kwargs): super(File, self).__init__(**kwargs) self.data = data self.filename = filename self.mimetype = mimetype self.size = size
def create_column_if_not_exists(table, attrname, attrtype): """ Create a clolumn in a table given its python type. :param table: :param attribute: """ colnames = [ col.name for col in table.columns ] #~ if attrtype == np.ndarray or attrtype == pq.Quantity : #~ if attrname+'_blob' not in colnames: #~ col = Column(attrname+'_shape', String(128)) #~ col.create( table) #~ col = Column(attrname+'_dtype', String(128)) #~ col.create( table) #~ col = Column(attrname+'_blob', LargeBinary(MAX_BINARY_SIZE)) #~ col.create( table) #~ if attrtype == pq.Quantity : #~ col = Column(attrname+'_units', String(128)) #~ col.create( table) if attrtype == np.ndarray : if attrname+'_numpy_id' not in colnames: col = Column(attrname+'_numpy_id', Integer) col.create( table) elif attrtype == pq.Quantity : if attrname+'_quantity_id' not in colnames: col = Column(attrname+'_quantity_id', Integer) col.create( table) elif attrtype in python_to_sa_conversion: if attrname not in colnames: if attrtype is buffer: col = Column(attrname, LargeBinary(MAX_BINARY_SIZE)) else: col = Column(attrname, python_to_sa_conversion[attrtype]) col.create( table ) else: raise NotImplementedError
class Bindata(database.BASE): __tablename__ = 'bindata' id = Column(Integer, primary_key=True) asset_id = Column(Integer, ForeignKey('asset.id')) name = Column(String(512)) tag = Column(String(512)) note = Column(String(512)) content = Column(LargeBinary(1e7)) def __init__(self, asset_id=None, name=None, tag=None, note=None, content=None): self.asset_id = asset_id self.name = name self.tag = tag self.note = note self.content = content def __repr__(self): return '<Bindata id: %r />' % (self.id) def mimetype(self): ext = self.name.split('.')[-1].lower() if ext == 'png': return 'image/png' elif ext in ['jpg', 'jpeg']: return 'image/jpeg' else: raise ValueError('"%s" is not support' % ext) @property def serialize(self): # omit content to reduce transport size return { 'id': self.id, 'asset_id': self.asset_id, 'name': self.name, 'tag': self.tag, 'note': self.note }
class Compound(Base, ColumnPrinterMixin): FILE_TYPE = 'File' DIR_TYPE = 'Dir' __tablename__ = 'compounds' compound_id = Column(Integer, Sequence('compound_id_seq'), primary_key=True, unique=True, index=True) # type: CompoundID compound_name = Column(Text, unique=False, index=True) # type: CompoundName compound_type = Column(String(255)) # type: CompoundType compound_hash = Column(LargeBinary(64)) # type: CompoundHash compound_size = Column(BigInteger) # type: CompoundSize wrapping_type = Column(String(255)) # type: CompoundWrappingType compression_type = Column(String(255)) # type: CompoundCompressionType compound_version = Column(Integer) # type: CompoundVersion # compound_pending = Column(Boolean) # type: CompoundPendingFlag __table_args__ = (UniqueConstraint('compound_name', 'compound_hash', 'compound_type', 'compound_version'), UniqueConstraint('compound_name', 'compound_version')) def __init__(self, compound_name, compound_type, compound_hash, compound_size, wrapping_type, compression_type, compound_version=None): # type: (CompoundName, CompoundType, CompoundHash, CompoundSize, CompoundWrappingType, CompoundCompressionType, CompoundVersion) -> None self.compound_name = compound_name self.compound_type = compound_type self.compound_hash = compound_hash self.compound_size = compound_size self.wrapping_type = wrapping_type self.compression_type = compression_type self.compound_version = compound_version
class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key = True, autoincrement = True) username = Column(String, ForeignKey("users.username")) image = Column(LargeBinary(length = (2**32) - 1)) caption = Column(String) likes = relationship("User", secondary = likePostsTable, back_populates = "likePosts") comments = relationship("Comment") def __init__(self, username, image, caption, likes, comments): self.username = username self.image = image self.caption = caption self.likes = likes self.comments = comments def getID(self): return self.id def getUserName(self): return self.username def getImage(self): return base64.b64encode(self.image).decode("ascii") def getCaption(self): return self.caption def getLikes(self): return len(self.likes) def getComments(self): return self.comments def addLike(self, user): return self.likes.append(user) def removeLike(self, user): return self.likes.remove(user)
class User(UserMixin, Model): """A user of the app.""" __tablename__ = "user" id = Column(Integer, primary_key=True) username = Column(String(80), unique=True, nullable=False) email = Column(String(80), unique=True, nullable=False) password = Column(LargeBinary(128), nullable=True) first_name = Column(String(30), nullable=True) last_name = Column(String(30), nullable=True) def __init__(self, username=username, email=email, password=None, **kwargs): """Create instance.""" Model.__init__(self, username=username, email=email, **kwargs) if password: self.set_password(password) else: self.password = None def set_password(self, password): """Set password.""" self.password = bcrypt.generate_password_hash(password) def check_password(self, value): """Check password.""" return bcrypt.check_password_hash(self.password, value) @property def full_name(self): """Full user name.""" return f"{self.first_name} {self.last_name}" def __repr__(self): """Represent instance as a unique string.""" return f"<User({self.username!r})>"
class StoredEventRecord(Base): __tablename__ = "stored_events" # Application ID. application_name = Column(String(length=32), primary_key=True) # Originator ID (e.g. an entity or aggregate ID). originator_id = Column(UUIDType(), primary_key=True) # Originator version of item in sequence. originator_version = Column( BigInteger().with_variant(Integer, "sqlite"), primary_key=True ) # Pipeline ID. pipeline_id = Column(Integer(), nullable=True) # Notification ID. notification_id = Column( BigInteger().with_variant(Integer, "sqlite"), nullable=True ) # Topic of the item (e.g. path to domain event class). topic = Column(Text(), nullable=False) # State of the item (serialized dict, possibly encrypted). state = Column(LargeBinary()) # Causal dependencies. causal_dependencies = Column(Text()) __table_args__ = ( Index( "stored_events_notification_index", "application_name", "pipeline_id", "notification_id", unique=True, ), )
class DbCacheAddress(Base): """ Address Cache Table Stores transactions and unspent outputs (UTXO's) per address """ __tablename__ = 'cache_address' address = Column(String(255), primary_key=True, doc="Address string base32 or base58 encoded") network_name = Column(String(20), doc="Blockchain network name of this transaction") balance = Column(BigInteger, default=0, doc="Total balance of UTXO's linked to this key") last_block = Column(Integer, doc="Number of last updated block") last_txid = Column(LargeBinary(32), doc="Transaction ID of latest transaction in cache") n_utxos = Column(Integer, doc="Total number of UTXO's for this address") n_txs = Column(Integer, doc="Total number of transactions for this address")
class MemoryAccess(Base): __tablename__ = "memoryaccesses" # Access types READ = 0 WRITE = 1 id = Column(Integer, Sequence("memoryaccesses_id_seq"), primary_key=True) type = Column(Integer) rva = Column(BigInteger) module_id = Column(Integer, ForeignKey("modules.id")) chunk_id = Column(Integer, ForeignKey("memorychunks.id")) offset = Column(BigInteger) size = Column(BigInteger) # This is because in SQLite 64bit integer are signed. content = Column(LargeBinary(8)) def __init__(self, type, module, rva, chunk, offset, size, content): self.type = type self.module = module self.rva = rva self.chunk = chunk self.offset = offset self.size = size self.content = content def __repr__(self): return "MemoryAccess: type=%s, chunk=%x, module=%s, rva=%x, offset=%x, size=%x, content=%x" % \ (self.__type_to_string__(type), self.chunk.timestamp, self.module.name, self.rva, self.offset, self.size, unpack("<Q", self.content)[0]) def __type_to_string__(self, type): if type == MemoryAccess.READ: return "Read" return "Write"
def open(self): db_uri = self.db_uri if db_uri is None: # These are settings that apply only for development / testing only. The additional args are necessary # due to some limitations of the in-memory sqlite database. db_uri = 'sqlite:///:memory:' self.engine = create_engine( db_uri, poolclass=StaticPool, connect_args={'check_same_thread': False}) else: self.engine = create_engine(db_uri, echo=self.verbose, echo_pool=self.verbose) metadata = MetaData() metadata.bind = self.engine self.table = Table( self.table_name, metadata, Column('key', String(KEY_LEN), primary_key=True), Column('value', LargeBinary(VALUE_LEN)), )
class User(Base): __tablename__ = 'user' id: Column = Column(Integer, primary_key=True) email: Column = Column(String(), unique=True, nullable=False) hashed_password: Column = Column(String(), nullable=True) name: Column = Column(String(), nullable=False) blurb: Column = Column(Text(), nullable=True) avatar_data: Column = Column(LargeBinary(), nullable=True) avatar_image_type: Column = Column(String(), nullable=True) phone_number: Column = Column(String(), nullable=False) street: Column = Column(String(), nullable=False) suburb: Column = Column(String(), nullable=False) postcode: Column = Column(String(), nullable=False) state: Column = Column(String(), nullable=False) country: Column = Column(String(), nullable=False) listings = relationship('Listing', back_populates='owner') registrations = relationship('Registration', back_populates='user') starred_listings = relationship('Starred', back_populates='user') def __repr__(self): return f"<User: {self.name}>"
class Schedule(Base): __tablename__ = 'schedule' id = Column(Integer, primary_key=True) data = Column(LargeBinary()) delay = Column(String(32)) uuid = Column(String(160), unique=True) repeat = Column(Boolean, default=False) time = Column(DateTime()) def __init__(self, event, delay, uuid, repeat, time): self.event = event self.delay = delay self.uuid = uuid self.repeat = repeat self.time = time @property def event(self): return pickle.loads(self.data) if self.data else None @event.setter def event(self, event): self.data = pickle.dumps(event)
class Log(database.BASE): """Log Model.""" __tablename__ = 'log' id = Column(Integer, primary_key=True) result_id = Column(Integer, ForeignKey('result.id')) data = Column(LargeBinary(2048)) def __init__(self, data=None): bdata = msgpack.packb(data, use_bin_type=True) self.data = bdata def __repr__(self): return '<Log id: %r />' % (self.id) @property def serialize(self): """serialize.""" log_items = [] data = msgpack.unpackb(self.data, raw=False) for item in data.items(): value_to_store = (None if not isinstance(item[1], numbers.Number) or isinf(item[1]) or isnan(item[1]) else item[1]) log_items.append({ 'logId': self.id, 'key': item[0], 'value': value_to_store }) return { 'id': self.id, 'resultId': self.result_id, 'logItems': log_items }
class Log(DB_BASE): """Log Model.""" __tablename__ = 'log' id = Column(Integer, primary_key=True) result_id = Column(Integer, ForeignKey('result.id')) data = Column(LargeBinary(2048)) def __init__(self, data=None): bdata = msgpack.packb(data, use_bin_type=True) self.data = bdata def __repr__(self): return '<Log id: %r />' % (self.id) @property def serialize(self): """serialize.""" log_items = [] data = msgpack.unpackb(self.data, encoding='utf-8') for item in data.items(): log_items.append({ 'logId': self.id, 'key': item[0], 'value': None if isinf(item[1]) or isnan(item[1]) else item[1] }) return { 'id': self.id, 'resultId': self.result_id, 'logItems': log_items }
class UserModel(OrmModelBase): """ A user model also containing the elo for matchmaking points. """ __tablename__ = 'users' id = Column(Integer(), primary_key=True, autoincrement=True) name = Column(String(), unique=True, nullable=False) password = deferred(Column(LargeBinary(), nullable=False)) elo = Column(Float(), unique=False, default=1200) def __init__(self, name: str, password: str = None): self.name = name if password is not None: self.password = bcrypt.hashpw(password.encode(), bcrypt.gensalt()) def check_password(self, password): return bcrypt.checkpw(password.encode(), self.password) def to_json(self): return {"id": self.id, "name": self.name, "elo": self.elo}
class Plot(BASE): """ Sqlalchemy object for plot data """ __tablename__ = 'plot' id = Column(Integer, primary_key=True) signal_id = Column(Integer, ForeignKey('signal.id')) # for plot data in viewer data = Column(LargeBinary(length=(2**32)-1)) signal = relationship("Signal", backref="plot") def __repr__(self): return "PlotData(id=%r)" % self.id def get_plot(self): """ Converts plot blob to plot object :return: """ import binascii try: return json.loads(zlib.decompress(base64.b64decode(self.data)).decode('utf-8')) except binascii.Error: return None def add_plot_to_data(self, my_object): """ Converts plot object to blob :param my_object: :return: """ my_json_str = json.dumps(my_object) my_pack_str = base64.b64encode(zlib.compress(my_json_str.encode("utf-8"), 9)) self.data = my_pack_str