Issue #82 wontfix

sqlite-jdbc cannot parse trigger

Anonymous created an issue

sqlite-jdbc 3.7.15-M1 fails in parsing a valid trigger. The following SQL (which I have also attached) is sufficient for reproducing the bug:

CREATE TABLE [foo]  (
    [foo_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    [name] TEXT,
    [value] TEXT
);

CREATE TABLE [history] (
    [time] DATETIME NOT NULL,
    [operation] TEXT NOT NULL,
    [tableName] TEXT NOT NULL,
    [oldName] TEXT,
    [oldValue] TEXT);

CREATE TRIGGER [FOO_DELETE]
AFTER DELETE
ON [foo]
BEGIN
    INSERT INTO HISTORY 
    (TIME, OPERATION, TABLENAME, OLDNAME, OLDVALUE)
    VALUES (CURRENT_TIMESTAMP, 'Delete', 'foo', OLD.NAME, OLD.VALUE);
END;

The following excerpt of pom.xml shows how the error can be reproduced from Maven by placing the above SQL in "sqlite-jdbc-trigger-bug.sql" and executing "mvn package":

<build>
    <plugins>
        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>sql-maven-plugin</artifactId>
            <version>1.5</version>
            <dependencies>
                <dependency>
                    <groupId>org.xerial</groupId>
                    <artifactId>sqlite-jdbc</artifactId>
                    <version>3.7.15-M1</version>
                </dependency>
            </dependencies>
            <configuration>
                <driver>org.sqlite.JDBC</driver>
                <escapeProcessing>false</escapeProcessing>
                <forceMojoExecution>true</forceMojoExecution>
            </configuration>
            <executions>
                <execution>
                    <id>create-db</id>
                    <phase>package</phase>
                    <goals>
                        <goal>execute</goal>
                    </goals>
                    <configuration>
                        <url>jdbc:sqlite:/${project.build.directory}/sqlite-jdbc-trigger-bug.db</url>
                        <srcFiles>
                            <srcFile>${basedir}/sqlite-jdbc-trigger-bug.sql</srcFile>
                        </srcFiles>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
<build>

Maven 3 produces the a stack trace including:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error)

This ")" appears to indicate the last parenthesis before "END".

This in my opinion is a blocker bug that needs high priority, as it represents the inability to parse valid SQL.

Comments (3)

  1. Garret Wilson

    It turns out this is a shortcoming of the sql-maven-plugin, not of sqlite-jdbc. According to the FAQ, the sql-maven-plugin SQL parser can't handle parsing triggers that have a delimiter (;) embedded inside the statement.

    The workaround is to tell sql-maven-plugin to only interpret a character as the delimiter if it appears on a line by itself. Then one has to go through and put all delimiters on separate lines, except for those inside triggers. I have confirmed that this works with the sample SQL attached to this bug. Therefore this is not a bug with sqlite-jdbc, but with sql-maven-plugin, and this bug can be closed.

  2. Log in to comment