Additional compile time symbols

[utelle]

We should discuss a few additional compile time symbols:

Symbol	Description
SQLITE_TEMP_STORE=2	Temporary data will be held in memory (1)
SQLITE_SECURE_DELETE=1	Deleted database content will be overwritten with zeros (1)
SQLITE_USE_URI=1	Support URI parameters (2)
SQLITE_ENABLE_EXTFUNC=1	Additional user-defined SQL functions (3)
SQLITE_DQS=0	Do not support double-quoted string literals (4)

1. Symbols SQLITE_TEMP_STORE and SQLITE_SECURE_DELETE are important to increase data security/confidentiality. Especially the first option is important, because temporary database data are not encrypted and should therefore be kept in memory.
2. SQLITE_USE_URI allows to specify cipher configuration parameters in URI database file names, instead of using PRAGMA statements.
3. SQLite has support for additional math functions nowadays, but that was not alwways the case. SQLite3 Multiple Ciphers allows to enable an additional extension with math and string functions (see https://github.com/utelle/SQLite3MultipleCiphers/issues/22 for details).
4. Double-quoted strings should be used for identifiers only, not for string literals. This option disables support for double-quoted string literals.

[rogerbinns]

Secure delete and memory temp store compilation options, tests, and doc added.

SQLITE_USE_URI is off by default because that is what SQLite does. It is however trivial to change the config, or to supply URI open flag. Added example to the README.

SQLITE_ENABLE_EXTFUNC is one of yours. The standard apsw build for pypi has the flag --enable-all-extensions which does

   "fts4", "fts3", "fts3_parenthesis", "rtree", "stat4", "fts5", "rbu", "geopoly",
                "math_functions"

I'm having a look at the others mentioned.

SQLITE_DQS is left at its default value to keep backwards compatibility. APSW has a bestpractice module which is encouraged and the second thing done in the tour after printing versions.

[rogerbinns]

My opinion is that the extra functions listed in https://github.com/utelle/SQLite3MultipleCiphers/issues/22 are not worth enabling by default for the following reasons:

• they could unintentionally clash with existing developer code
• they do things that are hard/annoying in C, but trivial one liners in Python, and in most cases would be done in Python code not as SQL functions
• Python developers would expect the Python standard library numeric methods be used
• the string functions aren't unicode aware (need ICU to be correct)
• the standard window functions would be preferable to the aggregate functions
• it creates a dependency on sqlite3 multiple ciphers - ie you can't run the same code using standard sqlite3
• it is unexpected out of scope when using a library that encrypts files
• they aren't mentioned in the documentation

My recommendation therefore is to leave them off, and see if anyone notices.

[utelle]

Secure delete and memory temp store compilation options, tests, and doc added.

Thanks.

SQLITE_USE_URI is off by default because that is what SQLite does. It is however trivial to change the config, or to supply URI open flag. Added example to the README.

As long as the user can easily use URI parameters, I'm fine with the default setting.

SQLITE_ENABLE_EXTFUNC is one of yours.

My component had this extension enabled for a long time. Long before the math functions were added officially to SQLite. However, I understand your reasoning. APSW users are used to the current settings, and the additional functions included in the extension are not so important in a Python environment.

If someone really wants to enable additional extensions, he/she can do so by building a separate APSW module or by using loadable extensions.

So, I'm fine with not enabling this extension.

SQLITE_DQS is left at its default value to keep backwards compatibility.

For SQLite3 Multiple Ciphers I decided differently, because wrongly using double-quoted string literals can cause quite some grieve. But I'm fine with SQLite's default setting.

[rogerbinns]

Everything in this is completed, documented, and tested.. Closing.