#!/usr/bin/perl
use warnings;
use strict;

use DBI;
use Data::Dumper;

use constant {
        KEY   => 0,
        VALUE => 1,
        WHERE => 2,
        NEW   => 3,
       };


sub store {
    my ($db, $struct) = @_;
    $db->do('DROP TABLE IF EXISTS t');
    $db->do('CREATE TABLE t (key VARCHAR, value VARCHAR, ref INT)');

    my $store = $db->prepare('INSERT INTO t VALUES (?, ?, ?)');

    while (my ($key, $value) = each %$struct) {
        _store($db, $store, $key, $value);
    }
}


sub _store {
    my ($db, $store, $key, $value, $where) = @_;
    if (my $ref = ref $value) {
        $store->execute($key, $ref, $where);
        my $new = $db->last_insert_id((q()) x 4);

        if ('HASH' eq $ref) {
            while (my ($k, $v) = each %$value) {
                _store($db, $store, $k, $v, $new);
            }

        } elsif ('ARRAY' eq $ref) {
            _store($db, $store, $_, $value->[$_], $new) for 0 .. $#{$value};
        }

    } else {
        $store->execute($key, $value, $where);
    }
}


sub retrieve {
    my ($db) = @_;
    my %hash;
    my $select = $db->prepare('SELECT ROWID, key, value, ref FROM t ORDER BY ROWID');
    $select->execute;
    while (my @row = $select->fetchrow_array) {
        $hash{ $row[0] } = [ @row[1 .. 3] ];
    }

    my %return;
    for my $key (sort { $b <=> $a } keys %hash) {
        my $new = defined $hash{$key}[NEW] ? $hash{$key}[NEW] : $hash{$key}[VALUE];

        if (defined $hash{$key}[WHERE]) {
            my $where = $hash{ $hash{$key}[WHERE] };

            if ('ARRAY' eq $where->[VALUE]) {
                unshift @{ $where->[NEW] }, $new;
            } else {
                $where->[NEW]{ $hash{$key}[KEY] } = $new;
            }

        } else {
            $return{$hash{$key}[KEY]} = $new;
        }
    }
    return %return;
}


my %struct
    = (
       sv0 => 100,
       sv1 => 'string',
       hv  => {
                l1sv => 'deeper',
                l1hv => { x => 10, y => 20 },
                l1av => [qw/1 2 3 A B C/]
              },
       av  => [ 5, 'e',
                { k0 => 'v0', k1 => 'v1' },
                [qw/Mercury Venus Mars/]
              ],
      );


my $db = 'DBI'->connect('dbi:SQLite:dbname=1.db', q(), q());

store($db, \%struct);

%struct = retrieve($db);

print Dumper \%struct;