DatabaseAuto

SYNOPSIS

DSN username password [ catalog [ schema [ name [ type ] ] ] ]

DESCRIPTION

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:

NoImport TABLENAME
Database TABLENAME TABLENAME.txt dbi:mysql:test_foundation
Database TABLENAME USER interch
Database TABLENAME PASS pass

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):

Database  TABLENAME CREATE_SQL   CREATE TABLE TABLENAME ( ...)

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”.

DIRECTIVE TYPE AND DEFAULT VALUE

Catalog directive

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

NOTES

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.

AVAILABILITY

DatabaseAuto is available in Interchange versions:

4.6.0-5.9.0 (git-head)

SOURCE

Interchange 5.9.0:

Source: lib/Vend/Config.pm
Line 592

['DatabaseAuto',   'dbauto',          ''],

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;
}

AUTHORS

Mike Heins

Name