class UserRole(BaseModel):
"""Join table between User and Role"""
user_id = foreign_key('User', primary_key=True)
user = relationship('User', back_populates='user_roles')
role_id = foreign_key('Role', primary_key=True)
role = relationship('Role', back_populates='role_users')
__repr_props__ = ('user_id', 'role_id')
def __init__(self, user=None, role=None, **kwargs):
super().__init__(**kwargs)
if user:
self.user = user
if role:
self.role = role
class SeriesTag(BaseModel):
"""Join table between Series and Tag"""
series_id = foreign_key('Series', primary_key=True)
series = relationship('Series', back_populates='series_tags')
tag_id = foreign_key('Tag', primary_key=True)
tag = relationship('Tag', back_populates='tag_series')
__repr_props__ = ('series_id', 'tag_id')
def __init__(self, series=None, tag=None, **kwargs):
super().__init__(**kwargs)
if series:
self.series = series
if tag:
self.tag = tag
class ArticleTag(BaseModel):
"""Join table between Article and Tag"""
# __tablename__ = 'article_tag'
article_id = foreign_key('Article', primary_key=True)
article = relationship('Article', back_populates='article_tags')
tag_id = foreign_key('Tag', primary_key=True)
tag = relationship('Tag', back_populates='tag_articles')
__repr_props__ = ('article_id', 'tag_id')
def __init__(self, article=None, tag=None, **kwargs):
super().__init__(**kwargs)
if article:
self.article = article
if tag:
self.tag = tag
class Series(Model):
title = Column(String(100))
slug = Column(String(100))
file_path = Column(String(255), nullable=True)
header_image = Column(String(255), nullable=True)
summary = Column(Text)
series_articles = relationship('SeriesArticle',
back_populates='series',
lazy='joined',
innerjoin=True,
order_by='SeriesArticle.part',
cascade='all, delete-orphan')
articles = association_proxy(
'series_articles',
'article',
creator=lambda article: SeriesArticle(article=article))
category_id = foreign_key('Category', nullable=True)
category = relationship('Category', back_populates='series')
series_tags = relationship('SeriesTag',
back_populates='series',
cascade='all, delete-orphan')
tags = association_proxy('series_tags',
'tag',
creator=lambda tag: SeriesTag(tag=tag))
__repr_props__ = ('id', 'title', 'articles')
@on('series_articles', 'append')
def on_append_series_article(self, series_article, *_):
# auto increment series article part number if necessary
if series_article.part is None:
series_article.part = len(self.series_articles) + 1
# set the article's category to be the same as the series' category
article = series_article.article
article.category = self.category
# set the article's tags to include the series' tags
for tag in self.tags:
if tag not in article.tags:
article.tags.append(tag)
class Article(Model):
title = Column(String(100))
slug = Column(String(100))
publish_date = Column(DateTime)
last_updated = Column(DateTime, nullable=True)
file_path = Column(String(255), nullable=True)
header_image = Column(String(255), nullable=True)
preview = Column(Text)
html = Column(Text)
category_id = foreign_key('Category', nullable=True)
category = relationship('Category', back_populates='articles')
__repr_props__ = ('id', 'title')
@classmethod
def get_published(cls):
return cls.query\
.filter(cls.publish_date <= utcnow())\
.order_by(cls.publish_date.desc(), cls.last_updated.desc())\
.all()
class SeriesArticle(Model):
"""Join table between Series and Article"""
article = relationship('Article',
back_populates='article_series',
uselist=False,
cascade='all, delete-orphan')
part = Column(Integer)
series_id = foreign_key('Series')
series = relationship('Series', back_populates='series_articles')
__repr_props__ = ('id', 'series_id', 'part')
def __init__(self, article=None, series=None, part=None, **kwargs):
super().__init__(**kwargs)
if article:
self.article = article
if series:
self.series = series
if part is not None:
self.part = part
class Article(Model):
title = Column(String(100))
slug = Column(String(100))
publish_date = Column(DateTime)
last_updated = Column(DateTime, nullable=True)
file_path = Column(String(255), nullable=True)
header_image = Column(String(255), nullable=True)
preview = Column(Text)
html = Column(Text)
author_id = foreign_key('User')
author = relationship('User', back_populates='articles')
article_series_id = foreign_key('SeriesArticle', nullable=True)
article_series = relationship('SeriesArticle',
back_populates='article',
cascade='all, delete-orphan',
single_parent=True)
series = association_proxy(
'article_series',
'series',
creator=lambda series: SeriesArticle(series=series))
part = association_proxy('article_series', 'part')
category_id = foreign_key('Category', nullable=True)
category = relationship('Category', back_populates='articles')
article_tags = relationship('ArticleTag',
back_populates='article',
cascade='all, delete-orphan')
tags = association_proxy('article_tags',
'tag',
creator=lambda tag: ArticleTag(tag=tag))
__repr_props__ = ('id', 'title')
@classmethod
def get_published(cls):
return cls.query\
.filter(cls.publish_date <= utcnow())\
.order_by(cls.publish_date.desc(), cls.last_updated.desc())\
.all()
def get_series_prev_next(self):
if self.series:
series_articles = self.series.series_articles
prev = None
prev_i = self.part - 2
if prev_i >= 0:
prev = series_articles[prev_i]
prev = {'slug': prev.article.slug, 'title': prev.article.title}
next = None
next_i = self.part
if next_i < len(series_articles):
next = series_articles[next_i]
next = {'slug': next.article.slug, 'title': next.article.title}
if prev and next:
return prev, next
elif prev:
return prev, None
elif next:
return None, next
return None, None
def get_prev_next(self):
if self.series:
return self.get_series_prev_next()
result = db.session.execute(
f'''
WITH articles AS (
SELECT
slug,
title,
ROW_NUMBER() OVER (ORDER BY publish_date ASC, last_updated ASC) AS row_number
FROM {Article.__tablename__}
WHERE publish_date <= :now AND article_series_id IS NULL
)
SELECT
slug,
title
FROM articles
WHERE row_number IN (
SELECT
row_number + i
FROM articles
CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n
WHERE slug = :slug
)
''', {
'now': utcnow(),
'slug': self.slug
})
rows = [{'slug': row[0], 'title': row[1]} for row in result.fetchall()]
if len(rows) == 1:
return None, None
elif len(rows) == 3:
return rows[0], rows[2]
elif rows[0]['slug'] == self.slug:
return None, rows[1]
elif rows[1]['slug'] == self.slug:
return rows[0], None
