The Intellog Blog

Most instances of Feed refer to a hardware sensor, and stores the periodically collected sensor values in related instances of Poll.  Some instances of Feed, however, are used to store information resulting from a calculation of some sort.  Initially, this is limited to the aggregation of values from several instances of Feed once the sensor values have been transformed to actual values with a Correlation-based lookup.  The relationship of which instance of Feed is the aggregator, and which instances of Feed are being aggregated, is maintained through the use of the newly-created FeedDetail table. 

In a given instance of FeedDetail, the id_Feed attribute identifies the aggregator Feed, whereas the id_Feed_related attribute identifies the feed being aggregated.  id_Feed must be populated, because all FeedDetail instances must be related to a Feed.  On the other hand, id_Feed_related is only populated when one Feed has a relationship to another.  In the future, other types of FeedDetail instances will be introduced which won’t have related instances of Feed, but rather, will have some other sort of calculation logic that needs to be applied.

Speaking of Correlation-based lookup, the first step for the logic was to create the PairRange view.  This takes subsequent instances of Pair, and creates lower and upper boundaries with them.  For instance, if xAmt and yAmt for two successive instances of Pair were populated with 0, 0 and 10, 15 respectively, then PairRange would have a single row with lowerXAmt, upperXAmt set to 0 & 10,  and lowerYAmt and upperYAmt set to 0 & 15 respectively.  This enables the easy comparison of values with Poll.amt to see where a particular value falls. The instance of PairRange also contains enough information to enable straight line interpolation between the lowerYAmt and upperYAmt.  For example, if Poll.amt is 4, the yAmt can be interpolated by taking 4 / (upperXAmt - lowerXAmt) * (upperYAmt - lowerYAmt) + lowerYAmt.  To complete the example, 4 / (10 - 0) * (15 - 0) + 0 => 0.4 * 15 + 0 which results in 6 for yAmt.

PairRange basically uses H2’s ROWNUM function to assign row numbers to two subqueries of Pair, and then joins them based on SubQuery1.seq - 1 = SubQuery0.seq AND SubQuery1.id_Correlation = SubQuery0.id_Correlation.  It works perfectly well, but does result in odd behaviour though; unless you query this view with a WHERE clause which specifies id_Correlation, it will only ever return rows for the first Correlation.  When H2 processes the view, the first time id_Correlation changes, the clause which is the basis for the join is no longer true, and because it’s not true, it’s also not true for any subsequent row of the view.  In other words, the breakage cascades its way though the balance of the view.  It’s just a little jarring to have things show up when you consciously specify something with a WHERE clause, and for things to disappear when you don’t, which is the reverse of what’s usually the case.

Code Shavings  Many thanks to Sergi Vladykin for his reply to my post on the H2 forum.  For a while there, I figured I was going to have to write some Java code to get around a limitation of H2’s ROWNUM function, but his suggestion worked like a charm and prevented all that.  ♦  One thing which got omitted from the previous post was the necessity for having the H2 Console open in order to have the JDBC code work.  This is because H2 Console starts the instance of H2 to which the JDBC code then connects.  The JDBC code, on its own, is not smart enough to start an instance of H2.

The time had come to tie together the ability to capture data from a sensor using Java, with the database structure described in recent posts.  Specifically, the objective was to poll the temperature sensor at specified intervals, and insert the captured value into Poll. A Sun-provided tutorial provided a great refresher on JDBC.  The only hitch in getting a simple JDBC-based application up and running was forgetting to add the H2 jar file into the CLASSPATH environment variable.

Then, it was necessary to modify OpenIFKitExample.java so it would take a value from the temperature sensor and insert the resulting value into Poll.  Once this was done, it was permitted to run on-and-off for an entire afternoon, which resulted in 8000+ instances of Poll being created.   Keep in mind, though, the value being polled from the sensor required conversion from a sensor value to an actual value.  This was accomplished through a JOIN with Correlation tentatively called writeTest.sql.  To completely close the loop, H2’s CSVWRITE function was used to export the results of writeTest.sql to a CSV file.  The latter was then imported into Excel, which was then used to produce the chart above (click for larger image).

This represents another fairly significant milestone, as the capture and storage of sensor information is fundamental to the overall design.  There are still many other aspects of this capability which need to be fleshed out, but until then, it’s good to know there are no major obstacles to implementing the necessary functionality.

Code Shavings  Some initial investigation of memory utilization was also undertaken.  As best could be determined — using Windows Task Manager, for lack of a better tool — initiating the process took about 15 MB of memory.  One it had been running for a while, the amount of memory crept down until it eventually stabilized around 10 MB while polling the temperature sensor once per second.

The previous post described the implementation of tables to support event scheduling.  The next objective was to establish logic to populate Journal based on descriptive information found in Event.  This resulted in the creation of the following objects;

• EventVerbose  A new database view, initially with two attributes; scheduleUdt and id_Event.  scheduleUdt contains the timestamp (ie. date/time) for the next occurrence of the event identified with EventVerbose.id_Event.  This timestamp is based on the timestamp of the previous occurrence of the event* plus the time period specified by the combination of Event.periodicAmt and periodicTlbl attributes.  For instance, if the previous timestamp was 2009-07-29 12:00:00, and periodicAmt and periodicTlbl were populated with 5 and mi respectively, EventVerbose.scheduledUdt would contain 2009-07-29 12:05:00.  In the case of the very first occurrence of a given event, then the current system time is used instead of the timestamp of the previous occurrence of the event.  (ie. ‘now’ plus five minutes.)
• insertEventSchedule.sql  This is a SQL statement (stored in a text file for the time being) which utilizes EventVerbose to determine when the next occurrence of an event is going to be, and inserts that information into Journal.  But here’s the trick; it only does this if this if the next occurrence of the event does not exist in Journal already.  If the next occurrence of the event does exist, insertEventSchedule.sql knows it should not insert the next occurrence into Journal a second time.  It does this primarily by looking for an instance of Journal which has scheduleUdt populated, but initiateUdt still empty.  In other words, an event has been scheduled to run, but has not yet started to run.
• updateEventSchedule.sql  This SQL statement is the counterpoint to insertEventSchedule.sql.  It finds instances of Journal where scheduledUdt is populated and initiateUdt is empty, and populates initiateUdt with the current system time.  Now, when insertEventSchedule.sql runs subsequently, it can no longer find the criteria for a scheduled event, and therefore inserts the next scheduled event into Journal.

*One would think the ‘timestamp of the previous occurrence of the event’ would be found somewhere in Event.  But one would be wrong.  It’s determined by taking the maximum (latest) scheduleUdt from the Journal table for a given Journal.id_Event.

Continuing the work which was introduced in the previous post, the focus shifted to matters related to the scheduling of events.  One of the main design objectives is to manage the scheduling of all events with one mechanism, regardless of whether that’s polling sensors, transferring data, running maintenance routines or other type of activity.  It appeared the majority of the required functionality could be delivered with three tables;

• Schedule  This table was almost not necessary, as there will likely only be one, more-or-less fixed schedule of events for a given smart box at a given time.  However, for the price of this one table, it’s possible to have multiple schedules defined, each with its own, separate events and related journal entries.  For example, it could be used to maintain seasonal schedules; there might be a separate schedule of events appropriate for spring, another schedule for summer, yet another winter and one for fall.  Schedule also enables a new set of events to be developed and put into service while the previous schedule is kept as a backup*. 
• Event  One or more instances of Event are related to a given instance of Schedule through the id_Schedule foreign key.  Instances of Event contain a name and description of the event, along with information describing the frequency with which the event occurs (eg. one time, versus once per week).  Just one type of Event was identified, initially, which is known as a periodic event.  This is an event which occurs at regular intervals of time, such as once every five minutes, once per day, or once per week. 
• Journal*  One-or-more instances of Journal are related to a given instance of Event through the id_Event foreign key.  An instance of Journal is initially created for the next time an Event is scheduled to occur, based on the information contained in the related Event instance.  The specific time and date is stored in scheduleUdt.  When that time/date is reached, the event process is started and initiateUdt attribute is updated from the system clock.  Once the event process is complete, terminateUdt is updated from the system clock.

Code Shavings  As with Poll, noted in the previous post, Journal may not be the best name, and it’s possible it may yet change.  But it was considered better than the other alternatives, which were History and Log.   History implies everything is in the past, yet Journal contains historical information and the next scheduled occurrence of the event.  Log just seems too generic, or at least could see service in some other part of the application.  ♦  Couldn’t all this be done with the Linux utility cron?  Quite possibly, and that investigation will likely occur at some point in the near future.  cron will likely have some of role to play, even if it’s just to kick off some Java code at specified intervals.  ♦  H2 implements user-defined data types with the CREATE DOMAIN syntax.  This was used to create a TLBL (ie. time label) data type which limits the content to a specific abbreviations for various periods of time.

*However, a fundamental design assumption is one schedule will be active at any one time.

With the database decision out of the way, the job of designing the specific data structures began in earnest.  This not only served to move the application development along, but was a pretty decent test for H2, as well.  In fact, the working assumption was H2 would  do everything expected of a commercial database such as SQL Server, and only in situations where it could not, would that fact be noted.

With database design, it’s very easy to slip into the mode of attempting to capture every imaginable nuance of application functionality.  It’s a profound mistake; you can’t possibly know how the application will evolve over time, and with it, a high likelihood of data structure change.  Why design what you know is going to change in the future anyway?   So, only the structure required to support the easily-visualized, immediate future should be designed.  To wit, here are the first set of tables nailed down;

• Type  Simply maps numeric codes to unique, verbose names.  Rather than having lots of little tables which all basically do the same thing (eg. FeedType, CorrelationType etc.), these tables are reconciled into a single table, which is then grouped through the id_TypeClass foreign key to TypeClass.
• TypeClass  Groups one-or-more instances of Type, and stores unique name and descriptive information about the TypeClass as a whole.  For example, for TypeClass.id = 1, the unique name is Sensor, and through its foreign key relationship with Type, enumerates all the supported sensor types.
• Pair  A set of two values (the attributes xAmt and yAmt) which have some type of relationship to each other that cannot be derived computationally.  For example, if xAmt is the sensor value, then yAmt would be the actual value to which the sensor value equates.  (Likely derived during some sort of calibration process, for example.)  Instances of Pair are grouped through the id_Correlation foreign key to Correlation.
• Correlation  Groups one-or-more instances of Pair, and stores unique names and descriptive information about the Correlation as a whole.  Each Correlation represents the complete set of   empirically derived value pairs belonging to that Correlation.  It’s main, initial use is to provide lookup table functionality.  For example, the Pair instances related to a Correlation can be used to translate sensor values into actual values; the sensor value would be found in Pair.xAmt, and the associated actual value would be stored in Pair.yAmt.
• Poll*  A value, obtained at a known point in time**.  In most cases, this would be the value obtained from a sensor.  (The name makes more sense if it’s put in the context of the phrase "taking a poll of the sensors".)  This connotes looking at a sensor , making a note of the value associated with it, and the time when the value was recorded.  Instances of Poll are grouped through the id_Feed foreign key to the Feed table.
• Feed  Groups one-or-more instances of Poll, and stores unique names and descriptive information about the Feed as a whole.  Each Feed represents the history of values recorded at specific points in time.  Instances of Feed can be further subdivided in two ways; those in which the id_Correlation attribute is populated, and those where it isn’t.  In the former case, the amt attribute of the related instances of Poll represent values prior to their transformation to an actual value with a Correlation-based lookup.  Conversely, when id_Correlation is not populated, the amt attribute of the related Poll instances are already actual values.

One further note about Feed; there are situations where it does not represent values recorded from a sensor.  For example, values from four sensors could be recorded in their own instance of Feed and related instances of Poll.  Each needs to be transformed to actual values with a Correlation-based lookup, and then the actual values aggregated.  The result of this process could be stored in its own instance of Feed, which could be called something like Aggregate Actual Value.  When transferring the data, a decision can be made to simply transfer the calculated aggregate, or the underlying sensor values, or any combination thereof.

*It’s not at all certain that Poll is the best name, but it will do for now, and if something better comes along, it can most certainly be replaced.

**It’s likely that all timestamping will be UTC.

Last time, the need for an application database (as opposed to simply using flat files) was discussed, along with criteria for selecting from the many alternatives.  After a not-so-scientific selection process, here are the results;

Chosen: H2  According to the website, this project is led by  Thomas Mueller, the original developer of Hypersonic SQL, which was a open source, Java-based database project started back in 1998.  It is a complete rewrite of Hypersonic SQL, and "does not share any code with Hypersonic SQL or HSQLDB".  Although it may not be completely objective, the feature comparison provided on the H2 website was the basis of choosing H2 over many worthy alternatives, as well as H2 having met most or all of the other criteria outline in the previous post.

First Alternate: HSQLDB  This is also based on the original Hypersonic SQL database.  According to their website "[i]n March 2001, a number of developers who were using the software for their work got together through the Internet and formed the HSQLDB Development Group to continue the development."  Looks like this will be a great alternative if the H2 decision doesn’t work out for some reason.  It also met most or all of the other criteria spelled out previously.

Future Consideration: Derby, db4o, Ozone, NeoDatis, Perst, One$DB/Daffodil, Neo4j, tinySQL, JODB, AcornDB   Having met the basic criteria, these are all possibilities for the future.  There are simply not the resources available to do a credible, rigorous of evaluating them all at this time.

Eliminated: Berkeley DB Java Edition Redirected to an Oracle page, which implies they control them now;  jiql  JDBC wrapper for cloud-based databases (eg. SimpleDB from Amazon);  Axion  "[E]ssentially dormant since mid-2006" from their website;  Mckoi  "Mckoi is in transition" from their website, dated November, 2008;   Ashpool  Redirected to a site which is currently broken.  Too bad, because their native storage format is XML which is intriguing;   SmallSQL  Based on "Disadvantages"  listed on their site;  Jalisto  Most recent  release September, 2005;  MyOODB   From their website, a "Database, Web and System Framework", which is more than is needed for this application;  Metanotion  Based on "Unfeatures" listed on their home page.

So there you have it.  Now, to download, install and start using H2 to start building the application.  Thanks for reading, and by all means, if you have any questions, comments or any other random thought, please leave a comment below! 

With the ability to collect sensor data as described in the previous post, it was time to figure out where all that data is going to go.  Initially, it seemed simple flat files would handle all of the requirements, with the added advantage of being very simple — much less to go wrong.  But once the application requirements were taken into consideration, it was evident true database capability would be a significant benefit, and a huge development time saver.  For example, the sensor data will be gathered periodically — let’s say every five minutes — but only uploaded to the central server once an hour, or even less frequently.  As a result, it would be necessary to select records based on date since the last successful upload, and then delete the records once they have been successfully uploaded.  Standard stuff for even the most rudimentary database, but a fair amount of code for simple flat files.  Hence, the hunt for a relatively capable, lightweight database suitable to run on an embedded CPU.

It was felt open source, Java-based candidates seemed like a good place to start, and Java-Source.net maintains a list of just such animals*.  In addition to these two high criteria, the rough checklist for which specific product to choose consisted of the following, basically in priority order;

• JDBC support; this is likely a given with any Java-based product, but it’s an absolute must-have, and therefore worth mentioning
• ability to operate in an embedded (ie. resource-constrained) environment
• ability to handle random power-down events while maintaining the integrity of the database and/or providing safe, unattended recovery (ie. without any user interaction)
• solid development history; traceable lineage, organized releases and developer team with a decent track record
• active discussion community; lots of posts to the user forum(s) and an otherwise active discussion about the software which includes the development team itself
• the bigger the install base, the better, along with continuing strong growth
• XML friendly; these days you encounter XML sooner rather than later, and you might as well be prepared for it

No attempt was made to rigorously review all candidate products with respect to the criteria above, but rather, it was used it to identify major contenders for a (somewhat) closer examination.  Or perhaps it would simply correlate the instinctive choice with some quasi-objective criteria so it’s not a completely random event.  But really, how bad a decision can it  be;  if the application code is written with JDBC, it should theoretically be possible to swap out the database for another JDBC-compliant database with little, if any, need to rewrite the code.

*And a bunch of other open source Java applications — so little time, so much neat stuff to look at!

The previous post discussed the use of the Phidget Control Panel (PCP) to interact with their temperature sensor, an exercise which proved the hardware was basically functional.  The subsequent objective was to replace the PCP with Java code, which is intended to be the primary implementation language for the application which will eventually run on the target CPU.  Setting up the Java development environment had a few pitfalls, based on wanting to utilize the phidget21.jar Java library, and the newly-minted Intellog Java library together in one block of source code.  A refresher tutorial on Java packages, and a little help from a Java Errors and Error Handling forum thread got things straightened away.  The short version of the story was that both phidget21.jar and ../Intellog/Java had to be added to the development machine’s CLASSPATH variable. 

Phidgets provides a lots of sample source code on their Programming Resources page, and the first go ’round at connecting to the temperature sensor was based on the TemperatureExample.java.  However, it did not produce the expected result.  In fact, it hung up entirely waiting to connect to the sensor.    Thanks in part to a Phidgets forum thread, it was then I realized I had missed a fundamental point; Phidgets 1000 series sensors plug directly into the USB port of the host computer, whereas the 1100 series sensors are intended to be used with the 8/8/8 interface kit (8/8/8)*.  Once I realized I should have started with was OpenIFKitExample.java, everything went pretty much as expected.  With some minor hacking, it was possible to interact with the temperature sensor from within the Java code.

So, why is temperature sensing important?  Actually, it isn’t — at least not that important.   But temperature is an analogue value, which is being converted to a digital value, which is then readable in application code.  That’s the essential building block; getting a numeric value — any numeric value will do, for now — off the hardware and into the Java world, where it can be displayed, cached in a file, sent somewhere, or taken in whatever direction the application takes in the future.  Or, stated another way, the application now has ears with which it can hear what’s going on in its world.

*For example, the 1051 temperature sensor plugs directly into the USB port, whereas the 1124 temperature sensor plugs into the 8/8/8.  Besides explaining why TemperatureExample.java didn’t work, it certainly explains the difference in price!

Sometimes things work exactly as advertised, and sometimes things are easier than expected.  It’s delightful when both are the case, as it was when the 8/8/8 interface kit (8/8/8) and temperature sensor (described previously) were connected for the first time.  As the picture to the immediately left illustrates (click for larger image), the 8/8/8 is connected to the host computer (a trusty Toshiba laptop, in this case) using a standard USB cable.  The temperature sensor is then attached to the 8/8/8 using the cable provided, which is based on Molex C-Grid® / SL™ connectors.

The Phidget Control Panel (which was installed along with the Phidget21 C library described in the previous post) immediately recognizes the interface and the attached sensor like any other USB device.  It also begins to immediately report the numeric value, post-conversion, from the analogue voltage provided by the sensor.   It’s also possible to specifically identify it as a temperature sensor, in which case, the calculation to convert the sensor value into a ‘real’ temperature value is performed.  There you have it –  41.101° Celsius.

For those who might think 41C is a tad high and therefore suspect, the temperature sensor has been strategically beside the outlet vent on the laptop, which pumps out a steady stream of pretty hot air.  As expected, moving the temperature sensor in and out of the hot air stream caused the temperature value to rise and fall.

There comes a time when you have to put all the theory into practice and begin to assemble components into something that actually works.  The first step was to put together the Java programming environment to interact with the the sensing and control interface described in the previous post.  The basic layout is illustrated in the block diagram to the immediate left.

The main objective is to make the Java application code ‘cross platform’; what’s written in the development environment (most likely running on a Windows PC) should port over to the target CPU without change.  Phidgets provides the tool to do this with the Phidget21 C library.  In the case of the Windows PC, this is delivered in the form of an installable Windows package — specifically, Phidget-x86_2.1.6.20090717.msi downloaded from the drivers page on the Phidgets website.  In the case of the Linux/ARM environment, this library is provided as source code which must eventually be compiled on the target.  The source code can also be downloaded from the driver page.

Phidgets also provide a Java Native Interface (JNI) executable file called phidget21.jar, which effectively wraps the Phidget21 C library so it can be accessed with Java application code.  phidget21.jar file comes with the installer package noted above.  Finally, Phidgets provides some samples of pure Java code illustrating how to access the values being reported by the sensors, and how to initiate controlling events in response to those values.  (These sample folders also seem to come with their own copy of phidget21.jar — it’s assumed they’re the same).  The sample code is a good jumping off point for the application code. 

Code Shavings  One self-imposed constraint is to create the Java code using nothing but Notepad++.  As described in previous posts, this is the best way to make sure nothing is being papered over with respect to what’s actually going on below the covers.  ♦  To ensure the Java development environment was installed properly in the development environment, I went through a quick ‘Hello World’ application, as found on the The Java Tutorials on the Sun website.