iOS/SQLite

1
iOS/SQLite

• CS328
• Dick Steflik

2
Embedded Databases

• SQLite is the embedded database of choice for
  both the iOS and Android mobile platforms
• SQLite is called an embedded database because its
  primary use is to provide data persistence to
  your app, it is an integral part of your app.
• Apps do not share Embedded Databases the way they
  share databases like Oracle and DB2.

3
Application Access to SQLite

• Core Data
• an Apple provided Object-Relational Mapping
  framework
• stores Objective-C objects into a SQLite Database
• object instance data looks like a row in an SQL
  table.
• Flying Meat Database (FMDB)
• FMDB is a set of Objective C wrappers for SQLite
• let you program using embedded SQL
• modeled after Java JDBC

4
Core Data

• Benefits
• supported by Apple
• don't need to know SQL
• Drawbacks
• does not work with RDBMS as the store
• ordered relationships are hard
• undo/rollback doesn't always work
• data migration to a revised model is hard

5
FMDB

• Two main classes
• FMDatabase
• represents a single SQLite database, used for
  executing SQL statements
• FMResultSet
• represents the results of executing a query on an
  FMDataBase

6
Database Creation

• Persistent and temporary databases can be created
• Permanent at some file system pathFMDatabase
  mydb FMDatabase databaseWithPath_at_"/var/mydb.db
  "
• Empty database at a temp location, database is
  deleted when it is closedFMDatabase mydb
  FMDatabase databaseWithPath_at_""
• In memory database created destroyed when
  database connection is closed
• FMDatabase mydb FMDatabase
  databaseWithPathNULL

7
Opening the Database

• before you can use the database it must be
  opened. if (!db open) db release
  return
• fails insufficient resources, permissions

8
Closing the Database

• Close the FMDatabase connection when you are done
  with it. SQLite will then relinquish and
  resources it has acquired.db close

9
Executing Updates

• FMDatabase executeUpdate
• use this method to execute all SQL statements
  except SELECT.
• returns a bool, yes is a good return, no is not
• Exdb executeUpdate_at_"INSERT INTO mytable VALUES
  ( ?)"

10
Select

• FMDatabase executeQuery
• Query results come back in an FMResultSet
• ExFMResultSet s db executeQuery_at_"SELECT
  FROM mytable"while (s next)
  // retrieve the values

11
sqlite3.h

• This .h file is on your MAC where you installed
  SQLite, it is the C language interface to SQLite.
  It can be used in place of FMDB.
• The documentation in sqlite3.h is excellent.

12
FMResultSet data retrieval

• intForColumn
• longForColumn
• longLongIntForColumn
• boolForColumn
• doubleForColumn
• stringForColumn
• dateForColumn
• dataForColumn
• dataNoCopyForColumn
• UTF8StringForColumnIndex
• objectForColumn