SequelSphere / YUI Connector
Overview Example Download

SequelSphere / YUI Connector Example

Overview of YUI - SequelSphere Connection Points

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.

  • Example #1: Loading a SequelSphere Database
  • Example #2: Performing a Simple Query
  • Example #3: Using the YUI DataTable and DataSources
  • Example #4: Performing a Single Row Query and Row Formats
  • Example #5: Using the YUI Charts Widget
  • Example #6: Inserting, Updating and Deleting a Row
  • Example #7: Using a YUI Model
  • Example #8: Using a YUI ModelList

    • 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.

    Example HTML: 
    <script type="text/javascript" src="http://sequelsphere.com/db/edge/your_token/SequelSphereDB.js"></script>

    Example #1: Loading a SequelSphere Database

    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"]
	});