Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 2.5.0b5

Bug #10488 MySQL TIMESTAMP magic won't work
Submitted: 2007-03-25 09:24 UTC
From: hschletz Assigned: quipo
Status: Closed Package: MDB2
PHP Version: Irrelevant OS:
Roadmaps: (Not assigned)    
Comments Add Comment Add patch

Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know! Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
Solve the problem : 42 - 15 = ?

 [2007-03-25 09:24 UTC] hschletz (Holger Schletz)
Description: ------------ I extracted an XML schema from an existing MySQL database. Columns of type TIMESTAMP were correctly transformed to: <field> <name>fieldname</name> <type>timestamp</type> <notnull>false</notnull> <default>CURRENT_TIMESTAMP</default> </field> Unfortunately, MySQL (tested with 4.1) won't allow re-creation of the table because the XML type "timestamp" will be mapped to MySQL type "DATETIME" which doesn't allow non-constant DEFAULT values. As a solution for MySQL 4.1.2 and later, the SQL generator could check whether the default value is CURRENT_TIMESTAMP and in that case use the MySQL datatype "TIMESTAMP".


 [2007-03-25 13:54 UTC] ifeghali (Igor Feghali)
Hello Holger! Thank you for submitting a bug. I am looping to MDB2 as this a MySQL driver behavior. regards, Igor.
 [2007-03-25 18:21 UTC] quipo (Lorenzo Alberton)
The DATETIME data type is used for a reason. It's the closest to the SQL'99 standard, and its range isn't limited to 1970-2037 like the TIMESTAMP data type, so it's the best portable option. If you really want to use TIMESTAMPs instead of DATETIMEs, you can probably do so by adding a custom datatype map: ================================== <?php function datatype_ts_callback(&$db, $method, $field) { if (is_null($db->datatype)) { $db->loadModule('Datatype', null, true); } $name = $field['name']; $field = $field['field']; $default = ''; if (array_key_exists('default', $field)) { if ($field['default'] == 'CURRENT_TIMESTAMP') { $default = ' DEFAULT CURRENT_TIMESTAMP'; } else { $default = ' DEFAULT '.$this->quote($field['default'], 'datetime'); } } $notnull = empty($field['notnull']) ? '' : ' NOT NULL'; return $db->quoteIdentifier($name, true) . ' DATETIME ' . $default . $notnull; } require_once 'MDB2.php'; $db = MDB2::factory('mysqli://root@localhost/test'); $db->setOption('datatype_map', array('timestamp' => 'timestamp')); $db->setOption('datatype_map_callback', array('timestamp' => 'datatype_ts_callback')); $fields = array( 'id' => array( 'type' => 'integer' ), 'ts' => array( 'type' => 'timestamp', 'default' => 'CURRENT_TIMESTAMP', 'notnull' => true ), ); $db->loadModule('Manager'); echo $db->manager->getFieldDeclarationList($fields); ?> ==================================