summaryrefslogtreecommitdiff
path: root/srv/src/post/sql.go
blob: c768c9a97bde829bf3468f1572ec2bf560dbf951 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
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
}