Developer's guide to the ACS Java 4.0 Database API

by Luke Pond (luke@arsdigita.com)

Introduction

In ACS Java 3.4, we used Sun's JSP tag extension mechanism to implement tags for a database api and ad_page_contract. Frustrated by the limitations of this mechanism, we decided to take a different approach for ACS Java 4.0, and created a compiler that expands macro invocations into java source code. This allows us to preserve the high-level syntax that was developed for the ACS/TCL database api and ad_page_contract. The results are best shown by example:
ACS Java 3.4ACS Java 4.0 
<ad:db_query id="insert_new_alert">
insert into bboard_email_alerts ( 
 user_id, topic_id, frequency, keywords
) 
values
( :user_id, :topic_id, :frequency, :keywords)
<ad:db_bind><%= user.getUserId() %></ad:db_bind>
<ad:db_bind><%= topic_id %></ad:db_bind>
<ad:db_bind><%= frequency %></ad:db_bind>
<ad:db_bind><%= db.bindObj(keywords) %></ad:db_bind>
</ad:db_query>

<%
   db.dml(insert_new_alert);
%>

<%
   db_dml insert_new_alert {
      insert into bboard_email_alerts (
        user_id, topic_id, frequency, keywords
      ) 
      values
      ( :user_id, :topic_id, :frequency, :keywords)
   }
%>
This is not an attempt to embed TCL in java - we have no plans to implement any superfluous TCL language features. It's only the familiar syntax that we're borrowing - the generated code is perfectly normal java/jdbc, and some of the problems that can arise in TCL, such as accidentally clobbering variables, show up as errors when the java code is compiled. For further motivation, see the competitive analysis in the requirements document. Along with Bill's implementation of the Arsdigita Templating System, this allows us to eliminate our use of custom JSP tags.

Using the macros

What macros can I use?

The currently implemented macros are listed in the ACS Java 4.0 project file /WEB-INF/db-api.grm. The most frequently used database api commands are all there, as well as ad_page_contract and ad_return_template.

What should I name my files?

To signify the presence of the macros in your code, we suggest the following naming convention: a JSP that contains macros should be given the extension .ajp, and an ordinary java source file with macros should be given the extension .acsj.

How does the macro parser work?

It looks through your code for a macro name appearing as the first symbol on a line, or following a square bracket "[" anywhere on the line. In the first case, we say the command has statement context. In the second case, it has expression context. Having met one of those two conditions, it attempts to parse a complete command using rules similar to TCL. The command's arguments are separated by spaces, unless they are quoted using curly braces. We don't support the use of double quoted strings containing newlines - a multiline argument must be delineated by curly braces. The arguments end when an unquoted newline is reached, or in the case of expression context, when the closing square bracket is reached.

If an argument is specified as being a code block (in the db-api.grm file), the parser recursively searches it for additional macros.

What are the valid switches for a macro?

In addition to one or more arguments, a macro may support the use of optional named arguments, specified as arguments that begin with a dash. The switches supported by each macro are listed separately in the db-api.grm file. The switches may appear anywhere in the argument list for a macro.

What's expression context good for?

A subset of the macros behave differently when they appear in expression context (surrounded by square brackets). Here's an example using db_string:
Valid macro usageGenerated code 
db_string next_seq {
  select my_sequence.nextval from dual
}
String next_seq = new SqlFragment("select my_sequence.nextval from dual",
new Object[] {}).oneValue().toString();

String next_seq = [db_string next_seq {
  select my_sequence.nextval from dual
}];

String next_seq =  new SqlFragment("select my_sequence.nextval from dual",
new Object[] {}).oneValue().toString();
Appearing on its own, in statement context, the db_string macro generates a String declaration, along with a semicolon to end the complete java statement. Appearing within a java statement, in expression context, it generates an expression whose value is the query result. You may choose either usage according to your needs. Macros that work this way are:

Interpolating SQL fragments

The macros provide a command new to the database api: db_sql. Use db_sql to create a clause which may be optionally interpolated into another SQL statement. For example: 
db_sql user_clause {
   user_id=:user_id
}
db_1row user_email {
   select email from users where $user_clause
}

Is there anything different about JSP's ?

The macros expand into java source code. So just make sure to only use macros inside of a <% ... %> escape, as you would if you were writing java code. The macros do not behave differently in a JSP than they do in an ordinary java source file.

Using the macro compiler

If you're writing .ajp pages, the macro translation happens automatically. The RequestProcessor servlet recognizes the .ajp extension when it resolves abstract URLs. If a corresponding .jsp file does not exist, or its timestamp is older than the .ajp, it invokes the compiler to produce the .jsp. You may encounter syntax errors at this step, which will contain the correct line number. However, it's more likely that you will need to resolve errors generated by the subsequent steps in the process, the JSP translation or servlet compilation. We're looking for ways to make it easier to find these errors in the original .ajp source file. 
.ajp -> .jsp -> .java -> .class

Should you use macros in java source code?

It's your choice. If you want to avoid the extra preprocessing step, the database api runtime object, com.arsdigita.db.SqlFragment, is available to use directly. However, the ability to properly format your SQL queries without needing to declare them as java Strings is a wonderful thing. Also, the macros give you the capability to easily build up SQL statements from multiple clauses containing bind variables. If you need to do this, we strongly recommend use of the macros. 
.acsj -> .java -> .class

How to compile a .acsj file

The name of the compiler is com.arsdigita.db.MacroTranslator; it can be run standalone to translate one or more .acsj files specified on the command line. The following script works well during development to make compilation a single-step process. (It's in the project; see WEB-INF/src/acsj.pl). 
#!/usr/bin/perl
$args2 = $args1 = join(@ARGV);
$args2 =~ s/\.acsj/\.java/;
`java com.arsdigita.db.MacroTranslator \$ACS_JAVA/WEB-INF/db-api.grm $args1`
`javac $args2`
Whenever you do an update of the acs-java-4 project from CVS, you should rebuild all of your java library sources using the following procedure:
  1. Go to the acs-java-4/WEB-INF/src directory.
  2. Run the make-clean.sh script. This will remove all .class files and translate all .acsj files to .java. To run this script, you must have the ACS_JAVA environment variable set to the root of your acs-java-4 checkout.
  3. Run the make-build.pl script. This will generate the file Build.java.
  4. Finally, compile the Build.java file with your java compiler.
luke@arsdigita.com