In AIR (Flex/Flash) application, syslog support might be very useful for QA/developers also can be helpful for end user as well. I wrote some fast code for my personal project Dailytasks on this (and another more complex SQLite database with transaction manager) during this long weekend.

You can download source code from here. (Need some tweaks to fit your own application for sure.)

Here’s how it works:

• (Run once and comment it out) Create syslog database file and table. (I use some code to create and run once only, when I need update my table structure, rebuild database again.)
• Initialize SyslogService instance in preinitialize process of application. (it will open SQLConnection and compile statement to be used later.)
• Call static function from any points, i.e.:
  • SyslogService.log(SyslogLevel.INFO, 'Application start.');
• If statement is executing, push log data into stack and insert after.

Update (September 3):

I don’t have time to test completely. If you got database open error, just add open listener before execute the statement. Code is just simple like this.

if(!sqlConnection.connected)

sqlConneciton.addEventListener ...

Because the connection and statement will be reused all the time, you need to detect only once.

Execute statement

Every statement has an associated connection object, a statement represents a single ‘compiled’ SQL statement. There’re two methods to execute SQLStatement instance in AIR, one execute a single statement each time, another use .begin() .commit() to execute multiple statements, as a transaction. For SQLite engine, they both transaction, the single statement operates in autocommit mode by default.

SQL statement execution process: Statement+SQL -> Compilation -> Byte Code (with parameters) -> execution -> Finalization

Use parameters for statements is highly recommended. One important benefit Adobe might not documented: SQLite takes care of escaping the string values binding to parameters, helping avoid syntax errors and SQL injection attacks, i.e, it will convert ‘ to ”.

Transaction and Locks

• Multiple read transactions (SELECT), one single write transaction (INSERT/UPDATE/DELETE) at a time.
• When write transaction is writing (exclusive locked), no new transactions can connect.
• Write transaction will wait for any running read transactions to finish before writing.

Each transaction (or SQLStatement execution in autocommit mode) transit from one state to another state.

A default SELECT statement run in path as followed: Unlocked -> Pending -> Shared -> Unlocked

Two SELECT statements grouped with .begin() and .commit() in a single transaction:

Unlocked -> Pending -> Shared -> Unlocked

Two SELECT statements run in turn:

Unlocked -> Pending -> Shared -> Unlocked -> Pending -> Shared -> Unlocked

A write transaction (i.e. UPDATE) by default (autocommit mode):

Unlocked -> Pending -> Shared -> Reserved -> Pending -> Exclusive locked(commit) -> Unlocked

A write transaction with ‘BEGIN IMMEDIATE’:

Reserved -> Pending -> Exclusive locked(commit) -> Unlocked

The ‘Reserved’ state is a tricky state that may a place to improve performance. It actually store modifications in a memory cache, and create a rollback journal file for possible crash recovery. In this state the write transaction get real work done without affecting other read transactions. That’s how ‘multiple reader, one writer’ works.

After the write transaction get it work done, it begins trying get a Pending lock to move to Exclusive commit state, where (Pending) no more new read/write transactions (they have to wait in queue), the write transaction need to wait any other transactions still working to finish. Only after anything else cleared, this transaction enter into Exclusive state.

The ‘Exclusive’ state is to flush the modifications from cache to database file.

SQLConnection has a property cacheSize that affect write transaction. In ‘Reserved’ state if page cache fills up, it have to move to ‘Exclusive’ state before finish its own task. The bigger cacheSize the more modified pages can store and minimize the time to be used in ‘Exclusive’ state. (sqlite_analyzer can dump detailed statistics helpful for adjusting cacheSize)