CODEWITHSUNDEEP
perlschemadbi
karthi_ms
karthi_ms

Reputation: 5698

How do I get schemas from Perl's DBI?

I am using Perl DBI. I know that $dbase->tables() will return all the tables in the corresponding database. Likewise, I want to know the schemas available in the database. Is there any function available for that?

Upvotes: 6

Views: 6836

Answers (3)

mivk
mivk

Reputation: 14889

Using ODBC to an Oracle database, I had to use this variation on Uncle Arnie's answer:

my $table_info = $dbh->table_info(undef, '%', undef);
my $schemas    = $table_info->fetchall_arrayref([1]);

print "Schemas :\n",
      join( "\n", map {$_->[0]} @$schemas ), "\n";

Otherwise, $schemas would be undefined when trying to use selectcol_arrayref($sth, ...).

Upvotes: 0

Uncle Arnie
Uncle Arnie

Reputation: 1655

What you're looking for is: DBI->table_info()

Call it like this:

my $sth = $dbh->table_info('', '%', '');
my $schemas = $dbh->selectcol_arrayref($sth, {Columns => [2]});
print "Schemas: ", join ', ', @$schemas;

Upvotes: 11

daxim
daxim

Reputation: 39158

This works.

Create a database:

echo 'create table foo (bar integer primary key, quux varchar(30));' | sqlite3 foobar.sqlite

Perl program to print schema:

use 5.010;
use Data::Dumper qw(Dumper);
use DBIx::Class::Schema::Loader qw();
DBIx::Class::Schema::Loader->naming('current');
DBIx::Class::Schema::Loader->use_namespaces(1);

my $dbi_dsn = 'dbi:SQLite:dbname=foobar.sqlite';
my ($dbi_user, $dbi_pass);
my $schema = DBIx::Class::Schema::Loader->connect(
    $dbi_dsn, $dbi_user, $dbi_pass, {'AutoCommit' => 1, 'RaiseError' => 1,}
);

for my $source_name ($schema->sources) {
    say "*** Source: $source_name";
    my $result_source = $schema->source($source_name);
    for my $column_name ($result_source->columns) {
        say "Column: $column_name";
        say Dumper $result_source->column_info($column_name);
    }
}

Output:

*** Source: Foo
Column: bar
$VAR1 = {
          'data_type' => 'integer',
          'is_auto_increment' => 1,
          'is_nullable' => 1
        };

Column: quux
$VAR1 = {
          'data_type' => 'varchar',
          'is_nullable' => 1,
          'size' => 30
        };

Upvotes: 1

Related Questions