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()) 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) 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'(? 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) 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())=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)