Building the code for the PerlSQL generator

Embedding SQL with generators

8.4 A CASE STUDY : P ERL SQL

8.4.4 Building the code for the PerlSQL generator

You’re now ready to write the underlying code of our generator. This code reads in the PerlSQL and uses templates to create the Perl for the database access code. It then uses regular expressions to replace the SQL segments in the PerlSQL code with the gener-ated Perl code. The result is a completed Perl implementation that is then stored in the output file. Listing 8.1 contains the main code for the generator.

require "FlexibleSQL"

require "erb/erb"

require "ftools"

$database_handle = "$dbh" # The database handle Perl variable name

$next_sth = 0 # The statement handle index

$sth_prefix = "$ps_sth" # The statement handle Perl variable prefix

$template_path = "templates/" # The directory for the templates

$output_path = "output/" # The directory for the output PerlSQL generator

Listing 8.1 psqlgen.rb

A CASESTUDY: PERLSQL 165

def run_template( template, sql, type ) template = File.new( "#{$template_path}#{template}.template" ).read erb = ERb.new( template )

flex = FlexibleSQL.new( sql, type ) varHash = { "sql" => flex.perl_sql(), "binds" => flex.perl_binds(),

return run_template( "select_into", sql, "select" ) end

def tag_foreach( sql )

return run_template( "foreach", sql, "select" ) end

def tag_function( sql )

return run_template( "function", sql, "function" ) end

def tag_procedure( sql )

return run_template( "procedure", sql, "procedure" ) end

def tag_insert( sql )

return run_template( "insert", sql, "insert" ) end

def tag_update( sql )

return run_template( "update", sql, "update" ) end

print "You must specify a psql file name as the first argument\n"

exit

in_text.gsub!( /<option\s+name=\"([^"]*)\"\s+value=\"([^"]*)\"\s*>/i ) {

q

This function runs the ERb template with the specified SQL from the tag. The return value is the result of the template.

w

Each tag type has its own helper function. The name of each function is tag_<tag-name>. So the function that handles the select tag is ^tag_select, and so on.

e

This regular expression pattern handles tags that both open and close (e.g., <select>

…</select>).

r

After you have parsed the tags that both open and close, it is time to read the stand-alone tags (e.g., <select …>).

Looks for tags

A CASESTUDY: PERLSQL 167 The generator uses FlexibleSQL to handle the SQL statements in the PerlSQL class. The class creates the Perl code required for the SQL string, the bind operations, and the arguments to the DBI ^execute method. Listing 8.2 shows the code for the FlexibleSQL class.

class FlexibleSQL

@bound_variables.keys.each() { |item|

str += "#{$sth_prefix}#{$next_sth}->bind_param"

str += "( \"#{item}\", #{@bound_variables[item]} );\n";

end

A CASESTUDY: PERLSQL 169

q

_TheFlexibleSQL class wraps an SQL string that contains special markup. The spe-cial markup specifies where variables go, or where values are to be read from, in the return stream.

w

_{Using the}perl_sql method on the object returns the SQL that should be sent to the DBI ^prepare statement.

e

_Theperl_binds method returns a string that has Perl code in it to handle all of the required bind calls that bind variables to returned data.

r

_Theperl_mapped method returns a string that contains the arguments given to the DBI ^execute method.

t

This is the main method of the class. It parses the SQL and creates the Perl SQL, bind-ings, and mappings.

y

_The<<variable>> syntax indicates that the variable specified in the current scope should be inserted in this position in the SQL stream. The insertion is accomplished using bind variables so there is no problem with quoted strings or special characters.

Here is an example of the use of the <<…>> syntax:

SELECT * FROM user WHERE id=<<$id>>

u

_The<$ [sql_field]([perl_variable]) $> syntax tells FlexibleSQL that you want the data returned for the field stored in a particular Perl variable. Here is an example:

SELECT <$ first_name($first_name) $> FROM user WHERE id=<< $id >>

In this case, you are telling FlexibleSQL that the field name is ^first_name and that you want the value of the ^first_name field returned from the query to go into the $first_name Perl variable.

i

_The_**…** syntax is used specifically by ^insert and ^update statements. It speci-fies the array of SQL field names and Perl variable names that are to be inserted or updated. Here’s an example of this type of statement:

UPDATE user SET ** first_name($first_name), last_name($last_name) ** WHERE id=<<$id>>

o

This code handles the placement of the SQL variables and their bindings, depending on whether this is an ^update or ^insert statement.

A template file is available for each of the operands supported by the PerlSQL gen-erator (e.g., ^select, ^insert, ^update, select_into). This template file is used to create the Perl code that implements the ^select tag, as shown here:

eval {

my <%= $statement_handle %> = <%= $database_handle %>>prepare( "<%= varH ash['sql'] %>" );

<%= varHash['binds'] %><%= $statement_handle %>>execute();

my $ps_out = ();

while( my $ps_row_ref = <%= $statement_handle %>>fetchrow_hashref() ) { push @$ps_out, $ps_row_ref;

}

$ps_row_ref;

}

The rest of the templates are available in the code package associated with this book (www.codegeneration.net/cgia).

8.4.5 Performing system tests

You can unit-test the PerlSQL generator by running the generator on a number of test files and then storing the output of those files as known goods after they have been inspected. After the generator is modified, you can run the test system again and com-pare the new output against the known good output.

To implement the system test, use the simple system test framework (described in appendix B). The definition file for the test system is shown here:

<out>output/test1.pl</out>

</test>

</test>

</test>

</test>

</test>

</test>

</test>

</test>

</test>

</ut>

SUMMARY 171

In document Code Generation in action (Page 193-200)