| Subject: | MySQL -> SQLite problems with timestamps and default constraints |
This MySQL 'CREATE TABLE' statement has been used for testing:
---
CREATE TABLE test (
id int(11) NOT NULL auto_increment,
last_changed timestamp(14) NOT NULL,
created timestamp(14) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
---
SQL::Translator 0.05 translates it to the following which is plain wrong.
---
BEGIN TRANSACTION;
CREATE TABLE test (
id INTEGER PRIMARY KEY NOT NULL,
last_changed timestamp(14) NOT NULL,
created timestamp(14) NOT NULL,
);
CREATE TRIGGER ts_test after insert on test
begin
update test set last_changed=timestamp() where id=new.id;
end;
CREATE TRIGGER ts_test after insert on test
begin
update test set created=timestamp() where id=new.id;
end;
COMMIT;
---
The correct statement would be:
---
BEGIN TRANSACTION;
CREATE TABLE test (
id INTEGER PRIMARY KEY NOT NULL,
last_changed timestamp(14),
created timestamp(14) NOT NULL,
);
CREATE TRIGGER ts_test1 after insert on test
begin
update test set last_changed=timestamp()
where id=new.id AND new.last_changed is null;
end;
CREATE TRIGGER ts_test2 after update on test
begin
update test set created=timestamp()
where id=new.id AND new.last_changed is null;
end;
COMMIT;
---
Explaination:
Only the first timestamp column in MySQL is changed automatically but
this on either insert or update. MySQL accepts NULL values to this
column as well, which is not the case with SQLite where the value is
generated by the trigger _after_ the insert. Every timestamp other
than the first one is ignored by MySQL.
Another (mis-)feature of MySQL is that automatic timestamp generation
may be overridden when inserting or updating with a non-null value in
this column.
Hope this helps...
Regards,
Sebastian