0

Sqlite and iPhone

by Deepak Dhakal 29. May 2009 11:05

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.

createDB.jpg

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:

schema.png

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:

RecipeTable.jpg

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:

IngredientsTable.jpg

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.)

createIndex.jpg

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.

addFile.jpg

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…”.

addFramework.jpg

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.)

Xcode.jpg

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:

Project “SQLiteBooks” Info.jpg

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.
Ref:http://leefalin.com/blog/2008/10/02/iphone-sqlite-database-basics/

Tags:

iPhone

0

App Store iPhone app and You

by Deepak Dhakal 15. May 2009 07:08

With marketers caught up in the iPhone app frenzy, a new report aims to help companies take a step back and soberly assess whether to hawk their own branded mobile wares on App Store shelves.

 

Asking "Is An iPhone App Right For You?" the study from Forrester Research acknowledges the popularity of the iPhone and Apple's App Store -- with more than 1 billion downloads so far -- while highlighting factors that brands should consider before racing off to launch the next Tap Tap Revenge.

Among the initial considerations is cost: Forrester estimates that mobile apps range from $20,000 at the low end to $150,000 for more sophisticated ones. Since most marketers don't have the in-house resources to build apps, the report suggests turning to experienced mobile app developers such as Moderati and Genex, which created Kraft's popular iFood Assistant app.

And with more than 35,000 apps now available through the App Store, Forrester analyst Neil Strother also reminds marketers to factor in a media budget to promote their wares -- "or risk getting lost in the sea of applications that is the App Store."

Another key question: free or paid? Forrester advises most brands to offer a free app to reach the widest possible audience, pointing to the popularity of Zippo's free Virtual Lighter app (developed by Moderati), with more than 3 million downloads to date. Selling an app can help cover development costs, but since many are free, some consumers would balk at paying even 99 cents.

What about potential pitfalls? With all the hype surrounding iPhone apps, little attention has been given to the downside of an ill-conceived effort. Forrester notes how early adopters trashed Sony's "underwhelming" Underworld game app. Such negative early reviews can spread quickly and hobble an app's chances of gaining traction.

Behind every app there also needs to be a "big idea" -- an overarching strategy matched to the brand objective. A utility-based app such as Bank of America's ATM locator can be used to provide practical information fast. Zippo's Virtual Lighter or Coca-Cola's Spin the Coke and Magic Coke Bottle apps, meanwhile, take an entertainment-focused approach to building brand awareness and affinity.

Other apps can combine both strategies such as Kraft's iFood Assistant and Nike's Goal app ( in Italy), which offers the utility of live match scores with richer content like action photos and player news.

Among other practical tips, Strother urges brand managers to get their hands on an iPhone or competing device to fully understand their capabilities and how people use them. They should also do a little research to find out what devices their customers are using, whether a BlackBerry, iPhone or T-Mobile's G1.

When Coke found out a segment of its younger customers were on the iPhone, for example, it realized that an app could be another way to connect with youthful consumers at a reasonable cost.

With several competing app storefronts being rolled out, the report also points out the App Store isn't the only game in town. "If you target people who use mobile email or business-oriented services, then an application with BlackBerry's App World should be a good fit," wrote Strother. Brands targeting entertainment-centric users, on the other hand, might want to investigate an app on Nokia's Ovi store.

Regardless of outlet, Forrester emphasizes that advertisers should take a long-term view of their app and plan for future changes in mobile technology. This summer, for instance, Apple will launch the upgraded version of the iPhone operating system, which could alter development costs. The 3.0 OS will also allow for e-commerce opportunities within applications.

Unfortunately, iPhone users don't necessarily take a long-term view of apps. According to a report earlier this year from Pinch Media, only 5% of those who downloaded a paid app were actively using it 20 days after purchase. The fall-off was even steeper for free apps.

Tags:

iPhone

Powered by BlogEngine.NET 1.5.0.7
Original Design by Laptop Geek, Adapted by onesoft