NAME

Class::Phrasebook::SQL - Implements the Phrasebook pattern for SQL statements.

SYNOPSIS

  use Class::Phrasebook::SQL;
  my $sql = new Class::Phrasebook::SQL($log, "test.xml");
  $sql->load("Pg");
  $statement = $sql->get("INSERT_INTO_CONFIG_ROW", 
		       { id => 88,
			 parent => 77,
			 level => 5 });

DESCRIPTION

This class inherits from Class::Phrasebook and let us manage all the SQL code we have in a project, in one file. The is done by placing all the SQL statements as phrases in the XML file of the Class::Phrasebook. See Phrasebook for details about that file format.

METHODS

get(KEY [, REFERENCE_TO_ANONYMOUS_HASH ])

Will return the SQL statement that fits to the KEY. If a reference to anonymous has is sent, it will be used to define the parameters in the SQL statement.

For example, if the following statement is defined in the XML file: <statement name="INSERT_INTO_CONFIG_ROW"> insert into t_config (id, parent_id, level) values($id, $parent, $level) </statement> We usually will call get method to get this statement in the following way: $statement = $sql->get("INSERT_INTO_CONFIG_ROW", { id => 88, parent => 77, level => 5 });

Special case are the SQL update instructions. Most of the time, when we call update, we would like to update only part of the columns in a row. Yet, we usually prefer to avoid from writing all the possible update combinations. For example if we have the following update call:

update t_account set
                      login = '$login',
                      description = '$description', 
                      dates_id = $dates_id, 
                      groups = $groups,
                      owners = $owners
                                  where id = $id

We do not want to write special update for each case like:

update t_account set
                      owners = $owners
                                  where id = $id

or

update t_account set
                      login = '$login',
                      owners = $owners
                                  where id = $id

In order to solve this, the get method will delete the "set" lines of the update method where the were the parameter value is udefined. Because of that we should write the update statements were the pairs of <column name> = <parameter> are in separate lines from the rest of the statement. Note that the get method will also fix comma problems between the pairs (so if the last pair is deleted we will not have extra comma). The method returns the SQL statement, or undef if there is no SQL statement for the sent KEY.

escaped_quote ( STRING )

An access method to the data memeber ESCAPED_QUOTE. The default way to escape a quote is to have two quotes (''). This will work on Postgres and on MSQL. Yet, if this default is not working with your database of choice, you can change it by seting the ESCAPE_QUOTE data member using this method.

use_is_null( BOOLEAN )

Sometimes, when we have an argument in SQL statement, we will want to change the equal sign to 'is'. For example:

select * from my_table where my_id = $id

If $id is NULL, we sometimes want to have 'my_id is NULL'. We can have that by sending to this method 1. This will promis that where ever we have the pattern '= NULL' it will become 'is NULL'. The default is not to use the 'is' (thus 0).

save_statements_file_path ( [ FILE_PATH ] )

Access method to the SAVE_STATEMENTS_FILE_PATH data member. If this data member is set, for each call to the get method, the statement that is returned also will be appended to that file. This might be useful while debugging big projects - it will let the user have a full log of all the statemnets that were generated by the get method.

ENVIRONMENTS

PHRASEBOOK_SQL_DEBUG_PRINTS

If this environment is set to "COLOR", the get method will print the statements it gets, with some extra information in color screen output using ANSI escape sequences. If the environment is set to "HTML", the information will be printed in HTML format. If the environment is set to "TEXT" - the information will be printed as simple text. If the environment is not set, or empty - nothing will be printed. This feature comes to help debugging the SQL statements that we get from the object of this class.

PHRASEBOOK_SQL_SAVE_STATEMENTS_FILE_PATH

Another way to set the SAVE_STATEMENTS_FILE_PATH data member is by setting this environment variable.

AUTHOR

Rani Pinchuk, rani@cpan.org

COPYRIGHT

Copyright (c) 2001-2002 Ockham Technology N.V. & Rani Pinchuk. All rights reserved. This package is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

SEE ALSO

XML::Parser(3), Class::Phrasebook(3), Log::LogLite(3), Log::NullLogLite(3)