The Ultimate Guide to High Availability with MariaDB

Wait for changes on a table

Hi, I am making an Android app that should connect to a db and update the information shown whenever a table on the database changes, so, is there a way to make the app recognise thoose changes with the java connector? I could just make a function to look the table every few seconds but i would like to know if there is something built in.

Thanks!

Answer Answered by Markus Mäkelä in this comment.

Like is mentioned in MDEV-16590, this can be emulated in SQL. Here's a quick little prototype of a pair of stored procedures and a table that can be used to send and wait for signals. Adding signal IDs and versions for notify_one/notify_all should be possible.

delimiter $$ ;
CREATE OR REPLACE PROCEDURE wait_for_signal(IN timeout INT)
BEGIN
    INSERT INTO signal_waiters VALUES (CONNECTION_ID());
    EXECUTE IMMEDIATE CONCAT('SELECT ''wait_for_signal'', SLEEP (', timeout, ')');
    DELETE FROM signal_waiters WHERE id = CONNECTION_ID();
END $$

CREATE OR REPLACE PROCEDURE send_signal()
BEGIN
    START TRANSACTION;
    FOR rec IN (SELECT id FROM signal_waiters FOR UPDATE)
    DO
      EXECUTE IMMEDIATE CONCAT('KILL QUERY ', rec.id);
      DELETE FROM signal_waiters WHERE id = rec.id;
    END FOR;
    COMMIT;
END $$
delimiter ; $$

CREATE OR REPLACE TABLE signal_waiters(id BIGINT PRIMARY KEY);

-- To wait for a signal
CALL wait_for_signal(10);

-- To send a signal
CALL send_signal();

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.