Make Your Program Write Code For You

I’m back to writing PLSQL and it’s pain as always. Recently I was dealing with the issue of synchronizing table data between 2 databases and needed to provide a comparison report about the values that do not match. Well, that’s easy: select rows locally and over the dblink and compare the data. But… what if there are 40 different tables to compare and hundreds of selection criteria to get the data? And no, I do not have time to type it all.

So I needed some sort of automation. Of course PLSQL provides something:

I can use one procedure to populate collections using the selection criteria;

another procedure can check the same primary keys exist in the local and remote collection;

and the last one can browse through a pair of collection rows to compare that all fields have the same values.

That sounds reasonable and short. But each collection should represent a row in a table (something like MyTable%rowtype), that means that collections for different tables will be completely different. So how to make it generic (especially in the case when all fields are to be compared)?

I did not found any elegant answer in PLSQL and typing code for all table variations seemed to be impossible. I had to look for a better solution and I decided to use code patterns and replace pieces that do not match. Thanks to StringTemplate the work turned to be really easy. StringTemplate allows separation of your code from the template code. The benefit here is that the template looks semantically the same as the result, so it is very easy to create, correct and maintain. In the same time the code stays clean and very simple. Though StringTemplate has a rich API, I basically mastered only a small piece of it in 5 minutes and that was far enough to do the entire job.

For those who are still interested, here is an example.

Example

Imagine we have a list of tables (Table1, Table2, Table3 for instance) in 2 databases connected over a db link; the tables can have any amount of fields with different names. Our goal is to compare all the values in matching tables between 2 databases, i.e. first we will check that all IDs from local database table are present in the remote table and then we will compare that the rows with the same ID have all other fields with the same values.

To make it shorter let’s imagine that the code populating data into Table1%rowtype (Table2%rowtype, Table3%rowtype etc) collections is already generated. So we will need the following templates (I’ve started by writing this code manually for 1 table to be sure about the template syntax):

Templates

In all the templates below $$ is used to distinguish a replaceable attribute.

compare.st – Top-level checking function:

———————————————————————

procedure $compare_procedure_name$($local_table$ $table_type$,
$remote_table$ $table_type$) is

rec_id integer;
column varchar2(30);
begin
for i in 1 .. $local_table$.count
loop
rec_id := $find_procedure_name$($remote_table$, $local_table$(i).id);
if rec_id < 0 then
log_error(‘Missing $entity_name$: ‘|| $local_table$(i).id);
error_count := error_count + 1;
else
if not $compare_function_name$($local_table$(i), $remote_table$(i), column) then
log_error(‘Different values observed in $entity_name$.’ || column || ‘:’ ||$local_table$(i).id);
error_count := error_count + 1;
end if;
end if;
end loop;
end;

find.st Function to locate a matching record in a collection

function $find_function_name$($generic_table$ $table_type$, $entity_id$ varchar2)
return integer
is
begin
for i in 1 .. $generic_table$.count
loop
if $generic_table$(i).id = $entity_id$ then
return i;
end if;
end loop;
return -1;
end $find_function_name$;

compare_row.st Function to compare a selected row

function $compare_function_name$(local_entities edh_core.$entity_name$%rowtype,
remote_entities edh_core.$entity_name$%rowtype,
column out varchar2) return boolean
is
begin
$compare_declarations$
return true;
end $compare_function_name$;

compare_field.st A piece of code to compare a single field value:

if local_entities.$field_name$ <> remote_entities.$field_name$ then
column := ‘$field_name$’;
return false;
end if;

Filling the templates

Now that the templates are ready, we will only need to apply them for all tables and fields and write to a file (I used a package template too, which you can easily create yourself).

General way to call a template:

// Templates are stored in templatesFolder
StringTemplateGroup group = new StringTemplateGroup(“GroupName”,
“templatesFolder”, DefaultTemplateLexer.class);

You can make group variable a class field as it will be used in all the template-handling procedures.

// compare is the name of template file without “.st” extension
StringTemplate procedure = group.getInstanceOf(“compare”);

Now we need to set all the attributes used in the template

// Compare procedure name can be generated based on table name and selection criteria: compare_table1_description, compare_table2_time etc
procedure.setAttribute(“compare_procedure_name “, “compare_” + tableName + “_” + criteria);
procedure.setAttribute(“local_table”, tableName + “_local”);
procedure.setAttribute(“table_type”, “t_” + tableName);
procedure.setAttribute(“remote_table”, tableName + “_remote”);
procedure.setAttribute(“find_procedure_name”,“compare_” + tableName + “_” + criteria);
procedure.setAttribute(“entity”, tableName + “_remote”);
// You can type the result or return it as the function result
System.out.println(procedure.toString());
return procedure.toString();

In an analogous way you can get the result from find.st template.

A bit more work is to use compare_row and compare_field templates, as compare_field template must be used inside compare_row as many times as there are fields in a row:

public String generateCompare() {
StringTemplate procedure = group.getInstanceOf(“compare_row”);
procedure.setAttribute(“compare_function_name”, getCompareProcedureName());
procedure.setAttribute(“entity_name”, tableName);
procedure.setAttribute(“compare_declarations”, getCompareTemplate(tableName));
System.out.println(procedure.toString());
return procedure.toString();
}

public String getCompareTemplate(String tableName) {
if (entityName == “Table1”) {
return getCompareTemplate(new String[]{“Table1Field1″, ” Table1Field2″, ” Table1Field3″});
} else if (entityName == “Table2”){
return getCompareTemplate(new String[]{“Table2Field1″, ” Table2Field2″});
} else if (

///………

}
return “”;
}

private String getCompareTemplate(String[] fields) {
String compares = “”;
for (int i = 0; i < fields.length; i++){
StringTemplate template = group.getInstanceOf(“compare_field”);
template.setAttribute(“field_name”, fields[i]);
compares += template.toString() + “\r\n”;
}
return compares;
}

Now instead of typing all these procedures for all the tables, you can just specify which table you are interested in, and then pass the columns array to getCompareTemplate.

In this example I do not show how to put all the procedures and functions together in a PLSQL package file and how to fill table rows collections. However, you will be able to add those simple templates yourself and write the result into *.pck file.

In general StringTemplate can save you lots of copy-pasting work and help to tame PLSQL, html, xml and other code.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: