from sqlalchemy.sql import func, select, insert, except_
from sqlalchemy.exc import IntegrityError
import pandas as pd
DATABASE_URL = os.environ.get('DATABASE_URL',
'postgres://localhost/accelerator')
engine = sqlalchemy.create_engine(DATABASE_URL)
metadata = MetaData()
search_table = Table(
'searches', metadata, Column('id', BigInteger, primary_key=True),
Column('query_id', None, ForeignKey('queries.query_id',
ondelete='CASCADE')),
Column('dataset_id', None,
ForeignKey('datasets.dataset_id', ondelete='CASCADE')),
Column('clicked_urls', ARRAY(String), nullable=False),
Column('all_urls', ARRAY(String), nullable=False),
Column('final_click_url', String, nullable=False),
Column('final_click_rank', Integer, nullable=False))
query_table = Table(
'queries', metadata, Column('query_id', BigInteger, primary_key=True),
Column('search_term_lowercase', String, unique=True, nullable=False),
Column('normalised_search_term', String, unique=False, nullable=False),
Column('high_volume', Boolean, default=False))
dataset_table = Table(
'datasets',
metadata,
Column('dataset_id', BigInteger, primary_key=True),
Column('filename', String, nullable=False, unique=True),
class Plans(Base):
"""
This is the Medicare plans that are paid for don't change
"""
__tablename__ = 'plans'
id = Column(Integer, primary_key=True)
CONTRACT_ID = Column(String)
PLAN_ID = Column(Integer)
SEGMENT_ID = Column(String)
CONTRACT_NAME = Column(String)
PLAN_NAME = Column(String)
FORMULARY_ID = Column(Integer)
PREMIUM = Column(DECIMAL(precision=8, asdecimal=True, scale=2),
nullable=True)
DEDUCTIBLE = Column(DECIMAL(precision=8, asdecimal=True, scale=2),
nullable=True)
ICL = Column(Integer, nullable=True)
MA_REGION_CODE = Column(Integer)
PDP_REGION_CODE = Column(Integer)
STATE = Column(String)
COUNTY_CODE = Column(Integer)
SNP = Column(Integer, nullable=True)
PLAN_SUPPRESSED_YN = Column(Boolean)
GEO_ids = Column(ARRAY(Integer, ForeignKey('geolocate.id')))
@classmethod
def find_by_formulary_id(cls, fid):
"""
:param fid:
:return:
"""
qry = cls.session.query(cls).filter(cls.FORMULARY_ID == fid)
return qry.all()
@classmethod
def find_by_plan_name(cls, name, exact=False, geo=None):
"""
Find similar drugs in the database
:param name: drug name
:return: matches
"""
if not exact:
name = f"%{name.lower()}%"
else:
name = name.lower()
fltr = cls.PLAN_NAME.ilike(name)
if geo:
#select * from plans where 2090 = ANY("GEO_ids");
fltr = and_(fltr, cls.GEO_ids.any(geo))
qry = cls.session.query(cls).filter(fltr)
return qry.all()
@classmethod
def find_in_county(cls, county_code, ma_region, pdp_region, name='*'):
"""
Query plans in a certain county
"""
flter = or_(cls.COUNTY_CODE == county_code,
cls.MA_REGION_CODE == ma_region,
cls.PDP_REGION_CODE == pdp_region)
if not name == '*':
look_for = f"{name.lower()}%"
flter = and_(flter, cls.PLAN_NAME.ilike(look_for))
qry = cls.session.query(Plans.PLAN_NAME).filter(flter).distinct(
cls.PLAN_NAME).all()
results = [r.PLAN_NAME for r in qry]
return results
def __repr__(self):
return "<{}>".format(self.PLAN_NAME)
def __init__(self, sql, meta):
self.sql = sql
self.tb_guild_settings = Table(
"guild_settings",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("prefix", Unicode, nullable=True),
Column("max_delete_messages", SmallInteger),
Column("warn_manual_mod_action", Boolean),
Column("remove_other_roles", Boolean),
Column("mentionable_name_prefix", SmallInteger),
CheckConstraint(
"mentionable_name_prefix >= 0 AND mentionable_name_prefix <= 32",
name="mentionable_name_prefix_in_range",
),
)
self.tb_special_roles = Table(
"special_roles",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("member_role_id", BigInteger, nullable=True),
Column("guest_role_id", BigInteger, nullable=True),
Column("mute_role_id", BigInteger, nullable=True),
Column("jail_role_id", BigInteger, nullable=True),
Column("focus_role_id", BigInteger, nullable=True),
Column("nonpurge_role_id", BigInteger, nullable=True),
# Ensures special roles aren't assigned to @everyone
CheckConstraint(
"member_role_id is NULL OR member_role_id != guild_id",
name="special_role_member_not_everyone_check",
),
CheckConstraint(
"guest_role_id is NULL or guest_role_id != guild_id",
name="special_role_guest_not_everyone_check",
),
CheckConstraint(
"mute_role_id is NULL or mute_role_id != guild_id",
name="special_role_mute_not_everyone_check",
),
CheckConstraint(
"jail_role_id is NULL or jail_role_id != guild_id",
name="special_role_jail_not_everyone_check",
),
CheckConstraint(
"focus_role_id is NULL or focus_role_id != guild_id",
name="special_role_focus_not_everyone_check",
),
CheckConstraint(
"nonpurge_role_id is NULL or nonpurge_role_id != guild_id",
name="special_role_nonpurge_not_everyone_check",
),
# Ensures Guest and punishment roles aren't the same as the Member role
CheckConstraint(
"guest_role_id is NULL OR guest_role_id != member_role_id",
name="special_role_guest_not_member_check",
),
CheckConstraint(
"mute_role_id is NULL OR mute_role_id != member_role_id",
name="special_role_mute_not_member_check",
),
CheckConstraint(
"jail_role_id is NULL OR jail_role_id != member_role_id",
name="special_role_jail_not_member_check",
),
CheckConstraint(
"focus_role_id is NULL OR focus_role_id != member_role_id",
name="special_role_focus_not_member_check",
),
CheckConstraint(
"nonpurge_role_id is NULL OR nonpurge_role_id != member_role_id",
name="special_role_nonpurge_not_member_check",
),
)
self.tb_reapply_roles = Table(
"reapply_roles",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("auto_reapply", Boolean),
Column("role_ids", ARRAY(BigInteger)),
)
self.tb_tracking_blacklists = Table(
"tracking_blacklists",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
index=True),
Column("type", Enum(LocationType)),
Column("data_id", BigInteger),
UniqueConstraint("guild_id",
"type",
"data_id",
name="tracking_blacklist_uq"),
CheckConstraint(
"type IN ('CHANNEL'::locationtype, 'USER'::locationtype)",
name="type_is_channel_or_user_check",
),
)
self.tb_optional_cog_settings = Table(
"optional_cog_settings",
meta,
Column("guild_id",
BigInteger,
ForeignKey("guilds.guild_id"),
primary_key=True),
Column("cog_name", String, primary_key=True),
Column("settings", JSON),
)
self.guild_settings_cache = {}
self.special_roles_cache = {}
self.reapply_roles_cache = {}
self.tracking_blacklist_cache = {}
self.optional_cog_settings_cache = {}
register_hook("on_guild_join", self.add_guild_settings)
register_hook("on_guild_join", self.add_special_roles)
register_hook("on_guild_join", self.add_reapply_roles)
class Venue(db.Model):
"""Holds data for Venues"""
__tablename__ = 'venue'
id = Column(Integer, primary_key=True)
name = Column(String)
city = Column(String(120))
state = Column(String(120))
phone = Column(String(120))
image_link = Column(String(500))
facebook_link = Column(String(120))
genres = Column(ARRAY(String))
website = Column(String(120))
seeking_talent = Column(Boolean, default=False)
seeking_description = Column(String(120))
address = Column(String(120))
shows = db.relationship('Show', backref='venue', lazy='dynamic')
def __init__(self,
name,
city,
state,
phone,
image_link,
facebook_link,
genres,
website,
seeking_description="",
address=address,
seeking_talent=False):
"""__init__ for Venue Class
Values stored represent the attributes of a Venue
Parameters
----------
name : String
name of venue
city : String
name of city
state : String
name of state
address : String
address of venue
phone : String
phone number of venue
image_link : String
image link for venue
facebook_link : String
facebook link for venue
genres : list (String)
list of genres for venue
website : String
website link for venue
seeking_talent : Boolean
seeking talent (yes/no)
seeking_description : String
venue description
"""
self.name = name
self.city = city
self.state = state
self.phone = phone
self.image_link = image_link
self.facebook_link = facebook_link
self.genres = genres
self.website = website
self.seeking_description = seeking_description
self.address = address
self.seeking_talent = seeking_talent
def delete(self):
"""Deletes Venue item from database
Raises
------
SQLAlchemyError :
raises error on failed delete attempt
"""
try:
db.session.delete(self)
db.session.commit()
flash(self.name + ' was successfully deleted!')
except SQLAlchemyError as e:
flash('Error! Venue could not be deleted')
db.session.rollback()
finally:
db.session.close()
def title(self):
return {'id': self.id, 'name': self.name}
def detail(self):
return {
'id': self.id,
'name': self.name,
'genres': self.genres,
'address': self.address,
'city': self.city,
'state': self.state,
'phone': self.phone,
'website': self.website,
'facebook_link': self.facebook_link,
'seeking_talent': self.seeking_talent,
'seeking_description': self.seeking_description,
'image_link': self.image_link
}
class Book(Base):
__tablename__ = "books"
id = Column(Integer, primary_key=True)
title = Column(String(128))
author = Column(String(128))
readers = Column(ARRAY(String(128)))
from sqlalchemy import (Column, Integer, MetaData, String, Table,
create_engine, ARRAY)
from databases import Database
#import os
# DATABASE_URL: postgres://{user}:{password}@{hostname}:{port}/{database-name}
DATABASE_URL = 'postgresql://*****:*****@localhost/movie_db'
#DATABASE_URI = os.getenv('DATABASE_URI')
engine = create_engine(DATABASE_URL)
metadata = MetaData()
movies = Table(
'movies',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('plot', String(250)),
Column('genres', ARRAY(String)),
Column('casts', ARRAY(String))
)
database = Database(DATABASE_URL)
class DataSource(Base):
SUPPORTED_SOURCES = [
('bigquery', 'BigQuery',),
('postgres', 'PostgreSQL'),
('mysql', 'MySQL', ),
('exa+pyexasol', 'Exasol')
]
__tablename__ = 'data_source'
id = Column(Integer, primary_key=True)
name = Column(String, unique=True)
source_type = Column(String)
host = Column(String)
database = Column(String)
user = Column(String)
password = Column(String)
schemas = Column(ARRAY(String))
run_for_all = Column(Boolean, default=True)
def __str__(self):
return f"{self.name}"
@property
def db_url(self):
if self.source_type == 'bigquery':
return f'{self.source_type}://{self.host}'
return f'{self.source_type}://{self.user}:{self.password}@{self.host}/{self.database}'
def get_db_object(self):
db_url = self.db_url
if self.source_type == 'exa+pyexasol':
return Exasol(self, ExasolEngine(db_url), schema=self.schemas)
if self.source_type == 'bigquery':
db = create_engine(db_url, credentials_path=settings.REDATA_BIGQUERY_DOCKER_CREDS_FILE_PATH)
return BigQuery(self, db, schema=self.schemas)
db = create_engine(db_url)
if self.source_type == 'postgres':
return Postgres(self, db, schema=self.schemas)
if self.source_type == 'mysql':
return MySQL(self, db, schema=self.schemas)
raise Exception('Not supported DB')
@classmethod
def source_dbs(cls):
if not getattr(cls, 'source_db_objects', None):
sources = metrics_session.query(cls).all()
source_dbs = [s.get_db_object() for s in sources]
cls.source_db_objects = source_dbs
return cls.source_db_objects
def get_db_by_name(name):
for source_db in settings.REDATA_SOURCE_DBS:
if source_db['name'] == name:
return get_db_object(source_db)
from sqlalchemy import Integer, String, ARRAY, MetaData
from sqlalchemy.schema import Table, Column, ForeignKey
from Utils import DatabaseEngine
from Users import UserTable
metadata = MetaData()
# Create Messages Table
table = Table(
'messages', metadata, Column('id', Integer, primary_key=True, index=True),
Column('usr_id', Integer,
ForeignKey(UserTable.c.id, onupdate="CASCADE", ondelete="CASCADE")),
Column('text', String), Column('media', ARRAY(String)),
Column('link', String))
# Relationship table with users likes
likes = Table(
'messages_likes_users', metadata,
Column('msg_id',
Integer,
ForeignKey(table.c.id, onupdate="CASCADE", ondelete="CASCADE"),
primary_key=True),
Column('usr_id',
Integer,
ForeignKey(UserTable.c.id, onupdate="CASCADE", ondelete="CASCADE"),
primary_key=True))
metadata.create_all(DatabaseEngine)
class Venue(db.Model):
__tablename__ = "venue"
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
city = Column(String(120), nullable=False)
state = Column(String(120), nullable=False)
address = Column(String(120), nullable=False)
phone = Column(String(120), nullable=False)
image_link = Column(String(500))
facebook_link = Column(String(120))
website_link = Column(String(500))
seeking_talent = Column(Boolean)
seeking_description = Column(String(100))
genres = Column(ARRAY(String(50)), nullable=True)
shows = db.relationship("Musicshows", backref="venue", lazy="dynamic")
def __init__(
self,
name,
genres,
address,
city,
state,
phone,
website_link,
facebook_link,
image_link,
seeking_talent=False,
seeking_description="",
):
self.name = name
self.genres = genres
self.address = address
self.city = city
self.state = state
self.phone = phone
self.website = website_link
self.facebook_link = facebook_link
self.seeking_talent = seeking_talent
self.seeking_description = seeking_description
self.image_link = image_link
def insert(self):
db.session.add(self)
db.session.commit()
def update(self):
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def short(self):
return {
"id": self.id,
"name": self.name,
}
def long(self):
print(self)
return {
"id": self.id,
"name": self.name,
"city": self.city,
"state": self.state,
}
def details(self):
return {
"id": self.id,
"name": self.name,
"genres": self.genres,
"address": self.address,
"city": self.city,
"state": self.state,
"phone": self.phone,
"website_link": self.website_link,
"facebook_link": self.facebook_link,
"seeking_talent": self.seeking_talent,
"seeking_description": self.seeking_description,
"image_link": self.image_link,
}
class UIPerformanceTests(AbstractBaseMixin, Base):
__tablename__ = "ui_performance_tests"
id = Column(Integer, primary_key=True)
project_id = Column(Integer, unique=False, nullable=False)
test_uid = Column(String(128), unique=True, nullable=False)
name = Column(String(128), nullable=False)
bucket = Column(String(128), nullable=False)
file = Column(String(128), nullable=False)
entrypoint = Column(String(128), nullable=False)
runner = Column(String(128), nullable=False)
browser = Column(String(128), nullable=False)
reporting = Column(ARRAY(String), nullable=False)
parallel = Column(Integer, nullable=False)
params = Column(JSON)
env_vars = Column(JSON)
customization = Column(JSON)
cc_env_vars = Column(JSON)
last_run = Column(Integer)
job_type = Column(String(20))
loops = Column(Integer)
aggregation = Column(String(20))
def configure_execution_json(self,
output='cc',
browser=None,
test_type=None,
params=None,
env_vars=None,
reporting=None,
customization=None,
cc_env_vars=None,
parallel=None,
execution=False):
reports = []
for report in self.reporting:
if report:
reports.append(f"-r {report}")
cmd = f"-f {self.file} -sc /tmp/data/{self.entrypoint} -l {self.loops} -b {browser} " \
f"-a {self.aggregation} {' '.join(reports)}"
execution_json = {
"container": self.runner,
"execution_params": {
"cmd":
cmd,
"REMOTE_URL":
f'{unsecret("{{secret.redis_host}}", project_id=self.project_id)}:4444'
},
"cc_env_vars": {},
"bucket": self.bucket,
"job_name": self.name,
"artifact": self.file,
"job_type": self.job_type,
"concurrency": 1
}
if "jira" in self.reporting:
execution_json["execution_params"]["JIRA"] = unsecret(
"{{secret.jira}}", project_id=self.project_id)
if "quality" in self.reporting:
execution_json["quality_gate"] = True
if "junit" in self.reporting:
execution_json["junit"] = True
if self.env_vars:
for key, value in self.env_vars.items():
execution_json["execution_params"][key] = value
if self.cc_env_vars:
for key, value in self.cc_env_vars.items():
execution_json["cc_env_vars"][key] = value
if "REDIS_HOST" not in execution_json["cc_env_vars"].keys():
execution_json["cc_env_vars"][
"REDIS_HOST"] = "{{secret.redis_host}}"
if "REDIS_PASSWORD" not in execution_json["cc_env_vars"].keys():
execution_json["cc_env_vars"][
"REDIS_PASSWORD"] = "******"
if self.customization:
for key, value in self.customization.items():
if "additional_files" not in execution_json[
"execution_params"]:
execution_json["execution_params"][
"additional_files"] = dict()
execution_json["execution_params"]["additional_files"][
key] = value
execution_json["execution_params"] = dumps(
execution_json["execution_params"])
if execution:
execution_json = unsecret(execution_json,
project_id=self.project_id)
if output == 'cc':
current_app.logger.error(execution_json)
return execution_json
return f'docker run -t --rm -e project_id={self.project_id} ' \
f'-e galloper_url={unsecret("{{secret.galloper_url}}", project_id=self.project_id)} ' \
f"-e token=\"{unsecret('{{secret.auth_token}}', project_id=self.project_id)}\" " \
f'getcarrier/control_tower:latest ' \
f'--test_id {self.test_uid}'
class PerformanceTests(AbstractBaseMixin, Base):
__tablename__ = "performance_tests"
id = Column(Integer, primary_key=True)
project_id = Column(Integer, unique=False, nullable=False)
test_uid = Column(String(128), unique=True, nullable=False)
name = Column(String(128), nullable=False)
parallel = Column(Integer, nullable=False)
bucket = Column(String(128), nullable=False)
file = Column(String(128), nullable=False)
entrypoint = Column(String(128), nullable=False)
runner = Column(String(128), nullable=False)
reporting = Column(ARRAY(String), nullable=False)
emails = Column(Text)
params = Column(JSON)
env_vars = Column(JSON)
customization = Column(JSON)
cc_env_vars = Column(JSON)
git = Column(JSON)
last_run = Column(Integer)
job_type = Column(String(20))
container_mapping = {
"jmeter5": {
"container": "getcarrier/perfmeter:latest",
"job_type": "perfmeter",
"influx_db": "jmeter"
},
"jmeter4": {
"container": "getcarrier/perfmeter:latest",
"job_type": "perfmeter",
"influx_db": "jmeter"
},
"gatling2": {
"container": "getcarrier/perfgun:2.3",
"job_type": "perfgun",
"influx_db": "gatling"
},
"gatling3": {
"container": "getcarrier/perfgun:latest",
"job_type": "perfgun",
"influx_db": "gatling"
}
}
def set_last_run(self, ts):
self.last_run = ts
self.commit()
@staticmethod
def sanitize(val):
valid_chars = "_%s%s" % (string.ascii_letters, string.digits)
return ''.join(c for c in val if c in valid_chars)
def insert(self):
if self.runner not in self.container_mapping.keys():
return False
self.name = self.sanitize(self.name)
if not self.test_uid:
self.test_uid = str(uuid4())
if "influx.port" not in self.params.keys():
self.params["influx.port"] = "{{secret.influx_port}}"
if "influx.host" not in self.params.keys():
self.params["influx.host"] = "{{secret.influx_ip}}"
if "galloper_url" not in self.env_vars.keys():
self.params["galloper_url"] = "{{secret.galloper_url}}"
if "influx.db" not in self.params.keys():
self.params["influx.db"] = self.container_mapping[
self.runner]['influx_db']
if "test_name" not in self.params.keys():
self.params["test_name"] = self.name # TODO: add sanitization
if "comparison_db" not in self.params.keys():
self.params["comparison_db"] = 'comparison'
if "loki_host" not in self.env_vars.keys():
self.params["loki_host"] = "{{secret.loki_host}}"
if "loki_port" not in self.env_vars.keys():
self.params["loki_port"] = "{{secret.loki_port}}"
self.job_type = self.container_mapping[self.runner]['job_type']
test_type = "test.type" if self.job_type == "perfmeter" else "test_type"
if test_type not in self.params.keys():
self.params[test_type] = 'default'
self.runner = self.container_mapping[self.runner][
'container'] # here because influx_db
super().insert()
def configure_execution_json(self,
output='cc',
test_type=None,
params=None,
env_vars=None,
reporting=None,
customization=None,
cc_env_vars=None,
parallel=None,
execution=False,
emails=None):
pairs = {
"customization": [customization, self.customization],
"params": [params, self.params],
"env_vars": [env_vars, self.env_vars],
"cc_env_vars": [cc_env_vars, self.cc_env_vars],
"reporting": [reporting, self.reporting]
}
for pair in pairs.keys():
if not pairs[pair][0]:
pairs[pair][0] = pairs[pair][1]
else:
for each in list(pairs[pair][0].keys()) + list(
set(pairs[pair][1].keys()) -
set(pairs[pair][0].keys())):
pairs[pair][0][each] = pairs[pair][0][each] if each in list(pairs[pair][0].keys()) \
else pairs[pair][1][each]
cmd = ''
if not params:
params = self.params
if self.job_type == 'perfmeter':
entrypoint = self.entrypoint if path.exists(
self.entrypoint) else path.join('/mnt/jmeter', self.entrypoint)
cmd = f"-n -t {entrypoint}"
for key, value in params.items():
if test_type and key == "test.type":
cmd += f" -Jtest.type={test_type}"
else:
cmd += f" -J{key}={value}"
execution_json = {
"container": self.runner,
"execution_params": {
"cmd": cmd
},
"cc_env_vars": {},
"bucket": self.bucket,
"job_name": self.name,
"artifact": self.file,
"job_type": self.job_type,
"concurrency": self.parallel if not parallel else parallel
}
if self.reporting:
if "junit" in self.reporting:
execution_json["junit"] = "True"
if "quality" in self.reporting:
execution_json["quality_gate"] = "True"
if "perfreports" in self.reporting:
execution_json["save_reports"] = "True"
if "jira" in self.reporting:
execution_json["jira"] = "True"
if "email" in self.reporting:
execution_json["email"] = "True"
if "rp" in self.reporting:
execution_json["report_portal"] = "True"
if "ado" in self.reporting:
execution_json["azure_devops"] = "True"
if emails:
_emails = self.emails
for each in emails.split(","):
if each not in _emails:
_emails += f",{each}"
execution_json["email_recipients"] = _emails
else:
execution_json["email_recipients"] = self.emails
if self.env_vars:
for key, value in self.env_vars.items():
execution_json["execution_params"][key] = value
if "influxdb_host" not in execution_json["execution_params"].keys():
execution_json["execution_params"][
"influxdb_host"] = "{{secret.influx_ip}}"
if "loki_host" not in execution_json["execution_params"].keys():
execution_json["execution_params"][
"loki_host"] = "{{secret.loki_host}}"
if "loki_port" not in execution_json["execution_params"].keys():
execution_json["execution_params"]["loki_port"] = "3100"
if self.cc_env_vars:
for key, value in self.cc_env_vars.items():
execution_json["cc_env_vars"][key] = value
if "REDIS_HOST" not in execution_json["cc_env_vars"].keys():
execution_json["cc_env_vars"][
"REDIS_HOST"] = "{{secret.redis_host}}"
if "REDIS_PASSWORD" not in execution_json["cc_env_vars"].keys():
execution_json["cc_env_vars"][
"REDIS_PASSWORD"] = "******"
if "GALLOPER_WEB_HOOK" not in execution_json["cc_env_vars"].keys():
execution_json["cc_env_vars"][
"GALLOPER_WEB_HOOK"] = "{{secret.post_processor}}"
if self.customization:
for key, value in self.customization.items():
if "additional_files" not in execution_json[
"execution_params"]:
execution_json["execution_params"][
"additional_files"] = dict()
execution_json["execution_params"]["additional_files"][
key] = value
if self.git:
execution_json["git"] = self.git
if self.job_type == "perfgun":
execution_json["execution_params"]['test'] = self.entrypoint
execution_json["execution_params"]["GATLING_TEST_PARAMS"] = ""
for key, value in params.items():
execution_json["execution_params"][
"GATLING_TEST_PARAMS"] += f"-D{key}={value} "
execution_json["execution_params"] = dumps(
execution_json["execution_params"])
if execution:
execution_json = unsecret(execution_json,
project_id=self.project_id)
if output == 'cc':
return execution_json
else:
return "docker run -e project_id=%s -e galloper_url=%s -e token=%s" \
" getcarrier/control_tower:latest --test_id=%s" \
"" % (self.project_id, unsecret("{{secret.galloper_url}}", project_id=self.project_id),
unsecret("{{secret.auth_token}}", project_id=self.project_id), self.test_uid)
def to_json(self, exclude_fields: tuple = ()) -> dict:
test_param = super().to_json()
for key in exclude_fields:
if self.params.get(key):
del test_param['params'][key]
elif key in test_param.keys():
del test_param[key]
return test_param
wkb_geometry = Column(Geometry('POLYGON', 4326), index=True)
t_raster_columns = Table('raster_columns', metadata,
Column('r_table_catalog', String),
Column('r_table_schema', String),
Column('r_table_name', String),
Column('r_raster_column', String),
Column('srid', Integer), Column('scale_x', Float(53)),
Column('scale_y', Float(53)),
Column('blocksize_x', Integer),
Column('blocksize_y', Integer),
Column('same_alignment', Boolean),
Column('regular_blocking', Boolean),
Column('num_bands', Integer),
Column('pixel_types', ARRAY(Text())),
Column('nodata_values', ARRAY(Float(precision=53))),
Column('out_db', Boolean), Column('extent', Geometry),
Column('spatial_index', Boolean))
t_raster_overviews = Table('raster_overviews', metadata,
Column('o_table_catalog', String),
Column('o_table_schema', String),
Column('o_table_name', String),
Column('o_raster_column', String),
Column('r_table_catalog', String),
Column('r_table_schema', String),
Column('r_table_name', String),
Column('r_raster_column', String),
Column('overview_factor', Integer))
UniqueConstraint('account', 'id'))
account_relationship = Table(
'account_relationship', metadata,
Column('account', Integer, ForeignKey('account.id'), nullable=False),
Column('follower', Integer, ForeignKey('account.id'), nullable=False),
CheckConstraint('account != follower', name='checkRelationship'))
topic_model = Table(
'topic_model', metadata, Column('id', Integer, primary_key=True),
Column('date',
DateTime(timezone=True),
server_default=func.now(),
nullable=False),
Column('application', GUID, ForeignKey('application.id'), nullable=False),
Column('topics', ARRAY(String), nullable=False),
Column('source', String, ForeignKey('source.id'), nullable=False))
topic_iteration = Table(
'topic_iteration', metadata, Column('id', Integer, primary_key=True),
Column('topic_model',
Integer,
ForeignKey('topic_model.id'),
nullable=False),
Column('date',
DateTime(timezone=True),
server_default=func.now(),
nullable=False))
topic = Table(
'topic', metadata, Column('id', Integer, primary_key=True),
import os
from sqlalchemy import (Column, Integer, MetaData, String, Table,
create_engine, ARRAY)
from databases import Database
DATABASE_URI = os.getenv('DATABASE_URI')
engine = create_engine(DATABASE_URI)
metadata = MetaData()
homepage = Table(
'homepage',
metadata,
Column('group_id', Integer, primary_key=True),
Column('welcome_message', String(500)),
Column('about_section', String(250)),
Column('email', String(10)),
Column('phone_number', String(10)),
Column('website', String(10)),
Column('events', ARRAY(Integer)),
Column('group_name', String(10)),
)
database = Database(DATABASE_URI)
class WebsiteRecycler(db.Model, BaseColumns):
__tablename__ = "website_recycler"
def __init__(self, **kwargs):
for attr in [
'url', 'domain', 'domain_id', 'city_code', 'region_code', 'ip',
'ip_area', 'title', 'web_type'
'host_dept', 'industries', 'ai_industries', 'tags',
'code_language', 'http_status', 'http_status_list', 'category',
'reason'
]:
if kwargs.get(attr) is not None:
setattr(self, attr, kwargs[attr])
url = Column(String(254), nullable=False, unique=True, index=True) # 网站地址
domain = Column(String(100), nullable=False, index=True) # 主域名
domain_id = Column(BigInteger, nullable=False) # 主域名ID
city_code = Column(String(50)) # 一级归属地
region_code = Column(String(100)) # 二级归属地
ip = Column(String(50), nullable=False, index=True) # IP地址
ip_area = Column(String(254), server_default='') # IP归属地
title = Column(String(254), server_default='') # 标题
web_type = Column(String(30),
server_default='',
nullable=False,
index=True) # 网站类型
host_dept = Column(String(254), server_default='') # 归属单位
host_type = Column(String(50), server_default='') # 单位性质
industries = Column(ARRAY(String), server_default='{}', index=True) # 行业
ai_industries = Column(ARRAY(String), server_default='{}',
index=True) # 智能分类行业
tags = Column(ARRAY(String), server_default='{}') # 标签
code_language = Column(String(50), server_default='') # 编程语言
http_status = Column(Integer(), server_default='-1',
index=True) # http status
http_status_list = Column(String(254),
server_default='') # http status list
category = Column(String(50), server_default='', index=True) # 类别
reason = Column(String(254), server_default='') # 删除原因
def as_dict(self, fields=None):
detail = {
"id": self.id,
"create_time": self.create_time.strftime("%Y-%m-%d %H:%M:%S"),
"update_time": self.update_time.strftime("%Y-%m-%d %H:%M:%S"),
"url": self.url,
"city_code": self.city_code,
"region_code": self.region_code,
"domain_id": self.domain_id,
"domain": self.domain,
"ip": self.ip,
"ip_area": self.ip_area,
"title": self.title,
"web_type": self.web_type,
"host_dept": self.host_dept,
"host_type": self.host_type,
"industries": self.industries,
"ai_industries": self.ai_industries,
"tags": self.tags,
"code_language": self.code_language,
"http_status": self.http_status,
"http_status_list": self.http_status_list,
"category": self.category
}
if not fields:
return detail
return {attr: detail[attr] for attr in fields if attr in detail}
from sqlalchemy import Table, Column, Integer, String, Boolean, ARRAY
from sqlalchemy.dialects.postgresql import JSONB
import sqlalchemy as sa
METADATA = sa.MetaData()
services = Table('services', METADATA,
Column('name', String(40), unique=True, primary_key=True),
Column('protocols', JSONB),
Column('squad', String, nullable=True), Column('meta', JSONB))
endpoints = Table(
'endpoints', METADATA,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('path', String, unique=True, index=True),
Column('service', String, index=True), Column('methods', ARRAY(String)),
Column('deprecated', Boolean, server_default=sa.text('false')),
Column('locked', Boolean, server_default=sa.text('true')),
Column('new_service', String, nullable=True),
Column('toggle', String, nullable=True))
class Venue(db.Model):
__tablename__ = 'Venue'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
city = db.Column(db.String(120))
state = db.Column(db.String(120))
address = db.Column(db.String(120))
phone = db.Column(db.String(120))
image_link = db.Column(db.String(500))
facebook_link = db.Column(db.String(120))
description = db.Column(db.String(500), default='')
seeking_talent = db.Column(Boolean, default=False)
website = db.Column(String(120))
genres = db.Column(ARRAY(String))
shows = db.relationship('Show', backref='Venue', lazy='dynamic')
def __init__(self,
name,
genres,
address,
city,
state,
phone,
website,
facebook_link,
image_link,
seeking_talent=False,
description=""):
self.name = name
self.genres = genres
self.city = city
self.state = state
self.address = address
self.phone = phone
self.image_link = image_link
self.facebook_link = facebook_link
self.website = website
self.description = description
def insert(self):
db.session.add(self)
db.session.commit()
def update(self):
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def short(self):
return {
'id': self.id,
'name': self.name,
}
def long(self):
print(self)
return {
'id': self.id,
'name': self.name,
'city': self.city,
'state': self.state,
}
def detail(self):
return {
'id': self.id,
'name': self.name,
'genres': self.genres,
'address': self.address,
'city': self.city,
'phone': self.phone,
'website': self.website,
'facebook_link': self.facebook_link,
'seeking_talent': self.seeking_talent,
'description': self.description,
'image-link': self.image_link
}
# Table of Hermes users - eg top level product users
# Used to filter comm nodes by 'owner' of the data
users = Table(
"users", metadata,
Column('id', UUID(as_uuid=True), primary_key=True, unique=True),
Column("email", String(length=100), unique=True, nullable=False),
Column("name", String(length=100)),
Column("permission_level", Enum(PermissionLevel), nullable=False),
Column("last_fetch", DateTime),
Column("token", String()),
Column("refresh_token", String()),
Column("token_uri", String()),
Column("client_id", String()),
Column("client_secret", String()),
Column("scopes", ARRAY(String()))
)
class User:
'''
User Object
Methods:
@staticmethod
_encrypt_password(input_pass: str, salt: str)
takes an input password and a salt to hash the password
@staticmethod
check_password(input_pass: str, hashed_pass: str, salt: str)
checks whether the hashed input password
def ensure_table(sqla_metadata, tablelike_schema, table_name):
"""
create a SQLAlchemy Table object bound to the
input metadata object, or returns an existing table
if it's already been registered with sqla_metadata
(it is safe to call multiple times).
The SQLA Table will have the given table_name
and columns appropriate to the schema's attributes.
Note that 'array' attributes will be postgres 'array'
columns in the Table.
Uses 'Text' columns, not 'String' b/c postgres docs say
there is no difference in effiency between varchar and
text columns, varchars are just to enforce contraints
(which we don't care about for categorical and jsonblob
types).
"""
if table_name in sqla_metadata.tables:
tbl = sqla_metadata.tables[table_name]
else:
columns = list()
id_col = Column('id', Integer, primary_key=True)
columns.append(id_col)
owner_id_col = Column('owner_id', Integer)
columns.append(owner_id_col)
for att in tablelike_schema.numeric_attributes:
nm = att.get_name()
col_type = Numeric(precision=12, scale=6)
#TODO: think through 'nullable'
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.categoric_attributes:
nm = att.get_name()
col_type = Text()
#removing use of 'Enum' columns b/c you can't
#reuse the name (of the column) between tables
#if att.valid_values is None:
# col_type = Text()
#else:
# enum_name = nm + '_enum'
# col_type = Enum(*att.valid_values, name=enum_name)
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.foreignid_attributes:
nm = att.get_name()
col_type = Integer()
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.boolean_attributes:
nm = att.get_name()
col_type = Boolean()
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.json_attributes:
nm = att.get_name()
col_type = Text()
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.int_attributes:
nm = att.get_name()
col_type = Integer()
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.numeric_list_attributes:
nm = att.get_name()
col_type = ARRAY(Numeric(precision=12, scale=6))
col = Column(nm, col_type)
columns.append(col)
#not using enums in the list for now b/c of this issue:
#http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#using-enum-with-array
for att in tablelike_schema.categoric_list_attributes:
nm = att.get_name()
#TODO: at least make the length as long as the longest possible value
col_type = ARRAY(Text())
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.foreignid_list_attributes:
nm = att.get_name()
col_type = ARRAY(Integer())
col = Column(nm, col_type)
columns.append(col)
for att in tablelike_schema.int_list_attributes:
nm = att.get_name()
col_type = ARRAY(Integer())
col = Column(nm, col_type)
columns.append(col)
#TODO: put the columns back in order that they were
#declared in the schema (follow the developer's intent)
#this is smart enough to return the existing table in
#sqla_metadata if it already has been declared
#http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table
tbl = Table(table_name, sqla_metadata, *columns)
#add the indexes declared in the schema
for list_of_column_names in tablelike_schema.indexes:
columns = list()
for cname in list_of_column_names:
columns.append(tbl.c[cname])
#we don't really care about the name, but sqlalchemy requires
#one (postgres does not)
index_name = 'nestidx_' + '_'.join(list_of_column_names)
Index(index_name, *columns)
return tbl
class HMDBData(EntityBase):
__tablename__ = 'hmdb_data'
# Primary Ids
hmdb_id = Column(String(20), primary_key=True)
# Reference Ids
kegg_id = Column(
String(24)) #ForeignKey('kegg_data.kegg_id', ondelete='SET NULL'))
pubchem_id = Column(String(
24)) #ForeignKey('pubchem_data.pubchem_id'), ondelete='SET NULL')
chebi_id = Column(
String(24)) #ForeignKey('chebi_data.chebi_id'), ondelete='SET NULL')
cas_id = Column(String(24))
ref_etc = Column(JSON_GEN()) # Extra ref Refs
chemspider_id = Column(String(24))
metlin_id = Column(String(24))
pubchem_sub_id = Column(String(24))
wiki_id = Column(String(256))
drugbank_id = Column(String(24))
pdb_id = Column(String(24))
pubmed_id = Column(String(24))
# Shared metadata
names = Column(ARRAY(Text))
description = Column(Text)
avg_mol_weight = Column(Float)
monoisotopic_mol_weight = Column(Float)
# Structure
formula = Column(Text)
smiles = Column(Text)
inchi = Column(Text)
inchikey = Column(Text)
# Other Fun Facts
state = Column(String(32))
# biofluid_locations = Column(ARRAY(String(64)))
# tissue_locations = Column(ARRAY(String(64)))
#
# # Complex data
# taxonomy = Column(JSON_GEN)
# ontology = Column(JSON_GEN)
# proteins = Column(JSON_GEN)
# diseases = Column(JSON_GEN)
# synthesis_reference = Column(TEXT)
def __init__(self, **kwargs):
self.hmdb_id = kwargs.get('hmdb_id')
self.hmdb_id_alt = kwargs.get('hmdb_id_alt')
self.kegg_id = kwargs.get('kegg_id')
self.pubchem_id = kwargs.get('pubchem_id')
self.chebi_id = kwargs.get('chebi_id')
self.cas_id = kwargs.get('cas_id')
self.ref_etc = kwargs.get('ref_etc')
self.chemspider_id = kwargs.get('chemspider_id')
self.metlin_id = kwargs.get('metlin_id')
self.pubchem_sub_id = kwargs.get('pubchem_sub_id')
self.wiki_id = kwargs.get('wiki_id')
self.drugbank_id = kwargs.get('drugbank_id')
self.pdb_id = kwargs.get('pdb_id')
self.pubmed_id = kwargs.get('pubmed_id')
self.names = kwargs.get('names')
self.description = kwargs.get('description')
self.avg_mol_weight = kwargs.get('avg_mol_weight')
self.monoisotopic_mol_weight = kwargs.get('monoisotopic_mol_weight')
self.formula = kwargs.get('formula')
self.smiles = kwargs.get('smiles')
self.inchi = kwargs.get('inchi')
self.inchikey = kwargs.get('inchikey')
self.state = kwargs.get('state')
if isinstance(self.avg_mol_weight, str):
if not self.avg_mol_weight:
self.avg_mol_weight = None
else:
self.avg_mol_weight = str(self.avg_mol_weight)
if isinstance(self.monoisotopic_mol_weight, str):
if not self.monoisotopic_mol_weight:
self.monoisotopic_mol_weight = None
else:
self.monoisotopic_mol_weight = str(
self.monoisotopic_mol_weight)
class Artist(db.Model):
"""Holds data for Artist"""
__tablename__ = 'artist'
id = Column(Integer, primary_key=True)
name = Column(String)
city = Column(String(120))
state = Column(String(120))
phone = Column(String(120))
genres = Column(ARRAY(String))
image_link = Column(String(500))
facebook_link = Column(String(120))
website = Column(String(120))
seeking_venue = Column(Boolean, default=False)
seeking_description = Column(String(120))
shows = db.relationship('Show', backref='artist', lazy=True)
def __init__(self,
name,
city,
state,
phone,
genres,
image_link,
facebook_link,
website,
seeking_description="",
seeking_venue=False):
"""__init__ for Artist Class
Values stored represent the attributes of an Artist
Parameters
----------
name : String
name of venue
city : String
name of city
state : String
name of state
phone : String
phone number of venue
genres : list (String)
list of genres for venue
image_link : String
image link for venue
facebook_link : String
facebook link for venue
website : String
website link for venue
seeking_venue : Boolean
seeking venue (yes/no)
seeking_description : String
venue description
"""
self.name = name
self.city = city
self.state = state
self.phone = phone
self.genres = genres
self.image_link = image_link
self.facebook_link = facebook_link
self.website = website
self.seeking_description = seeking_description
self.seeking_venue = seeking_venue
def title(self):
return {'id': self.id, 'name': self.name}
def detail(self):
return {
'id': self.id,
'name': self.name,
'genres': self.genres,
'city': self.city,
'state': self.state,
'phone': self.phone,
'website': self.website,
'facebook_link': self.facebook_link,
'seeking_venue': self.seeking_venue,
'seeking_description': self.seeking_description,
'image_link': self.image_link
}
class Artist(db.Model):
__tablename__ = "Artist"
id = Column(Integer, primary_key=True)
name = Column(String)
genres = Column(ARRAY(String))
city = Column(String(120))
state = Column(String(120))
phone = Column(String(120))
website = Column(String(120))
facebook_link = Column(String(120))
seeking_venue = Column(Boolean)
seeking_description = Column(String(500))
image_link = Column(String(500))
shows = db.relationship("Show", backref="Artist", lazy="dynamic")
def __init__(
self,
name,
genres,
city,
state,
phone,
image_link,
website,
facebook_link,
seeking_venue=False,
seeking_description="",
):
self.name = name
self.genres = genres
self.city = city
self.state = state
self.phone = phone
self.website = website
self.facebook_link = facebook_link
self.seeking_venue = seeking_venue
self.seeking_description = seeking_description
self.image_link = image_link
def insert(self):
db.session.add(self)
db.session.commit()
def update(self):
db.session.commit()
def short(self):
return {"id": self.id, "name": self.name}
def details(self):
return {
"id": self.id,
"name": self.name,
"genres": self.genres,
"city": self.city,
"state": self.state,
"phone": self.phone,
"website": self.website,
"facebook_link": self.facebook_link,
"seeking_venue": self.seeking_venue,
"seeking_description": self.seeking_description,
"image_link": self.image_link,
}
class Citizen(db.Model):
""" Один житель """
__tablename__ = 'citizens'
__table_args__ = (
# Every citizen_id is unique within an import
UniqueConstraint('import_id', 'citizen_id'), )
id = Column(Integer,
primary_key=True,
doc="Уникальный номер человека в нашей системе")
import_id = Column(Integer,
ForeignKey(Import.import_id),
nullable=False,
doc="Номер выгрузки")
import_rel = relationship(Import, back_populates='citizens')
# Incoming data
citizen_id = Column(
Integer,
nullable=False,
doc=
"Уникальный идентификатор жителя (уникальный в пределах одной выгрузки)"
)
town = Column(String, nullable=False, doc="Название города")
street = Column(String, nullable=False, doc="Название улицы")
building = Column(String,
nullable=False,
doc="Номер дома, корпус и строение")
apartment = Column(Integer, nullable=False, doc="Номер квартиры")
name = Column(String, nullable=False, doc="Непустая строка")
birth_date = Column(Date,
nullable=False,
doc="Дата рождения в формате (UTC)")
gender = Column(Enum(Gender), nullable=False, doc="Пол")
_relatives = Column(
MutableList.as_mutable(ARRAY(Integer)),
nullable=False,
default=list, # list of ids
doc=
"Ближайшие родственники, уникальные значения существующих citizen_id жителей из этой же выгрузки."
)
def __init__(self, relatives=(), **fields):
""" Init method that supports `relatives` """
super().__init__(**fields)
self._relatives = relatives
def __json__(self):
""" Representation in JSON """
return {
'citizen_id': self.citizen_id,
'town': self.town,
'street': self.street,
'building': self.building,
'apartment': self.apartment,
'name': self.name,
'birth_date': self.birth_date,
'gender': self.gender,
'relatives': self.relatives,
}
def get_age(self, today: date.today()):
""" Get age in years """
age = today.year - self.birth_date.year
if today.month < self.birth_date.month:
age -= 1
elif today.month == self.birth_date.month and today.day < self.birth_date.day:
age -= 1
return age
@property
def relatives(self):
""" Get the list of relatives """
return self._relatives
@relatives.setter
def relatives(self, relatives):
""" Change the value of `relatives`
This method would also update the lists of related citizens.
Make sure to do `db.session.commit()` to save those changes to the DB
"""
# Detect modifications: added, removed
current_relatives = set(self._relatives)
updated_relatives = set(relatives)
# List of added, list of removed ids
added_ids = updated_relatives - current_relatives
removed_ids = current_relatives - updated_relatives
if 0 and 'debug section':
print(f'citizen_id: {self.citizen_id}')
print(f' current_relatives: {current_relatives}')
print(f' updated_relatives: {updated_relatives}')
print(f' added_ids: {added_ids}')
print(f' removed_ids: {removed_ids}')
# Get the Session from ourselves
ssn = object_session(self)
# Find those added people and modify them
added = ssn.query(Citizen).filter(
# Find people in the same import
Citizen.import_id == self.import_id,
# Query by id
Citizen.citizen_id.in_(added_ids))
for citizen in added:
citizen._relatives.append(self.citizen_id)
citizen._relatives = list(citizen._relatives)
ssn.add(citizen)
# Find those removed people and modify them
removed = ssn.query(Citizen).filter(
Citizen.import_id == self.import_id,
Citizen.citizen_id.in_(removed_ids))
for citizen in removed:
citizen._relatives.remove(self.citizen_id)
citizen._relatives = list(citizen._relatives)
ssn.add(citizen)
# Finally, update ourselves
self._relatives = list(updated_relatives)
return self.relatives
# Validators
@validates('citizen_id')
def validate_citizen_id(self, _key, value):
return validate_positive_number(value)
@validates('apartment')
def validate_apartment(self, _key, value):
return validate_positive_number(value)
@validates('town')
def validate_town(self, _key, value):
return validate_nullable_alphanumeric(value)
@validates('street')
def validate_street(self, _key, value):
return validate_nullable_alphanumeric(value)
@validates('building')
def validate_building(self, _key, value):
return validate_nullable_alphanumeric(value)
@validates('name')
def validate_name(self, _key, value):
assert isinstance(value, str) and value
return value
@validates('birth_date')
def validate_birth_date(self, _key, value):
# Convert date string to `date` object
if isinstance(value, str):
try:
day, month, year = map(int, value.split('.'))
value = date(year, month, day)
except ValueError as e:
raise AssertionError('Invalid date format') from e
return value
@validates('gender')
def validate_gender(self, _key, value):
assert value == Gender.male.name or value == Gender.female.name
return value
@validates('_relatives')
def _validate_relatives(self, _key, values):
# List of int
assert all(isinstance(v, int) for v in values)
return values
class Offer(PcObject, Model, ExtraDataMixin, DeactivableMixin,
ProvidableMixin):
__tablename__ = "offer"
id = Column(BigInteger, primary_key=True, autoincrement=True)
productId = Column(BigInteger,
ForeignKey("product.id"),
index=True,
nullable=False)
product = relationship("Product",
foreign_keys=[productId],
backref="offers")
venueId = Column(BigInteger,
ForeignKey("venue.id"),
nullable=False,
index=True)
venue = relationship("Venue", foreign_keys=[venueId], backref="offers")
bookingEmail = Column(String(120), nullable=True)
name = Column(String(140), nullable=False)
Index("idx_offer_trgm_name", name, postgresql_using="gin")
description = Column(Text, nullable=True)
withdrawalDetails = Column(Text, nullable=True)
conditions = Column(String(120), nullable=True)
ageMin = Column(Integer, nullable=True)
ageMax = Column(Integer, nullable=True)
url = Column(String(255), nullable=True)
mediaUrls = Column(ARRAY(String(220)), nullable=False, default=[])
durationMinutes = Column(Integer, nullable=True)
isNational = Column(Boolean, default=False, nullable=False)
isDuo = Column(Boolean,
server_default=false(),
default=False,
nullable=False)
dateCreated = Column(DateTime, nullable=False, default=datetime.utcnow)
criteria = relationship("Criterion",
backref=db.backref("criteria", lazy="dynamic"),
secondary="offer_criterion")
audioDisabilityCompliant = Column(Boolean, nullable=True)
mentalDisabilityCompliant = Column(Boolean, nullable=True)
motorDisabilityCompliant = Column(Boolean, nullable=True)
visualDisabilityCompliant = Column(Boolean, nullable=True)
externalTicketOfficeUrl = Column(String, nullable=True)
lastValidationDate = Column(DateTime, index=True, nullable=True)
validation = Column(
Enum(OfferValidationStatus),
nullable=False,
default=OfferValidationStatus.APPROVED,
# changing the server_default will cost an UPDATE migration on all existing null rows
server_default="APPROVED",
index=True,
)
authorId = Column(BigInteger, ForeignKey("user.id"), nullable=True)
author = relationship("User", foreign_keys=[authorId], backref="offers")
rankingWeight = Column(Integer, nullable=True)
# This field will replace the idAtProviders coming from ProvidableMixin
idAtProvider = Column(
Text,
CheckConstraint(
'"lastProviderId" IS NULL OR "idAtProvider" IS NOT NULL',
name="check_providable_with_provider_has_idatprovider",
),
nullable=True,
)
isEducational = Column(Boolean,
server_default=false(),
default=False,
nullable=False)
subcategoryId = Column(Text, nullable=False, index=True)
dateUpdated: datetime = Column(DateTime,
nullable=True,
default=datetime.utcnow,
onupdate=datetime.utcnow)
# FIXME: We shoud be able to remove the index on `venueId`, since this composite index
# can be used by PostgreSQL when filtering on the `venueId` column only.
Index("venueId_idAtProvider_index", venueId, idAtProvider, unique=True)
@hybrid_property
def isSoldOut(self):
for stock in self.stocks:
if (not stock.isSoftDeleted
and (stock.beginningDatetime is None
or stock.beginningDatetime > datetime.utcnow())
and (stock.remainingQuantity == "unlimited"
or stock.remainingQuantity > 0)):
return False
return True
@isSoldOut.expression
def isSoldOut(cls): # pylint: disable=no-self-argument
return (~exists().where(Stock.offerId == cls.id).where(
Stock.isSoftDeleted.is_(False)).where(
or_(Stock.beginningDatetime > func.now(),
Stock.beginningDatetime.is_(None))).where(
or_(Stock.remainingQuantity.is_(None),
Stock.remainingQuantity > 0)))
@property
def activeMediation(self) -> Optional[Mediation]:
sorted_by_date_desc = sorted(self.mediations,
key=lambda m: m.dateCreated,
reverse=True)
only_active = list(filter(lambda m: m.isActive, sorted_by_date_desc))
return only_active[0] if only_active else None
@hybrid_property
def canExpire(self) -> bool:
return self.subcategoryId in subcategories.EXPIRABLE_SUBCATEGORIES
@canExpire.expression
def canExpire(cls) -> bool: # pylint: disable=no-self-argument
return cls.subcategoryId.in_(subcategories.EXPIRABLE_SUBCATEGORIES)
@property
def isReleased(self) -> bool:
return (self.isActive
and self.validation == OfferValidationStatus.APPROVED
and self.venue.isValidated
and self.venue.managingOfferer.isActive
and self.venue.managingOfferer.isValidated)
@hybrid_property
def isPermanent(self) -> bool:
return self.subcategoryId in subcategories.PERMANENT_SUBCATEGORIES
@isPermanent.expression
def isPermanent(cls) -> bool: # pylint: disable=no-self-argument
return cls.subcategoryId.in_(subcategories.PERMANENT_SUBCATEGORIES)
@property
def dateRange(self) -> DateTimes:
if self.isThing or not self.activeStocks:
return DateTimes()
start = min([stock.beginningDatetime for stock in self.activeStocks])
end = max([stock.beginningDatetime for stock in self.activeStocks])
return DateTimes(start, end)
@property
def isEvent(self) -> bool:
return self.subcategory.is_event
@property
def isThing(self) -> bool:
return not self.subcategory.is_event
@property
def isDigital(self) -> bool:
return self.url is not None and self.url != ""
@property
def isEditable(self) -> bool:
"""This property is used by the pro frontend, to display the Edit button in the Offers list"""
if not self.isFromProvider:
return True
return self.isFromAllocine
@property
def isFromProvider(self) -> bool:
return self.lastProviderId is not None
@property
def isFromAllocine(self) -> bool:
return self.isFromProvider and self.lastProvider.isAllocine
@property
def isBookable(self) -> bool:
for stock in self.stocks:
if stock.isBookable:
return True
return False
is_eligible_for_search = isBookable
@hybrid_property
def hasBookingLimitDatetimesPassed(self) -> bool:
if self.activeStocks:
return all(stock.hasBookingLimitDatetimePassed
for stock in self.activeStocks)
return False
@hasBookingLimitDatetimesPassed.expression
def hasBookingLimitDatetimesPassed(cls): # pylint: disable=no-self-argument
return and_(
exists().where(Stock.offerId == cls.id).where(
Stock.isSoftDeleted.is_(False)),
~exists().where(Stock.offerId == cls.id).where(
Stock.isSoftDeleted.is_(False)).where(
Stock.hasBookingLimitDatetimePassed.is_(False)),
)
@property
def activeStocks(self) -> list[Stock]:
return [stock for stock in self.stocks if not stock.isSoftDeleted]
@property
def bookableStocks(self) -> list[Stock]:
return [stock for stock in self.stocks if stock.isBookable]
# TODO(fseguin, 2021-10-14: remove after force updating native app to 156+)
@property
def offer_category_name_for_app(self) -> str:
return CATEGORIES_LABEL_DICT.get(self.subcategory.app_label)
@property
def subcategory(self) -> subcategories.Subcategory:
if self.subcategoryId not in subcategories.ALL_SUBCATEGORIES_DICT:
raise ValueError(
f"Unexpected subcategoryId '{self.subcategoryId}' for offer {self.id}"
)
return subcategories.ALL_SUBCATEGORIES_DICT[self.subcategoryId]
@property
def category_type(self) -> str:
if self.isEvent:
return CategoryType.EVENT.value
return CategoryType.THING.value
@property
def image(self) -> Optional[OfferImage]:
activeMediation = self.activeMediation
if activeMediation:
url = activeMediation.thumbUrl
if url:
return OfferImage(url, activeMediation.credit)
productUrl = self.product.thumbUrl if self.product else None
if productUrl:
return OfferImage(productUrl, credit=None)
return None
@property
def thumbUrl(self) -> str:
image = self.image
return image.url if image else None
@property
def is_offline_only(self) -> bool:
return self.subcategory.online_offline_platform == subcategories.OnlineOfflinePlatformChoices.OFFLINE.value
@hybrid_property
def status(self) -> OfferStatus:
# pylint: disable=too-many-return-statements
if self.validation == OfferValidationStatus.REJECTED:
return OfferStatus.REJECTED
if self.validation == OfferValidationStatus.PENDING:
return OfferStatus.PENDING
if self.validation == OfferValidationStatus.DRAFT:
return OfferStatus.DRAFT
if not self.isActive:
return OfferStatus.INACTIVE
if self.validation == OfferValidationStatus.APPROVED:
if self.hasBookingLimitDatetimesPassed: # pylint: disable=using-constant-test
return OfferStatus.EXPIRED
if self.isSoldOut: # pylint: disable=using-constant-test
return OfferStatus.SOLD_OUT
return OfferStatus.ACTIVE
@status.expression
def status(cls): # pylint: disable=no-self-argument
return case(
[
(cls.validation == OfferValidationStatus.REJECTED.name,
OfferStatus.REJECTED.name),
(cls.validation == OfferValidationStatus.PENDING.name,
OfferStatus.PENDING.name),
(cls.validation
== OfferValidationStatus.DRAFT.name, OfferStatus.DRAFT.name),
(cls.isActive.is_(False), OfferStatus.INACTIVE.name),
(cls.hasBookingLimitDatetimesPassed.is_(True),
OfferStatus.EXPIRED.name),
(cls.isSoldOut.is_(True), OfferStatus.SOLD_OUT.name),
],
else_=OfferStatus.ACTIVE.name,
)
@property
def max_price(self) -> float:
return max(stock.price for stock in self.stocks
if not stock.isSoftDeleted)
class LipidMapsData(EntityBase):
__tablename__ = 'lipidmaps_data'
# Primary Ids
lipidmaps_id = Column(String(20), primary_key=True)
# Reference Ids
kegg_id = Column(
String(20)) # ForeignKey('kegg_data.kegg_id', ondelete='SET NULL'))
hmdb_id = Column(
String(20)) # ForeignKey('hmdb_data.hmdb_id', ondelete='SET NULL'))
chebi_id = Column(
String(20)) # ForeignKey('chebi_data.chebi_id', ondelete='SET NULL'))
pubchem_id = Column(String(
20)) # ForeignKey('pubchem_data.pubchem_id', ondelete='SET NULL'))
lipidbank_id = Column(String(20))
cas_id = Column(String(20))
ref_etc = Column(JSON_GEN()) # Extra ref Refs
chemspider_id = Column(String(24))
metlin_id = Column(String(24))
pubchem_sub_id = Column(String(24))
wiki_id = Column(String(256))
drugbank_id = Column(String(24))
pdb_id = Column(String(24))
pubmed_id = Column(String(24))
# Shared metadata
names = Column(ARRAY(Text))
mass = Column(Float)
# Structure
smiles = Column(Text)
inchi = Column(Text)
inchikey = Column(Text)
formula = Column(Text)
# Other Fun Facts
category = Column(String(32))
main_class = Column(String(64))
sub_class = Column(String(128))
lvl4_class = Column(String(128))
def __init__(self, **kwargs):
self.lipidmaps_id = kwargs.get('lipidmaps_id')
self.kegg_id = kwargs.get('kegg_id')
self.hmdb_id = kwargs.get('hmdb_id')
self.chebi_id = kwargs.get('chebi_id')
self.pubchem_id = kwargs.get('pubchem_id')
self.lipidbank_id = kwargs.get('lipidbank_id')
self.cas_id = kwargs.get('cas_id')
self.ref_etc = kwargs.get('ref_etc')
self.chemspider_id = kwargs.get('chemspider_id')
self.metlin_id = kwargs.get('metlin_id')
self.pubchem_sub_id = kwargs.get('pubchem_sub_id')
self.wiki_id = kwargs.get('wiki_id')
self.drugbank_id = kwargs.get('drugbank_id')
self.pdb_id = kwargs.get('pdb_id')
self.pubmed_id = kwargs.get('pubmed_id')
self.names = kwargs.get('names')
self.mass = kwargs.get('mass')
self.smiles = kwargs.get('smiles')
self.inchi = kwargs.get('inchi')
self.inchikey = kwargs.get('inchikey')
self.formula = kwargs.get('formula')
self.category = kwargs.get('category')
self.main_class = kwargs.get('main_class')
self.sub_class = kwargs.get('sub_class')
self.lvl4_class = kwargs.get('lvl4_class')
if isinstance(self.mass, str):
if not self.mass:
self.mass = None
else:
self.mass = float(self.mass)
class Courier(Base, BaseDbModel):
__tablename__ = "couriers"
id = Column(Integer, primary_key=True)
courier_type = Column(Enum(CourierType))
regions = Column(ARRAY(Integer))
working_hours = Column(ARRAY(String))
working_hours_timedeltas = Column(ARRAY(JSON))
orders = relationship("Order", backref="courier")
rating = Column(FLOAT, nullable=True)
earnings = Column(Integer, default=0)
last_delivery_time = Column(FLOAT, nullable=True)
delivery_data = Column(JSON, nullable=True)
def get_capacity(self):
return {
CourierType.FOOT: 10,
CourierType.BIKE: 15,
CourierType.CAR: 50,
}[self.courier_type]
def get_coefficient(self):
return {
CourierType.FOOT: 2,
CourierType.BIKE: 5,
CourierType.CAR: 9,
}[self.courier_type]
@classmethod
async def create_couriers(
cls, session: AsyncSession, json_data: dict
) -> Tuple[Optional[List[Union["Courier", int]]], Optional[List[int]]]:
return await cls.create(session=session,
json_data=json_data,
id_key="courier_id")
@classmethod
async def get_one(cls, session: AsyncSession,
_id: int) -> Optional["Courier"]:
result = (await session.execute(
select(cls).where(cls.id == _id).options(selectinload(cls.orders))
)).first()
return result[0] if result is not None else result
@classmethod
async def get_courier(cls, session: AsyncSession,
courier_id: int) -> Optional["Courier"]:
return await cls.get_one(session=session, _id=courier_id)
@classmethod
async def get_all_data_courier(cls, session: AsyncSession,
courier_id: int) -> Optional["Courier"]:
base_courier = await cls.get_courier(session=session,
courier_id=courier_id)
if base_courier is None:
return base_courier
average_values = []
if base_courier.delivery_data is None:
return base_courier
for region, times in base_courier.delivery_data["regions"].items():
average_values.append(sum(times) / len(times))
if not average_values:
return base_courier
t = min(average_values)
rating = (60 * 60 - min(t, 60 * 60)) / (60 * 60) * 5
base_courier.rating = round(rating, 2)
await session.commit()
return base_courier
@classmethod
async def patch_courier(cls, session: AsyncSession, courier_id: int,
new_data: dict) -> Row:
return await cls.patch(session=session,
_id=courier_id,
new_data=new_data)
def test_array_agg_array_datatype(self):
expr = func.array_agg(column("data", ARRAY(Integer)))
is_(expr.type._type_affinity, ARRAY)
is_(expr.type.item_type._type_affinity, Integer)
class PubChemData(EntityBase):
__tablename__ = 'pubchem_data'
# Primary Ids
pubchem_id = Column(String(20), primary_key=True)
#pubchem_sub_id = Column(String(24)) #ForeignKey('pubchem_substrate_data.pubchem_id', ondelete='SET NULL'))
# Reference Ids
chebi_id = Column(
String(24)) #ForeignKey('chebi_data.chebi_id', ondelete='SET NULL'))
kegg_id = Column(
String(24)) #ForeignKey('kegg_data.kegg_id', ondelete='SET NULL'))
hmdb_id = Column(
String(24)) #ForeignKey('hmdb_data.hmdb_id', ondelete='SET NULL'))
cas_id = Column(String(24))
ref_etc = Column(JSON_GEN()) # Extra ref Refs
chemspider_id = Column(String(24))
#metlin_id = Column(String(24))
#wiki_id = Column(String(24))
#drugbank_id = Column(String(24))
#pdb_id = Column(String(24))
#pubmed_id = Column(String(24))
# Shared metadata
names = Column(ARRAY(Text))
mass = Column(Float)
monoisotopic_mass = Column(Float)
# Structure
smiles = Column(ARRAY(Text))
inchi = Column(Text)
inchikey = Column(String(27))
formula = Column(String(256))
# Other Fun Facts
logp = Column(Float)
def __init__(self, **kwargs):
self.pubchem_id = kwargs.get('pubchem_id')
self.pubchem_sub_id = kwargs.get('pubchem_sub_id')
self.chebi_id = kwargs.get('chebi_id')
self.kegg_id = kwargs.get('kegg_id')
self.hmdb_id = kwargs.get('hmdb_id')
self.cas_id = kwargs.get('cas_id')
self.ref_etc = kwargs.get('ref_etc')
self.chemspider_id = kwargs.get('chemspider_id')
self.metlin_id = kwargs.get('metlin_id')
self.wiki_id = kwargs.get('wiki_id')
self.drugbank_id = kwargs.get('drugbank_id')
self.pdb_id = kwargs.get('pdb_id')
self.pubmed_id = kwargs.get('pubmed_id')
self.names = kwargs.get('names')
self.mass = kwargs.get('mass')
self.weight = kwargs.get('weight')
self.monoisotopic_mass = kwargs.get('monoisotopic_mass')
self.smiles = kwargs.get('smiles')
self.inchi = kwargs.get('inchi')
self.inchikey = kwargs.get('inchikey')
self.formula = kwargs.get('formula')
self.logp = kwargs.get('logp')
print("TODO: pubchem handle synonyms and description")
import os
from sqlalchemy import (Column, DateTime, Integer, MetaData, String, Table,
create_engine, ARRAY)
from databases import Database
#DATABASE_URI = os.getenv('DATABASE_URI')
DATABASE_URI = 'postgresql://*****:*****@localhost/movie_db'
engine = create_engine(DATABASE_URI)
metadata = MetaData()
movies = Table('movies', metadata, Column('id', Integer, primary_key=True),
Column('name', String(50)), Column('plot', String(250)),
Column('genres', ARRAY(String)),
Column('casts_id', ARRAY(Integer)))
database = Database(DATABASE_URI)
class Movie(BaseEntity):
__tablename__ = "movie"
name = Column(String(), primary_key=True)
update_date = Column(DateTime(timezone=False), nullable=False)
reviews = Column(ARRAY(String), nullable=False)
