SQLUnit Include Files

Include files in SQLUnit refer to plain text files which contain a list of SQL or stored procedure calls that need to be run before or after a test suite is run.

The include file is parsed according to a set of fairly simple and hopefully intuitive rules. An include file consists of a collection of statements separated by delimiters.

The set of allowed delimiters are a free-standing slash ("/") character, a free-standing semicolon (";") character or a free-standing string "go" on its own line. In all cases other than when the SQL starts with a CREATE or REPLACE, a terminating semicolon (";") character is also supported.

The various kinds of statements supported in an include file are comments, CREATE and REPLACE statements, EXECUTE PROCEDURE calls, other SQL statements and shell calls. We will describe each of them in detail below.

Both single line and multi line SQL comments are supported. Single line SQL comments are those that begin with "--" on a new line. Multi-line comments begin with a "/*" and end with a "*/" followed by a newline. Comments are only for the benefit of the human author or user of the include file and is of no consequence to SQLUnit. Comments are discarded by the parser.

CREATE and REPLACE statements are typically used to define stored procedures, functions or triggers and can contain embedded semicolon characters at the end of the line before the definition is complete. These statements cannot be delimited with a terminating semicolon character because of ambiguity. Although CREATE and REPLACE can be used generically for DDL operations, making this rule only for stored procedures or functions would have led to a complexity in the parser grammar. It is also easier to remember about this restriction when applied to a generic set of statements rather than a very specific subset. CREATE and REPLACE statements can be and often will be multi-line calls.

EXECUTE PROCEDURE statements are those start with "EXEC ", "EXECUTE PROCEDURE ", or "{CALL ". They differ from regular SQL statements in that they need to be handled using a CallableStatement. The statement will be re-written to the form expected by the JDBC driver, ie {CALL procedurename()}, if it is not already specified in that form. The syntax rules for EXECUTE PROCEDURE statements are identical to that of regular SQL statements (described below), but SQLUnit will handle them as a CallableStatement rather than as a PreparedStatement. Any of the delimiters listed above will work with an EXECUTE PROCEDURE statement. Like CREATE and REPLACE statements, EXECUTE PROCEDURE statements can be multi-line.

Other SQL statements are those which do not fall into the two categories described above. Examples are SELECT, INSERT, UPDATE, DELETE, INDEX, DROP, etc. They are handled by SQLUnit using PreparedStatement. Any of the listed delimiters will work for these statements. Like the CREATE/REPLACE and EXECUTE PROCEDURE statements, these can also be multi-line. Some examples are listed below:

-- Simple select
select * from foo where bar = 1
/
-- A multi-line example
UPDATE foo 
  SET bar = 1
  WHERE bar = 2
/
      

In all the types of SQL calls described above, case does not matter, unless it is enforced by the database engine, for example, case sensitive table and column names in Sybase.

Shell calls are calls to the operating system. They are preceded with a bang ("!") character on a new line. Since Java will spawn a separate process for the shell command, it has no knowledge of the user's environment. It is generally recommended to call a shell script or batch file with the full path name and source the environment within the script. In case you wish to use the shell metacharacters such as the pipe ("|") or redirection metacharacters, it must be called within the context of the shell, with the actual command in parenthesis. As before, the full path to the command processor will need to be supplied. Multi-line commands can be specified using backslash ("\") character. Any of the delimiters listed above can be used. Here are some examples of a shell call.

-- Example of a shell script call
!/path/to/shell/script.sh
/
-- Example of a call using the shell
!/bin/sh -c (rm -rf /tmp/*.test; ls -l | wc -l >/tmp/xyz)
/
-- Example of a call using the Windows NT command processor
!COMMAND.COM /C (DEL C:\TMP\*.TST)
/
-- Example of a call using Windows XP command processor
!CMD.EXE /C (DEL C:\TMP\*.TST)
/
      

In all cases, the parser makes no attempt to validate or parse the SQL or shell script. If SQLUnit encounters an error while running the statement, it will return the stack trace to the caller to aid in debugging.

The BNF grammar (generated by jjdoc) for the Include File Parser is shown below. The comments come from inline comments in the parser grammar file, and are terse versions of the description above.

DOCUMENT START
NON-TERMINALS
// An include file is a collection of statements separated by one of the
// available delimiter characters.
	StatementCollection	:=	( CallableSQLStatement | ShellCallStatement | OtherSQLStatement | MultilineSQLStatement | CommentStatement )* ( <EOF> )?
// Comment statements can either be single line or multiline.
	CommentStatement	:=	( SingleLineCommentStatement | MultiLineCommentStatement )
// Single line comments begin with a "--" string on a newline and are 
// terminated by newline.
	SingleLineCommentStatement	:=	( <START_SINGLELINE_COMMENT> ( <SINGLELINE_COMMENT_CHAR> )* <END_SINGLELINE_COMMENT> )
// Multi line comments begin with "/*" on a newline and can contain
// multiple lines. They are terminated with a "*/" string at the end
// of the line.
	MultiLineCommentStatement	:=	( <START_MULTILINE_COMMENT> ( <MULTILINE_COMMENT_CHAR> )* <END_MULTILINE_COMMENT> )
// Multi line SQL statements are those that have a semi-colon (";") character
// terminating lines within the multi-line SQL. The trailing semi-colon 
// delimiter is not allowed for this type of statement.
	MultilineSQLStatement	:=	( <START_MULTILINE> ( <MULTILINE_CHAR> )* <MULTILINE_DELIMITER> )
// A Callable SQL statement is one which starts with "EXEC(UTE PROCEDURE) "
// or "{CALL ". The execute procedure call will be rewritten to a CALL form,
// since that is the form all JDBC drivers understand. The handler will 
// use a java.sql.CallableStatement to handle this call.
	CallableSQLStatement	:=	( ( <START_EXEC_PROC_CALL> | <START_EXEC_PROC_OTHER> ) ( <EXEC_PROC_CHAR> | <EXEC_PROC_OPEN_PAREN> | <EXEC_PROC_CLOSE_PAREN> | <EXEC_PROC_CONT_CHAR> )* <EXEC_PROC_DELIMITER> )
// A Shell call statement represents a call to an operating system command
// or external utility. This may be useful for cleaning up temporary files
// or making SQL calls through the database client of your choice. A statement
// can span multiple lines using the backslash (\) continuation character. 
// The parser treats the shell call as a system process and will start up a 
// Java Runtime object for it. Note that the runtime will have no knowledge
// of the environment settings, so you need to provide full path names. Special
// features of the shell such as pipes and redirectors will also not work 
// unless you wrap the command in the shell processor, like so:
//   !/bin/sh -c (command) for Unix/Linux
//   !command.com /c (command) for Windows XP
//   !cmd.exe /c (command) for Windows NT
// The parenthesis are required. The recommended procedure would be to 
// simply invoke a shell script which does what you want as well as source
// environment variables that you may need.
	ShellCallStatement	:=	( <START_SHELL_CALL> ( <SHELL_CALL_CHAR> | <SHELL_CONT_CHAR> | <OPEN_PAREN> | <CLOSE_PAREN> )* <SHELL_CALL_DELIMITER> )
// The Other SQL statement represents an SQL statement that is not a CREATE
// or REPLACE call (MultilineSQLStatement) or an EXEC(UTE PROCEDURE) call
// (CallableSQLStatement). SQL statements such as SELECT, INSERT, UPDATE,
// DELETE, RENAME, DROP, etc, fall into this category. The SQL statement
// can contain newlines which will be transformed by the parser into white
// space. The parser will use a java.sql.PreparedStatement to process this
// statement.
	OtherSQLStatement	:=	( ( <START_OTHER_SQL_CHAR> | <OTHER_SQL_CHAR> )* <OTHER_SQL_DELIMITER> )

DOCUMENT END