Skip to content

Instantly share code, notes, and snippets.

@afarber

afarber/words.sql

Last active October 31, 2016 12:45
Show Gist options
  • Save afarber/c40b9fc5447335db7d24 to your computer and use it in GitHub Desktop.
Save afarber/c40b9fc5447335db7d24 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
words.sql
DROP TABLE IF EXISTS words_scores;
DROP TABLE IF EXISTS words_chat;
DROP TABLE IF EXISTS words_notes;
DROP TABLE IF EXISTS words_moves;
DROP TABLE IF EXISTS words_games;
DROP TABLE IF EXISTS words_social;
DROP TABLE IF EXISTS words_users;
DROP TABLE IF EXISTS words_nouns;
DROP TABLE IF EXISTS words_verbs;
DROP TABLE IF EXISTS words_values;
DROP TABLE IF EXISTS words_boards;
DROP TYPE IF EXISTS words_action;
CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign', 'ban');
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip inet NOT NULL,
vip_until timestamptz,
grand_until timestamptz,
banned_until timestamptz,
banned_reason varchar(255) CHECK (LENGTH(banned_reason) > 0),
win integer NOT NULL CHECK (win >= 0),
loss integer NOT NULL CHECK (loss >= 0),
draw integer NOT NULL CHECK (draw >= 0),
elo integer NOT NULL CHECK (elo >= 0),
medals integer NOT NULL CHECK (medals >= 0),
green integer NOT NULL CHECK (green >= 0),
red integer NOT NULL CHECK (red >= 0),
coins integer NOT NULL
);
CREATE TABLE words_social (
sid varchar(255) NOT NULL,
social integer NOT NULL CHECK (0 <= social AND social <= 6),
female integer NOT NULL CHECK (female = 0 OR female = 1),
given varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo varchar(255) CHECK (photo ~* '^https?://...'),
place varchar(255),
stamp integer NOT NULL,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_nouns (
word varchar(255) PRIMARY KEY CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я' AND
word !~ 'Ц[ЮЯ]'),
hashed varchar(32) NOT NULL
);
CREATE TABLE words_verbs (
word varchar(255) PRIMARY KEY CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я' AND
word !~ 'Ц[ЮЯ]' AND
(word ~ '[ТЧ]ЬСЯ$' OR
word ~ '[ТЧ]Ь$' OR
word ~ 'ТИ$')),
hashed varchar(32) NOT NULL
);
CREATE TABLE words_values (
letter varchar(1) PRIMARY KEY CHECK (letter ~ '^[*А-Я]$'),
value integer CHECK (value >= 0 AND value <= 15)
);
INSERT INTO words_values (letter, value) VALUES
('*', 0),
('А', 1),
('Б', 3),
('В', 2),
('Г', 3),
('Д', 2),
('Е', 1),
('Ж', 5),
('З', 5),
('И', 1),
('Й', 2),
('К', 2),
('Л', 2),
('М', 2),
('Н', 1),
('О', 1),
('П', 2),
('Р', 2),
('С', 2),
('Т', 2),
('У', 3),
('Ф', 10),
('Х', 5),
('Ц', 10),
('Ч', 5),
('Ш', 10),
('Щ', 10),
('Ъ', 15),
('Ы', 5),
('Ь', 5),
('Э', 10),
('Ю', 10),
('Я', 3);
CREATE TABLE words_boards (
bid integer PRIMARY KEY CHECK (bid > 0),
mult varchar[15][15] NOT NULL
);
INSERT INTO words_boards (bid, mult) VALUES
(1, ARRAY[
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, 'TW', NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ],
[ NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL ],
[ NULL, NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, NULL ],
[ 'DL', NULL, NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, NULL, 'DL' ],
[ NULL, NULL, NULL, NULL, 'TL', NULL, NULL, NULL, NULL, NULL, 'TL', NULL, NULL, NULL, NULL ],
[ NULL, 'DW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DW', NULL ],
[ NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL ],
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ],
[ NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL ],
[ NULL, 'DW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DW', NULL ],
[ NULL, NULL, NULL, NULL, 'TL', NULL, NULL, NULL, NULL, NULL, 'TL', NULL, NULL, NULL, NULL ],
[ 'DL', NULL, NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, NULL, 'DL' ],
[ NULL, NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, NULL ],
[ NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL ],
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, 'TW', NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ]
]),
(2, ARRAY[
[ NULL, NULL, NULL, 'TW', NULL, NULL, 'TL', NULL, 'TL', NULL, NULL, 'TW', NULL, NULL, NULL ],
[ NULL, NULL, 'DL', NULL, NULL, 'DW', NULL, NULL, NULL, 'DW', NULL, NULL, 'DL', NULL, NULL ],
[ NULL, 'DL', NULL, NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, NULL, 'DL', NULL ],
[ 'TW', NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, 'TW' ],
[ NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ 'TL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'TL' ],
[ NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL ],
[ 'TL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'TL' ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL ],
[ 'TW', NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, 'TW' ],
[ NULL, 'DL', NULL, NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, NULL, 'DL', NULL ],
[ NULL, NULL, 'DL', NULL, NULL, 'DW', NULL, NULL, NULL, 'DW', NULL, NULL, 'DL', NULL, NULL ],
[ NULL, NULL, NULL, 'TW', NULL, NULL, 'TL', NULL, 'TL', NULL, NULL, 'TW', NULL, NULL, NULL ]
]),
(3, ARRAY[
[ NULL, NULL, 'TW', NULL, 'TL', NULL, NULL, NULL, NULL, NULL, 'TL', NULL, 'TW', NULL, NULL ],
[ NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL ],
[ 'TW', NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, 'TW' ],
[ NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL ],
[ 'TL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'TL' ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ NULL, NULL, 'TL', NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, 'TL', NULL, NULL ],
[ NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL ],
[ NULL, NULL, 'TL', NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, 'TL', NULL, NULL ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ 'TL', NULL, NULL, NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, NULL, NULL, 'TL' ],
[ NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL ],
[ 'TW', NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, 'TW' ],
[ NULL, 'TL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'TL', NULL ],
[ NULL, NULL, 'TW', NULL, 'TL', NULL, NULL, NULL, NULL, NULL, 'TL', NULL, 'TW', NULL, NULL ]
]),
(4, ARRAY[
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, 'TW', NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ],
[ NULL, NULL, 'DW', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DW', NULL, NULL ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ 'DL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'DL' ],
[ NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL ],
[ NULL, NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, NULL ],
[ NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL ],
[ 'TW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'TW' ],
[ NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL ],
[ NULL, NULL, 'TL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'TL', NULL, NULL ],
[ NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL ],
[ 'DL', NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL, 'DL' ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ NULL, NULL, 'DW', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DW', NULL, NULL ],
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, 'TW', NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ]
]),
(5, ARRAY[
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, 'TW', NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ NULL, NULL, 'DW', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DW', NULL, NULL ],
[ 'DL', NULL, NULL, 'DW', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'DW', NULL, NULL, 'DL' ],
[ NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL ],
[ NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL ],
[ NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL ],
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ],
[ NULL, NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DL', NULL, NULL ],
[ NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL ],
[ NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL, NULL, 'DW', NULL, NULL, NULL, NULL ],
[ 'DL', NULL, NULL, 'DW', NULL, NULL, NULL, 'DL', NULL, NULL, NULL, 'DW', NULL, NULL, 'DL' ],
[ NULL, NULL, 'DW', NULL, NULL, NULL, 'DL', NULL, 'DL', NULL, NULL, NULL, 'DW', NULL, NULL ],
[ NULL, 'DW', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'TL', NULL, NULL, NULL, 'DW', NULL ],
[ 'TW', NULL, NULL, 'DL', NULL, NULL, NULL, 'TW', NULL, NULL, NULL, 'DL', NULL, NULL, 'TW' ]
]);
\i words_hash.sql
CREATE OR REPLACE FUNCTION words_trigger()
RETURNS TRIGGER AS
$func$
BEGIN
NEW.hashed := words_hash(NEW.word);
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
CREATE TRIGGER words_nouns_trigger
BEFORE INSERT OR UPDATE ON words_nouns
FOR EACH ROW EXECUTE PROCEDURE words_trigger();
CREATE TRIGGER words_verbs_trigger
BEFORE INSERT OR UPDATE ON words_verbs
FOR EACH ROW EXECUTE PROCEDURE words_trigger();
-- INSERT INTO words_nouns (word) VALUES ('АБВ'), ('АБВГДЕ'), ('ЭКОНОМИЯ');
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
finished timestamptz,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
score1 integer NOT NULL CHECK (score1 >= 0),
score2 integer NOT NULL CHECK (score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);
CREATE TABLE words_chat (
cid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
-- delivered timestamptz,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
msg varchar NOT NULL
);
CREATE TABLE words_notes (
nid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
delivered timestamptz,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
msg varchar NOT NULL
);
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score >= 0)
);
CREATE TABLE words_scores (
mid integer NOT NULL REFERENCES words_moves ON DELETE CASCADE,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
word varchar NOT NULL CHECK(word ~ '^[А-Я]{2,}$'),
score integer NOT NULL CHECK(score >= 0)
);
\i words_shuffle.sql
\i words_paying_user.sql
\i words_valid_user.sql
\i words_valid_tile.sql
\i words_word_mult.sql
\i words_letter_mult.sql
\i words_merge_users.sql
\i words_join_new_game.sql
\i words_swap_game.sql
\i words_check_positions.sql
\i words_check_words.sql
\i words_play_game.sql
\i words_skip_game.sql
\i words_resign_game.sql
\i words_get_games.sql
\i words_get_opponent.sql
\i words_get_chat.sql
\i words_ban_user.sql
-- print commands for dropping all stored functions in this database
SELECT 'DROP FUNCTION IF EXISTS ' || proname || '(' || oidvectortypes(proargtypes) || ') CASCADE;'
AS COPY_PASTE_TO_DROP_METHODS_BELOW
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'public' ORDER BY proname;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment