view release on metacpan or  search on metacpan

lib/DBD/Informix/Metadata.pm  view on Meta::CPAN

=head1 DESCRIPTION

This document describes the metadata methods for DBD::Informix
(Informix Database Driver for Perl DBI Version 2018.1031 (2018-10-31)).

Note that you would seldom actually use this package directly (despite
the synopsis above); the methods you would use are defined in the
DBD::Informix::db package (in the Informix.pm file).

=head2 The ix_tables function

You can call two methods using the DBI func() to get
at some basic Informix metadata relatively conveniently.

    @list = $dbh->func('_tables');
    @list = $dbh->func('user', '_tables');
    @list = $dbh->func('base', '_tables');
    @list = $dbh->func('user', 'base', '_tables');
    @list = $dbh->func('system', '_tables');
    @list = $dbh->func('view', '_tables');
    @list = $dbh->func('synonym', '_tables');

Alternatively, the direct calling mechanism is:

    @list = DBD::Informix::Metadata::ix_tables($dbh, @attrs);

The lists of tables are all qualified as "owner".tablename (with
metacharacter mapping done by ix_map_tablename described below), and you
can use them in SQL statements without fear that the table is not
present in the database (unless someone deletes it behind your back).
The leading arguments qualify the list of names returned.
Private synonyms are reported for just the current user.

Note that the names are returned in the format suitable for use in SQL statements;
this is distinct from the format the values are stored in the database.

=head2 The ix_columns function

The normal mechanism for calling this function is:

    @list = $dbh->func('_columns');
    @list = $dbh->func(@tables, '_columns');

Alternatively, the direct calling mechanism is:

    @list = DBD::Informix::Metadata::ix_columns($dbh, @tables);

The lists are each references to an array of values corresponding to the
owner name, table name, column number, column name, basic data type
(ix_ColType value--see below), and data length (ix_ColLength--see
below).
If no tables are listed, all columns in the database are listed.
This can be quite slow because handling synonyms properly requires a
UNION operation.

Further, although the '_tables' method reports the names of remote
synonyms, the '_columns' method does not expand them (mainly because it
is very hard to do properly).
See the examples in t/t55mdata.t for how to use these methods.
Exercise for the reader: Extend '_columns' to get reports on the columns
in remote synonyms, including relocated remote synonyms where the
original referenced site now forwards the name to a third site!

Note that the return values from this are in the same format as found in
the system catalogues and are not necessary suitable for directly
embedding in an SQL statement.

=head2 The ix_map_tablename method

This method is used internally to map the owner, table (and optionally
column) names from the format found in the system catalog to a format
that can be used in an SQL statement.
The difference is important - and can be substantial.

    $sql = ix_map_tablename($owner, $table [, $column]);

The owner name will be enclosed in double quotes; if it contains double
quotes, those will be doubled up as required by SQL.
The table name will only be enclosed in double quotes if it is not a
valid C identifier (meaning, it starts with an alphabetic character or
underscore, and continues with alphanumeric characters or underscores).
If it is enclosed in double quotes, any embedded double quotes are
doubled up.
If provided, the column name is given the same treatment as the table
name.

=head2 The ix_table_info method

    $sth = ix_table_info($dbh);

The ix_table_info method returns a statement handle for the given
database handle that will return a description of all the tables in the
database.
The description of the data complies with a very old version the
requirements of the DBI table_info method.

Expect this function to change!

=head2 The ix_delimit_identifier method

    $id = ix_delimit_identifier($id);

The ix_delimit_identifier encloses the given argument in double quotes,
and doubles up any embedded double quotes, and returns the delimited
identifier.
This converts a value from the system catalog into a form that can be
used in an SQL statement provided $ENV{DELIMIDENT} is set.

=head2 The ix_cond_delimit_identifier method

    $id = ix_cond_delimit_identifier($id);

The ix_cond_delimit_identifier calls ix_delimit_identifier on the
argument if the argument is not a valid C identifier.
This converts a value from the system catalog into a form that can be
used in an SQL statement if $ENV{DELIMIDENT} is set, but avoids
converting values that do not have to be delimited to maximize the
chance of it working when $ENV{DELIMIDENT{ is not set.

=head2 The ix_undelimit_identifier method