diff options
| -rw-r--r-- | src/cracha.go | 35 | ||||
| -rw-r--r-- | tests/queries.sql | 35 |
2 files changed, 44 insertions, 26 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( diff --git a/tests/queries.sql b/tests/queries.sql index 6e68db1..85cc5fb 100644 --- a/tests/queries.sql +++ b/tests/queries.sql @@ -8,14 +8,16 @@ email TEXT NOT NULL UNIQUE, salt BLOB NOT NULL UNIQUE, pwhash BLOB NOT NULL - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_confirmation_attempts" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), -- uuid BLOB NOT NULL UNIQUE, user_id INTEGER NOT NULL REFERENCES "cracha_users"(id), token TEXT NOT NULL UNIQUE - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_user_confirmations" ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), @@ -23,34 +25,39 @@ REFERENCES "cracha_users"(id) UNIQUE, attempt_id INTEGER NOT NULL REFERENCES "cracha_confirmation_attempts"(id) UNIQUE - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_user_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), user_id INTEGER NOT NULL REFERENCES "cracha_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 "cracha_tokens" ( -- id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, -- timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), -- uuid BLOB NOT NULL UNIQUE, -- type TEXT NOT NULL - -- ); + -- ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_roles" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL REFERENCES "cracha_users"(id), role TEXT NOT NULL, UNIQUE (user_id, role) - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_role_changes" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), user_id INTEGER NOT NULL REFERENCES "cracha_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 "cracha_sessions" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), @@ -60,21 +67,23 @@ -- revoked_at TEXT, -- revoker_id INTEGER REFERENCES "cracha_users"(id), -- FIXME: add provenance: login, refresh, confirmation, etc. - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_attempts" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), user_id INTEGER REFERENCES "cracha_users"(id), session_id INTEGER REFERENCES "cracha_sessions"(id) - ); + ) STRICT; + CREATE TABLE IF NOT EXISTS "cracha_audit" ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f000000Z', 'now')), 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; -- read: |