Polyhedra - another fine product from...

How-to Guides‎ > ‎

Controlling the GPIO lines of a Raspberry Pi

How Polyhedra's speed and active nature make it appropriate for interacting with the real world

This article looks at how the Polyhedra relational DBMS can be used to act as the interface between the GPIO lines of a Raspberry Pi computer and applications running in user space. It describes a client application that runs in super-user space to interact with the memory-mapped GPIO, and the structure of the tables it monitors. This is then developed further to show how one can drive the LEDs and switches on an I/O card emulating a set of traffic lights.

Polyhedra features illustrated include SQL, active queries, transaction rollback, CL trigger language, our ODBC extensions for events, and table inheritance. An early version of this application is shown in action in a webcast; at that time the circuit was mounted on a breadboard rather than mounted on an I/O card.

(A copy of this article in PDF format is appended below, for ease of printing and offline viewing; it contains as an appendix a copy of the source code for the client application described in this article. also attached below is a ZIP file containing source code, table definitions, compile scripts etc  to allow the the example to be built and run on a Raspberry Pi running Raspian Linux; it also includes code and Windows shortcuts to allow a test-compilation on Windows.)


An overview of Polyhedra

Polyhedra is a family of relational database management systems designed from the start for use in embedded applications. It has two main flavours: Polyhedra FlashDBMS and Polyhedra IMDB, with the latter being available for 32-bit platforms (Polyhedra32, the ‘original’ version) and 64-bit platforms (Polyhedra64). The difference between these products is that Polyhedra IMDB is in-memory for speed, and has journalling and fault-tolerant mechanisms to ensure data persistence and system availability, whereas Polyhedra FlashDBMS trades performance against RAM footprint by using a tuneable in-memory cache in front of a file-based database (assumed to be on a flash-based file system). In 2012 a new product was released: Polyhedra Lite, a free version (subject to license conditions) of Polyhedra32 IMDB, but omitting some functionality such as support of fault-tolerant configurations.

All Polyhedra products support an extended SQL-92 subset and the ODBC and JDBC client libraries conform to the international standards. They have object-oriented features, such as table inheritance (which simplifies and speeds up many queries and updates) and behaviour (to perform knock-on actions and additional integrity checks).

Polyhedra was designed from the start for client-server use. When all the software is running on the same machine, the client-server architecture has two main benefits:
  • The software naturally allows for multiple clients, which could be running the same application software (on behalf of different users, say) or performing different parts of the overall application.

  • Where supported by the operating system and hardware platform, the client application(s) and the server will run in separate address spaces, protected from each other - so a failure of an application will not damage the integrity of the database, nor stop other parts of the application running.
The client-server architecture naturally extends to allowing client and server to be on separate machines, to allow the application to be distributed. On an SMP multicore or multiprocessor machine, the client-server architecture allows clients to be running on separate cores from each other (and from the server), and thus allow the overall application to make more use of the available CPU cycles.

Polyhedra servers can operate stand-alone, with each instance handling a separate database, and in addition both Polyhedra IMDB and Polyhedra FlashDBMS can operate in master-standby mode, where one server is acting as a hot standby of another server and has a read-only copy of the database. Polyhedra is fully transactional, and satisfies the Atomic, Consistent and Isolated properties needed for ACID compliance. Polyhedra FlashDBMS transactions are all durable, but in Polyhedra IMDB durability can be balanced against performance: critical changes to the data are preserved by streaming journal records to a log file, and client applications can choose whether the success of a transaction is to be reported immediately or when the log file has been flushed.

Polyhedra has a special feature called active queries that allows clients to keep up to date without polling. Basically, for the lifetime of the query the server remembers enough about the query (and the previously-transmitted result set) to know when it has become out of date – so when the transaction completes it can send a ‘delta’ to the client that launched the query to bring it up to date. There is a delta merging mechanism to avoid problems if the client is slow.

Polyhedra runs on a number of platforms, and when a client is running on a different OS or a different architecture to the server the Polyhedra libraries automatically handles issues such as differing endianisms.

Polyhedra and the Raspberry Pi

The Polyhedra in-memory relational database system has been ported to run on the Raspberry Pi computer, a cheap ($35 US) computer the size of a credit card that was designed for educational and hobbyist use. In fact, Polyhedra Lite is available for free on this platform under Linux, subject to certain license restrictions. On this platform you can achieve thousands of transactions per second.

picture of a raspberry Pi board
Figure 1: the Raspberry Pi computer and a schematic diagram. Newer versions have more RAM (512MB).
For more information about the Raspberry Pi, visit www.raspberrypi.org

The Raspberry Pi makes available some GPIO lines via a header (visible in the top-left of the picture in figure 1), and the standard Linux distribution allows a program with super-user privileges the ability to access these lines via memory-mapped I/O. The client-server nature of Polyhedra means that one can have a relatively-simple client application running with super-user privileges that acts as an interface between a Polyhedra database and the GPIO lines, with the database server and all other client applications running in user mode. This paper describes how this can be implemented, and illustrates its use with an example traffic lights emulator that drives some LEDs and switches.

Overall design of the Polyhedra GPIO interface

The basic idea is to have some tables in the database that will be monitored by a special client application, which we shall call polygpio. The client will make use of Polyhedra’s active query mechanism to avoid the need for polling the database: instead, the database will automatically push through relevant updates to the client application in a timely fashion. Thus, when a record is created in a table called, say, output, polygpio will know that it is to set the state of the corresponding GPIO line according to the values stored in this record. In a similar way, polygpio will monitor an input table, and will report back the current state of relevant GPIO lines through this table. The polygpio client application is not concerned about why records are created in these tables, nor what other client applications will do with the values it reports: it has a very limited job to do, and will concentrate solely on that job.

The database tables

When designing the schema for the tables used by the polygpio application, we will make use of a special feature or the Polyhedra relational database system: table inheritance. This allows us to define a base table, and then derive other tables from this; a derived table will inherit all the attributes of the table from which it is derived, and can add extra attributes. If a record is created in a derived table, then part of the record can also be seen in the underlying table. This is an object-oriented approach to database design, where a table can be considered as a class. One immediate advantage to this approach in this application is that it is easy to ensure that it is impossible to set up a GPIO line as being both an input and an output: simply derive both tables (classes) from a common table where the primary key identifies the GPIO line:

create table iopoint

( id integer primary key

, …


create table input

( derived from iopoint

, …


create table output

( derived from iopoint

, …


If you create a record in input with an id of, say, 1, than if you inspect the base table iopoint you will also see a record in that table with an id of 1 (though attributes defined in the derived table will not be visible). It should now be obvious that creating a record in output with the same id will be rejected, as you cannot have two records in iopoint with the same primary key value. (Polyhedra strictly enforces this type of integrity policy, as well as referential integrity.)

Before going on to fleshing out the above table definitions, let me introduce another Polyhedra extension: the idea of static and virtual attributes. A static attribute is similar to the idea of a static class member in C++, in that all members of the class (that is, all records in the table, and derived tables) share the same attribute value; the value is stored once, and though it is visible in each record it does not occupy any space in the record. A virtual attribute expands on this, except that a separate value is stored for each derived table: we shall use this to store information about the type of a record, so that by looking at the base table (iopoint) you can see immediately the derived table in which each record was created.

We can now give the SQL to define each of our tables, starting with iopoint:

create table iopoint


   ( id         integer primary key    -- identifies the GPIO pin

   , type       large varchar virtual

   , value      bool   transient

   , status     large varchar          -- used for problem reports



insert into iopoint (id, type) values (-1, 'iopoint');

delete from iopoint where id < 0;


(In Polyhedra, a transient attribute is one whose value is likely to change fast, and for which the contents do not have to survive a shut-down and restart of the database. This means there is no need to journal tranactions that only affect such attributes, which improves system performance.)

The insert and delete statements appear to cancel each other out, but there is a side-effect of setting the value of the virtual attribute, which is remembered even though the table will be empty. If another new record is created without mentioning the type field, the stored value for the field will be used.

The table for output points

Now we move on to the output table:

create table output


   ( derived from iopoint

   , pullup        bool

   , desired_value bool



insert into output (id, type) values (-1, 'output');

delete from iopoint where id < 0; commit;  

As indicated earlier, we derive this table from iopoint, and as we did when setting up the base table we do a quick insert and delete to set the contents of the virtual attribute. We have also added two attributes: pullup and desired_value. The expectation is that the polygpio client value will look at the desired_value field to decide whether to set the GPIO line to true or false, and then record the fact in the (inherited) value field. To consider the use of the pullup field, let us look at two different circuits for driving a LED via a GPIO pin:

Figure 2: two ways of driving LEDs

In the upper circuit the resistor pulls up the LED, which will light up if the GPIO pin is set to low; by contrast, in the lower diagram you have to set the GPIO line high to cause the LED to light up. To give the hardware designer flexibility, while retaining the convention that TRUE means the LED is on, pullup can be used to tell the polygpio client which logic to adopt, on a pin-by-pin basis.

Input points

We can now turn to the input table, and here we have adopted a slightly different approach to minimise the work of the polygpio client:

create table input


   ( derived from iopoint

   , low_is_true   bool

   , settle_time   integer

   , settling      bool     transient local

   , timestamp     datetime transient

   , raw_value     bool     transient



insert into input (id, type) values (-1, 'input');

delete from iopoint where id < 0; commit;  

As with the output table, we have created a table derived from iopoint and then initialised the value for the virtual field type that will be used for records instead into this table. The job of polygpio will be simply to set the raw_value field to TRUE if it notices that the GPIO pin is high, and FALSE if it is low.

To handle issues such as debounce logic, we shall attach trigger code to the database. Polyhedra has a very powerful proprietary trigger language called CL, and the CL engine runs inside the database server: this allows it to run within the transaction as changes are made. It is possible to associate different pieces of code (or ‘methods’) with various events, such as the creation of a record in a table, the deletion of a record, and the changing of individual attributes. The combination of CL and the ability to have derived tables means that you can consider a Polyhedra server as an object-oriented programming environment with persistent storage that is accessible via SQL.

We will see some of the power of CL by looking at the methods we attach to the input table:

script input


    on  setvalue

        -- set value, taking into account whether high or

        -- low means true.

        Set timestamp to now()

        if not exists raw_value then

            set value to null

        else if low_is_true then

            set value to not raw_value


            set value to raw_value

        end if

    end setvalue


    on  set raw_value

        -- propogate the raw value into value, taking into account

        -- whether we need to wait for the state to settle.

        if settle_time = 0 then

            setvalue ()

        else if not settling then

            setvalue ()

            set settling to true

            send wait_a_bit () to me -- set something off in a new CL thread

        end if

    end set raw_value


    on  wait_a_bit

        -- handle settling time.

        delay settle_time     -- this suspends the CL thread, but not the transaction

        -- (by now, we will be in a new transaction!)

        set settling to false

        setvalue ()

    end wait_a_bit


    on  set value

        set timestamp to now ()

    end set value

end script

(In CL, -- flags that the rest of the line is a comment. CL also supports /* … */ which can be used for multi-line comments.) The script <tablename> … end script lines specify that the enclosed methods apply to the indicated table. In this case, we have four methods, two of which are parameter-less procedures (setvalue and wait_a_bit), and the others are ‘on set’ handlers, which will be discussed shortly. Let us first look at the setvalue method, whose body consists of the following CL statements:

        set timestamp to now()

        if not exists raw_value then

            set value to null

        else if low_is_true then

            set value to not raw_value


            set value to raw_value

        end if

The first line instructs the CL engine to set the timestamp attribute of the record being changed to the current date and time.

The next two lines check whether raw_value is set to NULL, and if so sets the value attribute to NULL. The rest of the methods just sets value to raw_value or its inverse, depending on whether low_is_true is set.

We now turn to the on set raw_value method. ‘On set’ handlers such as this are automatically run whenever the named field is changed, and run within the context of the changed record. For efficiency, they are not triggered when something specifies a new value for the cell, and the cell already contains this value. Unlike some trigger languages, CL is run mid-transaction as the attributes are altered, and the triggers cascade: if any CL method alters an attribute that has its own ‘on set’ handler, then that handler is immediately invoked in-line.

In this case, if settle_time is zero (or NULL), then the method simply triggers the setvalue method; in other cases, the function uses the settling flag and the wait_a_bit method to ensure that (a) once a change has come through, no further changes will be propagated through to the value field until the settling time has passed; and, (b) at the end of the settling time, the setvalue function is called (again) to ensure that the value field is set correctly given the then-current values of raw_value and low_is_true. The ‘magic’ comes from the delay statement in the wait_a_bit. In essence, the CL thread of execution is suspended for a certain number of milliseconds, while allowing the current transaction to conclude; at the end of the delay, a new transaction is initiated by the CL engine to handle the resumed thread. While it is rare to need this CL feature, when appropriate it can simplify the code significantly.

The final method is on set raw_value. This simply sets the timestamp field to the current date and time whenever the value is changed. We could have incorporated this into the on set raw_value method, but it is simpler to do it as a separate handler –and thus easier to maintain.

Reserved points

Before moving on to discuss the application that uses the input and output tables, we shall just point out that for safety we ought to ensure that only sensible records occur in these tables. As they are both derived from the same base class, we are already guaranteed we cannot have records in both tables with the same id (and thus referring to the same GPIO line), but we out also to ensure the GPIO line numbers are reasonable and do not clash with lines which already are reserved for other purposes. It is easy to ensure the id lies within a particular range by adding suitable methods on the iopoint table, but reserving lines that are otherwise valid is best done by adding another table…

create table reserved

   ( derived from iopoint


-- create some records for pins 14 and 15, which are set to UART mode

-- by Linux. Best if we don't touch them.

insert into reserved (id, type) values (14, 'reserved');

insert into reserved (id)       values (15); -- no need to define type here!


The polygpio application

A complete copy of the source code of the application – plus instructions on how to build and run it – can be found in in the appended ZIP file - and also in Appendix A of the PDF file. In this section, we shall just give an overview, plus some significant code snippets showing some details of its operation.

The application is written in C, and uses the ODBC libraries provided with Polyhedra. These libraries contain some Polyhedra-specific enhancements, primarily to enable the library to be used in an event-driven fashion, and therefore allow efficient use of Polyhedra’s active query capabilities. For the most part, the application operates in ‘manual commit mode’, where instructions to change the database are queued up client-side by the library code rather than being sent immediately to the server, awaiting a call of SQLCommit.

The application is split into three main parts: an initialisation phase, an outer loop handling transactions to the database and waiting for a signal that there are some events that need processing, and an inner loop processing the events.

Initial phase

Once the command line parameters have been analysed (to decide which database server(s) it should connect to, and to discover whether it should use fault-tolerant connections), the initial phase performs a number of actions:
  • It instructs the Polyhedra ODBC library to enable event handling

  • It establishes a connection to the database server

  • It establishes an active query using the following SQL:

    select id,value,desired_value,pullup from output

  • The ODBC library is told to enable events for the query, and to bind the result columns to particular memory locations, so that when a row is retrieved from the result set the library will know where to put the values for that row

  • A similar process is followed for the 2nd active query, which in this case is:

    select id,raw_value from input

  • Having established both active queries, we put the connection into manual commit mode (so that requests to update the database are batched client-side until SQLCommit is called), set up the GPIO memory map, and process the initial result sets for the two queries:
  • For each row returned by the query on output, we set up the relevant GPIO pin as an output point, set the pin high or low depending on the contents of the desired_value and pullup attributes, and then ask the database to set the value field to match desired_value (but since auto-commit hase been turned off, this request will be batched by the ODBC library).

  • For each row returned by the query on input, we set up the pin as an input point, read the value, and push it to the raw_value field in the database record (with this being batched, as before)

Outer loop

Once the initial phase is complete, we enter the outer loop, which never exits. The loop has three parts:

  • If there are any pending changes that need to be applied to the database, apply them

  • Invoke the inner loop to process any events that have to be handled (see below). By the time this loop terminates, there may be some batched commands that need to be sent to the database.

  • Check to see if the values in the GPIO input pins match what has previously been reported to the database; if not batch up some requests to update the raw_value field of the relevant record in the database.

  • If there are no batched requests to update the database, call the Polyhedra ODBC function SQLHandleMsg to delay until the library signals that there are some events to process. If we are monitoring any input pins, we set a timeout before entering this function, to ensure we are regularly reading the pins

Inner loop

The inner loop consists of a while statement that invokes a function, with a switch statement acting on one of the values set by that function:

        while (SQLGetAsyncEvent(henv, &functionId, &handleType, &handle) == SQL_SUCCESS)


            switch (functionId)




If SQLGetAsyncEvent returns a value other than SQL_SUCCESS, it is safe to assume that we have reached the end of the list of pending events. In other cases, though we will expect functionId to take one of 3 values:

The database server has been shut down – or the connection was not set up as a fault-tolerant one, and contact with the server has been lost. In either case, the application will tidy itself up and quit.


This will only occur if the connection to the database had been set up to be fault-tolerant, and there has been a change in the status of the underlying connection. There are 4 possibilities:

1.      We have lost the underlying connection to the master server, and the ODBC library will now try to establish a connection

2.      We have now re-established a connection to the master server

3.      The master server now has a standby server running

4.      The master server has lost its standby

In each case, the polygpio application just produces a diagnostic message.


This is a signal that a ‘delta’ has been received for an active query: a quick look at the contents of handle will tell us which query the delta is for. In either case, we can tell what records have been affect – that is, the rows in the result set that have been inserted, updated or changed – and in the case of a changed record we can easily find out which attributes have been altered. In the case of an output record with a changed desired_value field, the client application can cheaply determine just what needs to be done to the GPIO pin.

A worked example: some traffic lights

Having defined some tables (along with some associated CL) and developed a simple client application which will interface between those tables and the real world, the next stage is to put together a simple demo to show off its use.

The hardware

The example we have chosen is an emulation of a pair of traffic lights, controlling different directions through an intersection, with each traffic light represented by a red, amber and green LED. To help control the demo, we’ll add in a couple of switches, which will be used to speed up and slow down the animation. The first version of the demo hardware (without the switches) was put together on a breadboard, but it has since been rebuilt using a CISECO ‘Slice of Pi’ interface board for a more permanent solution:

populated interface card
underside of the interface card the populated interface card
mounted on a Raspberry Pi
everything mounted in
a clear case from Farnell.
Figure 3: the demo hardware, using a Slice of Pi interface card produced by CISECO plc (www.ciseco.co.uk)

The LEDs are connected via a pull-up resistor to the +V line (as in the top diagram of figure 2, earlier), and thus will be turned on when the GPIO pin is set low. The push-buttons are arranged to pull the GPIO lines up to high when pressed, as shown in figure 4 below.

Figure 4: switch circuitry used on Slice of Pi board

The new tables

As all the outputs we shall be using are LEDs (representing individual signals in a traffic light), the first thing we shall do is define a view that makes experimentation easier:

create view light as select id, desired_value value from output;

If you query light rather than output, you will see the same number of attributes, but will only get back two attributes – and one of these is a renamed version of the attribute in the underlying table. It is even possible to make changes through this view, so a command such as…

update light set value = false where id=17; commit;

… will set the desired_value field of the corresponding output object.

We next add two tables, trafficlight and junction to model the hardware, A traffic light (represented by a record in traffic_light) groups together a set of outputs, and has a state which is reflected through into the state of the outputs it owns. Each traffic light is part of a junction, which also has a state, and the state of a traffic light is controlled by the state of the junction and its position within the junction. The following SQL defines the schema representing traffic lights and junctions:

create schema


create table trafficlight

   ( id         integer primary key

   , position   large varchar                    -- for diagnostics

   , state      integer transient                -- controls which colour(s) will be lit

   , colour     varchar (9) transient            -- for diagnostics

   , red        integer not null references output

   , amber      integer not null references output

   , green      integer not null references output


   , owner      integer references junction      -- which junction is it in?

   , sequence   integer not null                 -- an offset in the junction state sequence

   )                                             -- (note that there is no semicolon here!)


create table junction

   ( id         integer primary key

   , state      integer

   , lights     array of trafficlight


;                                                -- (this semicolon ends the 'create schema')

There are two things to note about the above. Firstly, there is a many to one relationship between the tables, which is modeled by having a pointer (foreign key reference) from the trafficlight table to the junction table. However, in our CL trigger code we will want to be able to get from the record for a junction to the traffic lights that belong to the junction; as there will be a variable number of these, we will use the Polyhedra array type. An array of trafficlight is an ordered set of pointers to records in the trafficlight table. If you look at the table using CL the attribute is not visible: it is only there for use by CL code, which can add and remove entries in the set, and iterate over the members of the set.

Secondly, the definition of each table refers to the other table, so we need to tell the SQL engine that these tables are to be created in a single transaction. Polyhedra uses create schema for this.Let us now add some CL code to give behavior to these tables. Looking first at junction:

script junction


    on  set state

        local reference trafficlight tl


        repeat foreach tl in lights

            set state of tl to state

        end repeat

    end set state


end script

The on set trigger iterates over the array of trafficlight pointers, and sets the state of each traffic light to match that of the junction. Turning now to the other table, we have:

script trafficlight


    on  create

        insert me into lights of owner

        checkstate ()

    end create


    on  delete

        set state to null -- to turn the lights off!

        remove me from lights of owner

    end delete


    on  set state

        checkstate ()

    end set state


    on  checkstate

        local integer newstate = state - sequence


        if not exists state then        -- state is NULL, so alll lights off.

            set colour to "off"       

            setstate (false) of red

            setstate (false) of amber

            setstate (false) of green

        else if newstate = 1 then       -- 1 => about to go green; use UK convention.

            set colour to "red/amber" 

            setstate (true ) of red

            setstate (true ) of amber

            setstate (false) of green

        else if newstate = 2 then       -- 2 => traffic may flow: show green

            set colour to "green"     

            setstate (false) of red

            setstate (false) of amber

            setstate (true ) of green

        else if newstate = 3 then       -- 3 => about to go red; show amber

            set colour to "amber"

            setstate (false) of red

            setstate (true ) of amber

            setstate (false) of green

        else then                       -- any other value => stop: show red.

            set colour to "red"

            setstate (true ) of red

            setstate (false) of amber

            setstate (false) of green

        end if

    end checkstate


    on  set owner

        -- to avoid breaking the logic of the application, ban this change!

        abort transaction "sorry, we cannot change the junction of a trafficlight"

    end set owner


end script

The on create handler registers the new object with the junction; the on delete handler undoes this. Both the on create and the on set state handlers call setstate() to ensure the right lights are set.

The final method ensures the owner field is never changed, as (without other changes) this would break the logic of the application. (The alternative would be to deregister itself with the old junction, register itself with the new one, and then pick up the state of the new junction.) It is good practice to add such triggers when developing the application even if eventually you intend to replace the code that properly handles the change, as it helps ensure a degree of consistency in the database in the meantime. In fact, you can use abort transaction freely in your CL code, wherever you have functions that are not yet fully implemented!

The alert reader will have spotted that the database is far from normalized, in that, for example, the desired state of a traffic light can be determined from other information already present in the database. The principle of normalisation would imply that we should remove the redundant attribute, but for Polyhedra databases it is often better to have a degree of denormalisation (to simplify queries, and to simplify the trigger code). Thus, it might be better to add some lines into the on set state handler of the trafficlights class to ensure the state matches that of the owning junction object:

    on  set state

        if (exists state) <> (exists state of owner) \

        or state <> state of owner then

            abort transaction “wrong state for trafficlight” && id

        end if

        checkstate ()

    end set state

Database population

Having defined the schema and attached trigger code, the next step is to create some records. For the hardware pictured above, the SQL to do this is fairly simple:

insert into junction (id) values (1);


-- north traffic light

insert into output (id, pullup) values (17, true); -- gpio 0

insert into output (id, pullup) values (18, true); -- gpio 1

insert into output (id, pullup) values (27, true); -- gpio 2 (earlier boards used 21)

insert into trafficlight (id, position, red, amber, green, owner, sequence)

                  values (1,  'North',  17,  18,    27,    1,     0);


-- east traffic light

insert into output (id, pullup) values (22, true);

insert into output (id, pullup) values (23, true);

insert into output (id, pullup) values (24, true);

insert into trafficlight (id, position, red, amber, green, owner, sequence)

                  values (2,  'East',   22,  23,     24,     1,     4);


We have set one junction to have a sequence number of zero, and the other to have a sequence of 4; if the state of the junction cycles from 0 to 7 and then back to zero, we should see the lights changing as follows:

Junction state

North-South lights

East-West lights





























Assuming the polygpio client application is running, we can now turn on all the LEDS by a command such as update output set desired_value=true (or, using the view we defined earlier, the command update light set value=true). If we wanted to tune an individual LED on or off where could use a where clause: update light set value=false where id=17.[1] More relevantly, we can get the junction to cycle through the valid states by repeatedly using the following commands:

update junction set state=-1 where state=7; update junction set state=state+1; commit;

One can readily imagine having an external application performing such an action on a timed basis, but in the next section we shall show how we can use code inside the database to do this.

Animation code

What we shall do is add two new tables,  animator_button and animator (plus some CL code) to animate the demo. The animator table will be derived from an inbuilt table called timer which can be configured to beriaodically increment a counter attribute; we can attach CL code to this attribute that will update the state of a junction. The animator_button table will be derived  from input, and will add an attribute pointing at an animator; one button will speed up the animation (by decreasing the interval between ticks) whereas the other will slow it down. Pressing both together will stop the animation, which is resumed when the next button press occurs. As usual, the first thing we do is to define the tables:

create schema


create table animator_button

   ( derived from input

   , owner integer not null references junction_animator



create table junction_animator

    ( derived from timer

    , jn          integer not null references junction

    , times       large varchar not null

    , tick        integer transient

    , up_button   integer references animator_button

    , down_button integer references animator_button


The times attribute will control how many ticks the junction will stay in each state. The SQL to populate these tables is:

-- create an animator whose counter attribute is incremented every 250 milliseconds

insert into junction_animator (id, active, continuous, interval, ticktime,

                               jn, times)

                       values ( 1, false,  true,       250,      1000,

                               1, '2,10,2,1,2,5,2,1');

-- add two buttons to the animator; the first will speed it up, the 2nd slow it down

insert into animator_button (id, owner, low_is_true, settle_time, type)

                     values ( 4, 1,     true,        70,          'animator button');

insert into animator_button (id, owner, low_is_true, settle_time) values (25, 1, true, 70);


The CL code for the animator buttons is straightforward, as it simply has to tell the animator when it is created, and when the value changes:

script animator_button

    on  create

        add_button (me) of owner

    end create


    on  set value

        set timestamp to now ()

        if value then button_changed (me) of owner

    end set value

end script

The animator just has three methods: one to handle counter (an inherited attribute) being incremented, and two procedures that will be invoked by its buttons. Let’s look first at add_button, which takes as a parameter a pointer to the button that invoked the function:

    on  add_button (animator_button b)

        if not exists up_button then

            set up_button to b

        else if not exists down_button then

            set down_button to b


            abort transaction "too many buttons - I can't cope!"

        end if

    end add_button

This just says the first button to register itself will be treated as the ‘up’ button (which speeds up the animation), the second is down, and the third is enemy action. The button_changed method is rather more complex, as it enables the animator if it is currently disabled, disables the animator if the other button is already pressed, and in other cases decreases to interval by 2/3rds or increases it by one half:

    on  button_changed (animator_button b)

        local integer                   i 

        local reference animator_button b2


        if not active then – enable the animator

           set active to true


            -- (which button was pressed?)

            if b = up_button then

                set i  to (interval * 2) div 3

                set b2 to down_button


                set i  to (interval * 3) div 2

                set b2 to up_button

            end if


            -- is the other button pressed? if so, disable the animator

            if exists b2 then

                if  value of b2 then

                    set active to false

                    set i to interval -- (don't change the speed)

                end if

            end if

        end if


        -- update the interval, but keep it within a sensible range.       

        if i <> interval and i > 1 and i < 100000 then

            set interval to i

        end if

    end button_changed

That just leaves the on set handler, which treats the times attribute as a comma-separated list of integers which denote how many ticks the junction should spend in each state.

    on  set counter


        local integer maxticks = 0

        local integer maxstates

        local integer newstate = state of jn


        if tick > 1 then

            subtract 1 from tick


            set maxstates to numberofitems (times)

            if maxstates > 0 then

                repeat for maxstates

                    set newstate to 1 + newstate mod maxstates

                    set maxticks to chartonum (item newstate of times)

                    if maxticks > 0 then exit repeat

                end repeat

            end if

            set state of jn to newstate

            set tick to maxticks

        end if

    end set counter

You should note that we have not had to alter the polygpio client application, even though we have added extra tables; it does not need to worry that we have derived tables from the ones in which it is interested. Likewise, the tables we have added do not need to worry about what causes events to occur (such as a change in the value field of an animator_button), they are only concerned about handling the event appropriately. This leads to a defensive style of programming which makes for maintainable and re-usable code.


A database is an ideal place for multiple applications – or multiple components of a larger application – to share data. When the database is fast and has features such as active queries and a fine-granularity trigger language that allows much of the ‘business logic’ to be embedded in the database, it becomes appropriate for real-time use, such as controlling and monitoring the GPIO lines of a device – especially as the code that actually handles the GPIO lines (which may need special permissions to run) can be minimised. Add in the table inheritance capability of Polyhedra, it becomes possible to build up the application in stages, in an object-oriented fashion; as each stage will only be responsible for a small amount of new functionality it becomes easier to get it right, and have a system that is easy to develop and maintain.

[1] This is possible as there is no code that says that the state of an output must correspond to the state of the traffic light of which it is a part; instead, we just ensure that a change in state of a traffic light causes the individual lights to be set appropriately. A more complete implementation would derive an extra table from output called, say, lightbulb, with an extra attribute pointing at the trafficlight record, and another indicating its colour: this would allow us to add CL code to ensure each traffic light had appropriately-coloured bulbs and that for each bulb the state was right for the state of the junction.

Nigel Day,
7 Feb 2013 10:32
Nigel Day,
7 Feb 2013 10:15