Polyhedra 8.7

Polyhedra 8.7 was released in March 2013, and brought two new headline features to Polyhedra IMDB and Polyhedra Flash DBMS: locking and cascaded deletes.

Locking

In principle, database locking allows an application to claim part or all of the database, so that others cannot make changes while the client with the lock is deciding on the changes it wants to make. Earlier versions of Polyhedra did not allow locking, providing instead a way of attaching trigger code that can automatically perform sophisticated integrity checks (and abort ‘bad’ transactions, or perform corrections and knock-on actions as part of the transaction). However, there are circumstances where users do not want to attach trigger code nor adopt in-house convention to implement an application-specific locking mechanism, so we have now added locking capabilities to Polyhedra – but we have been careful to do this in a way that is appropriate to the needs of real-time applications.

Technically, the simplest way of providing locking is to allow the client to grab the whole database – but this is too heavy-handed an approach for most purposes, so many databases systems allow clients to be more selective in their locking, down to the level of individual tables or even individual records in a table. Polyhedra goes one better than that, and it is possible to lock individual fields of a record. This level of granularity is critical for real-time systems – for example, if you have a table holding information about analogue sensors, it allows one client to lock the configuration fields for a record without stopping other applications updating the value field as changes are reported. As a further safeguard, Polyhedra allows locks to be specified as pessimistic or optimistic (which controls which transaction fails if someone tries to alter a locked piece of data) and can automatically convert pessimistic locks to optimistic locks after a short period. And, of course, the new locking mechanism is integrated into Polyhedra’s user-based security system, so the database administrator can control which tables (and which attributes of a table) someone can lock.

Cascaded deletes

Like many database systems, Polyhedra allows the database administrator to define cross-references between tables, by saying that a field is a ‘foreign key’ to another table in the database. (It is also possible to reference the same table, or to define references to tables with multi-column primary keys.) Polyhedra also enforces referential integrity – which means that if the value in a record for a foreign key attribute is non-null, then the record pointed at must exist. Earlier versions of Polyhedra enforced this by saying that any attempt to delete a record that was pointed at by another record would fail, but Polyhedra 8.7 introduced a more flexible solution. In essence, the person creating the tables containing the foreign keys can say what should happen when the referenced record is deleted: instead of the default behaviour of failing the transaction, the reference can be set to null, or the record containing the reference can be deleted – a ‘cascaded delete’.

(Polyhedra also copes with the case where the primary key of a referenced record is altered – although it is ‘bad form’ to alter the primary key, the SQL standard does not rule out this action. Previously, Polyhedra would propagate the change into the record containing the reference; this is still the default action, but it is now possible to say the reference should be nulled, or the transaction should be aborted.)

Other changes

Polyhedra 8.7 also introduced an ODBC driver for use on the Raspberry Pi, in conjunction with unixODBC; this make it easy to write PHP and Python programs that interact with Polyhedra. You will, of course have to ensure that unixODBC is installed on your system (sudo apt-get install unixodbc), and, for Python programs, you will also need pyodbc (sudo apt-get python-pyodbc). A copy of the driver for use with Polyhedra Lite can be downloaded from here.