Loading calendar database

Hi,

I plan to move my Data::Dumper based occupancy table to a real database. Wh=
ile the database itself is quite, I am not sure how to load it with the app=
ropriate dates (including February 29, 2008 etc).

Is there a Perl module or a certain technique for loading fields of the dat=
e type with the appropriate information?

Thanks,

Jan
--=20
How many Microsoft engineers does it take to screw in a lightbulb? None. Th=
ey just redefine "dark" as the new standard.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Loading calendar database

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.

Thanks,

Jan
--=20
Common sense is what tells you that the world is flat.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Loading calendar database

--qDbXVdCdHGoSgWSk
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Mon, Dec 20, 2004 at 06:19:09PM +0100, Jan Eden wrote:
> Jan Eden wrote on 20.12.2004:
>=20
> >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?
>=20
> I already thought about a loop like
>=20
> for (1..1000) {
> $dbh->do(INSERT INTO ... VALUES ADDDATE('2004-01-01', INTERVAL $_ DAY=
S);
> }
>=20
> But there must be something better.

I think it would really help if you told us how the data is *currently*
stored :)

G'luck,
Peter

--=20
Peter Pentchev roam@ringlet.net roam@cnsys.bg roam@FreeBSD.org
PGP key: http://people.FreeBSD.org/~roam/roam.key.asc
Key fingerprint FDBA FD79 C26F 3C51 C95E DF9E ED18 B68D 1619 4553
I am the meaning of this sentence.

--qDbXVdCdHGoSgWSk
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFBx8eP7Ri2jRYZRVMRAs0RAJ9kZMeGyqrWq1LCiXTxVEx085ijPACe PtSZ
SQNtd8uiMZtmbtxcgWOleEE=
=Kw1W
-----END PGP SIGNATURE-----

--qDbXVdCdHGoSgWSk--

Re: Loading calendar database

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:
>>=20
>> >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?
>>=20
>> I already thought about a loop like
>>=20
>> for (1..1000) {
>> $dbh->do(INSERT INTO ... VALUES ADDDATE('2004-01-01', INTERVAL $_ DA=
YS);
>> }
>>=20
>> 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, con=
taining an anonymous hash of arrays of hashes like this:

{
'07' =3D> [
'Juli',
{
'20' =3D> 'frei',
'07' =3D> 'frei',
'01' =3D> 'frei',
'23' =3D> 'frei',
'31' =3D> 'frei',
'29' =3D> 'frei',
'11' =3D> 'frei',
'17' =3D> 'frei',
'19' =3D> 'frei',
'09' =3D> 'frei',
'26' =3D> 'frei',
'28' =3D> 'frei',
'25' =3D> 'frei',
'10' =3D> 'frei',
'04' =3D> 'frei',
'30' =3D> 'frei',
'08' =3D> 'frei',
'24' =3D> 'frei',
'02' =3D> 'frei',
'03' =3D> 'frei',
'12' =3D> 'frei',
'27' =3D> 'frei',
'15' =3D> 'frei',
'06' =3D> 'frei',
'18' =3D> 'frei',
'05' =3D> 'frei',
'14' =3D> 'frei',
'21' =3D> 'frei',
'22' =3D> 'frei',
'16' =3D> 'frei',
'13' =3D> 'frei'
}
],
'01' =3D> [
'Januar',
{
'20' =3D> 'frei',
'07' =3D> 'frei',
'01' =3D> 'frei',
'23' =3D> 'frei',
'31' =3D> 'frei',
'29' =3D> 'frei',
'11' =3D> 'frei',
'17' =3D> 'frei',
'19' =3D> 'frei',
'09' =3D> 'frei',
'26' =3D> 'frei',
'28' =3D> 'frei',
'25' =3D> 'frei',

(continued)

I know this is clumsy and inefficient, that's why I want to switch, ;-)

Cheers,

Jan

--=20
There are two major products that come out of Berkeley: LSD and UNIX. We do=
n't believe this to be a coincidence. - Jeremy S. Anderson

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Loading calendar database

--4jXrM3lyYWu4nBt5
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Dec 21, 2004 at 10:00:05AM +0100, Jan Eden wrote:
> Hi Peter,
>=20
> Peter Pentchev wrote on 21.12.2004:
>=20
> >On Mon, Dec 20, 2004 at 06:19:09PM +0100, Jan Eden wrote:
> >> Jan Eden wrote on 20.12.2004:
> >>=20
> >> >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?
> >>=20
> >> I already thought about a loop like
> >>=20
> >> for (1..1000) {
> >> $dbh->do(INSERT INTO ... VALUES ADDDATE('2004-01-01', INTERVAL $_ =
DAYS);
> >> }
> >>=20
> >> But there must be something better.
> >
> >I think it would really help if you told us how the data is *currently*
> >stored :)
>=20
> Oh, sure. I currently store the data in a file created by Data::Dumper, c=
ontaining an anonymous hash of arrays of hashes like this:
>=20
> {
> '07' =3D> [
> 'Juli',
> {
> '20' =3D> 'frei',
> '07' =3D> 'frei',
[snip]
> }
> ],
> '01' =3D> [
> 'Januar',
> {
> '20' =3D> 'frei',
[snip]
> '25' =3D> 'frei',
>=20
> (continued)
>=20
> 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 =3D $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 =3D ();
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

--=20
Peter Pentchev roam@ringlet.net roam@cnsys.bg roam@FreeBSD.org
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.

--4jXrM3lyYWu4nBt5
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFBx++K7Ri2jRYZRVMRAkesAJ4gqsWpWA56fDGEZBi7VXpwz4e2GwCf eO9r
v+p9b3gSVYUXx80qx61kp1o=
=qZDD
-----END PGP SIGNATURE-----

--4jXrM3lyYWu4nBt5--

Re: Loading calendar database

Hi Peter,

Peter Pentchev wrote on 21.12.2004:

>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 =3D $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 =3D (); 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 :)

It certainly does. Thanks!

- Jan
--=20
I'd never join any club that would have the likes of me as a member. - Grou=
cho Marx

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org