include tag - delimiter change breaks tests

[David Fishburn]

Using todays CVS it appears a change was introduced (not sure when) that breaks existing tests.

The include Tag
Description

The include tag is used to specify an external file that contains a list of SQL statements that should be executed together. The statements can be separated from one another by a free standing slash (/) or semi-colon character (;) on its own line, or by a line terminated by a semi-colon (;) character.

I am guessing this has recently changed to include:
"or by a line terminated by a semi-colon (;) character."

If you have a stored procedure that has statement delimiters of ;'s, it would look like this:

CREATE PROCEDURE add_dept(
    @dept_name varchar(64)
)
RESULT( dept_id integer )  
BEGIN   
    SET TEMPORARY OPTION ON_TSQL_ERROR='Stop';

    IF EXISTS( SELECT 1
                 FROM department
                WHERE dept_name = @dept_name ) THEN
        RAISERROR 20746 'add_dept: Department already exists';
    END IF;

    INSERT INTO department( dept_name, num_employees )
    VALUES (@dept_name, 0);

    IF (SQLCODE < 0) THEN
        RAISERROR 20746 'add_dept: Insert into Department failed';
    END IF;

    SELECT @@IDENTITY;
END
;

So you can see here, there are semi-colons everywhere.

This is recent change, now SQLUnit sends this to the database:
CREATE PROCEDURE add_dept(
    @dept_name varchar(64)
)
RESULT( dept_id integer )  
BEGIN   
    SET TEMPORARY OPTION ON_TSQL_ERROR='Stop';

Which is wrong, since it stops too early.

I can understand why this change may have been added, but if you are going to change something like this, it is best to allow the user to specify the delimiter.

    <include file="test/asa/schema.sql" delimiter="/" />

Or something, not sure how would define *any* semi-colon vs a semi-colon only on a blank line.  You could do it easily with a regex ("^;$"), but that might introduce other complexities.

As it stands now, I have no work arounds for this. 

Dave

[Sujit Pal]

Hi Dave,

The trailing semi-colon feature, as well as the capability to process stored procedures from the include file was introduced in version 4.2
based on a feature request from Satish Chitnis.

I have added a test in IncludeFileParserTest.java to reproduce the situation you mention. IncludeFileParserTest#testWithFile() reads parsertest.sql. Turning DEBUG to true will show the SQL being parsed on stdout. To verify this, change:
private static final boolean DEBUG = false;
to
private static final boolean DEBUG = true;
in the file test/java/IncludeFileParserTest.java in CVS (checked in today).

and run:
ant junit-test -Dtest.class=net.sourceforge.sqlunit.test.IncludeFileParserTest

It will show the SQLs being parsed out of the file (the second to last test is
the one which reads a file), and it shows:

    [junit] M:CREATE PROCEDURE add_dept(
    [junit]     @dept_name varchar(64)
    [junit] )
    [junit] RESULT( dept_id integer )
    [junit] BEGIN
    [junit]     SET TEMPORARY OPTION ON_TSQL_ERROR='Stop';
    [junit]     IF EXISTS( SELECT 1
    [junit]                  FROM department
    [junit]                 WHERE dept_name =  dept_name   THEN
    [junit]         RAISERROR 20746 'add_dept: Department already exists';
    [junit]     END IF;
    [junit]     INSERT INTO department( dept_name, num_employees )
    [junit]     VALUES (@dept_name, 0);
    [junit]     IF (SQLCODE < 0) THEN
    [junit]         RAISERROR 20746 'add_dept: Insert into Department failed';
    [junit]     END IF;
    [junit]     SELECT @@IDENTITY;
    [junit] END

which identifies this as a multi-line SQL statement which is then processed by the IncludeHandler. I have added debug calls to the IncludeHandler to
show what is being sent, look for the string "executeMultilineSQL" when you run your failing test against the database.

BTW, it uses a Statement object to pass the SQL to the database. Not sure from your post whether you looked at the database logs to determine that the SQL
passed in is terminated at the first semi-colon character it sees. In that case, it may be the implementation of the Statement object which may be to blame. AFAIK, (and I would appreciate someone on the forum who has oracle double checking this) multi-line SQL with Statement works in Oracle, which is what Satish was using, although I havent heard back from him about this officially.

The parser looks for any statement that starts with CREATE | REPLACE to decide if its a "multi-line" SQL statement, and if so, ignores trailing semi-colons. It will stop parsing only when it sees one of the other demiliters, ie "\n/\n", "\n;\n", or "\ngo\n".

Can you rerun the test with logging turned on and verify what is being sent from SQLUnit (from the logs mentioned above) and let me know what you find?

Thanks
Sujit

[David Fishburn]

Sujit:
The parser looks for any statement that starts with CREATE | REPLACE to decide if its a "multi-line" SQL statement, and if so, ignores trailing semi-colons. It will stop parsing only when it sees one of the other demiliters, ie "\n/\n", "\n;\n", or "\ngo\n".

Dave:
That will be a problem.

There are many multi-statement statements that can be run in the setup scripts.

In my case, have a look at:
-- $Source: /cvsroot/sqlunit/sqlunit/test/asa/add_dept.sql,v $

It does this:

IF EXISTS( SELECT 1
             FROM sys.sysprocedure sp KEY JOIN sys.sysuserperm sup
            WHERE sp.proc_name = 'add_dept'
              AND sup.user_name = user_name()                     ) THEN
    DROP PROCEDURE add_dept;
END IF
;
CREATE PROCEDURE add_dept(
    @dept_name varchar(64)
)
RESULT( dept_id integer )  
BEGIN   
...
END
;

So SQLUnit was having trouble with the first IF statement that is used to test for the table and drop it otherwise.

The other types of things I would do here is LOOPs that prepopulate the tables with data.

I liked the idea of specifying the end-statement delimited.  For example, in PLSQL you would often use a "/".

[Sujit Pal]

I see the problem, the SQLUnit parser does not consider the IF to be a multi-line although it does contain trailing semi-colons. Thanks for catching this, I will probably go with your idea of adding one of the three free standing delimiters in an additional attribute to the include tag.

-sujit

[David Fishburn]

I managed to work around it by changing this file:
D:\Programs\sqlunit-cvs\src\net\sourceforge\sqlunit\utils\IncludeFileParser.jj

Replacing line 70 to:
  | <TRAILING_DELIMITER: ("\n;\n" | "\r;\r" | "\r\n;\r\n")>
From:
  | <TRAILING_DELIMITER: (";\n" | ";\r" | ";\r\n")>

This is in case other people have the same issue until Sujit addresses it.

Dave

[Sujit Pal]

Hi Dave,

Thought about this some more, having a configurable delimiter will make the parser harder to maintain, since we are going to bypass the parser's parsing logic to cherry pick which of the delimiters are allowed, and if the right token is not found, having to back up and return control or throw an error. So I went in the other direction and decided to include the IF as the starting point of a multi-line statement. We may have to add more tokens as we find them...

I ran the JUnit test against all the ASA sql files and they parsed them out correctly. The test is:
test/java/IncludeFileParserTest#testParseAsaFiles()

The files changed for this is:
M src/net/sourceforge/sqlunit/utils/IncludeFileParser.jj
M test/java/IncludeFileParserTest.java

Just checked it in few minutes ago. BTW, you should probably use the :ext: method to get to the main repository so you will get the changes right away.

-sujit

[David Fishburn]

Just thinking about this.
Probably another good tag would be the BEGIN statement.

I often do something like this:

BEGIN
    DECLARE count INTEGER;
    SET count = 10;
    WHILE count < 40 LOOP
    INSERT INTO Client VALUES(
        'Client ' || count,
        'Client ' || count || ' Name' );
       
    // two policies for each client
    INSERT INTO Policy VALUES(
        count,
        'Client ' || count,
        'Agent ' || count / 10 );
    INSERT INTO Policy VALUES(
        count + 100,
        'Client ' || count,
        'Agent ' || count / 10 );
       
    SET count = count + 1;
    END LOOP;
   
END;
COMMIT;

[Sujit Pal]

Ok, will add BEGIN to the list as well.

-sujit

[Sujit Pal]

BEGIN is also in the list of starter keywords for multi-line SQL statements (embedded ;). Its in CVS now.