From 396f302937cc83c8cb50e4bba64b67f4b76b7c13 Mon Sep 17 00:00:00 2001 From: Kevin J Hoerr Date: Sun, 26 Aug 2018 22:04:41 -0400 Subject: Add Diesel schema and initial migration SQL files --- .gitignore | 2 - .../00000000000000_diesel_initial_setup/down.sql | 11 +++ .../00000000000000_diesel_initial_setup/up.sql | 96 +++++++++++++++++++ src/main.rs | 4 +- src/schema.rs | 66 +++++++++++++ src/util/fresh.sql | 103 --------------------- 6 files changed, 176 insertions(+), 106 deletions(-) create mode 100644 migrations/00000000000000_diesel_initial_setup/down.sql create mode 100644 migrations/00000000000000_diesel_initial_setup/up.sql create mode 100644 src/schema.rs delete mode 100644 src/util/fresh.sql diff --git a/.gitignore b/.gitignore index 5f84f27..9fe3b61 100644 --- a/.gitignore +++ b/.gitignore @@ -3,9 +3,7 @@ !.env.sample Cargo.lock -migrations/ target/ - *.out *~ **/*.rs.bk \ No newline at end of file diff --git a/migrations/00000000000000_diesel_initial_setup/down.sql b/migrations/00000000000000_diesel_initial_setup/down.sql new file mode 100644 index 0000000..74a7b3d --- /dev/null +++ b/migrations/00000000000000_diesel_initial_setup/down.sql @@ -0,0 +1,11 @@ +-- down.sql: Destroy initial database migration for AO +DROP FUNCTION IF EXISTS public.GETDATE(); + +DROP TABLE users; +DROP TABLE activation_keys; +DROP TABLE games; +DROP TABLE allegiances; +DROP TABLE regions; +DROP TABLE nationstates; +DROP TABLE borders; +DROP TABLE games_nationstates; \ No newline at end of file diff --git a/migrations/00000000000000_diesel_initial_setup/up.sql b/migrations/00000000000000_diesel_initial_setup/up.sql new file mode 100644 index 0000000..83a7869 --- /dev/null +++ b/migrations/00000000000000_diesel_initial_setup/up.sql @@ -0,0 +1,96 @@ + -- up.sql: Initial database migration for AO + +CREATE OR REPLACE FUNCTION public.GETDATE() RETURNS TIMESTAMPTZ + STABLE LANGUAGE SQL AS 'SELECT NOW()'; + + -- base table for users +CREATE TABLE users ( + userid SERIAL, + email VARCHAR(40) UNIQUE NOT NULL, + firstname VARCHAR(20) NOT NULL, + lastname VARCHAR(20) NOT NULL, + password VARCHAR(255) NOT NULL, -- intend to use bcrypt hash + joindate DATE DEFAULT GETDATE(), + activated BIT(1) DEFAULT B'0', -- see activation_keys table + PRIMARY KEY (userid) +); + + -- when activation is completed, key gets deleted and activated bit for user gets flipped to 1. +CREATE TABLE activation_keys ( + code VARCHAR(128), + userid SERIAL, + PRIMARY KEY (code), + CONSTRAINT activation_keys_userid + FOREIGN KEY (userid) REFERENCES users(userid) +); + + -- base table for games +CREATE TABLE games ( + gameid SERIAL, + title VARCHAR(140) NOT NULL, + gametypeid SERIAL NOT NULL, -- future proofing: suppose different game types + players SMALLINT NOT NULL, -- number of players in a game + waitfor INTEGER NOT NULL, -- time in seconds to wait for player to complete a turn + lastturn DATE DEFAULT GETDATE(), + gamestate INTEGER DEFAULT 1, + PRIMARY KEY (gameid) +); + + -- associative table: many users, many games +CREATE TABLE allegiances ( + gameid SERIAL, + userid SERIAL, + allegiance VARCHAR(140), -- important/unimportant role for user (depends on gametype) + ordernum SMALLINT NOT NULL, -- user # spot in game + playing SMALLINT DEFAULT 1, -- default is binary: 0 means player OOP + PRIMARY KEY (gameid, userid), + CONSTRAINT users_games_gameid + FOREIGN KEY (gameid) REFERENCES games(gameid), + CONSTRAINT users_games_userid + FOREIGN KEY (userid) REFERENCES users(userid) +); + + -- Regions are composed of nationstates +CREATE TABLE regions ( + regionid SERIAL, + name VARCHAR(64) NOT NULL, + abbreviation CHAR(2), + bonus INTEGER NOT NULL, + PRIMARY KEY (regionid) +); + + -- base table for nationstates +CREATE TABLE nationstates ( + nationid SERIAL, + regionid SERIAL, + name VARCHAR(64) NOT NULL, + abbreviation CHAR(4), + PRIMARY KEY (nationid), + CONSTRAINT nationstates_regionid + FOREIGN KEY (regionid) REFERENCES regions(regionid) +); + + -- nationstates border nationstates +CREATE TABLE borders ( + nationid SERIAL, + borderid SERIAL, + PRIMARY KEY (nationid, borderid), + CONSTRAINT borders_nationid + FOREIGN KEY (nationid) REFERENCES nationstates(nationid), + CONSTRAINT borders_borderid + FOREIGN KEY (borderid) REFERENCES nationstates(nationid) +); + + -- associative table: many games, many nationstates. Users control them. +CREATE TABLE games_nationstates ( + gameid SERIAL, + nationid SERIAL, + userid SERIAL, -- owner of nationstate for particular game + PRIMARY KEY (gameid, nationid), + CONSTRAINT games_nationstates_gameid + FOREIGN KEY (gameid) REFERENCES games(gameid), + CONSTRAINT games_nationstates_nationid + FOREIGN KEY (nationid) REFERENCES nationstates(nationid), + CONSTRAINT games_nationstates_userid + FOREIGN KEY (userid) REFERENCES users(userid) +); diff --git a/src/main.rs b/src/main.rs index 4c2c020..157d035 100644 --- a/src/main.rs +++ b/src/main.rs @@ -7,9 +7,11 @@ extern crate error_chain; extern crate actix; extern crate actix_web; extern crate env_logger; +#[macro_use] extern crate diesel; -mod errors; +pub mod errors; +pub mod schema; use actix_web::{middleware, server, App, HttpRequest}; use diesel::prelude::*; diff --git a/src/schema.rs b/src/schema.rs new file mode 100644 index 0000000..2f0daba --- /dev/null +++ b/src/schema.rs @@ -0,0 +1,66 @@ +table! { + users(userid) { + userid -> Serial, + email -> VarChar, + firstname -> VarChar, + lastname -> VarChar, + password -> VarChar, + joindate -> Date, + activated -> Bool, + } +} +table! { + activation_keys(code) { + code -> VarChar, + userid -> Serial, + } +} +table! { + games(gameid) { + gameid -> Serial, + title -> VarChar, + gametypeid -> Serial, + players -> SmallInt, + waitfor -> Integer, + lastturn -> Date, + gamestate -> Integer, + } +} +table! { + allegiances(gameid, userid) { + gameid -> Serial, + userid -> Serial, + allegiance -> VarChar, + ordernum -> SmallInt, + playing -> SmallInt, + } +} +table! { + regions(regionid) { + regionid -> Serial, + name -> VarChar, + abbreviation -> Char, + bonus -> Integer, + } +} +table! { + nationstates(nationid) { + nationid -> Serial, + regionid -> Serial, + name -> VarChar, + abbreviation -> Char, + } +} +table! { + borders(nationid, borderid) { + nationid -> Serial, + borderid -> Serial, + } +} +table! { + regions_nationstates(gameid, nationid) { + gameid -> Serial, + nationid -> Serial, + userid -> Serial, + } +} \ No newline at end of file diff --git a/src/util/fresh.sql b/src/util/fresh.sql deleted file mode 100644 index f39b360..0000000 --- a/src/util/fresh.sql +++ /dev/null @@ -1,103 +0,0 @@ - -- fresh.sql: SQL script that creates the tables used by AO - -- please drop all tables in db before initiating unless told otherwise - - -- uncomment the following two lines to automatically drop all tables ---DROP SCHEMA "public" CASCADE; ---CREATE SCHEMA "public"; - -CREATE OR REPLACE FUNCTION public.GETDATE() RETURNS TIMESTAMPTZ - STABLE LANGUAGE SQL AS 'SELECT NOW()'; - - -- base table for users -CREATE TABLE users ( - userid SERIAL, - email VARCHAR(40) UNIQUE NOT NULL, - firstname VARCHAR(20) NOT NULL, - lastname VARCHAR(20) NOT NULL, - password VARCHAR(255) NOT NULL, -- intend to use bcrypt hash through PHP - games INTEGER DEFAULT 0, -- simple tracking statistics - wins INTEGER DEFAULT 0, -- " - joindate DATE DEFAULT GETDATE(), - activated BIT(1) DEFAULT B'0', -- see activation_keys table - PRIMARY KEY (userid) -); - - -- when activation is completed, key gets deleted and activated bit for user gets flipped to 1. -CREATE TABLE activation_keys ( - code VARCHAR(128), - userid SERIAL, - PRIMARY KEY (code), - CONSTRAINT activation_keys_userid - FOREIGN KEY (userid) REFERENCES users(userid) -); - - -- base table for games -CREATE TABLE games ( - gameid SERIAL, - title VARCHAR(140) NOT NULL, - gametypeid SERIAL NOT NULL, -- future proofing: suppose different game types - players SMALLINT NOT NULL, -- number of players in a game - waitfor INTEGER NOT NULL, -- time in seconds to wait for player to complete a turn - lastturn DATE DEFAULT GETDATE(), - gamestate INTEGER DEFAULT 1, - PRIMARY KEY (gameid) -); - - -- associative table: many users, many games -CREATE TABLE allegiances ( - gameid SERIAL, - userid SERIAL, - allegiance VARCHAR(140), -- important/unimportant role for user (depends on gametype) - ordernum SMALLINT NOT NULL, -- user # spot in game - playing SMALLINT DEFAULT 1, -- default is binary: 0 means player OOP - PRIMARY KEY (gameid, userid), - CONSTRAINT users_games_gameid - FOREIGN KEY (gameid) REFERENCES games(gameid), - CONSTRAINT users_games_userid - FOREIGN KEY (userid) REFERENCES users(userid) -); - - -- Regions are composed of nationstates -CREATE TABLE regions ( - regionid SERIAL, - name VARCHAR(64) NOT NULL, - abbreviation CHAR(2), - bonus INTEGER NOT NULL, - PRIMARY KEY (regionid) -); - - -- base table for nationstates -CREATE TABLE nationstates ( - nationid SERIAL, - regionid SERIAL, - name VARCHAR(64) NOT NULL, - abbreviation CHAR(4), - PRIMARY KEY (nationid), - CONSTRAINT nationstates_regionid - FOREIGN KEY (regionid) REFERENCES regions(regionid) -); - - -- nationstates border nationstates -CREATE TABLE borders ( - nationid SERIAL, - borderid SERIAL, - PRIMARY KEY (nationid, borderid), - CONSTRAINT borders_nationid - FOREIGN KEY (nationid) REFERENCES nationstates(nationid), - CONSTRAINT borders_borderid - FOREIGN KEY (borderid) REFERENCES nationstates(nationid) -); - - -- associative table: many games, many nationstates. Users control them. -CREATE TABLE games_nationstates ( - gameid SERIAL, - nationid SERIAL, - userid SERIAL, -- owner of nationstate for particular game - PRIMARY KEY (gameid, nationid), - CONSTRAINT games_nationstates_gameid - FOREIGN KEY (gameid) REFERENCES games(gameid), - CONSTRAINT games_nationstates_nationid - FOREIGN KEY (nationid) REFERENCES nationstates(nationid), - CONSTRAINT games_nationstates_userid - FOREIGN KEY (userid) REFERENCES users(userid) -); -- cgit