DBIx::Admin::TableInfo - A wrapper for all of table_info(), column_info(), *_key_info()
#!/usr/bin/env perl use strict; use warnings; use Data::Dumper::Concise; use DBI; use DBIx::Admin::TableInfo 2.10; # --------------------- my($attr) = {}; $$attr{sqlite_unicode} = 1 if ($ENV{DBI_DSN} =~ /SQLite/i); my($dbh) = DBI -> connect($ENV{DBI_DSN}, $ENV{DBI_USER}, $ENV{DBI_PASS}, $attr); $dbh -> do('pragma foreign_keys = on') if ($ENV{DBI_DSN} =~ /SQLite/i); print Dumper(DBIx::Admin::TableInfo -> new(dbh => $dbh) -> info() );
If the environment vaiables DBI_DSN, DBI_USER and DBI_PASS are set (the latter 2 are optional [e.g. for SQLite), then this demonstrates extracting a lot of information from a database schema.
Also, for Postgres, you can set DBI_SCHEMA to a list of schemas, e.g. when processing the MusicBrainz database.
For details, see http://blogs.perl.org/users/ron_savage/2013/03/graphviz2-and-the-dread-musicbrainz-db.html.
See also xt/author/mysql.fk.pl and xt/author/fk.t.
DBIx::Admin::TableInfo is a pure Perl module.
DBIx::Admin::TableInfo
It is a convenient wrapper around all of these DBI methods:
Warnings:
I am testing V 2.04 of this module with MySql V 5.0.51a and DBD::mysql V 4.014.
To get foreign key information in the output, the create table statement has to:
As an example, a column definition for Postgres and SQLite, which looks like:
site_id integer not null references sites(id),
has to, for MySql, look like:
site_id integer not null, index (site_id), foreign key (site_id) references sites(id),
Further, the create table statement, which for Postgres and SQLite looks like:
create table designs (...)
create table designs (...) engine=innodb
You have been warned.
The MySQL client DBD::mysql V 3.0002 does not support primary_key_info(), so this module emulates it by stockpiling a list of columns which have the attribute 'mysql_is_pri_key' set.
DBD::mysql
primary_key_info()
The problem with this is that if a primary key consists of more than 1 column, DBD::mysql does not indicate the order of these columns within the key, so this module pretends that they are in the same order as the order of columns returned by the call to column_info().
column_info()
Likewise, DBD::mysql does not support foreign_key_info(), so in the case of MySQL, nothing is reported for foreign keys.
foreign_key_info()
For MySQL V 5.0.18, section 14.2.6.4 of the manual says that for InnoDB tables, the SQL "show table status from 'db name' like 'table name'" will display the foreign key info in the column called 'Comment', but this is simply not true. The 'Comment' column contains a string such as 'InnoDB free: 4096 kB'.
Likewise, the SQL "show create table 'table name'" reveals than MySQL does not preserve 'create table' clauses such as 'references other_table(other_column)'.
So, at the moment, I see no way of displaying foreign key information under MySQL.
See the "FAQ" for which tables are ignored under Oracle.
I am testing V 2.04 of this module with Postgres V 08.03.1100 and DBD::Pg V 2.17.1.
The latter now takes '%' as the value of the 'table' parameter to new(), whereas older versions of DBD::Pg required 'table' to be set to 'table'.
See the "FAQ" for which tables are ignored under Postgres.
I am testing V 2.04 of this module with SQLite V 3.6.22 and DBD::SQLite V 1.29.
See the "FAQ" for which tables are ignored under SQLite.
This module is available both as a Unix-style distro (*.tgz) and an ActiveState-style distro (*.ppd). The latter is shipped in a *.zip file.
See http://savage.net.au/Perl-modules.html for details.
See http://savage.net.au/Perl-modules/html/installing-a-module.html for help on unpacking and installing each type of distro.
new(...) returns a DBIx::Admin::TableInfo object.
This is the class contructor.
Usage: DBIx::Admin::TableInfo -> new().
This method takes a set of parameters. Only the dbh parameter is mandatory.
For each parameter you wish to use, call new as new(param_1 => value_1, ...).
This is the value passed in as the catalog parameter to table_info() and column_info().
The default value is undef.
undef was chosen because it given the best results with MySQL.
Note: The MySQL driver DBD::mysql V 2.9002 has a bug in it, in that it aborts if an empty string is used here, even though the DBI docs say an empty string can be used for the catalog parameter to table_info().
table_info()
This parameter is optional.
This is a database handle.
This parameter is mandatory.
This is the value passed in as the schema parameter to table_info() and column_info().
Note: If you are using Oracle, call new() with schema set to uc $user_name.
new()
Note: If you are using Postgres, call new() with schema set to 'public'.
Note: If you are using SQLite, call new() with schema set to 'main'.
This is the value passed in as the table parameter to table_info().
The default value is '%'.
Note: If you are using an 'old' version of DBD::Pg, call new() with table set to 'table'.
Sorry - I cannot tell you exactly what 'old' means. As stated above, the default value (%) works fine with DBD::Pg V 2.17.1.
This is the value passed in as the type parameter to table_info().
The default value is 'TABLE'.
Returns an array ref of column names.
By default they are sorted by name.
However, if you pass in a true value for $by_position, they are sorted by the column attribute ORDINAL_POSITION. This is Postgres-specific.
Warning: This is a function, not a method. It is called like this:
my($schema) = DBIx::Admin::TableInfo::dbh2schema($dbh);
The code is just:
my($dbh) = @_; my($vendor) = uc $dbh -> get_info(17); # SQL_DBMS_NAME. my(%schema) = ( MYSQL => undef, ORACLE => uc $$dbh{Username}, POSTGRESQL => 'public', SQLITE => 'main', ); return $schema{$vendor};
Returns a hash ref of all available data.
The structure of this hash is described next:
my($info) = $obj -> info(); my(@table_name) = sort keys %$info;
I use singular names for my arrays, hence @table_name rather than @table_names.
my($table_attributes) = $$info{$table_name}{attributes};
This is a hash ref of the attributes of the table. The keys of this hash ref are determined by the database server.
my($columns) = $$info{$table_name}{columns};
This is a hash ref of the columns of the table. The keys of this hash ref are the names of the columns.
my($foreign_keys) = $$info{$table_name}{foreign_keys};
This is a hash ref of the foreign keys of the table. The keys of this hash ref are the names of the tables which contain foreign keys pointing to $table_name.
For MySQL, $foreign_keys will be the empty hash ref {}, as explained above.
my($primary_keys) = $$info{$table_name}{primary_keys};
This is a hash ref of the primary keys of the table. The keys of this hash ref are the names of the columns which make up the primary key of $table_name.
For any database server, if there is more than 1 column in the primary key, they will be numbered (ordered) according to the hash key 'KEY_SEQ'.
For MySQL, if there is more than 1 column in the primary key, they will be artificially numbered according to the order in which they are returned by column_info(), as explained above.
my($table_attributes) = $$info{$table_name}{attributes}; while ( ($name, $value) = each(%$table_attributes) ) { Use... }
For the attributes of the tables, there are no more levels in the hash ref.
my($columns) = $$info{$table_name}{columns}; my(@column_name) = sort keys %$columns;
for $column_name (@column_name) { while ( ($name, $value) = each(%{$columns{$column_name} }) ) { Use... } }
These tables have foreign keys which point to the current table.
my($foreign_keys) = $$info{$table_name}{foreign_keys}; for $foreign_table (sort keys %$foreign_keys) { $foreign_key = $$foreign_keys{$foreign_table}; for $attribute (sort keys %$foreign_key) { Use... } }
These columns make up the primary key of the current table.
my($primary_keys) = $$info{$table_name}{primary_keys}; for $primary_key (sort{$$a{KEY_SEQ} <=> $$b{KEY_SEQ} } keys %$primary_keys) { $primary = $$primary_keys{$primary_key}; for $attribute (sort keys %$primary) { Use... } }
Returns the same hash ref as info().
Use this after changing the database schema, when you want this module to re-interrogate the database server.
Returns an array ref of table names.
They are sorted by name.
See the "FAQ" for which tables are ignored under which databases.
Here are tested parameter values for various database vendors:
my($admin) = DBIx::Admin::TableInfo -> new(dbh => $dbh); In other words, the default values for catalog, schema, table and type will Just Work.
my($dbh) = DBI -> connect($dsn, $username, $password); my($admin) = DBIx::Admin::TableInfo -> new ( dbh => $dbh, schema => uc $username, # Yep, upper case. ); See the FAQ for which tables are ignored under Oracle.
my($admin) = DBIx::Admin::TableInfo -> new ( dbh => $dbh, schema => 'public', ); For PostgreSQL, you probably want to ignore table names matching /^(pg_|sql_)/. As stated above, for 'old' versions of DBD::Pg, use: my($admin) = DBIx::Admin::TableInfo -> new ( dbh => $dbh, schema => 'public', table => 'table', # Yep, lower case. ); See the FAQ for which tables are ignored under Postgres.
my($admin) = DBIx::Admin::TableInfo -> new ( dbh => $dbh, schema => 'main', ); In other words, the default values for catalog, table and type will Just Work. See the FAQ for which tables are ignored under SQLite.
See the examples/ directory in the distro.
Versions as at 2014-03-07 +----------|------------+ | Vendor | V | +----------|------------+ | MariaDB | 5.5.36 | +----------|------------+ | Oracle | 10.2.0.1.0 | (Not tested for years) +----------|------------+ | Postgres | 9.1.12 | +----------|------------+ | SQLite | 3.7.17 | +----------|------------+
Here is the code which skips some tables:
next if ( ($vendor eq 'ORACLE') && ($table_name =~ /^BIN\$.+\$./) ); next if ( ($vendor eq 'POSTGRESQL') && ($table_name =~ /^(?:pg_|sql_)/) ); next if ( ($vendor eq 'SQLITE') && ($table_name eq 'sqlite_sequence') );
See "FAQ" in DBIx::Admin::CreateTable for database server-specific create statements to activate foreign keys.
Then try:
my($info) = DBIx::Admin::TableInfo -> new(dbh => $dbh) -> info; print Data::Dumper::Concise::Dumper($$info{one}{foreign_keys}), "\n";
Output follows. Each is a hashref with the keys being the names of tables (in this case 'two') pointing to table 'one'.
But beware slightly differing spellings depending on the database server. This is documented in https://metacpan.org/pod/DBI#foreign_key_info. Look closely at the usage of the '_' character.
two => { DEFERABILITY => undef, DELETE_RULE => undef, FKCOLUMN_NAME => "one_id", FKTABLE_CAT => "def", FKTABLE_NAME => "two", FKTABLE_SCHEM => "testdb", FK_NAME => "two_ibfk_1", KEY_SEQ => 1, PKCOLUMN_NAME => "id", PKTABLE_CAT => undef, PKTABLE_NAME => "one", PKTABLE_SCHEM => "testdb", PK_NAME => undef, UNIQUE_OR_PRIMARY => undef, UPDATE_RULE => undef }
two => { DEFERABILITY => 7, DELETE_RULE => 3, FK_COLUMN_NAME => "one_id", FK_DATA_TYPE => "int4", FK_NAME => "two_one_id_fkey", FK_TABLE_CAT => undef, FK_TABLE_NAME => "two", FK_TABLE_SCHEM => "public", ORDINAL_POSITION => 1, UK_COLUMN_NAME => "id", UK_DATA_TYPE => "int4", UK_NAME => "one_pkey", UK_TABLE_CAT => undef, UK_TABLE_NAME => "one", UK_TABLE_SCHEM => "public", UNIQUE_OR_PRIMARY => "PRIMARY", UPDATE_RULE => 3 }
two => { DEFERABILITY => undef, DELETE_RULE => 3, FK_COLUMN_NAME => "one_id", FK_DATA_TYPE => undef, FK_NAME => undef, FK_TABLE_CAT => undef, FK_TABLE_NAME => "two", FK_TABLE_SCHEM => undef, ORDINAL_POSITION => 0, UK_COLUMN_NAME => "id", UK_DATA_TYPE => undef, UK_NAME => undef, UK_TABLE_CAT => undef, UK_TABLE_NAME => "one", UK_TABLE_SCHEM => undef, UNIQUE_OR_PRIMARY => undef, UPDATE_RULE => 3 }
You can also play with xt/author/fk.t and xt/author/dsn.ini (especially the 'active' option).
Yes. As of V 2.08, this module uses SQLite's "pragma foreign_key_list($table_name)" to emulate DBI's $dbh -> foreign_key_info(...).
Fields returned are: 0: COUNT (0, 1, ...) 1: KEY_SEQ (0, or column # (1, 2, ...) within multi-column key) 2: FKTABLE_NAME 3: PKCOLUMN_NAME 4: FKCOLUMN_NAME 5: UPDATE_RULE 6: DELETE_RULE 7: 'NONE' (Constant string)
As these are stored in an arrayref, I use $$row[$i] just below to refer to the elements of the array.
my(%referential_action) = ( 'CASCADE' => 0, 'RESTRICT' => 1, 'SET NULL' => 2, 'NO ACTION' => 3, 'SET DEFAULT' => 4, );
The hashref returned for foreign keys contains these key-value pairs:
{ DEFERABILITY => undef, DELETE_RULE => $referential_action{$$row[6]}, FK_COLUMN_NAME => $$row[3], FK_DATA_TYPE => undef, FK_NAME => undef, FK_TABLE_CAT => undef, FK_TABLE_NAME => $foreign_table, FK_TABLE_SCHEM => undef, ORDINAL_POSITION => $$row[1], UK_COLUMN_NAME => $$row[4], UK_DATA_TYPE => undef, UK_NAME => undef, UK_TABLE_CAT => undef, UK_TABLE_NAME => $table_name, UK_TABLE_SCHEM => undef, UNIQUE_OR_PRIMARY => undef, UPDATE_RULE => $referential_action{$$row[5]}, }
This list of keys matches what is returned when processing a Postgres database.
No, I don't think so.
Here is a method from the module App::Office::Contacts::Util::Create, part of App::Office::Contacts.
sub create_organizations_table { my($self) = @_; my($table_name) = 'organizations'; my($primary_key) = $self -> creator -> generate_primary_key_sql($table_name); my($engine) = $self -> engine; my($result) = $self -> creator -> create_table(<<SQL); create table $table_name ( id $primary_key, visibility_id integer not null references visibilities(id), communication_type_id integer not null references communication_types(id), creator_id integer not null, role_id integer not null references roles(id), deleted integer not null, facebook_tag varchar(255) not null, homepage varchar(255) not null, name varchar(255) not null, timestamp timestamp not null default localtimestamp, twitter_tag varchar(255) not null, upper_name varchar(255) not null ) $engine SQL $self -> dbh -> do("create index ${table_name}_upper_name on $table_name (upper_name)"); $self -> report($table_name, 'created', $result); } # End of create_organizations_table.
Consider this line:
visibility_id integer not null references visibilities(id),
That means, for the 'visibilities' table, the info() method in the current module will return a hashref like:
{ visibilities => { ... foreign_keys => { ... organizations => { UK_COLUMN_NAME => 'id', DEFERABILITY => undef, ORDINAL_POSITION => 0, FK_TABLE_CAT => undef, UK_NAME => undef, UK_DATA_TYPE => undef, UNIQUE_OR_PRIMARY => undef, UK_TABLE_SCHEM => undef, UK_TABLE_CAT => undef, FK_COLUMN_NAME => 'visibility_id', FK_TABLE_NAME => 'organizations', FK_TABLE_SCHEM => undef, FK_DATA_TYPE => undef, UK_TABLE_NAME => 'visibilities', DELETE_RULE => 3, FK_NAME => undef, UPDATE_RULE => 3 }, }, }
This is saying that for the table 'visibilities', there is a foreign key in the 'organizations' table. That foreign key is called 'visibility_id', and it points to the key called 'id' in the 'visibilities' table.
You may need to do something like this:
$dbh -> do("set search_path to $ENV{DBI_SCHEMA}") if ($ENV{DBI_SCHEMA});
$ENV{DBI_SCHEMA} can be a comma-separated list, as in:
$dbh -> do("set search_path to my_schema, public");
See DBD::Pg for details.
DBIx::Admin::CreateTable.
DBIx::Admin::DSNManager.
Version numbers < 1.00 represent development versions. From 1.00 up, they are production versions.
Log a bug on RT: https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-Admin-TableInfo.
DBIx::Admin::TableInfo was written by Ron Savage <ron@savage.net.au> in 2004.
Home page: http://savage.net.au/index.html
Australian copyright (c) 2004, Ron Savage.
All Programs of mine are 'OSI Certified Open Source Software'; you can redistribute them and/or modify them under the terms of The Artistic License, a copy of which is available at: http://www.opensource.org/licenses/index.html
To install DBIx::Admin::TableInfo, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Admin::TableInfo
CPAN shell
perl -MCPAN -e shell install DBIx::Admin::TableInfo
For more information on module installation, please visit the detailed CPAN module installation guide.