LVRT and Relational Databases

[GoGators]

At the request of my sales force and some customers I am going to be writing an article and examples on NI Real-Time targets and Relational Databases (Historical databases seem to be pretty much covered by Citadel). The idea is to generate some starting points, an provide some examples on how-to. After speaking with some peers I have some good ideas, and wanted to bounce them off you Lavanians (do we have a name for LAVA posters?)

Before I get too deep I have to ask the most important question. Is this useful. So has anyone out there needed Database support on RT? Ideally there would be information about every topic you could come up with, but the reality is that if no one needs it I am going to use my time on something else.

OK assuming someone needs it, there are three scenarios I am pretty sure I am going to talk about.

1) The current NI recommended way to have databases on RT Targets is to make a communication layer between your RT and Host. Then have the host do all the real database operations, and the RT is just passing the information along. I have an example that has the RT send data over TCP to be recorded to a blank .mdb using the Database Connectivity toolkit. Then after some time the RT target requests something like 100 records to use in decision for state machine. Pretty straight forward, and shows that an SQL statement can be passed from RT to host without any translation of data on the host-side (in otherwords the host does not need to know anything about the data, it just works with variants).

2) The RT target makes files in a specific format, then the host retrieves the files periodically and places the data into the database. Some FTP operations, and thats about it. The file format is tricky, so I am still deciding on what to make it.

3) The coolest one by far, make RT targets actually support databases by porting SQLite to Pharlap and VxWorks targets. There has been some talks about it but I didn't see anything definitive on OpenG. Currently trying to get SQLite to build as a DLL in CVI.

SO:

A) Anybody actually going to use this stuff, or am I wasting time.

B) Does the three above meet pretty much all the scenerios you can think of?

C) Anyone been working on the SQLite thing lately?

[Nick S]

A) Anybody actually going to use this stuff, or am I wasting time.

Although I don't have an immediate need for this, this is something that I guess I would have assumed already had a solution and wouldn't have thought about it until I wasn't able to do it

So I know I would definitely be interested in reading more about this. It never hurts to have more tools in the toolkit.

C) Anyone been working on the SQLite thing lately?

I'm just slightly confused what you mean to do with this. Do you mean to have your LV program transact with the local SQLite instance and then clone the data on the RT over to another machine, or is there functionality for network databases in SQLite that I don't know about?

[Tim_S]

A) Anybody actually going to use this stuff, or am I wasting time.

We just had another virus hit a customer machine, taking production test down. This is a hazard of using Windows on a plant floor where the data has to get to the plant network (especially in areas where IT practices are not so stringent). I'd have to sell people on why it's a good idea to add some thousands of USD to the project cost, but being able to eliminate this type of event would be a huge selling point. One reason I've not been able to sell it is I can't get the test data to a database directly from RT; "why add a second PC to a system when you've already got one".

[GoGators]

I'm just slightly confused what you mean to do with this. Do you mean to have your LV program transact with the local SQLite instance and then clone the data on the RT over to another machine, or is there functionality for network databases in SQLite that I don't know about?

Sorry. I should have given a little history. If you look through old lava posts, people have talked about doing an OpenG project where they make SQLite work in LV. The idea was to make a DLL, which would work for PharLap RT controllers (essentailly RT-PXI, and some older FPs and cRIOs). I don't know what happened to that project, but I didn't see anything on OpenG.

And yes, since SQLite is ideal for embedded systems, store the database in a single file locally. Since SQLite is connectionless, I would assume it would stay connectionless.

[Nick S]

If you look through old lava posts, people have talked about doing an OpenG project where they make SQLite work in LV. The idea was to make a DLL...

Okay, I understand now. I will have to look into those old posts - having a LV implementation of SQLite on RT targets would certainly be a worthwhile project. If you did a writeup on it I'd be very interested in reading it. We currently have a system that is logging all data as TDMS, but it would be a lot more intuitive (and easier to search) if it were in a relational database.

[Rolf Kalbermatter]

Sorry. I should have given a little history. If you look through old lava posts, people have talked about doing an OpenG project where they make SQLite work in LV. The idea was to make a DLL, which would work for PharLap RT controllers (essentailly RT-PXI, and some older FPs and cRIOs). I don't know what happened to that project, but I didn't see anything on OpenG.

And yes, since SQLite is ideal for embedded systems, store the database in a single file locally. Since SQLite is connectionless, I would assume it would stay connectionless.

The problem of this setup as it seems to me is that the data resides on the RT system. This has a number of limitations:

1) How do you backup that? No IT provided backup scheme is going to plug into your RT system easily to have that data backed up automatically.

2) How do you access the data? The data may be stored by the RT system but usually the results are not interesting to the RT system anymore but instead to your test department, calibration services, production management, etc. Accessing the data on your RT system by them will require them to use custom made tools by you to retrieve that data and copy it into a more normal database so they can perform their queries, and data mining tasks on them.

3) Assuming you do not need this data external to the RT system, why would you need a relational database at all?

That are the fundamental problems of this question.

There are also technical ones and building the SQLLite into a shared library that can run on those systems is probably not the biggest of them. More interesting to me would be the stress on the system caused by such a DB engine continuously running in the background, how long the various storage media (some are still pretty simple flash media) will last with a DB engine continuously reading and writing to it, etc.

Also assuming you want to do some really usefull stuff with that DB on the RT system, there is a lot more than just making the C code compile, link and run on the RT system. Making sure the resulting engine will really do the right thing independent of all the specialties and constrains of an RT system such as for instance endianess (yes the VxWorks RT targets are all PPC based and use big endian where as all the rest of LabVIEW nowadays uses x86 with little endian), will be likely a lot more work than to get the C code compile and run would be. You do not want to trust your vital data to a DB engine that will eventually crunch your data to an unrecognizable mess on some border condition.

All these are things that would need to be investigated very thoroughly before you start to spend many days of working power to port the C sources to compile and run properly on an RT system.

Can you explain to me your use case for an embedded relational database more clearly? I would like to understand that issue.

For what I have used DBs so far, it was either data logging (historical DB) or managing test results (where a relational DB may come in handy) but those data are typically needed by people that have no direct access to the RT system and so storing it in a network database is a much more sane approach. So what I usually do for test data, is to store the data locally in a buffer and cache it regularly in a simple binary file format. This data then gets regularly transferred to the host application and from there stored in a network database using an ODBC connection.

Rolf Kalbermatter

[Nick S]

Can you explain to me your use case for an embedded relational database more clearly? I would like to understand that issue.

I can't speak for GoGators, but for me at least the plan was never to leave the data in the database on the machine as I think you are thinking. Rather I would use it as a cache that provides atomic commits in cases where test data absolutely can't be lost. The problem with the binary file scheme that you talk about is if the system goes out to lunch during a write, you could end up with a corrupted cache file and lost data. True, it may only be a minute or two of data, but in some tests that is unacceptable. Because SQLite provides atomic commits, if the system goes out in the middle of a write, no problem - SQLite knows exactly how to handle the incomplete data and nothing is corrupted. Having the data already in relational database format also means an easier time cloning the data to a central database (where MySQL et al would be appropriate).

The other unique thing about SQLite versus systems like MySQL, MSSQL, Postgres, etc. is that its calls are all done directly through the DLL/shared library - there is no "engine" running in the background. It is specifically meant to be used as an embedded database and says so right on its main page.

I will say though that outside the caching scenario, leaving a large amount of data on an RT system seems unwise, especially cRIO's.

[Grampa_of_Oliva_n_Eden]

I have worked with two general type of RT apps. Those that wanted reliablity, the others wanted determinism. In my book determinism and relational databases are like "oil and watter", they don't mix. One take the attitude I'm going to do this now , to hell with saving" and the the other is "I'm going to log this stuff no matter how long and how much CPU it takes".

The reliablity apps were the ones that wanted relational DB and a low cost solution. So these were generally developed on FP or cFP. Putting a RDB on a FP controllers is asking a lot of the current technology (but some day....). I nthose cases I had a server that grabbed any new history data and released the files or buffers holding the history. So the RDB was on a Windoze machine. I have worked with more than one of these types. I have used DSC, SQL and a custom DB to do these apps.

So I trying to say your idea is ahead of the technology but if you prep now you could be ready when cheap targets have enough horse power to pull that plow.

Ben

[Rolf Kalbermatter]

I can't speak for GoGators, but for me at least the plan was never to leave the data in the database on the machine as I think you are thinking. Rather I would use it as a cache that provides atomic commits in cases where test data absolutely can't be lost. The problem with the binary file scheme that you talk about is if the system goes out to lunch during a write, you could end up with a corrupted cache file and lost data. True, it may only be a minute or two of data, but in some tests that is unacceptable. Because SQLite provides atomic commits, if the system goes out in the middle of a write, no problem - SQLite knows exactly how to handle the incomplete data and nothing is corrupted. Having the data already in relational database format also means an easier time cloning the data to a central database (where MySQL et al would be appropriate).

While this are all nice to have things, it is not such a big problem to structure your local caching in such a way that you can minimize possible dataloss, when your system decides "to go out for lunch". It's definitly not even close to the time you would have to spend to port SQLlite into a proven, reliable RT solution for your target. Of course this assumes that you would do the porting .

Hoping someone else will do it because of the nice technical challenge this provides would require less time on your part but has a very good change to happen somewhere between now and the end of all worlds . It would be different if such a solution would be possible to be commercialized but I see little chances for that.

The other unique thing about SQLite versus systems like MySQL, MSSQL, Postgres, etc. is that its calls are all done directly through the DLL/shared library - there is no "engine" running in the background. It is specifically meant to be used as an embedded database and says so right on its main page.

Well when I talk about an SQL engine I also consider a direct DLL implementation as such. For me it is not the fact of a deamon like implementation that qualifies as engine but the implementation itself. And I agree deamons are in general not a very good idea on an RT system, unless you know exactly what they do and how they could interact with your system in the worst case.

Rolf Kalbermatter

[Nick S]

Hoping someone else will do it because of the nice technical challenge this provides would require less time on your part but has a very good change to happen somewhere between now and the end of all worlds . It would be different if such a solution would be possible to be commercialized but I see little chances for that.

Same thing could be said of the possibility of having a stable, powerful PC operating system that is freely available AND distributes all the source code associated it, and last I checked it is not Armageddon (talking about Linux btw in case the reference was too oblique)

Kidding aside, I'm not waiting on this for a job that I currently have lined up, or one I am even planning on doing. GoGators asked if it'd be useful, and I was positing a use case. If he does it, great, I'll take a look. If he doesn't, the world goes on. If I'm bored some day (or in this case, week), maybe I'll look at it myself.

So I trying to say your idea is ahead of the technology but if you prepnow you could be ready when cheap targets have enough horse power topull that plow.

Perhaps some background - I work a lot with RT targets on PXI systems. PLENTY of horsepower there for running whatever you need (since PXI systems often have desktop-caliber processors in them). This probably isn't the best choice for a cRIO quite yet, but who knows - SQLite is pretty well, light. If someone tries it and it doesn't work, then they've at least gained the knowledge on the way. If it does work, great! Other than doing something that causes permanent hardware damage, software experimentation is pretty safe...

[Grampa_of_Oliva_n_Eden]

...

If someone tries it and it doesn't work, then they've at least gained the knowledge on the way. If it does work, great! ...

How can I possilbly knock that type of attitude?

Sea Story:

When VMS 4.0 was released the developer responsible to write the code taht decoded the error packets from hardware was behind schedule so quoting them "Oh, I was behind schedule so I commented that part out.". This left me with mainframes tha were crashing and the only clue was a pile of 1's and 0's. I brought the issue to my support people with the idea that I could write code to decode the packets and format a report etc. The Support Engineer's eye crossed as I started to outline the idea and he said to me "That project is over YOUR head." That satement pissde me off sooo much that I taught myself to program (VAX-11 Macro). That support engineer never said another word to me the rest of the time I was at DEC.

I soon followed that up by partnering with Robert J. Schmalstieg (now with HP network support) to write the first ethernet Sniffer starting in 1987 through 1988 (would have taken less than a week in LV). Our attitude at the time and still remains the same as what you have writtien above.

even if we failed, we learned something along the way.

Ben

[Christian_L]

3) The coolest one by far, make RT targets actually support databases by porting SQLite to Pharlap and VxWorks targets. There has been some talks about it but I didn't see anything definitive on OpenG. Currently trying to get SQLite to build as a DLL in CVI.

Just FYI, in Systems Engineering we have implemented this solution using SQLite for the newer VxWorks based cRIO targets. You may want to contact us before spending a lot of time on that implementation.

[FrankH]

Hi!

I read this thread Very interesting about the based topic to implement a database in a RT-System!

My topics at work are automation topics. Also with RT-systems in a stand-alone formation. For example a special interlock system at the ESRF facility in Grenoble (X-rays at a electron accelerator).

This system has only a web interface. lt based on sbRlO. And this system needs a process data logging system and a message logging system. For process data l use TDMS with pre processing to reduce data amount (dead band). For messages l use a special file and a translation table, since l need the visualization in German, English and France. The message record needs some columns. Time stamp, massage reference, status and place of origin. This is a typical DB- application. Why I should develop my one message DB ?

Either I do it really or Christian_L provide his solution. We will see.

Frank

Edited September 13, 2009 by FrankH

[GoGators]

Sorry for not looking over these posts earlier.

Like Christian_L said the VxWorks version is up and running. A simple LV API is already made. Still working on the PharLap version in CVI RT. Since I am still working on it the code it is still messy, but if anyone needs the VxWorks portion right now I can post what I got.

As a side note:

From my original post I ran into an interesting idea for #2. The file format I choose was ADO schema XML. It turns out the Database Connectivity toolkit has some Save/Load Recordset VIs in the Utilities palette. These Write and Read in XML. If you reverse engineer the schema you can make your RT target write files that can be read into database using the toolkit. Just something I noticed and implemented if anyone is interested.

[Rolf Kalbermatter]

As a side note:

From my original post I ran into an interesting idea for #2. The file format I choose was ADO schema XML. It turns out the Database Connectivity toolkit has some Save/Load Recordset VIs in the Utilities palette. These Write and Read in XML. If you reverse engineer the schema you can make your RT target write files that can be read into database using the toolkit. Just something I noticed and implemented if anyone is interested.

A very nice catch!

I'll have to look into this more closely. Would seem to me almost a perfect solution, provided the schema is not to complicated.

Rolf Kalbermatter

[GoGators]

Would seem to me almost a perfect solution, provided the schema is not to complicated.

Rolf Kalbermatter

The schema is not too complicated. To be honest I just wrote out a bunch of files on a windows machine, and recreated what they wrote out (simple read/writes). The documetnation on MSDN is pretty poor. I'm starting to think an XML parser on a real-time target would be a useful feature.

I published the article and examples here:

Supporting Relational Databases on LabVIEW Real-Time

Feedback should go http://forums.ni.com/ni/board/message?board.id=deployment&thread.id=11

[Christian_L]

I published the article and examples here:

Supporting Relational Databases on LabVIEW Real-Time

Corrected link: http://zone.ni.com/devzone/cda/epd/p/id/6287

[ShaunR]

3) The coolest one by far, make RT targets actually support databases by porting SQLite to Pharlap and VxWorks targets. There has been some talks about it but I didn't see anything definitive on OpenG. Currently trying to get SQLite to build as a DLL in CVI.

I agree. SQLite is made for these systems.

With my recent success with compiling for the Mac (and because a couple of people asked me about it recently), I decided to look into it. VxWorks is a long way off due to a lack of hardware for development and testing. However, I have an old copy of the Pharlap ETS so that is "supportable" in theory.

First step though is to get the unsupported kernel calls addressed and I have had some success with that. I'm now down to 3 (from 13). Can't for the life of me find where the AddAtomA, FindAtomA and GetAtomNameA are referenced so it must be an indirect dependency.

I know it's a while ago this was posted,and you succeeded in compiling for Pharlap. Have you looked at this more recently?

Edited June 27, 2012 by ShaunR

[Rolf Kalbermatter]

I agree. SQLite is made for these systems.

With my recent success with compiling for the Mac (and because a couple of people asked me about it recently), I decided to look into it. VxWorks is a long way off due to a lack of hardware for development and testing. However, I have an old copy of the Pharlap ETS so that is "supportable" in theory.

First step though is to get the unsupported kernel calls addressed and I have had some success with that. I'm now down to 3 (from 13). Can't for the life of me find where the AddAtomA, FindAtomA and GetAtomNameA are referenced so it must be an indirect dependency.

I know it's a while ago this was posted,and you succeeded in compiling for Pharlap. Have you looked at this more recently?

I thought, if the source code doesn't show these, we have to look into the binary. But there seems nowhere any reference to these three APIs. I've used the DLL Checker, a LabVIEW VI listing the import section of a DLL, Dependency Walker and even looked directly at the disassembly of the DLL, but I can find nowhere any reference to any of these three APIs.

So which DLL are you looking at, the official 3.7.14 from the sqlite site? How do you determine that these APIs are required? Attached is the report produced by the LV 2010 DLL Checker for the latest sqlite3.dll from their site.

Note that you will probably have to tackle the stubbed imports too, or at least check that there is no chance for the code to run through these code paths on an embedded system.

sqllite3.report.txt

[ShaunR]

I thought, if the source code doesn't show these, we have to look into the binary. But there seems nowhere any reference to these three APIs. I've used the DLL Checker, a LabVIEW VI listing the import section of a DLL, Dependency Walker and even looked directly at the disassembly of the DLL, but I can find nowhere any reference to any of these three APIs.

So which DLL are you looking at, the official 3.7.14 from the sqlite site? How do you determine that these APIs are required? Attached is the report produced by the LV 2010 DLL Checker for the latest sqlite3.dll from their site.

Bloody hell. They've released 3.7.14 already?I'm definitely going to have to separate out the binaries from LV source (which I've been thinking about for a while now).

I'm looking at the dll shipped with the SQLite API For LabVIEW (for obvious reasons). I too cannot find any references to them in the source and, the DLL Dependency walker says they are not bound. Still. The LV 2011 SP1 DLL Checker says they are there. If I run the DLL checker on SQLite.orgs DLL they are not listed (as you have shown), but if I build the dll from the source amalgamation (I use GCC under MingW) they appear. It isn't a straight comparison, however, since the API DLL has encryption and different compiler settings. It'll take me a while to incrementally build everything back up from the original amalgamation to where it is now to identify the "bit" that drags it in. It may even be the compiler dragging it in.

Note that you will probably have to tackle the stubbed imports too, or at least check that there is no chance for the code to run through these code paths on an embedded system.

Maybe. One step at a time. I'm not too worried about file system support since there are a plethora of methods built in to the SQLite API. Especially now that they support Windows 8 RT and WinCE (note that the RT stand for run-time not real time as I first thought...lol) . They have implemented #define switches for the differing WIN APIs. There is bound to be something but hell, "It's only software"

Edited June 27, 2012 by ShaunR

[Rolf Kalbermatter]

Bloody hell. They've released 3.7.14 already?I'm definitely going to have to separate out the binaries from LV source (which I've been thinking about for a while now).

Sorry I mistyped there, it is 3.7.13.

Are you adding any LabVIEW specific C wrapper code to the DLL? Because if you don't or separate that code into its own DLL, it is really just a drop in replacement of the sqlite3.dll.

I'm looking at the dll shipped with the SQLite API For LabVIEW (for obvious reasons). I too cannot find any references to them in the source and, the DLL Dependency walker says they are not bound. Still. The LV 2011 SP1 DLL Checker says they are there. If I run the DLL checker on SQLite.orgs DLL they are not listed (as you have shown), but if I build the dll from the source amalgamation (I use GCC under MingW) they appear. It isn't a straight comparison, however, since the API DLL has encryption and different compiler settings. It'll take me a while to incrementally build everything back up from the original amalgamation to where it is now to identify the "bit" that drags it in. It may even be the compiler dragging it in.

The GCC compiler itself is quite unlikely as it is in itself quite agnostic of the underlaying target platform. My guess would be the MingW C runtime libraries and here specifically the startup stub that wraps your DLLMain() function. It may be possible to avoid that by selecting a different C runtime option or target subsystem. Not sure if the MingW toolchain provides different subsystem options for DLL targets. I'm not really sure what build toolchain they use at sqlite themselves for the released binaries, but I have some doubt that Richard would be using anything not GCC based.

[ShaunR]

Sorry I mistyped there, it is 3.7.13.

Are you adding any LabVIEW specific C wrapper code to the DLL? Because if you don't or separate that code into its own DLL, it is really just a drop in replacement of the sqlite3.dll.

No. The SQLite amalgamation code is untouched. All differences are via the in-built defines or features. Yes you can just drop in the sqlite3.dll (renaming it of course). But there are features enabled that, are not enabled by default from the sqlite.org dll (foreign keys for example). The dll available from sqlite.org also doesn't support encryption. Up until now. I've just recompiled the binaries with the latest version when I released a new API version. That was fine whilst the API was fluid since my updates where faster than the sqlite ones. Now, however, the API is updated far less often than the binaries and since they are distributed as part of the API package and installer, I don't really want to update the API version just because there are new binaries. It's been coming for a while. I just need to get off my arse and do it

The GCC compiler itself is quite unlikely as it is in itself quite agnostic of the underlaying target platform. My guess would be the MingW C runtime libraries and here specifically the startup stub that wraps your DLLMain() function. It may be possible to avoid that by selecting a different C runtime option or target subsystem. Not sure if the MingW toolchain provides different subsystem options for DLL targets. I'm not really sure what build toolchain they use at sqlite themselves for the released binaries, but I have some doubt that Richard would be using anything not GCC based.

They probably use a x-compiler from linux (really must get me one of those ). So what you are saying makes sense. I'll do a grep on the Mingw source/includes and see what turns up now I know where to look.

[ShaunR]

OK. Reinstalling the GCC compiler with the latest version fixed the spurious references (0 bad, 6 stubs).

I'm set to go for the next step. Woot.

[Jordan Kuehn]

Yeah, this is an old thread, but I can't find what I'm looking for.  Any SQLite support for the newer linux based RT targets?

[ShaunR]

I think SQLite is shipped with the Linux RT. There is a how-to-update thread over on the dark-side at any rate.