search

ericsink / SQLitePCL.raw

A Portable Class Library (PCL) for low-level (raw) access to SQLite
Apache License 2.0
512 stars 106 forks source link

Consider enabling SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile option when building e_sqlite3 #377

Open crozone opened 3 years ago

crozone commented 3 years ago

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

From the SQLite Compile-time Options page:

This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

If this option is defined, then it must also be defined when using the Lemon parser generator tool to generate a parse.c file. Because of this, this option may only be used when the library is built from source, not from the amalgamation or from the collection of pre-packaged C files provided for non-Unix like platforms on the website.

The SQLitePCLRaw.lib.e_sqlite3 package does not appear to be built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT at this time.

Enabling this compile time option would allow queries like UPDATE "table" SET "thing" = 'value' WHERE "thing" IS NULL ORDER BY "id" LIMIT 1; and similarly DELETE FROM "table" WHERE "thing" = 'value' ORDER BY "id" LIMIT 1;.

This functionality is available in other database implementations like Postgres and MySql, and is particularly useful for performing atomic updates without the need for a table lock via a BEGIN IMMEDIATE TRANSACTION, which makes implementing job queues quite elegant.

ericsink commented 3 years ago

Looks appealing, but the e_sqlite3 builds are currently made from the amalgamation, so the change to the C side of my build system would be significant.