MySQL materialized view

Hi @akt01,

My apologies for my extremely late reply! During my stay in Aruba I was not able to recharge my laptop battery which was limiting my internet access. I’ll try to pick up open issues asap now.

The Query Builder supports multiple SQL commands. This is documented here:
https://wpdataaccess.com/docs/documentation/query-builder/writing-queries/#multiple-sql-commands

But I’m afraid this will not completely satisfy your needs. I think you need to automatically run the SQL code in regular intervals. That option is planned but not yet available. Unless you don’t mind to run your SQL manually….?

Does this help?

Best regards,
Peter

MySQL also supports an EVENT type which can run on a schedule. It can be multiple SQL statements.

https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html

I wasn’t aware of that MySQL feature Charles. Might make implementing my scheduler much easier… ??

Thanks,
Peter

It’s also supported by MariaDB https://mariadb.com/kb/en/events/

I think it will only run SQL statements.

Hi @peterschulznl,

No prob! Thank you for your reply!

Is it possible to use WP Data Access to store a SQL query (preferably containing multiple statements), and call it via a wordpress hook? The hook can be called by cron, so WP Data Access does not have to handle the auto-scheduling.

Thanks!

Hi guys,

@charlesgodwin You just saved me a loooot of work… ?? THX

@akt01 You can use multiple SQL statements in an event. Please visit this page:
https://dev.mysql.com/doc/refman/8.0/en/create-event.html

And search for: more complex compound statements

I tested this with the Query Builder and it works! Here is just some stupid code for testing:

CREATE EVENT e ON SCHEDULE EVERY 60 SECOND DO
BEGIN
	DECLARE v INTEGER;
	SET v = 0;
	truncate table aaa;
	WHILE v < 5 DO
	  INSERT INTO aaa VALUES (v);
	  SET v = v + 1;
	END WHILE;
END
/

Make sure you use the semi colon to separate your SQL statements and add a slash at the end of your code. I will add a page to the documentation so other users can use it as well.

Let me know if you need further assistance @akt01.

@charlesgodwin How can I sent you a beer? ??

Best regards,
Peter

Very interesting. I haven’t used EVENT before, it looks like MySQL has its own scheduler that can run scheduled jobs. And it’s nice that this can be setup via WP Data Access on WP systems. I’ll give it a try.

Thanks much @peterschulznl and @charlesgodwin, this is very helpful!