Archive for January, 2009

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.

MenuBar Skinning

The default menubar looks quite bad, I used half day to google and skin it. Ok, the result looks not too bad.

  • Take out top menu item background.
  • Add filter to top item label.
  • Change top item rollover background.
  • Set minimum width for top item.
  • Set minimum width for drop down menu.
  • Skinning for dropdown menu in CSS.

Full example source code download here.

File list:

  • MenuBarSkin.mxml, with menu style and menuBar style especially fillColors, fillAlphas.
  •, extend MenuBar class, getMenuAt() to set minimum width for drop-down menu, updateBackground() was to remove the background.
  •, extend MenuBarItem, set top item label dropshadow filter and set minimum width for top item.
  •, replace default up/down/over halo skin.

Simple Flash CS4-style Docking in Flex

Inspired by Degrafa, I spent couple of days to build a quick docking manager. It can add any component/containers into the list dynamically.

Full source code download here.

My working project with this docking manager: