Way to view the raw SQL of a query binding?

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:

  1. 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
    image)

  2. 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?

Ready to go down a rabbit hole to WonderLand FPMIWindowLand?

Start here:

https://forum.inductiveautomation.com/search?q=InteractionController%20@pturmel%20order:latest

References start here:

https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.1/com/inductiveautomation/factorypmi/application/binding/InteractionController.html

1 Like

The QueryManager logger in the client on TRACE might be helpful.

Ok I think this is the rabbit hole I have to jump down at least for the rest of the day.

I am testing it on a single component that I know has a data query binding on it.

Here is my code

import com.inductiveautomation.factorypmi.application.binding

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

chatTable = rc.getComponent('PTable_Chat')

IC = com.inductiveautomation.factorypmi.application.binding.InteractionController
results = IC.getAllAdaptersForTarget(chatTable)

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.

2 Likes

My (lack of) java knowledge is showing :sweat_smile:

I followed what you said and now have things sort of working though things don’t seem right - assuming it’s a problem with my code.

import com.inductiveautomation.factorypmi.application.FPMIWindow

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

chatTable = rc.getComponent('PTable_Chat')

FPMIWindow = com.inductiveautomation.factorypmi.application.FPMIWindow("Pop_HelpDesk")
IC = FPMIWindow.getInteractionController()
results_1 = IC.getAllAdaptersForTarget(chatTable)
results_2 = IC.getInteractionDescriptorsWithSource(chatTable)
results_3 = IC.getInteractionDescriptorsWithTarget(chatTable)
results_4 = IC.getPropertyAdapter(chatTable, 'data')

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.

What am I doing wrong this time?

That’s constructing a new window. What’s the type of win when you retrieve it with getWindow()?

Ahh I see, <type 'com.inductiveautomation.factorypmi.application.FPMIWindow'>

import com.inductiveautomation.factorypmi.application.FPMIWindow

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

chatTable = rc.getComponent('PTable_Chat')

IC = win.getInteractionController()
results_1 = IC.getAllAdaptersForTarget(chatTable)
results_2 = IC.getInteractionDescriptorsWithSource(chatTable)
results_3 = IC.getInteractionDescriptorsWithTarget(chatTable)
results_4 = IC.getPropertyAdapter(chatTable, 'data')

Is giving me what I expect now. Now I can really get lost in the weeds.

1 Like

I look away for a bit and all hell breaks loose. :grin:

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.

1 Like

I was able to use this script

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.

Thanks!

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).

Nothing that would readily help you, I don’t think. The code editors used aren’t particular smart.

1 Like

Consider rewriting your imports like so:

from com.inductiveautomation.factorypmi.application.binding import SQLPropertyAdapter

Then you can just use SQLPropertyAdapter where needed.

1 Like

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.

@pturmel thanks, that is much cleaner.

Final script I use in case anyone wants to use it:

def getAllQueriesInWindow(windowName):
	win = system.gui.getWindow(windowName)
	rc = win.getRootContainer()
	results = {'rc':{}}
	IC = win.getInteractionController()
	rcAdapters = IC.getAllAdaptersForTarget(rc)
	
	for adapter in rcAdapters:
		if isinstance(adapter, SQLPropertyAdapter):
			propName = adapter.getTargetPropertyName()
			query = adapter.getPullQuery().getQuery().lower().replace('\n', ' ')
			results['rc'][propName]= query
	
	for component in rc.getComponents():
		allAdapters = IC.getAllAdaptersForTarget(component)
		for adapter in allAdapters:
			if isinstance(adapter, SQLPropertyAdapter):
				propName = adapter.getTargetPropertyName()
				query = adapter.getPullQuery().getQuery().lower().replace('\n', ' ')
				if component.name not in results.keys():
					results[component.name]={}
				results[component.name][propName]= query
	return results

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.

2 Likes

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 :upside_down_face:

2 Likes

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

results in

initialData
_out_projectId
projectData
datasetsLoaded
vendorData
_out_vendorPONumber
vendorId
addMode
_out_customShipViaMethod
_out_initialInventoryList
allowCalcUpdates
previousPOsForThisProject
locationChoices
com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter@68ca03fd

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
image

Any ideas on what this could be? Expected or unexpected behavior?

ActionAdapter says:

/*
 * This is the implementation of event scripts associated with Vision components
 */
1 Like

Once you found your redundancy, how did you clean them up?
Like if 5 are basically the same, how did you eliminate 4?

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.

3 Likes