Here are some working snippets (From old code I wrote) that may help you.
This was intended to display to a web page using CGI - but you can strip that out.
my $dbh=DBI->connect("dbi:SQLite:dbname=$db_file","","",{ RaiseError =
+> 1 }) or die("$!");
...
my %table;
Show_Tables_and_Identify_Linkages(\%table); # %table gets populated
...
#----------------------------------------------
sub Fetch_w_callback{
my ($sql, $callback,$state) = @_;
$debug{SQL} = $sql;
(my $sth=$dbh->prepare($sql))->execute();
$state->{FIELDNAMES} = $sth->{NAME};
$state->{ROW} = 0;
while (my $row = $sth->fetchrow_hashref()){
$debug{ROW} = $state->{ROW}++;
last if $callback->($row,$state) < 0; # Return negative to quit
}
$sth->finish;
}
#------------------------------------------------------
#------------------------------------------------------
sub Show_Tables_and_Identify_Linkages{
my ($tableRef) = @_; #Tableref gets populated here
#--- Show View and Table names/links -------
my %Do_not_render = map {$_ => 1} qw|XP_PROC sqlite_sequence|;
print start_table({-border=>undef, -width=>'75%', -align=>'CENTER'
+,-cellspacing=>0,
-cellpadding=>2, -bgcolor=>'SKYBLUE', id=>'ViewsAn
+dTables'}), "\n",
start_Tr;
# -- Use DBI "table_info" to get Table/view, then dig and get Colu
+mn names --
# Store obtained info into $tableref, and display table/view names
+ in html
my $count;
my %stylemap = (table=>'SampleBlock ShowTable', view=>'SampleBlock
+ ShowView');
my $sth = $dbh->table_info(undef,undef,'%',"'TABLE','VIEW'" );#(
+$catalog, $schema, $table, $type ));
while (my $t = $sth->fetchrow_hashref() ){
next if $Do_not_render{ $t->{TABLE_NAME} };
$count++ % 4 == 0 and print end_Tr,"\n",start_Tr;
param("NamedQuery", escape($t->{TABLE_NAME}));
print td( {-class=>$stylemap{ lc($t->{TABLE_TYPE}) } },
a({-href=>url(-relative=>1,-query=>1), -class=>'ShowView'
+, -title=> $t->{TABLE_TYPE} . ' query'},
$t->{TABLE_NAME}));
my @primary_keys = $dbh->primary_key( @{$t}{qw|TABLE_CAT TABLE
+_SCHEM TABLE_NAME|});# $catalog, $schema, $table );
s/"//g for @primary_keys; # Zap quotes
$tableRef->{ $t->{TABLE_NAME}}{_TYPE} = $t->{TABLE_TYPE};
$tableRef->{ $t->{TABLE_NAME}}{_TABLELC}= lc $t->{TABLE_NAME};
$tableRef->{ $t->{TABLE_NAME}}{_SQL} = $t->{sqlite_sql};
my $colcount=0;
my $colinfo = $dbh->column_info(@{$t}{qw|TABLE_CAT TABLE_SCHEM
+TABLE_NAME|},'%' );#$catalog, $schema, $table, $column );
while ($colinfo and my $c = $colinfo->fetchrow_hashref() ){
push @{ $tableRef->{$t->{TABLE_NAME}}{FIELDS} },$c->{CO
+LUMN_NAME};
push @{ $tableRef->{$t->{TABLE_NAME}}{FIELDSLC} }, lc($c-
+>{COLUMN_NAME});
$tableRef->{$t->{TABLE_NAME}}{ $c->{COLUMN_NAME} }{TYPE}=
+$c->{TYPE_NAME};# INTEGER TEXT etc
$tableRef->{$t->{TABLE_NAME}}{ $c->{COLUMN_NAME} }{PK} =
+grep {m/$c->{COLUMN_NAME}/} @primary_keys; # Primary Key
}
next if $colinfo;
# Did not get col info - try Pragma ..
Fetch_w_callback("pragma table_info($t->{TABLE_NAME})",
sub{ my ($row)=@_;
push @{ $tableRef->{$t->{TABLE_NAME}}{FIELDS} }, $row-
+>{name}; # Fld name
push @{ $tableRef->{$t->{TABLE_NAME}}{FIELDSLC} }, lc
+($row->{name});
$tableRef->{$t->{TABLE_NAME}}{ $row->{name} }{TYPE} =
+ $row->{type};
$tableRef->{$t->{TABLE_NAME}}{ $row->{name} }{PK} =
+ $row->{pk};
});
}
$sth->finish;
print "\n",end_Tr, end_table,"\n";
CGI::delete(qw|q NamedQuery|); # From parameter list - keep this c
+lean
# Identify Table Linkages (Must be post-processed after ALL table/
+field names are obtained) -----
# A Field named "xxxid' is assumed to be an FKEY to table xxx
for my $t (keys %$tableRef){
for my $f (@{ $tableRef->{$t} {FIELDS} } ){
next if $tableRef->{$t}{$f}{PK}; # Cannot link Primary keys
#next unless my ($otherTable) = grep {$_ ne $t && $tableRef->{
+$_}{_TABLELC} . "id" eq lc($f) } keys %table;
#Find linked table, if any, for this field
my $otherTable;
for my $CandidateTable (keys %table){
next if $t eq $CandidateTable; # dont link to self
my $lc_candidateT = $tableRef->{$CandidateTable}{_TAB
+LELC};
my $singular_CandidateT = substr($lc_candidateT,-1,1) eq "s
+"
? substr($lc_candidateT,0,-1) : $lc_candidateT;
$otherTable = $CandidateTable , last if $lc_candidateT . "i
+d" eq lc ($f);
$otherTable = $CandidateTable , last if $singular_Candidate
+T . "id" eq lc ($f);
}
next unless $otherTable;
$tableRef->{$t}{$f}{LINKEDTABLE} = $otherTable;
for my $fk( @{$tableRef->{$otherTable}{FIELDS}} ){
next unless $tableRef->{$otherTable}{$fk}{PK}
or lc( $fk ) eq "id";
$tableRef->{$t}{$f}{LINKEDFIELD} = $fk;
push @{ $tableRef->{$otherTable}{LINKEDTABLES} }, [$t,$f,
+$fk ];
last;
}
}
}
# Identify/Invent "View" Linkages -------
for my $t (keys %$tableRef){
next unless $tableRef->{ $t }{_TYPE} =~/^view$/i;
#Need to invent "Fields" -- INCOMPLETE - *TODO* do something h
+ere..
my $sql = $tableRef->{ $t }{_SQL} or next;
my %renamed_fields =map {lc} reverse ($sql=~/,\s*(.+?)\s+as\s+
+(\w+)/go);
next unless %renamed_fields;
for my $f (@{ $tableRef->{$t} {FIELDSLC} } ){
next unless my $origfldinfo = $renamed_fields{$f};
next unless my ($origtable,$orgfld) = $origfldinfo=~/(\w+)\
+.(\w+)/;
}
}
CGI::delete(qw|q NamedQuery|); # From parameter list
return ;
}
#------------------------------------------------------