FactoryPMI/SQL Realtime Engine

One thing I found out is PMI does not have a realtime database engine. It solely relies on OPC tags for all data exchanges. The EXPRESSION inside of Action Items (from FSQL) are limited to just one basic statement. Creating a whole bunch of cascading action items can be very convoluted.

I would like to see a realtime scripting engine (with VBScript or JavaScript or etc.) and the ability to create global variables that can be exposed to any tasks within the engine. This idea is of course going back to the conventional "Tag" structures of typical HMI packages such as iFix and WW (sorry, I have to bring these up for comparison) where the clients can freely access these global variables (Tags). Currently in FSQL, one can write his own functions in .NET and call dlls directly from the actions but that seems to defeat the purpose of having an off-the-shelf package.

If the next version addresses these concerns then I think this software package will be the mother of all HMIs.

Everything else in the package looks extremely amazing! The database APIs and the client/server hooks are top notch.

Can you give an example of what you are trying to do? I use IA, and I access the tags directly (as SQL tags) from FPMI, and FactorySQL is used mainly for triggering more complex events that don't really have anything to do with the GUI.

Are you using one of the later versions that uses SQL tags?

Have you tried using client or DB type SQLTags in FactoryPMI?

I did answer a few of your other questions here.

surferb said:

Have you tried using client or DB type SQLTags in FactoryPMI?

I did answer a few of your other questions here.

Click to expand...

what I am referring here is not the GUI but the entire package as a whole (FactorySQL/PMI).

I have no problem with either module. It just seems to me there is a 3rd module missing called the Realtime Database Engine.

For the sake of discussion here I am not going to mention FPMI since it is simply just a client. Without operator intervention, the FPMI does almost nothing for the background tasks. I only want to talk about things that run automatically 24/7.

The FSQL uses 100's or 1000's of action items (with a return value) to do logic under Expression. These "mini functions" are good for something quick and dirty but will not be able to replace good programming languages or scripting exist in many modern HMI packages. These scripting engines are capable of handling extremely complex logic utilizing statements such as If, While, Switch, Case, etc. One can write many thousand lines of code to do whatever he needs to do instead of managing a bunch of little expressions all over the computing environment which could be a maintenance nightmare.
Imagine having to search for a logical bug from 800 action items made of expressions. This can get ugly.

On the other hand, if the .NET source code can be typed and compiled within these action item pages then I should rest my case.

Use Microsoft Excel for example. One can enter =IF (A1>5, "TRUE", "FALSE") at B1 but by no means this statement can replace the more powerful VBA code behind the scene where more complex tasks are done.

The Realtime kernel will manage all global variables in memory instead of hard drive dependent such as RDBMS. These global variables can be access from everywhere similar to an OPC item.

The FSQL cleverly borrows the Sim Driver from the OPC Server to emulate a Realtime Database. At this point I am not quite sure whether it's a good idea or not. The OPC item data types are not only limited, they are quite clumpsy since you have to define some unique USER(,,,,) or K00X addresses. What if you run out of addresses?

Again, right now I only see problems with the Expression part of the Action Item. The SQL Query part I do love although I have not gone though a complete review on it.

To answer Nathan's question about SQLTags in FPMI, yes, I do use those tags but I have no issue with the PMI client whatsoever. Once I kill the PMI clients I want the Realtime Data Engine with complex logic to live.

12brich said:

To answer Nathan's question about SQLTags in FPMI, yes, I do use those tags but I have no issue with the PMI client whatsoever. Once I kill the PMI clients I want the Realtime Data Engine with complex logic to live.

Click to expand...

In a nutshell, a Realtime Data Engine as you describe is a pending feature request. Your current options include: writing the functionality in .NET and running that in FSQL or relying on FPMI clients to perform the task.

I recommend posting your feature request and feedback on the Inductive Automation Forum.

12brich said:

what I am referring here is not the GUI but the entire package as a whole (FactorySQL/PMI).

I have no problem with either module. It just seems to me there is a 3rd module missing called the Realtime Database Engine.

Click to expand...

The FactoryPMI Gateway is the Java application that runs 24x7 as a Windows service - it's responsible for running the embedded Tomcat web server and servlet that responds to FPMI client queries. That would be an ideal location to run user generated scripts. They would most likely be the same Python system including libraries and modules that the rest of the FactoryPMI system has currently.

12brich said:

For the sake of discussion here I am not going to mention FPMI since it is simply just a client. Without operator intervention, the FPMI does almost nothing for the background tasks. I only want to talk about things that run automatically 24/7.

Click to expand...

Constantly running FactoryPMI client(s) running scripts via Global Event Scripts would do the trick. It's not ideal, Though.

12brich said:

The FSQL uses 100's or 1000's of action items (with a return value) to do logic under Expression. These "mini functions" are good for something quick and dirty but will not be able to replace good programming languages or scripting exist in many modern HMI packages. These scripting engines are capable of handling extremely complex logic utilizing statements such as If, While, Switch, Case, etc. One can write many thousand lines of code to do whatever he needs to do instead of managing a bunch of little expressions all over the computing environment which could be a maintenance nightmare.
Imagine having to search for a logical bug from 800 action items made of expressions. This can get ugly.

Click to expand...

True statement. FactorySQL Action Items are not intended to be fully functional scripts - they are more like expressions.

12brich said:

On the other hand, if the .NET source code can be typed and compiled within these action item pages then I should rest my case.

Click to expand...

You can write your own functions in .NET and call them with Action Items. Check out the UDP Monitor Plugin here for an example of that.

12brich said:

The Realtime kernel will manage all global variables in memory instead of hard drive dependent such as RDBMS. These global variables can be access from everywhere similar to an OPC item.

Click to expand...

Client SQLTags run in memory on the FPMI gateway as you describe. FSQL and FPMI are fairly closely tied to RDBMS systems by design (database-centric model). They support redundancy features so that the system will continue to run with single machine (including hard drive) failures. Ultimately, everything is stored in an SQL database to maintain state.

12brich said:

The FSQL cleverly borrows the Sim Driver from the OPC Server to emulate a Realtime Database. At this point I am not quite sure whether it's a good idea or not. The OPC item data types are not only limited, they are quite clumpsy since you have to define some unique USER(,,,,) or K00X addresses. What if you run out of addresses?

Click to expand...

Humm - this I'm not sure about... As I understand, the OPC Sim Driver is included as a simulated OPC data source for those users who haven't connected real PLCs. I've never heard of it being used as a tag database - clever, though. SQLTags or SQL tables of your own schema are often used as the "tag database".

surferb said:

I appreciate hearing your insights regarding FactoryPMI.

In the most true terms, none of the mainstream HMIs have a realtime or deterministic database engine. FPMI users utilize an SQL database as that engine, typically Microsoft SQL Server (what all the other "enterprise" SCADA systems run), MySQL, Oracle, PostrgreSQL, or others. It's very fast, and often a good point for external (non-PLC) data exchanges.

Addressing your "Global Variables" - I think what you're looking for are DB or Client Type SQLTags. These are: treated as tags within FPMI, and support complex expressions. DB type are accessible via the SQL database, whereas "client" tags are more like globally accessible Dynamic Properties (or "Tags" in HMI-talk). Alternatively, developers can just use tables in the SQL database and direct queries.

A continuous scripting engine is missing in FactoryPMI. You can use the Python "Global Scripts" to accomplish this, but the catch is that they must run in a Client, not the Gateway, which would be ideal. This is an existing feature request.

Click to expand...

1) We do work for some of the large public agencies out here in California and the #1 requirement for a qualified Supervisory Control Package is to have a RTDB or Realtime Database Engine. Although there is latency involved depending on hardware and OS resources, the RTDB is very near Realtime. We never missed a single calculated alarm point in a 200,000 point RTDB.

2) We use MS SQL Server intensively in the last 10 years on all applications so we know what it does. We were planning to use PSQL to spill 10 second snapshot data into SQL Server 2005, our many stored procedures will pick up from there. Believe me, we do need to browse the results back out once it's done computing.

3) SQL (hard disk) transactions are good for other purposes. It will not be able to replace the RTDB which orbits in memory. We wouldn't have this discussion otherwise.

4) It doesn't make sense to go in and out of MS SQL Server all day long since memory computing tasks are so much more efficient. Our RTDB code runs every 1 second and it has to typically solve no less than 5000 lines of code. Many realtime alarms are generated from these code modules.

5) The existing SQL language does not support arrays. You have to create temp tables to emulate them and drop them when you are done (and pray that you do not ended up with any left over for mem leak). Again, more reason for using RTDB variables where multiple element arrays are friendly. Maybe the next version of MS SQL will support array.

6) In my opinion, the FPMI is fine. It is best to remain as a client. Jython can be used as window scripts for some fun or interactive stuff. I don't see the need for it to be part of the Gateway. The RTDB (if ever implemented) however shall reside at the Gateway and use Jython as a computing language for all Realtime tasks.

7) We were about ready to port all of our applications over to FSQL/PMI until we discovered the above inconveniences.

What are your applications and RTDB currently implemented in?

As an idea, you can implement "memory tables" in MySQL - I think there's an equivalent in SQL Server that runs mostly in memory - not all activity goes through the disk.

for #5 the SQL language isn't what supports or doesn't support arrays. You may want to look into an Object database instead of a relational one.

<<<<Humm - this I'm not sure about... As I understand, the OPC Sim Driver is included as a simulated OPC data source for those users who haven't connected real PLCs. I've never heard of it being used as a tag database. SQLTags or SQL tables of your own schema are often used as the "tag database".>>>>

About the Sim Driver consider the following action item:
-------------------------------------------------
[ActionItem1 - Using Expression]

if ((OPCItem1 + OPCItem2) > 2, "True","False")

//*My syntax could be wrong here but you got the idea

//Store the result to OPCItem3
[OPCItem3]
------------------------------------------------
Now if you didn't create the "OPCItem3" using a Sim Driver address of S001, what would you use? It doesn't make sense to assign "OPCItem3" to a real PLC address say ST20:1.

You mentioned earlier that I should try SQLTag, how would I use that in this case?

P.S.: I will later animate a label in FPMI with OPCItem3.

We do a lot of work for the EPA which we may not be able to discuss custom tools with you over the internet, but I can tell you that it is a custom application that were ported over from the old Unix days. It is now running under Win2k3. The RTDB were written with C++. We had looked at Wonderware App Server and Siemens Factorylink. They are absolutely qualified as RTDB's. I like your software best in terms of remote management and licensing.

I've never heard of memory tables in SQL Server 2005.

Our application has lots of SQL Server code behind it. Custom reports were written along with data accessing from other client tools. Too late to use non-RDBMS.

1. Create a new DB type SQLTag called Item3
2. Under Expression/SQL, select Expression mode.
3. Build the expression with the insert tag/operators/functions buttons. It will look something like this:

if({[.]OPCItem1}+{[.]OPCItem2} > 2, "True", "False")

This item will evaluate continually, regardless of the state of FPMI clients.

To animate a label, drag Item3 from the SQLTags browser to your window in FactoryPMI.

Alternatively, you could use an FSQL Action item that runs the same expression. You can write back the result to an OPC address or to the SQL database directly.

12brich said:

<<<<Humm - this I'm not sure about... As I understand, the OPC Sim Driver is included as a simulated OPC data source for those users who haven't connected real PLCs. I've never heard of it being used as a tag database. SQLTags or SQL tables of your own schema are often used as the "tag database".>>>>

About the Sim Driver consider the following action item:
-------------------------------------------------
[ActionItem1 - Using Expression]

if ((OPCItem1 + OPCItem2) > 2, "True","False")

//*My syntax could be wrong here but you got the idea

//Store the result to OPCItem3
[OPCItem3]
------------------------------------------------
Now if you didn't create the "OPCItem3" using a Sim Driver address of S001, what would you use? It doesn't make sense to assign "OPCItem3" to a real PLC address say ST20:1.

You mentioned earlier that I should try SQLTag, how would I use that in this case?

P.S.: I will later animate a label in FPMI with OPCItem3.

Click to expand...

Sorry I seem to waste so much of your time but I admit I do enjoy the discussion.

Right now, I'm torned between the 2 IDE's (FSQL and FPMI), each has pros and cons:

FPMI can create DBTag but its tags are just simply expressions. Each tag expression runs continously with no trigger controls even when PMI client is shutdown. FPMI global database does not seem to have triggers. It can call runScript() but I have no idea where the scripts are stored and what they can do. This obviously cannot call .NET
functions since it's Java based.

FSQL can only bind expression result to OPC tag. It has much more flexibility in terms on triggering (on time or change in value) and the ability to create groups similar to scan ONLINE/OFFLINE. It can call custom dll functions created in .NET but that requires external tools, not very ideal in an integrated development environment.

Consider the following PSEUDO code:

[Alarm Code]

for ii = 1 to 100, ii++
{
_alarm[ii] = ProcessVar[ii] > ProcessSp[ii]

if _alarm[ii] then AlarmMsg[ii] = "Trouble"
}


{DbTag1.Value} = AlarmMsg[1]
{DbTag2.Value} = AlarmMsg[2]
{DbTag3.Value} = AlarmMsg[3]
...
{DbTag100.Value} = AlarmMsg[100]

--------------------------------------
Where would I put this code? Things can get a lot more complex than the above. It would be nice if the Global Event Scripts can be moved to the gateway with all triggers available similar to FSQL. That would nicely address all concerns.

I'll get back to you with more specifics. This one is a little over my head, so I'll check with the big heads at Inductive Automation.

You could probably use Python in FPMI to accomplish that task. Again, keep in mind that a client needs to be running - you could set one up to run automatically.

You could run through a loop in Python that dynamically builds the tag paths and runs your conditions. Are you expecting to see those error messages on your cell phone/email via the alerting? I'll whip up a code sample and check into alerting with dynamic paths later.

There is development in the works that should meet your needs more directly.

Python is good.
Cell phone message & email alerting are great.

We'll wait for some new development from IA.

Surferb,

I believe I found a solution to our real-time database (RTDB) problem. We are going to build our RTDB in .NET and complete it with an OPC Server/Client wrapper. The Client module will exchange data with PLC OPC Servers. Within .NET environment, we are now free to do thousands of calculations and interface with SQL database.

Since I conversed with you last, I was able to create the OPC Server module within our engine. I am going to work on the Client side next week.

I tested our OPC Server (RTDB engine) with your FactoryPMI today and it came in perfect. We still need to use your PMI product for all the cool things that it offers. What we don't need now is FactorySQL.

What do you think about this concept? Are we heading in the right direction? Do you see any drawbacks to this approach?

Sounds great! Your requirements exceed that of most users, so a custom application makes sense as the engine. Using standard technologies is also a plus - OPC Server, SQL database, etc. I would recommend using packages for the "typical" portions - don't think that you have to write the whole thing yourself because you have custom code performing some task.

The onus goes on you to ensure that you do OPC block (array) reads, efficient SQL writes, manage threads/connection pools well, etc. I would recommend using packages to the extent that you can - like a .NET OPC library. FactorySQL also does things like local data caching, elegant network reconnections, and error handling/recovery. Of course you could implement anything relevant in .NET - just don't underestimate the time required to develop and maintain this in the future.

There are many users who use FactoryPMI as an SQL database interface without OPC or FactorySQL - there is no problem with this. The only catch is that the only SQLTags provider for FactoryPMI resides in FactorySQL (for OPC-DA, DCOM type reasons - this should change with UA).

Bottom line, FPMI works well alone and should work great with your custom engine, which isn't mutually exclusive with FactorySQL. In other words, any combination of the 3, including all, will work fine together.