zinricky
/
bubble
mirror of https://git.skyjake.fi/gemini/bubble.git
1
0
Fork 0
Code Issues Packages Projects Releases Wiki Activity
bubble/model.py

3034 lines
115 KiB
Python
Raw Permalink Blame History

import datetime
import hashlib
import mariadb
import os
import random
import re
import shutil
import time
from gmcapsule import Identity
from OpenSSL import crypto
from typing import Union
from utils import ago_text, clean_title, parse_at_names, shorten_text, strip_links, \
GeminiError, UTC, INNER_LINK_PREFIX, gemini_fetch, certificate_sha256, pubkey_sha256, \
strip_invalid
def parse_asn1_time(asn1_bytes):
m = re.search(r'^(\d\d\d\d)(\d\d)(\d\d)(\d\d)?(\d\d)?', asn1_bytes.decode('ascii'))
if m:
year, month, day = int(m[1]), int(m[2]), int(m[3])
if m[4] and m[5]:
hour, minute = int(m[4]), int(m[5])
else:
hour, minute = 0, 0
return datetime.datetime(year, month, day, hour, minute, 0)
return None
def address_hash(from_addr):
m = hashlib.sha256()
m.update(from_addr[0].encode('utf-8'))
return m.hexdigest()
# NOTE: All enum values are used in database, don't change them!
FOLLOW_USER, FOLLOW_POST, FOLLOW_SUBSPACE = range(3)
MUTE_USER, MUTE_POST, MUTE_SUBSPACE = range(3)
class LogEntry:
ACCOUNT_CREATED, POST_CREATED = range(2)
class Segment:
TEXT, LINK, IMAGE, ATTACHMENT, POLL = range(5)
def __init__(self, id, post, type, pos, content, url, counter):
self.id = id
self.post = post
self.type = type
self.pos = pos
self.content = content
self.url = url
self.counter = counter
class Notification:
# Note the default `notif` mask in create_tables.
MENTION = 0x000001
LIKE = 0x000002
COMMENT = 0x000004
COMMENT_ON_COMMENTED = 0x000008
COMMENT_ON_FOLLOWED_POST = 0x000010
POST_IN_FOLLOWED_SUBSPACE = 0x000020
POST_BY_FOLLOWED_USER = 0x000040
COMMENT_BY_FOLLOWED_USER = 0x000080
NEW_POLL = 0x000100
ISSUE_CLOSED = 0x000200
ADDED_AS_MODERATOR = 0x000400
REMOVED_AS_MODERATOR = 0x000800
COMMENT_IN_FOLLOWED_SUBSPACE = 0x001000
THANKS = 0x002000
REACTION = 0x004000
USER_CREATED = 0x008000
SUBSPACE_CREATED = 0x010000
SUBSPACE_INFO_UPDATED = 0x020000
ROLE_CHANGED = 0x040000
REMINDER = 0x080000
REPORT = 0x100000
USER_RENAMED = 0x200000
USER_FLAIR_CHANGED = 0x400000
ALL_MASK = 0xffffff
# Priority order for merging/overriding notifications.
PRIORITY = {
REACTION: -1, # only one kept
USER_RENAMED: -1,
USER_FLAIR_CHANGED: -1,
LIKE: -1,
THANKS: -1,
COMMENT_IN_FOLLOWED_SUBSPACE: 0,
POST_IN_FOLLOWED_SUBSPACE: 1,
COMMENT_BY_FOLLOWED_USER: 2,
POST_BY_FOLLOWED_USER: 3,
NEW_POLL: 4,
COMMENT_ON_FOLLOWED_POST: 5,
COMMENT_ON_COMMENTED: 6,
# Directed at the user.
COMMENT: 10,
MENTION: 11,
SUBSPACE_INFO_UPDATED: 12,
}
def __init__(self, id, type, dst, src, post, subspace, comment, is_sent, ts,
src_name=None, post_title=None, post_issueid=None, post_summary=None,
post_subname=None, post_subowner=None, subname=None, reaction=None,
post_subid=None):
self.id = id
self.type = type
self.dst = dst
self.src = src
self.post = post
self.subspace = subspace
self.comment = comment
self.is_sent = is_sent
self.ts = ts
self.src_name = src_name
self.post_title = post_title
self.post_issueid = post_issueid
self.post_summary = post_summary
self.post_subid = post_subid
self.post_subname = post_subname
self.post_subowner = post_subowner
self.subname = subname
self.reaction = reaction
def ymd_date(self, fmt='%Y-%m-%d', tz=None):
dt = datetime.datetime.fromtimestamp(self.ts, UTC)
if tz:
dt = dt.astimezone(tz)
return dt.strftime(fmt)
def age(self):
return ago_text(self.ts)
def title_text(self):
return shorten_text(self.post_title, 50) if self.post_title \
else shorten_text(strip_links(clean_title(self.post_summary)), 50) if self.post_summary \
else None
def entry(self, show_age=True, with_time=False, with_title=True, tz=None) -> tuple:
"""Returns (link, label) to use in the notification list."""
event = ''
icon = '🔔 '
kind = 'issue' if self.post_issueid else 'post'
with_src = True
if self.type == Notification.LIKE:
event = f'liked your {kind}'
icon = '👍 '
elif self.type == Notification.THANKS:
event = f'thanked you'
icon = '🙏 '
elif self.type == Notification.REACTION:
event = f'reacted to your {kind}'
icon = (self.reaction if self.reaction else '🔔') + ' '
elif self.type == Notification.COMMENT:
event = f'commented on your {kind}'
icon = '💬 '
elif self.type == Notification.COMMENT_ON_COMMENTED:
event = 'replied in discussion'
icon = '💬 '
elif self.type == Notification.COMMENT_ON_FOLLOWED_POST:
event = f'commented on followed {kind}'
icon = '💬 '
elif self.type == Notification.COMMENT_BY_FOLLOWED_USER:
event = f'commented on'
icon = '💬 '
elif self.type == Notification.POST_BY_FOLLOWED_USER:
event = f'published new {kind}'
elif self.type == Notification.POST_IN_FOLLOWED_SUBSPACE:
event = f'posted in {"u/" if self.post_subowner else "s/"}{self.post_subname}'
elif self.type == Notification.COMMENT_IN_FOLLOWED_SUBSPACE:
event = f'commented in {"u/" if self.post_subowner else "s/"}{self.post_subname}'
elif self.type == Notification.MENTION:
event = 'mentioned you'
elif self.type == Notification.NEW_POLL:
event = 'posted a new poll'
icon = '🗳️ '
elif self.type == Notification.ISSUE_CLOSED:
event = f'closed issue'
icon = '✔︎ '
elif self.type == Notification.ADDED_AS_MODERATOR:
event = f'added you as moderator of s/{self.subname}'
elif self.type == Notification.REMOVED_AS_MODERATOR:
event = f'removed you as moderator of s/{self.subname}'
elif self.type == Notification.USER_CREATED:
event = 'created an account'
icon = '👤 '
elif self.type == Notification.USER_RENAMED:
event = f'renamed their account (ID:{self.src})'
icon = '👤 '
elif self.type == Notification.USER_FLAIR_CHANGED:
event = 'changed their flair'
icon = '📛 '
elif self.type == Notification.SUBSPACE_CREATED:
event = f'created the subspace s/{self.subname}'
icon = '🌘 '
elif self.type == Notification.SUBSPACE_INFO_UPDATED:
event = f'updated info about s/{self.subname}'
icon = '✏️ '
elif self.type == Notification.ROLE_CHANGED:
event = "granted your account full access"
elif self.type == Notification.REMINDER:
icon = '🔔'
event = "Reminder"
with_src = False
elif self.type == Notification.REPORT:
icon = '⚠️ '
event = f"reported the {kind}"
if with_title:
vis_title = self.title_text()
if vis_title:
if self.type == Notification.MENTION:
event += ' in'
elif self.type == Notification.THANKS:
event += ' for'
elif self.type == Notification.COMMENT_ON_COMMENTED:
event += ' about'
elif self.type == Notification.COMMENT_IN_FOLLOWED_SUBSPACE:
event += ', in'
elif self.type & (Notification.POST_IN_FOLLOWED_SUBSPACE |
Notification.REMINDER):
event += ':'
if self.post_issueid:
event += f" #{self.post_issueid}"
event += f' "{vis_title}"'
age = f' · {self.age()}' if show_age else ''
hm_time = f" at {self.ymd_date('%H:%M', tz)}" if with_time else ''
return f'/notif/{self.id}', f'{icon}{self.src_name if with_src else ""} {event}{hm_time}{age}'
class Flair:
TYPES = [
('🛂', 'Note from moderator'),
('👤', 'Self description'),
('🏝️', 'Absence'),
('🗣️', 'Interaction style'),
('✍️', 'Writing style'),
('', 'Custom note'),
]
ENC_SYMBOLS = ['M', 'd', 'a', 'i', 'w', 'n']
MODERATOR_NOTE = 0
SELF_DESCRIPTION = 1
CUSTOM_NOTE = 5
def __init__(self, scope, flair_type, label, is_admin_assigned):
assert(flair_type < len(Flair.TYPES))
self.scope = scope
self.type = flair_type
self.label = label
self.is_admin_assigned = is_admin_assigned
def icon(self):
return Flair.TYPES[self.type][0]
def description(self):
return Flair.TYPES[self.type][1]
class User:
# Roles:
BASIC, ADMIN, LIMITED = range(3)
# Sort modes:
SORT_POST_RECENT = 'r'
SORT_POST_HOTNESS = 'h'
SORT_POST_ACTIVITY = 'a'
SORT_POST_FLAT = 'f'
SORT_COMMENT_OLDEST = 'o'
SORT_COMMENT_NEWEST = 'n'
# Flags:
HOME_FOLLOWED_FEED_FLAG = 0x0001
ASCII_ICONS_FLAG = 0x0002
HIDE_LIKES_FLAG = 0x0004
HIDE_REACTIONS_FLAG = 0x0008
HIDE_THANKS_FLAG = 0x0010
SHORT_PREVIEW_FLAG = 0x0020
DISABLE_ROTATION_FLAG = 0x0040
HOME_NO_USERS_FEED_FLAG = 0x0080
HOME_USERS_FEED_FLAG = 0x0100
COMPOSER_SPLIT_FLAG = 0x0200
HIDE_FLAIRS_FLAG = 0x0400
FEED_MASK = HOME_FOLLOWED_FEED_FLAG | HOME_USERS_FEED_FLAG | HOME_NO_USERS_FEED_FLAG
def __init__(self, id, name, info, flair, url, recovery, avatar, role, flags, notif, \
email, email_inter, email_range, \
password, ts_password, ts_created, ts_active, sort_post, sort_cmt,
timezone):
self.id = id
self.name = name
self.info = info
self.flair = flair
self.url = url
self.recovery = recovery
self.avatar = avatar
self.role = role
self.flags = flags
self.notif = notif
self.email = email
self.email_inter = email_inter
self.email_range = email_range
self.password = password
self.ts_password = ts_password
self.ts_created = ts_created
self.ts_active = ts_active
self.sort_post = sort_post
self.sort_cmt = sort_cmt
self.timezone = timezone
def subspace_link(self, prefix=''):
return f'=> /u/{self.name} {self.avatar} {prefix}u/{self.name}\n'
def password_expiry(self):
if self.ts_password:
return max(0, 3600 - (int(time.time()) - self.ts_password))
return None
def parse_flair(user_flair):
flairs = []
for flair in user_flair.split('\n'):
try:
flags, scope, enc_type, label = flair.split('\t')
is_admin_assigned = (flags == '*')
scope = int(scope)
type = Flair.ENC_SYMBOLS.index(enc_type)
flairs.append(Flair(scope, type, label, is_admin_assigned))
except Exception as x:
print(x)
flairs = list(sorted(flairs, key=lambda f: (f.scope, f.type, f.label)))
return flairs
def unparse_flair(flairs):
result = []
for f in flairs:
assert(type(f.scope) == int)
#icon = f'{f.icon} ' if f.icon else ''
label = f.label.replace('\t', '') # remove separators
result.append(f"{'*' if f.is_admin_assigned else ''}\t{f.scope}\t{Flair.ENC_SYMBOLS[f.type]}\t{label}")
return '\n'.join(result)
def render_flair(user_flair, context, abbreviate=False, long_form=False, db=None,
user_mod=False, user_op=False):
"""
Arguments:
db (Database): database object for looking up subspace names
in the long form.
context (Subspace): where the flair is being shown. If None,
the flair is being shown in the home feed.
abbreviate (bool): user-provided text is omitted and only
icons are shown.
long_form (bool): a description of the flair type is included
and the output is formatted onto multiple lines instead
of being a single line.
"""
out = ''
has_abbrev = False
if user_flair.strip():
for flair in User.parse_flair(user_flair):
#print(user_flair, icon, label, scope)
if long_form:
# Show everything in the long form.
if flair.icon():
out += flair.icon() + ' '
if flair.scope:
subspace = db.get_subspace(id=flair.scope)
if subspace:
scope = f" (in {subspace.title()})"
else:
scope = " (in a deleted subspace)"
else:
scope = ''
if flair.icon():
out += f"{flair.description()}: "
elif not scope and flair.is_admin_assigned:
out += '📛 Assigned flair: '
else:
out += '📛 Personal flair: '
out += f"{flair.label}{scope}{' (set by admin)' if flair.is_admin_assigned else ''}\n"
elif flair.scope == 0 or (context and flair.scope == context.id):
if abbreviate:
if not flair.icon() or flair.type == Flair.SELF_DESCRIPTION:
has_abbrev = True
continue
# Just showing icons and "...".
out += flair.icon()
# elif flair.label:
# has_abbrev = True
else:
# Showing icons and labels.
if len(out):
out += ', '
icon = flair.icon() if flair.type != Flair.SELF_DESCRIPTION else ''
out += icon
if flair.label:
if icon: out += ' '
out += flair.label
if not long_form:
if user_op or user_mod:
if len(out):
out = ', ' + out
if user_op and user_mod:
out = 'OP/mod' + out
elif user_op:
out = 'OP' + out
elif user_mod:
out = 'mod' + out
if has_abbrev:
out += '...'
return out
class Subspace:
OMIT_FROM_ALL_FLAG = 0x1
ISSUE_TRACKER = 0x2
OMIT_FROM_FEED_BY_DEFAULT = 0x4
HIDE_OMIT_SETTING_FLAG = 0x8
def __init__(self, id, name, info, url, flags, owner, ts_created, ts_active,
latest_post_id=None, num_people=None, num_posts=None, num_cmts=None):
self.id = id
self.name = name
self.info = info
self.url = url
self.flags = flags
self.owner = owner
self.ts_created = ts_created
self.ts_active = ts_active
self.latest_post_id = latest_post_id
self.num_people = num_people
self.num_posts = num_posts
self.num_cmts = num_cmts
def title(self):
if self.owner:
return 'u/' + self.name
else:
return 's/' + self.name
def subspace_link(self):
return f'=> /{self.title()} {self.title()}\n'
class Repository:
def __init__(self, id, subspace, clone_url, view_url, idlabel, ts_fetch):
self.id = id
self.subspace = subspace
self.clone_url = clone_url
self.view_url = view_url
self.idlabel = idlabel
self.ts_fetch = ts_fetch
class Commit:
def __init__(self, repo, hash, msg, ts):
self.repo = repo
self.hash = hash
self.msg = msg
self.ts = ts
def ymd_date(self, tz=None):
dt = datetime.datetime.fromtimestamp(self.ts, UTC)
if tz:
dt = dt.astimezone(tz)
return dt.strftime('%Y-%m-%d')
def entry(self, view_url, outgoing=False):
if outgoing:
return f'=> {view_url}/{self.hash} Commit {self.hash[:8]} "{shorten_text(clean_title(self.msg), 40)}" · {ago_text(self.ts)}\n'
else:
return f'=> {view_url}/{self.hash} Commit {self.hash[:8]} · {clean_title(self.msg)}\n{ago_text(self.ts)}\n'
class Post:
TAG_PINNED = 'pinned'
TAG_ANNOUNCEMENT = 'announcement' # Admin only
TAG_POLL = 'poll'
TAG_CLOSED = 'closed'
OMIT_FROM_FEED_FLAG = 0x1
OMIT_FROM_ALL_FLAG = 0x2
DISABLE_FEATURED_LINK_FLAG = 0x4
LOCKED_FLAG = 0x8
SORT_CREATED, SORT_ACTIVE, SORT_HOTNESS = range(3)
def __init__(self, id, subspace, parent, user, issueid, title, flags, is_draft, is_pinned,
num_cmts, num_likes, tags, ts_created, ts_edited, summary,
sub_name=None, sub_owner=None, poster_avatar=None, poster_name=None,
poster_flair=None, num_notifs=0, num_per_day=None, ts_comment=None):
self.id = id
self.subspace = subspace
self.parent = parent
self.user = user
self.issueid = issueid
self.title = title
self.flags = flags
self.is_draft = is_draft
self.is_pinned = is_pinned
self.num_cmts = num_cmts
self.num_likes = num_likes
self.tags = tags
self.ts_created = ts_created
self.ts_edited = ts_edited
self.ts_comment = ts_comment
self.summary = summary
self.sub_name = sub_name
self.sub_owner = sub_owner
self.poster_avatar = poster_avatar
self.poster_name = poster_name
self.poster_flair = poster_flair
self.num_notifs = num_notifs
self.num_per_day = num_per_day
def title_text(self):
"""Title shown in the composer."""
if len(self.title):
return self.title
elif self.parent:
text = f'Comment on {"u" if self.sub_owner else "s"}/{self.sub_name}/{self.parent}'
return text
else:
return '(untitled issue)' if self.issueid else '(untitled post)'
def feed_title(self, max_len=100):
if len(self.title) == 0:
return shorten_text(strip_links(clean_title(self.summary), placeholder=False), max_len)
else:
return shorten_text(strip_links(self.title, placeholder=False), max_len)
def quoted_title(self, max_len=60):
return f'"{self.feed_title(max_len)}"'
def ymd_date(self, tz=None):
dt = datetime.datetime.fromtimestamp(self.ts_created, UTC)
if tz:
dt = dt.astimezone(tz)
return dt.strftime('%Y-%m-%d')
def ymd_hm(self, tz=None, date_fmt='%Y-%m-%d', time_prefix=''):
dt = datetime.datetime.fromtimestamp(self.ts_created, UTC)
if tz:
dt = dt.astimezone(tz)
return dt.strftime(f'{date_fmt} {time_prefix}%H:%M')
def ymd_hm_tz(self, tz=None):
dt = datetime.datetime.fromtimestamp(self.ts_created, UTC)
if tz:
dt = dt.astimezone(tz)
return dt.strftime(f'%Y-%m-%d %H:%M %z')
else:
return dt.strftime(f'%Y-%m-%d %H:%M UTC')
def age(self, tz=None):
if self.is_draft:
return 'Now'
else:
return ago_text(self.ts_created, tz=tz)
def page_url(self):
if self.sub_owner:
return f'/u/{self.poster_name}/{self.id}'
return f'/s/{self.sub_name}/{self.issueid if self.issueid else self.id}'
class Crossref (Post):
def __init__(self, id, subspace, user, issueid, title, tags, ts_created,
sub_name):
super().__init__(id, subspace, None, user, issueid, title, None,
None, None, None, None, tags, ts_created,
None, None, sub_name)
def incoming_entry(self):
x_url = f'/s/{self.sub_name}/{self.issueid}'
x_icon = '✔︎' if '✔︎' in self.tags else '🐞'
return f'=> {x_url} {x_icon} Mentioned in [#{self.issueid}] {self.title}\n{ago_text(self.ts_created)}\n'
def outgoing_entry(self):
x_url = f'/s/{self.sub_name}/{self.issueid}'
x_icon = '✔︎' if '✔︎' in self.tags else '🐞'
return f'=> {x_url} {x_icon} {INNER_LINK_PREFIX}[#{self.issueid}] {self.title}\n'
class File:
def __init__(self, id, segment, user, name, mimetype, data,
segment_url=None, segment_label=None, segment_post=None):
self.id = id
self.segment = segment
self.user = user
self.name = name
self.mimetype = mimetype
self.data = data
self.segment_url = segment_url
self.segment_label = segment_label
self.segment_post = segment_post
class Database:
def __init__(self, cfg):
self.cfg = cfg
self.max_summary = 500
self.conn = mariadb.connect(
user=cfg.get('db.user'),
password=cfg.get('db.password'),
host=cfg.get('db.host'),
port=cfg.getint('db.port'),
database=cfg.get('db.name'))
self.conn.autocommit = False
self.repo_cachedir = cfg.get('repo.cachedir', None)
def close(self):
if self.conn:
self.conn.close()
self.conn = None
def create_tables(self, admin_certpass):
db = self.conn.cursor()
db.execute("""CREATE TABLE IF NOT EXISTS users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) UNIQUE,
flair VARCHAR(1000) DEFAULT '',
info VARCHAR(1000) DEFAULT '',
url VARCHAR(1000) DEFAULT '',
recovery VARCHAR(1000) DEFAULT '',
avatar CHAR(2),
role INT DEFAULT 0,
flags INT DEFAULT 0,
notif INT DEFAULT ?,
email VARCHAR(256) DEFAULT NULL,
email_inter INT DEFAULT 30,
email_range VARCHAR(30) DEFAULT '',
password VARCHAR(128) DEFAULT NULL COLLATE utf8mb4_bin,
ts_password TIMESTAMP DEFAULT '2000-01-01 00:00:00',
ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_email TIMESTAMP DEFAULT '2000-01-01 00:00:00',
sort_post CHAR(1) DEFAULT 'r',
sort_cmt CHAR(1) DEFAULT 'o',
timezone VARCHAR(40) DEFAULT 'UTC'
)""", (Notification.ALL_MASK,))
db.execute("""CREATE TABLE IF NOT EXISTS certs (
user INT NOT NULL,
fp_cert CHAR(64) UNIQUE NOT NULL,
fp_pubkey CHAR(64) NOT NULL,
subject VARCHAR(200) NOT NULL,
ts_until DATETIME NOT NULL,
INDEX (user)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS subspaces (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) UNIQUE,
info VARCHAR(1000),
url VARCHAR(1000) DEFAULT '',
flags INT DEFAULT 0,
owner INT DEFAULT 0,
nextissueid INT DEFAULT 1,
ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY (name, owner)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS mods (
subspace INT,
user INT,
UNIQUE KEY (subspace, user)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS posts (
id INT PRIMARY KEY AUTO_INCREMENT,
subspace INT,
parent INT,
user INT NOT NULL,
issueid INT,
title VARCHAR(1000),
flags INT DEFAULT 0,
is_draft BOOLEAN DEFAULT TRUE,
is_pinned TINYINT(1) DEFAULT 0,
num_cmts INT DEFAULT 0,
num_people INT DEFAULT 0, -- distinct commenters
num_likes INT DEFAULT 0,
tags VARCHAR(1000) DEFAULT '',
ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_edited TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_comment TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- time of latest comment
summary TEXT DEFAULT '',
UNIQUE KEY (subspace, issueid),
INDEX (subspace),
INDEX (parent),
INDEX (user),
INDEX (issueid)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS tags (
post INT NOT NULL,
tag VARCHAR(30),
UNIQUE KEY (post, tag),
iNDEX (post)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS segments (
id INT PRIMARY KEY AUTO_INCREMENT,
post INT NOT NULL,
type INT DEFAULT 0,
pos INT DEFAULT 0,
content MEDIUMTEXT DEFAULT NULL,
url VARCHAR(1000) DEFAULT NULL,
counter INT DEFAULT 0
)""")
db.execute("""CREATE TABLE IF NOT EXISTS notifs (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT NOT NULL,
dst INT NOT NULL,
src INT,
post INT,
subspace INT,
comment INT, -- if not NULL, notification is about a comment (can be linked to)
is_sent BOOLEAN DEFAULT FALSE,
is_hidden BOOLEAN DEFAULT FALSE,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX (dst),
INDEX (dst, post),
CONSTRAINT no_self_notif CHECK (src!=dst)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS follow (
user INT NOT NULL,
type INT,
target INT DEFAULT NULL,
UNIQUE KEY (user, type, target),
INDEX (user)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS mute (
user INT NOT NULL,
type INT,
target INT,
UNIQUE KEY (user, type, target),
INDEX (user)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS likes (
user INT NOT NULL,
post INT NOT NULL,
UNIQUE KEY (user, post),
INDEX (post)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS reactions (
user INT NOT NULL,
post INT NOT NULL,
reaction VARCHAR(4) NOT NULL,
UNIQUE KEY (user, post),
INDEX (post)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS votes (
user INT NOT NULL,
segment INT NOT NULL,
post INT NOT NULL,
UNIQUE KEY (user, post),
INDEX (post)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS files (
id INT PRIMARY KEY AUTO_INCREMENT,
segment INT,
user INT NOT NULL,
name VARCHAR(1000) DEFAULT '',
mimetype VARCHAR(1000),
data MEDIUMBLOB
)""")
db.execute("""CREATE TABLE IF NOT EXISTS tokens (
user INT NOT NULL,
token CHAR(10) NOT NULL,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX (user),
INDEX (token)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS repos (
id INT PRIMARY KEY AUTO_INCREMENT,
subspace INT NOT NULL UNIQUE,
clone_url VARCHAR(300) DEFAULT '',
view_url VARCHAR(300) DEFAULT '',
idlabel VARCHAR(10) DEFAULT 'IssueID',
ts_fetch TIMESTAMP
)""")
db.execute("""CREATE TABLE IF NOT EXISTS commits (
repo INT NOT NULL,
hash CHAR(64) NOT NULL,
msg VARCHAR(200),
ts TIMESTAMP,
UNIQUE INDEX (repo, hash),
INDEX (repo)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS issuerefs (
repo INT NOT NULL,
commit CHAR(64) NOT NULL,
issue INT NOT NULL,
UNIQUE INDEX (repo, commit, issue),
INDEX (repo, commit),
INDEX (repo, issue)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS crossrefs (
subspace INT NOT NULL, -- for convenience
post INT NOT NULL, -- for convenience
segment INT NOT NULL,
issueid INT NOT NULL, -- source issue ID
ts TIMESTAMP NOT NULL, -- source segment's timestamp
dst_post INT NOT NULL,
dst_issueid INT NOT NULL,
UNIQUE INDEX (segment, dst_issueid),
INDEX (segment),
INDEX (issueid),
INDEX (dst_issueid)
)""")
db.execute("""CREATE TABLE IF NOT EXISTS log (
remote CHAR(64) NOT NULL,
type INT NOT NULL,
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX (remote),
INDEX (type)
)""")
db.execute('INSERT IGNORE INTO users (name, avatar, role, password, ts_password) '
'VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP())',
('admin', '🚀', User.ADMIN, admin_certpass))
db.execute('INSERT IGNORE INTO subspaces (name, info, owner) VALUES (?, ?, 1)',
('admin', 'Administator of the discussion board'))
self.commit()
def commit(self):
return self.conn.commit()
def create_user(self, username, identity, role=User.BASIC):
cur = self.conn.cursor()
cur.execute("""
INSERT INTO users (name, avatar, role)
VALUES (?, ?, ?)""",
(username, '🚀', role))
self.commit()
user_id = cur.lastrowid
if identity:
try:
self.add_certificate(user_id, identity)
except:
# Invalid certificate?
cur.execute("DELETE FROM users WHERE id=?", (user_id,))
self.commit()
raise GeminiError(62, 'Certificate not valid')
try:
cur.execute("INSERT INTO subspaces (name, owner) VALUES (?, ?)", (username, user_id))
self.commit()
except:
# Undo the previous database changes.
cur.execute("DELETE FROM users WHERE id=?", (user_id,))
cur.execute("DELETE FROM certs WHERE user=?", (user_id,))
self.commit()
raise GeminiError(50, 'Subspace already exists')
self.notify_admin(Notification.USER_CREATED, user_id)
return user_id
def get_users(self, role, max_age=None, avatar=None, empty_info=None, max_post_count=None):
cur = self.conn.cursor()
cond = ['role=?']
values = [role]
if max_age != None:
cond.append('TIMESTAMPDIFF(SECOND, ts_created, CURRENT_TIMESTAMP())<?')
values.append(max_age)
if avatar != None:
cond.append('avatar=?')
values.append(avatar)
if empty_info != None:
cond.append('info=""')
if max_post_count != None:
cond.append('(SELECT COUNT(*) FROM posts WHERE user=users.id)<=?')
values.append(max_post_count)
cur.execute(f"""
SELECT id, name, avatar, UNIX_TIMESTAMP(ts_created), UNIX_TIMESTAMP(ts_active)
FROM users
WHERE {' AND '.join(cond)}""", values)
users = []
for (id, name, avatar, ts_created, ts_active) in cur:
user = User(id, name, None, None, None, None,
avatar, role, 0, 0, None, None, None, None, None,
ts_created, ts_active, None, None, None)
users.append(user)
return users
def get_user(self, id=None, identity=None, name=None, password=None):
cur = self.conn.cursor()
if identity:
# First try finding via the certificate fingerprint.
cur.execute("SELECT user FROM certs WHERE fp_cert=?", (identity.fp_cert,))
for (id,) in cur:
pass
cond = []
values = []
if id != None:
cond.append('u.id=?')
values.append(id)
elif name != None:
cond.append('u.name=?')
values.append(name)
if password:
cond.append('u.password=?')
values.append(password)
if not cond:
return None
cur.execute(f"""
SELECT
u.id,
u.name,
u.info,
u.flair,
u.url,
u.recovery,
u.avatar,
u.role,
u.flags,
u.notif,
u.email,
u.email_inter,
u.email_range,
u.password,
UNIX_TIMESTAMP(u.ts_password),
UNIX_TIMESTAMP(u.ts_created),
UNIX_TIMESTAMP(u.ts_active),
u.sort_post,
u.sort_cmt,
u.timezone,
s.id
FROM users u
JOIN subspaces s ON s.owner=u.id
WHERE {' AND '.join(cond)}""", values)
for (id, name, info, flair, url, recovery, avatar, role, flags, notif, email, email_inter,
email_range, password, ts_password, ts_created, ts_active,
sort_post, sort_cmt, timezone, user_subspace_id) in cur:
user = User(id, name, info, flair, url, recovery, avatar, role, flags, notif,
email, email_inter, email_range,
password, ts_password,
ts_created, ts_active,
sort_post, sort_cmt, timezone)
user.moderated_subspace_ids = [user_subspace_id] + self.get_moderated_subspace_ids(user)
return user
return None
def add_certificate(self, user: Union[User, int], identity):
if isinstance(user, User):
user = user.id
cur = self.conn.cursor()
cert = crypto.load_certificate(crypto.FILETYPE_ASN1, identity.cert)
cur.execute("""
INSERT INTO certs (user, fp_cert, fp_pubkey, subject, ts_until)
VALUES (?, ?, ?, ?, ?)""",
(user,
identity.fp_cert,
identity.fp_pubkey,
identity.subject()['CN'],
parse_asn1_time(cert.get_notAfter())))
self.commit()
def remove_certificate(self, user, fp_cert):
"""If `fp_cert` is NULL, deletes all certificates of the user."""
cur = self.conn.cursor()
if fp_cert is None:
cur.execute("DELETE FROM certs WHERE user=?", (user.id,))
else:
cur.execute("DELETE FROM certs WHERE user=? AND fp_cert=?", (user.id, fp_cert))
self.commit()
def recover_certificate(self, user, identity):
_, _, body = gemini_fetch(user.recovery)
recovered = Identity(crypto.load_certificate(crypto.FILETYPE_PEM, body))
if recovered.fp_cert == identity.fp_cert:
self.add_certificate(user, recovered)
def get_certificates(self, user) -> list:
cur = self.conn.cursor()
cur.execute("SELECT fp_cert, subject, ts_until FROM certs WHERE user=?", (user.id,))
certs = []
for (fp, subject, expiry) in cur:
certs.append((fp, subject, expiry))
return certs
def update_user(self, user: Union[User, int],
role=None,
avatar=None, name=None, info=None, flair=None,
url=None, recovery=None,
email=None, email_inter=None, email_range=None,
notif=None, flags=None,
password=None, password_expiration_offset_minutes=0,
sort_post=None, sort_cmt=None, timezone=None,
active=False):
if type(user) is User:
user = user.id
cur = self.conn.cursor()
stm = []
values = []
if avatar != None:
stm.append('avatar=?')
values.append(avatar)
if role != None:
stm.append('role=?')
values.append(role)
if name != None:
stm.append('name=?')
values.append(name)
if info != None:
stm.append('info=?')
values.append(info)
if flair != None:
stm.append('flair=?')
values.append(flair)
if url != None:
stm.append('url=?')
values.append(url)
if recovery != None:
stm.append('recovery=?')
values.append(recovery)
if password != None:
stm.append(f'ts_password=TIMESTAMPADD(MINUTE, {password_expiration_offset_minutes}, CURRENT_TIMESTAMP())')
stm.append('password=?')
values.append(password if password else None)
if email != None:
stm.append('email=?')
values.append(email if email else None)
if email_inter != None:
stm.append('email_inter=?')
values.append(min(max(5, email_inter), 60 * 24))
if email_range != None:
stm.append('email_range=?')
values.append(email_range) # format was validated earlier
if notif != None:
stm.append('notif=?')
values.append(notif)
if flags != None:
stm.append('flags=?')
values.append(flags)
if sort_post:
stm.append('sort_post=?')
values.append(sort_post)
if sort_cmt:
stm.append('sort_cmt=?')
values.append(sort_cmt)
if timezone:
stm.append('timezone=?')
values.append(timezone)
if active:
stm.append('ts_active=CURRENT_TIMESTAMP()')
if stm:
values.append(user)
cur.execute(f"UPDATE users SET {','.join(stm)} WHERE id=?", tuple(values))
if name != None:
cur.execute(f"UPDATE subspaces SET name=? WHERE owner=?", (name, user))
self.commit()
def add_flair(self, user, flair, editor: User):
if editor.role != User.ADMIN:
# Some words are reserved.
RESTRICTED_WORDS = ['OP', 'mod', 'sysop', 'admin']
if flair.type in (Flair.CUSTOM_NOTE, Flair.SELF_DESCRIPTION) and \
flair.label in RESTRICTED_WORDS:
raise GeminiError(61, 'Not authorized')
flairs = [flair]
for f in User.parse_flair(user.flair):
if f.scope == flair.scope and f.type == flair.type:
# The new one replaces this one.
if f.is_admin_assigned and editor.role != User.ADMIN:
raise GeminiError(61, 'Not authorized')
continue
flairs.append(f)
self.update_user(user, flair=User.unparse_flair(flairs))
assert(flair.scope != None)
self.notify_moderators(Notification.USER_FLAIR_CHANGED,
editor,
None,
subspace=self.get_subspace(id=flair.scope) if flair.scope != 0 else None)
def remove_flair(self, user, flair, editor: User):
flairs = []
for f in User.parse_flair(user.flair):
if f.scope == flair.scope and f.type == flair.type:
# This will be removed.
if not f.is_admin_assigned or editor.role == User.ADMIN:
continue
flairs.append(f)
self.update_user(user, flair=User.unparse_flair(flairs))
assert(flair.scope != None)
self.notify_moderators(Notification.USER_FLAIR_CHANGED,
editor,
None,
subspace=self.get_subspace(id=flair.scope) if flair.scope != 0 else None)
def destroy_user(self, user: User):
# This will already delete all posts made in the user's own subspace.
self.destroy_subspace(self.get_subspace(owner=user.id))
# Delete everything relating to this user.
cur = self.conn.cursor()
cur.execute("DELETE FROM users WHERE id=?", (user.id,))
cur.execute("DELETE FROM certs WHERE user=?", (user.id,))
cur.execute("DELETE FROM mods WHERE user=?", (user.id,))
cur.execute("DELETE FROM tags WHERE post IN (SELECT id FROM posts WHERE user=?)",
(user.id,))
cur.execute("DELETE FROM segments WHERE post IN (SELECT id FROM posts WHERE user=?)",
(user.id,))
cur.execute("DELETE FROM likes WHERE user=? OR post IN (SELECT id FROM posts WHERE user=?)",
(user.id, user.id))
cur.execute("DELETE FROM votes WHERE user=?", (user.id,))
cur.execute("DELETE FROM reactions WHERE user=?", (user.id,))
cur.execute("DELETE FROM notifs WHERE src=? OR dst=? OR post IN (SELECT id FROM posts WHERE user=?)", (user.id, user.id, user.id))
cur.execute("DELETE FROM follow WHERE type=? AND target=?", (FOLLOW_USER, user.id))
cur.execute("DELETE FROM files WHERE user=?", (user.id,))
cur.execute("DELETE FROM posts WHERE user=?", (user.id,))
self.commit()
def get_follows(self, user: User) -> set:
cur = self.conn.cursor()
cur.execute("SELECT type, target FROM follow WHERE user=?", (user.id,))
follows = set()
for (type, target) in cur:
follows.add((type, target))
return follows
def get_mutes(self, user: User) -> set:
cur = self.conn.cursor()
cur.execute("SELECT type, target FROM mute WHERE user=?", (user.id,))
mutes = set()
for (type, target) in cur:
mutes.add((type, target))
return mutes
def modify_follows(self, user: User, is_adding: bool, follow_type, target_id):
cur = self.conn.cursor()
if is_adding:
cur.execute("INSERT IGNORE INTO follow (user, type, target) VALUES (?, ?, ?)",
(user.id, follow_type, target_id))
else:
cur.execute("DELETE FROM follow WHERE user=? AND type=? AND target=?",
(user.id, follow_type, target_id))
self.commit()
def modify_mutes(self, user: User, is_adding: bool, mute_type, target_id):
cur = self.conn.cursor()
if is_adding:
cur.execute("INSERT IGNORE INTO mute (user, type, target) VALUES (?, ?, ?)",
(user.id, mute_type, target_id))
else:
cur.execute("DELETE FROM mute WHERE user=? AND type=? AND target=?",
(user.id, mute_type, target_id))
self.commit()
def create_file(self, user: User, name: str, mimetype: str, data: bytes) -> int:
cur = self.conn.cursor()
cur.execute("""
INSERT INTO files (segment, user, name, mimetype, data)
VALUES (?, ?, ?, ?, ?)""", (0, user.id, name, mimetype, data))
self.commit()
return cur.lastrowid
def get_file(self, id):
cur = self.conn.cursor()
cur.execute("""
SELECT segment, user, name, mimetype, data
FROM files
WHERE id=?""", (id,))
for (segment, user, name, mimetype, data) in cur:
return File(id, segment, user, name, mimetype, data)
return None
def get_user_files(self, user: User):
cur = self.conn.cursor()
cur.execute("""
SELECT
f.id, f.segment, f.name, f.mimetype, f.data, s.url, s.content, s.post
FROM files f
JOIN segments s ON s.id=f.segment
WHERE user=?
""", (user.id,))
files = []
for (file_id, segment, name, mimetype, data, url, label, post) in cur:
files.append(File(file_id, segment, user.id, name, mimetype, data,
url, label, post))
return files
def get_subspace_files(self, subspace: Subspace):
cur = self.conn.cursor()
cur.execute("""
SELECT
f.id, f.segment, f.user, f.name, f.mimetype, f.data, s.url, s.content, s.post
FROM files f
JOIN segments s ON f.segment=s.id
JOIN posts p ON s.post=p.id
WHERE p.subspace=?
""", (subspace.id,))
files = []
for (file_id, segment, user, name, mimetype, data, url, label, post) in cur:
files.append(File(file_id, segment, user, name, mimetype, data,
url, label, post))
return files
def get_time_files(self, ts_range):
cur = self.conn.cursor()
cur.execute("""
SELECT
f.id, f.segment, f.name, f.mimetype, f.data, s.url, s.content, s.post, p.user
FROM files f
JOIN segments s ON s.id=f.segment
JOIN posts p ON s.post=p.id
WHERE UNIX_TIMESTAMP(p.ts_edited)>=? AND UNIX_TIMESTAMP(p.ts_edited)<?
""", ts_range)
files = []
for (file_id, segment, name, mimetype, data, url, label, post, post_user) in cur:
files.append(File(file_id, segment, post_user, name, mimetype, data,
url, label, post))
return files
def set_file_segment(self, file_id, segment_id):
cur = self.conn.cursor()
cur.execute("UPDATE files SET segment=? WHERE id=?", (segment_id, file_id))
self.commit()
def create_subspace(self, name: str, mod_id: int):
cur = self.conn.cursor()
cur.execute("""
INSERT INTO subspaces (name)
VALUES (?)
""", (name,))
self.commit()
sub_id = cur.lastrowid
cur.execute("""
INSERT INTO mods (subspace, user)
VALUES (?, ?)
""", (sub_id, mod_id))
self.commit()
self.modify_follows(self.get_user(id=mod_id), True, FOLLOW_SUBSPACE, sub_id)
self.notify_admin(Notification.SUBSPACE_CREATED, mod_id, sub_id)
def destroy_subspace(self, sub: Subspace):
cur = self.conn.cursor()
cur.execute("DELETE FROM subspaces WHERE id=?", (sub.id,))
cur.execute("DELETE FROM mods WHERE subspace=?", (sub.id,))
cur.execute("DELETE FROM tags WHERE post IN (SELECT id FROM posts WHERE subspace=?)",
(sub.id,))
cur.execute("""DELETE FROM files WHERE segment IN (
SELECT id FROM segments WHERE post IN (
SELECT id FROM posts WHERE subspace=?
)
)""", (sub.id,))
cur.execute("DELETE FROM segments WHERE post IN (SELECT id FROM posts WHERE subspace=?)",
(sub.id,))
cur.execute("DELETE FROM crossrefs WHERE subspace=?", (sub.id,))
cur.execute("DELETE FROM notifs WHERE post IN (SELECT id FROM posts WHERE subspace=?)",
(sub.id,))
cur.execute("DELETE FROM likes WHERE post IN (SELECT id FROM posts WHERE subspace=?)",
(sub.id,))
cur.execute("DELETE FROM votes WHERE post IN (SELECT id FROM posts WHERE subspace=?)",
(sub.id,))
cur.execute("DELETE FROM posts WHERE subspace=?", (sub.id,))
cur.execute("DELETE FROM follow WHERE type=? AND target=?", (FOLLOW_SUBSPACE, sub.id,))
self.commit()
def update_subspace(self, subspace: Union[int, Subspace], info=None, url=None, flags=None,
name=None, active=False, actor_id=None):
need_notify = False
if type(subspace) is Subspace:
subspace = subspace.id
cur = self.conn.cursor()
stm = []
values = []
if name != None:
stm.append('name=?')
values.append(name)
need_notify = True
if info != None:
stm.append('info=?')
values.append(info)
need_notify = True
if url != None:
stm.append('url=?')
values.append(url)
need_notify = True
if flags != None:
stm.append('flags=?')
values.append(flags)
if active:
stm.append('ts_active=CURRENT_TIMESTAMP()')
if stm:
values.append(subspace)
cur.execute(f"UPDATE subspaces SET {','.join(stm)} WHERE id=?", tuple(values))
self.commit()
if need_notify and actor_id:
self.notify_admin(Notification.SUBSPACE_INFO_UPDATED, actor_id, subspace)
def get_subspace(self, id=None, owner=None, name=None):
cur = self.conn.cursor()
if id:
cond = 'id=?'
cond_value = id
elif owner:
cond = 'owner=?'
cond_value = owner if isinstance(owner, int) else owner.id
else:
cond = 'name=?'
cond_value = name
cur.execute(f"""
SELECT
id, name, info, url, flags, owner,
UNIX_TIMESTAMP(ts_created),
UNIX_TIMESTAMP(ts_active)
FROM subspaces
WHERE {cond}""",
(cond_value,))
for (id, name, info, url, flags, owner, ts_created, ts_active) in cur:
return Subspace(id, name, info, url, flags, owner, ts_created, ts_active)
return None
def get_subspaces(self, owner=None, mod=None, locked=False):
cond = []
values = []
cur = self.conn.cursor()
if locked:
cond.append("id NOT IN (SELECT DISTINCT subspace FROM mods) AND owner=0")
if owner != None:
cond.append('owner=?')
values.append(owner)
if mod != None:
cond.append('mods.user=?')
values.append(mod)
cur.execute(f"""
SELECT DISTINCT
id, name, info, url, flags, owner,
UNIX_TIMESTAMP(ts_created),
UNIX_TIMESTAMP(ts_active),
(SELECT p.id
FROM posts p
WHERE p.subspace=sub.id AND p.parent=0 AND p.is_draft=FALSE
ORDER BY p.ts_created DESC
LIMIT 1
) AS latest_post_id,
(SELECT COUNT(DISTINCT(p.user))
FROM posts p
WHERE p.subspace=sub.id AND p.is_draft=FALSE
) AS num_people,
(SELECT COUNT(p.id)
FROM posts p
WHERE p.subspace=sub.id AND p.parent=0 AND p.is_draft=FALSE
) AS num_posts,
(SELECT COUNT(p.id)
FROM posts p
WHERE p.subspace=sub.id AND p.parent!=0 AND p.is_draft=FALSE
) AS num_cmts
FROM subspaces sub
LEFT JOIN mods ON id=mods.subspace
WHERE {' AND '.join(cond)}
ORDER BY name
""", tuple(values))
subs = []
for (id, name, info, url, flags, owner, ts_created, ts_active,
latest_post_id, num_people, num_posts, num_cmts) in cur:
subs.append(Subspace(id, name, info, url, flags, owner, ts_created, ts_active,
latest_post_id=latest_post_id,
num_people=num_people,
num_posts=num_posts,
num_cmts=num_cmts))
return subs
def get_moderated_subspace_ids(self, user) -> list:
cur = self.conn.cursor()
cur.execute("SELECT subspace FROM mods WHERE user=?", (user.id,))
subids = []
for (sid,) in cur:
subids.append(sid)
return subids
def get_mods(self, subspace: Union[Subspace, int]):
if type(subspace) is Subspace:
subspace = subspace.id
cur = self.conn.cursor()
cur.execute("""
SELECT
u.id, u.avatar, u.name, UNIX_TIMESTAMP(u.ts_active)
FROM mods m JOIN users u ON u.id=m.user
WHERE m.subspace=?
ORDER BY u.name
""", (subspace,))
mods = []
for (id, avatar, name, ts_active) in cur:
mods.append(User(id, name, None, None, None, None, avatar, None, None, None, None,
None, None, None, None, None, ts_active, None, None, None))
return mods
def modify_mods(self, subspace, actor=None, add=None, remove=None):
cur = self.conn.cursor()
if add:
cur.execute("INSERT INTO mods (subspace, user) VALUES (?, ?)",
(subspace.id, add.id))
if remove:
cur.execute("DELETE FROM mods WHERE subspace=? AND user=?",
(subspace.id, remove.id))
self.commit()
if cur.rowcount > 0:
# Notify the affected (ex-)moderator.
src = actor.id if actor else 0
dst = add.id if add else remove.id
if src != dst:
cur.execute("INSERT IGNORE INTO notifs (type, src, dst, subspace) VALUES (?, ?, ?, ?)",
(Notification.ADDED_AS_MODERATOR if add
else Notification.REMOVED_AS_MODERATOR,
src,
dst,
subspace.id))
self.commit()
if add:
# Moderators should automatically follow their subspaces.
self.modify_follows(add, True, FOLLOW_SUBSPACE, subspace.id)
NUM_CMTS_QUERY = """
UPDATE posts
SET num_cmts=(SELECT COUNT(id) FROM posts WHERE parent=?),
num_people=(SELECT COUNT(DISTINCT user) FROM posts WHERE parent=?)
WHERE id=?
"""
def create_post(self, user: User, subspace_id: int, parent=0, title=''):
assert type(user) is User
# Unmoderated subspaces become locked.
subspace = self.get_subspace(id=subspace_id)
if subspace.owner == 0 and len(self.get_mods(subspace_id)) == 0 \
and user.role != User.ADMIN:
raise GeminiError(61, "Subspace is locked")
if user.role == User.LIMITED and subspace.owner != user.id:
return GeminiError(61, "Not authorized")
flags = 0
if subspace.flags & Subspace.OMIT_FROM_FEED_BY_DEFAULT:
flags = flags | Post.OMIT_FROM_FEED_FLAG
if user.role == User.LIMITED:
flags |= Post.OMIT_FROM_ALL_FLAG | Post.OMIT_FROM_FEED_FLAG
cur = self.conn.cursor()
cur.execute("""
INSERT INTO posts (subspace, parent, user, title, flags)
VALUES (?, ?, ?, ?, ?)
""", (subspace_id, parent, user.id, title, flags))
self.commit()
post_id = cur.lastrowid
self.update_user(user, active=True)
return post_id
def destroy_post(self, post):
cur = self.conn.cursor()
cur.execute('DELETE FROM files WHERE segment IN (SELECT id FROM segments WHERE post=?)',
(post.id,))
cur.execute('DELETE FROM segments WHERE post=?', (post.id,))
cur.execute('DELETE FROM crossrefs WHERE post=? OR dst_post=?', (post.id, post.id))
cur.execute('DELETE FROM tags WHERE post=?', (post.id,))
cur.execute('DELETE FROM follow WHERE type=? AND target=?', (FOLLOW_POST, post.id))
cur.execute('DELETE FROM notifs WHERE post=?', (post.id,))
cur.execute('DELETE FROM likes WHERE post=?', (post.id,))
cur.execute('DELETE FROM votes WHERE post=?', (post.id,))
cur.execute("DELETE FROM reactions WHERE post=?", (post.id,))
cur.execute('DELETE FROM posts WHERE id=?', (post.id,))
# NOTE: Comments on the destroyed post are kept as orphans.
if post.parent:
cur.execute(Database.NUM_CMTS_QUERY, (post.parent, post.parent, post.parent))
self.commit()
def update_post_summary(self, post):
subspace = self.get_subspace(id=post.subspace)
# Render a concise version of the segments to be displayed in feeds,
# save in the `render` field.
segments = self.get_segments(post)
render = ''
# Use only the first link/attachment.
if not post.flags & Post.DISABLE_FEATURED_LINK_FLAG:
for seg in filter(lambda s: s.type in [Segment.LINK, Segment.ATTACHMENT], segments):
# No web URLs in the feeds.
if seg.url.lower().startswith('http'):
continue
seg_content = seg.content.strip()
if len(seg_content) == 0:
seg_content = seg.url
if seg_content.startswith('gemini://'):
seg_content = seg_content[9:] # Omit default scheme.
render += f'=> {seg.url} {INNER_LINK_PREFIX}{seg_content}\n'
break
if len(post.title) and not (subspace.flags & Subspace.ISSUE_TRACKER):
render += post.title
with_title = True
else:
with_title = False
first = True
for text in filter(lambda s: s.type == Segment.TEXT, segments):
str = strip_links(clean_title(text.content))
if len(str) == 0: continue
if with_title and first:
# Separate title from the body text.
render += ' — '
first = False
render += str + ' '
if len(render) > self.max_summary + 10: # with some buffer for cleaner shortening
break
# Cannot have hashes in the beginning (never a Gemtext header).
while len(render) > 0 and render[0] == '#':
render = render[1:].lstrip()
render = shorten_text(render, self.max_summary)
for seg in filter(lambda s: s.type == Segment.IMAGE, segments):
render += f'\n=> {seg.url} {seg.content}\n'
break
if len(render) and not render.endswith('\n'):
render += '\n'
post.summary = render
cur = self.conn.cursor()
cur.execute("""
UPDATE posts
SET summary=?, ts_edited=CURRENT_TIMESTAMP()
WHERE id=?
""", (render, post.id))
self.commit()
def unpublish_post(self, post):
if post.is_draft:
return
if post.num_cmts > 0:
raise GeminiError(50, "Cannot unpublish a post that has comments")
post.is_draft = True
cur = self.conn.cursor()
cur.execute("""
UPDATE posts
SET
is_draft=TRUE,
ts_edited=CURRENT_TIMESTAMP()
WHERE id=?""", (post.id,))
self.commit()
# Undo notifications because drafts don't publicly exist.
cur.execute("DELETE FROM notifs WHERE post=? OR comment=?", (post.id, post.id))
def notify_followed_about_post(self, post: Post):
if not post.parent:
user = self.get_user(id=post.user)
self.notify_followers(user, post.id,
Notification.POST_IN_FOLLOWED_SUBSPACE,
FOLLOW_SUBSPACE, post.subspace)
self.notify_followers(user, post.id,
Notification.POST_BY_FOLLOWED_USER,
FOLLOW_USER, user.id)
def publish_post(self, post):
if post.is_draft:
cur = self.conn.cursor()
cur.execute("""
UPDATE posts
SET
is_draft=FALSE,
ts_created=CURRENT_TIMESTAMP(),
ts_edited=CURRENT_TIMESTAMP()
WHERE id=?""", (post.id,))
self.commit()
# Notify about a new poll.
if Post.TAG_POLL in self.get_tags(post):
self.notify_new_poll(post)
# Notify about the new published post (but not a comment).
user = self.get_user(id=post.user)
if not post.parent:
self.notify_followed_about_post(post)
else:
cur.execute(Database.NUM_CMTS_QUERY, (post.parent, post.parent, post.parent))
cur.execute("UPDATE posts SET ts_comment=CURRENT_TIMESTAMP() WHERE id=?",
(post.parent,))
self.notify_commenters(post)
# Notify the author of the parent post. We can do it here because comments are
# never drafted. They become published immediately after creation.
parent_post = self.get_post(id=post.parent)
self.notify_followers(user, parent_post.id,
Notification.COMMENT_IN_FOLLOWED_SUBSPACE,
FOLLOW_SUBSPACE, parent_post.subspace, comment_id=post.id)
self.notify_followers(user, parent_post.id,
Notification.COMMENT_BY_FOLLOWED_USER,
FOLLOW_USER, user.id, comment_id=post.id)
self.notify_followers(user, parent_post.id,
Notification.COMMENT_ON_FOLLOWED_POST,
FOLLOW_POST, parent_post.id, comment_id=post.id)
if parent_post.user != user.id:
# Notify post author of a new comment.
cur.execute("INSERT IGNORE INTO notifs (type, dst, src, post, comment) "
"VALUES (?, ?, ?, ?, ?)",
(Notification.COMMENT, parent_post.user, user.id, parent_post.id, post.id))
self.commit()
all_text = []
for segment in self.get_segments(post):
if segment.type == Segment.TEXT:
all_text.append(segment.content)
# Notify mentioned users.
self.notify_mentioned(post, ' '.join(all_text))
if self.get_subspace(post.subspace).flags & Subspace.ISSUE_TRACKER:
if post.issueid is None and post.parent == 0:
# Time to assign a new issue number.
cur.execute("""
UPDATE posts
SET issueid=(SELECT nextissueid FROM subspaces WHERE id=?)
WHERE id=?
""", (post.subspace, post.id))
cur.execute("UPDATE subspaces SET nextissueid=nextissueid+1 WHERE id=?",
(post.subspace,))
self.commit()
# Update all crossrefrences found in text segments (posts and comments).
for segment in self.get_segments(post):
self.update_segment_crossrefs(segment)
self.update_user(post.user, active=True)
self.update_subspace(post.subspace, active=True)
def update_poll_tag(self, post):
cur = self.conn.cursor()
# If the post has a poll, automatically tag it with #poll.
cur.execute("SELECT id FROM segments WHERE type=? AND post=?",
(Segment.POLL, post.id))
is_poll = cur.rowcount > 0
self.modify_tags(post, Post.TAG_POLL, None, add=is_poll)
return is_poll
def create_segment(self, post: Post, type, content=None, url=None) -> int:
cur = self.conn.cursor()
cur.execute("""
INSERT INTO segments (post, type, content, url)
VALUES (?, ?, ?, ?)
""", (post.id, type, content, url))
self.commit()
seg_id = cur.lastrowid
cur.execute("""
UPDATE segments
SET pos=(SELECT MAX(pos) FROM segments WHERE post=?) + 1
WHERE id=?
""", (post.id, seg_id,))
self.commit()
if content:
# References to other issues.
self.update_segment_crossrefs(self.get_segment(seg_id))
if not post.is_draft:
self.update_post_summary(post)
if content:
self.notify_mentioned(post, content)
if type == Segment.POLL:
if self.update_poll_tag(post):
# Polls are notified when publishing, but if the post is already public,
# notify right away.
self.notify_new_poll(post)
elif type == Segment.POLL:
# Drafts get tagged with "poll" immediately. Notifications sent out when published.
self.update_poll_tag(post)
return seg_id
def get_segment(self, id):
cur = self.conn.cursor()
cur.execute("SELECT post, type, pos, content, url, counter FROM segments WHERE id=?",
(id,))
for (post, type, pos, content, url, counter) in cur:
return Segment(id, post, type, pos, content, url, counter)
return None
def update_segment(self, segment, content=None, url=None):
set_stm = []
values = []
if content != None:
set_stm.append('content=?')
values.append(strip_invalid(content))
old_content = segment.content
segment.content = content
if url != None:
set_stm.append('url=?')
values.append(url)
if set_stm:
cur = self.conn.cursor()
values.append(segment.id)
cur.execute(f"UPDATE segments SET {','.join(set_stm)} WHERE id=?", tuple(values))
self.commit()
post = self.get_post(id=segment.post)
if not post.is_draft:
self.update_post_summary(post)
if content:
self.update_segment_crossrefs(segment)
self.notify_mentioned(post, content, old_content)
def get_segments(self, post, poll=None):
cur = self.conn.cursor()
cond = ['post=?']
values = [post.id]
if poll != None:
cond.append(f'type={Segment.POLL}' if poll else f'type!={Segment.POLL}')
cur.execute(f"""
SELECT id, type, pos, content, url, counter
FROM segments
WHERE {' AND '.join(cond)}
ORDER BY pos
""", values)
segments = []
for (id, type, pos, content, url, counter) in cur:
segments.append(Segment(id, post.id, type, pos, content, url, counter))
return segments
def get_segments_of_similar_type(self, post, is_poll):
return list(filter(lambda s:
(is_poll and s.type == Segment.POLL or
not is_poll and s.type != Segment.POLL),
self.get_segments(post)))
def get_segment_position_as_index(self, post, segment):
"""Gets the position of the segment as displayed in the composer (0-based)."""
pos = 0
for seg in self.get_segments_of_similar_type(post, segment.type == Segment.POLL):
if seg.id == segment.id:
return pos
pos += 1
return None
def destroy_segment(self, segment):
cur = self.conn.cursor()
cur.execute('DELETE FROM files WHERE segment=?', (segment.id,))
cur.execute('DELETE FROM segments WHERE id=?', (segment.id,))
cur.execute('DELETE FROM crossrefs WHERE segment=?', (segment.id,))
self.commit()
post = self.get_post(id=segment.post)
if not post.is_draft:
self.update_post_summary(post)
if segment.type == Segment.POLL:
self.update_poll_tag(post)
self.unpublish_if_empty(post)
def unpublish_if_empty(self, post):
if not post.is_draft and not post.title and post.num_cmts == 0 and \
len(self.get_segments(post)) == 0:
self.unpublish_post(post)
def update_segment_crossrefs(self, segment: Segment):
if segment.type == Segment.TEXT:
# The segment may be in a comment or in a top-level post.
seg_post = self.get_post(id=segment.post)
parent_post = self.get_post(id=seg_post.parent) if seg_post.parent else None
issue_post = parent_post if parent_post else seg_post
if not issue_post.issueid:
return
cur = self.conn.cursor()
cur.execute("DELETE FROM crossrefs WHERE segment=?", (segment.id,))
self.commit()
for dst_issueid in map(int, re.findall(r'(?<!#)#(\d+)', segment.content)):
if dst_issueid == issue_post.issueid:
continue
cur.execute(f"""
INSERT IGNORE INTO crossrefs
(subspace, post, segment, issueid, dst_post, dst_issueid, ts)
SELECT
{issue_post.subspace}, {issue_post.id}, {segment.id},
{issue_post.issueid}, id, {dst_issueid}, ?
FROM posts
WHERE issueid=?
""", (datetime.datetime.fromtimestamp(seg_post.ts_created),
dst_issueid))
self.commit()
def get_issue_crossrefs(self, subspace: Subspace,
incoming_to_issueid=None, outgoing_from_issueid=None):
"""Looks for cross-references originating from `issueid` (a dict of
post IDs -> Posts) or cross-references targeting an issue ID (list of Posts)."""
cond = ['p.subspace=?']
values = [subspace.id]
if outgoing_from_issueid:
cond.append('x.issueid=?')
values.append(outgoing_from_issueid)
else:
cond.append('x.dst_issueid=?')
values.append(incoming_to_issueid)
cond.append('p.is_draft=FALSE') # Not incoming from drafts.
cur = self.conn.cursor()
q = f"""
SELECT
p.id, seg.post, p.user, p.issueid, p.title, p.tags,
UNIX_TIMESTAMP(x.ts), s.name
FROM crossrefs x
JOIN posts p ON {"p.id=x.dst_post" if outgoing_from_issueid else "p.id=x.post"}
JOIN segments seg ON x.segment=seg.id
JOIN subspaces s ON p.subspace=s.id
WHERE {' AND '.join(cond)}
ORDER BY x.ts
"""
cur.execute(q, values)
xrefs = [] if incoming_to_issueid else {}
for (id, segpost_id, user_id, xissueid, title, tags, ts_created, sub_name) in cur:
x = Crossref(id, subspace.id, user_id, xissueid, title, tags,
ts_created, sub_name)
if incoming_to_issueid:
xrefs.append(x)
else:
if x.id not in xrefs:
xrefs[segpost_id] = [x]
else:
xrefs[segpost_id].append(x)
return xrefs
def move_segment(self, post, moved_segment, new_pos):
is_poll = (moved_segment.type == Segment.POLL)
# Poll options are segments but are handled in a separate listing.
segments = list(filter(lambda s: s.id != moved_segment.id,
self.get_segments_of_similar_type(post, is_poll)))
segments = segments[:new_pos] + [moved_segment] + segments[new_pos:]
cur = self.conn.cursor()
pos = 0
for seg in segments:
cur.execute('UPDATE segments SET pos=? WHERE id=?', (pos, seg.id))
pos += 1
self.commit()
if not post.is_draft:
self.update_post_summary(post)
def modify_vote(self, user: User, segment: Segment):
cur = self.conn.cursor()
# Remove the old vote.
cur.execute("SELECT segment FROM votes WHERE user=? AND post=?",
(user.id, segment.post))
for (old_id,) in cur:
cur.execute("UPDATE segments SET counter=counter-1 WHERE id=?", (old_id,))
cur.execute("DELETE FROM votes WHERE user=? AND post=?",
(user.id, segment.post))
self.commit()
break
# Apply the new vote.
cur.execute("INSERT INTO votes (user, segment, post) VALUES (?, ?, ?)",
(user.id, segment.id, segment.post))
cur.execute("UPDATE segments SET counter=counter+1 WHERE id=?",
(segment.id,))
# Automatically follow the vote post.
post = self.get_post(id=segment.post)
if user.id != post.user:
self.modify_follows(user, True, FOLLOW_POST, segment.post)
self.commit()
def get_vote(self, user: User, post: Post) -> Segment:
cur = self.conn.cursor()
cur.execute("SELECT segment FROM votes WHERE user=? AND post=?",
(user.id, post.id))
for (seg,) in cur:
return self.get_segment(id=seg)
return None
# These joins are used with
FOLLOW_FILTER_JOIN = f"""
JOIN follow fol
ON fol.user=? AND
((fol.type={FOLLOW_POST} AND fol.target=p.id) OR
(fol.type={FOLLOW_SUBSPACE} AND fol.target=p.subspace) OR
(fol.type={FOLLOW_USER} AND fol.target=p.user))
"""
MUTE_ALL_COND_JOIN = f"""
LEFT JOIN mute mt
ON mt.user=? AND
mt.user!=p.user AND
((mt.type={MUTE_SUBSPACE} AND mt.target=p.subspace) OR
(mt.type={MUTE_USER} AND mt.target=p.user))
"""
MUTE_SUBSPACE_COND_JOIN = f"""
LEFT JOIN mute mt
ON mt.user=? AND
mt.user!=p.user AND
(mt.type={MUTE_USER} AND mt.target=p.user)
"""
TAG_FILTER_JOIN = "JOIN tags tg ON tg.post=p.id"
def get_posts(self,
id=None,
subspace=None,
comment=None,
user=None,
draft=None,
parent=None,
filter_by_followed=None,
filter_issue_status=None,
filter_tag=None,
omit_user_subspaces=False,
omit_nonuser_subspaces=False,
rotation_group_of_post:Post=None,
rotate_per_day=False,
gemini_feed=False,
notifs_for_user_id=0,
muted_by_user_id=0,
sort=Post.SORT_CREATED,
sort_descending=True,
sort_by_subspace=False,
sort_by_post=False,
ts_range=None,
limit=None,
page=0):
if type(subspace) is Subspace:
subspace = subspace.id
cur = self.conn.cursor()
where_stm = []
values = [notifs_for_user_id]
filter = ''
if filter_by_followed:
filter = Database.FOLLOW_FILTER_JOIN
values.append(filter_by_followed.id)
if muted_by_user_id:
filter += Database.MUTE_ALL_COND_JOIN if subspace is None \
else Database.MUTE_SUBSPACE_COND_JOIN
values.append(muted_by_user_id)
if filter_tag:
filter += Database.TAG_FILTER_JOIN
where_stm.append('tg.tag=?')
values.append(filter_tag)
if comment != None:
where_stm.append('p.parent!=0' if comment else 'p.parent=0')
if parent != None:
where_stm.append('p.parent=?')
values.append(parent)
if id != None:
where_stm.append('p.id=?')
values.append(id)
if user != None:
where_stm.append('p.user=?')
values.append(user.id)
if omit_user_subspaces:
where_stm.append('sub1.owner=0')
if omit_nonuser_subspaces:
where_stm.append('sub1.owner!=0')
if subspace != None:
where_stm.append('p.subspace=?')
values.append(subspace)
PIN_ORDER = 'is_pinned DESC, '
elif ts_range != None:
where_stm.append('(UNIX_TIMESTAMP(p.ts_edited)>=? AND UNIX_TIMESTAMP(p.ts_edited)<?)')
values.append(ts_range[0])
values.append(ts_range[1])
PIN_ORDER = ''
else:
if id is None and user is None and parent is None:
where_stm.append(f'((sub1.flags & {Subspace.OMIT_FROM_ALL_FLAG})=0 AND (p.flags & {Post.OMIT_FROM_ALL_FLAG})=0)')
PIN_ORDER = 'is_pinned=2 DESC, '
if draft != None:
where_stm.append('p.is_draft=?')
values.append(draft)
if filter_issue_status != None:
where_stm.append('p.tags NOT LIKE ?' if filter_issue_status else \
'p.tags LIKE ?')
values.append('%✔︎%')
if muted_by_user_id:
where_stm.append('mt.user IS NULL')
if gemini_feed:
where_stm.append(f'(p.flags & {Post.OMIT_FROM_FEED_FLAG})=0')
if rotation_group_of_post:
where_stm.append(f'(p.subspace={rotation_group_of_post.subspace} AND DATE(FROM_UNIXTIME({rotation_group_of_post.ts_created}))=DATE(p.ts_created))')
if sort == Post.SORT_ACTIVE:
order_by = "p.ts_comment " + ('DESC' if sort_descending else 'ASC')
elif sort == Post.SORT_HOTNESS:
order_by = """(
(10.0 + num_people * 200.0 + num_likes * 50.0) /
((UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) - ts_comment + 1800.0) / 3600.0)
) DESC"""
else:
order_by = "ts_created " + ('DESC' if sort_descending else 'ASC')
if sort_by_post:
order_by = f"id {'DESC' if sort_descending else 'ASC'}, " + order_by
if sort_by_subspace:
order_by = "sub_name, " + order_by
if limit:
limit_expr = 'LIMIT ? OFFSET ?'
values.append(limit) # number of results
values.append(page * limit) # first result
else:
limit_expr = ''
visible_posts_query = f"""
SELECT
p.id,
p.subspace,
p.parent,
p.user,
p.issueid,
p.title,
p.flags,
p.is_draft,
p.is_pinned,
p.num_cmts,
p.num_likes,
p.tags,
UNIX_TIMESTAMP(p.ts_created) AS ts_created,
UNIX_TIMESTAMP(p.ts_edited) AS ts_edited,
UNIX_TIMESTAMP(p.ts_comment) AS ts_comment,
p.summary,
sub1.name AS sub_name,
sub1.owner, -- sub2.owner,
u.avatar,
u.name AS u_name,
u.flair,
(SELECT COUNT(notifs.id)
FROM notifs
WHERE notifs.dst=? AND notifs.post=p.id AND NOT notifs.is_hidden
) AS num_notif,
p.num_people,
DATE(p.ts_created) AS date_created
FROM posts p
JOIN users u ON p.user=u.id
JOIN subspaces sub1 ON p.subspace=sub1.id
-- LEFT JOIN subspaces sub2 ON p.subspace=sub2.id AND p.user=sub2.owner
{filter}
WHERE {' AND '.join(where_stm)}
"""
if rotate_per_day:
# Each subspace shows a single post per day, but the visible one rotates as time passes.
now = datetime.datetime.now()
rotator = int((now.hour * 60 + now.minute) / 15)
cur.execute(f"""
WITH visible_posts AS (
{visible_posts_query}
), grouped_posts AS (
SELECT
p.*,
ROW_NUMBER() OVER (
PARTITION BY p.subspace, p.date_created
ORDER BY p.subspace, p.ts_created
) AS row_num
FROM visible_posts p
)
SELECT
p.*,
gr.num_per_day
FROM
grouped_posts p
JOIN (
SELECT
subspace,
date_created,
COUNT(*) AS num_per_day
FROM grouped_posts
GROUP BY subspace, date_created
) gr ON gr.subspace=p.subspace AND gr.date_created=p.date_created
WHERE (p.row_num+{rotator})%gr.num_per_day=0
ORDER BY {PIN_ORDER}{order_by}
{limit_expr}
""", tuple(values))
else:
cur.execute(f"""
WITH visible_posts AS ({visible_posts_query})
SELECT
p.*,
0, -- row_num
NULL -- num_per_day
FROM
visible_posts p
ORDER BY {PIN_ORDER}{order_by}
{limit_expr}
""", tuple(values))
posts = []
for (id, subspace, parent, user, issueid, title, flags, is_draft, is_pinned,
num_cmts, num_likes, tags, ts_created, ts_edited, ts_comment,
summary, sub_name, sub_owner, poster_avatar, poster_name, flair, num_notifs,
_num_people, _date_created, _row_num, num_per_day) in cur:
posts.append(Post(id, subspace, parent, user, issueid,
title,
flags,
is_draft, is_pinned,
num_cmts, num_likes,
tags,
ts_created, ts_edited, summary,
ts_comment=ts_comment,
sub_name=sub_name,
sub_owner=sub_owner,
poster_avatar=poster_avatar,
poster_name=poster_name,
poster_flair=flair,
num_notifs=num_notifs,
num_per_day=num_per_day))
return posts
def get_post(self, id, draft=None):
posts = self.get_posts(id=id, draft=draft)
if len(posts) > 0:
return posts[0]
return None
def get_post_for_issueid(self, subspace: Subspace, issueid):
cur = self.conn.cursor()
cur.execute("SELECT id FROM posts WHERE issueid=? AND subspace=?",
(issueid, subspace.id))
for (post_id,) in cur:
return self.get_post(id=post_id)
return None
def is_empty_subspace(self, subspace):
return self.count_posts(subspace=subspace) == 0
def count_posts(self,
user=None,
subspace=None,
parent_id=None,
draft=False,
is_comment=False,
ignore_omit_flags=False,
omit_user_subspaces=False,
omit_nonuser_subspaces=False,
filter_by_followed=None,
filter_issue_status=None,
filter_tag=None,
muted_by_user_id=0,
rotate_per_day=False):
cond = []
values = []
grouping = ''
filter = ''
if is_comment != None:
cond.append('p.parent!=0' if is_comment else 'p.parent=0')
# elif not parent_id and not draft:
# cond.append('p.parent=0') # no comments
if filter_by_followed:
filter = Database.FOLLOW_FILTER_JOIN
values.append(filter_by_followed.id)
if muted_by_user_id:
filter += Database.MUTE_ALL_COND_JOIN if subspace is None \
else Database.MUTE_SUBSPACE_COND_JOIN
values.append(muted_by_user_id)
cond.append('mt.user IS NULL')
if filter_tag:
filter += Database.TAG_FILTER_JOIN
cond.append('tg.tag=?')
values.append(filter_tag)
if omit_user_subspaces:
cond.append('s.owner=0')
if omit_nonuser_subspaces:
cond.append('s.owner!=0')
if user != None:
cond.append('p.user=?')
values.append(user.id)
if parent_id != None and not draft:
cond.append('p.parent=?')
values.append(parent_id)
if subspace != None:
cond.append('p.subspace=?')
values.append(subspace.id)
elif not draft and not ignore_omit_flags:
# Need filter out posts from subspaces that are flagged for omission.
cond.append(f'((s.flags & {Subspace.OMIT_FROM_ALL_FLAG})=0 AND (p.flags & {Post.OMIT_FROM_ALL_FLAG})=0)')
if filter_issue_status != None:
cond.append('p.tags NOT LIKE ?' if filter_issue_status else \
'p.tags LIKE ?')
values.append('%✔︎%')
cond.append('p.is_draft=?')
values.append(draft)
if not rotate_per_day:
q = f"""
SELECT
COUNT(p.id)
FROM posts p
JOIN subspaces s ON p.subspace=s.id
{filter}
WHERE {' AND '.join(cond)}
"""
else:
q = f"""
SELECT
COUNT(*)
FROM (
SELECT
p.subspace, DATE(p.ts_created)
FROM posts p
JOIN subspaces s ON p.subspace=s.id
{filter}
WHERE {' AND '.join(cond)}
GROUP BY p.subspace, DATE(p.ts_created)
) AS per_day_counts
"""
cur = self.conn.cursor()
cur.execute(q, values)
for (count,) in cur:
return count
return 0
def unset_post_omit_flags(self, user: User):
# This is used when a Limited user is elevated to a Basic role.
# The forced omit flags are cleared.
cur = self.conn.cursor()
cur.execute("UPDATE posts SET flags=0 WHERE user=?", (user.id,))
self.commit()
def batch_delete_limited_users(self, max_age, max_post_count=1):
for user in self.get_users(role=User.LIMITED, max_age=max_age, avatar='🚀',
empty_info=True,
max_post_count=max_post_count):
self.destroy_user(user)
def update_post(self, post, title=None, flags=None, subspace_id=None):
set_stm = []
values = []
if title != None:
set_stm.append('title=?')
values.append(title)
post.title = title
if flags != None:
set_stm.append('flags=?')
values.append(flags)
post.flags = flags
if subspace_id != None:
set_stm.append('subspace=?')
values.append(subspace_id)
post.subspace = subspace_id
if set_stm:
cur = self.conn.cursor()
values.append(post.id)
cur.execute(f"UPDATE posts SET {','.join(set_stm)} WHERE id=?", tuple(values))
self.commit()
if title != None and not post.is_draft:
self.update_post_summary(post)
self.unpublish_if_empty(post)
def move_comments(self, post: Post, oldsub_id, newsub_id):
cur = self.conn.cursor()
cur.execute("UPDATE posts SET subspace=? WHERE subspace=? AND parent=?",
(newsub_id, oldsub_id, post.id))
self.commit()
def notify_mentioned(self, post, content, old_content=None):
names = parse_at_names(content)
post_user = self.get_user(id=post.user)
if post_user.role == User.LIMITED:
# Limited users can only mention @admin.
names = list(filter(lambda n: n == 'admin', names))
if old_content:
# Only notify newly added @-names.
for old in parse_at_names(old_content):
if old in names:
names.remove(old)
if names:
cur = self.conn.cursor()
notif_post_id = post.parent if post.parent else post.id
where_names_cond = f"name IN ({('?,' * len(names))[:-1]})"
cur.execute(f"""
INSERT IGNORE INTO notifs (type, dst, src, post, comment)
SELECT
{Notification.MENTION}, id, {post.user}, {notif_post_id}, {post.id if post.parent else "NULL"}
FROM users
WHERE id!={post.user} AND {where_names_cond}
""", names)
self.commit()
def notify_commenters(self, new_comment):
"""When `new_comment` is posted, notify other participants of the same thread about it."""
cur = self.conn.cursor()
cur.execute("""
SELECT DISTINCT user
FROM posts
WHERE is_draft=FALSE AND parent=? AND user!=? AND
user!=(SELECT user FROM posts WHERE id=?)
""", (new_comment.parent, new_comment.user, new_comment.parent))
uids = []
for (i,) in cur: uids.append(i)
for uid in uids:
cur.execute("""
INSERT IGNORE INTO notifs (type, dst, src, post, comment)
VALUES (?, ?, ?, ?, ?)
""", (Notification.COMMENT_ON_COMMENTED, uid, new_comment.user, new_comment.parent,
new_comment.id))
if uids:
self.commit()
def notify_followers(self, actor: User, post_id, notif_type, follow_type, target_id,
comment_id=None):
cur = self.conn.cursor()
cur.execute(f"""
INSERT IGNORE INTO notifs (type, dst, src, post, comment)
SELECT
{notif_type},
user,
{actor.id},
{post_id},
?
FROM follow
WHERE type={follow_type} AND target={target_id}
""", (comment_id,))
self.commit()
def notify_moderators(self, notif_type, user:User, post:Post=None, subspace:Subspace=None):
if notif_type == Notification.REPORT and post.user == user.id:
# No self-reporting.
return
cur = self.conn.cursor()
# Always notify the administrator.
if user.id != 1:
cur.execute("""
INSERT IGNORE INTO notifs (type, dst, src, post)
VALUES (?, 1, ?, ?)
""", (notif_type, user.id, post.id if post else None))
if not subspace and post:
subspace = self.get_subspace(id=post.subspace)
if subspace:
# Notify each moderator of the post's subspace.
cur.execute("""
INSERT IGNORE INTO notifs (type, dst, src, post)
SELECT ?, user, ?, ?
FROM mods
WHERE subspace=? AND NOT user IN (?, ?, ?)
""", (notif_type, user.id, post.id if post else None,
subspace.id,
user.id, post.user if post else user.id, subspace.owner))
self.commit()
def notify_reminder(self, user: User, post: Post):
# Enable the notification automatically. Old users may not have the flag set.
if not (user.notif & Notification.REMINDER):
self.update_user(user, notif=user.notif | Notification.REMINDER)
cur = self.conn.cursor()
cur.execute("""
INSERT IGNORE INTO notifs (type, dst, src, post)
VALUES (?, ?, 1, ?)
""", (Notification.REMINDER, user.id, post.id))
self.commit()
def notify_thanks(self, user: User, post: Post):
cur = self.conn.cursor()
ntype = Notification.THANKS
cur.execute(f"""
IF (SELECT COUNT(id)=0
FROM notifs
WHERE is_hidden=FALSE AND type={ntype} AND src=? AND dst=? AND post=?)
THEN
INSERT IGNORE INTO notifs (type, src, dst, post) VALUES ({ntype}, ?, ?, ?);
END IF
""", (user.id, post.user, post.id,
user.id, post.user, post.id))
self.update_user(user, active=True)
self.commit()
def notify_new_poll(self, post: Post):
# If this subspace or post is omitted from All Posts, don't notify anyone.
if post.flags & Post.OMIT_FROM_ALL_FLAG:
return
subspace = self.get_subspace(id=post.subspace)
if subspace.flags & Subspace.OMIT_FROM_ALL_FLAG:
return
cur = self.conn.cursor()
cur.execute(f"""
INSERT IGNORE INTO notifs (type, dst, src, post)
SELECT
{Notification.NEW_POLL}, id, ?, ?
FROM users
WHERE (notif & {Notification.NEW_POLL})!=0 AND id!=?
""", (post.user, post.id, post.user))
self.commit()
def notify_admin(self, notif_type, user_id, subspace_id=None):
cur = self.conn.cursor()
cur.execute(f"""
INSERT IGNORE INTO notifs (type, dst, src, subspace)
VALUES (?, 1, ?, ?)
""", (notif_type, user_id, subspace_id))
self.commit()
def notify_role(self, user: User):
cur = self.conn.cursor()
cur.execute(f"""
INSERT IGNORE INTO notifs (type, dst, src)
VALUES (?, ?, 1)
""", (Notification.ROLE_CHANGED, user.id))
self.commit()
def count_notifications(self, user):
return len(self.get_notifications(user, expire=False))
def modify_likes(self, user, post, add=True):
cur = self.conn.cursor()
if add:
cur.execute("INSERT INTO likes (user, post) VALUES (?, ?)", (user.id, post.id))
if user.id != post.user:
cur.execute("INSERT IGNORE INTO notifs (type, dst, src, post) VALUES (?, ?, ?, ?)",
(Notification.LIKE, post.user, user.id, post.id))
self.update_user(user, active=True)
else:
cur.execute("DELETE FROM likes WHERE user=? AND post=?", (user.id, post.id))
cur.execute("""
UPDATE posts
SET num_likes=(SELECT COUNT(user) FROM likes WHERE post=?)
WHERE id=?
""", (post.id, post.id))
self.commit()
def modify_reactions(self, user: User, post: Post, reaction: str):
"""If `reaction` is None, it existing reaction will be removed."""
cur = self.conn.cursor()
cur.execute("DELETE FROM reactions WHERE post=? AND user=?", (post.id, user.id))
if reaction:
cur.execute("INSERT IGNORE INTO reactions (post, user, reaction) VALUES (?, ?, ?)",
(post.id, user.id, reaction))
cur.execute("INSERT IGNORE INTO notifs (type, src, dst, post) VALUES (?, ?, ?, ?)",
(Notification.REACTION, user.id, post.user, post.id))
self.update_user(user, active=True)
self.commit()
def get_reactions(self, post, user_mutes=set()) -> dict:
"""Returns a dictionary of {reactions: count}."""
cur = self.conn.cursor()
cur.execute("SELECT reaction, user FROM reactions WHERE post=?", (post.id,))
counts = {}
for (r, user_id) in cur:
if (MUTE_USER, user_id) in user_mutes:
continue
if r not in counts:
counts[r] = 1
else:
counts[r] += 1
return counts
def get_user_reaction(self, post: Post, user_id: int):
cur = self.conn.cursor()
cur.execute("SELECT reaction FROM reactions WHERE post=? AND user=?", (post.id, user_id))
for (r,) in cur: return r
return None
def get_likes(self, post, user_mutes=set()):
cur = self.conn.cursor()
cur.execute("""
SELECT u.name, u.id
FROM likes
JOIN users u ON likes.user=u.id
WHERE likes.post=?
""", (post.id,))
users = []
for (name, liker_id) in cur:
if not (MUTE_USER, liker_id) in user_mutes:
users.append(name)
return users
def get_tags(self, post):
cur = self.conn.cursor()
cur.execute("SELECT DISTINCT tag FROM tags WHERE post=? ORDER BY tag", (post.id,))
tags = []
for (tag,) in cur:
tags.append(tag)
return tags
def get_popular_tags(self, subspace: Subspace):
cur = self.conn.cursor()
if subspace:
cond = 'WHERE s.id=?'
values = [subspace.id]
else:
cond = ''
values = tuple()
subspace_cond = ''
if not subspace:
# Issue tracker tags should not be included in the All Posts tags.
subspace_cond = f' AND (s.flags & {Subspace.ISSUE_TRACKER})=0'
cur.execute(f"""
SELECT t.tag, COUNT(t.tag)
FROM tags t
JOIN posts p ON p.id=t.post
JOIN subspaces s ON s.id=p.subspace {subspace_cond}
{cond}
GROUP BY t.tag
ORDER BY COUNT(t.tag) DESC
""", values)
popular = []
for (tag, _) in cur:
popular.append(tag)
return popular
def modify_tags(self, post, tag, actor: User, add=True):
was_changed = False
cur = self.conn.cursor()
if add:
cur.execute("INSERT IGNORE INTO tags (post, tag) VALUES (?, ?)",
(post.id, tag))
was_changed = cur.rowcount > 0
else:
cur.execute("DELETE FROM tags WHERE post=? AND tag=?", (post.id, tag))
was_changed = cur.rowcount > 0
# Notify post author and followers of the closing.
if add and was_changed and tag == Post.TAG_CLOSED and actor:
if post.user != actor.id:
cur.execute("INSERT IGNORE INTO notifs (type, dst, src, post) "
"VALUES (?, ?, ?, ?)",
(Notification.ISSUE_CLOSED, post.user, actor.id, post.id))
self.notify_followers(actor, post.id, Notification.ISSUE_CLOSED, FOLLOW_POST, post.id)
# Update the cached tagline.
tags = self.get_tags(post)
is_pinned = 0
if Post.TAG_PINNED in tags:
is_pinned = 1
elif Post.TAG_ANNOUNCEMENT in tags:
is_pinned = 2
def format_tag(name):
if name == Post.TAG_PINNED:
return '📌'
elif name == Post.TAG_ANNOUNCEMENT:
return '📣'
elif name == Post.TAG_POLL:
return '🗳️'
elif name == Post.TAG_CLOSED:
return '✔︎'
return '#' + name
cur.execute("UPDATE posts SET tags=?, is_pinned=? WHERE id=?",
(' '.join(map(format_tag, tags)), is_pinned, post.id))
self.commit()
return was_changed
def mark_notifications_sent(self, user):
cur = self.conn.cursor()
cur.execute("UPDATE notifs SET is_sent=TRUE WHERE dst=?", (user.id,))
cur.execute("""UPDATE users
SET ts_email=CURRENT_TIMESTAMP(), ts_active=CURRENT_TIMESTAMP()
WHERE id=?""", (user.id,))
self.commit()
def get_notifications(self, user, id=None, post_id=None, include_hidden=False, clear=False,
only_unsent=False, sort_desc=False, expire=True) -> list:
cond = ['dst=?', '(n.type & ?)!=0']
values = [user.id, user.notif]
if id != None:
cond.append('n.id=?')
values.append(id)
if post_id != None:
cond.append('n.post=?')
values.append(post_id)
if not include_hidden:
cond.append('n.is_hidden=FALSE')
if only_unsent:
cond.append('n.is_sent=FALSE')
cond.append('mt.user IS NULL')
cur = self.conn.cursor()
cur.execute(f"""
SELECT
n.id, n.type, n.dst, n.src, n.post, n.subspace, n.comment, n.is_sent, UNIX_TIMESTAMP(n.ts),
u.name,
p.title, p.issueid, p.summary,
s.id, s.name, s.owner,
s2.name,
r.reaction
FROM notifs n
JOIN users u ON src=u.id
LEFT JOIN posts p ON post=p.id
LEFT JOIN subspaces s ON s.id=p.subspace -- Post subspace
LEFT JOIN subspaces s2 ON s2.id=n.subspace -- Notification subspace (may be null)
LEFT JOIN reactions r ON r.post=n.post AND n.src=r.user
LEFT JOIN mute mt ON mt.user={user.id} AND (
(mt.type={MUTE_USER} AND mt.target=n.src) OR
(mt.type={MUTE_SUBSPACE} AND mt.target=p.subspace AND p.user!={user.id} AND n.type!={Notification.MENTION}) OR
(mt.type={MUTE_POST} AND mt.target=n.post AND n.type!={Notification.MENTION})
)
WHERE {' AND '.join(cond)}
""", tuple(values))
notifs = []
for (id, type, dst, src, post, subspace, comment, is_sent, ts, src_name, post_title, post_issueid,
post_summary, post_subid, post_subname, post_subowner, subname, reaction) in cur:
notifs.append(Notification(id, type, dst, src, post, subspace, comment, is_sent, ts,
src_name, post_title, post_issueid, post_summary,
post_subname, post_subowner, subname, reaction,
post_subid=post_subid))
notifs = self.resolve_notifications(notifs)
notifs.sort(key=lambda n: n.ts, reverse=sort_desc)
if clear and notifs:
cur.execute(f"""
UPDATE notifs SET is_hidden=TRUE
WHERE id IN ({','.join(map(lambda n: str(n.id), notifs))})
""")
# Delete archived notifications after a week, and disabled notifications immediately.
if expire:
cur.execute("""
DELETE FROM notifs
WHERE
(is_hidden=TRUE AND TIMESTAMPDIFF(DAY, ts, CURRENT_TIMESTAMP())>=7)
OR (is_hidden=FALSE AND dst=? AND (type & ?)=0)
""", (user.id, user.notif))
self.commit()
return notifs
def resolve_notifications(self, notifs):
"""Given a list of notifications, apply priority tiers to discard the less important
ones. The discarded notifications are deleted from the database."""
resolved = []
kept_ids = set()
discarded_ids = set()
have_single = {} # Only one of some types are kept.
# Create a table of (post, src) -> [notifs].
indexed = {}
for notif in notifs:
# Some notifications are always kept as-is.
if (notif.type not in (Notification.SUBSPACE_INFO_UPDATED,
Notification.USER_FLAIR_CHANGED)) and (
not notif.post or notif.type in (Notification.ISSUE_CLOSED,
Notification.REMINDER,
Notification.REPORT)):
resolved.append(notif)
kept_ids.add(notif.id)
continue
key = (notif.post, notif.src)
# The priority value -1 means "keep only one".
if notif.type in Notification.PRIORITY and Notification.PRIORITY[notif.type] < 0:
if not notif.type in have_single:
have_single[notif.type] = {key}
elif key in have_single[notif.type]:
continue
else:
have_single[notif.type].add(key)
if key not in indexed:
indexed[key] = [notif]
else:
indexed[key].append(notif)
# Keep the highest priority notification with the earliest timestamp about an event.
# TODO: Could merge multiple types into one for a more elaborate description?
for key in indexed:
top_notif = None
top = -100
for notif in indexed[key]:
prio = Notification.PRIORITY[notif.type]
if prio > top or (prio == top and notif.ts < top_notif.ts):
top = prio
top_notif = notif
if top_notif:
resolved.append(top_notif)
kept_ids.add(top_notif.id)
for notif in notifs:
if notif.id not in kept_ids:
discarded_ids.add(notif.id)
if discarded_ids:
cur = self.conn.cursor()
cur.execute(f"DELETE FROM notifs WHERE id IN ({','.join(map(str, discarded_ids))})")
self.commit()
return resolved
def get_notification(self, user, id, clear=False) -> Notification:
notif = self.get_notifications(user, id=id, include_hidden=True, clear=clear)
if notif:
return notif[0]
return None
TOKEN_CHARS = [chr(ord('a') + i) for i in range(26)] + \
[chr(ord('A') + i) for i in range(26)] + \
[chr(ord('0') + i) for i in range(10)]
def expire_tokens(self):
cur = self.conn.cursor()
cur.execute("""
DELETE FROM tokens
WHERE TIMESTAMPDIFF(MINUTE, ts, CURRENT_TIMESTAMP())>=60
""")
self.commit()
def get_token(self, user: User):
self.expire_tokens()
user_id = user.id if user else 0
# Reuse a valid token within 5 minutes.
cur = self.conn.cursor()
cur.execute("""
SELECT token
FROM tokens
WHERE user=? AND TIMESTAMPDIFF(MINUTE, ts, CURRENT_TIMESTAMP())<5
LIMIT 1
""", (user_id,))
for (token,) in cur:
return token
# Generate a new token.
token = ''.join([Database.TOKEN_CHARS[random.randint(0, len(Database.TOKEN_CHARS) - 1)]
for _ in range(10)])
cur.execute("INSERT INTO tokens (user, token) VALUES (?, ?)", (user_id, token))
self.commit()
return token
def verify_token(self, user: User, token):
self.expire_tokens()
cur = self.conn.cursor()
cur.execute("""
SELECT user
FROM tokens
WHERE user=? AND token=? COLLATE utf8mb4_bin
""", (user.id if user else 0, token))
return cur.rowcount > 0
def create_repository(self, subspace) -> int:
cur = self.conn.cursor()
cur.execute("INSERT INTO repos (subspace) VALUES (?)", (subspace.id,))
self.commit()
return cur.lastrowid
def destroy_repository(self, repo: Repository):
cur = self.conn.cursor()
cur.execute("DELETE FROM repos WHERE subspace=?", (repo.subspace,))
cur.execute("DELETE FROM commits WHERE repo=?", (repo.id,))
cur.execute("DELETE FROM issuerefs WHERE repo=?", (repo.id,))
self.commit()
if self.repo_cachedir:
shutil.rmtree(os.path.join(self.repo_cachedir, str(repo.id)), ignore_errors=True)
def update_repository(self, repo: Repository, clone_url=None, view_url=None, idlabel=None):
cur = self.conn.cursor()
stm = []
values = []
if clone_url:
stm.append('clone_url=?')
values.append(clone_url)
stm.append('ts_fetch=NULL')
if idlabel and idlabel != repo.idlabel:
stm.append('idlabel=?')
values.append(idlabel)
# Need to recheck the issue numbers.
cur.execute("DELETE FROM issuerefs WHERE repo=?", (repo.id,))
stm.append('ts_fetch=NULL')
if view_url:
stm.append('view_url=?')
values.append(view_url)
values.append(repo.id)
cur.execute(f"UPDATE repos SET {','.join(stm)} WHERE id=?", values)
self.commit()
def get_repositories(self, id=None, subspace=None):
cond = []
values = []
if id:
cond.append('id=?')
values.append(id)
if subspace:
cond.append('subspace=?')
values.append(subspace.id)
cur = self.conn.cursor()
cur.execute(f"""
SELECT id, subspace, clone_url, view_url, idlabel, UNIX_TIMESTAMP(ts_fetch)
FROM repos
{"WHERE" if cond else ""} {" AND ".join(cond)}
""", values)
repos = []
for (id, sub_id, clone_url, view_url, idlabel, ts_fetch) in cur:
repos.append(Repository(id, sub_id, clone_url, view_url, idlabel, ts_fetch))
return repos
def get_repository(self, id=None, subspace=None):
repos = self.get_repositories(id=id, subspace=subspace)
return repos[0] if repos else None
def count_commits(self, repo: Repository):
if not repo:
return 0
cur = self.conn.cursor()
cur.execute("SELECT COUNT(*) FROM commits WHERE repo=?", (repo.id,))
return cur.fetchone()[0]
def get_commits(self, repo: Repository, issueid):
if not repo:
return []
cur = self.conn.cursor()
cur.execute("SELECT c.hash, c.msg, UNIX_TIMESTAMP(c.ts) "
"FROM commits c JOIN issuerefs i ON i.repo=c.repo AND i.commit=c.hash "
"WHERE c.repo=? AND i.issue=?",
(repo.id, issueid))
commits = []
for (hash, msg, ts) in cur:
commits.append(Commit(repo.id, hash, msg, ts))
return commits
def find_commits_by_hash(self, repo: Repository, likely_hashes: list):
if not repo or not likely_hashes:
return []
cond = []
values = []
for hash_prefix in likely_hashes:
cond.append('hash LIKE ?')
values.append(hash_prefix + '%')
cur = self.conn.cursor()
cur.execute(f"""
SELECT DISTINCT hash, msg, UNIX_TIMESTAMP(ts)
FROM commits
WHERE {' OR '.join(cond)}
""", values)
commits = []
for (hash, msg, ts) in cur:
commits.append(Commit(repo.id, hash, msg, ts))
return commits
def get_access_rate(self, window_seconds, from_addr, entry_type):
cond = ['type=?']
values = [entry_type]
if from_addr != None:
cond.append('remote=?')
values.append(address_hash(from_addr))
cond.append('TIMESTAMPDIFF(SECOND, ts, CURRENT_TIMESTAMP())<?')
values.append(window_seconds)
cur = self.conn.cursor()
cur.execute(f"SELECT COUNT(*) FROM log WHERE {' AND '.join(cond)}", values)
for (rate,) in cur:
pass
cur.execute("DELETE FROM log WHERE TIMESTAMPDIFF(MINUTE, ts, CURRENT_TIMESTAMP())>=60")
self.commit()
return rate
def add_log_entry(self, from_addr, type):
cur = self.conn.cursor()
cur.execute("INSERT INTO log (remote, type) VALUES (?, ?)", (address_hash(from_addr), type))
self.commit()
def get_statistics(self):
cur = self.conn.cursor()
cur.execute("""SELECT
(SELECT COUNT(id) FROM users) AS total,
(SELECT COUNT(id) FROM users WHERE TIMESTAMPDIFF(DAY, ts_active, CURRENT_TIMESTAMP()) <= 30) AS m_visited,
(SELECT COUNT(DISTINCT(user)) FROM posts WHERE TIMESTAMPDIFF(DAY, ts_created, CURRENT_TIMESTAMP()) <= 30) AS m_post_cmt,
(SELECT COUNT(DISTINCT(user)) FROM posts WHERE parent=0) AS posters,
(SELECT COUNT(DISTINCT(user)) FROM posts WHERE parent!=0) AS commenters
""")
for (total, m_visited, m_post_cmt, posters, commenters) in cur:
return {
'total': total,
'posters': posters,
'commenters': commenters,
'm_visited': m_visited,
'm_post_cmt': m_post_cmt
}
return {}
class Search:
def __init__(self, db):
self.db = db
self.clear()
def clear(self):
self.context = None
self.terms = []
self.results = []
def run(self, terms, subspace=None, limit=30, page_index=0):
self.clear()
self.context = subspace
self.terms = terms
if len(terms) == 0:
return 0
p_terms = []
tag_terms = []
for term in terms:
if term[0] == '#':
tag_terms.append(term[1:])
else:
p_terms.append(f'%{term}%')
cur = self.db.conn.cursor()
def exact_match(w):
for term in terms:
if w.lower() == term.lower():
return 1
return 0
# Users.
if not subspace and p_terms:
ulimit = int(limit / 2)
cond = ['(name LIKE ? OR info LIKE ? OR url LIKE ?)'] * len(p_terms)
values = []
for pt in p_terms:
values += [pt, pt, pt]
cur.execute(f"""
SELECT UNIX_TIMESTAMP(ts_active), id, name, avatar, info, flair, url
FROM users
WHERE {' AND '.join(cond)}
ORDER BY ts_active DESC
LIMIT {ulimit}
OFFSET {ulimit * page_index}
""", values)
for (ts, id, name, avatar, info, flair, url) in cur:
self.results.append(((exact_match(name), ts),
User(id, name, info, flair, url, None, avatar, None, None, None,
None, None, None, None, None, ts, None, None, None, None)))
# Subspaces.
cur.execute(f"""
SELECT UNIX_TIMESTAMP(ts_active), id, name, info, url
FROM subspaces
WHERE {' AND '.join(['owner=0'] + cond)}
ORDER BY ts_active DESC
LIMIT {ulimit}
OFFSET {ulimit * page_index}
""", values)
for (ts, id, name, info, url) in cur:
self.results.append(((exact_match(name), ts),
Subspace(id, name, info, url, None, None, None, ts)))
# Posts (including comments).
cond = ['is_draft=FALSE']
values = []
if subspace:
cond.append('subspace=' + str(subspace.id))
for pt in p_terms:
cond.append('(p.title LIKE ? OR u.name LIKE ? OR s.content LIKE ? OR tags LIKE ? OR s.url LIKE ?)')
values += [pt, pt, pt, pt, pt]
for tt in tag_terms:
cond.append('t.tag=?')
values += [tt]
cur.execute(f"""
SELECT DISTINCT
UNIX_TIMESTAMP(ts_edited),
p.id, subspace, parent, user, issueid, title, tags,
summary, s.type, sub.name, sub.owner, u.name, u.avatar
FROM segments s
JOIN posts p ON s.post=p.id
JOIN subspaces sub ON p.subspace=sub.id
JOIN users u ON p.user=u.id
LEFT JOIN tags t ON t.post=p.id
WHERE {' AND '.join(cond)}
ORDER BY ts_edited DESC
LIMIT {limit}
OFFSET {page_index * limit}
""", values)
for (ts, id, psub, parent, user, issueid, title, tags, summary, seg_type,
sub_name, sub_owner, poster_name, poster_avatar) in cur:
self.results.append(((0, ts),
Post(id, psub, parent, user, issueid, title, False, False,
None, None, None, tags, ts, ts, summary,
sub_name, sub_owner, poster_avatar, poster_name),
seg_type))
# Sort everything by timestamp.
self.results.sort(key=lambda result: result[0], reverse=True)
return len(self.results)
Reference in New Issue View Git Blame Copy Permalink