1DBIx::Admin::TableInfo(U3s)er Contributed Perl DocumentatDiBoInx::Admin::TableInfo(3)
2
6 DBIx::Admin::TableInfo - A wrapper for all of table_info(),
7 column_info(), *_key_info()
8
10 This is scripts/synopsis.pl:
11 #!/usr/bin/perl
13 use strict;
15 use warnings;
16 use DBI;
18 use DBIx::Admin::TableInfo 3.02;
19 use Lingua::EN::PluralToSingular 'to_singular';
21 use Text::Table::Manifold ':constants';
23 # ---------------------
25 my($attr) = {};
27 $$attr{sqlite_unicode} = 1 if ($ENV{DBI_DSN} =~ /SQLite/i);
28 my($dbh) = DBI -> connect($ENV{DBI_DSN}, $ENV{DBI_USER}, $ENV{DBI_PASS}, $attr);
29 my($vendor_name) = uc $dbh -> get_info(17);
30 my($info) = DBIx::Admin::TableInfo -> new(dbh => $dbh) -> info;
31 $dbh -> do('pragma foreign_keys = on') if ($ENV{DBI_DSN} =~ /SQLite/i);
33 my($temp_1, $temp_2, $temp_3);
35 if ($vendor_name eq 'MYSQL')
37 {
38 $temp_1 = 'PKTABLE_NAME';
39 $temp_2 = 'FKTABLE_NAME';
40 $temp_3 = 'FKCOLUMN_NAME';
41 }
42 else # ORACLE && POSTGRESQL && SQLITE (at least).
43 {
44 $temp_1 = 'UK_TABLE_NAME';
45 $temp_2 = 'FK_TABLE_NAME';
46 $temp_3 = 'FK_COLUMN_NAME';
47 }
48 my(%special_fk_column) =
50 (
51 spouse_id => 'person_id',
52 );
53 my($destination_port);
55 my($fk_column_name, $fk_table_name, %foreign_key);
56 my($pk_table_name, $primary_key_name);
57 my($singular_name, $source_port);
58 for my $table_name (sort keys %$info)
60 {
61 for my $item (@{$$info{$table_name}{foreign_keys} })
62 {
63 $pk_table_name = $$item{$temp_1};
64 $fk_table_name = $$item{$temp_2};
65 $fk_column_name = $$item{$temp_3};
66 if ($pk_table_name)
68 {
69 $singular_name = to_singular($pk_table_name);
70 if ($special_fk_column{$fk_column_name})
72 {
73 $primary_key_name = $special_fk_column{$fk_column_name};
74 }
75 elsif (defined($$info{$table_name}{columns}{$fk_column_name}) )
76 {
77 $primary_key_name = $fk_column_name;
78 }
79 elsif (defined($$info{$table_name}{columns}{id}) )
80 {
81 $primary_key_name = 'id';
82 }
83 else
84 {
85 die "Primary table '$pk_table_name'. Foreign table '$fk_table_name'. Unable to find primary key name for foreign key '$fk_column_name'\n"
86 }
87 $foreign_key{$fk_table_name} = {} if (! $foreign_key{$fk_table_name});
89 $foreign_key{$fk_table_name}{$fk_column_name} = {} if (! $foreign_key{$fk_table_name}{$fk_column_name});
90 $primary_key_name =~ s/${singular_name}_//;
91 $foreign_key{$fk_table_name}{$fk_column_name}{$table_name} = $primary_key_name;
92 }
93 }
94 }
95 my(@header) =
97 (
98 'Name',
99 'Type',
100 'Null',
101 'Key',
102 'Auto-increment',
103 );
104 my($table) = Text::Table::Manifold -> new
106 (
107 alignment =>
108 [
109 align_left,
110 align_left,
111 align_left,
112 align_left,
113 align_left,
114 ],
115 format => format_text_unicodebox_table,
116 headers => \@header,
117 join => "\n",
118 );
119 my(%type) =
120 (
121 'character varying' => 'varchar',
122 'int(11)' => 'integer',
123 '"timestamp"' => 'timestamp',
124 );
125 my($auto_increment);
127 my(@data);
128 my($index);
129 my($nullable);
130 my($primary_key);
131 my($type);
132 for my $table_name (sort keys %$info)
134 {
135 print "Table: $table_name.\n\n";
136 @data = ();
138 $index = undef;
139 for my $column_name (keys %{$$info{$table_name}{columns} })
141 {
142 $type = $$info{$table_name}{columns}{$column_name}{TYPE_NAME};
143 $type = $type{$type} ? $type{$type} : $type;
144 $nullable = $$info{$table_name}{columns}{$column_name}{IS_NULLABLE} eq 'NO';
145 $primary_key = $$info{$table_name}{primary_keys}{$column_name};
146 $auto_increment = $primary_key; # Database server-independent kludge :-(.
147 push @data,
149 [
150 $column_name,
151 $type,
152 $nullable ? 'not null' : '',
153 $primary_key ? 'primary key' : '',
154 $auto_increment ? 'auto_increment' : '',
155 ];
156 $index = pop @data if ($column_name eq 'id');
158 }
159 @data = sort{$$a[0] cmp $$b[0]} @data;
161 unshift @data, $index if ($index);
163 $table -> data(\@data);
165 print $table -> render_as_string, "\n\n";
167 }
168 If the environment vaiables DBI_DSN, DBI_USER and DBI_PASS are set (the
170 latter 2 are optional [e.g. for SQLite), then this demonstrates
171 extracting a lot of information from a database schema.
172 Also, for Postgres, you can set DBI_SCHEMA to a list of schemas, e.g.
174 when processing the MusicBrainz database.
175 For details, see
177 <http://blogs.perl.org/users/ron_savage/2013/03/graphviz2-and-the-dread-musicbrainz-db.html>.
178 See also xt/author/fk.t, xt/author/mysql.fk.pl and
180 xt/author/person.spouse.t.
181
183 "DBIx::Admin::TableInfo" is a pure Perl module.
184 It is a convenient wrapper around all of these DBI methods:
186 o table_info()
188 o column_info()
189 o primary_key_info()
190 o foreign_key_info()
191 o MySQL
192 Warning:
193 To get foreign key information in the output, the create table
195 statement has to:
196 o Include an index clause
198 o Include a foreign key clause
199 o Include an engine clause
200 As an example, a column definition for Postgres and SQLite,
201 which looks like:
202 site_id integer not null references sites(id),
204 has to, for MySql, look like:
206 site_id integer not null, index (site_id), foreign key (site_id) references sites(id),
208 Further, the create table statement, which for Postgres and
210 SQLite looks like:
211 create table designs (...)
213 has to, for MySql, look like:
215 create table designs (...) engine=innodb
217 o Oracle
219 See the "FAQ" for which tables are ignored under Oracle.
220 o Postgres
222 The latter now takes '%' as the value of the 'table' parameter to
223 new(), whereas older versions of DBD::Pg required 'table' to be set
224 to 'table'.
225 See the "FAQ" for which tables are ignored under Postgres.
227 o SQLite
229 See the "FAQ" for which tables are ignored under SQLite.
230
232 This module is available both as a Unix-style distro (*.tgz) and an
233 ActiveState-style distro (*.ppd). The latter is shipped in a *.zip
234 file.
235 See http://savage.net.au/Perl-modules.html for details.
237 See http://savage.net.au/Perl-modules/html/installing-a-module.html for
239 help on unpacking and installing each type of distro.
240
242 new(...) returns a "DBIx::Admin::TableInfo" object.
243 This is the class contructor.
245 Usage: DBIx::Admin::TableInfo -> new().
247 This method takes a set of parameters. Only the dbh parameter is
249 mandatory.
250 For each parameter you wish to use, call new as new(param_1 => value_1,
252 ...).
253 o catalog
255 This is the value passed in as the catalog parameter to
256 table_info() and column_info().
257 The default value is undef.
259 undef was chosen because it given the best results with MySQL.
261 Note: The MySQL driver DBD::mysql V 2.9002 has a bug in it, in that
263 it aborts if an empty string is used here, even though the DBI docs
264 say an empty string can be used for the catalog parameter to
265 "table_info()".
266 This parameter is optional.
268 o dbh
270 This is a database handle.
271 This parameter is mandatory.
273 o schema
275 This is the value passed in as the schema parameter to table_info()
276 and column_info().
277 The default value is undef.
279 Note: If you are using Oracle, call "new()" with schema set to uc
281 $user_name.
282 Note: If you are using Postgres, call "new()" with schema set to
284 'public'.
285 Note: If you are using SQLite, call "new()" with schema set to
287 'main'.
288 This parameter is optional.
290 o table
292 This is the value passed in as the table parameter to table_info().
293 The default value is '%'.
295 Note: If you are using an 'old' version of DBD::Pg, call "new()"
297 with table set to 'table'.
298 Sorry - I cannot tell you exactly what 'old' means. As stated
300 above, the default value (%) works fine with DBD::Pg V 2.17.1.
301 This parameter is optional.
303 o type
305 This is the value passed in as the type parameter to table_info().
306 The default value is 'TABLE'.
308 This parameter is optional.
310
312 columns($table_name, $by_position)
313 Returns an array ref of column names.
314 By default they are sorted by name.
316 However, if you pass in a true value for $by_position, they are sorted
318 by the column attribute ORDINAL_POSITION. This is Postgres-specific.
319 dbh2schema($dbh)
321 Warning: This is a function, not a method. It is called like this:
322 my($schema) = DBIx::Admin::TableInfo::dbh2schema($dbh);
324 The code is just:
326 my($dbh) = @_;
328 my($vendor) = uc $dbh -> get_info(17); # SQL_DBMS_NAME.
329 my(%schema) =
330 (
331 MYSQL => undef,
332 ORACLE => uc $$dbh{Username},
333 POSTGRESQL => 'public',
334 SQLITE => 'main',
335 );
336 return $schema{$vendor};
338 info()
340 Returns a hash ref of all available data.
341 The structure of this hash is described next:
343 o First level: The keys are the names of the tables
345 my($info) = $obj -> info();
346 my(@table_name) = sort keys %$info;
347 I use singular names for my arrays, hence @table_name rather than
349 @table_names.
350 o Second level: The keys are 'attributes', 'columns', 'foreign_keys'
352 and 'primary_keys'
353 my($table_attributes) = $$info{$table_name}{attributes};
354 This is a hash ref of the attributes of the table. The keys of
356 this hash ref are determined by the database server.
357 my($columns) = $$info{$table_name}{columns};
359 This is a hash ref of the columns of the table. The keys of this
361 hash ref are the names of the columns.
362 my($foreign_keys) = $$info{$table_name}{foreign_keys};
364 This is a hash ref of the foreign keys of the table. The keys of
366 this hash ref are the names of the tables which contain foreign
367 keys pointing to $table_name.
368 For MySQL, $foreign_keys will be the empty hash ref {}, as
370 explained above.
371 my($primary_keys) = $$info{$table_name}{primary_keys};
373 This is a hash ref of the primary keys of the table. The keys of
375 this hash ref are the names of the columns which make up the
376 primary key of $table_name.
377 For any database server, if there is more than 1 column in the
379 primary key, they will be numbered (ordered) according to the hash
380 key 'KEY_SEQ'.
381 For MySQL, if there is more than 1 column in the primary key, they
383 will be artificially numbered according to the order in which they
384 are returned by "column_info()", as explained above.
385 o Third level, after 'attributes': Table attributes
387 my($table_attributes) = $$info{$table_name}{attributes};
388 while ( ($name, $value) = each(%$table_attributes) )
390 {
391 Use...
392 }
393 For the attributes of the tables, there are no more levels in the
395 hash ref.
396 o Third level, after 'columns': The keys are the names of the columns.
398 my($columns) = $$info{$table_name}{columns};
399 my(@column_name) = sort keys %$columns;
401 o Fourth level: Column attributes
403 for $column_name (@column_name)
404 {
405 while ( ($name, $value) = each(%{$columns{$column_name} }) )
406 {
407 Use...
408 }
409 }
410 o Third level, after 'foreign_keys': An arrayref contains the details
412 (if any)
413 But beware slightly differing spellings depending on the database
414 server. This is documented in
415 <https://metacpan.org/pod/DBI#foreign_key_info>. Look closely at
416 the usage of the '_' character.
417 my($vendor) = uc $dbh -> get_info(17); # SQL_DBMS_NAME.
419 for $item (@{$$info{$table_name}{foreign_keys} })
421 {
422 # Get the name of the table pointed to.
423 $primary_table = ($vendor eq 'MYSQL') ? $$item{PKTABLE_NAME} : $$item{UK_TABLE_NAME};
425 }
426 o Third level, after 'primary_keys': The keys are the names of columns
428 These columns make up the primary key of the current table.
429 my($primary_keys) = $$info{$table_name}{primary_keys};
431 for $primary_key (sort{$$a{KEY_SEQ} <=> $$b{KEY_SEQ} } keys %$primary_keys)
433 {
434 $primary = $$primary_keys{$primary_key};
435 for $attribute (sort keys %$primary)
437 {
438 Use...
439 }
440 }
441 refresh()
443 Returns the same hash ref as info().
444 Use this after changing the database schema, when you want this module
446 to re-interrogate the database server.
447 tables()
449 Returns an array ref of table names.
450 They are sorted by name.
452 See the "FAQ" for which tables are ignored under which databases.
454
456 Here are tested parameter values for various database vendors:
457 o MS Access
459 my($admin) = DBIx::Admin::TableInfo -> new(dbh => $dbh);
460 In other words, the default values for catalog, schema, table and type will Just Work.
462 o MySQL
464 my($admin) = DBIx::Admin::TableInfo -> new(dbh => $dbh);
465 In other words, the default values for catalog, schema, table and type will Just Work.
467 o Oracle
469 my($dbh) = DBI -> connect($dsn, $username, $password);
470 my($admin) = DBIx::Admin::TableInfo -> new
471 (
472 dbh => $dbh,
473 schema => uc $username, # Yep, upper case.
474 );
475 See the FAQ for which tables are ignored under Oracle.
477 o PostgreSQL
479 my($admin) = DBIx::Admin::TableInfo -> new
480 (
481 dbh => $dbh,
482 schema => 'public',
483 );
484 For PostgreSQL, you probably want to ignore table names matching /^(pg_|sql_)/.
486 As stated above, for 'old' versions of DBD::Pg, use:
488 my($admin) = DBIx::Admin::TableInfo -> new
490 (
491 dbh => $dbh,
492 schema => 'public',
493 table => 'table', # Yep, lower case.
494 );
495 See the FAQ for which tables are ignored under Postgres.
497 o SQLite
499 my($admin) = DBIx::Admin::TableInfo -> new
500 (
501 dbh => $dbh,
502 schema => 'main',
503 );
504 In other words, the default values for catalog, table and type will Just Work.
506 See the FAQ for which tables are ignored under SQLite.
508 See the examples/ directory in the distro.
510
512 Which versions of the servers did you test?
513 Versions as at 2014-08-06:
514 +----------|-------------+
515 | Vendor | V |
516 +----------|-------------+
517 | MariaDB | 5.5.38 |
518 +----------|-------------+
519 | Oracle | 10.2.0.1.0 | (Not tested for years)
520 +----------|-------------+
521 | Postgres | 9.1.3 |
522 +----------|-------------+
523 | SQLite | 3.8.4.1 |
524 +----------|-------------+
525 But see these warnings
527 <https://metacpan.org/pod/DBIx::Admin::TableInfo#Description> when
528 using MySQL/MariaDB.
529 Which tables are ignored for which databases?
531 Here is the code which skips some tables:
532 next if ( ($vendor eq 'ORACLE') && ($table_name =~ /^BIN\$.+\$./) );
534 next if ( ($vendor eq 'POSTGRESQL') && ($table_name =~ /^(?:pg_|sql_)/) );
535 next if ( ($vendor eq 'SQLITE') && ($table_name eq 'sqlite_sequence') );
536 How do I identify foreign keys?
538 o See scripts/foreign.keys.pl
539 First set the environment variables DBI_DSN, DBI_USER and DBI_PASS.
540 Then, it writes in CSV format to STDOUT, which you can redirect to,
542 say, foreign.keys.csv.
543 o Take a very simplistic and brute-force approach
545 Note: The table names here come from xt/author/person.spouse.t.
546 See "FAQ" in DBIx::Admin::CreateTable for database server-specific
548 create statements to activate foreign keys.
549 First set the environment variables DBI_DSN, DBI_USER and DBI_PASS.
551 Then try:
553 my($info) = DBIx::Admin::TableInfo -> new(dbh => $dbh) -> info;
555 print Data::Dumper::Concise::Dumper($$info{people}{foreign_keys}), "\n";
557 Sample output follows below.
559 Beware: Slightly differing spellings depending on the database server.
561 This is documented in <https://metacpan.org/pod/DBI#foreign_key_info>.
562 Look closely at the presence or absence of the '_' character.
563 o MySQL
565 [
566 {
567 DEFERABILITY => undef,
568 DELETE_RULE => undef,
569 FKCOLUMN_NAME => "spouse_id",
570 FKTABLE_CAT => "def",
571 FKTABLE_NAME => "spouses",
572 FKTABLE_SCHEM => "testdb",
573 FK_NAME => "spouses_ibfk_2",
574 KEY_SEQ => 1,
575 PKCOLUMN_NAME => "id",
576 PKTABLE_CAT => undef,
577 PKTABLE_NAME => "people",
578 PKTABLE_SCHEM => "testdb",
579 PK_NAME => undef,
580 UNIQUE_OR_PRIMARY => undef,
581 UPDATE_RULE => undef
582 }
583 ]
584 Yes, there is just 1 element in this arrayref. MySQL can sliently
586 drop an index if another index can be used.
587 o Postgres
589 [
590 {
591 DEFERABILITY => 7,
592 DELETE_RULE => 3,
593 FK_COLUMN_NAME => "person_id",
594 FK_DATA_TYPE => "int4",
595 FK_NAME => "spouses_person_id_fkey",
596 FK_TABLE_CAT => undef,
597 FK_TABLE_NAME => "spouses",
598 FK_TABLE_SCHEM => "public",
599 ORDINAL_POSITION => 1,
600 UK_COLUMN_NAME => "id",
601 UK_DATA_TYPE => "int4",
602 UK_NAME => "people_pkey",
603 UK_TABLE_CAT => undef,
604 UK_TABLE_NAME => "people",
605 UK_TABLE_SCHEM => "public",
606 UNIQUE_OR_PRIMARY => "PRIMARY",
607 UPDATE_RULE => 3
608 },
609 {
610 DEFERABILITY => 7,
611 DELETE_RULE => 3,
612 FK_COLUMN_NAME => "spouse_id",
613 FK_DATA_TYPE => "int4",
614 FK_NAME => "spouses_spouse_id_fkey",
615 FK_TABLE_CAT => undef,
616 FK_TABLE_NAME => "spouses",
617 FK_TABLE_SCHEM => "public",
618 ORDINAL_POSITION => 1,
619 UK_COLUMN_NAME => "id",
620 UK_DATA_TYPE => "int4",
621 UK_NAME => "people_pkey",
622 UK_TABLE_CAT => undef,
623 UK_TABLE_NAME => "people",
624 UK_TABLE_SCHEM => "public",
625 UNIQUE_OR_PRIMARY => "PRIMARY",
626 UPDATE_RULE => 3
627 }
628 ]
629 o SQLite
631 [
632 {
633 DEFERABILITY => undef,
634 DELETE_RULE => 3,
635 FK_COLUMN_NAME => "spouse_id",
636 FK_DATA_TYPE => undef,
637 FK_NAME => undef,
638 FK_TABLE_CAT => undef,
639 FK_TABLE_NAME => "spouses",
640 FK_TABLE_SCHEM => undef,
641 ORDINAL_POSITION => 0,
642 UK_COLUMN_NAME => "id",
643 UK_DATA_TYPE => undef,
644 UK_NAME => undef,
645 UK_TABLE_CAT => undef,
646 UK_TABLE_NAME => "people",
647 UK_TABLE_SCHEM => undef,
648 UNIQUE_OR_PRIMARY => undef,
649 UPDATE_RULE => 3
650 },
651 {
652 DEFERABILITY => undef,
653 DELETE_RULE => 3,
654 FK_COLUMN_NAME => "person_id",
655 FK_DATA_TYPE => undef,
656 FK_NAME => undef,
657 FK_TABLE_CAT => undef,
658 FK_TABLE_NAME => "spouses",
659 FK_TABLE_SCHEM => undef,
660 ORDINAL_POSITION => 0,
661 UK_COLUMN_NAME => "id",
662 UK_DATA_TYPE => undef,
663 UK_NAME => undef,
664 UK_TABLE_CAT => undef,
665 UK_TABLE_NAME => "people",
666 UK_TABLE_SCHEM => undef,
667 UNIQUE_OR_PRIMARY => undef,
668 UPDATE_RULE => 3
669 }
670 ]
671 You can also play with xt/author/fk.t and xt/author/dsn.ini (especially
673 the 'active' option).
674 fk.t does not delete the tables as it exits. This is so
676 xt/author/mysql.fk.pl has something to play with.
677 See also xt/author/person.spouse.t.
679 Does DBIx::Admin::TableInfo work with SQLite databases?
681 Yes. As of V 2.08, this module uses the SQLite code "pragma
682 foreign_key_list($table_name)" to emulate the DBI call to
683 foreign_key_info(...).
684 What is returned by the SQLite "pragma foreign_key_list($table_name)" call?
686 An arrayref is returned. Indexes and their interpretations:
687 0: COUNT (0, 1, ...)
689 1: KEY_SEQ (0, or column # (1, 2, ...) within multi-column key)
690 2: PK_TABLE_NAME
691 3: FK_COLUMN_NAME
692 4: PK_COLUMN_NAME
693 5: UPDATE_RULE
694 6: DELETE_RULE
695 7: 'NONE' (Constant string)
696 As these are stored in an arrayref, I use $$row[$i] just below to refer
698 to the elements of the array.
699 How are these values mapped into the output?
701 See also the next point.
702 my(%referential_action) =
704 (
705 'CASCADE' => 0,
706 'RESTRICT' => 1,
707 'SET NULL' => 2,
708 'NO ACTION' => 3,
709 'SET DEFAULT' => 4,
710 );
711 The hashref returned for foreign keys contains these key-value pairs:
713 {
715 DEFERABILITY => undef,
716 DELETE_RULE => $referential_action{$$row[6]},
717 FK_COLUMN_NAME => $$row[3],
718 FK_DATA_TYPE => undef,
719 FK_NAME => undef,
720 FK_TABLE_CAT => undef,
721 FK_TABLE_NAME => $table_name,
722 FK_TABLE_SCHEM => undef,
723 ORDINAL_POSITION => $$row[1],
724 UK_COLUMN_NAME => $$row[4],
725 UK_DATA_TYPE => undef,
726 UK_NAME => undef,
727 UK_TABLE_CAT => undef,
728 UK_TABLE_NAME => $$row[2],
729 UK_TABLE_SCHEM => undef,
730 UNIQUE_OR_PRIMARY => undef,
731 UPDATE_RULE => $referential_action{$$row[5]},
732 }
733 This list of keys matches what is returned when processing a Postgres
735 database.
736 Have you gotten FK and PK backwards?
738 I certainly hope not. To me the FK_TABLE_NAME points to the
739 UK_TABLE_NAME.
740 The "pragma foreign_key_list($table_name)" call for SQLite returns data
742 from the create statement, and thus it reports what the given table
743 points to. The DBI call to foreign_key_info(...) returns data about
744 foreign keys referencing (pointing to) the given table. This can be
745 confusing.
746 Here is a method from the module App::Office::Contacts::Util::Create,
748 part of App::Office::Contacts.
749 sub create_organizations_table
751 {
752 my($self) = @_;
753 my($table_name) = 'organizations';
754 my($primary_key) = $self -> creator -> generate_primary_key_sql($table_name);
755 my($engine) = $self -> engine;
756 my($result) = $self -> creator -> create_table(<<SQL);
757 create table $table_name
758 (
759 id $primary_key,
760 visibility_id integer not null references visibilities(id),
761 communication_type_id integer not null references communication_types(id),
762 creator_id integer not null,
763 role_id integer not null references roles(id),
764 deleted integer not null,
765 facebook_tag varchar(255) not null,
766 homepage varchar(255) not null,
767 name varchar(255) not null,
768 timestamp timestamp not null default localtimestamp,
769 twitter_tag varchar(255) not null,
770 upper_name varchar(255) not null
771 ) $engine
772 SQL
773 $self -> dbh -> do("create index ${table_name}_upper_name on $table_name (upper_name)");
775 $self -> report($table_name, 'created', $result);
777 } # End of create_organizations_table.
779 Consider this line:
781 visibility_id integer not null references visibilities(id),
783 That means, for the 'visibilities' table, the info() method in the
785 current module will return a hashref like:
786 {
788 visibilities =>
789 {
790 ...
791 foreign_keys =>
792 {
793 ...
794 organizations =>
795 {
796 UK_COLUMN_NAME => 'id',
797 DEFERABILITY => undef,
798 ORDINAL_POSITION => 0,
799 FK_TABLE_CAT => undef,
800 UK_NAME => undef,
801 UK_DATA_TYPE => undef,
802 UNIQUE_OR_PRIMARY => undef,
803 UK_TABLE_SCHEM => undef,
804 UK_TABLE_CAT => undef,
805 FK_COLUMN_NAME => 'visibility_id',
806 FK_TABLE_NAME => 'organizations',
807 FK_TABLE_SCHEM => undef,
808 FK_DATA_TYPE => undef,
809 UK_TABLE_NAME => 'visibilities',
810 DELETE_RULE => 3,
811 FK_NAME => undef,
812 UPDATE_RULE => 3
813 },
814 },
815 }
816 This is saying that for the table 'visibilities', there is a foreign
818 key in the 'organizations' table. That foreign key is called
819 'visibility_id', and it points to the key called 'id' in the
820 'visibilities' table.
821 How do I use schemas in Postgres?
823 You may need to do something like this:
824 $dbh -> do("set search_path to $ENV{DBI_SCHEMA}") if ($ENV{DBI_SCHEMA});
826 $ENV{DBI_SCHEMA} can be a comma-separated list, as in:
828 $dbh -> do("set search_path to my_schema, public");
830 See DBD::Pg for details.
832 See Also
834 DBIx::Admin::CreateTable.
835 DBIx::Admin::DSNManager.
837
839 Version numbers < 1.00 represent development versions. From 1.00 up,
840 they are production versions.
841
843 <https://github.com/ronsavage/DBIx-Admin-TableInfo>
844
846 Bugs should be reported via the CPAN bug tracker at
847 <https://github.com/ronsavage/DBIx-Admin-TableInfo/issues>
849
851 "DBIx::Admin::TableInfo" was written by Ron Savage <ron@savage.net.au>
852 in 2004.
853 Home page: http://savage.net.au/index.html
855
857 Australian copyright (c) 2004, Ron Savage.
858 All Programs of mine are 'OSI Certified Open Source Software';
860 you can redistribute them and/or modify them under the terms of
861 The Perl License, a copy of which is available at:
862 http://www.opensource.org/licenses/index.html
863perl v5.34.0 2022-01-21 DBIx::Admin::TableInfo(3)