diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/0000_initial.sql | 53 | ||||
| -rw-r--r-- | migrations/0001_indices.sql | 3 | ||||
| -rw-r--r-- | migrations/0002_vote_value.sql | 2 | ||||
| -rw-r--r-- | migrations/0003_whole_day_poll.sql | 1 | ||||
| -rw-r--r-- | migrations/0004_vote_manage_code.sql | 1 | ||||
| -rw-r--r-- | migrations/0005_indices.sql | 4 |
6 files changed, 44 insertions, 20 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); diff --git a/migrations/0001_indices.sql b/migrations/0001_indices.sql deleted file mode 100644 index 42007ee..0000000 --- a/migrations/0001_indices.sql +++ /dev/null @@ -1,3 +0,0 @@ -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); diff --git a/migrations/0002_vote_value.sql b/migrations/0002_vote_value.sql deleted file mode 100644 index 94f7afb..0000000 --- a/migrations/0002_vote_value.sql +++ /dev/null @@ -1,2 +0,0 @@ -ALTER TABLE votes ADD COLUMN IF NOT EXISTS value INTEGER NOT NULL DEFAULT 1; -ALTER TABLE votes ALTER COLUMN value DROP DEFAULT; diff --git a/migrations/0003_whole_day_poll.sql b/migrations/0003_whole_day_poll.sql deleted file mode 100644 index e532d0c..0000000 --- a/migrations/0003_whole_day_poll.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE polls ADD COLUMN IF NOT EXISTS whole_day BOOLEAN NOT NULL DEFAULT FALSE; diff --git a/migrations/0004_vote_manage_code.sql b/migrations/0004_vote_manage_code.sql deleted file mode 100644 index 69ce38f..0000000 --- a/migrations/0004_vote_manage_code.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE votes ADD COLUMN manage_code uuid DEFAULT uuid_generate_v4(); diff --git a/migrations/0005_indices.sql b/migrations/0005_indices.sql deleted file mode 100644 index 9f28ffb..0000000 --- a/migrations/0005_indices.sql +++ /dev/null @@ -1,4 +0,0 @@ -ALTER TABLE votes ADD CONSTRAINT idx_votes_unique_voter_name UNIQUE (poll_id, choice_id, voter_name); - -CREATE INDEX idx_votes_manage_code ON votes (manage_code); -CREATE INDEX idx_polls_manage_code_pub_date ON polls (manage_code, pub_date); |