class UserSkill(db.Model):
__tablename__ = 'users_skills'
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'skill_id'), )
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
skill_id = db.Column(db.Integer, db.ForeignKey('skills.skill_id'))
rating = db.Column(db.Integer, nullable=False)
db.UniqueConstraint('user_id', 'user_id')
skill = db.relationship("Skill", back_populates="users")
user = db.relationship("User", back_populates="skills")
def __init__(self, rating):
self.rating = rating
class Refus(db.Model):
''' Un refus explicite ou implicite à une proposition. '''
__tablename__ = 'refus'
proposition = db.Column(db.Integer, db.ForeignKey('propositions.numero'))
moment = db.Column(db.DateTime)
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
explicite = db.Column(db.Boolean)
__table_args__ = (
db.PrimaryKeyConstraint('proposition', 'moment', name='pk_refus'),
)
class Penalites(db.Model):
''' Une pénalité infligée à un conducteur. '''
__tablename__ = 'penalites'
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
debut = db.Column(db.DateTime)
fin = db.Column(db.DateTime)
raison = db.Column(db.String)
__table_args__ = (
db.PrimaryKeyConstraint('conducteur', 'debut', name='pk_penalites'),
)
class CustomerGroupDescription(db.Model):
__tablename__ = 'fp_customer_group_description'
__table_args__ = (db.PrimaryKeyConstraint('customer_group_id',
'language_id'), )
customer_group_id = db.Column(db.Integer,
db.ForeignKey('fp_customer_group.id'))
language_id = db.Column(db.Integer, db.ForeignKey('fp_language.id'))
name = db.Column(db.String(32))
description = db.Column(db.Text())
def __str__(self):
return self.name
class SubInventory(db.Model):
storeId = db.Column(db.Integer, db.ForeignKey("store.storeId"))
productId = db.Column(db.Integer, db.ForeignKey('product.productId'))
price = db.Column(db.DECIMAL(10000, 9), nullable=False)
quantity = db.Column(db.Integer, db.CheckConstraint('quantity > 0'), nullable=False)
discount = db.Column(db.Integer, nullable=False)
orderId = db.Column(db.Integer, db.ForeignKey('order.orderId'))
delivery = db.Column(db.Boolean, default=False)
__table__args__ = (
db.PrimaryKeyConstraint(storeId, productId, orderId)
)
def __repr__(self):
return "<SubInventory {}>".format(self.storeId, self.productId)
class Inventory(db.Model):
storeId = db.Column(db.Integer, db.ForeignKey('store.storeId'))
productId = db.Column(db.Integer, db.ForeignKey('product.productId'))
price = db.Column(db.DECIMAL(10000, 9), nullable=False)
quantity = db.Column(db.Integer, db.CheckConstraint('quantity>=0'), nullable=False)
discount = db.Column(db.Integer, default=0)
stores = db.relationship("Store", backref=db.backref("inventory", cascade='all'))
product = db.relationship("Product", backref=db.backref("inventory", cascade='all'))
__table__args__ = (
db.PrimaryKeyConstraint(storeId, productId)
)
def __repr__(self):
return "<Inventory {} - {}>".format(self.storeId, self.productId)
class Notification(db.Model):
''' Une notification envoyée à un utilisateur. '''
__tablename__ = 'notifications'
utilisateur = db.Column(db.String, db.ForeignKey('utilisateurs.telephone'))
course = db.Column(db.Integer, db.ForeignKey('courses.numero'))
moment = db.Column(db.DateTime)
sujet = db.Column(db.String)
__table_args__ = (db.PrimaryKeyConstraint('utilisateur',
'course',
'moment',
name='pk_notifications'), )
class Habitude(db.Model):
'''
Une adresse que l'utilisateur a l'habitude de choisir comme point de
départ.
'''
__tablename__ = 'habitudes'
utilisateur = db.Column(db.String, db.ForeignKey('utilisateurs.telephone'))
adresse = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
__table_args__ = (db.PrimaryKeyConstraint('utilisateur',
'adresse',
name='pk_habitudes'), )
class Attending(db.Model):
__tablename__ = 'attending'
eventid = db.Column('eventid',
db.String(256),
db.ForeignKey('events.eventid'),
nullable=False)
uid = db.Column('uid',
db.String(256),
db.ForeignKey('ruser.uid'),
nullable=False)
__table_args__ = (db.PrimaryKeyConstraint(eventid, uid), {})
def insert(eventid, uid):
try:
dictionary = {'eventid': eventid, 'uid': uid}
db.session.execute('INSERT INTO Attending VALUES (:eventid, :uid)',
dictionary)
db.session.commit()
except Exception as e:
db.session.rollback()
raise e
def delete(eventid, uid):
try:
dictionary = {'eventid': eventid, 'uid': uid}
db.session.execute(
'DELETE FROM Attending WHERE eventid = :eventid and uid = :uid',
dictionary)
db.session.commit()
except Exception as e:
db.session.rollback()
raise e
def query(uid):
try:
dictionary = {'uid': uid}
events = db.session.execute(
'SELECT * FROM Attending WHERE uid = :uid', dictionary)
print("welp at least that worked")
UE = [event.eventid for event in events]
print(UE)
return UE
except Exception as e:
db.session.rollback()
raise e
class BagPiece(db.Model):
__tablename__ = "bag_piece"
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'player_id'), )
user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
player_id = db.Column(db.Integer, db.ForeignKey("player_base.id"))
num = db.Column(db.Integer)
user = db.relationship('User', backref='bagpiece')
player_base = db.relationship('PlayerBase', backref='bagpiece')
def __init__(self, user_id, player_id, num):
self.user_id, self.player_id, self.num = (user_id, player_id, num)
def __repr__(self):
return "<BagPiece %r, %r>" % (self.user_id, self.player_id)
class RoundContest(db.Model):
round_id = db.Column(db.String(200), db.ForeignKey('round.id', ondelete='cascade'), nullable=False)
contest_id = db.Column(db.String(200), db.ForeignKey('targeted_contest.id', ondelete='cascade'), nullable=False)
sample_size_options = db.Column(db.String(1000), nullable=True)
results = relationship('RoundContestResult', backref='round_contest', passive_deletes=True)
__table_args__ = (
db.PrimaryKeyConstraint('round_id', 'contest_id'),
)
end_p_value = db.Column(db.Float)
is_complete = db.Column(db.Boolean)
sample_size = db.Column(db.Integer)
class BadgeTemplateToFlag(Model):
__tablename__ = 'badge_template_to_flag'
__table_args__ = (db.PrimaryKeyConstraint('badge_template_id',
'flag_id'), )
badge_template_id = db.Column(db.BigInteger(),
db.ForeignKey('badge_template.id',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
flag_id = db.Column(db.BigInteger(),
db.ForeignKey('flag.id',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False)
class Bannissement(db.Model):
''' Un banissement d'un utilisateur. '''
__tablename__ = 'banissements'
utilisateur = db.Column(db.String, db.ForeignKey('utilisateurs.telephone'))
debut = db.Column(db.DateTime)
fin = db.Column(db.DateTime)
raison = db.Column(db.String)
__table_args__ = (
db.PrimaryKeyConstraint('utilisateur', 'debut',
name='pk_bannissements'),
)
class CategoryDescription(db.Model):
__tablename__ = 'fp_category_description'
__table_args__ = (db.PrimaryKeyConstraint('category_id', 'language_id'), )
category_id = db.Column(db.Integer, db.ForeignKey('fp_category.id'))
language_id = db.Column(db.Integer, db.ForeignKey('fp_language.id'))
name = db.Column(db.String(64), unique=True, index=True)
description = db.Column(db.String(140))
meta_title = db.Column(db.String(255), nullable=True)
meta_description = db.Column(db.String(255), nullable=True)
meta_keyword = db.Column(db.String(255), nullable=True)
def __str__(self):
return self.name
class Friend(db.Model):
__tablename__ = "friend"
__table_args__ = (
db.PrimaryKeyConstraint('user_id', 'friend_id'),
)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
friend_id = db.Column(db.Integer, db.ForeignKey('user.id'))
def __init__(self, user_id, friend_id):
self.user_id, self.friend_id = (
user_id, friend_id
)
def __repr__(self):
return "<Friend %r, %r>" % (self.user_id, self.friend_id)
class Forfait(db.Model):
__tablename__ = 'forfaits'
entreprise = db.Column(db.String, db.ForeignKey('entreprises.nom'))
destination_1 = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
destination_2 = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
tarif = db.Column(db.String)
montant = db.Column(db.Float)
__table_args__ = (
db.PrimaryKeyConstraint('entreprise', 'destination_1', 'destination_2',
'tarif', name='pk_forfait'),
#db.CheckConstraint('montant >= 0', name='montant_positif_forfait_check'),
#db.CheckConstraint('destination_1 <> destination_2', name='destination_1_dif_destination_2_forfait_check'),
)
class Seats(db.Model):
__tablename__ = 'seats'
__table_args__ = (db.PrimaryKeyConstraint('aircraft_code',
'seat_no',
name='boarding_passes_pkey'),
db.ForeignKeyConstraint(
['aircraft_code'], ['aircrafts_data.aircraft_code'],
name='boarding_passes_ticket_no_fkey'))
aircraft_code = db.Column(CHAR(3), nullable=False)
seat_no = db.Column(CHAR(4), nullable=False)
fare_conditions = db.Column(CHAR(10), nullable=False)
def __init__(self, aircraft_code, seat_no, fare_conditions):
self.aircraft_code = aircraft_code
self.seat_no = seat_no
self.fare_conditions = fare_conditions
class RefreshToken(db.Model):
__tablename__ = 'refreshtokens'
__table_args__ = (db.PrimaryKeyConstraint('userid'), )
userid = db.Column(db.Integer, db.ForeignKey('users.id'))
tokenname = db.Column(db.String)
token = db.Column(db.String)
expireat = db.Column(db.Date)
def serialize(self):
return {
userid: self.userid,
tokenname: self.tokenname,
token: self.token,
expireat: self.expireat
}
class SiteSeo(db.Model):
"""店铺SEO优化设置"""
__tablename__ = 'fp_site_seo'
__table_args__ = (db.PrimaryKeyConstraint('site_id', 'language_id'), )
site_id = db.Column(db.Integer, db.ForeignKey('fp_site.id'))
language_id = db.Column(db.Integer, db.ForeignKey('fp_language.id'))
title = db.Column(db.String(255), nullable=True)
description = db.Column(db.String(255), nullable=True)
keywords = db.Column(db.String(255), nullable=True)
def __repr__(self):
return '<SiteSeo {}>'.format(self.site_id)
class Position(db.Model):
''' Une position d'un conducteur à un moment donné. '''
__tablename__ = 'positions'
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
moment = db.Column(db.DateTime)
position = db.Column(Geometry('POINT'))
statut = db.Column(db.String)
__table_args__ = (
db.PrimaryKeyConstraint('conducteur', 'moment', name='pk_positions'),
# db.CheckConstraint('statut in ('libre', 'en charge', 'destination','indisponible')', name='statut_position_check'),
)
class PlayerEquip(db.Model):
__tablename__ = "player_equip"
__table_args__ = (db.PrimaryKeyConstraint('bag_player_id'), )
bag_player_id = db.Column(db.Integer, db.ForeignKey("bag_player.id"))
coat_id = db.Column(db.Integer)
pants_id = db.Column(db.Integer)
shoes_id = db.Column(db.Integer)
bag_player = db.relationship("BagPlayer", backref='playerequip')
def __init__(self, bag_player_id, coat_id, pants_id, shoes_id):
self.bag_player_id, self.coat_id, self.pants_id, self.shoes_id = (
bag_player_id, coat_id, pants_id, shoes_id)
def __repr__(self):
return "<PlayerEquip %r %r %r %r>" % (self.bag_player_id, self.coat_id,
self.pants_id, self.shoes_id)
class Paiement(db.Model):
__tablename__ = 'paiements'
entreprise = db.Column(db.String, db.ForeignKey('entreprises.nom'))
mois = db.Column(db.String)
annee = db.Column(db.Integer)
montant = db.Column(db.Float)
montant_majore = db.Column(db.Float)
__table_args__ = (
db.PrimaryKeyConstraint('entreprise', 'mois', 'annee', name='pk_paiement'),
#db.CheckConstraint('montant >= 0', name='montant_positif_paiement_check'),
#db.CheckConstraint('montant_majore >= 0', name='montant_majore_positif_paiement_check'),
#db.CheckConstraint('mois in ('janvier','fevrier','mars','avril','mai','juin','juillet','aout','septembre','octobre','novembre','decembre')', name='mois_paiement_check'),
#db.CheckConstraint('length('annee')=4', name='taille_annee_paiement_check'),
)
class PropUsing(db.Model):
__tablename__ = "prop_using"
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'prop_type'), )
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
# prop_id == 0:fund_card
# prop_id == 1:exp_card
prop_type = db.Column(db.Integer)
duetime = db.Column(db.DateTime)
user = db.relationship('User', backref='propusing')
def __init__(self, user_id, prop_type, duetime):
self.user_id, self.prop_type, self.duetime = (user_id, prop_type,
duetime)
def __repr__(self):
return "<PropUsing %r, %r, %r>" % (self.user_id, self.prop_type,
self.duetime)
class Rawdata(db.Model):
dataviz = db.Column(db.String(50),
db.ForeignKey(schema + 'dataviz.dataviz'),
index=True,
nullable=False)
dataid = db.Column(db.String(50),
db.ForeignKey(schema + 'dataid.dataid'),
index=True,
nullable=False)
dataset = db.Column(db.String(50), nullable=False)
order = db.Column(db.Integer, nullable=False)
label = db.Column(db.String(250))
data = db.Column(db.String(250))
__table_args__ = (db.PrimaryKeyConstraint('dataviz', 'dataid', 'dataset',
'order'), tableschema)
def __repr__(self):
return '<Rawdata {}>'.format(self.dataviz)
class Proposition(db.Model):
''' Une proposition de course faite à un conducteur. '''
__tablename__ = 'propositions'
iteration = db.Column(db.Integer)
course = db.Column(db.Integer, db.ForeignKey('courses.numero'))
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
proposition = db.Column(db.DateTime)
reponse = db.Column(db.DateTime)
statut = db.Column(db.String)
raison = db.Column(db.String)
ordre = db.Column(db.Integer)
meme_station = db.Column(db.Boolean)
__table_args__ = (db.PrimaryKeyConstraint('iteration',
'course',
'conducteur',
name='pk_proposition'), )
class ProductDescription(db.Model):
__tablename__ = 'fp_product_description'
__table_args__ = (
db.PrimaryKeyConstraint('product_id', 'language_id'),
)
master_uid = db.Column(db.Integer, index=True, default=0)
product_id = db.Column(db.Integer, db.ForeignKey('fp_product.id'))
language_id = db.Column(db.Integer, db.ForeignKey('fp_language.id'))
name = db.Column(db.String(100), nullable=False)
description = db.Column(db.Text())
tag = db.Column(db.Text())
meta_title = db.Column(db.String(255), nullable=False)
meta_description = db.Column(db.String(255), nullable=False)
meta_keyword = db.Column(db.String(255), nullable=False)
def __repr__(self):
return '<ProductDescription {}>'.format(self.product_id)
class Vote(db.Model):
"""
Class that keeps track of upvotes"""
__tablename__ = 'votes'
__table_args__ = (db.PrimaryKeyConstraint('event_id', 'user_id'), )
#TODO: add primary key?
event_id = db.Column(db.Integer, db.ForeignKey('events.id'), index=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), index=True)
def __init__(self, event_id, user_id):
self.event_id = event_id
self.user_id = user_id
def save(self):
db.session.add(self)
db.session.commit()
@staticmethod
def upvote(event_id, user_id):
vote = Vote.query.filter_by(user_id=user_id, event_id=event_id).first()
if not vote:
vote = Vote(event_id=event_id, user_id=user_id)
vote.save()
return True
return False
@staticmethod
def remove_vote(event_id, user_id):
vote = Vote.query.filter_by(user_id=user_id, event_id=event_id)
if vote:
vote.delete()
db.session.commit()
return True
return False
@staticmethod
def user_voted(event_id, user_id):
return db.session.query(
Vote.query.filter_by(user_id=user_id,
event_id=event_id).exists()).scalar()
class BagTrailCard(db.Model):
__tablename__ = "bag_trail_card"
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'player_id',
'time'), )
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
player_id = db.Column(db.Integer, db.ForeignKey('player_base.id'))
num = db.Column(db.Integer)
time = db.Column(db.Integer)
user = db.relationship('User', backref='bagtrailcard')
player = db.relationship('PlayerBase', backref='bagtrailcard')
def __init__(self, user_id, palyer_id, num, time):
self.user_id, self.player_id, self.num, self.time = (user_id,
palyer_id, num,
time)
def __repr__(self):
return "<BagTrailCard %r, %r, %r, %r>" % (self.user_id, self.player_id,
self.num, self.time)
class TimeLog(HasCreatedModified, Serializable, db.Model):
""" Time logs table
Design Decisions
----------------
Date, Employee ID and Report ID will be a composite primary key to support
the Payroll View.
Employee ID must not be constrained by foreign key, so that records can
exist without a corresponding user entry.
"""
__tablename__ = 'time_logs'
__table_args__ = (db.PrimaryKeyConstraint('date', 'employee_id',
'report_id'), {})
date = db.Column(db.Date, index=True, nullable=False)
hours_worked = db.Column(db.Float, nullable=False)
employee_id = db.Column(db.Integer, index=True, nullable=False)
job_group = db.Column(db.String(1), nullable=False)
report_id = db.Column(db.Integer, nullable=False)
class RoundContestResult(db.Model):
round_id = db.Column(db.String(200),
db.ForeignKey('round.id', ondelete='cascade'),
nullable=False)
contest_id = db.Column(db.String(200),
db.ForeignKey('targeted_contest.id',
ondelete='cascade'),
nullable=False)
__table_args__ = (db.PrimaryKeyConstraint('round_id',
'targeted_contest_choice_id'),
db.ForeignKeyConstraint(['round_id', 'contest_id'], [
'round_contest.round_id', 'round_contest.contest_id'
],
ondelete='cascade'))
targeted_contest_choice_id = db.Column(db.String(200),
db.ForeignKey(
'targeted_contest_choice.id',
ondelete='cascade'),
nullable=False)
result = db.Column(db.Integer)
