private static function truncate()
{
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_JOBS)->truncate();
} catch (DatabaseException $de) {
unset($db);
throw $de;
}
unset($db);
}
public static function show($name)
{
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$query = $db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_JOBS)->keys(array("id", "name", "task", "description", "min", "hour", "dayofmonth", "month", "dayofweek", "year", "params", "lastrun", "firstrun", "enabled"))->orderBy("name");
if (!is_null($name)) {
$query->where("name", "=", $name);
}
$result = $query->get();
} catch (DatabaseException $de) {
unset($db);
throw $de;
} catch (Exception $e) {
unset($db);
throw $e;
}
unset($db);
return $result->getData();
}
protected function initDatabase(Configuration $configuration)
{
$model = $configuration->get('database-model');
$host = $configuration->get('database-host');
$port = $configuration->get('database-port');
$name = $configuration->get('database-name');
$user = $configuration->get('database-user');
$password = $configuration->get('database-password');
$prefix = $configuration->get('database-prefix');
try {
$database = new EnhancedDatabase($model, $host, $port, $name, $user, $password);
$database->tablePrefix($prefix);
$database->autoClean();
} catch (DatabaseException $de) {
throw $de;
} catch (Exception $e) {
throw $e;
}
return $database;
}
/**
* Get cache status
*
* @return array
*/
public function status()
{
$this->resetErrorState();
try {
$this->dbh->tablePrefix($this->table_prefix)->table($this->table)->keys('COUNT::name=>count');
$count = $this->dbh->get();
$objects = $count->getData();
} catch (DatabaseException $de) {
$this->raiseError("Failed to get cache status (Database), exiting gracefully", array("ERRORNO" => $de->getCode(), "ERROR" => $de->getMessage()));
$this->setErrorState();
return array("provider" => "database", "enabled" => false, "objects" => 0, "options" => array());
}
return array("provider" => "database", "enabled" => $this->isEnabled(), "objects" => intval($objects[0]['count']), "options" => array('host' => $this->dbh->getHost(), 'port' => $this->dbh->getPort(), 'name' => $this->dbh->getName(), 'user' => $this->dbh->getUser(), 'model' => $this->dbh->getModel()));
}
/**
* Check if database is available and initialized correctly
*
* @return bool
*/
public static final function database()
{
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_JOBS)->keys("*")->get(1);
} catch (DatabaseException $de) {
unset($db);
return false;
}
unset($db);
return true;
}
define("EXTENDER_DATABASE_FOLDER", EXTENDER_REAL_PATH . "database/");
define("EXTENDER_CACHE_FOLDER", EXTENDER_REAL_PATH . "cache/");
define("EXTENDER_DATABASE_MODEL", "SQLITE_PDO");
define("EXTENDER_DATABASE_HOST", "localhost");
define("EXTENDER_DATABASE_PORT", 1);
define("EXTENDER_DATABASE_NAME", EXTENDER_DATABASE_FOLDER . "extender.sqlite");
define("EXTENDER_DATABASE_USER", "comodojo");
define("EXTENDER_DATABASE_PASS", "");
define("EXTENDER_DATABASE_PREFIX", "extender_");
define("EXTENDER_DATABASE_TABLE_JOBS", "jobs");
define("EXTENDER_DATABASE_TABLE_WORKLOGS", "worklogs");
// custom config files
define("EXTENDER_TASKS_CONFIG", EXTENDER_REAL_PATH . "tasks.yaml");
define("EXTENDER_COMMANDS_CONFIG", EXTENDER_REAL_PATH . "commands.yaml");
define("EXTENDER_PLUGINS_CONFIG", EXTENDER_REAL_PATH . "plugins.yaml");
// override exit codes
define("COMODOJO_PHPUNIT_TEST", true);
// create database
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$db->autoClean();
$db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_JOBS)->drop(true);
$db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_WORKLOGS)->drop(true);
$jobs = $db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_JOBS)->column(Column::create('id', 'INTEGER')->unsigned()->autoIncrement()->primaryKey())->column(Column::create('name', 'STRING')->length(128)->notNull()->unique())->column(Column::create('task', 'STRING')->length(128)->notNull())->column(Column::create('description', 'TEXT')->defaultValue(null))->column(Column::create('enabled', 'BOOL')->defaultValue(0))->column(Column::create('min', 'STRING')->length(16)->defaultValue(null))->column(Column::create('hour', 'STRING')->length(16)->defaultValue(null))->column(Column::create('dayofmonth', 'STRING')->length(16)->defaultValue(null))->column(Column::create('month', 'STRING')->length(16)->defaultValue(null))->column(Column::create('dayofweek', 'STRING')->length(16)->defaultValue(null))->column(Column::create('year', 'STRING')->length(16)->defaultValue(null))->column(Column::create('params', 'TEXT')->defaultValue(null))->column(Column::create('lastrun', 'INTEGER')->length(64)->defaultValue(null))->column(Column::create('firstrun', 'INTEGER')->length(64)->notNull())->create(EXTENDER_DATABASE_TABLE_JOBS);
// $db->clean();
$worklogs = $db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_WORKLOGS)->column(Column::create('id', 'INTEGER')->unsigned()->autoIncrement()->primaryKey())->column(Column::create('pid', 'INTEGER')->unsigned()->defaultValue(null))->column(Column::create('jobid', 'INTEGER')->unsigned()->defaultValue(null))->column(Column::create('name', 'STRING')->length(128)->notNull())->column(Column::create('task', 'STRING')->length(128)->notNull())->column(Column::create('status', 'STRING')->length(12)->notNull())->column(Column::create('success', 'BOOL')->defaultValue(0))->column(Column::create('result', 'TEXT')->defaultValue(null))->column(Column::create('start', 'STRING')->length(64)->notNull())->column(Column::create('end', 'STRING')->length(64)->defaultValue(null))->create(EXTENDER_DATABASE_TABLE_WORKLOGS);
} catch (DatabaseException $de) {
unset($db);
throw new ShellException("Database error: " . $de->getMessage());
}
unset($db);
private static function pushDefaultContent()
{
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$db->tablePrefix(EXTENDER_DATABASE_PREFIX);
$db->autoClean();
// admin role
$admin_role_id = $db->table('roles')->keys(array('name', 'description'))->values(array('admin', 'Default administrator role'))->store()->getInsertId();
$authentication_id = $db->table('authentication')->keys(array('name', 'description', 'class'))->values(array('local', 'Local Authentication Provider', '\\Comodojo\\Authentication\\Provider\\LocalProvider'))->store()->getInsertId();
$admin_pwd = password_hash('comodojo', PASSWORD_DEFAULT);
$admin_user_id = $db->table('users')->keys(array('username', 'password', 'displayname', 'mail', 'authentication', 'enabled', 'primaryrole'))->values(array('admin', $admin_pwd, 'Administrator', 'administrator@localhost', $authentication_id, 1, $admin_role_id))->store()->getInsertId();
$db->table('users_to_roles')->keys(array('user', 'role'))->values(array($admin_user_id, $admin_role_id))->store();
} catch (DatabaseException $de) {
unset($db);
throw new ShellException("Database error: " . $de->getMessage());
}
unset($db);
}
private static function getDatabase()
{
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_WORKLOGS)->keys(array("id", "pid", "jobid", "name", "task", "status", "success", "result", "start", "end"));
} catch (DatabaseException $de) {
throw $de;
} catch (Exception $e) {
throw $e;
}
return $db;
}
require "../src/QueryBuilder/QueryStore.php";
require "../src/QueryBuilder/QueryUpdate.php";
use Comodojo\Exception\DatabaseException;
use Comodojo\Database\Database;
use Comodojo\Database\EnhancedDatabase;
use Comodojo\Database\QueryBuilder\Column;
$query_output_pattern = '
<h1> __NAME__ </h1>
<p> Query should like: </p>
<pre><code> __TEXT__ </code></pre>
<p> QueryBuilder returns: </p>
<pre><code> __BUILDER__ </code></pre>
';
$query_case = array();
try {
$db = new EnhancedDatabase(COMODOJO_DB_MODEL, COMODOJO_DB_HOST, COMODOJO_DB_PORT, COMODOJO_DB_NAME, COMODOJO_DB_USER, COMODOJO_DB_PASSWORD);
$db->autoClean();
array_push($query_case, array('name' => "Simple SELECT (GET)", 'text' => "SELECT this,is FROM test", 'builder' => $db->table('test')->keys(array('this', 'is'))->getQuery("GET")));
array_push($query_case, array('name' => "Simple SELECT DISTINCT (GET)", 'text' => "SELECT DISTINCT this,is FROM test", 'builder' => $db->table('test')->distinct()->keys(array('this', 'is'))->getQuery("GET")));
array_push($query_case, array('name' => "Simple SELECT with simple where condition (GET)", 'text' => "...", 'builder' => $db->table('test')->keys(array('this', 'is'))->where('this', '=', 'test')->getQuery("GET")));
array_push($query_case, array('name' => "Simple SELECT with double where condition (GET)", 'text' => "...", 'builder' => $db->table('test')->keys(array('this', 'is'))->where('this', '=', 'test')->andWhere('is', '!=', 'foo')->getQuery("GET")));
array_push($query_case, array('name' => "Simple SELECT with complex where condition (GET)", 'text' => "...", 'builder' => $db->table('test')->keys(array('this', 'is'))->where(array('this', 'LIKE', 'test%'), 'OR', array('this', 'IN', array(1, 10, 'boo', 'koo')))->orWhere('is', '!=', 'foo')->getQuery("GET")));
array_push($query_case, array('name' => "Simple SELECT with very complex where condition (GET)", 'text' => "...", 'builder' => $db->table('test')->keys(array('this', 'is', 'bla', 'cra'))->where(array('this', 'LIKE', 'test%'), 'OR', array('this', 'IN', array(1, 10, 'boo', 'koo')))->orWhere('is', '!=', 'foo')->andWhere(array('bla', 'NOT BETWEEN', array(1, 10000)), 'AND', array('cra', 'IS NOT', null))->getQuery("GET")));
array_push($query_case, array('name' => "SELECT with inner join condition (GET)", 'text' => "...", 'builder' => $db->table('test')->keys(array('this', 'is'))->join('INNER', 'test2', 't2')->getQuery("GET")));
$column_1 = new Column('foo', 'STRING');
$column_2 = new Column('koo', 'INTEGER');
$column_3 = new Column('id', 'INTEGER');
array_push($query_case, array('name' => "CREATE table", 'text' => "...", 'builder' => $db->column($column_3->length(32)->unsigned()->notNull()->autoIncrement()->primaryKey())->column($column_1->length(128)->defaultValue(NULL))->column($column_2->length(64)->notNull()->unsigned())->getQuery("CREATE", array("name" => 'testTable', "if_not_exists" => true, "engine" => null))));
array_push($query_case, array('name' => "DROP table", 'text' => "...", 'builder' => $db->table('testTable')->getQuery("DROP")));
array_push($query_case, array('name' => "EMPTY table", 'text' => "...", 'builder' => $db->table('testTable')->getQuery("EMPTY")));
} catch (DatabaseException $de) {
/**
* Get planned jobs
*
* @return array
* @throws \Comodojo\Exception\DatabaseException
*/
private static function getJobs()
{
$jobs = Cache::get();
if ($jobs !== false) {
return $jobs;
}
try {
$db = new EnhancedDatabase(EXTENDER_DATABASE_MODEL, EXTENDER_DATABASE_HOST, EXTENDER_DATABASE_PORT, EXTENDER_DATABASE_NAME, EXTENDER_DATABASE_USER, EXTENDER_DATABASE_PASS);
$result = $db->tablePrefix(EXTENDER_DATABASE_PREFIX)->table(EXTENDER_DATABASE_TABLE_JOBS)->keys(array("id", "name", "task", "description", "min", "hour", "dayofmonth", "month", "dayofweek", "year", "params", "lastrun", "firstrun"))->where("enabled", "=", true)->get();
} catch (DatabaseException $de) {
unset($db);
throw $de;
}
unset($db);
$jobs = $result->getData();
Cache::set($jobs);
return $jobs;
}