ZmanEmet - MySQL 和 MySQL 之间的实时通信Socket.io 客户端
A node.js 用于 MySQL 服务器和用户界面之间实时更新的包。
该包监听 MySQL 数据库上的事件并实时更新用户。
这个包基于原始的ZongJi和原始 mysql-events 模块。
作者:Niv Apo
安装
npm install zmanemet
快速入门
const mysql = require('mysql');
const ZmanEmet = require('zmanemet');
const { Server } = require("socket.io");
const program = async () => {
// Create a connection to your DB
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
});
// Create a socket.IO server
const io = new Server(3000);
// Create a ZmanEmet instance and pass your db & socket.io connections
const instance = new ZmanEmet(connection,io);
// Start your instance
await instance.start();
// Create a trigger for an event
let trigger = {
name: 'TEST',
expression: 'MY_SCHEMA.users.name',
statement: "UPDATE", // see MySQLEvents.STATEMENTS.ALL for full list of options
onEvent: (event) => {
// Process the event and return the data to send to the client
let {before,after} = event.affectedRows[0];
let user_id = after.id;
let room_id = `user_${user_id}`; // The socket room to send the update
let event_name = "user.update"; // The event name in the sockets on the client side
// The data that will be sent to the socket
let data = {
user_id:user_id,
new_name:after.name
}
// Optional - send an update to the client via socket.io server
return [room_id,event_name,data];
},
};
zman.addTrigger(trigger);
// Add a listener for connection issues
instance.on(MySQLEvents.EVENTS.CONNECTION_ERROR, console.error);
};
program()
.then(() => console.log('Waiting for database events...'))
.catch(console.error);
使用
### #constructor(dbConnection, socketsConnection)
-
使用预先存在的实例化并创建数据库连接连接
const 连接 = mysql.createConnection({
主机:'本地主机',
用户:'用户名',
密码:'密码',
});
const myInstance = new ZmanEmet(连接,io);
#start()
-
start 函数确保 MySQL 在解决其承诺之前已连接
JavaScript
myInstance.start()
.then(() => console.log('我正在运行!'))
.catch(err => console.error('发生了一些不好的事情', err));
### #stop()
-
stop 函数在解决其承诺之前终止 MySQL 连接并停止 ZongJi
JavaScript
myInstance.stop()
.then(() => console.log('我停了下来!'))
.catch(err => console.error('发生了一些不好的事情', err));
### #pause()
-
pause 函数暂停 MySQL 连接,直到调用 #resume()
,当您接收的数据超出您当时可以处理的数据时,这很有用
JavaScript
myInstance.pause();
### #resume()
-
resume 函数恢复暂停的 MySQL 连接,因此它再次开始生成 binlog 事件
JavaScript
myInstance.resume();
### #addTrigger({ name, expression, statements, onEvent })
-
为给定表达式/语句添加触发器,并在事件发生时调用 onEvent
函数
instance.addTrigger({
名称:'MY_TRIGGER',
表达式:'MY_SCHEMA.MY_TABLE.MY_COLUMN',
语句:MySQLEvents.STATMENTS.INSERT,
onEvent: 异步(事件) => {
// 在这里您将获取给定表达式/语句的事件。
// 该函数在数据库触发触发器之后、数据发送到套接字之前执行。
// 您可以处理数据并将您想要的数据发送到 socket.iolet socket_room = "";
让事件名称=“”;
让 event_data = "";
返回 [socket_room,event_name,event_data];
},
});
name
参数必须是唯一的每个表达式/语句,如果您想删除触发器,稍后将由用户
instance.addTrigger({
名称:'MY_TRIGGER',
表达式:'MY_SCHEMA.*',
语句:MySQLEvents.STATMENTS.ALL,
...
});
实例.removeTrigger({
名称:'MY_TRIGGER',
表达式:'MY_SCHEMA.*',
语句:MySQLEvents.STATMENTS.ALL,
});
表达式
参数非常动态,您可以用 *
替换任何步骤,以使其等待任何架构、表或列事件
JavaScript
实例.addTrigger({
name: '从 SCHEMA2 的表 USERS 更新名称',
表达式:'SCHEMA2.USERS.name',
...
});
JavaScript
实例.addTrigger({
name: '所有数据库事件',
表达: '*',
...
});
JavaScript
实例.addTrigger({
name: 'SCHEMA2 中的所有事件',
表达式:'SCHEMA2.*',
...
});
JavaScript
实例.addTrigger({
name: '表 USERS 的所有数据库事件',
表达式:'*.USERS',
...
});
JavaScript
实例.addTrigger({
name: '表 USERS 的所有数据库事件',
表达式:'*.USERS',
...
});
-
statement
参数指示应在哪个数据库操作中触发事件
JavaScript
实例.addTrigger({
...
语句:MySQLEvents.STATMENTS.ALL,
...
});
允许的语句
-
onEvent
参数是应该威胁触发事件的函数
JavaScript
实例.addTrigger({
...
onEvent: (事件) => {
控制台.log(事件); // { 类型、架构、表、affectedRows: []、affectedColumns: []、timestamp, }
},
...
});
### #removeTrigger({ name, expression, statements })
-
从当前实例中删除触发器
JavaScript
实例.removeTrigger({
name: '我之前创建的触发器',
表达: '',
语句:MySQLEvents.STATMENTS.INSERT,
});
Tigger 事件对象
它具有以下结构:
{
type: 'INSERT | UPDATE | DELETE',
schema: 'SCHEMA_NAME',
table: 'TABLE_NAME',
affectedRows: [{
before: {
column1: 'A',
column2: 'B',
column3: 'C',
...
},
after: {
column1: 'D',
column2: 'E',
column3: 'F',
...
},
}],
affectedColumns: [
'column1',
'column2',
'column3',
],
timestamp: 1530645380029,
nextPosition: 1343,
binlogName: 'bin.001',
}
确保数据库用户有权读取要监视的数据库上的 binlog。
LICENSE
MIT © Niv Apo
ZmanEmet - realtime communication between MySQL & Socket.io client
A node.js package for Real-Time update between your MySQL server and the user interface.
The package listens to event on an MySQL database and updates the user in real time.
This package is based on the original ZongJi and the original mysql-events modules.
Written by Niv Apo
Install
npm install zmanemet
Quick Start
const mysql = require('mysql');
const ZmanEmet = require('zmanemet');
const { Server } = require("socket.io");
const program = async () => {
// Create a connection to your DB
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'root',
});
// Create a socket.IO server
const io = new Server(3000);
// Create a ZmanEmet instance and pass your db & socket.io connections
const instance = new ZmanEmet(connection,io);
// Start your instance
await instance.start();
// Create a trigger for an event
let trigger = {
name: 'TEST',
expression: 'MY_SCHEMA.users.name',
statement: "UPDATE", // see MySQLEvents.STATEMENTS.ALL for full list of options
onEvent: (event) => {
// Process the event and return the data to send to the client
let {before,after} = event.affectedRows[0];
let user_id = after.id;
let room_id = `user_${user_id}`; // The socket room to send the update
let event_name = "user.update"; // The event name in the sockets on the client side
// The data that will be sent to the socket
let data = {
user_id:user_id,
new_name:after.name
}
// Optional - send an update to the client via socket.io server
return [room_id,event_name,data];
},
};
zman.addTrigger(trigger);
// Add a listener for connection issues
instance.on(MySQLEvents.EVENTS.CONNECTION_ERROR, console.error);
};
program()
.then(() => console.log('Waiting for database events...'))
.catch(console.error);
Usage
### #constructor(dbConnection, socketsConnection)
Instantiate and create a database connection using a preexisting connection
const connection = mysql.createConnection({
host: 'localhost',
user: 'username',
password: 'password',
});
const myInstance = new ZmanEmet(connection,io);
#start()
start function ensures that MySQL is connected before resolving its promise
javascript
myInstance.start()
.then(() => console.log('I\'m running!'))
.catch(err => console.error('Something bad happened', err));
### #stop()
stop function terminates MySQL connection and stops ZongJi before resolving its promise
javascript
myInstance.stop()
.then(() => console.log('I\'m stopped!'))
.catch(err => console.error('Something bad happened', err));
### #pause()
pause function pauses MySQL connection until #resume()
is called, this it useful when you're receiving more data than you can handle at the time
javascript
myInstance.pause();
### #resume()
resume function resumes a paused MySQL connection, so it starts to generate binlog events again
javascript
myInstance.resume();
### #addTrigger({ name, expression, statement, onEvent })
Adds a trigger for the given expression/statement and calls the onEvent
function when the event happens
instance.addTrigger({
name: 'MY_TRIGGER',
expression: 'MY_SCHEMA.MY_TABLE.MY_COLUMN',
statement: MySQLEvents.STATEMENTS.INSERT,
onEvent: async (event) => {
// Here you will get the events for the given expression/statement.
// This function executes after a trigger has fired from the DB and before the data was sent to the sockets.
// You can process the data and send just what you want to the socket.iolet socket_room = "";
let event_name = "";
let event_data = "";
return [socket_room,event_name,event_data];
},
});
The name
argument must be unique for each expression/statement, it will be user later if you want to remove a trigger
instance.addTrigger({
name: 'MY_TRIGGER',
expression: 'MY_SCHEMA.*',
statement: MySQLEvents.STATEMENTS.ALL,
...
});
instance.removeTrigger({
name: 'MY_TRIGGER',
expression: 'MY_SCHEMA.*',
statement: MySQLEvents.STATEMENTS.ALL,
});
The expression
argument is very dynamic, you can replace any step by *
to make it wait for any schema, table or column events
javascript
instance.addTrigger({
name: 'Name updates from table USERS at SCHEMA2',
expression: 'SCHEMA2.USERS.name',
...
});
javascript
instance.addTrigger({
name: 'All database events',
expression: '*',
...
});
javascript
instance.addTrigger({
name: 'All events from SCHEMA2',
expression: 'SCHEMA2.*',
...
});
javascript
instance.addTrigger({
name: 'All database events for table USERS',
expression: '*.USERS',
...
});
javascript
instance.addTrigger({
name: 'All database events for table USERS',
expression: '*.USERS',
...
});
The statement
argument indicates in which database operation an event should be triggered
javascript
instance.addTrigger({
...
statement: MySQLEvents.STATEMENTS.ALL,
...
});
Allowed statements
The onEvent
argument is a function where the trigger events should be threated
javascript
instance.addTrigger({
...
onEvent: (event) => {
console.log(event); // { type, schema, table, affectedRows: [], affectedColumns: [], timestamp, }
},
...
});
### #removeTrigger({ name, expression, statement })
Removes a trigger from the current instance
javascript
instance.removeTrigger({
name: 'My previous created trigger',
expression: '',
statement: MySQLEvents.STATEMENTS.INSERT,
});
Tigger event object
It has the following structure:
{
type: 'INSERT | UPDATE | DELETE',
schema: 'SCHEMA_NAME',
table: 'TABLE_NAME',
affectedRows: [{
before: {
column1: 'A',
column2: 'B',
column3: 'C',
...
},
after: {
column1: 'D',
column2: 'E',
column3: 'F',
...
},
}],
affectedColumns: [
'column1',
'column2',
'column3',
],
timestamp: 1530645380029,
nextPosition: 1343,
binlogName: 'bin.001',
}
Make sure the database user has the privilege to read the binlog on database that you want to watch on.
LICENSE
MIT © Niv Apo