TweetFollow Us on Twitter

Life with SQLite

Volume Number: 21 (2005)
Issue Number: 6
Column Tag: Programming

Tips From Big Nerd Ranch

Life with SQLite

by Aaron Hillegass, Chris Campbell, Marquis Logan

With Tiger, Apple has started using SQLite in many ways. Most notably, SQLite is the recommended store for Core Data applications.

What is SQLite?

A database server, for example Oracle or PostgreSQL, is a daemon that responds to requests from client applications. The clients make requests of the server using Structured Query Language (SQL) via some client library. The server takes care of the security, concurrency, and distribution issues. The server stores tables of data that can be indexed for quick random access.

    SQLite is not a database server.

SQLite is an open-source C library written by Dr. Richard Hipp. It creates a file that holds tables of data that can be indexed for quick random access. You read and write to this file using SQL. The main advantage of SQLite over archiving is that the data can be read and updated incrementally. (An archive is read in one big piece, and must be completely rewritten if the resulting object graph is edited.) The performance and scalability of SQLite is excellent -- Dr. Hipp's mother has every right to be very proud of him.

You can learn all about SQLite at the website:

http://www.sqlite.org/

Looking at SQLite files

SQLite comes with a command-line tool called sqlite3. The tool is installed automatically with Tiger. Using sqlite3, you can interactively inspect and edit any SQLite file. If you are curious about how Core Data structures the file, sqlite3 is a great way to explore it. sqlite3 has several non-SQL commands that start with a dot. .schema will show you the create statements for all the tables and indices in the file:

% sqlite3 test.eventsq 
sqlite> .schema
CREATE TABLE ZLOCATION 
    (Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, 
    ZDETAILDESCRIPTION VARCHAR, ZNAME VARCHAR );
CREATE TABLE ZPERSON 
    (Z_ENT INTEGER, Z_PK INTEGER PRIMARY KEY, Z_OPT INTEGER, 
    ZNOTES VARCHAR, ZFIRSTNAME VARCHAR, ZLASTNAME VARCHAR );
...
CREATE INDEX ZEVENT_ZOCCASION_INDEX ON ZEVENT (ZOCCASION);
sqlite>  select * from zperson;
 5 | 1 | 1 | Bride  | Laura | Smith
 5 | 2 | 1 | Groom  | Craig | Adams

Watching the SQL executed by Core Data

This tip requires that you use some API that Apple has not yet exposed, and I would not use it in a shipping application. It is, however, a great way to understand exactly what Core Data is doing with SQLite. To log every SQL command sent to SQLite, execute the following code early in your application:

Class privateClass;
privateClass = NSClassFromString(@"NSSQLConnection");
// The compiler will give a warning here
[privateClass setDebugDefault:YES];

Using SQLite in non-Core Data applications

There are a couple of reasons why you might want to write an application that works with SQLite files, but doesn't use Core Data:

1) Backwards compatibility: Core Data is only available on Tiger.

2) Performance: While Core Data is very clever about which rows to fetch from a sqlite file, it fetches all the columns for those rows. If you know that you only need one or two columns, you may get a significant performance win from writing an explicit SELECT statement.

QuickLite is an Objective-C wrapper for SQLite by Tito Ciuro. It is an open source framework, and you can download it at:

http://www.webbotech.com/

One of the QuickLite example programs is SQLiteManagerX, a very useful Cocoa application for browsing and editing SQLite files.


Figure 1. SQLiteManagerX browsing a SQLite file

SQLite is not a database server

Before we end this, let us remind you that SQLite is not a database server. If more than one user is going to be accessing the data, you need a database server to take care of security, distribution, and concurrency issues. (There are, for example, many stories of companies trying to use Microsoft Access as a networked datastore. Most of the stories end with tears and a massive rewrite.) If your data is accessed by only one user, SQLite is an elegant and efficient solution.


Aaron Hillegass, Chris Campbell, Marquis Logan

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Latest Forum Discussions

See All

How to evolve Eevee in Pokemon GO
By now, almost everyone should be hip to how to evolve Pokemon in Pokemon GO (and if not, there's a guide for that). Just gather enough candy of the appropriate type, feed them all to the Pokemon, and evolution happens. It's a miracle that would... | Read more »
CSR Racing 2: Guide to all game modes
It might not seem like there are all that many ways to go fast in a straight line, but CSR Racing 2 begs to differ. [Read more] | Read more »
Bulb Boy (Games)
Bulb Boy 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: Multi-award winning 2D point & click horror adventure about a boy with a glowing head. | Read more »
5 top free emoji keyboard apps
If we're not at peak emoji yet as a society, it feels like we definitely should be. The emoji concept has gone far beyond what anyone in Japan could have envisioned when the people there unleashed it on an unsuspecting world, but the West has... | Read more »
How to unlock more characters in Disney...
One of the big charms of Disney Emoji Blitz is seeing a wide variety of beloved Disney and Pixar characters transformed into smiling emojis. Even someone like the sneaky Randall from Monsters Inc., who probably never cracked a smile on film, is... | Read more »
Cubway (Games)
Cubway 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Cubway is a journey with an abstract story of lifecycle of rebirth, called Samsara. Guide the cube through the long way full of dangers... | Read more »
Colorcube (Games)
Colorcube 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Turn pieces and blend colours in this minimal yet visually stunning puzzler.Over 200 handcrafted and challenging levels. Features... | Read more »
Doodle God Griddlers (Games)
Doodle God Griddlers 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: | Read more »
Crusader Kings: Chronicles (Games)
Crusader Kings: Chronicles 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Crusader Kings: Chronicles is an interactive text based game that puts you in the shoes of Guy de Rose as you make... | Read more »
Roads of Rome: New Generation (Games)
Roads of Rome: New Generation 1.0 Device: iOS Universal Category: Games Price: $5.99, Version: 1.0 (iTunes) Description: | Read more »

Price Scanner via MacPrices.net

Free iOS Business App notably* Helps Service...
PayStudio Inc. has introduced their new business app notably* 1.0, developed for iPhone and iPod touch. notably* was specifically developed to help service and trade professionals go digital and... Read more
27-inch iMacs on sale for $200 off MSRP
Amazon has 27″ iMacs on sale for $200 off MSRP including free shipping: - 27″ 3.3GHz iMac 5K: $2099 $200 off MSRP - 27″ 3.2GHz/1TB Fusion iMac 5K: $1799.99 $200 off MSRP - 27″ 3.2GHz/1TB HD iMac 5K... Read more
Mac Pros on sale for $200 off MSRP
B&H Photo has Mac Pros on sale for $200 off MSRP. Shipping is free, and B&H charges sales tax in NY only: - 3.7GHz 4-core Mac Pro: $2799, $200 off MSRP - 3.5GHz 6-core Mac Pro: $3799, $200... Read more
Save up to $600 with Apple refurbished Mac Pr...
Apple has Certified Refurbished Mac Pros available for up to $600 off the cost of new models. An Apple one-year warranty is included with each Mac Pro, and shipping is free. The following... Read more
Apple price trackers, updated continuously
Scan our Apple Price Trackers for the latest information on sales, bundles, and availability on systems from Apple’s authorized internet/catalog resellers. We update the trackers continuously: - 15″... Read more
13-inch 2.5GHz MacBook Pro (Apple refurbished...
Apple has Certified Refurbished 13″ 2.5GHz MacBook Pros available for $829, or $270 off the cost of new models. Apple’s one-year warranty is standard, and shipping is free: - 13″ 2.5GHz MacBook Pros... Read more
21-inch iMacs on sale for up to $120 off MSRP
B&H Photo has 21″ iMacs on sale for up to $120 off MSRP including free shipping plus NY sales tax only: - 21″ 3.1GHz iMac 4K: $1379 $120 off MSRP - 21″ 2.8GHz iMac: $1199.99 $100 off MSRP - 21″ 1... Read more
Charitybuzz Set to Auction Unique Apple-1 Com...
Offering an opportunity to own the computer that sparked a revolution, on Monday, July 25, leading online charity auction platform Charitybuzz will auction what is claimed to be the world’s most... Read more
MacBook Airs on sale for up to $150 off MSRP
Amazon has 11″ and 13″ MacBook Airs on sale for up to $150 off MSRP for a limited time. Shipping is free: - 13″ 1.6GHz/128GB MacBook Air (sku MMGF2LL/A): $899.99 $100 off MSRP - 13″ 1.6GHz/256GB... Read more
Apple refurbished 13-inch Retina MacBook Pros...
Apple has Certified Refurbished 13″ Retina MacBook Pros available for up to $270 off the cost of new models. An Apple one-year warranty is included with each model, and shipping is free: - 13″ 2.7GHz... Read more

Jobs Board

*Apple* Retail - Multiple Positions, Willow...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* Evangelist - JAMF Software (United S...
The Apple Evangelist is responsible for building and cultivating strategic relationships with Apple 's small and mid-market business development field teams. This Read more
*Apple* Solutions Consultant - APPLE (United...
Job Summary As an Apple Solutions Consultant, you'll be the link between our future customers and our products. You'll showcase your entrepreneurial spirit as you Read more
*Apple* Professional Learning Specialist - A...
Job Summary The Apple Professional Learning Specialist is a full-time position for one year with Apple in the Phoenix, AZ area. This position requires a high Read more
*Apple* Picker - Apple Hill Orchard (United...
Apple Hill Orchard, Co. Rte. 21,Whitehall, NY 9/7/16-10/228/16. Pick fresh market or processing apples Productivity of 60 boxes and 80 boxes processing fruit per Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.