-
Notifications
You must be signed in to change notification settings - Fork 364
Secondary Indexes
There's an extension for that.
A key/value database, in and of itself, is not all that flexible. There are times when you need to find items based on criteria other than the key. That's where secondary indexes come in. A secondary index gives you access to the SQL tools you're used to, allowing you to create indexes on important properties, and to query those indexes to find the item(s) you need.
There are multiple extensions that come with YapDatabase. It's important to understand what's available so that you can pick the best extension for the job.
- Views
- Full Text Search
- Secondary Indexes
There is some overlap in functionality between each of these extensions. For example, you could use a secondary index to index strings, and then perform searches on them. This is fine and well if you're searching for exact matches. But if you're doing google style searching for terms and such, then the full text search extension is better suited to your needs.
In a similar manner, there is overlap between views and secondary indexes. And this is important to understand:
-
A view allows you to specify something similar to a SELECT clause (but with blocks), and gives you a sorted subset of your items. This makes it perfect for acting as the datasource for a table. Or any other kind of SELECT type query you need to access on a regular basis. Further, the view automatically updates as the database changes, and also pumps out change-sets which can be used to animate changes to a tableView.
-
A secondary index gives you one or more indexes in SQLite, which can then be queried directly to find or enumerate matching items.
Your specific use cases will dictate which extension is a better match for your needs. Keep in mind that you can use both extensions simultaneously. In fact, you can have multiple views and multiple secondary indexes. So you can make these decisions on a per-need basis.
Extensions work seamlessly behind the scenes. You don't have to change the way you write objects to the database. Extensions automatically take part in the transaction architecture, and automatically update themselves as you make changes to the database.
The end result is that your extensions can come and go with ease. You can change your extensions around every build-and-go as you're developing. But the objects in your database stay the same. And, just as important, the code you have that inserts, deletes & updates objects in the database can stay the same.
For a deeper understanding of the extensions architecture, see the Extensions article.
The secondary index extension works by creating one or more indexes within SQLite. But one of the nice things about secondary indexes in YapDatabase is that you can create these indexes on only a subset of your data. So if you have a million items in your database, but only a thousand employee objects, you could create a secondary index on employee.salary, and only incur the overhead of an index with 1,000 entries.
The first step in creating a secondary index is to identify the properties you'd like to index. These translate into column names and column types.
YapDatabaseSecondaryIndexSetup *setup = [ [YapDatabaseSecondaryIndexSetup alloc] init];
[setup addColumn:@"department" withType:YapDatabaseSecondaryIndexTypeText];
[setup addColumn:@"salary" withType:YapDatabaseSecondaryIndexTypeReal];The column types are used to specify what type of data is being indexed. And the column names can be anything you choose.
The next step is to create a block which will extract the index information for a given row in the database.
YapDatabaseSecondaryIndexBlockType blockType = YapDatabaseSecondaryIndexBlockTypeWithObject;
YapDatabaseSecondaryIndexWithObjectBlock block = ^(NSMutableDictionary *dict, NSString *collection, NSString *key, id object){
if ([object isKindOfClass:[Employee class]])
{
Employee *employee = (Employee *)object;
[dict setObject:employee.department forKey:@"department"];
[dict setObject:@(employee.salary) forKey:@"salary"];
}
else
{
// Don't need to index this item.
// So we simply don't add anything to the dict.
}
};The first parameter to the block is a mutable dictionary. Just add the proper fields into the dictionary. In the example above, employee.department is a string. This matches the department column and type we specified. And employee.salary is a float or double. This matches the salary column and type we specified. (Of course we wrap it in NSNumber to stuff it in the dictionary.)
The other parameters to the block correspond to the row in the database. In this example we just needed the object. But it's also possible to index information in the metadata as well.
Next we create the secondary index instance by feeding it all the configuration from above.
YapDatabaseSecondaryIndex *secondaryIndex =
[[YapDatabaseSecondaryIndex alloc] initWithSetup:setup block:block blockType:blockType];And lastly we need to plug the extension into the database system.
[database registerExtension:secondaryIndex withName:@"idx"];Once plugged in, the extension will be available to use, and will automatically update itself. Remember that you can register extensions, and unregister extensions at any time. So extensions that you use on a regular basis should be registered on every app launch, as part of the database setup code. Otherwise you can bring up extensions on-the-fly as you're running the app.
For more information about registering & unregistering extensions, see the Extensions article.
Once you've setup your secondary index, you can access it from within any transaction. For example:
[connection readWithBlock:^(YapDatabaseReadTransaction *transaction) {
YapDatabaseQuery *query = [YapDatabaseQuery queryWithFormat:@"WHERE salary >= 100000"];
[[transaction ext:@"idx"] enumerateKeysMatchingQuery:query usingBlock:^(NSString *collection, NSString *key, BOOL *stop) {
// ...
}];
}];There are two steps to using a secondary index:
- creating the query
- executing the query
If you're familiar with SQL, you'll notice that the query looks like a subset of a normal query. Rather than shield you from SQL, the door is left wide open for you. Everything that SQLite supports you can throw into the query. Including ORDER BY and GROUP BY if you want.
Here's how it works. The query you supply will be automatically prepended with the proper SELECT clause that matches the enumerate method you call. In the example above we used enumerateKeysMatchingQuery:, so the full query looks something akin to this:
SELECT key FROM database WHERE salary >= 100000
And then the enumerate method handles stepping over the results and invoking the given block.
Also, YapDatabaseQuery supports query parameters. This works in almost the exact same way as it does in SQLite:
[connection readWithBlock:^(YapDatabaseReadTransaction *transaction) {
YapDatabaseQuery *query = nil;
// Find 28% federal tax bracket employees:
double minSalary = 87850;
double maxSalary = 183250;
query = [YapDatabaseQuery queryWithFormat:@"WHERE salary > ? AND salary <= ?",
@(minSalary), @(maxSalary)];
[[transaction ext:@"idx"] enumerateKeysMatchingQuery:query usingBlock:^(NSString *collection, NSString *key, BOOL *stop) {
// ...
}];
// Find engineers in the 28% federal tax bracket
NSString *department = @"engineering";
query = [YapDatabaseQuery queryWithFormat:@"WHERE salary > ? AND salary <= ? AND department == ?",
@(minSalary), @(maxSalary), department];
[[transaction ext:@"idx"] enumerateKeysMatchingQuery:query usingBlock:^(NSString *collection, NSString *key, BOOL *stop) {
// ...
}];
}];All the same rules that apply to query syntax in SQLite apply here. The primary differences are:
- You don't specify the SELECT part the of the query
- All query parameters need to be objective-c objects (so wrap numbers in NSNumber)
You're highly encouraged to use query parameters. That is, specifying '?' for a value, and then passing the value separately (as above). Doing so means you don't have to worry about escaping strings, and other such caveats. But also because there's an overhead to compile the query string you pass into an executable routine by the sqlite engine. The YapDatabase layer uses some caching optimizations to skip this overhead when you use the same query string multiple times. That is, if the query string doesn't change, then the yapDatabase layer can better optimize automatically for you. Thus using query parameters means the query string remains the same, even when you pass different values.
The examples so far have demonstrated enumerating over just the matching keys. But you can enumerate over whatever components of the row you want. Such as the keys and objects:
[connection readWithBlock:^(YapDatabaseReadTransaction *transaction) {
YapDatabaseQuery *query =
[YapDatabaseQuery queryWithFormat:@"WHERE department = ? ORDER by salary DESC", department];
[[transaction ext:@"idx"] enumerateKeysAndObjectsMatchingQuery:query
usingBlock:^(NSString *collection, NSString *key, id object, BOOL *stop) {
// ...
}];
}];