diff options
Diffstat (limited to 'common')
| -rw-r--r-- | common/config.php.example | 34 | ||||
| -rw-r--r-- | common/config_cls.php | 26 | ||||
| -rw-r--r-- | common/db.php | 310 | ||||
| -rw-r--r-- | common/db_classes.php | 346 | ||||
| -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 |
11 files changed, 944 insertions, 0 deletions
diff --git a/common/config.php.example b/common/config.php.example new file mode 100644 index 0000000..14cb84b --- /dev/null +++ b/common/config.php.example @@ -0,0 +1,34 @@ +<?php +namespace mcoop; +/* Copy this to config.php and edit the $config declaraction below + appropriately */ +require_once("config_cls.php"); +require_once("db.php"); +require_once("db_classes.php"); +require_once("vendor/autoload.php"); + +$config = new Config( + "mysql:host=localhost;dbname=mcoop", + "[username]", + "[password]", + "[recaptcha_site_key]", + "[recaptcha_secret_key]", + "Email Name <email@address>", + "[http://mcoop.example.com/]", + "[Site Name]" +); + +$db = new Database($config); + +if (!isset($logout)) + $logout = false; + +$sess_info = new SessionInfo($db, $logout); + +$twig_loader = new \Twig_Loader_Filesystem("./tmpl/"); +$twig = new \Twig_Environment($twig_loader, array( + "cache" => "./cache/", + "auto_reload" => true +)); + +?> diff --git a/common/config_cls.php b/common/config_cls.php new file mode 100644 index 0000000..b4174be --- /dev/null +++ b/common/config_cls.php @@ -0,0 +1,26 @@ +<?php +namespace mcoop; + +class Config { + public $pdo_connstring = null; + public $db_username = null; + public $db_password = null; + public $recaptcha_sitekey = null; + public $recaptcha_secret = null; + public $email_from_address = null; + public $webapp_base_uri = null; + public $website_name = null; + + function __construct($pdo_connstring, $db_uname, $db_passwd, $recaptcha_sitekey, $recaptcha_sec, $email_from_addr, $webapp_base_uri, $website_name) { + $this->pdo_connstring = $pdo_connstring; + $this->db_username = $db_uname; + $this->db_password = $db_passwd; + $this->recaptcha_sitekey = $recaptcha_sitekey; + $this->recaptcha_secret = $recaptcha_sec; + $this->email_from_address = $email_from_addr; + $this->webapp_base_uri = $webapp_base_uri; + $this->website_name = $website_name; + } +} + +?> diff --git a/common/db.php b/common/db.php new file mode 100644 index 0000000..71dbf81 --- /dev/null +++ b/common/db.php @@ -0,0 +1,310 @@ +<?php +namespace mcoop; +require_once("config_cls.php"); +require_once("vendor/busybee/urljoin/src/urljoin.php"); +require_once("db_classes.php"); +require_once("tables/members.php"); +require_once("tables/dividend_credits.php"); +require_once("tables/tasks.php"); +require_once("tables/task_claims.php"); +require_once("tables/task_dividend_credits.php"); +require_once("tables/tcc_history.php"); + +// TODO: move this into the config object, or auto add to it in each decl file +$table_decls = array( + "members" => $members_table_decl, + "dividend_credits" => $dividend_credits_table_decl, + "tasks" => $tasks_table_decl, + "task_claims" => $task_claims_table_decl, + "task_dividend_credits" => $task_dividend_credits_table_decl, + "tcc_history" => $tcc_history_table_decl +); + +class UnknownMember extends \Exception { + public $searched = null; + public $value = null; + + function __construct($searched, $value) { + $this->searched = $searched; + $this->value = $value; + + $message = "Unable to find a member with $searched = $value"; + parent::__construct($message); + } +} + +class RegistrationError extends \Exception { + public $invalid_part = null; + public $reason = null; + + function __construct($invalid_part, $reason) { + $this->invalid_part = $invalid_part; + $this->reason = $reason; + + $message = "Registration Error ($invalid_part): $reason"; + parent::__construct($message); + } +} + +class LoginError extends \Exception {} + +class Database { + public $config = null; + public $conn = null; + public $target_tableversions = array( + "unexpected_errors" => 1, + ); + public $statements = array(); + public $upgraders = array(); + public $table_decls = array(); + + function __construct($config) { + $this->config = $config; + global $table_decls; + $this->table_decls = $table_decls; + $this->connect(); + } + + function connect() { + // XXX/TODO: the exceptions from this need to be caught, see the warning on http://php.net/manual/en/pdo.connections.php + $this->conn = new \PDO( + $this->config->pdo_connstring, + $this->config->db_username, + $this->config->db_password, + array(\PDO::ATTR_PERSISTENT => true) + ); + $this->create_tableversions_table(); + $this->create_unexpected_errors_table(); + // TODO: make table creation optional for everything except tableversions and unexpected_errors + // (you can change the $create argument to setup_table_decls to false for that now) + $this->setup_table_decls(); + $this->auto_upgrade_all(); + //$this->create_members_table(); + //$this->upgraders["members"] = new MembersUpgrader($this->conn); + //$this->auto_upgrade_all(); + //var_dump($this->conn); + } + + function setup_table_decls($create=true) { + $conn = $this->conn; + foreach ($this->table_decls as $tname => $td) { + if ($create) { + $td->create_table($this); + } + $td->set_statements($this); + $clsname = $td->upgrader_classname; + $this->upgraders[$tname] = new $clsname($conn); + } + } + + function get_cur_table_version($table_name) { + $this->statements["get_tableversion"]->execute(array(":name" => $table_name)); + $x = $this->statements["get_tableversion"]->fetchAll(\PDO::FETCH_COLUMN); + $ver = (int)$x[0]; + return $ver; + } + + function auto_upgrade($upgrader) { + // This shouldn't be called until at the very least after create_tableversions_table, since it adds set_tableversion to statements + $table_name = $upgrader->table_name; + $old_ver = $this->get_cur_table_version($table_name); + $new_ver = $this->table_decls[$table_name]->newest_tableversion; + if ($old_ver < $new_ver) { + error_log("upgrading $table_name from $old_ver to $new_ver"); + // XXX/TODO: this is assuming it doesn't error, going to get really ugly if it does + $upgraded_to = $upgrader->upgrade($old_ver, $new_ver); + $this->statements["set_tableversion"]->execute(array(":table_name" => $table_name, ":version" => $upgraded_to)); + //print_r($upgraded_to); + return $upgraded_to; + } + } + + function auto_upgrade_all() { + foreach ($this->upgraders as $table_name => $upgrader) { + $this->auto_upgrade($upgrader); + } + } + + function create_tableversions_table() { + $conn = $this->conn; + $conn->exec("CREATE TABLE IF NOT EXISTS `table_versions` (`name` TEXT CHARACTER SET utf8 NOT NULL, `version` INT, PRIMARY KEY (`name`(1024)));"); + $this->statements["set_tableversion"] = $conn->prepare("INSERT INTO table_versions VALUES (:table_name, :version) on duplicate key UPDATE version=:version"); + $this->statements["get_tableversion"] = $conn->prepare("SELECT version FROM table_versions WHERE name = :name"); + } + + function create_unexpected_errors_table() { + // TODO: this table needs a datetime field so you know when the error happened + $conn = $this->conn; + $conn->exec("CREATE TABLE `unexpected_errors` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `action` TEXT CHARACTER SET utf8 NOT NULL, `error_sql_code` CHAR(5) CHARACTER SET ascii NOT NULL, `error_server_code` INT, `error_server_description` TEXT CHARACTER SET utf8);"); + $err_info = $conn->errorInfo(); + //var_dump($err_info); + if ($err_info[0] == "00000") { + $this->statements["set_tableversion"]->execute(array( + ":table_name" => "unexpected_errors", + ":version" => $this->target_tableversions["unexpected_errors"] + )); + } + $this->statements["insert_unexpected_error"] = $conn->prepare("INSERT INTO unexpected_errors (action, error_sql_code, error_server_code, error_server_description) VALUES (:action, :sql_code, :server_code, :server_description)"); + } + + function create_members_table() { + $conn = $this->conn; + $conn->exec( + "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)) +);"); + $err_info = $conn->errorInfo(); + // the SQL errorcode for a table already existing seems to be 42S01 + // but I think we don't need to check for it specifically here, + // just make sure there was no error + //print_r($err_info); + //var_dump($err_info); + if ($err_info[0] == "00000") { + $this->statements["set_tableversion"]->execute(array( + ":table_name" => "members", + ":version" => $this->target_tableversions["members"] + )); + } else if ($err_info[0] != "42S01") { + $this->statements["insert_unexpected_error"]->execute(array( + ":action" => "create_members_table", + ":sql_code" => $err_info[0], + ":server_code" => $err_info[1], + ":server_description" => $err_info[2] + )); + } + $this->statements["register_member"] = $conn->prepare("INSERT INTO members (username, email, last_updated_table_version, full_name, argon2_password_hash) VALUES (:username, :email, :version, :full_name, :argon2_phash)"); + $this->statements["get_member_passhash_by_username"] = $conn->prepare("SELECT argon2_password_hash FROM members WHERE username = :username"); + $this->statements["get_member_by_username"] = $conn->prepare("SELECT * FROM members WHERE username = ?"); + $this->statements["get_member_by_email"] = $conn->prepare("SELECT * FROM members WHERE email = ?"); + $this->statements["get_members_by_uname_or_email"] = $conn->prepare("SELECT * FROM members WHERE username = :username OR email = :email"); + } + + function validate_username($username) { + if (!preg_match("/^[a-zA-Z0-9]{3,}$/", $username)) { + throw new RegistrationError("username", "Invalid username (must be at least 3 characters long, only english alphanumeric characters are allowed"); + } + $conn = $this->conn; + $username_check = $conn->prepare("SELECT * FROM members WHERE username = :username"); + $username_check->execute(array(":username" => $username)); + $x = $username_check->fetchAll(\PDO::FETCH_COLUMN); + if ($x) { + throw new RegistrationError("username", "Username already registered"); + } + return $username; + } + + function validate_email($email) { + $filt_email = filter_var($email, FILTER_VALIDATE_EMAIL); + if ($filt_email != $email) { + throw new RegistrationError("email", "Doesn't validate as a valid email address"); + } + $conn = $this->conn; + $email_check = $conn->prepare("SELECT * FROM members where email = :email"); + $email_check->execute(array(":email" => $filt_email)); + $x = $email_check->fetchAll(\PDO::FETCH_COLUMN); + if ($x) { + throw new RegistrationError("email", "An account with that email address already exists, you can <a href=\"/reset_password.php\">Reset your password</a> if you need a new password"); + } + return $filt_email; + } + + function validate_fullname($full_name) { + $filt_fname = filter_var($full_name, FILTER_SANITIZE_STRING, array( + "flags" => FILTER_FLAG_NO_ENCODE_QUOTES | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH | FILTER_FLAG_STRIP_BACKTICK + )); + return $filt_fname; + } + + // TODO: reset password functionality that sends a randomly generated password to your email address + function register($username, $email, $password, $full_name, $validate_email=false, $email_twig_env=null) { + $filt_email = $this->validate_email($email); + $filt_username = $this->validate_username($username); + $filt_fname = $this->validate_fullname($full_name); + $statements = $this->statements; + $conn = $this->conn; + $pass_hash = password_hash($password, PASSWORD_ARGON2I); + $conn->beginTransaction(); + $success = $statements["register_member"]->execute(array( + ":username" => $username, + ":email" => $filt_email, + ":version" => $this->table_decls["members"]->newest_tableversion, + ":full_name" => $filt_fname, + ":argon2_phash" => $pass_hash + )); + // TODO: check that it was actually successful + if (!$success) { + $einfo = $statements["register_member"]->errorInfo(); + error_log("mcoop member registration failed inserting into the database: " . var_export($einfo, true) . " ($username, $email, $password, $pass_hash, $full_name)"); + throw new RegistrationError("database", "registration failed internally, contact the admin"); + } + if ($validate_email) { + $this->send_validation_email($username, $filt_email, $email_twig_env); + } + $conn->commit(); + // TODO: setup the login session here too, not sure if session_start should be called in the script setup code actually instead of just here (and just set the variable in $_SESSION here) + //session_start(); + $_SESSION["logged_in"] = true; + $_SESSION["login_username"] = $username; + // TODO: include login time in here and other things so you can invalidate sessions/log people out if a password reset gets requested or anything else + //session_write_close(); + } + + function send_validation_email($username, $email, $email_twig_env) { + // generate a validation code, just use some random bytes, hash them and use the hexdigest for now + $rbytes = random_bytes(64); + $h = hash("sha256", $rbytes, false); + $conn = $this->conn; + $stmt = $conn->prepare("UPDATE members SET validation_code = :vcode WHERE username = :username AND email = :email"); + $success = $stmt->execute(array( + ":vcode" => $h, + ":username" => $username, + ":email" => $email + )); + if (!$success) { + $einfo = $stmt->errorInfo(); + error_log("mcoop: send_validation_email failed updating members: " . var_export($einfo, true) . " ($h, $username, $email)"); + throw new RegistrationError("database", "internal error generating validation code, contact the admin"); + } + $headers = "From: " . $this->config->email_from_address; + $sitename = $this->config->website_name; + $vurl = urljoin($this->config->webapp_base_uri, "validate.php?un=$username&vcode=$h"); + $deactivate_url = urljoin($this->config->webapp_base_uri, "deactivate.php?un=$username&vcode=$h"); + $body = $email_twig_env->render("validation_email.tmpl", array( + "email" => $email, + "website_name" => $sitename, + "vurl" => $vurl, + "deactivate_url" => $deactivate_url + )); + mail($email, "Activate your $sitename account", $body, $headers); + } + + function login($username, $password, $session_info) { + $m = DBMember::load_by($this, "username", $username); + // TODO: password reset logic + if (password_verify($password, $m->argon2_password_hash)) { + $_SESSION["logged_in"] = true; + $_SESSION["login_username"] = $username; + $session_info->re_init(); + return true; + } else { + throw new LoginError("Invalid Password"); + } + } + + function get_members($only_verified=true) { + + } +} +?> diff --git a/common/db_classes.php b/common/db_classes.php new file mode 100644 index 0000000..dde26eb --- /dev/null +++ b/common/db_classes.php @@ -0,0 +1,346 @@ +<?php +namespace mcoop; + +class TableCreationError extends \Exception { + public $table_name = null; + public $sql_error_code = null; + public $server_error_code = null; + public $server_error_message = null; + + function __construct($table_name, $sql_error_code, $server_error_code, $server_error_message) { + $this->table_name = $table_name; + $this->sql_error_code = $sql_error_code; + $this->server_error_code = $server_error_code; + $this->server_error_message = $server_error_message; + + $message = "Failed to create table '$table_name': ($sql_error_code, $server_error_code, $server_error_message)"; + parent::__construct($message); + } +} + +interface TableUpgrader { + function upgrade($from_version, $to_version); +} + +class BaseIncrementalTableUpgrader implements TableUpgrader { + public $upgrade_method_names = array(); + public $table_name = null; + public $conn = null; + + function upgrade($from_version, $to_version) { + // first make sure a full update is possible with what's in method_names + $full_upgrade_path = true; + for ($i = $from_version + 1; $i <= $to_version; $i++) { + $method_name = $this->upgrade_method_names[$i]; + if (!is_callable(array($this, $method_name))) { + $full_upgrade_path = false; + error_log(get_class($this) . "::upgrade_method_names for $i not callable: " . var_export($method_name, true)); + } + } + if (!$full_upgrade_path) { + die("couldn't upgrade the database, check error_log"); + } else { + $highest_version = $from_version; + for ($i = $from_version + 1; $i <= $to_version; $i++) { + $method_name = $this->upgrade_method_names[$i]; + // XXX/TODO: this really needs trying around, not sure how to deal with upgrades that fail though, might want a downgrade function to rollback if you fail somewhere between, or just leave it in the middle state + $this->$method_name(); + $highest_version = $i; + } + return $highest_version; + } + } +} + +class SimpleTableDecl { + public $table_name = null; + public $statements_sql = array(); + // it might make sense to make this an array in the future, but for now just make it a simple string for creating the newest version + public $create_table_sql = null; + public $newest_tableversion = null; // int + public $upgrader_classname = null; // string, this should be fully namespaced so it can be created dynamically + // TODO: table dependencies variables for creating and/or for the statements? + + function create_table($db) { + $conn = $db->conn; + $conn->exec($this->create_table_sql); + $err_info = $conn->errorInfo(); + // the SQL errorcode for a table already existing seems to be 42S01 + // but I think we don't need to check for it specifically here, + // just make sure there was no error + //print_r($err_info); + //var_dump($err_info); + if ($err_info[0] == "00000") { + $db->statements["set_tableversion"]->execute(array( + ":table_name" => $this->table_name, + ":version" => $this->newest_tableversion + )); + } else if ($err_info[0] != "42S01") { + $db->statements["insert_unexpected_error"]->execute(array( + ":action" => "create_table($this->table_name)", + ":sql_code" => $err_info[0], + ":server_code" => $err_info[1], + ":server_description" => $err_info[2] + )); + throw new TableCreationError($this->table_name, $err_info[0], $err_info[1], $err_info[2]); + } + } + + function set_statements($db) { + foreach ($this->statements_sql as $name => $sql) { + if (array_key_exists($name, $db->statements)) { + // TODO: I should probably also raise an exception here + error_log("mcoop: table_decl for $this->table_name: key $name already exists in the db's statements, not replacing"); + } else { + $db->statements[$name] = $db->conn->prepare($sql); + }; + } + } + + function __construct($table_name, $statements_sql, $create_table_sql, $newest_tableversion, $upgrader_classname) { + $this->table_name = $table_name; + $this->statements_sql = $statements_sql; + $this->create_table_sql = $create_table_sql; + $this->newest_tableversion = $newest_tableversion; + $this->upgrader_classname = $upgrader_classname; + } +} + +class SimpleDBMixin { + static function load_mult_with_statement($db, $st_name, $varray, $clsname=null, $keyprop=null) { + if ($clsname == null) + $clsname = get_called_class(); + $st = $db->statements[$st_name]; + $st->execute($varray); + $ret = array(); + $last_obj = null; + do { + $last_obj = $st->fetchObject($clsname); + //var_dump($st_name, $varray, $clsname, $keyprop, $st, $last_obj); + //var_dump($clsname); + if ($last_obj != FALSE) { + if ($keyprop) { + $key = $last_obj->$keyprop; + // TODO: deal with duplicates + $ret[$key] = $last_obj; + } else { + $ret[] = $last_obj; + } + } + } while ($last_obj != FALSE); + return $ret; + } +} + +// TODO: all of these classes need a get_json function so you don't serialize the entire thing (or look at how to override json +// serialization for classes/objects again) + +class DBMember extends SimpleDBMixin { + // from the members table: + //public $db = null; + public $userid = null; + public $username = null; + public $email = null; + public $last_updated_table_version = null; + public $full_name = null; + public $validation_code = null; + public $validated = null; + public $full_member = null; + public $argon2_password_hash = null; + public $reset_password_hash = null; + public $reset_requested = null; + // from the shares table: + //public $shares = array(); + // generated in __construct + public $display_name = null; + + function __construct() { + if (isset($this->full_name) && $this->full_name) + $this->display_name = $this->full_name; + else + $this->display_name = $this->username; + } + + static function load_by($db, $by, $value) { + // by should be either "username" or "email" here + $st = $db->statements["get_member_by_$by"]; + $st->execute(array($value)); + $self = $st->fetchObject("\mcoop\DBMember"); + if (!$self) { + throw new UnknownMember($by, $value); + } + //$self->db = $db; + return $self; + } + + static function load_public_info($db, $userid) { + $ret = DBMember::load_mult_with_statement($db, "get_public_member_info_by_userid", array(":userid" => $userid)); + if (!$ret) { + throw new UnknownMember("userid", $userid); + } else { + //$ret[0]->db = $db; + return $ret[0]; + } + } +} + +class TccHistoryEntry extends SimpleDBMixin { + // tcc_history table values + public $from_tdc_id = null; // this should probably index into a table in the task object + public $to_claim_id = null; // contains this object, sometimes (the tdc could also contain them, hmm) + public $action = null; + public $credits = null; + public $last_updated_table_version = null; + + static function get_history_by_claimid($db, $claimid, $clsname=null, $keyprop=null) { + return TccHistoryEntry::load_mult_with_statement($db, "get_tcc_history_by_claimid", array(":claimid" => $claimid), $clsname, $keyprop); + } + + static function get_history_by_tdc_id($db, $tdc_id, $clsname=null, $keyprop=null) { + return TccHistoryEntry::load_mult_with_statement($db, "get_tcc_history_by_tdc_id", array(":tdc_id" => $tdc_id), $clsname, $keyprop); + } +} + + +class SimpleTaskClaim extends SimpleDBMixin { + // claims table values + public $claim_id = null; + public $task_id = null; + public $userid = null; + public $last_updated_table_version = null; + public $description = null; + // loaded via. userid + public $member_public = null; + // loaded from tcc_history, see $this->load_tcc_history + public $tcc_history = null; + + static function get_claims_by_taskid($db, $taskid, $clsname=null, $keyprop=null) { + return SimpleTaskClaim::load_mult_with_statement($db, "get_task_claims_by_taskid", array(":task_id" => $taskid), $clsname, $keyprop); + } + + function load_member($db) { + $this->member_public = DBMember::load_public_info($db, $this->userid); + } + + function load_tcc_history($db) { + $this->tcc_history = TccHistoryEntry::get_history_by_claimid($db, $this->claim_id); + } + + function load_all($db) { + $this->load_member($db); + $this->load_tcc_history($db); + } +} + +class SimpleTdc extends SimpleDBMixin { + // tdc table values + public $tdc_id = null; + public $task_id = null; + public $posted_userid = null; + public $posted_by_coop = null; + public $total_credits = null; + public $remaining_credits = null; + public $last_updated_table_version = null; + // loaded via. posted_userid, will be null after $this->load_member() if posted_by_coop is true + public $member_public = null; + // Set to either $member_public->display_name or "By the Co-Operative" + // TODO: maybe use the website_name in $config with posted_by_coop + public $member_name = null; + + static function get_tdcs_by_taskid($db, $taskid, $clsname=null, $keyprop=null) { + return SimpleTdc::load_mult_with_statement($db, "get_tdcs_by_taskid", array(":task_id" => $taskid), $clsname, $keyprop); + } + + function load_member($db) { + if ($this->posted_by_coop) { + $this->member_name = "By the Co-Operative"; + } else { + $this->member_public = DBMember::load_public_info($db, $this->posted_userid); + $this->member_name = $this->member_public->display_name; + } + } + + function load_all($db) { + $this->load_member($db); + } +} + +class SimpleTask extends SimpleDBMixin { + // tasks table values + public $taskid = null; + public $admin_userid = null; + public $last_updated_table_version = null; + public $title = null; + public $description = null; + public $state = null; + // loaded via. admin_userid (DBMember with userid, username and full_name filled in, see $this->load_admin -- using a join might also be a good idea but then you have to replicate the display_name code or change DBMember's construct to pass in args directly) + public $member_public = null; + // loaded from task_dividend_credits, see $this->load_tdcs() + public $tdcs = null; + // loaded from task_claims, see $this->load_claims() or $this->load_full() + public $claims = null; + + static function get_all_simple($db, $clsname=null, $keyprop=null) { + return SimpleTask::load_mult_with_statement($db, "get_all_tasks_simple", array(), $clsname, $keyprop); + } + + static function get_all_full($db, $clsname=null, $keyprop=null) { + return SimpleTask::load_mult_with_statement($db, "get_all_tasks", array(), $clsname, $keyprop); + } + + function load_admin($db) { + $this->member_public = DBMember::load_public_info($db, $this->admin_userid); + } + + function load_claims($db, $full) { + $this->claims = SimpleTaskClaim::get_claims_by_taskid($db, $this->taskid, null, "claim_id"); + if ($full) { + foreach ($this->claims as $k => $claim) { + $claim->load_all($db); + } + } + } + + function load_tdcs($db, $full) { + $this->tdcs = SimpleTdc::get_tdcs_by_taskid($db, $this->taskid, null, "tdc_id"); + if ($full) { + foreach ($this->tdcs as $k => $tdc) { + $tdc->load_all($db); + } + } + } + + function load_all($db) { + $this->load_admin($db); + $this->load_tdcs($db, true); + $this->load_claims($db, true); + } +} + +class SessionInfo { + public $db = null; + public $login_member = null; + + function __construct($db, $logout) { + $this->db = $db; + session_start(); + if ($logout) { + $this->logout(); + } + $this->re_init(); + } + + function logout() { + session_unset(); + $this->login_member = null; + } + + function re_init() { + $this->login_member = null; + if (isset($_SESSION["logged_in"]) && $_SESSION["logged_in"] && isset($_SESSION["login_username"])) { + // TODO: validate login, either based on password, password reset time or possibly allow users to log out sessions explicitly (might require database session storage to make it easier) + $this->login_member = DBMember::load_by($this->db, "username", $_SESSION["login_username"]); + } + } +} +?> 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" +); + +?> |