Telerik UI for Windows 8 HTML

Telerik Data Storage supports database schema definition through a set of initialization options. These options specify the tables, columns and indices in your database. Although a schema is not mandatory for the operation of the database, defining a schema has a set of clearly defined benefits. The database schema enables:

  • Constraint definition. A database schema defines the tables, columns and their types, as well as nullable, identity and auto-increment settings for columns. Thus, a predictable, well-defined database structure is created.

  • Runtime validation of data before INSERT, UPDATE and DELETE operations. When a schema is defined, every data object goes through a series of checks that enforce the constraints defined in the schema. A database schema does not allow insertion of invalid data, such as null values for non-nullable columns or non-numeric data for numeric columns.

  • Custom identity columns. A database schema can define an arbitrary column as identity. Without a schema, only the field named id in data objects is considered to be the identity.

  • Index definition for better query performance. A database schema allows the definition of SQL indices. Fine-tuned indices greatly improve performance of critical SELECT queries.

Schema Definition

To define a database schema, provide an object as the third argument to the Telerik.Data.Database.open() method when the database is first opened (see Code Listing 1 below).

Code Listing 1: Sample Schema Definition Copy imageCopy
var db = Telerik.Data.Database.open("ProductsDB", "local", {
tables: [{
name: "Products",
columns: [
{ name: "productId", type: "number", identity: true, autoIncrement: true },
{ name: "productName", type: "string", nullable: false },
{ name: "categoryId", type: "number" },
{ name: "unitPrice", type: "number" },
{ name: "dateAdded", type: "date" },
{ name: "discontinued", type: "boolean" }
]
}]
});
Caution

A schema can be defined only if the database does not previously exist. Trying to define a schema on an existing database will throw an error.

Once defined, the schema object is saved in the database and accessible through the db.schema field. The schema may define multiple tables and columns. After the first time a database is opened with a schema, successive Telerik.Data.Database.open() calls can omit the schema argument, as shown in Code Listing 2.

Code Listing 2: Open DB with Already Defined Schema Copy imageCopy
db.close();    //close the database
db = Telerik.Data.Database.open("ProductsDB", "local");
//db.schema is already defined
Caution

The schema is read-only after being defined. Any modifications to the db.schema object will result in invalid schema state and prevent any modification to the underlying database. At this point, the database must be closed and reopened for re-initializing the schema.

Table Definition

A table schema is defined as an object literal in the tables array. Multiple table definitions can be defined in a database schema. A table definition has the following fields:

  • name: A string value that specifies the table name.

  • columns: An array that contains one or more column definitions.

  • indices: An array that optionally contains one or more index definitions.

When defining a table schema, the following restrictions are enforced:

  • A name must be defined.

  • At least one column definition must be defined.

  • Exactly one identity column must be defined.

  • No two columns can have the same name.

  • No two indices can have the same name.

  • All columns specified in an index must also be defined in the table columns.

Column Definition

A column definition object specifies an SQL column in a given table. A column definition has the following fields:

  • name: A string representing the column name.

  • type: A string specifying the column type, one of "string", "number", "boolean", "date" or "object".

  • identity: A Boolean value that when set to true, marks the column as PRIMARY KEY.

  • nullable: A Boolean value that specifies whether the column can contain null values (defaults to true).

  • autoIncrement: A Boolean value that specifies whether an identity column is auto-incremented.

    Important

    When you insert data to a table with an auto-increment column, make sure that you either pass a null or no value at all to the auto-incremented field. If you pass a number, the Data Storage will try to insert it, which will result in an error if such value already exists in the column.

    When you read data from a table with an auto-increment column, if you need this column to be included in your query results, set the serializeObject property of the Database object to false. This is needed because when the property is set to true (default value), the data that you insert is saved inside a serialized JSON object in the data base and when querying the data base only this data is returned (without any auto-generated values).

When defining a column, the following restrictions are enforced:

  • A name must be defined.

  • A type must be defined.

  • If a column is marked autoIncrement it must be marked identity.

  • If a column is marked autoIncrement it must be have type: "number".

Index Definition

A table may specify one or more indices in an indices field. Indices have the following fields:

  • name: The string name of the index.

  • unique: A Boolean value that specifies whether the index may contain only unique values.

  • columns: One or more column entries in object literal format: { name: "string", order: "asc"|"desc" }.

When defining an index, the following restrictions apply:

  • A name must be defined.

  • At least one column entry must be defined with a non-empty name.

  • No two column entries can have the same name.

Indices are useful for performance optimization in the following if:

  • You work with large table(s) of data.

  • You are going to perform mostly Read operations.

Do not use indices in scenarios where:

  • You work with small data sets.

  • You are going to perform frequent and numerous Create, Update and Delete operations on the table.

  • The column that you want to index contains many null values.

For further information about indexes in SQLite, check out the links in the See Also section below.

INSERT, UPDATE and DELETE with Schema

When a database schema is defined, all data mutation operations first go through a validation step, where the data is checked against the schema. Any errors produced during this step abort the operation and prevent database modification. This ensures the all-time validity of the stored data. In Code Listing 3 below, you can see how validation is processed during code execution.

Code Listing 3: Schema Validation Copy imageCopy
//valid product
var product = {
//productId is auto-incremented, so do not specify
productName: "Chai",
categoryId: 3,
unitPrice: 2.63,
dateAdded: new Date(),
discontinued: false
};
db.use("Products").insert(product).sync();    //validation against schema occurs at this point before data is committed

//another valid product
var product2 = {
productId: 2,    //specifying a value for an auto-incremented field is valid if the index value doesn’t already exist
productName: "Chang",
categoryId: "2",    //strings that can be parsed to numbers are valid
discontinued: "truthy value is OK"

//all remaining fields are nullable by default,
//so omitting them is valid
}

db.use("Products").insert(product2).sync();    //validation will pass here

//invalid product
var invalidProduct = {
productId: "my product",    //invalid numeric ID
//productName must be defined
dateAdded: "invalid date value"
}

//this statement will throw and error and prevent database modification
db.use("Products").insert(invalidProduct).sync();

Error Handling During Schema Validation

When a schema validation error occurs during a data operation, an error is thrown before the data is passed to the underlying storage layer. The error message describes in detail the reason the data has been invalidated. Code Listing 4 shows how to handle schema validation errors.

Code Listing 4: Schema Validation Error Handling Copy imageCopy
//invalid product
var invalidProduct = {
productId: "my product",    //invalid numeric ID
//productName must be defined
dateAdded: "invalid date value"
}

try {
//this statement will throw and error and prevent database modification
db.use("Products").insert(invalidProduct).sync();
}
catch (e) {
//e.message provides error info
}

See Also