commit ff25d2d662b345fcba55919ff45d61714925794e
author Evan Stade <evanstade@microsoft.com> Tue Jun 24 19:03:44 2025
committer Chromium LUCI CQ <chromium-scoped@luci-project-accounts.iam.gserviceaccount.com> Tue Jun 24 19:03:44 2025
tree d020167ce32ad7a509f0ebb941d522bc6911cee0
parent 460535ed311cdf429fabe3b6a4dedd3ae03c43f6

SQL: revise guidance around triggers and add ability to enable them

Previous guidance and code banned the use of SQLite triggers without
exception. The reasoning is the (unsubstantiated) claim that triggers
"increase the difficulty of reviewing and maintaining" SQL usage.

The guidance is updated to add nuance: misuse of triggers is bad, but
there are also appropriate ways to use triggers. If code/queries get a
lot simpler (to review, maintain, update, etc) with the use of triggers,
then they should be applied. Otherwise, steer clear.

A new DatabaseOptions field is introduced to allow enabling triggers.
We could just enable triggers by default, but making this a non-
default configuration aligns with the guidance to avoid triggers most
of the time, and increases the likelihood that the guidance will be
discovered and read.

IndexedDB's SQLite implementation is updated to enable triggers. IDB is
easily the most sophisticated client of //sql in Chromium (after WebSQL
has been disabled) by number of and complexity of queries. As a case
study of code that is better served by the use of triggers, IDB has
several paths for deleting a record (i.e. row in the records table),
which then has a cascading effect on some other SQLite tables:

1. records can be deleted explicitly, given a certain key range
2. records can be deleted by deleting or clearing the object store
   that they're in.
3. a record can be *updated* via Put(), which is equivalent to
   deleting the old row and inserting a new one (via Put())

In all cases, the table that tracks blob references must be updated, and
the table that holds blobs may also need to be updated depending on the
remaining references. This means that *multiple* additional queries
would need to be written for *each* of the above record deletion
pathways, as well as any future record deletion pathways that are added.
A pair of triggers greatly simplifies this process. (Note this is just
one example of the application of triggers in IDB code; there are more.)

Bug: 40253999
Change-Id: Id89dfe38bdc7648f0837fb002441478383c3706a
Reviewed-on: https://chromium-review.googlesource.com/c/chromium/src/+/6657148
Reviewed-by: Abhishek Shanthkumar <abhishek.shanthkumar@microsoft.com>
Commit-Queue: Evan Stade <evanstade@microsoft.com>
Cr-Commit-Position: refs/heads/main@{#1478096}