Tree-Control Menus: A Case Study in Data Management

The last time we were together, we discussed the first of two common use cases for tree controls: displaying tabular data. This time out, we are going to look at the other major use case: using tree controls as a sort of menu system to control an application’s operation – or at least its GUI.

The Problem We’re Solving

If you look at the testbed application that we have been working on for almost a year, it’s pretty clear that much of the work has been going on “behind the scenes” and not in the GUI. Oh it is nicely modularized thanks to a structure built around a subpanel interface, but the actual controls are really pretty bare bones. A good example of the utilitarian, but unsophisticated structure is the usage of a simple pop-up menu to select the screen to view. Right now it works pretty well because there is only a handful of plugin screens from which we can choose. However, it doesn’t take much imagination to visualize the mess that would result if there were a dozen, or even hundreds of screens available. We need better organization.

Fixing the Data

The biggest conceptual difference between our current goal, and the one we worked on last time is that in our earlier discussion we were displaying data that already existed outside the application. In other words, my disk has directories that contain files and other directories whether or not I chose to create a program that can read and display the directory’s contents. By contrast, the data we are going to be displaying now only exists within the context of our program, or perhaps within the context of our test environment as a whole. One big consequence of this fact is that we a lot more freedom to define the data’s structure and presentation.

For instance, when our testbed runs right now, there are two “acquisition” processes and three “temperature controllers”. Let’s say, for the sake of argument, that the controller functions are dispersed geographically, and what we see on the local interface is status data from three remote processes. In such a situation, we can observe that there is no “correct” way of viewing that overall structure. Depending upon who the user is and what they need to do there are (at least) two ways that these systems could be organized.

One user, might want to see a top-level breakdown that groups systems based on the function they perform. With this approach to organization, you would have sections for “Data Sources” and “Temperature Controllers”. The individual screen would then be grouped under one or the other of those headings:

By Function

Alternatively, a different user might want to see the network resources grouped primarily by each system’s geographical location, with the functions for each site then grouped together like so:

By Location

However, as I said before, neither view is any more “correct” than the other. Therefore. we need to be able to support either one – and any other structure that our customers request, as well. Although this level of flexibility might seem to be a tall order, the truth of the matter is that the tree control’s basic operation is very simple, so all we are really talking about is a matter of data management. Moreover, we already have in our hands the tools we need to accomplish the job. I am talking, of course about our database.

Creating the Data Management Structures

So in defining our data structures, we can start with what we already know: The user needs to be able to select basic menu structures by changing a single value. From this requirement it’s obvious that we’re going to need a table to identify the menu’s basic context. We will then use the values stored in that table to qualify the menu item groupings. Here is the definition for this table, and the three records we are going to insert into it:

CREATE TABLE menu_context (
  id     AUTOINCREMENT PRIMARY KEY,
  label  TEXT(100),
  CONSTRAINT contextlabel_uc UNIQUE(label)
  )
;

INSERT INTO menu_context (id, label) VALUES (0,'NULL');
INSERT INTO menu_context (label) VALUES ('By Function');
INSERT INTO menu_context (label) VALUES ('By Location');

The second table we need to define, will hold the records that describe the actual menu entries. Each record defines one line of the tree control’s contents.

CREATE TABLE menu_group (
  id           AUTOINCREMENT PRIMARY KEY,
  context_id   INTEGER NOT NULL,
  item_name    TEXT(100),
  parent_id    INTEGER NOT NULL,
  sort_order   INTEGER,
  CONSTRAINT context_group_fk FOREIGN KEY (context_id) REFERENCES menu_context(id),
  CONSTRAINT self_ref_fk FOREIGN KEY (parent_id) REFERENCES menu_group(id)
  )
;

As is typical, the data for each record incorporates a primary key that uniquely identifies it. Next, comes a foreign key value that relates each record to one of the menu context values defined in the menu_context table. The last three fields store the data that controls the entry’s appearance in the tree control. The item_name field contains the text that will appear for the item’s entry in the tree control. The parent_id is the ID key for the item’s parent. A key value of 0 indicates a top-level item. Note that this values relates to the id field value in the same table. This sort of self-referential relationship is common when creating tables that are, in essence, linked lists. Finally, the sort_order field defines the order in which the menu entries will be added to the tree control. This last field is necessary because we are storing the configuration data in a database – and as you will recall DBMS make no promises about the order of data in queries unless you explicitly include an ORDER BY clause in the query.

Now that we have a table defining the overall tree control menu structure, we need to be able to insert into that structure the entries that will represent the plugin screens. In order to accomplish that task we need a table that relates data we already have in the database (the contents of the launch_item table) to the specific tree control entries that will be their parents in the tree control. The following table fulfills that task:

CREATE TABLE subpanel_group_xref (
  id               AUTOINCREMENT PRIMARY KEY,
  launch_item_id   INTEGER NOT NULL,
  menu_group_id    INTEGER NOT NULL,
  menu_context_id  INTEGER NOT NULL,
  CONSTRAINT launchid_subpanel_FK FOREIGN KEY (launch_item_id) REFERENCES launch_item(id),
  CONSTRAINT groupid_subpanel_FK FOREIGN KEY (menu_group_id) REFERENCES menu_group(id),
  CONSTRAINT contextid_subpanel_FK FOREIGN KEY (menu_context_id) REFERENCES menu_context(id)
  )
;

This table might seem a strange candidate for implementing this crucial bit of functionality because it doesn’t appear to actually store any data. The table only has 4 fields and they are all seem to be holding integers. The distinction here is that while most of the tables we have considered serve to store data, this table stores relationships – specifically the 3-way relationship that defines where each plugin will appear in the menu for each menu context. To see how these bits fit together we need to start considering the LabVIEW code that will read these structures and build the tree control based menus.

Creating the LabVIEW

The basic approach that we will take in creating the entries for the tree control is going to incorporate two distinct phases.

  1. Draw the menu structure
  2. Fill in the entries associated with the plugins

Reading the Data

The VI that is responsible for reading the menu data from the database (Config Data_DB_ADO:Read Tree Menu Structure.vi) has an enumerated input that selects the menu context the code will display. This value drives a subVI (Get Menu Context ID.vi) that reads and buffers the id value associated with the desired menu context.

Read Tree Menu Structure

In one sense, this subVI really isn’t necessary because you could theoretically perform this look-up operation using a so-called “subquery”, but this approach is far less efficient because it forces the DBMS to repeat the look-up with each query. In addition, these values are not going to change, so better to let LabVIEW remember them. To my way of thinking, however, the biggest issue with this approach is that it complicates the query itself. Given that this is the logic that maintainers (who may not be knowledgeable in SQL) are going to see, it’s a good idea to keep the SQL logic as simple as possible. The other thing to notice about the query is that it puts the entries in the correct order for display by incorporating the clause ORDER BY parent_id, sort_order ASC. Finally, you can see that I built this logic inside the generic ADO database subclass of the existing Config Data object structure.

For reading the tree entries associated with the plugins we use this VI, which is similar to the one for reading the main menu structure, but with some important differences.

Read Tree Menu Plugin Entries

The first obvious thing is that the query is much more complex because the primary table being queried is a cross-reference table. Consequently, we have to de-reference the id numbers to derive the data we need to build the menu entries. In learning how this de-referencing works, it’s important to remember that SQL is a language created by a mathematician – specifically a mathematician who specialized in a branch of mathematics called “Set Theory”. His (incredibly optimistic) idea was that if he could create a language based on mathematic principles, he would be able to prove, in the mathematical sense, that the program was correct (read: bug free).

While his grand hope evaporated in the face of the harsh reality that most programming has surprising little to do with mathematics (i.e. computing an answer), the set orientation of SQL has survived. For example, when you perform a query, what you are really doing is SELECTing a data subset FROM a larger set of data – which is typically a table. However, sometimes you need to gather data from a still larger set of data that is spread across multiple tables. To do that, you need to temporarily JOIN those tables together into one large virtual dataset based ON some criteria, like matching id numbers. Get the idea?

A not-so-obvious thing about this LabVIEW code is where it is located in the Config Data object structure. Unlike the routine for reading the basic menu structure, this VI is not located in the generic ADO database subclass. Instead it can be found in the JET database subclass, and the reason for this placement lies in the query. Unlike the other query operation which was implemented in generic SQL, there are aspects of this query that utilize JET-specific syntax (specifically, all the parentheses).

Generating the Menu Tags

With the data in hand that defines the tree-control menus, we now need to turn that data into menu entries. The first step in that transformation is to process the raw data we have acquired from the database to generate the tags that are needed to properly organize the tree entries. I won’t take up the room to show the code for this VI (Parse Tree Management Data.vi) because it’s easy to explain what it does – but feel free to check it out in the code. The VI’s primary program structure is a while loop that iterates through the raw tree-definition data generating the tags and formatting the data to generate the tree items. The loop has on it two shift registers: one holds an array of ID numbers that the loop has already processed, the other holds an array of clusters. Each element contains the four items that we will need to define a menu item (Parent Tag, Child Name, Child Tag and Child Only?).

With each iteration, the loop extracts the top element from the array and tests the Parent ID. If it is zero, the item is a top-level entry so the code builds its entry and continues with the next iteration. If Parent ID is anything other than 0, it searches the array of processed IDs to see if its parent has already been processed. When the comparison finds the new entry’s Parent ID it uses its tag value to synthesize the new entry’s child tag. When the new entry’s Parent ID is not found, the code adds the entry’s element back onto the bottom of the array of entries to be processed so it can be retried later. Normally, this search should never fail because the ordering in the queries should put the elements in the correct order, but this is just in case. This operation continues until there are no more entries left to process.

Finally, in terms of tree control infrastructure, the only thing we have left is to actually insert the entries that we have defined into the tree control. By the time we get to this point in the code we have gotten the definitions in the correct order so all we have left to do is disable front panel updates, clear the tree control, add the new tree entries and re-enable front panel updates. Again, this code is very simple so to save space I will refer you to the last post (http://www.notatamelion.com/2015/09/14/a-tree-grows-in-brooklyn-labview/) for details on the call and how it works.

Integration with the Testbed

To integrate this code with the existing testbed application requires very little work. First off on the front panel, we remove the existing ring control that we were using to select screens and add the tree control (I’m using he one from the System-themed palette), and a System-themed enumeration that will allow the operator to switch between the two menu context values. Note that this control could also be defined as a ring with the String[] control populated at run time to show the available options. This implementation would be useful if you want to provide the ability in your program to either allows the users to dynamically configure the basic structure of the tree control menus, or provide different options depending on who is using the system.

New Front Panel Controls

On the block diagram, the front panel changes impact the program logic in two places. First, we need to create a new Value Changeevent to handle the Menu Context control. This event (which is also fired when the GUI initializes itself) is responsible for rebuilding the tree-control menu display.

Menu Context-Value Change Event

The event handler starts by calling a subVI (Get Tree Menu Data.vi) that accepts as an input a Menu Context value and internally calls the two database query VIs we discussed above. After concatenating the arrays that it gets from the two routines, it passes the raw data to the VI (Parse Tree Management Data.vi) I described that converts the raw data into tree control entries. Finally it returns the array of tree control entries to the event handler, which passes it, and two references to the subVI (Draw Menu.vi) which does exactly what it name says. The first of the references is, obviously, a control reference to the tree control. The other is a VI reference to the GUI itself so the subVI can defer and then re-enable front panel updates.

The other block diagram change is to purpose an existing value change event. The event n question used to handle the ring control that changed screens and while it will still be a value change event, it will be a value change event on the aptly labeled tree control, Tree.

Tree-Value Change Event

The original logic that occupied this space took the string value of the selected ring item and used it to look up the name of the associated screen in an array of strings. The string array consisted of screen labels that were generated when the GUI loaded the subpanel VIs into memory and started them running. The resulting index was then used to index the screen’s VI reference from an array of plugin screen VI references. This VI reference would, in turn, drive the subpanel’s Insert VI method to make that screen visible in the subpanel.

The modified form works basically the same, but with a couple minor differences. Although the tree control’s value is a string, the string is the tag associated with the entry. Since the part we need to perform our search is the last item in the colon-delimited list, the first thing we need to do is strip off everything up to, and including, the lastcolon in the string. Moreover because we want this operation to be efficient as possible – so no looping. A very efficient solution is to use the built-in Match Pattern function with the rather curious-looking pattern shown. To see how it works, consider that a dot (“.”) is a special character matches any character. Next, the asterisk (“*”) is a special character that matches the longest sequence of the token that came just before it. Hence, it will match the longest sequence of any characters. Finally, the colon is not a special character so it will match just a colon. The end result is that the complete pattern will match the longest sequence of characters that are followed by a colon, and it works the same whether there is one colon in the string or a dozen. The string I want will be what is left after the match.

The other change that was needed for the tree control, is the case structure, which is there to work around a bug in the way LabVIEW handles value change events with tree controls. I configured the tree control such that only entries that are marked as Child Only are selectable. The bug is that when you click on one of the parent items, LabVIEW still fires the value change event even though the value of the tree control isn’t changing. To work around this issue, the event handler bypasses all further event processing of the “selected” item when it isn’t found in the list of screens.

Testing the Interface

As always, the “Proof of the pudding is in the eating” so let’s try running the application with its new GUI feature. One minor difference in behavior is that the subpanel now remains empty at startup until the user makes a selection. However, from the time the application starts, the user has visible a complete list of all the display screens that are available. In addition, the tree will automatically reconfigure itself when a different context is selected.

Testbed Application – Release 17
Toolbox – Release 14

The Big Tease

At one time when I started doing this work, test systems were surprisingly homogeneous. While it was true that instruments came from many different vendors, the software environment was pretty monolithic. Today, however, things have really changed. Every day it is becoming more common and accepted to have multiple applications running in parallel that were developed using a variety of development tools ranging from C++ to Java to C# and F.

In the past we have talked about creating a LabVIEW-based backend application with the main GUI built using standard web tools such as HTML, CSS and JavaScript (http://www.notatamelion.com/2015/06/08/building-a-web-backend-in-labview/). Over the next few posts I want to consider some of the other ways that your LabVIEW application can work with external applications.

Until Next Time…
Mike…

Building a Web Backend in LabVIEW

When building a web-based application, one of the central goals is often to maximize return on investment (ROI) by creating an interface that is truly cross-platform. One of the consequences of this approach is that if you are using a LabVIEW-based program to do so-called “backend” tasks like data acquisition and hardware control, the interface to that program will likely be hidden from the user. You see the thing is, people like consistent user interfaces and while LabVIEW front panels can do a pretty good job as a conventional application, put that same front panel in a web page and it sticks out like the proverbial sore thumb.

Plus simply embedding a LabVIEW front panel in a web pages breaks one of the basic tenets of good web design by blurring the distinction between content and appearance. Moreover, it makes you — the LabVIEW developer — at least partially responsible for the appearance of a website. Trust me, that is a situation nobody likes. You are angered by what you see as a seemingly endless stream of requests for what to you are “pointless” interface tweaks, “So big deal! The control is 3 pixels too far to the left, what difference does that make?” And for their part, the full-time web developers are rankled by what they experience as a lack of control over an application for which they are ultimately responsible.

If you think about it, though, this situation is just another example of why basic computer science concepts like “low coupling” are, well, basic computer science concepts. Distinguishing between data collection and data presentation is just another form of modularity, and we all agree that modularity is A Very Good Thing, right?

It’s All About the (Data)Base

So if the concept that is really in play here is modularity, then our immediate concern needs to be the structure of the interface between that part of the system which is our backend responsibility, and that which is the “web guy’s” customer facing web-based GUI.

Recalling what we have learned about the overall structure of a web application, we know that the basic way most websites work is that PHP (or some other server-side programming tool) dynamically builds the web pages that people see based on data that they extract from a database. Consequently, as LabVIEW developer trying to make data available to the web, the basic question then is really, “How do I get my data into their database?”

The first option is to approach communications with the website’s database as we would with any other database. Although we have already talked at length about how to access databases in general, there is a problem. While most website admins are wonderful people who will share with you many, many things — direct access to their database is usually not one of those things.

The problem is security. Opening access to your LabVIEW program would create a security hole through which someone not as kind and wholesome as you, could wiggle. The solution is to let the web server mediate the data transfer — and provide the security. To explore this technique, we will consider two simple examples: one that inserts data and one that performs a query. In both cases, I will present a LabVIEW VI, and some simple PHP code that it will be accessing.

However, remember that the point of this lesson is the LabVIEW side of the operations. The PHP I present simply exists so you can get a general idea of how the overall process works. I make no claims that the PHP code bears any similarity to code running on your server, or even that it’s very good. In fact, the server you access might not even use PHP, substituting instead some other server-side tool such as Perl. But regardless of what happens on the other end of the wire, the LabVIEW code doesn’t need to change.

As a quick aside, if you don’t have a friendly HTTP server handy and you want to try some of this stuff out, there is a good option available. An organization called Apache Friends produces an all-in-one package that will install a serviceable server on just about any Windows, Linux or Apple computer you may have sitting around not being used. Note that you do not want to actually put a server that you create in this way on the internet. This package is intended for training and experimentation so they give short shrift to things like security.

The following two examples will be working with a simple database table with the following definition:

CREATE TABLE temp_reading (
   sample_dttm  TIMESTAMP PRIMARY KEY,
   temperature  FLOAT
   )
;

Note that although the table has two columns we will only be interacting directly with the temperature column. The sample_dttm column is configured in the DBMS such that if you don’t provide a value, the server will automatically generate one for the current time. I wanted to highlight this feature because the handling of date/time values is one of the most un-standardized parts of the SQL standard. So it will simplify your life considerably if you can get the DBMS to handle the inserting or updating of time data for you.

Writing to the Database

The first (and often the most common) thing a backend application has to do is insert data into the database, but to do that you need to be able to send data to the server along with the URL you are wanting to access. Now HTTP’s GET method which we played with a bit last week can pass parameters, but it has a couple big limitations: First, is the matter of security.

The way the GET method passes data is by appending it to the URL it is getting. For example, say you have a web page that is expecting two numeric parameters called “x” and “y”. The URL sent to the server would be in the form of:

http://www.mysite.html?x=3.14;y=1953

Consequently the data is visible to even the most casual observer. Ironically though, the biggest issue is not the values that are being sent. The biggest security hole is that it exposes the names of the variables that make the page work. If a person with nefarious intent know the names of the variables, they can potentially discover a lot about how the website works by simply passing different values and seeing how the page responds.

A second problem with the GET method’s way of passing data is that it limits the amount of data you can sent. According to the underlying internet standards, URLs are limited to a maximum of 2048 characters.

To deal with both of these issues, the HTTP protocol defines a method called POST that works much like GET, but without exposing the internal operation or limiting the size of the data it can pass. Here is an example of a simple LabVIEW VI for writing a temperature reading to the database.

Insert to database with PHP

This code is pretty similar to the other HTTP client functions that we have looked at before, but there are a few differences. To begin with, it is using the POST we just talked about. Next, it is formatting and passing a parameter to the method. Likewise, after the HTTP call it verifies that the text returned to it is the success message. But where do those values (the parameter name and the success message) come from? As it so happens I didn’t just pull them out of thin air.

Check out the URL the code is calling and you’ll note that it is not a web page, but PHP file. In fact, what we are doing is asking the server to run a short PHP program for us. And here is the program:

<?php
	if($dbc = mysqli_connect("localhost","myUserID","myPassword","myDatabase")) {
    	// Check connection
		if (mysqli_connect_errno()) {
			// There was a connection error, so bail with the error data
			echo "Failed to connect to MySQL: " . mysqli_connect_error();
		} else {
			// Create the command string...
			$insert = "INSERT INTO temp_reading (temperature) VALUES (" . $_POST["tempData"] . ")" ;
			
			// Execute the insert
			if(!mysqli_query($dbc,$insert)){
				// The insert generated an error so bail with the error data
				die('SQL Error: ' . mysqli_error($dbc));
			}
			
			// Close the connection and return the success message
			mysqli_close($dbc);
			echo "1 Record Added";
		}
	}
?>

Now the first thing that you need to know about PHP code is that whenever you see the keyword echo being used, some text is being sent back to the client that is calling the PHP. The first thing this little program does it try to connect to the database. If the connection fails the code echoes back to the clients a message indicating that fact, and telling it why the connection failed.

If the connection is successful, the code next assembles a generic SQL statement to insert a new temperature into the database. Note in particular the phrase $_POST["tempData"]. This statement tells the PHP engine to look through the data that the HTTP method provided and insert into the string it’s building the value associated with the parameter named tempData. This is where we get the name of the parameter we have to use to communicate the new temperature value. Finally, the code executes the insert that it assembled and echoes back to the client either an error, or a success message.

Although this code would work “as is” in many situations, it does have one very large problem. There is an extra communications layer in the process that can hide or create errors. Consider that a good response tells you that the process definitely worked. Likewise receiving one of the PHP generated errors tells you that the process definitely did not work. A network error, however, is ambiguous. You don’t know if the process failed because the transmission to the server was interrupted; or if the process worked but you don’t know about it because the response from the server to you got trashed.

PHP Mediated Database Reads

The other thing that backend applications have to do is fetch information (often configuration data) from the database. To demonstrate the process go back to the GET method:

Fetch from database with PHP

As with the first example, the target URL is for a small PHP program, but this time the goal is to read a time ordered list of all the saved temperatures and return them to the client. However, the data needs to be returned in such a way that it will be readable from any client — not just one written in LabVIEW. In the web development world the first and most common standard, is to return datasets like this in an XML string. This standard is very complete and rigorous. Unfortunately, it tends to produce strings that are very large compared to the data payload they are carrying.

Due to the size of XML output, and the complexity of parsing it, a new standard is gaining traction. Called JSON, for JavaScript Object Notation, this standard is really just the way that the JavaScript programming language defines complex data structures. It is also easy for human beings to read and understand, and unlike XML, features a terseness that makes it very easy to generate and parse.

Although, there are standard libraries for generating JSON, I have chosen in this PHP program to generate the string myself:

<?php
    if($dbc = mysqli_connect("localhost","myUserID","myPassword","myDatabase")) {

    	// Check connection
		if (mysqli_connect_errno()) {
			echo "Failed to connect to MySQL: " . mysqli_connect_error();
		} else {
			// Clear the output string and build the query
			$json_string = "";
			$query = "SELECT temperature FROM temp_reading ORDER BY sample_dttm ASC";
			
			// Execute the query and store the resulting data set in a variable (called $result)
			$result = mysqli_query($dbc,$query);
			
			// Parse the rows in $result and insert the values into a JSON string
			while($row = mysqli_fetch_array($result)) {
				$json_string = $json_string . $row['temperature'] . ",";
			}
			
			// Replace the last comma with the closing square bracket and send the
			// to the waiting client
			$json_string = "[" . substr_replace($json_string, "]", -1);
			echo $json_string;
		}
	}
	mysqli_close($dbc);
?>

As in the first PHP program, the code starts by opening a database connection and defining a small chunk of SQL — this time a query. The resulting recordset is inserted into a data structure that the code can access on a row by row basis. A while loop processes this data structure, appending the data into a comma-delimited list enclosed in square brackets, which the JSON notation for an array. This string is echoed to the client where a built-in LabVIEW function turns it into a 1D array of floats.

This technique works equally well for most complex datatypes so you should keep it in mind even if you are not working on web project. I once had an application where the client wanted to write the GUI in (God help them) C# but do all the DAQ and control in LabVIEW-generated DLLs. In this situation the C# developer had a variety of structs containing configuration data that he wanted to send me. He, likewise, wanted to receive data from me in another struct. The easy solution was to use JSON. He had a standard library that would turn his structs into JSON strings, and LabVIEW would turn those strings directly into LabVIEW clusters. Then on the return trip my LabVIEW data structure (a cluster containing a couple of arrays) could be turned, via JSON, directly into a C# struct. Very, very cool.

The Big Tease

So what is in store for next time? Well something I have been noticing is that people seem to be looking for new ways to let users interact with their applications. With that in mind I thought I might take some time to look at some nonstandard user interfaces.

For my first foray into this area I was thinking about a user interface where there are several screens showing different data, but by clicking a button you can pop the current screen out into a floating window. Of course, when you close the floating window, it will go back into being a screen the you can display in the main GUI. Should be fun.

Until Next Time…

Mike…

Objectifying the Testbed

Object-oriented programming as a technique promises a host of benefits, but suffers from the impression that it is in some way an “advanced” topic. In contrast, I feel that OOP is just a logical extension of the concepts that LabVIEW developers use every day. The basic problem has been with the way it has been taught. However, the various object-oriented frameworks that are overly complex and difficult to learn, haven’t helped matters. These bad “actors” often only serve to hide the inherent elegance of the OOP paradigm and scare off users that could benefit from it.

To help clear away some of the extraneous mystique, I have presented a brief introduction to the topic that provides a foundation sufficient to let us get into OOP by implementing a module for managing program configuration data that provides the calling application with a common interface regardless of how (or where) the data is stored.

Filling a Niche

Most of the work we will be doing will eventually replace the Configuration Management library. Now while this might sound like a major shift, it really is not because (like I repeatedly tell you) the whole point of good design is to make changes and upgrades like this possible. So let’s look at what this upgrade will need to do.

Normally a large part of any upgrade projects is defining the requirements, but due to the design work that was put in originally, we already have a pretty good handle on what the new class structure has to do. In terms of surface functionality, we know we have to be able to handle all the same information as before — with, of course, the ability to add more when we want or need that ability.

Designing the Structure

The trick is going to be sorting out what new functionality will be needed under the covers. At the most basic level we need to be able use either text files or databases to actually store the configuration data, so there we have two subclasses. But we need to consider whether each of those options needs to be broken down further.

On the “text file” side, the data might be coming from a standard INI file, or the code might be in using a custom text file format. Custom text configuration files are very common when some of the configuration data is tabular, since it is a pain to store tabular data in an INI file. However, regardless of the format of the contents, the basic mechanism for reading and writing text files remains the same so it probably won’t be valuable to have any subclasses under “text files”.

On the “database” side of things, however, the situation is very different. First of all, in terms of connectivity, you can access most databases through the standard ADO (ActiveX Data Objects, also sometimes called ole-db) interface. However, “most” is not the same as “all” and one common exception is SQLite. A popular, lightweight data management engine, SQLite can run on a variety of platforms — including some real-time systems. To keep its footprint small, SQLite utilizes a small custom DLL, rather than a large, but standardized, interface. So we need to make provisions for other types of connectivity by creating (for now) two subclasses below “database”: “ado” and “sqlite” — though we won’t be implementing the SQLite functionality right now.

Finally, what about “ado”? Can it be broken down further? Maybe. One of the advantages of ADO is that it, for the most part it does a pretty good job of hiding the differences between one database management system (or DBMS) and the next, but there are some variations it can’t paper over. These differences often relate to the version, or dialect, of SQL the DBMS speaks. However sometimes differences arise because some DBMS fundamentally don’t operate the same. For example, while most DBMS go to extraordinary lengths to hide exactly where and how the data is actually stored, Jet (the DBMS built into Windows) stores the data in a file you explicitly identify. Hence, while the connection to other DBMS might be defined in terms of network paths and logical names, with Jet you are connecting to a particular file.

To provide for these sorts of functional nuances, let’s create a subclass below “ado” for “jet” — understanding there could be others in the future.

Configuration Data Classes

This is what the hierarchy looks like so far, all drawn out.

Doing the Rough Framing

When you are building a house the first tradesmen to show up onsite are the carpenters to do the so-called “rough framing”. This process creates the skeletal form of the final house that is covered with rough exterior plywood. The idea is that later workers will fill in the details and fine tune the construction. And metaphorically speaking, that’s what we have to do now for our configuration data class.

For a class hierarchy, that framing consists of the directory structure and the class files themselves. Using the techniques I gave last time, I first create mirroring directory structures inside the project directory and the project itself…

Configuration Data Classes in Project

Note that I have also created some virtual folders inside the Config Data class which represents actual sub directories.

  • Interface
    The VIs in this folder will have public access scope. In fact they will be the only VIs in the library that are so scoped. Because these VIs are the only ones that outside callers will be able to call, they alone form the interface between the class hierarchy and the rest of the code.
  • _private
    As the folder title implies, the files that go in here will have private access scope. This assignment means that they will only be accessible from other VIs in the top-level class.
  • _protected
    This is another folder that specifies access scope for its contents. In the case of protected scope, the VIs in this folder will only be accessible from the top-level class, or any of its child classes.

Creating the Interface

With the functional scaffolding in place, we can start filling in the blanks. And the first thing we need to do is create what I referred to as the “blueprint” in the previous post — the VIs that the rest of the application will call. After going through the public VIs in the old library we see that there are really only 4 VIs that the rest of the application uses directly

  1. Get Default Sample Period.vi
  2. Get Error Handling Parameters.vi
  3. Get Processes to Launch.vi
  4. Load Machine Configuration.vi

Many of the others will still be used, but their presence will be hidden in subclasses. As I create these (for now) empty VIs, I make sure they have the same front panels and connector panes as the ones they will be replacing.

Adding Infrastructure

Getting back to our housebuilding analogy: After the framework is completed and the outside skin is on, the next job is to start installing some of the needed infrastructure, because without electric, water, sewer and perhaps gas connections, our new home is not much better than the cave dwellings that our prehistoric ancestors inhabited — and in some ways is far worse.

What we need to add to our nascent configuration management subsystem is some data handling, but not for our data. The data I’m talking is the private internal data that the subsystem needs to maintain in order to do its job.

Thinking about what our various bits of code need to do, we see first that there is certain data that will commonly be needed regardless of how you actually end up getting the data. What I’m thinking about here is the name of the operator and a password. Now, some subclasses might need both, while others might need only one or the other, and that’s fine. The important point is that if all subclasses could potentially need at least some of this data, it has to be data that is associated with the top-level class. However, this requirement for global availability causes a problem.

Remember how when we were defining terms in the previous post we said that in the LabVIEW world an object is a wire? LabVIEW wires, and data contained in them are by definition not global. If you want data from a wire you need to be connected to it. So how can we create wires that are separate, but which still share at least some of the same data? Well, one very good way of doing it would be to create one central data store that all the wires can access, and as it turns out LabVIEW incorporates a feature that is very efficient and so is perfect for such an implementation. I’m talking about the Data Value Reference, or DVR.

Our approach will be simple. We first create a DVR that is defined to hold the data we need and make a reference to that DVR the class data for our Config Data class. Then to access that data, we create a family of data access VIs to insert data into, or read data from, the DVR.

The first step on this process is to create another virtual folder in the top-level class named _dvr with privateaccess scope. Next, I create in that folder a typedef control named Config Mgr Data.ctl that consists of a cluster containing two strings, one for user name and one for password.

Config Mgr Data

When saving this control I create a subdirectory (called _dvr) to hold it. Likewise, I also create a VI in the same directory (and virtual folder) called Config Mgr DVR.vi that contains this code:

Config Mgr DVR

Two comments about this code. First, it has the basic form of a FGV where the variable value is the DVR reference. Because the case that generates the reference is only run once, this VI will always return a reference to the same DVR no matter how many times it is run. Second, the data for the DVR is the typedef we created. This point is critical. As with UDEs, if you define a DVR reference using a typedef, you can later change the typedef and it won’t break the reference.

To make this DVR available and inheritable through the class, I copy and paste the reference indicator into the class data cluster, like so.

Config Mgr DVR in Class Data

Then to provide access to the DVR contents, I create protected scope VIs that I store in a virtual folder and subdirectory both named _data access. Here is what the read VI for the user ID parameter looks like…

User ID Read

…and the write VI…

User ID Write

I next realize that the two main subclasses also have some data that will need to be held in common for their subclasses. So I repeat the process I just used to store a file path for the file subclass and the connection string that the db subclass needs. Here’s what the project looks like now.

project with basic infrastructure

Finally, before moving on we are going to need a way to initialize all the logic we have created, and to do that we will take our first foray into the exciting — though sometimes confusing — world of creating dynamic dispatch VIs. The goal is to create a method called Initialize New that causes the DVR in a new instance of a class to automatically initialize itself.

I start by right clicking on the _protected virtual folder in Config Data.lvclass and from the New sub menu selecting the option to create a new VI using the dynamic dispatch template. I leave the front panel of the resulting VI the way it is, but I change the connector pane, edit the icon and add this code to the block diagram.

Initialize top-level dvr

If the DVR in the class data is not valid, I call the DVR VI to get a valid reference and then use that reference to populate the class data. If the DVR reference is valid, the false case (not shown) does nothing. When I save this VI, I put it in a subdirectory named _protected.

To create the subclass versions of this method, I right-click on the subclass name and from the New sub menu select the item to create a new VI for override — which is the technical term for what we are doing. We are overriding the parent functionality with different functionality in the child.

However before we get a new VI, LabVIEW opens a dialog to ask us which parent method we want to override. After double clicking on Initialize New we get our new VI, also called Initialize New. After saving this new VI, (the default location LabVIEW picks is perfect) I modify the code to look like this:

initialize new - child

Most of this logic should be familiar because it is the same as we did for the parent. If the child DVR reference is not valid, we initialize it. Otherwise, we do nothing. But what is that funny looking VI in front of the initialization logic?

Object-oriented methodology recognizes that there are going to be times when a method in a child class will need to do what the parent method does, but perhaps a bit more or maybe do it a bit differently. One solution to this situation would be to simply duplicate all the parent code in the child. However that approach would be wasteful. What object-oriented logic does instead is it allows a child to directly call its parent’s version of the method. So here, the code first calls the parent’s version of the initialization VI and then executes the logic to initialize itself. In the end, both the parent and the child will get initialized.

Creating Objects

The time is now upon us to start tying this infrastructure together into an organized system. The first thing to sort out is how to create an object of a given type. One way is very similar to what you would do with a conventional datatype. If you wanted to create, say an I32 value, you would drop down a constant and start using it. In the same way, you can also drop down a class constant and wire to it, thus creating an object. The problem with this approach is that when LabVIEW instantiates a class it also loads into memory all the VIs associated with the class. What you can end up with is a situation where all the VIs for all the classes are loaded, even if you will never use some of the classes. The way to get around that problem is to load classes dynamically as you need them. This is the code I use to perform that operation.

create object dynamically

You will notice that the VI has no inputs, save the requisite error cluster. This is because the basic piece of information that specifies the specific class to be created will be loaded from the application INI file. But why the INI file? Isn’t the point of this exercise to get rid or configuration data in that file? Well yes, but there is a bit of a paradox at work here. Simply put, an application can’t go to a database it doesn’t know it has to find if it should look in the database to get its setup data. That basic piece of information has to be stored somewhere that will always be there, and on the Windows platform you have exactly two choices: the INI file and the Windows registry. Of the two, the INI file is much safer — you at least don’t have to worry about a user going wild and trashing their whole computer.

We are initially only going to support two options for managing configuration data, so we only need two settings (Text and Jet). The VI that communicates with the INI file reads one of these values from the Configuration Data key in the Data Management section and returns two values based on what it finds there: A relative path to the location of a class file, and the name of the file. Thanks to the naming convention we use, these two values are very closely related.

The remaining code loads the target class into memory and initializes it. In addition, the resulting object is buffered as in a FGV. For the details of how this process works, see the comments in the code. The final thing to notice is that the output from this VI is an indicator of the Config Data class datatype.

Building Out the Remaining Methods

All that’s left now is to implement the code that does what the testbed needs done, however this post is getting long and I have already passed on a lot of information for you to absorb — so we’ll leave that discussion (and the testing!) for next week.

Until Next Time…

Mike…

Talking to a Database from LabVIEW

Ok, this is the third part of our discussion of how to effectively utilize databases in a LabVIEW-based application. To recap, In Part 1 we have covered the basic justifications for utilizing databases, and checked-out some drivers that implement basic database connectivity. Part 2 expanded on that information by going into the (very basic) basics of how to design and build a database. I also provided some links for further reading, and a LabVIEW tool for executing an SQL script to turn it into an actual database. What you are looking at now is Part 3 and it will present code that implements the LabVIEW side of the data management capability we discussed earlier. If you haven’t read the other two sections, I would recommend that you do so before continuing — don’t worry, we’ll wait for you.

Those of you who did read the previous portions, feel free to talk among yourselves until the others get back.

Everybody back? Good! Let’s continue.

Example Code

The database we have implemented so far covers three basic areas in the Testbed application. Something that I didn’t mention before is that these areas were not picked at random, or arbitrarily. Rather, if you look at them you see that each one presents an example of one kind of database operation while presenting useful concepts that you will want to know about in the future.

  • Processes to Launch: This section demonstrates data that has an inherent structure as embodied in its one foreign key relationship.
  • Event Recording: Here we considered a table to which applications will eventually write. It also shows a little more structure in that it relates to two different header tables: one that identifies the application generating the event and one that identifies the type of event that is being recorded.
  • Default Sample Period: Although much of the data in a system will be structured, there is still a place for simple setting such as you might store in an INI file. This last example showed such a situation.

Carrying forward with this idea of demonstrating a variety of concepts, as we go through the code that implements the LabVIEW side of the connection, I will point out a few different techniques that you will find useful. The thing to remember is that there are engineering decisions that you have to make and no one technique or approach will serve in every possible situation.

Reading Processes to Launch

The new VI that performs this operation is still named Configuration Management.lvlib:Get Processes to Launch.vi and has the same basic structure as the INI file version (which I renamed), but the configuration file IO logic is replaced with the database IO drivers I presented earlier.

Read Processes to Launch

Although the structure is basically the same as before, there are a few changes due to the improved structure that the database provides. First, there is a new input (Launch Condition) that is tied internally to a database search term. Second, the output data structure is modified to utilize the enumeration for the launch mode, replacing the boolean value used before.

In terms of the query code itself, the large SQL statement in the string constant is for the most part pretty standard code. The statement specifies what columns we want (label, item_path, launch_mode), the table they are in (launch_item) and the WHERE clause provides the search terms that define the output dataset we want. Likewise, note that although I never read the launch_order value, I use it to sort the order of the results. If you have data that needs to be in a specific order this is an important point. Unless you explicitly tell the DBMS how to order the results, the sequence of records is totally undefined. The only real complication is contained in the WHERE clause.

You will recall from our discussion of normalization that the two primary search terms we will be using are stored as ID numbers that reference a pair of header tables. These header tables contain the human-readable labels that we want to use for our searches. This code demonstrates one approach to resolving the ID references through the use of subqueries. A subquery is (as its name suggests) a small query that occurs inside the main query. They are typically employed to lookup data that the calling application doesn’t directly know. In this example, we know that the application name and launch condition, but we don’t know what ID numbers are associated with those inputs. The subqueries look up those values for us so the main query can use them to get the data we want.

The advantage of subqueries is that they allow you to specify what you want in the terms that are meaningful to the calling application. The disadvantage is that they can complicate SQL code and the resulting code can be more DBMS-specific. In addition, with some DBMS (like for example, Jet) there can be a significant performance hit involved in subqueries. You’ll note that the example code mitigates this potential issue by buffering the search results, thus ensuring that the “hit” will only occur once.

Saving Errors to the Database

This operation is performed by a new version of Startup Processes.lvlib:Store Error.vi. As with the original version, the input error cluster provides the information for the error record, and the output error cluster is the result of the record insert.

Store Error to Database

This code shows an alternative to using subqueries to obtain the ID references. The two subVIs look-up the required IDs in the database the first time they are called and buffers the results for later reuse. The two routines are very similar, so here is what the application ID VI looks like:

Get Appl_ID

The advantage of this approach is that the required SQL is much simpler and very standard. The only real “disadvantage” is that you have to create these buffers — which really isn’t very much of a disadvantage. I really like this technique for situations where there are common IDs that are used over and over again. Unfortunately, this sort of modularization isn’t always possible, in most real-world applications you will need to know both techniques.

Read Default Sample Period

You will recall that this is new functionality so there is no “old code” to compare it to. Here is one option for what Configuration Management.lvlib:Get Default Sample Period.vi could look like.

Read Default Sample Period

The code here is very similar to that for reading the processes to launch, the main difference being that two of the search terms are in essence hardcoded. Current, there is only one parameter being stored in this table, but that situation could change at any time. While replicating this VI for each additional parameter would work, the result could be a lot of duplicated code. Consequently, I typically prefer to start with a lower-level VI that is something like this…

Read Misc Setup Values

…and then make the VIs like Configuration Management.lvlib:Get Default Sample Period.vi simple wrappers for the buffer.

Read Default Sample Period - Improved

Hopefully by now you are beginning to see a bit of a pattern here. Every type of access doesn’t require a unique technique. In fact, there are really very few unique use cases — and frankly we just covered most of them. The real key is to get comfortable with these basic methods so you can customize them to address your specific requirements.

Moving On…

So we now have our database, and the VIs for accessing it. How do we roll this into out application? One way is to simply replace the old INI file versions of the VIs with the new database-oriented ones — which is what I have done for now. I say. “…for now…” because there is a better solution, but that will have to wait for another day. Right now, the solution we have is serviceable so I want to get back to a couple more important topics. Here’s the updated application:

http://svn.notatamelion.com/blogProject/testbed application/Tags/Release 7

Until next time…

Mike…

Building a Testbed Database

As we continue our exploration of using databases to store data in your applications, the next thing we need to do is consider what the basic structure off a database should look like and how to construct a simple one that would be suitable for our Testbed application. Considering what we have assembled so far, we can immediately see two existing areas where a database would be useful:

  • Lookup for processes to launch: Currently this information is being stored in the INI file.
  • Recording errors that occur: Currently errors are being logged to a test file that is stored in the same directory as the application.

In both cases, moving this data into a database will offer a number of benefits in terms of configurability and reliability. But in addition to these two existing opportunities, there is a third value to implement that is new.

  • The default sample period: In the existing code, this value is defined by the default value of a control on the front panel of the Display Data process. This technique works, and in many case is a perfectly fine solution, but there are also cases where your customers will want to be able to change a value without you needing to modify the code. So we’ll make that change too.

As we go through the following discussion, an important point to remember is that project development is — in addition to everything else — a process of ongoing refinement. As you work through a project you are always going to be learning more about what it is that you are trying to accomplish and you need to be willing to incorporate those “lessons-learned” in your code. Of course this point assumes that your code is modularized such that this sort of “mid-course correction” can be implemented without doing violence to what you already have in place.

In order to break this work up into bite-sized pieces, this post will address the structure of the database itself. The VIs needed to implement this configurability will be addressed in the next installment.

Why be Normal(ized)?

According to Wikipedia, normalization (in the database sense) is “…the process of organizing the fields and tables of a relational database to minimize redundancy.” Basically, this means that a given piece of information, like a person’s name, should only be stored in one place in the database. Normalization is important because it is one of the major ways that databases ensure consistency in the data they contain. For example, if an operator’s name is only stored in one place, you don’t have to worry after the fact if “Robert Porter” and “Robert Portor” are really the same person.

Beyond this basic explanation, there is a lot of detail that we don’t need to discuss right now. In fact, there are whole books written on the topic of normalization. For your further reading, I have included links to two very good ones are at the end on the post. Our goal right now is to simply cover the basics so as you can understand what a real database developer puts together for you. Moreover, if the need should ever arise for you to create a database for yourself, this information certainly won’t make you an expert, but it will help you avoid a few major pitfalls.

The first step in creating a database is called Data Modelling. Data modelling is a process of looking at your data in order to understand its inherent structure. Ideally, data modelling should be something that you do early in your design process, regardless of whether you are going to be using a database. If you think about it, this point is particularly true when doing LabVIEW development. After all, LabVIEW’s core paradigm is one of dataflow, so how can you expect to properly define an application’s dataflows if you don’t understand the data’s inherent structure? Naturally therefore, when starting work on this testbed, I did some basic data modelling. Let’s go over what I discovered.

Supporting Many Applications

When studying the data that an application needs to handle, a good place to start is with a list of the data items that the code will need to complete the intended tasks. Some of this information will be common to all the tasks the code will be supporting, and some will be unique. At first glance, it might not seem like there is any information that the various functions have in common, but if we think ahead a bit, we see that there is one. To this point, we have been talking about the program that we are creating, as if it is the only one we are ever going to build and install on a user’s computer. But is this really a reasonable assumption?

In a way, this is sort of like talking about a computer that will only have one program installed on it. In addition, we have talked before about the advantages of seeing an “application” as the aggregation of the independent behaviors of many different processes that operate more or less independent of one another. It seems to me unlikely that the assumption we have been making will continue to hold up in the future.

In the world governed by INI files, this reality is addressed by simply having a separate INI file for each program. Databases accomplish the same task by adding a field that identifies which application is associated with (or in database-speak, related to) each data record. But how should these relationships be recorded in the database? Well, we could just include a field in each table called something like application_name, but this solution has two problems. First, remembering what we said about normalization, this approach would produce a lot of redundant data and a lot of opportunities for things to be misspelled, or mislabeled. Second, since we would be using that value to qualify the queries we perform in order to extract just the data related to one particular application, those string fields will end up being search terms and indexing or searching strings is inherently very inefficient.

Both problems are solved by creating a separate table that uniquely identifies each application. Primarily, it would hold the application name, but could contain other information such as version number, who wrote it, and when it was modified. In this table, each application will have one record and each record has a unique number associated with it called the Primary Key. Any other table that needs to relate to its data to a specific application can simply include a field that holds this number — and numbers are very fast to search and index. Here is the SQL code needed to create this type of table, sometimes called a header table:

CREATE TABLE appl (
    id       AUTOINCREMENT PRIMARY KEY,
    label    TEXT(40) WITH COMPRESSION,
    ver_nbr  TEXT(10) WITH COMPRESSION,
    dev_name TEXT(50) WITH COMPRESSION
  )
;

The first line contains the command (CREATE TABLE) and specifies the new table’s name (appl). Everything between the parentheses is a comma-delimited list of the columns that the table will contain. Each column definition consists of a column name and a definition of the column’s contents. Note that column names can contain spaces, but it complicates the syntax, so I don’t use them.

The first column is named id and the column definition presents the Jet DBMS syntax for identifying a column that you want to be an automatically generated integer primary key. Other DBMS also have mechanisms for accomplishing the same thing, but the syntax varies. The next three columns are defined as variable-length text fields of differing sizes. Note that defined in this way, the indicated count specifies the maximum number of characters that the field can contain, but the DBMS only stores the actual number of characters you put in it. Finally, the WITH COMPRESSION keywords are needed because a few versions ago, Jet was converted to storing UNICODE characters, which are 2-bytes long. The effect of this change is that ASCII strings started taking up twice as much memory as was needed. The WITH COMPRESSION keywords tell Jet to store the strings as 1-byte values.

One more point. The last field (dev_name) is intended to contain the name of the developer that last modified the application. But, wouldn’t what we said earlier about normalization apply to this information as well? Yes it would. To be formally correct, there should be another table of developers to which this table should relate. However, I put this in here to highlight the important point that in database design — as with LabVIEW programming — there trade-offs and so we need to ask ourselves whether the added normalization provides sufficient benefit to justify the added complication of another table. In this case, the answer I came up with was, “No”.

Processes to Launch

When we consider the information needed to launch the startup processes with the desired user feedback, we find that there are three things we need — plus of course a reference to the application identified in the table we just created. This is what we have.

  1. appl_id: This is the numeric reference to the table identifying the applications.
  2. label: This is the string that will be displayed to the user while the process is being launched. Before it was derived from the name of the VI. This approach is more flexible.
  3. item_path: As when this data was stored in the INI file, this string is a relative path to the VI to be launched.
  4. item_mode: This string indicates the mode used to launch the process VI. It is associated with an enumeration in LabVIEW that (for now at least) simply indicates whether the VI is reentrant or non-reentrant.
  5. launch_order: This is an integer column we will use to sort the query results so the process VIs are launched in the right order.

So with this information in hand we are ready to define our next table, right? Well, not quite. We need to consider another assumption that we have made. To this point, the code we have created only uses VIs that are dynamically loaded in one place: at program startup. Will this assumption always be true? Simple logic would say that dynamic loading is a very powerful technique, so we will very likely be wanting to use it in the future. So let’s create another header table that will store a string indicating the condition where the VI will be launched. Right now the table will only have a single value in it, “Startup”.

Turning all this discussion into SQL commands to create the needed tables, this code creates the new header table:

CREATE TABLE launch_cond (
    id                AUTOINCREMENT PRIMARY KEY,
    launch_condition  TEXT(128) WITH COMPRESSION
  )
;

…then this code inserts the one record we need now…

INSERT INTO launch_cond (launch_condition) VALUES ('Startup');

…and finally we can create the table for holding the launch items:

CREATE TABLE launch_item (
    id              AUTOINCREMENT PRIMARY KEY,
    appl_id         INTEGER NOT NULL,
    launch_cond_id  INTEGER NOT NULL,
    label           TEXT(40) WITH COMPRESSION,
    item_path       TEXT(128) WITH COMPRESSION,
    launch_mode     TEXT(40) WITH COMPRESSION,
    launch_order    INTEGER,
    CONSTRAINT launch_cond_FK FOREIGN KEY (launch_cond_id) REFERENCES launch_cond(id),
    CONSTRAINT launch_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id)
  )
;

This table contains all the same stuff we saw in the other table creation commands, but with a couple additions. The last two items in the column list aren’t columns. Rather, they create the relational links between this table and the two header tables by defining a pair of foreign key constraints. The name of the constraints are important because they will be used in error messages associated with the constraint. The constraint definitions themselves are straight-forward, specifying a column that references a specified column in a specified table.

Storing Errors

The foregoing case demonstrates how to deal with tables that store configuration data. Now we turn our attention to a table that the application writes to while running. Specifically, we will look at the table for storing errors that occur during program execution. Here are the pieces of information we know we will need going in:

  1. appl_id: This is the numeric reference to the table identifying the applications. In this case it is the application that generated the associated event.
  2. evt_dttm: This field holds timestamp values indicating when the events occurred.
  3. evt_type_id: In addition to knowing when an event occurred, you also need to capture what type of event it was. Starting off we can imagine three types of events (errors, warnings and notes) but there could be more, so we’ll store the type in another header table and reference it here.
  4. evt_code: This integer column hold the error code from the LabVIEW error cluster.
  5. evt_source: Another text column, this field holds the Source string from the LabVIEW error cluster.

First comes the new header table and its three records. Note that these event types correlate to the three values of a LabVIEW enumeration (Event Types.ctl).

CREATE TABLE event_type (
    id     AUTOINCREMENT PRIMARY KEY,
    label  TEXT(40) WITH COMPRESSION
  )
;

INSERT INTO event_type (label) VALUES ('Error');
INSERT INTO event_type (label) VALUES ('Warning');
INSERT INTO event_type (label) VALUES ('Note');

And then the table for storing the events…

CREATE TABLE event (
    id             AUTOINCREMENT PRIMARY KEY,
    appl_id        INTEGER NOT NULL,
    event_type_id  INTEGER NOT NULL,
    evt_dttm       DATETIME DEFAULT now() NOT NULL,
    evt_code       INTEGER,
    evt_source     MEMO WITH COMPRESSION,
    CONSTRAINT event_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id),
    CONSTRAINT event_event_type_FK FOREIGN KEY (event_type_id) REFERENCES event_type(id)
  )
;

The first new idea on display here is in the definition of the evt_dttm field. In addition to stating the data type (DATETIME), it also specifies that the field cannot be empty (NOT NULL) and tells Jet what value to use if an insert statement does not contain a time value: now(). This built-in Jet constant returns the current date and time. You also see that I introduced a new datatype MEMO to hold the textual description of the error. Given that it uses the same WITH COMPRESSION keywords as we use with the TEXT, you might assume that it is another way of storing textual data — and you’d be right. The difference between the two is that while both are variable length fields, a TEXT field can only hold a maximum of 255 characters. By contrast a MEMO field can (in theory at least) hold strings as long as 2.14-Gbytes.

The Default Sample Period

Finally there are always simple, unstructured setup values, like the default sample period, so let’s set up a table for them too. Note that I picked column names that reflect the simple organization of an INI file with sections, keys and values.

CREATE TABLE misc_setting (
    id         AUTOINCREMENT PRIMARY KEY,
    appl_id    INTEGER NOT NULL,
    p_section  TEXT(50),
    p_key      TEXT(50) WITH COMPRESSION,
    p_value    TEXT(255) WITH COMPRESSION,
    CONSTRAINT miscsettings_appl_FK FOREIGN KEY (appl_id) REFERENCES appl(id)
  )
;

INSERT INTO misc_setting (appl_id, p_section, p_key, p_value)
   SELECT id, 'Data Acquisition','Sample Period','1000'
     FROM appl
    WHERE label = 'Testbed'
;

The syntax used for inserting the record we need is a bit different because it has to look up the value that will go into the appl_id field using something called a subquery. The syntax for the Jet implementation of this type of operation is rather obscure and is in no way compliant with the SQL standard, but this is what it looks like. Unfortunately, it is not the only place where Jet runs counter to the standard. For example, getting the event table to automatically insert the event timestamp value using by using a default value of now() sidesteps the problem of formatting time values, which is also very non-standard.

A Few More Things

So there we have it: the structure for our simple database — or as much of it as we need right now. But you may be wondering what you are supposed to do with all that SQL code? I’m glad you asked. I have created a simple utility that executes the code to create the database, and you can find it (and the complete SQL source code file) here:

http://svn.notatamelion.com/blogProject/local database builder/Tags/Release 1

When you run the utility, it builds the new database file in the directory where the source code file is located. While I’m linking to things, it can be helpful sometimes to simply open a database file and look at its contents. If you have Access installed on your computer you can look at the resulting database file immediately, otherwise I can recommend a small, lightweight utility called Database .Net. It doesn’t require an installer, supports a bunch of different DBMS, and does not impact a computer’s registry so can run from anywhere. I like to keep it on a USB thumb-drive in case I need to take a quick look at the contents of a database.

Finally, here are a couple more links for books that I recommend for learning more about the issues we rushed through in this post. I really like The Practical SQL Handbook because it covers all the important points in a readable, entertaining way. A dry college text, this is not. Designing Quality Databases With IDEF1X Information Models, on the other hand, is a college text that goes into a tremendous amount of detail on the topic of data modelling and a common technique for documenting what you find — which of course makes it very through. This book is not a quick read, but it is worth the effort.

All we have left to cover now is the LabVIEW side of the job, which we’ll get into with the next post.

Until next time …

Mike…

Managing Data — the Easy Way

As I work on this blog there are times when I have to put certain topics on hold because the infrastructure doesn’t yet exist to allow me to effectively cover the topic. Well, this is one of those times. What I want to do is start getting into some topics like advanced user interfaces. However, these capabilities presume the existence of a data management capability that our testbed does not yet possess. To begin filling in that blank, the next few posts are going to start looking at techniques for utilizing databases as a data storage mechanism for LabVIEW-based applications.

But why databases? Wouldn’t it be easier to store all our configuration or test data in text files? When considering the storage of any sort of data the first thought for many developers is often to just, “…throw it in a text file…”. Therefore, this question needs to be the first thing that we discuss.

The Ubiquitous INI File

The INI file, as a method for storing configuration data, is a standard that has been around for a long time. Its main advantage is that it is a simple human-readable format that anyone with a text editor can manipulate. However, INI files also have a significant downside, and its number one problem is security. Simply put, INI files are inherently insecure because they use a simple human-readable format that anyone with a text editor can manipulate. Anybody who knows how to use Notepad can get in and play around with your program’s configuration, so unless you are very careful in terms of data validation, this openness can become an open door to program instability and failure.

A second issue is that while INI files can be subdivided into sections, and each section can contain key name and value pairs; beyond that simplistic formatting they are largely unstructured. This limitation shouldn’t be a surprise, after all the format was developed at a time when programs were very simple and had equally simple configuration requirements. However, with many modern applications one of the big challenges that you have to face when using INI files is that it is difficult, if not impossible, to store configuration data that exhibits internal relationships.

For example, say you have 2 or 3 basic types of widgets that you’re testing and each type of widget comes in 2 or 3 variants or models. In this scenario, some of the test parameters will be common for all widgets of a specific type while others are specific to a particular model. If you want to capture this data in a text file, you have two basic options. First you can put everything in one big table — and so run the risk of errors resulting from redundant data. Second, you can store it in a more normalized form and try to maintain the relationships manually — which is a pain in the neck, and just as error prone.

Text Data Files?

OK, so text files aren’t the best for storing configuration data. What about test data? Unfortunately, you have all the same problems — in spades — plus a few new ones: For example, consider Application Dependency.

Application dependency means that the data format or structure is specific to the program that created the file. Basically, the limitation is that outside programs have to know the structure of your file ahead of time or it won’t be able to effectively read your data. Depending upon the complexity of your application, the information required to read the data may expose more about the inner workings of your code than you really feel comfortable publishing.

Another problem is numeric precision. You can’t save numbers in a text file, just string representations of the numbers. Consequently, numeric precision is limited to whatever the program used when the file was created. So if you think that all you need when saving the data is 3 decimal places, and then find out later that you really need 4, you’re pretty much hosed since there is no way to recreate the precision that was thrown way when the data was saved.

Finally, data in a text file usually has no, or at least inadequate, context. Everybody worries about the accuracy of their test results, but context is just as important. Context is the information that tells you how to interpret the data you have before you. Context includes things like who ran a test, when it was run, and how the test was configured. Context also tells you things like what unit of measure to use in reading numeric data, or when the instruments were calibrated.

The Case for a Database

Due to all the foregoing issues, my clear preference is to use databases to both manage configuration data and store test results. However some LabVIEW developers refuse to consider databases in a misguided effort to avoid complication. Out of a dearth of real information they raise objections unencumbered by facts.

My position is that when you take into consideration the total problem of managing data, databases are actually the easiest solution. Of course that doesn’t mean that there won’t be things for you to learn. The truth is that there will be things to learn regardless of the way you approach data management. My point is that with databases there is less for you to learn due to the outside resources that you can leverage along the way. For one simple (but huge) example, consider that you could figure out on your own the correct ways to archive and backup all your test data — or you could put the data into a database and let the corporate IT folks, who do this sort of thing for a living, handle your data as well.

So let’s get started with a few basic concepts.

What is a DBMS?

One common point of confusion is the term DBMS, which stands for DataBase Management System. A DBMS is software that provides a standardized interface for creating, maintaining and using databases. In a sense, the relationship between a DBMS and a database is exactly the same as the relationship between a word-processor, like Microsoft Word, and a letter home to your Mom. Just as a word-processor is a program for creating textual documents, so a DBMS can be seen as a program for creating databases. One big difference though, is that while word-processors are programs that you have to explicitly start before you can use them, a DBMS will typically run in the background as a Windows service. For example, if you are reading this post from a Windows-based computer, there is at least one DBMS (called Jet) running on your computer right now.

What is a database?

A database is a structured collection of data. In that definition, one word is particularly important: “structured”. One of the central insights that lead to the development of databases was that data has structure. Early on, people recognized that some pieces of information are logically related to other pieces and that these relationships are just as important as the data itself. The relationships in the data are how we store the data context we discussed earlier.

By the way, as an aside, people who talk about an “Access Database” are wrong on two counts since Access is neither a database or a DBMS. Rather is it an application development environment for creating applications that access databases. By default, Access utilizes the Jet DBMS that is built into Widows, but it can access most others as well.

How do you communicate with a database?

It wasn’t that long ago that communicating with a database from LabVIEW was painful. The first product I ever sold was an add-on for the early Windows version of LabVIEW that was built around a code interface node that I had to write using the Watcom C compiler. Actually, looking back on it, “painful” is an understatement…

In any case, things are now dramatically different. On the database side the creation of standards such as ODBC and later ADO (also called OLE DB) provided standardized cross-vendor interfaces. For their part, National Instruments began providing ways of accessing those interfaces from within LabVIEW (starting with Version 5). Today accessing databases through ActiveX or .net interfaces is a breeze. To demonstrate this point, I’ll present a package of drivers that I have developed and posted on the user forum several years ago.

Getting Connected…

The VIs we will now discuss are the core routines that you will need to interact with any DBMS that supports an ADO interface — which is basically all of them. The only common DBMS that doesn’t support ADO is SQLite. Instead, it has its own DLL that you have to access directly. Still, if you want a very lightweight database engine that will run on nearly anything (including some real-time hosts) it is a good choice and there are driver packages available through the forum.

Getting back to our standard interface, the following six routines provide all the support most applications will ever need. One thing to notice is that with the exception of one subVI that requires added code to work around a bug in certain version of SQL Server, most of the code is very simple, which is as it should be.

To start that exploration, we’ll look at the routine that is the logical starting place for any database interaction — and incidentally is the only routine that cares what database you are using.

ADO Database Drivers.lvlib:Acquire Connection.vi

The start of any interaction with a database, naturally involves establishing a connection to the DBMS that is managing it, and then identifying which specific database you want to use. This VI calls the ADO method that performs that operation.

Open Connection.vi

You’ll notice that the ActiveX method only has one required input: the Connection String. This string is a semicolon-delimited list of input parameters. Although the exact parameters that are required depends on the DBMS you are accessing, there is one required parameter, Provider. It tells the ADO functionality what driver to use to access the DBMS. This is what a typical connection string would look like for connecting to a so-called Access database.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=;Password=;

Note that in this case the Data Source consists of a path to a specific file. For another DBMS this same parameter might point to an IP address, a logical name the driver defines or the name of a Windows service. But what if you don’t know what connection string to use? Well you can ask the DBMS vendor — or you can check: www.ConnectionStrings.com. Yes, there is an entire website dedicated to listing connection strings, and it covers nearly every ADO-compatible DBMS on the planet.

ADO Database Drivers.lvlib:Execute SQL Command.vi

OK, you are connected to your DBMS and you have a reference to your database. The next question is what do you want to do now? One answer to that question would be to send a command to the DBMS telling it to do something like create a new table in which you can store data, or add a new record to an existing table. This VI meets that need by sending to the DBMS command strings consisting of statements written in a language called SQL.

Execute SQL Command.vi

The core of this routine is the connection Execute method. Because this method could be used to execute a command that returns data, the 0x80 input optimizes operation by telling the method to not expect or handle any returned data. The method, instead, returns a count of the number of rows that the last command impacted.

ADO Database Drivers.lvlib:Create and Read Recordset.vi

While sending commands is important, the most common thing to do with a database connection is to use it to read data from the database. To optimize operation when you have multiple users concurrently accessing the same database, ADO creates something called a recordset. A recordset is a memory-resident copy of the requested data that you can access as needed without requiring further interaction with the database itself.

Create and Read Recordset

The three subVIs shown create a recordset using a source string consisting of an SQL query, reads the recordset’s contents, and then closes the recordset to free its memory. For details of how these subVIs work, checkout the comments on their block diagrams.

ADO Database Drivers.lvlib:Release Connection.vi

Once you are finished using a connection, you need to tell ADO that you are done with it.

Close Connection.vi

Note that although the method called is named Close, it doesn’t really close anything. Thanks to built-in functionality called connection pooling the connection isn’t really closed, rather Window just marks the connection as not being used and puts it into a pool of available connections. Consequently, the next time there is a request for a connection to the same database, Windows doesn’t have to go to the trouble of opening a new connection, it can just pull from the pool a reference to a connection that isn’t currently in use.

ADO Database Drivers.lvlib:Start Transaction.vi

A topic that database folks justifiably spend a lot of time talking about is data integrity. One way that a DBMS supports data integrity is by ensuring that all operations are “atomic”, i.e. indivisible. In simple terms, this means that for a given operation either all the changes to the database are successful, or none of them are. This constraint is easy to enforce when inserting or modifying a single record, but what about the (common) case where a single logical update entails adding or modifying several individual records?

To handle this situation, DBMS allows you to define a transaction that turns several database operations into a single atomic operation. This VI marks the start of a transaction.

Start Transaction.vi

To mark the other end of the transaction, you have to perform either a Commit (make all the changes permanent) or a Rollback (undo all the changes since the transaction’s start).

ADO Database Drivers.lvlib:Rollback Transaction on Error.vi

This VI combines the Commit and Rollback operations into a single routine. But how does it know which to do? Simple, it looks at the error cluster. If there is no error it commits the transaction…

Commit Transaction on no Error

…but if there is an error it will rollback the transaction.

Rollback Transaction on Error

What’s Next

Clearly the foregoing six VIs are not a complete implementation of all that is useful in the ADO interface — for instance, they totally ignore “BLOBs”. However I have found that they address 95% of what I do on a daily basis, and here is a link to the new version of the toolbox that includes the drivers.

Toolbox Release 3

But let’s be honest, having an appropriate set of drivers is in some ways the easy part of the job. You still need a database to access and you need to know at least a bit about the SQL language. This is where what I said earlier I said about leveraging the time and talents of other people come into play. Your corporate or institutional IT will often assist you in setting up a database — many times, in fact, they will insist on doing it for you. Likewise, with an interface that is based on command strings written in SQL, you will find a wealth of talent willing to lend a hand, perhaps in-house but certainly online. Still, having said all that, it’s good for you to understand at least a bit about how these other aspects operate. Therefore, next time we’ll create a small local database (using Jet) and start implementing the data management for our testbed application.

Until next time…

Mike…