Setting default values
From ClassDBI
Say, for example, you have a table with a 'created' DATETIME column in MySQL. Unfortunately, MySQL doesn't allow you to specify NOW() as a DEFAULT, since you can't use functions in your schema definition. One possible solution is to always try to remember to add a 'created' entry to your create() arguments, but that's a pain and prone to error. Also, if you have multiple Web servers accessing a central database, you really want the timestamps to come from the database machine, not each of the Web servers.
The best way to achieve this is to change the default SQL that Class::DBI uses for creates.
The default in Class::DBI is:
__PACKAGE__->set_sql(MakeNewObj => <<); INSERT INTO __TABLE__ (%s) VALUES (%s)
In your class you can override it, and all create() calls will use your new SQL:
__PACKAGE__->set_sql(MakeNewObj => <<); INSERT INTO __TABLE__ (created, %s) VALUES (NOW(), %s)
(Of course, this could be SYSDATE if you are using Oracle or whatever.)
If you wanted to do something similar for UPDATEs, you would override the 'update' SQL:
__PACKAGE__->set_sql(update => <<""); UPDATE __TABLE__ SET lastupdate = NOW(), %s WHERE __IDENTIFIER__
In some situations you will get a "column specified twice" error when you do the above, usually when you have all your columns set to 'NOT NULL' so that you can use the underlying database mechanism for setting default column values when you don't supply a value. I tweaked _create() in 0.96 to work around this:
my ($real, $temp) = ({}, {});
($class->has_real_column($_) ? $real : $temp)->{$_} = $self->_attrs($_)
foreach grep { $self->_attribute_exists($_) and defined $self->_attrs($_) }
$self->all_columns;
$self->_insert_row($real);
The change is where I filter out undefined values in the grep condition.
After reading MySQL documentation on timestamps, I changed all my created and modified columns to TIMESTAMP. It's by far easier. All you do is leave that column out of the key value hash for the Class::DBI->create call and it gets set. Class::Date can inflate with "new" just fine with a TIMESTAMP of the form YYYYMMDDXXXXXX, as used in 4.0, and I'm sure the same for DATETIME-formated columns. I haven't acutally upgraded to 4.1 yet but I will. 4.1 has all kinds of improvements to TIMESTAMP columns. For example, now they return the same form as DATE and DATETIME. I'm posting an excerpt from manual at end. I'd post a link but the mysql site is down. See "FROM MYSQL MANUAL -- ON TIMESTAMPS" further down.
-- p speltz
Yes, that's great for a "last_updated" column -- but not for a "created" column which you don't want to change when you update something. :-) -- AskBjoernHansen
You're right. Only one timestamp column can have automatic initialize and/or update. This part of docs had me dreaming. The key i overlooked was the singular use of "column". I ----- "Beginning with MySQL 4.1.2, you have more flexible control over when automatic TIMESTAMP initialization and updating occur and which column should have those behaviors. You can assign the current timestamp as the default value and the auto-update value, as before. But now it is possible to have just one automatic behavior or the other, or neither of them. You can specify which TIMESTAMP column to automatically initialize or update to the current date and time. This no longer need be the first TIMESTAMP column." -------
It should add in bold -- "BUT ONLY ONE OR THE OTHER CAN HAVE ANY OF THESE FEATURES. YOU CAN'T HAVE ONE COLUMN GET INITIALIZED AT CREATION ONLY AND THE OTHER GET INITIALIZED AT UPDATE ONLY." -- so morons like me don't humiliate themselves as often. :) thanks. Now it all makes sense.
-- p speltz
Isn't this more easily handled with triggers? I solved this issue by adding two triggers to my base class, one for create and one for update:
### automatically set dates on records
### uses HTTP::Date::time2iso
__PACKAGE__->add_trigger(before_create => sub { $_[0]->set(create_date => time2iso()) });
__PACKAGE__->add_trigger(before_update => sub { $_[0]->set(modify_date => time2iso()) });
That way your classes remain portable. Is there any advantage to your method that I'm missing?
I'd counter your assertion that the timestamps should be generated on the central database with another assertion: all servers should be running ntpdate :-)
-- RhesaRozendaal
The problem with this is that you are invoking an additional query each and every time you create and update. It is far more efficent to insert the timestamp values during the initial query as mentioned in the first example.
-- dbetz
Triggers are also useful if the column that you want to set a default value for is a primary key. CDBI does not appear to like to insert a row that has a primary key which is set by a default (unless that column is a sequence). Using a before_create trigger to set a timestamp gets around this problem.
Also, for those folks using Time::Piece instead of HTTP::Date, the triggers for a PostgreSQL timestamp with timezone field would look like the following:
### automatically set timestamp on record creation
use Time::Piece;
__PACKAGE__->add_trigger(before_create => sub {
my $tpobj = localtime;
$_[0]->set(time_stamp => $tpobj->strftime("%Y-%m-%d %T%z"));
});
/Is there a way to set 'created' and 'updated' timestamps automatically based on whether those columns exist or not? Thus making it a behaviour of the system as a whole./
You could add a trigger to your application base class, something like:
__PACKAGE__->add_trigger(before_update => sub {
my $class = shift;
return unless $class->find_column('updated');
$class->updated(get_current_time());
});
And similar for created.
I've been using this for a while in my code. It will use the DB time to update commonly named date fields:
# Triggers
sub after_create {
my $self = shift;
my $sth = $self->db_Main->prepare('select now()+0');
$sth->execute;
my $time = $sth->fetch->[0];
foreach my $col (qw(created_date modified_date mod_date event_time)) {
if ($self->find_column($col)) {
$self->$col($time);
}
}
$self->update;
};
sub before_update {
my $self = shift;
my $sth = $self->db_Main->prepare('select now()+0');
$sth->execute;
my $time = $sth->fetch->[0];
foreach my $col (qw(modified_date mod_date event_time)) {
if ($self->find_column($col)) {
$self->$col($time);
}
}
};
__PACKAGE__->add_trigger(after_create => \&after_create);
__PACKAGE__->add_trigger(before_update => \&before_update);
Add this to your base class, and modify the column names as needed. Any CDBI classes that inherit from it will have created dates and mod date columns updated automatically. The 'select now()+0' is used to get a proper timestamp value as an integer. There is surely a more elegant way to do this, but it works.
Also, consider using the Class::Date module (see the ClassDate page for examples) for automated date handling.
-- JeremySeitz
9/22/04 FROM MYSQL MANUAL -- ON TIMESTAMPS
Note: From version 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are no longer supported.
You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:
As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'. As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).
END EXCERPT.
