i OSSQLite CS 328 Dick Steflik Embedded Databases
i. OS/SQLite CS 328 Dick Steflik
Embedded Databases • SQLite is the embedded database of choice for both the i. OS 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 DB 2.
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
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
FMDB • Two main classes: • FMDatabase – represents a single SQLite database, used for executing SQL statements • FMResult. Set – represents the results of executing a query on an FMData. Base
Database Creation • Persistent and temporary databases can be created • Permanent at some file system path: FMDatabase * mydb = [FMDatabase database. With. Path: @"/var/mydb. db"]; • Empty database at a temp location, database is deleted when it is closed: FMDatabase * mydb = [FMDatabase database. With. Path: @""]; • In memory database created destroyed when database connection is closed: • FMDatabase * mydb = [FMDatabase database. With. Path: NULL];
Opening the Database • before you can use the database it must be opened. if (![db open]) { [db release]; return; } • fails – insufficient resources, permissions
Closing the Database • Close the FMDatabase connection when you are done with it. SQLite will then relinquish and resources it has acquired. [db close]
Executing Updates • [FMDatabase execute. Update] • use this method to execute all SQL statements except SELECT. • returns a bool, yes is a good return, no is not • Ex [db execute. Update: @"INSERT INTO mytable VALUES ( ? )"]
Select • [FMDatabase execute. Query] • Query results come back in an FMResult. Set • Ex FMResult. Set * s = [db execute. Query: @"SELECT * FROM mytable"]; while ([s next]) { // retrieve the values }
sqlite 3. 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 sqlite 3. h is excellent.
FMResult. Set data retrieval • • • int. For. Column long. Long. Int. For. Column bool. For. Column double. For. Column string. For. Column date. For. Column data. No. Copy. For. Column UTF 8 String. For. Column. Index object. For. Column
- Slides: 12