http://www.iphonedevsdk.com/forum/iphone-sdk-development/6573-howto-customize-uikeyboard.html?0f266420

http://cocoawithlove.com/2009/04/showing-message-over-iphone-keyboard.html

http://www.iphonedevsdk.com/forum/iphone-sdk-tutorials/7350-adding-subviews-custimize-keyboard.html

http://www.ipodtouchfans.com/forums/archive/index.php/t-176944.html

http://www.ipodtouchfans.com/forums/archive/index.php/t-173786.html

http://www.ipodtouchfans.com/forums/archive/index.php/t-168191.html

iPhone SDK Tutorial: Reading data from a SQLite Database

I see many people asking for SQLite tutorials around, and since I am using SQLite for the next part in the Advanced RSS Reader Tutorial, I thought I would write up a quick tutorial on using SQLite with the iPhone SDK.

1. Project Requirements

I suggest that you have at least a basic understanding of SQLite, writing SQL statements, the XCode interface and using the terminal in OSX. If you don’t know anything about any of these topics then this tutorial probably isn’t for you.

2. Creating our SQLite database for our tutorial

We first need to create a database for use with our application. For the purposes of this tutorial we will be building a database of animals along with a little information on them and a picture.

Fire up a new Terminal window and make a new folder to store the database in, here are the commands I ran

cd /Users/lookaflyingdonkey/Documents
mkdir SQLiteTutorial
cd SQLiteTutorial
sqlite3 AnimalDatabase.sql

You should now be at a “sqlite” command prompt, this is where we will be building our database structure and entering some test data.

For our example we need the name of the animal, a short description and a link to an image. Follow the commands below to create the table and to enter some sample data.

CREATE TABLE animals ( id INTEGER PRIMARY KEY, name VARCHAR(50), description TEXT, image VARCHAR(255) );

INSERT INTO animals (name, description, image) VALUES ('Elephant', 'The elephant is a very large animal that lives in Africa and Asia', 'http://dblog.com.au/wp-content/elephant.jpg');
INSERT INTO animals (name, description, image) VALUES ('Monkey', 'Monkies can be VERY naughty and often steal clothing from unsuspecting tourists', 'http://dblog.com.au/wp-content/monkey.jpg');
INSERT INTO animals (name, description, image) VALUES ('Galah', 'Galahs are a wonderful bird and they make a great pet (I should know, I have one)', 'http://dblog.com.au/wp-content/galah.jpg');
INSERT INTO animals (name, description, image) VALUES ('Kangaroo', 'Well I had to add the Kangaroo as they are the essence of the Australian image', 'http://dblog.com.au/wp-content/kangaroo.jpg');

The first command will create the table with the required structure and the next four will insert some test data for us to work with. To ensure that you have entered the data correctly you can execute “SELECT * FROM animals;” and see if it returns the items above. Once you are confident that everything had been created successfully you can leave the sqlite command line by typing “.quit”.

3. Creating our Project

Now that our database is all ready to go we need to setup our X-Code project.

Start off by creating a new “Navigation-Based Application”.

Give your Project a name, I called mine “SQLiteTutorial”.

Now set your screen layout to how you prefer it, I suggest making the window as large as possible, and making the code view as tall as possible by dragging the horizontal slider to the top. This will allow you the most room to move when building your application.

Now its time to create the required classes and views for our application, we will start off by making our views.

Right Click on the “Resources” folder in the left hand pane and click “Add File”, we want to create a new “View XIB” under the “User Interfaces” group.

We now need to give it a name, to stick the Apple’s naming conventions we are going to call it “AnimalViewController.xib”, Now Click “Finish”.

Now we need to create two classes, the first one will represent an animal, right click on the “Classes” folder in the left hand pane, click “Add > New File…”, choose the “NSObject subclass” template under the “Cocoa Touch Classes” group and name it “Animal”.

The second class will be for our AnimalsViewController, right click on the “Classes” folder in the left hand pane, click “Add > New File…”, choose the “UIViewController subclass” under the “Cocoa Touch Classes” group and name it “AnimalViewController”.

4. Adding SQLite Framework and our Animal Database

Now that we have created all of our views and classes it is time to start the real grunt work.

First off we need to include the SQLite libraries so our application can utilise them. To do this you will need to right click on the “Frameworks” folder in the left hand pane, then click on “Add > Existing Frameworks…”, then navigate to “/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.0.sdk/usr/lib/” and double click the “libsqlite3.0.dylib” file. A popup will appear, just click “Add” and the library will be added to your project.

We also need to add our database we created earlier to the Resources folder, to do this simply right click on the “Resources” folder, click “Add > Existing Files…”, navigate to the location you created the database in then double click on the AnimalDatabase.sql file. Another popup will appear, just click add.

All done with the importing, time to code!

5. The Coding begins!

We are going to start the coding by building our “Animal” object, every animal will have 3 properties, a name, a description and an image URL.

Open up the “Animal.h” file from the “Classes” folder and edit its contents to look like below,

#import 

@interface Animal : NSObject {
 NSString *name;
 NSString *description;
 NSString *imageURL;
}

@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *description;
@property (nonatomic, retain) NSString *imageURL;

-(id)initWithName:(NSString *)n description:(NSString *)d url:(NSString *)u;

@end

Most of the above code should be pretty familiar to you, the only thing that may not be is the initWithName line, this line will allow us to create a new object with the required data, we could have used the default init function, but it will be easier for us to define our own.

Now we will actually have to implement the Animal Object, open up the “Animal.m” file and edit its contents to look like below:

#import "Animal.h"

@implementation Animal
@synthesize name, description, imageURL;

-(id)initWithName:(NSString *)n description:(NSString *)d url:(NSString *)u {
 self.name = n;
 self.description = d;
 self.imageURL = u;
 return self;
}
@end

The above code should be pretty easy to read as well, it basically stores the supplied data from the initWithName function and return the object (self).

Now its time to setup the Application delegate to access the database.

Open up the “SQLiteTutorialAppDelegate.h” and edit its contents to look like below:

#import 
#import  // Import the SQLite database framework

@interface SQLiteTutorialAppDelegate : NSObject  {

   UIWindow *window;
   UINavigationController *navigationController;

 // Database variables
 NSString *databaseName;
 NSString *databasePath;

 // Array to store the animal objects
 NSMutableArray *animals;
}

@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet UINavigationController *navigationController;
@property (nonatomic, retain) NSMutableArray *animals;

@end

What we are doing here is importing the SQLite database framework and creating some variables for storing the database details and an array of animal objects.

Now open up the “SQLiteTutorialAppDelegate.m” file and edit its contents to look like below:

#import "SQLiteTutorialAppDelegate.h"
#import "RootViewController.h"
#import "Animal.h" // Import the animal object header

@implementation SQLiteTutorialAppDelegate

@synthesize window;
@synthesize navigationController;
@synthesize animals; // Synthesize the aminals array

- (void)applicationDidFinishLaunching:(UIApplication *)application {
 // Setup some globals
 databaseName = @"AnimalDatabase.sql";

 // Get the path to the documents directory and append the databaseName
 NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
 NSString *documentsDir = [documentPaths objectAtIndex:0];
 databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

 // Execute the "checkAndCreateDatabase" function
 [self checkAndCreateDatabase];

 // Query the database for all animal records and construct the "animals" array
 [self readAnimalsFromDatabase];

 // Configure and show the window
 [window addSubview:[navigationController view]];
 [window makeKeyAndVisible];
}

- (void)applicationWillTerminate:(UIApplication *)application {
 // Save data if appropriate
}

- (void)dealloc {
 [animals release];
 [navigationController release];
 [window release];
 [super dealloc];
}

-(void) checkAndCreateDatabase{
 // Check if the SQL database has already been saved to the users phone, if not then copy it over
 BOOL success;

 // Create a FileManager object, we will use this to check the status
 // of the database and to copy it over if required
 NSFileManager *fileManager = [NSFileManager defaultManager];

 // Check if the database has already been created in the users filesystem
 success = [fileManager fileExistsAtPath:databasePath];

 // If the database already exists then return without doing anything
 if(success) return;

 // If not then proceed to copy the database from the application to the users filesystem

 // Get the path to the database in the application package
 NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

 // Copy the database from the package to the users filesystem
 [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];

 [fileManager release];
}

-(void) readAnimalsFromDatabase {
 // Setup the database object
 sqlite3 *database;

 // Init the animals Array
 animals = [[NSMutableArray alloc] init];

 // Open the database from the users filessytem
 if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
  // Setup the SQL Statement and compile it for faster access
  const char *sqlStatement = "select * from animals";
  sqlite3_stmt *compiledStatement;
  if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
   // Loop through the results and add them to the feeds array
   while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
    // Read the data from the result row
    NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
    NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
    NSString *aImageUrl = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];

    // Create a new animal object with the data from the database
    Animal *animal = [[Animal alloc] initWithName:aName description:aDescription url:aImageUrl];

    // Add the animal object to the animals Array
    [animals addObject:animal];

    [animal release];
   }
  }
  // Release the compiled statement from memory
  sqlite3_finalize(compiledStatement);

 }
 sqlite3_close(database);

}

@end

Now I know that may look like a fair bit of code and it probably also looks quite scary! But really it is quite simple and I have tried to comment nearly every line to describe to you what the line does and why it is there.

The checkAndCreateDatabase function checks to see if we have already copied our database from the application bundle to the users filesystem (in their documents folder), if the database hasn’t already been created or it has been removed for some reason it will be recreated from the default database.

Next the readAnimalsFromDatabase function will make a connection to the database that is stored in the users documents folder, and then executes the SQL statement “SELECT * FROM animals”. It will then go through each row that is returned and it will extract the name, description and imageURL from the result and build an Animal object for each. You will see the “sqlite3_column_text” function used here, there are many more of these for returning other field types such as “sqlite3_column_int” for integers, “sqlite3_column_blob” for blobs or “sqlite3_column_value” to get an unknown value.

Now that we have the data in our array and we have it in our known format we are ready to start displaying it.

Open up the “RootViewController.m” file and edit the numberOfRowsInSection to look like the following:

SQLiteTutorialAppDelegate *appDelegate = (SQLiteTutorialAppDelegate *)[[UIApplication sharedApplication] delegate];
return appDelegate.animals.count;

What this does is it creates a link to the application delegate, and then the second line returns the size f the animals array in out Application delegate, this array was filled previously from the SQLite database.

Now in the cellForRowAtIndexPath function you will need at change it to look like the following:

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

   static NSString *CellIdentifier = @"Cell";

   UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
   if (cell == nil) {
       cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];
   }

   // Set up the cell
   SQLiteTutorialAppDelegate *appDelegate = (SQLiteTutorialAppDelegate *)[[UIApplication sharedApplication] delegate];
 Animal *animal = (Animal *)[appDelegate.animals objectAtIndex:indexPath.row];

 [cell setText:animal.name];
 return cell;
}

We pretty much just added 3 lines under the “// Set up the cell” line, the first one is the same as we added previously to access the application delegate. The second line creates a new Animal object based on the array from the application delegate, it will be used to create a row for each individual record in the database. On the final line we are just setting the text of the cell to the name field from the Animal object.

You can now run the program and you should see a table view with the 4 animals we added to the database, if you added more than my default animals you should see them in here as well.

We will now setup the AnimalViewController, open up the “AnimalViewController.h” file and edit its contents to below:

#import 

@interface AnimalViewController : UIViewController {
 IBOutlet UITextView *animalDesciption;
 IBOutlet UIImageView *animalImage;
}

@property (nonatomic, retain) IBOutlet UITextView *animalDesciption;
@property (nonatomic, retain) IBOutlet UIImageView *animalImage;

@end

What we are doing above is adding an outlet for the description and image for the Animal, we will use these later on when we link the view up.

Now open up the “AnimalViewController.m” file and add a synthesize call for for the description and image, this will go under the “@implementation AnimalViewController” line, like so:

#import "AnimalViewController.h"

@implementation AnimalViewController

@synthesize animalDesciption, animalImage;

Now it is time to make the detailed view page appear when you select a record. Open up the “AnimalViewController.xib” file from the resources folder and the interface builder should appear.

The first thing we need to do is to set the File’s Owner Class to AnimalViewController, this is done by selecting the “File’s Owner” item in the main window and then clicking Tools > Identity Inspector in the top menu, and then selecting AnimalViewController from the class dropdown.

Your inspector window should now look like this:

We are going to be using a UITextView for the description (as it will allow for word wrapping and scrolling in the case that the description is quite large) and a UIImageView to display the image. I have laid mine out like below:

Now that we have everything laid out it is time to link them all up, start by holding control and click+drag from the “File’s Owner” to the “View” objects, a little gray menu will appear and you will need to select view. Now hold control and click+drag from the “File’s Owner” to the UITextView in the layout window, you should see “animalDescription” in the popup list, select it. Repeat this process for the UIImageView and you should see animalImage appear, select it also.

Now save the interface and close the interface builder.

Nearly done! All we have to do now is to setup the code for when a user presses on a record in the table view.

Open up the “RootViewController.h” file and edit its contents to below:

#import 
#import "AnimalViewController.h"

@interface RootViewController : UITableViewController {
 AnimalViewController *animalView;
}

@property(nonatomic, retain) AnimalViewController *animalView;

@end

We are creating an instance of the AnimalViewController to be used bu the RootViewController when a user presses on an item.

Now open up the “RootViewController.m” file and edit the top part of the file to look like below:

#import "RootViewController.h"
#import "SQLiteTutorialAppDelegate.h"
#import "Animal.h"

@implementation RootViewController
@synthesize animalView;

This will just synthesize the animalView that we just added.

First up lets set the default title of our view, to do this you need to uncomment the viewDidLoad function, and edit it to below:

- (void)viewDidLoad {
   [super viewDidLoad];
   // Uncomment the following line to add the Edit button to the navigation bar.
   // self.navigationItem.rightBarButtonItem = self.editButtonItem;

 self.title = @"My Zoo";
}

We also need to edit the didSelectRowAtIndexPath

function in this file, edit it to look like below:

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
   // Navigation logic -- create and push a new view controller
 SQLiteTutorialAppDelegate *appDelegate = (SQLiteTutorialAppDelegate *)[[UIApplication sharedApplication] delegate];
 Animal *animal = (Animal *)[appDelegate.animals objectAtIndex:indexPath.row];

 if(self.animalView == nil) {
  AnimalViewController *viewController = [[AnimalViewController alloc] initWithNibName:@"AnimalViewController" bundle:nil];
  self.animalView = viewController;
  [viewController release];
 }

 // Setup the animation
 [self.navigationController pushViewController:self.animalView animated:YES];
 // Set the title of the view to the animal's name
 self.animalView.title = [animal name];
 // Set the description field to the animals description
 [self.animalView.animalDesciption setText:[animal description]];
 // Load the animals image into a NSData boject and then assign it to the UIImageView
 NSData *imageData = [NSData dataWithContentsOfURL:[NSURL URLWithString:[animal imageURL]]];
 UIImage *animalImage = [[UIImage alloc] initWithData:imageData cache:YES];
 self.animalView.animalImage.image = animalImage;

}

What we are doing here is checking to see if the animalView object has already been created, if not then create it.

The next few lines are used to setup the animation (slide from right to left) and to set the actual data fields to those of the selected animal.

Now you should be ready to fire up the application and see it in all its glory.

You should see your windows looking like below.

5. Project Files

Here are the source files for this project: Download the project source files

If you have any questions or comments please feel free to post them here and I will try to get back to you ASAP. Also keep your eye out for my second part to the advanced RSS Reader tutorial, it will be using TouchXML, SQLite, UIWebview and more!

original post is here

http://dblog.com.au/iphone-development-tutorials/iphone-sdk-tutorial-reading-data-from-a-sqlite-database/comment-page-3/#comment-3095

iPhone SQLite Database Basics - Part 2

After writing the article on iPhone SQLite Database Basics, I received lots of followup questions and comments, some of which I will discuss here.

SQLite Database Creation

In my first article I wrote that the easiest way to work with SQLite databases on the mac was the SQLite extension for Firefox.

Since then, my attention has been brought to Menial’s Base application. This is the application that I now use for all of my SQLite work. It is super fast, and has an interface that is extremely easy to use. It also has a lot of nice touches that make it more Mac-like than using a Firefox extension. If you’re doing SQLite work on the Mac, I strongly recommend that you check it out.

Making a Writable Copy of the Database

In the first part of this tutorial, I mentioned that if you needed to write to your database you needed to copy it to a location that you could write to.

A couple of people have asked how to do this. To answer that, I first want to talk a little bit about how to control SQLite access in Cocoa applications. There are really three ways to do this:

1. Have DB access code in each class that needs it (bad)
2. Have you application delegate handle all DB access for you (better)
3. Use a singleton object for your DB access (best)

For more about the tradeoffs between application delegate usage and singleton objects see this post from Cocoa with Love.

If you are using the singleton pattern, then you can make a writable copy of your database in your singleton object’s allocation method, using something like this:

1. -(NSString*)createWriteableDB
2. {
3. // See if writeable database already exists
4. NSFileManager *fileManager = [NSFileManager defaultManager];
5. NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
6. NSString *documentsDirectory = [paths objectAtIndex:0];




7. NSString *readOnlyDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseFileName];
8. NSString *writableDBPath = [[documentsDirectory stringByAppendingPathComponent:self.databaseFileName] retain];




9. // First time install
10. if(![fileManager fileExistsAtPath:writableDBPath])
11. {
12. if(![fileManager copyItemAtPath:readOnlyDBPath toPath:writableDBPath error:&self.lastError])
13. {
14. NSAssert1(0, @”Failed to create writable database file with message ‘%@’.”, [self.lastError localizedDescription]);
15. return nil;
16. }
17. }




18. return [writableDBPath autorelease];
19. }

A couple of important things to note here:

If you have a very large database and the things that you need to allow the user to update, (i.e. bookmarks, or annotations) are small in comparison, it may be better for you to have two databases files, one that contains the data that won’t change, and the other that contains the data used for annotations. This way you won’t have two large copies of the database on the user’s device.

This will of course have a large impact on your query design, and some database purists may be lining up to lynch me, but as I mentioned in the first article, there are some SQLite considerations that are unique to mobile devices like the iPhone.

Another thing to consider is versioning. You need to be very careful how you handle application updates that change the database structure and/or content when you are allowing the user to write data (that presumably they want to persist between updates).

One way to handle this is to simply have a table in your writable database called “Version” which contains one row and one column with the version number of the database schema.

Then in the above code you can add some logic to transfer the data from a pervious schema version to the new writable database file.

Third Party SQLite Wrappers

There are lots of SQLite wrappers available for iPhone development. I’ve never used any so I can’t say if any one is better than another. If the idea of using c-based SQLite calls makes you anxious, you may want to check them out.

Security

Someone suggested that I point out that SQLite databases on the iPhone aren’t really secure. I’m not a database security expert, but that statement is true of databases on any platform. The database is only as secure as the device itself. If someone gets ahold of your iPhone, they can most likely get the data off of it.

iPhone SQLite Database Basics

In celebration of the lifting of the NDA on the iPhone SDK, I decided to write a tutorial on using SQLite on the iPhone.

This tutorial covers the basics of SQLite database creation, things to consider in the design of the database, and some particular things to be aware of when deploying these types of applications to the iPhone.

Once you have finished with these basics, take a look at the SQLiteBooks sample code from the iPhone dev center for details on how to interface with the database from your code.

Step 1: Create a SQLite Database File

There are lots of ways to create a SQLite database file. The easiest way that I have found is to download the SQLite Firefox extension, which provides a GUI to create and manage your SQLite database. The rest of this tutorial will assume you have that extension installed.

In this tutorial we’ll be creating a recipe application, so we are naming our database “recipes”.

To create the database:

1. Click the “New Database” toolbar button.
2. Enter the name of the database file.
3. Click OK.

You will then be prompted for a location to save the database. The ideal place is in the directory of your XCode project.

Step 2: Create the Database Schema

Now we are going to setup our database tables. This will be a very simple database with two tables:

There are entire textbooks about “proper” database design and if you are developing a super large-scale ultra robust data warehouse app, you might want to look them over. However rather than make your eyes glaze over with the merits of third normal form, let me just share a few guidelines that I picked up during several years as a DBA/Database application developer:

1. Every table should mirror an object, with each non-calculated object property mapping directly to a table field.
2. In addition to the object properties, every table should have an integer field that is autofilled with a unique identifier (commonly know as the primary key).
3. Every table that is dependent on a parent table should have a way to refer back to its parent. (This is sometimes called a Foreign Key)

In our application we’re going to have two model objects. A Recipe object, and an Ingredient object. Each recipe object can have 1 or more ingredient child objects. (The blue arrow in the above diagram signifies this, and is called a 1-to many relationship).

The Recipe object has a name and description property. These are both strings, and these map to database fields of type VarChar (meaning Variable length Character string). In addition, the Recipes table has a Recipe_ID field of type Integer. This field is the primary key of the Recipes table.

The ingredient object in our simple application only has one property, Description. Since this is a string it also maps to a VarChar field. Our Ingredients table also has a primary key (Ingredient_ID) which gives us a way to refer to a specific ingredient.

Finally, the Recipe_ID field in the Ingredients table is our foreign key back to the Recipes table. Using that key you can look at any ingredient and know what Recipe it belongs to. More commonly an application will use that key to get all of the ingredients that belong to a specific recipe.

To create the Recipes table, click the “New Table” toolbar button and enter the information as seen below:

Notice that we check the Primary Key and Autoinc checkboxes for the Recipe_ID field. The Primary Key checkbox tells SQLite to treat this field as a Unique Index, meaning that it can guarantee that the values in this field won’t be duplicated and should be kept sorted for faster queries. (Later we’ll talk a little more about optimizing queries by adding additional table indices.)

The Autoinc checkbox tells SQLite how to handle the automatic creation of primary key values. Without autoinc being checked, when a new row is added SQLite sets the primary key to one greater than the current highest primary key. With the box checked, SQLite sets the primary key to one greater than the highest primary key that the table has every had. (Which it keeps track of in its own table called sqlite_sequence). How you set this box will depend on how you want to handle deleted records.

For the Ingredients table, it is much the same:

Notice that we do NOT mark the Recipe_ID field of the Ingredients table as a primary key. It is the primary key of the Recipes table, not of this table. (In this table some people would call the Recipe_ID field the foreign key).

The last thing that we want to do regarding our database schema is to create an index on the Recipe_ID field of the Ingredients table. Since most of our queries that involve this table will involve looking up a specific value in the Recipe_ID field, it will speed things up to have an index on this field. (Note: You really won’t notice a significant speed increase unless your Ingredients table has a lot of records, but since it is a good design principle, we’re going to do it anyway.)

Finally you need to populate the table with some data. You can do that using the SQLite Manager by selecting the table and then clicking the “Add new Record” button, or by selecting the “Execute SQL” tab and using several Insert statements.

For example to insert this data into my Recipes table:

Recipe_ID	Name	Description
1	Omelet	A delicious and eggy breakfast.
2	Grilled Cheese Sandwich	A delicious and cheese filled lunch.
3	Pizza	A classic dinner from the old world.

I could use these three insert statements:

INSERT into Recipes(Name, Description) VALUES(’Omelet’, ‘A delicious and eggy breakfast.’);

INSERT into Recipes(Name, Description) VALUES(’Grilled Cheese Sandwich’, ‘A delicious and cheese filled lunch.’);

INSERT into Recipes(Name, Description) VALUES(’Pizza’, ‘A classic dinner from the old world.’);

Notice that I did not insert the values for the Recipe_ID field. As I mentioned above, since it is the primary key the database calculates those values automatically.

Next comes the Ingredients table:

Ingredient_ID	Recipe_ID	Description
1	1	Eggs
2	1	Water
3	1	Salt
4	2	Bread
5	2	Cheese
6	2	Butter
7	3	Pizza Dough
8	3	Pizza Sauce
9	3	Cheese
10	3	Toppings

Which I could use the following statements for:

INSERT into Ingredients(Recipe_ID, Description) VALUES(1, ‘Eggs’);

INSERT into Ingredients(Recipe_ID, Description) VALUES(1, ‘Water’);

INSERT into Ingredients(Recipe_ID, Description) VALUES(1, ‘Salt’);

INSERT into Ingredients(Recipe_ID, Description) VALUES(2, ‘Bread’);

…

INSERT into Ingredients(Recipe_ID, Description) VALUES(3, ‘Toppings’);

Again notice that I did not specify the values for the table’s primary key (Ingredient_ID), but I did specify the foreign key values (Recipe_ID).

Step 3: Add the Database File to Your XCode Project

To do this, just right click on the Resources folder and select “Add->Existing Files…” and then select the database file that you created.

XCode should automatically add the file that you select to the “Copy Bundle Resources” build phase of your project target. (Meaning that when the program is compiled, your database file is automatically stored in the application bundle’s resource folder so that you can access it from your application.)

Update: Jonathan Wight suggested that instead of embedding the SQLite db file directly into the XCode project, it is better to embed the SQL source file into the project and then setup a compile rule in XCode to generate the db file and embed it into the bundle at compile time. As he points out this has a particular advantage related to source code control. He provides an example of how to do this here.

Step 4: Link to the SQLite Library

Finally you are ready to link to the library that contains all of the SQLite functions. To link to that library, from your XCode project right click the frameworks folder and select “Add->Existing Frameworks…”.

Next, navigate to the your iPhoneSDK folder and select the usr/lib/libsqlite3.0.dylib file. (Don’t be confused by the other libsqlite*.lib files in that folder, they are all just aliases to this one.)

You are now ready to start writing some SQLite code. (Again see the SQLiteBooks sample code for some tips on doing that.)

Update: Jonathan Wight also suggested that it is better to use the other linker flags setting in the the Build tab of the Project settings to link to the SQLite library instead of using the framework method. You can do this by adding the -lsqlite3 flag to that field as shown here:

This method has the advantage of linking to the correct library no matter what platform you are targeting.

SQLite on the iPhone Nuances

1. SQLite on the actual iPhone is much slower for some things than on the simulator. In particular the same queries that run in the blink of an eye on the simulator take several seconds to run on the actual device.
2. If you need to write to your database, make sure you make a copy of it in a location that you can write to. See the SQLiteBooks sample code for an example of how to do this.
3. Be aware that SQLite extensions such as Full Text Search don’t appear to be compiled into the iPhone SQLite libraries.
4. Plan for the future! If you plan on adding a feature that relies on a certain table or field in the database make that part of your database design now. This will save you the trouble of having to write all the necessary SQL commands for versioning your database later when you release an application update.

Creating a ToDo List Using SQLite Part 4

This is the final installment of our 4 part series of creating a Todo list for the iPhone. In this tutorial, I will detail how to add and delete new todo objects from the SQLite database. Make sure that you have completed the following tutorials before you begin this one:

• iPhone Programming Tutorial - Creating a ToDo List Using SQLite Part 1
• iPhone Programming Tutorial - Creating a ToDo List Using SQLite Part 2
• iPhone Programming Tutorial - Creating a ToDo List Using SQLite Part 3

When you have completed this tutorial, you should have a main screen that looks something like this:

Let’s get started…

The first thing we need to do is add the UIBarButtonItem items to the NavigationBar so that we get the “Edit” and “Add” button. Open up RootViewController.m and add the following code to the viewDidLoad method.

The first thing we see is the line that sets the leftBarButtonItem to self.editButtonItem. This automatically adds the “Edit” button to the NavigationController. Also, it sets up the functionality that allows the “delete” buttons to be displayed when the button is pressed. You can see this functionality if you do a “Build and Go” at this step. Next, I have manually created a UIBarButtonItem and added it to the navigationbar. This can be done in Interface Builder, but I wanted to show you how to do it manually and assign an action to it (I’m sure you will require this functionality in a future program). Here is a break down of the parameters:

• initWithTitle - The text to be displayed on the button
• style - How the button will look
• target - The class object that handles the messages sent from this button
• action - The method to be called when the button is passed. We can use @selector and give it the name of the function to call.

Finally, we assign this button to the rightBarButtonItem. If you do a Build and Go, it should error since we haven’t created the addTodo method. We will do that in a bit. Now, let’s create a method inside of our Todo object that will add new Todos to the database.

Open up Todo.h and add the following code:

So in addition to the insertNewTodoIntoDatabase method, we also see the deleteFromDatabase method signature. I have just added this so I don’t have to come back to it later. We will be implementing this when I show you how to delete todos from the database. One thing to note about the insertNewTodoIntoDatabase method is it has a “+” rather than a “-” sign. This means that it is a static method. Static methods are associated with the class not the instance meaning we can call this method without instanciating this class. So we can do stuff like Todo.insertNewTodoIntoDatabase. Now we will implement this method.

Before we can do this, we must declare a few more static sqlite3_statement’s. Add the following statements to the top of Todo.m

Nothing new here…Now implement the following method:

This is similar to our update method. Notice that we are inserting default values into the database. This is so we don’t run into any problems with null or nil values. The most important part of this method is the fact that it returns the primary key of the newly created todo object. This will be used later so we can immediately transition to the todo when the “Add” button is pressed. The last thing we need to do to the todo object is update the dehydrate method so that the todoText gets saved if it gets changed. Update the dehydrate method to look like this:

There are only a few minor changes here. First we see the “text = ?” part added to the sql statement. This is simply so we can update the text of the todo. The other change is we bound the self.text property to the 1st question mark in the sql statement. One thing to notice is we call [self.text UTF8String]. This is because sqlite3_bind_text takes a (char *). This will convert an NSString to an acceptable format.

Now we need to add a method inside of our RootViewController to add a todo. This is the method that will be called when the user presses the “Add” button. Inside of RootViewController.m add the following code:

First, we get a reference to the appDelegate object. This is because we need to call its addTodo method. Next, we instantiate the TodoViewController if it has not already been instantiated. We need this around because we will push it on to the view stack and transition to it after we create our new todo object. After this is done, we call the addTodo method of the appDelegate. It will return the newly created todo object and the view will be transitioned to its detail screen in order to update its details. Now we need to implement the method addTodo inside of our appDelegate. Open up todoAppDelegate.h and add the following code to create the method signature.

Now, let’s implement this method. Open up todoAppDelegate.m and add the following code:

First, we are calling the insertNewTodoIntoDatabase method of the Todo object. Notice that we are simply calling the method without first building an instance of a todo object. As I said in tutorial 3, this is because that method is static and gets called without building an instance of the class. Next, we insatiate the todo object that was just created by calling its initWithPrimaryKey method. This will give us reference to the new todo object. Finally, we append this todo to the end of our todos array. Since our UITableView is updated with this array, it will automatically include the new todo object. The last line just returns this todo object.

Remember is the last tutorial we made it so the users could update the status and the priority of a todo? Well, now we also need to give them the ability to update the text of the todo. So open up TodoViewController.h and add the following code:

Ok, so I’m guessing you are wondering why the UITextView for the todoText object has been changed to a UITextField. Well, I will tell you. UITextView doesn’t have the methods that we need to save the text with our current design. We will also be changing this on our Interface inside of Interface Builder. So for now, just believe me and anywhere it says UITextView, change it to UITextField. The only additional code we added here is the method signature for the updateText method. It’s an IBAction that will get called when the user presses the “Done” button on the keyboard after setting the text for the todo. Next, we need to implement this method. Open up TodoViewController.m and add the following code:

All this does is update the text of the todo to the text that the user entered inside of the UITextField. The last thing we need to do in order to add a todo is to replace the UITextView with a UITextField and connect it to our updateText method. Double click on your TodoViewController.xib file to open it in Interface Builder.

Now click on the UITextView on your interface and press the delete key on your keyboard to delete it. Now, drag a UITextField from the library and drop it onto your interface. Resize it to fit. When you have completed that, your interface should look something like this:

Now we need to connect this component. Make sure it is selected and click Tools -> Connections Inspector to open up the connections inspector. Drag from the circle next to the method “Did End On Exit” to the “File’s Owner” object. The words udpateText should pop up. Click on them to make the connection. Next, click in the circle next to “New Referencing Outlet” and drag it to the “File’s Owner” object. Select todoText when it pops up. The Connections Inspector should look like this:

Now we are done with Interface Builder. Go ahead and close it. We are now able to add todos. The last thing we need to do is give the ability to delete todos from the list as well as our database. This is all done in code, and we won’t need interface builder for this.

Let’s start by adding the methods to the appDelegate to handle the deletion of todos. Open up todoAppDelegate.h and add the following code:

All we see here is a signature for the removeTodo method. Also, be sure to add a #import “Todo.h” statement to the top of this file so that we can interface with the todo objects. Now let’s implement the removeTodo method. Open up todoAppDelegate.m and add the following code:

The first line looks up the todo in the todos NSArray. It returns the index in the array of the todo to be deleted. Then, we call the deleteFromDatabase method on the todo object and then remove it from the todos array. Since the UITableView is updated via this array, it will automatically remove the todo without any additional code on our part.

Now, let’s create the removeTodo method for the todo object. We have already written the method signature in Todo.h in a previous step, so open up Todo.m and add the following code:

Remember the delete_statement variable is a static sqlite3_stmt that we declared in a previous step. First, we check to see if it is nil. If it is we compile the statement using the sqlite3_prepare statement. Next, we bind the primary key of the current todo to the “?” in the sqlite3 statement. Next, we just step the statement to execute it and reset it. The last thing we need to do to delete todos from the database is to specify what happens when the user presses the “delete” button. Open up RootViewController.m and add the following code:

The first step (like the first step of many functions) is to get a reference to the appDelegate. Next, we check to see if we are currently editing. If so, call the removeTodo method on appDelegate. The next line, removes the row from the UITableView at the given indexPath.

Now click Build and Go! You should now be able to add and delete todo items from the database. This concludes our four part series of creating a todo list for the iPhone. As always, if you have any questions feel free to leave them in the comments section. If you get lost at any time you can download the sample code here.

Happy iCoding!

Creating a ToDo List Using SQLite Part 3

This is part 3 in our multipart series of creating a todo list for the iPhone. For this, you must have completed the following tutorials.

• iPhone Programming Tutorial - Creating a ToDo List Using SQLite Part 1
• iPhone Programming Tutorial - Creating a ToDo List Using SQLite Part 2

The focus of this tutorial will mainly be on viewing the todo items when selected. I will also show you how to update the todo status. This will require us to use interface builder. When you are completed, you will be able to edit todos through an interface similar to this:

Bringing Your Code Up To Speed

For this tutorial, we will need to get the last variable from the todo database. This is of course being the status variable. If you recall, it’s a boolean value (1 for complete, 0 for in progress). We need to get it from the database and associate it with the todo object. First let’s create a property to hold this value. Open todo.h and add the following code:

So a few changes here…First there is the added NSInteger status. This will be the property we associate with the status (complete or not) of the todo. We also create a property from it. Next, there is a BOOL property called “dirty”. We will use this object to signify when a todo has been altered. You will see how this comes into play when we implement the dehydrate method. Also, I have added 3 method signatures. updateStatus will be the method called when we want to update our status variable. Similarly, the updatePriority method will be called to update the priority. Finally, we have added a dehydrate method. This method should be familiar (or confusing) if you have messed with Apple’s books example. Basically, it will be used to save the state of the todo to the database. We will be calling this method on each todo item when the program exits. I will show you how to do this in a little bit.

Be sure to add the status variable to the synthesize line. Also, as we did before, we need to create a static sqlite3_stmt to hold the compiled dehydration statement. Add the following code to Todo.m:

Now let’s implement the methods. Add the following code:

The first two methods (udpateStatus and updatePriority) are pretty strait forward. They update the status and the priority of the todo and then set the “dirty” property to 1. This signifies that the todo has been altered and will need to be saved to the database.

Finally, there is the dehydrate method… We will call this method on each todo upon termination of the program. If the todo is “dirty” meaning the dirty property was set to YES, we will need to save the new data to the database. The database code should look pretty similar to code in previous tutorials. First, we check to see if the dehydrate_statement is equal to nil. If you recall, this will only happen the first time this method gets called. Next we create the update statement and then bind our variables to each of the “?”’s. Notice the ordering. The numbers represent the question marks from left to right (1 being the first, 2 being the second, 3 being the third). It took me quite some time to figure this out. Finally, we execute the sqlite statement by calling sqlite3_step and then reset the statement.

The last thing we need to do to Todo.m is change the SELECT statement in the initWithPrimaryKey method to grab the ‘complete’ field. Update the code to look like the screenshot below:

There are not really many changes. The first change is the added status to the synthesize line. Next, the sql statement was updated to read

SELECT text,priority,complete FROM todo WHERE pk=?

This allows us to get the “complete” field from the database. Finally, there is the line “self.status = sqlite3_column_in(init_statement,2);”. This is assigning the status property to the data at index 2 in the sql data array. We can now use this field.

One thing we need to do for the navigation to function properly is add a title to our main view. Open up rootViewController.m and add the following code to the viewDidLoad method:

Create the Todo Detail View

Now we are going to create the view that will display when the user selects the todo in the UITableView. Go ahead and open up Interface Builder by selecting one of you existing nib (.xib) files. Once it’s open add a new View by clicking File -> New and select View. Drag the following controls.

• UITextView
• UISegmentedControl - For this you will need to set the number of segments to 3. You will also see a dropdown menu below this option. Select each segment and fill in one of the priorities for the title. Here is a screenshot. You should give a title to each (Low , Medium, High).

  

• UILabel - This will be used to display the status
• UIButton - Users will click this button to update the status (Mark as complete)

When you are done, your interface should look something like this (but probably better):

I know that my interface doesn’t look the coolest. I’m a programmer not a graphic designer… Ok save this view by pressing Command-S. Make sure you are in your current projects directory. Name it TodoViewController and press Save.

It will then ask you if you want to add it to your project. Check the box next to the word todo and click Add.

Now close Interface Builder. Next, we are going to add the viewController class and set up variables to interface with this view.

Create TodoViewController Class Files

Click File -> New File… Select UIViewController Subclass and click Next.

Name the file TodoViewController and make sure that the box that says “Also create TodoViewController.h” is checked and click Finish.

Open up TodoViewController.h and add the following code.

Basically, we are setting up Interface Builder Outlets for each of the UI components to be connected to. Notice, the UIButton has an IBOutlet. This is because we will need to update the text on the button depending on whether or not the todo is completed. Also, I have an IBAction called updateStatus. We will be connecting this to the button we created. It will toggle the status (pending/complete) of a todo item. Finally, we see the updatePriority method. This method will be called when the user selects one of the priority segments in the UISegmentedControl. Next, open up TodoViewController.m and add the following synthesize code:

This will allow us to get and set these variables.

Before we connect this code to the Interface, we need to implement the methods that will be called when the user presses the button to mark a todo as complete as well as when the user presses a segment in the UISegmentedControl. Inside of TodoViewController add the following methods.

Let’s go through this. First we see the updateStatus method. This gets called when a user presses the button to alter the status. We basically check the current status of the todo (whether or not it’s completed) and depending on that, change the text to be displayed on the UIButton. So, if the todo is not complete (in progress) and this button is pressed, the text will be changed from “Mark As Complete” to “Mark As In Progress”. Finally, we call the updateStatus of the todo and pass the new value (1 or 0) to it.

Next we see the updatePriority method. It simply reads the value of the UISegmentedControl by calling the selectedSegmentIndex method on it. The next part looks a little messy. There are 2 reasons that reason we can’t just pass the value of the UISegmentedControl directly to the method. The first is, the UISegmentedControl is ordered in acending order (1, 2, 3…), but our priorities are in descending order (3 = low, 2 = medium, 1 = high). This is where the “2 - priority” comes from. Next, UISegmented controls are “0 indexed” meaning the indices start at 0 and increment from there. So we need to add a “+1″ to the index as our todo priorities start at 1.

Now we need to connect the UI Components in Interface Builder to this code. Double click on TodoViewController.xib to open it in Interface Builder.

Connecting UI Components To Code

We first need to associate this view with the class we just created. In the Interface Builder, click on the File’s Owner object. Next click Tools -> Identity Inspector. You should see a drop-down next to class. Select TodoViewController from this list and you will see the variables we just created appear in the boxes below.

This is what the Identity window should look like after you have selected TodoViewController.

Now that the class is associated, we can begin connecting the components. We will start by connecting the view. Click on the top of your view window to select the view itself (make sure you haven’t selected any of the UI components). Click Tools -> Connections Inspector. Next to where is says “New Referencing Outlet” click in the circle and drag it to the “File’s Owner” object and release it. The word “view” should pop up. Click on the word view. It should now look like this.

Now repeat these steps for each of the components (UITextView, UISegmentedControl, UILabel, UIButton) connecting each to the “File’s Owner Object”. Instead of the word “view” popping up, you should see the variable name for the corresponding variable that you want to connect the component to. So for the UITextView, you should see the word “todoText” appear when you drag it to the File’s Owner object.

We need to connect the UIButton to the updateStatus method we created. To do this click inside the “Touch up inside” circle and drag it to the “File’s Owner” object. You should see the text “updateStatus” appear. Click on it. If all goes well it should look like this.

The last thing we need to do inside of Interface Builder is connect the UISegmentedControl. Click on it in your view and then click Tools -> Connections Inspector… Click on the circle next to the “Value Changed” method and drag it to the “File’s Owner” object. You will see the method updatePriority popup. Go ahead and click on it. Your window for the UISegmentedControl should now look like this:

Now, let’s display this view when a row is selected. Close Interface Builder and open up RootViewController.h and add the following code:

We need a variable to associate with the TodoViewController that we will be transitioning to. Next, open up RootViewController.m and add the following code to synthesize this property.

Keeping the UITableView Up To Date

Whenever a todo item is altered (status or priority) the UITableView needs to be updated with the new changes. Add the following code to the viewWillAppear.

The line [self.tableView reloadData] reloads the table data every time the view appears (or reappears). This will ensure that our table is always up to date.

Now add the following code to the didSelectRowAtIndex method:

This is quite a bulky method with a lot of familiar code. First, we get a handle to the appDelegate and the todo object that was selected. Next, we push the todoView (the view you created in interface builder) on to the viewController stack to transition to it. After that, we are setting some of the properties of the view. The title is set to the text of the todo (it will get truncated if it is too long) and the UITextView is also set to the todo text. Next, we are translating our priority to an index for the UISegmentedView. I explained why this was necessary above. Then the index of the UISegmentedControl is set by using the setSelectedSegmentIndex method. Finally, we set the text of the button and label based on the status of the todo.

The very last thing we need to do is tell the application to save itself when it closes. Open up todoAppDelegate.m and add the following code to the applicationWillTerminate method:

If you ask me, this is some freakin sweet functionality. The method “makeObjectsPerformSelector” is a built in method on an NSArray. It basically loops over every object in the array, calling the method you pass in to it on each one. It’s like doing a for loop and calling the todo[x].dehydrate method for each todo. Only this is much cleaner. So, to reiterate, this method will call the dehydrate method on each todo. If the todo is “dirty” meaning it was altered, it will be saved to the database, otherwise the dehydrate method will do nothing.

* One thing to note. The applicationWillTerminate method will not be called if you quit the simulator while the application is running. To make sure it gets called (and the todo data gets saved) make sure you press the home button on the simulator after you make alterations to the todos. If you simply press Apple-q and quit the simulator while inside of the todo program, no data will be saved and you will post angry comments on my site telling me that my tutorial is wrong.

Click Build and Go and just sit back and enjoy the magic of rock! I mean XCode…

When you select a todo item, your screen should look something like this:

Well, that concludes part 3 of our series. Join me next time, when I will show you how to add and delete todos from the SQLite database. If you have any comments or questions, feel free to leave them in the comments section. I would love to hear them. Please subscribe to the RSS feed if you want to be automatically notified of new tutorials. If you get lost at any point, you can download the sample code for this tutorial here.

Happy iCoding!