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]
}
