summaryrefslogtreecommitdiff
path: root/common/tables
diff options
context:
space:
mode:
authorKyle McFarland <tfkyle@gmail.com>2018-01-31 00:51:07 -0600
committerKyle McFarland <tfkyle@gmail.com>2018-01-31 00:51:07 -0600
commit61d1aa04d8d44b17bfe6dace90088669fc6c3df8 (patch)
tree7ede15c880e4c41a18cded46fe6d03fb2dc4543b /common/tables
downloadmcoop-master.zip
mcoop-master.tar.gz
mcoop-master.tar.bz2
Initial importHEADmaster
* Registration system's almost done * Just part way through implementing tasks So not much done yet, but it's a start.
Diffstat (limited to 'common/tables')
-rw-r--r--common/tables/dividend_credits.php36
-rw-r--r--common/tables/members.php47
-rw-r--r--common/tables/messages.php5
-rw-r--r--common/tables/task_claims.php32
-rw-r--r--common/tables/task_dividend_credits.php32
-rw-r--r--common/tables/tasks.php45
-rw-r--r--common/tables/tcc_history.php31
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"
+);
+
+?>