Based on my own limited experience with the Dojo Toolkit, you either love or hate it. First, I disliked the steep learning curve. After a few days, I began to enjoy it, for example, because of the Dijit interface vodoo: spreadsheet (data grid), auto-completion combobox, effects & animation… Many of these GUI elements can be connected to a dojo store. A store is responsible for accessing and manipulating data.
Needless to say how easy it is to populate a sortable, … data grid using a store. Based on the proxy sketched in the previous post, I’ve prototyped two MySQL stores for dojo. One maps to a MySQL table, the other one maps to JSON stored in a BLOB to make MySQL look a bit like a document database.
var store = new WhateverStore(/*...*/)
var ostore = new ObjectStore({ objectStore: store });
var grid = new DataGrid({
store: ostore,
query: {},
queryOptions: {},
structure: [
{ name: "First Name", field: "first_name", width: "25%" },
{ name: "Last Name", field: "last_name", width: "25%" },
{ name: "Mail", field: "email", width: "50%" }
]
},
"gridDiv"
);
grid.startup();
Dojo stores
Dojo 1.9 stores are somewhat similar to HTML5/W3C’s IndexedDB object stores. The dojo store API lets you store and fetch objects in a key value style. Unlike with a plain key-value store, the data model is not limited to a one dimensional list/array structure. Instead, a parent-child hierarchy can be established between values. Searching is not limited to key lookup. A dojo store can support sorting and complex filtering. There is even the concept of a transaction.
The two MySQL stores examples implement no more than the most basic functionality. Both ignore transactions and hierarchical storage. Transactions would not be difficult to support. The nested set model could be used for mapping trees to flat relational tables. Associating arbitrary meta data falls into the same category: it could be implemented.
| Dojo store API | JsonpMySQLFields | JsonpMySQL (BLOB) |
|---|---|---|
get(id) |
yes | yes |
query(query, options)
|
|
|
put(object, options) |
yes, no hierarchical structure | yes, no hierarchical structure |
add(object, options) |
yes | yes |
remove(id) |
yes | yes |
getIdentity(object) |
yes | yes |
queryEngine(query, options) |
yes, default | yes, default |
transaction() |
not implemented | not implemented |
getChildren(object, options) |
not implemented | not implemented |
getMetadata(object) |
not implemented | not implemented |
JSON object in BLOB: sure, but…
A MySQL dojo store that maps a table (JsonpMySQLFields) could support all of the dojo store API but all objects would have to have the same structure. The object must map to the table schema. When storing JSON serialized objects in a BLOB column (JsonpMySQL), there is no schema to observe. However, searching and sorting stored objects efficiently becomes almost impossible. MySQL lacks SQL functions to query a JSON object/document stored in a BLOB. Serializing an object to XML, for which MySQL has XML functions, was out of scope. It could be worth trying, some hints are here.
JsonpMySQLFields: mapped table approach
The complete source code of the JsonpMySQLFields dojo data store is below. The store prototype assumes the use of the PHP proxy script from the previous post. The PHP proxy script takes arbitrary SQL statements as a GET parameter, executes the SQL and returns the result as a JSON document. As noted in the last post you should ask yourself twice whether the necessary proxy should allow exeution of arbitrary SQL or a proper REST API would be more appropriate. Whatever your take is, the direct SQL approach is nice for prototyping: implementing a dojo store boils down to dynamically generating some SQL statements and executing them through a proxy script. The details have been described already in the last posting.
define("dojo/store/JsonpMySQLFields", ["../number", "../_base/array", "../string", "../request/script", "../when", "../_base/xhr", "../_base/lang", "../json", "../_base/declare", "./util/QueryResults" /*=====, "./api/Store" =====*/
], function(number, array, string, script, when, xhr, lang, JSON, declare, QueryResults /*=====, Store =====*/){
// No base class, but for purposes of documentation, the base class is dojo/store/api/Store
var base = null;
/*===== base = Store; =====*/
/*=====
var __HeaderOptions = {
// headers: Object?
// Additional headers to send along with the request.
},
__PutDirectives = declare(Store.PutDirectives, __HeaderOptions),
__QueryOptions = declare(Store.QueryOptions, __HeaderOptions);
=====*/
return declare("dojo.store.JsonpMySQLFields", base, {
// summary:
// This is a basic store for RESTful communicating with a server through JSON
// formatted data. It implements dojo/store/api/Store.
constructor: function(options){
// summary:
// This is a basic store for RESTful communicating with a server through JSON
// formatted data.
// options: dojo/store/JsonRest
// This provides any configuration information that will be mixed into the store
declare.safeMixin(this, options);
},
method: "http",
host: "127.0.0.1",
port: 8080,
interface: "sql",
basicAuthUser: "root",
basicAuthPassword: "secret",
mysqlTable: "dojo_jsonp_fields",
mysqlFields: [],
mysqlIdProperty: "dojo_id",
// idProperty: String
// Indicates the property to use as the identity property. The values of this
// property should be unique.
idProperty: "id",
// sortParam: String
// The query parameter to used for holding sort information. If this is omitted, than
// the sort information is included in a functional query token to avoid colliding
// with the set of name/value pairs.
get: function(oid){
// summary:
// Retrieves an object by its identity. This will trigger a GET request to the server using
// the url `this.target + id`.
// id: Number
// The identity to use to lookup the object
// returns: Promise
// The object in the store that matches the given id.
// SQL INJECTION
var sql = "SELECT " + this.mysqlIdProperty + "," + this.mysqlFields.toString();
sql += " FROM " + this.mysqlTable + " WHERE dojo_id = " + number.parse(oid);
return when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp"}
).then(lang.hitch(this, this._extractFirstRow))
);
},
getIdentity: function(object){
// summary:
// Returns an object's identity
// object: Object
// The object to get the identity from
// returns: Number
return object[this.idProperty];
},
put: function(object, options){
// summary:
// Stores an object. This will trigger a PUT request to the server
// if the object has an id, otherwise it will trigger a POST request.
// object: Object
// The object to store.
// options: __PutDirectives?
// Additional metadata for storing the data. Includes an "id"
// property if a specific id is to be used.
// returns: dojo/_base/Deferred
// Store.PutDirectives = declare(null, {
// summary:
// Directives passed to put() and add() handlers for guiding the update and
// creation of stored objects.
// id: String|Number?
// Indicates the identity of the object if a new object is created
// before: Object?
// If the collection of objects in the store has a natural ordering,
// this indicates that the created or updated object should be placed before the
// object specified by the value of this property. A value of null indicates that the
// object should be last.
// parent: Object?,
// If the store is hierarchical (with single parenting) this property indicates the
// new parent of the created or updated object.
// overwrite: Boolean?
// If this is provided as a boolean it indicates that the object should or should not
// overwrite an existing object. A value of true indicates that a new object
// should not be created, the operation should update an existing object. A
// value of false indicates that an existing object should not be updated, a new
// object should be created (which is the same as an add() operation). When
// this property is not provided, either an update or creation is acceptable.
options = options || {};
var sql = "";
var values = "";
var id = ("id" in options) ? options.id : this.getIdentity(object);
var hasId = typeof id != "undefined";
if (("overwrite" in options) && options["overwrite"]) {
if (!hasId) {
throw "You must provide the id of the object to update";
}
array.forEach(this.mysqlFields, lang.hitch(this, function (field) {
if (field in object) {
values += field + "=";
values += "'" + this._escapeString(object[field]) + "', ";
}
}));
if (values.length == 0) {
throw "Object has no known property for SQL column mapping";
}
sql = "UPDATE " + this.mysqlTable + " SET " + values + " version = version + 1";
sql += " WHERE " + this.mysqlIdProperty + "= " + number.parse(id);
} else {
var fields = "";
if (hasId) {
fields += this.mysqlIdProperty + ", ";
values += number.parse(id) + ", "
}
array.forEach(this.mysqlFields, lang.hitch(this, function (field) {
if (field in object) {
fields += field + ", ";
values += "'" + this._escapeString(object[field]) + "', "
}
}));
if (fields.length == 0) {
throw "Object has no known property for SQL column mapping";
}
sql = "INSERT INTO " + this.mysqlTable + "(";
sql += fields.substring(0, fields.length - 2);
sql += ") VALUES (" + values.substring(0, values.length - 2) + ")";
}
return when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(
function (reply) {
if (reply && "last_insert_id" in reply)
return reply.last_insert_id;
return reply;
}
)
);
},
add: function(object, options){
// summary:
// Adds an object. This will trigger a PUT request to the server
// if the object has an id, otherwise it will trigger a POST request.
// object: Object
// The object to store.
// options: __PutDirectives?
// Additional metadata for storing the data. Includes an "id"
// property if a specific id is to be used.
options = options || {};
options.overwrite = false;
return this.put(object, options);
},
remove: function(id, options){
// summary:
// Deletes an object by its identity. This will trigger a DELETE request to the server.
// id: Number
// The identity to use to delete the object
// options: __HeaderOptions?
// HTTP headers.
options = options || {};
var sql = "DELETE FROM " + this.mysqlTable + " WHERE " + this.mysqlIdProperty + "=" + number.parse(id);
return when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(
function (reply) {
if (reply && "errno" in reply) {
return reply;
}
return;
}
)
);
},
query: function(query, options){
// summary:
// Queries the store for objects. This will trigger a GET request to the server, with the
// query added as a query string.
// query: Object
// The query to use for retrieving objects from the store.
// options: __QueryOptions?
// The optional arguments to apply to the resultset.
// returns: dojo/store/api/Store.QueryResults
// The results of the query, extended with iterative methods.
options = options || {};
var sql = "SELECT " + this.mysqlFields.toString() + ", " + this.mysqlIdProperty;
sql += " FROM " + this.mysqlTable;
if (options) {
if (options.sort && options.sort.length) {
var order_by = "";
for (var i = 0; i< options.sort.length; i++) {
if (order_by.length)
order_by += ", ";
var sort = options.sort[i];
order_by += sort.attribute;
order_by += (sort.descending) ? " DESC" : " ASC";
}
if (order_by.length)
sql += " ORDER BY " + order_by;
}
if (options.start >= 0 || options.cout >= 0) {
var limit = "";
if (options.start)
limit += options.start;
if ("count" in options && options.count != Infinity) {
if (limit.length)
limit += ", ";
limit += options.count;
}
if (limit.length)
sql += " LIMIT " + limit;
}
}
var results = when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(lang.hitch(this, this._extractAllRows))
);
return QueryResults(results);
},
_getAddress : function(query) {
return this.method + "://" + this.basicAuthUser + ":" + this.basicAuthPassword + "@" +
this.host + ":" + this.port + "/" + this.interface + encodeURIComponent(query);
},
_extractRows : function(result, limit) {
var data_only = new Array();
var result_idx, row_idx;
var object;
if (result && "errno" in result) {
data_only.push(result);
return data_only;
}
for (result_idx = 0; result_idx < result.length; result_idx++) {
if ("errno" in result[result_idx]) {
data_only.push(result[result_idx]);
} else {
for (row_idx = 0; row_idx < result[result_idx].data.length; row_idx++) {
if ((limit > 0) && (row_idx >= limit)) {
return data_only;
}
tmp = new Object;
array.forEach(result[result_idx].data[row_idx], function (value, column_idx) {
tmp[result[result_idx].meta[column_idx].column] = value;
});
data_only.push(tmp);
}
}
}
return data_only;
},
_extractAllRows: function (result) {
return this._extractRows(result, -1);
},
_extractFirstRow: function (result) {
return this._extractRows(result, 1);
},
_escapeString: function (sql_value) {
sql_value = sql_value.toString();
return sql_value.replace('/"/g', '\\"');
}
});
});
The store invokes the proxy script using dojo.script. The "phrase" for doing so contains a dojo feature that has not been discussed before: hitching. The snippet below performs the asynchronous HTTP proxy call and then calls the function _extractAllRows() to convert the result into the format expected by the dojo store API.
/* ... */
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(lang.hitch(this, this._extractAllRows))
/* ... */
_extractAllRows: function (result) {
return this._extractRows(result, -1);
},
Due to the asynchronous execution model, _extractAllRows() may execute in a different context than it has been defined. Thus, this may point to a different object and _extractAllRows() may fail, for example, because this._extractRows is undefined in the execution context. hitch() solves the problem and forces the function to retain its original context.
The remainder of the sample store should bare no surprises. Note the incomplete escapeString() method – it is not production ready code. It may not even be clean or beautiful code but should help you getting started.
Putting things together: a data grid demo
As you are digging deeper into dojo, it is handy to have a copy of dojo on your local system instead of using a CDN service. For a data grid HTML demo, use cut&paste to copy the example JsonpMySQLFields store into a file JsonpMySQLFields.js and save it in /path/to/your_dojo_installation/dojo/store. Then, create a HTML document and load the necessary dojo modules using require.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="/home/nixnutz/Downloads/dojo-release-1.9.0/dijit/themes/claro/claro.css">
<style type="text/css">
@import "/home/nixnutz/Downloads/dojo-release-1.9.0/dojox/grid/resources/Grid.css";
@import "/home/nixnutz/Downloads/dojo-release-1.9.0/dojox/grid/resources/claroGrid.css";
#gridDiv {
height: 10em;
}
</style>
</head>
<body class="claro">
<script src="/home/nixnutz/Downloads/dojo-release-1.9.0/dojo/dojo.js"
data-dojo-config="async: true"></script>
<script>
require(
["dojo/dom-construct", "dojo/parser", "dijit/Dialog", "dojox/grid/DataGrid", "dojo/data/ObjectStore", "dijit/form/Select", "dojo/store/Memory", "dojo/dom", "dojo/on",
"dojo/when", "dojo/request/script", "dojo/request/notify", "dojo/json",
"dojo/store/JsonpMySQLFields", "dojo/domReady!"],
function(domConstruct, parser, Dialog, DataGrid,
ObjectStore, Select, Memory, dom, on, when,
script, notify, JSON, mysqlp) {
[...]
Do not forget to load the CSS required by the Dijit data grid.
Putting things together is way more complicated than populating a data grid with the contents of a MySQL table. Displaying a MySQL table as a sortable spreadsheet boils down to the following lines of code. First, create an object of the type JsonpMySQLFields with new mysqlp (hint: the require() – see above – is responsible for the name "mysqlp"). Give information to the object where to find the proxy script and which SQL table and SQL columns to use for the mapping. Convert the store into an object store and pass it to the DataGrid constructor. Tell the grid which div to render to and call startup().
var store = new mysqlp({
method: "http",
host: "127.0.0.1",
port: 8080,
interface: "index.php?sql=",
basicAuthUser: "root",
basicAuthPassword: "secret",
mysqlTable: "dojo_jsonp_fields",
mysqlFields: ["first_name", "last_name", "email"]
});
var ostore = new ObjectStore({ objectStore: store });
var grid = new DataGrid(
{
store: ostore,
query: {},
queryOptions: {},
structure: [
{ name: "First Name", field: "first_name", width: "25%" },
{ name: "Last Name", field: "last_name", width: "25%" },
{ name: "Mail", field: "email", width: "50%" }
]
}, "gridDiv");
grid.startup();
This is the SQL table used with the above:
CREATE TABLE `dojo_jsonp_fields` ( `dojo_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(255) DEFAULT '', `last_name` varchar(255) DEFAULT '', `email` varchar(255) DEFAULT '', `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `version` int(10) unsigned DEFAULT '1', PRIMARY KEY (`dojo_id`) )
Add a few bells and whistles (buttons to add, update, remove records) and you get the complete HTML document used for the screenshot on top. Please, understand the reason for my brevity with regards to a general dojo introduction. Neither am I a dojo expert nor would it be much related to MySQL which is at the core of my interest.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="/home/nixnutz/Downloads/dojo-release-1.9.0/dijit/themes/claro/claro.css">
<style type="text/css">
@import "/home/nixnutz/Downloads/dojo-release-1.9.0/dojox/grid/resources/Grid.css";
@import "/home/nixnutz/Downloads/dojo-release-1.9.0/dojox/grid/resources/claroGrid.css";
#gridDiv {
height: 10em;
}
</style>
</head>
<body class="claro">
<script src="/home/nixnutz/Downloads/dojo-release-1.9.0/dojo/dojo.js"
data-dojo-config="async: true"></script>
<script>
require(
["dojo/dom-construct", "dojo/parser", "dijit/Dialog", "dojox/grid/DataGrid", "dojo/data/ObjectStore", "dijit/form/Select", "dojo/store/Memory", "dojo/dom", "dojo/on",
"dojo/when", "dojo/request/script", "dojo/request/notify", "dojo/json",
"dojo/store/JsonpMySQLFields", "dojo/domReady!"],
function(domConstruct, parser, Dialog, DataGrid, ObjectStore, Select, Memory, dom, on, when, script, notify, JSON, mysqlp) {
/* Dojo newbees - consider this the main() function */
function debugMsg(action, msg) {
domConstruct.place(
"<div>[click " + clicks + "][" + action + "] " + msg + "</div>",
debugDiv,
"after"
);
}
var debugDiv = dom.byId("debugDiv");
var formDiv = dom.byId("formDiv");
var clicks = 0;
var addedIds = new Array();
var store = new mysqlp({
method: "http",
host: "127.0.0.1",
port: 8080,
interface: "index.php?sql=",
basicAuthUser: "root",
basicAuthPassword: "secret",
mysqlTable: "dojo_jsonp_fields",
mysqlFields: ["first_name", "last_name", "email"]
});
var ostore = new ObjectStore({ objectStore: store });
var grid = new DataGrid(
{
store: ostore,
query: {},
queryOptions: {},
structure: [
{ name: "First Name", field: "first_name", width: "25%" },
{ name: "Last Name", field: "last_name", width: "25%" },
{ name: "Mail", field: "email", width: "50%" }
]
}, "gridDiv");
grid.startup();
on(dom.byId('queryButton'), 'click', function (evt) {
/* Dump some records into debug log */
clicks++;
var records = store.query(
"",
{
start: 1,
count: 5,
sort: [{attribute:"last_name", descending: false}]
});
records.forEach(function (each) {
debugMsg("dump", JSON.stringify(each));
});
});
on(dom.byId('addButton'), 'click', function (evt) {
/* Add a record */
clicks++;
store.add(
{"first_name" : "Johannes (" + clicks + ")", "last_name" : "Schlueter"}
).then(
function (reply) {
addedIds.push(reply);
debugMsg("add", JSON.stringify(reply));
grid.render();
}
);
});
on(dom.byId('removeButton'), 'click', function (evt) {
/* Remove freshly added button */
clicks++;
if (addedIds.length) {
store.remove(addedIds.pop()).then(
function (reply) {
debugMsg("remove", "OK, " + JSON.stringify(reply));
grid.render();
}
);
} else {
debugMsg("remove", "All added records already removed");
}
});
on(dom.byId('updateButton'), 'click', function (evt) {
/* Add and update freshly added... */
clicks++;
store.put(
{"first_name" : "Andrey (" + clicks + ")"}
).then(
function (reply) {
var insert_id = reply;
debugMsg("update, step 1 (add)", JSON.stringify(reply));
store.get(insert_id).then(
function (reply) {
debugMsg("update, step 2 (fetch ... to demo fetch ;-))", JSON.stringify(reply));
store.put(
{"first_name" : "Andrey (" + clicks + ")", "last_name" : "Hristov"},
{"overwrite": true, "id": insert_id}
).then(
function (reply) {
debugMsg("update, step 3 (the actual update)", JSON.stringify(reply));
grid.render();
}
);
}
);
}
);
});
}
);
</script>
<h1>MySQL spreadsheet</h1>
<form>
<fieldset>
<legend>Grid/spreadsheet</legend>
<div id="gridDiv"></div>
</fieldset>
<fieldset>
<legend>Actions</legend>
<input type="button" id="queryButton" value="Dump some records into debug log"><br />
<input type="button" id="addButton" value="Add a record">
<input type="button" id="removeButton" value="Remove added record">
<input type="button" id="updateButton" value="Add, read and update a record">
</fieldset>
<fieldset>
<legend>Debug Log</legend>
<span style="overflow: auto; height: 5em">
<div id="debugDiv"></div>
</span>
</fieldset>
</form>
</body>
</html>
JsonpMySQL: JSON in BLOB
Finally a prototype of a dojo store for storing objects as JSON in a BLOB column of a MySQL table. Again, it is written for use with the PHP proxy script presented earlier.
define("dojo/store/JsonpMySQL", ["../number", "../string", "../request/script", "../when", "../_base/xhr", "../_base/lang", "../json", "../_base/declare", "./util/QueryResults" /*=====, "./api/Store" =====*/
], function(number, string, script, when, xhr, lang, JSON, declare, QueryResults /*=====, Store =====*/){
// No base class, but for purposes of documentation, the base class is dojo/store/api/Store
var base = null;
/*===== base = Store; =====*/
/*=====
var __HeaderOptions = {
// headers: Object?
// Additional headers to send along with the request.
},
__PutDirectives = declare(Store.PutDirectives, __HeaderOptions),
__QueryOptions = declare(Store.QueryOptions, __HeaderOptions);
=====*/
return declare("dojo.store.JsonpMySQL", base, {
// summary:
// This is a basic store for RESTful communicating with a server through JSON
// formatted data. It implements dojo/store/api/Store.
constructor: function(options){
// summary:
// This is a basic store for RESTful communicating with a server through JSON
// formatted data.
// options: dojo/store/JsonRest
// This provides any configuration information that will be mixed into the store
declare.safeMixin(this, options);
},
method: "http",
host: "127.0.0.1",
port: 8080,
interface: "sql",
basicAuthUser: "root",
basicAuthPassword: "secret",
mysqlTable: "dojo_jsonp",
mysqlBlob: "dojo_blob",
mysqlId: "dojo_id",
// target: String
// The target base URL to use for all requests to the server. This string will be
// prepended to the id to generate the URL (relative or absolute) for requests
// sent to the server
target: "",
// idProperty: String
// Indicates the property to use as the identity property. The values of this
// property should be unique.
idProperty: "id",
// sortParam: String
// The query parameter to used for holding sort information. If this is omitted, than
// the sort information is included in a functional query token to avoid colliding
// with the set of name/value pairs.
get: function(oid){
// summary:
// Retrieves an object by its identity. This will trigger a GET request to the server using
// the url `this.target + id`.
// id: Number
// The identity to use to lookup the object
// returns: Promise
// The object in the store that matches the given id.
var sql = "SELECT " + this.mysqlId + ", " + this.mysqlBlob + "FROM";
sql += this.mysqlTable + " WHERE " + this.mysqlId + "=" + number.parse(oid);
return when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp"}
).then(lang.hitch(this, this._extractFirstObject))
);
},
getIdentity: function(object){
// summary:
// Returns an object's identity
// object: Object
// The object to get the identity from
// returns: Number
return object[this.idProperty];
},
put: function(object, options){
// summary:
// Stores an object. This will trigger a PUT request to the server
// if the object has an id, otherwise it will trigger a POST request.
// object: Object
// The object to store.
// options: __PutDirectives?
// Additional metadata for storing the data. Includes an "id"
// property if a specific id is to be used.
// returns: dojo/_base/Deferred
/*
Store.PutDirectives = declare(null, {
// summary:
// Directives passed to put() and add() handlers for guiding the update and
// creation of stored objects.
// id: String|Number?
// Indicates the identity of the object if a new object is created
// before: Object?
// If the collection of objects in the store has a natural ordering,
// this indicates that the created or updated object should be placed before the
// object specified by the value of this property. A value of null indicates that the
// object should be last.
// parent: Object?,
// If the store is hierarchical (with single parenting) this property indicates the
// new parent of the created or updated object.
// overwrite: Boolean?
// If this is provided as a boolean it indicates that the object should or should not
// overwrite an existing object. A value of true indicates that a new object
// should not be created, the operation should update an existing object. A
// value of false indicates that an existing object should not be updated, a new
// object should be created (which is the same as an add() operation). When
// this property is not provided, either an update or creation is acceptable.
});
*/
options = options || {};
var sql = "";
var id = ("id" in options) ? options.id : this.getIdentity(object);
var hasId = typeof id != "undefined";
if (("overwrite" in options) && options["overwrite"]) {
if (!hasId) {
throw "You must provide the id of the object to update";
}
sql = "UPDATE " + this.mysqlTable + " SET " + this.mysqlBlob + "= '";
sql += this._escapeString(JSON.stringify(object)) + "'";
sql += " WHERE " + this.mysqlId + "=" + number.parse(id);
} else {
sql = "INSERT INTO " + this.mysqlTable + "(" + this.mysqlBlob;
if (hasId) {
sql += ", " + this.mysqlId;
}
sql += ") VALUES ('" + this._escapeString(JSON.stringify(object)) + "'";
if (hasId) {
sql += ", " + number.parse(id);
}
sql += ")";
}
return when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(
function (reply) {
if (reply && "last_insert_id" in reply)
return reply.last_insert_id;
return reply;
}
)
);
},
add: function(object, options){
// summary:
// Adds an object. This will trigger a PUT request to the server
// if the object has an id, otherwise it will trigger a POST request.
// object: Object
// The object to store.
// options: __PutDirectives?
// Additional metadata for storing the data. Includes an "id"
// property if a specific id is to be used.
options = options || {};
options.overwrite = false;
return this.put(object, options);
},
remove: function(id, options){
// summary:
// Deletes an object by its identity. This will trigger a DELETE request to the server.
// id: Number
// The identity to use to delete the object
// options: __HeaderOptions?
// HTTP headers.
options = options || {};
var sql = "DELETE FROM " + this.mysqlTable + " WHERE " + this.mysqlId + "=" + number.parse(id);
return when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(
function (reply) {
if (reply && "errno" in reply) {
return reply;
}
return;
}
)
);
},
query: function(query, options){
// summary:
// Queries the store for objects. This will trigger a GET request to the server, with the
// query added as a query string.
// query: Object
// The query to use for retrieving objects from the store.
// options: __QueryOptions?
// The optional arguments to apply to the resultset.
// returns: dojo/store/api/Store.QueryResults
// The results of the query, extended with iterative methods.
var sql = "SELECT " + this.mysqlId + ", " + this.mysqlBlob;
sql += " FROM " + this.mysqlTable;
var results = when(
script.get(
this._getAddress(sql),
{jsonp: "jsonp" }
).then(lang.hitch(this, this._extractAllObjects))
);
return QueryResults(results);
},
_getAddress : function(query) {
return this.method + "://" + this.basicAuthUser + ":" + this.basicAuthPassword + "@" +
this.host + ":" + this.port + "/" + this.interface + encodeURIComponent(query);
},
_extractObjects : function(result, limit) {
var data_only = new Array();
var result_idx, row_idx;
for (result_idx = 0; result_idx < result.length; result_idx++) {
for (row_idx = 0; row_idx < result[result_idx].data.length; row_idx++) {
if ((limit > 0) && (row_idx >= limit)) {
return data_only;
}
data_only.push(JSON.parse(result[result_idx].data[row_idx][1]));
}
}
return data_only;
},
_extractAllObjects : function (result) {
return this._extractObjects(result, -1);
},
_extractFirstObject: function (result) {
return this._extractObjects(result, 1);
},
_escapeString: function (sql_value) {
sql_value = sql_value.toString();
return sql_value.replace('/"/g', '\\"');
}
});
});
The name of the SQL table, the name of the BLOB column and the name of the ID column can be set through the stores constructor. Otherwise it is just a variation of the theme "SQL over HTTP" at the example of a specific JavaScript framework…
var store = new mysqlp({
method: "http",
host: "127.0.0.1",
port: 8080,
interface: "index.php?sql=",
basicAuthUser: "root",
basicAuthPassword: "secret",
mysqlTable: "name_of_the_table",
mysqlBlob: "blob_column",
mysqlId: "id_column"
});
Happy hacking!
