Background:
I am using an in-house query builder to generate prepared statements. The value of doing this is that maintenance is extremely simple, but I have some queries - such as getting a User table - which I run very frequently but for which the results never really change unless a User is added.
I can cache in each client with a decorator function, but each client still needs to make a call to the database once to initialize the DataSet. I would like to have the Ignition server query the query, cache the results, and then give clients access to this; however, I would like to do this on the fly in scripting.
It looks like NamedQueries are the only in-built tool in Ignition to handle this particular problem, but it seems you have to write each query in the designer.
Question:
Is there any way to create named queries through scripting at runtime?
No. In 8.0 you can technically create the appropriate files on disk, but they're serialized in an opaque format that's not documented - so you'd have to reverse engineer the Java serialization, and it'd be prone to breaking on upgrade.
Is there any suggested (or hackish) way to handle the following:
Dynamic client data caching
Query caching, which can be shared across clients through the gateway
You can use system.util.sendRequest() in the client to delegate anything you want to the gateway. Implement your caching logic in jython via gateway message handlers and script modules. Your script modules can use python dictionaries (script global) as cache containers with any cache policy you like.
I would encourage you to not pass any query strings or where clauses through the message payload, just names of queries (using your own index, not named queries) and parameters.
For time-series data in wide tables, I also recommend my own Time Series DB Cache Module. (:
Best paired with my own NoteChart Module, since its EasyNoteChart component can automatically route DB pens through the TS DB Cache.
Create a memory sql query tag with a long scan class time.
Then just read the tag for your data?
That was going to be my hacky solution if there was no official way. The issue with using SQLQuery tags is that I would need to dynamically manage them. While Ignition provides tools to do this to some extent, the actual creation of these tags would require I create a string to act as the Expression/SQL script and use SDK functions to set this on a tag. I am not completely opposed, but this solution is really involved.
Using tags brings too many challenges. @pturmel’s solution avoids having to work with the intricate details of tags and the complexity of managing them and instead allows for the use of simple built-in datatypes in Python/Java. His solution still may require a central index, but I believe it will be easier to maintain. His solution may also be slightly faster, because it avoids the cost of tag creation/deletion.
I want a dynamic caching system. I am thinking - ideally - of something where I can add a decorator to a function and when a client makes a call that has not been made previously, the gateway caches the result so that all clients can share the data. On top of this, each function in each client uses an LRU (least-recently-used) cache of its own to avoid tons of requests to the gateway. Ideally, I would be able to do this without a central index in a module and instead could just add/remove decorators from functions on the fly.
All that said, if you or anyone else has a working, scalable, dynamic caching system, I would love to hear more.