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