class Application(BaseObject):
__tablename__ = 'applications'
id = Column(UUID, primary_key=True, default=func.uuid_generate_v4())
secret = Column(UUID, nullable=False, default=func.uuid_generate_v4())
name = Column(String, nullable=False, default='')
creation = Column(DateTime, nullable=False, default=datetime.utcnow)
modification = Column(DateTime,
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
main_url = Column(String, nullable=False, default='')
callback_url = Column(String, nullable=False, default='')
authorized_origins = Column(ARRAY(Text, dimensions=1), nullable=True)
image_url = Column(String, nullable=False, default='')
description = Column(Text, nullable=False, default='')
production_ready = Column(Boolean, nullable=False, default=False)
user_id = Column(UUID, ForeignKey('users.id'), nullable=False)
user = relationship('User', backref=backref('applications'))
def upgrade():
# We want to avoid an error trying to use the uuid_generate_v4(), so we have to install this extension
# Example Error: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function uuid_generate_v4() does not exist
op.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
op.create_table(
'users',
sa.Column('created_at',
sa.DateTime(timezone=True),
server_default=func.now(),
nullable=False),
sa.Column('updated_at',
sa.DateTime(timezone=True),
server_default=func.now(),
nullable=False),
sa.Column('id',
UUID(),
nullable=False,
server_default=func.uuid_generate_v4()),
sa.Column('email', sa.String(), nullable=False),
sa.Column('password', sa.String(), nullable=True),
sa.Column('meta', JSON(), nullable=True),
sa.Column('active', sa.Boolean(), nullable=False),
sa.Column('is_system_user', sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint('id'),
)
op.create_index(op.f('ix_users_created_at'),
'users', ['created_at'],
unique=False)
op.create_index(op.f('ix_users_updated_at'),
'users', ['updated_at'],
unique=False)
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
op.create_index(op.f('ix_users_active'), 'users', ['active'], unique=False)
class ContactTaxon(DB.Model):
'''
Informations recueillies sur un taxon donné lors d'une visite
'''
__tablename__ = 't_visite_contact_taxons'
__table_args__ = {'schema': 'monitoring_chiro'}
id_contact_taxon = DB.Column(DB.Integer, primary_key=True)
id_base_visit = DB.Column(DB.Integer,
ForeignKey(TBaseVisits.id_base_visit))
tx_presume = DB.Column(DB.Unicode(250))
cd_nom = DB.Column(DB.Integer)
nom_complet = DB.Column(DB.Unicode(250))
espece_incertaine = DB.Column(DB.Boolean, default=False)
id_nomenclature_behaviour = DB.Column(DB.Integer)
id_nomenclature_preuve_repro = DB.Column(DB.Integer)
id_nomenclature_bio_condition = DB.Column(DB.Integer)
id_nomenclature_observation_status = DB.Column(DB.Integer)
indices_cmt = DB.Column(DB.Unicode(250))
commentaire = DB.Column(DB.Unicode(250))
id_digitiser = DB.Column(DB.Integer)
uuid_chiro_visite_contact_taxon = DB.Column(UUID(as_uuid=True),
default=select(
[func.uuid_generate_v4()]))
denombrements = DB.relationship("CountingContact",
cascade="all, delete-orphan")
indices = DB.relationship(RelContactTaxonIndices,
lazy='joined',
cascade="all, delete-orphan")
class Password(BaseObject):
__tablename__ = 'passwords'
id = Column(UUID, primary_key=True, default=func.uuid_generate_v4())
creation = Column(DateTime, nullable=False, default=now)
modification = Column(DateTime, nullable=False, default=now, onupdate=now)
notes = Column(Text, nullable=False, default='')
tags = Column(ARRAY(Text, dimensions=1), nullable=False, default=[])
secret = Column(String, nullable=False, default='')
account = Column(String, nullable=False, default='')
service = Column(String, nullable=False, default='')
expiration = Column(Integer, nullable=True)
user_id = Column(UUID, ForeignKey('users.id'), nullable=False)
user = relationship(
'User',
backref=backref('passwords', cascade='all, delete-orphan'),
)
def as_dict(self):
return dict(
id=self.id,
creation=self.creation,
modification=self.modification,
notes=self.notes,
tags=self.tags,
secret=self.secret,
account=self.account,
service=self.service,
expiration=self.expiration,
user=self.user_id,
owner=self.user_id, # backwards compatibility
)
def upgrade():
op.create_table(
'audit_logs',
sa.Column('created_at',
sa.DateTime(timezone=True),
server_default=func.now(),
nullable=False),
sa.Column('id',
UUID(),
nullable=False,
server_default=func.uuid_generate_v4()),
sa.Column('user_id', UUID(), nullable=False),
sa.Column('user_email', sa.String(), nullable=False),
sa.Column('action', sa.String(), nullable=False),
sa.Column('description', sa.String(), nullable=True),
sa.Column('meta', UUID(), nullable=True),
sa.Column('request', sa.TEXT(), nullable=True),
sa.Column('url', sa.String(), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['users.id']),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_audit_logs_created_at'),
'audit_logs', ['created_at'],
unique=False)
op.create_index(op.f('ix_audit_logs_user_id'),
'audit_logs', ['user_id'],
unique=False)
op.create_index(op.f('ix_audit_logs_action'),
'audit_logs', ['action'],
unique=False)
op.create_index(op.f('ix_audit_logs_url'),
'audit_logs', ['url'],
unique=False)
class User(db.Model):
"""This class represent user model."""
__tablename__ = 'user'
guid = db.Column(postgresql.UUID, index=True, unique=True, server_default=func.uuid_generate_v4(), primary_key=True)
email = db.Column(db.String(255), index=True, unique=True, nullable=False)
password = db.Column(db.String(255), nullable=False)
first_name = db.Column(db.String(32), nullable=True)
last_name = db.Column(db.String(32), nullable=True)
created_at = db.Column(db.DateTime, server_default=func.now(), nullable=False)
updated_at = db.Column(db.DateTime, nullable=True)
def set_password(self, password):
"""Set user password"""
self.password = generate_password_hash(password)
def check_password(self, password):
"""Check user password"""
return check_password_hash(self.password, password)
def __iter__(self):
yield 'guid', self.guid
yield 'email', self.email
yield 'first_name', self.first_name
yield 'last_name', self.last_name
yield 'created_at', self.created_at
yield 'updated_at', self.updated_at
def __repr__(self):
return '<User %s>' % self.email
class AuthorizedApplication(BaseObject):
__tablename__ = 'authorized_applications'
id = Column(UUID, primary_key=True, default=func.uuid_generate_v4())
scope = Column(ARRAY(Text, dimensions=1), nullable=True)
response_type = Column(String, nullable=False, default='')
redirect_uri = Column(String, nullable=False, default='')
application_id = Column(
UUID,
ForeignKey('applications.id'),
nullable=False,
)
application = relationship(
'Application',
backref=backref('authorized_applications',
cascade='all, delete-orphan'),
)
user_id = Column(
UUID,
ForeignKey('users.id'),
nullable=False,
)
user = relationship(
'User',
backref=backref('authorized_applications',
cascade='all, delete-orphan'),
)
__table_args__ = (UniqueConstraint(application_id, user_id), )
def synthese_data(users, datasets):
with db.session.begin_nested():
source = TSources(name_source='Fixture',
desc_source='Synthese data from fixture')
db.session.add(source)
now = datetime.datetime.now()
geom_4326 = from_shape(Point(3.63492965698242, 44.3999389306734),
srid=4326)
with db.session.begin_nested():
taxon = Taxref.query.filter_by(cd_nom=713776).one()
s = Synthese(id_source=source.id_source,
unique_id_sinp=func.uuid_generate_v4(),
dataset=datasets['own_dataset'],
digitiser=users['self_user'],
nom_cite='Ashmeadopria Kieffer',
cd_nom=taxon.cd_nom,
cd_hab=3,
the_geom_4326=geom_4326,
the_geom_point=geom_4326,
the_geom_local=func.st_transform(geom_4326, 2154),
date_min=now,
date_max=now)
db.session.add(s)
data = [s]
return data
class Donation(BaseObject):
__tablename__ = 'donations'
id = Column(UUID, primary_key=True, default=func.uuid_generate_v4())
creation = Column(DateTime, nullable=False, default=now)
modification = Column(DateTime, nullable=False, default=now, onupdate=now)
first_name = Column(String, nullable=False, default='')
last_name = Column(String, nullable=False, default='')
email = Column(String, nullable=False, default='')
street = Column(String, nullable=False, default='')
city = Column(String, nullable=False, default='')
zipcode = Column(String, nullable=False, default='')
state = Column(String, nullable=False, default='')
country = Column(String, nullable=False, default='')
amount = Column(Integer, nullable=False)
send_sticker = Column(Boolean, nullable=False, default=True)
user_id = Column(UUID, ForeignKey('users.id'), nullable=True)
user = relationship('User', backref=backref('donations'))
def should_include_sticker(self):
return include_sticker(self.amount)
class CountingContact(DB.Model):
__tablename__ = 'cor_counting_contact'
__table_args__ = {'schema': 'monitoring_chiro'}
id_counting_contact = DB.Column(DB.Integer, primary_key=True)
id_contact_taxon = DB.Column(
DB.Integer,
ForeignKey(
'monitoring_chiro.t_visite_contact_taxons.id_contact_taxon'))
# Correspondance nomenclature INPN = stade_vie (10)
id_nomenclature_life_stage = DB.Column(DB.Integer)
# Correspondance nomenclature INPN = sexe (9)
id_nomenclature_sex = DB.Column(DB.Integer)
# Correspondance nomenclature INPN = obj_denbr (6)
id_nomenclature_obj_count = DB.Column(
DB.Integer,
default=select(
[func.ref_nomenclatures.get_id_nomenclature('OBJ_DENBR', 'IND')]))
# Correspondance nomenclature INPN = typ_denbr (21)
id_nomenclature_type_count = DB.Column(
DB.Integer,
default=select(
[func.ref_nomenclatures.get_id_nomenclature('TYP_DENBR', 'Co')]))
count_min = DB.Column(DB.Integer)
count_max = DB.Column(DB.Integer)
unique_id_sinp = DB.Column(UUID(as_uuid=True),
default=select([func.uuid_generate_v4()]))
class Post(db.Model):
"""This class represent post model."""
__tablename__ = 'post'
guid = db.Column(postgresql.UUID,
index=True,
unique=True,
server_default=func.uuid_generate_v4(),
primary_key=True)
blog_guid = db.Column(postgresql.UUID, index=True, nullable=False)
title = db.Column(db.String(255), index=True, nullable=False)
body = db.Column(db.Text, nullable=True)
created_at = db.Column(db.DateTime,
server_default=func.now(),
nullable=False)
updated_at = db.Column(db.DateTime, nullable=True)
def __iter__(self):
yield 'guid', self.guid
yield 'blog_guid', self.blog_guid
yield 'title', self.title
yield 'body', self.body
yield 'created_at', self.created_at
yield 'updated_at', self.updated_at
def __repr__(self):
return '<Post %s>' % self.title
class Address(db.Model):
id = db.Column(UUIDColumn,
primary_key=True,
server_default=func.uuid_generate_v4())
street_address = db.Column(db.String(255), nullable=False)
city = db.Column(db.String(255), nullable=False)
zipcode = db.Column(db.String(30), nullable=True)
user_id = db.Column(UUIDColumn, db.ForeignKey('user.id'))
class Image(db.Model):
__tablename__ = "images"
id = db.Column(UUID(),
primary_key=True,
server_default=func.uuid_generate_v4())
filetype = db.Column(db.Text(), nullable=False)
image = db.Column(db.LargeBinary(), nullable=False)
def upgrade():
op.execute('CREATE EXTENSION "uuid-ossp"')
op.create_table(
'user', sa.Column('id', UUID, server_default=func.uuid_generate_v4()),
sa.Column('email', sa.String(120), unique=True, nullable=False),
sa.Column('first_name', sa.String(120), nullable=False),
sa.Column('last_name', sa.String(120), nullable=False),
sa.Column('password', sa.Binary(), nullable=False),
sa.PrimaryKeyConstraint('id'))
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('contract', sa.Column('service_id', postgresql.UUID(), nullable=True))
op.create_table('service',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), autoincrement=False, nullable=False),
sa.Column('created', postgresql.TIMESTAMP(timezone=True), server_default=func.now(), autoincrement=False, nullable=False),
sa.Column('updated', postgresql.TIMESTAMP(timezone=True), server_default=func.now(), autoincrement=False, nullable=False),
sa.Column('name', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column('description', sa.TEXT(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name=u'service_pkey')
)
class ModelMixin:
# depends on `create extension "uuid-ossp";`
id: UUID = db.Column(Uuid,
primary_key=True,
server_default=func.uuid_generate_v4())
created_at: datetime = db.Column(db.DateTime(timezone=True),
nullable=False,
server_default=func.now())
updated_at: datetime = db.Column(db.DateTime(timezone=True),
nullable=False,
server_default=func.now(),
onupdate=func.now())
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'address',
sa.Column('id',
postgresql.UUID(),
nullable=False,
server_default=func.uuid_generate_v4()),
sa.Column('street_address', sa.String(length=255), nullable=False),
sa.Column('city', sa.String(length=255), nullable=False),
sa.Column('zipcode', sa.String(length=30), nullable=True),
sa.PrimaryKeyConstraint('id'))
class Image(db.Model):
__tablename__ = "images"
id = db.Column(UUID(), primary_key=True, server_default=func.uuid_generate_v4())
author_id = db.Column(
None, db.ForeignKey("users.discord_id", ondelete="SET NULL"), nullable=True
)
author = relationship(
User, backref=backref("images"), lazy="joined"
)
filetype = db.Column(db.Text(), nullable=False)
image = db.Column(db.LargeBinary(), nullable=False)
class Named(object):
__tablename__ = ''
id = Column(UUID(as_uuid=True),
primary_key=True,
server_default=func.uuid_generate_v4())
name = Column(String, nullable=False)
added_at = Column(DateTime(timezone=False),
server_default=text("timezone('utc', now())"))
@property
def url(self):
return '{}/{}/{}'.format(BASE_GH_URL, self.__tablename__, self.name)
def __init__(self, name=name):
self.name = name
class User(db.Model):
id = db.Column(UUIDColumn,
primary_key=True,
server_default=func.uuid_generate_v4())
email = db.Column(db.String(120), unique=True, nullable=False)
first_name = db.Column(db.String(120), nullable=False)
last_name = db.Column(db.String(120), nullable=False)
password = db.Column(db.Binary(), nullable=False)
country = db.Column(db.String(120), nullable=True)
sex = db.Column(ENUM("female",
"male",
"other",
name="sex_enum",
create_type=True),
nullable=False,
default='other')
addresses = db.relationship('Address')
class Email(GHDBase):
__tablename__ = 'emails'
id = Column(UUID(as_uuid=True),
primary_key=True,
server_default=func.uuid_generate_v4())
email = Column(String, nullable=False, unique=True)
user_id = Column(UUID(as_uuid=True), ForeignKey('users.id'))
user = relationship("User", back_populates="emails")
committed = relationship(
"Commit",
back_populates='committer_email',
primaryjoin='Email.id == Commit.committer_email_id')
authored = relationship("Commit",
back_populates='author_email',
primaryjoin='Email.id == Commit.author_email_id')
def __init__(self, email, user=None):
self.email = email
if user is not None:
self.user = user
class File(GHDBase):
__tablename__ = 'files'
__table_args__ = (
Index('filename_index', 'filename'),
Index('status_index', 'status'),
)
id = Column(UUID(as_uuid=True),
primary_key=True,
server_default=func.uuid_generate_v4())
filename = Column(String, nullable=False)
additions = Column(Integer, nullable=False, default=0)
deletions = Column(Integer, nullable=False, default=0)
status = Column(String)
commit_id = Column(UUID(as_uuid=True), ForeignKey('commits.id'))
commit = relationship('Commit', back_populates='files')
def __init__(self, commit, filename, status, additions, deletions):
self.commit = commit
self.filename = filename
self.status = status
self.additions = additions
self.deletions = deletions
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(u'consultant',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('address', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table(u'service',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table(u'ballot_type',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('percent_required', sa.Numeric(precision=2, scale=2), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table(u'employer',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table(u'tag',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table(u'election',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('date', sa.Date(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table(u'donor',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('first_name', sa.Text(), nullable=False),
sa.Column('last_name', sa.Text(), nullable=False),
sa.Column('address', sa.Text(), nullable=False),
sa.Column('latitude', sa.Float(), nullable=False),
sa.Column('longitude', sa.Float(), nullable=False),
sa.Column('employer_id', postgresql.UUID(), nullable=True),
sa.ForeignKeyConstraint(['employer_id'], [u'employer.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('first_name','last_name','latitude','longitude')
)
op.create_index('ix_donor_employer_id', 'donor', ['employer_id'], unique=False)
op.create_table(u'committee',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('filer_id', sa.Text(), nullable=True),
sa.Column('sponsor', sa.Text(), nullable=True),
sa.Column('election_id', postgresql.UUID(), nullable=True),
sa.ForeignKeyConstraint(['election_id'], [u'election.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index('ix_committee_election_id', 'committee', ['election_id'], unique=False)
op.create_table(u'ballot_measure',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('name', sa.Text(), nullable=True),
sa.Column('prop_id', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('num_yes', sa.Integer(), nullable=True),
sa.Column('num_no', sa.Integer(), nullable=True),
sa.Column('passed', sa.Boolean(), nullable=True),
sa.Column('ballot_type_id', postgresql.UUID(), nullable=True),
sa.Column('election_id', postgresql.UUID(), nullable=True),
sa.ForeignKeyConstraint(['ballot_type_id'], [u'ballot_type.id'], ),
sa.ForeignKeyConstraint(['election_id'], [u'election.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index('ix_ballot_measure_election_id', 'ballot_measure', ['election_id'], unique=False)
op.create_index('ix_ballot_measure_ballot_type_id', 'ballot_measure', ['ballot_type_id'], unique=False)
op.create_table(u'donation',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('amount', sa.Float(), nullable=False),
sa.Column('transaction_date', sa.Date(), nullable=False),
sa.Column('donor_id', postgresql.UUID(), nullable=False),
sa.Column('committee_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['committee_id'], [u'committee.id'], ),
sa.ForeignKeyConstraint(['donor_id'], [u'donor.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index('ix_donation_committee_id', 'donation', ['committee_id'], unique=False)
op.create_index('ix_donation_donor_id', 'donation', ['donor_id'], unique=False)
op.create_table(u'contract',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('payment', sa.Float(), nullable=False),
sa.Column('consultant_id', postgresql.UUID(), nullable=False),
sa.Column('service_id', postgresql.UUID(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('committee_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['committee_id'], [u'committee.id'], ),
sa.ForeignKeyConstraint(['consultant_id'], [u'consultant.id'], ),
sa.ForeignKeyConstraint(['service_id'], [u'service.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index('ix_contract_consultant_id', 'contract', ['consultant_id'], unique=False)
op.create_index('ix_contract_service_id', 'contract', ['service_id'], unique=False)
op.create_index('ix_contract_committee_id', 'contract', ['committee_id'], unique=False)
op.create_table(u'stance',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('voted_yes', sa.Boolean(), nullable=False),
sa.Column('committee_id', postgresql.UUID(), nullable=False),
sa.Column('ballot_measure_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['ballot_measure_id'], [u'ballot_measure.id'], ),
sa.ForeignKeyConstraint(['committee_id'], [u'committee.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('committee_id','ballot_measure_id')
)
op.create_index('ix_stance_ballot_measure_id', 'stance', ['ballot_measure_id'], unique=False)
op.create_index('ix_stance_committee_id', 'stance', ['committee_id'], unique=False)
op.create_table(u'ballot_measure_tags',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('created', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('ballot_measure_id', postgresql.UUID(), nullable=False),
sa.Column('tag_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['ballot_measure_id'], [u'ballot_measure.id'], ),
sa.ForeignKeyConstraint(['tag_id'], [u'tag.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('ballot_measure_id','tag_id')
)
op.create_index('ix_ballot_measure_tags_tag_id', 'ballot_measure_tags', ['tag_id'], unique=False)
op.create_index('ix_ballot_measure_tags_ballot_measure_id', 'ballot_measure_tags', ['ballot_measure_id'], unique=False)
class Booking(Base):
"""
Model class to represent a booking in the system.
Uses a unique constraint on the combination of the check-in date, the guest
id and the room id to prevent duplicated reservations. It also uses a check
constraint to ensure the check-in date is always before in time than the
check-out date.
TODO: Add an exclude constraint on the check-in and check-out dates to
prevent dates from overlapping.
Includes b-tree indexes to compare, sort and reduce memory consumption
on fields `check_in`, `check_out`, `locator`, `status`, `meal_plan` and
`deleted`.
Uses the `hashids`_ library to create a hashed value for the
:attr:`~genesisng.schema.Booking.booking.locator` attribute based on the
booking id, the guest id and the room id.
Uses the `random`_ library to create a Personal Identification Number for
the :attr:`~genesisng.schema.Booking.booking.pin` attribute.
Records are not deleted from the database but instead marked as deleted
via the :attr:`~genesisng.schema.booking.Booking.deleted` attribute, which
contains a timestamp of the date and time when the record was deleted.
.. _hashids: https://pypi.org/project/hashids/
.. _random: https://docs.python.org/2/library/random.html
"""
__tablename__ = 'booking'
__rels__ = []
__table_args__ = (
# TODO: Prevent overlappings using dates as well
UniqueConstraint('id_guest',
'id_room',
'check_in',
name='booking_id_guest_id_room_check_in'),
# Never check out before checking in
CheckConstraint('check_in < check_out'),
)
id = Column(Integer, primary_key=True)
"""Primary key. Autoincrementing integer."""
id_guest = Column(Integer, ForeignKey('guest.id'))
"""Guest id. Foreign key."""
id_room = Column(Integer, ForeignKey('room.id'))
"""Room id. Foreign key."""
reserved = Column(DateTime,
nullable=False,
default=datetime.now,
server_default=func.now())
"""Date and time when the reservation was placed. Defaults to now."""
guests = Column(Integer, nullable=False, default=1)
"""The number of guests in the reservation. Defaults to 1."""
check_in = Column(Date, nullable=False, index=True)
"""The check-in date."""
check_out = Column(Date, nullable=False, index=True)
"""The check-out date."""
checked_in = Column(DateTime)
"""The date and time when the guest actually checked in."""
checked_out = Column(DateTime)
"""The date and time when the guest actually checked out."""
cancelled = Column(DateTime, default=None)
"""The date and time when the booking was cancelled. Defaults to None."""
base_price = Column(Float, nullable=False, default=0)
"""The base price of the booking, calculated by the availability engine and
saved here upon creation. Defaults to 0."""
taxes_percentage = Column(Float, nullable=False, default=0)
"""The percentage to be applied as taxes upon the base price, calculated by
the availability engine and saved here upon creation. Defaults to 0."""
taxes_value = Column(Float, nullable=False, default=0)
"""The value of the taxes to be added to the base price, calculated by the
availability engine and saved here upon creation. Defaults to 0."""
total_price = Column(Float, nullable=False, default=0)
"""The total price of the booking, calculated by the availability engine
and saved here upon creation. Defaults to 0."""
locator = Column(String(6),
nullable=False,
index=True,
unique=True,
default=generate_locator)
"""Unique locator of the reservation. Generated through a trigger."""
pin = Column(String(4), nullable=False, default=generate_pin)
"""Personal Identification Number (PIN) for the reservation, used to
access the client area."""
status = Column(Enum(BookingStatus),
nullable=False,
index=True,
default='New')
"""Status of the reservation. Defaults to New."""
meal_plan = Column(Enum(BookingMealPlan),
nullable=False,
index=True,
default='BedAndBreakfast')
"""Meal plan included in the reservation. Defaults to BedAndBreakfast."""
extras = Column(JSONB, nullable=False, default=lambda: {})
"""Additional services included in the reservation, taken from the values
in the :class:`~genesisng.schema.extra.Extra` model class and stored as a
JSON document. The column defaults to an empty dictionary instead
of None to prevent newly added values to be swallowed without error."""
uuid = Column(UUID(as_uuid=True),
nullable=False,
index=True,
unique=True,
server_default=func.uuid_generate_v4(),
comment='Unique code used to detect duplicates')
"""Universally Unique IDentifier of the reservation. Used to prevent
double-booking by mistake."""
deleted = Column(DateTime, index=True, default=None)
"""Timestamp of the deletion of the record. Defaults to None."""
@hybrid_property
def nights(self):
"""The amount of nights the guests are staying for this booking."""
return (self.check_out - self.check_in).days
@nights.expression
def nights(cls):
return func.date_part('day', func.age(cls.check_out, cls.check_in))
room = relationship("Room", backref="booking")
guest = relationship("Guest", backref="booking")
def __repr__(self):
"""String representation of the object."""
return "<Booking(id='%s', guests='%s', check_in='%s', check_out='%s')>" % (
self.id, self.guests, self.check_in, self.check_out)
import uuid
from sqlalchemy import Column, Table, Unicode, func
from premo import db
entities = Table(
'entities', db.Metadata,
Column('id', db.UUID, primary_key=True,
server_default=func.uuid_generate_v4()),
Column('value', Unicode),
)
class Entity(db.Model):
__table__ = entities
@classmethod
def create(cls, resource):
entity = cls(**dict(resource))
cls.Session.add(entity)
return entity
@classmethod
def get(cls, guid):
return cls.query.get(guid)
class LeagueRoster(db.Model):
__tablename__ = 'league_roster'
player_id = db.Column(UUID,
primary_key=True,
server_default=func.uuid_generate_v4())
name = db.Column(TEXT)
team = db.Column(TEXT)
gp = db.Column(INTEGER)
mins = db.Column(NUMERIC)
mins_sd = db.Column(NUMERIC)
fg = db.Column(NUMERIC)
fg_sd = db.Column(NUMERIC)
fga = db.Column(NUMERIC)
fga_sd = db.Column(NUMERIC)
ft = db.Column(NUMERIC)
ft_sd = db.Column(NUMERIC)
fta = db.Column(NUMERIC)
fta_sd = db.Column(NUMERIC)
tpm = db.Column(NUMERIC)
tpm_sd = db.Column(NUMERIC)
pts = db.Column(NUMERIC)
pts_sd = db.Column(NUMERIC)
reb = db.Column(NUMERIC)
reb_sd = db.Column(NUMERIC)
ast = db.Column(NUMERIC)
ast_sd = db.Column(NUMERIC)
stl = db.Column(NUMERIC)
stl_sd = db.Column(NUMERIC)
blk = db.Column(NUMERIC)
blk_sd = db.Column(NUMERIC)
tov = db.Column(NUMERIC)
tov_sd = db.Column(NUMERIC)
def __repr__(self):
return '<Player {}>'.format(self.name)
def get_stddevs():
stddevs = {
'fg':
((db.session.query(func.stddev(LeagueRoster.fg))).all())[0][0],
'ft':
((db.session.query(func.stddev(LeagueRoster.ft))).all())[0][0],
'tpm':
((db.session.query(func.stddev(LeagueRoster.tpm))).all())[0][0],
'pts':
((db.session.query(func.stddev(LeagueRoster.pts))).all())[0][0],
'reb':
((db.session.query(func.stddev(LeagueRoster.reb))).all())[0][0],
'ast':
((db.session.query(func.stddev(LeagueRoster.ast))).all())[0][0],
'stl':
((db.session.query(func.stddev(LeagueRoster.stl))).all())[0][0],
'blk':
((db.session.query(func.stddev(LeagueRoster.blk))).all())[0][0],
'tov':
((db.session.query(func.stddev(LeagueRoster.tov))).all())[0][0]
}
return stddevs
def get_means():
means = {
'fg': ((db.session.query(func.avg(LeagueRoster.fg))).all())[0][0],
'ft': ((db.session.query(func.avg(LeagueRoster.ft))).all())[0][0],
'tpm':
((db.session.query(func.avg(LeagueRoster.tpm))).all())[0][0],
'pts':
((db.session.query(func.avg(LeagueRoster.pts))).all())[0][0],
'reb':
((db.session.query(func.avg(LeagueRoster.reb))).all())[0][0],
'ast':
((db.session.query(func.avg(LeagueRoster.ast))).all())[0][0],
'stl':
((db.session.query(func.avg(LeagueRoster.stl))).all())[0][0],
'blk':
((db.session.query(func.avg(LeagueRoster.blk))).all())[0][0],
'tov': ((db.session.query(func.avg(LeagueRoster.tov))).all())[0][0]
}
return means
def get_zscores(player, stddevs, means):
scores = {
mean: ((player.get(mean) - means.get(mean)) / stddevs.get(mean))
for mean in means
}
return scores
def wrap(row):
player = {
'name': row.name,
'team': 'OKC', #placeholder until abbreviation function is written
'gp': row.gp,
'mins': round(row.mins, 2),
'fg': round(row.fg, 2),
'fga': round(row.fga, 2),
'ft': round(row.ft, 2),
'fta': round(row.fta, 2),
'tpm': round(row.tpm, 2),
'pts': round(row.pts, 2),
'reb': round(row.reb, 2),
'ast': round(row.ast, 2),
'stl': round(row.stl, 2),
'blk': round(row.blk, 2),
'tov': round(row.tov, 2)
}
return player
def evaluate(player, zscores, weights):
player['value'] = 0
zscores.update(tov=-zscores.get('tov'))
for key, val in zscores.items():
if key == 'fg':
player['vfg'] = (round(
(val * weights.get('fg_wt') * player.get('fga') / 5), 2))
player.update(value=player.get('value') +
round((val * weights.get('fg_wt')), 2))
elif key == 'ft':
player['vft'] = (round(
(val * weights.get('ft_wt') * player.get('fta') / 2), 2))
player.update(value=player.get('value') +
round((val * weights.get('ft_wt')), 2))
else:
player['v' + key] = round(val * weights.get(key + '_wt'), 2)
player.update(value=player.get('value') +
round((val * weights.get(key + '_wt')), 2))
def delta(give, receive):
delta = give.copy()
for key in delta.keys():
if key == 'name':
delta[key] = u'Δ'
elif (key == 'team') or (key == 'gp'):
delta[key] = ""
else:
delta[key] = (give.get(key) - receive.get(key))
return delta
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('user',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('first_name', sa.Text(), nullable=False),
sa.Column('last_name', sa.Text(), nullable=False),
sa.Column('age', sa.Integer(), nullable=True),
sa.Column('phone_number', sa.Text(), nullable=True),
sa.Column('username', sa.Text(), nullable=False),
sa.Column('password', sa.Text(), nullable=False),
sa.Column('role', sa.Enum('Doer', 'Leader', 'Unknown', name='role'), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('username')
)
op.create_table('interest',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('name', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('leader',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.ForeignKeyConstraint(['id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('doer',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.ForeignKeyConstraint(['id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('leader_interest',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('leader_id', postgresql.UUID(), nullable=False),
sa.Column('interest_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['interest_id'], ['interest.id'], ),
sa.ForeignKeyConstraint(['leader_id'], ['leader.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('leader_id', 'interest_id')
)
op.create_table('doer_interest',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('doer_id', postgresql.UUID(), nullable=False),
sa.Column('interest_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['doer_id'], ['doer.id'], ),
sa.ForeignKeyConstraint(['interest_id'], ['interest.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('doer_id', 'interest_id')
)
op.create_table('request',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('doer_id', postgresql.UUID(), nullable=False),
sa.Column('interest_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['doer_id'], ['doer.id'], ),
sa.ForeignKeyConstraint(['interest_id'], ['interest.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('doer_id', 'interest_id')
)
op.create_table('event',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('date', sa.DateTime(), nullable=True),
sa.Column('leader_id', postgresql.UUID(), nullable=False),
sa.Column('interest_id', postgresql.UUID(), nullable=True),
sa.ForeignKeyConstraint(['interest_id'], ['interest.id'], ),
sa.ForeignKeyConstraint(['leader_id'], ['leader.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('doer_event',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), nullable=False),
sa.Column('create_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=False),
sa.Column('modify_date', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.Column('doer_id', postgresql.UUID(), nullable=False),
sa.Column('event_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['doer_id'], ['doer.id'], ),
sa.ForeignKeyConstraint(['event_id'], ['event.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('doer_id', 'event_id')
)
users_table = Table(
"users",
metadata,
Column("id", BigInteger, primary_key=True, autoincrement=False),
Column("first_name", String()),
Column("last_name", String()),
Column("version", BigInteger, nullable=False),
Column("meta", JSONB, server_default="{}", nullable=False),
)
profiles_table = Table(
"profiles",
metadata,
Column("id",
UUID,
server_default=func.uuid_generate_v4(),
primary_key=True),
Column("created",
DateTime(timezone=True),
nullable=False,
server_default=func.now()),
Column("updated",
DateTime(timezone=True),
nullable=True,
onupdate=func.now()),
Column("version", BigInteger, nullable=False),
Column("state", element_statuses, default="active", nullable=False),
Column("name", String, nullable=False),
Column("code", String, nullable=False),
Column("common", Boolean, default=False, nullable=False),
Column("meta", JSONB, server_default="{}", nullable=False),
