An app with a database

The view controller creates an SQLite database file named Documents/database.db in the app’s home directory, creates a table named people in the file, and creates records in the table. It then reads the records from the table, and displays them in an UITextView.

Source code in Base.zip

Create the project

The view controller has an instance variable of type sqlite3 that points to the database. ViewController.h must therefore #import the header file /usr/include/sqlite3.h in order to mention the name of this data type. If sqlite3.h is not in the /usr/include directory, you can search for it with the following command in Terminal.

find / -type f -name sqlite3.h 2> /dev/null &

Add the dynamic library libsqlite3.dylib to your project. See Gone for adding a framework or library to a project.

The gory details

The SQLite functions are plain old C functions (like the main function), not Objective-C methods. The strings passed to these functions must be plain old C strings, not Objective-C NSString objects. That’s why there is no @ in front of the "double quotes", and why we format them with %s instead of %@.

The initWithNibName:bundle: method of the view controller

fileName is an NSString containing the full pathname of the database file. But the function that creates the file, sqlite3_open, is a C function that will not accept an Objective-C NSString. We have to convert fileName to a C string by calling the method UTF8String.

Once the database file has been created, call the function sqlite3_exec to execute an SQL statement such as the ones that we typed interactively into sqlite3 in Terminal.app. The SQL statements we execute are drop table, create table, and insert.

We saw a C array of structures in Manhattan.

The C function foundRecord

The function foundRecord is called each time the select statement finds a record that it’s looking for. foundRecord has to be defined as a plain old C function, not an Objective-C method, because sqlite3_exec is a C function that will accept only C functions. The first argument of foundRecord is a pointer to the UITextView. Since foundRecord is a C function, the pointer has to be passed as a “pointer to void”.

Dump the database file

After the app has created the database file, you can examine the file on your Mac. To see the names of all the apps in the simulator, open the Terminal app and type the following commands, changing myname and 5.0 as necessary. The 'single quotes' are needed because of the space in the name.

cd '/Users/myname/Library/Application Support/iPhone Simulator/5.0/Applications'
pwd

ls -ld */*.app
drwxr-xr-x  8 myname  mygroup  272 Apr 15 13:12 3050CE6C-C4E2-4091-984C-96D4CFB6503A/Button.app
drwxr-xr-x  6 myname  mygroup  204 Apr 15 12:19 A78EF428-B108-4D42-BD24-8DF3153F7235/Sine.app
drwxr-xr-x  6 myname  mygroup  204 Apr 15 12:02 E4677A97-66EF-46E5-A7C8-938D4A61C585/Pong.app
drwxr-xr-x  5 myname  mygroup  170 Apr 15 13:51 FF284904-6B85-4BB3-BB26-F3784C66B60C/Base.app

find / -type f -name database.db 2> /dev/null
/Users/myname/Library/Application Support/iPhone Simulator/5.0/Applications/FF284904-6B85-4BB3-BB26-F3784C66B60C/Documents/database.db

Write `back quotes` within the "double quotes". The .dump is an SQLite “meta-command”; see the man page.

sqlite3 "`find / -type f -name database.db 2> /dev/null`" .dump

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE people (
   lastname text,
   firstname text,
   ss integer
);
INSERT INTO 'people' VALUES('Ali','Gamal',1);
INSERT INTO 'people' VALUES('Bass','Drew',2);
INSERT INTO 'people' VALUES('Basu','Piyal',3);
INSERT INTO 'people' VALUES('Caraballo','Zimri',4);
INSERT INTO 'people' VALUES('Ciano','Dominick',5);
INSERT INTO 'people' VALUES('Compas','Jean Michael',6);
INSERT INTO 'people' VALUES('Fong','Matthew A.',7);
INSERT INTO 'people' VALUES('Giraud','Frederique',8);
INSERT INTO 'people' VALUES('Grubb','Michael Alexander',9);
INSERT INTO 'people' VALUES('Guglielmo','Lino',10);
INSERT INTO 'people' VALUES('Gupta','Anita',11);
INSERT INTO 'people' VALUES('Haughwout','James',12);
INSERT INTO 'people' VALUES('Krieger','Rob',13);
INSERT INTO 'people' VALUES('McConnel','Nick',14);
INSERT INTO 'people' VALUES('Meretzky','Mark',15);
INSERT INTO 'people' VALUES('Nitz','Ryan G.',16);
INSERT INTO 'people' VALUES('Nunez','Axel',17);
INSERT INTO 'people' VALUES('Poon','Ho-Ling',18);
INSERT INTO 'people' VALUES('Prior','Julia',19);
INSERT INTO 'people' VALUES('Ristea','Dan',20}
COMMIT;

Things to try

Run the app once and close it. Then comment out the calls to sqlite3_exec that execute the drop and create table. Then run the app again. What do you see now?

In the initWithNibName:bundle: method of class BaseAppDelegate, change

	"select * from people;"

to any one of the following. The % is a wildcard.

	"select * from people where firstname == 'Dan';"
	"select * from people where firstname like '%Michael%';"
	"select * from people order by firstname;"
	"select * from people where firstname like '%Michael%' order by firstname;"
	"select * from people where ss % 2 == 0 order by ss desc;"

Display the records in an HTML TABLE in a UIWebView (Smoky), or in a UITableView (States) instead of in a UITableView.

If you are displaying the records in an HTML TABLE in a UIWebView, make the table as wide as possible. Change
```
"<TABLE BORDER ALIGN = "CENTER">\n"
```
to
```
"<TABLE BORDER ALIGN = "CENTER" WIDTH = "100%">\n"
```

If you are displaying the records in an HTML TABLE in a UIWebView, make the margins as narrow as possible. Change
```
"<BODY>\n"
```
to
```
"<BODY STYLE = \"margin: 1px;\">\n"
```

If you are displaying the records in an HTML TABLE in a UIWebView, right-justify the numbers in column 3 of the HTML table. (Since the sqlite column numbers start at zero, it’s officially column 2.) The HTML for a TD that holds a number will have to look like this:
```
<TD ALIGN = "RIGHT">1</TD>
```
In the foundRecord function in the file BaseViewController.m, change, you probably have a statement that appends text to an NSString that is accumulating the HTML for the table.
```
	[html appendFormat: @"<TD>%s</TD>\n", argv[i]];
```
Change it to the following.
```
	[html appendFormat: @"<TD%@>%s</TD>\n",
		i == 2 ? @" ALIGN = \"RIGHT\"" : @"",
		argv[i]
	];
```

Invent a class Model to hold the database.