121 lines
4.5 KiB
Nim
121 lines
4.5 KiB
Nim
import tiny_sqlite
|
|
import logging
|
|
import options
|
|
import times
|
|
import sequtils
|
|
|
|
import ./util
|
|
|
|
let migrations = @[
|
|
"""
|
|
CREATE TABLE users (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
pwhash BLOB NOT NULL,
|
|
created INTEGER NOT NULL
|
|
);
|
|
CREATE TABLE threads (
|
|
id INTEGER PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
created INTEGER NOT NULL,
|
|
updated INTEGER NOT NULL,
|
|
creator INTEGER NOT NULL REFERENCES users(id)
|
|
);
|
|
CREATE TABLE posts (
|
|
id INTEGER PRIMARY KEY,
|
|
time INTEGER NOT NULL,
|
|
content TEXT NOT NULL,
|
|
thread INTEGER NOT NULL REFERENCES threads(id),
|
|
author INTEGER NOT NULL REFERENCES users(id),
|
|
idx INTEGER NOT NULL
|
|
);
|
|
CREATE TABLE sessions (
|
|
id INTEGER PRIMARY KEY,
|
|
timestamp INTEGER NOT NULL,
|
|
data TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX posts_thread_idx ON posts (thread);
|
|
"""
|
|
]
|
|
|
|
type
|
|
User* = object
|
|
id*: int64
|
|
name*: string
|
|
pwhash*: string
|
|
created*: Time
|
|
Thread* = object
|
|
id*: int64
|
|
title*: string
|
|
created*: Time
|
|
updated*: Time
|
|
creator*: int64
|
|
creatorName*: string
|
|
replies*: int
|
|
Post* = object
|
|
id*: int64
|
|
thread*: int64
|
|
time*: Time
|
|
content*: string
|
|
author*: int64
|
|
authorName*: string
|
|
index*: int
|
|
|
|
proc migrate*(db: DbConn) =
|
|
let currentVersion = fromDbValue(get db.value("PRAGMA user_version"), int)
|
|
for mid in (currentVersion + 1) .. migrations.len:
|
|
db.transaction:
|
|
logger().log(lvlInfo, "Migrating to schema " & $mid)
|
|
db.execScript migrations[mid - 1]
|
|
# for some reason this pragma does not work using normal parameter binding
|
|
db.exec("PRAGMA user_version = " & $mid)
|
|
logger().log(lvlDebug, "DB ready")
|
|
|
|
proc processPost(r: ResultRow): Post =
|
|
let (id, ts, content, thread, author, authorName, index) = r.unpack((int64, Time, string, int64, int64, string, int))
|
|
Post(id: id, time: ts, content: content, thread: thread, author: author, authorName: authorName, index: index)
|
|
|
|
proc processThread(r: ResultRow): Thread =
|
|
let (id, title, created, updated, creator, creatorName, replies) = r.unpack((int64, string, Time, Time, int64, string, int))
|
|
Thread(id: id, created: created, title: title, updated: updated, creator: creator, creatorName: creatorName, replies: replies)
|
|
|
|
proc processUser(r: ResultRow): User =
|
|
let (id, name, pwhash, created) = r.unpack((int64, string, string, Time))
|
|
User(id: id, name: name, pwhash: pwhash, created: created)
|
|
|
|
proc getPosts*(db: DbConn, threadID: int64): seq[Post] =
|
|
db.all("SELECT posts.id, posts.time, posts.content, posts.thread, posts.author, users.name, posts.idx FROM posts JOIN users ON users.id = posts.author WHERE thread = ? ORDER BY time ASC", threadID).map(processPost)
|
|
|
|
proc allThreads*(db: DbConn): seq[Thread] =
|
|
db.all("""SELECT threads.id, threads.title, threads.created, threads.updated, threads.creator, users.name, (SELECT COUNT(*) FROM posts WHERE thread = threads.id)
|
|
FROM threads JOIN users ON users.id = threads.creator ORDER BY updated DESC""").map(processThread)
|
|
|
|
proc getThread*(db: DbConn, id: int64): Option[Thread] =
|
|
db.one("""SELECT threads.id, threads.title, threads.created, threads.updated, threads.creator, users.name, (SELECT COUNT(*) FROM posts WHERE thread = threads.id)
|
|
FROM threads JOIN users ON users.id = threads.creator WHERE threads.id = ?""", id).map(processThread)
|
|
|
|
proc postPost*(db: DbConn, post: string, threadID: int64, userID: int64): int =
|
|
var index: int
|
|
db.transaction:
|
|
db.exec("UPDATE threads SET updated = ? WHERE id = ?", getTime(), threadID)
|
|
index = db.value("SELECT COUNT(*) FROM posts WHERE posts.thread = ?", threadID).get.fromDbValue(int)
|
|
db.exec("INSERT INTO posts VALUES (?, ?, ?, ?, ?, ?)", randomID(), getTime(), post, threadID, userID, index)
|
|
index
|
|
|
|
proc postThread*(db: DbConn, title: string, userID: int64): int64 =
|
|
let id = randomID()
|
|
db.exec("INSERT INTO threads VALUES (?, ?, ?, ?, ?)", id, title, getTime(), getTime(), userID)
|
|
id
|
|
|
|
proc getUser*(db: DbConn, id: int64): Option[User] =
|
|
db.one("SELECT * FROM users WHERE id = ?", id).map(processUser)
|
|
|
|
proc getUser*(db: DbConn, name: string): Option[User] =
|
|
db.one("SELECT * FROM users WHERE name = ?", name).map(processUser)
|
|
|
|
proc addUser*(db: DbConn, name: string, password: string): int64 =
|
|
let id = randomID()
|
|
let pwhash = passwordHash(password)
|
|
db.exec("INSERT INTO users VALUES (?, ?, ?, ?)", id, name, pwhash, getTime())
|
|
id |