diff options
author | Kyle McFarland <tfkyle@gmail.com> | 2018-01-31 00:51:07 -0600 |
---|---|---|
committer | Kyle McFarland <tfkyle@gmail.com> | 2018-01-31 00:51:07 -0600 |
commit | 61d1aa04d8d44b17bfe6dace90088669fc6c3df8 (patch) | |
tree | 7ede15c880e4c41a18cded46fe6d03fb2dc4543b /common/tables | |
download | mcoop-61d1aa04d8d44b17bfe6dace90088669fc6c3df8.zip mcoop-61d1aa04d8d44b17bfe6dace90088669fc6c3df8.tar.gz mcoop-61d1aa04d8d44b17bfe6dace90088669fc6c3df8.tar.bz2 |
* 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.php | 36 | ||||
-rw-r--r-- | common/tables/members.php | 47 | ||||
-rw-r--r-- | common/tables/messages.php | 5 | ||||
-rw-r--r-- | common/tables/task_claims.php | 32 | ||||
-rw-r--r-- | common/tables/task_dividend_credits.php | 32 | ||||
-rw-r--r-- | common/tables/tasks.php | 45 | ||||
-rw-r--r-- | common/tables/tcc_history.php | 31 |
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" +); + +?> |