29 January 2007

do what I asked you to do, not what you think I wanted

Sometimes software is just too smart for it's own good.

So as I'm working on coding the client for CAATT I was creating some test data in the database, and I found that my input timestamp keeps changing, even though I'm not touching it.


mysql> select * from work_queue;
+---------+---------------------+---------------------+------+----------+------+
| work_id | input | output | task | comments | doer |
+---------+---------------------+---------------------+------+----------+------+
| 1 | 2007-01-22 20:43:36 | 0000-00-00 00:00:00 | 1 | NULL | 501 |
| 2 | 2007-01-28 20:43:43 | 0000-00-00 00:00:00 | 2 | NULL | 502 |
| 3 | 2007-01-28 20:43:51 | 0000-00-00 00:00:00 | 4 | NULL | 501 |
| 4 | 2007-01-29 00:25:44 | 0000-00-00 00:00:00 | 5 | NULL | 501 |
+---------+---------------------+---------------------+------+----------+------+
4 rows in set (0.00 sec)

mysql> update work_queue set output=now(), comments="howdy" where work_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from work_queue;
+---------+---------------------+---------------------+------+----------+------+
| work_id | input | output | task | comments | doer |
+---------+---------------------+---------------------+------+----------+------+
| 1 | 2007-01-22 20:43:36 | 0000-00-00 00:00:00 | 1 | NULL | 501 |
| 2 | 2007-01-28 20:43:43 | 0000-00-00 00:00:00 | 2 | NULL | 502 |
| 3 | 2007-01-29 00:26:45 | 2007-01-29 00:26:45 | 4 | howdy | 501 |
| 4 | 2007-01-29 00:25:44 | 0000-00-00 00:00:00 | 5 | NULL | 501 |
+---------+---------------------+---------------------+------+----------+------+
4 rows in set (0.00 sec)


So I set the completion date and a comment on #3, but it changed the input time on me! ARGH!!! So I went back and looked at my table definition...


mysql> explain work_queue;
+----------+----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------------------+----------------+
| work_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| input | timestamp | NO | | CURRENT_TIMESTAMP | |
| output | timestamp | NO | | 0000-00-00 00:00:00 | |
| task | tinyint(3) unsigned | NO | | | |
| comments | varchar(4096) | YES | | NULL | |
| doer | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)


Nope, nothing there to indicate why it did that. Off to the manuals I go, finally find this. GRRRR! show create table work_queue confirms that it set both the default AND the on update to CURRENT_TIMESTAMP when the table was created. Several attempts to modify the table fail to achieve anything even remotely close to what I want. In the end I did a mysqldump of the entire database and modified the creation script to be as such:


DROP TABLE IF EXISTS `work_queue`;
CREATE TABLE `work_queue` (
`work_id` bigint(20) unsigned NOT NULL auto_increment,
`input` timestamp NOT NULL default CURRENT_TIMESTAMP references work_queue on update NO ACTION,
`output` timestamp NOT NULL,
`task` tinyint(3) unsigned NOT NULL,
`comments` varchar(4096) default NULL,
`doer` smallint(5) unsigned default NULL,
UNIQUE KEY `work_id` (`work_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


So the magic bullet to create a mysql table that has a column that tracks INSERTION time, but NOT UPDATE TIME? A self referential on update no action. And you can only do that when you CREATE the table, there's no way to get alter to accept that as near as I can tell.

Now that that's out of my system, a quick update on the caatt clients: I've decided to do a quick LAMP implementation instead of a native OS/X client. It already knows who you are, asks if you've not told it, and remembers... and it can dump a list of work to do, with overdue item's highlighted separately. Details of a task and marking it complete were what I was working on when this stopped me. Oh yeah, and I slapped together a quick logo/favicon for it with OmniGraffle (and photoshop for cropping, since OmniGraffle's export sometimes picks bizare margins).

Labels: ,

0 Comments:

Post a Comment

<< Home