package database import ( "context" "database/sql" "fmt" "cerca/util" "html/template" "log" "net/url" "time" _ "github.com/mattn/go-sqlite3") type DB struct { db *sql.DB} func InitDB(filepath string) DB { db, err := sql.Open("sqlite3", filepath) util.Check(err, "opening sqlite3 database at %s", filepath) if db == nil { log.Fatalln("db is nil") } createTables(db) return DB{db}} func createTables(db *sql.DB) { // create the table if it doesn't exist queries := []string{ /* used for versioning migrations */ ` CREATE TABLE IF NOT EXISTS meta ( schemaversion INTEGER NOT NULL ); `, ` CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, passwordhash TEXT NOT NULL ); `, ` CREATE TABLE IF NOT EXISTS pubkeys ( id INTEGER PRIMARY KEY AUTOINCREMENT, pubkey TEXT NOT NULL UNIQUE, userid integer NOT NULL UNIQUE, FOREIGN KEY (userid) REFERENCES users(id) ); `, ` CREATE TABLE IF NOT EXISTS registrations ( id INTEGER PRIMARY KEY AUTOINCREMENT, userid INTEGER, host STRING, link STRING, time DATE, FOREIGN KEY (userid) REFERENCES users(id) ); `, /* also known as forum categories; buckets of threads */ ` CREATE TABLE IF NOT EXISTS topics ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, description TEXT ); `, /* thread link structure: <domain>.<tld>/thread/<id>/[<blurb>] */ ` CREATE TABLE IF NOT EXISTS threads ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, publishtime DATE, topicid INTEGER, authorid INTEGER, FOREIGN KEY(topicid) REFERENCES topics(id), FOREIGN KEY(authorid) REFERENCES users(id) ); `, ` CREATE TABLE IF NOT EXISTS posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT NOT NULL, publishtime DATE, lastedit DATE, authorid INTEGER, threadid INTEGER, FOREIGN KEY(authorid) REFERENCES users(id), FOREIGN KEY(threadid) REFERENCES threads(id) ); `} for _, query := range queries { if _, err := db.Exec(query); err != nil { log.Fatalln(util.Eout(err, "creating database table %s", query)) } }} /* goal for 2021-12-26* create thread* create post* get thread* + html render of begotten thread */ /* goal for 2021-12-28* in browser: reply on a thread* in browser: create a new thread */func (d DB) Exec(stmt string, args ...interface{}) (sql.Result, error) { return d.db.Exec(stmt, args...)} func (d DB) CreateThread(title, content string, authorid, topicid int) (int, error) { ed := util.Describe("create thread") // create the new thread in a transaction spanning two statements tx, err := d.db.BeginTx(context.Background(), &sql.TxOptions{}) // proper tx options? ed.Check(err, "start transaction") // first, create the new thread publish := time.Now() threadStmt := `INSERT INTO threads (title, publishtime, topicid, authorid) VALUES (?, ?, ?, ?) RETURNING id` replyStmt := `INSERT INTO posts (content, publishtime, threadid, authorid) VALUES (?, ?, ?, ?)` var threadid int err = tx.QueryRow(threadStmt, title, publish, topicid, authorid).Scan(&threadid) if err = ed.Eout(err, "add thread %s by %d in topic %d", title, authorid, topicid); err != nil { _ = tx.Rollback() log.Println(err, "rolling back") return -1, err } // then add the content as the first reply to the thread _, err = tx.Exec(replyStmt, content, publish, threadid, authorid) if err = ed.Eout(err, "add initial reply for thread %d", threadid); err != nil { _ = tx.Rollback() log.Println(err, "rolling back") return -1, err } err = tx.Commit() ed.Check(err, "commit transaction") // finally return the id of the created thread, so we can do a friendly redirect return threadid, nil} // c.f.// https://medium.com/aubergine-solutions/how-i-handled-null-possible-values-from-database-rows-in-golang-521fb0ee267// type NullTime sql.NullTimetype Post struct { ThreadTitle string Content template.HTML Author string Publish time.Time LastEdit sql.NullTime // TODO: handle json marshalling with custom type} func (d DB) DeleteThread() {}func (d DB) MoveThread() {} // TODO(2021-12-28): return error if non-existent threadfunc (d DB) GetThread(threadid int) []Post { // TODO: make edit work if no edit timestamp detected e.g. // (sql: Scan error on column index 3, name "lastedit": unsupported Scan, storing driver.Value type <nil> into type // *time.Time) // join with: // users table to get user name // threads table to get thread title query := ` SELECT t.title, content, u.name, p.publishtime, p.lastedit FROM posts p INNER JOIN users u ON u.id = p.authorid INNER JOIN threads t ON t.id = p.threadid WHERE threadid = ? ORDER BY p.publishtime ` stmt, err := d.db.Prepare(query) util.Check(err, "get thread: prepare query") defer stmt.Close() rows, err := stmt.Query(threadid) util.Check(err, "get thread: query") defer rows.Close() var data Post var posts []Post for rows.Next() { if err := rows.Scan(&data.ThreadTitle, &data.Content, &data.Author, &data.Publish, &data.LastEdit); err != nil { log.Fatalln(util.Eout(err, "get data for thread %d", threadid)) } posts = append(posts, data) } return posts} type Thread struct { Title string Author string Slug string ID int Publish time.Time} // get a list of threadsfunc (d DB) ListThreads() []Thread { query := ` SELECT t.title, t.id, u.name FROM threads t INNER JOIN users u on u.id = t.authorid ORDER BY t.publishtime DESC ` stmt, err := d.db.Prepare(query) util.Check(err, "list threads: prepare query") defer stmt.Close() rows, err := stmt.Query() util.Check(err, "list threads: query") defer rows.Close() var data Thread var threads []Thread for rows.Next() { if err := rows.Scan(&data.Title, &data.ID, &data.Author); err != nil { log.Fatalln(util.Eout(err, "list threads: read in data via scan")) } data.Slug = fmt.Sprintf("%d/%s/", data.ID, util.SanitizeURL(data.Title)) threads = append(threads, data) } return threads} func (d DB) AddPost(content string, threadid, authorid int) { stmt := `INSERT INTO posts (content, publishtime, threadid, authorid) VALUES (?, ?, ?, ?)` publish := time.Now() _, err := d.Exec(stmt, content, publish, threadid, authorid) util.Check(err, "add post to thread %d (author %d)", threadid, authorid)} func (d DB) EditPost(content string, postid int) { stmt := `UPDATE posts set content = ?, lastedit = ? WHERE id = ?` edit := time.Now() _, err := d.Exec(stmt, content, edit, postid) util.Check(err, "edit post %d", postid)} func (d DB) DeletePost(postid int) { stmt := `DELETE FROM posts WHERE id = ?` _, err := d.Exec(stmt, postid) util.Check(err, "deleting post %d", postid)} func (d DB) CreateTopic(title, description string) { stmt := `INSERT INTO topics (name, description) VALUES (?, ?)` _, err := d.Exec(stmt, title, description) util.Check(err, "creating topic %s", title)} func (d DB) UpdateTopicName(topicid int, newname string) { stmt := `UPDATE topics SET name = ? WHERE id = ?` _, err := d.Exec(stmt, newname, topicid) util.Check(err, "changing topic %d's name to %s", topicid, newname)} func (d DB) UpdateTopicDescription(topicid int, newdesc string) { stmt := `UPDATE topics SET description = ? WHERE id = ?` _, err := d.Exec(stmt, newdesc, topicid) util.Check(err, "changing topic %d's description to %s", topicid, newdesc)} func (d DB) DeleteTopic(topicid int) { stmt := `DELETE FROM topics WHERE id = ?` _, err := d.Exec(stmt, topicid) util.Check(err, "deleting topic %d", topicid)} func (d DB) CreateUser(name, hash string) (int, error) { stmt := `INSERT INTO users (name, passwordhash) VALUES (?, ?) RETURNING id` var userid int err := d.db.QueryRow(stmt, name, hash).Scan(&userid) if err != nil { return -1, util.Eout(err, "creating user %s", name) } return userid, nil} func (d DB) GetPasswordHash(username string) (string, int, error) { stmt := `SELECT passwordhash, id FROM users where name = ?` var hash string var userid int err := d.db.QueryRow(stmt, username).Scan(&hash, &userid) if err != nil { return "", -1, util.Eout(err, "get password hash") } return hash, userid, nil} func (d DB) existsQuery(substmt string, args ...interface{}) (bool, error) { stmt := fmt.Sprintf(`SELECT exists (%s)`, substmt) var exists bool err := d.db.QueryRow(stmt, args...).Scan(&exists) if err != nil { return false, util.Eout(err, "exists: %s", substmt) } return exists, nil} func (d DB) CheckUserExists(userid int) (bool, error) { stmt := `SELECT 1 FROM users WHERE id = ?` return d.existsQuery(stmt, userid)} func (d DB) CheckUsernameExists(username string) (bool, error) { stmt := `SELECT 1 FROM users WHERE name = ?` return d.existsQuery(stmt, username)} func (d DB) UpdateUserName(userid int, newname string) { stmt := `UPDATE users SET name = ? WHERE id = ?` _, err := d.Exec(stmt, newname, userid) util.Check(err, "changing user %d's name to %s", userid, newname)} func (d DB) UpdateUserPasswordHash(userid int, newhash string) { stmt := `UPDATE users SET passwordhash = ? WHERE id = ?` _, err := d.Exec(stmt, newhash, userid) util.Check(err, "changing user %d's description to %s", userid, newhash)} func (d DB) DeleteUser(userid int) { stmt := `DELETE FROM users WHERE id = ?` _, err := d.Exec(stmt, userid) util.Check(err, "deleting user %d", userid)} func (d DB) AddPubkey(userid int, pubkey string) error { ed := util.Describe("add pubkey") stmt := `INSERT INTO pubkeys (pubkey, userid) VALUES (?, ?)` _, err := d.Exec(stmt, userid, pubkey) if err = ed.Eout(err, "inserting record"); err != nil { return err } return nil} func (d DB) AddRegistration(userid int, verificationLink string) error { ed := util.Describe("add registration") stmt := `INSERT INTO registrations (userid, host, link, time) VALUES (?, ?, ?, ?)` t := time.Now() u, err := url.Parse(verificationLink) if err = ed.Eout(err, "parse url"); err != nil { return err } _, err = d.Exec(stmt, userid, u.Host, verificationLink, t) if err = ed.Eout(err, "add registration"); err != nil { return err } return nil}
package database
import (
"context"
"database/sql"
"fmt"
"cerca/util"
"html/template"
"log"
"net/url"
"time"
_ "github.com/mattn/go-sqlite3"
)
type DB struct {
db *sql.DB
}
func InitDB(filepath string) DB {
db, err := sql.Open("sqlite3", filepath)
util.Check(err, "opening sqlite3 database at %s", filepath)
if db == nil {
log.Fatalln("db is nil")
}
createTables(db)
return DB{db}
}
func createTables(db *sql.DB) {
// create the table if it doesn't exist
queries := []string{
/* used for versioning migrations */
`
CREATE TABLE IF NOT EXISTS meta (
schemaversion INTEGER NOT NULL
);
`,
`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
passwordhash TEXT NOT NULL
);
`,
`
CREATE TABLE IF NOT EXISTS pubkeys (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pubkey TEXT NOT NULL UNIQUE,
userid integer NOT NULL UNIQUE,
FOREIGN KEY (userid) REFERENCES users(id)
);
`,
`
CREATE TABLE IF NOT EXISTS registrations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
userid INTEGER,
host STRING,
link STRING,
time DATE,
FOREIGN KEY (userid) REFERENCES users(id)
);
`,
/* also known as forum categories; buckets of threads */
`
CREATE TABLE IF NOT EXISTS topics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
description TEXT
);
`,
/* thread link structure: <domain>.<tld>/thread/<id>/[<blurb>] */
`
CREATE TABLE IF NOT EXISTS threads (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
publishtime DATE,
topicid INTEGER,
authorid INTEGER,
FOREIGN KEY(topicid) REFERENCES topics(id),
FOREIGN KEY(authorid) REFERENCES users(id)
);
`,
`
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT NOT NULL,
publishtime DATE,
lastedit DATE,
authorid INTEGER,
threadid INTEGER,
FOREIGN KEY(authorid) REFERENCES users(id),
FOREIGN KEY(threadid) REFERENCES threads(id)
);
`}
for _, query := range queries {
if _, err := db.Exec(query); err != nil {
log.Fatalln(util.Eout(err, "creating database table %s", query))
}
}
}
/* goal for 2021-12-26
* create thread
* create post
* get thread
* + html render of begotten thread
*/
/* goal for 2021-12-28
* in browser: reply on a thread
* in browser: create a new thread
*/
func (d DB) Exec(stmt string, args ...interface{}) (sql.Result, error) {
return d.db.Exec(stmt, args...)
}
func (d DB) CreateThread(title, content string, authorid, topicid int) (int, error) {
ed := util.Describe("create thread")
// create the new thread in a transaction spanning two statements
tx, err := d.db.BeginTx(context.Background(), &sql.TxOptions{}) // proper tx options?
ed.Check(err, "start transaction")
// first, create the new thread
publish := time.Now()
threadStmt := `INSERT INTO threads (title, publishtime, topicid, authorid) VALUES (?, ?, ?, ?)
RETURNING id`
replyStmt := `INSERT INTO posts (content, publishtime, threadid, authorid) VALUES (?, ?, ?, ?)`
var threadid int
err = tx.QueryRow(threadStmt, title, publish, topicid, authorid).Scan(&threadid)
if err = ed.Eout(err, "add thread %s by %d in topic %d", title, authorid, topicid); err != nil {
_ = tx.Rollback()
log.Println(err, "rolling back")
return -1, err
}
// then add the content as the first reply to the thread
_, err = tx.Exec(replyStmt, content, publish, threadid, authorid)
if err = ed.Eout(err, "add initial reply for thread %d", threadid); err != nil {
_ = tx.Rollback()
log.Println(err, "rolling back")
return -1, err
}
err = tx.Commit()
ed.Check(err, "commit transaction")
// finally return the id of the created thread, so we can do a friendly redirect
return threadid, nil
}
// c.f.
// https://medium.com/aubergine-solutions/how-i-handled-null-possible-values-from-database-rows-in-golang-521fb0ee267
// type NullTime sql.NullTime
type Post struct {
ThreadTitle string
Content template.HTML
Author string
Publish time.Time
LastEdit sql.NullTime // TODO: handle json marshalling with custom type
}
func (d DB) DeleteThread() {}
func (d DB) MoveThread() {}
// TODO(2021-12-28): return error if non-existent thread
func (d DB) GetThread(threadid int) []Post {
// TODO: make edit work if no edit timestamp detected e.g.
// (sql: Scan error on column index 3, name "lastedit": unsupported Scan, storing driver.Value type <nil> into type
// *time.Time)
// join with:
// users table to get user name
// threads table to get thread title
query := `
SELECT t.title, content, u.name, p.publishtime, p.lastedit
FROM posts p
INNER JOIN users u ON u.id = p.authorid
INNER JOIN threads t ON t.id = p.threadid
WHERE threadid = ?
ORDER BY p.publishtime
`
stmt, err := d.db.Prepare(query)
util.Check(err, "get thread: prepare query")
defer stmt.Close()
rows, err := stmt.Query(threadid)
util.Check(err, "get thread: query")
defer rows.Close()
var data Post
var posts []Post
for rows.Next() {
if err := rows.Scan(&data.ThreadTitle, &data.Content, &data.Author, &data.Publish, &data.LastEdit); err != nil {
log.Fatalln(util.Eout(err, "get data for thread %d", threadid))
}
posts = append(posts, data)
}
return posts
}
type Thread struct {
Title string
Author string
Slug string
ID int
Publish time.Time
}
// get a list of threads
func (d DB) ListThreads() []Thread {
query := `
SELECT t.title, t.id, u.name FROM threads t
INNER JOIN users u on u.id = t.authorid
ORDER BY t.publishtime DESC
`
stmt, err := d.db.Prepare(query)
util.Check(err, "list threads: prepare query")
defer stmt.Close()
rows, err := stmt.Query()
util.Check(err, "list threads: query")
defer rows.Close()
var data Thread
var threads []Thread
for rows.Next() {
if err := rows.Scan(&data.Title, &data.ID, &data.Author); err != nil {
log.Fatalln(util.Eout(err, "list threads: read in data via scan"))
}
data.Slug = fmt.Sprintf("%d/%s/", data.ID, util.SanitizeURL(data.Title))
threads = append(threads, data)
}
return threads
}
func (d DB) AddPost(content string, threadid, authorid int) {
stmt := `INSERT INTO posts (content, publishtime, threadid, authorid) VALUES (?, ?, ?, ?)`
publish := time.Now()
_, err := d.Exec(stmt, content, publish, threadid, authorid)
util.Check(err, "add post to thread %d (author %d)", threadid, authorid)
}
func (d DB) EditPost(content string, postid int) {
stmt := `UPDATE posts set content = ?, lastedit = ? WHERE id = ?`
edit := time.Now()
_, err := d.Exec(stmt, content, edit, postid)
util.Check(err, "edit post %d", postid)
}
func (d DB) DeletePost(postid int) {
stmt := `DELETE FROM posts WHERE id = ?`
_, err := d.Exec(stmt, postid)
util.Check(err, "deleting post %d", postid)
}
func (d DB) CreateTopic(title, description string) {
stmt := `INSERT INTO topics (name, description) VALUES (?, ?)`
_, err := d.Exec(stmt, title, description)
util.Check(err, "creating topic %s", title)
}
func (d DB) UpdateTopicName(topicid int, newname string) {
stmt := `UPDATE topics SET name = ? WHERE id = ?`
_, err := d.Exec(stmt, newname, topicid)
util.Check(err, "changing topic %d's name to %s", topicid, newname)
}
func (d DB) UpdateTopicDescription(topicid int, newdesc string) {
stmt := `UPDATE topics SET description = ? WHERE id = ?`
_, err := d.Exec(stmt, newdesc, topicid)
util.Check(err, "changing topic %d's description to %s", topicid, newdesc)
}
func (d DB) DeleteTopic(topicid int) {
stmt := `DELETE FROM topics WHERE id = ?`
_, err := d.Exec(stmt, topicid)
util.Check(err, "deleting topic %d", topicid)
}
func (d DB) CreateUser(name, hash string) (int, error) {
stmt := `INSERT INTO users (name, passwordhash) VALUES (?, ?) RETURNING id`
var userid int
err := d.db.QueryRow(stmt, name, hash).Scan(&userid)
if err != nil {
return -1, util.Eout(err, "creating user %s", name)
}
return userid, nil
}
func (d DB) GetPasswordHash(username string) (string, int, error) {
stmt := `SELECT passwordhash, id FROM users where name = ?`
var hash string
var userid int
err := d.db.QueryRow(stmt, username).Scan(&hash, &userid)
if err != nil {
return "", -1, util.Eout(err, "get password hash")
}
return hash, userid, nil
}
func (d DB) existsQuery(substmt string, args ...interface{}) (bool, error) {
stmt := fmt.Sprintf(`SELECT exists (%s)`, substmt)
var exists bool
err := d.db.QueryRow(stmt, args...).Scan(&exists)
if err != nil {
return false, util.Eout(err, "exists: %s", substmt)
}
return exists, nil
}
func (d DB) CheckUserExists(userid int) (bool, error) {
stmt := `SELECT 1 FROM users WHERE id = ?`
return d.existsQuery(stmt, userid)
}
func (d DB) CheckUsernameExists(username string) (bool, error) {
stmt := `SELECT 1 FROM users WHERE name = ?`
return d.existsQuery(stmt, username)
}
func (d DB) UpdateUserName(userid int, newname string) {
stmt := `UPDATE users SET name = ? WHERE id = ?`
_, err := d.Exec(stmt, newname, userid)
util.Check(err, "changing user %d's name to %s", userid, newname)
}
func (d DB) UpdateUserPasswordHash(userid int, newhash string) {
stmt := `UPDATE users SET passwordhash = ? WHERE id = ?`
_, err := d.Exec(stmt, newhash, userid)
util.Check(err, "changing user %d's description to %s", userid, newhash)
}
func (d DB) DeleteUser(userid int) {
stmt := `DELETE FROM users WHERE id = ?`
_, err := d.Exec(stmt, userid)
util.Check(err, "deleting user %d", userid)
}
func (d DB) AddPubkey(userid int, pubkey string) error {
ed := util.Describe("add pubkey")
stmt := `INSERT INTO pubkeys (pubkey, userid) VALUES (?, ?)`
_, err := d.Exec(stmt, userid, pubkey)
if err = ed.Eout(err, "inserting record"); err != nil {
return err
}
return nil
}
func (d DB) AddRegistration(userid int, verificationLink string) error {
ed := util.Describe("add registration")
stmt := `INSERT INTO registrations (userid, host, link, time) VALUES (?, ?, ?, ?)`
t := time.Now()
u, err := url.Parse(verificationLink)
if err = ed.Eout(err, "parse url"); err != nil {
return err
}
_, err = d.Exec(stmt, userid, u.Host, verificationLink, t)
if err = ed.Eout(err, "add registration"); err != nil {
return err
}
return nil
}