How can I push data to a browser where the data is based on a SQL statement?

Question

I know there are threads out there on this topic but do seem to answer quite what I am looking for. I have never done any push technology before so some guidance here is appreciated. I understand how when something has changed that that triggers the push to any browser that is listening but I do not think that quite fits the scenario that I am looking at trying to do.

We are rebuilding our users web application where they track shipments. We will be allowing the users to build there own searches that match how they do their job. For example, some will look for any shipment that is scheduled to deliver today where others look for shipments that are to be picked up today and still other that look for shipments that need to be scheduled for pickup. So when they come in an open the application I can give them a count for each of their work tasks that they need to do today. So now what I want is that the count will change based on the SQL being re-run. But I do not want the user to have to refresh the page to see the new count.

How do I have this SQL run and push the current count to any browser that is using this SQL. What is the mechanism that automatically re-runs this SQL? Keep in mind that I will have 50 or more of these unique SQLs that will need to be executed and the count pushed.

Thanks for your guidance!

Answer 1

I think this falls pretty cleanly into AJAX's role. AJAX will allow you to make GET and POST requests to the server, which will process the query and return results to a JS function. At risk of jQuery evangelism, the API makes this sort of thing extremely easy and standard, and you can have pretty much any event you want to activate it.

This has a few concerns, namely client-side inputs and SQL injection. If you're sending any input through a POST request, you have to be VERY careful to sanitize everything. Use prepared statements, don't perform query string concatination+execution, and generally assume the user will try to send text that you don't want them to. Give some server-side bounding to what inputs will be acknowledged successfully (e.g. If the options are "Left" or "Right", and they give "Bottom", either default it or drop it).

1. Client activates request (timed or event)
2. JS makes AJAX call to server (optionally with parameters)
3. Server validates any inputs and processes query
4. Server sends results back
5. JS uses results to modify DOM

Answer 2

AJAX pulling is one solution, although others exist that might be better suited and save you resources*...

For example, having a persistent Websocket connection would help minimize the cost of establishing new connections and having repeated requests that are mostly redundant.

As a result, your server should have a lower workload and your application would require less bandwidth (if these are important to you).

Even using a Websocket connection just to tell your client when to send an AJAX request can sometime save resources.

Here's a quick Websocket Push demo

You can use many different Websocket solutions. I wrote a quick demo using the Plezi framework because it's super easy to implement, but there are other ways to go about this.

The Plezi framework is a Ruby framework that runs it's own HTTP and Websocket server, independent of Rack.

The example code includes a controller for a model (DemoController), a controller for the root index page (DemoIndex) and a controller for the Websocket connection (MyWSController).

The example code seems longer because it's all in one script - even the HTML page used as a client... but it's really quite easy to read.

The search requirements are sent from the client to the web server (the search requires that the model's object ID is between 0 and 50).

Every time an object is created (or updated), an alert is sent to all the connected clients, first running each client's searches and then sending any updates.

The rest of the time the server is resting (except pinging every 45 seconds or so, to keep the websocket connection alive).

To see the demo in action, just copy and paste the following code inside your IRB terminal** and visit the demo's page:

require 'plezi'

class MyWSController
    def on_open
        # save the user data / register them / whatever
        @searches = []
    end
    def on_message data
        # get data from the user
        data = JSON.parse data
        # sanitize data, create search parameters...
        raise "injection attempt: #{data}}" unless data['id'].match(/^\([\d]+\.\.[\d]+\)\z/)
        # save the search
        @searches << data
    end
    def _alert options
        # should check @searches here
        @searches.each do |search|
            if eval(search['id']).include? options[:info][:id]
                # update
                response << {event: 'alert'}.merge(options).to_json
            else
                response << "A message wouldn't be sent for id = #{options[:info][:id]}.\nSaved resources."
            end
        end
    end
end

class DemoController
    def index
        "use POST to post data here"
    end
    # called when a new object is created using POST
    def save
        # ... save data posted in params ... then:
        _send_alert
    end
    # called when an existing object is posted using POST or UPDATE
    def update
        # ... update data posted in params ... then:
        _send_alert
    end
    def demo_update
        _send_alert message: 'info has been entered', info: params.update(id: rand(100), test: 'true')
        "        This is a demo for what happens when a model is updated.\n
        Please Have a look at the Websocket log for the message sent."
    end
    # sends an alert to 
    def _send_alert alert_data
        MyWSController.broadcast :_alert, alert_data
    end
end

class DemoIndex
    def index search = '(0..50)'
        response['content-type'] = 'text/html'
        <<-FINISH
<html>
<head>
<style>
   html, body {height: 100%; width:100%;}
   #output {margin:0 5%; padding: 1em 2em; background-color:#ddd;}
   #output li {margin: 0.5em 0; color: #33f;}
</style>
</head><body>
<h1> Welcome to your Websocket Push Client </h1>
<p>Please open the following link in a <b>new</b> tab or browser, to simulate a model being updated: <a href='#{DemoController.url_for id: :demo_update, name: 'John Smith', email: '[email protected]'}', target='_blank'>update simulation</a></p>
<p>Remember to keep this window open to view how a simulated update effects this page.</p>
<p>You can also open a new client (preferably in a new tab, window or browser) that will search only for id's between 50 and 100: <a href='#{DemoIndex.url_for :alt_search}'>alternative search</a></p>
<p>Websocket messages recieved from the server should appeare below:</p>
<ul id='output'>
</ul>
<script>
var search_1 = JSON.stringify({id: '#{search}'})
output = document.getElementById("output");
websocket = new WebSocket("#{request.base_url 'ws'}/ws"); 
websocket.onmessage = function(e) { output.innerHTML += "<li>" + e.data + "</li>" }
websocket.onopen = function(e) { websocket.send(search_1) }
</script>
</body></html>
FINISH
    end
    def alt_search
        index '(50..100)'
    end
end

listen

route '/model', DemoController
route '/ws', MyWSController
route '/', DemoIndex

exit

To view this demo visit localhost:3000 and follow the on screen instructions.

The demo will instruct you to open a number of browser windows, simulating different people accessing your server and doing different things.

As you can see, both the client side javascript and the server side handling aren't very difficult to write, while Websockets provide for a very high level of flexibility and allows for better resource management (for instance, the search parameters need not be sent over and ver again to the server).

---

* the best solution for your application depends on your specific design. I'm just offering another point of view.

** ruby's terminal is run using irb from bash, make sure first to install the plezi network using gem install plezi