from sqlalchemy import String from app.main import db truck_shipment = db.Table( 'truck_shipment', db.Column('truck_id', String, db.ForeignKey('truck.id')), db.Column('shipment_id', String, db.ForeignKey('shipment.id'), unique=True))
class sdUser(db.Model): __tablename__ = "sd11_users" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), nullable=False, comment="Name") display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") status = db.Column(db.Integer, server_default="0", comment="Status") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") password_hash = db.Column("password", db.String(64), comment="Password") cust_id = db.Column(db.Integer, db.ForeignKey("sd10_customers.id"), comment="Customer id") email = db.Column(db.String(100), comment="Email") telephone = db.Column(db.String(30), comment="Telephone number") line_id = db.Column(db.String(30), comment="LINE id") __table_args__ = (db.UniqueConstraint("cust_id", "name"), ) def __repr__(self): return f"<sdUser id={self.id}/name={self.name}/display_name={self.display_name}>" @property def password(self): raise AttributeError("password field cannot be read") @password.setter def password(self, new_password): self.password_hash = bcrypt.generate_password_hash( new_password).decode("utf-8") def check_password(self, password): return bcrypt.check_password_hash(self.password_hash, password) @staticmethod def search(cust_id, user_name): return sdUser.query.filter_by(cust_id=cust_id).filter_by( name=user_name).first() @staticmethod def getall(cust_id): users = db.session.query(sdUser).filter( sdUser.cust_id == cust_id).all() return users @staticmethod def getdetail(cust_id, user_list): users = db.session.query(sdUser).filter( sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).all() return users @staticmethod def add(cust_id, name, display_name, password, email, telephone, comment): obj = sdUser() obj.cust_id = cust_id obj.name = name obj.display_name = display_name obj.password = password obj.email = email obj.telephone = telephone obj.comment = comment return obj # HINT delete_all_user_groups: delete all user groups record corresponding to the user from m2m table @staticmethod def delete_all_user_groups(cust_id, user_list): users = db.session.query(sdUser).filter( sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).all() for user in users: ug_rels = user.user_groups ug_rels.clear() @staticmethod def delete(cust_id, user_list): sdUser.query.filter( sdUser.cust_id == cust_id, sdUser.id.in_(user_list)).delete(synchronize_session=False) @staticmethod def update(cust_id, user_id, name, display_name, password, email, telephone, comment): obj = db.session.query(sdUser).filter(sdUser.id == user_id).first() if obj: # HINT This method has too much if/else, # here should handle by Frontend, or use this method obj.password = password if password else obj.password obj.name = name if name else obj.name obj.display_name = display_name if display_name else obj.display_name obj.email = email if email else obj.email obj.telephone = telephone if telephone else obj.telephone obj.comment = comment if comment else obj.comment else: obj = sdUser() obj.cust_id = cust_id obj.name = name obj.display_name = display_name obj.password = password obj.email = email obj.telephone = telephone obj.comment = comment return obj @staticmethod def get_admin(cust_id): admin = db.session.query(sdUser).filter( sdUser.cust_id.in_(cust_id), sdUser.name == Constant.ADMIN).all() return admin
remove_from_index(obj.__tablename__, obj) session._changes = None @classmethod def reindex(cls): for obj in cls.query: add_to_index(cls.__tablename__, obj) db.event.listen(db.session, "before_commit", SearchableMixin.before_commit) db.event.listen(db.session, "after_commit", SearchableMixin.after_commit) followers = db.Table( "followers", db.Column("follower_id", db.Integer, db.ForeignKey("user.id")), db.Column("followed_id", db.Integer, db.ForeignKey("user.id")), ) likes = db.Table( "likes", db.Column("post_id", db.Integer, db.ForeignKey("post.id")), db.Column("user_id", db.Integer, db.ForeignKey("user.id")), ) class User(UserMixin, db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True)
class File(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) directory = db.Column(db.String, nullable=False) url = db.Column(db.String, nullable=False) status_id = db.Column(db.Integer, db.ForeignKey("status.id"), nullable=False, index=True) type_id = db.Column(db.Integer, db.ForeignKey("type.id"), nullable=False, index=True) size = db.Column(db.String) speed = db.Column(db.String) percent = db.Column(db.Float, nullable=False) time_remaining = db.Column(db.String) @classmethod def get_all_files(cls, status=None, page=None, limit=None): page = int( page) if page is not None and page.isdigit() else INITIAL_PAGE limit = int( limit) if limit is not None and limit.isdigit() else DEFAULT_LIMIT query = File.filter_status(File.query, status) query = query.order_by(File.id.desc()).paginate(page, limit, False).items return { "downloads": [item.marshal() for item in query], "totalPages": File.total_pages(status, limit), } @classmethod def get_file(cls, file_id): query = File.query.filter_by(id=file_id).first() return query @classmethod @retry def new_file(cls, url, type_id, directory, name=None): new_file = File( url=url, name=name, directory=directory, type_id=type_id, status_id=Status.STATUS_IN_PROGRESS_ID, percent=0.0, ) db.session.add(new_file) db.session.commit() logger.info(f"New download ID: {new_file.id}") return new_file @retry def update_db(self, stats): if "name" in stats: self.name = stats["name"] if "percent" in stats: self.percent = stats["percent"] if "size" in stats: self.size = stats["size"] if "speed" in stats: self.speed = stats["speed"] if "time_remaining" in stats: self.time_remaining = stats["time_remaining"] db.session.commit() @retry def complete(self): self.percent = 100 self.status_id = Status.STATUS_COMPLETED_ID logger.info(f"{self.name} completed") db.session.commit() @retry def error(self): self.status_id = Status.STATUS_ERROR_ID logger.error(f"{self.name} errored") db.session.commit() @staticmethod def filter_status(query, status): if status is None: return query elif isinstance(status, list): status_ids = [Status.name_to_id(item) for item in status] return query.filter(File.status_id.in_(status_ids)) else: return query.filter(File.status_id == Status.name_to_id(status)) @staticmethod def total_pages(status, limit): total_count = File.filter_status( db.session.query(db.func.count(File.id)), status).scalar() return math.ceil(total_count / limit) or 1 def marshal(self): return { "id": self.id, "name": self.name, "url": self.url, "directory": self.directory, "status": Status.id_to_name(self.status_id), "type": Type.id_to_name(self.type_id), "percent": self.percent, "size": self.size, "speed": self.speed, "timeRemaining": self.time_remaining, }
class ProductCommentsModel(db.Model): __tablename__ = 'productcomments' id = db.Column(db.Integer, primary_key=True) product_id = db.Column(db.Integer, db.ForeignKey('products.id')) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) comment = db.Column(db.String(256))
class Post(Base, SearchableMixin): """ Description of User model. Columns ----------- :id: int [pk] :title: Text [not NULL] :author_id: int [Foreign Key] :creation_time: DateTime [not NULL] :last_edit_time: DateTime [not NULL] :post_body: Text # Relationships :comments: Relationship -> Comments (one to many) """ # Columns id = db.Column(db.Integer, db.ForeignKey("base.id"), primary_key=True) post_id = db.Column(db.Integer, autoincrement=True, primary_key=True, unique=True) title = db.Column(db.Text, nullable=False) post_movie = db.Column(db.String(20)) tags = db.Column(db.JSON) __searchable__ = ['title', 'body', 'tags'] __mapper_args__ = { 'polymorphic_identity': 'post', 'inherit_condition': (id == Base.id) } comments = db.relationship( 'Comment', primaryjoin="(Post.post_id == Comment.parent_post_id)", backref=db.backref('post'), lazy='dynamic') def __init__(self, author_id, post_movie, title, post_body, tags): super().__init__(author_id, post_body, "post") self.title = title self.post_movie = post_movie self.tags = tags db.session.add(self) db.session.commit() def add_comment(self, author_id, comment_body): parent_post_id = self.id comment = Comment(author_id, parent_post_id, comment_body) self.comments.append(comment) db.session.commit() return comment.id def update_col(self, key, value): setattr(self, key, value) db.session.commit() def delete_post(self, post_id): post = Post.query.filter_by(id=post_id).delete() db.session.commit()
import datetime from app.main import db, flask_bcrypt import jwt from app.main.model.blacklist import BlacklistToken from ..config import key # many to many table between users and card lists user_card_list = db.Table( 'user_card_list', db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True), db.Column('card_list_id', db.Integer, db.ForeignKey('card_list.id'), primary_key=True), ) class UserModel(db.Model): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True, autoincrement=True) email = db.Column(db.String(255), unique=True, nullable=False) username = db.Column(db.String(50), unique=True) password_hash = db.Column(db.String(100)) role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
class Post(db.Model): """ Description of Post model. Rows ----------- :post_id: int [pk] :authorId: varchar [ref: > users.id] :title: varchar :body: text :post_time: timestamp [not null] :avg_rating: float :num_rating: int """ # Columns post_id = db.Column(db.Integer, primary_key=True) _author_id = db.Column(db.String(256), db.ForeignKey('user.username'), nullable=False) title = db.Column(db.String(128), nullable=False) body = db.Column(db.Text, nullable=False) post_time = db.Column(db.DateTime, default=datetime.datetime.now()) avg_rating = db.Column(db.Float, default=0.0) num_rating = db.Column(db.Integer, default=0) # Relationships author = db.relationship('User', backref='posts', lazy=False) tags = db.relationship('Tag', secondary=postTagJunction, lazy='subquery', backref=db.backref('posts', lazy=True)) # savers = db.relationship('User', secondary=postSaves, lazy=True, # backref=db.backref('posts', lazy='subquery')) images = db.relationship('ImgLink', secondary=imgPostJunction, lazy='subquery') def __init__(self, author, title, body): self.author = author self.title = title self.body = body db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() # Getters and Setters for the fields # @hybrid_property # def author_id(self): # return self.author # @author_id.setter # def author_id(self, authorId): # try: # user = User.query.filter_by(id=authorId).first() # if user.last_logout != None: # raise LoginError # else: # self._author_id = authorId # except: # # Stub to be handled later # print("Get back to login page") @classmethod def getArticlesByTags(cls, tagList, connector='AND'): """ Get all articles that have a tag in tagList. If connector is AND intersection of all posts set for each tag will be returned. If it is OR, union will be returned """ if connector == 'AND': posts = set() for tag in tagList: if len(posts) == 0: posts = set(cls.query.filter(tag in cls.tags).all()) else: posts.intersection( set(cls.query.filter(tag in cls.tags).all())) return list(posts) elif connector == 'OR': posts = set() for tag in tagList: if len(posts) == 0: posts = set(cls.query.filter(tag in cls.tags).all()) else: posts.union(set(cls.query.filter(tag in cls.tags).all())) return list(posts) @staticmethod def getArticles(post_id): return Post.query.filter_by(post_id=post_id).first() @staticmethod def getRandomizedArticles(size): return sample(Post.query.all(), size)
class UserAddressModel(db.Model): __tablename__ = "useraddress" id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) address = db.Column(db.String(50))
class Wishlist(db.Model): #creating user wishlist table in database __tablename__ = "wishlist" id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) product_id = db.Column(db.Integer, db.ForeignKey("products.product_id"))
from random import sample from sqlalchemy.ext.hybrid import hybrid_property # from . import * from app.main import db from app.main.models.enums import PostType # from app.main.models.users import User from app.main.models.errors import LoginError from app.main.models.imgLinks import imgPostJunction postTagJunction = db.Table( 'postTagJunction', db.Column('post_id', db.Integer, db.ForeignKey('post.post_id'), primary_key=True), db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True)) class Post(db.Model): """ Description of Post model. Rows ----------- :post_id: int [pk] :authorId: varchar [ref: > users.id] :title: varchar :body: text :post_time: timestamp [not null] :avg_rating: float
# -*- coding:utf-8 -*- from app.main import db from app.staff.models import StaffAccount course_instructors = db.Table('eduqa_course_instructor_assoc', db.Column('course_id', db.Integer, db.ForeignKey('eduqa_courses.id')), db.Column('instructor_id', db.Integer, db.ForeignKey('eduqa_course_instructors.id')) ) session_instructors = db.Table('eduqa_session_instructor_assoc', db.Column('session_id', db.Integer, db.ForeignKey('eduqa_course_sessions.id')), db.Column('instructor_id', db.Integer, db.ForeignKey('eduqa_course_instructors.id')) ) class EduQAProgram(db.Model): __tablename__ = 'eduqa_programs' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(255), nullable=False, info={'label': u'ชื่อ'}) degree = db.Column(db.String(), nullable=False, info={'label': u'ระดับ', 'choices': (('undergraduate', 'undergraduate'), ('graudate', 'graduate')) }) class EduQACurriculum(db.Model): __tablename__ = 'eduqa_curriculums' id = db.Column(db.Integer, primary_key=True, autoincrement=True) program_id = db.Column(db.ForeignKey('eduqa_programs.id'),
# coding:utf-8 from app.main import db from sqlalchemy.sql import func from werkzeug.security import generate_password_hash, check_password_hash from flask_login import UserMixin from datetime import datetime from flask import session, abort, globals import functools sys_user_role = db.Table( 'sys_user_role', # 用户角色关联表 db.Column('userId', db.Integer, db.ForeignKey('sys_user.id')), db.Column('roleId', db.Integer, db.ForeignKey('sys_role.id')), db.Column('createTime', db.DateTime, default=datetime.now), ) sys_role_permission = db.Table( 'sys_role_permission', # 角色权限关联表 db.Column('permissionId', db.Integer, db.ForeignKey('sys_permission.id')), db.Column('roleId', db.Integer, db.ForeignKey('sys_role.id')), db.Column('createTime', db.DateTime, default=datetime.now), ) sys_role_menu = db.Table( 'sys_role_menu', # 用户菜单关联表 db.Column('roleId', db.Integer, db.ForeignKey('sys_role.id')), db.Column('menuId', db.Integer, db.ForeignKey('sys_menu.id')), db.Column('createdTime', db.DateTime, default=datetime.now), db.Column('isDelete', db.Boolean, default=False), )
class UpdateForm(db.Model): """Namings as in Odoo. """ __tablename__ = "update_form" form_id = db.Column(db.Integer, primary_key=True, autoincrement=True) form_status = db.Column(db.String(100)) name = db.Column(db.String(100)) # full name in ukrainian birth_date = db.Column(db.Date()) image_1920 = db.Column( db.String(500000) ) # Odoo saves image as base64 encoded string, f*cking large str # email = db.Column(db.String(100)) # Do not allow to update email here, because we use email for login contact_country = db.Column(db.String(100)) # selection field contact_city = db.Column(db.String(100)) mobile = db.Column(db.String(15)) skype = db.Column(db.String(100)) telegram = db.Column(db.String(100)) viber = db.Column(db.String(100)) facebook_link = db.Column(db.String(100)) linkedin_link = db.Column(db.String(100)) diploma_naukma = db.Column(db.Boolean) bachelor_degree = db.Column(db.Boolean()) bachelor_faculty = db.Column(db.String(100)) # selection field bachelor_speciality = db.Column(db.String(100)) # selection field bachelor_year_in = db.Column(db.String(100)) # selection field bachelor_year_out = db.Column(db.String(100)) # selection field master_degree = db.Column(db.Boolean()) master_faculty = db.Column(db.String(100)) # selection field master_speciality = db.Column(db.String(100)) # selection field master_year_in = db.Column(db.String(100)) # selection field master_year_out = db.Column(db.String(100)) # selection field parent_id = db.Column( db.Integer) # company id in Odoo, many2one field in Odoo company_name = db.Column(db.String(100)) function = db.Column(db.String(100)) # job position # foreign keys alumni_id = db.Column(db.Integer, db.ForeignKey('alumni.alumni_id', onupdate="CASCADE", ondelete="NO ACTION"), nullable=False) alumni = db.relationship("Alumni", back_populates="update_form") operator_id = db.Column(db.Integer, db.ForeignKey('operator.operator_id', onupdate="CASCADE", ondelete="NO ACTION"), nullable=True) operator = db.relationship("Operator", back_populates="update_form") def __init__(self, name, birth_date, image_1920, contact_country, contact_city, mobile, skype, telegram, viber, facebook_link, linkedin_link, diploma_naukma, bachelor_degree, bachelor_faculty, bachelor_speciality, bachelor_year_in, bachelor_year_out, master_degree, master_faculty, master_speciality, master_year_in, master_year_out, parent_id, company_name, function, alumni_id, operator_id): self.form_status = 'new' # TODO: create enum for the form statuses self.name = name self.birth_date = datetime.strptime(birth_date, '%Y-%m-%d').date() self.image_1920 = image_1920 self.contact_country = contact_country self.contact_city = contact_city self.mobile = mobile self.skype = skype self.telegram = telegram self.viber = viber self.facebook_link = facebook_link self.linkedin_link = linkedin_link self.diploma_naukma = diploma_naukma self.bachelor_degree = bachelor_degree self.bachelor_faculty = bachelor_faculty self.bachelor_speciality = bachelor_speciality self.bachelor_year_in = bachelor_year_in self.bachelor_year_out = bachelor_year_out self.master_degree = master_degree self.master_faculty = master_faculty self.master_speciality = master_speciality self.master_year_in = master_year_in self.master_year_out = master_year_out self.parent_id = parent_id self.company_name = company_name self.function = function self.alumni_id = alumni_id self.operator_id = operator_id def update(self, data): for key, item in data.items(): if hasattr(self, key): setattr(self, key, item) db.session.commit()
class Cart(db.Model): #create class table in sql __tablename__ = "cart" id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) product_id = db.Column(db.Integer, db.ForeignKey("products.product_id"))
class sdDevice(db.Model): __tablename__ = "sd21_devices" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), comment="Name") display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") dev_type = db.Column(db.Integer, server_default="0", comment="Device type: 0: unknown, 1: traffic light, 2: others") cust_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Customer id") vendor_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Vendor id") status = db.Column(db.Integer, server_default="0", comment="Device status flag(bit). 0: warning, bit 1: error,可持續新增") power_status = db.Column(db.Integer, comment="Device power status. null: unknown, 0: off, 1: on") device_group_id = db.Column(db.Integer, db.ForeignKey("sd22_device_groups.id"), comment="Device group id, null means ungroup") controller_id = db.Column(db.Integer, db.ForeignKey(sdController.id), comment="Controller id") led_id = db.Column(db.Integer, db.ForeignKey(sdLed.id), comment="LED id") vender_device_id = db.Column(db.String(25), comment="Vender device id") wgs_x = db.Column(db.Float, comment="GPS X") wgs_y = db.Column(db.Float, comment="GPS Y") address = db.Column(db.String(100), comment="Address") device_info = db.relationship("sdDeviceInfo", uselist=False, backref="device") __table_args__ = (db.UniqueConstraint("cust_id", "name"),) def __repr__(self): return ( f"<sdDevice id={self.id}/name={self.name}/display_name={self.display_name}/cust_id={self.cust_id}" f"/wgs_x={self.wgs_x}/wgs_y={self.wgs_y}/address={self.address}>" f"/controller_id={self.controller_id}/led_id={self.led_id}" ) @staticmethod def delete(cust_id, device_list): sdDevice.query.filter(sdDevice.cust_id == cust_id, sdDevice.id.in_(device_list)).delete(synchronize_session=False) @staticmethod def get_detail(cust_id, device_id_list): devices = db.session.query(sdDevice).filter(or_(sdDevice.cust_id == cust_id, sdDevice.vendor_id == cust_id), sdDevice.id.in_(device_id_list)).all() return devices @staticmethod def get_devices_in_groups(cust_id, group_id_list): id_list = [] device_id_tuple = db.session.query(sdDevice.id).filter(sdDevice.cust_id == cust_id, sdDevice.device_group_id.in_(group_id_list)).all() for device_id in device_id_tuple: id_list.append(device_id[0]) return id_list @staticmethod def add(cust_id, device, led, controller): obj = sdDevice() obj.cust_id = cust_id obj.name = device.get("name") obj.display_name = device.get("display_name") obj.comment = device.get("comment") obj.controller = controller obj.led = led obj.wgs_x = device.get('wgs_x') obj.wgs_y = device.get('wgs_y') obj.address = device.get('address') return obj @staticmethod def update(cust_id, device, data): obj = db.session.query(sdDevice).filter(sdDevice.id == device["id"]).first() if obj: obj.cust_id = cust_id obj.name = device.get("name") obj.display_name = device.get("display_name") obj.comment = device.get("comment") obj.wgs_x = device.get("wgs_x") obj.wgs_y = device.get("wgs_y") obj.address = device.get("address") # if threre is led or controller then update. led = data.get("led") if led: obj.led = led controller = data.get("controller") if controller: obj.controller = controller return obj
class CartModel(db.Model): __tablename__ = 'cart' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
class Movie(SearchableMixin, db.Model): """ Description of User model. Columns ----------- :id: int [pk] :user_id: int [Foreign Key -> User.id] :imdb_ID: varchar(128) [not NULL] :title: Text [not NULL] :year: int :release_date: DateTime :runtime: int :genre: JSON :director: JSON :writer: JSON :actors: JSON :plot: Text :language: JSON :country: JSON :awards: Text :ratings: JSON :imdb_rating: Float :rotten_tomatoes: int :metascore: int :poster_URL: varchar(255) :box_office: varchar(255) :added_to_db: DateTime """ # Columns id = db.Column(db.Integer, primary_key=True) parent_user_id = db.Column(db.Integer, db.ForeignKey('user.id')) imdb_ID = db.Column(db.String(128), unique=True, nullable=False) title = db.Column(db.Text, nullable=False) year = db.Column(db.Integer) release_date = db.Column(db.String(128)) runtime = db.Column(db.String(128)) plot = db.Column(db.Text) genre = db.Column(db.JSON) director = db.Column(db.JSON) writer = db.Column(db.JSON) actors = db.Column(db.JSON) language = db.Column(db.JSON) country = db.Column(db.JSON) awards = db.Column(db.Text) imdb_rating = db.Column(db.String(128)) rotten_tomatoes = db.Column(db.String(128)) metascore = db.Column(db.String(128)) poster_url = db.Column(db.String(255)) box_office = db.Column(db.String(128)) added_time = db.Column(db.DateTime) __searchable__ = [ 'title', 'year', 'genre', 'director', 'actors', 'language', 'country' ] def __init__(self, imdb_ID, title, year, release_date, runtime, genre, director, writer, actors, plot, language, country, awards, imdb_rating, rotten_tomatoes, metascore, poster_url, box_office): self.imdb_ID = imdb_ID self.title = title self.year = year self.release_date = release_date self.runtime = runtime self.genre = genre self.director = director self.writer = writer self.actors = actors self.plot = plot self.language = language self.country = country self.awards = awards self.imdb_rating = imdb_rating self.rotten_tomatoes = rotten_tomatoes self.metascore = metascore self.poster_url = poster_url self.box_office = box_office self.added_time = datetime.datetime.now() db.session.add(self) db.session.commit() def update_col(self, key, value): setattr(self, key, value) db.session.commit()
class Recipe(db.Model): __tablename__ = "recipes" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text(), nullable=False) description = db.Column(db.Text(), nullable=False) steps = db.Column(db.Text(), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey( "accounts.id", ondelete="CASCADE", ), nullable=False, index=True) ingredient_amounts = db.relationship( "RecipeIngredient", backref="recipe", lazy=True, passive_deletes=True) def __init__(self, name, description, steps): self.name = name self.description = description self.steps = steps def get_ingredients(self): stmt = text(""" SELECT ri.amount AS amount, ri.amount_unit AS amount_unit, ri.group_name AS group_name, i.id AS id, i.name AS name FROM ingredients i, recipe_ingredient ri WHERE ri.recipe_id = :recipe_id AND i.account_id = :account_id AND ri.ingredient_id = i.id ORDER BY ri.id """).bindparams(recipe_id=self.id, account_id=self.account_id) return db.session().execute(stmt) def get_shopping_list_amounts(self): stmt = text(""" SELECT i.id AS id, SUM(sli.amount) AS amount, sli.amount_unit AS unit FROM ( SELECT DISTINCT i.id AS id, i.name AS name FROM ingredients i, recipe_ingredient ri WHERE i.account_id = :account_id AND i.id = ri.ingredient_id AND ri.recipe_id = :recipe_id ) i LEFT JOIN shopping_list_items sli ON sli.ingredient_id = i.id WHERE sli.account_id = :account_id GROUP BY i.id, sli.amount_unit """).bindparams(recipe_id=self.id, account_id=self.account_id) return db.session().execute(stmt) def insert_ingredients_from_form(self, form): ingredients = [] missing_ingredients = [] ingredients_by_name = {} for recipe_ingredient_form in form.ingredient_amounts: name = recipe_ingredient_form.data["name"].strip() lower_name = name.lower() existing_ingredient = ingredients_by_name.get(lower_name) if not existing_ingredient: existing_ingredient = Ingredient.query.filter( Ingredient.account_id == self.account_id, func.lower(Ingredient.name) == lower_name, ).first() if not existing_ingredient: existing_ingredient = Ingredient(name) existing_ingredient.account_id = self.account_id missing_ingredients.append(existing_ingredient) ingredients_by_name[lower_name] = existing_ingredient ingredients.append(existing_ingredient) db.session().bulk_save_objects(missing_ingredients, return_defaults=True) db.session().flush() recipe_ingredients = [] for index, recipe_ingredient_form in enumerate(form.ingredient_amounts): amount, unit = recipe_ingredient_form.parse_amount() recipe_ingredients.append(RecipeIngredient( amount=amount, amount_unit=unit, ingredient_id=ingredients[index].id, recipe_id=self.id, group_name=recipe_ingredient_form.group.data, )) db.session().bulk_save_objects(recipe_ingredients) db.session().flush()
class Recipe(db.Model): """ Recipe Model for storing recipes """ __tablename__ = "recipe" id = db.Column(db.Integer, primary_key=True, autoincrement=True) title = db.Column(db.String(255), nullable=False) created_on = db.Column(db.DateTime, nullable=False) cooktime = db.Column(db.String(255), nullable=False) preptime = db.Column(db.String(255), nullable=False) totaltime = db.Column(db.String(255), nullable=False) username = db.Column(db.String(50), db.ForeignKey('user.username'), nullable=False) remixes = db.relationship("Recipe", backref=db.backref('parent', remote_side=[id])) parent_id = db.Column(db.Integer, db.ForeignKey('recipe.id')) public = db.Column(db.Boolean) difficulty = db.Column(db.Integer, nullable=True) servings = db.Column(db.String(255), nullable=False) source = db.Column(db.String(255), nullable=False) calories = db.Column(db.Integer, nullable=True) cost = db.Column(db.Float, nullable=True) description = db.Column(db.Text) f_image = db.Column(db.Integer, nullable=True) images = db.relationship('Image', order_by='Image.id', backref='recipe', cascade='all,delete,delete-orphan') ingredients = db.relationship('Ingredient', order_by='Ingredient.number', collection_class=ordering_list('number', count_from=1), backref='recipe', lazy=True, cascade='all,delete,delete-orphan') steps = db.relationship('Step', order_by='Step.number', collection_class=ordering_list('number', count_from=1), backref='recipe', lazy=True, cascade='all,delete,delete-orphan') @hybrid_property def remix_count(self): return len(self.remixes) @remix_count.expression def _remix_count_expression(cls): q = db.select([db.func.count(Recipe.parent_id)]).\ where(Recipe.parent_id == cls.id).\ label("remix_count") return q @hybrid_property def featured_image(self): if self.f_image is not None: return self.images[self.f_image] return None @hybrid_property def community_images(self): return (list( filter(lambda img: img.username != self.username, self.images)) + [ r.featured_image for r in self.remixes if r.featured_image is not None ]) @hybrid_property def owner_images(self): return list( filter(lambda img: img.username == self.username, self.images)) @hybrid_property def likes_count(self): return len(self.likers) #TODO make has_liked work when nested, as in viewing other user's recipes @hybrid_method def has_liked(self, username): return db.session.query(likes).filter( likes.c.username == username).filter( likes.c.recipe_id == self.id).first() != None @likes_count.expression def _likes_count_expression(cls): return (db.select([ db.func.count(likes.c.username).label("likes_count") ]).where(likes.c.recipe_id == cls.id).label("sum_likes")) def __repr__(self): return "<Recipe '{}'>".format(self.title)
class UserModel(db.Model): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True, autoincrement=True) email = db.Column(db.String(255), unique=True, nullable=False) username = db.Column(db.String(50), unique=True) password_hash = db.Column(db.String(100)) role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False) card_lists = db.relationship('CardListModel', secondary=user_card_list, backref=db.backref('users', lazy='dynamic')) cards = db.relationship('CardModel', backref='user', lazy='dynamic') comments = db.relationship('CommentModel', backref='user', lazy='dynamic') @property def password(self): raise AttributeError('password: write-only field') @password.setter def password(self, password): self.password_hash = flask_bcrypt.generate_password_hash( password).decode('utf-8') def check_password(self, password): return flask_bcrypt.check_password_hash(self.password_hash, password) def json(self): return {'id': self.id, 'email': self.email, 'username': self.username} @staticmethod def encode_auth_token(user_id): """ Generates the Auth Token :return: string """ try: payload = { 'exp': datetime.datetime.utcnow() + datetime.timedelta(days=1, seconds=5), 'iat': datetime.datetime.utcnow(), 'sub': user_id } return jwt.encode(payload, key, algorithm='HS256') except Exception as e: return e @staticmethod def decode_auth_token(auth_token): """ Decodes the auth token :param auth_token: :return: integer|string """ try: payload = jwt.decode(auth_token, key, algorithms='HS256') is_blacklisted_token = BlacklistToken.check_blacklist(auth_token) if is_blacklisted_token: return 'Token blacklisted. Please log in again.' else: return payload['sub'] except jwt.ExpiredSignatureError: return 'Signature expired. Please log in again.' except jwt.InvalidTokenError: return 'Invalid token. Please log in again.' def __repr__(self): return f"<User name:'{self.username}', id:'{self.id}', role: {self.role}>"
from app.main import db import datetime from sqlalchemy.sql import func from app.main import db import datetime from sqlalchemy.sql import func association_table = db.Table( 'ASSOCIATION_CC_TT', db.Model.metadata, db.Column('tt_code', db.String(32), db.ForeignKey('CONTENT.id')), db.Column('cc_code', db.String(32), db.ForeignKey('COMPANY.id'))) class Content(db.Model): __tablename__ = "CONTENT" id = db.Column(db.String(32), primary_key=True) companies = db.relationship("Company", secondary=association_table, back_populates="contents") class Company(db.Model): __tablename__ = "COMPANY" id = db.Column(db.String(32), primary_key=True) name = db.Column(db.String(256), index=True, unique=True) link = db.Column(db.String(256), index=True, unique=True) contents = db.relationship("Content", secondary=association_table, back_populates="companies")
class sdLed(db.Model): __tablename__ = "sd32_leds" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), comment="Name") display_name = db.Column(db.String(50), comment="Display name") comment = db.Column(db.Text, comment="Comment") create_time = db.Column(db.DateTime, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, server_default=func.now(), comment="Update time") cust_id = db.Column(db.Integer, db.ForeignKey(sdCustomer.id), comment="Customer id") status = db.Column( db.Integer, server_default="0", comment="Device status flag. bit 0: warning, bit 1: error") model_id = db.Column(db.Integer, db.ForeignKey(sdDeviceModel.id), server_default="0", comment="Traffic Light model/part no") serial_no = db.Column(db.String(50), nullable=False, comment="Serial No") device = db.relationship("sdDevice", uselist=False, backref="led") __table_args__ = (db.UniqueConstraint("serial_no"), db.UniqueConstraint("cust_id", "name")) def __repr__(self): return f"<sdLed id={self.id}/name={self.name}/display_name={self.display_name}/sn={self.serial_no}>" @staticmethod def add(cust_id, led): model = db.session.query(sdDeviceModel) \ .filter(sdDeviceModel.name == led.get("model_name")).first() obj = sdLed() obj.cust_id = cust_id obj.name = led.get("name") obj.display_name = led.get("display_name") obj.comment = led.get("comment") obj.model = model obj.serial_no = led.get("serial_no") return obj @staticmethod def update(cust_id, led): model = db.session.query(sdDeviceModel) \ .filter(sdDeviceModel.name == led.get("model_name")).first() sdn = led["serial_no"] obj = db.session.query(sdLed).filter(sdLed.serial_no == sdn).first() if obj: obj.cust_id = cust_id obj.name = led.get("name") obj.display_name = led.get("display_name") obj.comment = led.get("comment") obj.model = model obj.serial_no = led.get("serial_no") else: obj = sdLed.add(cust_id, led) return obj
class sdCustomer(db.Model): __tablename__ = "sd10_customers" id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20), unique=True, nullable=False, index=True, comment="Name") display_name = db.Column(db.String(50), nullable=False, comment="Display name") comment = db.Column(db.Text, comment="Comment") status = db.Column(db.Integer, nullable=False, server_default="0", comment="Status") create_time = db.Column(db.DateTime, nullable=False, server_default=func.now(), comment="Create time") update_time = db.Column(db.DateTime, nullable=False, server_default=func.now(), comment="Update time") cust_id = db.Column(db.Integer, db.ForeignKey("sd10_customers.id"), comment="Vendor of customer") vendors = db.relationship("sdCustomer", lazy="dynamic") users = db.relationship("sdUser", backref="cust", lazy="dynamic") controllers = db.relationship("sdController", backref="cust", lazy="dynamic") leds = db.relationship("sdLed", backref="cust", lazy="dynamic") device_groups = db.relationship("sdDeviceGroup", backref="cust", lazy="dynamic") schedules = db.relationship("sdSchedule", backref="cust", lazy="dynamic") codes = db.relationship("sdCode", backref="cust", lazy="dynamic") user_groups = db.relationship("sdUserGroup", backref="cust", lazy="dynamic") status_privilege = db.relationship("sdStatusPrivilege", backref="cust", lazy="dynamic") device_info = db.relationship("sdDeviceInfo", backref="cust", lazy="dynamic") cust_devices = db.relationship("sdDevice", foreign_keys="sdDevice.cust_id", backref="cust", lazy="dynamic") vendor_devices = db.relationship("sdDevice", foreign_keys="sdDevice.vendor_id", backref="vendor", lazy="dynamic") def __repr__(self): return f"<sdCustomer id={self.id}/name={self.name}/display_name={self.display_name}>" @staticmethod def search(cust_id): customer = db.session.query( sdCustomer.id).filter(sdCustomer.id == cust_id).first() return customer @staticmethod def search_by_name(cust_name): customer = sdCustomer.query.filter_by(name=cust_name).first() return customer @staticmethod def search_cust(cust_id): customer = db.session.query(sdCustomer).filter( sdCustomer.id == cust_id, sdCustomer.cust_id.is_(None)).all() return customer @staticmethod def get_vendors(cust_id): vendors = db.session.query(sdCustomer).filter( sdCustomer.cust_id == cust_id).all() return vendors @staticmethod def search_cust_obj(cust_id): customer = db.session.query(sdCustomer).filter( sdCustomer.id == cust_id).first() return customer
""" import datetime from flask_bcrypt import check_password_hash, generate_password_hash from flask_login import UserMixin from sqlalchemy.sql import and_, select from app.main import db, login_manager from app.main.models.enums import PriorityType from app.main.models.payments import Payment from app.main.models.posts import Post from app.main.models.tags import Tag userTagJunction = db.Table('userTagJunction', db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True), db.Column('keyword_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True), db.Column('priority', db.Enum(PriorityType), default=PriorityType.follow) ) userPostInteraction = db.Table('userPostInteraction', db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True), db.Column('post_id', db.Integer, db.ForeignKey('post.post_id'), primary_key=True), db.Column('rating', db.Integer, default=0), db.Column('save', db.Boolean, default=True) )
from app.main import db user_follows_course = db.Table( 'user_follows_course', db.Column('user_id', db.String(255), db.ForeignKey('user.id'), primary_key=True), db.Column('course_id', db.Integer, db.ForeignKey('course.id'), primary_key=True), )
lazy='dynamic', cascade='all, delete-orphan')) def get_recipients(self, round_org_id): receipt = self.doc_receipts.filter_by(round_org_id=round_org_id, doc_id=self.id).first() if receipt: return receipt.members else: return [] receipt_receivers = db.Table( 'doc_receipt_receivers_assoc', db.Column('receipt_id', db.Integer, db.ForeignKey('doc_receive_records.id')), db.Column('personal_info_id', db.Integer, db.ForeignKey('staff_personal_info.id'))) class DocReceiveRecord(db.Model): __tablename__ = 'doc_receive_records' id = db.Column(db.Integer, primary_key=True, autoincrement=True) predefined_comment = db.Column( db.String(255), info={ 'label': 'Predefined Comment', 'choices': [(c, c) for c in [u'แจ้งเพื่อทราบ', u'แจ้งเพื่อพิจารณา', u'ขอความร่วมมือเข้าร่วม']]
class OrderProduct(db.Model): #creating relationship table with order table and product table __tablename__ = "order_product" id = db.Column(db.Integer, primary_key=True) order_id = db.Column(db.Integer, db.ForeignKey('orders.id')) product_id = db.Column(db.Integer, db.ForeignKey('products.product_id'))
"""DB Model for imgLink table""" import datetime import os from logging import getLogger from flask import current_app from app.main import db LOG = getLogger(__name__) imgPostJunction = db.Table( 'imgPostJunction', db.Column('img_id', db.Integer, db.ForeignKey('imgLink.id'), primary_key=True), db.Column('post_id', db.Integer, db.ForeignKey('post.post_id'))) class ImgLink(db.Model): """ Description of ImgLink Model Rows ----------- :id: int [pk] :link: varchar (url) """ __tablename__ = 'imgLink' id = db.Column(db.Integer, primary_key=True)
class Resource(TimestampMixin, db.Model): __tablename__ = 'resources' id = db.Column(db.BigInteger, primary_key=True) category_id = db.Column(db.BigInteger, db.ForeignKey('categories.id', onupdate='CASCADE', ondelete='RESTRICT'), nullable=False) category = db.relationship('Category', backref='resources') user_id = db.Column(db.BigInteger, db.ForeignKey('users.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False) user = db.relationship('User', backref='resources') description = db.Column(db.UnicodeText(), nullable=True) type = db.Column(sau.ChoiceType(USER_RESOURCE_TYPES), index=True) quantity_available = db.Column(db.BigInteger) quantity_needed = db.Column(db.BigInteger) fulfilled = db.Column(db.Boolean, nullable=False, default=False, server_default='0') name = db.Column(db.UnicodeText(), nullable=False) picture = db.Column(db.UnicodeText()) requested = db.Column(db.Boolean(), nullable=True, default=False) def __setattr__(self, name, value): if name == 'picture': if value: filename = '{}.{}'.format(self.id, value.filename.split('.')[-1]) super().__setattr__( 'picture', upload_file(value, 'resource_pictures', filename)) else: super().__setattr__(name, value) @property def picture_url(self): if not self.picture: return None return file_url(self.picture) @property def quantity_remaining(self): if self.type != 'HAVE': return None fulfillment = ResourceFulfillment.query.filter( ResourceFulfillment.fulfilling_resource == self, ResourceFulfillment.confirmed_by_recipient == True, ) return self.quantity_available - sum( [f.fulfilled_quantity for f in fulfillment]) @property def quantity_fulfilled(self): if self.type != 'NEED': return None fulfillment = ResourceFulfillment.query.filter( ResourceFulfillment.fulfilled_resource == self, ResourceFulfillment.confirmed_by_recipient == True, ) return self.quantity_needed - sum( [f.fulfilled_quantity for f in fulfillment]) def serialize(self): return { 'id': self.id, 'name': self.name, 'category': self.category.name, 'user_id': self.user.id, 'type': self.type.code, 'quantity_available': self.quantity_available, 'quantity_needed': self.quantity_needed, 'fulfilled': self.fulfilled, }