Changeset 420 in code for trunk

Timestamp:

Oct 24, 2020, 12:52:55 PM (5 years ago)

Author:

contact

Message:

Add id column to User table

We used rowid before, but an explicit ID column is cleaner.

File:

• trunk/db.go (modified) (4 diffs)

trunk/db.go

@@ -62 +62 @@
 const schema = `
 CREATE TABLE User (
-        username VARCHAR(255) PRIMARY KEY,
-        password VARCHAR(255) NOT NULL,
+        id INTEGER PRIMARY KEY,
+        username VARCHAR(255) NOT NULL UNIQUE,
+        password VARCHAR(255),
         admin INTEGER NOT NULL DEFAULT 0
 );
@@ -104 +105 @@
         "ALTER TABLE Network ADD COLUMN sasl_external_key BLOB DEFAULT NULL",
         "ALTER TABLE User ADD COLUMN admin INTEGER NOT NULL DEFAULT 0",
+        `
+                CREATE TABLE UserNew (
+                        id INTEGER PRIMARY KEY,
+                        username VARCHAR(255) NOT NULL UNIQUE,
+                        password VARCHAR(255),
+                        admin INTEGER NOT NULL DEFAULT 0
+                );
+                INSERT INTO UserNew SELECT rowid, username, password, admin FROM User;
+                DROP TABLE User;
+                ALTER TABLE UserNew RENAME TO User;
+        `,
 }
 
@@ -191 +203 @@
         defer db.lock.RUnlock()
 
-        rows, err := db.db.Query("SELECT rowid, username, password, admin FROM User")
+        rows, err := db.db.Query("SELECT id, username, password, admin FROM User")
         if err != nil {
                 return nil, err
@@ -221 +233 @@
 
         var password *string
-        row := db.db.QueryRow("SELECT rowid, password, admin FROM User WHERE username = ?", username)
+        row := db.db.QueryRow("SELECT id, password, admin FROM User WHERE username = ?", username)
         if err := row.Scan(&user.ID, &password, &user.Admin); err != nil {
                 return nil, err