class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
account_id = db.Column(db.Integer, unique=True)
user_id = db.Column(db.Integer)
username = db.Column(db.String(100))
role = db.Column(db.String(30))
is_banned = db.Column(db.Boolean)
end_ban_date = db.Column(db.DateTime, nullable=True)
reputation = db.Column(db.Integer)
profile_image = db.Column(db.String(200))
profile_link = db.Column(db.String(200))
def __init__(self,
account_id,
user_id,
username,
reputation,
profile_image,
profile_link,
role="user",
is_banned=False):
self.account_id = account_id
self.user_id = user_id
self.username = username
self.reputation = reputation
self.profile_image = profile_image
self.profile_link = profile_link
self.role = role
self.is_banned = is_banned
def __repr__(self):
return '<User %r>' % str(self.id)
class EmailVerificationRequest(db.Model):
"""EmailVerificationRequest object
"""
key = db.Column(db.String(64), primary_key=True)
fk_user = db.Column(db.Integer, db.ForeignKey('user.id'))
create_ts = db.Column(db.DateTime, default=datetime.datetime.utcnow, \
nullable=False)
class TelegramTextMessage(db.Model):
__tablename__ = 'telegram_text_message'
id = db.Column(db.Integer, primary_key=True)
message_id = db.Column(db.BigInteger)
message = db.Column(db.String)
created = db.Column(db.DateTime, default=datetime.datetime.now)
channel_id = Column(db.BigInteger)
user_id = Column(db.BigInteger)
reply_to_id = db.Column(db.BigInteger)
def __init__(self,
message_id,
message,
channel_id,
user_id,
reply_to_id,
created=datetime.datetime.now()):
self.message_id = message_id
self.message = message
self.channel_id = channel_id
self.user_id = user_id
self.reply_to_id = reply_to_id
self.created = created
def __repr__(self):
return '<TTxtMsg %r>' % str(self.id)
class User(db.Model):
"""User object
"""
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(120), index=True, unique=True, nullable=False)
password = db.Column(db.String(60), index=True, nullable=False)
is_verified = db.Column(db.Boolean(), default=False, nullable=False)
password_reset_requests = db.relationship('PasswordResetRequest',
backref=db.backref('user'))
email_verification_requests = db.relationship('EmailVerificationRequest',
backref=db.backref('user'))
def __repr__(self):
return '<User %r>' % self.email
# ===========================
# Flask-Login methods
# ===========================
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return unicode(self.id)
class TFModel(db.Model):
__tablename__ = 'tf_model'
id = db.Column(db.Integer, primary_key=True)
model_name = db.Column(db.String)
dump_filename = db.Column(db.String)
def __init__(self, model_name, dump_filename):
self.model_name = model_name
self.dump_filename = dump_filename
def __repr__(self):
return '<TFModel %r>' % str(self.id)
@staticmethod
def get_last(model_name):
session = db_session()
query = session.query(TFModel).filter(
TFModel.model_name == model_name).order_by(desc(
TFModel.id)).first()
session.close()
return query.dump_filename
@staticmethod
def create_one(model_name, dump_filename):
session = db_session()
model = TFModel(model_name, dump_filename)
session.add(model)
session.commit()
session.close()
class TelegramUser(db.Model):
__tablename__ = 'telegram_user'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.BigInteger)
first_name = db.Column(db.String)
last_name = db.Column(db.String)
username = db.Column(db.String)
def __init__(self, user_id, first_name, last_name, username):
self.user_id = user_id
self.first_name = first_name
self.last_name = last_name
self.username = username
def __repr__(self):
return '<TUser %r>' % str(self.id)
class QuestionViewHistory(db.Model):
__tablename__ = 'question_view_history'
id = db.Column(db.Integer, primary_key=True)
question_id = db.Column(db.Integer)
view_count = db.Column(db.Integer)
view_date = db.Column(db.DateTime)
counted = db.Column(db.Boolean)
def __init__(self, question_id, view_count, view_date):
self.question_id = question_id
self.view_count = view_count
self.view_date = view_date
self.counted = False
def __repr__(self):
return '<QuestionViewHistory %s>' % str(self.id)
class Site(db.Model):
__tablename__ = 'site'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
url = db.Column(db.String(100))
meta = db.Column(db.String(100))
chat = db.Column(db.String(100))
api_name = db.Column(db.String(100), unique=True)
launch_date = db.Column(db.DateTime, nullable=False)
creation_date = db.Column(db.DateTime, nullable=False)
language = db.Column(db.String(100), unique=True)
def __init__(self, name, url, meta, chat, api_name, launch_date, language):
self.creation_date = datetime.datetime.now()
self.name = name
self.url = url
self.meta = meta
self.chat = chat
self.api_name = api_name
self.launch_date = launch_date
self.language = language
@staticmethod
def is_exist(adder, api_name):
return True if adder.session.query(func.count(Site.id)).filter_by(
api_name=api_name).scalar() > 0 else False
@staticmethod
def by_language(language):
session = db_session()
query = session.query(Site).filter_by(language=language)
result = query.first()
session.close()
return result
@staticmethod
def by_api_name(api_name):
session = db_session()
query = session.query(Site).filter_by(api_name=api_name)
result = query.first()
session.close()
return result
@staticmethod
def all():
return Site.query.all()
def __repr__(self):
return '<Site %r>' % str(self.id)
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
account_id = db.Column(db.Integer, unique=True)
user_id = db.Column(db.Integer)
username = db.Column(db.String)
role = db.Column(db.String)
is_banned = db.Column(db.Boolean)
end_ban_date= db.Column(db.DateTime, nullable=True)
reputation = db.Column(db.Integer)
profile_image = db.Column(db.String)
profile_link= db.Column(db.String)
def __init__(self, account_id,
user_id,
username,
reputation,
profile_image,
profile_link,
role="user",
is_banned=False):
self.account_id = account_id
self.user_id = user_id
self.username = username
self.reputation = reputation
self.profile_image = profile_image
self.profile_link = profile_link
self.role = role
self.is_banned = is_banned
def __repr__(self):
return '<User %r>' % str(self.id)
@staticmethod
def get_by_account_id(account_id):
session = db_session()
query = session.query(User).filter(User.account_id==account_id)
result = query.first()
session.close()
return result
class Action(db.Model):
__tablename__ = 'action'
action_skip_name = 'skip'
action_translate_request_name = 'transreq'
id = db.Column(db.Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user.id'))
question_id = db.Column(db.Integer)
# Action could be: skipped, wanted
action_name = db.Column(String(50))
action_date = db.Column(db.DateTime)
canceled = db.Column(db.Boolean)
def __init__(self, user_id, question_id, action_name):
self.user_id = user_id
self.question_id = question_id
self.action_name = action_name
self.action_date = datetime.datetime.now()
self.canceled = False
def __repr__(self):
return '<Action %s>' % str(self.id)
class Verification(db.Model):
__tablename__ = 'verification'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, ForeignKey('user.id'))
action_id = db.Column(db.Integer, ForeignKey('action.id'))
creation_date = db.Column(db.DateTime, nullable=False)
is_valid = db.Column(db.Boolean, default=False)
def __init__(self, user_id, action_id, is_valid):
self.user_id = user_id
self.action_id = action_id
self.is_valid = is_valid
self.creation_date = datetime.datetime.now()
@staticmethod
def by_user_and_action(user_id, action_id):
session = db_session()
query = session.query(Verification).filter_by(
user_id=user_id,
action_id=action_id).order_by(asc(Verification.creation_date))
result = query.first()
session.close()
return result
class Question(db.Model):
__tablename__ = 'question'
question_type_most_viewed = 'most_viewed'
question_type_suggested = 'suggested'
id = db.Column(db.Integer, primary_key=True)
question_id = db.Column(db.Integer)
view_count = db.Column(db.Integer)
is_associated = db.Column(db.Boolean, default=False)
question_type = db.Column(String(50))
# Is used in case of question_type is "suggested"
suggested_user_id = Column(Integer, ForeignKey('user.id'), nullable=True)
# Allows to remove a question from the list manually
can_be_associated = db.Column(db.Boolean, default=True)
# Currenly these are not in use.
# It seems it could be a good idea
# to add some kind of cache and search
tags = db.Column(db.String(500))
# In order to track changes we need to know when we updated
# a record last time.
last_update_date = db.Column(db.DateTime, default=datetime.datetime.now)
def __init__(self,
question_type,
question_id,
view_count,
suggested_user_id=None,
is_associated=False):
self.question_type = question_type
self.question_id = question_id
self.view_count = view_count
self.is_associated = is_associated
self.can_be_associated = True
self.suggested_user_id = suggested_user_id
self.last_update_date = datetime.datetime.now()
def __repr__(self):
return '<Question %s>' % str(self.id)
class TelegramChannel(db.Model):
__tablename__ = 'telegram_channel'
id = db.Column(db.Integer, primary_key=True)
channel_id = db.Column(db.BigInteger)
title = db.Column(db.String)
username = db.Column(db.String)
tags = db.Column(db.String)
access_hash = db.Column(db.BigInteger)
def __init__(self, channel_id, title, username, access_hash):
self.channel_id = channel_id
self.title = title
self.username = username
self.access_hash = access_hash
def __repr__(self):
return '<TChannel %r>' % str(self.id)
class Association(db.Model):
__tablename__ = 'association'
id = db.Column(db.Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user.id'))
soen_id = db.Column(db.Integer)
soint_id = db.Column(db.Integer)
comment_id = db.Column(db.Integer)
status = db.Column(String(50))
association_date = db.Column(db.DateTime)
def __init__(self, user_id, soen_id, soint_id, comment_id, status="added"):
self.user_id = user_id
self.soen_id = soen_id
self.soint_id = soint_id
self.comment_id = comment_id
self.status = status
self.association_date = datetime.datetime.now()
def __repr__(self):
return '<Association %s>' % str(self.id)
class DBStaticAssessment(db.Model):
__tablename__ = 'static_assessment'
id = db.Column(db.Integer, primary_key=True)
question_count = db.Column(db.Integer)
mimimum_question_length = db.Column(db.Integer)
maximum_question_length = db.Column(db.Integer)
mimimum_question_word_count = db.Column(db.Integer)
maximum_question_word_count = db.Column(db.Integer)
def __init__(self, question_count, mimimum_question_length,
maximum_question_length, mimimum_question_word_count,
maximum_question_word_count):
self.question_count = question_count
self.mimimum_question_length = mimimum_question_length
self.maximum_question_length = maximum_question_length
self.mimimum_question_word_count = mimimum_question_word_count
self.maximum_question_word_count = maximum_question_word_count
def __repr__(self):
return '<DBSA %r>' % str(self.id)
class Event(db.Model):
__tablename__ = 'event'
event_type_meetup = 1
event_type_webcast = 2
event_type_makeathon = 3
id = db.Column(db.Integer, primary_key=True)
created_by = db.Column(db.Integer, ForeignKey('user.id'))
site_id = db.Column(db.Integer, ForeignKey('site.id'))
creation_date = db.Column(db.DateTime, nullable=False)
date = db.Column(db.DateTime, nullable=False)
title = db.Column(db.String)
description = db.Column(db.String)
location = db.Column(db.String)
meta_link = db.Column(db.String)
meta_post_id = db.Column(db.Integer)
chat_link = db.Column(db.String)
event_type = db.Column(db.Integer)
is_valid = db.Column(db.Boolean, default=True, nullable=True)
validated_by = db.Column(db.Integer, ForeignKey('user.id'), nullable=True)
def __init__(self, event_type, created_by, site_id, date, title,
description, location, meta_link, meta_post_id, chat_link):
self.creation_date = datetime.datetime.now()
self.event_type = event_type
self.created_by = created_by
self.site_id = site_id
self.date = date
self.title = title
self.description = description
self.location = location
self.meta_link = meta_link
self.meta_post_id = meta_post_id
self.chat_link = chat_link
@staticmethod
def by_meta_post_id(meta_post_id, site_id):
session = db_session()
query = session.query(Event).filter(Event.meta_post_id == meta_post_id,
Event.site_id == site_id).order_by(
asc(Event.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def by_id(event_id):
session = db_session()
query = session.query(Event).filter_by(id=event_id).order_by(
asc(Event.creation_date))
result = query.first()
session.close()
return result
class Action(db.Model):
__tablename__ = 'action'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, ForeignKey('user.id'))
activity_id = db.Column(db.Integer, ForeignKey('activity.id'))
creation_date = db.Column(db.DateTime, nullable=False)
post_id = db.Column(db.Integer, ForeignKey('post.id'), nullable=True)
link = db.Column(db.String)
def __init__(self, user_id, activity_id, post_id, link):
self.user_id = user_id
self.activity_id = activity_id
self.post_id = post_id
self.link = link
self.creation_date = datetime.datetime.now()
def __repr__(self):
return '<Action %r>' % str(self.id)
@staticmethod
def all_for_activity(activity_id):
session = db_session()
query = session.query(Action).filter_by(
activity_id=activity_id).order_by(asc(Action.creation_date))
result = query.all()
session.close()
return result
@staticmethod
def activists(activity_id):
session = db_session()
query = session.query(User).join(Action).filter(
Action.activity_id == activity_id).distinct()
result = query.all()
session.close()
return result
@staticmethod
def user_act_times(user_id, site_id):
session = db_session()
result = session.query(func.count(Action.id)).join(Activity).filter(
Action.user_id == user_id, Activity.site_id == site_id).scalar()
session.close()
return result
@staticmethod
def last_by_user(user_id):
session = db_session()
query = session.query(Action).filter_by(user_id=user_id).order_by(
desc(Action.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def by_id(action_id):
session = db_session()
query = session.query(Action).filter_by(id=action_id).order_by(
asc(Action.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def is_exist(adder, activity_id, link):
return True if adder.session.query(func.count(Action.id)).filter_by(
activity_id=activity_id).filter_by(
link=link).scalar() > 0 else False
class Activist(db.Model):
__tablename__ = 'activist'
role_coordinator = 1
role_attendee = 2
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, ForeignKey('user.id'))
activity_id = db.Column(db.Integer,
ForeignKey('activity.id'),
nullable=True)
event_id = db.Column(db.Integer, ForeignKey('event.id'), nullable=True)
role = db.Column(db.Integer)
creation_date = db.Column(db.DateTime, nullable=False)
canceled = db.Column(db.Boolean, default=False)
updated_date = db.Column(db.DateTime, nullable=True)
def __init__(self, user_id, activity_id, event_id, role):
self.creation_date = datetime.datetime.now()
self.user_id = user_id
self.activity_id = activity_id
self.event_id = event_id
self.role = role
def __repr__(self):
return '<activist %r>' % str(self.id)
@staticmethod
def coordinator(user_id, activity_id):
return Activist(user_id, activity_id, None, Activist.role_coordinator)
@staticmethod
def attendee(user_id, event_id):
return Activist(user_id, None, event_id, Activist.role_coordinator)
@staticmethod
def coordinators(activity_id):
session = db_session()
query = session.query(User).join(Activist).filter_by(
activity_id=activity_id,
role=Activist.role_coordinator,
canceled=False).order_by(asc(Activist.creation_date))
result = query.all()
session.close()
return result
@staticmethod
def attendees(event_id):
session = db_session()
query = session.query(User).join(Activist).filter_by(
event_id=event_id, role=Activist.role_attendee,
canceled=False).order_by(asc(Activist.creation_date))
result = query.all()
session.close()
return result
@staticmethod
def is_attendee(user_id, event_id):
session = db_session()
result = session.query(func.count(Activist.id)).filter_by(
user_id=user_id,
event_id=event_id,
role=Activist.role_attendee,
canceled=False).scalar()
session.close()
return True if result > 0 else False
@staticmethod
def user_attend_times(user_id, site_id):
session = db_session()
result = session.query(func.count(Activist.id)).join(Event).filter(
Activist.user_id == user_id,
Activist.role == Activist.role_attendee,
Activist.canceled == False, Event.site_id == site_id).scalar()
session.close()
return result
@staticmethod
def user_coordinate_times(user_id, site_id):
session = db_session()
result = session.query(func.count(Activist.id)).join(Activity).filter(
Activist.user_id == user_id,
Activist.role == Activist.role_coordinator,
Activist.canceled == False,
Activity.site_id == site_id).distinct().scalar()
session.close()
return result
@staticmethod
def is_coordinator(user_id):
session = db_session()
result = session.query(func.count(Activist.id)).filter_by(
user_id=user_id, role=Activist.role_coordinator).scalar()
session.close()
return True if result > 0 else False
@staticmethod
def by_user_and_event(user_id, event_id):
session = db_session()
query = session.query(Activist).filter_by(
event_id=event_id, user_id=user_id,
role=Activist.role_attendee).order_by(asc(Activist.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def is_exist(adder, activity_id, user_id):
return True if adder.session.query(func.count(Activist.id)).filter_by(
activity_id=activity_id, user_id=user_id).scalar() > 0 else False
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
account_id = db.Column(db.Integer, unique=True)
user_id = db.Column(db.Integer)
username = db.Column(db.String(100))
role = db.Column(db.String(30))
is_banned = db.Column(db.Boolean, default=False)
end_ban_date = db.Column(db.DateTime, nullable=True)
reputation = db.Column(db.Integer)
profile_image = db.Column(db.String(200))
profile_link = db.Column(db.String(200))
creation_date = db.Column(db.DateTime, nullable=False)
def __init__(self,
account_id,
user_id,
username,
reputation,
profile_image,
profile_link,
role="user",
is_banned=False):
self.creation_date = datetime.datetime.now()
self.account_id = account_id
self.user_id = user_id
self.username = username
self.reputation = reputation
self.profile_image = profile_image
self.profile_link = profile_link
self.role = role
self.is_banned = is_banned
def __repr__(self):
return '<User %r>' % str(self.id)
@staticmethod
def is_exist_with_account_id(adder, account_id):
return True if adder.session.query(func.count(User.id)).filter_by(
account_id=account_id).scalar() > 0 else False
@staticmethod
def by_account_id(account_id):
session = db_session()
query = session.query(User).filter_by(account_id=account_id).order_by(
desc(User.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def by_id(user_id):
session = db_session()
query = session.query(User).filter_by(id=user_id).order_by(
desc(User.creation_date))
result = query.first()
session.close()
return result
class Activity(db.Model):
__tablename__ = 'activity'
activity_type_interesting = 1
activity_type_needed = 2
activity_type_creative = 3
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, ForeignKey('site.id'))
title = db.Column(db.String)
description = db.Column(db.String)
creation_date = db.Column(db.DateTime, nullable=False)
activity_type = db.Column(db.Integer)
meta_post_url = db.Column(db.String)
meta_post_title = db.Column(db.String)
chat_url = db.Column(db.String)
chat_name = db.Column(db.String)
tab_name = db.Column(db.String)
def __init__(self, site_id, title, description, activity_type,
meta_post_url, meta_post_title, chat_url, chat_name,
tab_name):
self.creation_date = datetime.datetime.now()
self.site_id = site_id
self.title = title
self.description = description
self.activity_type = activity_type
self.meta_post_url = meta_post_url
self.meta_post_title = meta_post_title
self.chat_url = chat_url
self.chat_name = chat_name
self.tab_name = tab_name
@staticmethod
def is_exist(adder, site_id, activity_type):
return True if adder.session.query(func.count(Activity.id)).filter_by(
site_id=site_id).filter_by(
activity_type=activity_type).scalar() > 0 else False
@staticmethod
def by_site_id_and_activity_type(site_id, activity_type):
session = db_session()
query = session.query(Activity).filter_by(
site_id=site_id,
activity_type=activity_type).order_by(asc(Activity.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def by_id(activity_id):
session = db_session()
query = session.query(Activity).filter_by(id=activity_id).order_by(
asc(Activity.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def all(site_id):
session = db_session()
query = session.query(Activity).filter_by(site_id=site_id).order_by(
asc(Activity.creation_date))
result = query.all()
session.close()
return result
def __repr__(self):
return '<Activity %r>' % str(self.id)
class Post(db.Model):
__tablename__ = 'post'
post_type_question = 1
post_type_answer = 2
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, ForeignKey('site.id'))
post_id = db.Column(db.Integer)
creation_date = db.Column(db.DateTime, nullable=False)
post_type = db.Column(db.Integer)
owner_id = db.Column(db.Integer)
score = db.Column(db.Integer)
down_vote_count = db.Column(db.Integer)
up_vote_count = db.Column(db.Integer)
view_count = db.Column(db.Integer)
answer_count = db.Column(db.Integer)
is_answered = db.Column(db.Boolean, default=False)
tags = db.Column(db.String(500))
is_accepted = db.Column(db.Boolean, default=False)
parent_id = db.Column(db.Integer)
def __init__(self, site_id, post_id, post_type, creation_date, owner_id,
score, down_vote_count, up_vote_count):
self.site_id = site_id
self.post_id = post_id
self.post_type = post_type
self.creation_date = creation_date
self.owner_id = owner_id
self.score = score
self.down_vote_count = down_vote_count
self.up_vote_count = up_vote_count
@staticmethod
def question(site_id, question_id, creation_date, score, down_vote_count,
up_vote_count, view_count, answer_count, is_answered, tags,
owner_id):
q = Post(site_id, question_id, Post.post_type_question, creation_date,
owner_id, score, down_vote_count, up_vote_count)
q.view_count = view_count
q.answer_count = answer_count
q.is_answered = is_answered
q.tags = tags
return q
@staticmethod
def answer(site_id, answer_id, question_id, creation_date, score,
down_vote_count, up_vote_count, owner_id, is_accepted):
a = Post(site_id, answer_id, Post.post_type_answer, creation_date,
owner_id, score, down_vote_count, up_vote_count)
a.is_accepted = is_accepted
a.parent_id = question_id
return a
@staticmethod
def last(site_id):
session = db_session()
query = session.query(Post).filter_by(site_id=site_id).order_by(
desc(Post.creation_date))
result = query.first()
session.close()
return result
@staticmethod
def is_exist(adder, site_id, post_id):
return True if adder.session.query(func.count(Post.id)).filter_by(
site_id=site_id).filter_by(post_id=post_id).scalar() > 0 else False
def __repr__(self):
return '<Post %r>' % str(self.id)
class SiteInfo(db.Model):
__tablename__ = 'site_info'
id = db.Column(db.Integer, primary_key=True)
site_id = db.Column(db.Integer, ForeignKey('site.id'))
date = db.Column(db.DateTime, nullable=False)
total_users = db.Column(db.Integer)
total_badges = db.Column(db.Integer)
total_votes = db.Column(db.Integer)
total_comments = db.Column(db.Integer)
total_answers = db.Column(db.Integer)
total_accepted = db.Column(db.Integer)
total_unanswered = db.Column(db.Integer)
total_questions = db.Column(db.Integer)
def __init__(self, site_id, total_users, total_badges, total_votes,
total_comments, total_answers, total_accepted,
total_unanswered, total_questions):
self.site_id = site_id
self.date = datetime.datetime.now()
self.total_users = total_users
self.total_badges = total_badges
self.total_votes = total_votes
self.total_comments = total_comments
self.total_answers = total_answers
self.total_accepted = total_accepted
self.total_unanswered = total_unanswered
self.total_questions = total_questions
def __repr__(self):
return '<SiteInfo %r>' % str(self.id)
class SourceData(db.Model):
__tablename__ = 'source_data'
source_type_so_question = 1
source_type_so_answer = 2
source_type_so_comment = 3
source_type_tl_statement = 4
source_type_so_bq_question = 5
id = db.Column(db.Integer, primary_key=True)
source_id = db.Column(db.Integer)
source_type = db.Column(db.Integer)
body = db.Column(String)
title = db.Column(String)
tags = db.Column(String)
score = db.Column(db.Integer)
length = db.Column(db.Integer)
word_count = db.Column(db.Integer)
code_words = db.Column(String)
filtered_words = db.Column(db.String)
question_words = db.Column(db.String)
is_negative = db.Column(db.Boolean, default=True)
def __init__(self, source_id, source_type, body, title, tags, score,
length, word_count, question_words, filtered_words,
code_words, is_negative):
self.source_id = source_id
self.source_type = source_type
self.body = body
self.title = title
self.tags = tags
self.score = score
self.length = length
self.word_count = word_count
self.code_words = code_words
self.question_words = question_words
self.filtered_words = filtered_words
self.is_negative = is_negative
def __repr__(self):
return '<VQues %r>' % str(self.id)
@staticmethod
def update_or_create_raw(session, source_id, source_type, score,
answer_count, title, body, tags, is_negative):
length = len(body)
processed_body = str(process_text(body, True, 2))
code_words = str(process_code(body))
filtered_vocabualary = processed_body.split()
tags = tags.replace('|', " ")
word_count = len(filtered_vocabualary)
question_words = ""
for word in filtered_vocabualary:
if question_words_checker.is_question_word(word):
question_words += " " + word
SourceData.update_or_create(session, source_id, source_type, body,
title, tags, score, length, word_count,
question_words, processed_body, code_words,
is_negative)
@staticmethod
def update_or_create(session, source_id, source_type, body, title, tags,
score, length, word_count, question_words,
filtered_words, code_words, is_negative):
if session is None:
local_session = db_session()
voc_qstn = SourceData.query.filter(
and_(SourceData.source_id == source_id,
SourceData.source_type == source_type)).first()
if voc_qstn is not None:
update_query = SourceData.__table__.update().values(
body=body,
title=title,
tags=tags,
score=score,
length=length,
word_count=word_count,
code_words=code_words,
question_words=question_words).\
where(SourceData.source_id==source_id)
if session is None:
local_session.execute(update_query)
local_session.commit()
local_session.close()
else:
session.execute(update_query)
return
voc_qstn = SourceData(source_id, source_type, body, title, tags, score,
length, word_count, question_words,
filtered_words, code_words, is_negative)
if session is None:
local_session.add(voc_qstn)
local_session.commit()
local_session.close()
else:
session.add(voc_qstn)
@staticmethod
def count():
session = db_session()
count = session.query(func.count(SourceData.id)).scalar()
session.close()
return count
@staticmethod
def all():
session = db_session()
items = session.query(SourceData).distinct().all()
session.close()
return items
@staticmethod
def full_vocabualary():
return SourceData.get_vocabualary()
@staticmethod
def positive_vocabualary():
return SourceData.get_vocabualary(True)
@staticmethod
def get_vocabualary(only_positive=False):
session = db_session()
query = session.query(
func.string_agg(
SourceData.filtered_words,
aggregate_order_by(literal_column("' '"), SourceData.id)))
if only_positive:
query = query.filter(SourceData.is_negative == False)
query = query.first()
session.close()
return u"%s" % str(query)
@staticmethod
def test_data(length, is_negative=False):
session = db_session()
if not is_negative:
items = session.query(SourceData).filter(
SourceData.is_negative == False).order_by(
func.random()).limit(length).all()
else:
items = session.query(SourceData).filter(SourceData.source_type==SourceData.source_type_tl_statement).\
join(Statement, SourceData.source_id==Statement.id).\
filter(and_(Statement.reviewed==True, Statement.false_assumption==True)).\
distinct().\
all()
session.close()
return items
@staticmethod
def load_all():
SourceData.load_good_questions()
SourceData.load_good_answers()
SourceData.load_spammy_answers()
SourceData.load_spammy_comments()
SourceData.load_spammy_questions()
SourceData.load_bq_questions()
SourceData.load_negative_statements()
"""
Methods that help to upload data from csv files
"""
@staticmethod
def load_good_questions():
print("Starting loading good questions...")
session = db_session()
with open("data/questions.csv", 'rt', encoding="utf8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
for row in csv_reader:
source_id, score, answer_count, title, body, tags = row
SourceData.update_or_create_raw(
session, source_id, SourceData.source_type_so_question,
score, answer_count, title, body, tags, False)
session.commit()
session.close()
@staticmethod
def load_good_answers():
print("Starting loading good answers...")
session = db_session()
with open("data/answers.csv", 'rt', encoding="utf8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
for row in csv_reader:
source_id, score, body, tags = row
SourceData.update_or_create_raw(
session, source_id, SourceData.source_type_so_answer,
score, 0, "", body, tags, False)
session.commit()
session.close()
@staticmethod
def load_spammy_answers():
print("Starting loading spammy answers...")
session = db_session()
with open("data/spammy_answers.csv", 'rt', encoding="utf8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
for row in csv_reader:
source_id, title, score, body, tags = row
SourceData.update_or_create_raw(
session, source_id, SourceData.source_type_so_answer,
score, 0, title, body, tags, True)
session.commit()
session.close()
@staticmethod
def load_spammy_comments():
print("Starting loading spammy comments...")
session = db_session()
with open("data/spammy_comments.csv", 'rt',
encoding="utf8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
for row in csv_reader:
source_id, body, tags, score = row
SourceData.update_or_create_raw(
session, source_id, SourceData.source_type_so_answer,
score, 0, "", body, tags, True)
session.commit()
session.close()
@staticmethod
def load_spammy_questions():
print("Starting loading spammy questions...")
session = db_session()
with open("data/spammy_questions.csv", 'rt',
encoding="utf8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
for row in csv_reader:
source_id, title, score, body, tags = row
SourceData.update_or_create_raw(
session, source_id, SourceData.source_type_so_question,
score, 0, title, body, tags, True)
session.commit()
session.close()
@staticmethod
def load_bq_questions():
print("Starting loading bq questions...")
session = db_session()
with open("data/bq_questions.csv.csv", 'rt',
encoding="utf8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
for row in csv_reader:
source_id, title, body, tags, score = row
SourceData.update_or_create_raw(
session, source_id, SourceData.source_type_so_bq_question,
score, 0, title, body, tags, True)
session.commit()
session.close()
@staticmethod
def load_negative_statements():
print("Starting loading negative statements...")
items = Statement.get_negative()
session = db_session()
for item in items:
SourceData.update_or_create_raw(
session, item.statement_id,
SourceData.source_type_tl_statement, 0, 0, "",
item.agg_message, "", True)
session.commit()
session.close()
class Statement(db.Model):
__tablename__ = 'statement'
id = db.Column(db.Integer, primary_key=True)
channel_id = db.Column(db.BigInteger)
user_id = db.Column(db.BigInteger)
first_msg_id = db.Column(db.BigInteger)
last_msg_id = db.Column(db.BigInteger)
text = db.Column(db.String)
# first added msg creation time
created = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now)
# last added msg creation time
updated = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now)
was_processed = db.Column(db.Boolean, default=False)
is_question = db.Column(db.Boolean, default=False)
false_assumption = db.Column(db.Boolean, default=False)
reviewed = db.Column(db.Boolean, default=False)
def __init__(self,
channel_id,
user_id,
first_msg_id,
created=datetime.datetime.now(),
is_question=False,
was_processed=False,
reviewed=False):
self.channel_id = channel_id
self.user_id = user_id
self.first_msg_id = first_msg_id
self.last_msg_id = first_msg_id
self.created = created
self.updated = created
self.is_question = is_question
self.was_processed = was_processed
self.reviewed = reviewed
def __repr__(self):
return '<Stmnt %r>' % str(self.id)
@staticmethod
def get_negative():
session = db_session()
query = session.query(Statement.id.label('statement_id'), func.string_agg(TelegramTextMessage.message,
aggregate_order_by(literal_column("'. '"),
TelegramTextMessage.created)).label('agg_message')).\
filter(Statement.reviewed==True).\
filter(Statement.is_question==Statement.false_assumption).\
filter(and_(TelegramTextMessage.channel_id==Statement.channel_id, TelegramTextMessage.user_id==Statement.user_id)).\
filter(TelegramTextMessage.message_id.between(Statement.first_msg_id, Statement.last_msg_id)).\
group_by(Statement.id).\
subquery()
query_results = session.query(query.c.statement_id, query.c.agg_message, func.length(query.c.agg_message).label('len'), TelegramChannel.tags.label('tags')).\
outerjoin(Statement, Statement.id==query.c.statement_id).\
outerjoin(TelegramChannel, TelegramChannel.channel_id==Statement.channel_id).distinct().all()
session.close()
return query_results
