ACS Java Database API Requirements

by Luke Pond, Rafael Schloming and Joseph Bank

I. Introduction

This document outlines the requirements for a Java version of the ACS Database API.

II. Vision Statement

By providing the capability to reuse common paradigms from ACS/TCL directly in Java, this system leverages our existing investment in the ACS, makes the tasks of porting and learning the new environment easier for developers, and does not interfere with any of the tenets or advantages of the J2EE standard platform. We believe that although there is great demand for an intermediate layer to improve the ease of use of JDBC, there is no other system with the power or simplicity of our TCL database api.

III. System/Application Overview

Every tcl command in the ACS/TCL database api has a corresponding implementation as a precompiler command for ACS Java. We'll discuss the requirements and assumptions for the original database api, and how well we expect them to hold up in this new environment. To a large extent however we expect the original documentation to remain accurate for the new system.

IV. Use-cases and User-scenarios

A programmer familiar with writing code to do database access in ACS/TCL will not have to learn very much about JDBC to become productive in ACS Java. A programmer familiar with JDBC will find that by using the database api they will become more productive thanks to the higher-level syntax it provides.

V. Competitive Analysis

1. SQLJ
   SQLJ is very similar in intent to the ACS Database API. It defines a mini-language for database interaction, which may be embedded in java code. However, it has the following drawbacks from our perspective:
   • Not supported by every database vendor
   • Not intended for use in the JSP environment
2. JSP Tag libraries
   In response to the difficulty of using JDBC directly, several vendors have released tag libraries for database access from JSPs. We did this ourselves in ACS 3.4 Java. However, the JSP tag extension mechanism places severe limits on the expressiveness of the language you can create with it.
   1. Gefion InstantOnline
   2. Apache Jakarta taglib project
      
   3. Sun's proposed standard JSP tag library
3. Cold Fusion
   

VI. Related Links

• ACS Database API
• Precompiler Requirements

VII.A Functional Requirements

10.0 Database API Compatibility

20.0 Connection handling

30.0 Dynamic SQL

30.0.1 Safety of Dynamic Queries

Dynamically generated queries must be immune to "SQL Smuggling", where user inputs add clauses to a dynamically generated query.

30.0.2 Bind variables

When a command takes an argument that contains a SQL statement, the SQL code is examined for words beginning with ":". These are taken to be named bind variables. The bind variable values are found in one of two ways. If a "-bind" argument is used, the variable values are found in the AdSet data structure given with the argument. Otherwise, variable values are found by examining the environment for Java variables with the same name. The command automatically creates the SQL statement and binds the values it finds for the variables contained in the statement. Commands with this behavior are db_foreach, db_1row, db_0or1row, db_multirow, db_dml, db_string and db_sql.

30.0.3 Dynamic Clauses: Interpolated SQL fragments

In order to support dynamic SQL clauses, a new command "db_sql" has been provided. This command creates a Java variable which represents the fragment of a SQL statement along with the values of any bind variables it contains.

SQL fragments created with db_sql may be interpolated into other queries using the $fragment syntax, for example:

db_sql my_where_clause {
  where user_id = :my_user_id
}

db_1row users {
   select user_id, password from users 
   $my_where_clause
} 

40.0 Side effects: how results are made available

40.0.1 Support for automatic setting of result variables

The Java Database API must set Java variables with names corresponding to the columns to the values obtained from the database for each of the columns named in a query.

40.0.2 Support for automatic creation of result variables

To provide maximally concise code, the Java Database API should not require the user to declare the result variables from a query that has named columns in the query. These variables must only be bound within the scope of the command (i.e. within the body of db_foreach loop, or at the same scope as a db_1row command).

40.0.3 Support for full variable declarations

For full flexibility, the Java Database API should allow the user to use predeclared result variables of a limited set of types including at least java.lang.Object, java.lang.String, java.util.Data.

40.0.5 Availability of ResultSets

Commands that perform queries (db_foreach, db_1row, db_multirow) must create a variable of type ResultSet, with the name given in the command's first argument.

40.0.6 Expressions return values

Commands that may be used in expressions (db_string, db_list, db_list_of_lists) evaluate into java expressions. An end-of-statement semicolon ";" is not generated, therefore the programmer must use them within an assignment statement, for example:

String password = [db_string pw {
   select password from users where user_id=:user_id
}];

50.0 Transparent to users

60.0 Informative Debugging Information

VII.B Quality Requirements

10.0 Regression testing against Tcl Database API

20.0 Performance

VIII. Revision History