/**
* process event
*
* @param Charcoal_IEventContext $context
*
* @return boolean|Charcoal_Boolean
*/
public function processEvent($context)
{
/** @var GenerateModelEvent $event */
$event = $context->getEvent();
// get event parameters
$db_name = $event->getDatabase();
/** @var Charcoal_SmartGateway $gw */
$gw = $context->getComponent('smart_gateway@:charcoal:db');
// find models in project/app path
$find_path = Charcoal_EnumFindPath::FIND_PATH_PROJECT | Charcoal_EnumFindPath::FIND_PATH_APPLICATION;
$models = $gw->listModels($find_path);
// switch database
$gw->selectDatabase($db_name);
// create tables
foreach ($models as $model_name => $model) {
$table = $model->getTableName();
echo "creating table: [TABLE NAME]{$table} [MODEL NAME]{$model_name}\n";
$rows_affected = $gw->createTable(null, $model_name, true);
if ($rows_affected) {
echo "successfully created table[{$table}].\n";
} else {
echo "failed to create table[{$table}].\n";
}
}
return b(true);
}
/**
* execute tests
*
* @param string $action
* @param Charcoal_IEventContext $context
*
* @return boolean
*/
public function test($action, $context)
{
$action = us($action);
// temp file component
/** @var Charcoal_TempFileComponent $tf */
$tf = $context->getComponent('temp_file@:charcoal:file');
switch ($action) {
case "create":
$file = $tf->create("test");
$this->assertTrue($file->exists());
$this->assertTrue($file->canRead());
$this->assertEquals("test", $file->getContents());
return TRUE;
case "get_contents":
$temp_file = new Charcoal_File(CHARCOAL_TMP_DIR . '/tmpfile.txt');
$temp_file->putContents("test");
$tf->setFile($temp_file);
$this->assertEquals("test", $tf->getContents());
return TRUE;
case "put_contents":
$temp_file = new Charcoal_File(CHARCOAL_TMP_DIR . '/tmpfile.txt');
$tf->setFile($temp_file);
$tf->putContents("cat");
$this->assertTrue($temp_file->exists());
$this->assertTrue($temp_file->canRead());
$this->assertEquals("cat", $temp_file->getContents());
return TRUE;
}
return FALSE;
}
/**
* process event
*
* @param Charcoal_IEventContext $context
*
* @return boolean|Charcoal_Boolean
*/
public function processEvent($context)
{
/** @var GenerateModelEvent $event */
$event = $context->getEvent();
// get event parameters
$db_name = $event->getDatabase();
$table_name = $event->getTable();
$out_dir = $event->getTargetDir();
$entity = Charcoal_System::pascalCase($table_name);
$config_key = Charcoal_System::snakeCase($table_name);
$table_model_class_name = "{$entity}TableModel";
$table_dto_class_name = "{$entity}TableDTO";
$listing_dto_class_name = "{$entity}ListingDTO";
/** @var Charcoal_SmartGateway $gw */
$gw = $context->getComponent('smart_gateway@:charcoal:db');
//=======================================
// Mmake output directory
//=======================================
$out_dir = $this->prepareOutputDirectory($out_dir);
//=======================================
// confirm if the table exists
//=======================================
$sql = "SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ? ";
$params = array($table_name, $db_name);
$count = $gw->queryValue(NULL, $sql, $params);
if ($count < 1) {
print "[ERROR] Specified table '{$table_name}' does not exist in schema: '{$db_name}'. Please check your database settings." . PHP_EOL;
return b(true);
}
//=======================================
// Retrieve column information
//=======================================
$sql = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT ";
$sql .= " FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ? ";
$params = array($table_name, $db_name);
$colmn_attr_list = $gw->query(NULL, $sql, $params);
//=======================================
// Genarate table model file
//=======================================
$this->generateTableModelFile($table_name, $colmn_attr_list, $table_model_class_name, $table_dto_class_name, $out_dir);
//=======================================
// Genarate table DTO file
//=======================================
$this->generateTableDtolFile($table_name, $colmn_attr_list, $table_dto_class_name, $out_dir);
//=======================================
// Genarate listing DTO file
//=======================================
$this->generateListingDtolFile($table_name, $colmn_attr_list, $listing_dto_class_name, $out_dir);
//=======================================
// Genarate config file
//=======================================
$this->generateTableConfigFile($table_name, $table_model_class_name, $config_key, $out_dir);
return b(true);
}
/**
* Process events
*
* @param Charcoal_IEventContext $context event context
*/
public function processEvent($context)
{
$request = $context->getRequest();
$response = $context->getResponse();
$sequence = $context->getSequence();
$procedure = $context->getProcedure();
$event = $context->getEvent();
// form token component
$form_token = $context->getComponent(s('form_token@:charcoal:form'));
if ($event instanceof Charcoal_SetupEvent) {
$form_token->setupForm($sequence, $response);
return b(TRUE);
} elseif ($event instanceof Charcoal_AuthTokenEvent) {
return $form_token->checkToken($sequence, $request);
}
return b(FALSE);
}
/**
* execute tests
*
* @param string $action
* @param Charcoal_IEventContext $context
*
* @return boolean
*/
public function test($action, $context)
{
$action = us($action);
// file system component
/** @var Charcoal_FileSystemComponent $fs */
$fs = $context->getComponent('file_system@:charcoal:file');
switch ($action) {
case "create_dir":
$dir = $fs->createDirectory("hoge", "707");
echo "created dir: {$dir}" . PHP_EOL;
return TRUE;
case "create_file":
$file = $fs->createFile("test.txt", "Hello, File System!");
echo "created file: {$file}" . PHP_EOL;
return TRUE;
}
return FALSE;
}
/**
* テスト
*
* @param string $action
* @param Charcoal_IEventContext $context
*
* @return boolean
*/
public function test($action, $context)
{
$action = us($action);
/** @var Charcoal_SmartGateway $gw */
$gw = $context->getComponent('smart_gateway@:charcoal:db');
$gw->reset();
switch ($action) {
case "commit":
// トランザクション開始
$gw->beginTrans();
$gw->autoCommit(FALSE);
// 初期データの確認
$result = $gw->query(NULL, "SELECT * FROM posts WHERE post_id=1");
$this->assertEquals('stk2k', $result[0]['post_user'], true);
// 更新
$gw->execute(NULL, "UPDATE posts set post_user = '******' where post_id = 1");
// コミット
$gw->commitTrans();
// 検索
$result = $gw->query(NULL, "SELECT * FROM posts WHERE post_id=1");
$this->assertEquals('hoge', $result[0]['post_user']);
return TRUE;
case "query":
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query("query #1", $sql);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(1, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "select":
$where = "blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("select #1", 'blogs', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(1, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "select_alias":
$where = "b.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("select_alias #1", 'blogs as b', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(1, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "select_alias_forupdate":
$where = "blog_name like ?";
$criteria = new Charcoal_SQLCriteria($where, array("My First Blog"));
$result = $gw->findAllForUpdate("select_alias_forupdate #1", 'blogs as b', $criteria);
foreach ($result as $row) {
print print_r($row, true) . PHP_EOL;
}
return TRUE;
case "inner_join":
$where = "blogs.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("inner_join #1", 'blogs + posts on "blogs.blog_id = posts.blog_id" + comments on "posts.post_id = comments.post_id"', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(3, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "left_join":
$where = "blogs.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("left_join #1", 'blogs (+ posts on "blogs.blog_id = posts.blog_id"', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(2, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "right_join":
$where = "blogs.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("right_join #1", 'blogs +) posts on "blogs.blog_id = posts.blog_id"', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
// 評価
$this->assertEquals(2, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "inner_join_alias":
$where = "b.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("inner_join_alias #1", 'blogs as b + posts as p on "b.blog_id = p.blog_id"', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(2, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "inner_join_multi":
$where = "blogs.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("inner_join_multi #1", 'blogs + posts on "blogs.blog_id = posts.blog_id" + comments on "posts.post_id = comments.post_id"', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(3, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "inner_join_multi_alias":
$where = "b.blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array(1));
$result = $gw->findAll("inner_join_multi_alias #1", 'blogs as b + posts as p on "b.blog_id = p.blog_id" + comments as c on "p.post_id = c.post_id"', $criteria);
$blog_name = '';
foreach ($result as $row) {
$blog_name = $row['blog_name'];
echo "blog_name:{$blog_name}" . eol();
}
$this->assertEquals(3, count($result));
$this->assertEquals("my blog", $blog_name);
return TRUE;
case "count":
$criteria = new Charcoal_SQLCriteria();
$result = $gw->count("count #1", 'posts', $criteria, '*');
echo "result:" . $result . eol();
// 評価
$this->assertEquals(3, $result);
return TRUE;
case "max":
$criteria = new Charcoal_SQLCriteria();
$result = $gw->max("max #1", 'posts', $criteria, 'favorite');
echo "result:" . $result . eol();
$this->assertEquals(11, $result);
return TRUE;
case "min":
$criteria = new Charcoal_SQLCriteria();
$result = $gw->min("min #1", 'posts', $criteria, 'favorite');
echo "result:" . $result . eol();
// 評価
$this->assertEquals(5, $result);
return TRUE;
case "avg":
$criteria = new Charcoal_SQLCriteria();
$result = $gw->avg("avg #1", 'posts', $criteria, 'favorite');
echo "result:" . $result . eol();
$this->assertEquals(8, $result);
return TRUE;
case "count_alias":
$criteria = new Charcoal_SQLCriteria();
$result = $gw->count("count_alias #1", 'posts as p', $criteria, '*');
echo "result:" . $result . eol();
$this->assertEquals(3, $result);
return TRUE;
case "max_alias":
$criteria = new Charcoal_SQLCriteria();
$result = $gw->max("max_alias #1", 'posts as p + comments as c on "p.post_id = c.post_id"', $criteria, 'favorite');
echo "result:" . $result . eol();
$this->assertEquals(11, $result);
return TRUE;
case "find_first":
$criteria = new Charcoal_SQLCriteria();
$criteria->setOrderBy('favorite');
$result = $gw->findFirst("find_first #1", 'posts', $criteria);
echo "result:" . $result['post_title'] . eol();
$this->assertEquals('How does it work?', $result['post_title']);
return TRUE;
case "find_by_id":
$result = $gw->findAll("find_by_id #1", 'posts', new Charcoal_SQLCriteria());
foreach ($result as $row) {
$blog_id = $row['blog_id'];
$blog = $gw->findById("find_by_id #2", 'blogs', $blog_id);
$this->assertEquals($this->blog_name_expected[$blog_id], $blog['blog_name']);
$blog_category_id = $blog['blog_category_id'];
$category = $gw->findById("find_by_id #3", 'blog_category', $blog_category_id);
$this->assertEquals($this->category_name_expected[$blog_category_id], $category['blog_category_name']);
}
return TRUE;
case "save":
// save by INSERT
$dto = new PostTableDTO();
$dto->post_title = 'New Post';
$dto->post_body = 'New Post Body';
$dto->post_user = '******';
$count = $gw->count(NULL, 'posts', new Charcoal_SQLCriteria(), NULL);
//echo $gw->popSQLHistory() . PHP_EOL;
//echo "count(before save):" . $count . eol();
$this->assertEquals(3, $count);
$new_id = $gw->save(NULL, "posts", $dto);
//echo $gw->popSQLHistory() . PHP_EOL;
$criteria = new Charcoal_SQLCriteria();
$criteria->setWhere("post_id = ?");
$criteria->setParams(array($new_id));
$new_record = $gw->findFirst(NULL, 'posts', $criteria);
//echo $gw->popSQLHistory() . PHP_EOL;
$count = $gw->count(NULL, 'posts', new Charcoal_SQLCriteria());
//echo $gw->popSQLHistory() . PHP_EOL;
//echo "count(after save):" . $count . eol();
$this->assertEquals(4, $count);
$this->assertEquals(4, $new_record['post_id']);
$this->assertEquals('New Post', $new_record['post_title']);
$this->assertEquals('New Post Body', $new_record['post_body']);
$this->assertEquals('Ichiro', $new_record['post_user']);
// save by UPDATE
$dto->post_id = $new_id;
$dto->post_date = array('function', 'now');
$dto->post_user = array('value', 'null');
$dto->post_body = array(1, 2, "apple", array('name' => 'stk2k'), array('sex' => 'male'));
$time_before_update = time();
sleep(1);
$gw->save(NULL, "posts", $dto);
sleep(1);
$time_after_update = time();
$criteria = new Charcoal_SQLCriteria();
$criteria->setWhere("post_id = ?");
$criteria->setParams(array($dto->post_id));
$the_record = $gw->findFirst(NULL, 'posts', $criteria);
// check post date
$this->assertGreaterThan($time_before_update, strtotime($the_record['post_date']));
$this->assertLessThan($time_after_update, strtotime($the_record['post_date']));
// check post user
$this->assertEquals(NULL, $the_record['post_user']);
// check post body(JSON)
$post_body = json_decode($the_record['post_body'], true);
$expeced = array(1, 2, "apple", array('name' => 'stk2k'), array('sex' => 'male'));
$this->assertEquals($expeced, $post_body);
return TRUE;
case "fluent_api":
$gw->select("b.blog_name, b.post_total, p.post_user, p.post_title")->from(s("blogs"), s("b"))->leftJoin(s("posts"), s("p"))->on("b.blog_id = p.blog_id")->where()->gt(s("b.post_total"), i(1))->orderBy(s("b.post_total DESC"))->limit(i(5))->offset(i(0))->prepareExecute()->findFirst("fluent_api #1")->result();
echo $gw->popSQLHistory() . PHP_EOL;
// echo print_r($rs,true) . eol();
// echo "last SQL:" . $gw->getLastSQL() . eol();
// echo "last params:" . $gw->getLastParams() . eol();
return TRUE;
case "recordset_query":
$rsfactory1 = $gw->createRecordsetFactory();
// fetch mode: FETCHMODE_BOTH
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query("recordset_query #1", $sql, NULL, $rsfactory1, NULL);
echo $gw->popSQLHistory() . PHP_EOL;
foreach ($result as $row) {
$this->assertEquals($this->blog_name_expected[$row['blog_id']], $row['blog_name']);
}
$rsfactory2 = $gw->createRecordsetFactory(Charcoal_IRecordset::FETCHMODE_ASSOC);
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query("recordset_query #2", $sql, NULL, $rsfactory2, NULL);
echo $gw->popSQLHistory() . PHP_EOL;
foreach ($result as $row) {
$this->assertEquals($this->blog_name_expected[$row['blog_id']], $row['blog_name']);
}
$rsfactory3 = $gw->createRecordsetFactory(Charcoal_IRecordset::FETCHMODE_NUM);
$sql = "SELECT blog_id, blog_name, post_total FROM blogs WHERE blog_id = 1";
$result = $gw->query("recordset_query #3", $sql, NULL, $rsfactory3, NULL);
echo $gw->popSQLHistory() . PHP_EOL;
foreach ($result as $row) {
$this->assertEquals($this->blog_name_expected[$row[0]], $row[1]);
}
return TRUE;
case "recordset_find":
$rsfactory1 = $gw->createRecordsetFactory();
// fetch mode: FETCHMODE_ASSOC
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query("recordset_find #1", $sql, NULL, $rsfactory1, NULL);
echo $gw->popSQLHistory() . PHP_EOL;
foreach ($result as $row) {
$this->assertEquals($this->blog_name_expected[$row['blog_id']], $row['blog_name']);
}
$rsfactory2 = $gw->createRecordsetFactory(Charcoal_IRecordset::FETCHMODE_ASSOC);
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query("recordset_find #2", $sql, NULL, $rsfactory2, NULL);
echo $gw->popSQLHistory() . PHP_EOL;
foreach ($result as $row) {
$this->assertEquals($this->blog_name_expected[$row['blog_id']], $row['blog_name']);
}
$rsfactory3 = $gw->createRecordsetFactory(Charcoal_IRecordset::FETCHMODE_NUM);
$sql = "SELECT blog_id, blog_name, post_total FROM blogs WHERE blog_id = 1";
$result = $gw->query("recordset_find #3", $sql, NULL, $rsfactory3, NULL);
echo $gw->popSQLHistory() . PHP_EOL;
foreach ($result as $row) {
$this->assertEquals($this->blog_name_expected[$row[0]], $row[1]);
}
return TRUE;
case "nested_recordset_query":
$rsfactory1 = $gw->createRecordsetFactory();
// fetch mode: FETCHMODE_BOTH
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query(NULL, $sql, NULL, $rsfactory1);
foreach ($result as $row) {
$this->assertEquals(1, $row['blog_id']);
$this->assertEquals($this->blog_name_expected[1], $row['blog_name']);
$blog_category_id = $row['blog_category_id'];
$sql = "SELECT * FROM blog_category WHERE blog_category_id = ?";
$result2 = $gw->query(NULL, $sql, array($blog_category_id), $rsfactory1);
foreach ($result2 as $row2) {
$this->assertEquals($blog_category_id, $row2['blog_category_id']);
$this->assertEquals($this->category_name_expected[$blog_category_id], $row2['blog_category_name']);
}
}
/*
$rsfactory2 = $gw->createRecordsetFactory( Charcoal_IRecordset::FETCHMODE_ASSOC );
$sql = "SELECT * FROM blogs WHERE blog_id = 1";
$result = $gw->query( $sql, NULL, $rsfactory2 );
foreach( $result as $row ){
$this->assertEquals( 1, $row['blog_id'] );
$this->assertEquals( $this->blog_name_expected[1], $row['blog_name'] );
$blog_category_id = $row['blog_category_id'];
$sql = "SELECT * FROM blog_category WHERE blog_category_id = ?";
$result2 = $gw->query( $sql, array($blog_category_id), $rsfactory2 );
foreach( $result2 as $row ){
$this->assertEquals( $blog_category_id, $row['blog_category_id'] );
$this->assertEquals( $this->category_name_expected[$blog_category_id], $row['blog_category_name'] );
}
}
$rsfactory3 = $gw->createRecordsetFactory( Charcoal_IRecordset::FETCHMODE_NUM );
$sql = "SELECT blog_id, blog_name, post_total, blog_category_id FROM blogs WHERE blog_id = 1";
$result = $gw->query( $sql, NULL, $rsfactory3 );
foreach( $result as $row ){
$this->assertEquals( 1, $row[0] );
$this->assertEquals( $this->blog_name_expected[1], $row[1] );
$blog_category_id = $row[3];
$sql = "SELECT blog_category_id, blog_category_name FROM blog_category WHERE blog_category_id = ?";
$result2 = $gw->query( $sql, array($blog_category_id), $rsfactory3 );
foreach( $result2 as $row ){
$this->assertEquals( $blog_category_id, $row[0] );
$this->assertEquals( $this->category_name_expected[$blog_category_id], $row[1] );
}
}
*/
$another_ds = $context->createObject('PDO', 'data_source');
$default_ds_config = $gw->getDataSource()->getConfig();
//ad($default_ds_config);
$config = $context->createConfig($default_ds_config->getAll());
$GLOBALS['hoge'] = true;
ad($config);
$config->set('token_key', 'foo');
$another_ds->configure($config);
return TRUE;
case "nested_recordset_find":
return TRUE;
case "delete_by_id":
$gw->deleteById("delete_by_id #1", 'blog_category', 1);
echo $gw->popSQLHistory() . PHP_EOL;
$criteria = new Charcoal_SQLCriteria('');
$cnt = $gw->count("delete_by_id #2", 'blog_category', $criteria);
echo 'cnt:' . $cnt . PHP_EOL;
echo $gw->popSQLHistory() . PHP_EOL;
return TRUE;
case "delete_by_ids":
$gw->deleteByIds("delete_by_ids #1", 'blog_category', array(1, 3));
echo $gw->popSQLHistory() . PHP_EOL;
$criteria = new Charcoal_SQLCriteria('');
$cnt = $gw->count("delete_by_ids #2", 'blog_category', $criteria);
echo 'cnt:' . $cnt . PHP_EOL;
echo $gw->popSQLHistory() . PHP_EOL;
return TRUE;
/* ------------------------------
increment/decrement test
*/
/* ------------------------------
increment/decrement test
*/
case "increment_field":
// increment post_toal by 1
$gw->incrementField("increment_field #1", 'blogs', 1, 'post_total');
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 2 => 3
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(3, $post_total);
// increment post_toal by 2
$gw->incrementField("increment_field #1", 'blogs', 1, 'post_total', 2);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 3 => 5
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(5, $post_total);
// increment post_toal by -5
$gw->incrementField("increment_field #1", 'blogs', 1, 'post_total', -5);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 5 => 0
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(0, $post_total);
return TRUE;
case "increment_field_by":
// increment post_toal by 1
$gw->incrementFieldBy("increment_field_by #1", 'blogs', 'post_total', 'blog_name', 'my blog');
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 2 => 3
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(3, $post_total);
// increment post_toal by 2
$gw->incrementFieldBy("increment_field_by #2", 'blogs', 'post_total', 'blog_name', 'my blog', 2);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 3 => 5
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(5, $post_total);
// increment post_toal by -5
$gw->incrementFieldBy("increment_field_by #3", 'blogs', 'post_total', 'blog_name', 'my blog', -5);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 5 => 0
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(0, $post_total);
return TRUE;
case "decrement_field":
// decrement post_toal by 1
$gw->decrementField("decrement_field #1", 'blogs', 1, 'post_total');
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 2 => 1
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(1, $post_total);
// decrement post_toal by 2
$gw->decrementField("decrement_field #2", 'blogs', 1, 'post_total', 2);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 1 => -1
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
var_dump($post_total);
$this->assertEquals(-1, $post_total);
// decrement post_toal by -5
$gw->decrementField("decrement_field #3", 'blogs', 1, 'post_total', -5);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be -1 => 4
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(4, $post_total);
return TRUE;
case "decrement_field_by":
// decrement post_toal by 1
$gw->decrementFieldBy("decrement_field_by #1", 'blogs', 'post_total', 'blog_name', 'my blog');
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 2 => 1
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(1, $post_total);
// decrement post_toal by 2
$gw->decrementFieldBy("decrement_field_by #2", 'blogs', 'post_total', 'blog_name', 'my blog', 2);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be 1 => -1
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//ar_dump($post_total);
$this->assertEquals(-1, $post_total);
// decrement post_toal by -5
$gw->decrementFieldBy("decrement_field_by #3", 'blogs', 'post_total', 'blog_name', 'my blog', -5);
//echo $gw->popSQLHistory() . PHP_EOL;
// post_total must be -1 => 4
$pdo = new DuplicatedPdo($gw);
$post_total = $pdo->queryValue("SELECT post_total FROM blogs WHERE blog_id = ?", [1]);
//var_dump($post_total);
$this->assertEquals(4, $post_total);
return TRUE;
/* ------------------------------
insert test
*/
/* ------------------------------
insert test
*/
case "insert":
// INSERT
$blog = new BlogTableDTO();
$blog->blog_name = 'my new blog';
$blog->post_total = 123;
$blog->created_date = array('function', 'now');
$blog->modified_date = array('function', 'now');
$time_insert_before = time();
sleep(1);
$gw->insert(NULL, 'blogs', $blog);
sleep(1);
$time_insert_after = time();
$blog_id = $gw->getLastInsertId();
$this->assertEquals(3, $blog_id);
// INSERTの確認
$where = "blog_id = ?";
$criteria = new Charcoal_SQLCriteria($where, array($blog_id));
$blog = $gw->findFirst("insert #1", 'blogs', $criteria);
$this->assertEquals(3, $blog['blog_id']);
$this->assertEquals('my new blog', $blog['blog_name']);
$this->assertEquals(123, $blog['post_total']);
$this->assertGreaterThan($time_insert_before, strtotime($blog['created_date']));
$this->assertLessThan($time_insert_after, strtotime($blog['created_date']));
return TRUE;
case "bulk_insert":
// BULK INSERT
$blogs = array();
$blog = new BlogTableDTO();
$blog->blog_name = 'my new blog';
$blog->post_total = 123;
$blog->created_date = array('function', 'now');
$blog->modified_date = array('function', 'now');
$blogs[] = $blog;
$blog = new BlogTableDTO();
$blog->blog_name = 'my new blog2';
$blog->post_total = 44;
$blog->created_date = array('function', 'now');
$blog->modified_date = array('function', 'now');
$blogs[] = $blog;
$time_insert_before = time();
sleep(1);
$inserted_rows = $gw->insertAll(NULL, 'blogs', $blogs);
$this->assertEquals(2, $inserted_rows);
sleep(1);
$time_insert_after = time();
// INSERTの確認
$where = "blog_name like 'my new blog%'";
$criteria = new Charcoal_SQLCriteria($where, NULL, 'blog_id');
$result = $gw->findAll(NULL, 'blogs', $criteria);
$blog = array_shift($result);
$this->assertEquals(3, $blog['blog_id']);
$this->assertEquals('my new blog', $blog['blog_name']);
$this->assertEquals(123, $blog['post_total']);
$this->assertGreaterThan($time_insert_before, strtotime($blog['created_date']));
$this->assertLessThan($time_insert_after, strtotime($blog['created_date']));
$blog = array_shift($result);
$this->assertEquals(4, $blog['blog_id']);
$this->assertEquals('my new blog2', $blog['blog_name']);
$this->assertEquals(44, $blog['post_total']);
$this->assertGreaterThan($time_insert_before, strtotime($blog['created_date']));
$this->assertLessThan($time_insert_after, strtotime($blog['created_date']));
return TRUE;
/* ------------------------------
update test
*/
/* ------------------------------
update test
*/
case "update_field":
echo "selected database:" . $gw->getSelectedDatabase() . PHP_EOL;
// update a field
$gw->updateField('update_field #1', 'blogs', 1, 'post_total', 5);
echo $gw->popSQLHistory() . PHP_EOL;
// confirm result
$pdo = new DuplicatedPdo($gw);
$rows = $pdo->queryRows("SELECT * FROM blogs");
//var_dump($rows);
foreach ($rows as $row) {
switch ($row['blog_id']) {
case 1:
$this->assertEquals(1, $row['blog_category_id']);
$this->assertEquals('my blog', $row['blog_name']);
$this->assertEquals(5, $row['post_total']);
break;
case 2:
$this->assertEquals(2, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(1, $row['post_total']);
break;
}
}
return TRUE;
case "update_fields":
// update fields
$fields = array('post_total' => 999, 'blog_name' => 'super popular blog');
$gw->updateFields('update_fields #1', 'blogs', 1, $fields);
echo $gw->popSQLHistory() . PHP_EOL;
// confirm result
$pdo = new DuplicatedPdo($gw);
$rows = $pdo->queryRows("SELECT * FROM blogs");
//var_dump($rows);
foreach ($rows as $row) {
switch ($row['blog_id']) {
case 1:
$this->assertEquals(1, $row['blog_category_id']);
$this->assertEquals('super popular blog', $row['blog_name']);
$this->assertEquals(999, $row['post_total']);
break;
case 2:
$this->assertEquals(2, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(1, $row['post_total']);
break;
}
}
return TRUE;
case "update_field_by":
// update fields in a row
$rows = $gw->updateFieldBy('update_field_by #1', 'blogs', 'blog_name', 'another blog', 'blog_id', 1);
echo $gw->popSQLHistory() . PHP_EOL;
$this->assertEquals(1, $rows);
// confirm result
$pdo = new DuplicatedPdo($gw);
$rows = $pdo->queryRows("SELECT * FROM blogs");
//var_dump($rows);
foreach ($rows as $row) {
switch ($row['blog_id']) {
case 1:
$this->assertEquals(1, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(2, $row['post_total']);
break;
case 2:
$this->assertEquals(2, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(1, $row['post_total']);
break;
}
}
// update fields in multiple rows
$rows = $gw->updateFieldBy('update_field_by #1', 'blogs', 'post_total', 3, 'blog_name', 'another blog');
echo $gw->popSQLHistory() . PHP_EOL;
$this->assertEquals(2, $rows);
// confirm result
$pdo = new DuplicatedPdo($gw);
$rows = $pdo->queryRows("SELECT * FROM blogs");
//var_dump($rows);
foreach ($rows as $row) {
switch ($row['blog_id']) {
case 1:
$this->assertEquals(1, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(3, $row['post_total']);
break;
case 2:
$this->assertEquals(2, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(3, $row['post_total']);
break;
}
}
return TRUE;
case "update_field_now":
$start_time = time();
// update field to current time
$gw->updateFieldNow('update_field_now #1', 'blogs', 1, 'modified_date');
echo $gw->popSQLHistory() . PHP_EOL;
// confirm result
$pdo = new DuplicatedPdo($gw);
$rows = $pdo->queryRows("SELECT * FROM blogs");
//var_dump($rows);
foreach ($rows as $row) {
switch ($row['blog_id']) {
case 1:
$this->assertEquals(1, $row['blog_category_id']);
$this->assertEquals('my blog', $row['blog_name']);
$this->assertEquals(2, $row['post_total']);
$this->assertEquals('2010-01-02 12:56:12', $row['created_date']);
$this->assertLessThanOrEqual($start_time, strtotime($row['modified_date']));
$this->assertGreaterThanOrEqual(time(), strtotime($row['modified_date']));
break;
case 2:
$this->assertEquals(2, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(1, $row['post_total']);
$this->assertEquals('2010-01-12 02:12:32', $row['created_date']);
$this->assertEquals('2010-01-15 11:42:02', $row['modified_date']);
break;
}
}
return TRUE;
case "update_field_null":
// update fields in a row
$gw->updateFieldNull('update_field_null #1', 'blogs', 1, 'blog_name');
echo $gw->popSQLHistory() . PHP_EOL;
// confirm result
$pdo = new DuplicatedPdo($gw);
$rows = $pdo->queryRows("SELECT * FROM blogs");
//var_dump($rows);
foreach ($rows as $row) {
switch ($row['blog_id']) {
case 1:
$this->assertEquals(1, $row['blog_category_id']);
$this->assertEquals(null, $row['blog_name']);
$this->assertEquals(2, $row['post_total']);
break;
case 2:
$this->assertEquals(2, $row['blog_category_id']);
$this->assertEquals('another blog', $row['blog_name']);
$this->assertEquals(1, $row['post_total']);
break;
}
}
return TRUE;
/* ------------------------------
select db test
*/
/* ------------------------------
select db test
*/
case "select_db":
echo "selected database:" . $gw->getSelectedDatabase() . PHP_EOL;
$exists = $gw->existsTable('item');
$this->assertEquals(true, $exists);
$exists = $gw->existsTable('item2');
$this->assertEquals(false, $exists);
$gw->selectDatabase('test2');
echo "selected database:" . $gw->getSelectedDatabase() . PHP_EOL;
$exists = $gw->existsTable('item');
$this->assertEquals(false, $exists);
$exists = $gw->existsTable('item2');
$this->assertEquals(true, $exists);
return TRUE;
/* ------------------------------
list models test
*/
/* ------------------------------
list models test
*/
case "list_models_all":
$models = $gw->listModels();
$this->assertEquals(7, count($models));
$bundled_models = array('Charcoal_SessionTableModel', 'BlogCategoryTableModel', 'BlogTableModel', 'CommentTableModel', 'ItemTableModel', 'Item2TableModel', 'PostTableModel', 'TestTableModel');
foreach ($models as $model) {
$this->assertTrue($model instanceof Charcoal_ITableModel);
$clazz = get_class($model);
echo $clazz . PHP_EOL;
$this->assertTrue(in_array($clazz, $bundled_models));
}
return TRUE;
case "list_models_framework":
$models = $gw->listModels(Charcoal_EnumFindPath::FIND_PATH_FRAMEWORK);
$this->assertEquals(1, count($models));
$bundled_models = array('Charcoal_SessionTableModel');
foreach ($models as $model) {
$this->assertTrue($model instanceof Charcoal_ITableModel);
$clazz = get_class($model);
echo $clazz . PHP_EOL;
$this->assertTrue(in_array($clazz, $bundled_models));
}
return TRUE;
case "list_models_project":
$models = $gw->listModels(Charcoal_EnumFindPath::FIND_PATH_PROJECT);
$this->assertEquals(6, count($models));
$bundled_models = array('BlogCategoryTableModel', 'BlogTableModel', 'CommentTableModel', 'ItemTableModel', 'Item2TableModel', 'PostTableModel', 'TestTableModel');
foreach ($models as $model) {
$this->assertTrue($model instanceof Charcoal_ITableModel);
$clazz = get_class($model);
echo $clazz . PHP_EOL;
$this->assertTrue(in_array($clazz, $bundled_models));
}
return TRUE;
case "list_models_application":
$models = $gw->listModels(Charcoal_EnumFindPath::FIND_PATH_APPLICATION);
$this->assertEquals(0, count($models));
return TRUE;
default:
break;
}
return FALSE;
}
/**
* execute tests
*
* @param string $action
* @param Charcoal_IEventContext $context
*
* @return boolean
*/
public function test($action, $context)
{
$action = us($action);
/** @var Charcoal_SmartGateway $gw */
$gw = $context->getComponent('smart_gateway@:charcoal:db');
switch ($action) {
case "dml_truncate":
$rows = $this->countTableRows('test');
$this->assertEquals(1, $rows);
$gw->truncateTable(__FILE__ . '(' . __LINE__ . ')', 'test');
$rows = $this->countTableRows('test');
$this->assertEquals(0, $rows);
return TRUE;
case "dml_bulk_insert":
// テーブルの行数は1になっているはず
$this->assertEquals(1, $this->countTableRows('test'));
// appleの行数は0になっているはず
$this->assertEquals(0, $this->countRows('test', 'name = "apple"'));
// grapeの行数は0になっているはず
$this->assertEquals(0, $this->countRows('test', 'name = "grape"'));
// orageの行数は0になっているはず
$this->assertEquals(0, $this->countRows('test', 'name = "orage"'));
// melonの行数は0になっているはず
$this->assertEquals(0, $this->countRows('test', 'name = "melon"'));
// apple/grape/orage/melonのデータをバルクインサート
$data_set = array(new Charcoal_DTO(['name' => 'apple', 'price' => 100]), new Charcoal_DTO(['name' => 'grape', 'price' => 200]), new Charcoal_DTO(['name' => 'orage', 'price' => 150]), new Charcoal_DTO(['name' => 'melon', 'price' => 1800]));
$gw->insertAll(null, 'test', $data_set);
// テーブルの行数は5になっているはず
$this->assertEquals(5, $this->countTableRows('test'));
// appleの行数は1になっているはず
$this->assertEquals(1, $this->countRows('test', 'name = "apple"'));
// grapeの行数は1になっているはず
$this->assertEquals(1, $this->countRows('test', 'name = "grape"'));
// orageの行数は1になっているはず
$this->assertEquals(1, $this->countRows('test', 'name = "orage"'));
// melonの行数は1になっているはず
$this->assertEquals(1, $this->countRows('test', 'name = "melon"'));
return TRUE;
}
return FALSE;
}
/**
* process event
*
* @param Charcoal_IEventContext $context
*
* @return boolean|Charcoal_Boolean
*/
public function processEvent($context)
{
/** @var ShowTableEvent $event */
$event = $context->getEvent();
// get event parameters
$db_name = $event->getDatabase();
$table_name = $event->getTable();
/** @var Charcoal_SmartGateway $gw */
$gw = $context->getComponent('smart_gateway@:charcoal:db');
//=======================================
// confirm if the table exists
//=======================================
$sql = "SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ? ";
$params = array($table_name, $db_name);
$count = $gw->queryValue(NULL, $sql, $params);
if ($count < 1) {
print "[ERROR] Specified table '{$table_name}' does not exist in schema: '{$db_name}'. Maybe table name is wrong?" . PHP_EOL;
return b(true);
}
print "Showing table information." . PHP_EOL . PHP_EOL;
print "=========================================" . PHP_EOL;
print "Table description: {$table_name}" . PHP_EOL . PHP_EOL;
//=======================================
// Retrieve column information
//=======================================
$sql = "SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT ";
$sql .= " FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ? ";
$params = array($table_name, $db_name);
$colmn_attr_list = $gw->query(NULL, $sql, $params);
// get max length
$field_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'COLUMN_NAME', 'Field Name');
$type_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'COLUMN_TYPE', 'Type');
$null_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'IS_NULLABLE', 'Null');
$key_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'COLUMN_KEY', 'Key');
$default_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'COLUMN_DEFAULT', 'Default');
$extra_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'EXTRA', 'Extra');
$comment_max_width = $this->getMaxLengthOfMeta($colmn_attr_list, 'COLUMN_COMMENT', 'Comment');
print "null_max_width: {$null_max_width}\n";
print "key_max_width: {$key_max_width}\n";
print str_pad("Field Name", $field_max_width + 1) . " ";
print str_pad("Type", $type_max_width + 1) . " ";
print str_pad("Null", $null_max_width + 1) . " ";
print str_pad("Key", $key_max_width + 1) . " ";
print str_pad("Default", $default_max_width + 1) . " ";
print str_pad("Extra", $extra_max_width + 1) . " ";
print str_pad("Comment", $comment_max_width + 1) . PHP_EOL;
$line_width = $field_max_width + $type_max_width + $null_max_width + $key_max_width + $default_max_width + $extra_max_width + $comment_max_width + 15;
print str_repeat("-", $line_width) . PHP_EOL;
$conv = Charcoal_EncodingConverter::fromString($this->getSandbox(), 'DB', 'CLI');
foreach ($colmn_attr_list as $colmn_attr) {
$field = $colmn_attr['COLUMN_NAME'];
$type = $colmn_attr['COLUMN_TYPE'];
$null = $colmn_attr['IS_NULLABLE'];
$key = $colmn_attr['COLUMN_KEY'];
$default = $colmn_attr['COLUMN_DEFAULT'];
$extra = $colmn_attr['EXTRA'];
$comment = $colmn_attr['COLUMN_COMMENT'];
$comment = $conv->convert($comment);
$field = str_pad($field, $field_max_width + 1);
$type = str_pad($type, $type_max_width + 1);
$null = str_pad($null, $null_max_width + 1);
$key = str_pad($key, $key_max_width + 1);
$default = str_pad($default, $default_max_width + 1);
$extra = str_pad($extra, $extra_max_width + 1);
$comment = str_pad($comment, $comment_max_width + 1);
print "{$field} {$type} {$null} {$key} {$default} {$extra} {$comment}" . PHP_EOL;
}
print PHP_EOL . "Done." . PHP_EOL;
return b(true);
}
