Re: Loading calendar database

On Tue, Dec 21, 2004 at 10:00:05AM +0100, Jan Eden wrote:
> Hi Peter,
> 
> Peter Pentchev wrote on 21.12.2004:
> 
> >On Mon, Dec 20, 2004 at 06:19:09PM +0100, Jan Eden wrote:
> >> Jan Eden wrote on 20.12.2004:
> >> 
> >> >Hi,
> >> >
> >> >I plan to move my Data::Dumper based occupancy table to a real
> >> >database. While the database itself is quite, I am not sure how to
> >> >load it with the appropriate dates (including February 29, 2008
> >> >etc).
> >> >
> >> >Is there a Perl module or a certain technique for loading fields of
> >> >the date type with the appropriate information?
> >> 
> >> I already thought about a loop like
> >> 
> >> for (1..1000) {
> >>     $dbh->do(INSERT INTO ... VALUES ADDDATE('2004-01-01', INTERVAL $_ DAYS);
> >> }
> >> 
> >> But there must be something better.
> >
> >I think it would really help if you told us how the data is *currently*
> >stored :)
> 
> Oh, sure. I currently store the data in a file created by Data::Dumper, containing an anonymous hash of arrays of hashes like this:
> 
> {
>           '07' => [
>                     'Juli',
>                     {
>                       '20' => 'frei',
>                       '07' => 'frei',
[snip]
>                     }
>                   ],
>           '01' => [
>                     'Januar',
>                     {
>                       '20' => 'frei',
[snip]
>                       '25' => 'frei',
> 
> (continued)
> 
> I know this is clumsy and inefficient, that's why I want to switch, ;-)

Disclaimer: none of the below pieces of code were actually tested :)

Well then, you might want to try something like:

  my ($month, $day, $sth);

  $sth = $dbh->prepare("INSERT INTO tablename(datecolumn) VALUES (?)");
  foreach $month (keys %{$hash}) {
    foreach $day (keys %{$hash->{$month}->[1]}) {
      $sth->execute(sprintf('2004-%02d-%02d', $month, $day)) or
        die "Inserting 2004/$month/$day failed: ".$dbh->errstr();
    }
  }
  $sth->finish();

This will have a slight disadvantage with older versions of MySQL, where
prepare() is not really implemented in the server, but simulated by the
DBD driver, but it would have the advantage of being portable to other
SQL servers.  Alternatively, you might decide to use MySQL's extended
insert capabilities and insert all the dates in one fell swoop:

  @dates = ();
  foreach $month (keys %{$hash}) {
    foreach $day (keys %{$hash->{$month}->[1]}) {
      push @dates, sprintf('"2004-%02d-%02d"', $month, $day);
    }
  }
  $dbh->do('INSERT INTO tablename(datecolumn) VALUES ('.
    join(', ', @dates).')') or die $dbh->errstr();

With this option, you might want to make a query per month though, or you
might run into limits such as the MySQL client query buffer size or
something similar.

Hope this helps :)

G'luck,
Peter

-- 
Peter Pentchev	[email protected]    [email protected]    [email protected]
PGP key:	http://people.FreeBSD.org/~roam/roam.key.asc
Key fingerprint	FDBA FD79 C26F 3C51 C95E  DF9E ED18 B68D 1619 4553
If wishes were fishes, the antecedent of this conditional would be true.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (FreeBSD)

iD8DBQFBx++K7Ri2jRYZRVMRAkesAJ4gqsWpWA56fDGEZBi7VXpwz4e2GwCfeO9r
v+p9b3gSVYUXx80qx61kp1o=
=qZDD
-----END PGP SIGNATURE-----