See the MySQL docs for the DATE_FORMAT() function. That gives a formatted date similar to POSIX::strftime. For example,

$sth = $dbh->prepare <<EOSQL;
    select
        DATE_FORMAT(The_Date_Col, '%d/%m/%Y')
    from the_table
    where
        Some_key = ?
EOSQL
[download]

After Compline,
Zaxo

I think this is too low level to use transparently via Class::DBI.
I'm not too concerned about how to convert the dates but where.
At what level of the Class::DBI architecture?

If there's something in MySQL like Postgres' PGDATESTYLE="ISO,European" which made date handling transparent then that would do the trick. I couldn't find such an option so decided that automatic handling in Perl would be better than fiddling with SQL.

I use the has_a relationship using the Time::Piece::MySQL module which will do the formatting and stringifying for you.

use Time::Piece::MySQL;
__PACKAGE__->has_a(created => 'Time::Piece',
                   inflate => 'from_mysql_datetime',
                   deflate => 'mysql_datetime',
);
[download]

- Cees

This is still not quite there although thanks for the tip.

One reason is that I can't change the stringification.
Another is that the mutator still only accepts ISO format since inflate gets called both going from the database to the object and from the mutator/user to the object.

  DB<1>  p $p->gp_ref_date
Thu Mar  1 00:00:00 2001
 
  DB<2> p $p->gp_ref_date('01/02/2003')
after_set_gp_ref_date trigger error: Error parsing time
[download]

I presume that you're "created" column doesn't get modified.

Thanks again for a good lead though.

I've never used Class::DBI, but based on what I've read in the docs, i think maybe you aren't fully understanding what cees is trying to say.

According to the docs, you can use has_a to specify a type of object to model a nugget of data, and whenever the value of that nugget is "set", the specified "inflate" method will be called to construct an object of that type, and the specified "deflate" method will be used whenever the object needs to be formatted to insert into the DB.

So you should be able to specify an "inflate" method that parses the string in whatever format(s) you want your users to be able to provide as input, and specify a "deflate" method that stringifies using whatever format your DB wants the date to be in.

The documentation of the "has_a" method has a good example of this.

I guess I don't quite get what you are trying to do then. To parse a date into a Time::Piece object you can use the strptime:

my $date = Time::Piece->strptime("01/02/2003",
                                 "%e/%B/%Y");
$t->created($date);
[download]

Where $t is a Class::DBI object with a 'created' column. With the has_a relationship you pass an object to the mutator, not a string. If you pass a string to the mutator, it will try to use the inflate method which will expect your date string to be in MySQL format. So just pass it a Time::Piece object which is what it is after anyway.

Now when you want to print it or retrieve it later on in the format you are after, you can use:

$t->created->dmy('/');
[download]

You don't have to worry about MySQL date formats at all, you just have to know how to create Time::Piece objects, and how to alter and access Time::Piece objects.

If you want these formats to happen automatically, then write a couple of functions that do the work for you:

sub parse_date {
  return Time::Piece->strptime(shift, "%e/%B/%Y");
}

sub stringify_date {
  return shift->dmy('/');
}
[download]

You could probably write a custom module that inherits from Time::Piece::MySQL and override the new and stringification subroutines with something like the above to make things a little more transparent.

If I am still misunderstanding what you are trying to accomplish, perhaps you could write a quick code sample of how you would like to use dates with Class::DBI.

- Cees

Updated: Fixed a dumb little typo

FWIW this is how SPOPS does it as well: using the SPOPS::Tool::DateConvert module you can convert to/from DateTime, Time::Piece, and Class::Date objects, specifying the format of the date as it's pulled from the database as a strptime string in the metadata. I do like the shortcuts you've used in your example... maybe I'll have to swipe that :-)

Chris
M-x auto-bs-mode

I'm now just overriding the accessors for each date column and keeping the date in MySQL's format.

I'd still be interesting in knowing how a has_a solution should work all together.

Here's my (prototype) code:

  DB<1> p $s->ses_date
23/06/2003
 
  DB<2> x $s->{ses_date}
0  '2003-06-23'
  DB<3> $s->ses_date('1/2/3')
 
  DB<4> p $s->{ses_date}
2003-02-01
 
  DB<5> x $s->{ses_date}
0  '2003-02-01'
[download]

my $class=__PACKAGE__;
$class->has_a
   (
   date=>'DateTime::Format::MySQl',
   inflate=>sub {DateTime::Format::MySQL->parse_datetime(shift)}
   );
[download]

$obj->date->year;  # or whatever, see perldoc DateTime
[download]

Argh. I forgot to log in (damn browser was blocking cookies..) Anyway, the above node is by me..

--
#!/usr/bin/perl
for(ref bless{},just'another'perl'hacker){s-:+-$"-g&&print$_.$/}
[download]

I know im not going to be popular for saying this but I think you should take a different tack. Tell you users that they have to use YYYY-MM-DD and be done with it. Even though I can hear the "boo hiss" I have a strong point here. The fact is that ISO dates are there for a reason. They are standard in Europe, they are specified in Duden in Germany (not to mention being a DIN standard as well), the US military uses them etc. Frankly its not unreasable to say that anybody sane uses ISO dates. Sure you may hear a bit of grumbling but after a week or two they wont even remember being able to enter dates in such a foolish format as DD/MM/YYYY. And actually I bet a whole bunch of them are quite used to using ISO formatted dates and wont even bat an eye.

What I would do is ignore the issue. Then if they asked I would say no. Then when they pleaded I would tell them to get a budget to write the code to handle the format and tell them itll be at minimum two weeks to sort out. Then they will go away. :-)

And before anybody rejoins with one of those "following orders" kinda reply, ask yourself this: if the user asks you to design the system so that some kind of horrible bug is possible, say one that would allow under some circumstance the DB to be wiped or the filesystem to be corrupted or something, just so they can have some "feature" of dubious worth would you do it? I know I wouldnt. My company hires me because Im (more or less :-) a professional. They hire me to give professional opinions, and to Do the Right Thing. They hire me (and you) because we have skills and knowledge they need, not to blindly follow orders. So when I say to them "No, i wont do that, its a bad idea for these reasons" I expect them to listen. If they choose to overrule me then I ask for it on paper. It is _very_ rare that a user/manager etc will overrule you when you insist on paperwork to show they will carry the can if something goes wrong. The very fact that you insist shows them that you are pretty much certain they will end up with egg on their face. And nobody likes that.

NOTE: as my be able to tell :-) I have strong feelings on this particular subject. IMO no computer program should either emit or accept any human readble date format other than an ISO compliant one. (Well, except under extreme duress.)

Tell you users that they have to use YYYY-MM-DD and be done with it.

Computers are supposed to make things easier. If I told all my users (customers at client sites) they had to enter 'YYYY-MM-DD', most of them would throw fits, and rightfully so, IMHO. The only downside is that we use a 4gl (not perl) for the data entry, and it automatically converts different entered formats, but we don't have complete control over the conversion, and it allows, e.g., '050103' to mean '2003-05-01', which is fine by me, but if (and I mean when) that last '0' is accidentally left out, and they enter '05013', it is interpreted as '0003-05-01'. If they enter '5/1/3' it is interpreted correctly, but they'd rather just use the numbers on the number pad.

And don't ask whether it would be easier for them to enter YYYY-MM-DD rather than have me fix the data when it goes bad... :-)

Computers are supposed to make things easier.

Computers are supposed to make the possible easier. But they can't do much with the totally ambiguous. And I suspect that they wouldnt throw fits as often as you think. Especially if they were shown the obvious benefits of reduced maintenance (that they pay for) that can be obtained by not using ambiguous formats. Also IMO most corporate types like the idea of being ISO compliant.


---
demerphq

_{<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...}

Refreshing.

But the people using this repeatedly deserve a short form. Or at least a shortcut. Maybe prefilling the field with 2003-00-00... Javascript help...

But at the end of the day, they can have whatever they want to pay for.

Brad

But at the end of the day, they can have whatever they want to pay for.

I suspect that you haven't thought this through. Presumably if you gave them certain things without exculpatory documentation that you warned them of the dangers and they still insisted that you could be sued after the fact for far more money than you have been originally paid.

Consider an engineering firm hired to build a bridge. The client then asks that some ornament was placed on the bridge that under certain circumstances (unusually high winds perhaps) could pose a threat to the structural integrtiy of the bridge or a threat to life and limb. If the firm involved knows these risks, and fails to communicate them to the client in such a way that the client takes all responsibility for the consequences then it certainly would be in the position to be sued for every penny it had for negligence, or perhaps even worse.

To be honest it may be in the case of construction that _whatever_ happened the engineering firm would be at fault. And as such the onus is on them to refuse to perform such a service. I dont know. The point is still valid IMO.

And what does allowing two digit years (you mentioned you wanted to allow this), and odd date formats imply? Well its a threat to the structural integrity of the data. And I suspect that if you allowed that to happen you could be sued for allowing the DB to be corrupted.

So, while I can see how its difficult to tell your customer NO, its not difficult to write a document advising them of the risks of allowing such things and getting them to sign that they understand the implications before you do so. At the bare minimum it will protect your ass from potential law suits.

PS, the idea of prefilled fields or a button that puts in todays date or whatnot sounds like a good way to ease the pain of repetitive entry while at the same time ensuring the integrity of the data.

Anyway, theres the cynical other point of view for you. :-)


---
demerphq

_{<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...}

__PACKAGE__->has_a(
    startdate => 'Date::Class',
    inflate => sub { Date::Class->new(shift) },
    deflate => 'some_method_name'
);
[download]

Incorrect:

SomeTab->create({startdate => '07/08/03'});
[download]

Correct:

SomeTab->create({startdate => Date::Class->new('07/08/03')});
[download]

The deflate value if present is the name of a method of
the Date::Class object that will return a string of
the proper format for insertion into the database.
Or if Date::Class objects will stringify properly you
may leave the deflate key out all together.

We struggled with this for quite some time
only after reading through the Class::DBI code did I
come to this conclusion, as it is very poorly documented
One thing to note, you should replace Date::Class with
some real class which deals with dates.

For our purposes I used Date::EzDate, as it seemed to
easily support the most formats for parsing input
I subclassed Date::EzDate to get the default ouput
format that we needed to work.

I hope this helps if you need further assistance please
contact me, mgrubb-web-perlmonks at fifthvision dot net

That is a good explaination of what is going on with with the has_a relationships. I agree with you that the documentation for Class::DBI in regards to this is not that great, and it took me a while to figure things out as well.

I have a couple of small additions to make to clarify or simplify your example.

__PACKAGE__->has_a(
    startdate => 'Date::Class',
    inflate => sub { Date::Class->new(shift) },
    deflate => 'some_method_name'
);
[download]

That code can be reduced to:

__PACKAGE__->has_a(
    startdate => 'Date::Class',
    inflate => 'new',
    deflate => 'some_method_name'
);
[download]

Since by default Class::DBI will call the inflate method as a class method and pass it the value as the first arguement. It can even be reduced to:

__PACKAGE__->has_a(
    startdate => 'Date::Class',
    deflate => 'some_method_name'
);
[download]

Since 'new' is the default method used for inflation.

Similarly, for the deflate method, stringification is used by default. So if you don't provide a 'deflate' method, then Class::DBI will assume that your object overrides "" and will stringify it to deflate it.

Also, a small correction, you mention that the following is incorrect:

SomeTab->create({startdate => '07/08/03'});
[download]

That will actually work, since Class::DBI is smart enough to call the 'inflate' method when you pass it a string. I will agree that it is better in most cases to pass an object, but a string will work just as well. The only caveat is that the string you pass must be consistent with what 'deflate' actually generates.

So in other words, you can pass it a 'deflated' string or an 'inflated' object and Class::DBI will do the right thing.

There seems to be enough information in this thread to get a good start on a Tutorial on relationships in Class::DBI...

Cheers,

Cees

Maybe data_normalization using cees's multi-format parser + an overriden accessor (but unchanged mutator)

The reason I'm still thinking about this is that my previous accessor solution required an ugly loop for validation which mimicked the innards of Class::DBI::validate_column_values. I'd have to eval and collect the per column errors. Ick.

Brad

Maybe data_normalization using cees's multi-format parser + an overriden accessor (but unchanged mutator)

I guess that would probably work as well. I think if I were to take this approach, I would try to be consistent about the functionality change and override the accessor for every has_a relationship (ones that don't relate to another Class::DBI table object). The easiest place to do this is probably in the 'get' method. All accessors appearantly call 'get' to fetch the value (ie $self->date() gets turned into $self->get('date'); ). Also note that 'get' can accept multiple keys to fetch at once.

sub get {
  my ($self, @keys) = @_;
  my @values = $self->SUPER::get(@keys);

  foreach (@values) {
    # if $_ is an object, 
    #  and it is not a Class::DBI object
    #  and it overloads ""
    if (UNIVERSAL::isa($_, 'UNIVERSAL') &&
       !UNIVERSAL::isa($_, 'Class::DBI') &&
       overload::Method($_,'""')) {
      $_ = "$_";  # stringify the object
    }
  }
  return shift @values if @keys == 1;
  return @values;
}
[download]

I did a quick test, and it seems to work. Effectively it will check to see if the value is an object, and stringify it if "" has been overloaded in the object. So it shouldn't care what object you use in a has_a reloationship. As long as it overrides "" it will get stringified before it gets returned.

Personally I am happy with the way has_a lets you work with objects instead of just plain strings, so I wouldn't go this route myself. But everyone has their own style...

Cheers,

Cees