diff options
Diffstat (limited to 'db')
| -rwxr-xr-x | db/0-create.psql | 16 | ||||
| -rwxr-xr-x | db/0-create.psql-altered | 11 |
2 files changed, 16 insertions, 11 deletions
diff --git a/db/0-create.psql b/db/0-create.psql index 762bc3a..9a9b771 100755 --- a/db/0-create.psql +++ b/db/0-create.psql @@ -1,4 +1,8 @@ -CREATE DATABASE dumpfm; +-- dump.fm schema +-- +-- Run with: +-- createdb dumpfm +-- psql -d dumpfm -f db/0-create.psql CREATE TABLE users ( user_id SERIAL PRIMARY KEY, @@ -17,9 +21,8 @@ CREATE TABLE users ( profile_bg text ); -ALTER TABLE users ADD CONSTRAINT users_nick_constraint UNIQUE (LOWER(nick)); - -CREATE INDEX users_nick_lowercase_idx ON users (lower(nick)); +-- Case-insensitive uniqueness for nicks. +CREATE UNIQUE INDEX users_nick_lowercase_uidx ON users (lower(nick)); CREATE TABLE rooms ( room_id SERIAL PRIMARY KEY, @@ -147,7 +150,7 @@ CREATE TABLE direct_messages ( -- dont add this yet CREATE TABLE avatars ( avatar_id SERIAL PRIMARY KEY, - user_id NOT NULL REFERENCES users, + user_id integer NOT NULL REFERENCES users, created_on timestamp NOT NULL DEFAULT now(), type text, url text, @@ -160,6 +163,5 @@ CREATE TABLE avatars ( -- dont add this yet CREATE TABLE images ( - image_id SERIAL PRIMARY KEY, - + image_id SERIAL PRIMARY KEY ); diff --git a/db/0-create.psql-altered b/db/0-create.psql-altered index cb1469b..68c9f1e 100755 --- a/db/0-create.psql-altered +++ b/db/0-create.psql-altered @@ -1,4 +1,8 @@ -CREATE DATABASE dumpfm; +-- dump.fm schema (altered copy) +-- +-- Run with: +-- createdb dumpfm +-- psql -d dumpfm -f db/0-create.psql-altered CREATE TABLE users ( user_id SERIAL PRIMARY KEY, @@ -17,9 +21,8 @@ CREATE TABLE users ( profile_bg text ); -ALTER TABLE users ADD CONSTRAINT users_nick_constraint UNIQUE (LOWER(nick)); - -CREATE INDEX users_nick_lowercase_idx ON users (lower(nick)); +-- Case-insensitive uniqueness for nicks. +CREATE UNIQUE INDEX users_nick_lowercase_uidx ON users (lower(nick)); CREATE TABLE rooms ( room_id SERIAL PRIMARY KEY, |