IndexedDB: Keys: efficiently retrieving data from a database

In order to retrieve data efficiently from an IndexedDB database, each record is organized by its key. The only condition for the key is that it is a valid key. A valid key can be one of the following types:

• Array (Is only valid when every item inside the array is a valid key and the array doesn’t contains it self. Also all non-numeric properties are ignored and will not affect whether the array is a valid key)
• DOMString
• Date (The Primitivevalue (internal property) of the date object can’t be NaN)
• Float (The key can’t be NaN)

When comparing keys, the order in the list above applies. Array is greater than all DOMStrings, DOMString is greater than …

Comparing arrays is done by comparing the values inside the array (as long that both arrays got values on the position). If the values on the same position differ, then the outcome of that comparison will determine which array is greater. If the values on each position are the same, the length of the array will determine the which one is greater or equal if the arrays have the same length.

Complex Keys

The IndexedDB API also support complex keys. This are keys who refer to properties nested inside an object. This way you are able to filter on data that is available are properties of a nested object. In the example below we have an person object. Besides a name and firstname property it contains an address property. This address property is also an object containing properties like city, street, … If we want to filter data on its city we can do this by defining a key like this “address.city”.

   1: var person = {

   2:     name: "name",

   3:     firstname: "firstname",

   4:     address: {

   5:         street: "street",

   6:         city: "city"    

   7:     }

   8: }

Since Linq2IndexedDB 1.0.11 this is also supported for all filters, even for filters that don’t take advantage of the IndexedDB API. For this reason it is advised to use the linq2IndexedDB.prototype.utilities.getPropertyValue method to determine the value by it’s property name. Two arguments need to be provided:

• first argument is the object containing the data
  
• second argument is the propertyName string. For example “address.city”

The return value of the method is the value at this location if it exists, otherwise undefined will be returned.

Note that this nesting only works for objects. If the property contains an array of objects, you won’t be able to use this way to filter your data.

Object store keys

Inside an object store, keys even have a more special reason of existence. In here keys are used to uniquely identify an object. This means you can never add an object twice with the same key, but this enables you to update and delete objects when you know it’s key. For an object store, there are 2 ways you can provide a key:

• Inline key: the key is present inside the object (A KeyPath must be defined on the object store)
  
• External key: the key is provided separately (in most cases the value of the key/value will be a primary type like a string or float)

In both cases you can choose for the option to let the key be generated by the IndexedDB API. You can do this by setting the object store autoIncrement attribute on true when creating the object store. In this case the key will have a numeric value.

Index keys

The keys in for an index are always defined by a KeyPath. These keys can be complex keys as defined in the section “Complex keys” above. Every object in the object store keeping the index that has a value for the key path a record will be added. The key will be the value of the property defined by the key path. For example we have a keyPath “firstname”. In the example below, the key in the index record would be “Kristof” and the value the person object.

   1: var person = {

   2:     name: "Degrave",

   3:     firstname: "Kristof"

   4: }

In the index you can also have 2 special cases. The first one is the unique attribute. This will make sure, that a value for this keyPath can only be defined once in a value of the object store. This constraint will be checked when you add data to an object store. If there is an object present with the same value for that property, an ConstraintError will be thrown.

A second attribute is the multiEntry. This allows to have multiple values for only one key. In stead of adding a new record for every object with the same value, the key is only added once and all the objects that have same value are added to a collection.

Conclusion

Now you should have insight how the keys work inside the IndexedDB API. With the complex keys you will be able to filter on data with nested object, this way you don’t need to provide an object store for every object you want to store. Also the Linq2IndexedDB framework also supports these complex keys and you can even use them in your custom filters.

Linq2IndexedDB: Debugging IndexedDB

In my latest version of the Linq2IndexedDB 1.0.7, I added the viewer object. This object allows you to inspect your IndexedDB database and gives you an overview of all the object stores and indexes that are present. It even gives you details about how they are configured and what data they contain. Also using this, you can take a look at your database in various debugging tools like:

• Google Chrome Developer tools
• Firefox Developer tools
• IE Developer tools
• Visual Studio

The viewer object contains 4 properties:

1. configuration: In here you will find the way the Linq2IndexedDB framework is configured. (The same properties as you can define in the configuration object are present +  a property indicating if framework is running in auto create mode.)
2. name: The name of the IndexedDB database
3. objectStores: A collection of all the object stores that are present in the database
4. version: The current version of the database

Inside an object store object, we can see the following:

1. autoIncrement: indicates if the keys for the object store are generated by the IndexedDB API
2. data: A collection of all the data available in the object store. These data objects are shown as key/value pair objects.
3. indexes: A collection of all the indexes available on the object store.
4. keyPath: The property inside the object that keeps the key
5. name: The name of the object store

The last level we can take a look at, are the indexes. The following information will be provided:

1. data: A collection of all the data available in the object store. These data objects are shown as key/value pair objects.
2. keyPath: The property inside the object that keeps the key
3. multiEntry: Determines if a key is only once defined in the index (which means the value keeps an array of all the values in the object store who match) or the key can be present multiple times (once for every value that matches in the object store.)
4. name: The name of the index

conclusion

I hope this debugging information can help you speed up your software development and finding bugs while working with the IndexedDB API. The latest version of the framework can be found on codeplex, nuget and MyGet.

Update 28/07/2012

I added a new version of the framework, because there were some little bugs with the viewer. The following things change:

• The viewer no longer blocks the deletion or upgrade of the database
• The viewer now gets updated when the structure of the database changes
• The viewer is default disabled for performance issues. If you want to enable it, add true as 3th argument on the function to create a linq2indexeddb function

   1: var db = $.linq2indexedDB("test", null, true);

Linq2IndexedDB: Custom filters

Since the 1.0.5 version of the Linq2IndexedDB framework, I added support for custom filters. And since the 1.0.6 version (released last week) you can add a function to the where clause to filter your data. These functionalities are provided by the framework, because the IndexedDB API only provides a handful filter possibilities. Also you can only use one filter when retrieving data.

Create a custom filter

To create a custom filter, the Linq2IndexedDB framework has an addFilter method. This method can be found in the linq namespace and accepts 3 arguments.

• The first argument is a name for the filter, this name will be used when you want to use your filter. Make sure that this name is unique over all the filters, if not an exception will be thrown.
• The second argument is a function that will be used to determine if the data is valid or not. When this function gets called 2 arguments will be passed. The first one is the object to validated. The second one is filter metadata. For example this object contains the name of the property you need to filter on, or an additional value provided, … The result of this function must be a Boolean value telling if the provided object is valid or not.
• The last argument is also a function and will be used to retrieve additional filter metadata. This function must return a new function with optionally arguments to retrieve the additional in formation. For example this information can be one or more values to use in the IsValid function. When called 3 arguments are passed to this function. The first one is a callback function that needs to be the return value of the function to retrieve the information. The second argument is the queryBuilder object that needs to be passed to the callback method. The third argument is the filterMetaData object. The additional information you retrieve, needs to be added to this object so you can use it later on in the IsValid method. After this is done, this object must also be provided as argument to the callback method.

In the code below I added an example of an equals filter.

   1: linq2indexedDB.prototype.linq.addFilter("equals", function (data, filter) {

   2:     return data[filter.propertyName] == filter.value;

   3: }

   4: , function (callback, queryBuilder, filterMetaData) {

   5:     return function (value) {

   6:         filterMetaData.value = value

   7:         return callback(queryBuilder, filterMetaData);

   8:     }

   9: });

When all this is done, you can start using this filter just by calling it after the where, or and and method call.

   1: db.from("objectStore").where("property").equals("value").select()

Anonymous filters

An other way to add a custom filter is by adding a callback function to the where, or or and method. When this callback function gets called, the object you need to validate is passed as an argument. The result of the callback function must be a Boolean value determining if the object is valid or not

   1: db.linq.from("objectStore").where(function (data) {

   2:     return data.Age > 3;

   3: }).and(function (data) {

   4:     return data.Age < 10;

   5: }).select();

conclusion

In the Linq2IndexedDB framework, you now have 2 ways to add custom filters. In the case you use the addFilter method, you can add a custom filter that can be reused and will appear in intellisense so you can easily call it. The other way provides an easy only once use of a filter. In both cases you are now flexible to provide your own filtering.

JSON: serialize and deserialize functions in JavaScript

In my Linq2IndexedDB project, I take advantage of the web workers to do all the filtering that the IndexedDB API doesn’t allow (multiple filters, like, inArray, …). For all these filters I have an “isValid” method which determines if the data satisfies the condition. So when I want to use these methods in my background worker, I need to make sure I can call them. For that I have 3 possibilities

1. Add a copy of all the filters in my background worker file
2. Include the file where my filters are defined
3. Serialize and deserialize the “isValid” functions

The first one wasn’t an option for me, because i wanted to keep all filter logic at one place. The second one was an option, but I didn’t want to use multiple JavaScript files for my library. And an other reason why I don’t like the first 2 is because developers would have to change my library if they want to add additional filters. So this left me only with the third possibility.

For this I’ve done some research. I already knew that you could call .toString on a function and this would result into a string representation of the function. And with the Function.call method I would be able to call the function in my background worker. But I didn’t wanted a solution that had to call the Function.call method in the background worker, I wanted to use the .isValid method that was provided in the filter objects. So I dug into the JSON API and found the following solution.

Serialize Objects

The JSON.stringify (the method that turns JavaScript objects into JSON text) accepts 2 arguments. The first argument is the object you want to turn into a JSON text and the second argument accepts a replacer function. This function gets called for every value in the object structure (even for properties in child objects) and accepts a key (name of the property) and a value (the value of the property) argument. The return value of the object is the object that will be stringified. By using this function, I can return the string representation of the function (in case of function) and return the value in the other cases.

   1: JSON.stringify(filters, function (key, value) {

   2:     if (typeof value === 'function') {

   3:         return value.toString();

   4:     }

   5:     return value;

   6: });

deserialize objects

The next thing we want to do, is deserialize the function again. Like the stringify method, the parse method (returns a JSON string into a JavaScript objects) also a callback (reviver) method as second argument. This method also gets called for every key and value for every level of the result. In this callback you can reform generic objects into instances of pseudo classes, strings into dates, strings into functions, …

   1: JSON.parse(filtersString, function (key, value) {

   2:     // reform your objects here

   3: });

Once I have the string representation of the function, I can start rebuilding that function. This is done by creating a new Function Object. The constructor of the Function objects accepts 2 arguments, a list of arguments for the function you want to create and the string representation of the body of the function you want to create.

   1: new Function(arguments, functionBody);

So if we put those 2 together we get the following:

   1: JSON.parse(filtersString, function (key, value) {

   2:     if (value 

   3:         && typeof value === "string" 

   4:         && value.substr(0,8) == "function") {

   5:         var startBody = value.indexOf('{') + 1;

   6:         var endBody = value.lastIndexOf('}');

   7:         var startArgs = value.indexOf('(') + 1;

   8:         var endArgs = value.indexOf(')');

   9:  

  10:         return new Function(value.substring(startArgs, endArgs)

  11:                           , value.substring(startBody, endBody));

  12:     }

  13:     return value;

  14: });

Detecting if the value is a function, is done by checking if the value is a string and starts with the word ‘function’. If this is the case, we will determine the arguments and the function body so we can pass it to the Function constructor. Retrieving the arguments of the new function is done by taking the string value between the first ‘(‘ and ‘)’. Retrieving the function body is done by getting the string value between the first ‘{‘ and the last ‘}’. Passing these 2 values to the constructor of the function will create a new function. This is the value that gets returned in case of a function. In all other cases we just return the value.

Conclusion

By using the stringify and parse method of the JSON API you can provide a generic way to serialize and deserialize your objects. Serializing functions can easily be done by calling the toString method on the function and deserializing a function can be done by using the Function constructor which is present in the JavaScript language.

Linq2IndexedDB: Release 1.0.3

This weekend I released a new version of the Linq2IndexedDB. The major changes in this release is a broader support of browsers. Because of the release of a new version of the IndexedDB spec some changes were made. The biggest change of them all is the transactionType. In previous versions this was a number (READ_ONLY = 0, READ_WRITE = 1, VERSION_CHANGE = 2), but this has changed into string values (“readonly”, “readwrite”, “versionchange”). Also these values aren’t available any more under the IDBTransaction interface.

Since this release the Linq2IndexedDB library now also supports browsers that doesn’t implement the IndexedDB specification yet. The only condition is they support the obsolete WebSQL spec. This means that Opera and Safari are now supported by the Linq2IndexedDB due the IndexedDB shim written by nparashuram who is also a contributor to the Linq2IndexedDB project.

With the release of the Windows 8 Release Preview, a new version of Internet Explorer 10 was released. Next to the change I described in the beginning of this post, IE10 Release Preview now uses the window.indexeddb implementation and no longer uses the ms prefix (window.msIndexedDB). The only disadvantage is they do not implement the autoIncrement and multiEntry attribute yet.

Next to the changes made to the Linq2IndexedDB library, I’ve started building on a new project: an IndexedDBViewer. This allows you easy access to the database structure and the contents of the object stores. The only thing you need to provide is the name of the IndexedDB you want to inspect. The only thing you need to keep in mind is that this IndexedDB viewer must be added to the Web app. This is necessary because you can only access the IndexedDB databases defined within your origin (domain).

All these changes are now available on MyGet:

• Linq2IndexedDB (jQuery) + IndexedDBViewer
• Linq2IndexedDB (Windows 8 implementation, uses WinJS Promises)

and on NuGet:

• Linq2indexedDB (jQuery)
• Linq2IndexedDB (Windows 8 implementation)
• IndexedDBViewer

All the sources of these projects can be found on CodePlex.

Using LINQ to Indexed DB

While the library is still under development, I want to take some time to explain how you can use this framework, and show how easy it gets to work with the Indexed DB. As mentioned before, the library is based on the on the Promises context. This way we can easily handle all the async callbacks on which the Indexed DB API is based, and provide a uniform object to the developer for handling the results and/or errors. And by using the progress callback, we can provide returning multiple records one by one instead of a whole collection in the complete callback. For more information about promises:

• jQuery promise
• WinJS promise

Another strength of the library is, that you get the opportunity to let it auto build it’s structure. It’s not necessary to provide the database structure. You can just start writing queries, and the library will take care of the creation of tables and indexes. This way it can easily be used for writing POCs or demo applications. This means when ever you are mentioning a string value in the from method that isn’t known in the object store collection, it will be created for you.

But enough about the theory, let us watch some code.

Setting UP Linq2indexeddb

The first thing you need to do is getting the linq2indexeddb library. You can do this by getting the JS script files from codeplex. The only thing you need to make sure is: the sort.js and where.js file need to be in a “Script” folder under the root of the project. These files are used to preform web worker tasks and are hardcoded referenced for now.

Or VS developers can use Nuget Packages:

• http://www.myget.org/feed/Packages/linq2indexeddb (jQuery version)
• http://www.myget.org/feed/Packages/linq2indexeddbmetro (Win8 version, using WinJS)

Once you have the linq2indexeddb library, the next thing you need to do is adding a reference to it in your page. Note, if you are using the jQuery version, you need to add a reference to the jQuery framework first.

using Linq2indexeddb

Once all the references are added, we can start using the library. The first thing you need to do is creating a instance of the linq2indexeddb object. You can do this by calling the linq2indexeddb method on the window object. This method accepts 3 parameters:

• The first one is the name of the database u want to use.
• The second one is a database configuration object. More on that later on the post.
• The last is an Boolean which allows you to enable logging. By default this is disabled

   1: var db = window.linq2indexedDB("dbName", dbConfig, false);

Configuring the database

When opening/creating the database, you can provide a database configuration. Here you can configure the object stores and indexes you want to use. There are several ways to do the configuration, but one thing needs to be provided at all time: the version of the database. This makes sure that the database will have the correct structure we need.

   1: var dbConfig = {};

   2: dbConfig.version = 1;

A second thing we need to do, is providing a way to define the structure. In the current implementation of the library, there are 4 ways to do this. For now I would advise you to use the definition. This doesn’t require you to write the create/delete statements your self. But if you do want to, you can make use of the promises present in the linq2indexeddb.core to create/delete object stores and indexes.

onupgradeneeded

This a function that gets called when the database needs to get updated to the most recent version you provided in the configuration object. 3 parameters are passed to this function:

• Transaction: on this transaction you can create/delete object stores and indexes
  
• The current version of the database
  
• The version the database is upgrading to.

   1: dbConfig.onupgradeneeded = 

   2:     function (transaction, oldVersion, newVersion){

   3:         // Code to upgrade the db structure

   4:     }

The onupgradeneeded callback is the only one that can’t be used in combination with one of the other ways to define the function

schema

The schema is an object which defines the several versions as a key/value. The key keeps the version it targets and in the value an upgrade function. In this function you can add your code to upgrade the database to the version given in the key. The upgrade function has one parameter:

• Transaction: on this transaction you can create/delete object stores and indexes

   1: dbConfig.schema = {

   2:     1: function (transaction){

   3:             // Code to upgrade the db structure to version 1

   4:        }

   5:     2: function (transaction){

   6:             // Code to upgrade the db structure to version 2

   7:        }

   8: }

Note: If the database needs to upgrade from version 0 to 2, the upgrade function of version 1 gets called first. When the upgrade to version 1 is done, the upgrade function of version 2 gets called.

definition

The definition object is the only way to define your database structure without having to write upgrade code. The object keeps a collection of objects which describe what needs to be added or removed for a version. Each object exists out of the following properties:

• Version: The version where for the definitions are.
  
• objectStores: Collection of objects that define an object store
  
  
  
  • name: the name of the object store
    
  • objectStoreOptions
    
    
    
    • autoincrement: defines if the key is handled by the database
      
    • keyPath: the name of the property that keeps the key for the object store
    
    
  • remove: indicates if the object store must be removed
  
  
• indexes: Collection of objects that define an index
  
  
  
  • objectStoreName: the name of the object store where the index needs to be created on
    
  • propertyName: the name of the property where for we want to add an index
    
  • indexOptions
    
    
    
    • unique: defines if the value of this property needs to be unique
      
    • multirow: Defines the way keys are handled that have an array value in the key
    
    
  • remove: indicates if the index must be removed
  
  
• defaultData: Collection of default data that needs to be added in the version
  
  
  
  • objectStoreName: the name of the object store where we want to add the data
    
  • data: the data we want to add
    
  • key: the key of the data
    
  • remove: indicates if the data needs to be removed

   1: dbConfig.definition= [{

   2:     version: 1,

   3:     objectStores: [{ name: "ObjectStoreName"

   4:                        , objectStoreOptions: { autoIncrement: false

   5:                                              , keyPath: "Id" } 

   6:                    }]

   7:     indexes: [{ objectStoreName: "ObjectStoreName"

   8:                   , propertyName: "PropertyName"

   9:                   , indexOptions: { unique: false, multirow: false } 

  10:               }],

  11:     defaultData: [{ objectStoreName: ObjectStoreName

  12:                       , data: { Id: 1, Description: "Description1" }

  13:                       , remove: false },

  14:                   { objectStoreName: ObjectStoreName

  15:                       , data: { Id: 2, Description: "Description2" }

  16:                       , remove: false },

  17:                   { objectStoreName: ObjectStoreName

  18:                         , data: { Id: 3, Description: "Description3" }

  19:                         , remove: false }]

  20: }];

onversionchange

This a function that gets called when the database needs to get updated to the most recent version you provided in the configuration object. But in contrast to the onupgradeneeded callback, this function can be called multiple times. For example if an database is upgrading from version 0 to version 2, the onversionchange will be called 2 times. Once for version 1 and once for version 2.  2 parameters are passed to the function:

• Transaction: on this transaction you can create/delete object stores and indexes
  
• The version of the database it is upgrading to.

   1: dbConfig.onversionchange = 

   2:     function (transaction, version){

   3:         // Code to upgrade the db structure

   4:     }

Querying data

Once the database structure is created, we can start querying on it. On the linq2indexeddb object we have a field linq which holds all the linq functionality. The first method you always need to call is the from method. This method excepts only one parameter, the name of the object store we want to work on. Once we have this we can start inserting, updating, deleting and selecting data from it.

   1: // inserting data, key is optional

   2: db.linq.from("objectstore").insert({}, key);

   3: // updating data, key is optional

   4: db.linq.from("objectstore").update({}, key);

   5: // removing data

   6: db.linq.from("objectstore").remove(id);

   7: // clears all data from the object store

   8: db.linq.from("objectstore").clear();

   9: // gets a single record by his key

  10: db.linq.from("objectstore").get(key);

  11: // selects all objects

  12: db.linq.from("objectstore").select();

But the library also provides ways to filter data. This can be done by calling the where method. This accepts the name of the property we want to filter on as parameter. On this you can call the filter you want to add. For now these are limited to the following:

• equals(value)
  
• between(value1, value2, value1included, value2included)
  
• greaterThen(value, valueIncluded)
  
• smallerThen(value, valueIncluded)
  
• inArray(arrayOfObjects)
  
• like(value)

So if we want to select all the objects which have a field called '”property” and have the value “value” we write the following query:

   1: db.linq.from("objectstore").where("property").equals("value").select()

If you want to add multiple filters you need to do the following:

   1: db.linq.from("objectstore")

   2:        .where("property").equals("value")

   3:        .and("anotherproperty").greaterThen(4)

   4:        .select()

You are also able to sort your data:

• orderBy(propertyName)


• orderByDesc(propertyName)

   1: db.linq.orderBy("property").select()

As last, the library also enables you to get only a subset of the properties stored in the objects

   1: db.linq.from("objectstore").select(["property1", "property2"])

Conclusion

This was a brief introduction of the functionalities of the linq2indexeddb library. In future post I will go more in-depth into the advanced functionalities like linq2indexeddb.core. If you have any suggestions, bugs, additions,… about the library, feel free to contact me. Hope you enjoy using the library.