class Subscription(db.Model):
id = db.Column(db.Integer, primary_key=True)
enable_email = db.Column(db.Boolean, default=False)
email_frequency = db.Column(db.Integer, default=EFREQ_DAILY)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), index=True)
feed_id = db.Column(db.Integer, db.ForeignKey('feed.id'), index=True)
class Keyword(db.Model):
id = db.Column(db.Integer, primary_key=True)
keyword = db.Column(db.String(120), index=True, unique=True)
@staticmethod
def auto_complete_keyword(search_term):
return Keyword.query.filter(Keyword.keyword.ilike(search_term) + '%')
def __repr__(self):
return self.keyword
class ArticleAuthor(db.Model):
__tablename__ = 'articlesauthors'
article_id = db.Column(db.Integer,
db.ForeignKey('article.id'),
primary_key=True,
index=True)
author_id = db.Column(db.Integer,
db.ForeignKey('author.id'),
primary_key=True,
index=True)
position = db.Column(db.Integer)
def __init__(self, author):
self.author = author
class Author(db.Model):
id = db.Column(db.Integer, primary_key=True)
forenames = db.Column(db.String(), index=True)
lastname = db.Column(db.String(), index=True)
associations = db.relationship('ArticleAuthor', backref='author')
articles = association_proxy('associations', 'article')
def collaborators(self):
articles_q = db.session.query(
ArticleAuthor.article_id).filter_by(author_id=self.id).subquery()
q = db.session.query(Author,
func.count()).join(Author.associations).filter(
ArticleAuthor.article_id.in_(articles_q),
ArticleAuthor.author_id != self.id).group_by(
Author.id).order_by(func.count().desc())
return q
def similar_authors(self):
return Author.query.filter(
Author.lastname.ilike(self.lastname),
Author.forenames.ilike(self.forenames[0] + '%'),
Author.id != self.id)
@staticmethod
def author_id_articles(id):
return Article.query.filter(Article.authors.any(
Author.id == id)).order_by(Article.created.desc())
def author_articles(self):
return Author.author_id_articles(self.id)
@staticmethod
def auto_complete_authors(search_term):
names = search_term.split(' ')
conditions = []
conditions.append(Author.lastname.ilike(names[-1] + '%'))
if len(names) > 1:
conditions.append(Author.forenames.ilike(search_term[0] + '%'))
similar_authors = Author.query.filter(and_(*conditions)).with_entities(
Author.id, Author.forenames, Author.lastname)
return similar_authors
def __repr__(self):
return self.forenames + ' ' + self.lastname
class Feed(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120), index=True)
public = db.Column(db.Boolean, default=True)
timestamp = db.Column(db.DateTime)
owner_id = db.Column(db.Integer, db.ForeignKey('user.id'), index=True)
authors = db.relationship('Author',
secondary=feedauthors,
lazy='dynamic',
backref=db.backref('authors', lazy='dynamic'))
keywords = db.relationship('Keyword',
secondary=feedkeywords,
backref=db.backref('feeds', lazy='dynamic'))
categories = db.relationship('Category',
secondary=feedcategories,
backref=db.backref('feeds', lazy='dynamic'))
subscriptions = db.relationship('Subscription',
backref=db.backref('feed'),
lazy='dynamic')
def feed_articles(self):
# explain (analyze,buffers) select * from article INNER JOIN (select id from article as blah where search_vector @@ to_tsquery('circuit:* & qed:* | qubit:*') union select article_id from articlesauthors as blah where author_id in (54962, 55738, 85464, 85465, 125598, 55921)) on id=blah order by created desc;
#select * from (select distinct on (id) * from (select articles.* from articles where search_vector @@ ... union all select a.* from articles a join articlesauthors aa on ... where author_id = any (...)) s1) s2 order by created_at desc;
#explain (analyze,buffers) select article.*, (article.id+0) as dummy_article_id from article where search_vector @@ to_tsquery('circuit:* & qed:* | qubit:*') union select a.*, (a.id+0) as dummy_article_id from article a join articlesauthors aa on a.id=aa.article_id where author_id in (54962, 55738, 85464, 85465, 125598, 55921) order by created desc;search_query = parse_search_query(' or '.join([kw.keyword for kw in self.keywords]))
#select article.*, (article.id+0) as dummy_article_id from article where search_vector @@ to_tsquery('circuit:* & qed:* | qubit:*') union select a.*, (a.id+0) as dummy_article_id from article a join articlesauthors aa on a.id=aa.article_id where author_id in (54962, 55738, 85464, 85465, 125598, 55921) order by created desc;search_query = parse_search_query(' or '.join([kw.keyword for kw in self.keywords]))
search_query = parse_search_query(' or '.join(
[kw.keyword for kw in self.keywords]))
alist = [a.id for a in self.authors]
s1 = select([ArticleAuthor.article_id
]).where(ArticleAuthor.author_id.in_(alist))
s2 = select([Article.id]).where(
Article.search_vector.match_tsquery(search_query))
q = Article.query.filter(Article.id.in_(s1.union(s2))).order_by(
(Article.created + cast("0", Interval)).desc()
) #The addition of the extra interval is important because it changes the way the query plan is computed and makes it run 100x faster!
return q
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
fullname = db.Column(db.String(), index=True)
username = db.Column(db.String(200), index=True)
nickname = db.Column(db.String(64), unique=True, index=True)
password = db.Column(db.String(200))
email = db.Column(db.String(), unique=True, index=True)
active = db.Column(db.Boolean, default=True)
role = db.Column(db.Integer, default=ROLE_USER)
last_seen = db.Column(db.DateTime())
enable_email = db.Column(db.Boolean)
email_frequency = db.Column(db.Integer)
subscriptions = db.relationship('Subscription',
backref=db.backref('subscriber'),
lazy='dynamic')
feeds = db.relationship('Feed',
backref=db.backref('owner'),
lazy='dynamic')
likes = db.relationship('Article',
secondary=articlelikes,
backref=db.backref('likers', lazy='dynamic'))
def avatar(self, size):
return 'http://www.gravatar.com/avatar/' + md5(
self.email).hexdigest() + '?d=mm&s=' + str(size)
@staticmethod
def make_unique_nickname(nickname):
if User.query.filter_by(nickname=nickname).first() == None:
return nickname
version = 2
while True:
new_nickname = nickname + str(version)
if User.query.filter_by(nickname=new_nickname).first() == None:
break
version += 1
return new_nickname
def is_authenticated(self):
return True
def is_active(self):
return self.active
def is_anonymous(self):
return False
def get_id(self):
return unicode(self.id)
def __repr__(self):
return '<User %r>' % (self.username)
class Synchronization(db.Model):
id = db.Column(db.Integer, primary_key=True)
date = db.Column(db.DateTime(), index=True)
class Category(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(120), unique=True, index=True)
def __repr__(self):
return self.name
class Article(db.Model):
query_class = ArticleQuery
id = db.Column(db.Integer, primary_key=True)
arxiv_id = db.Column(db.String(64), index=True, unique=True)
title = db.Column(db.String(), index=True)
abstract = db.Column(db.String())
full_description = db.Column(db.String())
comments = db.Column(db.String(), index=True)
created = db.Column(db.Date(), index=True)
updated = db.Column(db.Date(), index=True)
doi = db.Column(db.String(), index=True)
journalref = db.Column(db.String(), index=True)
mscclass = db.Column(db.String(), index=True)
acmclass = db.Column(db.String(), index=True)
license = db.Column(db.String(), index=True)
title_search_vector = deferred(db.Column(TSVectorType('title')))
abstract_search_vector = deferred(db.Column(TSVectorType('abstract')))
title_abstract_search_vector = deferred(
db.Column(TSVectorType('title', 'abstract')))
search_vector = deferred(db.Column(TSVectorType('full_description')))
#These set up the ordered list of authors
associations = db.relationship('ArticleAuthor',
order_by="ArticleAuthor.position",
collection_class=ordering_list('position'),
backref='article')
authors = association_proxy('associations', 'author')
categories = db.relationship('Category',
secondary=articlescategories,
backref=db.backref('articles',
lazy='dynamic'))
@staticmethod
def simple_search(query):
q1 = Article.query.filter(
Article.search_vector.match_tsquery(parse_search_query(query)))
#q2 = Article.query.filter(Article.authors.any(Author.lastname.ilike(query)))
#q2 = Article.query.filter(Article.authors.any(func.lower(Author.lastname) == func.lower(query)))
#q = q1.union(q2)
return q1.order_by((Article.created + cast("0", Interval)).desc())
#return q1.order_by(Article.created.desc())
def __repr__(self):
return self.title
from sqlalchemy.orm import deferred
from flask.ext.login import UserMixin
# from social.apps.flask_app import models
import datetime
ROLE_USER = 0
ROLE_ADMIN = 1
EFREQ_DAILY = 0
EFREQ_WEEKLY = 1
EFREQ_MONTHLY = 2
feedkeywords = db.Table(
'feedkeywords',
db.Column('feed_id', db.Integer, db.ForeignKey('feed.id'), index=True),
db.Column('keyword_id',
db.Integer,
db.ForeignKey('keyword.id'),
index=True))
feedauthors = db.Table(
'feedauthors',
db.Column('feed_id', db.Integer, db.ForeignKey('feed.id'), index=True),
db.Column('author_id', db.Integer, db.ForeignKey('author.id'), index=True))
feedcategories = db.Table(
'feedcategories',
db.Column('feed_id', db.Integer, db.ForeignKey('feed.id'), index=True),
db.Column('category_id',
db.Integer,
