Can I publish a message via a database trigger?
發表於 : 2018-04-01 21:43:48
https://support.pubnub.com/support/solu ... e-trigger-
CAN I TRIGGER A MESSAGE PUBLISH IN RESPONSE TO A DATABASE SQL OPERATION?
NOTE: Using triggers is not the best practice as it will not scale properly for high transaction applications. You should consider implementing the publish code in your application layer. For example, the language/framework you are using (in Java, Node, Ruby, etc) provides some sort of database API. That API should have some database transaction manager. When that manager confirms that the insert/update/delete was successful via some commit/success event, you can perform the publish in that event.
Yes, you can invoke a PubNub publish via a MySQL Trigger on UPDATE, INSERT and DELETE.
MySQL makes it simple to wrap your coding into easily accessible TRIGGERS via Stored Procedures. The following is an easy way to bind any UPDATE, INSERT and DELETE action on your database table to invoke a stored procedure that will publish a message to subscribers.
DELIMITER $$
CREATE PROCEDURE publish_message(p1 DOUBLE, p2 DOUBLE, p3 BIGINT)
BEGIN DECLARE cmd CHAR(255);
DECLARE result CHAR(255);
SET cmd = CONCAT('curl https://pubsub.pubnub.com/publish/demo/ ... ,',p3,'%22');
SET result = sys_eval(cmd);
END$$;
NOTE: PROCEDURE types need to be correct DOUBLE or VARCHAR or TEXT
MYSQL TRIGGER CODE INSERT EXAMPLE
CREATE TRIGGER publish_trigger
AFTER INSERT ON your_table_name_here
FOR EACH ROW CALL do_publish(NEW.Column1, NEW.Column2, NEW.Column3);
NOTE: Include the columns needed in the message.
MYSQL TRIGGER CODE UPDATE EXAMPLE
CREATE TRIGGER publish_message_trigger
AFTER UPDATE ON your_table_name_here
FOR EACH ROW CALL publish_message(NEW.Column1, NEW.Column2, NEW.Column3);
MONITOR THE PUBLISHED MESSAGE VIA DEBUG CONSOLE
You can watch the messages appear in the PubNub Dev Console (pre-configured for this example) as they are published from the MySQL triggers.
RECEIVING THE PUSH MESSAGE IN JAVASCRIPT
<div id=pubnub ssl=on></div>
<script src="http://cdn.pubnub.com/pubnub.min.js"></script>
<script>
(function(){
var pubnub = PUBNUB.init({'subscribe_key' : 'demo', 'ssl' : true});
pubnub.subscribe({
'channel' : 'mysql_triggers',
'callback' : function(mysql_trigger_details) {
alert(mysql_trigger_details);
}
});
})();
</script>
The above are the steps needed to send and receive change events from MySQL directly via stored procedures. There are ways to optimize this method as well, such as issuing a signal to a daemon process that queues and pools HTTPS push notifications.
CAN I TRIGGER A MESSAGE PUBLISH IN RESPONSE TO A DATABASE SQL OPERATION?
NOTE: Using triggers is not the best practice as it will not scale properly for high transaction applications. You should consider implementing the publish code in your application layer. For example, the language/framework you are using (in Java, Node, Ruby, etc) provides some sort of database API. That API should have some database transaction manager. When that manager confirms that the insert/update/delete was successful via some commit/success event, you can perform the publish in that event.
Yes, you can invoke a PubNub publish via a MySQL Trigger on UPDATE, INSERT and DELETE.
MySQL makes it simple to wrap your coding into easily accessible TRIGGERS via Stored Procedures. The following is an easy way to bind any UPDATE, INSERT and DELETE action on your database table to invoke a stored procedure that will publish a message to subscribers.
DELIMITER $$
CREATE PROCEDURE publish_message(p1 DOUBLE, p2 DOUBLE, p3 BIGINT)
BEGIN DECLARE cmd CHAR(255);
DECLARE result CHAR(255);
SET cmd = CONCAT('curl https://pubsub.pubnub.com/publish/demo/ ... ,',p3,'%22');
SET result = sys_eval(cmd);
END$$;
NOTE: PROCEDURE types need to be correct DOUBLE or VARCHAR or TEXT
MYSQL TRIGGER CODE INSERT EXAMPLE
CREATE TRIGGER publish_trigger
AFTER INSERT ON your_table_name_here
FOR EACH ROW CALL do_publish(NEW.Column1, NEW.Column2, NEW.Column3);
NOTE: Include the columns needed in the message.
MYSQL TRIGGER CODE UPDATE EXAMPLE
CREATE TRIGGER publish_message_trigger
AFTER UPDATE ON your_table_name_here
FOR EACH ROW CALL publish_message(NEW.Column1, NEW.Column2, NEW.Column3);
MONITOR THE PUBLISHED MESSAGE VIA DEBUG CONSOLE
You can watch the messages appear in the PubNub Dev Console (pre-configured for this example) as they are published from the MySQL triggers.
RECEIVING THE PUSH MESSAGE IN JAVASCRIPT
<div id=pubnub ssl=on></div>
<script src="http://cdn.pubnub.com/pubnub.min.js"></script>
<script>
(function(){
var pubnub = PUBNUB.init({'subscribe_key' : 'demo', 'ssl' : true});
pubnub.subscribe({
'channel' : 'mysql_triggers',
'callback' : function(mysql_trigger_details) {
alert(mysql_trigger_details);
}
});
})();
</script>
The above are the steps needed to send and receive change events from MySQL directly via stored procedures. There are ways to optimize this method as well, such as issuing a signal to a daemon process that queues and pools HTTPS push notifications.