How-to Guides‎ > ‎

Managing and using single-record tables


It is often useful to have a table with precisely one record, to hold management information for example. This note shows how such a table can be set up and populated, how CL code can ensure the 'singleton' nature of this table, and how CL global constants can be used to make the singleton object accessible to other CL code in the database.

note: the code presented here is included in Polyhedra release kits, and may be freely adapted for use in your Polyhedra-based application.

Defining the table

You define the table in the normal way, using any type you like for the primary key: as there will only be one record in this table, space is not an issue. Immediately after creating it, though, you should create 'the' record you want it to contain.

create table singleton
(    persistent
,    id integer primary key

-- add an attribute which we will use when illustrating how the singleton
-- can be used

,  nextid integer default 0
-- create the object NOW, whilst no CL is running.
insert into singleton (id) values (1); commit;

CL methods on the table

We now need to attach CL code to the table to enforce the singleton nature: that is, to stop anyone deleting the record, or creating any more. This is done by having a file that contains a definition of the triggers/methods we want to attach to the table, and telling the database server about this file next time the server is started. In the file, we say that a group of methods are for a particular table by introducing them with a 'script' line:

script singleton

We then define the methods that are applied when an object is created in the table, and deleted from the table:

    on  create
        abort transaction "you should have only one object in singleton."
    end create

    on  delete
        abort transaction "you must not delete the singleton object."
    end delete

Both these methods cause the transaction to be abandoned with a suitable error message. (It is also possible to define an error code for the transaction, which might make it easier for the client to detect the reason for their error.) We could now flag that we have finished adding methods to the singleton table, but instead we take this opportunity to add another method that makes use of the nextid attribute.

    function integer next_integer
        add 1 to nextid
        return nextid
    end next_integer
end script

(The 'end script' line matches up with the earlier script line, and signifies then end of the methods to be attached to the named table.) The function can be used by any other CL method to generate a new integer value, providing the caller can access the singleton object.

Accessing the singleton from other CL methods

If any CL method wants to access the singleton object - for example, to make use of its next_integer() function - it can include lines such as the following at the start of the method:

     local reference Singleton obj
     locate singleton (id=1) into obj

The locate statement finds the record in the table that matches the specified criteria; if no record matches it, the pointer is set to null (and if more than one record matches the criteria, a run-time error is generated). To make things easier, the above code can be moved int a 'global function':

function Singleton Find_Singleton
    local reference Singleton obj
    locate singleton (id=1) into obj
    if not exists obj then
                debug "APPLICATION ERROR:\n\tSingleton not found"
        end if   
    return obj
end Find_Singleton

This function is made global in scope by defining it outside the scope of a script ... end script grouping. As it is good programming practice to be somewhat paranoid, we have added in an extra check, that will stop the database server (by use of the quit statement) if the singleton object cannot be found. As a result of this check, any code calling the Find_Singleton() function can safely assume that the return value is not a null pointer.

While the locate statement is fast, as it can make use of the index that is automatically added to the primary key of a table, it is better to avoid doing this more often than needed; fortunately, Polyhedra's CL engine supports the concept of global variables, which are initialised each time the database starts:

constant reference Singleton The_Singleton = Find_Singleton ()

As with global variables, constants have global scope if defined outside a script ... end script grouping. The above definition allows CL methods to call next_integer() of the_singleton whenever they need a new integer value, and this will be cheaper than using next_integer() of Find_Singleton().

(By the way, names in CL are not case-sensitive! The same holds true for Polyhedra's implementation of SQL.)