package post import ( "database/sql" "fmt" "path" "dev.mediocregopher.com/mediocre-blog.git/src/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 { *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() } // WithTx initializes a transaction, runs the callback using it, and either // commits or rolls it back depending on if the callback returns an error. 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 }