class Tag(db.Model): __tablename__ = "tags" tag_id = db.Column(db.Integer, autoincrement=True, primary_key=True) gratitude_id = db.Column(db.Integer, db.ForeignKey('gratitudes.gratitude_id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False) gratitude = db.relationship('Gratitude', backref='tags') user = db.relationship('User', backref="tags") @classmethod def add_new_tag(cls, gratitude_id, user_id): """Adds new tag to the db. """ tag = Tag(gratitude_id=gratitude_id, user_id=user_id) db.session.add(tag) db.session.commit() return tag
class Feedback(db.Model): """.""" __tablename__ = "feedback" feedback_id = db.Column(db.Integer, autoincrement=True, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False) email = db.Column(db.String(255), nullable=False) addressed = db.Column(db.Boolean, default=False, nullable=False) category_id = db.Column(db.Integer, db.ForeignKey('feedback_categories.category_id'), nullable=False) new_vendor_name = db.Column(db.String(32)) new_program_name = db.Column(db.String(32)) outgoing_name = db.Column(db.String(32)) receiving_name = db.Column(db.String(32)) new_numerator = db.Column(db.Integer) new_denominator = db.Column(db.Integer) feedback = db.Column(db.Text, nullable=False) user = db.relationship('User', backref=db.backref('feedback')) user = db.relationship('FeedbackCategory', backref=db.backref('feedback')) def __repr__(self): """Provide helpful representation when printed.""" return "<from {}: {}>".format( self.email, self.feedback, )
class Attraction(db.Model): __tablename__ = 'attractions' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) category = db.Column(db.String(255)) description = db.Column(db.Text) address = db.Column(db.Text) transport = db.Column(db.Text) mrt = db.Column(db.String(255)) latitude = db.Column(db.Float) longitude = db.Column(db.Float) images = db.Column(db.Text) db_attraction_booking = db.relationship("Booking", backref="attractions", lazy='dynamic') db_attraction_collection = db.relationship("Collection", backref="attractions", lazy='dynamic') def __init__(self, name, category, description, address, transport, mrt, latitude, longitude, images): self.name = name self.category = category self.description = description self.address = address self.transport = transport self.mrt = mrt self.latitude = latitude self.longitude = longitude self.images = images
class TransactionHistory(db.Model): """Transactions that impact program balance.""" __tablename__ = "transaction_history" transaction_id = db.Column(db.Integer, autoincrement=True, primary_key=True) action_id = db.Column(db.Integer, db.ForeignKey('actions.action_id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False) program_id = db.Column(db.Integer, db.ForeignKey('programs.program_id'), nullable=False) beginning_balance = db.Column(db.Integer, nullable=False) ending_balance = db.Column(db.Integer, nullable=False) created_at = db.Column(db.DateTime, default=db.func.current_timestamp()) user = db.relationship('User', backref=db.backref('transaction_history')) program = db.relationship('Program', backref=db.backref('transaction_history')) action = db.relationship('Action', backref=db.backref('transaction_history')) def __repr__(self): """Provide helpful representation when printed.""" return "<{} |user: {} |program: {} | beg: {} end: {} | {}>".format(self.transaction_id, self.user_id, self.program_id, self.beginning_balance, self.ending_balance, self.created_at, )
class Comment(db.Model): """Class for user comments on gratitudes.""" __tablename__ = "comments" comment_id = db.Column(db.Integer, autoincrement=True, primary_key=True) text = db.Column(db.Text, nullable=False) gratitude_id = db.Column(db.Integer, db.ForeignKey('gratitudes.gratitude_id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False) gratitude = db.relationship('Gratitude', backref='comments') user = db.relationship('User', backref='users') @classmethod def add_new_comment(cls, text, gratitude_id, user_id): """Adds new comment to the db.""" comment = Comment(text=text, gratitude_id=gratitude_id, user_id=user_id) db.session.add(comment) db.session.commit() return comment
class Question(Base): text = db.Column(db.String(128), nullable=False) issue_id = db.Column(db.Integer, db.ForeignKey('issue.id'), nullable=True) answer_id = db.Column(db.Integer, db.ForeignKey('answer.id'), nullable=True) options = db.relationship("Option") responses = db.relationship("Response")
class Issue(Base): title = db.Column(db.String(128), nullable=False) description = db.Column(db.String(500), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=True) questions = db.relationship("Question") votes = db.relationship("Vote")
def seed_flights(): for line in open("dropflightstable.csv"): line.strip("\n") print line.split('"') # 123,,JFK,SEA,B6,63,"2015-09-02 09:01","2015-09-02 12:11",SEA,JFK,B6,464,"2015-09-16 13:19","2015-09-16 21:43",346,54,401,, flight_id = db.Column(db.Integer, autoincrement=True, primary_key=True, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=True) outbound_city_origin = db.Column(db.String(75), nullable=False) outbound_city_final_destination = db.Column(db.String(75), nullable=False) outbound_airline_code = db.Column(db.String(75), nullable=False) outbound_airline_name = db.Column(db.String(75), nullable=False) outbound_flight_number = db.Column(db.Integer, nullable=False) outbound_datetime_departure = db.Column(db.String(75), nullable=False) outbound_datetime_arrival = db.Column(db.String(75), nullable=False) inbound_city_origin = db.Column(db.String(75), nullable=False) inbound_city_final_destination = db.Column(db.String(75), nullable=False) inbound_airline_code = db.Column(db.String(75), nullable=False) inbound_airline_name = db.Column(db.String(75), nullable=False) inbound_flight_number = db.Column(db.Integer, nullable=False) inbound_datetime_departure = db.Column(db.String(75), nullable=False) inbound_datetime_arrival = db.Column(db.String(75), nullable=False) outbound_destination_city_airport = db.Column(db.String(75), nullable=True) inbound_destination_city_airport = db.Column(db.String(75), nullable=True) outbound_city_airport = db.Column(db.String(75), nullable=True) inbound_city_airport = db.Column(db.String(75), nullable=True) base_fare = db.Column(db.Integer, nullable=False) taxes = db.Column(db.Integer, nullable=False) total_fare = db.Column(db.Integer, nullable=False) user = db.relationship('User', backref=db.backref('flights', order_by=user_id))
class Gratitude(db.Model): """Creating class for gratitude entries.""" __tablename__ = "gratitudes" gratitude_id = db.Column(db.Integer, autoincrement=True, primary_key=True) text = db.Column(db.Text, nullable=False) entry_id = db.Column(db.Integer, db.ForeignKey('entries.entry_id'), nullable=False) entry = db.relationship('Entry', backref='gratitudes') @classmethod def add_new_gratitude(cls, text, entry_id): """Adds new gratitude to the db. """ gratitude = Gratitude(text=text, entry_id=entry_id) db.session.add(gratitude) db.session.commit() return gratitude @classmethod def get_gratitudes_by_entry(cls, entry_id): """Returns all gratitudes associated with a given entry.""" return Gratitude.query.filter_by(entry_id=entry_id).all()
class RPGManagerCampaign(db.Model): __tablename__ = 'rpg_manager_campaign' id = db.Column(db.Integer(), primary_key=True) room_code = db.Column(db.String(4), nullable=False, unique=True) players = db.relationship('RPGManagerCharacter', backref='campaign', order_by="RPGManagerCharacter.id.desc()") name = db.Column(db.String(100)) region = db.Column(db.String(100)) day = db.Column(db.Integer()) party_level = db.Column(db.Integer()) money_ratio = db.Column(db.Integer(), default=10) notes = db.Column(db.String(3000)) created_time = db.Column(db.TIMESTAMP(timezone=True), nullable=False) last_read_time = db.Column(db.TIMESTAMP(timezone=True), nullable=False) def row_as_dict(self): return { "id": self.room_code, "name": self.name, "region": self.region, "day": self.day, "party_level": self.party_level, "money_ratio": self.money_ratio, "notes": self.notes, "characters": [player.row_as_dict() for player in self.players] } def __repr__(self): return "RPGManagerCampaign(id={}, room={}, name={})".format( self.id, self.room_code, self.name)
class Note(db.Model): id = db.Column(db.Integer, primary_key=True) text = db.Column(db.String(60), nullable=False) notebook_id = db.Column(db.Integer, db.ForeignKey("notebook.id"), nullable=False) notebook = db.relationship("Notebook", back_populates="notes")
class ItemAddedTrackerEntry(TrackerEntry): """ TrackerEntry that indicates that a new item has been added to a survey. """ id = db.Column(db.Integer, db.ForeignKey(TrackerEntry.id, ondelete='CASCADE'), primary_key=True) # polymorphic config __tablename__ = 'item_added_tracker_entry' __mapper_args__ = {'polymorphic_identity': __tablename__} # columns parent_item_name = db.Column(db.String(50)) added_item_name = db.Column(db.String(50)) # foreign keys parent_item_id = db.Column( db.Integer, db.ForeignKey('survey_base.id', onupdate='CASCADE', ondelete='CASCADE')) added_item_id = db.Column( db.Integer, db.ForeignKey('survey_base.id', onupdate='CASCADE', ondelete='CASCADE')) # relationships parent_item = db.relationship('SurveyBase', uselist=False, foreign_keys=[parent_item_id], backref=backref( 'item_added_parent_tracker_entries', cascade='all, delete')) added_item = db.relationship('SurveyBase', uselist=False, foreign_keys=[added_item_id], backref=backref('item_added_tracker_entries', cascade='all, delete')) def __init__(self, dataclient, parent_item_name: str, parent_item: 'SurveyBase', added_item_name: str, added_item: 'SurveyBase', **kwargs): super(ItemAddedTrackerEntry, self).__init__(dataclient, **kwargs) self.parent_item_name = ellipse(parent_item_name, 50) self.added_item_name = ellipse(added_item_name, 50) self.parent_item = parent_item self.added_item = added_item
class Team(db.Model): """ a team """ id = db.Column(db.Integer, primary_key=True) owner_id = db.Column(db.Integer, db.ForeignKey('user.id')) name = db.Column(db.String(), nullable=False, unique=True) color = db.Column(db.Integer, nullable=False) owner = db.relationship('User', foreign_keys=owner_id) # l'objet users utilise la reference owner_id alors qu'il ne devrait pas!! qrs = db.relationship('FoundQR', backref='team', cascade='all, delete') pictures = db.relationship('Picture', backref='team', cascade='all, delete') def __repr__(self): return f'<Team {self.name}, owner {self.owner.name}, color {self.color}>'
class Settlement(db.Model, TimestampMixin): __tablename__ = 'settlement' __table_args__ = {'sqlite_autoincrement': True} id = db.Column(db.BigInteger, primary_key=True) gift_code_id = db.Column(db.BigInteger, db.ForeignKey('gift_code.id'), nullable=False) transition = db.relationship('Transition')
class Book(db.Model): book_id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String, nullable=False) publish_date = db.Column(db.Date, nullable=False) subject = db.Column(db.String, nullable=False) genre = db.Column(db.String, nullable=False) notes = db.relationship('Note', backref=db.backref('book')) authors = db.relationship('Author', secondary=authorship_table, backref=db.backref('books', lazy='dynamic')) copies = db.relationship('BookCopy', cascade="all,delete", backref=db.backref('book')) def __repr__(self): return"<Book(book_id='%s',title='%s',publish_date='%s',subject='%s',genre='%s'," \ "book_note='%s',authors='%s',copies='%s'>" \ % (self.book_id, self.title, self.publish_date, self.subject, self.genre, self.notes, self.authors, self.copies) def to_dict(self): """ Method to transform Book to a dictionary object. :return: """ print('Book to_dict') book_dict = { 'book_id': self.book_id, 'title': self.title, 'publish_date': self.publish_date, 'subject': self.subject, 'genre': self.genre, 'notes': self.notes, 'authors': self.authors, 'copies': self.copies } return book_dict def update(self, **kwargs): """ Method to update a Book's attributes :param kwargs: Given a dictionary of valid key value pairs :return: None """ print('Book.update()') for key, value in kwargs.items(): setattr(self, key, value)
class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255), nullable=False) email = db.Column(db.String(255,collation='binary'), nullable=False) password = db.Column(db.String(255,collation='binary'), nullable=False) time = db.Column(db.DateTime) db_user_booking = db.relationship("Booking", backref="users",lazy='dynamic') db_user_collection = db.relationship("Collection", backref="users", lazy='dynamic') def __init__(self,name,email,password,time): self.name = name self.email = email self.password = password self.time = time
class OAuth2AuthorizationCode(db.Model, SerializerMixin, OAuth2AuthorizationCodeMixin): __tablename__ = "oauth2_code" id = db.Column(db.String, primary_key=True) user_id = db.Column(db.String, db.ForeignKey("users.id", ondelete="CASCADE")) user = db.relationship("User")
class Product(db.Model, TimestampMixin): __tablename__ = 'product' __table_args__ = {'sqlite_autoincrement': True} id = db.Column(db.BigInteger, primary_key=True) name = db.Column(db.String(255), nullable=False) price = db.Column(db.Integer, nullable=False) overview = db.Column(db.String(255), nullable=False) site_url = db.Column(db.String(255), nullable=False) image_url = db.Column(db.String(255), nullable=False) transition = db.relationship('Transition')
class FoundQR(db.Model): """ a qrcode found by a team """ team_id = db.Column(db.Integer, db.ForeignKey('team.id'), primary_key=True) qr_id = db.Column(db.String, db.ForeignKey('qr_code.id'), primary_key=True) qr = db.relationship('QRCode', backref=db.backref('teams', cascade='all, delete')) def __repr__(self): return f'<FoundQR {self.team.name} found {self.qr}>'
class Ratio(db.Model): """Transfer ratios between two programs.""" __tablename__ = "ratios" __table_args__ = (UniqueConstraint('outgoing_program', 'receiving_program', name='directed_flow'), ) ratio_id = db.Column(db.Integer, autoincrement=True, primary_key=True) outgoing_program = db.Column(db.Integer, db.ForeignKey('programs.program_id'), nullable=False) receiving_program = db.Column(db.Integer, db.ForeignKey('programs.program_id'), nullable=False) numerator = db.Column(db.Numeric, nullable=False) denominator = db.Column(db.Numeric, nullable=False) outgoing = db.relationship( "Program", primaryjoin="Ratio.outgoing_program==Program.program_id") receiving = db.relationship( "Program", primaryjoin="Ratio.receiving_program==Program.program_id") def __repr__(self): """Provide helpful representation when printed.""" return "<ratio of [program {}] {} to [program {}] {} = {} / {}>".format( self.outgoing_program, self.outgoing.program_name, self.receiving_program, self.receiving.program_name, self.numerator, self.denominator, ) def ratio_to(self): """Provide numerical ratio of numerator / denominator""" ratio = float(self.numerator) / float(self.denominator) return ratio
class GiftCode(db.Model, TimestampMixin): __tablename__ = 'gift_code' __table_args__ = {'sqlite_autoincrement': True} # ToDo: sqliteとMySQLの両方に対応させる # sqliteだとBIGINTでautoincrementが使用できないので対応する # https://stackoverflow.com/questions/18835740/does-bigint-auto-increment-work-for-sqlalchemy-with-sqlite id = db.Column(db.BigInteger, primary_key=True, autoincrement=True) code = db.Column(db.String(16), unique=True, nullable=False) balance = db.Column(db.Integer, nullable=False) # 現状はUTCで保存されるはず expiration_date = db.Column(db.DateTime, nullable=False) settlement = db.relationship('Settlement')
class Sightseeing(db.Model): __tablename__ = 'Sightseeing' id = db.Column(db.Integer) name = db.Column(db.String(50), unique=True, nullable=False) category = db.Column(db.String(50), nullable=False) description = db.Column(db.Text) address = db.Column(db.Text) transport = db.Column(db.Text) mrt = db.Column(db.String(255)) latitude = db.Column(db.Float, nullable=False) longitude = db.Column(db.Float, nullable=False) images = db.Column(db.Integer, primary_key=True) db_sightseeing_img = db.relationship("Images", backref="Sightseeing")
class ShadowDimension(Dimension): id = db.Column(db.Integer, db.ForeignKey(Dimension.id), primary_key=True) __tablename__ = 'shadow_dimension' __mapper_args__ = {'polymorphic_identity': __tablename__} _referenced_object_id = db.Column(db.Integer, db.ForeignKey(ConcreteDimension.id)) _referenced_object = db.relationship(ConcreteDimension, foreign_keys=[_referenced_object_id], backref='copies') shadow = True def __init__(self, dimension, **kwargs): self._referenced_object = dimension super(ShadowDimension, self).__init__(**kwargs) for question in dimension.questions: if not isinstance(question, ConcreteQuestion): question = question.concrete s_question = ShadowQuestion(question) self.questions.append(s_question) @property def concrete_id(self): if self._referenced_object is None: return None return self._referenced_object.id @property def concrete(self): if self._referenced_object is None: return None return self._referenced_object @property def reference_id(self): return self._referenced_object.reference_id @property def original_language(self) -> BabelLanguage: return self._referenced_object.original_language @property def name(self) -> str: return self._referenced_object.name @property def name_translations(self) -> Dict[str, str]: return self._referenced_object.name_translations
class OAuth2Token(db.Model, OAuth2TokenMixin, SerializerMixin): __tablename__ = 'oauth2_token' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.String, db.ForeignKey('users.id', ondelete='CASCADE')) user = db.relationship('User') def is_refresh_token_active(self): if self.revoked: return False expires_at = self.issued_at + self.expires_in * 2 return expires_at >= time.time()
class User(db.Model): """ a user """ id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(), nullable=False, unique=True) team_id = db.Column(db.Integer, db.ForeignKey('team.id')) team = db.relationship('Team', backref=db.backref('users', uselist=True), uselist=False, foreign_keys=team_id, post_update=True) def __repr__(self): return f'<User {self.name} in {self.team.name if self.team else None}>'
class Role(db.Model): """ Create a Role table """ __tablename__ = 'roles' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(60), unique=True) description = db.Column(db.String(200)) employees = db.relationship('Employee', backref='role', lazy='dynamic') def __repr__(self): return '<Role: {}>'.format(self.name)
class Balance(db.Model): """Balance a user has at program.""" __tablename__ = "balances" balance_id = db.Column(db.Integer, autoincrement=True, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False) action_id = db.Column(db.Integer, db.ForeignKey('actions.action_id'), nullable=False) program_id = db.Column(db.Integer, db.ForeignKey('programs.program_id'), nullable=False) updated_at = db.Column(db.DateTime, default=db.func.current_timestamp()) current_balance = db.Column(db.Integer, nullable=False) user = db.relationship('User', backref=db.backref('balances', order_by=program_id)) program = db.relationship('Program', backref=db.backref('balances', order_by=balance_id)) action = db.relationship('Action', backref=db.backref('balances', order_by=balance_id)) def __repr__(self): """Provide helpful representation when printed.""" return "<{} |user: {} |program: {} | balance: {} | {}>".format(self.balance_id, self.user_id, self.program.program_name, self.current_balance, self.updated_at, ) def transferred_to(self, amount, ratio): """Update balance to receiving program balance after transfer.""" self.current_balance = self.current_balance + amount * ratio self.action_id = Action.query.filter(Action.action_type == 'Transfer').one().action_id def transferred_from(self, amount): """Update balance to outgoing program balance after transfer.""" self.current_balance = self.current_balance - amount self.action_id = Action.query.filter(Action.action_type == 'Transfer').one().action_id
class Transfer(db.Model): """Transfers between two programs.""" __tablename__ = "transfers" transfer_id = db.Column(db.Integer, autoincrement=True, primary_key=True) transferred_at = db.Column(db.DateTime, default=db.func.current_timestamp()) user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False) outgoing_program = db.Column(db.Integer, db.ForeignKey('programs.program_id'), nullable=False) receiving_program = db.Column(db.Integer, db.ForeignKey('programs.program_id'), nullable=False) outgoing_amount = db.Column(db.Integer, nullable=False) outgoing = db.relationship( "Program", primaryjoin="Transfer.outgoing_program==Program.program_id") receiving = db.relationship( "Program", primaryjoin="Transfer.receiving_program==Program.program_id") def __repr__(self): """Provide helpful representation when printed.""" return "<{} |user: {} |program: {} to program: {}| amount: {} | {}>".format( self.transfer_id, self.user_id, self.outgoing_program, self.receiving_program, self.outgoing_amount, self.transferred_at, )
class Picture(db.Model): team_id = db.Column(db.Integer, db.ForeignKey('team.id'), primary_key=True) objective_id = db.Column(db.Integer, db.ForeignKey('objective.id'), primary_key=True) filename = db.Column(db.String, nullable=False) status = db.Column(db.Integer, nullable=False) objective = db.relationship('Objective', backref=db.backref('teams', cascade='all, delete')) def __repr__(self): return f'<Picture for {self.objective} by {self.team.name}, status={self.status}>'
class PropertyUpdatedTrackerEntry(TrackerEntry): """ Stores the old and new values for a property of a certain survey item. Also stores a href to that item as well as the name of the item. """ id = db.Column(db.Integer, db.ForeignKey(TrackerEntry.id, ondelete='CASCADE'), primary_key=True) # polymorphic config __tablename__ = 'property_updated_tracker_entry' __mapper_args__ = {'polymorphic_identity': __tablename__} # columns item_name = db.Column(db.String(50)) property_name = db.Column(db.String(50)) _values = db.Column(MUTABLE_HSTORE) # foreign keys item_id = db.Column( db.Integer, db.ForeignKey('survey_base.id', onupdate='CASCADE', ondelete='CASCADE')) # relationships item = db.relationship('SurveyBase', uselist=False, foreign_keys=[item_id], backref=backref('property_updated_tracker_entries', cascade='all, delete')) def __init__(self, dataclient: DataClient, item_name: str, item: 'SurveyBase', property_name, previous_value, new_value, **kwargs): super(PropertyUpdatedTrackerEntry, self).__init__(dataclient, **kwargs) self.item_name = ellipse(item_name, 50) self.item = item self.property_name = ellipse(property_name, 50) self._values = {'previous': str(previous_value), 'new': str(new_value)} @property def previous_value(self): return self._values['previous'] @property def new_value(self): return self._values['new']
class OAuth2Client(db.Model, OAuth2ClientMixin, SerializerMixin): __tablename__ = "oauth2_client" id = db.Column(db.String, primary_key=True) client_id = db.Column(db.String, unique=True) client_secret = db.Column(db.String, unique=True) client_id_issued_at = db.Column(db.Integer) user_id = db.Column(db.String, db.ForeignKey("users.id", ondelete="CASCADE")) user = db.relationship("User") def __init__(self, c_id, client_id, client_secret, client_id_issued_at, user_id): self.id = c_id self.client_id = client_id self.client_secret = client_secret self.client_id_issued_at = client_id_issued_at self.user_id = user_id