0

I am writing an application whereby some external module/component is updating a SQLite database with new data every few hundred milliseconds or so, and my job is to write an application that queries that data and broadcasts it over sockets every few hundred milliseconds as well.

So currently I'm doing something like this with node, express, and socket.io:

timer = setInterval(function() {
        db.all('SELECT * FROM cache', function(err, rows) {
            io.emit('data', rows);
        });
    }, 
    400
);

But I feel like there should be a more direct approach to this, whereby I can maintain a socket connection directly to the database, and listen for changes "live", rather than having to do blind queries (even if the data may not have changed), and emit.

Maybe this is not supported by SQLite (which is fine, I think I have some flexibility in the storage system I'm using), but is what I'm asking at all possible?

Note that I don't have control over the database updating process, so I can't just emit the data I'm about to store in the database. That whole process is a black box C program and I ONLY have access to the database itself.

AgmLauncher
  • 7,070
  • 8
  • 41
  • 67
  • Your browser could connect to a WebSocket-to-TCP bridge with a browser-side ODBC driver (note: I don't know if any exist, but you could port one from Node; you'll definitely need to make it use WebSockets instead of TCP, in addition to any other environment changes). The WS-to-TCP bridge (e.g., https://github.com/kanaka/websockify) would be a long-running server process to accept WebSocket connections and forward the data to a host (here, your DB) over raw TCP. That said, getting the database to proactively report updates is a bit tricky; see http://stackoverflow.com/q/812233/710446 – apsillers Sep 08 '14 at 15:41

1 Answers1

2

What you're looking for is commonly called pub/sub (short for publish and subscribe). Clients waiting for data connect to a server and subscribe to the sort of events they want to receive. The data originators also connect to this server and publish events. The RPC with events that Socket.IO gives you are really similar to this. The clients have set up handlers for certain types of events, and the server fires these events with the appropriate data.

The problem is, pub/sub isn't typically implemented in a database. (Redis is an exception.) SQLite certainly has no capability for this. Since you can't modify the original application and only have access to the file database, there is nothing you can do. What you need is to effectively make your server an adapter from polling the database to broadcasting messages.

I do see a problem though with your setup. The first is that you are querying the database every 400 milliseconds. Don't do that. What if your query takes 500 milliseconds? Now you have a second query piling up. What if those two queries are now slow because they are both attempting to run at the same time? Now you have 3, 4, 5, and then 100 queries piling up. Don't schedule your next query to run until one is done. Check out an implementation of throttle for this.

The next problem is that you are blindly sending out all of the results to the client every time. I don't know what your application does, but I'm guessing that there is a chance for overlap from the previous query. Does your database have columns with timestamps? You could modify your query to use them. Or, modify your application to filter them.

Brad
  • 159,648
  • 54
  • 349
  • 530