initial commit

4 files changed, 169 insertions, 0 deletions

diff --git a/database/conn.go b/database/conn.go
new file mode 100644
index 0000000..be27586
--- /dev/null
+++ b/database/conn.go
@@ -0,0 +1,17 @@
+package database
+
+import (
+	"database/sql"
+	_ "github.com/mattn/go-sqlite3"
+	"log"
+)
+
+func MakeConn(databasePath *string) *sql.DB {
+	log.Println("opening database at", *databasePath, "with foreign keys enabled")
+	dbConn, err := sql.Open("sqlite3", *databasePath+"?_foreign_keys=on")
+	if err != nil {
+		panic(err)
+	}
+
+	return dbConn
+}
diff --git a/database/frens.go b/database/frens.go
new file mode 100644
index 0000000..b8b72ad
--- /dev/null
+++ b/database/frens.go
@@ -0,0 +1,33 @@
+package database
+
+import (
+	"database/sql"
+	"log"
+)
+
+type Fren struct {
+	Id   string
+	Name string
+}
+
+func FindFren(dbConn *sql.DB, id string) (*Fren, error) {
+	row := dbConn.QueryRow(`SELECT id, name FROM frens WHERE id = ?;`, id)
+	var fren Fren
+	err := row.Scan(&fren.Id, &fren.Name)
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+	return &fren, nil
+}
+
+func FindFrenByName(dbConn *sql.DB, name string) (*Fren, error) {
+	row := dbConn.QueryRow(`SELECT id, name FROM frens WHERE name = ?;`, name)
+	var fren Fren
+	err := row.Scan(&fren.Id, &fren.Name)
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+	return &fren, nil
+}
diff --git a/database/messages.go b/database/messages.go
new file mode 100644
index 0000000..7646b50
--- /dev/null
+++ b/database/messages.go
@@ -0,0 +1,49 @@
+package database
+
+import (
+	"database/sql"
+	"time"
+)
+
+type Message struct {
+	Id       string
+	FrenId   string
+	Message  string
+	Time     time.Time
+	FrenSent bool
+}
+
+type ListMessageQuery struct {
+	FrenId string
+	Before time.Time
+	Limit  int
+}
+
+func ListMessages(dbConn *sql.DB, query ListMessageQuery) ([]Message, error) {
+	rows, err := dbConn.Query(`SELECT id, fren_id, message, time, fren_sent FROM messages WHERE fren_id = ? AND time < ? ORDER BY time ASC LIMIT ?;`, query.FrenId, query.Before, query.Limit)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	messages := []Message{}
+	for rows.Next() {
+		var message Message
+		err := rows.Scan(&message.Id, &message.FrenId, &message.Message, &message.Time, &message.FrenSent)
+		if err != nil {
+			return nil, err
+		}
+		messages = append(messages, message)
+	}
+
+	return messages, nil
+}
+
+func SaveMessage(db *sql.DB, message *Message) (*Message, error) {
+	_, err := db.Exec("INSERT OR REPLACE INTO messages (id, fren_id, message, time, fren_sent) VALUES (?, ?, ?, ?, ?)", message.Id, message.FrenId, message.Message, message.Time, message.FrenSent)
+
+	if err != nil {
+		return nil, err
+	}
+	return message, nil
+}
diff --git a/database/migrate.go b/database/migrate.go
new file mode 100644
index 0000000..a6b63f8
--- /dev/null
+++ b/database/migrate.go
@@ -0,0 +1,70 @@
+package database
+
+import (
+	"log"
+
+	"database/sql"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+type Migrator func(*sql.DB) (*sql.DB, error)
+
+func AddFriends(dbConn *sql.DB) (*sql.DB, error) {
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS frens (
+           id TEXT PRIMARY KEY,
+           name TEXT
+        );`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	log.Println("creating unique index on frens table")
+	_, err = dbConn.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_frens_name ON frens (name);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	return dbConn, nil
+}
+
+func AddMessages(dbConn *sql.DB) (*sql.DB, error) {
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS messages (
+           id TEXT PRIMARY KEY,
+           fren_id TEXT,
+           message TEXT,
+           time TIMESTAMP,
+           fren_sent BOOLEAN NOT NULL CHECK (fren_sent IN (0, 1)),
+           FOREIGN KEY (fren_id) REFERENCES frens (id) ON DELETE CASCADE
+        );`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	log.Println("creating time and fren_id index on message table")
+	_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_message_timestamp ON messages (time);`)
+	_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_message_fren ON messages (fren_id);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	return dbConn, nil
+}
+
+func Migrate(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating database")
+
+	migrations := []Migrator{
+		AddFriends,
+		AddMessages,
+	}
+
+	for _, migration := range migrations {
+		dbConn, err := migration(dbConn)
+		if err != nil {
+			return dbConn, err
+		}
+	}
+
+	return dbConn, nil
+}