From 1f060bf2ef8b3d475b0d37d32bab73e1a601ea11 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Fri, 31 Dec 2021 22:05:18 +0100
Subject: Parameterize site name and domain.

---
 tools/sql/schema.txt | 515 ---------------------------------------------------
 1 file changed, 515 deletions(-)
 delete mode 100644 tools/sql/schema.txt

(limited to 'tools/sql/schema.txt')

diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
deleted file mode 100644
index 419da0d..0000000
--- a/tools/sql/schema.txt
+++ /dev/null
@@ -1,515 +0,0 @@
--- Blacklists --
-
-create table if not exists blacklist_ip ( ip text primary key ) without rowid;
-create table if not exists blacklist_mail ( mail text primary key ) without rowid;
-
--- Titles and roles --
-
-create table if not exists titles (
-	title_id text
-		primary key,
-	title_name text,
-	bgg integer,
-	is_hidden boolean
-) without rowid;
-
-create table if not exists roles (
-	title_id text
-		references titles,
-	role text,
-	unique (title_id, role)
-);
-
--- Users --
-
-create table if not exists logins (
-	sid integer
-		primary key,
-	user_id integer
-		references users
-			on delete cascade,
-	expires real
-);
-
-create table if not exists users (
-	user_id integer
-		primary key,
-	name text
-		unique
-		collate nocase,
-	mail text
-		unique
-		collate nocase,
-	notify boolean
-		default 0,
-	is_banned boolean
-		default 0,
-	ctime timestamp
-		default current_timestamp,
-	password text,
-	salt text,
-	about text
-);
-
-create table if not exists user_last_seen (
-	user_id integer
-		primary key
-		references users
-			on delete cascade,
-	atime timestamp,
-	aip text
-);
-
-create table if not exists tokens (
-	user_id integer
-		primary key
-		references users
-			on delete cascade,
-	token text,
-	time timestamp
-);
-
-create table if not exists last_notified (
-	game_id integer
-		references games
-			on delete cascade,
-	user_id integer
-		references users
-			on delete cascade,
-	time timestamp,
-	primary key (game_id, user_id)
-) without rowid;
-
-drop view if exists user_view;
-create view user_view as
-	select
-		user_id, name, mail, notify
-	from
-		users
-	;
-
-drop view if exists user_login_view;
-create view user_login_view as
-	select
-		user_id, name, mail, notify, password, salt
-	from
-		users
-	;
-
-drop view if exists user_profile_view;
-create view user_profile_view as
-	select
-		user_id, name, mail, notify, ctime, atime, about
-	from
-		users
-		natural left join user_last_seen
-	;
-
--- Messages --
-
-create table if not exists messages (
-	message_id integer
-		primary key,
-	is_deleted_from_inbox boolean
-		default 0,
-	is_deleted_from_outbox boolean
-		default 0,
-	from_id integer
-		references users,
-	to_id integer
-		references users,
-	time timestamp
-		default current_timestamp,
-	is_read boolean
-		default 0,
-	subject text,
-	body text
-);
-
-drop view if exists message_view;
-create view message_view as
-	select
-		messages.*,
-		users_from.name as from_name,
-		users_to.name as to_name
-	from
-		messages
-		left join users as users_from
-			on messages.from_id = users_from.user_id
-		left join users as users_to
-			on messages.to_id = users_to.user_id
-	;
-
-create index if not exists messages_inbox_idx
-	on
-		messages(to_id)
-	where
-		is_deleted_from_inbox = 0
-	;
-
-create index if not exists messages_inbox_unread_idx
-	on
-		messages(to_id)
-	where
-		is_read = 0 and is_deleted_from_inbox = 0
-	;
-
--- Forum --
-
-create table if not exists threads (
-	thread_id integer
-		primary key,
-	author_id integer
-		references users,
-	subject text,
-	is_locked boolean
-		default 0
-);
-
-create table if not exists posts (
-	post_id integer
-		primary key,
-	thread_id integer
-		references threads
-			on delete cascade,
-	author_id integer
-		references users,
-	ctime timestamp
-		default current_timestamp,
-	mtime timestamp
-		default current_timestamp,
-	body text
-);
-
-drop view if exists thread_view;
-create view thread_view as
-	select
-		threads.*,
-		author.name as author_name,
-		(
-			select
-				count(*) - 1
-			from
-				posts
-			where
-				posts.thread_id = threads.thread_id
-		) as replies,
-		(
-			select
-				max(posts.mtime)
-			from
-				posts
-			where
-				posts.thread_id = threads.thread_id
-		) as mtime
-	from
-		threads
-		left join users as author
-			on threads.author_id = author.user_id
-	;
-
-drop view if exists post_view;
-create view post_view as
-	select
-		posts.*,
-		author.name as author_name
-	from
-		posts
-		left join users as author
-			on posts.author_id = author.user_id
-	;
-
-create index if not exists posts_thread_idx on posts(thread_id);
-
--- Games --
-
-create table if not exists games (
-	game_id integer
-		primary key,
-	title_id text
-		references titles,
-	scenario text,
-	options text,
-	owner_id integer
-		references users,
-	ctime timestamp
-		default current_timestamp,
-	is_private boolean
-		default 0,
-	is_random boolean
-		default 0,
-	description text,
-	status integer
-		default 0,
-	result text
-);
-
-create index if not exists games_title_idx on games(title_id);
-create index if not exists games_status_idx on games(status);
-
-create table if not exists game_state (
-	game_id integer
-		primary key
-		references games
-			on delete cascade,
-	mtime timestamp,
-	active text,
-	state text
-);
-
-create table if not exists game_chat (
-	chat_id integer
-		primary key,
-	game_id integer
-		references games
-			on delete cascade,
-	time timestamp
-		default current_timestamp,
-	user_id integer
-		references users,
-	message text
-);
-
-drop view if exists game_chat_view;
-create view game_chat_view as
-	select
-		chat_id, game_id, time, name, message
-	from
-		game_chat
-		natural join users
-	;
-
-create index if not exists game_chat_idx on game_chat(game_id);
-
-create table if not exists game_replay (
-	game_id integer
-		references games
-			on delete cascade,
-	time timestamp
-		default current_timestamp,
-	role text,
-	action text,
-	arguments text
-);
-
-create table if not exists players (
-	game_id integer
-		references games
-			on delete cascade,
-	role text,
-	user_id integer
-		references users,
-	primary key (game_id, role)
-) without rowid;
-
-create index if not exists player_user_idx on players(user_id);
-create index if not exists player_game_user_idx on players(game_id, user_id);
-
-drop view if exists game_view;
-create view game_view as
-	select
-		games.*,
-		titles.title_name,
-		owner.name as owner_name,
-		game_state.mtime,
-		game_state.active
-	from
-		games
-		natural left join game_state
-		natural join titles
-		join users as owner
-			on owner.user_id = games.owner_id
-	;
-
-drop view if exists game_full_view;
-create view game_full_view as
-	select
-		*,
-		(
-			select
-				group_concat(name, ', ')
-			from
-				players
-				natural join users
-			where
-				players.game_id = game_view.game_id
-		) as player_names,
-		(
-			select
-				count(distinct user_id) = 1
-			from
-				players
-			where
-				players.game_id = game_view.game_id
-		) as is_solo
-	from
-		game_view
-	;
-
-drop view if exists opposed_games;
-create view opposed_games as
-	select
-		*
-	from
-		games
-	where
-		status > 0
-		and (
-			select
-				count(distinct user_id) > 1
-			from
-				players
-			where
-				players.game_id = games.game_id
-		)
-	;
-
-drop view if exists your_turn_reminder;
-create view your_turn_reminder as
-	select
-		game_id, role, user_id, name, mail, notify
-	from
-		game_full_view
-		join players using(game_id)
-		join users using(user_id)
-	where
-		status = 1
-		and active in ('All', 'Both', role)
-		and is_solo = 0
-		and notify = 1
-		and datetime('now') > datetime(mtime, '+1 hour')
-	;
-
-drop view if exists your_turn;
-create view your_turn as
-	select
-		game_id, user_id, role
-	from
-		players
-		join games using(game_id)
-		join game_state using(game_id)
-	where
-		status = 1
-		and active in ('All', 'Both', role)
-	;
-
--- Triggers --
-
-drop trigger if exists no_part_on_active_game;
-create trigger no_part_on_active_game before delete on players
-begin
-	select
-		raise(abort, 'Cannot remove players from started games.')
-	where
-		(select status from games where games.game_id = old.game_id) > 0
-	;
-end;
-
-drop trigger if exists no_join_on_active_game;
-create trigger no_join_on_active_game before insert on players
-begin
-	select
-		raise(abort, 'Cannot add players to started games.')
-	where
-		(select status from games where games.game_id = new.game_id) > 0
-	;
-end;
-
-drop trigger if exists must_be_valid_role;
-create trigger must_be_valid_role before insert on players
-begin
-	select
-		raise(abort, 'Invalid role.')
-	where
-		not exists (
-			select
-				1
-			from
-				roles
-			where
-				roles.title_id = (
-					select
-						title_id
-					from
-						games
-					where
-						games.game_id = new.game_id
-				)
-				and roles.role = new.role
-		)
-		and new.role != 'Random 1'
-		and new.role != 'Random 2'
-		and new.role != 'Random 3'
-		and new.role != 'Random 4'
-		and new.role != 'Random 5'
-		and new.role != 'Random 6'
-		and new.role != 'Random 7'
-	;
-end;
-
--- Manual key management if pragma foreign_keys = off
-drop trigger if exists trigger_delete_on_games;
-create trigger trigger_delete_on_games after delete on games
-begin
-	delete from game_state where game_id = old.game_id;
-	delete from game_chat where game_id = old.game_id;
-	delete from game_replay where game_id = old.game_id;
-	delete from last_notified where game_id = old.game_id;
-	delete from players where game_id = old.game_id;
-end;
-
--- Game stats
-
-drop view if exists role_index_view;
-create view role_index_view (t, i, r) as
-	select
-		title_id,
-		row_number() over (partition by title_id),
-		role
-	from
-		roles
-	;
-
-drop view if exists game_stat_view;
-create view game_stat_view as
-	select
-		title_name,
-		null as scenario,
-		null as total,
-		(select r from role_index_view where t=title_id and i=1) as r1,
-		(select r from role_index_view where t=title_id and i=2) as r2,
-		(select r from role_index_view where t=title_id and i=3) as r3,
-		(select r from role_index_view where t=title_id and i=4) as r4,
-		(select r from role_index_view where t=title_id and i=5) as r5,
-		(select r from role_index_view where t=title_id and i=6) as r6,
-		(select r from role_index_view where t=title_id and i=7) as r7
-	from
-		titles
-	where
-		is_hidden = 0
-	union
-	select
-		title_name,
-		scenario,
-		count(*) as total,
-		sum((select i from role_index_view where t=title_id and r=result) = 1) as r1,
-		sum((select i from role_index_view where t=title_id and r=result) = 2) as r2,
-		sum((select i from role_index_view where t=title_id and r=result) = 3) as r3,
-		sum((select i from role_index_view where t=title_id and r=result) = 4) as r4,
-		sum((select i from role_index_view where t=title_id and r=result) = 5) as r5,
-		sum((select i from role_index_view where t=title_id and r=result) = 6) as r6,
-		sum((select i from role_index_view where t=title_id and r=result) = 7) as r7
-	from
-		games
-		natural join titles
-	where
-		is_hidden = 0
-		and status = 2
-		and game_id in (select game_id from opposed_games)
-	group by
-		title_id,
-		scenario
-	order by
-		title_name,
-		total desc nulls first
-	;
-- 
cgit v1.2.3