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,
