Home / Resources / SQLTags Guide for existing FactorySQL and FactoryPMI Users

SQLTags Guide for existing FactorySQL and FactoryPMI Users

SQLTags™: A Guide for Existing FactorySQL™ and FactoryPMI™ Users

SQLTags represents a significant change in architecture for FactorySQL and FactoryPMI. While our core architecture of web-launched clients and database-centric data storage hasn’t changed, the techniques used for configuring status and control screens have changed greatly. This guide is meant as a primer for existing users of FactorySQL and FactoryPMI. It will explain the design rationale of SQLTags, introduce the new features, and to provide some motivation for why it might be in your best interest to upgrade your existing system.

The Basics

SQLTags is an underlying technology in FactoryPMI 3.0 and FactorySQL 4.0. These versions are completely backwards compatible. SQLTags works by creating a high-performance tag database inside your existing database. It does this by creating new tables that both FactorySQL and FactoryPMI will use. Because both FactorySQL and FactoryPMI “understand” how these tables work, you (the designer) no longer need to be concerned about writing SQL queries to interface with your data. Rather, you simply deal with “tags”. In essence, we’ve more tightly coupled communication between FactoryPMI and FactorySQL, but all data is still in the database so that we don’t lose any of the much-loved flexibility, scalability, and simplicity of our database-centric architecture.

It is important to note, however, that SQLTags currently only deals with real-time status and control data. All history, downtime tracking, etc is accomplished in the same manner as before. There are future plans for a historical addition to SQLTags.

Design Rationale

  1. Ease-of-Use. We wanted to decrease the learning curve for using our software for basic HMI-type screens. Specifically, we wanted a new designer to be able to set up a status and control screen without leaving the FactoryPMI Designer, and without writing a single SQL query.
  2. Status Feedback. Our existing status and control architecture was highly de-coupled. It was so de-coupled, in fact, that status information was typically lost without expending extra design effort to capture it. For instance, if a write failed to OPC, the operator wouldn’t know about it. If the PLC was unplugged, the operator wouldn’t know that the numbers s/he was looking at shouldn’t be trusted. SQLTags completely fixes this.
  3. Scalability. Our existing architecture involved polling simple SQL queries to retrieve real-time status information. This didn’t scale very well, because each concurrent client was running its own queries. This put a large strain on the database as more clients were launched. While there was a nice technique to mitigate this problem, it wasn’t immediately obvious. SQLTags improves scalability dramatically.

How You Use Tags

    SQLTags introduces a new panel to the FactoryPMI Designer: the SQLTags Tree Browser. This panel gives you access to all the tags in your system. In addition to the tags stored in your database, you also have client tags and system tags. There are actually 4 different types of tags:
  1. OPC Tags. These tags are stored in the database, and driven by FactorySQL from an OPC server.
  2. DB Tags. These tags are also stored in the database, but aren’t attached to OPC. They can simply be values accessible by all clients, can be calculated via an expression, or be the results of a SQL query.
  3. Client Tags. These tags are stored in your FactoryPMI project itself. Each client will have its own copy of these tags.
  4. System Tags. These tags are available to each client and provide client-specific system information, such as the currently logged-in username, system time, etc.

Tags are addressed by their path, which looks like this:

[MyDB]EastSection/TankFarm/Tank15.value

The first part, in square brackets [MyDB], is called the source. Typically this will be the name of your datasource connection, or simply blank to use the project’s default datasource. The next part is the path to the tag with forward slashes as folder delimiters. Lastly, one of the tags properties is referenced (in this case, “Value”). If no property is present, Value is assumed. Tags have lots of properties, such as Value, Quality, Documentation, EngUnits, etc. See the documentation for a full list.

Tags are used in your FactoryPMI projects via the familiar property-binding paradigm. Just like you are used to binding properties to expressions or SQL queries, you can now bind properties to tag values. You can bind to most properties of a tag. You can also use tags in expressions and SQL query bindings.

Even better, you can simply drag a tag and drop it onto a component. Depending on the component and the type of the tag, appropriate property bindings will be added. Or, if you drop a tag onto a container, it will prompt you to create an appropriate component to display that tag. Lastly, you can drag a tag onto a property, which will add a binding to that property.

The Overlay System

Each tag has a quality. There are many different qualities defined (see the documentation), but some common ones are:
  • OPC_BAD_DATA: OPC is reporting that the data is bad, probably due to communication issues with the PLC
  • GOOD_DATA: The tag is working well
  • STALE: The value is stale. Either FactoryPMI isn’t fetching the value because you’ve turned the datasource communication off, or FactorySQL isn’t running.
  • CONFIG_ERROR: Something is wrong with the configuration of the tag.

New with SQLTags is a status overlay system. Each component in FactoryPMI will calculate its quality based on the worst quality of any tag bindings configured on it. If the quality is anything but GOOD_DATA, you’ll get a noticeable visual overlay on top of the component. This is a crucial part of SQLTags – with no extra effort, you get reliable status indication on every component on your screen.

OPC Browsing

With SQLTags, you can create a new tag manually by pressing the “New Tag” button on the SQLTags Browser Panel’s toolbar. Even better, though, you can now browse your OPC servers from the Designer! Simply open the OPC browsing panel and your FactorySQL instance will be listed. From there you can browse the OPC servers that are available. Drag OPC items into the SQLTags tree to create OPC tags based on those items.

Writing to Tags / New Control Buttons

Writing to tags is very simple – simply check the “bidirectional” checkbox on your tag binding. This is applicable for bindings on properties that change as the user interacts with a component, such as a numeric input field. There are also 3 new control buttons in this release that are made specifically to work with SQLTags:

  1. One-Shot Button. This button writes a value to a tag, and then waits for the value to be reset by the PLC.
  2. Two-State Toggle: This button is great for tags that toggle between two values (such as 0 and 1). It will display differently based on the current state, and is configurable with the Styles feature.
  3. Multi-State Button: This button is really a series of buttons. Each button represents a value of a multi-state tag. For instance, 1=Hand, 0=Off, 2=Auto. The states and how they are displayed are configurable via the button’s Customizer.

All 3 of these buttons can be configured simply by dropping the appropriate tag onto them.

Scalability

As mentioned before, SQLTags is a highly scalable system. The load to the database, which has until now been variable with the number of clients, is now fixed. The Gateway handles all SQLTags polling activities irrespective of how many clients are running. Clients then poll the Gateway, which is highly optimized to handle a large number of concurrent clients.

As of this writing, we hadn’t performed formal benchmarks yet, but preliminary benchmarks look promising. A quick in-office test with FactorySQL, FactoryPMI, and an un-tuned MySQL database running on a desktop machine served 93 clients running a load test application that handled over 35 tag changes per second with under 50% CPU utilization.

Touchscreen Components

All input components are touchscreen-enabled by default now. No more need to handle touchscreen events with mouse scripts anymore. Don’t worry, this was done in a backwards-compatible manner – your old scripts won’t step on the toes of the new automatic system.

Conclusion

SQLTags completely obsoletes the “old way” of implementing status and control. Using SQLTags, one can implement a more scalable, more reliable status and control screen in much less time compared to FactoryPMI 2.0. The good news for existing users is that it is backwards compatible, but also easy to convert. If you want to convert your existing status and control screens, all you need to do is drag your OPC items into the SQLTags tree, and drop the appropriate tags on top of your existing status and control components. Want to see more? Call us for a personalized web-demo.

published: 04/30/14