Inline SQL queries using boolean parameters

[H-Shay]

Before SQLite 3.23, TRUE and FALSE were not recognised as constants by SQLite, and the IS [NOT] TRUE/IS [NOT] FALSE operators were not supported. While Synapse supported SQLite versions older 3.23 that made it necessary to avoid using TRUE and FALSE constants in SQL commands. To work around this constraint the boolean operators were passed as parameters like so:

synapse/synapse/storage/databases/main/purge_events.py

Lines 251 to 258 in 57aeeb3

	txn.execute(
	"UPDATE events SET outlier = ?"
	" WHERE event_id IN ("
	" SELECT event_id FROM events_to_purge "
	" WHERE NOT should_delete"
	")",
	(True,),
	)

However, Synapse now requires a SQLite version of 3.27.0 or higher if SQLite is configured so we no longer need to do this and futhermore should convert old-style queries in the codebase to use TRUE/FALSE inline as it is easier to parse/read.

[prithvi009]

Is this issue still open?

[clokep]

Is this issue still open?

Yes, there's the start of a PR at #15525, however.

[Attul-Sharma]

txn.execute(
"UPDATE events SET outlier = ?"
" WHERE event_id IN ("
" SELECT event_id FROM events_to_purge"
" WHERE NOT should_delete"
")",
(1,),
)