Implementation of a web screen to monitor DB's change data in real time

Hello.

I came to Hashcode to solve the blockage after a long time.

Thank you always for your reply.

Developing in node.js environment.

You are using mysql, and you have created a msg, value column, for example, in the tb_test table.

<tb_test>
no(pk)    msg     value 

1        'test'    1 

2        'hello    32

When data is inserted as shown above,

The value of pk 1 is updated from 1 to 382

pk 3's new tuple is created

You want to implement the case so that it can be detected in real time and monitored by web users.

Currently using socket.io,

<index.html>
var socket = io();
            $('#add_status').click(function() {
                socket.emit('status added', $('#comment').val());
            });

            socket.on('refresh feed', function(msg) {
                $('#show_comments').append(msg + '<br /><br />');
            });

<app.js>
io.on('connection', function(socket) {
  console.log('A user is connected');

  socket.on('status added', function(status) {

    add_status(status, function (res) {
      if (res) {
        io.emit('refresh feed', status);
      } } else {
        io.emit('error');
      }
    });

  });

});

var add_status = function (status, callback) {
  pool.getConnection( function (err, conn) {
    if (err) {
      callback (false);
      return;
    }

    const insertQuery = ' INSERT INTO tb_test (msg, createdAt) VALUES '
                      + ' ( ' 
                      + + mysql.escape(status) 
                      + + ' , now() '
                      + ' ) ';

    console.log(insertQuery);

    conn.query(insertQuery, function (err, rows) {
      conn.release();

      if (!err) {
        callback (true);
      }

    });

    conn.on('error', function (err) {
      callback (false);
      return;
    });

  });
}

In the same way, if you type a message on the web and press the button,

We were able to save the entered message to db and at the same time insert it into <divid="show_comments"></div> on the web.

Please advise me how to detect and show DB data when it is changed (insert, update, delete) and show it on the web.

mysql socket.io node.js real-time

2022-09-21 23:03

4 Answers

You can push (broadcast) data to connected clients using HTml5's web socket.

You can use mysql's trigger to process callback when changing data.

You can create mysqludf so that httpurl can be called, and you can call the udf from the trigger to send changed data, etc. to the server side. The server can push the received data to clients connected to the web socket.

In the past, real-time web implementation was used as comet technology, but now that html5 is the standard, it can be easily implemented as a web socket.

2022-09-21 23:03

If the prerequisite is a change in DB data, the DB server should be the subject of the event. I've never thought about this before, so I looked up the materials There's a trigger https://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

If you collect the change history on one table and show it on the screen, it will be possible to implement it.

Or you can search mysql's log to analyze the SQL syntax executed and collect data.

http://kimyongjin.com/post/631

2022-09-21 23:03

The simplest way is

If the data is not large, the DataSet is held as JS in the Object formula, and only the changed parts are received and updated...

I think there is also a way to add sydate() with something like the Last_Update column and after the last DB query time, or to add FLAG column at UPDATE/INSERT.

Thank you.

2022-09-21 23:03

I don't know the case of node.js, but I think Ruby on Rails would do this.

Aren't you sending the Insert/Update/Delete command from node.js anyway? If so, the server can handle it. You create a specific class that is responsible for changing the value of the table, and if there is a change, you can take care of it.

In the case of Ruby on Rails, the value is written to the table in the DB through the model. There is a method that is called whenever the value of DB is changed (before_create, before_save, etc.) It is convenient because you can do what you want here.

2022-09-21 23:03

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656