class Review(BaseModel): __tablename__ = 'reviews' id = db.Column(db.Integer, primary_key=True) book_id = db.Column(db.Integer, db.ForeignKey('books.id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) text = db.Column(db.String(120), nullable=False) rating = db.Column(db.Integer, nullable=False) __table_args__ = (db.UniqueConstraint('book_id', 'user_id', name="_book_user_uc"), ) def save(self): if not self.id: db.session.add(self) db.session.commit() def get_user(self, user_id): return User.get_by_id(user_id) @staticmethod def get_all(): return Review.query.all() def __repr__(self): return f"<Review {self.id}"
class Share(db.Model, Serializer): __private__ = ('id') id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) device_id = db.Column(db.Integer, db.ForeignKey('device.id', ondelete="CASCADE"), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) device = db.relationship("Device", back_populates="shares") user = db.relationship("User", back_populates="shares") paths = db.relationship("Path", back_populates="share") groups = db.relationship("Group", secondary=share_group_association, back_populates="shares") __table_args__ = (db.UniqueConstraint('name', 'user_id', name='shares_name_user_id_uc'), ) def __init__(self, name, user_id, device_id): self.name = name self.user_id = user_id self.device_id = device_id
class FundUser(db.Model): """ SQLAlchemy object :: `fund_users` table. Represents a lookup table for which users belong to what funds. A user can be a member of multiple funds. There exists a unique index on the combination of a user and a fund. Fields ------ id : int The primary key and fund user identifier. fund_id : int The fund identifier. user_id : int The user identifier. """ __tablename__ = "fund_users" __table_args__ = (db.UniqueConstraint('fund_id', 'user_id', name='unique_idx_fund_id_user_id'), ) id = db.Column(db.Integer, primary_key=True) fund_id = db.Column(db.Integer, db.ForeignKey("funds.id"), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) def __repr__(self): return f"<FundUser `{self.id}`>"
class Venue(db.Model): id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column( db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp(), ) name = db.Column(db.String(144), nullable=False) location = db.Column(db.String(144), nullable=False) events = db.relationship("Event", backref="event") __table_args__ = (db.UniqueConstraint("name", "location"),) def __init__(self, name, location): self.name = name self.location = location @staticmethod def venue_summary(): sql = text( """ WITH t2 AS ( SELECT venue.id, count(*) as events FROM venue LEFT JOIN event ON venue.id=event.venue_id WHERE event.end_time > date('now') GROUP BY venue.id ) SELECT venue.id, venue.name, venue.location, t2.events AS events FROM venue LEFT JOIN t2 ON venue.id=t2.id ORDER BY venue.name """ ) return db.engine.execute(sql)
class Group(db.Model, Serializer): __private__ = ('id') id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) user = db.relationship("User", back_populates="own_groups") members = db.relationship("User", secondary=user_group_association, back_populates="in_groups") shares = db.relationship("Share", secondary=share_group_association, back_populates="groups") __table_args__ = (db.UniqueConstraint('name', 'user_id', name='group_name_user_id_uc'), ) def __init__(self, name, user_id): self.name = name self.user_id = user_id
class Pair(db.Model): """ Contains a word1 (caller) and its partner, word2 - \n distinguished by sound1 (caller's sound) and sound2 """ __tablename__ = "pairs" __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True, autoincrement=True) word_id = db.Column(db.Integer, db.ForeignKey('words.id'), nullable=False) partner_id = db.Column( db.Integer, db.ForeignKey('words.id'), nullable=False) word_sound_id = db.Column( db.Integer, db.ForeignKey('sounds.id'), nullable=False) partner_sound_id = db.Column( db.Integer, db.ForeignKey('sounds.id'), nullable=False) # These sounds should be replaced word_sound = db.Column(db.String()) partner_sound = db.Column(db.String()) # Each pair has a word 1 (word) and a word 2 (partner) w1 = db.relationship("Word", primaryjoin="Pair.word_id==Word.id") w2 = db.relationship("Word", primaryjoin="Pair.partner_id==Word.id") s1 = db.relationship("Sound", primaryjoin="Pair.word_sound_id==Sound.id") s2 = db.relationship( "Sound", primaryjoin="Pair.partner_sound_id==Sound.id") # explicit/composite unique constraint. 'name' is optional. db.UniqueConstraint('word_id', 'partner_id', 'word_sound', 'sound1t') groups = db.relationship( "Group", secondary=group_pairs, back_populates="pairs") def __str__(self): string = "{}: {} / {} - ({} vs. {})".format(self.id, self.w1.word, self.w2.word, self.s1.sound, self.s2.sound) return string @classmethod def allPairCombinations(cls, wordSet): """ Takes a list of words and returns a list of all possible existing pairs between them""" pairList = [] for word in wordSet: for word2 in wordSet: pairs = word.getPairs(word2) if pairs: pairList.extend(pairs) return pairList # Some id is displayed even if pair is not committed yet def textify(self): string = "{}: {} / {} - ({} vs. {})".format(self.id, self.w1.word, self.w2.word, self.s1.sound, self.s2.sound) return string
class User(db.Model): __table_name__ = 'user' __table_args__ = (db.UniqueConstraint('google_id', 'en_name', name="unique_user_constraint"), ) id = db.Column(db.Integer, primary_key=True) google_id = db.Column(db.String(30), nullable=False, unique=True) ko_name = db.Column(db.String(10), default="None") en_name = db.Column(db.String(10), nullable=False) entry_date = db.Column(db.DateTime, nullable=False, default=datetime.datetime.today()) admin = db.Column(db.Integer, default=False) def __repr__(self): return "사용자 번호 : %r / Google ID : %r / 한글이름 : %r / 영어이름 : %r / 입사일 : %r / 관리자 여부 : %r" % ( self.id, self.google_id, self.ko_name, self.en_name, str(self.entry_date)[:10], bool(self.admin)) def get_id(self): return self.id def get_google_id(self): return self.google_id def get_en_name(self): return self.en_name
class CommentLike(db.Model): id = db.Column(db.Integer, primary_key=True) user_id = db.Column( db.Integer, db.ForeignKey('user.id', onupdate="CASCADE", ondelete="CASCADE")) user = db.relationship('User') liked_time = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now()) comment_id = db.Column( db.Integer, db.ForeignKey('comment.id', onupdate="CASCADE", ondelete="CASCADE")) comment = db.relationship('Comment', backref=db.backref('likes', cascade='all, delete-orphan', lazy='dynamic')) liked_time = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now()) __table_args__ = (db.UniqueConstraint('user_id', 'comment_id', name='unq_comment_like_uid_cid'), )
class ImageLike(db.Model): id = db.Column(db.Integer, primary_key=True) user_id = db.Column( db.Integer, db.ForeignKey('user.id', onupdate="CASCADE", ondelete="CASCADE")) user = db.relationship('User', backref=db.backref('liked_images', cascade='all, delete-orphan', lazy='dynamic')) liked_time = db.Column(db.DateTime, default=db.func.now()) place_image_id = db.Column( db.Integer, db.ForeignKey('place_image.id', onupdate="CASCADE", ondelete="CASCADE")) place_image = db.relationship('PlaceImage', backref=db.backref( 'liked_users', cascade='all, delete-orphan', lazy='dynamic')) __table_args__ = (db.UniqueConstraint('user_id', 'place_image_id', name='unq_image_like_uid_pid'), )
class SignaturesByConstituency(db.Model): __tablename__ = "signatures_by_constituency" __table_args__ = ( db.UniqueConstraint( "record_id", "ons_code", name="uniq_sig_constituency_for_record" ), ) CODE_CHOICES = CONSTITUENCIES CODE_LOOKUP = LazyDict({v: k for k, v in dict(CODE_CHOICES).items()}) id = db.Column(Integer, primary_key=True) record_id = db.Column(Integer, ForeignKey(Record.id, ondelete="CASCADE"), index=True, nullable=False) ons_code = db.Column(ChoiceType(CODE_CHOICES), index=True, nullable=False) count = db.Column(Integer, default=0) record = relationship(Record, back_populates="signatures_by_constituency") code = synonym("ons_code") @reconstructor def init_on_load(self): self.timestamp = self.record.timestamp @validates("ons_code") def validate_code_choice(self, key, value): return Record.validate_locale_choice(self, key, value) def __repr__(self): template = "<id: {}, code: {}, count: {}>" return template.format(self.id, self.code.code, self.count)
class Datastore(db.Model): __versioned__ = {} id = db.Column(db.Integer(), primary_key=True) key = db.Column(db.String(256), nullable=False) value = db.Column(db.LargeBinary, nullable=False) storage_spec = db.Column(db.String(256), nullable=False) timestamp = db.Column(db.DateTime, default=datetime.datetime.now) alias = db.Column(db.String(256), nullable=False) __table_args__ = (db.UniqueConstraint('key', 'user_id', name='_key_user_uc'), ) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) user = db.relationship('User') def __init__(self, key, value, storage_spec, user, alias): self.key = key self.set_value(value) self.storage_spec = storage_spec self.user = user self.alias = alias def set_value(self, value): self.value = value.encode() def get_value(self): return self.value.decode()
class CourseStudent(db.Model): id = db.Column(db.Integer, primary_key=True) course_id = db.Column(db.Integer, nullable=False) student_id = db.Column(db.Integer, nullable=False) __table_args__ = (db.UniqueConstraint('course_id', 'student_id', name='_student_course_uc'), ) def __init__(self, course_id, student_id): self.course_id = course_id self.student_id = student_id
class User(db.Model): """Provides a generalised User model for linking stored data. The expectation is that the User will be remotely authenticated against another API via some sort of credential that will be sent by the client via the headers during a request. If this class doesn't fulfill your requiremnets, for example: * When a local user is desirable * Missing a very important column that must absolutely be stored * Authenticating per-request is too expensive ..then building off of this User model through inheritence is recommended. This will allow for a more flexible approach while tailoring for your needs. """ id = db.Column(db.Integer, primary_key=True) domain = db.Column(db.String(192), nullable=False) remote_id = db.Column(db.String(128), nullable=False) __table_args__ = (db.UniqueConstraint('remote_id', 'domain', name='_remote_user_uc'), ) def __init__(self, remote_id, domain): """ Generate the User Args: remote_id: The user_id on the remote server domain: The domain identifier of the remote server """ self.remote_id = remote_id self.domain = domain def __repr__(self): """ Represent the user more helpfully Returns: String such as 'User id 30 (379273@alveo)' """ return 'User id %s (%s@%s)' % (self.id, self.remote_id, self.domain) def __str__(self): """ Represent the user more helpfully Returns: String such as 'User id 30 (379273@alveo)' """ return 'User id %s (%s@%s)' % (self.id, self.remote_id, self.domain)
class Device(db.Model, Serializer): __private__ = ('id', 'user_id', 'user') id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) address = db.Column(db.String(80), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) user = db.relationship("User", back_populates="devices") shares = db.relationship("Share", back_populates="device") __table_args__ = (db.UniqueConstraint('name', 'user_id', name='name_user_uc'), ) def __init__(self, name, address, user_id): self.name = name self.address = address self.user_id = user_id
class CourseResponsiblePerson(db.Model): __tablename__ = 'Course_Responsible_Person' id = db.Column(db.Integer, nullable = False, primary_key=True) course_id = db.Column(db.Integer(), db.ForeignKey('EducationalСourse.id'), nullable = False ) person_id = db.Column(db.Integer(), db.ForeignKey('students.id'), nullable = False ) is_active = db.Column(db.Integer ) created_on = db.Column(db.DateTime(), default=datetime.utcnow) updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow) __table_args__ = (db.UniqueConstraint('course_id', 'person_id', name='_course_person_uniq_const'),) def __init__(self, course_id, person_id): self.course_id = course_id self.person_id = person_id self.is_active = 1 def set_active_flg(self, is_active): self.is_active = is_active def __repr__(self): return "<CourseResponsiblePerson Info {}:{}:{}>".format(self.id, self.course_id, self.person_id)
class Path(db.Model, Serializer): __private__ = ('id') id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) path = db.Column(db.String(350), nullable=False) share_id = db.Column(db.Integer, db.ForeignKey('share.id', ondelete="CASCADE"), nullable=False) share = db.relationship("Share", back_populates="paths") __table_args__ = (db.UniqueConstraint('name', 'share_id', name='paths_name_share_id_uc'), ) def __init__(self, name, path, share_id): self.name = name self.path = path self.share_id = share_id
class StudentHometask(db.Model): __tablename__ = 'student_hometask' id = db.Column(db.Integer, nullable = False, primary_key=True) course_hometask_id = db.Column(db.Integer(), db.ForeignKey('course_hometask.id'), nullable = False ) student_id = db.Column(db.Integer(), db.ForeignKey('students.id'), nullable = False ) content = db.Column(db.String(100000), nullable = False) created_on = db.Column(db.DateTime(), default=datetime.utcnow) updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow) __table_args__ = (db.UniqueConstraint('course_hometask_id', 'student_id', name='_course_hometask_student_uniq_const'),) def __init__(self, course_hometask_id, student_id, content): self.course_hometask_id = course_hometask_id self.student_id = student_id self.content = content def set_content(self, content): self.content = content def __repr__(self): return "<StudentHometask Info {}:{}:{}>".format(self.id, self.student_id, self.course_hometask_id)
class ServiceAccount(db.Model): __tablename__ = 'users_service_account' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50)) password = db.Column(db.String(200)) oauth_token = db.Column(db.String(200)) oauth_secret = db.Column(db.String(200)) service = db.Column(db.SmallInteger, default=USER.LOCAL) __table_args__ = (db.UniqueConstraint('name', 'service', name='_name_service_uc'), ) def __init__(self, name=None, password=None, service=None): self.name = name self.password = password self.service = service def __repr__(self): return '<ServiceAccount %r %r>' % (self.name, self.service)
class Questions(db.Model): __table_args__ = (db.UniqueConstraint('user_id', 'question_id', name='unique_user_question'), ) # A record of the questions solved for every user. Filter by user id and question number. # Add in each question as it gets solved. solved_id = db.Column(db.Integer, primary_key=True) question_id = db.Column(db.Integer, db.ForeignKey('questionnames.question_id')) # Current time. Don't put parentheses - because we want to pass in the # function as the argument, and not the current time. date_solved = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) # The id of the user who authored the post. in the foreign key, we're # referencing the table/column name, so we use lowercase user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) def __repr__(self): # Since users will author posts, we need a one-to-many relationship. # One user = many posts. one post = one user only. return f"Questions('{self.user_id}', '{self.question_id}', '{self.date_solved}')"
class Video(db.Model): """ Main video model """ __tablename__ = 'mylust_video' __table_args__ = ( db.UniqueConstraint('masturbator', 'remote_id', name='masturbator_remote_id_uc'), ) id = db.Column(db.BigInteger, primary_key=True) title = db.Column(db.Unicode(256), index=True) duration = db.Column(db.Integer, default=0) masturbator = db.Column(db.String(32), index=True) remote_id = db.Column(db.BigInteger) remote_url = db.Column(db.String(256), index=True) remote_date = db.Column(db.DateTime(), nullable=True) import_date = db.Column(db.DateTime(), default=datetime.datetime.now, index=True) views = db.Column(db.BigInteger, default=0) tags = db.relationship('VideoTag', secondary=video_tags, backref=db.backref('videos', lazy='dynamic'), lazy='dynamic') thumbs = db.relationship('VideoThumb', backref=db.backref('video', cascade='all,delete'), lazy='dynamic') stars = db.relationship('VideoStar', secondary=video_stars, backref=db.backref('videos', lazy='dynamic'), lazy='dynamic') @property def slug(self): return slugify(self.title) @property def duration_formated(self): return time.strftime('%H:%M:%S', time.gmtime(self.duration)) def __repr__(self): return "<Video '{0}'>".format(self.remote_url)
class Author(Base): __tablename__ = "author" firstname = db.Column(db.String(30), nullable=False) lastname = db.Column(db.String(30), nullable=False) books_count = db.Column(db.Integer, nullable=False, default=0) __table_args__ = (db.UniqueConstraint('firstname', 'lastname', name='_author_name_uc'), ) books = db.relationship("Book", secondary=authors_books, backref=db.backref('books', lazy='dynamic')) def __init__(self, firstname, lastname): self.firstname = firstname self.lastname = lastname self.books_count = 0 @staticmethod def all_authors(): stmt = text("SELECT * FROM author") res = db.engine.execute(stmt) return res.fetchall() @staticmethod def all_author_by_bookcount(): stmt = text("SELECT * FROM author ORDER BY books_count DESC") res = db.engine.execute(stmt) return res @staticmethod def authors_books_by_author_name(firstname, lastname): stmt = text("SELECT a.id, a.firstname, a.lastname, ab.book_id, b.name FROM author a" " JOIN authors_books ab ON ab.author_id=a.id" " JOIN book b ON ab.book_id=b.id" " WHERE a.firstname=:firstname AND a.lastname=:lastname") \ .params(firstname=firstname, lastname=lastname) res = db.engine.execute(stmt) return res.fetchall() @staticmethod def check_if_author_in_database(firstname, lastname): authornamequery = text("SELECT COUNT(id) FROM author WHERE UPPER(firstname)=:firstname" " AND UPPER(lastname)=:lastname") \ .params(firstname=firstname.upper(), lastname=lastname.upper()) res = db.engine.execute(authornamequery) return res.fetchone()[0] @staticmethod def change_booknames_todelete(authorid): stmt = text("UPDATE book SET name = 'TO-DELETE!!'" " WHERE book.id IN (SELECT book_id FROM authors_books WHERE author_id = :authorid)") \ .params(authorid=authorid) db.engine.execute(stmt) db.session().commit() @staticmethod def delete_authorsbooks_connection(authorid): stmt = text("DELETE FROM authors_books WHERE author_id=:authorid" ).params(authorid=authorid) db.engine.execute(stmt) db.session().commit() @staticmethod def delete_author(firstname, lastname): stmt = text("DELETE FROM author WHERE firstname=:firstname AND lastname=:lastname") \ .params(firstname=firstname, lastname=lastname) db.engine.execute(stmt) db.session().commit() @staticmethod def update_authorinfo(firstname, lastname, authorid): stmt = text("UPDATE author SET firstname=:firstname, lastname=:lastname WHERE id=:authorid") \ .params(firstname=firstname, lastname=lastname, authorid=authorid) db.engine.execute(stmt) db.session().commit()
class Conversation(db.Model): __tablename__ = "Conversation" id = db.Column(db.Integer, primary_key=True, autoincrement=True) date_created = db.Column(db.DateTime, default=func.now(), server_default=func.now(), nullable=False) alpha = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) beta = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) __table_args__ = ( db.UniqueConstraint("alpha", "beta"), db.CheckConstraint("beta > alpha"), ) def __init__(self, alpha, beta): """An alpha is always less than beta, and never equal.""" self.alpha = alpha self.beta = beta @staticmethod def get_new_message_count(): return {"count": "123"} @staticmethod def find_my_conversations(): statement = text( 'SELECT "Conversation".id AS "conversation_id", "Conversation".date_created AS "conversation_started", ' '(SELECT LEFT(content, 32) FROM "Message" WHERE "Message".conversation_id = "Conversation".id ORDER BY date_created DESC LIMIT 1) AS "latest_message",' '(SELECT date_created FROM "Message" WHERE "Message".conversation_id = "Conversation".id ORDER BY date_created DESC LIMIT 1) AS "date_created",' '(SELECT handle FROM "Account" WHERE id=4) AS "alpha",' '(SELECT handle FROM "Account" WHERE id=3) AS "beta"' 'FROM "Conversation" WHERE alpha=:id OR beta=:id;') connection = db.engine.connect() result_set = connection.execute(statement, id=current_user.id) response = [] for row in result_set: alpha = row["alpha"] beta = row["beta"] if alpha == current_user.handle: other_profile = beta else: other_profile = alpha response.append({ "conversation_id": row["conversation_id"], "date_created": row["date_created"], "other_profile": other_profile, "latest_message": row["latest_message"] }) connection.close() return response @staticmethod def get_conversation(conversation_id): statement = text( 'SELECT id, date_created AS "sent", content, ' '(SELECT "Account".handle FROM "Account" WHERE "Account".id = "Message".source_id) AS "source", ' '(SELECT "Account".handle FROM "Account" WHERE "Account".id = "Message".target_id) AS "target"' 'FROM "Message" WHERE conversation_id = :conversation_id ORDER BY date_created ASC;' ) connection = db.engine.connect() result_set = connection.execute(statement, conversation_id=conversation_id) response = [] for row in result_set: if row["source"] == current_user.handle: own_message = True else: own_message = False response.append({ "id": row["id"], "sent": row["sent"], "content": row["content"], "own_message": own_message }) connection.close() return response @staticmethod def __get_date_string(datetime): return str(datetime.hour) + ":" + str(datetime.minute) + ":" + str(datetime.second) \ + " " + str(datetime.day) + "." + str(datetime.month) + "." + str(datetime.year)
from ipapy import is_valid_ipa word_grouping = db.Table('groupwords', db.Column('group_id', db.Integer, db.ForeignKey('groups.id')), db.Column('word_id', db.Integer, db.ForeignKey('words.id')) ) group_sounds = db.Table('groupsounds', db.Column('group_id', db.Integer, db.ForeignKey('groups.id')), db.Column('sound_id', db.Integer, db.ForeignKey('sounds.id')), db.UniqueConstraint('group_id', 'sound_id') ) group_pairs = db.Table('grouppairs', db.Column('group_id', db.Integer, db.ForeignKey('groups.id')), db.Column('pair_id', db.Integer, db.ForeignKey('pairs.id')) ) class Sound(db.Model): """ So far this table is made exclusively for association groups with all its sounds """ __tablename__ = "sounds" __table_args__ = {'extend_existing': True}
class Sound(db.Model): """ So far this table is made exclusively for association groups with all its sounds """ __tablename__ = "sounds" __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True, autoincrement=True) sound = db.Column(db.String(), nullable=False) groups = db.relationship( "Group", secondary=group_sounds, back_populates="sounds", lazy="select") db.UniqueConstraint('sound') def __str__(self): return"[{}]".format(self.sound) @ classmethod def get(cls, soundString=None, soundStringList=None): """ Converts both sounds and lists of sounds to Sound objects, stores them if new and returns either list or single entity """ def fixSoundTyping(soundString): # Fix g and r IPA typos that are definitely typos newSound = "" for char in soundString: if char == 'ɡ': char = 'g' elif char == 'r': char = 'ʁ' newSound += char return newSound def objectifyAndAddSound(fixedSound): thisSound = cls.query.filter_by(sound=fixedSound).first() if not thisSound: print("'{}' is a new sound.".format(fixedSound)) thisSound = Sound(sound=fixedSound) db.session.add(thisSound) db.session.flush() return thisSound if soundString: # If sound is a string given by user as opposed to Sound object if isinstance(soundString, str): fixedSound = fixSoundTyping(soundString) thisSound = objectifyAndAddSound(fixedSound) else: thisSound = soundString # Assumes that thisSound is a Sound object? return thisSound if soundStringList: soundList = [] for sound in soundStringList: if isinstance(sound, str): fixedSound = fixSoundTyping(sound) sound = objectifyAndAddSound(fixedSound) soundList.append(sound) return soundList def getContrasts(self, sound2): """ return a list of pairs\n The list is sorted so all word1 have the same sound.\n If no such pair exists list will be empty """ sound1 = self if sound2 != "*": # Make a query for populating the contrasts to be returned in the list clauseA = and_(Pair.s1 == sound1, Pair.s2 == Sound.get(soundString=sound2)) clauseB = and_(Pair.s1 == Sound.get(soundString=sound2), Pair.s2 == sound1) else: clauseA = Pair.s1 == sound1 clauseB = Pair.s2 == sound1 contrastsQuery = db.session.query(Pair).filter(or_( clauseA, clauseB)).all() # Order the items returned from query, add to instances of Contrast and append to contrasts list contrasts = [] if contrastsQuery: contrasts = sound1.orderedPairs(contrastsQuery) else: print("This pair didn't exist. Suggestions?") return contrasts def orderedPairs(self, pairs, sound2List=None): """ (Sound) Sorts a given list of pairs so sound1 is self. Throws out pairs without sound1==self\n If sound2List is given, only returns a list of pairs if all contrasts are present\n Returns every pair from a group where sound 1 is the same, but word 1 is NOT necessarily the same:\n (kor, Thor - klor, glor) <-- k is sound1 """ # Arranging words in pairs so given sound always comes first. swappedPairs = [] for pair in pairs: if pair.s1 is self: pass elif pair.s2 is self: pair = Pair(id=pair.id, s1=pair.s2, s2=pair.s1, w1=pair.w2, w2=pair.w1) else: continue swappedPairs.append(pair) pairs = swappedPairs swapped_pairs_sound_2s = [] # If sound2list, deal with it in the following code block if sound2List: # Convert strings to Sound objects in case they're strings newSound2List = Sound.get(soundStringList=sound2List) # Filter out pairs without wanted sound2 filteredPairs = [] for pair in pairs: if pair.s2 in newSound2List: filteredPairs.append(pair) else: print("filtering out {}".format(pair)) # Check that all wanted sound2s are present for pair in filteredPairs: swapped_pairs_sound_2s.append(pair.s2) if all(sound in swapped_pairs_sound_2s for sound in newSound2List): pairs = filteredPairs else: pairs = [] return pairs def getMOPairs(self, sound2List=[]): """ (Sound) Returns 2D array.\n Takes a key sound and a list of opposition sounds.\n Searches in relevant groups for Multiple Oppositions and returns\n a list of lists containg MO-sets for each group. """ print("sounds") print(sound2List) def group_pairs_by_w1(inputList): """ Returns a list of pair lists where word 1 is the same in each list """ newLists = [] for checkpair in inputList: # pairList's pairs each need to be checked to be distributed into w1-unique lists add_to_pairList = False # see if checkpair can be added to any of the pairlists in newlists, if not, add to new list in newlists for pairList in newLists: for savedpair in pairList: if checkpair.w1 == savedpair.w1: # if yes we can add checkpair to pairList in newlist adn we can break this loop add_to_pairList = True break if add_to_pairList: pairList.append(checkpair) break if not add_to_pairList: newLists.append([checkpair]) return newLists groups = db.session.query(Group).all() relevantGroups = [] # Filter out groups that don't have all the sounds sound2s = Sound.get(soundStringList=sound2List) for group in groups: if all(elem in group.sounds for elem in (sound2s + [self])): relevantGroups.append(group) # print("Group {} has all the sounds!".format(group.id)) # Search relevant groups and add their MO-sets to pair list pairLists = [] for group in relevantGroups: print("ordering pairs and only returning if all sound2s are present?") pairs_ordered_by_sound = self.orderedPairs(group.pairs, sound2List) for p in pairs_ordered_by_sound: print(p) # Split pair lists into smaller lists based on word 1 being the same. Only keep end result list if all sound2s are present. uniqueLists = group_pairs_by_w1(pairs_ordered_by_sound) # include only lists with more than one for uniqueList in uniqueLists: if len(uniqueList) > 1: pairLists.append(uniqueList) return pairLists
class Stat(db.Model): # varmistetaan, ettei tietokantaan mene samalle pelaajalle yhteen otteluun useampia tilastoja __table_args__ = (db.UniqueConstraint("game_id", "player_id", name="unique_game_player"), ) id = db.Column(db.Integer, primary_key=True) game_id = db.Column(db.Integer, db.ForeignKey("game.id"), nullable=False) player_id = db.Column(db.Integer, db.ForeignKey("player.id"), nullable=False) goals = db.Column(db.Integer, nullable=False) assists = db.Column(db.Integer, nullable=False) penalties = db.Column(db.Integer, nullable=False) def __init__(self, game_id, player_id, goals, assists, penalties): self.game_id = game_id self.player_id = player_id self.goals = goals self.assists = assists self.penalties = penalties # metodi tilastojen hakemiseen @staticmethod def list_points(team_id): stmt = text( "SELECT Player.number, Player.name, COUNT(Stat.player_id) AS games," " SUM(Stat.goals) AS goals," " SUM(Stat.assists) AS assists, SUM(goals + assists) AS points," " SUM(Stat.penalties) AS penalties" " FROM Player" " LEFT JOIN Stat ON Stat.player_id=Player.id" " WHERE Player.team_id = :team_id" " GROUP BY Player.id" " ORDER BY points DESC, goals DESC, penalties;").params( team_id=team_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({ "number": row[0], "name": row[1], "games": row[2], "goals": row[3], "assists": row[4], "points": row[5], "penalties": row[6] }) return response # metodi tilastojen poistamiseen, kun peli poistetaan @staticmethod def delete_stats(game_id): stmt = text("DELETE FROM Stat WHERE game_id = :game_id;").params( game_id=game_id) res = db.engine.execute(stmt) # metodi tietyn pelin tilastojen hakemiseen @staticmethod def game_details(id): stmt = text( "SELECT Player.number, Player.name, Stat.goals, Stat.assists, Stat.penalties" " FROM Player INNER JOIN Stat ON Stat.player_id=Player.id" " WHERE Stat.game_id = :id;").params(id=id) res = db.engine.execute(stmt) response = [] for row in res: response.append({ "number": row[0], "name": row[1], "goals": row[2], "assists": row[3], "penalties": row[4] }) return response
from application import db from serializer import Serializer from users.models import user_group_association share_group_association = db.Table( 'share_group_association', db.Model.metadata, db.Column('share_id', db.Integer, db.ForeignKey('share.id', ondelete="CASCADE"), nullable=False), db.Column('group_id', db.Integer, db.ForeignKey('group.id', ondelete="CASCADE"), nullable=False), db.UniqueConstraint('share_id', 'group_id', name='share_group_association_uc')) class Group(db.Model, Serializer): __private__ = ('id') id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) user = db.relationship("User", back_populates="own_groups") members = db.relationship("User", secondary=user_group_association,
from urllib.parse import urljoin import settings from application import db from models import Category from models.mixins import SetFieldsMixin logger = logging.getLogger(__name__) product_color = db.Table('product_color', db.Column('product_id', db.Integer, db.ForeignKey('product.id')), db.Column('color_id', db.Integer, db.ForeignKey('color.id')), db.UniqueConstraint('product_id', 'color_id', name='US_product_id_color_id') ) product_size = db.Table('product_size', db.Column('product_id', db.Integer, db.ForeignKey('product.id')), db.Column('size_id', db.Integer, db.ForeignKey('size.id')), db.UniqueConstraint('product_id', 'size_id', name='US_product_id_size_id') ) class Product(SetFieldsMixin, db.Model): __table_name__ = 'product' GENDER_MALE = 'male'
# -*- coding: utf-8 -*- import models as m from application import db rule_color_ratio = db.Table('rule_clothing_color_ratio', db.Column('rule_id', db.Integer, db.ForeignKey('fashion_rule.id')), db.Column('color_ratio_id', db.Integer, db.ForeignKey('clothing_color_ratio.id')), db.UniqueConstraint('rule_id', 'color_ratio_id', name='US_rule_id_color_ratio_id') ) class FashionRule(db.Model): __tablename__ = 'fashion_rule' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(256), nullable=False) is_active = db.Column(db.Boolean, default=True) season_id = db.Column(db.Integer, db.ForeignKey('fashion_season.id'), nullable=False) color_ratios = db.relationship('ClothingColorRatio', secondary=rule_color_ratio, backref=db.backref('rules', lazy='dynamic')) def __repr__(self): return '<Правило моды {} "{}">'.format(self.id, self.name) def get_compatibility_percentage(self, pids): """ :type pids: (list|tuple)
class Task(db.Model): __table_args__ = (db.UniqueConstraint("name", "key", name="uniq_name_key_for_task"), ) id = db.Column(Integer, primary_key=True) name = db.Column(String, nullable=False) key = db.Column(String, nullable=False) module = db.Column(String, nullable=False) enabled = db.Column(Boolean, default=False) startup = db.Column(Boolean, default=False) periodic = db.Column(Boolean, default=False) description = db.Column(String) kwargs = db.Column(JSONType) opts = db.Column(JSONType) schedule = db.Column(JSONType) last_failed = db.Column(DateTime) last_success = db.Column(DateTime) db_created_at = db.Column(DateTime, default=sqlfunc.now()) db_updated_at = db.Column(DateTime, default=sqlfunc.now(), onupdate=sqlfunc.now()) runs_rel_attrs = { "lazy": "dynamic", "back_populates": "task", "cascade": "all,delete-orphan" } runs = relationship(lambda: TaskRun, **runs_rel_attrs) def __repr__(self): return f"<id: {self.id}, name: {self.name}, key: {self.key}, enabled: {self.enabled}>" @property def once_opts(self): return self.opts.get("once", {}) @property def retrying(self): return bool(self.where(["RETRYING"]).count()) @property def pending(self): return bool(self.where(["PENDING"]).count()) @classmethod def get(cls, name, key, enabled=None, will_raise=False): task = cls.query.filter_by(name=name.lower(), key=key).first() if not task and will_raise: raise TaskNotFound(name, key) if task and enabled and task.enabled is not enabled: logger.info(f"Task disabled {name}/{key}") return None return task @classmethod def parse_config(cls, **config): timeout = config["opts"].get("once", {}).get("timeout") if timeout: timeout = round(timedelta(**timeout).total_seconds()) config["opts"]["once"]["timeout"] = timeout config["opts"]["queue"] = config["module"] config["kwargs"]["key"] = config["key"] config["kwargs"]["periodic"] = config["periodic"] return config @classmethod def get_all(cls, tasks): task_tuples = [tuple(t.values()) for t in tasks] tuple_filter = sqlalchemy.tuple_(Task.name, Task.key).in_(task_tuples) return Task.query.filter(tuple_filter).all() @classmethod def get_or_create(cls, **config): config = cls.parse_config(**config) task = Task.get(name=config["name"], key=config["key"]) if not task: task = Task(**config) db.session.add(task) db.session.commit() return task @classmethod def create_or_update(cls, **config): config = cls.parse_config(**config) task = Task.get(name=config["name"], key=config["key"]) task = task.update(**config) if task else Task(**config) db.session.add(task) db.session.commit() return task @classmethod def purge_locks(cls, pattern="qo_*"): lock_keys = cls.get_lock_keys(pattern) if lock_keys: logger.info(f"deleting lock keys: {lock_keys}") c_app.redis.delete(*lock_keys) return lock_keys @classmethod def get_lock_keys(cls, pattern="qo_*"): return c_app.redis.keys(pattern=pattern) @classmethod def revoke_all(cls, tasks=None): tasks_to_revoke = tasks or Task.query.all() return [task.revoke() for task in tasks_to_revoke] def revoke(self, states=None): states = states or ["PENDING", "RUNNING", "RETRYING"] return [run.revoke() for run in self.where(states).all()] def unlock(self): return [run.unlock() for run in self.runs.all()] def run(self, kwargs=None): return c_app.celery_utils.send_task(task=self, **(kwargs or {})) def where(self, states, unique=False): states = [TaskRun.STATE_LOOKUP[s] for s in states] query = self.runs.filter(TaskRun.state.in_(states)) return query.filter_by(unique=unique) def update(self, **kwargs): for key, value in kwargs.items(): if hasattr(self, key): setattr(self, key, value) return self
__author__ = 'kent' from application import db from flask_restful import fields from datetime import datetime permissions = db.Table( 'permissions', db.Column('user_id', db.Integer, db.ForeignKey('user.id')), db.Column('tool_id', db.Integer, db.ForeignKey('tool.id')), db.UniqueConstraint('user_id', 'tool_id', name='uix_permissions_user_tool')) class User(db.Model): id = db.Column(db.Integer, primary_key=True) rfid = db.Column(db.String(40), unique=True) name = db.Column(db.Text) tools = db.relationship('Tool', secondary=permissions, backref='users') def __init__(self, rfid, name): self.rfid = rfid self.name = name tool_fields = { 'id': fields.Integer, 'name': fields.String, 'description': fields.String, 'rfid': fields.String, 'checked_out_by': fields.Integer,