Last active
August 16, 2016 04:20
-
-
Save pfz/310cba1a8d0e6cb5a9c1617c713b249c to your computer and use it in GitHub Desktop.
All in one script to show bug with Phalcon 3.0 on type casting bug and use of mysql BIT(1)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/* | |
All in one script to show bug with Phalcon 3.0 on type casting bug and use of mysql BIT(1). | |
My purpose is to get a configuration allowing a coherent type binding, while being able to use snapshots in models. | |
Default configuration works but is not satisfying for reason #1 : | |
[ | |
'use_bit' => true, | |
'use_dynamic_update' => false, (default) | |
'cast_on_hydrate' => true, (forced) | |
'db' => [ 'options' => [\PDO::ATTR_EMULATE_PREPARES => true (default) ], | |
] | |
Bugs | |
---- | |
1 - Whatever the settings, on Model::save(), *->id (last_insert_id) is always a string, should be cast. Not really phalcon's fault here, more a PDO bug I assume. Would be great though. | |
2 - can't use BIT without ATTR_EMULATE_PREPARES (false). Inserts and updates are not processed. | |
3 - castOnHydrate and useDynamicUpdate can't be used(true) simultaneously: | |
Get this error while using Model::find() | |
`PHP Fatal error: Uncaught Phalcon\Mvc\Model\Exception: Column 'id' doesn't make part of the column map ... ` | |
To test one bug, set options, then `php phalcon_orm_datatype_bug.php`. | |
It will output what's ok or not. | |
*/ | |
$options = [ | |
/* Use BIT(1) or TINYINT(1) for workaround reason */ | |
'use_bit' => true, | |
/* Casting and optimisations */ | |
'use_dynamic_update' => false, | |
'cast_on_hydrate' => true, | |
'db' => [ | |
'options' => [\PDO::ATTR_EMULATE_PREPARES => true], | |
'host' => '127.0.0.1', | |
'username' => 'testdb', | |
'password' => 'testdb', | |
'dbname' => 'testdb', | |
], | |
/* Echo SQL queries */ | |
'debug_sql' => false, | |
]; | |
$db = init($options); | |
$w1 = $options['use_dynamic_update'] ? new WrongTableDynamic() : new WrongTable(); | |
$w1->save(); | |
if ($w1->id <= 0) { | |
//$error = $db->getInternalHandler()->errorInfo(); | |
echo ' ** FAIL TO INSERT. Force using direct query', PHP_EOL; | |
$db->query('INSERT INTO wrong_table(title, foo, bar) VALUES ("foobar", b\'0\', b\'1\')'); | |
} | |
// We compare last_insert_id from Phalcon against pdo direct query. | |
echo ' - Phalcon LAST_ID = ('.gettype($w1->id).')'.$w1->id.' should be (integer)1', PHP_EOL; | |
$pdo_last_insert_id = $db->getInternalHandler()->lastInsertId(); | |
echo ' - PDO LAST_ID = ('.gettype($pdo_last_insert_id).')'.$pdo_last_insert_id.' should be (integer)1', PHP_EOL; | |
$pdo_last_insert_id = $db->getInternalHandler()->query('SELECT LAST_INSERT_ID()')->fetch()[0]; | |
echo ' - PDO/alt LAST_ID = ('.gettype($pdo_last_insert_id).')'.$pdo_last_insert_id.' should be (integer)1', PHP_EOL; | |
$w2 = $options['use_dynamic_update'] ? WrongTableDynamic::findFirst(1) : WrongTable::findFirst(1); | |
if ($w2) { | |
echo ' - foo/bar (before update) = ('.gettype($w2->foo).')'. | |
($w2->foo ? 'TRUE' : 'FALSE').'/'. | |
($w2->bar ? 'TRUE' : 'FALSE'). | |
' should be a (boolean)FALSE/TRUE', PHP_EOL; | |
$w2->update(['foo' => true, 'bar' => false]); | |
if ($w2->getWriteConnection()->affectedRows() != 1) { | |
echo ' ** FAIL TO UPDATE', PHP_EOL; | |
} else { | |
$w3 = $options['use_dynamic_update'] ? WrongTableDynamic::findFirst(1) : WrongTable::findFirst(1); | |
echo ' - foo/bar ( after update) = ('.gettype($w3->foo).')'. | |
($w3->foo ? 'TRUE' : 'FALSE').'/'. | |
($w3->bar ? 'TRUE' : 'FALSE'). | |
' should be now a (boolean)TRUE/FALSE', PHP_EOL; | |
} | |
} else { | |
echo ' ** FAIL TO FETCH', PHP_EOL; | |
} | |
class WrongTable extends \Phalcon\Mvc\Model | |
{ | |
public $id = 0; | |
public $title = 'foobar'; | |
public $foo = false; | |
public $bar = true; | |
public static function getCreateTable($bit) | |
{ | |
return 'CREATE TABLE `wrong_table` ('. | |
'`id` INT NOT NULL AUTO_INCREMENT,'. | |
'`title` VARCHAR(100) NOT NULL DEFAULT "foobar",'. | |
'`foo` '.($bit ? 'BIT(1)' : 'TINYINT(1)').' NOT NULL DEFAULT 0,'. | |
'`bar` '.($bit ? 'BIT(1)' : 'TINYINT(1)').' NOT NULL DEFAULT 1,'. | |
'PRIMARY KEY (`id`));'; | |
} | |
} | |
class WrongTableDynamic extends WrongTable | |
{ | |
public function initialize() | |
{ | |
$this->useDynamicUpdate(true); | |
} | |
public function getSource() | |
{ | |
return 'wrong_table'; | |
} | |
} | |
function init($options) | |
{ | |
$di = new \Phalcon\Di\FactoryDefault\Cli(); | |
$db = new Phalcon\Db\Adapter\Pdo\Mysql($options['db']); | |
$di->setShared('db', $db); | |
\Phalcon\Mvc\Model::setup(['castOnHydrate' => $options['cast_on_hydrate']]); | |
$db->query('DROP TABLE IF EXISTS wrong_table'); | |
$db->query(WrongTable::getCreateTable($options['use_bit'])); | |
if ($options['debug_sql']) { | |
$eventManager = new \Phalcon\Events\Manager(); | |
$eventManager->attach( | |
'db:afterQuery', | |
function (\Phalcon\Events\Event $event, \Phalcon\Db\Adapter\Pdo\Mysql $connection) { | |
$sql = $connection->getSQLStatement(); | |
$pieces = $connection->getSqlVariables(); | |
echo ' ['.$sql.']'.($pieces ? ' ['.join(',', $pieces).']' : ''), PHP_EOL; | |
} | |
); | |
$db->setEventsManager($eventManager); | |
} | |
return $db; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment