1DBIx::Admin::TableInfo(U3s)er Contributed Perl DocumentatDiBoInx::Admin::TableInfo(3)
2
3
4
6 DBIx::Admin::TableInfo - A wrapper for all of table_info(),
7 column_info(), *_key_info()
8
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
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
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
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
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
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
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
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
848 <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
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.38.0 2023-07-20 DBIx::Admin::TableInfo(3)