Sequence Numbering

There is a built-in facility to assist in the handling of numeric fields intended for use as a record sort order (N.B. does not include MySQL auto-increment fields). It operates on the principle that records are numbered by default in increments of 10, thus making it possible for a new record to be placed in between two existing records when required.

There are two basic functions associated with record sequencing:-

• Allocate the next sequence number to a new record.
• Renumber the whole table resetting everything to increments of 10.

The sequencing parameters for a given table are defined in the associated record in dba_table_info using the following fields:-

• sort_1_field - This is optional and if it is defined then a two-level sort is applied using this field as the first level sort and the sequence number field as the second level sort.
• seq_no_field - This specifies the field to be used as the sequence number. If this is blank then sequencing will not be applied to the table.
• seq_method - This is is an enum and is set to continuous or repeat. If it is set to continuous then all records will be numbered in a single sequence running through the whole table. If it is set to repeat then the records will be sequenced locally against each value of the level 1 sort. The repeat option is not valid without a level 1 sort field. The continuous option however is valid regardless of whether there is a level 1 sort field defined.
• renumber_enabled - This flag must be set to allow the table to be renumbered. If it is not set, then sequence number allocation for new records is allowed but renumbering is inhibited.

The function next_seq_number is called to allocate the next sequence number for a new record. This takes two parameters for the table name and the value of the level 1 sort field (the latter being ignored if there is no level 1 sort field defined for the table). The new sequence number is returned. The following definition must be accessible by any code using this function:-

define('NEXT_SEQ_NO_INDICATOR',9999);

The value of 9999 is not mandatory, though this has tended to be used by way of convention. It is important however that whatever this constant is set to, this is used as the default value of the sequence number field in the MySQL table definition. Typically next_seq_number will be called by the afterSave method of the table class. This will check if the sequence number field is set to the default and if so will re-save the record with an updated sequence number.