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