daktylos/libs/DatabaseBuilder.class.php

<?php
class DatabaseBuilder {
	private $database;

	public function init() {
		$this->database = new SqliteDatabase();
		$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);";
		$this->database->exec($sql);

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

		$sql = "CREATE TABLE projects (
	bucket CHAR(64) NOT NULL,
	folder CHAR(64) NOT NULL,
	display_name TEXT);";
		$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
		('".BUCKET_PREFIX."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);
		}

		
	}
}