SQLite as Service in AIR (Flash/Flex)
I introduce here is my approach (not fully complete framework), to use SQLite like an external service in AIR, in most simple way.
The following code is how it to be used in Flash/Flex application:
That simple? Sure it is.
Don’t forget, in AIR applications, using SQLite we MUST use asynchronous connections for user experiences. The framework will do rest hard jobs for you.
Framework Features:
- SQLConnection instances reused.
- SQLStatement instances reused.
- Statements executed in queue automatically, no extra control required.
- Dealing with SQLite locks inside framework.
- Feedback(result/fault) functions can be reused, similar with pureMVC’s handleNotifications.
- Support to run feedback functions not original object, i.e. a view sent a query then closed, after query complete, run feedback function in another object/view.
- All statement/connection objects managed and resources can be cleaned.
SQLite Performance Best Practices
It’s important to get benefit of maximum performance of database, SQLite has its own specifications. One of them, Adobe has already documented, is the cached statements, cached statements can be reused and increase performance largely.
The second one, I didn’t see anyone mentioned it (maybe I missed), is the lock system of SQLite.
SQLite has a special lock system, have a look the figure followed:
Here’s several important rules about SQLite lock system:
- A connection can have only one transaction (working statement) at a time.
- SQLite can have multiple read transactions(read statement) and one single write transaction(write statement) at the same time.
- When the write transaction working or waiting, no more new connections can work.
- The waiting write transaction will be executed only after all other read transactions complete.
The third one, using SQLTransactionLockType.IMMEDIATE for write transaction can improve performance when necessary.
My solution implement first and second, the last one I didn’t find a good solution to use it in asynchronous connection mode, will talk it later.
Framework Structure
The framework include three major classes:
- SQLManager
- ConnectionPool, manage and reuse connections for statements.
- StatementManager, add/remove statements.
These object classes/interface used by the framework:
- SQLiteStatement, after an operation finished, it run feedback based on caller and function(string). Feedback function can be different to the original view(object).
- SQLiteConnection
- CachedStatement, statement cannot to be run immediately will be cached as a CachedStatement in waiting list.
- ISQLResponder, optional interface for caller view/object.
Framework workflow
- Create instance for a database file (file can not exist even)
- Add statements when needed, statements will be initialized.
- View call for execute one or several statements with statement_id, set result/fault function, and flag whether need to ignore fault (if ignore, sqlite will continue to run waiting statements)
- SQLManager get statement instance, or cache your request to a waiting list if already used.
- SQLManager ask ConnectionPool to apply a SQLiteConnection for statement to execute.
- Statement try to execute, if SQLConnection not connected, waiting for that connection and execute.
- Statement completed/fault, call feedback function with statement_id (if asked), and execute next waiting statement if exist.
The framework keep minimum instances of SQLConnection and SQLStatement, and easy to be removed as they’re managed for garbage collection.
I’m not going to publish source code right now as it’s not a complete framework yet.
