A number of factors conspired to persuade me to junk the mono/ASP.NET set-up of previous examples:

With this in mind, I sent less than a ton of my hard-earned to New IT, took delivery of my SheevaPlug 2 days later and 2 days after that had ported over my webserver, mailserver and database and rejigged my websites to use PHP.

The Plug has a 1.2GHz processor, half a gig of RAM and half a gig of (flash) disk space as opposed to the 1.4GHz, 1 gig and half TB of my trusty old Ubuntu PC. Despite the seeming shortfall in resources, I reckon my sites are snappier than before:

I still use my PC to develop and test the sites, but they are copied via FireFTP to the webserver on the Plug, which can be left on 24-7 without stressing me (much) about the leccy bill. I'm using SSH and webmin to administer the Plug.

MySQL via PHP

The WebService that acted as a bridge between jQuery and MySQL has been replaced with 2 PHP files:

MYSQL2JSON.php

This takes the parameter passed by jQuery in a POST request, slaps it into a SELECT query and returns the MySQL DataSet as JSON:


$SQLparm = $_POST['SQLparm'];

// Connecting, selecting database
$link = mysql_connect($_server, $_SQLuser, $_SQLpwd)
    or die('Could not connect: ' . mysql_error());
mysql_select_db('SQLTestDB') or die('Could not select database');

// Performing SQL query
$query = 'SELECT '.$SQLparm.';';
$res = mysql_query($query) or die('Query failed: ' . mysql_error());

// iterate over every row
while ($row = mysql_fetch_assoc($res)) {
    $rows[] = $row;
}
// JSON-ify all rows together as one big array
echo json_encode($rows);

// Free resultset
mysql_free_result($res);

// Closing connection
mysql_close($link);
                

MYSQLexec.php

Calls a MySQL stored procedure. Note that the POST parameter arrives with escaped quotes, so the extraneous slashes are stripped out.


$SQLparm = str_replace("\\", "", $_POST['SQLparm']);

// Connecting, selecting database
$link = mysql_connect($_server, $_SQLuser, $_SQLpwd)
    or die('Could not connect: ' . mysql_error());
mysql_select_db('SQLTestDB') or die('Could not select database');

// Run stored procedure
mysql_query('CALL '.$SQLparm) or die('Query failed: ' . mysql_error());

// Closing connection
mysql_close($link);
                

And that's all the server-side code I need to bridge jQuery to MySQL.

MySQL Stored Procedures

The SQLexec() WebMethod defined in the WebService allows us to call Stored Procedures, so we can go beyond simple SQL commands and exploit the performance of MySQL, rather than relying on client-side code to manipulate the data.


/*
    MySQL version (created in Catalogs section of MySQL Administrator)
*/
CREATE PROCEDURE `SimulatedTestRun`(
    runStarted DATETIME,
    testPC VARCHAR(50),
    testBuild VARCHAR(50),
    testCode INT,
    outputMatch TINYINT(1))
BEGIN
    INSERT INTO TestRun (RunStarted,TestPC,TestBuild) 
    VALUES (runStarted, testPC, testBuild);
    SELECT MAX(RunID) FROM TestRun INTO @LastRunID;
    INSERT INTO TestResults (TestCode,RunID,OutputMatch)
    VALUES (testCode,@LastRunID,outputMatch);
END
/*
MySQL version
*/
CREATE PROCEDURE `CleanSimulatedTestRun`()
BEGIN
    DELETE FROM TestRun WHERE (RunID > 1003);
    DELETE FROM TestResults WHERE (TestResultsID > 4);
END

jQuery

A few changes to the previous examples: no need to specify contentType or dataType, the POST parameter is coded differently and there is no .d property in the returned JSON:


//_jsSQL() a class to pass messages between Javascript and SQL
//via PHP
function _jsSQL() {
//_ajaxSQL() is a private method used to pass SQL commands via jQuery's .ajax() method
    function _ajaxSQL(_url, _data, _error, _success) {
        $.ajax({
            type: "POST",
            url: "/PHP/" + _url + ".php",
            data: _data,            
            error: function (msg) {
                _error(msg);
            },
            success: function (msg) {
                _success(msg);
            }
        });
    };
//_SQLtable() is a private method to handle requests to turn SQL results into HTML tables
// it allows for an optional callback function when the SQL call is successful
    function _SQLtable(_url, _data, _TableName, _fnCallback) {
        _ajaxSQL(_url, _data, 
            function () {
                _SQLtableError(_TableName);
            },
            function(msg) {
                _SQLtableSuccess(msg, _TableName, _fnCallback);
            });
    }
//_SQLtableSuccess is a private method to iterate through the JSON returned from SQL
// and construct an HTML table with a caption and header row based on the table and column names.
// The table is inserted into an HTML element with an ID based on the table name
    function _SQLtableSuccess(msg, _TableName, _fnCallback) {
        var strJSON = eval(msg);
        _TableName = _TableName.split(" ")[0];
        var JSONtable = "<table><caption>" + _TableName + "</caption>";
        JSONtable += "<thead><tr>";
        for (var field in strJSON[0]) {
            JSONtable += "<th>" + field + "</th>";
        }
        JSONtable += "</tr></thead>";
        for (var i = 0, l = strJSON.length; i < l; i++) {
            JSONtable += "<tr>";
            var thisRecord = strJSON[i];
            for (var field in thisRecord) {
                JSONtable += "<td>" + thisRecord[field] + "</td>";
            }
            JSONtable += "</tr>";
        }
        JSONtable += "</table>";
        $("#" + _TableName).html(JSONtable);
        if (_fnCallback) {
            _fnCallback();
        }
    }
//_SQLtableError() replaces the table with an error message if there's a problem
    function _SQLtableError(_TableName) {
        $("#" + _TableName).text("Sorry - can't fetch " + _TableName + "  from the database")
    }
//_getTable() creates a table based on the table name with an optional callback
    function _getTable(_TableName, _fnCallback) {
        _SQLtable(
            "MYSQL2JSON", 
            "SQLparm=* FROM " + _TableName,
            _TableName,
            _fnCallback
        );
    }

//Calling a Stored Procedure is simpler as the parameters can be passed in as quoted strings 
//(but note that there is a bit more work to do in the PHP above):            

//_cleanRun() resets and scrolls to the tables displayed on this page
    function _cleanRun() {
        _ajaxSQL(
            "MYSQLexec", 
            "SQLparm=CleanSimulatedTestRun",
            function () {
                alert("Can't clean Test Runs");
            },
            function() {                _getTable("TestBed");
                _getTable("TestConditions");
                _getTable("TestRun ORDER BY RunID", 
                    function () {
                        $("#TestRun caption").append("<form><input id='btnSTR' type='button' value='Simulate Test Run' /></form>");
                    });
                _getTable("TestResults ORDER BY TestResultsID", 
                    function () {                        $("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
                        $("#lblSQL").fadeIn("slow");
                        $("#frmSQL").removeClass("loading");
                    });
	        }                
        );
    }
//_generateRun() simulates a new test run and updates the tables
    function _generateRun(rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch) {
        _ajaxSQL(
            "MYSQLexec", 
            "SQLparm=SimulatedTestRun('" + rtRunStarted + "','" + rtTestPC + 
                "','" + rtTestBuild + "'," + rtTestCode + "," + rtOutputMatch + ")",
            function () {
                alert("Can't create new test run");
            },
            function () {
                _getTable("TestRun ORDER BY RunID", 
                    function () {
                        $("#TestRun caption").append("<form><input id='btnSTR' type='button' value='Simulate Test Run' /></form>");
                    });
                _getTable("TestResults ORDER BY TestResultsID", 
                    function (){
                        $("#TestRun tr:last td:eq(0)").addClass("highlight");
                        $("#TestResults tr:last td:eq(0)").addClass("highlight");
                        $("html,body").animate({ scrollTop: $("#btnSQL").offset().top }, 500);
                    });
            }
        );
    }
//finally, close out the class by exposing some of the methods publicly
//also return this so calls can be chained
    return {
        getTable: function (_TableName, _fnCallback) {
            _getTable(_TableName, _fnCallback);
            return this;
        },
        cleanRun: function () {
            _cleanRun();
            return this;
        },
        generateRun: function (rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch) {
            _generateRun(rtRunStarted,rtTestPC,rtTestBuild,rtTestCode,rtOutputMatch);
            return this;
        }
    }
};

Then, within our onload function, we can create a _jsSQL() object and call its public methods, e.g.:


    var jsSQL = _jsSQL();
    jsSQL.cleanRun();
    jsSQL.getTable("TestBed")
        .getTable("TestConditions")
        .getTable("TestRun")
        .getTable("TestResults");
    jsSQL.generateRun("2009-10-19 6:02:00","TestVista","1.1.0.1",100000,0);    

Note that I've changed the date delimiter to a hyphen - trying to pass slashes from JavaScript to PHP to MySQL is just asking for trouble...

Possible Enhancements

The length of the JSON string returned by the WebService needs to be set to a sensible limit (if you find the default inadequate) - but if you were expecting more than a screenful of results, you would page it, wouldn't you?...

Questions

You said this was for performance - why are you using eval() and <table>?

  • Because I'm in control of the string being evaluated.
  • Because it's tabular data - I'm not using tables for layout.

Well, it's not that fast, is it?

It's a webserver that cost less than a ton, sips electricity and makes do with resources that would just about have been impressive on a desktop 10 years ago - what more do you want?

Besides, it's tiny, look:
plug computer
Under it is an external hard drive based on Cylon technology, allowing the plug to function as a household NAS, among other things.
The thing on top is a 50 pence piece to provide scale.

Sample Tables

These examples are based on this skeleton outline of some sample tables and relationships:
Example SQL Tables