#110 Issue with timestamp via xmlschema
I'm having an issue when trying to create a TIMESTAMP field that has a default value of "CURRENT_TIMESTAMP".
I'm using the latest adodb509a.tgz downloaded from SF. I'm also using the adodb-xmlschema03.inc.php include.
My XML file looks like this:
<table name="users">
<opt platform="mysql">TYPE=INNODB</opt>
<field name="id" type="I" size="10">
<KEY/>
<AUTOINCREMENT/>
<UNSIGNED/>
</field>
<field name="username" type="C" size="32">
<NOTNULL/>
</field>
<field name="password" type="C" size="64">
<NOTNULL/>
</field>
<field name="level" type="I" size="4">
<NOTNULL/>
<DEFAULT value="0"/>
</field>
<field name="ctime" type="T">
<NOTNULL/>
<DEFTIMESTAMP value="CURRENT_TIMESTAMP"/>
</field>
<field name="atime" type="T">
<NOTNULL/>
<DEFTIMESTAMP value="0000-00-00 00:00:00"/>
</field>
<index name="username">
<UNIQUE/>
<col>username</col>
</index>
</table>
What happens is that I get a table looking like what I want BUT it has also included an "on update CURRENT_TIMESTAMP" statement. I only want the default value to be a CURRENT_TIMESTAMP not on subsequent updates. I have worked around this for now by just including the following:
<sql>
<query platform="mysql">ALTER TABLE `users` CHANGE `ctime` `ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP</query>
</sql>
Also, in relation to this, the PrintSQL('TEXT') output does not show what I expect in relation to the default information that actually gets added to the database. The output I'm seeing is as follows:
CREATE TABLE users (
id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(32) NOT NULL,
password VARCHAR(64) NOT NULL,
level INTEGER(4) NOT NULL DEFAULT 0,
ctime TIMESTAMP NOT NULL,
atime TIMESTAMP NOT NULL,
PRIMARY KEY (id)
)TYPE=INNODB;
I would have expected the default CURRENT_TIMESTAMP info to be listed.
Thanks for looking!
