AutoincrementColumns
From Eigenpedia
Contents |
I. Overview
One might choose to define a table as follows:
create table product (
product_id int primary key,
<other fields>
);
Such a table requires supplying a unique empno to be
the primary key for every record. To save the effort,
SQL 2003 supports the identity column:
create table product (
product_id int generated always as identity,
<other fields>
);
An identity column is an autoincrement column. It generates
a sequence of values (such as 1,2,3 ...) that may be used as
a primary key.
Internally, an identity column references an anonymous SQL
sequence object. It supports all of the options for sequences.
A statement with more options might be:
create table product (
product_id decimal(10,0) generated always as identity
( start with 1000 increment by 10
maxvalue 1000000 no minvalue no cycle ),
<other fields>
);
Options of an autoincrement column include:
Table 1. Autoincrement options
| Field | Default | Comments |
| data type | - | required, must have scale 0 |
| generated by default | - | allows a value to be explicitly inserted into the column |
| start value | min or max | specifies the first value to use for the sequence |
| increment | 1 | must not be 0, and can be negative |
| maxvalue | highest value of type | an upper limit for returned by the sequence |
| minvalue | 0 | the least value of the sequence |
| cycle | false | whether to cycle once all values have been used |
Note: min and max are implementation defined.
II. Syntax
The full syntax of an identity column is as follows:
<identity column definition> :=
<column name> <data type> <identity column specification>
<identity column specification> :=
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ LPAREN <common sequence generator option> ... RPAREN ]
<common sequence generator option> :=
START WITH <value>
|
<basic sequence generator option>
<basic sequence generator option> :=
INCREMENT BY <value>
|
{ MAXVALUE <value> | NO MAXVALUE }
|
{ MINVALUE <value> | NO MINVALUE }
|
{ CYCLE | NO CYCLE }
ALTER TABLE <table name>
ALTER COLUMN <column name> <alter identity column spec>
<alter identity column spec> :=
(
RESTART WITH <value>
| SET <basic sequence generator option>
)+
Example:
alter table product alter column product_id
restart with 10 set increment by 10;
III. Restrictions
Some of the restrictions are as follows:
- There may be at most one identity column for a table
- The data type must have a scale of 0
- Values are signed integers, representable by data type
- Increment must not be 0
- MIN <= START <= MAX
- Each kind of option (ex: MINVALUE, NO MINVALUE) is specified only once
- When "generated always" is specified, explicit values may not be inserted into identity columns
The behavior of sequences include:
- Sequences are updated independently of transaction rollback or commit
- Sequences may have gaps in some situations
- When a sequence has ended and cycles are not allowed a data exception is thrown
- (For named sequences): multiple references in a single row have the same value
Alter sequence is a bit tricky, because sequences become somewhat
undefined after they have expired. Common uses might be to reenable
the sequence by expanding the range of the sequence or allowing cycles.
We support the following semantics:
- If the sequence is valid, alter follows the conventions of SQL2003
- If the sequence expires, set base to last valid value, and set "expired" flag
- On alter, update the value and expired flag
IV. Implementation
A. General design
Autoincrement columns become important when inserting into a table.
To obtain a generated value for the column, ReposDefaultValueFactory is
asked to generate a new column default value. For sequences, we return
a RexCall to a new SqlOperator, NextValueOperator. The operator takes
an argument to identify the sequence. This operator returns a long
value which is later casted to the appropriate type.
(No overflow check is required.)
public class ReposDefaultValueFactory ...
{
public RexNode newColumnDefaultValue()
{
if (sequence associated with column) {
return rexBuilder.makeCall(
NextValueOperator,
sequenceMofId);
} else {
return column default value;
}
}
}
This RexCall is then translated into Java code. The translation
includes two components (1) initialization and (2) value access:
import net.sf.farrago.catalog.FarragoSequenceAccessor;
...
class CalcTupleIter
{
static FarragoSequenceAccessor sequence;
...
public Object fetchNext()
{
if (sequence == null) {
sequence = connection.getSequenceAccessor(mofId);
...
nextValue = sequence.getNext();
...
}
}
}
On initialization, a singleton sequence accessor is retrieved from
the catalog. For value access, a call is made to access the next valuet.
Both calls are synchronized to allow concurrent access. As a
result of generating a value,the sequence may update its current base
value in the catalog.
B. Catalog
FemStoredColumn gets the new attribute generatedAlways, a nullable
value implies no values are generated. False implies it is legal to insert
into the columns while generatedAlways true implies that it is illegal
to insert values into the identify column..
SequenceGenerator is a new class in the Farrago catalog. It exists
as part of the SQL2003 package and contains attributes for name,
dataType, currentBaseValue, increment, maxValue, minValue, cycle,
and expired. The attributes are resolved during sequence creation time
and no fields are nullable.
A SequenceGenerator has a composite relationship with either a column
(1 to 0..1) or a schema (1 to *). This allows a SequenceGenerator to
be owned by (and cleaned up with) either a column or schema but not
both. (The schema association models named sequences, which we can
implement later). The specific column is FemStoredColumn. For schema
it is sufficient to have SequenceGenerator inherit from ModelElement to
be a child of schema.
FemStoredColumn has an attribute generatedAlways, which describes
whether a value may be inserted. The concept also applies to computed
columns (which are not implemented yet).
C. Updates
A sequence is guaranteed not to produce duplicate values (until values
cycle). In order to
enforce this requirement, the SequenceAccessor updates the catalog
before uisng values. For example, the accessor increments the
catalog by 1000 on initialization. Then it is able to allocate
up to 1000 values without worrying about crashes, other statements,
future constraint violations, etc. After it consumes all of its reserved
values, it reserves another chunk from the catalog before continuing.
In an effort to reduce gaps in sequences, the unused values are
returned are unreserved by closing allocation with FarragoRepos.
Access to a sequence generator is synchronized. Only one statement at
a time may access a sequence generator. However, the retrieval of
individual values is not synchronized.
V. External Interfaces
A. Views
select
table_cat, table_schem, table_name, column_name,
current, increment, min, max, cycle
from
sys_boot.mgmt.sequences_view;
The query returns all fields for sequences.
B. JDBC API
Deferred for contributors to implement on the basis that the API
would not be required for ETL.
VI. References
Taken from an unofficial SQL2003 Part II recommended by Whitemarsh
Table Definition 11.3 (p.525)
Column Definition 11.4 (p.536)
Creation of sequence generator 9.22 (p.463)
Sequence generator definition 11.62 (p.727)
Alter sequence generator statement 11.63 (p.728)
Alter column definition 11.12 (p.574)
Alter identity column spec 11.17 (p.580)
Generation of the next value 9.21 (p.462)
