repos / pico

sqlite.go

  1package pgsdb
  2
  3import (
  4	"fmt"
  5	"log/slog"
  6
  7	"github.com/jmoiron/sqlx"
  8	_ "modernc.org/sqlite"
  9)
 10
 11var sqliteSchema = `
 12CREATE TABLE IF NOT EXISTS app_users (
 13	id INTEGER PRIMARY KEY AUTOINCREMENT,
 14	name TEXT NOT NULL,
 15	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 16	UNIQUE(name)
 17);
 18
 19CREATE TABLE IF NOT EXISTS public_keys (
 20	id INTEGER PRIMARY KEY AUTOINCREMENT,
 21	user_id INTEGER NOT NULL,
 22	public_key TEXT NOT NULL UNIQUE,
 23	name TEXT NOT NULL,
 24	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 25	UNIQUE (user_id, public_key),
 26	CONSTRAINT public_keys_user_id_fk
 27		FOREIGN KEY(user_id) REFERENCES app_users(id)
 28		ON DELETE CASCADE
 29		ON UPDATE CASCADE
 30);
 31
 32CREATE TABLE IF NOT EXISTS projects (
 33	id INTEGER PRIMARY KEY AUTOINCREMENT,
 34	user_id INTEGER NOT NULL,
 35	name TEXT NOT NULL,
 36	project_dir TEXT NOT NULL DEFAULT '',
 37	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 38	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 39	acl BLOB DEFAULT '{"data": [], "type": "public"}' NOT NULL,
 40	blocked TEXT NOT NULL DEFAULT '',
 41	UNIQUE (user_id, name),
 42	CONSTRAINT projects_user_id_fk
 43		FOREIGN KEY(user_id) REFERENCES app_users(id)
 44		ON DELETE CASCADE
 45		ON UPDATE CASCADE
 46);
 47
 48CREATE TABLE IF NOT EXISTS feature_flags (
 49	id INTEGER PRIMARY KEY AUTOINCREMENT,
 50	user_id INTEGER NOT NULL,
 51	name TEXT NOT NULL,
 52	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 53	expires_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 54	data BLOB DEFAULT '{}' NOT NULL,
 55	payment_history_id INTEGER,
 56	CONSTRAINT feature_flags_user_id_fk
 57		FOREIGN KEY(user_id) REFERENCES app_users(id)
 58		ON DELETE CASCADE
 59		ON UPDATE CASCADE
 60);
 61
 62CREATE TABLE IF NOT EXISTS form_entries (
 63	id INTEGER PRIMARY KEY AUTOINCREMENT,
 64	user_id INTEGER NOT NULL,
 65	name TEXT NOT NULL,
 66	data BLOB NOT NULL,
 67	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 68	CONSTRAINT form_entries_user_id_fk
 69		FOREIGN KEY(user_id) REFERENCES app_users(id)
 70		ON DELETE CASCADE
 71		ON UPDATE CASCADE
 72);
 73`
 74
 75var sqliteMigrations = []string{
 76	"", // migration #0 is reserved for schema initialization
 77}
 78
 79func NewSqliteDB(databaseUrl string, logger *slog.Logger) (*PgsPsqlDB, error) {
 80	var err error
 81	d := &PgsPsqlDB{
 82		Logger: logger,
 83	}
 84	d.Logger.Info("connecting to sqlite", "databaseUrl", databaseUrl)
 85
 86	db, err := SqliteOpen(databaseUrl, logger)
 87	if err != nil {
 88		return nil, err
 89	}
 90
 91	d.Db = db
 92	return d, nil
 93}
 94
 95// Open opens a database connection.
 96func SqliteOpen(dsn string, logger *slog.Logger) (*sqlx.DB, error) {
 97	logger.Info("opening db file", "dsn", dsn)
 98	db, err := sqlx.Connect("sqlite", dsn)
 99	if err != nil {
100		return nil, err
101	}
102
103	err = sqliteUpgrade(db)
104	if err != nil {
105		_ = db.Close()
106		return nil, err
107	}
108
109	return db, nil
110}
111
112func sqliteUpgrade(db *sqlx.DB) error {
113	var version int
114	if err := db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
115		return fmt.Errorf("failed to query schema version: %v", err)
116	}
117
118	if version == len(sqliteMigrations) {
119		return nil
120	} else if version > len(sqliteMigrations) {
121		return fmt.Errorf("(version %d) older than schema (version %d)", len(sqliteMigrations), version)
122	}
123
124	tx, err := db.Beginx()
125	if err != nil {
126		return err
127	}
128	defer func() {
129		_ = tx.Rollback()
130	}()
131
132	if version == 0 {
133		if _, err := tx.Exec(sqliteSchema); err != nil {
134			return fmt.Errorf("failed to initialize schema: %v", err)
135		}
136	} else {
137		for i := version; i < len(sqliteMigrations); i++ {
138			if _, err := tx.Exec(sqliteMigrations[i]); err != nil {
139				return fmt.Errorf("failed to execute migration #%v: %v", i, err)
140			}
141		}
142	}
143
144	// For some reason prepared statements don't work here
145	_, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(sqliteMigrations)))
146	if err != nil {
147		return fmt.Errorf("failed to bump schema version: %v", err)
148	}
149
150	return tx.Commit()
151}