Changeset 255 in code for trunk

Timestamp:

Apr 10, 2020, 8:45:02 PM (5 years ago)

Author:

contact

Message:

Set up DB migration infrastructure

The database is now initialized automatically on first run. The schema
version is stored in SQLite's user_version special field. Migrations are
stored in an array and applied based on the schema version.

Location:

trunk

Files:

• README.md (modified) (1 diff)
• db.go (modified) (3 diffs)
• schema.sql (deleted)

trunk/README.md

@@ -11 +11 @@
 ## Usage
 
-    sqlite3 soju.db <schema.sql
     go run ./cmd/sojuctl create-user <username>
     go run ./cmd/soju

trunk/db.go

@@ -49 +49 @@
 var ErrNoSuchChannel = fmt.Errorf("soju: no such channel")
 
+const schema = `
+CREATE TABLE User (
+        username VARCHAR(255) PRIMARY KEY,
+        password VARCHAR(255) NOT NULL
+);
+
+CREATE TABLE Network (
+        id INTEGER PRIMARY KEY,
+        name VARCHAR(255),
+        user VARCHAR(255) NOT NULL,
+        addr VARCHAR(255) NOT NULL,
+        nick VARCHAR(255) NOT NULL,
+        username VARCHAR(255),
+        realname VARCHAR(255),
+        pass VARCHAR(255),
+        sasl_mechanism VARCHAR(255),
+        sasl_plain_username VARCHAR(255),
+        sasl_plain_password VARCHAR(255),
+        FOREIGN KEY(user) REFERENCES User(username),
+        UNIQUE(user, addr, nick)
+);
+
+CREATE TABLE Channel (
+        id INTEGER PRIMARY KEY,
+        network INTEGER NOT NULL,
+        name VARCHAR(255) NOT NULL,
+        key VARCHAR(255),
+        FOREIGN KEY(network) REFERENCES Network(id),
+        UNIQUE(network, name)
+);
+`
+
+var migrations = []string{
+        "", // migration #0 is reserved for schema initialization
+}
+
 type DB struct {
         lock sync.RWMutex
@@ -55 +91 @@
 
 func OpenSQLDB(driver, source string) (*DB, error) {
-        db, err := sql.Open(driver, source)
-        if err != nil {
-                return nil, err
-        }
-        return &DB{db: db}, nil
+        sqlDB, err := sql.Open(driver, source)
+        if err != nil {
+                return nil, err
+        }
+
+        db := &DB{db: sqlDB}
+        if err := db.upgrade(); err != nil {
+                return nil, err
+        }
+
+        return db, nil
 }
 
@@ -66 +108 @@
         defer db.lock.Unlock()
         return db.Close()
+}
+
+func (db *DB) upgrade() error {
+        db.lock.Lock()
+        defer db.lock.Unlock()
+
+        var version int
+        if err := db.db.QueryRow("PRAGMA user_version").Scan(&version); err != nil {
+                return fmt.Errorf("failed to query schema version: %v", err)
+        }
+
+        if version == len(migrations) {
+                return nil
+        } else if version > len(migrations) {
+                return fmt.Errorf("soju (version %d) older than schema (version %d)", len(migrations), version)
+        }
+
+        tx, err := db.db.Begin()
+        if err != nil {
+                return err
+        }
+        defer tx.Rollback()
+
+        if version == 0 {
+                if _, err := tx.Exec(schema); err != nil {
+                        return fmt.Errorf("failed to initialize schema: %v", err)
+                }
+        } else {
+                for i := version; i < len(migrations); i++ {
+                        if _, err := tx.Exec(migrations[i]); err != nil {
+                                return fmt.Errorf("failed to execute migration #%v: %v", i, err)
+                        }
+                }
+        }
+
+        // For some reason prepared statements don't work here
+        _, err = tx.Exec(fmt.Sprintf("PRAGMA user_version = %d", len(migrations)))
+        if err != nil {
+                return fmt.Errorf("failed to bump schema version: %v", err)
+        }
+
+        return tx.Commit()
 }