NAME

GT::SQL - A database independent perl interface

SYNOPSIS

    use GT::SQL;

    my $db      = GT::SQL->new('/path/to/def');
    my $table   = $db->table('Links');
    my $editor  = $db->editor('Links');
    my $creator = $db->creator('NewTable');
    my $html    = $db->html('Links', new CGI);

DESCRIPTION

GT::SQL is a perl database abstraction layer to relational databases, providing a native Perl interface rather than a query-based interface.

A GT::SQL object provides the interface to the entire database by providing objects that are able to perform the work needed.

Creating a new GT::SQL object

There are two ways to get a GT::SQL object. First, you can simply provide the path to the def file directory where GT::SQL stores all it's information:

    $db = GT::SQL->new('/path/to/def');

or you can pass in a hash or hash ref and specify options:

    $db = GT::SQL->new(
        def_path => '/path/to/def',
        cache    => 1,
        debug    => 1,
        subclass => 1
    );

You must specify def_path. Setting cache => 1 will result in all table and relation objects being cached, which provides a performance improvement in any situation where the same table or relation is used again.

Specifying subclass => 0 or subclass => 1 will enable or disable the ability to subclass any of the objects GT::SQL creates. The default value is 1, and should not normally be changed.

GT::SQL has significant amounts of debugging output that can be enabled by specifying a value of 1 to the debug option. Larger values can be specified for more detailed debugging output, however a level of 1 is almost always more than sufficient. The accepted values are as follows:

Level 0: This is the default, no debugging information is printed to stderr. All errors can be obtained in $GT::SQL::error.
Level 1: All queries will be displayed to stderr. This is the recommended value if query debugging is desired.
Level 2: Same as level 1, but includes more detailed information. Also, when calling query_stack you get a stack trace on what generated each query. Not recommended except when working directly on GT::SQL.
Level 3: Very detailed debug logs including creation and destruction of objects. query_stack generates a javascript page with query, stack trace, and data dump of arguments, but can be extremely large. Not recommended except for debugging GT::SQL internals.

Pass in a def path

    $obj = GT::SQL->new('/path/to/def/directory');

This method of calling new is also supported, however has the drawback that none of the above options can be provided.

Getting Connected

GT::SQL loads the database connection info from database.def which is located in the defs directory.

To create this file, you call set_connect() as follows:

    $obj->set_connect({
        driver     => 'mysql',
        host       => 'localhost',
        port       => 3243,
        database   => 'databasename',
        login      => 'username',
        password   => 'password',
        PREFIX     => 'prefix_'
    });

This will test the database information, and save it to the def file. All future connections will automatically use this connection information.

Not all of the arguments in this hash are necessary; some have reasonable defaults for the connection.

driver: This needs to be the driver that is being used for the connection. The default for this is mysql. Driver names are case-insensitive. Available drivers are:
host: This will specify the host to connect to. The default, which is acceptable for most installations, is localhost.
port: This is the port on which to connect to the SQL server. The default for this is to allow the DBI driver to choose the default, which is almost always the appropriate choice.
database: This is the database name to use on the SQL server. This is required to connect. For MSSQL, this is the Data Source name.
PREFIX: This specifies a prefix to use for table names. See the Table Prefixes section below for more information.

Supported Objects

The following objects can be obtained through a GT::SQL object:

Table/Relation

To get a table or relation object for working with SQL tables, you should call:

    my $table = $db->table('table_name');

or for a table join:

    my $relation = $db->table('table_name', 'other_table');

See the GT::SQL::Table manpage for more information on how to use a table object.

Creator

To create new tables, you need to use a creator. You can get one by calling:

    my $creator = $db->creator('new_table');

where new_table is the name of the table you wish to create. See the GT::SQL::Creator manpage for more information on how to use a creator object.

Editor

To edit existing tables (i.e. add/drop/change columns, add/drop indexes, etc.) you need an editor object:

    my $editor = $db->editor('existing_table');

where existing_table is the name of the table you wish the modify. See the GT::SQL::Editor manpage for more information on how to use an editor object.

HTML

To get an html object for generating forms and html output, you need to pass in the table/relation object you want to work with, and a cgi object:

    my $html = $db->html($table, $cgi);

The html object uses information found in CGI to set values, etc. See the GT::SQL::Display::HTML manpage for more information on how to use a html object.

Table Prefixes

GT::SQL supports the concept of table prefixes. If you specify a prefix using the accessor, it is saved in the database.def file and will be used in all future calls to table(), editor() and creator().

To set a prefix:

    $db->prefix("foo");

to get the current prefix:

    my $prefix = $db->prefix;

What this will do is transparently prepend foo to the beginning of every table name. This means anywhere you access the table bar, the actual table stored on the SQL server will be foobar. Note that the prefix should not be included when getting table/creator/editor/etc. objects - the prefix is handled completely transparently to all public GT::SQL functionality.

Query Stack

To display a list of all raw SQL queries sent to the database you can use:

    my @queries = $db->query_stack;

or to have them formatted try

    print $db->query_stack_disp;

which will join them up, displayed nicely. This is also available as a class method:

    print GT::SQL->query_stack_disp;

COPYRIGHT

VERSION

Revision: $Id: SQL.pm,v 1.111 2005/04/14 20:22:37 alex Exp $