QuickLite Overview

Purpose of this document

QuickLite 1.5 incorporates many new features and improvements. The purpose of this document is to introduce the developer to these additions and changes, as well as explain the reasoning behind some of the new implementations.

Datatype support

SQLite is inherently type-less. Any type of data can be stored in the database, strings, sounds, numbers, BLOBs... anything. This brings the following problem: what is stored in the database and how can we interpret it? In v1.0, we just can't.

New features such as QuickEdit and the new caching method requires datatype support because it's essential to instantiate the proper object for the data stored in the database. These are the new datatypes supported in QuickLite 1.5:

Datatype	Cocoa class	Purpose
QL_String QL_Number QL_DateTime QL_Boolean QL_Container	NSString NSNumber NSCalendarDate NSNumber NSData	String of characters Integer and floating point Date and time with timezone support Boolean Values Any type of data: images, sound, video...

QuickLite will store the data transparently, based on its declared datatype. In the case of BLOBs, for example, the NSData object will be encoded using Base64 and stored in the database. Likewise, a BLOB will be decoded back properly and converted to NSData prior to be returned.

Better data caching

Typically, the developer must choose between maximum performance and lower memory footprint. To get the best performance, data should be stored in RAM. This can lead to a large data set being pushed to RAM. In most systems today, it may not be an issue, but developers may still want to keep the memory footprint leaner. Starting in v1.5, three new caching methods are supported:

Cache method	What it does	Performance	Memory footprint
CacheAllData CacheDataOnDemand DoNotCacheData	Caches all selected data Caches data as it is requested Fetches data from disk each time	Best Better Good	Large May grow large Best

It's important to understand that even though CacheDataOnDemand starts with an empty cache, it can grow quickly if the app requests lots of data for most of its columns. If memory is definitely a concern, DoNotCache is the best method because it'll fetch data from disk each time. Obviously, performance will not be the best but since SQLite fetches data very quickly, the negative on performance should not be too bad. Your mileage may vary depending on the application being written.

QuickEdit: edit and delete data the OOP way

Data is retrieved from the database performing a query and obtaining a QuickLiteCursor. To access the data, a QuickLiteRow must be obtained first through an index. Once we have the row object, the data can be accessed via its accessors.

In v1.0, the concept of Namespaces were introduced. Namespaces has brought serious limitations in v1.5 while implementing the new caching scheme as well as QuickEdit. For these reasons alone, the concept of Namespace has been removed. Starting in v1.5, the API expects columns to be declared in the following form: table.column (i.e. address.ZIP). The only exceptions are those methods which require a 'table' parameter. A few examples follow:

- (BOOL)createTable:(NSString*)table withColumns:(NSArray*)columns andDatatypes:(NSArray*)datatypes;
- (BOOL)addColumn:(NSString*)columnName withDataType:(NSString*)dataType toTable:(NSString*)table;
- (BOOL)insertValues:(NSArray*)values forColumns:(NSArray*)columns inTable:(NSString*)table;

In the above examples, columns are declared without prefixing the table name to the column name.

In short, QuickEdit allows cursors to be edited directly without dealing with SQL statements at all. Typically, cursors are static, that is, its data cannot be modified and new data cannot be appended in any way. QuickEdit takes cursors to a new level, allowing data to be modified, added or deleted:

- (BOOL)setValue:(id)value forColumn:(NSString*)tableAndColumn;
- (BOOL)insertRowWithValues:(NSArray*)values andColumns:(NSArray*)columns;
- (BOOL)removeAllRowUIDsAtIndex:(unsigned long)rowIndex;

When any combination of these actions take place, the cursor registers with the QuickLiteDatabase object. This registration allows the database object to communicate to the cursor when the database is about to be closed. The cursor then proceeds to commit the changes to the database. Of course, the developer is in full control, so uncommitted changes made to a cursor can safely be discarded via [QuickLiteCursor revert] or [Database revert]. Data can be committed to disk at once via [QuickLiteDatabase save] or on a cursor-per-cursor basis via [QuickLiteCursor save]. After either a save or revert has taken place, the cursors de-register automatically from the database object.

When new data is added to a cursor, it is appended at the end of its data set. If you need to know the range of rows that comprise these newly added rows, you can use the following method found in QuickLiteCursor:

- (NSRange)rangeOfInsertedRows;

If no new rows have been added, this method returns a range of {NSNotFound, 0}.

Changes can be applied to an entire column without having to traverse the cursor. In QuickLiteCursor, the following method does that:

- (BOOL)setValue:(id)value forColumn:(NSString*)tableAndColumn;

When setting data either on a row-per-row basis or to an entire column, the value datatype must match the column datatype. For instance, setting a string to a column declared as anything other than a QLString will produce an error and the change will not be honored. Another point to be aware of: QuickEdit requires that all columns specified when adding/modifying/deleting data must exist in the cursor. New columns cannot be added on-the-fly and only columns included as a result of the SELECT statement are allowed.

Special care must be taken when deleting rows. Row data can contain one or more rowUIDs collected from a SELECT query. For example, the following columns could be specified:

person.ROWID | person.first | order.ROWID | order.total
1673 | Joe | 984 | 231.45

You can delete a row via 'removeAllRowUIDsAtIndex', in QuickLiteCursor:

- (BOOL)removeAllRowUIDsAtIndex:(unsigned long)rowIndex;

QuickEdit will annotate all references to ROWID, no matter what table. In the case specified above, it will mark two records for deletion: ROWID 1673 in table 'person' and ROWID 984 in table 'order'. These marked deletions can be reclaimed via [QuickLiteCursor revert], but will go into effect once [QuickLiteDatabase save] or [QuickLiteCursor save] is called.

If this is not what you want to do, you can obtain the modified rowUIDs with:

- (NSDictionary*)modifiedRowUIDs;

The XML representation of the dictionary would be something like this:

This way you can delete the rowUIDs you desire at your own discretion with the following methods found in QuickLiteDatabase:

- (BOOL)deleteRowWithUID:(NSString*)rowUID inTable:(NSString*)table;
- (BOOL)deleteRowsWithUIDObjects:(NSArray*)rowUIDs inTable:(NSString*)table;

If you want to remove all entries in a specific table you can use the following method:

- (BOOL)deleteAllRowsInTable:(NSString*)table;

QuickEdit is completely optional. Since it is an extension of QuickLite, it can be ignored completely if you're not interested.

In-cursor data matching

Sometimes you may have a cursor with all the data you need, but there is a problem: the data is divided in smaller datasets. For example, a series of invoices belonging to different customers. With in-cursor data matching, you can find the first match for a given column starting at a given index, or obtain a series of row indexes which contain a given value in a given column. In other words, you can find out the rows contain invoices belonging to a specific cursor.

	Datatype		Comparison method
	QL_String QL_Number QL_DateTime QL_Boolean QL_Container		- (NSComparisonResult)compare:(NSString )aStringoptions:(unsigned)mask - (BOOL)isEqualToNumber:(NSNumber )aNumber - (BOOL)isEqualToDate:(NSDate )otherDate - (BOOL)isEqualToNumber:(NSNumber )aNumber - (BOOL)isEqualToData:(NSData *)other

There are two ways to perform data matching: on a row by row basis (individually) or by obtaining the rows at once (grouped). Please note that the 'comparisonMethod' argument in the following methods only makes sense with objects of type QL_String, since only NSString supports the following comparison methods: NSCaseInsensitiveSearch, NSLiteralSearch, NSBackwardsSearch, NSAnchoredSearch, NSBackwardsSearch, and NSNumericSearch. The default mode is 'NSLiteralSearch'.

These methods do not instantiate any QuickLiteRow object, it simply returns the row index it occupies in the cursor. The individual data matching methods return the row index as a scalar type, as oposed to the grouped ones, which are returned as NSNumbers in an array. The main reason to return an NSNumber instead of a QuickLiteRow has to do with memory and performance. Matching values could potentially return many rows, which would have to be instantiated all at once. Instead, we do that lazily, deferring it to when it's actually needed.

To find the rows individually, we have the following methods:

// Find the first row matching a value in a given column, starting at index 0.
- (unsigned long)matchingRowWithValue:(id)value forColumn:(NSString*)tableAndColumn;

// Find the first row matching a value in a given column, starting at the given index.
- (unsigned long)matchingRowWithValue:(id)value forColumn:(NSString*)tableAndColumn startAtIndex:(unsigned long)index;

// Find the first row matching a value in a given column, starting at a given index. It overrides 'NSLiteralSearch' with the comparison method supplied.
- (unsigned long)matchingRowWithValue:(id)value forColumn:(NSString*)tableAndColumn startAtIndex:(unsigned long)index comparisonMethod:(unsigned)method;

To find the rows grouped, we have the following methods are available:

// Find the row indexes matching a value in a given column.
- (NSArray*)matchingRowsWithValue:(id)value forColumn:(NSString*)tableAndColumn;

// Find the row indexes matching a value in a given column. It overrides 'NSLiteralSearch' with the comparison method supplied.
- (NSArray*)matchingRowsWithValue:(id)value forColumn:(NSString*)tableAndColumn comparisonMethod:(unsigned)method;

// Find the row indexes matching a series of values in a given column.
- (NSArray*)matchingRowsWithValues:(NSArray*)values forColumn:(NSString*)tableAndColumn;

// Find the row indexes matching a series of values in a given column. It overrides 'NSLiteralSearch' with the comparison method supplied.
- (NSArray*)matchingRowsWithValues:(NSArray*)values forColumn:(NSString*)tableAndColumn comparisonMethod:(unsigned)method;

Some examples:

unsigned int rowIndex = [cursor matchingRowWithValue:@"Tito" forColumn:@"address.First" startAtIndex:0 comparisonMethod:NSLiteralSearch]);

NSArray* valuesToFind = [NSArray arrayWithObjects:@"España", @"ITALY", nil];
NSArray* rows = [cursor matchingRowsWithValues: valuesToFind forColumn:@"address.Country" comparisonMethod:NSLiteralSearch]);

Set operations on cursors

QuickLite 1.5.3 also introduces the possibility to perform set operations on cursors: union, minus and intersection. These are the requirements:

- Only one table must be referenced in any of the two cursors
- The same table must exist in the two cursors
- The ROWID column must be present in the two cursors

If the result set of the operation is empty, an empty cursor is created. Otherwise, a new cursor is created with the ROWIDs as well as a distinct selection of all columns found in both cursors. Example:

Cursor A: people.last, people.first, people.phone, people.age
Cursor B: people.first, people.age, people.address, people.city

The resulting cursor from any set operation performed on these two cursors will contain the following columns: people.last, people.first, people.phone, people.age, people.address, and people.city.

Following are the methods used to perform set operations. The methods without the 'cacheMethod' argument assume the default cache method set in the QuickLiteDatabase object when the database was last opened.

- (QuickLiteCursor*)unionCursor:(QuickLiteCursor*)cursor;
- (QuickLiteCursor*)unionCursor:(QuickLiteCursor*)cursor cacheMethod:(QLCacheMethod)cacheMethod;
- (QuickLiteCursor*)minusCursor:(QuickLiteCursor*)cursor;
- (QuickLiteCursor*)minusCursor:(QuickLiteCursor*)cursor cacheMethod:(QLCacheMethod)cacheMethod;
- (QuickLiteCursor*)intersectCursor:(QuickLiteCursor*)cursor;
- (QuickLiteCursor*)intersectCursor:(QuickLiteCursor*)cursor cacheMethod:(QLCacheMethod)cacheMethod;

Performing set operations on cursors is very easy:

QuickLiteCursor *a = [db performQuery:@"SELECT * FROM address WHERE Country LIKE 'España';"];
QuickLiteCursor *b = [db performQuery:@"SELECT * FROM address WHERE Country LIKE 'Italy';"];
QuickLiteCursor *c = [a unionCursor:b cacheMethod:DoNotCacheData];

Notify data changes via notifications

Notifications are essential to keep QuickLiteCursors in sync with the database. Every QuickEdit operation is kept in memory until [QuickLiteDatabase save] or [QuickLiteCursor save] is called. When this happens, QuickLite issues an NSDistributedNotification, QLP_QuickLiteDatabaseDidChangeNotification, with a userInfo dictionary containing the following information:

QLP_ProcessID: NSNumber containing the process ID that generated the notification.
QLDatabasePathIdentifier: NSString containing the path of the database involved.
QLTimestampIdentifier: NSCalendarDate containing the timestamp the when SQL statement was executed.

This notification is also broadcasted to other currently logged-in users on Mac OS X 10.3 or later.

When you receive a QLP_QuickLiteDatabaseDidChangeNotification, you may want to update the current cursors by calling [QuickLiteCursor refresh]. This action executes the very same SQL statement that created the cursor in the first place, and while the static data is refreshed, it *does not* clear the other cached data (edits, inserts and/or deletes).

Debugging Tools

Two options allows the developer to obtain the SQL statements invoked and to trace the QuickLite methods called. These two options are found in [QuickLiteDatabase open: cacheMethod: exposeSQLOnNotify: debugMode:]

Setting 'exposeSQLOnNotify' sends a distributed notification, QLDatabaseHasSentSQLStatementNotification, each time a SQL statement is executed. The dictionary sent along the notification contains the following info:

QLP_ProcessID: NSNumber containing the process ID that generated the notification.
QLDatabasePathIdentifier: NSString containing the path of the database involved.
QLTimestampIdentifier: NSCalendarDate containing the timestamp the when SQL statement was executed.
QLSQLStatementIdentifier: NSString containing the SQL statement.

Be aware that turning this option on slows down QuickLite by ~50%, so make sure that it's turned off in your deployed version. For more info, refer to the QuickLiteListener example, found in the Examples folder.

If you want to trace which QuickLite methods are being called, turn on the 'debugMode' option. An example of the log output follows:

2004-08-21 14:19:07.695 SuperTableView[4610] [QuickLiteDatabase] beginTransaction
2004-08-21 14:19:07.698 SuperTableView[4610] [QuickLiteDatabase] dropTable:
2004-08-21 14:19:07.699 SuperTableView[4610] [QuickLiteDatabase] datatypesForTable:
2004-08-21 14:19:07.700 SuperTableView[4610] [QuickLiteCursor] valueForColumn:andRow:
2004-08-21 14:19:07.700 SuperTableView[4610] [QuickLiteDatabase] dropTable:
2004-08-21 14:19:07.700 SuperTableView[4610] [QuickLiteDatabase] createTable:withColumns:andDatatypes:
2004-08-21 14:19:07.702 SuperTableView[4610] [QuickLiteDatabase] datatypesForTable:
2004-08-21 14:19:07.702 SuperTableView[4610] [QuickLiteCursor] valueForColumn:andRow:
2004-08-21 14:19:07.703 SuperTableView[4610] [QuickLiteDatabase] datatypesForTable:
2004-08-21 14:19:07.703 SuperTableView[4610] [QuickLiteCursor] valueForColumn:andRow:
2004-08-21 14:19:07.722 SuperTableView[4610] [QuickLiteDatabase] insertValues:forColumns:inTable:
2004-08-21 14:19:07.722 SuperTableView[4610] [QuickLiteDatabase] commitTransaction
2004-08-21 14:19:07.766 SuperTableView[4610] [QuickLiteDatabase] compact
2004-08-21 14:19:07.792 SuperTableView[4610] [QuickLiteDatabase] performQuery:

These options are useful to know what goes on 'behind the scenes', and should be turned off if you want to obtain the optimal performance.

Summary of features

- Updated with SQLite 3.2.2
- Data integrity check provided by SQLite
- Datatype support includes QLString, QLNumber, QLBoolean, QLDateTime, and QLContainer
- Better data caching: CacheAllData, CacheOnDemand, or DoNotCacheData
- QuickEdit: insert, edit, and remove rows without a single SQL statement, the OOP-way
- In-cursor data matching
- Set operations on cursors
- Support for attached databases
- Save and revert supported, database-wide and on a cursor-by-cursor basis
- Distributed notifications, Fast User Switching-aware, sent when a commit action takes place
- Debugging facilities to observe all SQL statements executed, as well as tracing QuickLite methods