class Apiaries(Base): __tablename__ = "apiaries" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) name = Column(TEXT(), nullable=False) location = Column(TEXT(), nullable=False) user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False) honey_type_id = Column( UUID(as_uuid=True), ForeignKey(HoneyTypes.id), nullable=False ) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column( TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow ) deleted_at = Column(TIMESTAMP(), nullable=True) user = relationship(Users, backref="apiaries") honey_type = relationship(HoneyTypes) _nb_hives = None @property def nb_hives(self): if self._nb_hives is None: self._nb_hives = len(self.hives) # pylint: disable=no-member return self._nb_hives
class Events(Base): __tablename__ = "events" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) title = Column(TEXT(), nullable=False) description = Column(TEXT(), nullable=True) due_date = Column(TIMESTAMP(), nullable=False) user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False) type_id = Column(UUID(as_uuid=True), ForeignKey(EventTypes.id), nullable=False) status_id = Column(UUID(as_uuid=True), ForeignKey(EventStatuses.id), nullable=False) hive_id = Column(UUID(as_uuid=True), ForeignKey(Hives.id), nullable=True) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) deleted_at = Column(TIMESTAMP(), nullable=True) user = relationship(Users, backref="events") type = relationship(EventTypes) status = relationship(EventStatuses) hive = relationship(Hives, backref="events")
class ReleasesRaw(DeclarativeBase): __tablename__ = 'releases_raw' # column definitions beta_number = Column(u'beta_number', INTEGER()) build_id = Column(u'build_id', NUMERIC(), primary_key=True, nullable=False) # DEPRECATED COLUMN -- vendor supplied data, so this is a channel, not a # type, use 'update_channel' instead build_type = Column(u'build_type', CITEXT(), primary_key=True, nullable=False) platform = Column(u'platform', TEXT(), primary_key=True, nullable=False) product_name = Column(u'product_name', CITEXT(), primary_key=True, nullable=False) repository = Column(u'repository', CITEXT(), primary_key=True, nullable=False, server_default='mozilla-release') version = Column(u'version', TEXT(), primary_key=True, nullable=False) update_channel = Column(u'update_channel', TEXT()) # Above is a transition definition. # Ultimately we will define build_type as follows: # update_channel = Column(u'update_channel', TEXT(), primary_key=True, nullable=False) version_build = Column(u'version_build', TEXT()) # relationship definitions __table_args__ = {}
def downgrade(): op.create_table( 'bugs', Column(u'id', INTEGER(), primary_key=True, nullable=False), Column(u'status', TEXT()), Column(u'resolution', TEXT()), Column(u'short_desc', TEXT()), )
class User(MyBase, Base): __tablename__ = 'users' id = Column(UUID(), primary_key=True, nullable=False) active = Column(BOOLEAN()) username = Column(VARCHAR(50), unique=True) password = Column(TEXT()) google_id = Column(VARCHAR(50)) email = Column(VARCHAR(256), unique=True) face_analysis = Column(TEXT())
class GraphicsDevice(DeclarativeBase): __tablename__ = 'graphics_device' graphics_device_id = Column( u'graphics_device_id', INTEGER(), primary_key=True, nullable=False) vendor_hex = Column(u'vendor_hex', TEXT()) adapter_hex = Column(u'adapter_hex', TEXT()) vendor_name = Column(u'vendor_name', TEXT()) adapter_name = Column(u'adapter_name', TEXT())
class Correlations(DeclarativeBase): __tablename__ = 'correlations' # column definitions id = Column(u'id', INTEGER(), primary_key=True, autoincrement=True, nullable=False) product_version_id = Column(u'product_version_id', INTEGER( ), nullable=False, autoincrement=False, index=True) platform = Column(u'platform', TEXT(), primary_key=True, nullable=False) signature_id = Column(u'signature_id', INTEGER(), primary_key=False, nullable=False, index=True) key = Column(u'key', TEXT(), nullable=False) count = Column(u'count', INTEGER(), nullable=False, server_default=text('0')) notes = Column(u'notes', TEXT(), primary_key=False, nullable=False, server_default='') date = Column(u'date', DATE(), primary_key=False, nullable=False, index=True) payload = Column(u'payload', JSON()) # When looking for signatures by the correlations you need to query by: # product_version_id # platform # date # key # # When looking for correlations for a specific signature you need: # product_version_id # platform # key # date # signature # __table_args__ = ( Index( 'correlations_signatures_idx', product_version_id, platform, key, date, ), Index( 'correlations_signature_idx', product_version_id, platform, key, date, signature_id, unique=True ), )
class ProductVersion(DeclarativeBase): __tablename__ = 'product_versions' # column definitions product_version_id = Column( u'product_version_id', INTEGER(), primary_key=True, nullable=False) product_name = Column(u'product_name', CITEXT(), ForeignKey( 'products.product_name'), nullable=False, index=True) major_version = Column(u'major_version', MAJOR_VERSION(), index=True) release_version = Column(u'release_version', CITEXT(), nullable=False) version_string = Column(u'version_string', CITEXT(), nullable=False) beta_number = Column(u'beta_number', INTEGER()) version_sort = Column(u'version_sort', TEXT(), nullable=False, server_default="0", index=True) build_date = Column(u'build_date', DATE(), nullable=False) sunset_date = Column(u'sunset_date', DATE(), nullable=False) featured_version = Column(u'featured_version', BOOLEAN( ), nullable=False, server_default=text('False')) build_type = Column(u'build_type', CITEXT(), nullable=False, server_default='release') # DEPRECATED has_builds = Column(u'has_builds', BOOLEAN()) is_rapid_beta = Column(u'is_rapid_beta', BOOLEAN(), server_default=text('False')) rapid_beta_id = Column(u'rapid_beta_id', INTEGER(), ForeignKey( 'product_versions.product_version_id')) build_type_enum = Column(u'build_type_enum', build_type_enum()) # Above is a transition definition. # We will rename build_type_enum to build_type once old CITEXT column # is fully deprecated, also make this part of the primary key later. It # will look like this: # build_type = Column( # u'build_type_enum', build_type_enum(), nullable=False, server_default='release') version_build = Column(u'version_build', TEXT()) # Replaces 'beta_number' __table_args__ = ( Index('product_version_version_key', product_name, version_string, unique=True), ) # relationship definitions products = relationship( 'Product', primaryjoin='ProductVersion.product_version_id==ProductVersion.rapid_beta_id', secondary='ProductVersion', secondaryjoin='ProductVersion.product_name==Product.product_name' ) product_versions = relationship( 'ProductVersion', primaryjoin='ProductVersion.rapid_beta_id==ProductVersion.product_version_id' )
class SignatureProductsRollup(DeclarativeBase): __tablename__ = 'signature_products_rollup' signature_id = Column(u'signature_id', INTEGER(), ForeignKey('signatures.signature_id'), primary_key=True, nullable=False) product_name = Column(u'product_name', CITEXT(), ForeignKey('products.product_name'), primary_key=True, nullable=False) ver_count = Column(u'ver_count', INTEGER(), nullable=False, server_default=text('0')) version_list = Column(u'version_list', ARRAY(TEXT()), nullable=False, server_default=text("'{}'::text[]")) # relationship definitions products = relationship( 'Product', primaryjoin='SignatureProductsRollup.product_name==Product.product_name' ) signatures = relationship( 'Signature', primaryjoin= 'SignatureProductsRollup.signature_id==Signature.signature_id')
def sqlcol(dfparam, text=None): dtypedict = {} for i, j in zip(dfparam.columns, dfparam.dtypes): if "object" in str(j): if text == "postgresql-text": dtypedict.update({i: TEXT()}) else: try: x = int(df[i].str.len().max() / 40) + 1 except: x = 50 dtypedict.update({i: types.VARCHAR(length=x * 80)}) if "datetime" in str(j): dtypedict.update({i: types.DateTime()}) if "float" in str(j): dtypedict.update({i: types.Float(precision=3, asdecimal=True)}) if "int" in str(j): dtypedict.update({i: types.INT()}) return dtypedict
class PhotoThumbnails(db.Model): __tablename__ = 'photo_thumbnails' uuid = db.Column(UUID(as_uuid=True), unique=True, primary_key=True) photo_uuid = db.Column(UUID(as_uuid=True), db.ForeignKey('photos.uuid')) width = db.Column(INTEGER) height = db.Column(INTEGER) url = db.Column(TEXT()) created_at = db.Column(DateTime) def __init__(self, uuid, photo_uuid, url, width, height, created_at): self.uuid = uuid self.photo_uuid = photo_uuid self.url = url self.width = width self.height = height self.created_at = created_at def __repr__(self): return ','.join([ str(self.uuid), str(self.url), str(self.width), str(self.height), str(self.created_at) ])
class Crontabber(DeclarativeBase): __tablename__ = 'crontabber' # column definitions app_name = Column(u'app_name', TEXT(), primary_key=True, nullable=False) next_run = Column(u'next_run', TIMESTAMP(timezone=True)) first_run = Column(u'first_run', TIMESTAMP(timezone=True)) last_run = Column(u'last_run', TIMESTAMP(timezone=True)) last_success = Column(u'last_success', TIMESTAMP(timezone=True)) error_count = Column(u'error_count', INTEGER(), server_default=text('0')) depends_on = Column(u'depends_on', ARRAY(TEXT())) last_error = Column(u'last_error', JSON()) ongoing = Column(u'ongoing', TIMESTAMP(timezone=True)) __table_args__ = (Index('crontabber_app_name_idx', app_name, unique=True), )
class ProductProductidMap(DeclarativeBase): __tablename__ = 'product_productid_map' # column definitions product_name = Column(u'product_name', CITEXT(), ForeignKey('products.product_name'), nullable=False) productid = Column(u'productid', TEXT(), primary_key=True, nullable=False) rewrite = Column(u'rewrite', BOOLEAN(), nullable=False, server_default=text('False')) version_began = Column(u'version_began', MAJOR_VERSION()) version_ended = Column(u'version_ended', MAJOR_VERSION()) __table_args__ = (Index('productid_map_key2', product_name, version_began, unique=True), ) # relationship definitions products = relationship( 'Product', primaryjoin='ProductProductidMap.product_name==Product.product_name')
class BugAssociation(DeclarativeBase): __tablename__ = 'bug_associations' # column definitions bug_id = Column(u'bug_id', INTEGER(), primary_key=True, nullable=False, index=True) signature = Column(u'signature', TEXT(), primary_key=True, nullable=False)
class Photo(db.Model): __tablename__ = 'photos' uuid = db.Column(UUID(as_uuid=True), unique=True, primary_key=True) url = db.Column(TEXT()) status = db.Column( Enum('pending', 'completed', 'processing', 'failed', "male", name="photo_status", create_type=False)) created_at = db.Column(DateTime) def __init__(self, uuid, url, status, created_at): self.uuid = uuid self.url = url self.status = status self.created_at = created_at def __repr__(self): return ','.join([ str(self.uuid), str(self.url), str(self.status), str(self.created_at) ])
class Item(Base): __tablename__ = 'item' id = Column(BIGINT, primary_key=True) name = Column(TEXT, nullable=False) price = Column(NUMERIC, nullable=True) description = Column(TEXT(), nullable=True)
def data_decrypt(ciphertext): """ takes a ciphertext and returns the respective string reusing the Vault DB encryption module :param ciphertext: base64 ciphertext :return: plaintext string """ return Vault().process_result_value(ciphertext.encode("utf8"), TEXT())
class RawAdi(DeclarativeBase): __tablename__ = 'raw_adi' # column definitions adi_count = Column(u'adi_count', INTEGER()) date = Column(u'date', DATE()) product_name = Column(u'product_name', TEXT()) product_os_platform = Column(u'product_os_platform', TEXT()) product_os_version = Column(u'product_os_version', TEXT()) product_version = Column(u'product_version', TEXT()) build = Column(u'build', TEXT()) product_guid = Column(u'product_guid', TEXT()) update_channel = Column(u'update_channel', TEXT()) received_at = Column(u'received_at', TIMESTAMP(timezone=True), server_default=text('NOW()')) __mapper_args__ = { "primary_key": (adi_count, date, product_name, product_version, product_os_platform, product_os_version, build, product_guid, update_channel) } __table_args__ = (Index(u'raw_adi_1_idx', date, product_name, product_version, product_os_platform, product_os_version), )
class CrontabberLog(DeclarativeBase): __tablename__ = 'crontabber_log' # column definitions id = Column(u'id', INTEGER(), primary_key=True, nullable=False) app_name = Column(u'app_name', TEXT(), nullable=False) log_time = Column(u'log_time', TIMESTAMP(timezone=True), nullable=False, server_default=text('NOW()')) duration = Column(u'duration', INTERVAL()) success = Column(u'success', TIMESTAMP(timezone=True)) exc_type = Column(u'exc_type', TEXT()) exc_value = Column(u'exc_value', TEXT()) exc_traceback = Column(u'exc_traceback', TEXT()) __table_args__ = ( Index('crontabber_log_app_name_idx', app_name), Index('crontabber_log_log_time_idx', log_time), )
class Signature(DeclarativeBase): __tablename__ = 'signatures' # column definitions first_build = Column(u'first_build', NUMERIC()) first_report = Column(u'first_report', TIMESTAMP(timezone=True)) signature = Column(u'signature', TEXT(), index=True, unique=True) signature_id = Column(u'signature_id', INTEGER(), primary_key=True, nullable=False)
def data_encrypt(data): """ takes an input and returns a base64 encoded encryption reusing the Vault DB encryption module :param data: string :return: base64 ciphertext """ if not isinstance(data, str): data = str(data) ciphertext = Vault().process_bind_param(data, TEXT()) return ciphertext.decode("utf8")
class Comments(Base): __tablename__ = "comments" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) comment = Column(TEXT(), nullable=False) date = Column(TIMESTAMP(), nullable=False) type = Column(TEXT(), nullable=False) user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False) swarm_id = Column(UUID(as_uuid=True), ForeignKey(Swarms.id), nullable=True) hive_id = Column(UUID(as_uuid=True), ForeignKey(Hives.id), nullable=True) event_id = Column(UUID(as_uuid=True), ForeignKey(Events.id), nullable=True) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column( TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow ) deleted_at = Column(TIMESTAMP(), nullable=True) user = relationship(Users, backref="comments") swarm = relationship(Swarms, backref="comments") hive = relationship(Hives, backref="comments") event = relationship(Events, backref=backref("comment", uselist=False))
class SwarmHealthStatuses(Base): __tablename__ = "swarm_health_statuses" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) name = Column(TEXT()) user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) deleted_at = Column(TIMESTAMP(), nullable=True) user = relationship(Users, backref="swarm_healths")
class HiveConditions(Base): __tablename__ = "hive_conditions" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) name = Column(TEXT(), nullable=False) user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column( TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow ) deleted_at = Column(TIMESTAMP(), nullable=True) user = relationship(Users, backref="hive_conditions")
class Users(Base): __tablename__ = "users" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) email = Column(TEXT(), unique=True, nullable=False) activation_id = Column(UUID(as_uuid=True), nullable=True, default=uuid.uuid4) activated = Column(BOOLEAN(), nullable=False, default=False) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) deleted_at = Column(TIMESTAMP(), nullable=True)
def write_gg(path, conp, tbname, jytype=None): if jytype == 'gcjs': jytype = "工程建设" if jytype == 'zfcg': jytype = "政府采购" # path="D:\\bsttmp\\kuming_gcjs\\file" # conp=["postgres",'since2015','192.168.4.188','base','cdc'] # tbname="cdc_gg" arr = os.listdir(path) for w in arr: if w.endswith('csv'): #print(w) csv = w break dfs = pd.read_csv(os.path.join(path, csv), sep='\001', quotechar='\002', chunksize=1000) count = 1 for df in dfs: df.columns = [ 'bd_guid', 'bd_bh', 'bd_name', 'zbr', 'zbdl', 'xmjl', 'xmjl_dj', 'xmjl_zsbh', 'bm_endtime', 'bm_endtime_src', 'tb_endtime', 'tb_endtime_src', 'bzj_time', 'bzj_time_src', 'kb_time', 'kb_time_src', 'pb_time', 'pb_time_src', 'db_time', 'db_time_src', 'pb_time', 'pb_time_src', 'zhongbiao_hxr', 'zhongbiao_hxr_src', 'kzj', 'kzj_src', 'zhongbiaojia', 'zhongbiaojia_src', 'bd_dizhi', 'diqu', 'ggtype', 'gg_name', 'gg_fabutime', 'gg_file', 'gg_fujian_file', 'gg_href' ] df['jytype'] = jytype datadict = {w: TEXT() for w in df.columns} if count == 1: db_write(df, tbname, dbtype='postgresql', conp=conp, datadict=datadict) else: db_write(df, tbname, dbtype='postgresql', conp=conp, if_exists='append', datadict=datadict) print("写入第%d " % count) count += 1
class Credentials(Base): __tablename__ = "credentials" id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) username = Column(TEXT(), unique=True, nullable=False) password = Column(BYTEA(), nullable=False) last_seen = Column(TIMESTAMP()) user_id = Column(UUID(as_uuid=True), ForeignKey(Users.id), nullable=False) reset_id = Column(UUID(as_uuid=True), nullable=True) created_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow) updated_at = Column(TIMESTAMP(), default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) user = relationship(Users, backref="credentials", uselist=False)
class ProductVersionBuild(DeclarativeBase): __tablename__ = 'product_version_builds' # column definitions build_id = Column(u'build_id', NUMERIC(), primary_key=True, nullable=False) platform = Column(u'platform', TEXT(), primary_key=True, nullable=False) product_version_id = Column(u'product_version_id', INTEGER(), ForeignKey( 'product_versions.product_version_id'), primary_key=True, nullable=False) repository = Column(u'repository', CITEXT()) # relationship definitions product_versions = relationship( 'ProductVersion', primaryjoin='ProductVersionBuild.product_version_id==ProductVersion.product_version_id' )
class OsNameMatche(DeclarativeBase): __tablename__ = 'os_name_matches' # column definitions match_string = Column(u'match_string', TEXT(), primary_key=True, nullable=False) os_name = Column(u'os_name', CITEXT(), ForeignKey( 'os_names.os_name'), primary_key=True, nullable=False) __table_args__ = ( Index('os_name_matches_key', os_name, match_string, unique=True), ) # relationship definitions os_names = relationship( 'OsName', primaryjoin='OsNameMatche.os_name==OsName.os_name')
class Signature(DeclarativeBase): __tablename__ = 'signatures' # column definitions first_build = Column(u'first_build', NUMERIC()) first_report = Column(u'first_report', TIMESTAMP(timezone=True)) signature = Column(u'signature', TEXT(), index=True, unique=True) signature_id = Column(u'signature_id', INTEGER(), primary_key=True, nullable=False) # relationship definitions products = relationship( 'Product', primaryjoin='Signature.signature_id==SignatureProductsRollup.signature_id', secondary='SignatureProductsRollup', secondaryjoin='SignatureProductsRollup.product_name==Product.product_name')