protected void buildSQLStatements() {
        insertFileSQL = "insert into "
                + schemaObjectPrefix + "FSENTRY "
                + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_DATA, "
                + "FSENTRY_LASTMOD, FSENTRY_LENGTH) "
                + "values (?, ?, ?, ?, ?)";

        insertFolderSQL = "insert into "
                + schemaObjectPrefix + "FSENTRY "
                + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_LASTMOD, FSENTRY_LENGTH) "
                + "values (?, nvl(?, ' '), ?, null)";

        updateDataSQL = "update "
                + schemaObjectPrefix + "FSENTRY "
                + "set FSENTRY_DATA = ?, FSENTRY_LASTMOD = ?, FSENTRY_LENGTH = ? "
                + "where FSENTRY_PATH = ? and FSENTRY_NAME = ? "
                + "and FSENTRY_LENGTH is not null";

        updateLastModifiedSQL = "update "
                + schemaObjectPrefix + "FSENTRY set FSENTRY_LASTMOD = ? "
                + "where FSENTRY_PATH = ? and FSENTRY_NAME = ? "
                + "and FSENTRY_LENGTH is not null";

        selectExistSQL = "select 1 from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = nvl(?, ' ')";

        selectFileExistSQL = "select 1 from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null";

        selectFolderExistSQL = "select 1 from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = nvl(?, ' ') and FSENTRY_LENGTH is null";

        selectFileNamesSQL = "select FSENTRY_NAME from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_LENGTH is not null";

        selectFolderNamesSQL = "select FSENTRY_NAME from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME != ' ' "
                + "and FSENTRY_LENGTH is null";

        selectFileAndFolderNamesSQL = "select FSENTRY_NAME from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME != ' '";

        selectChildCountSQL = "select count(FSENTRY_NAME) from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ?  "
                + "and FSENTRY_NAME != ' '";

        selectDataSQL = "select nvl(FSENTRY_DATA, empty_blob()) from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null";

        selectLastModifiedSQL = "select FSENTRY_LASTMOD from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = nvl(?, ' ')";

        selectLengthSQL = "select nvl(FSENTRY_LENGTH, 0) from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null";

        deleteFileSQL = "delete from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null";

        deleteFolderSQL = "delete from "
                + schemaObjectPrefix + "FSENTRY where "
                + "(FSENTRY_PATH = ? and FSENTRY_NAME = nvl(?, ' ') and FSENTRY_LENGTH is null) "
                + "or (FSENTRY_PATH = ?) "
                + "or (FSENTRY_PATH like ?) ";

        copyFileSQL = "insert into "
                + schemaObjectPrefix + "FSENTRY "
                + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_DATA, "
                + "FSENTRY_LASTMOD, FSENTRY_LENGTH) "
                + "select ?, ?, FSENTRY_DATA, "
                + "FSENTRY_LASTMOD, FSENTRY_LENGTH from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null";

        copyFilesSQL = "insert into "
                + schemaObjectPrefix + "FSENTRY "
                + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_DATA, "
                + "FSENTRY_LASTMOD, FSENTRY_LENGTH) "
                + "select ?, FSENTRY_NAME, FSENTRY_DATA, "
                + "FSENTRY_LASTMOD, FSENTRY_LENGTH from "
                + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? "
                + "and FSENTRY_LENGTH is not null";
    }
protected void buildSQLStatements ( ) { insertFileSQL = "insert into " + schemaObjectPrefix + "FSENTRY " + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_DATA, " + "FSENTRY_LASTMOD, FSENTRY_LENGTH) " + "values (?, ?, ?, ?, ?)" ; insertFolderSQL = "insert into " + schemaObjectPrefix + "FSENTRY " + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_LASTMOD, FSENTRY_LENGTH) " + "values (?, nvl(?, ' '), ?, null)" ; updateDataSQL = "update " + schemaObjectPrefix + "FSENTRY " + "set FSENTRY_DATA = ?, FSENTRY_LASTMOD = ?, FSENTRY_LENGTH = ? " + "where FSENTRY_PATH = ? and FSENTRY_NAME = ? " + "and FSENTRY_LENGTH is not null" ; updateLastModifiedSQL = "update " + schemaObjectPrefix + "FSENTRY set FSENTRY_LASTMOD = ? " + "where FSENTRY_PATH = ? and FSENTRY_NAME = ? " + "and FSENTRY_LENGTH is not null" ; selectExistSQL = "select 1 from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = nvl(?, ' ')" ; selectFileExistSQL = "select 1 from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null" ; selectFolderExistSQL = "select 1 from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = nvl(?, ' ') and FSENTRY_LENGTH is null" ; selectFileNamesSQL = "select FSENTRY_NAME from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_LENGTH is not null" ; selectFolderNamesSQL = "select FSENTRY_NAME from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME != ' ' " + "and FSENTRY_LENGTH is null" ; selectFileAndFolderNamesSQL = "select FSENTRY_NAME from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME != ' '" ; selectChildCountSQL = "select count(FSENTRY_NAME) from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ?  " + "and FSENTRY_NAME != ' '" ; selectDataSQL = "select nvl(FSENTRY_DATA, empty_blob()) from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null" ; selectLastModifiedSQL = "select FSENTRY_LASTMOD from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = nvl(?, ' ')" ; selectLengthSQL = "select nvl(FSENTRY_LENGTH, 0) from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null" ; deleteFileSQL = "delete from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null" ; deleteFolderSQL = "delete from " + schemaObjectPrefix + "FSENTRY where " + "(FSENTRY_PATH = ? and FSENTRY_NAME = nvl(?, ' ') and FSENTRY_LENGTH is null) " + "or (FSENTRY_PATH = ?) " + "or (FSENTRY_PATH like ?) " ; copyFileSQL = "insert into " + schemaObjectPrefix + "FSENTRY " + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_DATA, " + "FSENTRY_LASTMOD, FSENTRY_LENGTH) " + "select ?, ?, FSENTRY_DATA, " + "FSENTRY_LASTMOD, FSENTRY_LENGTH from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_NAME = ? and FSENTRY_LENGTH is not null" ; copyFilesSQL = "insert into " + schemaObjectPrefix + "FSENTRY " + "(FSENTRY_PATH, FSENTRY_NAME, FSENTRY_DATA, " + "FSENTRY_LASTMOD, FSENTRY_LENGTH) " + "select ?, FSENTRY_NAME, FSENTRY_DATA, " + "FSENTRY_LASTMOD, FSENTRY_LENGTH from " + schemaObjectPrefix + "FSENTRY where FSENTRY_PATH = ? " + "and FSENTRY_LENGTH is not null" ; }
Builds the SQL statements
<p>
Since Oracle treats emtpy strings and BLOBs as null values the SQL
statements had to be adapated accordingly. The following changes were
necessary:
<ul>
<li>The distinction between file and folder entries is based on
FSENTRY_LENGTH being null/not null rather than FSENTRY_DATA being
null/not null because FSENTRY_DATA of a 0-length (i.e. empty) file is
null in Oracle.</li>
<li>Folder entries: Since the root folder has an empty name (which would
be null in Oracle), an empty name is automatically converted and treated
as " ".</li>
</ul>