DatabaseAuto — register database tables for use with Interchange automatically
The directive automatically registers database tables found for use with Interchange.
This only works for SQL databases, and the parameters provided to
DatabaseAuto
are used to establish the connection to the database.
For example, a setting of
DatabaseAuto dbi:mysql:test_foundation interch pass
would do the equivalent of:
NoImportTABLENAME
DatabaseTABLENAME
TABLENAME
.txt dbi:mysql:test_foundation
DatabaseTABLENAME
USERinterch
DatabaseTABLENAME
PASSpass
for every table (and not view, by default) found in the database test_foundation.
Any additional space-separated arguments are passed to DBI's
table_info
method as the catalog
, schema
,
name
, and type
(optionally quoted in
shell syntax). Since order is significant, you could use ''
to skip values.
Currently, the catalog
argument is not used by any
database driver, but it
may be in the future. The other options (schema
,
name
and type
) are database-specific;
see the DBI manual, appropriate DBD manual and
the section called “EXAMPLES” for details and examples.
If the Perl module DBIx::DBSchema
was found,
it would also dump the specification needed to re-create the table structures
(just like the CREATE_SQL
parameter to Database
):
DatabaseTABLENAME
CREATE_SQL CREATE TABLETABLENAME ( ...)
This information is available in $Vend:Cfg
(global
context) or $Config
(catalog context) and it is
trivial to dump it to the screen or file.
See the section called “EXAMPLES”.
Example: Standard DatabaseAuto definition, a standalone example
First, make sure MySQL is installed, then connect to it as root (using the mysql command-line tool), and issue the following commands to both create a sample database and allow access to it:
create database interchange; use interchange; create table table1(code INT, price INT, description VARCHAR(60)); create table table2(code INT, price INT, description VARCHAR(60)); create table table3(code INT, price INT, description VARCHAR(60)); create table table4(code INT, price INT, description VARCHAR(60)); grant all privileges on interchange.* to interchange@localhost identified by 'pass';
Add the following to your catalog.cfg
:
DatabaseAuto dbi:mysql:interchange interchange pass
At Interchange restart or catalog reconfiguration time, Interchange should discover the tables in the database and make them available for use.
Example: Getting a dump of $Vend::Cfg and extracting table creation commands
If DBIx::DBSchema
Perl module is installed, Interchange
is able to produce the commands that you need to run to recreate the structure
of the database tables. This can be useful for backup purposes and remote
copying, or just to get the layout, tune it to your needs, insert literally in
catalog.cfg
and finally turn off DatabaseAuto
.
So first of all, implement the above base example.
Then make sure the DBIx::DBSchema
module is
installed by making Interchange fail to configure the catalog if it's missing —
add the following to your catalog.cfg
:
Require module DBIx::DBSchema
Then, to dump CREATE_TABLE
sequence for say,
table1, add the following to a separate Interchange page:
Creation sequence: <pre> [perl] $Config->{Database}->{table1}->{CREATE_SQL}; [/perl] </pre>
Alternatively, to dump the complete $Vend::Cfg
structure,
you could create a "cfgdump
" usertag (see
dump-vend-structure
).
Then you could simply restart Interchange and call
<pre>[cfgdump]</pre>
in any Interchange page, and search
for table1 in the output.
Example: Specifying PostgreSQL schema
The following setting for PostgreSQL would make Interchange only see the tables
in the public
schema of a database, and not in
any other schemas.
DatabaseAuto dbi:Pg:dbname=DBNAME USERNAME PASSWORD '' public
Example: Making Interchange recognize views
DatabaseAuto dbi:Pg:dbname=DBNAME USERNAME PASSWORD '' public '' VIEW
(The VIEW
argument should be passed as shown
— literally and in uppercase).
Example: Automatically configure all tables from an existing PostgreSQL database
Variable SQLDSN dbi:Pg:dbname=DATABASE_NAME DatabaseAutoIgnore ^sql_ DatabaseAuto __SQLDSN__ NoImportExternal Yes
For more about Interchange and databases, and supported formats, see database glossary entry.
The schema
argument to this directive can be specified when
you want to avoid using the DatabaseAutoIgnore
directive (it's drawback
is that it could easily ignore more tables than you really intended).
Also be aware that it is possible to make some confusing configuration mistakes
here, if the schema you specify is not in the database user's
SEARCH_PATH
session variable,
or comes after some other schema that has tables with the same names.
Interchange 5.9.0:
Source: lib/Vend/Config.pm
Line 4429 (context shows lines 4429-4440)
sub parse_dbauto { my ($var, $value) = @_; return '' unless $value; my @inc = Vend::Table::DBI::auto_config($value); my %noed; for(@inc) { my ($t, $thing) = @$_; parse_boolean('NoImport', $t) unless $noed{$t}++; parse_database('Database', "$t $thing"); } return 1; }