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

 
AAPL
$94.72
Apple Inc.
+0.78
MSFT
$44.83
Microsoft Corpora
-0.01
GOOG
$594.74
Google Inc.
+5.27

MacTech Search:
Community Search:

Software Updates via MacUpdate

Airmail 1.4 - Powerful, minimal email cl...
Airmail is a powerful, minimal mail client.It was designed to retain the same experience with a single or multiple accounts and provide a quick, modern and easy-to-use user experience. Airmail... Read more
Macs Fan Control 1.1.12 - Monitor and co...
Macs Fan Control allows you to monitor and control almost any aspect of your computer's fans, with support for controlling fan speed, temperature sensors pane, menu-bar icon, and autostart with... Read more
A Better Finder Rename 9.37 - File, phot...
A Better Finder Rename is the most complete renaming solution available on the market today. That's why, since 1996, tens of thousands of hobbyists, professionals and businesses depend on A Better... Read more
MacBook Air EFI Firmware Update 2.9 - Fo...
MacBook Air EFI Firmware Update is recommended for MacBook Air (Mid 2011) models. This update addresses an issue where systems may take longer to wake from sleep than expected and fixes a rare issue... Read more
FileZilla 3.9.0.1 - Fast and reliable FT...
FileZilla (ported from Windows) is a fast and reliable FTP client and server with lots of useful features and an intuitive interface.Version 3.9.0.1: MSW: Fix installation issue with locked DLLs... Read more
OS X Yosemite 10.10 DP4 - Developer Prev...
Note: This is a Developer Preview. You must be a registered Apple Mac Developer to download this update. OS X Yosemite is Apple's newest operating system for Mac. An elegant design that feels... Read more
FinderPop 2.5.6 - Classic Mac utility, n...
FinderPop is a Universal preference pane that extends OS X's contextual menus using a FinderPop Items folder much as the Apple Menu Items folder used to do for the Apple menu. It has other features... Read more
SpiderOak 5.1.7 - Secure cloud backup, s...
SpiderOak is a multi-platform secure online backup, storage, access, and sharing solution engineered for the consumer and small businesses. You must first sign up to use SpiderOak. Running natively... Read more
Espionage 3.6 - Simple, state of the art...
Espionage offers state-of-the-art encryption and plausible deniability for your confidential data. Sometimes, encrypting your data isn't enough to protect it. That's why Espionage 3 goes beyond data... Read more
calibre 1.45.0 - Complete e-library mana...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital... Read more

Latest Forum Discussions

See All

Celebrate Summer With a Cat in the Hat L...
Celebrate Summer With a Cat in the Hat Learning Library Sale Posted by Ellis Spice on July 22nd, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
MyTaskList Review
MyTaskList Review By Jennifer Allen on July 22nd, 2014 Our Rating: :: EFFECTIVE IF PLAINUniversal App - Designed for iPhone and iPad It’s not the most stylish of task management apps, but MyTaskList has all the features you could... | Read more »
FlyCraft Herbie: Crazy Machines Review
FlyCraft Herbie: Crazy Machines Review By Jennifer Allen on July 22nd, 2014 Our Rating: :: TRICKY FLYINGUniversal App - Designed for iPhone and iPad A tough game of careful thrusting and navigation, FlyCraft Herbie: Crazy Machines... | Read more »
MTN Review
MTN Review By Jessica Fisher on July 22nd, 2014 Our Rating: :: ADORABLE, SERENE, AND AMUSINGUniversal App - Designed for iPhone and iPad MTN is an adorable, talking pet mountain that is less game and more zen garden.   | Read more »
Fly High with Ninja UP! Now Available o...
Fly High with Ninja UP! Now Available on the App Store Posted by Jessica Fisher on July 22nd, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Bio Inc. Review
Bio Inc. Review By Nadia Oxford on July 22nd, 2014 Our Rating: :: SICKENING - IN A COMPELLING WAYUniversal App - Designed for iPhone and iPad Bio Inc is about orchestrating the medical destruction of a single person. If that doesn’... | Read more »
HELMUT Review
HELMUT Review By Andrew Fisher on July 21st, 2014 Our Rating: :: TRUNDLE SIMULATOR 2014Universal App - Designed for iPhone and iPad HELMUT is a fun, fleeting time-sink that offers a momentary distraction and nothing else.   | Read more »
Walkr Review
Walkr Review By Jennifer Allen on July 21st, 2014 Our Rating: :: ORIGINAL WALKINGiPhone App - Designed for the iPhone, compatible with the iPad Walking is a bit more exciting thanks to this planet building/discovering sim reliant... | Read more »
Zombie Commando Review
Zombie Commando Review By Jennifer Allen on July 21st, 2014 Our Rating: :: MINDLESS SLAUGHTERUniversal App - Designed for iPhone and iPad Briefly fun but ultimately forgettable, Zombie Commando will scratch an itch then be... | Read more »
Swords & Poker Adventures Review
Swords & Poker Adventures Review By Jennifer Allen on July 21st, 2014 Our Rating: :: SOULLESS POKER PLAYUniversal App - Designed for iPhone and iPad Swords & Poker Adventures is a mishmash of Poker and RPGing, but it lacks... | Read more »

Price Scanner via MacPrices.net

15-inch 2.0GHz MacBook Pro Retina on sale for...
B&H Photo has the 15″ 2.0GHz Retina MacBook Pro on sale for $1829 including free shipping plus NY sales tax only. Their price is $170 off MSRP. B&H will also include free copies of Parallels... Read more
Apple restocks refurbished Mac minis for up t...
The Apple Store has restocked Apple Certified Refurbished Mac minis for up to $150 off the cost of new models. Apple’s one-year warranty is included with each mini, and shipping is free: - 2.5GHz Mac... Read more
Twelve South HiRise For MacBook – Height-Adju...
If you use your MacBook as a workhorse desktop substitute, as many of us do, a laptop stand combined with an external keyboard and pointing device are pretty much obligatory if you want to avoid... Read more
Why The Mac Was Not Included In The Apple/IBM...
TUAW’s Yoni Heisler cites Fredrick Paul of Network World whoi blogged last week that the Mac’s conspicuous absence from Apple and IBM’s landmark partnership agreement represents a huge squandered... Read more
Save $100 on 13-inch Retina MacBook Pros, plu...
Adorama has 13″ Retina MacBook Pros on sale for $100 off MSRP. Shipping is free, and Adorama charges sales tax in NY & NJ only: - 13″ 2.4GHz/128GB MacBook Pro with Retina Display: $1199 - 13″ 2.... Read more
Blurr it 2.3 for iOS – Quickly Blurs Selected...
Hyderabad, India based TouchLabs has announced a new update of Blurr it 2.3, their photography app for iOS users. Blurr it allows you to blur part of the image to hide potentially sensitive or... Read more
MacBook Airs on sale for $100 off MSRP, start...
Best Buy has the new 2014 MacBook Airs on sale for up to $100 off MSRP on their online store. Choose free home shipping or free local store pickup (if available). Prices valid for online orders only... Read more
Amazon Announces Kindle Unlimited: Unlimited...
Amazon.com has introduced Kindle Unlimited — a new subscription service which allows customers to freely read as much as they want from over 600,000 Kindle books, and listen as much as they want to... Read more
New Linksys Wireless Range Extenders Boost Wi...
Linksys has announced its new lineup of Linksys Wi-Fi Range Extenders. Consumers often experience a weak wireless signal in some parts of their house or apartment caused by blocking elements such as... Read more
MacBook Airs available starting at $719
The Apple Store has Apple Certified Refurbished 2013 & 2012 MacBook Airs in stock today starting at $719. An Apple one-year warranty is included with each MacBook, and shipping is free: 2013... Read more

Jobs Board

*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
Sr *Apple* Engineer - IT - Requisition #: -...
For more information about TIAA-CREF, visit our website . The Apple Engineer will provide engineering and third-level incident support for 300- 500 MacOS desktop/laptop Read more
*Apple* Systems Administrator - DISH (United...
…satellite service provider, and Dish is currently looking for an experienced Apple /Mac Systems Administrator. Apple systems administrator will be responsible for Read more
*Apple* Systems Administrator - DISH (United...
…satellite service provider, and Dish is currently looking for an experienced Apple /Mac Systems Administrator. Apple systems administrator will be responsible for Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.