NAME

SQL::Generator - Generate SQL-statements with oo-perl

SYNOPSIS

  use SQL::Generator;

DESCRIPTION

With this module you can easily (and very flexible) generate/construct sql-statements. As a rookie, you are used to write a lot of sprintf`s every time i needed a statement (i.e.for DBI). Later you start writing your own functions for every statement and every sql-dialect (RDBMS use to have their own dialect extending the general SQL standard). This SQL::Generator module is an approach to have a flexible abstraction above the statement generation, which makes it easy to implement in your perl code. Its main purpose is to directly use perl variables/objects with SQL-like code.

CLASSES

SQL::Generator

USE

Carp

CLASS METHODS

close

Destructor.

dialect_path( $dialect )

Returns the perl 'use' module path of the dialect.

change_dialect( $dialect )

Loads commandset for the dialect. Afterwards SQL::Generator interprets its calls with this dialect.

Method

dump_history

INSTALL

Standard configuration, just type in your shell:

        perl Makefile.PL
        make
        make test
        make install

or if CPAN module is working, type in your shell:

        perl -MCPAN -e 'shell install SQL::Generator'

Win32 enviroment (which have Microsoft Developer Studio installed), type in your shell:

        perl Makefile.PL
        nmake
        nmake test
        nmake install

or using CPAN, type in your shell:

        perl -MCPAN -e "shell install SQL::Generator"

EXPORT

None.

METHODS

new( ... )

new is the class constructor. It accepts a hash list of following arguments:

LANG => (string) .i.e.: 'MYSQL'

LANG is optional; Following alternatives are possible:

MYSQL (default), ORACLE, SQL92

This is the generator-engine specifier. It tells the SQL::Generator which dialect of the SQL-LANGUAGE will be generated / produced / constructed. This distribution only supports a very limited subset of the MYSQL (version 3.22.30) sql command set. The transparent implementation invites everyone to extend/contribute to the engine (have a look at IMPLEMENTATION near the end of this document). You may also use 'ORACLE' or 'SQL92' as a parameter, but they simply represents a copy of the 'MYSQL' implementation and were added for motivating to extend the interface (of course not tested for real compability yet).

FILE => (string) .i.e.: '>filename.sql'

FILE is optional;

A filename for alternativly dumping the generated sql output to a file.This should be valid filename as used by IO::File open function. The parameter is directly forwared to this function, therefore the heading mode controllers are significant, but you should only use only output modes (">", ">>", ..) because SQL::Generator will only print to the handle.

PRE => (string) .i.e.: "$line)\t\t"

PRE is optional.

A string which will be concated before each generated sql command.

PRE() is also a method, which may be called anywhere in the lifetime of an SQL::Generator instance and will have an impact on further behaviour.

POST => (string) .i.e.: ';\n\n'

POST is optional.

A string which will be concated after each generated sql command.

POST() is also a method, which may be called anywhere in the lifetime of an SQL::Generator instance and will have an impact on further behaviour.

AUTOPRINT => (boolean) .i.e. 0

AUTOPRINT is 0 (false) per default.

Normally when an SQL-generating method is called, it returns the result as a string, which may printed or stored or whatever. If you want that the method is also echoing the command to STDOUT, so turn this switch on (1).

AUTOPRINT() is also a method, which may be called anywhere in the lifetime of an SQL::Generator instance and will have an impact on further behaviour.

prettyprint => (boolean) .i.e. 0

prettyprint is 0 (false) per default.

history => (boolean) .i.e. 0

history is 0 (false) per default.

historysize => (scalar) .i.e. 50 [lines]

historysize is 100 lines per default.

debug => (boolean) .i.e. 0

debug is 0 (false) per default.

If true (1), it turns some diagnostic printings on, but should be used from very advanced users only.

debug() ,debugOn() or debugOff are also methods, which may be called anywhere in the lifetime of an SQL::Generator instance and will have an impact on further behaviour.

changLang( 'LANGID' )

see new( LANG ) argument description above.

Changes the (current) SQL language module. It accepts following arguments:

LANGID (string) .i.e.: 'MYSQL'

LANG is not optional; Following alternatives in this dist are possible:

MYSQL (default), ORACLE, SQL92

It changes the generator-language module (it is simply another module loaded), which switches the translation to another SQL dialect which controlls how the output is generated / produced / constructed. Generally it looks for <LANGID>.pm in a subpath 'Generator/Lang/' of the distribution (have a look at IMPLEMENTATION section near the end of this document).

totext( RULE )

For advanced users only ! Interprets (current) command with a rule. It accepts following arguments:

RULE (a SQL::Generator::Command instance)

RULE is not optional;

It translates the perl function call (via AUTOLOAD) to an SQL function. For the construction of the SQL it uses an SQL::Generator::Command instance, which holds the information how to transform the function parameters to an SQL string.

see the SQL::Generator::Command pod for specific description.

EXAMPLE 1 (see test.pl in the dist-directory)

DESCRIPTION

A very simple example. It instanciates the generator and creates some example output, which could be simply piped to an mysql database for testing (be careful if tables/database name is existing. I guess not !).

CODE

my $table = 'sql_statement_construct_generator_table';

my $database = 'sql_statement_construct_generator_db';

my %types = ( row1 => 'VARCHAR(10) AUTO_INCREMENT PRIMARY KEY',

        row2 => 'INTEGER',

        row3 => 'VARCHAR(20)'
);

my %columns = ( row1 => '1', row2 => '2', row3 => '3' );

my %alias = ( row1 => 'Name', row2 => 'Age', row3 => 'SocialID' );

        my $sql = new SQL::Generator(

                LANG => 'MYSQL',

                post => ";\n",

                history => 1,

                autoprint => 0,

                prettyprint => 0

        ) or die 'constructor failed';

        $sql->CREATE( DATABASE => $database );

        $sql->USE( DATABASE => $database );

        $sql->CREATE( COLS => \%types, TABLE => $table );

        $sql->DESCRIBE( TABLE => $table );

        $sql->INSERT(

                COLS => [ keys %columns ],

                VALUES => [ values %columns ],

                INTO => $table

                );

        foreach (keys %columns) { $columns{$_}++ }

        $sql->INSERT( SET => \%columns , INTO => $table );

        foreach (keys %columns) { $columns{$_}++ }

        $sql->REPLACE(

                COLS => [ keys %columns ],

                VALUES => [ values %columns ],

                INTO => $table,

                );

        $sql->SELECT( ROWS => '*', FROM => $table );

        $sql->SELECT( ROWS => [ keys %types ], FROM => $table );

        $sql->SELECT(

                ROWS =>  \%alias,

                FROM => $table,

                WHERE => 'row1 = 1 AND row3 = 3'

                );

        $sql->DROP( TABLE => $table );

        $sql->DROP( DATABASE => $database );

        # evocate an errormsg

        print "\nDumping sql script:\n\n";

        for( $sql->HISTORY() )
        {
                printf "%s", $_;
        }

OUTPUT OF EXAMPLE 1

CREATE DATABASE sql_statement_construct_db; USE sql_statement_construct_db; CREATE TABLE sql_statement_construct_table ( row1 VARCHAR(10) AUTO_INCREMENT PRIMARY KEY, row2 INTEGER, row3 VARCHAR(20) ); DESCRIBE sql_statement_construct_table; INSERT INTO sql_statement_construct_table ( row1, row2, row3 ) VALUES( 1, 2, 3 ); INSERT INTO sql_statement_construct_table SET row1='2', row2='3', row3='4'; REPLACE INTO sql_statement_construct_table ( row1, row2, row3 ) VALUES( 3, 4, 5 ); SELECT * FROM sql_statement_construct_table; SELECT row1, row2, row3 FROM sql_statement_construct_table; SELECT row1 AS 'Name', row2 AS 'Age', row3 AS 'SocialID' FROM sql_statement_construct_table WHERE row1 = 1 AND row3 = 3; DROP TABLE sql_statement_construct_table; DROP DATABASE sql_statement_construct_db;

EXAMPLE 2

DESCRIPTION

This example is uses the perl AUTOLOAD/BEGIN/END features. With this script template, you are enabled to write very straightforward code, without even sensing the OO architecture of the SQL::Generator implementation. Therefore you can directly use "functions" instead of writing method syntax.

It looks like a new SQL script language with perl powerfeatures. It tastes like an "embedded SQL" script, but you can simply change the destination SQL database language with one parameter.

CODE

use strict; use vars qw($AUTOLOAD);

my $sql;

BEGIN { use SQL::Generator;

        $sql = new SQL::Generator(

                LANG => 'ORACLE',

                FILE => '>create_table.oraclesql',

                post => ";\n",

                autoprint => 1,

                ) or print 'object construction failed';
}

END { $sql->close(); }

sub AUTOLOAD { my $func = $AUTOLOAD;

        $func =~ s/.*:://;

        return if $func eq 'DESTROY';

        my %args = @_;

        $sql->$func( %args );
}

## PERL/SQL STARTS HERE ##

my $table = 'sql_statement_construct_generator_table';

my $database = 'sql_statement_construct_generator_db';

my %types = ( row1 => 'VARCHAR(10) AUTO_INCREMENT PRIMARY KEY',

        row2 => 'INTEGER',

        row3 => 'VARCHAR(20)'
);

my %columns = ( row1 => '1', row2 => '2', row3 => '3' );

my %alias = ( row1 => 'Name', row2 => 'Age', row3 => 'SocialID' );

        CREATE( DATABASE => $database );

        USE( DATABASE => $database );

        CREATE( TABLE => $table, COLS => \%types );

        DESCRIBE( TABLE => $table );

        INSERT( SET => \%columns , INTO => $table );

        DROP( TABLE => $table );

        DROP( DATABASE => $database );

OUTPUT OF EXAMPLE 2

IMPLEMENTATION

see the SQL::Generator::Lang::MYSQL pod documentation for how to easily extend the archive of generators sql-standard/non-standard languages.

SQL::Generator::Debugable Baseclass for symdumps, croaks and intelligent debugging. SQL::Generator::Argument Class that implements the translation on command argument level and also formats the perl function arguments to text. SQL::Generator::Command Class that holds the configuration of the arguments of the SQL-command versus perl-function. Object::ObjectList A list class for Command's (SQL-commands) which defines the language. SQL::Generator::Lang::* A subclass of CommandList which is dynamically used for SQL generation. Dictates the language conformance.

SUPPORT

By author. Ask comp.lang.perl.misc or comp.lang.perl.module if you have very general questions. Or try to consult a perl and SQL related mailinglist before.

If all this does not help, contact me under my email below.

AUTHOR

Murat Uenalan, muenalan@cpan.org

COPYRIGHT

    The SQL::Generator module is Copyright (c) 1998-2002 Murat Uenalan. Germany. All rights reserved.

    You may distribute under the terms of either the GNU General Public
    License or the Artistic License, as specified in the Perl README file.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

CLASSES

USE

CLASS METHODS

close

dialect_path( $dialect )

change_dialect( $dialect )

Method

dump_history

INSTALL

Standard configuration, just type in your shell:

or if CPAN module is working, type in your shell:

Win32 enviroment (which have Microsoft Developer Studio installed), type in your shell:

or using CPAN, type in your shell:

EXPORT

METHODS

new( ... )

changLang( 'LANGID' )

totext( RULE )

EXAMPLE 1 (see test.pl in the dist-directory)

DESCRIPTION

CODE

OUTPUT OF EXAMPLE 1

EXAMPLE 2

DESCRIPTION

CODE

OUTPUT OF EXAMPLE 2

IMPLEMENTATION

SUPPORT

AUTHOR

COPYRIGHT

SEE ALSO

Module Install Instructions