Custom PRAGMA names support

[h8leet]

Is it possible to apply PRAGMA with custom name?

For example, I need apply PRAGMA plaintext_header_size = 32 which is available in SQlite3 from SQLite3MultipleCiphers project.

Existsing values of enum SQLiteConfig.Pragma doesn't contains PLAINTEXT_HEADER_SIZE.

The problem is that this PRAGMA is applied only when opening a database and its value cannot be changed later, so it is not possible to set it by a SQL query after establishing a connection. There are also other PRAGMA ​​in SQLite3MultipleCiphers that are not in sqlite-jdbc and they are not needed for plain SQLite3.

[gotson]

The problem is that this PRAGMA is applied only when opening a database and its value cannot be changed later, so it is not possible to set it by a SQL query after establishing a connection.

Could you clarify on that part ?

PRAGMA are always called using the PRAGMA instruction, and this requires a connection.

[gotson]

By the way, did you check https://github.com/Willena/sqlite-jdbc-crypt ?

[h8leet]

Could you clarify on that part ?

My bad, It means that PRAGMA plaintext_header_size must be executed before any SQL statements are executed. As I understand it, the trigger for creating a database with the plaintext_header_size parameter is the execution of any SQL, as is PRAGMAs cipher and key (https://utelle.github.io/SQLite3MultipleCiphers/docs/configuration/config_sql_pragmas/#key-handling).

By the way, did you check https://github.com/Willena/sqlite-jdbc-crypt ?

Yes, sqlite-jdbc-crypt has an extended set of PRAGMAs including plaintext_header_size. But at the moment the sqlite-jdbc-crypt is supported with a large delay due to the maintainer don't have a lot of spare time it currently (Willena#158 (comment))

Also after further analysis I think that sqlite-jdbc-crypt's additional PRAGMA won't help me. If Pragma#PASSWORD is used, the query "select 1 from sqlite_schema" is implicitly executed:

sqlite-jdbc/src/main/java/org/sqlite/SQLiteConfig.java

Line 208 in 4cd221a

stat.execute("select 1 from sqlite_schema");

So, if exclude the database connection check (key validity), it will be possible to install any PRAGMA using Statement#execute(String).

The problem is that this could break the logic of existing code for library users who rely on the built-in key check.

Based on this, my suggestions are:

1. Add the ability to use custom PRAGMAs, may be by additional system property extend the check of PRAGMAs in line

   sqlite-jdbc/src/main/java/org/sqlite/SQLiteConfig.java

   Line 214 in 4cd221a

   if (!pragmaParams.contains(key)) {

   
   For example if system property is not equals "true" checks that PRAGMA property is not removed from pragmaParams (Pragma.OPEN_MODE, Pragma.SHARED_CACHE etc.) but allow any other property from pragmaTable which added by the public SQLiteConfig(Properties) constructor or setPragma(String, String).

2. Move the applying of Pragma.PASSWORD key validation if Pragma.PASSWORD is provided to the end of the list of applying PRAGMAs.

[gotson]

Since https://github.com/Willena/sqlite-jdbc-crypt already supports the extension you want i would direct you there.