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.
Updating a row to increment the sequence value by
1 will do nothing[225]. 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.
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.[226] 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.[227]
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.[228]
[225] Well, it will waste some electrons.
[226] 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.
[227] 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.
[228] 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.