Hello, With 3.33 which are the right instructions to use SQLITE_ENABLE_UPDATE_DELETE_LIMIT? Is recompiling from sources still required or can this now be used through amalgamation and more specifically through the SEE supplied amalgamation replacements?

https://www.sqlite.org/compile.html#enable_update_delete_limit

https://www.sqlite.org/lang_delete.html#optional_limit_and_order_by_clauses

SQLite version 3.33.0 2020-08-09 18:02:03 with the Encryption (see)
Copyright 2016 Hipp, Wyrick & Company, Inc.
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma compile_options;
compile_options
CODEC=see
COMPILER=msvc-1926
DEFAULT_FOREIGN_KEYS
DEFAULT_WAL_SYNCHRONOUS=1
DEFAULT_WORKER_THREADS=2
DIRECT_OVERFLOW_READ
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS5
ENABLE_JSON1
ENABLE_MEMORY_MANAGEMENT
ENABLE_NULL_TRIM
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_SNAPSHOT
ENABLE_STAT4
ENABLE_STMTVTAB
ENABLE_STMT_SCANSTATUS
ENABLE_UNKNOWN_SQL_FUNCTION
ENABLE_UNLOCK_NOTIFY
>>ENABLE_UPDATE_DELETE_LIMIT<<
HAS_CODEC
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_EXPR_DEPTH=0
OMIT_DEPRECATED
OMIT_UTF16
THREADSAFE=2
USE_ALLOCA
sqlite> create table T(I integer);
sqlite> insert into T values (1), (2), (3);
sqlite> delete from T order by rowid limit 1;
Error: near "order": syntax error
sqlite>

A patch was added which allows the amalgamation to be build in such a way as it will work with or without SQLITE_ENABLE_UPDATE_DELETE_LIMIT when the amalgamation is compiled.

https://www.sqlite.org/src/info/1f96a29dd8654ee3

This would have first appeared in release code 3.33.0.

Previously SQLITE_ENABLE_UPDATE_DELETE_LIMIT be specified BOTH when the amalgamation is generated AND when it is compiled in order for it to work; or, specified in neither case in order for the additional ORDER BY/LIMIT clauses to not be recognized. Inconsistency resulted in inconsistent results.

I have no knowledge with respect to SEE ...

If you define either SQLITE_ENABLE_UPDATE_DELETE_LIMIT or SQLITE_UDL_CAPABLE_PARSER when generating the amalgamation, you must define one or the other of those two symbols when compiling the amalgamation.

Inconsistency results in a missing function (updateDeleteLimitError) declaration and failure to compile the amalgamation.

Thanks Keith,

Knew that, been there. Was working nicely up to version 3.31. To produce the SEE amalgamation, one had to append the SEE code to the standard amalgamation. It was easy to compile the amalgamation with the option properly enabled, then append SEE code.

With 3.32 that broke because the SEE code distribution was simplified, distributing pre-built amalgamations for the various flavours of cyphers sets supported.

Devs kindly and very quickly helped me by supplying me with the right SEE-amalgamation prebuilt to my needs.

I spotted the change for 3.33 and adjusted my build in order to use the SEE amalgamation as is along with compiling it with SQLITE_ENABLE_UPDATE_DELETE_LIMIT. It looked fine and I failed to properly test immediately though. :(

As the pages on the website clearly are a bit outdated about the way to activate this option, in regards to the checkin you referred to, I opened this thread here to trigger verification / clarification. Maybe I misunderstood a detail in how to properly use that feature now.

Spotted that too in the code, though unless SQLITE_UDL_CAPABLE_PARSER gets defined through some other mean, I don't see myself defining that. I'll dig deeper.

Thanks for the report. The 3.33 release of SEE has been updated with support for -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT. I will be taking additional steps to help ensure that I don't leave this out again in the future.

Thanks a lot.

Does the fix I'm seeing in the timeline address all codec variants (not limited to see-aes128-ccm)? Or should I better wait some days before updating?

We recently switched from using only sqlite3-see-aes128-ofb.c to using sqlite3-see.c itself, so as to support both aes128 and aes256 for some time, thanks to the key material prefixes (progressive migration to aes256). We will later settle on sqlite3-see-aes256-openssl.c and sqlite3-see-aes256-cryptoapi.c depending on the platform.

The fix in the SEE repository should apply to all of the SEE variants. But is is not in the "sqlite3.c" file in the SEE repository. Yet.

I have a fix on the public SQLite trunk (check-in 5b905d7d7714d1d2) that should provide a more general solution moving forward. With that latest trunk change, any amalgamation built using the -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT flag should be buildable with or without that flag. I will also add release checklist items to verify this prior to release.

The above check-in ensures the necessary define (SQLITE_UDL_CAPABLE_PARSER) is "baked-in" to the amalgamation if SQLITE_ENABLE_UPDATE_DELETE_LIMIT is defined when creating the amalgamation...

...on a slightly related matter, there was a recent thread where someone was trying to compile the amalgamation with some of the several SQLITE_OMIT_xxx options that only work when building from the canonical sources. Given this crops up every now and again (and despite it being documented at Options To Omit Features), would it be sensible/possible to include in amalgamations assembled without such options something like:

#ifdef SQLITE_OMIT_PRAGMA
#error SQLITE_OMIT_PRAGMA must be defined when building the amalgamation
#endif

In light of (my emphasis):

Important Note: The SQLITE_OMIT_* options may not work with the amalgamation. SQLITE_OMIT_* compile-time options usually work correctly only when SQLite is built from canonical source files.

from the above page, you may only want to do this for "omit" options that are known not to work properly with the amalgamation, or – if it's (justifiably) felt trying to keep track of which ones won't work / might work / will work is too much trouble, you could decide to "ban" all the "omit" options unless applied to the canonical sources.

(If something like this has already been considered and rejected, then please ignore this post!)

Thanks again Richard. Using code from SEE timeline Check-in [87401772a1], I get:

SQLite header and source version mismatch
2020-09-10 12:05:30 353f25ea2f06ad49adea9104cefabcda470009f8c0fddccf36a9017f50d4alt2
2020-08-09 18:02:03 525c8b50a8640c508e2aaf91f99b27518698a47d749a77cd1dc73e3c668dalt1

from running the cli. Obviously the .c file has:

#define SQLITE_VERSION        "3.33.0"
#define SQLITE_VERSION_NUMBER 3033000
#define SQLITE_SOURCE_ID      "2020-09-10 12:05:30 353f25ea2f06ad49adea9104cefabcda470009f8c0fddccf36a9017f50d4f188"

while the .h (obtained in the SEE ZIP package out of Check-In [87401772a1) has:

#define SQLITE_VERSION        "3.33.0"
#define SQLITE_VERSION_NUMBER 3033000
#define SQLITE_SOURCE_ID      "2020-08-09 18:02:03 525c8b50a8640c508e2aaf91f99b27518698a47d749a77cd1dc73e3c668dalt1"

May I safely patch the SQLITE_SOURCE_ID of the .h file or should I better wait for some other Check-In, in case some other detail (than the SOURCE_ID) would not be right?

That should be safe. But you can also update to the latest SEE check-in.