use strict; use DBI; use DBD::SQLite; use Data::Dumper; use Getopt::Long; $| = 1; my $datafile = $0 . q{.sqlite}; if ( scalar grep( /^-/, @ARGV ) ) { GetOptions( "datafile=s" => \$datafile, ); } if ( !-e $datafile ) { open( DF, q{>>} . $datafile ) or die(qq{Couldn't open $datafile for append: $!\n}); close(DF); } my $dbh = DBI->connect( qq{dbi:SQLite:dbname=$datafile}, q{}, q{}, { AutoCommit => 0 } ); { my $tables_query = q{ SELECT COUNT(name) FROM ( SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master ) WHERE type='table'; }; my $tqs = $dbh->prepare($tables_query); my $tqr = $tqs->execute; my @row_tqr = $tqs->fetchrow_array; $tqs->finish; if ( !$row_tqr[0] ) { my $create_table_query = q{ CREATE TABLE employee ( id INTEGER PRIMARY KEY, name STRING, title STRING, supervisor INTEGER, UNIQUE(id) ); }; my $cr = $dbh->do($create_table_query); foreach my $create_index_query ( q{CREATE INDEX id_idx ON employee ( id );}, q{CREATE INDEX name_idx ON employee ( name ); }, q{CREATE INDEX supervisor_idx ON employee ( supervisor );}, ) { $cr = $dbh->do($create_index_query) or die( $dbh->errstr ); } my $preload_query = q{INSERT INTO employee ( id, name, title, supervisor ) VALUES ( NULL, ?, 'foo', ? ); }; my $pq = $dbh->prepare($preload_query); # print q{employee_1}, q{ -> }, q{1}, qq{\n}; $pq->execute( q{employee_} . 1, 1 ) or die $dbh->errstr; foreach my $i ( 1 .. 8 ) { foreach my $j ( ( 2**( $i - 1 ) + 1 ) .. ( 2**$i ) ) { my $e_name = sprintf q{employee_%d}, $j; my $e_sup = int( 2**( $i - 1 ) ); # print $e_name, q{ -> }, $e_sup, qq{\n}; $pq->execute( $e_name, $e_sup ) or die $dbh->errstr; } } $pq->finish; $dbh->commit; } } my $employee = 128; # &walk_all; print qq{Hierarchy for employee ${employee}:\n}; my @chain = get_chain($employee); # print Data::Dumper->Dump( [ \@chain, ], [ qw( *chain ) ] ), qq{\n}; foreach (@chain) { print $_->{name}, qq{\n}; } $dbh->disconnect; sub walk_all { my $emp = q{SELECT id, name, title, supervisor FROM employee ORDER BY id; }; my $emps = $dbh->prepare($emp); my $empr = $emps->execute or die( $dbh->errstr ); while ( my @row_emp = $emps->fetchrow_array ) { print join( q{, }, @row_emp ), qq{\n}; } $emps->finish; } sub get_chain { my ($e_id) = @_; my $en_select = q{SELECT name, supervisor FROM employee WHERE ( id = ? ) LIMIT 1;}; my $ens = $dbh->prepare($en_select); my $enr = $ens->execute($e_id) or die $dbh->errstr; my %link = ( id => $e_id, ); my @row_enr = $ens->fetchrow_array; $link{name} = $row_enr[0]; if ( defined $row_enr[1] and $row_enr[1] != $e_id ) { $link{supervisor} = $row_enr[1]; return ( get_chain( $link{supervisor} ), \%link ); } else { return ( \%link ); } } # $dbh = DBI->connect( qq{dbi:SQLite:dbname=$datafile}, q{}, q{} ); # my $maintenance = q{VACUUM employee; }; # $dbh->do($maintenance); # $dbh->disconnect; __END__