Nick Mudge Ignition Software Consulting & Development

I created the Perfect Abstractions (PA) Power Scripting Module to make some things easier to do and to make new things possible.

I will give a free license for this module to the first five people that send me a good example of using it in some way.

Contents:

pa.db.insertRows

Have you ever had data in an Ignition table or dataset and wanted to insert the data into a database table?

Did you write a Python script that looped though the dataset row by row, inserting each row into a database table? Man, that is slow, especially if the script is running in a client.

The right way to do this is to create a single SQL query that contains all the rows to insert, for example:

INSERT INTO mytable (col1,col2) VALUES (?,?,?), (?,?,?), (?,?,?), (?,?,?)

That query inserts four rows of data into a database table (but such a query could insert many more rows). It is much, much faster than running four separate queries.

So you should aggregate multiple inserts into a single query when you can. But have you written the logic in Python to dynamically create such a multi-insert database query and arranged the values so they can be inserted? It is ugly and painful.

That is why I created the pa.dataset.insertRows function. Let it do the tiresome work of dynamically creating the single efficient database query needed to insert all the rows of your dataset into a database table.

pa.db.insertRows documentation

serverFunction

This is my favorite feature. It might be hard to learn or understand at first because it adds something new to Ignition. A new idea, that's not part of regular Ignition. But it is really worth understanding because it is awesome and because it allows you to do very valuable things that you could not do before.

It might also be hard to understand because it is a general capability. It allows you to do many different things so it can be easy to get lost in the generalness of it and not see the value. Kind of like a programming language. What good is a programming language? A programming language is so general that you can say things like, "A programming language allows you to do stuff, to make stuff". And to a lot of people that means nothing because it isn't specific enough. So don't let the generalness of this feature hide its value from you. And don't let specific examples of its use limit your understanding of what it could be used for.

serverFunction enables you to write a Python script that runs in the runtime Ignition client that calls a user-defined function that gets executed in the Ignition Gateway and returns its results back to the client. This is a remote function call from the client to the Gateway, with the return value returned back to the client. What can you do with this? That is up to your imagination.

You should really understand this so contact me personally if you don't understand after finishing reading this blog post.

Here is an example:

#First script
#This is in the project.util Python module.
@serverFunction
def getServerTextFile(filePath):
    textFileContent = system.file.readFileAsString(filePath)
    return textFileContent
#Second script
#This is in a visionWindowOpened component event script
filePath = "C:\\Users\\nick\\Documents\\MyTextFile.txt"
text = project.util.getServerTextFile(filePath)
root = event.source.rootContainer
root.getComponent("DocumentViewer").text = text

The "getServerTextFile(filePath)" function has been declared a server function. When it is called in the client it will be executed on the Ignition server and the results will be returned back to the client.

The second script executes when an Ignition window opens. The script retrieves a file from the Ignition server computer (not the client computer) and updates a Document Viewer component in the window with the text from the file. This is a nice way for a window to display the contents of a file from the Ignition server. Similarly a writeServerTextFile(filePath) server function could be written to save changes made in the client to files on the Ignition server computer.

Notice the "@" symbol before "serverFunction". You might wonder what that is. That is standard Python syntax for something that has probably never been used in Ignition before. That syntax is shorthand for creating a Python decorator, which is a function that takes as an argument a function and returns a function. In the case above the syntax is shorthand for getServerTextFile = serverFunction(getServerTextFile).

You can learn more about Python decorators by searching google for Python decorators. It isn't anything special.

@serverFunction replaces the function defined after it with a different function that makes a remote function call to the Ignition Gateway to the function with the same name that exists in the Gateway.

Three Great Uses of serverFunction

1. As you saw in the example above serverFunction provides a great way for runtime clients to retrieve resources that exist on the Ignition server computer. serverFunction can also be used to send data and resources from clients to an Ignition server computer.

2. There is another fantastic use of serverFunction that is completely different: improve performance of multiple dependent database queries. It sometimes happens that multiple database queries that depend on each other need to be run in a Python script in a project. This creates a performance problem because each query can only execute after the one before it has completed executing. Each query needs to be sent across a computer network, which could include the Internet, to an Ignition Gateway, executed and the results need to be sent back to the client. The back and forth network traffic nature of queries makes Python scripts run slow. If it takes 1 second to execute a database query in an Ignition client and a script needs to execute 5 of them then the Python script will take at least 5 seconds to execute which in many cases is a noticeable and annoying lag or frozen screen for users.

In the past the solution has been to write an ugly database stored procedure, which gets hidden away from Ignition. This isn't a good solution because stored procedures don't get saved with an Ignition project and it is much nicer to implement logic in Python than in a SQL langauge.

A server function can solve this problem. Often an Ignition Gateway is installed on the same computer server as the database server or close to it. This enables very fast communication between the Ignition Gateway and the database server. Therefore a client can call a server function that executes multiple queries. Now the queries run fast because they are executed in the Ignition Gateway very near the database server. There have been a number of times I wished I could send multiple database queries to the server to be executed together, now this can be done.

3. It is also possible for a client to call a server function that is defined in a different project than the one running in the client. This allows functions to be grouped into appropriate projects but they can still be called from other projects if needed.

serverFunction documentation

@run

@run provides a nicer syntax for calling system.util.invokeLater(function) and system.util.invokeAsynchronous(function) functions.

system.util.invokeLater(function) is used for two different things. It is used to execute a function after all events and bindings have completed in a client. This is sometimes useful in the "visionWindowOpened" window event script because this script executes before bindings. This script might set properties in the window which will get overwritten when bindings are executed. Defining a function in this script and executing it with invokeLater prevents component properties that have been assigned values in the script from getting overwritten by bindings.

The underlying programming framework used by Ignition clients is Java Swing. Java Swing is single threaded meaning all GUI access and interaction is done on a single thread called the Event Dispatch Thread or EDT for short. No other thread is supposed to touch GUI components.

But sometimes other threads are needed in Ignition clients. If an operation (like getting data from somewhere or making a lot of calculations) takes a long time to execute and the code is running in the EDT (GUI Thread) then the screen will freeze for the user and this is not good. No one likes a frozen screen for any length of time. And when something is taking a long time it is nice to be notified graphically of the progress. This is where the system.util.invokeAsynchronous(function) function comes in. system.util.invokeAsynchronous(function) executes a function in a background thread.

A way is needed for a background thread to update the GUI with its progress and update the GUI when it is done. This is the second use of system.util.invokeLater(function). When system.util.invokeLater(function) is called in a background thread the function that is passed to it is executed in the EDT (GUI thread). system.util.invokeAsynchronous(function) and system.util.invokeLater(function) are used together to run background threads and update the GUI.

Here is an example:

def async():
    result = functionThatTakesALongTimeToFinish()
    def later():
        event.source.parent.getComponent("Label").text = "Finished running"
    system.util.invokeLater(later)
system.util.invokeAsynchronous(async)

The system.util.invokeAsynchronous function returns immediately so the EDT is uninterrupted. When the functionThatTakesALongTimeToFinish() function completes the GUI is updated.

Here is the same code using the new @run syntax:

@run
def async():
    result = functionThatTakesALongTimeToFinish()
    @run
    def later():
        event.source.parent.getComponent("Label").text = "Finished running"

This second version is not much different than the first version. In the second version the explicit calls to system.util.invokeAsynchronous and system.util.invokeLater are gone and instead @run is at the top of the functions. @run will execute a function defined after it based on its name. Functions named "async" are executed with system.util.invokeAsynchronous and functions named "later" are executed with system.util.invokeLater. The specific function names are useful because they document what the function is for: asynchonous execution or EDT execution.

I found that using @run makes code cleaner, easier to read and write, especially when code is dense with invokeLater and invokeAsynchronous.

@run documentation

pa.thread.multiTask(functions, endFunction)

The pa.thread.multiTask(functions, endFunction) function executes multiple functions concurrently in different threads.

The pa.thread.multiTask(functions, endFunction) function is useful in cases where multiple long-running independent operations or code need to run. Instead of executing long running operations in sequence all the long running operations can run at the same time, speeding up the application.

Python lists, sets and dictionaries are thread-safe, which means they can be shared between concurrently running functions. When the concurrent functions finish running the endFunction is executed. The endFunction can do something with the results like update the GUI.

Here is a simple example:

myTextList = []
myFunctionsList = []

def func1():
   text = system.db.runScalarQuery("SELECT name FROM names1 LIMIT 1","datasource1")
   myTextList.append([text])
myFunctionsList.append(func1)

def func2():
   text = system.db.runScalarQuery("SELECT name FROM names2 LIMIT 1","datasource2")
   myTextList.append([text])
myFunctionsList.append(func2)

def func3():
   text = system.db.runScalarQuery("SELECT name FROM names3 LIMIT 1","datasource3")
   myTextList.append([text])
myFunctionsList.append(func3)

def updateGUI():
   @run
   def later():
       table = event.source.parent.getComponent("Table")
       table.data = system.dataset.toDataSet(["Names"],myTextList)

pa.thread.multiTask(myFunctionsList,updateGUI)

In this example the pa.thread.multiTask function executes three functions concurrently. Each function executes a query in a different database. The results are collected in the MyTextList Python list. After the three functions have completed running the results are put in an Ignition table in a window.

I have used the above pattern in production to concurrently execute 10 different queries at the same time. It is about 10 times faster than executing the database queries sequentially.

pa.thread.multiTask documentation

MutablePyDataSet

Datasets are very very common in Python scripts in Ignition. Ignition components use them to hold tabular data and SQL queries return results in datasets. So they are everywhere.

I found myself writing the same code over and over again in different projects for handling datasets. The solution was MutablePyDataSet. I put in MutablePyDataSet many capabilities and methods that are useful. If you think of more let me know.

MutablePyDataSet is a swiss army knife for datasets. MutablePyDataSet helps write clear, short, easy code for datasets.

MutablePyDataSet is like PyDataSet except that it has many methods for accessing and manipulating its data and it is mutable.

MutablePyDataSet can do so many things to datasets that a huge page of documentation is needed to show what it can do.

Some of MutablePyDataSet's power comes from the generality of its functions. For example the sortRows method can receive a comparison function to determining how rows should be sorted. A comparison function can sort rows in any way the programmer wishes. Similarly, the findRows, filterRows, and removeRows methods receive a function argument to determine the condition or algorithm to use for the task. Such methods give flexibility/power while removing looping boilerplate code.

MutablePyDataSets print out to the console very nicely. This is very useful for development and debugging. Here's an example:

columnNames = ["name","age","rank"]
rows = [["Bob",32,"Private"],
        ["Bill",28,"Major"],
        ["Sarah",34,"Colonel"], 
        ["Kane",56,"General"], 
        ["Kirk",46,"Captain"],       
        ["Steve",22,"Lieutenant"], 
        ["Spock",156,"First Officer"], 
        ["Sierra",18,"Private"]]                      
mutablePyDataSet = pa.dataset.toData(columnNames,rows)
mutablePyDataSet.filterRows(lambda row: row["age"] > 30)
mutablePyDataSet.reverseRows()
print mutablePyDataSet
Output Console:
row | name  age rank         
-----------------------------
 0  | Spock 156 First Officer
 1  | Kirk  46  Captain      
 2  | Kane  56  General      
 3  | Sarah 34  Colonel      
 4  | Bob   32  Private     

The code creates a MutablePyDataSet with columns and rows. The filterRow method removes all rows that do not meet the condition given in the lambda function. lambda is Python syntax for creating a function without a name on the fly. The MutablePyDataSet is reversed and printed.

Method calls can be chained. For example the code could be written like this:

print pa.dataset.toData(columnNames,rows).filterRows(lambda row: row["age"] > 30).reverseRows()

For a primer on Datasets and MutablePyDataSets in Ignition read this blog post: Datasets in Ignition

MutablePyDataSet documentation

pa.ignition.db.runPrepInternalDBQuery

This function enables you to query Ignition's internal database, (SELECT queries only). The results are returned as a MutablePyDataSet.

Here is an example that gives all the datable tables that can be queried in Ignition:

query = """SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.SYSTEM_TABLES
            WHERE TABLE_SCHEM='PUBLIC'"""
result = pa.ignition.db.runPrepInternalDBQuery(query)
print result
Output Console:
row | TABLE_NAME                                             
-------------------------------------------------------------
 0  | ALARMJOURNALSETTINGS                                   
 1  | ALARMNOTIFICATIONPROFILES                              
 2  | ALERTLOG                                               
 3  | ALERTNOTIFICATIONPROFILEPROPERTIES_BASIC               
 4  | ALERTNOTIFICATIONPROFILEPROPERTIES_BASIC_EMAILADDRESSES
 5  | ALERTNOTIFICATIONPROFILEPROPERTIES_DISTRIBUTION        
 6  | ALERTNOTIFICATIONPROFILES                              
 7  | ALERTSTATE                                             
 8  | ALERTSTORAGEPROFILEPROPERTIES_DATASOURCE               
 9  | ALERTSTORAGEPROFILES                                   
 10 | AUDITEVENTS                                            
 11 | AUDITPROFILEPROPERTIES_DATASOURCE                      
 12 | AUDITPROFILES                                          
 13 | AUTHPROFILEPROPERTIES_AD                               
 14 | AUTHPROFILEPROPERTIES_ADHYBRID                         
 15 | AUTHPROFILEPROPERTIES_ADTODB                           
 16 | AUTHPROFILEPROPERTIES_DB                               
 17 | AUTHPROFILES                                           
 18 | BASIC_SCHEDULES                                        
 19 | COMPACTLOGIXDRIVERSETTINGS                             
 20 | COMPOSITE_SCHEDULES                                    
 21 | CONTROLLOGIXDRIVERSETTINGS                             
 22 | DATASOURCEDRIVINGSQLTSETTINGS                          
 23 | DATASOURCES                                            
 24 | DBTRANSLATORS                                          
 25 | DEVICES                                                
 26 | DEVICESETTINGS                                         
 27 | DRIVERPROPERTIES                                       
 28 | DUAL                                                   
 29 | EMAILNOTIFICATIONSETTINGS                              
 30 | GENERALALARMSETTINGS                                   
 31 | HOLIDAYS                                               
 32 | HOMEPAGE_SETTINGS                                      
 33 | IMAGES                                                 
 34 | INTERNALAUTHMAPPINGTABLE                               
 35 | INTERNALCONTACTINFOTABLE                               
 36 | INTERNALROLETABLE                                      
 37 | INTERNALSCHEDULEADJUSTMENTTABLE                        
 38 | INTERNALSQLTPROVIDERSETTINGS                           
 39 | INTERNALUSEREXTRAPROPS                                 
 40 | INTERNALUSERTABLE                                      
 41 | JDBCDRIVERS                                            
 42 | LEGACYSQLTPROVIDERSETTINGS                             
 43 | LOGIXDRIVERSETTINGS                                    
 44 | MICROLOGIXDRIVERSETTINGS                               
 45 | MOBILEMODULESETTINGS                                   
 46 | MODBUSTCPDRIVERSETTINGS                                
 47 | OPCSERVERS                                             
 48 | PLC5DRIVERSETTINGS                                     
 49 | PROJECTS                                               
 50 | PROJECT_CHANGES                                        
 51 | PROJECT_RESOURCES                                      
 52 | REGIONSETTINGS                                         
 53 | ROSTER                                                 
 54 | ROSTER_ENTRY                                           
 55 | S71200DRIVERSETTINGS                                   
 56 | S7300DRIVERSETTINGS                                    
 57 | S7400DRIVERSETTINGS                                    
 58 | SCHEDULES                                              
 59 | SCHEDULE_PROFILES                                      
 60 | SCRIPTSETTINGSRECORD                                   
 61 | SIMPLETAGPROVIDERPROFILE                               
 62 | SIPNOTIFICATIONSETTINGS                                
 63 | SLCDRIVERSETTINGS                                      
 64 | SMSNOTIFICATIONPROFILESETTINGS                         
 65 | SQLTAG                                                 
 66 | SQLTAGALARMPROP                                        
 67 | SQLTAGALARMS                                           
 68 | SQLTAGEVENTSCRIPTS                                     
 69 | SQLTAGHISTORYPROVIDER                                  
 70 | SQLTAGPROP                                             
 71 | SQLTAGPROVIDER                                         
 72 | SQLTSCANCLASS                                          
 73 | SRFEATURES                                             
 74 | STOREANDFORWARDSYSSETTINGS                             
 75 | SYSPROPS                                               
 76 | TAGHISTORYPROVIDEREP                                   
 77 | TAGPERMISSIONRECORD                                    
 78 | TCPDRIVERSETTINGS                                      
 79 | TRANSLATIONSETTINGS                                    
 80 | TRANSLATIONTERMS                                       
 81 | UACONNECTIONSETTINGS                                   
 82 | UDPDRIVERSETTINGS                                      
 83 | XOPCSETTINGS  

pa.ignition.db.runPrepInternalDBQuery documentation

pa.ignition.getContext()

The pa.ignition.getContext() function returns the context object for the scope it is called in. For example if called in a client the ClientContext is returned. If called in a designer then the DesignerContext is returned. If called in the gateway then the GatewayContext is returned.

The context object provides access to the Ignition Module SDK API. This means that internal Ignition data can be accessed and changed with the context object. For example all of a projects resources, such as windows, client tags, client properties and scripts can be accessed through a context object.

pa.ignition.getContext() documentation

The PA Power Scripting Module is available on the Module Marketplace.

The module works fully without a license in an Ignition designer. Without a license it works fully during the 2 hour trial period in Ignition clients.

I am interested in being notified about new features and capabilities that could be added to this Ignition module. What features and capabilities do you want?

Comments

Name: (required)
Email: (required)
Website:
What has four legs, rhymes with bat and says, "Meow?" (One word answer.)
Spam Filter: