Just had an issue where I had to optimize a window. After searching through every component/root container property that had a query binding, I was able to remove a couple of redundant queries by putting them into a single query and grabbing from that via expressions.
I would like to be able to automate this a bit for some other slow windows that I have in this project (and other projects).
If possible I’d like to:
See if any of properties of a component or root container have a SQL Query binding (I assume there must be someway this is possible as the Designer knows and marks components with db with a )
If a component (or rc property) is a SQL binding - to get the SQL that would run.
For instance if a SQL Binding is SELECT * FROM someTable WHERE id = {Root Container.tableId} and the root container property tableId = 7, then I would see Component X has it’s data property set by the SQL Query SELECT * FROM someTable WHERE id=7. I figure this one is probably a bit harder. If I can’t get the values filled in but I could still see SELECT * FROM someTable WHERE id={Root Container.tableId} that would still be acceptable to me.
I have a lot of windows that are poorly optimized with redundant queries/un-indexed queries and I would very much like to be able to write a script, get all the queries that are run on it, so I could then do some automation with testing the speed of the query/checking the explain statement to look for full table scans/see where queries can be combined etc. Is this possible?
but this is telling me TypeError: getAllAdaptersForTarget(): expected 2 args; got 1
But according to the documentation it only seems to take a single argument getAllAdaptersForTarget(java.awt.Component target) What am I doing wrong/misreading?
You need an interaction controller instance - you can’t call getAllAdaptersForTarget statically (the second argument it would need to do that would be an instance to pass as self).
FPMIWindow has a getInteractionController() method.
The length of results_1, results_2, and results_3 are all 0. I know that the data prop has a query binding on it so I would have though that would have come through on results_1 but it does not, and results_4 is NoneType.
Also based on my reading, I one of results_2/3 should have given me something as the query itself has ORDER BY m.idx {Root Container.PTable_Chat.orderByClause} and these InteractionControllers are the way that Ignition provides that dropdown when deleting something. As in if I tried to delete {Root Container.PTable_Chat.orderByClause} I would get a warning my data binding relied on it etc which is why I would have thought results_2 or 3 would have gave me something.
As Paul hints, you don’t need to import FPMIWindow. You might want to import some of the Adapter types to use in isinstance calls. Jython will generally auto-map all the other stuff you need.
import com.inductiveautomation.factorypmi.application.FPMIWindow
import com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter
win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()
results = {'rc':[]}
IC = win.getInteractionController()
rcAdapters = IC.getAllAdaptersForTarget(rc)
for adapter in rcAdapters:
if isinstance(adapter, com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter):
propName = adapter.getTargetPropertyName()
query = adapter.getPullQuery().getQuery()
results['rc'].append((propName, query))
for component in rc.getComponents():
allAdapters = IC.getAllAdaptersForTarget(component)
for adapter in allAdapters:
if isinstance(adapter, com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter):
propName = adapter.getTargetPropertyName()
query = adapter.getPullQuery().getQuery()
results[component.name] = (propName, query)
To get me all the SQL Bindings for my root container properties and components into a dictionary so from here now I can write some functions that analyze this data.
Does Ignition have a SQL Parser? Given the contextual highlighting on query bindings/named queries/the database browser, I am hopeful. Was looking to grab table names from these queries with regex but that seems ill-advised after a little research if you can use a SQL Parser instead. Is that something that is part of the ecosystem? I tried searching the documentation but all I can find is QueryParser which doesn’t quite do what I need (which is to grab the tables out of the queries).
Fair enough. Figured it was a long shot as it doesn’t seem like Ignition has any reason to need to know this, it just needs to pass it onto the database.
You can probably simplify that a bit more with this utility function:
# Recursively scan a VisionContainer for VisionComponents and inner
# VisionComponents, calling a given routine for each child encountered.
#
def forEachChild(container, func):
func(container)
try:
children = container.getComponents()
except:
return
for component in children:
forEachChild(component, func)
Pass it your root component. This will catch any grouped components and contents of nested containers.
Took me a second to figure it out but I got it working! Thanks. I think I should probably do the full component path if possible as my dictionary key since now I am recursively going into containers and it will just make things more clear.
Anyways - new updated version:
import com.inductiveautomation.factorypmi.application.FPMIWindow
from com.inductiveautomation.factorypmi.application.binding import SQLPropertyAdapter
import re
def forEachChild(container, func):
func(container)
try:
children = container.getComponents()
except:
return
for component in children:
forEachChild(component, func)
def getAllQueriesInWindow(windowName):
win = system.gui.getWindow(windowName)
rc = win.getRootContainer()
IC = win.getInteractionController()
results = {}
def findAllAdapterProperties(container):
allAdapters = IC.getAllAdaptersForTarget(container)
for adapter in allAdapters:
if isinstance(adapter, SQLPropertyAdapter):
propName = adapter.getTargetPropertyName()
# Want to sanitie queries to be consistant
# the re.sub gets rid of extra whitespaces
query = re.sub('\s\s+', ' ', adapter.getPullQuery().getQuery().lower().replace('\n', ' '))
if container.name not in results.keys():
results[container.name]={}
results[container.name][propName]= query
forEachChild(rc, findAllAdapterProperties)
return results
And to use in conjunction with this I wrote a few diagnostics
def getQueryBindingCountInWindow(windowName):
results = getAllQueriesInWindow(windowName)
total=0
for key, value in results.items():
total+=len(value)
return total
def detectPotentialDuplicateQueries(windowName):
# First Niave Method of finding potential duplicates. Return data where the same database table is in multiple FROM clauses
# as a decent first pass to see where queries may be duplicated or potentially consolidated
results = getAllQueriesInWindow(windowName)
potentialDuplicates = {}
for component, componentData in results.items():
for prop, query in componentData.items():
# print "working on %s %s %s"%(component, prop, query)
tableSearchResult = re.search("from\s+([^ ,]+)(?:\s*,\s*([^ ,]+))*\s+", query)
if tableSearchResult is not None:
tableName = tableSearchResult.group(1)
# print "found table %s"%(tableName)
if tableName not in potentialDuplicates.keys():
potentialDuplicates[tableName] = []
potentialDuplicates[tableName].append((component, prop, query))
for k, v in potentialDuplicates.items():
if len(v) == 1:
del potentialDuplicates[k]
return potentialDuplicates
From the first function I found out my window has to evaluate 24 SQL Bindings wihch seems like too many, and from my second function I realized it’s because I have 5 that are basically the same query from the same exact table, so I was able to get rid of a lot of redundancy. Thanks @PGriffith@pturmel
Now to go run this on some of the first windows I ever made in Igntion that made it to production
This has been working nicely. I am trying it on a new window I made that is all Named Queries, to see if there’s instances where I am calling the same Named Query in two or more different spots for consolidation.
When I was debugging/figuring out how to parse out named query adapters (turned out to be NamedQueryAdapter lol) I notice something weird on my root container.
I am running this script in script console on my new window which at one point threw a AttributeError, so this is how I have it currently -
win = system.gui.getWindow("Pop_VendorPO")
rc = win.getRootContainer()
IC = win.getInteractionController()
rcAdapters = IC.getAllAdaptersForTarget(rc)
for adapter in rcAdapters:
try:
print adapter.getTargetPropertyName()
except AttributeError:
print adapter
So I have some ActionAdapter that does not have a targetPropertyName at all on my root container - a ghost action adapter? My root container does have a property change script so I don’t know if maybe if it represents that, but I didn’t think change script ever stopped you from deleting a binding via the InteractionController (in the way that if you try to delete a custom property used in another expression - you ARE stopped). So I don’t think its that?
Here are my root container properties which all match up, except obviously to com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter
Any ideas on what this could be? Expected or unexpected behavior?
My situation was I had 4 queries like the following -
SELECT column1
FROM someTable
WHERE parentThreadId={Root Container.threadId} AND userId = {client/userId}
and another would look like
SELECT column3
FROM someTable
WHERE parentThreadId={Root Container.threadId} AND userId = {client/userId}
etc. Slightly more complicated, some had CASE statements for instance, but they all had the same exact WHERE clause and were selecting from the same table.
So I was able to curate all of them into a single SELECT clause like
SELECT
column1,
column3,
CASE WHEN something THEN something ELSE somethingElse END as 'something',
CASE WHEN something2 THEN something2 ELSE somethingElse2 END as 'something2',
FROM someTable
WHERE parentThreadId={Root Container.threadId} AND userId = {client/userId}
So I put this into a single dataset, and then in those 4 spots where I was previously running a query for a single bit of data, I turned them into expressions like try({Root Container.newDataSet}[0, 'neededColumn'], 'fallbackValue').
Now a single query grabs all the column data that is needed, and the properties try to grab from that dataset, instead of running their own query.
)
