From b10ee85c04816f16738ff30b84262a6ab8440307 Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Thu, 3 Jan 2019 17:52:53 -0800 Subject: better username constraints in SQL --- rust/migrations/2018-05-12-001226_init/up.sql | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) 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); -- cgit v1.2.3