Transcript SQLite

Persistent Data
• We have used files already (Favorite Twitter
Searches)
• Core Data
• SQLite
• Can also access data from other apps
(photo, calendar, ..)
Core Data
• Object-Oriented data model framework to
build apps using MVC architecture
• Interface Builder provides pre-built Core
Data controller objects with a lot of code
already pre-written; uses SQLite underneath
but you do not have to write SQL code
SQLite
• Lightweight, powerful relational database engine
• Popular with people who already know SQL
• Data is stored in regular files but we use SQL
syntax to access and manipulate the data
• http://developer.apple.com/library/mac/#document
ation/Darwin/Reference/ManPages/man1/sqlite3.1
.html
SQLite
• Sqlite3 is a command line interface for
Sqlite version 3
• See apple doc at
http://developer.apple.com/library/mac/#doc
umentation/Darwin/Reference/ManPages/m
an1/sqlite3.1.html
• And also http://www.sqlite.org/
SQLite
• Old fashioned C library
• Pointer, pointer of pointer, address of
operator
• *, **, & notations
•  an NSString will have to be converted to
a char*
SQLite
• Create a database
• Create a table
• Insert, delete, update, select operations
Creating a database
• SQLite uses flat files (not B-trees)
• Similar to “finding” a file to write/append to
it
• Find a directory where we can write
• Define a file name for the database
• Create it
Creating a database
• NSArray *paths =
NSSearchPathForDirectoriesInDomain(
NSDocumentDirectory,
NSUserDomainMask, YES );
• NSString *doc = [paths objectAtIndex: 0];
• store full file path in an instance variable
named dbFile – we will need it in several
places
Creating a database
• dbFile = [doc
stringByAppendingPathComponent:
@”users.db”];
• NSFileManager *manager =
[NSFileManager defaultManager];
• if( [manager fileExistsAtPath: dbFile] )
• /* create database and table */
Creating a database
• sqlite3_open function: opens a sqlite database file
• int sqlite3_open( const char *filename,
sqlite3 **ppDb );
• Filename = same as dbFile, in C (need to convert
NSString to char *) – read only
• ppDb = pointer to a pointer to a sqlite3 variable;
sqlite3_open will write to it and create that
database handle
Creating a database
• sqlite3_open ‘s default behavior is to open a
database file for reading and writing
• If the file exists, it is open
• If the file does not exist, it is created
• sqlite3_open_v2 provides 2 more
parameters for more control (read only, ..)
Creating a database
• If successful, SQLITE_OK (i.e. 0) is
returned; otherwise some error code is
returned
• Can declare 2nd parameter as an instance
variable (used in many places)
• sqlite3 *db;
•  use &db as 2nd argument of sqlite3_open
Creating a database
• 1st parameter is of type char *  we need a
C like string
• In NSString class, we have the method:
• -(char *) UTF8String;
• It converts a NSString to a char *
• UTF8 = Unicode Transformation Format (8
bit)
Creating a database
• In NSString class, we have the method:
• -(char *) UTF8String;
const char *dbPath = [dbFile UTF8String];
if( sqlite3_open( dbPath, &db ) == SQLITE_OK )
{
// database is open
// create a table
}
Creating a table
• sqlite3_exec function
• int sqlite3_exec( sqlite3 *db,
const char *sql, int (*callback)
(void*,int,char**,char**), void *, char
**errmsg );
• db = an open database
• sql = sql statement to be executed
Creating a table
• 3rd parameter: Callback function
• 4th parameter: 1st argument to callback
function
• Error message to be written in 5th parameter
• Typically NULL, NULL, &error where we
declared error earlier:
• char *error;
Creating a table
const char *stmt = “create table users( id int,
name text, age int )”;
sqlite3_exec( db, stmt, NULL, NULL, &error
);
• // the table users has been created
sqlite3_close( db );
SQLite data types
• Not many data types are supported my
sqlite
• NULL, int, text, real, blob
For bool, use an int
For strings and dates/times, use text
For floats and doubles, use real
Check if successful
• Open a terminal window (inside Utilities
folder)
• Look for and find file path
• sqlite3 users.db
•  gives you an sql prompt
•  You are inside the users.db database
Check if successful
• .tables  (no ;) lists tables
• select * from users;
• .schema users  (no ;) show create
statement for users table
• .exit  (no ;) exits sqlite
Insert statement
• Use sqlite3_open to open the database
• Use sqlite3_exec to execute the insert statement
if( sqlite3_open( dbPath, &db ) == SQLITE_OK )
{
// make insert statement
// execute insert statement
}
Insert statement
NSString *insert = [NSString
stringWithFormat: @”insert into users
values( 1, ‘mike’, 24 )”];
// convert to char *
const char *stmt = [insert UTF8String];
// OR
char *stmt = “insert into users values( 2,
‘jane’, 22 )”;
Insert statement
// execute insert
sqlite3_exec( db, stmt, NULL, NULL, &error
);
sqlite3_close( );
Update and delete
•
•
•
•
Same as insert, just change the sql string
Open database using sqlite3_open
Make sql statement
Execute statement using sqlite3_exec
Select statement
• sqlite3_exec is actually a function that
combines 2 other functions:
• sqlite3_prepare (or sqlite3_prepare_v2)
• sqlite3_step
•  for select, we use sqlite3_prepare_v2
(version 2, more recent) and sqlite3_step
Select statement
• Use sqlite3_open to open the database
if( sqlite3_open( dbPath, &db ) ==
SQLITE_OK )
{
// make select statement
// execute select statement and process
results
}
Select statement
NSString *query = @”select * from users”;
const char *queryStmt = [query UTF8String];
• int sqlite3_prepare_v2( sqlite3 *db, const
char *zSql, int nByte, sqlite3_stmt
**ppStmt, const char **pzTail );
•  5 parameters
Select statement
• db = database handle
• zSql = sql query ( a string)
• nByte  zSql string read to the nByte-th byte or
\000, or \u0000, whichever comes first
• ppStmt = a statement handle (to be written to by
function sqlite3_prepare_v2)  will be used by
sqlite3_step function
• pzTail = remaining (unused) portion of sql string
Select statement
• 3rd and 5th argument: if nByte is < 0 (-1 for
example)  whole sql string is used, pass
NULL for pzTail
• 4th argument: Need to declare a sqlite3_stmt
* variable (named stmt for example) and
pass its address (as in &stmt)
Select statement
if( sqlite3_prepare_v2( db, queryStmt, -1,
&stmt, NULL ) == SQLITE_OK )
{
// process the results one row at a time
// use sqlite3_step function
}
// SQLITE_OK’s value is 0
Select statement
• int sqlite3_step( sqlite3_stmt *stmt )
• Returns SQLITE_ROW if some data is
returned
• SQLITE_ROW’s value is 100
•  can use while loop to process rows
Select statement
while(sqlite3_step( stmt ) == SQLITE_ROW)
{
// process current row
// retrieve values at various columns
// column index starts at 0
}
Select statement
• Use the function
• dataType sqlite3_column_dataType(
sqlite3_stmt *stmt, int columnIndex )
• dataType = blob, text, double, int, ..
Select statement
char *nameChars = (char *)
sqlite3_column_text( stmt, 1 );
int age = sqlite3_column_int( stmt, 2 );
NSString *name = [ NSString
stringWithUTFString: nameChars ];
•  do something with name and age