Telerik blogs

The Q3 2013 release of Telerik RadControls for Windows 8 brought a ton of new controls and features, making for a great development toolkit for your Windows 8 HTML and XAML applications. Along with it, the recently introduced Telerik Data Storage for HTML and XAML SDKs provide effortless local storage, powerful querying capabilities and supreme performance. We believe the Data Storage should be your preferred local data provider for Windows 8 development. In this blog post, we’ll have a look at this release’s major improvement in the Data Storage Javascript SDK – schema support.

Background

On a high level, Data Storage is a set of APIs for storing objects locally in your application. Built on top of the battle-tested SQLite, it provides automatic object graph serialization into regular SQL relational tables. You throw your Javascript objects in and we’ll take care of storing them. Combine that with the powerful querying APIs on top of SQL and you have a recipe for a great local storage solution.

However, the inherently dynamic, type-less nature of JavaScript objects means you can insert an object representing a bike into a collection intended to store pizza. This can lead to unpredictable result sets. Additionally, lack of schema or type metadata means the underlying engine cannot optimize storage or improve query performance.

Enter Database Schemas. In Q3 2013 we introduced support for schema definitions that addresses the above issues. Your existing applications (without schema definitions) will continue to work after upgrading to the current version. Moving forward, you can take advantage of schema definition in your applications. While defining a schema is not mandatory, it provides a set of clear benefits that developers will find vital for the structure, security and performance of their applications. The schema enforces data constraint, validates input data during runtime and provides indices for improved query performance.

Data Constraint

Prior to having a database schema, the API accepted any kind of object into any kind of object store. The assumption was that the developer knows what she’s doing and takes the entire responsibility for keeping her objects storage clean and uncluttered. However, this behavior limited applicability and put the burden of data validation on the shoulders of the developer. Now with schema definition, your object stores have a clear set of fields (or columns) with a specific type. This means the store enforces a specific structure of its data, preventing arbitrary objects from entering if they do not conform. Additionally, columns can be explicitly marked as nullable to allow or prevent null values.

Without a schema defined, the storage engine enforced a mandatory primary key field named id that had to contain unique values. If it was omitted, certain queries would return unexpected results due to the lack of a unique value to identify objects (or table rows). The developer had to take care of keeping a pool of unique IDs and provide one every time an object was inserted.

With a schema, the developer can now specify an arbitrary field as primary key. Furthermore, she can mark the field as auto-incremented and never worry about explicitly providing a unique value to identify an object.

Data Validation

Arguably the greatest benefit of schema definition is the runtime validation that takes place when objects are stored. The schema defines a structure that inserted objects need to follow. The runtime engine then runs a series of checks every time a new object is inserted. If the object does not conform to the structure defined by the schema, an error is thrown and the insertion is rejected. For example, if a column is marked as non-nullable, attempting to insert a null in that corresponding object field, or omitting that object field at all will result in an error and rejection. Similarly, numeric columns accept only numeric data. Date columns accept values that can successfully be converted into JavaScript Date objects. Boolean fields accept any kind of data and Boolean coercion is done behind the scenes to ensure the Boolean field constraint. A consistent object store structure is maintained and objects are retrieved in predictable formats on queries.

Another important benefit of using schemas for data validation is when performing data comparisons in queries. Having data in a strictly specified format means SQL WHERE conditions produce a predictable result. Data values are compared according to their specified type eliminating unpredictable results, such as comparing numbers to strings or objects. This means lucidity in query execution and expected results.

Finally, having a database schema means the engine has more metadata for storage optimization. Before schema support, the engine had to keep the entire JSON-serialized object graph inside a separate column in the SQL table. This was required, so that the object graph could be retrieved in the same way it was saved. However, this incurred an additional storage and serialization overhead. Now, with a schema, the engine has all the required metadata to retrieve the object field by field, meaning the excessive object graph serialization can be avoided by setting the serializeObject property to false.

Index Definition and Query Performance

Schema definition opens the door for another very important data storage improvement – query optimization. As part of their schema, developers can define custom SQL Indices that improve performance-critical queries. We’ve already presented the performance benefits of working with indices in Telerik Data Storage for Windows 8. Although the blog post gives an overview of the XAML API for index support, the general SQL-level optimizations work in the same way for JavaScript. Carefully constructed indices can lead up to 16 times faster queries, our tests show. Regardless of whether you care about data constraint and validation, index support alone should be a reason enough to consider using a schema in your Data Storage project.

Defining a Schema

A schema is defined when the database is first opened:

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" }
        ],
        indices: [{
            name: "CategoryIndex",
            columns: [{ name: "categoryId" }]
        }, {
            name: "PriceAddedIndex",
            columns: [
                { name: "unitPrice" },
                { name: "dateAdded", order: "desc" }
            ]
        }]
    }]
});

Once defined, the schema object can be retrieved using the
db.schema property. Successive calls to Telerik.Data.Database.open() may specify the same schema object, or may omit it completely. Once defined during first database initialization, the schema object is read-only. Any attempt to modify the schema properties after initialization will result in a runtime error. This enforces all calls to a single database instance to adhere to the same schema constraint. Through a set of options, the schema object defines the tables, columns and indices in a single database instance. For the complete list of schema options and their application, refer to the Data Storage Schema documentation article.

 

Once in place, the database schema validates any input data during an INSERT, UPDATE or DELETE action. Validation takes place in the Telerik.Data.Database.sync() method, in which all pending changes are checked against the schema constraints before being committed to the underlying database engine. If a constraint violation is detected, the sync action is aborted and no changes are committed. This behavior has an important implication – all insert, update and delete commands sent in a single sync() will abort even if there’s a schema validation error for only a single data record, leaving the database unchanged. This follows the transacted nature of sync() calls, where changes are committed as a single transaction.

Summary

In this blog post, we dived into the newly introduced schema support in Telerik Data Storage for Windows 8 JavaScript. Defining a database schema ensures consistent database structure, validates runtime data changes and provides a means for query optimizations through SQL indices. Schema support greatly improves the Windows 8 local data storage story. It takes away the hassle of storing and retrieving data and lets you focus on building great applications for Windows 8. If you haven’t already done so, now is the time to give it a go. Data Storage comes bundled with RadControls for Windows 8. Run the demo app from the installation, have a look at the docs, check out the Github samples, and share your opinion in the comments section.


Comments

Comments are disabled in preview mode.