How-to Guides‎ > ‎

Auto-incrementing fields

Polyhedra does not at present have the ability to flag a column of a table as auto-incrementing - which is a slight pity, as there are circumstances where there is no obvious choice for the value for a primary key value, and it would be nice for the DBMS to invent one for you. (In common with proper relational databases, Polyhedra insists all tables have a primary key.)

However, it is easy to implement such a facility for yourself, by attaching CL to a table that needs the auto-incrementing field, and in this article we shall show you a variety of ways of coding up this functionality. In the article, we shall assume we have a table called mytable, and we want it to have an integer primary key called id - and we want the value of the id field to be assigned by the database when a record is created. (By the way, in the rest of this article we shall use the terms column, field and attribute interchangeably, and likewise row and record -but we avoid using the word 'tuple', which is the mathematical term from relational set theory!)

Approach 1: use a manager table

Conceptually, the easiest way to emulate an auto-incrementing field is to have a special table in the database with a single record, and have a field in this record that holds the last value used for the primary key of the mytable table. This helper table could be created by the following SQL in the schema definition file:

create table manager
( id integer primary key
, mytable_id integer default 0
);
insert into manager (id) values (1); commit;

We can now attach code to the mytable table to make use of this field:

script mytable

    on  create
        local reference manager the_boss
        locate manager (id=1) into the_boss
        -- (note: we should add paranoia checks here to ensure the_boss is not null!)
        add 1 to mytable_id of the_boss
        set id to mytable_id of the_boss
    end create

end script

Every time a record is created in mytable, the 'on create' handler finds the (only) record in the manager table, and uses the mytable_id field to determine the id for the new record. As we are not making any use of the user-supplied value for id, we suggest you adopt the convention that someone creating a record always supplies the value zero for the id field when creating a record in this table.

The CL locate command will be pretty efficient, as it will make use of the index that Polyhedra automatically places on the primary key of the table; fortunately, though, there is a better way, as it is possible in CL to set up a global constant - called, say, the_boss - which points at the manager record. This allows us to omit the first two lines of the 'on create' handler, and get better performance. We could also add checks to ensure that this whole mechanism is not being abused, either by people creating records with silly values for the id field, or by attempting to change the value:

script mytable

    on  create
        if id <> 0 then
            abort transaction "the ID of a new MYTABLE record must be set to 0"
        end if
        add 1  to mytable_id of the_boss
        set id to mytable_id of the_boss
    end create

    on  set id
        if id <> mytable_id of the_boss then
            abort transaction "do not change the ID field of a MYTABLE record!"
        end if
    end set id


end script

Every attempt to create a mytable record with a non-zero is will now be rejected, and any attempt to change the id to a silly value will be rejected. Note that we have to allow setting it to mytable_id of the_boss, as the 'on set' trigger will be invoked as a result of the last statement in the on create handler: Polyhedra CL triggers cascade. However, entity integrity will stop us changing the primary key of another record to match that of the most recently-created record in the table (assuming it has not been deleted), as that would give a primary key clash.

Approach 2: a more scalable solution

If you want a lot of tables to have an auto-incrementing primary key, then a drawback to the above approach is that you will have to attach CL code to each of them - and the manager table would have to have a separate column for each table, if it was necessary to allow records in different tables to have the same values for their primary keys. There is, however, another approach. Consider the following table definition and attached CL:

create table auto_id
( id integer primary key
, nextid integer shared hidden
);
create table mytable
( derived from auto_id
-- add some attributes:
, name large varchar unique not null
);

script auto_id

    on  create
        if id <> 0 then
            abort transaction "only create records in this table with id=0"
        end if
        add 1 to nextid
        set id to nextid
    end create

    on  set id
        if id <> nextid then
            abort transaction "don't tweak the PK of this table!"
        end if
    end set id
 

end script

We are first creating a table called auto_id, with two attributes. The first attribute is normal enough, but we have flagged the second as shared and hidden. Flagging an attribute as shared means that every record in the table shares the same value for the attribute: in fact, the value is stored alongside the schema definition rather than in each record. A C++ programmer should probably think of this as a static attribute. Flagging an attribute as hidden simply instructs the Polyhedra SQL engine to hide this attribute if someone for a 'select * ' query.

Moving onto the definition of mytable, which make use of Polyhedra's derived from syntax. This instructs the Polyhedra SQL engine to construct a derived table, that inherits all the attributes of the auto_id table. So, as defined here mytable will have three attributes: the inherited attributes id and nextid, and the extra attribute name. As the second of these is hidden, performing a select * query on mytable will return 2 columns wide.

Turning now to the CL code, it should be clear we are using the nextid attribute to remember the last-used primary key; every time a record is inserted into auto_id with an id of zero, it will automatically be renumbered. However, mytable is derived from auto_id, so mytable records can also be considered as auto_id records (with extra attributes that are not visible when considering the records via the 'base class'), and so inherit this behaviour as well. There is no need for any specific CL code to be attached to the table for this to occur.

Summary

While Polyhedra does not have inbuilt support for auto-incrementing fields, this article shows there are many ways of achieving this effect by using CL. The approach to choose will depend on the needs of the application and the tools being used to develop it: if the development environment cannot readily make use of Polyhedra's table inheritance mechanism, for example, you would need to adopt a naming convention plus the use of a tool to generate CL for each table for which you wanted this functionality. If however, you are able to take advantage of the inheritance mechanism then you only need to attach CL to the base table and all directly- or indirectly-derived tables will automatically inherit not just the attributes but also the behaviour that implements the auto-incrementing mechanism.