class Application(db.Model):
email = db.Column(db.String(40), primary_key=True)
university = db.Column(db.String(40), nullable=False)
dname = db.Column(db.String(20), nullable=False)
program = db.Column(db.String(10),
db.CheckConstraint('program in (MS,PhD)'),
nullable=False)
dateOfApp = db.Column(db.DateTime, default=datetime.utcnow)
termOfAdmission = db.Column(
db.String(2),
db.CheckConstraint('termOfAdmission in (FA,SP,SU)'),
nullable=True)
yearOfAdmission = db.Column(
db.Integer,
db.CheckConstraint('yearOfAdmission > 1999 and yearOfAdmission<2100'),
nullable=True)
admissionStatus = db.Column(
db.String(10),
db.CheckConstraint('admissionStatus in (ACCEPT,REJECT,PENDING)'))
dataSentToPaws = db.Column(
db.String(3), db.CheckConstraint('dataSentToPaws in (YES,NO)'))
applicant_email = db.Column(db.String(40),
db.ForeignKey('applicant.email'))
applicant_program = db.Column(db.String(40),
db.ForeignKey('program.program'))
def __repr__(self):
return '<Application {}>'.format(self.email)
class Course(db.Model):
''' Une demande de course qui devient une course terminée. '''
__tablename__ = 'courses'
numero = db.Column(db.Integer, autoincrement=True, primary_key=True)
trouvee = db.Column(db.Boolean)
finie = db.Column(db.Boolean)
utilisateur = db.Column(db.String, db.ForeignKey('utilisateurs.telephone'))
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
places = db.Column(db.Integer, db.CheckConstraint('1 <= places'))
priorite = db.Column(db.String)
debut = db.Column(db.DateTime)
fin = db.Column(db.DateTime)
retour = db.Column(db.Boolean)
commentaire = db.Column(db.String)
bagages = db.Column(db.Integer)
animaux = db.Column(db.Integer)
gare = db.Column(db.Boolean)
aeroport = db.Column(db.Boolean)
depart = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
arrivee = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
__table_args__ = (db.CheckConstraint('debut < fin',
name='debut_inf_fin_check'), )
class Course(db.Model):
''' Une demande de course qui devient une course terminée. '''
__tablename__ = 'courses'
numero = db.Column(db.Integer, autoincrement=True, primary_key=True)
trouvee = db.Column(db.Boolean)
finie = db.Column(db.Boolean)
utilisateur = db.Column(db.String, db.ForeignKey('utilisateurs.telephone'))
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
places = db.Column(db.Integer, db.CheckConstraint('1 <= places'))
priorite = db.Column(db.String)
debut = db.Column(db.DateTime)
fin = db.Column(db.DateTime)
commentaire = db.Column(db.String)
depart = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
arrivee = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
bagages = db.Column(db.Integer)
animaux = db.Column(db.Integer)
animaux_grands = db.Column(db.Boolean)
gare = db.Column(db.Boolean)
aeroport = db.Column(db.Boolean)
entreprise = db.Column(db.String, db.ForeignKey('entreprises.nom'))
distance_estimee = db.Column(db.Float)
anglais = db.Column(db.Boolean)
tps_estime = db.Column(db.Integer)
__table_args__ = (
db.CheckConstraint('debut < fin', name='debut_inf_fin_check'),
#db.CheckConstraint('depart <> arrive', name='depart_dif_arrive_check'),
#db.CheckConstraint('distance_estime >=0', name='distance_estime_positive_check'),
#db.CheckConstraint('tps_estime >= 0', name='tsp_estime_positive_check'),
)
class Orders(db.Model):
__tablename__ = 'orders'
id_order = db.Column(db.String(50), primary_key = True)
edIndex = db.Column(db.Integer, db.ForeignKey('editions.index'))
custId = db.Column(db.Integer, db.ForeignKey('customers.cust_id_phone'))
period = db.Column(db.Integer, db.CheckConstraint('period>=1'))#, name = 'check_period'))
quantity = db.Column(db.Integer, db.CheckConstraint('quantity>=1'))#, name = 'check_quantity'))
order_date = db.Column(db.Date)
totalCost = db.Column(db.Float)
#__table_args__ = {'extend_existing': True}
#orders = relationship('Editions', back_populates = 'editions')
#order_cust = db.relationship('Customers', backref = 'cust_orders')#, nullable = False)
def __init__(self,id_order, edIndex, custId, period,quantity, order_date, totalCost):
self.id_order = id_order
self.edIndex = edIndex
self.custId = custId
self.period = period
self.quantity = quantity
self.order_date = order_date
self.totalCost = totalCost
def __repr__(self):
return '<User %r>' % self.id_order
class Students(db.Model):
__tablename__ = 'Students'
stud_id_phone = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(100), nullable=False, db.CheckConstraint(
'email LIKE ^([a-zA-Z0-9\\-]+\\.)*[a-zA-Z0-9_\\-]+@([a-zA-Z0-9_\\-]+\\.)+(com|org|edu|net|ca|au|coop|de|ee|es|fm|fr|gr|ie|in|it|jp|me|nl|nu|ru|uk|us|za)$'))
age = db.Column(db.Integer, db.CheckConstraint('age>0.0', 'age<100.0', name='check_age'))
class Enroll(db.Model):
eid = db.Column(db.Integer,db.Sequence('enroll_eid_seq'),db.CheckConstraint('eid > 999 and eid<10000'),primary_key=True)
sid = db.Column(db.Integer)
term = db.Column(db.String(2),db.CheckConstraint('term in (FA,SP,SU)'))
year = db.Column(db.Integer)
crn = db.Column(db.Integer)
grade = db.Column(db.String(2),db.CheckConstraint('grade in (A,B,C,D,F,I,IP,S,U)'))
student_sid = db.Column(db.Integer,db.ForeignKey('student.sid'))
section_tyc = db.Column(db.Integer,db.ForeignKey('section.crn'))
def __repr__(self):
return '<Enroll {}>'.format(self.sid)
class Facture(db.Model):
''' Une facture relative à une course. '''
__tablename__ = 'factures'
course = db.Column(db.Integer,
db.ForeignKey('courses.numero'),
primary_key=True)
forfait = db.Column(db.String)
estimation = db.Column(db.Float, db.CheckConstraint('0 <= estimation'))
montant = db.Column(db.Float, db.CheckConstraint('0 <= montant'))
rabais = db.Column(db.Float,
db.CheckConstraint('0 <= rabais AND rabais <= 1'))
paiement = db.Column(db.String)
class Station(db.Model):
''' Une station contenant des taxis. '''
__tablename__ = 'stations'
nom = db.Column(db.String, primary_key=True)
adresse = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
distance_entree = db.Column(db.Float,
db.CheckConstraint('0 <= distance_entree'))
distance_sortie = db.Column(db.Float,
db.CheckConstraint('0 <= distance_sortie'))
__table_args__ = (db.CheckConstraint('distance_entree < distance_sortie',
name='entree_inf_sortie_check'), )
class Account(db.Model):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(140))
platinum = db.Column(db.Integer)
gold = db.Column(db.Integer)
electrum = db.Column(db.Integer)
silver = db.Column(db.Integer)
copper = db.Column(db.Integer)
__table_args__ = (
db.CheckConstraint('platinum >= 0', name='platinum_gte_0'),
db.CheckConstraint('gold >= 0', name='gold_gte_0'),
db.CheckConstraint('electrum >= 0', name='electrum_gte_0'),
db.CheckConstraint('silver >= 0', name='silver_gte_0'),
db.CheckConstraint('copper >= 0', name='copper_gte_0'),
)
class Owner(db.Model):
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
owner_type = db.Column(db.Enum(OwnerType),
default=OwnerType.FARM,
nullable=False)
owned_by_farm_id = db.Column(db.Integer)
owned_by_user_id = db.Column(db.Integer)
__table_args__ = (db.CheckConstraint(
'( CASE WHEN owned_by_farm_id is NULL THEN 0 ELSE 1 END'
' + CASE WHEN owned_by_user_id is NULL THEN 0 ELSE 1 END'
') = 1'), )
def __init__(self, owned_by_farm_id, owned_by_user_id):
if owned_by_farm_id is not None:
self.owner_type = OwnerType.FARM
self.owned_by_farm_id = owned_by_farm_id
elif owned_by_user_id is not None:
self.owner_type = OwnerType.USER
self.owned_by_user_id = owned_by_user_id
def to_json(self):
if self.owner_type == OwnerType.FARM:
owner_id = self.owned_by_farm_id
else:
owner_id = self.owned_by_user_id
return {"owned_by": self.owner_type.value, "owner_id": owner_id}
class Vehicule(db.Model):
''' Une vehicule appartient à un conducteur. '''
__tablename__ = 'vehicules'
immatriculation = db.Column(db.String, primary_key=True)
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
places = db.Column(db.Integer,
db.CheckConstraint('1 <= places And places <= 10'))
couleur = db.Column(db.String)
marque = db.Column(db.String)
animaux = db.Column(db.Boolean)
modele = db.Column(db.String)
american_express = db.Column(db.Boolean)
carte_bleue = db.Column(db.Boolean)
cheque = db.Column(db.Boolean)
anglais = db.Column(db.Boolean)
espagnol = db.Column(db.Boolean)
allemand = db.Column(db.Boolean)
vip = db.Column(db.Boolean)
attelage = db.Column(db.Boolean)
vbreak = db.Column(db.Boolean)
voiture_basse = db.Column(db.Boolean)
blacklist = db.Column(db.Boolean)
mineur = db.Column(db.Boolean)
class Parameter(BaseIsActiveMixin):
__table_args__ = (db.CheckConstraint(
"param_type IN ('VARCHAR','NUMBER','DATE')",
name="parameter_param_type_ck"), )
parameter_key = db.Column(db.String(50), primary_key=True, nullable=False)
parameter_desc = db.Column(db.String(200), nullable=False)
param_type = db.Column(db.String(20), nullable=False, default='VARCHAR')
varchar_value = db.Column(db.String(3000),
nullable=True,
default='VARCHAR')
number_value = db.Column(db.Float)
date_value = db.Column(db.DateTime)
def get_value(self):
value = None
if self.param_type == 'VARCHAR':
value = self.varchar_value
elif self.param_type == 'NUMBER':
value = self.number_value
elif self.param_type == 'DATE':
value = self.date_value
return value
def __repr__(self):
return "<Parameter parameter key: {}, param_type: {}, param value {}" \
.format(self.parameter_key, self.param_type, self.get_value())
class Bot(SearchableMixin, BaseIsActiveMixin):
__searchable__ = ['name', 'description', 'user_id']
__table_args__ = (db.CheckConstraint("is_active IN ('V','F')",
name="bot_is_active_ck"), )
id = db.Column(db.Integer, primary_key=True, nullable=False)
name = db.Column(db.String(80), nullable=False)
description = db.Column(db.String(300))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
bot_secret = db.Column(db.String(100), nullable=False)
botJob = db.relationship('BotJob', backref='bot', lazy='dynamic')
task = db.relationship('BotTask', backref='bot', lazy='dynamic')
scheduled_job = db.relationship('BotScheduledJob',
backref='bot',
lazy='dynamic')
def set_bot_secret(self, bot_secret):
self.bot_secret = encode(bot_secret)
@property
def is_connected(self):
return len(
db.session.query(BotHeartbeat.bot_id).filter(
BotHeartbeat.registered_at >=
datetime.utcnow() + timedelta(seconds=-31), BotHeartbeat.bot_id
== self.id).all()) > 0
@property
def total_of_tasks(self):
return db.session.query(db.func.count(BotTask.id)) \
.filter(BotTask.bot_id == self.id
,BotTask.is_active == IsActive.V).scalar()
@property
def total_of_scheduled(self):
return db.session.query(db.func.count(BotScheduledJob.id)) \
.filter(BotScheduledJob.bot_id == self.id
,BotScheduledJob.is_active == IsActive.V).scalar()
def get_tasks(self, page):
bot_tasks = BotTask.query.filter(
BotTask.bot_id == self.id,
BotTask.is_active == IsActive.V).subquery()
tasks = db.session.query(Task).filter(Task.is_active == IsActive.V) \
.join(bot_tasks, bot_tasks.c.task_id == Task.id)\
.order_by(Task.id.desc())
return tasks.paginate(page=page,
per_page=current_app.config['TASK_PER_PAGE'])
def delete(self):
super().delete()
jobs = BotScheduledJob.query.filter(
BotScheduledJob.bot_id == self.id).all()
for job in jobs:
job.delete()
def __repr__(self):
return '<Bot name: {}, is_active: {}>'.format(self.name,
self.is_active)
class Tlahtolli(PaginatedAPIMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
word = db.Column(db.String(120), index=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
definition = db.Column(db.String(120))
state = db.Column(
db.String(10),
db.CheckConstraint("state in ['known','tlahtolli','ignore']"))
def to_dict(self):
data = {
'id': self.id,
'word': self.word,
'user_id': self.user_id,
'definition': self.definition,
'state': self.state
}
return data
def from_dict(self, data):
for field in ['word', 'user_id', 'definition', 'state']:
if field in data:
setattr(self, field, data[field])
def __repr__(self):
return '<{} {}>'.format(
self.word,
User.query.filter_by(id=self.user_id).first().username)
class Review(db.Model):
id = db.Column(db.Integer, primary_key=True)
reviewer_id = db.Column(db.Integer, db.ForeignKey('user.id'))
lesson_id = db.Column(db.Integer, db.ForeignKey('lesson.id'))
rating = db.Column(db.Integer, db.CheckConstraint("state in [1,-1]"))
def to_dict(self):
data = {
'id': self.id,
'reviewer_id': self.reviewer_id,
'lesson_id': self.lesson_id,
'rating': self.rating
}
def from_dict(self, data):
for field in ['reviewer_id', 'lesson_id', 'rating']:
if field in data:
setattr(self, field, data[field])
def __repr__(self):
options = {1: 'likes', -1: 'dislikes'}
return '<{} {} {}>'.format(
User.query.filter_by(id=self.reviewer_id).first().username,
options[self.rating],
Lesson.query.filter_by(id=self.lesson_id).first().title)
class Product(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.Unicode(100))
description = db.Column(db.UnicodeText)
price = db.Column(db.DECIMAL(precision=2), db.CheckConstraint('price>0'))
availability = db.Column(
'availability',
db.Integer,
db.CheckConstraint('availability>=0'),
default=0,
)
img_file_path = db.Column(db.Unicode(128), nullable=True)
img_url = db.Column(db.Unicode(200))
def __repr__(self):
return '{0} {1:.2f}$'.format(self.title, self.price)
class CissViewContasReceber(db.Model):
"""
Representa a View CONTAS_RECEBER_SALDOS_VIEW
do Ciss
"""
__bind_key__ = 'ciss'
__tablename__ = 'CONTAS_RECEBER_SALDOS_VIEW'
idempresa = db.Column(db.Integer, primary_key=True)
idtitulo = db.Column(db.Integer, primary_key=True)
digitotitulo = db.Column(db.Integer, primary_key=True)
origemmovimento = db.Column(db.String(5))
dtmovimento = db.Column(db.Date())
dtvencimento = db.Column(db.Date())
valliquidotitulo = db.Column(db.Numeric(12, 3))
valtitulo = db.Column(db.Numeric(12, 3))
flagbaixada = db.Column(
db.String(1), db.CheckConstraint("flagbaixada='T' or flagbaixada='F'"))
idclifor = db.Column(db.Integer,
db.ForeignKey('CLIENTE_FORNECEDOR.idclifor'),
primary_key=True)
idrecebimento = db.Column(db.Integer,
db.ForeignKey('FORMA_PAGREC.idrecebimento'))
# relacionamento que possui o cliente da relação com CLIENTE_FORNECEDOR
cliente = db.relationship('CissClienteFornecedor')
class Ratings(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
series_id = db.Column(db.Integer, db.ForeignKey('series.id'), index=True)
source_ip = db.Column(db.Text, index=True)
rating = db.Column(db.Float(), default=-1)
__table_args__ = (
UniqueConstraint('user_id', 'source_ip', 'series_id'),
db.CheckConstraint('rating >= 0', name='rating_min'),
db.CheckConstraint('rating <= 10', name='rating_max'),
db.CheckConstraint('''(user_id IS NOT NULL AND source_ip IS NULL) OR (user_id IS NULL AND source_ip IS NOT NULL)''', name='rating_src'),
)
series_row = relationship("Series", backref='Ratings')
class Assistantship(db.Model):
sid = db.Column(db.Integer,primary_key=True)
term = db.Column(db.String(2),db.CheckConstraint('term in (FA,SP,SU)'))
year = db.Column(db.Integer)
amount = db.Column(db.Integer)
def __repr__(self):
return '<Assistantship {}>'.format(self.sid)
class Station(db.Model):
''' Une station contenant des taxis. '''
__tablename__ = 'stations'
nom = db.Column(db.String, primary_key=True)
adresse = db.Column(db.Integer, db.ForeignKey('adresses.identifiant'))
distance = db.Column(db.Float, db.CheckConstraint('0 <= distance'))
secteur = db.Column(db.String, db.ForeignKey('secteurs.nom'))
class Cart(db.Model):
cid = db.Column(db.Integer,
db.ForeignKey('consumer.cid'),
primary_key=True)
item_id = db.Column(db.Integer,
db.ForeignKey('item.item_id'),
primary_key=True)
quantity = db.Column(db.Integer, nullable=False)
db.CheckConstraint('quantity>0', 'check1')
class Student(db.Model):
sid = db.Column(db.Integer,db.Sequence('student_sid_seq'),db.CheckConstraint('sid > 999 and sid<10000'),unique=True)
email = db.Column(db.String(40),primary_key=True)
password = db.Column(db.String(128),nullable=False)
fname = db.Column(db.String(20),nullable=False)
lname = db.Column(db.String(20),nullable=False)
address1 = db.Column(db.String(40))
address2 = db.Column(db.String(40))
city = db.Column(db.String(40))
state = db.Column(db.String(40))
zip = db.Column(db.Integer)
sType = db.Column(db.String(5),db.CheckConstraint('sType in (MS,phD,UGRAD)'))
majorDept = db.Column(db.String(4),db.CheckConstraint('majorDept in (CSC,MATH,POLS,HIST)'))
gradAssistant = db.Column(db.String(1),db.CheckConstraint('gradAssistant in (Y,N)'))
student = db.relationship('Enroll',backref='student',lazy='dynamic')
def __repr__(self):
return '<Student {}>'.format(self.email)
class Program(db.Model):
university = db.Column(db.String(40),nullable=False)
dname = db.Column(db.String(40),primary_key=True)
program = db.Column(db.String(10), db.CheckConstraint('program in (MS,PhD)'),primary_key=True)
department_dname = db.Column(db.String(40), db.ForeignKey('department.dname'))
applications = db.relationship('Application',backref='application',lazy='dynamic')
def __repr__(self):
return '<Program {}>'.format(self.program)
class AllowanceDays(db.Model):
id = db.Column(db.Integer, primary_key=True)
payout_day = db.Column(db.Integer, )
allowance_id = db.Column(db.Integer, db.ForeignKey('allowance.id'))
__table_args__ = (db.UniqueConstraint('id',
'payout_day',
name='_allowance'),
db.CheckConstraint('payout_day < 29',
name='cont_payout_day'))
class Vehicule(db.Model):
''' Une vehicule appartient à un conducteur. '''
__tablename__ = 'vehicules'
immatriculation = db.Column(db.String, primary_key=True)
conducteur = db.Column(db.String, db.ForeignKey('conducteurs.telephone'))
places = db.Column(db.Integer, db.CheckConstraint('1 <= places'))
couleur = db.Column(db.String)
marque = db.Column(db.String)
class Ratings(db.Model):
__tablename__ = "ratings"
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
book_id = db.Column(db.Integer, db.ForeignKey('book.id'), primary_key=True)
book = db.relationship("Book", back_populates="user_ratings")
user = db.relationship("User", back_populates="book_ratings")
rating = db.Column(db.SmallInteger, nullable=False)
notes = db.Column(db.Text)
__table_args__ = (db.CheckConstraint('rating BETWEEN 0 AND 10',
name='valid_rating_check'), )
class CardInfo(db.Model):
cardId = db.Column(db.Integer, primary_key=True, autoincrement=True)
cardNumber = db.Column(db.String(12), unique=True)
userId = db.Column(db.Integer, db.ForeignKey('users.userId'))
expMonth = db.Column(db.Integer, db.CheckConstraint('expMonth > 0 and expMonth <13'), nullable=False)
expYear = db.Column(db.Integer, nullable=False)
nameOnCard = db.Column(db.String(100), nullable=False)
def __repr__(self):
return "<Card {}>".format(self.cardNumber)
class Section(db.Model):
term = db.Column(db.String(2),db.CheckConstraint('term in (FA,SP,SU)'))
year = db.Column(db.Integer)
crn = db.Column(db.Integer,primary_key=True)
cprefix = db.Column(db.String(4))
cno = db.Column(db.Integer)
section = db.Column(db.Integer)
days = db.Column(db.String(6))
starttime = db.Column(db.String(5))
endtime = db.Column(db.String(5))
room = db.Column(db.String(10))
cap = db.Column(db.Integer)
instructor = db.Column(db.String(30))
auth = db.Column(db.String(1),db.CheckConstraint('auth in (Y,N)'))
course_cpcrn = db.Column(db.Integer,db.ForeignKey('course.cno'))
section = db.relationship('Enroll',backref='section',lazy='dynamic')
def __repr__(self):
return '<Section {}>'.format(self.crn)
class Ratings(db.Model, RatingBase, ModificationInfoMixin):
__tablename__ = 'ratings'
__table_args__ = (
db.UniqueConstraint('story', 'nickname'),
db.CheckConstraint('rating >= 0', name='overall'),
db.CheckConstraint('rating >= 0', name='be_ctnt'),
db.CheckConstraint('rating >= 0', name='chars_ctnt'),
db.CheckConstraint('rating >= 0', name='technical'),
db.CheckConstraint('rating <= 10', name='overall'),
db.CheckConstraint('rating <= 10', name='be_ctnt'),
db.CheckConstraint('rating <= 10', name='chars_ctnt'),
db.CheckConstraint('rating <= 10', name='technical'),
)
class Participant(db.Model):
id = db.Column(db.Integer, primary_key=True)
tournament_id = db.Column(db.Integer, db.ForeignKey('tournament.id'))
player_id = db.Column(db.Integer, db.ForeignKey('player.id'))
deck_id = db.Column(db.Integer, db.ForeignKey('deck.id'))
player2_id = db.Column(db.Integer, db.ForeignKey('player.id'))
deck2_id = db.Column(db.Integer, db.ForeignKey('deck.id'))
__table_args__ = (db.UniqueConstraint('player_id',
'player2_id',
'tournament_id',
name='uix_players_tournament'),
db.UniqueConstraint('deck_id',
'deck2_id',
'tournament_id',
name='uix_decks_tournament'),
db.CheckConstraint('player_id <> player2_id',
name='ck_players'),
db.CheckConstraint('deck_id <> deck2_id',
name='ck_decks'))
