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

TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.9.6.004. Contact Blog Owner