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')
