daktylos/libs/DatabaseBuilder.class.php

<?php
class DatabaseBuilder {
	private $database;

	public function __construct() {
		$this->database = new SqliteDatabase();
	}

	public function init() {
		$this->dropTables();
		$this->createTables();
		$this->insertDefaultData();
		$this->outputData();
	}

	private function createTables() {
		$sql = "CREATE TABLE users (
		username CHAR(50) NOT NULL,
		password CHAR(64) NOT NULL,
		display_name TEXT,
		CONSTRAINT unique_username UNIQUE (username));";
		$this->database->exec($sql);

		$sql = "CREATE TABLE accounts (
		subdomain CHAR(64) NOT NULL,
		display_name TEXT,
		CONSTRAINT unique_subdomain UNIQUE (subdomain));";
		$this->database->exec($sql);

		$sql = "CREATE TABLE projects (
		bucket CHAR(64) NOT NULL,
		folder CHAR(64) NOT NULL,
		display_name TEXT,
		CONSTRAINT unique_datasource UNIQUE (bucket, folder));";
		$this->database->exec($sql);

		$sql = "CREATE TABLE user_accounts (
		user_id INT NOT NULL,
		account_id INT NOT NULL,
		PRIMARY KEY (user_id, account_id) ON CONFLICT REPLACE);";
		$this->database->exec($sql);

		$sql = "CREATE TABLE account_projects (
		account_id INT NOT NULL,
		project_id INT NOT NULL,
		PRIMARY KEY (account_id, project_id) ON CONFLICT REPLACE);";
		$this->database->exec($sql);

		$sql = "CREATE TABLE security_logs (
		message TEXT,
		action CHAR(32),
		recordtime DATETIME);";
		$this->database->exec($sql);
	}

	private function dropTables() {
		$sql = "DROP TABLE IF EXISTS users;";
		$this->database->exec($sql);
		$sql = "DROP TABLE IF EXISTS accounts";
		$this->database->exec($sql);
		$sql = "DROP TABLE IF EXISTS projects";
		$this->database->exec($sql);
		$sql = "DROP TABLE IF EXISTS security_logs";
		$this->database->exec($sql);

		$sql = "DROP TABLE IF EXISTS user_accounts";
		$this->database->exec($sql);
		$sql = "DROP TABLE IF EXISTS account_projects";
		$this->database->exec($sql);
		
	}

	private function insertDefaultData() {
		$sql = "INSERT INTO users (username, password, display_name)
		VALUES 
		('jgilman', '55546635451d24c0ccdbe97ba94cd080fecf4d45123524af4503c9a8993e213c', 'Justin Gilman'),
		('jparra', 'ebb128706f3c5c05502c7f684035b6dbd139e8230cacb11f8fa9bf0e4911dd00', 'Jesus Parra'),
		('clienttest', 'fc7cb737c3a41323c556fb948fd59ed9cddf7833ecae0a5356d33c48ea996691', 'Client Test');";
		$this->database->exec($sql);

		$sql = "INSERT INTO accounts (subdomain, display_name)
		VALUES
		('admin', 'CCLD Admin'),
		('verizon', 'Verizon'),
		('att', 'AT&T');";
		$this->database->exec($sql);

		$sql = "INSERT INTO projects (bucket, folder, display_name)
		VALUES
		('ccld-test', 'ccld-test-folder', 'New_Route');";
		$this->database->exec($sql);

		$sql = "INSERT INTO user_accounts (user_id, account_id)
		VALUES
		(1, 1),
		(1, 2),
		(2, 1),
		(3, 2);";
		$this->database->exec($sql);

		$sql = "INSERT INTO account_projects (account_id, project_id)
		VALUES
		(2, 1),
		(3, 1);";

		$this->database->exec($sql);
	}

	private function outputData() {
		$sql = "SELECT rowid AS user_id, * FROM users;";
		echo $sql . "<br />\n";
		$return = $this->database->query($sql);
		while($row = $return->fetchArray(SQLITE3_ASSOC)) {
			var_dump($row);
		}

		$sql = "SELECT rowid as client_id, * FROM accounts;";
		echo $sql . "<br />\n";
		$return = $this->database->query($sql);
		while($row = $return->fetchArray(SQLITE3_ASSOC)) {
			var_dump($row);
		}

		$sql = "SELECT rowid as project_id, * FROM projects;";
		echo $sql . "<br />\n";
		$return = $this->database->query($sql);
		while($row = $return->fetchArray(SQLITE3_ASSOC)) {
			var_dump($row);
		}

		$sql = "SELECT * FROM security_logs;";
		echo $sql . "<br />\n";
		$return = $this->database->query($sql);
		while($row = $return->fetchArray(SQLITE3_ASSOC)) {
			var_dump($row);
		}

		$sql = "SELECT * FROM user_accounts;";
		echo $sql . "<br />\n";
		$return = $this->database->query($sql);
		while($row = $return->fetchArray(SQLITE3_ASSOC)) {
			var_dump($row);
		}

		$sql = "SELECT * FROM account_projects;";
		echo $sql . "<br />\n";
		$return = $this->database->query($sql);
		while($row = $return->fetchArray(SQLITE3_ASSOC)) {
			var_dump($row);
		}
	}

	public function updateV2() {
		$sql = "ALTER TABLE users ADD email CHAR(254) NOT NULL DEFAULT '';";
		$this->database->exec($sql);
		echo "db updated to v2";
	}

	public function updateV3() {
		$sql = "DROP TABLE IF EXISTS upload_logs;";
		$this->database->exec($sql);

		$sql = "CREATE TABLE upload_logs (
		user_id INT NOT NULL,
		project_id INT NOT NULL,
		filepath CHAR(256),
		uploadtime DATETIME);";
		$this->database->exec($sql);

		echo "db updated to v3";
	}

	public function updateV4() {
		
	}
}