From d25ec27fb1c3df175e1b98af1fdc26881d68a1ab Mon Sep 17 00:00:00 2001
From: Elizabeth Hunt <elizabeth.hunt@simponic.xyz>
Date: Sun, 5 Jan 2025 16:39:13 -0800
Subject: initial commit by simponic-infra

---
 database/conn.go    | 17 +++++++++++++++++
 database/migrate.go | 46 ++++++++++++++++++++++++++++++++++++++++++++++
 database/updates.go | 44 ++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 107 insertions(+)
 create mode 100644 database/conn.go
 create mode 100644 database/migrate.go
 create mode 100644 database/updates.go

(limited to 'database')

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/migrate.go b/database/migrate.go
new file mode 100644
index 0000000..06213dd
--- /dev/null
+++ b/database/migrate.go
@@ -0,0 +1,46 @@
+package database
+
+import (
+	"log"
+
+	"database/sql"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+type Migrator func(*sql.DB) (*sql.DB, error)
+
+func AddWhoisUpdates(dbConn *sql.DB) (*sql.DB, error) {
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS updates (
+           name TEXT,
+           time TIMESTAMP
+        );`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	log.Println("creating time index on updates table")
+	_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_update_timestamp ON updates (time);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	return dbConn, nil
+}
+
+func Migrate(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating database")
+
+	migrations := []Migrator{
+		AddWhoisUpdates,
+	}
+
+	for _, migration := range migrations {
+		dbConn, err := migration(dbConn)
+		if err != nil {
+			return dbConn, err
+		}
+	}
+
+	return dbConn, nil
+}
diff --git a/database/updates.go b/database/updates.go
new file mode 100644
index 0000000..3021334
--- /dev/null
+++ b/database/updates.go
@@ -0,0 +1,44 @@
+package database
+
+import (
+	"database/sql"
+	"time"
+)
+
+type Update struct {
+	Name string
+	Time time.Time
+}
+
+type ListUpdatesQuery struct {
+	Limit int
+}
+
+func ListUpdates(dbConn *sql.DB, query ListUpdatesQuery) ([]Update, error) {
+	rows, err := dbConn.Query(`SELECT name, time FROM updates ORDER BY time DESC LIMIT ?;`, query.Limit)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	updates := []Update{}
+	for rows.Next() {
+		var update Update
+		err := rows.Scan(&update.Name, &update.Time)
+		if err != nil {
+			return nil, err
+		}
+		updates = append(updates, update)
+	}
+
+	return updates, nil
+}
+
+func SaveUpdate(db *sql.DB, update *Update) (*Update, error) {
+	_, err := db.Exec("INSERT INTO updates (time, name) VALUES (?, ?)", update.Time, update.Name)
+
+	if err != nil {
+		return nil, err
+	}
+	return update, nil
+}
-- 
cgit v1.2.3-70-g09d2