class users(db.Model): _id = db.Column("id", db.Integer, primary_key=True) username = db.Column("username", db.Text(16)) email = db.Column("email", db.Text(320)) password = db.Column("password", db.Text(16383)) type = db.Column("type", db.Text(60)) def __init__(self, username, email, password, type): self.username = username self.email = email self.password = password self.type = type
class comments(db.Model): _id = db.Column("id", db.Integer, primary_key=True) username = db.Column("username", db.Text(16)) comment = db.Column("comment", db.Text(1000)) comment_date = db.Column("comment_date", db.Text(32), default=datetime.utcnow, nullable=False) postID = db.Column("postID", db.Text(99999999)) def __init__(self, username, comment, postID): self.username = username self.comment = comment self.postID = postID
class Banner(db.Model): __tablename__ = 'tb_banner' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255)) # 新闻标题 time = db.Column(db.Date) # 发布时间 photo = db.Column(db.String(255)) # 图片 content = db.Column(db.Text()) # 内容
class posts(db.Model): _id = db.Column("id", db.Integer, primary_key=True) title = db.Column("title", db.Text(32)) post = db.Column("post", db.Text(1638300)) posted_by = db.Column("posted_by", db.Text(16)) created_date = db.Column("created_date", db.Text(32), default=datetime.utcnow, nullable=False) views = db.Column("views", db.Text(99999999)) def __init__(self, title, post, posted_by, views): self.title = title self.post = post self.posted_by = posted_by self.views = views
class Vehicle(db.Model): """This class is the database model for a vehicle""" __tablename__ = 'vehicles' id = db.Column(db.Integer, primary_key=True, nullable=False, unique=True) number = db.Column(db.Integer, nullable=False) description = db.Column(db.Text(), nullable=True) name = db.Column(db.String(128), nullable=True) type = db.Column(db.Enum(VehicleType), nullable=False, default=VehicleType.BUS) created_by = db.Column(db.Integer, nullable=False) def __init__(self, id: int, number: int, description: str, vehicle_type: VehicleType or int, user_id: id, name: str): if type(vehicle_type) is int: vehicle_type = VehicleType(vehicle_type) self.id = id self.number = number self.description = description self.type = vehicle_type self.created_by = user_id self.name = name def serialize(self): return { 'id': int(self.id), 'number': int(self.number), 'description': str(self.description), 'type': str(self.type), 'created_by': int(self.created_by), 'name': str(self.name) }
class Image(db.Model): __tablename__ = "images" id = db.Column(db.Integer, primary_key=True, autoincrement=True) path = db.Column(db.Text(), nullable=False) names = db.Column(db.Text(), nullable=True) def __init__(self, path, names=None): self.path = path self.names = names def to_json(self): return { 'id': self.id, 'path': self.path, 'names': self.names, }
class News(db.Model): __tablename__ = 'tb_news' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255)) # 新闻标题 time = db.Column(db.Date) # 发布时间 photo = db.Column(db.String(255)) # 图片 content = db.Column(db.Text()) # 内容 type = db.Column(db.Integer) #文章类型 isTop = db.Column(db.Boolean, default=0) #默认不置顶
class Item(db.Model): __tablename__ = 'items' id = db.Column(db.BigInteger, primary_key=True) title = db.Column(db.String(length=100), nullable=False) description = db.Column(db.Text(),nullable=False) price = db.Column(db.DECIMAL(precision=20, scale=4), nullable=False) quantity = db.Column(db.Integer(),default=0,nullable=False) discount = db.Column(db.Integer(),default=0,nullable=False) details = db.Column(db.Text()) images = db.Column(db.Text, nullable=False) product_id = db.Column(db.BigInteger, db.ForeignKey('products.id'),nullable=False) product = db.relationship('Product') orders = db.relationship('Order') created = db.Column(db.TIMESTAMP, default=datetime.datetime.now, nullable=False) updated = db.Column(db.TIMESTAMP, default=datetime.datetime.now, nullable=False, onupdate=datetime.datetime.now) def __str__(self): return " محصول :"+str(self.product.title) + " آیتم: " + self.title
class Post(db.Model): id = db.Column(db.Integer, primary_key=True) # author = title = db.Column(db.String(255), unique=True, nullable=False) content = db.Column(db.Text()) # category = pub_date = db.Column(db.DateTime) created_at = db.Column(db.DateTime, default=dt.datetime.now) updated_at = db.Column(db.DateTime, default=dt.datetime.now, onupdate=dt.datetime.now) def __repr__(self): return '<Post %d: %r' % (self.id, self.title)
class Product(db.Model, AbstractModelWithId): name = db.Column(db.Text(), nullable=False) price = db.Column(db.Float(), nullable=False) url = db.Column(db.Text(), nullable=False) records = db.relationship('Record', uselist=True, lazy=True) # followers = db.relationship('UserToProduct') @classmethod def filter(cls, filters): query = AbstractModelWithId.filter(cls, filters) if 'name' in filters: query = query.filter_by(name=filters['name']) elif 'url' in filters: query = query.filter_by(url=filters['url']) return query def create(self): super().create() r = Record(product_id=self.id, price=self.price) self.records.append(r) db.session.flush() def update(self): super().update() r = Record(product_id=self.id, price=self.price) self.records.append(r) db.session.flush() @staticmethod def extract_data(url): response = requests.post(app.config.get('SCOUTER_ENDPOINT'), data={ "url": url }).json() return response.get('name'), response.get('price')
class Project(db.Model): __tablename__ = 'projects' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(175), unique=True, nullable=False) description = db.Column(db.Text) #--------------------------- region = db.Column(db.Integer, db.ForeignKey('regions.id')) district = db.Column(db.Integer, db.ForeignKey('districts.id')) subdistrict = db.Column(db.Integer, db.ForeignKey('subdistricts.id')) village = db.Column(db.Integer, db.ForeignKey('villages.id')) #------------------------------------ baseline = db.Column(db.Text()) performance_indicator = db.Column(db.Text) budget = db.Column(db.Float()) author = db.Column(db.Integer, db.ForeignKey('users.id')) posted_date = db.Column(db.DateTime) start_date = db.Column(db.DateTime) est_completion = db.Column(db.DateTime) mark_complete = db.Column(db.Boolean, default=False) activities = db.relationship('Activity', backref='projects', cascade='all, delete-orphan', lazy='dynamic') remarks = db.relationship('Remark', backref='projects', cascade='all, delete-orphan', lazy='dynamic') beneficiary = db.Column(db.Integer, db.ForeignKey('beneficiaries.id')) sector = db.Column(db.Integer, db.ForeignKey('sectors.id')) media = db.relationship('Media', secondary=projects_media, backref='projects') def __init__(self, id, title, description, baseline, performance_indicator, budget, remark, remark_author, author, posted_date, start_date, est_completion, mark_complete, sector): self.id = id self.title = title self.description = description self.baseline = baseline self.performance_indicator = performance_indicator self.budget = budget self.author = author self.posted_date = posted_date self.start_date = start_date self.est_completion = est_completion self.mark_complete = mark_complete self.sector = sector
class Expense(db.Model): id = db.Column(db.Integer, primary_key=True) category = db.Column(db.String(150), nullable=False) amount = db.Column(db.Numeric(6, 2)) month_id = db.Column(db.Integer, db.ForeignKey('month.id'), nullable=False) description = db.Column(db.Text()) short_descr = db.Column(db.String(83), nullable=True) def __repr__(self): return f"Expense('{self.id}', '{self.amount}')" def __init__(self, **kwargs): super(Expense, self).__init__(**kwargs) self.set_short_descr() def set_short_descr(self): if self.description and len(self.description) > 80: self.short_descr = self.description[:80] + '...' else: self.short_descr = None
class Task(db.Model): __tablename__ = "background_tasks" id = db.Column(db.VARCHAR(255), primary_key=True) action = db.Column(db.VARCHAR(255)) state = db.Column(db.VARCHAR(255)) result = db.Column(db.Text()) created_on = db.Column(db.DateTime(timezone=True)) query_id = db.Column(db.Integer, db.ForeignKey('youtube_queries.id')) def __init__(self, id, action): self.id = id self.action = action self.created_on = datetime.datetime.now() def as_dict(self): return { 'id': self.id, 'created_on': self.created_on, 'action': self.action, 'state': self.state, 'result': json.loads(self.result) if self.result is not None else None }
class User(UserMixin, db.Model): __tablename__ = "tb_users" id = db.Column(db.Integer, primary_key=True) # 用户名 username = db.Column(db.String(255), unique=True, index=True) # 密码 password_hash = db.Column(db.String(128)) # 注册时间 member_since = db.Column(db.DateTime(), default=datetime.utcnow) # 真实姓名 name = db.Column(db.String(64)) # 真实姓名 province = db.Column(db.String(64)) city = db.Column(db.String(64)) # 最后登陆时间 last_seen = db.Column(db.DateTime, default=datetime.utcnow) # 角色外键id role_id = db.Column(db.Integer, db.ForeignKey('tb_roles.id'), default=3) # 个人简介 about_me = db.Column(db.Text()) # 性别 sex = db.Column(db.Boolean(), default=0) # 身份选择 status = db.Column(db.Integer()) # 入学班级 grade = db.Column(db.String(255)) # 联系人 contact = db.Column(db.String(255)) # 出身日期 birthday = db.Column(db.Date) # 验证信息 identInfor = db.Column(db.Text) # 电话 phone = db.Column(db.BigInteger()) # 邮箱 email = db.Column(db.String(255), index=True) # 账号状态。1默认激活状态 state = db.Column(db.Boolean, default=1) # 入会审核,0默认为审核中 verify = db.Column(db.Boolean, default=0) enter = db.relationship('Enterprise', backref=db.backref('tb_users'), uselist=False) @property def password(self): raise AttributeError(u'密码不可读取') @password.setter def password(self, password): self.password_hash = generate_password_hash(password) def verify_passwrod(self, password): return check_password_hash(self.password_hash, password) def can(self, permissions): return self.role is not None and (self.role.permissions & permissions) == permissions def is_administrator(self): return self.can(Permission.ADMINISTER) def ping(self): self.last_seen = datetime.utcnow() db.session.add(self) @login_manager.user_loader def load_user(user_id): return User.query.get(int(user_id)) def __repr__(self): return '<User %r>' % (self.name) role = db.relationship('Role')
class AlumniIntro(db.Model): __tablename__ = 'tb_summary' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255)) # 标题 content = db.Column(db.Text()) # 内容
class Utente(UserMixin, db.Model): __tablename__ = 'utenti' id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(64), unique=True, index=True) username = db.Column(db.String(64), unique=True, index=True) password_hash = db.Column(db.String(128)) confirmed = db.Column(db.Boolean, default=False) name = db.Column(db.String(64)) location = db.Column(db.String(64)) about_me = db.Column(db.Text()) member_since = db.Column(db.DateTime(), default=datetime.utcnow) last_seen = db.Column(db.DateTime(), default=datetime.utcnow) avatar_hash = db.Column(db.String(32)) # FK - Ruolo dell'utente role_id = db.Column(db.Integer, db.ForeignKey('ruoli.id')) ''' posts = db.relationship('Post', backref='author', lazy='dynamic') followed = db.relationship('Follow', foreign_keys=[Follow.follower_id], backref=db.backref('follower', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') followers = db.relationship('Follow', foreign_keys=[Follow.followed_id], backref=db.backref('followed', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') comments = db.relationship('Comment', backref='author', lazy='dynamic') @staticmethod def add_self_follows(): for user in Utente.query.all(): if not user.is_following(user): user.follow(user) db.session.add(user) db.session.commit() ''' ''' Utile per il popolamento dei dati e per i test ''' @staticmethod def insert_test_users(): ''' admin_role = Ruolo.query.filter_by(name='Administrator').first() std_role = Ruolo.query.filter_by(name='User').first() ''' utenti = [ ("*****@*****.**", "mariateresa", "pwd1"), ("*****@*****.**", "davcom", "pwd2"), ] for ut in utenti: ut_db = Utente.query.filter_by(email=ut[0]).first() if ut_db is None: ut_db = Utente(email=ut[0], username=ut[1], password=ut[2], confirmed=True) db.session.add(ut_db) db.session.commit() def __init__(self, **kwargs): super(Utente, self).__init__(**kwargs) if self.ruolo is None: if self.email == current_app.config['PBG_ADMIN']: self.ruolo = Ruolo.query.filter_by( name='Administrator').first() if self.ruolo is None: self.ruolo = Ruolo.query.filter_by(default=True).first() if self.email is not None and self.avatar_hash is None: self.avatar_hash = self.gravatar_hash() ''' self.follow(self) ''' @property def password(self): raise AttributeError('password is not a readable attribute') @password.setter def password(self, password): self.password_hash = generate_password_hash(password) def verify_password(self, password): return check_password_hash(self.password_hash, password) # Per conferma mail def generate_confirmation_token(self, expiration=3600): s = Serializer(current_app.config['SECRET_KEY'], expiration) return s.dumps({'confirm': self.id}).decode('utf-8') # Per conferma mail def confirm(self, token): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token.encode('utf-8')) print(data) except: return False if data.get('confirm') != self.id: return False self.confirmed = True db.session.add(self) return True # Per conferma mail (generazione token) def generate_reset_token(self, expiration=3600): s = Serializer(current_app.config['SECRET_KEY'], expiration) return s.dumps({'reset': self.id}).decode('utf-8') # Per conferma mail @staticmethod def reset_password(token, new_password): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token.encode('utf-8')) except: return False user = Utente.query.get(data.get('reset')) if user is None: return False user.password = new_password db.session.add(user) return True # Per conferma mail def generate_email_change_token(self, new_email, expiration=3600): s = Serializer(current_app.config['SECRET_KEY'], expiration) return s.dumps({ 'change_email': self.id, 'new_email': new_email }).decode('utf-8') # Per cambio mail def change_email(self, token): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token.encode('utf-8')) except: return False if data.get('change_email') != self.id: return False new_email = data.get('new_email') if new_email is None: return False if self.query.filter_by(email=new_email).first() is not None: return False self.email = new_email self.avatar_hash = self.gravatar_hash() db.session.add(self) return True # Ruoli def can(self, perm): return self.ruolo is not None and self.ruolo.has_permission(perm) def is_administrator(self): return self.can(Permission.ADMIN) def ping(self): self.last_seen = datetime.utcnow() db.session.add(self) # Avatar def gravatar_hash(self): return hashlib.md5(self.email.lower().encode('utf-8')).hexdigest() def gravatar(self, size=100, default='identicon', rating='g'): url = 'https://secure.gravatar.com/avatar' hash = self.avatar_hash or self.gravatar_hash() return '{url}/{hash}?s={size}&d={default}&r={rating}'.format( url=url, hash=hash, size=size, default=default, rating=rating) ''' def follow(self, user): if not self.is_following(user): f = Follow(follower=self, followed=user) db.session.add(f) def unfollow(self, user): f = self.followed.filter_by(followed_id=user.id).first() if f: db.session.delete(f) def is_following(self, user): if user.id is None: return False return self.followed.filter_by( followed_id=user.id).first() is not None def is_followed_by(self, user): if user.id is None: return False return self.followers.filter_by( follower_id=user.id).first() is not None @property def followed_posts(self): return Post.query.join(Follow, Follow.followed_id == Post.author_id)\ .filter(Follow.follower_id == self.id) def to_json(self): json_user = { 'url': url_for('api.get_user', id=self.id), 'username': self.username, 'member_since': self.member_since, 'last_seen': self.last_seen 'posts_url': url_for('api.get_user_posts', id=self.id), 'followed_posts_url': url_for('api.get_user_followed_posts', id=self.id), 'post_count': self.posts.count() } return json_user ''' def generate_auth_token(self, expiration): s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration) return s.dumps({'id': self.id}).decode('utf-8') @staticmethod def verify_auth_token(token): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token) except: return None return Utente.query.get(data['id']) def __repr__(self): return '<Utente %r>' % self.username
class Recuit(db.Model): __tablename__ = 'tb_recuit' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255)) # 标题 content = db.Column(db.Text()) # 内容
class Article(db.Model, AbstractModelWithAttributes): title = db.Column(db.String(100), nullable=False) subtitle = db.Column(db.String(240), nullable=True) body = db.Column(db.Text(), nullable=False) is_publish = db.Column(db.Boolean(), default=False, nullable=False) writer = db.Column(db.String(40), db.ForeignKey("user.id"), nullable=False) reactions = db.relationship('Reaction', uselist=True, lazy=True) comments = db.relationship( 'Comment', uselist=True, lazy=True, primaryjoin= "and_(Article.id==Comment.article_id, Comment.enabled==True)") tags = db.relationship('ArticleRelationTag', uselist=True, lazy=True) @classmethod def filter(cls, filters): query = AbstractModelWithAttributes.filter(cls, filters) if 'is_publish' in filters: query = query.filter_by(is_publish=filters['is_publish']) if 'title' in filters: query = query.filter( Article.title.ilike('%{}%'.format(filters['title']))) if 'subtitle' in filters: query = query.filter( Article.subtitle.ilike('%{}%'.format(filters['subtitle']))) if 'body' in filters: query = query.filter( Article.body.ilike('%{}%'.format(filters['body']))) if 'q' in filters: query = query.filter( Article.body.ilike('%{}%'.format(filters['q'])) | Article.title.ilike('%{}%'.format(filters['q']))) if 'writer' in filters: query = query.filter_by(writer=filters['writer']) return query # TODO def get_tags_as_string(self): pass # TODO def save_tag_from_string(self, tags_as_string): pass def get_reactions(self): counter_reactions = Counter() for emotion in list(map(lambda x: x.emotion.name, self.reactions)): counter_reactions[emotion] += 1 return counter_reactions def check_unique_reactions(self, x): return x in set(list(map(lambda x: x.emotion.name, self.reactions))) def get_number_of_reactions(self): return len(self.reactions) # TODO def get_similar_articles(self): pass
class Cooperate(db.Model): __tablename__ = 'tb_cooperate' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(255)) # 标题 content = db.Column(db.Text()) # 内容
class Utente(UserMixin, db.Model): __tablename__ = 'utenti' id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(64), unique=True, index=True) username = db.Column(db.String(64), unique=True, index=True) password_hash = db.Column(db.String(128)) confirmed = db.Column(db.Boolean, default=False) name = db.Column(db.String(64)) location = db.Column(db.String(64)) about_me = db.Column(db.Text()) ''' The two timestamps are given a default value of the current time. Note that the datetime.utcnow is missing the () at the end. This is because the default argument in db.Column() can take a function as a value ''' member_since = db.Column(db.DateTime(), default=datetime.utcnow) # last_seen è aggiornato dalla funzione ping, chiamata da before_request ad ogni request last_seen = db.Column(db.DateTime(), default=datetime.utcnow) avatar_hash = db.Column(db.String(32)) # FK - Ruolo dell'utente role_id = db.Column(db.Integer, db.ForeignKey('ruoli.id')) ''' Per Blog ''' posts = db.relationship('Post', backref='author', lazy='dynamic') comments = db.relationship('Comment', backref='author', lazy='dynamic') ''' Utile per il popolamento dei dati e per i test ''' @staticmethod def insert_test_users(): # Il ruolo degli utenti è quello di default utenti = [ ("*****@*****.**", "maurici", "pwd1"), ("*****@*****.**", "davcom", "pwd2"), ("*****@*****.**", "burlesco70", "burlesco70"), ] for ut in utenti: ut_db = Utente.query.filter_by(email=ut[0]).first() if ut_db is None: ut_db = Utente(email=ut[0], username=ut[1], password=ut[2], confirmed=True) db.session.add(ut_db) db.session.commit() def __init__(self, **kwargs): super(Utente, self).__init__(**kwargs) # Gestione dell'amministratore: email da variabile di ambiente if self.ruolo is None: if self.email == current_app.config['PBG_ADMIN']: self.ruolo = Ruolo.query.filter_by(name='Admin').first() if self.ruolo is None: self.ruolo = Ruolo.query.filter_by(default=True).first() if self.email is not None and self.avatar_hash is None: self.avatar_hash = self.gravatar_hash() @property def password(self): raise AttributeError('La password non si può leggere') @password.setter def password(self, password): self.password_hash = generate_password_hash(password) def verify_password(self, password): return check_password_hash(self.password_hash, password) # Per conferma mail def generate_confirmation_token(self, expiration=3600): s = Serializer(current_app.config['SECRET_KEY'], expiration) return s.dumps({'confirm': self.id}).decode('utf-8') # Per conferma mail def confirm(self, token): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token.encode('utf-8')) except: return False if data.get('confirm') != self.id: return False self.confirmed = True db.session.add(self) return True # Per conferma mail (generazione token) def generate_reset_token(self, expiration=3600): s = Serializer(current_app.config['SECRET_KEY'], expiration) return s.dumps({'reset': self.id}).decode('utf-8') # Per conferma mail @staticmethod def reset_password(token, new_password): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token.encode('utf-8')) except: return False user = Utente.query.get(data.get('reset')) if user is None: return False user.password = new_password db.session.add(user) return True # Per conferma mail def generate_email_change_token(self, new_email, expiration=3600): s = Serializer(current_app.config['SECRET_KEY'], expiration) return s.dumps({ 'change_email': self.id, 'new_email': new_email }).decode('utf-8') # Per cambio mail def change_email(self, token): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token.encode('utf-8')) except: return False if data.get('change_email') != self.id: return False new_email = data.get('new_email') if new_email is None: return False if self.query.filter_by(email=new_email).first() is not None: return False self.email = new_email self.avatar_hash = self.gravatar_hash() db.session.add(self) return True # Ruoli def can(self, perm): return self.ruolo is not None and self.ruolo.has_permission(perm) def is_administrator(self): return self.can(Permission.ADMIN) def ping(self): self.last_seen = datetime.utcnow() db.session.add(self) # Avatar def gravatar_hash(self): return hashlib.md5(self.email.lower().encode('utf-8')).hexdigest() def gravatar(self, size=100, default='identicon', rating='g'): url = 'https://secure.gravatar.com/avatar' hash = self.avatar_hash or self.gravatar_hash() return '{url}/{hash}?s={size}&d={default}&r={rating}'.format( url=url, hash=hash, size=size, default=default, rating=rating) ''' def follow(self, user): if not self.is_following(user): f = Follow(follower=self, followed=user) db.session.add(f) def unfollow(self, user): f = self.followed.filter_by(followed_id=user.id).first() if f: db.session.delete(f) def is_following(self, user): if user.id is None: return False return self.followed.filter_by( followed_id=user.id).first() is not None def is_followed_by(self, user): if user.id is None: return False return self.followers.filter_by( follower_id=user.id).first() is not None @property def followed_posts(self): return Post.query.join(Follow, Follow.followed_id == Post.author_id)\ .filter(Follow.follower_id == self.id) ''' # Per la gestione API - Serializing Resources to JSON def to_json(self): json_user = { 'url': url_for('api.get_utente', id=self.id), 'username': self.username, 'member_since': self.member_since, 'last_seen': self.last_seen, 'posts_url': url_for('api.get_posts_utente', id=self.id), 'post_count': self.posts.count() } return json_user def generate_auth_token(self, expiration): s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration) return s.dumps({'id': self.id}).decode('utf-8') @staticmethod def verify_auth_token(token): s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token) except: return None return Utente.query.get(data['id']) def __repr__(self): return '<Utente %r>' % self.username
class YoutubeVideoMeta(db.Model): __tablename__ = "meta" id = db.Column(db.VARCHAR(12), db.ForeignKey("video.id"), primary_key=True, unique=True) snippet_publishedAt = db.Column(db.DateTime(timezone=True)) snippet_channel_id = db.Column(db.VARCHAR(50)) snippet_channel_title = db.Column(db.VARCHAR(100)) snippet_title = db.Column(db.Text()) snippet_description = db.Column(db.VARCHAR(5000)) snippet_category_id = db.Column(db.Integer, db.ForeignKey("categories.id")) snippet_tags = db.Column(db.Text()) snippet_liveBroadcastContent = db.Column(db.VARCHAR(10)) statistics_viewCount = db.Column(db.Integer) statistics_likeCount = db.Column(db.Integer) statistics_dislikeCount = db.Column(db.Integer) #deprecated since august 28, 2015. always set to zero statistics_favoriteCount = db.Column(db.Integer) statistics_commentCount = db.Column(db.Integer) status_uploadStatus = db.Column(db.VARCHAR(20)) status_privacyStatus = db.Column(db.VARCHAR(20)) status_license = db.Column(db.VARCHAR(20)) status_embeddable = db.Column(db.BOOLEAN) status_publicStatsViewable = db.Column(db.BOOLEAN) #the duration of the video is given as a ISO 8601 time interval contentDetails_duration = db.Column(db.VARCHAR(20)) #the ISO 8601 time interval calculated into seconds contentDetails_durationAsSeconds = db.Column(db.Integer) contentDetails_dimension = db.Column(db.VARCHAR(2)) contentDetails_definition = db.Column(db.VARCHAR(2)) #based on google documentation this field is a string, containing 'true' or 'false', if you want to use boolean instead, you have to manually convert the string into bool contentDetails_caption = db.Column(db.String(4)) contentDetails_licensedContent = db.Column(db.BOOLEAN) recordingDetails_location_latitude = db.Column(db.Float(precision='10,6')) recordingDetails_location_longitude = db.Column(db.Float(precision='10,6')) recordingDetails_location_altitude = db.Column(db.Float(precision='10,6')) recordingDetails_recordingDate = db.Column(db.DateTime(timezone=True)) def getCategoryId(self): return self.snippet_category_id def as_dict(self): return { 'snippet': { 'publishedAt': self.snippet_publishedAt, 'channelId': self.snippet_channel_id, 'channelTitle': self.snippet_channel_title, 'title': self.snippet_title, 'description': self.snippet_description, 'categoryId': self.snippet_category_id, 'tags': json.loads(self.snippet_tags) if self.snippet_tags != '' else None } }
class ReportAbstract(db.Model): __abstract__ = True __metaclass__ = abc.ABCMeta report_id = db.Column(db.Integer, primary_key=True, autoincrement=True) date_and_hour_of_emission = db.Column(db.DateTime(timezone=True), default=datetime.utcnow, nullable=False) description_report = db.Column(db.Text(convert_unicode=True)) @declared_attr def farm_id(cls): return db.Column(db.Integer, db.ForeignKey('farm.farm_id')) # farm = FarmModel.query.filter_by(farm_id=int(self.farm_id)).first() def __init__(self, farm_id): self.farm_id = farm_id def CONST_TEMPLATE_METHOD(self, user_id): header = self.generate_header(user_id) farm_info = self.generate_farm_info() metric = self.generate_metric() graphic = self.generate_graphic() return { 'header': header, 'farm_info': farm_info, 'metric': metric, 'graphic': graphic } def generate_header(self, user_id): months = { 'January': 'Janeiro', 'February': 'Fevereiro', 'March': 'Março', 'April': 'Abril', 'May': 'Maio', 'June': 'Junho', 'July': 'Julho', 'August': 'Agosto', 'September': 'Setembro', 'October': 'Outubro', 'November': 'Novembro', 'December': 'Dezembro' } now = datetime.now() current_time = now.strftime('%d/%m/%y') user = UserModel.query.filter_by(user_id=int(user_id)).first() farm = FarmModel.query.filter_by(farm_id=int(self.farm_id)).first() title = 'Relatório da fazenda ' + farm.farm_name + ' ' + current_time creation_date = '{} de {} de {}'.format(now.strftime('%d'), months[now.strftime('%B')], now.strftime('%Y')) creation_hour = now.strftime('%H:%M') return { 'title': title, 'creation_date': creation_date, 'creation_hour': creation_hour, 'created_by': user.fullname, 'refers_to': farm.farm_name, } def generate_farm_info(self): pass # farm = FarmModel.query.filter_by(farm_id=int(self.farm_id)).first() # pregnant_dairy_cattle_quantity, dairy_cattle_quantity = 0, 0 # beef_cattle_quantity = len(farm.beef_cattles) # employee_quantity = 0 # for user in farm.users: # if not user.is_proprietary: # employee_quantity += 1 # for dairy_cattle in farm.dairy_cattles: # if dairy_cattle.pregnant: # pregnant_dairy_cattle_quantity += 1 # dairy_cattle_quantity += 1 # return { # 'dairy_cattle_quantity': dairy_cattle_quantity, # 'pregnant_dairy_cattle_quantity': pregnant_dairy_cattle_quantity, # 'beef_cattle_quantity': beef_cattle_quantity, # 'employee_quantity': employee_quantity # } @abc.abstractmethod def generate_metric(self): pass @abc.abstractmethod def generate_graphic(self): pass
class YoutubeQuery(db.Model): __tablename__ = "youtube_queries" id = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) queryHash = db.Column(db.String(255), nullable=False) queryRaw = db.Column(db.Text(), nullable=False) apikey_id = db.Column(db.Integer, db.ForeignKey('apikeys.id')) tasks = db.relationship("Task", backref="youtube_queries") videos = relationship("QueryVideoMM", backref="queries") def __init__(self, queryRaw): self.queryHash = base64.urlsafe_b64encode(queryRaw) self.queryRaw = queryRaw def count_videos(self): """Returns the amount of videos fetched by this query""" return len(self.videos) def count_tasks(self): """Returns of many tasks where performed for this query""" return len(self.tasks) def count_video_meta(self): """Returns the amount of meta data associated to this query""" metas = YoutubeVideoMeta.query.outerjoin( (QueryVideoMM, QueryVideoMM.video_id == YoutubeVideoMeta.id)).filter_by( youtube_query_id=self.id) count = metas.count() return count def count_dash(self): """Returns the amount of dash representations associated to this query""" dashs = VideoRepresentation.query.outerjoin( (QueryVideoMM, QueryVideoMM.video_id == VideoRepresentation.video_id)).filter_by( youtube_query_id=self.id) count = dashs.count() return count def count_comments(self): """Returns the amount of dash representations associated to this query""" comments = YoutubeComment.query.outerjoin( (QueryVideoMM, QueryVideoMM.video_id == YoutubeComment.video_id)).filter_by( youtube_query_id=self.id) count = comments.count() return count def get_statistics_dayHistogram(self): """Returns a dictonary which contains an aggregation of day=>amount""" dates_query = db.session.query( YoutubeVideoMeta, db.func.count().label("count"), db.func.date( YoutubeVideoMeta.snippet_publishedAt).label("date")).outerjoin( (QueryVideoMM, QueryVideoMM.video_id == YoutubeVideoMeta.id )).filter_by(youtube_query_id=self.id).group_by( db.func.strftime( '%Y', YoutubeVideoMeta.snippet_publishedAt), db.func.strftime( '%m', YoutubeVideoMeta.snippet_publishedAt), db.func.strftime( '%d', YoutubeVideoMeta.snippet_publishedAt)).order_by( YoutubeVideoMeta.snippet_publishedAt) dates = dates_query.all() return [{"date": date.date, "count": date.count} for date in dates] def get_statistics_dash(self): """Returns a dictonary containing an aggregation of the DASH representation and amount of videos""" query = dates_query = db.session.query( VideoRepresentation, db.func.count().label("count"), VideoRepresentation.height.label("height")).outerjoin( (QueryVideoMM, QueryVideoMM.video_id == VideoRepresentation.video_id )).filter_by(youtube_query_id=self.id).group_by( VideoRepresentation.height).order_by( VideoRepresentation.height) representations = query.all() return [{ "height": representation.height if representation.height != '' else 'audio', "count": representation.count } for representation in representations] def getAggregations(self, table, field, forQuery=False): """Gets an aggregation of the table.field max,min,avg,sum,stdev forQuery: parameter to select the current query, or global """ if forQuery: res = db.session.query( table, db.func.stdev(field).label("stdev"), db.func.max(field).label("max"), db.func.min(field).label("min"), db.func.sum(field).label("sum"), db.func.avg(field).label("avg")).filter(field != '').outerjoin( (QueryVideoMM, QueryVideoMM.video_id == YoutubeVideoMeta.id)).filter_by( youtube_query_id=self.id) else: res = db.session.query( table, db.func.stdev(field).label("stdev"), db.func.max(field).label("max"), db.func.min(field).label("min"), db.func.sum(field).label("sum"), db.func.avg(field).label("avg")).filter(field != '') row = res.one() return row def get_statistics_percentile(self, table, field): """Gets the 0.1-1.0 percentile of table.field""" dict = {} for x in xrange(1, 11): dict[x] = db.session.execute( "select statistics_viewCount as percentile from meta order by percentile asc limit 1 OFFSET 19346*" + str(x) + "/10-1").first().percentile def get_statistics_assocQuery(self): """Performs a backward search to find all queries which have an intersection with the videos, this query has fetched""" backrefs = db.session.execute( "select first.youtube_query_id as queryid, count(*) as count, second.youtube_query_id as backrefQuery from query_video_mm as first JOIN query_video_mm as second on second.video_id=first.video_id WHERE first.youtube_query_id=" + str(self.id) + " AND backrefQuery!=" + str(self.id) + " GROUP BY backrefQuery ORDER BY count DESC") backrefsQueries = [] for row in backrefs: backrefsQueries.append({ 'query': YoutubeQuery.query.filter_by( id=row.backrefQuery).first().as_dict(), 'count': row.count, 'reference': len(self.videos) }) return backrefsQueries def get_statistic_categories(self): categories = db.session.execute( "SELECT count(*) AS total, meta.snippet_category_id AS category_id FROM meta LEFT OUTER JOIN query_video_mm ON query_video_mm.video_id = meta.id WHERE query_video_mm.youtube_query_id =" + str(self.id) + " GROUP BY meta.snippet_category_id ORDER BY total DESC LIMIT 10") return [{ "id": row.category_id, "count": row.total, "name": VideoCategory.query.filter_by(id=row.category_id).first().title } for row in categories] def get_statistic_section(self, section): """Returns only a section of the complete statistics""" if section == "summary": return { 'data': { 'videos': len(self.videos), 'meta': self.count_video_meta(), 'dash': self.count_dash(), 'comments': self.count_comments(), }, 'global': { 'videos': YoutubeVideo.query.count(), 'meta': YoutubeVideoMeta.query.count(), } } elif section == "publishedAt": return self.get_statistics_dayHistogram() elif section == "intersection": return self.get_statistics_assocQuery() elif section == "statistics_likeCount" or section == "statistics_dislikeCount" or section == "statistics_commentCount" or section == "statistics_viewCount": table = YoutubeVideoMeta if section == "statistics_likeCount": field = YoutubeVideoMeta.statistics_likeCount elif section == "statistics_dislikeCount": field = YoutubeVideoMeta.statistics_dislikeCount elif section == "statistics_commentCount": field = YoutubeVideoMeta.statistics_commentCount elif section == "statistics_viewCount": field = YoutubeVideoMeta.statistics_viewCount elif section == "dash_representations": return self.get_statistics_dash() globalStat = self.getAggregations(table, field, forQuery=False) queryStat = self.getAggregations(table, field, forQuery=True) #queryPercentile = self.get_statistics_percentile(table,field) return { section: { 'data': { 'max': queryStat.max, 'min': queryStat.min, 'sum': queryStat.sum, 'avg': queryStat.avg, 'stdev': queryStat.stdev }, #'percentile': queryPercentile, 'global': { 'max': globalStat.max, 'min': globalStat.min, 'sum': globalStat.sum, 'avg': globalStat.avg, 'stdev': globalStat.stdev } } } elif section == "category": categories = self.get_statistic_categories() return categories def as_dict(self): return { 'id': self.id, 'user_id': self.user_id, 'queryHash': self.queryHash, 'queryRaw': json.loads(self.queryRaw), 'tasks': [task.as_dict() for task in self.tasks] }