Page tree
Skip to end of metadata
Go to start of metadata

 

 

This guide explains how to use SQL Query Execution in Smartface.

What is SQLite?

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

Execute

Smartface improved the existing database feature of Data.execute method and changed the logic of the dataset; instead of bound with a table, it is bound with a query and it is highly customizable.

Data.execute method can be used for the following features:

  • Execute select query and get data
    • Returns tabular data
    • Returns single value
  • Execute non value returning query, such as delete, insert, update and as well as create, drop and other data structure manipulation; begin transaction and commit / rollback transaction.
  • Use parameters in queries
    • Named parameters
    • Anonymous parameters
    • Bind parameters

Parameters

There are two kinds of parameter declarations:

Named Parameters

Parameter names are defined with colon (:) prefix and followed by the name of the parameter. The named parameters can be referenced in a query multiple times with single declaration per parameter. The defined parameter should also have colon sign prefix in their names.

Anonymous Parameters

Parameters do not have names, instead of names they have orders. The anonymous parameter is referenced with a question mark (?) within a query.
The value of a parameter given with value property of the parameter. Parameters can be bound with an object, for example with a UI object’s property. When the parameter is bound with an object, no need to define value of the parameter. Giving value may overwrite the effect of binding. Binding can be done by assigning the binding property with assigning it with a string value which contains the full executable JavaScript path of the value.

Parameters can be defined with using Data.DataParameter object or anonymous JavaScript objects also can be used.

//Using with parameters
Data.execute("insert into table2 (value, timeStamp) values(?,?);", "A", new Date()); //Values are passed as it is
//Using with DataParameter
//DataParameter is required with named parameters
//Object that are not DataParameter will be tried to convert DataParameter
var param1 = new Data.DataParameter();
param1.name = "value";
param1.binding = "Pages.Page1.Editbox1.text";
Data.execute("update table2 set timeStamp =  : timeStamp where value =  : value; ",
	param1, {
	name : "timeStamp",
	value : new Date()
});
//Using with custom object as parameter
var customObject = {
	bar : "foo",
	get value() {
		return thisbar;
	}
};
Data.execute("delete from table3 where col1 = ?", customObject); //deletes all rows in table3 where col1 has value of "foo"

Select

We can get single or multiple data on select query.

Single Data

The executing query should return a value and this value should be maximum single column and single row. Otherwise it is considered as returning multiple value.

var count = Data.execute('select count(*) from tbl1');
//returned value is a number, the javascript type is a number
alert(count); // Will alert count of rows in tbl1
var firstValue = Data.execute('select c1 from tbl1 where rowid = 1');
// returned value is a text, the javascript type is a string
alert(firstValue); // Will alert first written value of c1 in tbl1
Multiple Data

The returned object while executing a select statement which returns at least more than one row or at least more than one column is a DataResult object, which has two array properties:

  • rows: It contains data of rows. Every row contains an array of data
  • columns: It is an array that contains string values of column names
var result = Data.execute("select timeStamp, value from table2 where timeStamp < ?;", new Date());
var networkResponseObject = [];
for (var i = 0; i < result.rows.length; i++) {
	networkResponseObject[i] = {};
	for (var j = 0; j < result.columns.length) {
		networkResponseObject[i][result.columns[j]] = result.rows[i][j];
	}
}

Insert

The SQLite INSERT INTO Statement is used to add new rows of data into a table in the database. Above the parameter, insert query can be used.

Data.execute("insert into table2 (value, timeStamp) values(?,?);", "A", new Date()); //Values are passed as it is

Delete

The SQLite DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted. Above the parameter, the delete query can be used.

Data.execute("delete from table3 where col1 = ?", customObject); //deletes all rows in table3 where col1 has value of "foo"

Update

The SQLite UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be updated. Above the parameter, the update query can be used.

Data.execute("update table2 set timeStamp =  : timeStamp where value =  : value; ",
	param1, {
	name : "timeStamp",
	value : new Date()
});

Create Table

The SQLite CREATE TABLE command is used to create a new table in the SQLite database. A CREATE TABLE command specifies the following attributes of the new table:

  • The name of the new table.
  • The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database.
  • The name of each column in the table.
  • The declared type of each column in the table.
  • A default value or expression for each column in the table.
  • A default collation sequence to use with each column.
  • Optionally, a PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported.
  • A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints.
Data.execute("DROP TABLE IF EXISTS testtable;");
Data.execute("Create table testtable (col1 int, col2 int)");

Add Index

The SQLite ADD INDEX command consists of the keywords “ADD INDEX” followed by the name of the new index, the keyword “ON”, the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. If the optional WHERE clause is included, then the index is a “partial index”.

Add Relation

Dataset is a central data source, which is responsible for providing data for other components, also maintains data and its state with the underlying SQLite database. In older versions of dataset, it was bound with a table using a select query. Through that query, dataset knows which table it was interacting with. By this knowledge, dataset could write the data to database when committed.

The new version has a query relation instead of a table relation. Every dataset must have a valid select query to work with, which does not limit the dataset working with more than multiple tables to commit. This is done with dataset (new) events.