def get_post(self, pid):
"""
Returns the post with the given pid.
"""
with Transaction(self) as t:
t.execute("""SELECT * FROM posts WHERE pid = ? LIMIT 1""", (pid, ))
return t.fetchone_dataclass(Post)
def purge(self):
"""
Resets the database to its initial state, deleting everything.
"""
with Transaction(self) as t:
for table in Database.SQL_TABLES.keys():
t.execute("""DELETE FROM {}""".format(table))
def update_fulltext(self, pid, content):
"""
Updates or creates the post fulltext search index for the post with the
given pid.
"""
# If a Post object is given as content, generate the corresponding
# searchable string
if isinstance(content, Post):
keywords = ""
if content.keywords:
keywords = " " + " ".join(content.keywords.split(","))
content = content.content + keywords
with Transaction(self) as t:
# Check whether there already is some fulltext stored for the given
# row id
t.execute("SELECT TRUE FROM fulltext WHERE rowid = ?", (pid, ))
if t.fetchone()[0]:
# If yes, just update the fulltext
t.execute(
"""UPDATE fulltext
SET content=? WHERE rowid = ?""", (
content,
pid,
))
else:
# Otherwise insert a new entry
t.execute(
"""INSERT INTO fulltext(rowid, content)
VALUES (?, ?)""", (
pid,
content,
))
def delete_user(self, uid):
"""
Deletes the user with the given id, returns true if the
"""
with Transaction(self) as t:
t.execute("""DELETE FROM users WHERE uid = ?""", (uid, ))
return t.rowcount > 0
def delete_session(self, sid):
"""
Deletes the session with the given identifier.
"""
with Transaction(self) as t:
t.execute("DELETE FROM sessions WHERE sid = ?", (sid, ))
return t.rowcount > 0
def delete_fulltext(self, pid):
"""
Deletes the fulltext for the post with the given pid.
"""
with Transaction(self) as t:
t.execute("""DELETE FROM fulltext WHERE rowid = ?""", (pid, ))
return t.rowcount > 0
def purge_sessions_for_user(self, uid):
"""
Deletes all sessions associated with the given user.
"""
with Transaction(self) as t:
t.execute("DELETE FROM sessions WHERE uid = ?", (uid, ))
return t.rowcount > 0
def get_session_uid(self, sid):
"""
Returns the uid associated with the given session or -1 if the session
does not exist.
"""
with Transaction(self) as t:
t.execute("SELECT uid FROM sessions WHERE sid = ?", (sid, ))
return t.fetchone()[0]
def update_session_mtime(self, sid):
"""
Advances the modification time for the given session id.
"""
with Transaction(self) as t:
t.execute("UPDATE sessions SET mtime = now() WHERE sid = ?",
(sid, ))
return t.rowcount > 0
def create_session(self, sid, uid):
"""
Creates a session for the given user and returns the session identifier.
"""
with Transaction(self) as t:
t.execute(
"INSERT INTO sessions(sid, uid, mtime) VALUES (?, ?, now())",
(sid, uid))
def purge_stale_sessions(self, max_age):
"""
Delete sessions older than the specified maximum age.
"""
with Transaction(self) as t:
t.execute("DELETE FROM sessions WHERE now() - mtime > ?",
(max_age, ))
return t.rowcount > 0
def get_user_by_name(self, user_name):
"""
Returns an object describing the user with the given user_name, or None
if the user does not exist.
"""
with Transaction(self) as t:
t.execute("""SELECT * FROM users WHERE name=? LIMIT 1""",
(user_name, ))
return t.fetchone_dataclass(User)
def create_post(self, post, history=False):
with Transaction(self) as t:
table = "posts_history" if history else "posts"
t.execute(
"""INSERT INTO {}(pid, revision, author, content, keywords,
date, ctime, cuid, mtime, muid)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""".format(table),
astuple(post))
return t.lastrowid
def list_users(self):
"""
Returns a table containing information about all users.
"""
with Transaction(self) as t:
t.execute("""SELECT uid, name, display_name, role, auth_method,
password, reset_password
FROM users ORDER BY uid""")
return t.fetchall_dataclass(User)
def __setitem__(self, key, value):
with Transaction(self.db) as t:
if multidict and (isinstance(value, list) or isinstance(
value, set) or isinstance(value, tuple)):
t.execute(_sql_delete, (key, ))
for v in value:
t.execute(_sql_upsert, (key, v, v))
else:
t.execute(_sql_upsert, (key, value, value))
return value
def get_user_by_id(self, uid):
"""
Returns an object describing the user with the given uid, or None if the
user does not exist.
"""
# Fetch the corresponding row from the DB
with Transaction(self) as t:
t.execute("""SELECT * FROM users WHERE uid=? LIMIT 1""",
(int(uid), ))
return t.fetchone_dataclass(User)
def match_fulltext(self, query):
"""
Returns the ids of the posts matching the given fulltext query.
Note: This function is mostly used for testing, the API usually uses
filters for filtering for posts.
"""
with Transaction(self) as t:
t.execute(
"""SELECT rowid FROM fulltext
WHERE content MATCH ?""", (query, ))
return list(map(lambda x: x[0], t.fetchall()))
def _create_indices(self):
"""
Creates all indices required for efficient operation of the database.
"""
with Transaction(self) as t:
t.execute(
"""CREATE INDEX IF NOT EXISTS posts_date_index ON posts(date DESC)"""
)
t.execute(
"""CREATE INDEX IF NOT EXISTS posts_pid_index ON posts(pid)""")
t.execute(
"""CREATE INDEX IF NOT EXISTS keywords_keyword_index ON keywords(keyword ASC)"""
)
def total_post_count(self, filter=None):
"""
Counts the total number of posts of a certain id.
"""
with Transaction(self) as t:
if filter is None:
t.execute("""SELECT COUNT() FROM posts""")
else:
flt_sql, flt_params, _ = filter.compile().emit([], count=True)
t.execute(flt_sql, flt_params)
res = t.fetchone()[0]
return 0 if res is None else res
def create_user(self, user):
"""
Creates a new user with the given properties.
"""
with Transaction(self) as t:
try:
t.execute(
"""
INSERT INTO users
(uid, name, display_name, role, auth_method, password, reset_password)
VALUES (?, ?, ?, ?, ?, ?, ?)""", astuple(user))
except sqlite3.IntegrityError:
raise UniqueKeyViolationError()
return t.lastrowid
def lookup(self, key):
"""
Looks up the given key. Returns the associated value or None if the
key-value pair does not exist.
@param key is the key that should be looked up.
"""
with Transaction(self.db) as t:
t.execute(_sql_lookup, (key, ))
if multidict:
x = t.fetchall()
return None if len(x) == 0 else set(map(lambda x: x[0], x))
else:
return t.fetchone()[0]
def update_post(self, post, history=False):
with Transaction(self) as t:
# Convert the post to a tuple
post = astuple(post)
# Select the correct target table
table = "posts_history" if history else "posts"
# Execute the update
t.execute(
"""UPDATE {} SET revision=?, author=?, content=?, keywords=?,
date=?, ctime=?, cuid=?, mtime=?, muid=?
WHERE pid=?""".format(table), post[1:] + (post[0], ))
return t.rowcount > 0
def update_user(self, user):
with Transaction(self) as t:
# Convert the user to a tuple
user = astuple(user)
# Update the user row
try:
t.execute(
"""
UPDATE users SET name=?, display_name=?, role=?, auth_method=?,
password=?, reset_password=?
WHERE uid=?""", user[1:] + (user[0], ))
except sqlite3.IntegrityError:
raise UniqueKeyViolationError()
return t.lastrowid
def list_posts(self, start=0, limit=-1, history=False, filter=None):
"""
Lists the newest revision of each post, ordered by date.
"""
with Transaction(self) as t:
# Assemble the SQL and corresponding parameter, depending on whether
# a filter was given or not
table = "posts_history" if history else "posts"
if filter is None:
sql = """SELECT * FROM {} ORDER BY date DESC
LIMIT ? OFFSET ?""".format(table)
params = (limit, start)
else:
# Fetch the keys we would like to read
keys = list(Post.__dataclass_fields__.keys())
# When selecting from the post history, substitute the able
# name "posts" with "posts_history". Note: This will not yield
# the correct results in conjunction with keyword filters, but
# that is not a use-case we should run into here.
if history:
flt_compiled = filter.compile(
table_subs={"posts": "posts_history"})
else:
flt_compiled = filter.compile()
# Compile the filter and extract the SQL and parameters
flt_sql, flt_params, flt_alias = flt_compiled.emit(keys)
# Construct the complete SQL and parameters
sql = """{} ORDER BY {}.date DESC LIMIT ? OFFSET ?""".format(
flt_sql, flt_alias)
params = flt_params + (limit, start)
# Actually execute the SQL
t.execute(sql, params)
return t.fetchall_dataclass(Post)
def clear(self):
with Transaction(self.db) as t:
t.execute(_sql_clear)
def __len__(self):
with Transaction(self.db) as t:
t.execute(_sql_len)
return t.fetchone()[0]
def __delitem__(self, key):
with Transaction(self.db) as t:
t.execute(_sql_delete, (key, ))
if t.rowcount == 0:
raise KeyError(key)
def delete_post(self, pid, history=False):
with Transaction(self) as t:
table = "posts_history" if history else "posts"
t.execute("""DELETE FROM {} WHERE pid=?""".format(table), (pid, ))
return t.lastrowid
def __contains__(self, key):
with Transaction(self.db) as t:
t.execute(_sql_contains, (key, ))
return bool(t.fetchone())