Initial importHEADmaster

* Registration system's almost done
* Just part way through implementing tasks
So not much done yet, but it's a start.

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"
+);
+
+?>