This feature is only available in the Pro version of WebSQL/SQLite and IndexedDb providers.
You can download your Pro provider from jaystack.com after a simple registration. For commercial usage you can either evaluate it for 30 days for free or you can buy it. For non-commercial usage it’s free.
Indexes on database tables are vital, they provide rapid random lookups as well as efficient access of ordered records. Unique indices create implicit constraints on the underlying table. Without indices or when a query can not use existing indices, the database must perform a full table scan, which can be slow, especially with lots of records. So proper indexing makes the difference between a performing app and a slowish one. On the other hand, having too many indices can slow down the inserts/updates and might take up considerable amount of disc space!
This feature works with the Pro version of indexedDb and WebSQL/SQLite providers. The appropriate provider must be manually included, autoloading does not work for Pro drivers. The inclusion must be before the first context is created.
Indexes must be declared on the EntityContext level. Entities might have many indices.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
var Category = $data.Entity.extend(<span class="str">"Category"</span>, { Id: { type: <span class="str">'int'</span>, key: <span class="kwrd">true</span>, computed: <span class="kwrd">true</span> }, Name: { type: String }, Name2: { type: String }, Name3: { type: String } }); $data.EntityContext.extend(<span class="str">"MyContext"</span>, { Categories: { type: $data.EntitySet, elementType: Category, indices: [ { name: <span class="str">'byName'</span>, keys: [<span class="str">'Name'</span>] } { name: <span class="str">'byName23'</span>, keys: [<span class="str">'Name2'</span>, <span class="str">'Name3'</span>], unique: true } ] </code><code class="language-javascript"> } }); |
Each index definition is an object, with the following properties:
name: name of the index
keys: array containing the name of the columns
unique: boolean, optional, default: false, true –> unique index
Limitation: Internet Explorer 10 with indexedDb does not support indices with more than one column!
WebSQL
WebSQL(sqLite) handles the indices automatically.
IndexedDb
The JayData indexedDb provider must explicitly select an index to be used for retrieving data from the database. Therefore it is important to match your filters and your index declarations, if JayData indexedDb provider can not match an index to the filter then it will do a full table scan.
For a simple filter (ex: it.Name2 == ‘Foo’) it’s straightforward, there should be an index for the ‘Name2’ field. In the previous example there is no index for ‘Name2’ so JayData will do a full table scan!
When the filter is complex with ‘and’ and ‘or’ logical operators, then JayData first builds a tree from the query. Every ‘or’ creates a new subquery and after every subqueries are executed the results are combined in memory. Subqueries might contain ‘and’ logical operations. For each subquery, JayData takes the first member of the subquery and tries to find an index for that field! The rest of the filtering is done in memory. In the light of the previous example, the following filter would be fast:
|
1 |
mydatabase.Categories.filter(<span class="str">"it.Name == 'Foo' and it.Name2 == 'Bar'"</span>) |
while this filter would do a full table scan:
|
1 |
mydatabase.Categories.filter(<span class="str">"it.Name2 == 'Bar' and it.Name == 'Foo'"</span>) |
