TestingMisconfused

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:

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.