1DBIx::Admin::TableInfo(U3s)er Contributed Perl DocumentatDiBoInx::Admin::TableInfo(3)
2
3
4

NAME

6       DBIx::Admin::TableInfo - A wrapper for all of table_info(),
7       column_info(), *_key_info()
8

Synopsis

10       This is scripts/synopsis.pl:
11
12               #!/usr/bin/env perl
13
14               use strict;
15               use warnings;
16
17               use DBI;
18               use DBIx::Admin::TableInfo 3.02;
19
20               use Lingua::EN::PluralToSingular 'to_singular';
21
22               use Text::Table::Manifold ':constants';
23
24               # ---------------------
25
26               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
32               $dbh -> do('pragma foreign_keys = on') if ($ENV{DBI_DSN} =~ /SQLite/i);
33
34               my($temp_1, $temp_2, $temp_3);
35
36               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
49               my(%special_fk_column) =
50               (
51                       spouse_id => 'person_id',
52               );
53
54               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
59               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
67                               if ($pk_table_name)
68                               {
69                                       $singular_name = to_singular($pk_table_name);
70
71                                       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
88                                       $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
96               my(@header) =
97               (
98                       'Name',
99                       'Type',
100                       'Null',
101                       'Key',
102                       'Auto-increment',
103               );
104
105               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
126               my($auto_increment);
127               my(@data);
128               my($index);
129               my($nullable);
130               my($primary_key);
131               my($type);
132
133               for my $table_name (sort keys %$info)
134               {
135                       print "Table: $table_name.\n\n";
136
137                       @data  = ();
138                       $index = undef;
139
140                       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
148                               push @data,
149                               [
150                                       $column_name,
151                                       $type,
152                                       $nullable       ? 'not null'       : '',
153                                       $primary_key    ? 'primary key'    : '',
154                                       $auto_increment ? 'auto_increment' : '',
155                               ];
156
157                               $index = pop @data if ($column_name eq 'id');
158                       }
159
160                       @data = sort{$$a[0] cmp $$b[0]} @data;
161
162                       unshift @data, $index if ($index);
163
164                       $table -> data(\@data);
165
166                       print $table -> render_as_string, "\n\n";
167               }
168
169       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
173       Also, for Postgres, you can set DBI_SCHEMA to a list of schemas, e.g.
174       when processing the MusicBrainz database.
175
176       For details, see
177       <http://blogs.perl.org/users/ron_savage/2013/03/graphviz2-and-the-dread-musicbrainz-db.html>.
178
179       See also xt/author/fk.t, xt/author/mysql.fk.pl and
180       xt/author/person.spouse.t.
181

Description

183       "DBIx::Admin::TableInfo" is a pure Perl module.
184
185       It is a convenient wrapper around all of these DBI methods:
186
187       o table_info()
188       o column_info()
189       o primary_key_info()
190       o foreign_key_info()
191       o MySQL
192           Warning:
193
194           To get foreign key information in the output, the create table
195           statement has to:
196
197           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
203                       site_id integer not null references sites(id),
204
205               has to, for MySql, look like:
206
207                       site_id integer not null, index (site_id), foreign key (site_id) references sites(id),
208
209               Further, the create table statement, which for Postgres and
210               SQLite looks like:
211
212                       create table designs (...)
213
214               has to, for MySql, look like:
215
216                       create table designs (...) engine=innodb
217
218       o Oracle
219           See the "FAQ" for which tables are ignored under Oracle.
220
221       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
226           See the "FAQ" for which tables are ignored under Postgres.
227
228       o SQLite
229           See the "FAQ" for which tables are ignored under SQLite.
230

Distributions

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
236       See http://savage.net.au/Perl-modules.html for details.
237
238       See http://savage.net.au/Perl-modules/html/installing-a-module.html for
239       help on unpacking and installing each type of distro.
240

Constructor and initialization

242       new(...) returns a "DBIx::Admin::TableInfo" object.
243
244       This is the class contructor.
245
246       Usage: DBIx::Admin::TableInfo -> new().
247
248       This method takes a set of parameters. Only the dbh parameter is
249       mandatory.
250
251       For each parameter you wish to use, call new as new(param_1 => value_1,
252       ...).
253
254       o catalog
255           This is the value passed in as the catalog parameter to
256           table_info() and column_info().
257
258           The default value is undef.
259
260           undef was chosen because it given the best results with MySQL.
261
262           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
267           This parameter is optional.
268
269       o dbh
270           This is a database handle.
271
272           This parameter is mandatory.
273
274       o schema
275           This is the value passed in as the schema parameter to table_info()
276           and column_info().
277
278           The default value is undef.
279
280           Note: If you are using Oracle, call "new()" with schema set to uc
281           $user_name.
282
283           Note: If you are using Postgres, call "new()" with schema set to
284           'public'.
285
286           Note: If you are using SQLite, call "new()" with schema set to
287           'main'.
288
289           This parameter is optional.
290
291       o table
292           This is the value passed in as the table parameter to table_info().
293
294           The default value is '%'.
295
296           Note: If you are using an 'old' version of DBD::Pg, call "new()"
297           with table set to 'table'.
298
299           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
302           This parameter is optional.
303
304       o type
305           This is the value passed in as the type parameter to table_info().
306
307           The default value is 'TABLE'.
308
309           This parameter is optional.
310

Methods

312   columns($table_name, $by_position)
313       Returns an array ref of column names.
314
315       By default they are sorted by name.
316
317       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
320   dbh2schema($dbh)
321       Warning: This is a function, not a method. It is called like this:
322
323               my($schema) = DBIx::Admin::TableInfo::dbh2schema($dbh);
324
325       The code is just:
326
327               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
337               return $schema{$vendor};
338
339   info()
340       Returns a hash ref of all available data.
341
342       The structure of this hash is described next:
343
344       o First level: The keys are the names of the tables
345                   my($info)       = $obj -> info();
346                   my(@table_name) = sort keys %$info;
347
348           I use singular names for my arrays, hence @table_name rather than
349           @table_names.
350
351       o Second level: The keys are 'attributes', 'columns', 'foreign_keys'
352       and 'primary_keys'
353                   my($table_attributes) = $$info{$table_name}{attributes};
354
355           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
358                   my($columns) = $$info{$table_name}{columns};
359
360           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
363                   my($foreign_keys) = $$info{$table_name}{foreign_keys};
364
365           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
369           For MySQL, $foreign_keys will be the empty hash ref {}, as
370           explained above.
371
372                   my($primary_keys) = $$info{$table_name}{primary_keys};
373
374           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
378           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
382           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
386       o Third level, after 'attributes': Table attributes
387                   my($table_attributes) = $$info{$table_name}{attributes};
388
389                   while ( ($name, $value) = each(%$table_attributes) )
390                   {
391                           Use...
392                   }
393
394           For the attributes of the tables, there are no more levels in the
395           hash ref.
396
397       o Third level, after 'columns': The keys are the names of the columns.
398                   my($columns) = $$info{$table_name}{columns};
399
400                   my(@column_name) = sort keys %$columns;
401
402           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
411       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
418                   my($vendor) = uc $dbh -> get_info(17); # SQL_DBMS_NAME.
419
420                   for $item (@{$$info{$table_name}{foreign_keys} })
421                   {
422                           # Get the name of the table pointed to.
423
424                           $primary_table = ($vendor eq 'MYSQL') ? $$item{PKTABLE_NAME} : $$item{UK_TABLE_NAME};
425                   }
426
427       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
430                   my($primary_keys) = $$info{$table_name}{primary_keys};
431
432                   for $primary_key (sort{$$a{KEY_SEQ} <=> $$b{KEY_SEQ} } keys %$primary_keys)
433                   {
434                           $primary = $$primary_keys{$primary_key};
435
436                           for $attribute (sort keys %$primary)
437                           {
438                                   Use...
439                           }
440                   }
441
442   refresh()
443       Returns the same hash ref as info().
444
445       Use this after changing the database schema, when you want this module
446       to re-interrogate the database server.
447
448   tables()
449       Returns an array ref of table names.
450
451       They are sorted by name.
452
453       See the "FAQ" for which tables are ignored under which databases.
454

Example code

456       Here are tested parameter values for various database vendors:
457
458       o MS Access
459                   my($admin) = DBIx::Admin::TableInfo -> new(dbh => $dbh);
460
461                   In other words, the default values for catalog, schema, table and type will Just Work.
462
463       o MySQL
464                   my($admin) = DBIx::Admin::TableInfo -> new(dbh => $dbh);
465
466                   In other words, the default values for catalog, schema, table and type will Just Work.
467
468       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
476                   See the FAQ for which tables are ignored under Oracle.
477
478       o PostgreSQL
479                   my($admin) = DBIx::Admin::TableInfo -> new
480                   (
481                           dbh    => $dbh,
482                           schema => 'public',
483                   );
484
485                   For PostgreSQL, you probably want to ignore table names matching /^(pg_|sql_)/.
486
487                   As stated above, for 'old' versions of DBD::Pg, use:
488
489                   my($admin) = DBIx::Admin::TableInfo -> new
490                   (
491                           dbh    => $dbh,
492                           schema => 'public',
493                           table  => 'table', # Yep, lower case.
494                   );
495
496                   See the FAQ for which tables are ignored under Postgres.
497
498       o SQLite
499                   my($admin) = DBIx::Admin::TableInfo -> new
500                   (
501                           dbh    => $dbh,
502                           schema => 'main',
503                   );
504
505                   In other words, the default values for catalog, table and type will Just Work.
506
507                   See the FAQ for which tables are ignored under SQLite.
508
509       See the examples/ directory in the distro.
510

FAQ

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
526       But see these warnings
527       <https://metacpan.org/pod/DBIx::Admin::TableInfo#Description> when
528       using MySQL/MariaDB.
529
530   Which tables are ignored for which databases?
531       Here is the code which skips some tables:
532
533               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
537   How do I identify foreign keys?
538       Note: The table names here come from xt/author/person.spouse.t.
539
540       See "FAQ" in DBIx::Admin::CreateTable for database server-specific
541       create statements to activate foreign keys.
542
543       Then try:
544
545               my($info) = DBIx::Admin::TableInfo -> new(dbh => $dbh) -> info;
546
547               print Data::Dumper::Concise::Dumper($$info{people}{foreign_keys}), "\n";
548
549       Output follows.
550
551       But beware slightly differing spellings depending on the database
552       server. This is documented in
553       <https://metacpan.org/pod/DBI#foreign_key_info>. Look closely at the
554       usage of the '_' character.
555
556       o MySQL
557                   [
558                     {
559                       DEFERABILITY => undef,
560                       DELETE_RULE => undef,
561                       FKCOLUMN_NAME => "spouse_id",
562                       FKTABLE_CAT => "def",
563                       FKTABLE_NAME => "spouses",
564                       FKTABLE_SCHEM => "testdb",
565                       FK_NAME => "spouses_ibfk_2",
566                       KEY_SEQ => 1,
567                       PKCOLUMN_NAME => "id",
568                       PKTABLE_CAT => undef,
569                       PKTABLE_NAME => "people",
570                       PKTABLE_SCHEM => "testdb",
571                       PK_NAME => undef,
572                       UNIQUE_OR_PRIMARY => undef,
573                       UPDATE_RULE => undef
574                     }
575                   ]
576
577           Yes, there is just 1 element in this arrayref. MySQL can sliently
578           drop an index if another index can be used.
579
580       o Postgres
581                   [
582                     {
583                       DEFERABILITY => 7,
584                       DELETE_RULE => 3,
585                       FK_COLUMN_NAME => "person_id",
586                       FK_DATA_TYPE => "int4",
587                       FK_NAME => "spouses_person_id_fkey",
588                       FK_TABLE_CAT => undef,
589                       FK_TABLE_NAME => "spouses",
590                       FK_TABLE_SCHEM => "public",
591                       ORDINAL_POSITION => 1,
592                       UK_COLUMN_NAME => "id",
593                       UK_DATA_TYPE => "int4",
594                       UK_NAME => "people_pkey",
595                       UK_TABLE_CAT => undef,
596                       UK_TABLE_NAME => "people",
597                       UK_TABLE_SCHEM => "public",
598                       UNIQUE_OR_PRIMARY => "PRIMARY",
599                       UPDATE_RULE => 3
600                     },
601                     {
602                       DEFERABILITY => 7,
603                       DELETE_RULE => 3,
604                       FK_COLUMN_NAME => "spouse_id",
605                       FK_DATA_TYPE => "int4",
606                       FK_NAME => "spouses_spouse_id_fkey",
607                       FK_TABLE_CAT => undef,
608                       FK_TABLE_NAME => "spouses",
609                       FK_TABLE_SCHEM => "public",
610                       ORDINAL_POSITION => 1,
611                       UK_COLUMN_NAME => "id",
612                       UK_DATA_TYPE => "int4",
613                       UK_NAME => "people_pkey",
614                       UK_TABLE_CAT => undef,
615                       UK_TABLE_NAME => "people",
616                       UK_TABLE_SCHEM => "public",
617                       UNIQUE_OR_PRIMARY => "PRIMARY",
618                       UPDATE_RULE => 3
619                     }
620                   ]
621
622       o SQLite
623                   [
624                     {
625                       DEFERABILITY => undef,
626                       DELETE_RULE => 3,
627                       FK_COLUMN_NAME => "spouse_id",
628                       FK_DATA_TYPE => undef,
629                       FK_NAME => undef,
630                       FK_TABLE_CAT => undef,
631                       FK_TABLE_NAME => "spouses",
632                       FK_TABLE_SCHEM => undef,
633                       ORDINAL_POSITION => 0,
634                       UK_COLUMN_NAME => "id",
635                       UK_DATA_TYPE => undef,
636                       UK_NAME => undef,
637                       UK_TABLE_CAT => undef,
638                       UK_TABLE_NAME => "people",
639                       UK_TABLE_SCHEM => undef,
640                       UNIQUE_OR_PRIMARY => undef,
641                       UPDATE_RULE => 3
642                     },
643                     {
644                       DEFERABILITY => undef,
645                       DELETE_RULE => 3,
646                       FK_COLUMN_NAME => "person_id",
647                       FK_DATA_TYPE => undef,
648                       FK_NAME => undef,
649                       FK_TABLE_CAT => undef,
650                       FK_TABLE_NAME => "spouses",
651                       FK_TABLE_SCHEM => undef,
652                       ORDINAL_POSITION => 0,
653                       UK_COLUMN_NAME => "id",
654                       UK_DATA_TYPE => undef,
655                       UK_NAME => undef,
656                       UK_TABLE_CAT => undef,
657                       UK_TABLE_NAME => "people",
658                       UK_TABLE_SCHEM => undef,
659                       UNIQUE_OR_PRIMARY => undef,
660                       UPDATE_RULE => 3
661                     }
662                   ]
663
664       You can also play with xt/author/fk.t and xt/author/dsn.ini (especially
665       the 'active' option).
666
667       fk.t does not delete the tables as it exits. This is so
668       xt/author/mysql.fk.pl has something to play with.
669
670       See also xt/author/person.spouse.t.
671
672   Does DBIx::Admin::TableInfo work with SQLite databases?
673       Yes. As of V 2.08, this module uses the SQLite code "pragma
674       foreign_key_list($table_name)" to emulate the DBI call to
675       foreign_key_info(...).
676
677   What is returned by the SQLite "pragma foreign_key_list($table_name)" call?
678       An arrayref is returned. Indexes and their interpretations:
679
680               0: COUNT   (0, 1, ...)
681               1: KEY_SEQ (0, or column # (1, 2, ...) within multi-column key)
682               2: PK_TABLE_NAME
683               3: FK_COLUMN_NAME
684               4: PK_COLUMN_NAME
685               5: UPDATE_RULE
686               6: DELETE_RULE
687               7: 'NONE' (Constant string)
688
689       As these are stored in an arrayref, I use $$row[$i] just below to refer
690       to the elements of the array.
691
692   How are these values mapped into the output?
693       See also the next point.
694
695               my(%referential_action) =
696               (
697                       'CASCADE'     => 0,
698                       'RESTRICT'    => 1,
699                       'SET NULL'    => 2,
700                       'NO ACTION'   => 3,
701                       'SET DEFAULT' => 4,
702               );
703
704       The hashref returned for foreign keys contains these key-value pairs:
705
706               {
707                       DEFERABILITY      => undef,
708                       DELETE_RULE       => $referential_action{$$row[6]},
709                       FK_COLUMN_NAME    => $$row[3],
710                       FK_DATA_TYPE      => undef,
711                       FK_NAME           => undef,
712                       FK_TABLE_CAT      => undef,
713                       FK_TABLE_NAME     => $table_name,
714                       FK_TABLE_SCHEM    => undef,
715                       ORDINAL_POSITION  => $$row[1],
716                       UK_COLUMN_NAME    => $$row[4],
717                       UK_DATA_TYPE      => undef,
718                       UK_NAME           => undef,
719                       UK_TABLE_CAT      => undef,
720                       UK_TABLE_NAME     => $$row[2],
721                       UK_TABLE_SCHEM    => undef,
722                       UNIQUE_OR_PRIMARY => undef,
723                       UPDATE_RULE       => $referential_action{$$row[5]},
724               }
725
726       This list of keys matches what is returned when processing a Postgres
727       database.
728
729   Have you got FK and PK backwards?
730       I certainly hope not. To me the FK_TABLE_NAME points to the
731       UK_TABLE_NAME.
732
733       The "pragma foreign_key_list($table_name)" call for SQLite returns data
734       from the create statement, and thus it reports what the given table
735       points to. The DBI call to foreign_key_info(...) returns data about
736       foreign keys referencing (pointing to) the given table. This can be
737       confusing.
738
739       Here is a method from the module App::Office::Contacts::Util::Create,
740       part of App::Office::Contacts.
741
742               sub create_organizations_table
743               {
744                       my($self)        = @_;
745                       my($table_name)  = 'organizations';
746                       my($primary_key) = $self -> creator -> generate_primary_key_sql($table_name);
747                       my($engine)      = $self -> engine;
748                       my($result)      = $self -> creator -> create_table(<<SQL);
749       create table $table_name
750       (
751               id $primary_key,
752               visibility_id integer not null references visibilities(id),
753               communication_type_id integer not null references communication_types(id),
754               creator_id integer not null,
755               role_id integer not null references roles(id),
756               deleted integer not null,
757               facebook_tag varchar(255) not null,
758               homepage varchar(255) not null,
759               name varchar(255) not null,
760               timestamp timestamp not null default localtimestamp,
761               twitter_tag varchar(255) not null,
762               upper_name varchar(255) not null
763       ) $engine
764       SQL
765
766                       $self -> dbh -> do("create index ${table_name}_upper_name on $table_name (upper_name)");
767
768                       $self -> report($table_name, 'created', $result);
769
770               }       # End of create_organizations_table.
771
772       Consider this line:
773
774               visibility_id integer not null references visibilities(id),
775
776       That means, for the 'visibilities' table, the info() method in the
777       current module will return a hashref like:
778
779               {
780                       visibilities =>
781                       {
782                               ...
783                               foreign_keys =>
784                               {
785                                       ...
786                                       organizations =>
787                                       {
788                                               UK_COLUMN_NAME    => 'id',
789                                               DEFERABILITY      => undef,
790                                               ORDINAL_POSITION  => 0,
791                                               FK_TABLE_CAT      => undef,
792                                               UK_NAME           => undef,
793                                               UK_DATA_TYPE      => undef,
794                                               UNIQUE_OR_PRIMARY => undef,
795                                               UK_TABLE_SCHEM    => undef,
796                                               UK_TABLE_CAT      => undef,
797                                               FK_COLUMN_NAME    => 'visibility_id',
798                                               FK_TABLE_NAME     => 'organizations',
799                                               FK_TABLE_SCHEM    => undef,
800                                               FK_DATA_TYPE      => undef,
801                                               UK_TABLE_NAME     => 'visibilities',
802                                               DELETE_RULE       => 3,
803                                               FK_NAME           => undef,
804                                               UPDATE_RULE       => 3
805                                       },
806                               },
807               }
808
809       This is saying that for the table 'visibilities', there is a foreign
810       key in the 'organizations' table. That foreign key is called
811       'visibility_id', and it points to the key called 'id' in the
812       'visibilities' table.
813
814   How do I use schemas in Postgres?
815       You may need to do something like this:
816
817               $dbh -> do("set search_path to $ENV{DBI_SCHEMA}") if ($ENV{DBI_SCHEMA});
818
819       $ENV{DBI_SCHEMA} can be a comma-separated list, as in:
820
821               $dbh -> do("set search_path to my_schema, public");
822
823       See DBD::Pg for details.
824
825   See Also
826       DBIx::Admin::CreateTable.
827
828       DBIx::Admin::DSNManager.
829

Version Numbers

831       Version numbers < 1.00 represent development versions. From 1.00 up,
832       they are production versions.
833

Repository

835       <https://github.com/ronsavage/DBIx-Admin-TableInfo>
836

Support

838       Log a bug on RT:
839       <https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-Admin-TableInfo>.
840

Author

842       "DBIx::Admin::TableInfo" was written by Ron Savage <ron@savage.net.au>
843       in 2004.
844
845       Home page: http://savage.net.au/index.html
846
848       Australian copyright (c) 2004, Ron Savage.
849
850               All Programs of mine are 'OSI Certified Open Source Software';
851               you can redistribute them and/or modify them under the terms of
852               The Perl License, a copy of which is available at:
853               http://www.opensource.org/licenses/index.html
854
855
856
857perl v5.30.1                      2020-01-29         DBIx::Admin::TableInfo(3)
Impressum