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

Is there cross-platform play in slither....
So you've sunken plenty of hours into crawling around in slither.io on your iPhone or iPad. You've got your stories of tragedy and triumph, the times you coiled four snakes at one time balanced out by the others when you had a length of more than... | Read more »
Rodeo Stampede guide to running a better...
In Rodeo Stampede, honing your skills so you can jump from animal to animal and outrun the herd as long as possible is only half the fun. Once you've tamed a few animals, you can bring them home with you. [Read more] | Read more »
VoxSyn (Music)
VoxSyn 1.0 Device: iOS Universal Category: Music Price: $6.99, Version: 1.0 (iTunes) Description: VoxSyn turns your voice into the most flexible vocal sound generator ever. Instantly following even subtle modulations of pitch and... | Read more »
Catch Battleplans on Google Play from Ju...
Real-time strategy title Battleplans is due for release on Google Play on June 30th, following its release for iOS systems last month. With its simple interface and pretty graphics, the crowd-pleaser brings a formerly overlooked genre out for the... | Read more »
iDoyle: The interactive Adventures of Sh...
iDoyle: The interactive Adventures of Sherlock Holmes - A Scandal in Bohemia 1.0 Device: iOS Universal Category: Books Price: $1.99, Version: 1.0 (iTunes) Description: Special Release Price $1.99 (Normally $3.99) | Read more »
Five popular free apps to help you slim...
Thanks to retail and advertising, we're used to thinking one season ahead. Here we are just a week into the summer and we're conditioned to start thinking about the fall. [Read more] | Read more »
How to ride longer and tame more animals...
It's hard to accurately describe Rodeo Stampede to people who haven't seen it yet. It's like if someone took Crossy Roadand Disco Zoo and put them in a blender, yet with a unique game mechanic that's still simple and fun for anyone. [Read more] | Read more »
Teeny Titans - A Teen Titans Go! Figure...
Teeny Titans - A Teen Titans Go! Figure Battling Game 1.0.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0.0 (iTunes) Description: Teeny Titans, GO! Join Robin for a figure battling RPG of epic proportions! TEENY... | Read more »
NinjAwesome: Tips and tricks to be a mor...
Sorry about that headline, but I'm going to go ahead and assume that GameResort would not have named its game NinjAwesome without expecting some of that. It is, in fact, pretty awesome the way it combines an endless runner and old school arcade... | Read more »
Into Mirror (Games)
Into Mirror 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: "Is all that we see or seem, but a dream within a dream?"- Edgar Allan Poe New game by Lemon Jam Studio, the team behind Pursuit... | Read more »

Price Scanner via MacPrices.net

13-inch Retina MacBook Pros on sale for up to...
B&H Photo has 13″ Retina MacBook Pros on sale for up to $150 off MSRP. Shipping is free, and B&H charges NY tax only: - 13″ 2.7GHz/128GB Retina MacBook Pro: $1179 $120 off MSRP - 13″ 2.7GHz/... Read more
Apple refurbished Mac minis available for up...
Apple has Certified Refurbished Mac minis available starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: - 1.4GHz Mac mini: $419 $80 off MSRP - 2.6GHz Mac... Read more
13-inch 2.5GHz MacBook Pro on sale for $999,...
B&H Photo has the 13″ 2.5GHz MacBook Pro on sale for $999 including free shipping plus NY sales tax only. Their price is $100 off MSRP. Read more
Apple refurbished iMacs available for up to $...
Apple has Certified Refurbished 2015 21″ & 27″ iMacs available for up to $350 off MSRP. Apple’s one-year warranty is standard, and shipping is free. The following models are available: - 21″ 3.... Read more
15-inch Retina MacBook Pros on sale for $200-...
B&H Photo has 15″ Retina MacBook Pros on sale for up to $210 off MSRP. Shipping is free, and B&H charges NY tax only: - 15″ 2.2GHz Retina MacBook Pro: $1799.99 $200 off MSRP - 15″ 2.5GHz... Read more
Mac minis on sale for up to $100 off MSRP
B&H Photo has Mac minis on sale for up to $100 off MSRP including free shipping plus NY sales tax only: - 1.4GHz Mac mini: $449 $50 off MSRP - 2.6GHz Mac mini: $649 $50 off MSRP - 2.8GHz Mac mini... Read more
Clearance 2015 13-inch MacBook Airs available...
B&H Photo has clearance 2015 13″ MacBook Airs available for $300 off original MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 1.6GHz/4GB/128GB MacBook Air (MJVE2LL/A): $799.... Read more
Apple refurbished Mac minis available for up...
Apple has Certified Refurbished Mac minis available starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: - 1.4GHz Mac mini: $419 $80 off MSRP - 2.6GHz Mac... Read more
ABBYY TextGrabber: 1,000,000 Installs in 5 Da...
ABBYY, an international OCR technologies provider, has announced that their image-to-text application TextGrabber, got installed 1,000,000 times in just five days while being featured by the App... Read more
New SkinIt Waterproof Case For iPhone 6
With its impact and waterproof design, the Skinit Waterproof case provides security and protection to guarantee your phone will get you through even the most demanding outdoor conditions. The impact-... Read more

Jobs Board

*Apple* Retail - Multiple Positions, Towson...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
*Apple* iPhone 6s and New Products Tester Ne...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
Music Marketing Lead, iTunes & *Apple*...
…Music Marketing Lead is responsible for developing robust marketing campaigns and programs for Apple Music and iTunes across the whole of Apple ecosystem. This Read more
*Apple* Valley Medical Clinic is Hiring - AP...
Apple Valley Medical Clinic is Hiring! Apple Valley Medical Clinic is an independently owned practice operating a Family Medicine Clinic, a 24/7 Urgent Care, Read more
*Apple* New Products Testers Needed - Apple...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.