Writes without triggers: sql_*, table.lock

The commands of lesson 20 run the full write cycle. Sometimes you want only the SQL statement. Data loads, repairs and bulk changes are examples. For this, asqueel has the commands with the prefix sql underscore. The table has sql insert, sql update and sql delete. A query has two terminals, sql update and sql delete, that write the rows the query selects. None of them runs hooks, field triggers or relation actions. They do not enter the stack behind current trigger.

Sql insert takes one record or a list of records. It returns what it received. Before writing, it assigns missing keys with the same rules as check pkey. So every note gets an id. No field trigger runs. That is why the insert timestamp of the system fields stays empty. Use sql insert when you control every value yourself.

With an integer key, the first missing key is the maximum plus one. In a list, each further missing key is the next integer after the highest key seen so far. Here ticket d gets eleven, after the explicit ten. A list is written with executemany. Consecutive records with the same columns share one statement. A list gets no RETURNING, so values decided by the database are not copied back. A single record does get RETURNING, as with insert. The ticket table of lesson 20 shows it: the opened column comes back only in the single insert.

Sql update writes one record, with the same key rules as update. The key comes from the old record, then from the pkey argument, then from the record. Only the columns present in the record are written. Sql delete takes a record that holds the key. Both return None. They do not check how many rows changed. Sql delete runs no on delete action. A related row is then handled only by the foreign key in the database.

A query can write the rows it selects. Sql update takes a dictionary of values. Sql delete takes nothing. Both build one statement. The WHERE clause is the key, inside the same SELECT the query would run, reduced to the key columns. So the filters, the order and the limit of the query decide the rows. The default filters apply too. A logically deleted row is not written, unless the query says exclude logical deleted equal to False. A useful pattern is to fetch first, look at the rows, and then call sql delete on the same query. Both terminals return the number of rows written. A query with group by or having cannot be used for writing.

This table summarises the difference. The run that produced this lesson recorded every hook call of the note table. The list stayed empty after every sql command, and current trigger stayed None. Use the sql commands when the hooks would be wrong or too slow. Remember that the rules in the hooks are then your responsibility.

Table lock takes a lock on the whole table, inside the current transaction. The default mode is access exclusive. You can pass any PostgreSQL table lock mode. With nowait, the statement fails at once when another transaction holds a conflicting lock. The lock ends with the commit or the rollback. SQLite has no table locks. There, lock takes the write lock of the database file, so other connections cannot write until the transaction ends.

The sql commands run one statement and no hooks. The query terminals write the selected rows and return their count. Table lock holds a table lock until the transaction ends. The next lesson returns to the full cycle, with three table options that bring their own triggers.