CODEWITHSUNDEEP
ajaxnode.jssqlitesocketssocket.io
Marc
Marc

Reputation: 1430

Nodejs - How to kill a running SQLite query

Possible similar question :

SQL: Interrupting a query

Is there a way to abort an SQLite call?

Hi everyone, I am actually using socket.io and sqlite3 modules to perform SELECT query on a SQLite database. When a user click on an OpenLayers Map, its sends a signal to the server (through socket.io) to gather informations by performing spatial request (like intersection, union... using Spatialite extension) and then finally send back data to the client (these are long-running queries (depending of the amount of geometries) ) to show a popup on the map where the user clicked to.

The problem is: if a user click many times on the map, sending many requests to the server, only the last one is important. Imagine that if a query takes 5 sec to be executed, and that a user click 3 times in a second on the map (he just wants the last location where he clicked to be used), then the server will do 3 queries, sending back 3 signals through socket.io (and opening 3 popup, we just need the last one to be opened) ! Is there any solution to kill/abort a running sqlite query with nodejs ?

Example code :

socket.on('askForInfo', function (data) {
    sendInfo(socket, data.latitude, data.longitude);
});

sendInfo definition : 

function sendInfo(socket, lat, lng) {
    // Database connection
    var db = new sqlite.Database('some file.sqlite', sqlite.OPEN_READONLY);

    // Load Spatialite extension
    db.loadExtension('mod_spatialite', function(err) {
        // Query doing spatial request
        db.get("VERY LONG SQL QUERY", function(err, row) {
            // Send the data gathered from database
            socket.emit('sendData', row['some sql column']);
        });
    });
}

I want to do something like : 

if ("sendInfo didn't finished to emit any signal through socket"
    AND "user did another resquest") 
then
    "kill all running sendInfo function execution and sql query"

I know that if there are many users connected this won't work like that (I may need to use session to know for which user the function is actually gathering data). But I don't find any solution even if there is only one user.

I tryed using AJAX(jquery) instead of socket.io. I can abort the xhr request, but the SQL query is still running even if the request is aborted until she is finish ( using lot of ressources uselessly )

Thanks in advance for your answer.

Upvotes: 3

Views: 1649

Answers (2)

Nick Zavaritsky
Nick Zavaritsky

Reputation: 1489

Nodejs’ SQLite has interrupt method, albeit currently undocumented: https://github.com/mapbox/node-sqlite3/issues/1205#issuecomment-559983976

Upvotes: 1

Marc
Marc

Reputation: 1430

Thanks for your answer Qualcuno.

I found a solution, using child_process to query database from another process, killing this one if user do another request.

var cp = require('child_process');
// more stuff ...

socket.on('askForInfo', function (data) {
    // if process is connected, kill it (query isn't terminated)
    if (child.connected) {
        child.kill();
    }

    // create a new process executing 'query_database.js'
    child = cp.fork('./query_database.js', [
        data.latitude, data.longitude
    ]);

    // when invoking [ process.send(some_data_here) ] in child process, fire this event to send data to the user
    child.on('message', function(d) {
        socket.emit('sendData', d)
    };
});

Upvotes: 4

Related Questions