initial commit

5 files changed, 251 insertions, 0 deletions

diff --git a/src/server/database/database.go b/src/server/database/database.go
new file mode 100644
index 0000000..0b5bb4f
--- /dev/null
+++ b/src/server/database/database.go
@@ -0,0 +1,74 @@
+package database
+
+import (
+	"database/sql"
+	"log"
+
+	// We're using sqlite3 because we want to make deploying the project easy.
+	// (ie, no need to set up a postgres server for a little forum application).
+	_ "github.com/mattn/go-sqlite3"
+)
+
+var db *sql.DB
+
+// Initialises the tables in the database (if they do not exist).
+func setup_db(db *sql.DB) error {
+	// FIXME executing multiple queries with one statement is definitely slower
+	// than a single query with multiple statments. IDK if our sqlite3 driver
+	// supports this, but a rewrite is needed if it does.
+	statements := [...]string{
+		("CREATE TABLE IF NOT EXISTS Users(" +
+			"uid INTEGER PRIMARY KEY AUTOINCREMENT," +
+			"email TEXT NOT NULL UNIQUE," +
+			"username TEXT," +
+			"password_hash TEXT NOT NULL," +
+			"password_salt TEXT NOT NULL);"),
+		("CREATE TABLE IF NOT EXISTS Posts(" +
+			"uid INTEGER PRIMARY KEY AUTOINCREMENT," +
+			"author INTEGER NOT NULL," +
+			"parent INTEGER," +
+			"time INTEGER NOT NULL," +
+			"subreact TEXT NOT NULL," +
+			"title TEXT NOT NULL," +
+			"contents TEXT NOT NULL," +
+			"thumbnail BLOB NOT NULL," +
+			"image BLOB NOT NULL," +
+
+			"FOREIGN KEY (author) REFERENCES Users (uid)," +
+			"FOREIGN KEY (parent) REFERENCES Posts (uid));"),
+	}
+
+	for _, statement := range statements {
+		_, err := db.Exec(statement)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// Call to initialise the database global var.
+func Init(path string) {
+	var err error
+	db, err = sql.Open("sqlite3", path)
+	if err != nil {
+		log.Fatal(err)
+	}
+	err = db.Ping()
+	if err != nil {
+		log.Fatal(err)
+	}
+	err = setup_db(db)
+	if err != nil {
+		log.Fatal(err)
+	}
+}
+
+func GetDb() *sql.DB {
+	return db
+}
+
+// Call to close connection to database.
+func Close() {
+	db.Close()
+}
diff --git a/src/server/database/image.go b/src/server/database/image.go
new file mode 100644
index 0000000..d85063f
--- /dev/null
+++ b/src/server/database/image.go
@@ -0,0 +1,31 @@
+package database
+
+import (
+	"database/sql"
+)
+
+func getThumbnailOrImage(thumbnail bool) string {
+	if thumbnail {
+		return "Posts.thumbnail "
+	}
+	return "Posts.image "
+}
+
+func GetImage(uid int, thumbnail bool) ([]byte, error) {
+	var db = GetDb()
+
+	var image []byte
+	row := db.QueryRow("SELECT "+getThumbnailOrImage(thumbnail)+
+		"FROM Posts "+
+		"WHERE Posts.uid = ?;", uid)
+	if err := row.Scan(&image); err != nil {
+
+		if err == sql.ErrNoRows {
+			return nil, nil
+		}
+
+		return nil, err
+	}
+
+	return image, nil
+}
diff --git a/src/server/database/post.go b/src/server/database/post.go
new file mode 100644
index 0000000..d73344a
--- /dev/null
+++ b/src/server/database/post.go
@@ -0,0 +1,28 @@
+package database
+
+import (
+	"time"
+)
+
+// Writes a post to the database, returns the uid of the new post.
+func WritePost(author int,
+	parent *int,
+	title string,
+	contents string,
+	subreact string,
+	image []byte,
+	thumbnail []byte) (int, error) {
+
+	var db = GetDb()
+
+	var uid int
+	row := db.QueryRow("INSERT INTO Posts "+
+		"(author, parent, time, subreact, title, contents, thumbnail, image) "+
+		"VALUES(?, ?, ?, ?, ?, ?, ?, ?) RETURNING Posts.uid;",
+		author, parent, time.Now().Unix(), subreact, title, contents, thumbnail, image)
+	if err := row.Scan(&uid); err != nil {
+		return 0, err
+	}
+
+	return uid, nil
+}
diff --git a/src/server/database/posts.go b/src/server/database/posts.go
new file mode 100644
index 0000000..033f5ae
--- /dev/null
+++ b/src/server/database/posts.go
@@ -0,0 +1,63 @@
+package database
+
+import (
+	"database/sql"
+)
+
+type Post struct {
+	Uid         int    `json:"uid"`
+	Author      string `json:"author"`
+	TimeUpdated int    `json:"time_updated"`
+	Subreact    string `json:"subreact"`
+	Title       string `json:"title"`
+	Contents    string `json:"contents"`
+}
+
+func GetPosts(subreact string, page int, amount int) ([]Post, error) {
+	var db = GetDb()
+
+	// This gets posts without parents (threads) and its latest updated time,
+	// which could be the post itself.
+    const query = "SELECT Parent.uid, Parent.author, COALESCE(Latest.updated_time, Parent.time) AS bump_time, Parent.subreact, " +
+        "Parent.title, Parent.contents " +
+        "FROM Posts as Parent " +
+        "    LEFT OUTER JOIN ( " +
+        "        SELECT Posts.parent, Posts.uid, MAX(Posts.time) AS updated_time " +
+        "        FROM Posts " +
+        "        WHERE Posts.parent IS NOT NULL " +
+        "        ORDER BY Posts.time " +
+        "    ) AS LATEST " +
+        "    ON Latest.parent = Parent.uid " +
+        "WHERE Parent.subreact = (CASE WHEN (? = \"\") then Parent.subreact ELSE ? END) " +
+        "ORDER BY bump_time DESC " +
+        "LIMIT ? " +
+        "OFFSET ?;"
+
+	rows, err := db.Query(query, subreact, subreact, amount, page*amount)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	posts := make([]Post, 0)
+	for rows.Next() {
+		var post Post
+		if err = rows.Scan(
+			&post.Uid,
+			&post.Author,
+			&post.TimeUpdated,
+			&post.Subreact,
+			&post.Title,
+			&post.Contents); err != nil {
+
+			if err == sql.ErrNoRows {
+				return posts, nil
+			}
+
+			return nil, err
+		}
+		posts = append(posts, post)
+	}
+
+	return posts, nil
+}
diff --git a/src/server/database/user.go b/src/server/database/user.go
new file mode 100644
index 0000000..14530ae
--- /dev/null
+++ b/src/server/database/user.go
@@ -0,0 +1,55 @@
+package database
+
+import (
+	"database/sql"
+)
+
+type User struct {
+	Uid           int
+	Email         string
+	Username      *string
+	Password_hash string
+	Password_salt string
+}
+
+// Gets a user struct from an email, returns nil-nil if the user did not exist.
+func MaybeGetUser(email string) (*User, error) {
+	var db = GetDb()
+
+	row := db.QueryRow(
+		"SELECT Users.uid, Users.email, Users.username, Users.password_hash, Users.password_salt "+
+			"FROM Users "+
+			"WHERE Users.email = ?;", email)
+
+	var user User
+	if err := row.Scan(
+		&user.Uid,
+		&user.Email,
+		&user.Username,
+		&user.Password_hash,
+		&user.Password_salt); err != nil {
+
+		if err != sql.ErrNoRows {
+			return nil, err
+		}
+
+		return nil, nil
+	}
+
+	return &user, nil
+}
+
+// Writes a new user into the database, returns the user's new uid if no error occurred.
+func WriteNewUser(email string, password_hash string, password_salt string) (int, error) {
+	var db = GetDb()
+
+	var uid int
+	row := db.QueryRow("INSERT INTO Users "+
+		"(email, password_hash, password_salt) "+
+		"VALUES(?, ?, ?) RETURNING Users.uid;", email, password_hash, password_salt)
+	if err := row.Scan(&uid); err != nil {
+		return 0, err
+	}
+
+	return uid, nil
+}