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/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() with
297           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       o See scripts/foreign.keys.pl
539           First set the environment variables DBI_DSN, DBI_USER and DBI_PASS.
540
541           Then, it writes in CSV format to STDOUT, which you can redirect to,
542           say, foreign.keys.csv.
543
544       o Take a very simplistic and brute-force approach
545           Note: The table names here come from xt/author/person.spouse.t.
546
547           See "FAQ" in DBIx::Admin::CreateTable for database server-specific
548           create statements to activate foreign keys.
549
550           First set the environment variables DBI_DSN, DBI_USER and DBI_PASS.
551
552           Then try:
553
554                   my($info) = DBIx::Admin::TableInfo -> new(dbh => $dbh) -> info;
555
556                   print Data::Dumper::Concise::Dumper($$info{people}{foreign_keys}), "\n";
557
558           Sample output follows below.
559
560       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
564       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
585           Yes, there is just 1 element in this arrayref. MySQL can sliently
586           drop an index if another index can be used.
587
588       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
630       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
672       You can also play with xt/author/fk.t and xt/author/dsn.ini (especially
673       the 'active' option).
674
675       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
678       See also xt/author/person.spouse.t.
679
680   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
685   What is returned by the SQLite "pragma foreign_key_list($table_name)" call?
686       An arrayref is returned. Indexes and their interpretations:
687
688               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
697       As these are stored in an arrayref, I use $$row[$i] just below to refer
698       to the elements of the array.
699
700   How are these values mapped into the output?
701       See also the next point.
702
703               my(%referential_action) =
704               (
705                       'CASCADE'     => 0,
706                       'RESTRICT'    => 1,
707                       'SET NULL'    => 2,
708                       'NO ACTION'   => 3,
709                       'SET DEFAULT' => 4,
710               );
711
712       The hashref returned for foreign keys contains these key-value pairs:
713
714               {
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
734       This list of keys matches what is returned when processing a Postgres
735       database.
736
737   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
741       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
747       Here is a method from the module App::Office::Contacts::Util::Create,
748       part of App::Office::Contacts.
749
750               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
774                       $self -> dbh -> do("create index ${table_name}_upper_name on $table_name (upper_name)");
775
776                       $self -> report($table_name, 'created', $result);
777
778               }       # End of create_organizations_table.
779
780       Consider this line:
781
782               visibility_id integer not null references visibilities(id),
783
784       That means, for the 'visibilities' table, the info() method in the
785       current module will return a hashref like:
786
787               {
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
817       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
822   How do I use schemas in Postgres?
823       You may need to do something like this:
824
825               $dbh -> do("set search_path to $ENV{DBI_SCHEMA}") if ($ENV{DBI_SCHEMA});
826
827       $ENV{DBI_SCHEMA} can be a comma-separated list, as in:
828
829               $dbh -> do("set search_path to my_schema, public");
830
831       See DBD::Pg for details.
832
833   See Also
834       DBIx::Admin::CreateTable.
835
836       DBIx::Admin::DSNManager.
837

Version Numbers

839       Version numbers < 1.00 represent development versions. From 1.00 up,
840       they are production versions.
841

Repository

843       <https://github.com/ronsavage/DBIx-Admin-TableInfo>
844

Support

846       Bugs should be reported via the CPAN bug tracker at
847
848       <https://github.com/ronsavage/DBIx-Admin-TableInfo/issues>
849

Author

851       "DBIx::Admin::TableInfo" was written by Ron Savage <ron@savage.net.au>
852       in 2004.
853
854       Home page: http://savage.net.au/index.html
855
857       Australian copyright (c) 2004, Ron Savage.
858
859               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
863
864
865
866perl v5.36.0                      2023-01-20         DBIx::Admin::TableInfo(3)
Impressum