diff options
Diffstat (limited to 'src/cracha.go')
| -rw-r--r-- | src/cracha.go | 35 |
1 files changed, 22 insertions, 13 deletions
diff --git a/src/cracha.go b/src/cracha.go index 5547d6e..e9ef266 100644 --- a/src/cracha.go +++ b/src/cracha.go @@ -177,14 +177,16 @@ func createTablesSQL(prefix string) queryT { email TEXT NOT NULL UNIQUE, salt BLOB NOT NULL UNIQUE, pwhash BLOB NOT NULL - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_confirmation_attempts" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), -- uuid BLOB NOT NULL UNIQUE, user_id INTEGER NOT NULL REFERENCES "%s_users"(id), token TEXT NOT NULL UNIQUE - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_user_confirmations" ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), @@ -192,34 +194,39 @@ func createTablesSQL(prefix string) queryT { REFERENCES "%s_users"(id) UNIQUE, attempt_id INTEGER NOT NULL REFERENCES "%s_confirmation_attempts"(id) UNIQUE - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_user_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), user_id INTEGER NOT NULL REFERENCES "%s_users"(id), attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + -- CREATE TABLE IF NOT EXISTS "%s_tokens" ( -- id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, -- timestamp TEXT NOT NULL DEFAULT (%s), -- uuid BLOB NOT NULL UNIQUE, -- type TEXT NOT NULL - -- ); + -- ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_roles" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL REFERENCES "%s_users"(id), role TEXT NOT NULL, UNIQUE (user_id, role) - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_role_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), user_id INTEGER NOT NULL REFERENCES "%s_roles"(id), role TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_sessions" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), @@ -229,21 +236,23 @@ func createTablesSQL(prefix string) queryT { -- revoked_at TEXT, -- revoker_id INTEGER REFERENCES "%s_users"(id), -- FIXME: add provenance: login, refresh, confirmation, etc. - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_attempts" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), user_id INTEGER REFERENCES "%s_users"(id), session_id INTEGER REFERENCES "%s_sessions"(id) - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "%s_audit" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (%s), uuid BLOB NOT NULL UNIQUE, attribute TEXT NOT NULL, value TEXT NOT NULL, - op BOOLEAN NOT NULL - ); + op INT NOT NULL CHECK(op IN (0, 1)) + ) STRICT; ` return queryT{ write: fmt.Sprintf( |