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.)
Polyhedra is a family of relational database management systems designed from the start for use in embedded applications. It has two main flavours: Polyhedra Flash DBMS 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 Flash DBMS 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:
Polyhedra servers can operate stand-alone, with each instance handling a separate database, and in addition both Polyhedra IMDB and Polyhedra Flash DBMS 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 Flash DBMS 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.
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.
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.
The basic idea is
to have some tables in the database that will be monitored by a special client
application, which we shall call
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:
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:
(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.
As indicated earlier, we derive this table from
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,
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:
(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:
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.
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.
Once the initial phase is complete, we enter the outer loop, which never exits. The loop has three parts:
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:
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.
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.
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:
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
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:
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…
… 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:
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
The final method ensures the
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
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:
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:
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.
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:
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:
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
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
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.
 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.
How-to Guides >