class Client_group_manager(Base):
__tablename__ = 'client_group_manager'
janium_client_group_manager_id = 'cf2c5ddc-eaba-4db1-adfd-03b133c2213b'
unassigned_client_group_manager_id = 'f0a50859-455a-448b-a6e0-64fb4f6c5416'
def __init__(self, client_group_manager_id, first_name, last_name):
self.client_group_manager_id = client_group_manager_id
self.first_name = first_name
self.last_name = last_name
# Primary Keys
client_group_manager_id = Column(String(36), primary_key=True, nullable=False)
# Foreign Keys
# Common Columns
first_name = Column(String(128), nullable=False)
last_name = Column(String(128), nullable=False)
full_name = Column(String(256), Computed("CONCAT(first_name, ' ', last_name)"))
# Table Metadata
asOfStartTime = Column(DateTime, server_default=func.now())
asOfEndTime = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
effective_start_date = Column(DateTime, server_default=func.now())
effective_end_date = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
updatedBy = Column(String(36), server_default=text("'45279d74-b359-49cd-bb94-d75e06ae64bc'"))
class Dte_sender(Base):
__tablename__ = 'dte_sender'
janium_dte_sender_id = '5202aea8-ab36-4e6d-9cda-5994d2c0bbe1'
unassigned_dte_sender_id = 'd07a45e1-8baa-4593-ae54-452697e7f559'
def __init__(self, dte_sender_id, email_config_id, first_name, last_name):
self.dte_sender_id = dte_sender_id
self.email_config_id = email_config_id
self.first_name = first_name
self.last_name = last_name
# Primary Keys
dte_sender_id = Column(String(36), primary_key=True)
# Foreign Keys
email_config_id = Column(String(36), ForeignKey('email_config.email_config_id'))
# Common Columns
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
full_name = Column(String(200), Computed("CONCAT(first_name, ' ', last_name)"))
# Table Metadata
asOfStartTime = Column(DateTime, server_default=func.now())
asOfEndTime = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
effective_start_date = Column(DateTime, server_default=func.now())
effective_end_date = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
updatedBy = Column(String(36), server_default=text("'45279d74-b359-49cd-bb94-d75e06ae64bc'"))
# SQLAlchemy Relationships and Backreferences
email_config = relationship('Email_config', uselist=False, lazy=True)
class Task(db.Model):
__tablename__ = 'task'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200), nullable=False, unique=True)
description = db.Column(db.Text, nullable=False)
created_at = db.Column(db.DateTime,
server_default=func.now(),
nullable=False)
created_by = db.Column(db.Integer, db.ForeignKey('user.id'))
assigned_by = db.Column(db.Integer, db.ForeignKey('user.id'))
assigned_to = db.Column(db.Integer, db.ForeignKey('user.id'))
reviewed_by = db.Column(db.Integer, db.ForeignKey('user.id'))
created_by_id = db.relationship('User', foreign_keys=[created_by])
assigned_by_id = db.relationship('User', foreign_keys=[assigned_by])
assigned_to_id = db.relationship('User', foreign_keys=[assigned_to])
reviewed_by_id = db.relationship('User', foreign_keys=[reviewed_by])
status_id = db.Column(db.Integer, db.ForeignKey('status.id'))
status = db.relationship('Status', backref='tasks')
criticality_id = db.Column(db.Integer, db.ForeignKey('criticality.id'))
criticality = db.relationship('Criticality', backref='tasks')
expected_completion_date = db.Column(
db.DateTime,
server_default=Computed(sqltext=text('created_at + INTERVAL 3 DAY'),
persisted=True),
nullable=False)
project_id = db.Column(db.Integer, db.ForeignKey('project.id'))
project = db.relationship('Project', backref='tasks')
actual_completion_date = db.Column(db.DateTime,
server_default=null(),
nullable=True)
class Client_group_manager(Base):
__tablename__ = 'client_group_manager'
dummy_client_group_manager_id = '9d2aa9ce-9078-45bf-a03f-181e5ce3d5a9'
def __init__(self, client_group_manager_id, first_name, last_name):
self.client_group_manager_id = client_group_manager_id
self.first_name = first_name
self.last_name = last_name
# Primary Keys
client_group_manager_id = Column(String(36),
primary_key=True,
nullable=False)
# Foreign Keys
# Common Columns
first_name = Column(String(128), nullable=False)
last_name = Column(String(128), nullable=False)
full_name = Column(String(256),
Computed("CONCAT(first_name, ' ', last_name)"))
# Table Metadata
asOfStartTime = Column(DateTime, server_default=func.now())
asOfEndTime = Column(DateTime,
server_default=text("'9999-12-31 10:10:10'"))
effective_start_date = Column(DateTime, server_default=func.now())
effective_end_date = Column(DateTime,
server_default=text("'9999-12-31 10:10:10'"))
updatedBy = Column(
String(36),
server_default=text("'45279d74-b359-49cd-bb94-d75e06ae64bc'"))
class Law(Base):
__tablename__ = "laws"
id = Column(Integer, primary_key=True)
doknr = Column(String, nullable=False, unique=True)
slug = Column(String, nullable=False, index=True)
gii_slug = Column(String, nullable=False, index=True)
abbreviation = Column(String, nullable=False)
extra_abbreviations = Column(postgresql.ARRAY(String), nullable=False)
first_published = Column(String, nullable=False)
source_timestamp = Column(String, nullable=False)
title_long = Column(String, nullable=False)
title_short = Column(String)
publication_info = Column(postgresql.JSONB, nullable=False)
status_info = Column(postgresql.JSONB, nullable=False)
notes_body = Column(String)
notes_footnotes = Column(String)
notes_documentary_footnotes = Column(String)
attachment_names = Column(postgresql.ARRAY(String), nullable=False)
# Search index. Cf. https://www.postgresql.org/docs/current/textsearch-controls.html
search_tsv = Column(
postgresql.TSVECTOR,
Computed("""
setweight(to_tsvector('german',
coalesce(laws.title_long, '') || ' ' ||
coalesce(laws.title_short, '') || ' ' ||
coalesce(laws.abbreviation, '')),
'A') ||
setweight(to_tsvector('german',
coalesce(laws.notes_body, '')),
'B')
"""))
contents = relationship("ContentItem",
back_populates="law",
order_by="ContentItem.order",
cascade="all, delete, delete-orphan",
passive_deletes=True)
__table_args = (Index('ix_laws_search_tsv',
search_tsv,
postgresql_using='gin'))
@staticmethod
def from_dict(law_dict, gii_slug):
law = Law(slug=slugify(law_dict["abbreviation"]),
gii_slug=gii_slug,
**{k: v
for k, v in law_dict.items() if k != "contents"})
content_item_dicts = law_dict["contents"]
content_items_by_doknr = {}
for idx, content_item_dict in enumerate(content_item_dicts):
content_item = ContentItem.from_dict(content_item_dict, idx,
content_items_by_doknr)
content_items_by_doknr[content_item.doknr] = content_item
law.contents.append(content_item)
return law
def fn(**kwargs):
m = MetaData()
Table(
"t",
m,
Column("x", Integer),
Column("y", Integer, Computed("x + 2"), **kwargs),
)
class Department(Base):
__tablename__ = "departments"
id = Column(Integer, primary_key=True)
name = Column(String(60))
attributes = Column(JSON)
employeesCount = Column(Integer,
Computed("`attributes` ->> '$.employeesCount'"),
index=True)
def define_tables(cls, metadata):
Table(
"test",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
Table(
"test_no_returning",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
implicit_returning=False,
)
def define_tables(cls, metadata):
Table(
"test",
metadata,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
class Region(Base):
__tablename__ = 'region'
id = Column(Integer, primary_key=True)
name = Column(String(255))
geometry = Column(NullType, nullable=False)
state = Column(String(255))
positional_accuracy_in_m = Column(Integer)
centroid = Column(NullType,
Computed('(st_centroid(`geometry`))', persisted=True))
surveys = relationship('T1Survey', secondary='t1_survey_region')
class Statistic(Base):
"""Main data table
TODO(zifnab): indexes + migrations
"""
__tablename__ = "stats"
device_id = Column(String, primary_key=True)
model = Column(String)
version_raw = Column(String)
country = Column(String)
carrier = Column(String)
carrier_id = Column(String)
submit_time = Column(DateTime, server_default=func.now())
if SQL_CONNECT_STRING.startswith("sqlite://"):
computed = "substr(version_raw, 0, 3)"
else:
computed = "substring(version_raw, '^\\d\\d\\.\\d')"
version = Column(String, Computed(computed))
@classmethod
def create(cls, data):
with session_scope() as session:
session.merge(
cls(
device_id=data["device_hash"],
model=data["device_name"],
version_raw=data["device_version"],
country=data["device_country"],
carrier=data["device_carrier"],
carrier_id=data["device_carrier_id"],
))
@classmethod
def get_most_popular(cls, field, days):
with session_scope() as session:
if hasattr(cls, field):
return (session.query(
getattr(cls, field),
func.count(cls.device_id).label("count")).group_by(
getattr(cls, field)).order_by(desc("count")))
@classmethod
def get_count(cls, days=90):
with session_scope() as session:
return session.query(func.count(cls.device_id))
@classmethod
def drop_old(cls, days=90):
with session_scope() as session:
limit = datetime.datetime.now() - datetime.timedelta(days=days)
session.query(cls).filter(cls.submit_time <= limit).delete()
def test_column_computed(self, persisted):
kwargs = {"persisted": persisted} if persisted != "ignore" else {}
t = Table(
"t",
self.metadata,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", **kwargs)),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE t (x INTEGER, y INTEGER GENERATED "
"ALWAYS AS (x + 2))",
)
def test_column_computed_raises(self, persisted):
t = Table(
"t",
self.metadata,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", persisted=persisted)),
)
assert_raises_message(
exc.CompileError,
"Firebird computed columns do not support a persistence method",
schema.CreateTable(t).compile,
dialect=firebird.dialect(),
)
def test_column_computed(self, text, persisted):
m = MetaData()
kwargs = {"persisted": persisted} if persisted != "ignore" else {}
t = Table(
"t",
m,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", **kwargs)),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE t (x INTEGER NULL, y AS (x + 2)%s)" % text,
)
def test_column_computed_persisted_true(self):
m = MetaData()
t = Table(
"t",
m,
Column("x", Integer),
Column("y", Integer, Computed("x + 2", persisted=True)),
)
assert_raises_message(
exc.CompileError,
r".*Oracle computed columns do not support 'stored' ",
schema.CreateTable(t).compile,
dialect=oracle.dialect(),
)
class ContentItem(Base):
__tablename__ = "content_items"
id = Column(Integer, primary_key=True)
doknr = Column(String, nullable=False, unique=True)
item_type = Column(String, nullable=False)
name = Column(String, nullable=False)
title = Column(String)
body = Column(String)
footnotes = Column(String)
documentary_footnotes = Column(String)
law_id = Column(Integer,
ForeignKey("laws.id", ondelete="CASCADE"),
index=True)
parent_id = Column(Integer, ForeignKey("content_items.id"))
order = Column(Integer, nullable=False)
# Search index. Cf. https://www.postgresql.org/docs/current/textsearch-controls.html
search_tsv = Column(
postgresql.TSVECTOR,
Computed("""
setweight(to_tsvector('german',
coalesce(content_items.name, '') || ' ' ||
coalesce(content_items.title, '')),
'A') ||
setweight(to_tsvector('german',
coalesce(content_items.body, '')),
'B')
"""))
law = relationship("Law", back_populates="contents")
parent = relationship("ContentItem", remote_side=[id], uselist=False)
__table_args = (Index('ix_content_items_search_tsv',
search_tsv,
postgresql_using='gin'))
@staticmethod
def from_dict(content_item_dict, order, content_items_by_doknr):
parent_dict = content_item_dict["parent"]
parent = parent_dict and content_items_by_doknr[parent_dict["doknr"]]
content_item_attrs = {
k: v
for k, v in content_item_dict.items() if k != "parent"
}
content_item = ContentItem(parent=parent,
order=order,
**content_item_attrs)
return content_item
def test_other_options(self):
t = Table(
"t",
MetaData(),
Column("y",
Integer,
Computed("x + 2"),
nullable=False,
unique=True),
)
self.assert_compile(
CreateTable(t),
"CREATE TABLE t ("
"y INTEGER GENERATED ALWAYS AS (x + 2) NOT NULL, UNIQUE (y))",
)
class MenuItem(AppFrenzyBase):
__tablename__ = "menu_item"
__table_args__ = (
Index(
"dish_name_gin_idx", "dish_name_search_vec", postgresql_using="gin"
),
)
id = Column(Integer, primary_key=True)
restaurant = Column(Integer, ForeignKey("restaurant.id"))
dish_name = Column(String, index=True)
price = Column(Float, index=True)
dish_name_search_vec = Column(
TSVector(),
Computed("to_tsvector('english', dish_name)", persisted=True),
)
def test_returning_insert_computed(self):
m = MetaData()
t1 = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
self.assert_compile(
t1.insert().values(id=1, foo=5).returning(t1.c.bar),
"INSERT INTO t1 (id, foo) VALUES (:id, :foo) "
"RETURNING t1.bar INTO :ret_0",
)
def test_returning_update_computed_warning(self):
m = MetaData()
t1 = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
with testing.expect_warnings(
"Computed columns don't work with Oracle UPDATE"):
self.assert_compile(
t1.update().values(id=1, foo=5).returning(t1.c.bar),
"UPDATE t1 SET id=:id, foo=:foo RETURNING t1.bar INTO :ret_0",
)
class Restaurant(AppFrenzyBase):
__tablename__ = "restaurant"
__table_args__ = (
Index(
"restaurant_name_gin_idx",
"name_search_vec",
postgresql_using="gin",
),
)
id = Column(Integer, primary_key=True)
name = Column(String, index=True)
cash_balance = Column(Float)
menu = relationship("MenuItem")
timings = relationship("RestaurantTiming")
name_search_vec = Column(
TSVector(), Computed("to_tsvector('english', name)", persisted=True)
)
def test_computed_column(metadata, persisted, extra_args):
Table(
'computed', metadata,
Column('id', INTEGER, primary_key=True),
Column('computed', INTEGER, Computed('1 + 2', persisted=persisted))
)
assert generate_code(metadata, noclasses=True) == """\
# coding: utf-8
from sqlalchemy import Column, Computed, Integer, MetaData, Table
metadata = MetaData()
t_computed = Table(
'computed', metadata,
Column('id', Integer, primary_key=True),
Column('computed', Integer, Computed('1 + 2'{extra_args}))
)
""".format(extra_args=extra_args)
def test_to_metadata(self):
comp1 = Computed("x + 2")
m = MetaData()
t = Table("t", m, Column("x", Integer), Column("y", Integer, comp1))
is_(comp1.column, t.c.y)
is_(t.c.y.server_onupdate, comp1)
is_(t.c.y.server_default, comp1)
m2 = MetaData()
t2 = t.to_metadata(m2)
comp2 = t2.c.y.server_default
is_not_(comp1, comp2)
is_(comp1.column, t.c.y)
is_(t.c.y.server_onupdate, comp1)
is_(t.c.y.server_default, comp1)
is_(comp2.column, t2.c.y)
is_(t2.c.y.server_onupdate, comp2)
is_(t2.c.y.server_default, comp2)
class Video(Base):
__tablename__ = 'video'
id = Column(Integer, primary_key=True)
channel_id = Column(Integer, ForeignKey('channel.id'))
channel = relationship('Channel',
primaryjoin='Video.channel_id==Channel.id')
idempotency = Column(String)
# File paths
caption_path = Column(String)
description_path = Column(String)
ext = Column(String)
info_json_path = Column(String)
poster_path = Column(String)
video_path = Column(String)
caption = Column(String)
duration = Column(Integer)
favorite = Column(DateTime)
size = Column(Integer)
source_id = Column(String)
title = Column(String)
upload_date = Column(DateTime)
validated_poster = Column(Boolean, default=False)
viewed = Column(DateTime)
textsearch = Column(
tsvector,
Computed('''to_tsvector('english'::regconfig,
((COALESCE(title, ''::text) || ' '::text) ||
COALESCE(caption, ''::text)))'''
))
def __repr__(self):
return f'<Video(id={self.id}, title={self.title}, path={self.video_path}, channel={self.channel_id})>'
def dict(self):
d = super().dict()
if self.channel_id:
d['channel'] = self.channel.dict()
return d
class User(Base):
__tablename__ = 'user'
unassigned_user_id = '9d34bb21-7037-4709-bb0f-e1e8b1491506'
system_user_id = 'a0bcc7a2-5e2b-41c6-9d5c-ba8ebb01c03d'
def __init__(self, user_id, first_name, title, company, location,
primary_email, campaign_management_email, alternate_dte_email,
phone):
self.user_id = user_id
self.first_name = first_name
self.last_name = last_name
self.title = title
self.company = company
self.location = location
self.primary_email = primary_email
self.campaign_management_email = campaign_management_email
self.alternate_dte_email = alternate_dte_email
self.phone = phone
user_id = Column(String(36), primary_key=True)
first_name = Column(String(126), nullable=False)
last_name = Column(String(126), nullable=False)
full_name = Column(String(256),
Computed("CONCAT(first_name, ' ', last_name)"))
title = Column(String(256), nullable=True)
company = Column(String(256), nullable=True)
location = Column(String(256), nullable=True)
primary_email = Column(String(256), nullable=False)
phone = Column(String(256), nullable=True)
additional_contact_info = Column(JSON, nullable=True)
asOfStartTime = Column(DateTime, server_default=text("(UTC_TIMESTAMP)"))
asOfEndTime = Column(
DateTime,
server_default=text("(DATE_ADD(UTC_TIMESTAMP, INTERVAL 5000 YEAR))"))
updated_by = Column(String(36), ForeignKey('user.user_id'), nullable=False)
class Dte_sender(Base):
__tablename__ = 'dte_sender'
dummy_dte_sender_id = '234971cf-8ae5-4cfd-8824-8d7c2f3446c7'
def __init__(self, dte_sender_id, email_config_id, first_name, last_name):
self.dte_sender_id = dte_sender_id
self.email_config_id = email_config_id
self.first_name = first_name
self.last_name = last_name
# Primary Keys
dte_sender_id = Column(String(36), primary_key=True)
# Foreign Keys
email_config_id = Column(String(36),
ForeignKey('email_config.email_config_id'))
# Common Columns
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
full_name = Column(String(200),
Computed("CONCAT(first_name, ' ', last_name)"))
# Table Metadata
asOfStartTime = Column(DateTime, server_default=func.now())
asOfEndTime = Column(DateTime,
server_default=text("'9999-12-31 10:10:10'"))
effective_start_date = Column(DateTime, server_default=func.now())
effective_end_date = Column(DateTime,
server_default=text("'9999-12-31 10:10:10'"))
updatedBy = Column(
String(36),
server_default=text("'45279d74-b359-49cd-bb94-d75e06ae64bc'"))
# SQLAlchemy Relationships and Backreferences
email_config = relationship('Email_config', uselist=False, lazy=True)
class Client(Base):
__tablename__ = 'client'
unassigned_client_id = 'a77a723d-09ea-4282-8c96-9be09937dc69'
def __init__(self, client_id, client_group_id, ulinc_config_id, email_config_id, is_active, is_sending_emails, is_sending_li_messages,
is_dte, is_assistant, first_name, last_name, title, company, location, primary_email, campaign_management_email,
alternate_dte_email, phone, assistant_first_name, assistant_last_name, assistant_email, voicemail_task_delay, is_email_forward, is_data_enrichment):
self.client_id = client_id
self.client_group_id = client_group_id
self.ulinc_config_id = ulinc_config_id
self.email_config_id = email_config_id
self.is_active = is_active
self.is_sending_emails = is_sending_emails
self.is_sending_li_messages = is_sending_li_messages
self.is_dte = is_dte
self.is_assistant = is_assistant
self.first_name = first_name
self.last_name = last_name
self.title = title
self.company = company
self.location = location
self.primary_email = primary_email
self.campaign_management_email = campaign_management_email
self.alternate_dte_email = alternate_dte_email
self.phone = phone
self.assistant_first_name = assistant_first_name
self.assistant_last_name = assistant_last_name
self.assistant_email = assistant_email
self.voicemail_task_delay = voicemail_task_delay
self.is_email_forward = is_email_forward
self.is_data_enrichment = is_data_enrichment
# Primary Keys
client_id = Column(String(36), primary_key=True)
# Foreign Keys
client_group_id = Column(String(36), ForeignKey('client_group.client_group_id'))
ulinc_config_id = Column(String(36), ForeignKey('ulinc_config.ulinc_config_id'))
email_config_id = Column(String(36), ForeignKey('email_config.email_config_id'))
# Common Columns
is_active = Column(Boolean, nullable=False, server_default=false())
is_sending_emails = Column(Boolean, nullable=False, server_default=false())
is_sending_li_messages = Column(Boolean, nullable=False, server_default=false())
is_dte = Column(Boolean, nullable=False, server_default=false())
is_assistant = Column(Boolean, nullable=False, server_default=false())
is_email_forward = Column(Boolean, nullable=False, server_default=false())
is_data_enrichment = Column(Boolean, nullable=False, server_default=false())
first_name = Column(String(126), nullable=False)
last_name = Column(String(126), nullable=False)
full_name = Column(String(256), Computed("CONCAT(first_name, ' ', last_name)"))
title = Column(String(256), nullable=True)
company = Column(String(256), nullable=True)
location = Column(String(256), nullable=True)
primary_email = Column(String(256), nullable=False)
campaign_management_email = Column(String(256), nullable=True)
alternate_dte_email = Column(String(256), nullable=True)
phone = Column(String(256), nullable=True)
voicemail_task_delay = Column(Integer, nullable=True)
assistant_first_name = Column(String(128), nullable=True)
assistant_last_name = Column(String(128), nullable=True)
assistant_full_name = Column(String(256), Computed("CONCAT(assistant_first_name, ' ', assistant_last_name)"))
assistant_email = Column(String(256), nullable=True)
# Table Metadata
asOfStartTime = Column(DateTime, server_default=func.now())
asOfEndTime = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
effective_start_date = Column(DateTime, server_default=func.now())
effective_end_date = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
updatedBy = Column(String(36), server_default=text("'45279d74-b359-49cd-bb94-d75e06ae64bc'"))
# SQLAlchemy Relationships and Backreferences
janium_campaigns = relationship('Janium_campaign', backref=backref('janium_campaign_client', uselist=False), uselist=True, lazy='dynamic')
ulinc_campaigns = relationship('Ulinc_campaign', backref=backref('ulinc_campaign_client', uselist=False), uselist=True, lazy='dynamic')
contacts = relationship('Contact', backref=backref('contact_client', uselist=False), uselist=True, lazy='dynamic')
email_config = relationship('Email_config', backref=backref('email_config_client', uselist=False), uselist=False, lazy=True)
ulinc_config = relationship('Ulinc_config', uselist=False, lazy=True)
class User(db.Model):
"""
Modello di utente della nostra app.
"""
__tablename__ = 'Users'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(30), unique=True)
password = Column(String(60))
admin = Column(Boolean)
vittorie = Column(Integer)
sconfitte = Column(Integer)
punteggio = Column(Integer,
Computed('3 * vittorie-sconfitte', persisted=False))
def __init__(self, username, password):
"""
:param username: NOT UTF-8 ENCODED
:param password: UTF-8 ENCODED
"""
self.username = username
self.password = hashpw(password, gensalt(consts.BCRYPT_SALT_ROUNDS))
self.vittorie = 0
self.sconfitte = 0
self.admin = False
def check_password(self, password):
"""
:param password: UTF-8 encoded password
:return: True if password matches hash, False if not
"""
"""
MySQL, a differenza di quanto pensi PyCharm,
restituisce una stringa decodificata come hash
della password, quindi va encode-ata per far
contento bcrypt che vuole le cose in UTF-8
"""
return checkpw(password.encode("utf-8"), self.password.encode("utf-8"))
def jsonify(self):
"""
Non è repr o str perché altrimenti non potremmo restituire un dict, e restituendo
una stringa il risultato finale nelle liste non è JSON valido. Si elimina l'hash della
password perché non siamo così poco attenti con i dati degli utenti, si elimina admin
perché per il momento non serve a niente e non ha senso esporre quel valore, e si elimina
_sa_instance_state perché è una cosa interna di SQLAlchemy.
"""
d = self.__dict__.copy()
del d["_sa_instance_state"]
del d["password"]
del d["admin"]
return d
@staticmethod
def validate_password(password: str):
"""
La password è valida se è lunga tra i 5 e i 50 caratteri e composta solo
da caratteri stampabili. Si potrebbe usare una regex in futuro ma
per il momento lo faccio a mano.
:param password: password to validate, MUST'NT BE UTF-8 ENCODED
:return: True if the password is valid, False if it isn't
"""
if password is None:
return False
password_len = len(password)
if password_len < 5 or password_len > 50:
return False
for char in password:
if not char.isprintable():
return False
return True
@staticmethod
def validate_username(username: str):
"""
Il nome utente è valido se composto solo da lettere
o numeri, e se è lungo almeno 3 caratteri e al
massimo 30
:param username: username to validate, MUST'NT BE UTF-8 ENCODED
:return: True if the username is valid, False if it isn't
"""
if username is None:
return False
username_len = len(username)
if username_len < 3 or username_len > 30:
return False
for char in username:
if not char.isalnum():
return False
return True
def increment_wins(self):
self.vittorie += 1
def increment_losses(self):
self.sconfitte += 1
class Contact(Base):
__tablename__ = 'contact'
def __init__(self, contact_id, client_id, janium_campaign_id, ulinc_campaign_id, webhook_response_id, ulinc_id, ulinc_ulinc_campaign_id, first_name, last_name, title,
company, location, email1, email2, email3, phone, website, li_profile_url, tib_id):
self.contact_id = contact_id
self.client_id = client_id
self.janium_campaign_id = janium_campaign_id
self.ulinc_campaign_id = ulinc_campaign_id
self.webhook_response_id = webhook_response_id
self.ulinc_id = ulinc_id
self.ulinc_ulinc_campaign_id = ulinc_ulinc_campaign_id
self.first_name = first_name
self.last_name = last_name
self.title = title
self.company = company
self.location = location
self.email1 = email1
self.email2 = email2
self.email3 = email3
self.phone = phone
self.website = website
self.li_profile_url = li_profile_url
self.tib_id = tib_id
# Primary Keys
contact_id = Column(String(36), primary_key=True, nullable=False)
# Foreign Keys
client_id = Column(String(36), ForeignKey('client.client_id'), nullable=False)
janium_campaign_id = Column(String(36), ForeignKey('janium_campaign.janium_campaign_id'), nullable=False)
ulinc_campaign_id = Column(String(36), ForeignKey('ulinc_campaign.ulinc_campaign_id'), nullable=False)
webhook_response_id = Column(String(36), ForeignKey('webhook_response.webhook_response_id'), nullable=False)
# Common Columns
ulinc_id = Column(String(20), nullable=False)
ulinc_ulinc_campaign_id = Column(String(20), nullable=False)
first_name = Column(String(100), nullable=False)
scrubbed_first_name = Column(String(100), nullable=True)
last_name = Column(String(100), nullable=False)
full_name = Column(String(200), Computed("CONCAT(first_name, ' ', last_name)"))
title = Column(String(250), nullable=True)
company = Column(String(250), nullable=True)
scrubbed_company = Column(String(250), nullable=True)
location = Column(String(250), nullable=True)
scrubbed_location = Column(String(250), nullable=True)
email1 = Column(String(250), nullable=True) # Personal Email
email2 = Column(String(250), nullable=True) # Business Email
email3 = Column(String(250), nullable=True)
phone = Column(String(250), nullable=True)
website = Column(String(250), nullable=True)
li_profile_url = Column(String(500), nullable=True)
tib_id = Column(String(36), nullable=True)
# Table Metadata
asOfStartTime = Column(DateTime, server_default=func.now())
asOfEndTime = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
effective_start_date = Column(DateTime, server_default=func.now())
effective_end_date = Column(DateTime, server_default=text("'9999-12-31 10:10:10'"))
updatedBy = Column(String(36), server_default=text("'45279d74-b359-49cd-bb94-d75e06ae64bc'"))
# SQLAlchemy Relationships and Backreferences
actions = relationship('Action', backref=backref('contact', uselist=False), uselist=True, lazy='dynamic')
def get_emails(self):
return [self.email1, self.email2, self.email3]
index=True), Column('value', Float(asdecimal=True), nullable=False),
Column('data_type', Integer, nullable=False),
Column('source_id',
ForeignKey('source.id', ondelete='CASCADE'),
nullable=False,
index=True), Column('region_id',
ForeignKey('region.id'),
index=True),
Column('unit_id', ForeignKey('unit.id'), nullable=False, index=True),
Column('positional_accuracy_in_m', Float(asdecimal=True)),
Column('centroid_coords', NullType, nullable=False),
Column('survey_count', Integer, nullable=False),
Column(
'time_series_id', String(32),
Computed(
"(concat(`source_id`,_utf8mb4'_',`unit_id`,_utf8mb4'_',coalesce(`search_type_id`,_utf8mb4'0'),_utf8mb4'_',coalesce(`site_id`,concat(_utf8mb4'g',`grid_cell_id`)),_utf8mb4'_',`taxon_id`))",
persisted=False)))
t_aggregated_by_year = Table(
'aggregated_by_year', metadata,
Column('start_date_y', SmallInteger, nullable=False),
Column('site_id', Integer),
Column('grid_cell_id', ForeignKey('grid_cell.id'), index=True),
Column('search_type_id', ForeignKey('search_type.id'), index=True),
Column('taxon_id', ForeignKey('taxon.id'), nullable=False, index=True),
Column('experimental_design_type_id',
ForeignKey('experimental_design_type.id'),
nullable=False,
index=True),
Column('response_variable_type_id',
ForeignKey('response_variable_type.id'),