Free real-time relational database for the Raspi


16 posts
by Nigel Day » Mon Oct 15, 2012 12:23 pm
Polyhedra is a high-speed real-time database, used in embedded systems as diverse as cellular base stations, water treatment plants, and remote meter reading networks. A free version is now available for the raspberry Pi, for use on Raspian. Links to the downloads can be found on the http://www.enea.com web site, or on the Polyhedra Lite page on the Polyhedra developer web site: http://developer.polyhedra.com/webcasts/polyhedra-on-the-raspberry-pi

There is also a little demo of Polyhedra Lite running on the Raspberry Pi, controlling some LEDs connected to the GPIO lines. Only a small client application is run using sudo (so that is can access the GPIO), with everything else running in user space; reducing the amount of code running with superuser privileges is always sensible! As the trusted client uses Polyhedra's active query mechanism to avoid polling the database, system load is kept low, and Polyhedra is capable of thousands of transactions per second. You can see a recording of the demo at http://developer.polyhedra.com/webcasts/polyhedra-on-the-raspberry-pi
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by gregp » Fri Apr 12, 2013 11:22 am
I have watched the video and really like what Polyhedra can do.

Is there perhaps an install procedure anywhere that I can follow to get it set up and running. I have had a look at http://developer.polyhedra.com/how-to-guides/controlling-the-gpio-lines-of-a-raspberry-pi guide but from what I can see it doesn't describe how to get Polyhedra installed on the Raspberry Pi.

Do you have any links that can help me out?

Thanks
Posts: 25
Joined: Wed Aug 29, 2012 2:23 pm
by Nigel Day » Fri Apr 12, 2013 12:00 pm
Just unpack the tar.gz file for the Polyhedra release kit into, say, your home directory. Then you have a number of choices: probably the easiest is to copy the contents of poly8.7/linux/raspi/bin into your ~/bin directory, or put symbolic links to them in that directory:
Code: Select all
cd ~/bin
ln -s ~/poly8.7/linux/raspi/bin/* .
The other way is to add ~/poly8.7/linux/raspi/bin to your command path, either by executin a command such as
Code: Select all
export PATH=~/poly8.7/linux/raspi/bin:$PATH
before you want to use any of the Polyhedra executables, or by adding such a line to your ~/.profile file.

You don't have to use root privileges to do any of the above. An alternative approach is to put the Polyhedra executables in a standard place:
Code: Select all
sudo cp ~/poly8.7/linux/raspi/bin/* /usr/local/bin

(This would be the sensible approach on a multi-user machine, but can be considered overkill on a raspberry pi!

If you want more details, see the installation guide and the evaluation guide, both of which are provided as .pdf files in the installation kits as well as being available in the manuals section of the Polyhedra developer site: http://developer.polyhedra.com/manuals.
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by rurwin » Fri Apr 12, 2013 12:09 pm
It does... eventually.

http://developer.polyhedra.com/webcasts ... spberry-pi has a link to http://developer.polyhedra.com/products/polyhedra-lite which has a section about obtaining it, and that has a link to http://www.enea.com/software/resources/ ... -Software/ where you can select any of the Polyhedra Lite verisons including the one for the RaspPi here: http://www.enea.com/software/resources/ ... spberryPi/

Then you need to fill in a registration form, after which you will be immediately given a download link (the form says you may have to wait for 72 hours, but you don't.)

The download is a .tar.gz file and you can just unpack it in your home directory on the pi.

There is a PDF file with installation instructions, although it somewhat forgets about the RaspPi. Follow the instructions for Unix and Linux.

Edit: Nigel beat me to it with more detailed instructions.
User avatar
Forum Moderator
Forum Moderator
Posts: 2933
Joined: Mon Jan 09, 2012 3:16 pm
by Jim Manley » Sat Apr 13, 2013 3:35 am
As Arte Johnson on "Rowan and Martin's Laugh-In" used to say, "Velly Intellesthting!" I'm always looking for decent DBMS implementations and I'd heard of Polyhedra, but never got a chance to work with it. Anyone happen to know what flavor of query language it supports, e.g., ANSI SQL or an extension, Oracle SQL, all of the above, none of the above, etc.?
The best things in life aren't things ... but, a Pi comes pretty darned close! :D
"Education is not the filling of a pail, but the lighting of a fire." -- W.B. Yeats
In theory, theory & practice are the same - in practice, they aren't!!!
User avatar
Posts: 1357
Joined: Thu Feb 23, 2012 8:41 pm
Location: SillyCon Valley, California, USA
by Nigel Day » Sat Apr 13, 2013 12:13 pm
Polyhedra Lite is an in-memory SQL database system using the client-server model. It's a cut-down version of SQL, with some extensions (a) to allow you to 'derive' one table from another, and (b) to allow you to specify whether individual tables are persistent or transient. (Transient data is the stuff that does not need to be preserved if the database server is shut down and restarted - for example, the latest reading from sensors.) You can find out details on the site we've set up for developers who are wanting to use Polyhedra in their application: http://developer.polyhedra.com. Yes, I said 'we', as I am the product manager for Polyhedra and one of the original development team.

Polyhedra 8.7 has just been released, and adds locking and cascaded deletes. We have also added an ODBC driver for use on the Raspberry Pi, which can be downloaded from the developer site: This makes it easy to use Polyhedra from PHP and Python; I'll be posting more info on how to do that on the developer site shortly - but here is a sample PHP program you could embed in a web page:

Code: Select all
<?php
$database = "8001";
$conn     = odbc_connect ("$database", "", ""); // (2nd & 3rd arguments are name, password)
if ($conn)
{
    $result = odbc_exec ($conn, "select code, usdollar value from currency");

    echo "<table cellpadding=\"2\" cellspacing=\"2\">\n<tr bgcolor=\"steelblue\">   ";

    // print field names
    $colCt = odbc_num_fields ($result);
    for ($j=1; $j<=$colCt; $j++)
        echo  "<th> " . odbc_field_name ($result, $j) . " </th>";
    echo "</tr>\n";

    // scan the result set
    while (odbc_fetch_row ($result))
    {
        echo "<tr bgcolor=\"lightskyblue\">";
        for($i=1; $i<=$colCt; $i++)
            echo "<td> " . odbc_result ($result,$i) . " </td>";
        echo "</tr>\n";
    }

    echo "</table>\n";
    odbc_close ($conn);
}
else echo "\nERROR: could not connect to database \"" . $database . "\".\n\n";
?>
... and here is a Python program doing roughly the same thing:
Code: Select all
import pyodbc
cnxn = pyodbc.connect('DSN=8001')
cursor = cnxn.cursor()
for row in cursor.execute("select code, usdollar from currency"):
    print ('Currency code', row.code, '- 1 US Dollar buys', row.usdollar)
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by sej7278 » Sat Apr 13, 2013 5:55 pm
so what makes this better than sqlite for example? its not free as in speech, just beer, so shareware really.
Posts: 237
Joined: Tue Jan 22, 2013 6:43 pm
by Nigel Day » Sat Apr 13, 2013 6:24 pm
I'll try not to give a marketing pitch here, but Polyhedra Lite is free as in beer for personal use, subject to some restrictions on distribution.*

how does Polyhedra compare with SQLite? Well, unlike SQLite it is client-server; this protects your database from programming errors in your application, and means many clients can connect to the database simultaneously. Polyhedra also allows the clients to be remote - so an application on Windows can access a database running on your Pi, for example. Also, unlike SQLite it is fully typed (no putting a string in a field designated as a real value, for example), and designed for in-memory use: this means it can do thousands of transactions per second. Polyhedra also allows you to attach code written in a 4GL language directly to your database, and much of the 'business logic' of your application can be specified like this.

It's horses for courses, of course. If you need a high degree of data integrity, then the strict typing, strict enforcement of referential integrity, client-server separation, and the ability to attach trigger code will mean Polyhedra is more appropriate than SQLite. If you need remote access, then Polyhedra will give you this - along with the ability to alter the schema on the fly (adding columns to tables, for example). If it is only a single application that wants to access the database, or you want to handle bigger databases than can fit in RAM, then SQLite may be more suitable. And if you want to patch the database management software, you can get the source code for SQLite.

(* basically, Polyhedra Lite is free for in-house use, but if you want to distribute Polyhedra license as part of a commercial application, you will need to pay a fee. If you want to distruibute as part of a freeware package, get in touch with us first. The full versions of Polyhedra IMDB adds fault tolerance and the ability to handle huge volumes of time-series data, and are available on many more platforms - but you need a commercial license for those.)
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by Jim Manley » Sun Apr 14, 2013 7:21 am
OK, so I installed Polyhedra on a 512 MB Pi and took it for a spin. Works well and pretty much as expected once I figured out where running the demos is explained. The file where I finally found the info is called "evaluate.pdf", which is literally the last place I would have checked. I would have expected to find where to learn how to run the demos in the poly8.7/readme.pdf file, or even better in the poly8.7/examples/readme.txt file.

The client-server model is guaranteed to stump people who are younger than the WWW, but the flexibility does provide advantages that are obvious to those who teethed on that model a few decades ago, or more. The web client is particularly useful and appreciated, and the PHP and Python examples above demonstrate how similar access is to that for other DBMSs.

I'm sure ENEA is aware and will soon update them, but the version covered in the http://developer.polyhedra.com/manuals pages is 8.6, not 8.7. As noted above, the Pi version isn't mentioned in the docs beyond a news page on the web site, but the Linux/Unix commands do work (Debian/Raspbian requires setting of the PATH environment variable using the export command, not setenv, for newbies who may be wondering).

I found the interspersed instructions for Windows, Linux/Unix, OSE, etc., to be distracting to a fault. I know that it's being done to maximize the probability that instructions will be consistent across platforms, which makes it easier on the developers and/or docs editors, but at the expense of a much larger number of users. This is a continuing problem as new developers enter the market who don't understand what life is like for users - but ENEA appears to be 20 years old and should have learned this long before now (I hope I hear the sound of whomever is assigned to be in charge of user experience either getting spanked or told to find something else to do).
The best things in life aren't things ... but, a Pi comes pretty darned close! :D
"Education is not the filling of a pail, but the lighting of a fire." -- W.B. Yeats
In theory, theory & practice are the same - in practice, they aren't!!!
User avatar
Posts: 1357
Joined: Thu Feb 23, 2012 8:41 pm
Location: SillyCon Valley, California, USA
by Nigel Day » Sun Apr 14, 2013 12:27 pm
Jim,

I'm glad you were able to get Polyhedra lite working. Re the client-server model, it is in essence the same as that used by Oracle, SQL Server, MySQL, etc, so should be familiar to many people with database experience on Windows, Linux, and Solaris - but I realise those using the raspberry Pi may be more familiar with the private database approach from SQLite. The client-server approach allows a big application to be broken into many parts, with separate clients taking responsibility for part of the overall application - this makes things easier to develop and maintain, once one has got past the small hurdle of the need to start up the database server before use (and shutting it down again later, if it is not to be left running indefinitely).

Thanks for pointing out that the developer site still referred to Polyhedra 8.6 in many places; I'm working on correcting this, though we had already updated all the PDF versions of the reference manuals to the latest release before Easter, when 8.7 was released. Re the manuals referring to multiple platforms: basically, we have only a small team of developers working with Polyhedra, and the functionality is essentially the same across all platforms, and there are only very minor differences between the way Polyhedra is invoked on each of these platforms. We'll look at making it easier to read the manuals in the next release, but most of our developer effort is concentrated on improvements in functionality and performance, and of course fixing bugs in the software when they are discovered: I have no intention of spanking them for this. I will, however, revise the developer site to point people more firmly at the evaluation guide (which would be better called the familiarisation guide).

Nigel
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by gregp » Mon Apr 15, 2013 5:14 am
Thanks for all the help guys. Been a bit of a busy weekend so haven't managed to give it a try again but will hopefully get a chance this week.

Will post back once I have it working and hopefully do a blog about it to help others.
Posts: 25
Joined: Wed Aug 29, 2012 2:23 pm
by Jim Manley » Tue Apr 16, 2013 12:55 am
Hi Nigel

I understand the trials and tribulations of developers doing mulltiple jobs in a small company - I've been working in startups ranging in size from me to a couple of handfuls of people starting out during the past couple of decades. I'm trying to point out to developers that what may have been good enough for a more technically savvy market may not be appropriate in the Pi market because its primary goal is education of complete beginners in many cases.

Since I'm on the front line supporting users at varying levels of expertise ranging from "What can I use for a power source?" to "What is the terminal air velocity of a sparrow?", that's why I can seem cranky about things like documentation (at least you provide it and it's fairly straightforward). I was only kidding about the spanking, but I know of people who can be contracted for that sort of thing should it ever become necessary :D

The Pi is certainly not going to be useful as a multi-user database server (Polyhedra or otherwise) in the traditional sense for more than a few users with lightweight demands, especially given the Pi's constrained RAM and Polyhedra's RAM-based model, but it can primarily serve as a transition mechanism to systems more appropriate for such uses. I would like to test it to see what it can handle if someone else hasn't already.

For small-scale projects that may grow, Polyhedra may be very appropriate, I assume that's the reason why the Lite version is offered for free, and that's greatly appreciated so we can try it out. I haven't made my way through all of the documentation yet, but if Polyhedra data can be readily distributed across multiple Pi systems without too much muss and fuss, that would be very interesting and potentially useful not only for educational purposes, but possibly real-world use.

Thanks again, and All the Best,
Jim
The best things in life aren't things ... but, a Pi comes pretty darned close! :D
"Education is not the filling of a pail, but the lighting of a fire." -- W.B. Yeats
In theory, theory & practice are the same - in practice, they aren't!!!
User avatar
Posts: 1357
Joined: Thu Feb 23, 2012 8:41 pm
Location: SillyCon Valley, California, USA
by Nigel Day » Tue Apr 16, 2013 10:24 am
Jim, as you say, it is unlikely that Polyhedra Lite would be used for a multi-user database on the Pi - but a multi-client database, that's another matter (especially as the clients could be on another Pi, or a Windows box or a Linux workstation). So some clients can be feeding readings into the database, others could be monitoring it (perhaps using Polyhedra's active query mechanism). Polyhedra-based applications have been deployed on systems with as little as 6MB or RAM, so the 256MB of RAM on older Pi boards seems positively decadent.
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by rurwin » Tue Apr 16, 2013 11:57 am
What are your ball-park commercial license prices for the full-version development and runtime?

I'm not looking for a quote; I'm wondering whether I should flag it up as an interesting technology, or whether it is way out of our league.
User avatar
Forum Moderator
Forum Moderator
Posts: 2933
Joined: Mon Jan 09, 2012 3:16 pm
by Nigel Day » Tue Apr 16, 2013 2:44 pm
rurwin wrote:What are your ball-park commercial license prices for the full-version development and runtime?
Pricing for Polyhedra Lite for Raspberry Pi for commercial applications has yet to be determined, but will be kept low. For non-commercial distribution, we want to be kept in the loop, but intend to grant the distribution rights for free based on formal requests.

Pricing for the full version of Polyhedra on other platforms depends on the number of developers and the number of deployments. Annual support fees will depend on the number of developers, not the number of deployed systems.
Posts: 19
Joined: Sat Jun 16, 2012 2:15 pm
by Jim Manley » Wed Apr 17, 2013 12:24 am
Nigel Day wrote:Polyhedra-based applications have been deployed on systems with as little as 6MB or RAM, so the 256MB of RAM on older Pi boards seems positively decadent.

As a veteran of the days when more than 16 KB of core memory in mainframes and minicomputers, and more than 256 bytes of static RAM in the earliest microcomputers was decadent, I can luxuriate in a 6 MB environment! I sometimes imagine myself going back in time to the engineering lab where I worked on an Evans & Sutherland Picture System 1, with a million dollars' worth of 1 MB of static RAM in a box a cubic yard in volume, that was tied to the Mass Bus of a PDP-11/70 with a whole 32 KB of core.

I would have with me my smartphone with 64 GB of flash memory, 512 MB of RAM, a 1+ GHz 32-bit processor, WiFi, etc., which would be worth around 100 billion dollars in those mid-1970s dollars if you could even wire up equivalent technology of that period, that would occupy at least a city block of infrastructure, probably within multiple stories. I would excitedly exclaim its capabilities to the military folks around me and they would be giving each other "the look" that the guys at Roswell must have exchanged. As I was being carried off on a stretcher securely restrained from harming myself or others, I would still be yelling, "Wait! Wait! I haven't shown you the WiFi, yet! Oh, that's right, there aren't any access points here ... OK, let me show you TCP/IP over the 4G digital cellular network! Hmmm ... the ARPANET only exists at four university labs in California and Utah over 50 KB/sec AT&T leased data lines, and TCP hasn't even been invented ... "

The smartphone would eventually make its way to the computing equivalent of Area 51, probably in a basement lab of an NSA (No Such Agency) group, while I might eventually show up at the old Agnews asylum in Silicon Valley and neither would ever see the light of day again ...which is probably for the best for all concerned! :D
The best things in life aren't things ... but, a Pi comes pretty darned close! :D
"Education is not the filling of a pail, but the lighting of a fire." -- W.B. Yeats
In theory, theory & practice are the same - in practice, they aren't!!!
User avatar
Posts: 1357
Joined: Thu Feb 23, 2012 8:41 pm
Location: SillyCon Valley, California, USA