Repository: xerial/sqlite-jdbc
Pull request #527 (2020-08-03 21:20:20)

Added limit handling
There are now new pragmas that makes the user able to set SQLite limits.
There parameters are:
  - limit_length: The maximum size of any string or BLOB or table row, in bytes.
  - limit_sql_length: The maximum length of an SQL statement, in bytes.
  - limit_column: The maximum number of columns in a table definition or in the result set of a SELECT or the maximum number of columns in an index or in an ORDER BY or GROUP BY clause.
  - limit_expr_depth: The maximum depth of the parse tree on any expression.
  - limit_compound_select: The maximum number of terms in a compound SELECT statement.
  - limit_vdbe_op: The maximum number of instructions in a virtual machine program used to implement an SQL statement. If sqlite3_prepare_v2() or the equivalent tries to allocate space for more than this many opcodes in a single prepared statement, an SQLITE_NOMEM error is returned.
  - limit_function_arg: The maximum number of arguments on a function.
  - limit_attached: The maximum number of attached databases.
  - limit_like_pattern_length: The maximum length of the pattern argument to the LIKE or GLOB operators.
  - limit_variable_number: The maximum index number of any parameter in an SQL statement.
  - limit_trigger_depth: The maximum depth of recursion for triggers.
  - limit_worker_threads: The maximum number of auxiliary worker threads that a single prepared statement may start.


Patch:
diff --git a/pom.xml b/pom.xml
index c2311a37..d6c29022 100644
--- a/pom.xml
+++ b/pom.xml
@@ -91,8 +91,8 @@
         <artifactId>maven-compiler-plugin</artifactId>
         <version>3.1</version>
         <configuration>
-          <source>1.6</source>
-          <target>1.6</target>
+          <source>1.8</source>
+          <target>1.8</target>
         </configuration>
       </plugin>
 
diff --git a/src/main/java/org/sqlite/SQLiteConfig.java b/src/main/java/org/sqlite/SQLiteConfig.java
index a874d306..3076ed27 100755
--- a/src/main/java/org/sqlite/SQLiteConfig.java
+++ b/src/main/java/org/sqlite/SQLiteConfig.java
@@ -113,6 +113,57 @@ public class SQLiteConfig
             pragmaParams.add(each.pragmaName);
         }
 
+        if(conn instanceof SQLiteConnection) {
+            if(pragmaTable.containsKey(Pragma.LIMIT_ATTACHED.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_ATTACHED, getInteger(Pragma.LIMIT_ATTACHED, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_COLUMN.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_COLUMN, getInteger(Pragma.LIMIT_COLUMN, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_COMPOUND_SELECT.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_COMPOUND_SELECT, getInteger(Pragma.LIMIT_COMPOUND_SELECT, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_EXPR_DEPTH.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_EXPR_DEPTH, getInteger(Pragma.LIMIT_EXPR_DEPTH, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_FUNCTION_ARG.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_FUNCTION_ARG, getInteger(Pragma.LIMIT_FUNCTION_ARG, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_LENGTH.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_LENGTH, getInteger(Pragma.LIMIT_LENGTH, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_LIKE_PATTERN_LENGTH.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_LIKE_PATTERN_LENGTH, getInteger(Pragma.LIMIT_LIKE_PATTERN_LENGTH, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_SQL_LENGTH.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_SQL_LENGTH, getInteger(Pragma.LIMIT_SQL_LENGTH, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_TRIGGER_DEPTH.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_TRIGGER_DEPTH, getInteger(Pragma.LIMIT_TRIGGER_DEPTH, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_VARIABLE_NUMBER.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_VARIABLE_NUMBER, getInteger(Pragma.LIMIT_VARIABLE_NUMBER, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_VDBE_OP.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_VDBE_OP, getInteger(Pragma.LIMIT_VDBE_OP, "-1"));
+            }
+
+            if(pragmaTable.containsKey(Pragma.LIMIT_WORKER_THREADS.pragmaName)) {
+                ((SQLiteConnection) conn).setLimit(SQLiteLimits.SQLITE_LIMIT_WORKER_THREADS, getInteger(Pragma.LIMIT_WORKER_THREADS, "-1"));
+            }
+
+        }
+
         pragmaParams.remove(Pragma.OPEN_MODE.pragmaName);
         pragmaParams.remove(Pragma.SHARED_CACHE.pragmaName);
         pragmaParams.remove(Pragma.LOAD_EXTENSION.pragmaName);
@@ -121,6 +172,19 @@ public class SQLiteConfig
         pragmaParams.remove(Pragma.DATE_STRING_FORMAT.pragmaName);
         pragmaParams.remove(Pragma.PASSWORD.pragmaName);
         pragmaParams.remove(Pragma.HEXKEY_MODE.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_ATTACHED.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_COLUMN.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_COMPOUND_SELECT.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_EXPR_DEPTH.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_FUNCTION_ARG.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_LENGTH.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_LIKE_PATTERN_LENGTH.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_SQL_LENGTH.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_TRIGGER_DEPTH.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_VARIABLE_NUMBER.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_VDBE_OP.pragmaName);
+        pragmaParams.remove(Pragma.LIMIT_WORKER_THREADS.pragmaName);
+
 
         Statement stat = conn.createStatement();
         try {
@@ -189,6 +253,16 @@ public class SQLiteConfig
         return Boolean.parseBoolean(pragmaTable.getProperty(pragma.pragmaName, defaultValue));
     }
 
+    /**
+     * Retrives a pragma integer value.
+     * @param pragma The pragma.
+     * @param defaultValue The default value.
+     * @return The value of the pragma or defaultValue.
+     */
+    private int getInteger(Pragma pragma, String defaultValue) {
+        return Integer.parseInt(pragmaTable.getProperty(pragma.pragmaName, defaultValue));
+    }
+
     /**
      * Checks if the shared cache option is turned on.
      * @return True if turned on; false otherwise.
@@ -277,7 +351,7 @@ public class SQLiteConfig
         CASE_SENSITIVE_LIKE("case_sensitive_like", OnOff),
         COUNT_CHANGES("count_changes", OnOff),
         DEFAULT_CACHE_SIZE("default_cache_size"),
-	DEFER_FOREIGN_KEYS("defer_foreign_keys", OnOff),
+	    DEFER_FOREIGN_KEYS("defer_foreign_keys", OnOff),
         EMPTY_RESULT_CALLBACKS("empty_result_callback", OnOff),
         ENCODING("encoding", toStringArray(Encoding.values())),
         FOREIGN_KEYS("foreign_keys", OnOff),
@@ -300,7 +374,21 @@ public class SQLiteConfig
         TEMP_STORE_DIRECTORY("temp_store_directory"),
         USER_VERSION("user_version"),
         APPLICATION_ID("application_id"),
-        
+
+        // Limits
+        LIMIT_LENGTH("limit_length", "The maximum size of any string or BLOB or table row, in bytes.", null),
+        LIMIT_SQL_LENGTH("limit_sql_length", "The maximum length of an SQL statement, in bytes.", null),
+        LIMIT_COLUMN("limit_column", "The maximum number of columns in a table definition or in the result set of a SELECT or the maximum number of columns in an index or in an ORDER BY or GROUP BY clause.", null),
+        LIMIT_EXPR_DEPTH("limit_expr_depth", "The maximum depth of the parse tree on any expression.", null),
+        LIMIT_COMPOUND_SELECT("limit_compound_select", "The maximum number of terms in a compound SELECT statement.", null),
+        LIMIT_VDBE_OP("limit_vdbe_op", "The maximum number of instructions in a virtual machine program used to implement an SQL statement. If sqlite3_prepare_v2() or the equivalent tries to allocate space for more than this many opcodes in a single prepared statement, an SQLITE_NOMEM error is returned.", null),
+        LIMIT_FUNCTION_ARG("limit_function_arg", "The maximum number of arguments on a function.", null),
+        LIMIT_ATTACHED("limit_attached", "The maximum number of attached databases.", null),
+        LIMIT_LIKE_PATTERN_LENGTH("limit_like_pattern_length", "The maximum length of the pattern argument to the LIKE or GLOB operators.", null),
+        LIMIT_VARIABLE_NUMBER("limit_variable_number", "The maximum index number of any parameter in an SQL statement.", null),
+        LIMIT_TRIGGER_DEPTH("limit_trigger_depth", "The maximum depth of recursion for triggers.", null),
+        LIMIT_WORKER_THREADS("limit_worker_threads", "The maximum number of auxiliary worker threads that a single prepared statement may start.", null),
+
         // Others
         TRANSACTION_MODE("transaction_mode", toStringArray(TransactionMode.values())),
         DATE_PRECISION("date_precision", "\"seconds\": Read and store integer dates as seconds from the Unix Epoch (SQLite standard).\n\"milliseconds\": (DEFAULT) Read and store integer dates as milliseconds from the Unix Epoch (Java standard).", toStringArray(DatePrecision.values())),
diff --git a/src/main/java/org/sqlite/SQLiteConnection.java b/src/main/java/org/sqlite/SQLiteConnection.java
index 4053dea5..d5d78b86 100644
--- a/src/main/java/org/sqlite/SQLiteConnection.java
+++ b/src/main/java/org/sqlite/SQLiteConnection.java
@@ -354,6 +354,14 @@ public abstract class SQLiteConnection
         db.busy_timeout(timeoutMillis);
     }
 
+    public void setLimit(SQLiteLimits limit, int value) throws SQLException {
+        db.limit(limit.getId(), value);
+    }
+
+    public void getLimit(SQLiteLimits limit) throws SQLException {
+        db.limit(limit.getId(), -1);
+    }
+
     @Override
     public boolean isClosed() throws SQLException
     {
diff --git a/src/main/java/org/sqlite/SQLiteLimits.java b/src/main/java/org/sqlite/SQLiteLimits.java
new file mode 100644
index 00000000..84d37d96
--- /dev/null
+++ b/src/main/java/org/sqlite/SQLiteLimits.java
@@ -0,0 +1,27 @@
+package org.sqlite;
+
+public enum SQLiteLimits {
+
+    SQLITE_LIMIT_LENGTH(0),
+    SQLITE_LIMIT_SQL_LENGTH(1),
+    SQLITE_LIMIT_COLUMN(2),
+    SQLITE_LIMIT_EXPR_DEPTH(3),
+    SQLITE_LIMIT_COMPOUND_SELECT(4),
+    SQLITE_LIMIT_VDBE_OP(5),
+    SQLITE_LIMIT_FUNCTION_ARG(6),
+    SQLITE_LIMIT_ATTACHED(7),
+    SQLITE_LIMIT_LIKE_PATTERN_LENGTH(8),
+    SQLITE_LIMIT_VARIABLE_NUMBER(9),
+    SQLITE_LIMIT_TRIGGER_DEPTH(10),
+    SQLITE_LIMIT_WORKER_THREADS(11);
+
+    private final int id;
+
+    private SQLiteLimits(int id) {
+        this.id = id;
+    }
+
+    public int getId() {
+        return id;
+    }
+}
diff --git a/src/main/java/org/sqlite/core/CorePreparedStatement.java b/src/main/java/org/sqlite/core/CorePreparedStatement.java
index 4f8063fd..9447af08 100644
--- a/src/main/java/org/sqlite/core/CorePreparedStatement.java
+++ b/src/main/java/org/sqlite/core/CorePreparedStatement.java
@@ -51,14 +51,6 @@ public abstract class CorePreparedStatement extends JDBC4Statement
         batchPos = 0;
     }
 
-    /**
-     * @see org.sqlite.core.CoreStatement#finalize()
-     */
-    @Override
-    protected void finalize() throws SQLException {
-        close();
-    }
-
     /**
      * @see org.sqlite.jdbc3.JDBC3Statement#executeBatch()
      */
diff --git a/src/main/java/org/sqlite/core/DB.java b/src/main/java/org/sqlite/core/DB.java
index 7b2241e9..fba7f814 100644
--- a/src/main/java/org/sqlite/core/DB.java
+++ b/src/main/java/org/sqlite/core/DB.java
@@ -15,14 +15,7 @@
  */
 package org.sqlite.core;
 
-import org.sqlite.BusyHandler;
-import org.sqlite.Function;
-import org.sqlite.ProgressHandler;
-import org.sqlite.SQLiteCommitListener;
-import org.sqlite.SQLiteConfig;
-import org.sqlite.SQLiteErrorCode;
-import org.sqlite.SQLiteException;
-import org.sqlite.SQLiteUpdateListener;
+import org.sqlite.*;
 
 import java.sql.BatchUpdateException;
 import java.sql.SQLException;
@@ -695,6 +688,15 @@ public abstract class DB implements Codes
      */
     public abstract int restore(String dbName, String sourceFileName, ProgressObserver observer) throws SQLException;
 
+    /**
+     * @param id The id of the limit.
+     * @param value The new value of the limit.
+     * @return The prior value of the limit
+     * @throws SQLException
+     * @see <a href="https://www.sqlite.org/c3ref/limit.html">https://www.sqlite.org/c3ref/limit.html</a>
+     */
+    public abstract int limit(int id, int value) throws SQLException;
+
 
     public static interface ProgressObserver
     {
diff --git a/src/main/java/org/sqlite/core/NativeDB.c b/src/main/java/org/sqlite/core/NativeDB.c
index 87ba3d94..70060291 100644
--- a/src/main/java/org/sqlite/core/NativeDB.c
+++ b/src/main/java/org/sqlite/core/NativeDB.c
@@ -1328,6 +1328,21 @@ JNIEXPORT void JNICALL Java_org_sqlite_core_NativeDB_free_1functions(
     }
 }
 
+JNIEXPORT jint JNICALL Java_org_sqlite_core_NativeDB_limit(JNIEnv *env, jobject this, jint id, jint value)
+{
+    sqlite3* db;
+
+    db = gethandle(env, this);
+
+    if (!db)
+    {
+        throwex_db_closed(env);
+        return 0;
+    }
+
+    return sqlite3_limit(db, id, value);
+}
+
 // COMPOUND FUNCTIONS ///////////////////////////////////////////////
 
 JNIEXPORT jobjectArray JNICALL Java_org_sqlite_core_NativeDB_column_1metadata(
@@ -1564,7 +1579,7 @@ JNIEXPORT jint JNICALL Java_org_sqlite_core_NativeDB_restore(
 #else
   return SQLITE_INTERNAL;
 #endif
-} 
+}
 
 
 // Progress handler
diff --git a/src/main/java/org/sqlite/core/NativeDB.java b/src/main/java/org/sqlite/core/NativeDB.java
index 7026b8e6..3193390d 100644
--- a/src/main/java/org/sqlite/core/NativeDB.java
+++ b/src/main/java/org/sqlite/core/NativeDB.java
@@ -16,15 +16,11 @@
 
 package org.sqlite.core;
 
-import org.sqlite.BusyHandler;
+import org.sqlite.*;
+
 import java.io.UnsupportedEncodingException;
 import java.sql.SQLException;
 
-import org.sqlite.Function;
-import org.sqlite.ProgressHandler;
-import org.sqlite.SQLiteConfig;
-import org.sqlite.SQLiteJDBCLoader;
-
 /** This class provides a thin JNI layer over the SQLite3 C API. */
 public final class NativeDB extends DB
 {
@@ -431,6 +427,9 @@ public final class NativeDB extends DB
     @Override
     native synchronized void free_functions();
 
+    @Override
+    public native synchronized int limit(int id, int value) throws SQLException;
+
     /**
      * @see org.sqlite.core.DB#backup(java.lang.String, java.lang.String, org.sqlite.core.DB.ProgressObserver)
      */
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java b/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java
index e70df476..303aad47 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3ResultSet.java
@@ -156,14 +156,6 @@ public abstract class JDBC3ResultSet extends CoreResultSet {
         throw new SQLException("function not yet implemented for SQLite");
     }
 
-    /**
-     * @see java.lang.Object#finalize()
-     */
-    @Override
-    protected void finalize() throws SQLException {
-        close();
-    }
-
     /**
      * @see java.sql.ResultSet#getRow()
      */
diff --git a/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java b/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java
index ad8154e3..c6c2e711 100644
--- a/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java
+++ b/src/main/java/org/sqlite/jdbc3/JDBC3Statement.java
@@ -27,13 +27,6 @@ public abstract class JDBC3Statement extends CoreStatement {
         internalClose();
     }
 
-    /**
-     * @see java.lang.Object#finalize()
-     */
-    protected void finalize() throws SQLException {
-        close();
-    }
-
     /**
      * @see java.sql.Statement#execute(java.lang.String)
      */


Test patch:
diff --git a/src/test/java/org/sqlite/ConnectionTest.java b/src/test/java/org/sqlite/ConnectionTest.java
index d932696c..31599a57 100644
--- a/src/test/java/org/sqlite/ConnectionTest.java
+++ b/src/test/java/org/sqlite/ConnectionTest.java
@@ -340,6 +340,19 @@ public class ConnectionTest
          conn.close();
     }
 
+    @Test(expected = SQLException.class)
+    public void limits() throws Exception{
+        File testDB = copyToTemp("sample.db");
+
+        assertTrue(testDB.exists());
+        Connection conn = DriverManager.getConnection(String.format("jdbc:sqlite:%s?limit_attached=0", testDB));
+        Statement stat = conn.createStatement();
+
+        stat.executeUpdate("ATTACH DATABASE attach_test.db AS attachDb");
+
+        stat.close();
+    }
+
     @Test
     public void ignoreUnknownParametersInURI() throws Exception {
     	Connection conn = DriverManager.getConnection("jdbc:sqlite:file::memory:?cache=shared&foreign_keys=ON&debug=&invalid");