diff options
| author | Jan Tuomi <jans.tuomi@gmail.com> | 2024-08-19 16:35:50 +0300 |
|---|---|---|
| committer | Jan Tuomi <jan@jantuomi.fi> | 2024-12-21 19:43:13 +0200 |
| commit | 6cabb205ea995a95f2211896f738f226fd27f7bb (patch) | |
| tree | f46a70cc331fc738d59acbc19a5ad9b0f8e6a031 /migrations/0000_initial.sql | |
| parent | 8c25a6f240d92f85a370df684da1d485829a894b (diff) | |
Migrate to SQLite3
Diffstat (limited to 'migrations/0000_initial.sql')
| -rw-r--r-- | migrations/0000_initial.sql | 53 |
1 files changed, 44 insertions, 9 deletions
diff --git a/migrations/0000_initial.sql b/migrations/0000_initial.sql index 8e9f1ae..a9f11c8 100644 --- a/migrations/0000_initial.sql +++ b/migrations/0000_initial.sql @@ -1,28 +1,63 @@ -CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; +PRAGMA journal_mode = WAL; +PRAGMA busy_timeout = 5000; +PRAGMA synchronous = NORMAL; +PRAGMA cache_size = 1000000000; +PRAGMA foreign_keys = true; +PRAGMA temp_store = memory; CREATE TABLE IF NOT EXISTS polls ( - id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), + id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4))) || '-' || + substr(lower(hex(randomblob(2))), 1, 4) || '-' || + substr('4' || substr(lower(hex(randomblob(2))), 2, 3), 1, 4) || '-' || + substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' || + lower(hex(randomblob(6)))), title TEXT NOT NULL, description TEXT, - pub_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, + pub_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, author_name TEXT NOT NULL, author_email TEXT, - manage_code uuid DEFAULT uuid_generate_v4() + manage_code TEXT DEFAULT (lower(hex(randomblob(4))) || '-' || + substr(lower(hex(randomblob(2))), 1, 4) || '-' || + substr('4' || substr(lower(hex(randomblob(2))), 2, 3), 1, 4) || '-' || + substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' || + lower(hex(randomblob(6)))), + whole_day BOOLEAN NOT NULL ); CREATE TABLE IF NOT EXISTS choices ( - id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), - poll_id uuid NOT NULL, + id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4))) || '-' || + substr(lower(hex(randomblob(2))), 1, 4) || '-' || + substr('4' || substr(lower(hex(randomblob(2))), 2, 3), 1, 4) || '-' || + substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' || + lower(hex(randomblob(6)))), + poll_id TEXT NOT NULL, start_datetime TIMESTAMP NOT NULL, end_datetime TIMESTAMP NOT NULL, FOREIGN KEY (poll_id) REFERENCES polls (id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS votes ( - id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), - poll_id uuid NOT NULL, - choice_id uuid NOT NULL, + id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4))) || '-' || + substr(lower(hex(randomblob(2))), 1, 4) || '-' || + substr('4' || substr(lower(hex(randomblob(2))), 2, 3), 1, 4) || '-' || + substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' || + lower(hex(randomblob(6)))), + manage_code TEXT DEFAULT (lower(hex(randomblob(4))) || '-' || + substr(lower(hex(randomblob(2))), 1, 4) || '-' || + substr('4' || substr(lower(hex(randomblob(2))), 2, 3), 1, 4) || '-' || + substr(hex((random() & 0x3fff) | 0x8000), 1, 4) || '-' || + lower(hex(randomblob(6)))), + poll_id TEXT NOT NULL, + choice_id TEXT NOT NULL, voter_name TEXT NOT NULL, + value INTEGER NOT NULL, + UNIQUE (poll_id, choice_id, voter_name), FOREIGN KEY (choice_id) REFERENCES choices (id) ON DELETE CASCADE, FOREIGN KEY (poll_id) REFERENCES polls (id) ON DELETE CASCADE ); + +CREATE INDEX IF NOT EXISTS idx_votes_poll_id_voter_name ON votes (poll_id, voter_name); +CREATE INDEX IF NOT EXISTS idx_choices_poll_id_start_datetime ON choices (poll_id, start_datetime); +CREATE INDEX IF NOT EXISTS idx_votes_choice_id ON votes (choice_id); +CREATE INDEX IF NOT EXISTS idx_votes_manage_code ON votes (manage_code); +CREATE INDEX IF NOT EXISTS idx_polls_manage_code_pub_date ON polls (manage_code, pub_date); |