This page shows examples for using SequelSphere with the YUI application framework.
YUI provides a number of integration points for which connectors could be provided, or
which appear to be suitable integration points.
First, you will need to reference the SequelSphere javascript file as in the example code below.
Please register for
your own SequelSphere token; the token in the example is only for evaluation purposes.
There is no cost to register, but it helps us to keep track of who is using our product.
Before the Sequel Sphere API can be used
there must be a database set up to use. Based on the persistence options
you use there are two events/actions that must be done before you can
use your SequelSphere database:
the
db.onready
event is fired when the database
catalog is loaded. If using SequelSphere for the first time, this
is fired when the database catalog is loaded and ready for database
tables to be created. Subsequently, and if using a persistence option
that has permanence across page loads, this includes the loading of the
existing database into memory.
a database has been created and loaded. On first use, an empty
database with no tables is created. It is necessary, then, to create
tables and populate them.
This example is not intended
to explore all of the options for populating your local SequelSphere database.
For the purposes of this
example, the database uses an in-memory storage scheme, so is created
as part of the example, and released when the page is released from memory.
If you refresh the page, the database will be re-initialized. SequelSphere
has many persistence options, and there are many ways you might consider loading
your database. Please see the SequelSphere documentation for more information.
The example runs automatically. The code shows a simple approach that mimics
calling an Ajax service to retrieve the data. See the
SequelSphere Basic Examples
for the structure of the data used to
initially populate the data.
Example database initialization Javascript code:
YUI().use("node", function (Y) {
// Wait for SequelSphere to initialize itself
db.onReady(function() {
// Simulate an asynchronous Ajax call
getData(function(data) {
try {
db.catalog.createTables(data);
alert("Database loaded!");
}
catch(err) {
showError(err, "Database load failed!");
}
});
});
});
Example #2: Performing a Simple Query
The most straightforward use of SequelSphere with YUI is to
perform a query and set the results in an HTML field using
the YUI API. This example demonstrates the simplest of queries
which returns a single scalar value.
Click Query Value to perform the following query:
"SELECT AVG(AGE)as MAXAGE FROM EMPL"
Average Age:
Example Javascript code:
YUI().use("event", function (Y) {
// Handle Example button click
Y.on("click",
function(e) {
// Perform qery and set HTML field
var sql = "SELECT AVG(age) FROM EMPL";
try {
var avg = db.queryValue(sql);
Y.one("#example2-results").set("value", avg);
}
catch(err) {
showError(err, "Unable to execute queryValue '" + sql + "'.");
}
},
"#example2-btnex"
);
});
Note the following return formats for the different query operations (see SequelSphere API document for completed information):
Operations
Return Format
Description
query
{ columnNames: [], data: [ [row] ] }
data is an array of row arrays
queryCursor
{ next:function(), getValue:function(col) }
A cursor object for browsing the result set
queryObjects
{ columnNames: [], data: [ {row} ] }
Data is an array of row objects
queryRow
[row]
A row array
queryRowObject
{row}
A row object, with each column a separate property
queryValue
obj
A scalar object, like string or number
Example #3: Using the YUI DataTable and DataSources
The most common use of a database query is to return multiple rows
for display in a table. This example demonstrates different methods
of accomplishing this using the YUI
DataTable
widget:
issue a query directly and set the data on the
DataTable
.
use a
DataSource.Function
to perform the
query.
use a SequelSphere
DataSource.SQL
to
perform the query.
Click on the each Query button to perform a query and populate a table with the results.
Click multiple times to demonstrate refreshing the table with the results of a different query
without recreating the table.
Example Javascript code for "Query with YUI Data Table":
Y.on("click",
function(e) {
// Select one of three queryies to execute
example3_reqidx = ++example3_reqidx % 3;
var sql = example3_requests[example3_reqidx];
// Perform the query
try {
var results = db.queryObjects(sql);
if (example3_dt1 == null) {
// Create the DataTable widget
example3_dt1 = new Y.DataTable({
data: results.data,
caption: "Example #3 DataTable",
summary: "Example DataTable showing simple use with SequelSphere"
});
example3_dt1.render("#example3-results-table-1");
}
else {
// Refresh the DataTable widget
example3_dt1.set("data", results.data);
}
}
catch(err) {
showError(err, "Unable to execute query '" + sql + "'.");
}
},
"#example3-btnex-1"
);
Example Javascript code for "Query with YUI DataTable/DataSource.Function":
Y.on("click",
function(e) {
// Create the DataSource
if (example3_ds2 == null) {
example3_ds2 = new Y.DataSource.Function({
source: function(request) {
// Execute the query
try {
var results = db.queryObjects(request);
return results.data;
}
catch(err) {
return { error:err };
}
}
});
// Specify the schema
example3_ds2.plug(Y.Plugin.DataSourceJSONSchema, {
schema: {
resultFields: ["EMPL_ID","NAME","AGE","DEPT_ID","DEPT_NAME"]
}
});
}
// Create the DataTable widget
// specifying the columns to include in the table
if (example3_dt2 == null) {
example3_dt2 = new Y.DataTable({
columns: ["EMPL_ID","NAME","AGE","DEPT_ID","DEPT_NAME"],
caption: "Example #3 DataTable with DataSource.Function",
summary: "Example DataTable using DataSource.Function"
});
// Plug the DataSource into the DataTable
example3_dt2.plug(Y.Plugin.DataTableDataSource, {
datasource: example3_ds2
});
// Render the empty table on the page
example3_dt2.render('#example3-results-table-2');
}
// Select a query to execute
example3_reqidx = ++example3_reqidx % 3;
var sql = example3_requests[example3_reqidx];
// Execute the query by loading the DataSource
example3_dt2.datasource.load({ request: sql });
},
"#example3-btnex-2"
);
Example Javascript code for "Query with YUI DataTable/DataSource.SQL":
Y.on("click",
function(e) {
// Create the DataSource
if (example3_ds3 == null) {
example3_ds3 = new Y.DataSource.SQL({});
// Specify the schema
example3_ds3.plug(Y.Plugin.DataSourceJSONSchema, {
schema: {
resultFields: ["EMPL_ID","NAME","AGE","DEPT_ID","DEPT_NAME"]
}
});
}
// Create the DataTable widget
// specifying the columns to include in the table
if (example3_dt3 == null) {
example3_dt3 = new Y.DataTable({
columns: ["EMPL_ID","NAME","AGE","DEPT_ID","DEPT_NAME"],
caption: "Example #3 DataTable with DataSource.SQL",
summary: "Example DataTable using DataSource.SQL"
});
// Plug the DataSource into the DataTable
example3_dt3.plug(Y.Plugin.DataTableDataSource, {
datasource: example3_ds3
});
// Render the empty table on the page
example3_dt3.render('#example3-results-table-3');
}
// Select a query to execute
example3_reqidx = ++example3_reqidx % 3;
var sql = example3_requests[example3_reqidx];
// Execute the query by loading the DataSource
example3_dt3.datasource.load({ request: sql });
},
"#example3-btnex-3"
);
Example #4: Performing a Single-row Query and Row Formats
This example shows the ability to return the query results as a single row
array or row object using the following SequelSphere operations:
queryRow // returns a row array
queryRowObject // return a row object
Generally, YUI works better with the data in an object with named properties
instead of an array, so it is better to use
queryRowObject
.
The UI contains simple HTML controls. The YUI API is used to populate the controls
from the data.
A different approach would be to yse the YUI
Model
class for holding a row of data. If you
prefer using this approach, see
Example #7
.
To execute this example, first type a number from 0-6 in the EMPL_ID field, then
click on the Query Row Array or Query Row Object button to perform a query that returns the specified EMPL row.
Search EMPL_ID
Search EMPL_ID
EMPL_ID
NAME
AGE
DEPT_ID
Example Javascript code:
Y.on("click",
function(e) {
var emplidstr = Y.one("#example4-fld-search-rowobj").get("value");
var emplid = Number(emplidstr);
var sql = "SELECT *FROM EMPL WHERE EMPL_ID=" + emplid;
try {
var row = db.queryRowObject(sql);
Y.one("#example4-fld-emplid").set("value", formatRowValue(row, "EMPL_ID"));
Y.one("#example4-fld-name").set("value", formatRowValue(row, "NAME"));
Y.one("#example4-fld-age").set("value", formatRowValue(row, "AGE"));
Y.one("#example4-fld-deptid").set("value", formatRowValue(row, "DEPT_ID"));
}
catch(err) {
showError("Unable to execute queryRowObject '" + sql + "'.");
}
},
"#example4-btnrowobj"
);
Example #5: Using the YUI Charts Widget
This example shows the use of the YUI
Charts
module for charting the results of a query. The
Charts
widget also works with
DataSource
classes in a manner
similar to the
DataTable
widget. Only the simplest use is
included in the example. Consult
Example #3
for more information on how to use data sources.
Click on the Chart Results button to chart the results.
Example Javascript code:
Y.on("click",
function(e) {
var sql = "SELECT NAME, AGE FROM EMPL";
try {
var results = db.queryObjects(sql);
var chartDiv = Y.one("#example5-chart");
chartDiv.set("offsetWidth", 640);
chartDiv.set("offsetHeight", 480);
var chart = new Y.Chart({
dataProvider: results.data,
categoryKey: "NAME",
seriesKeys: ["AGE"],
type:"column",
render: "#example5-chart"
});
}
catch(err) {
showError(err, "Unable to execute query '" + sql + "'.");
}
},
"#example5-btnex"
);
Example #6: Inserting, Updating and Deleting a Row
Data is inserted, updated and deleted in the SequelSphere database
using a simple API rather than SQL INSERT, UPDATE and DELETE statements.
Each API method takes as arguments the name of the table and the row
to be modified.
The row must be a complete row containing all columns if doing an
insert or update. On delete, the row may contain only the primary key
fields, if the table has a primary key defined, or the full row if
no primary key is defined.
Click on each button to insert, update or save a row.
After the operation, the row will be queried from the database
and displayed on the right. Click multiple times on insertRow
and saveRow to see the difference in behavior.
EMPL_ID
NAME
AGE
DEPT_ID
Insert Row Example Javacript code:
Y.on("click",
function(e) {
var rowins = { EMPL_ID:10, NAME:("INS-"+new Date().getTime() % 1000), AGE:33, DEPT_ID:1 };
var inserted = 0;
try {
inserted = db.catalog.getTable("EMPL").insertRow(rowins);
// Following re-reads and displays inserted row for verification
var sql = "SELECT *FROM EMPL WHERE EMPL_ID=" + rowins.EMPL_ID;
var row = db.queryRowObject(sql);
Y.one("#example6-fld-emplid").set("value", formatRowValue(row, "EMPL_ID"));
Y.one("#example6-fld-name").set("value", formatRowValue(row, "NAME"));
Y.one("#example6-fld-age").set("value", formatRowValue(row, "AGE"));
Y.one("#example6-fld-deptid").set("value", formatRowValue(row, "DEPT_ID"));
}
catch(err) {
showError(err,
(inserted == 0 ? ("Unable to insert row.") : ("Unable to execute query '" + sql + "'.")));
}
},
"#example6-btnins"
);
Update Row Example Javacript code:
Y.on("click",
function(e) {
var updated = 0;
var sql = "SELECT *FROM EMPL WHERE EMPL_ID=2";
try {
// First query the row
var row = db.queryRowObject(sql);
// Modify and save row
row.NAME = row.NAME.substr(0, 3) + "-" + (new Date().getTime() % 1000);
updated = db.catalog.getTable("EMPL").updateRow(row);
// Following re-reads and displays row for verification
row = db.queryRowObject(sql);
Y.one("#example6-fld-emplid").set("value", formatRowValue(row, "EMPL_ID"));
Y.one("#example6-fld-name").set("value", formatRowValue(row, "NAME"));
Y.one("#example6-fld-age").set("value", formatRowValue(row, "AGE"));
Y.one("#example6-fld-deptid").set("value", formatRowValue(row, "DEPT_ID"));
}
catch(err) {
showError(err, (updated == 0) ? "Unable to update row." : ("Unable to execute query '" + sql + "'."));
}
},
"#example6-btnupd"
);
Delete Row Example Javacript code:
Y.on("click",
function(e) {
var sql = "SELECT * FROM EMPL ORDER BY NAME";
try {
var deleted = 0;
// Following will return only the first matching row
var rowdel = db.queryRowObject(sql);
if (rowdel) {
Y.one("#example6-fld-emplid").set("value", formatRowValue(rowdel, "EMPL_ID"));
Y.one("#example6-fld-name").set("value", formatRowValue(rowdel, "NAME"));
Y.one("#example6-fld-age").set("value", formatRowValue(rowdel, "AGE"));
Y.one("#example6-fld-deptid").set("value", formatRowValue(rowdel, "DEPT_ID"));
}
if (rowdel && confirm("Delete employee " + rowdel.NAME)) {
deleted = db.catalog.getTable("EMPL").deleteRow(rowdel);
sql = "SELECT * FROM EMPL WHERE EMPL_ID=" + rowdel.EMPL_ID;
var row = db.queryRowObject(sql);
Y.one("#example6-fld-emplid").set("value", formatRowValue(row, "EMPL_ID"));
Y.one("#example6-fld-name").set("value", formatRowValue(row, "NAME"));
Y.one("#example6-fld-age").set("value", formatRowValue(row, "AGE"));
Y.one("#example6-fld-deptid").set("value", formatRowValue(row, "DEPT_ID"));
}
}
catch(err) {
showError(err, "Unable to execute query '" + sql + "'.");
}
},
"#example6-btndel"
);
Example #7: Using a YUI Model
YUI provides the
Model
and
ModelList
classes
for holding data and syncing the data with an underlying
data store. Many widgets understand how to interact with a
Model
or list of
Models
. The typical manner of using a Model is
to extend it with a class that defines the specific attributes, and to override
the
sync
method to synchronize it to a specific data store.
The application programmer could directly extend the
Model
and
ModelList
classes and override the
sync
method to access SequelSphere for maximum flexibiliy, but SequelSphere
provides a the
ssdb-sqlmodel
module containing the
SQLModel
and
SQLModelList
classes for a
much simpler interface. Using these classes, the application programmer does
not need to directly access SequelSphere or override the
sync
method.
In the simplest use, extend the
SQLModel
class to create a
model customized to the data table it will access with the following:
Attributes to hold the data. These must match the column names on the table
for the model attributes to be loaded automatically.
Information for accessing the database, including the query to execute
to load the model, the table for updates, and the key fields for linking the
data attributes to the YUI
Model
id.
Additional methods for convenience, or for overriding functionality
To execute this example, first type a number from 0-6 in the EMPL_ID field, then
click on the Query Model button to perform a query that returns the specified EMPL row
as Model. Then, modify the NAME, AGE or DEPT_ID and click Update Model. To
verify the save, click on Query Model again to re-read from the database.
Search EMPL_ID
EMPL_ID
NAME
AGE
DEPT_ID
To extend the
SQLModel
class, the following code is all that is necessary:
Y.EmplModel = Y.Base.create('emplModel', Y.SQLModel, [], {
dbReadSQL: "SELECT * FROM EMPL WHERE EMPL_ID={EMPL_ID}", // load query
dbTableName: "EMPL", // name required for updates
dbKeyFields: ["EMPL_ID"] // key fields needed for Model id
}, {
// Define the data attributes with default values
// The names must match the column names on the table
// to perform updates
ATTRS: {
EMPL_ID: -1,
NAME: "",
AGE: 0,
DEPT_ID: -1
}
});
To use the model class, instantiate it and set the key fields.
Then use the standard model methods, like
load
and
save
,
and the events fired by those events to interact with the object. The following
code shows how to instantiate and perform the main operations.
// Create model obect
var example7_model = new Y.EmplModel({EMPL_ID:emplid});
-or-
var example7_model = new Y.EmplModel({});
example7_model.set("EMPL_ID", emplid);
// Load from the database
example7_model.load();
// Insert or update
example7_model.save();
// Delete
example7_model.destroy({remove:true});
Finally,
Model
assumes an asynchronous interface, so events should be used to
know when an operation is completed. For instance, the
load
event is fired
when the model is loaded, and the
save
event is fired when it is saved.
example7_model.after("load", function(e) {
// e.parsed contains the row data
// this contains the loaded model
});
The following oode contains the event handlers for the example buttons.
// Query Model button
Y.on("click",
function(e) {
var emplidstr = Y.one("#example7-fld-search").get("value");
var emplid = Number(emplidstr);
example7_model.set("EMPL_ID", emplid);
example7_model.load();
},
"#example7-btn-query"
);
// Update Model button
Y.on("click",
function(e) {
if (example7_model != null) {
example7_model.save();
}
},
"#example7-btn-update"
);
Use event listeners to link the model to the UI controls.
example7_model.after("load", function(e) {
// The model has been loaded. Populate UI controls
for(var p in e.parsed) {
Y.one("#example7-fld-"+p).set("value", formatValue(e.parsed[p]));
}
});
example7_model.after("save", function(e) {
// The model has been saved. Reload?
if (confirm("Row saved!")) {
example7_model.load();
}
});
Y.all(".example7-fld").each(function (fld) {
fld.on("valuechange", function(e) {
// Update model when the value of any UI controlS is changed.
var fld_id = this.get("id");
var fld_nm = fld_id.substr(13); // strip off example7-fld- prefix
var fld_val = (this.hasClass("numeric")) ? Number(this.get("value")) : this.get("value");
example7_model.set(fld_nm, fld_val);
});
});
Example #8: Using a YUI ModelList
Once you have extended
SQLModel
, you can also extend
SQLModelList
to hold a multi-row query of them. No attributes are defined, but you will defined the following:
the model class that list items should use
the query to load the list
the query used to refresh an item in the list. This will be set on the model instances
when the model list is loaded
Finally, when creating the
DataTable
, specify the instantiated
SQLModelList
as the data.
Example Javascript code for "Query with YUI Data Table/ModelList":
var example8_dt = null;
var example8_modellist = null;
Y.on("click",
function(e) {
if (example8_modellist == null) {
example8_modellist = new Y.EmplList();
if (example8_dt == null) {
// Create the DataTable widget
example8_dt = new Y.DataTable({
data: example8_modellist,
caption: "Example #7 DataTable with ModelList",
summary: "Example DataTable showing SequelSphere ModelList"
});
example8_dt.render("#example8-results-modellist");
}
example8_modellist.load();
},
"#example8-btn-modellist"
);
Extend Y.ModelList Javascript code:
Y.EmplList = Y.Base.create('emplList', Y.SQLModelList, [], {
model: Y.EmplModel,
dbTableName: "EMPL",
dbReadSQL: "SELECT * FROM EMPL", // list query
dbItemReadSQL: "SELECT * FROM EMPL WHERE EMPL_ID={EMPL_ID}", // item query
dbKeyFields: ["EMPL_ID"]
});