Re: [sqlite] Getting a table's field labels with Perl, DBI
On Jan 26, 2011, at 11:04 AM, BareFeetWare wrote: > Yes, it is very unfortunate that INFORMATION_SCHEMA or similar is not > provided by SQLite, Agree, SQLite is seriously lacking in that domain :/ > a topic that keeps recurring here in one form or another. Eh! > Yu currently have to do your own parsing of the schema to get much of the > info about columns, triggers, foreign keys etc. Short of parsing the DDL itself, a concoction of sqlite_master.type, sqlite_temp_master , pragma database_list, table_info, index_list, index_info, foreign_key_list is what you have to brew to get a partial data dictionary of sort: http://dev.alt.textdrive.com/browser/IMDB/Info.ddl#L68 Oh, well, better than nothing I guess :)) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a table's field labels with Perl, DBI
On 26/01/2011, at 8:43 PM, John Delacour wrote: > I did try a few things along those lines and seemed to come across > the problem mentioned on that page : "...Unfortunately, > INFORMATION_SCHEMA views are seldom supported by the DBMS..." Yes, it is very unfortunate that INFORMATION_SCHEMA or similar is not provided by SQLite, a topic that keeps recurring here in one form or another. Yu currently have to do your own parsing of the schema to get much of the info about columns, triggers, foreign keys etc. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a table's field labels with Perl, DBI
At 22:41 -0800 25/01/2011, Darren Duncan wrote:
>How long have you been using Perl?
>
>Anyway, to start with I would replace the last couple lines with:
>
>my $catalog_rowset = $dbh->selectall_arrayref("PRAGMA
>table_info(contacts)") ;
>my @col_names = map { $_->[1] } @{$catalog_rowset};
>print join ', ', @col_names;
Yes, that's neat, and an encouragement to me to get 'map' into my
head. I've been using Perl long enough to know better but it's only
when I need to advance my knowledge for a real task that I push my
learning to meet the occasion.
>Another thing you can try is use DBI's special methods for basic
>system catalog information, rather than using a SQL query to get
>that information as you did, not that the way you did it is wrong
>per se, but just an alternate means to the end.
>
>I refer to http://search.cpan.org/dist/DBI/DBI.pm#Catalog_Methods :
>
>column_info ...
I did try a few things along those lines and seemed to come across
the problem mentioned on that page : "...Unfortunately,
INFORMATION_SCHEMA views are seldom supported by the DBMS..."
Nevertheless I obviously need to read DBI.pm more thoroughly.
Thank you.
JD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a table's field labels with Perl, DBI
John Delacour wrote:
> I've only been using SQLite for 5 days so I'm very much a beginner.
> I just spent an hour or so working out how to get a list of column
> headers from a table and come up with the script below, which will do
> fine, but I wonder if there's a more elegant way to do it.
>
> #!/usr/local/bin/perl
> use strict;
> use DBI qw(:sql_types);
> {
> my $db = "a.db";
> my $dbh = DBI->connect("dbi:SQLite:dbname=$db","","") or "...";
> $_ = $dbh->selectall_arrayref("PRAGMA table_info(contacts)") ;
> for (@$_) {push @_, $$_[1]} print join ', ', @_;
> }
> # => firm, adr1, postcode1, adr2, postcode2, ...
>
> JD
How long have you been using Perl?
Anyway, to start with I would replace the last couple lines with:
my $catalog_rowset = $dbh->selectall_arrayref("PRAGMA table_info(contacts)")
;
my @col_names = map { $_->[1] } @{$catalog_rowset};
print join ', ', @col_names;
Another thing you can try is use DBI's special methods for basic system catalog
information, rather than using a SQL query to get that information as you did,
not that the way you did it is wrong per se, but just an alternate means to the
end.
I refer to http://search.cpan.org/dist/DBI/DBI.pm#Catalog_Methods :
column_info
foreign_key_info
primary_key_info
table_info
statistics_info
... and those are described elsewhere on that page.
I haven't used those myself, though, but I believe they are popular for others.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting a table's field labels with Perl, DBI
Hello John Delacour...nice to see you join the SCLite list.
Folks, John may be a SCLite beginner, but he has quite a reputation on the
MacScript list. I personally am glad to see him here.
R,
John
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
> On Behalf Of John Delacour
> Sent: Tuesday, January 25, 2011 10:42 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Getting a table's field labels with Perl, DBI
>
>
> I've only been using SQLite for 5 days so I'm very much a beginner.
> I just spent an hour or so working out how to get a list of column
> headers from a table and come up with the script below, which will do
> fine, but I wonder if there's a more elegant way to do it.
>
>
> #!/usr/local/bin/perl
> use strict;
> use DBI qw(:sql_types);
> {
> my $db = "a.db";
> my $dbh = DBI->connect("dbi:SQLite:dbname=$db","","") or "...";
> $_ = $dbh->selectall_arrayref("PRAGMA table_info(contacts)") ;
> for (@$_) {push @_, $$_[1]} print join ', ', @_;
> }
> # => firm, adr1, postcode1, adr2, postcode2, ...
>
> JD
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
