cfObjective(): SQLite Database Development for AIR with Raymond Camden

Ray did a good job at talking about SQLite and the basic information on SQLite. I have not used SQLite before and wasn't aware of some of it's functionality and it's limitations.

• Features
  • Typeless
    • You can enter characters into an integer field
• What's Missing?
  • Right outer join, full outer join
  • When altering tables only rename table and add column
  • Views are read only
  • No grant/revoke
  • No stored procedures
• What's Missing in AIR?
  • FK Constraints
  • SQLite_Version(), Match()
• SQLite In AIR
  • Support in both FLEX and HTML
  • Actionscript API
  • Connecting/Performing Questions/Table Analysis
  • Synchonous and Asynchronous
  • Encryption for "Sensitive" Data
• Getting Started
  • Creating a DB
  • SQLConnection.open()
  • SQLConnection.openAsync()
  • Any filename is valid (and any extension)
  • In memory database are supported
• Creating Tables
  • With SQL!
  • Create Table if not exists
  • Can also copy a "seed" db
  • Types: Integer, real, text, blob, null
  • Typeless: you can put text in integer
  • Column "Affinities" are used as hints
  • Affinities: Text, Numeric, Integer, real, Boolean, Date, XML, XMLList, Object, None
• Performing Queries
  • Uses SQLStatement class
  • Speicfy SQL, Parameters, Connection, events
  • Returns a SQLResult Class
  • Contains Complete, data
• Parameters
  • Performance, typing, security
  • Named
    • Values(:name, @rank)
  • Ordered
    • Values (?, ?)
• Error Handling
  • Uses SQLErrorEvent Class
  • Focus on:Connection issues, sql syntax, constraint errors
• Selecting With Class
  • Select results can be bound to ActionSCript Classes
  • Allows for Typed Results
• Transactions
  • Gives much better performance for multiple inserts/updates
• Paged Results
  • Allows you to paginate through large result sets
  • Stmt.execute(n)
  • Stmt.next(n)
• Encryption
  • Uses a key for connection
  • Must be done at creation!
  • Can't change your mind...
  • Keys can be changed (reencryption())
  • Encryptions keys are bytearray (16 bytes)
• Schema
  • Gives you access to tables, views, columns.
• Tips
  • Using a pre-populated DB
  • Use On SQLSTatement per action
  • LITA is your friend
• Air 2?
  • DB Transactions have save points