From bcaccefc8bea56c99b21a63bcb6b29f358174bf1 Mon Sep 17 00:00:00 2001 From: lonkaars Date: Sun, 3 Jan 2021 15:26:00 +0100 Subject: added database initialization --- database/init_db.sh | 1 + database/init_db.sql | 43 +++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 44 insertions(+) create mode 100755 database/init_db.sh create mode 100644 database/init_db.sql (limited to 'database') diff --git a/database/init_db.sh b/database/init_db.sh new file mode 100755 index 0000000..0c5515b --- /dev/null +++ b/database/init_db.sh @@ -0,0 +1 @@ +sqlite3 database.db < init_db.sql diff --git a/database/init_db.sql b/database/init_db.sql new file mode 100644 index 0000000..cf26f7b --- /dev/null +++ b/database/init_db.sql @@ -0,0 +1,43 @@ +PRAGMA foreign_keys = ON; + +create table users ( + user_id text primary key not null, + username varchar(35) not null, + email text not null, + country text, + password_salt text not null, + password_hash text not null, + registered integer not null, + valid_tokens text, + verified_email boolean not null, + type text not null, + preferences text not null, + avatar text, + presence text +); + +create table games ( + game_id text primary key not null, + parent_game text, + moves text, + player_1_id text not null, + player_2_id text, + outcome text, + timestamp integer, + duration integer, + rating_delta_player_1 integer, + rating_delta_player_2 integer, + ranked boolean, + status text not null, + foreign key(player_1_id) references users(user_id), + foreign key(player_2_id) references users(user_id) +); + +create table social ( + user_id text not null, + friends text, + blocked text, + pending text, + foreign key(user_id) references users(user_id) +); + -- cgit v1.2.3