added database initialization

2 files changed, 44 insertions, 0 deletions

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)
+);
+