#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Date::Parse;
use Time::Local;

# temporäre Datenbank im RAM erzeugen
my $dbH = DBI->connect(          
    "dbi:SQLite:dbname=:memory:"
    , ""
    , ""
    , { RaiseError => 1 }
) or die $DBI::errstr;

# DB-Tabelle anlegen
$dbH->do(<<END_SQL);
CREATE TABLE bsp (
    item VARCHAR(25) not null
    , path VARCHAR(230) not null
    , date DATE not null
)
END_SQL

# Input-Daten verarbeiten
while (my $row = <DATA>) {

    chomp $row;

    my ($fullpath, $dateUnix) = split /;/, $row;
    
    my ($path, $item);
    
    if ($fullpath =~ m/^(.+)\/([^\/]+)$/) {
        ($path, $item) = ($1, $2);
    }
    else {
        die "wrong path format: $fullpath";
    }

    my ($ss,$mm,$hh,$day,$month,$year,$zone) = strptime($dateUnix) or die "wrong date format: $dateUnix";
    
    # Datum als Datumsobjekt (für andere RDBMS)
    #my $date = timelocal($ss,$mm,$hh,$day,$month,$year) or die "problem with date $dateUnix";
    
    # Datum im ISO-8601-Format
    my $date = sprintf "%04d-%02d-%02d %02d:%02d:%02d", $year+1900,$month+1,$day,$hh,$mm,$ss;
    
    # In DB-Tabelle schreiben
    my $dataH = $dbH->prepare(<<END_SQL) or die $dbH->errstr();
INSERT INTO bsp (
    item
    , path
    , date
) values (
    ?
    , ?
    , ?
)
END_SQL
    $dataH->execute($item, $path, $date) or die $dbH->errstr();
    $dataH->finish or die $dbH->errstr();
}

# DB-Tabelle auslesen
my $dataH = $dbH->prepare("SELECT * FROM bsp") or die $dbH->errstr();
$dataH->execute() or die $dbH->errstr();

# Zeilenweise ausgeben
printf "%-10s%-20s%s\n", 'item', 'path', 'date';
while (my $rowHR = $dataH->fetchrow_hashref()) {
    printf "%-10s%-20s%s\n", $rowHR->{item}, $rowHR->{path}, $rowHR->{date};
}

$dataH->finish or die $dbH->errstr();
$dbH->disconnect or die $dbH->errstr();

__DATA__
/a/a;Tue Sep 09 17:48:52 CEST 2014
/a/b;Tue Sep 09 17:49:52 CEST 2014
/a/c/a;Tue Sep 09 17:50:00 CEST 2014
/a/c/b;Tue Sep 09 17:53:52 CEST 2014
/a/c/c;Tue Sep 09 17:50:52 CEST 2014
/b/a;Tue Sep 09 17:53:52 CEST 2014

item      path                date
a         /a                  2014-09-09 17:48:52
b         /a                  2014-09-09 17:49:52
a         /a/c                2014-09-09 17:50:00
b         /a/c                2014-09-09 17:53:52
c         /a/c                2014-09-09 17:50:52
a         /b                  2014-09-09 17:53:52