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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
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
)`,
},
},
{
Id: "3",
Up: []string{
`ALTER TABLE post_drafts RENAME description TO description_old`,
`ALTER TABLE post_drafts ADD COLUMN description TEXT`,
`UPDATE post_drafts AS pd SET description=pd.description_old`,
`ALTER TABLE post_drafts DROP COLUMN description_old`,
`ALTER TABLE posts RENAME description TO description_old`,
`ALTER TABLE posts ADD COLUMN description TEXT`,
`UPDATE posts AS p SET description=p.description_old`,
`ALTER TABLE posts DROP COLUMN description_old`,
},
},
{
Id: "4",
Up: []string{
`ALTER TABLE post_drafts ADD COLUMN format TEXT DEFAULT 'md'`,
`ALTER TABLE posts ADD COLUMN format TEXT DEFAULT 'md'`,
},
},
}}
// 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
}
|
