Creating in MySQL dynamic views with dynamic unions that adds automatically newly created tables by Ignition SCADA or others…
During one of my project working with Ignition SCADA by Inductive Automation, and using it with MySql to store its data, I had to find a way to create views for external reporting that would dynamically integrate the new monthly automatically created tables of data by Ignition. Not very clear at the first read, let me explain: the design of Ignition makes it store its data in multiple specific tables and each month it creates new tables from these specific tables for the current month. It’s a kind of self made partitioning, I’d have preferred to work with partitioning instead but I would had the pleasure to write this post.
Looking for a specific solution
Duckducking go the Internet, I found an interesting article from Paul Campbell on Stack Overflow explaining how to dynamically create a procedure to dynamically create SQL with UNION of tables found in the system tables. My first idea was to find a way to know if a new table was created in the database. What a better place to look into that by searching in the system tables, specifically the information_schema.tables. One would have thought that we’d have just to add a trigger calling this procedure on this table and act accordingly… but if you read this article, this means that you already know that MySql prohibit to create triggers on a system tables. It’s mainly because system tables are not really tables, but are more acting like views. I was looking for a solution that will reside in the database, nothing called from the external: a SCADA system have to be resilient and depending on other external computers is not always a good idea. Finally MySql has everything we need: he owns a scheduler and a scheduled event is able to call a stored procedure. From this point it is pretty easy, just create the stored procedure that will create or replace the reporting views every month and call it with the an specific event in MySql.
Create the Stored Procedure that will generate a view with dynamic unions
First, here is a screen shot of the code as Medium will break most of it.
The code:
How to create the MySql scheduled event that will trigger the stored procedure and automatically create your view with dynamic unions
Now you have a stored procedure called dyn_view_maker. Let’s create the event that will trigger it. At first, you have to check if your MySql has Event Scheduler already enabled. If not, first add a line in your my.cnf (or my.ini on Windows) under [mysqld] to start automatically the event scheduler at launch time.
[mysqld]
event_scheduler=ENABLED
If you cannot restart your MySql to start the Event Scheduler, just run this sql
SET GLOBAL event_scheduler = ON;
SET @@GLOBAL.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@GLOBAL.event_scheduler = 1;
The event creation is pretty straightforward
CREATE EVENT `ev_create_v_temparature`
ON SCHEDULE EVERY 1 MONTH
STARTS ‘2018–01–01 01:00:00’
ON COMPLETION PRESERVE ENABLE
COMMENT ‘Your comment’
DO
call dyn_view_maker()
In my case the tables was created every month, but it’s pretty easy to adapt this sql to your needs. This is what I thought to implement to get to this result. If you have a better idea, feel free to leave me a comment.