From 2d796d48df6f4371111bcbc776ea781e4f45c831 Mon Sep 17 00:00:00 2001
From: Kevin J Hoerr <kjhoerr@protonmail.com>
Date: Sat, 8 Jul 2017 11:11:53 -0400
Subject: Expand on query and result, begin sql design

---
 private/Controller/Controller.php |  56 +++++++++++++++++++++
 private/Model/Connection.php      |  41 +++++++++++++++
 private/Model/Query.php           |  71 ++++++++++++++++++++++++++
 private/Model/Result.php          |  52 +++++++++++++++++++
 private/Model/creds.php           |  11 ++++
 private/Model/fresh.sql           | 103 ++++++++++++++++++++++++++++++++++++++
 private/View/Main.php             |  78 +++++++++++++++++++++++++++++
 private/controller/Controller.php |  16 ------
 private/model/Connection.php      |  41 ---------------
 private/view/Result.php           |  39 ---------------
 10 files changed, 412 insertions(+), 96 deletions(-)
 create mode 100644 private/Controller/Controller.php
 create mode 100644 private/Model/Connection.php
 create mode 100644 private/Model/Query.php
 create mode 100644 private/Model/Result.php
 create mode 100644 private/Model/creds.php
 create mode 100644 private/Model/fresh.sql
 create mode 100644 private/View/Main.php
 delete mode 100644 private/controller/Controller.php
 delete mode 100644 private/model/Connection.php
 delete mode 100644 private/view/Result.php

(limited to 'private')

diff --git a/private/Controller/Controller.php b/private/Controller/Controller.php
new file mode 100644
index 0000000..188345a
--- /dev/null
+++ b/private/Controller/Controller.php
@@ -0,0 +1,56 @@
+<?php
+
+declare(strict_types=1);
+
+namespace AugustOffensive\Controller;
+
+use AugustOffensive\Model;
+
+/**
+ * Static controller class for interfacing between the view and the model.
+ */
+class Controller
+{
+    /**
+     * Creates and returns a Query object.
+     *
+     * If the creation results in an error, a different query object is
+     * returned with the error message.
+     *
+     * @param array $path The array that holds the original request structure.
+     * @param string $request The request method made to the server.
+     * @param array $content The content object sent by the request.
+     *
+     * @return Model\Query
+     */
+    public static function createQuery (
+        array $path,
+        string $request,
+        array $content
+    ): Model\Query {
+        try {
+            return new Model\Query ($path, $request, $content);
+        } catch (\Exception $err) {
+            return new Model\Query (array(), "", array("ERROR" => $err->getMessage()));
+        }
+    }
+
+    /**
+     * Creates and returns a Result object.
+     *
+     * @param string $resultType The type of result to send back to the client.
+     * @param array $result The result object to send back to the client.
+     *
+     * @return Model\Result
+     */
+    public static function createResult (
+        string $resultType,
+        array $result
+    ): Model\Result {
+        try {
+            return new Model\Result ($resultType, $result);
+        } catch (\Exception $err) {
+            return new Model\Result ("ERROR", array($err->getMessage()));
+        }
+    }
+}
diff --git a/private/Model/Connection.php b/private/Model/Connection.php
new file mode 100644
index 0000000..48cf4fb
--- /dev/null
+++ b/private/Model/Connection.php
@@ -0,0 +1,41 @@
+<?php
+
+declare(strict_types=1);
+
+namespace AugustOffensive\Model;
+
+/**
+ * Model connection class for connecting to database via PDO.
+ */
+class Connection
+{
+    /** @var \PDO $_conn PDO connection to database. */
+    private $conn;
+    /**
+     * Initiates connection to PostGreSQL database.
+     *
+     * @return Connection
+     */
+    public function __construct ()
+    {
+        // Establish connection to db
+        include 'creds.php';
+
+        try {
+	        $conn = new \PDO(
+                "pgsql: host=" . $cred->host .
+                    (($cred->port !== '') ? ";port=" . $cred->port : '') .
+                    ";dbname=" . $cred->dbName,
+                $cred->login,
+                $cred->password
+            );
+            // we destroy $cred as quickly as possible
+            $cred = null;
+        } catch (\PDOException $err) {
+            // we destroy $cred as quickly as possible
+            $cred = null;
+            die(json_encode(array("Result-Type" => "ERROR", "Content" => array($err->getMessage()))));
+        }
+        return $this;
+    }
+}
diff --git a/private/Model/Query.php b/private/Model/Query.php
new file mode 100644
index 0000000..df05b24
--- /dev/null
+++ b/private/Model/Query.php
@@ -0,0 +1,71 @@
+<?php
+
+declare(strict_types=1);
+
+namespace AugustOffensive\Model;
+
+/**
+ * Query object for storing relevant query information.
+ */
+class Query 
+{
+    /** @var array $path array of request structure. */
+    private $path;
+
+    /** @var string $request type of request made to the server. */
+    private $request;
+
+    /** @var array $content structure of information sent to the server. */
+    private $content;
+
+    /**
+     * Store query information.
+     *
+     * @param array $path The array that holds the original request structure.
+     * @param string $request The request method made to the server.
+     * @param array $content The content object sent by the request.
+     * 
+     * @return Query
+     */
+    public function __construct (
+        array $path, 
+        string $request, 
+        array $content
+    ) {
+        $this->path = $path;
+        $this->request = $request;
+        $this->content = $content;
+
+        return $this;
+    }
+
+    /**
+     * Returns the request path made by the client.
+     * 
+     * @return array 
+     */
+    public function getPath (): array
+    {
+        return $this->path;
+    }
+
+    /**
+     * Returns the request type made by the client.
+     * 
+     * @return string
+     */
+    public function get_request (): string
+    {
+        return $this->request;
+    }
+
+    /**
+     * Returns the information that is built from outside the request path.
+     *
+     * @return array
+     */
+    public function getContent (): array
+    {
+        return $this->content;
+    }
+}
diff --git a/private/Model/Result.php b/private/Model/Result.php
new file mode 100644
index 0000000..ac08821
--- /dev/null
+++ b/private/Model/Result.php
@@ -0,0 +1,52 @@
+<?php
+
+declare(strict_types=1);
+
+namespace AugustOffensive\Model;
+/**
+ * Result object for storing information to send back to the client.
+ */
+class Result 
+{
+    /** @var string $resultType the type of result to return to the client. */
+    private $resultType;
+
+    /** @var array $result */
+    private $result;
+
+    /**
+     * Store result information.
+     *
+     * @param string $resultType The type of result to send back to the client.
+     * @param array $result The result object to send back to the client.
+     *
+     * @return Result
+     */
+    public function __construct (string $resultType, array $result)
+    {
+        $this->resultType = $resultType;
+        $this->result = $result;
+
+        return $this;
+    }
+
+    /**
+     * Returns the result type of the Result.
+     *
+     * @return string
+     */
+    public function getResultType (): string
+    {
+        return $this->resultType;
+    }
+
+    /**
+     * Returns the result array of the Result.
+     *
+     * @return array
+     */
+    public function getResult (): array
+    {
+        return $this->result;
+    }
+}
\ No newline at end of file
diff --git a/private/Model/creds.php b/private/Model/creds.php
new file mode 100644
index 0000000..d7b62f1
--- /dev/null
+++ b/private/Model/creds.php
@@ -0,0 +1,11 @@
+<?php
+
+declare(strict_types=1);
+
+$cred = new \stdClass();
+$cred->host = "localhost";
+$cred->port = "5432";
+$cred->dbName = "august";
+$cred->login = "smallg";
+$cred->password = "abc";
+
diff --git a/private/Model/fresh.sql b/private/Model/fresh.sql
new file mode 100644
index 0000000..f39b360
--- /dev/null
+++ b/private/Model/fresh.sql
@@ -0,0 +1,103 @@
+ -- 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)
+);
diff --git a/private/View/Main.php b/private/View/Main.php
new file mode 100644
index 0000000..1dcd760
--- /dev/null
+++ b/private/View/Main.php
@@ -0,0 +1,78 @@
+<?php
+
+declare(strict_types=1);
+
+namespace AugustOffensive\View;
+
+use AugustOffensive\Controller;
+use AugustOffensive\Model;
+
+/**
+ * Outputs the JSON result by communicating with the controller.
+ */
+class Main
+{
+    /** @var Model\Query $query the Query object that generated the request. */
+    private $query;
+
+    /** @var Model\Result $result the Result object to be sent to the client. */
+    private $result;
+
+    /**
+     * Prepares the output and environment for the front end of the service.
+     *
+     * @param Model\Connection $connection View "Needs to know" model exists.
+     *
+     * @return Result
+     */
+    public function __construct (Model\Connection $connection)
+    {
+        header("Content-Type: application/json");
+
+        $this->query = Controller\Controller::createQuery(
+            explode('/', trim($_SERVER['PATH_INFO'] ?? '/api', '/')),
+            $_SERVER['REQUEST_METHOD'],
+            $this->generateContent($_SERVER['REQUEST_METHOD'])
+        );
+        
+        return $this;
+    }
+
+    /**
+     * Generates the content of the query based on the request type.
+     *
+     * @param string $request The request method on which to base the content.
+     *
+     * @return array
+     */
+    public function generateContent (string $request): array
+    {
+        $content;
+        switch ($request) {
+            case "GET": // GET should always be empty
+            case "POST": // POST contains moves, account info, etc.
+            default:
+                $content = $_REQUEST;
+        }
+        return $content;
+    }
+
+    /**
+     * Communicates with the controller to generate the JSON result.
+     *
+     * @return string
+     */
+    public function generateResult (): string
+    {
+        $this->result = Controller\Controller::createResult(
+            "",
+            array()
+        );
+
+        // generate result
+        return json_encode(array(
+            "Result-Type" => $this->result->getResultType(),
+            "Content" => $this->result->getResult()
+        ));
+    }
+}
diff --git a/private/controller/Controller.php b/private/controller/Controller.php
deleted file mode 100644
index c563367..0000000
--- a/private/controller/Controller.php
+++ /dev/null
@@ -1,16 +0,0 @@
-<?php
-
-declare(strict_types=1);
-
-namespace AugustOffensive\controller;
-
-use AugustOffensive\model;
-
-/**
- * Static controller class for interfacing between the view and the model.
- */
-class Controller 
-{
-    //
-}
-
diff --git a/private/model/Connection.php b/private/model/Connection.php
deleted file mode 100644
index 42cba28..0000000
--- a/private/model/Connection.php
+++ /dev/null
@@ -1,41 +0,0 @@
-<?php
-
-declare(strict_types=1);
-
-namespace AugustOffensive\model;
-
-/**
- * Model connection class for connecting to database via PDO.
- */
-class Connection
-{
-    /** @var \PDO $_conn PDO connection to database. */
-    private $_conn;
-    /**
-     * Initiates connection to PostGreSQL database.
-     *
-     * @return Connection
-     */
-    public function __construct ()
-    {
-        // Establish connection to db
-        include 'creds.php';
-
-        try {
-	        $_conn = new \PDO(
-                "pgsql: host=" . $cred->host .
-                    (($cred->port !== '') ? ";port=" . $cred->port : '') .
-                    ";dbname=" . $cred->dbName,
-                $cred->login,
-                $cred->password
-            );
-            // we destroy $cred as quickly as possible
-            $cred = null;
-        } catch (\PDOException $e) {
-            // we destroy $cred as quickly as possible
-            $cred = null;
-            die(json_encode(array("Result-Type" => "Error", "Content" => array($e->getMessage()))));
-        }
-        return $this;
-    }
-}
diff --git a/private/view/Result.php b/private/view/Result.php
deleted file mode 100644
index 2d12c28..0000000
--- a/private/view/Result.php
+++ /dev/null
@@ -1,39 +0,0 @@
-<?php
-
-declare(strict_types=1);
-
-namespace AugustOffensive\view;
-
-use AugustOffensive\controller;
-use AugustOffensive\model;
-
-/**
- * Outputs the JSON result by communicating with the controller.
- */
-class Result
-{
-    /**
-     * Prepares the output and environment for the front end of the service.
-     *
-     * @param model\Connection $connection "needs to know" model exists
-     *
-     * @return Result
-     */
-    public function __construct (model\Connection $connection)
-    {
-        header("Content-Type: application/json");
-        //
-        return $this;
-    }
-
-    /**
-     * Communicates with the controller to generate the JSON result.
-     *
-     * @return string $result resulting sendback object generated from query.
-     */
-    public function collect (): string
-    {
-        //
-        return json_encode(array("Result-Type" => "", "Content" => array()));
-    }
-}
-- 
cgit