Automatic Sequencing

Note

This section describes how Babase automatically re-computes the sequence numbers used within various tables to give a timewise ordering to rows that would not otherwise have an ordering. The columns that hold the sequence values have names that vary by table. The following description uses the generic column name of Seq when referring to the name of the column that holds the sequential numbering.

The system automatically re-computes Seq values to ensure that they are contiguous and begin with 1. Seq may be NULL when the row is first inserted, in which case the system will automatically assign the next available sequence number. Changing a sequence number to match one that already exists (for, e.g., a given darting), or inserting a new row having a sequence number equal to that of an existing row (for, e.g., a given darting) causes the sequence number of the unchanged row to be incremented and the recomputation of subsequent sequence values. E.g. starting with rows A, B, C, and D having Seq values of 1, 2, 3, and 4 respectively, changing the Seq value of row D to 2 automatically changes the Seq values of rows B and C, increasing them by one. The result is that the new ordering of the rows by sequence number becomes: A, D, C, B. Deleting a row recomputes the sequence numbers of the remaining rows in a corresponding fashion.

Caution

Updating a row to increment the sequence value by 1 will do nothing[226]. Performing such an operation creates a gap in the sequence which is then filled by decrementing the sequence numbers of all the rows above the gap, including the row that the original update incremented.

Likewise, updating the Seq column in a way that assigns Seq numbers past the end of the sequence results not in the user-specified Seq values but rather in Seq values that are re-computed so as to maintain contiguity.

Warning

A single UPDATE statement that relies on automatic resequencing to eliminate more than one duplicate Seq (per, e.g., a given darting) produces indeterminate results.[227] For example given rows A, B, C, and D, with Seq values of 1, 2, 3, and 4 respectively. One UPDATE statement that changes the Seq of A to 3 and B to 4 will result in an indeterminate ordering.[228]

The system will report an error when the Seq values of inserted rows would create non-contiguous Seq values or a sequence that does not begin with 1.[229]



[226] Well, it will waste some electrons.

[227] Technically an UPDATE statement that, in the absence of any triggers, would result in more than one Seq value (for any given, e.g., Dartid) within a contiguous series of Seq values as examined after the UPDATE is an UPDATE that results in an indeterminate ordering (within the, e.g., Dartid). However in the future this behavior may change such that any duplication of Seq values, not just those within a contiguous series of Seq values, may result in an indeterminate ordering.

[228] The problem is that duplicate Seq values are eliminated on a row by row basis. When more than one duplicate exists (per, e.g., a given darting) the order in which duplicates are eliminated matters. But when 2 or more duplicates are created at once there is no way to control the order in which the system processes the removal of duplicates.

[229] This is done so that data entry errors are not invisibly corrected under the assumption that when a Seq value is deliberately assigned to a new row that there is a reason for the assignment. Updates that make the Seq numbers too large, that would create gaps in the sequence if not corrected, do not result in errors but are automatically fixed. The latter behavior could be considered a bug; one to be fixed if it ever causes a problem.


Page generated: 2024-10-29T13:28:51-04:00.