--- /dev/null
+#!/usr/bin/perl -w
+
+use strict;
+use DBI;
+use Data::Dump qw/dump/;
+
+$|++;
+
+my $f = DBI->connect("dbi:SQLite:dbname=knjiznica.sqlite","","", { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+$f->{unicode} = 1;
+my $k = DBI->connect("dbi:SQLite:dbname=knjiznica.sqlite","","", { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+
+our $lookup;
+sub lookup {
+ my ($t, $k, $v, $s ) = @_;
+ my $hash;
+ my $key = "$t $k $v";
+ if ( exists $lookup->{$key} ) {
+ $hash = $lookup->{$key};
+ } else {
+ warn "# select $k,$v from $t";
+ my $sth = $f->prepare( "select $k,$v from $t" );
+ $sth->execute;
+ while (my $row = $sth->fetchrow_hashref() ) {
+ $hash->{ $row->{$k} } = $row->{$v};
+ }
+ $lookup->{$key} = $hash;
+ warn dump($lookup);
+ }
+ return unless length $s > 1;
+ return $lookup->{$key}->{$s} || die "no $s in $key ",dump($lookup->{$key});
+}
+
+# select all posts which have been read or unread
+my $sql = qq{
+select
+ 'S' as categorycode,
+ 'SRE' as branchcode,
+ mbr_stud as cardnumber,
+ prez_stud as surname,
+ ime_stud as firstname,
+ djevprezime as othernames,
+ spol as sex,
+ dat_rodj as dateofbirth,
+-- ime_otac as ??,
+-- ime_majka as ??,
+ ozn_drzava_preb as country,
+ post_ozn_preb as zipcode,
+ post_ozn_preb as city,
+ adr_preb as address,
+ ozn_drzava_stan as B_country,
+ post_ozn_stan as B_zipcode,
+ post_ozn_stan as B_city,
+ adr_stan as B_address,
+ tel_stud as phone,
+ dat_prava_do as dateexpiry
+-- aktivan as ??,
+from studk
+limit 100
+};
+
+my $sth = $f->prepare($sql);
+$sth->execute;
+
+while (my $row = $sth->fetchrow_hashref ) {
+
+ # poor man's (sqlite) outer join
+ $row->{'country'} = lookup('drzava', 'ozn_drzava', 'naz_drzava', $row->{'country'} );
+ $row->{'city'} = lookup('mjesto', 'post_ozn', 'naz_mjesto', $row->{'city'} );
+
+ $row->{'B_country'} = lookup('drzava', 'ozn_drzava', 'naz_drzava', $row->{'B_country'} );
+ $row->{'B_city'} = lookup('mjesto', 'post_ozn', 'naz_mjesto', $row->{'B_city'} );
+
+ foreach my $c ( grep { /date/ } keys %$row ) {
+ $row->{$c} =~ s/(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/;
+ }
+
+ $row->{sex} =~ s/Ž/F/;
+
+ warn dump $row;
+
+}
+