1 files changed, 126 insertions, 0 deletions
diff --git a/src/post/sql.go b/src/post/sql.go
new file mode 100644
index 0000000..c768c9a
--- /dev/null
+++ b/src/post/sql.go
@@ -0,0 +1,126 @@
+package post
+
+import (
+	"database/sql"
+	"fmt"
+	"path"
+
+	"github.com/mediocregopher/blog.mediocregopher.com/srv/cfg"
+	migrate "github.com/rubenv/sql-migrate"
+
+	_ "github.com/mattn/go-sqlite3" // we need dis
+)
+
+var migrations = &migrate.MemoryMigrationSource{Migrations: []*migrate.Migration{
+	{
+		Id: "1",
+		Up: []string{
+			`CREATE TABLE posts (
+				id          TEXT NOT NULL PRIMARY KEY,
+				title       TEXT NOT NULL,
+				description TEXT NOT NULL,
+				series      TEXT,
+
+				published_at    INTEGER NOT NULL,
+				last_updated_at INTEGER,
+
+				body TEXT NOT NULL
+			)`,
+
+			`CREATE TABLE post_tags (
+				post_id TEXT NOT NULL,
+				tag     TEXT NOT NULL,
+				UNIQUE(post_id, tag)
+			)`,
+
+			`CREATE TABLE assets (
+				id   TEXT NOT NULL PRIMARY KEY,
+				body BLOB NOT NULL
+			)`,
+		},
+	},
+	{
+		Id: "2",
+		Up: []string{
+			`CREATE TABLE post_drafts (
+				id          TEXT NOT NULL PRIMARY KEY,
+				title       TEXT NOT NULL,
+				description TEXT NOT NULL,
+				tags        TEXT,
+				series      TEXT,
+				body        TEXT NOT NULL
+			)`,
+		},
+	},
+}}
+
+// SQLDB is a sqlite3 database which can be used by storage interfaces within
+// this package.
+type SQLDB struct {
+	db *sql.DB
+}
+
+// NewSQLDB initializes and returns a new sqlite3 database for storage
+// intefaces. The db will  be created within the given data directory.
+func NewSQLDB(dataDir cfg.DataDir) (*SQLDB, error) {
+
+	path := path.Join(dataDir.Path, "post.sqlite3")
+
+	db, err := sql.Open("sqlite3", path)
+	if err != nil {
+		return nil, fmt.Errorf("opening sqlite file at %q: %w", path, err)
+	}
+
+	if _, err := migrate.Exec(db, "sqlite3", migrations, migrate.Up); err != nil {
+		return nil, fmt.Errorf("running migrations: %w", err)
+	}
+
+	return &SQLDB{db}, nil
+}
+
+// NewSQLDB is like NewSQLDB, but the database will be initialized in memory.
+func NewInMemSQLDB() *SQLDB {
+
+	db, err := sql.Open("sqlite3", ":memory:")
+	if err != nil {
+		panic(fmt.Errorf("opening sqlite in memory: %w", err))
+	}
+
+	if _, err := migrate.Exec(db, "sqlite3", migrations, migrate.Up); err != nil {
+		panic(fmt.Errorf("running migrations: %w", err))
+	}
+
+	return &SQLDB{db}
+}
+
+// Close cleans up loose resources being held by the db.
+func (db *SQLDB) Close() error {
+	return db.db.Close()
+}
+
+func (db *SQLDB) withTx(cb func(*sql.Tx) error) error {
+
+	tx, err := db.db.Begin()
+
+	if err != nil {
+		return fmt.Errorf("starting transaction: %w", err)
+	}
+
+	if err := cb(tx); err != nil {
+
+		if rollbackErr := tx.Rollback(); rollbackErr != nil {
+			return fmt.Errorf(
+				"rolling back transaction: %w (original error: %v)",
+				rollbackErr, err,
+			)
+		}
+
+		return fmt.Errorf("performing transaction: %w (rolled back)", err)
+	}
+
+	if err := tx.Commit(); err != nil {
+		return fmt.Errorf("committing transaction: %w", err)
+	}
+
+	return nil
+}