public function testMultipleCreate()
{
$this->db->autoClean();
$people = $this->db->tablePrefix('test_')->table('people')->column(Column::create('id', 'INTEGER')->unsigned()->autoIncrement()->primaryKey())->column(Column::create('firstname', 'STRING')->length(64)->notNull())->column(Column::create('lastname', 'STRING')->length(64)->defaultValue(null))->create(true, 'InnoDB');
$this->assertInstanceOf('\\Comodojo\\Database\\QueryResult', $people);
$planets = $this->db->tablePrefix('test_')->table('planets')->column(Column::create('id', 'INTEGER')->unsigned()->autoIncrement()->primaryKey())->column(Column::create('name', 'STRING')->length(64)->notNull())->create(true, 'InnoDB');
$this->assertInstanceOf('\\Comodojo\\Database\\QueryResult', $planets);
$planets = $this->db->tablePrefix('test_')->table('starships')->column(Column::create('id', 'INTEGER')->unsigned()->autoIncrement()->primaryKey())->column(Column::create('name', 'STRING')->length(64)->notNull())->column(Column::create('speed', 'INTEGER')->defaultValue(null))->column(Column::create('capacity', 'INTEGER')->defaultValue(null))->create(true, 'InnoDB');
$this->assertInstanceOf('\\Comodojo\\Database\\QueryResult', $planets);
}
public static function installDatabase()
{
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);
}
/**
* Add a column in columns' list
*
* @param \Comodojo\Database\QueryBuilder\Column $column
*
* @return \Comodojo\Database\EnhancedDatabase
*/
public function column(\Comodojo\Database\QueryBuilder\Column $column)
{
array_push($this->columns, $column->getColumnDefinition($this->model));
return $this;
}
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);
<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) {
die("comodojo exception: " . $de->getMessage());
} catch (Exception $e) {
die($e->getMessage());
}
foreach ($query_case as $case) {
echo str_replace(array('__NAME__', '__TEXT__', '__BUILDER__'), array($case['name'], $case['text'], $case['builder']), $query_output_pattern);
}