SQLite -> Postgres = Much ranting

I'm on a "fun" journey of discovery here, having assumed (oops!) that developing some software using DBIx::Class using the SQLite database, and then switching the code to using Postgres, everything would Just Work (TM)

Here's what I've tripped over so far:

1. SQLite seems to ignore FK constraints - that is, delete a row in a table, which another table is storing the PK of (with a defined FK constaint), and it will be happily deleted, leaving the 2nd table's link hanging.

2. Fun with DateTimes!

   I'm using the standard DBIx::Class tools for managing datetime fields/objects: DBIx::Class::InflateColumn, which implements "convert a datetime string from the database to a DateTime object", and "convert a DateTime object to a string the database will understand", everytime you store/fetch a datetime value. This is terribly handy.. or would be if it were consistent across databases.

   The consistency issue is of course, about timezones, the bane of every programmers life. Even though I'm dealing with very local events, I still need to cope with daylight savings time, which is best coped with by setting the timezone to "Europe/London", and letting the system work it out.

   When using SQLite, our handy inflation module uses the DateTime parser/formatter DateTime::Format::SQLite, which, because SQLite knows diddly about timezones (or datetimes really, its strings all the way down), converts the DateTime object to UTC, stores the UTC value, and then sets the UTC timezone on the object it creates from parsing the string back again. This all worked like magic, so I assumed all was good.

   After switching to using Postgres for storage, I started noticing duplicate entries in the database, an hour apart. This morning, after much debugging, I discovered that the Pg variant of the formatting module DateTime::Format::Pg is not consistent with the SQLite one. It happily formats a DateTime object with timezone into an appropriate string, which postgres itself then ignores the timezone of (NB: my fields are all just "datetime", no "with timezone" stuff) so I was getting the literal value stored. Worse, when pulling the value back out of the database, the Pg formatter, if the value doesn't have a timezone on it (and given its been ignored, it doesn't), sets the Floating timezone.

   The eagle-eyed among you may have noticed that the docs for the InflateColumn module mention setting a "timezone" value for each of the datetime columns. This goes a long way to sorting out the problem - the timezone is used both to convert to for storing (getting around the "Pg just ignores it" issue), and for setting on the new DateTime object after parsing (getting around the "Floating timezone" issue). Tho I hadn't needed the whole thing until I switched to using Pg...

   The one thing this doesn't fix (and there doesn't seem to be a solution to), is that of doing a search() call, using datetime values. Currently, to search using datetimes, you need to fetch DBIC's datetime formatter object, and then call format_datetime yourself, for example:

    my $dtf = $schema->storage->datetime_parser;
    $rs->search({ start_time => $dtf->format_datetime($dt_obj) });
   

   Which bypasses the "timezone" set in column info, and this has the same issues as before setting it. I've now created my own formatting method to get around this:

    sub format_datetime {
      my ($self, $field, $dt) = @_;
      my $col_info = $self->result_source->column_info($field);
      my $tz = $col_info ? $col_info->{timezone} || 'UTC' : 'UTC';
      my $dtf = $self->result_source->schema->storage->datetime_parser;
      my $dt_with_tz = $dt->clone->set_time_zone($tz);
      return $dtf->format_datetime($dt_with_tz);
    }
   

I wonder what else I'll find..

Off to delete a bunch of duplicate event time entries now..