Everything I have ever seen in the Ignition documentation and in these forums says nothing of aliasing tables, schema names, and columns. In the past, I used the database names everywhere, but eventually there came times when I had to move a table to a different schema, rename a table, or rename a column. In these cases, I had to spend hours combing through the system to find and replace everything and then test everything to make sure I didn’t miss something somewhere.
Is there a best practice for handling this problem in Ignition? Is there a way to do it that will still allow me to use things like Named Queries with their built-in caching on the Gateway?
If there is not, I am leaning toward creating a DAL in my projects that defines all of this in one place and then creating all SQL code in scripts. The key downside to this approach at the moment is that I cannot use Named Queries and would have to down the same cache to every single client.
You can definitely use query string parameters to build your query with Named Queries – when you use the query, tie that parameter to a tag if you want a single place to update the query. Or you can just update your named queries when you update the database. It all depends on how many queries you use and if you want to do the work when you set up the query usage or when you update the database.
Both methods work with caching, because the cache is based on the text of the parsed query including parameter values.
I have a few hundred queries across all the programs. Tags might work, but I was hoping for some in-built functionality for handling this problem which I am sure everyone must have. For the purposes of speed, I think I mine as well just put the mapping in a global script rather than in tags.
In practice, there would be a cut-over point in production where I would need to update the database name and then a short delay before I could update the tag with the name. So while using tags would allow me to change things without having to push an update to the code, I wouldn’t really be able to use that in production without causing a blip.
If there is nothing built into Ignition, is there a suggested practice / library / etc. from Python / Java to handle this?
Convert everything to Named Queries. Suffer the pain once, then all future work of this kind is confined to looking through Named Queries.