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.
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
There are two kinds of parameter declarations:
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.
Parameters do not have names, instead of names they have orders. The anonymous parameter is referenced with a question mark (?) within a query.
We can get single or multiple data on select query.
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.
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
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.
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.
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.
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.
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”.
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.