summaryrefslogtreecommitdiffstats
path: root/rust/migrations/2018-05-12-001226_init/up.sql
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2019-01-03 17:52:53 -0800
committerBryan Newbold <bnewbold@robocracy.org>2019-01-03 17:52:53 -0800
commitb10ee85c04816f16738ff30b84262a6ab8440307 (patch)
treec0f30a9ad182d9e77e9ab4ee078bc091e7aada3d /rust/migrations/2018-05-12-001226_init/up.sql
parent9de1a1e01c20fc7df06fa09277d6a4f928b81a67 (diff)
downloadfatcat-b10ee85c04816f16738ff30b84262a6ab8440307.tar.gz
fatcat-b10ee85c04816f16738ff30b84262a6ab8440307.zip
better username constraints in SQL
Diffstat (limited to 'rust/migrations/2018-05-12-001226_init/up.sql')
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql4
1 files changed, 3 insertions, 1 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql
index 0d9d81d0..53762f40 100644
--- a/rust/migrations/2018-05-12-001226_init/up.sql
+++ b/rust/migrations/2018-05-12-001226_init/up.sql
@@ -16,7 +16,7 @@ CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE editor (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
- username TEXT NOT NULL UNIQUE, -- TODO: alphanum and length constraints?
+ username TEXT NOT NULL CHECK (username ~* '^[A-Za-z0-9][A-Za-z0-9._-]{2,15}$'), -- UNIQ below
is_admin BOOLEAN NOT NULL DEFAULT false,
is_bot BOOLEAN NOT NULL DEFAULT false,
is_active BOOLEAN NOT NULL DEFAULT true,
@@ -26,6 +26,8 @@ CREATE TABLE editor (
active_editgroup_id UUID -- REFERENCES( editgroup(id) via ALTER below
);
+-- case-insensitive UNIQ index on username
+CREATE UNIQUE INDEX editor_username_uniq_idx on editor(lower(username));
CREATE INDEX active_editgroup_idx ON editor(active_editgroup_id);
CREATE INDEX editor_username_idx ON editor(username);