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`
 62
 63var sqliteMigrations = []string{
 64	"", // migration #0 is reserved for schema initialization
 65}
 66
 67func NewSqliteDB(databaseUrl string, logger *slog.Logger) (*PgsPsqlDB, error) {
 68	var err error
 69	d := &PgsPsqlDB{
 70		Logger: logger,
 71	}
 72	d.Logger.Info("connecting to sqlite", "databaseUrl", databaseUrl)
 73
 74	db, err := SqliteOpen(databaseUrl, logger)
 75	if err != nil {
 76		return nil, err
 77	}
 78
 79	d.Db = db
 80	return d, nil
 81}
 82
 83// Open opens a database connection.
 84func SqliteOpen(dsn string, logger *slog.Logger) (*sqlx.DB, error) {
 85	logger.Info("opening db file", "dsn", dsn)
 86	db, err := sqlx.Connect("sqlite", dsn)
 87	if err != nil {
 88		return nil, err
 89	}
 90
 91	err = sqliteUpgrade(db)
 92	if err != nil {
 93		_ = db.Close()
 94		return nil, err
 95	}
 96
 97	return db, nil
 98}
 99
100func sqliteUpgrade(db *sqlx.DB) error {
101	var version int
102	if err := db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
103		return fmt.Errorf("failed to query schema version: %v", err)
104	}
105
106	if version == len(sqliteMigrations) {
107		return nil
108	} else if version > len(sqliteMigrations) {
109		return fmt.Errorf("(version %d) older than schema (version %d)", len(sqliteMigrations), version)
110	}
111
112	tx, err := db.Beginx()
113	if err != nil {
114		return err
115	}
116	defer func() {
117		_ = tx.Rollback()
118	}()
119
120	if version == 0 {
121		if _, err := tx.Exec(sqliteSchema); err != nil {
122			return fmt.Errorf("failed to initialize schema: %v", err)
123		}
124	} else {
125		for i := version; i < len(sqliteMigrations); i++ {
126			if _, err := tx.Exec(sqliteMigrations[i]); err != nil {
127				return fmt.Errorf("failed to execute migration #%v: %v", i, err)
128			}
129		}
130	}
131
132	// For some reason prepared statements don't work here
133	_, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(sqliteMigrations)))
134	if err != nil {
135		return fmt.Errorf("failed to bump schema version: %v", err)
136	}
137
138	return tx.Commit()
139}