7 files changed, 228 insertions, 0 deletions

diff --git a/common/tables/dividend_credits.php b/common/tables/dividend_credits.php
new file mode 100644
index 0000000..2f80ec0
--- /dev/null
+++ b/common/tables/dividend_credits.php
@@ -0,0 +1,36 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+class DividendCreditsUpgrader extends BaseIncrementalTableUpgrader {
+	function from_1_to_2() {
+		//$this->conn->exec("ALTER TABLE dividend_ ADD COLUMN (test BOOL)");
+		// No actual changes in this one, it was just to make sure the upgrader paths were working
+	}
+
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->table_name = "dividend_credits";
+		//$this->upgrade_method_names[2] = "from_1_to_2";
+	}
+}
+
+$dividend_credits_table_decl = new SimpleTableDecl(
+	"dividend_credits",
+	array(
+		"dividend_credits_ensure" => "INSERT IGNORE INTO dividend_credits (userid, year, credits, last_updated_table_version) VALUES (:userid, :year, 0, :table_ver)",
+		"add_to_dividend_credits" => "UPDATE dividend_credits SET credits=credits + :amount WHERE userid=:userid AND year=:year",
+		"remove_from_dividend_credits" => "UPDATE dividend_credits SET credits=credits - :amount WHERE userid=:userid AND year=:year",
+		"set_dividend_credits" => "UPDATE dividend_credits SET credits=:amount WHERE userid=:userid AND year=:year"
+	),
+	"CREATE TABLE `dividend_credits` (
+`userid` INT NOT NULL,
+`year` INT NOT NULL,
+`credits` BIGINT NOT NULL,
+`last_updated_table_version` INT NOT NULL,
+PRIMARY KEY (`userid`, `year`)
+);",
+	1,
+	"\mcoop\DividendCreditsUpgrader"
+);
+
diff --git a/common/tables/members.php b/common/tables/members.php
new file mode 100644
index 0000000..bf1240d
--- /dev/null
+++ b/common/tables/members.php
@@ -0,0 +1,47 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+class MembersUpgrader extends BaseIncrementalTableUpgrader {
+	function from_1_to_2() {
+		//$this->conn->exec("ALTER TABLE members ADD COLUMN (test BOOL)");
+		// No actual changes in this one, it was just to make sure the upgrader paths were working
+	}
+
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->table_name = "members";
+		$this->upgrade_method_names[2] = "from_1_to_2";
+	}
+}
+
+$members_table_decl = new SimpleTableDecl(
+	"members",
+	array(
+		"register_member" => "INSERT INTO members (username, email, last_updated_table_version, full_name, argon2_password_hash) VALUES (:username, :email, :version, :full_name, :argon2_phash)",
+		"get_member_passhash_by_username" => "SELECT argon2_password_hash FROM members WHERE username = :username",
+		"get_member_by_username" => "SELECT * FROM members WHERE username = ?",
+		"get_member_by_email" => "SELECT * FROM members WHERE email = ?",
+		"get_members_by_uname_or_email" => "SELECT * FROM members WHERE username = :username OR email = :email",
+		"get_public_member_info_by_userid" => "SELECT userid, username, full_name FROM members WHERE userid=:userid"
+	),
+	"CREATE TABLE `members` (
+`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+`username` TEXT CHARACTER SET utf8 NOT NULL,
+`email` TEXT CHARACTER SET utf8 NOT NULL,
+`last_updated_table_version` INT NOT NULL,
+`full_name` TEXT CHARACTER SET utf8,
+`validation_code` CHAR(64) CHARACTER SET ascii,
+`validated` BOOL DEFAULT false,
+`full_member` BOOL DEFAULT false,
+`argon2_password_hash` VARCHAR(256) CHARACTER SET ascii,
+`reset_password_hash` VARCHAR(256) CHARACTER SET ascii,
+`reset_requested` BOOL DEFAULT false,
+UNIQUE KEY (`username`(256)),
+UNIQUE KEY (`email`(256))
+);",
+	2,
+	"\mcoop\MembersUpgrader"
+);
+
+?>
diff --git a/common/tables/messages.php b/common/tables/messages.php
new file mode 100644
index 0000000..65d821d
--- /dev/null
+++ b/common/tables/messages.php
@@ -0,0 +1,5 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+?>
diff --git a/common/tables/task_claims.php b/common/tables/task_claims.php
new file mode 100644
index 0000000..4f30964
--- /dev/null
+++ b/common/tables/task_claims.php
@@ -0,0 +1,32 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+class TaskClaimsUpgrader extends BaseIncrementalTableUpgrader {
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->table_name = "task_claims";
+	}
+}
+
+$task_claims_table_decl = new SimpleTableDecl(
+	"task_claims",
+	array(
+		"create_task_claim" => "INSERT INTO task_claims (task_id, userid, last_updated_table_version, description) VALUES (:task_id, :userid, :table_ver, :description)",
+		// something that might be interesting is also keying on WHERE userid=:userid so the user that created it's the only one that can update it, I'll probably do something with perms though (that's a TODO)
+		"update_task_claim_desc" => "UPDATE task_claims SET description=:desc WHERE claim_id=:claim_id",
+		"get_task_claim_ids_by_taskid" => "SELECT claim_id FROM task_claims WHERE task_id=:task_id",
+		"get_task_claims_by_taskid" => "SELECT * FROM task_claims WHERE task_id=:task_id"
+	),
+	"CREATE TABLE `task_claims` (
+`claim_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+`task_id` INT NOT NULL,
+`userid` INT NOT NULL,
+`last_updated_table_version` INT NOT NULL,
+`description` TEXT CHARACTER SET utf8
+);",
+	1,
+	"\mcoop\TaskClaimsUpgrader"
+);
+
+?>
diff --git a/common/tables/task_dividend_credits.php b/common/tables/task_dividend_credits.php
new file mode 100644
index 0000000..d6a0ce7
--- /dev/null
+++ b/common/tables/task_dividend_credits.php
@@ -0,0 +1,32 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+class TaskDividendCreditsUpgrader extends BaseIncrementalTableUpgrader {
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->table_name = "task_dividend_credits";
+	}
+}
+
+$task_dividend_credits_table_decl = new SimpleTableDecl(
+	"task_dividend_credits",
+	array(
+		"create_new_tdc" => "INSERT INTO task_dividend_credits (task_id, posted_userid, posted_by_coop, total_credits, remaining_credits, last_updated_table_version) VALUES (:task_id, :userid, :coop_post, :credits, :credits, :table_ver)",
+		"tdc_remove_credits" => "UPDATE task_dividend_credits SET remaining_credits=remaining_credits - :amount WHERE tdc_id = :tdc_id",
+		"get_tdcs_by_taskid" => "SELECT * FROM task_dividend_credits WHERE task_id=:task_id"
+	),
+	"CREATE TABLE `task_dividend_credits` (
+`tdc_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+`task_id` INT NOT NULL,
+`posted_userid` INT NOT NULL,
+`posted_by_coop` BOOL NOT NULL,
+`total_credits` BIGINT NOT NULL,
+`remaining_credits` BIGINT NOT NULL,
+`last_updated_table_version` INT NOT NULL
+);",
+	1,
+	"\mcoop\TaskDividendCreditsUpgrader"
+);
+
+?>
diff --git a/common/tables/tasks.php b/common/tables/tasks.php
new file mode 100644
index 0000000..af754d2
--- /dev/null
+++ b/common/tables/tasks.php
@@ -0,0 +1,45 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+class TasksUpgrader extends BaseIncrementalTableUpgrader {
+	function from_1_to_2() {
+		//$this->conn->exec("ALTER TABLE tasks ADD COLUMN (test BOOL)");
+		// No actual changes in this one, it was just to make sure the upgrader paths were working
+	}
+
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->table_name = "tasks";
+		//$this->upgrade_method_names[2] = "from_1_to_2";
+	}
+}
+
+$tasks_table_decl = new SimpleTableDecl(
+	"tasks",
+	array(
+		"create_task" => "INSERT INTO tasks (admin_userid, last_updated_table_version, title, description) VALUES (:userid, :table_ver, :title, :desc)",
+		// TODO: dynamically generating these queries based on what fields are modified instead of statically would be a good idea
+		"update_task_title" => "UPDATE tasks SET title=:title WHERE taskid=:taskid",
+		"update_task_td" => "UPDATE tasks SET title=:title, description=:desc WHERE taskid=:taskid",
+		"update_task_desc" => "UPDATE tasks SET description=:description WHERE taskid=:taskid",
+		"get_all_tasks_simple" => "SELECT taskid, admin_userid, last_updated_table_version, title, state FROM tasks",
+		"get_all_tasks" => "SELECT * FROM tasks"
+	),
+	"CREATE TABLE `tasks` (
+`taskid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+`admin_userid` INT NOT NULL,
+`last_updated_table_version` INT NOT NULL,
+`title` TEXT CHARACTER SET utf8,
+`description` TEXT CHARACTER SET utf8,
+`state` ENUM ('open', 'closed') NOT NULL DEFAULT 'open'
+);",
+	1,
+	"\mcoop\TasksUpgrader"
+);
+
+// TODO: tables for comments both on tasks and task claims would be a good idea (and probably necessary)
+
+// TODO: should either title be unique or maybe have an extra text task id kind of like bugzilla aliases?
+// TODO: need a claims table, and a table listing dividend credits available for each task, also a notification table for the task admin so they know when someone wants to add dividend credits to a closed task so they can reopen the task if deemed appropriate (also comments like any bugtracker?)
+?>
diff --git a/common/tables/tcc_history.php b/common/tables/tcc_history.php
new file mode 100644
index 0000000..362d702
--- /dev/null
+++ b/common/tables/tcc_history.php
@@ -0,0 +1,31 @@
+<?php
+namespace mcoop;
+require_once("common/db_classes.php");
+
+// tcc stands for Task Claim Credits
+
+class TccHistoryUpgrader extends BaseIncrementalTableUpgrader {
+	function __construct($conn) {
+		$this->conn = $conn;
+		$this->table_name = "tcc_history";
+	}
+}
+
+$tcc_history_table_decl = new SimpleTableDecl(
+	"tcc_history",
+	array(
+		"get_tcc_history_by_claimid" => "SELECT * FROM tcc_history WHERE to_claim_id=:claimid",
+		"get_tcc_history_by_tdc_id" => "SELECT * FROM tcc_history WHERE from_tdc_id=:tdc_id"
+	),
+	"CREATE TABLE `tcc_history` (
+`from_tdc_id` INT NOT NULL,
+`to_claim_id` INT NOT NULL,
+`action` ENUM ('award', 'rescind') NOT NULL,
+`credits` BIGINT NOT NULL,
+`last_updated_table_version` INT NOT NULL
+);",
+	1,
+	"\mcoop\TccHistoryUpgrader"
+);
+
+?>