Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re: sqlite show tables

by NetWallah (Canon)
on Jul 19, 2021 at 05:10 UTC ( #11135154=note: print w/replies, xml ) Need Help??

in reply to sqlite show tables

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 ; } #------------------------------------------------------

                "The difficult we do today; the impossible takes a little longer."

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11135154]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (3)
As of 2022-09-25 15:33 GMT
Find Nodes?
    Voting Booth?
    I prefer my indexes to start at:

    Results (116 votes). Check out past polls.