Beginning SQLite Development for AIR

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

125 views

Beginning SQLite
Development for
AIR
Raymond Camden
ray@camdenfamily.com
@cfjedimaster
1
Who am I?
Architect for FirstComp
Long time ColdFusion User
AIR Enthusiast
2
What is SQLite?
"Self-Contained, Serverless, Zero-
configuration, transactional SQL Database
Engine" -sqlite.org
Most widely deployed SQL database engine
Open Source
3
SQLite Features
Autonumber PKs
Typeless (Integer, Real, Text, Blob, Null)
"Most" of the Standard SQL
Triggers, Transactions, Aggregate
Functions, Date Functions, Views
4
What's Missing?
Right Outer Join, Full Outer Join
When altering tables, only Rename Table/
Add Column
Views are Read Only
No Grant/Revoke (due to being file based)
No Stored Procedures
5
Balance
6
Support in both Flex and HTML (Obvious...)
ActionScript API
Connecting/Performing Queries/Table
Analysis
Synchronous and Asynchronous
Encryption for “Sensitive” Data
SQLite in AIR
7
Quick Demo
8
Getting Started
Creating a Database
SQLConnection.open()
SQLConnection.openAsync()
Any filename is valid (and any extension)
In memory databases are supported
9
Example
AirDB1
10
Creating Tables
With SQL!
CREATE TABLE IF NOT EXISTS
Can also copy a “Seed” DB
11
Creating Tables (2)
Types: Integer, Real, Text, Blob, Null
Typeless: You can put text into an integer
column
Column "Affinities" are used as hints
Affinities: TEXT, NUMERIC, INTEGER, REAL,
BOOLEAN, DATE, XML, XMLLIST, OBJECT,
NONE
12
Example
AirDB2, AIRDB2a
13
Performing Queries
Uses
SQLStatement
class
Specify SQL, Parameters, Connection,
Events
14
Example
AIRDB3
15
Parameters
Performance, Typing, Security
Named
values(:name,:rank)
Ordered
values(?, ?)
16
Example
AIRDB4
17
Error Handling
Uses
SQLErrorEvent
Class
Focuses on: connection issues, sql syntax,
constraint errors
18
Example
AIRDB5
19
Selecting with Class
Select results can be bound to ActionScript
classes
Allows for typed results
20
Example
AIRDB6
21
Paged Results
Allows you to paginate through large
result sets
stmt.execute(n)
stmt.next(n)
22
Example
AIRDB7
23
Encryption
Uses a key for connection
Must be done at
creation!
Keys can be changed
Encryption Keys are ByteArray (16 bytes)
24
Example
AIRDB10
25
Tips
Using a pre-populated DB (Hangman
Example)
Use one
SQLStatement
per action
Lita is your friend
Paul Robertson's Optimization Preso:
http://tinyurl.com/yb53mya
26
AIR 2?
Database Transaction Savepoints
27
Questions?
28