sys_fields, hierarchical, auto_counter

Some table options change the write cycle. Each option declares its columns, puts field triggers on them, and provides the trigger methods on the base table class. You saw field triggers in lesson 22. This lesson covers three options: system fields, hierarchical, and auto counter. You declare them under the options of a table.

System fields adds up to seven columns. Each parameter switches one column on or off. All are on by default, except draft. The id column becomes the primary key, a text of twenty-two characters, generated by check pkey as lesson 20 showed. The region table of the course model passes id equal to False, because its key is a code. The two timestamps have the type DHZ, a timestamp with time zone. The deletion column marks a logically deleted row. The draft column marks a draft row. These two have no trigger. They set table attributes that the read filters of lesson 18 use.

Set TS now writes the current time into the column. For a DHZ column, the time is in UTC. On insert both timestamps are set. On update only the modification timestamp changes. Set current user writes the user of the environment. The environment is the one of lesson 17. Its key user is read by db dot current user. Here we set it with temp env. Without a user in the environment, the user columns stay None. These triggers run only in the full cycle. The sql commands of lesson 24 leave the columns as you pass them.

Hierarchical turns a table into a tree. The table needs the system field id as primary key. The option adds a parent id column, with a relation to the same table. That relation has on delete cascade, so deleting a node deletes its subtree. For each field you list, here code and row count, the option adds a path column named hierarchical underscore field, and the same path for the parent. The pkey path is always added. Underscore h count holds a sortable position path. Two virtual columns give the number of children and the level. With hierarchical, auto counter keeps the position of a node among its siblings in the row count column.

We insert two roots and a chain of two children. You pass only the code, the description and the parent. The trigger on parent id reads the parent row and writes the path columns. It also gives each node the next position among its siblings. It locks the parent row while it computes the position, so two connections do not get the same number. Each position is two base-36 digits in the h count path.

To move a node, update its parent id. Pass the old record, read with a lock. The hierarchical triggers need it. The before-trigger rewrites the paths of the node. The after-trigger updates each child with the update command, one child at a time. Each child then updates its own children. So the whole subtree gets new paths, and every child runs the full update cycle. Moving a node under itself or under one of its descendants raises a business logic exception. An update of a hierarchical row without old record raises an exception too.

Auto counter also works without a hierarchy. It adds a counter column, row count by default, with the trigger set row counter. The scope names the columns that group the rows. In the example, the lines of each invoice are numbered from one. Without a scope, the whole table shares one counter. When the record already has a value, the trigger keeps it. Otherwise the value is the highest counter in the scope plus one. Rows with an empty counter are ignored in that computation. The trigger takes a lock before it reads the highest value, so two connections that insert in the same scope do not get the same number.

System fields gives a generated key, UTC timestamps, the user of the environment, and the deletion and draft columns. Hierarchical keeps paths and positions through triggers on parent id, and a move rewrites the subtree. Auto counter numbers rows within a scope. The next lesson shows two helpers that read with a lock and then write.