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

OmniGraffle Pro 7.2.2 - Create diagrams,...
OmniGraffle Pro helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use... Read more
OmniGraffle 7.2.2 - Create diagrams, flo...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more
Spotify 1.0.44.100. - Stream music, crea...
Spotify is a streaming music service that gives you on-demand access to millions of songs. Whether you like driving rock, silky R&B, or grandiose classical music, Spotify's massive catalogue puts... Read more
Microsoft OneNote 15.29 - Free digital n...
OneNote is your very own digital notebook. With OneNote, you can capture that flash of genius, that moment of inspiration, or that list of errands that's too important to forget. Whether you're at... Read more
WALTR 2 2.0.8 - $39.95
WALTR 2 helps you wirelessly drag-and-drop any music, ringtones, videos, PDF, and ePub files onto your iPhone, iPad, or iPod without iTunes. It is the second major version of Softorino's critically-... Read more
Dropbox 16.3.27 - Cloud backup and synch...
Dropbox is an application that creates a special Finder folder that automatically syncs online and between your computers. It allows you to both backup files and keep them up-to-date between systems... Read more
EtreCheck 3.1.5 - For troubleshooting yo...
EtreCheck is an app that displays the important details of your system configuration and allow you to copy that information to the Clipboard. It is meant to be used with Apple Support Communities to... Read more
Carbon Copy Cloner 4.1.12 - Easy-to-use...
Carbon Copy Cloner backups are better than ordinary backups. Suppose the unthinkable happens while you're under deadline to finish a project: your Mac is unresponsive and all you hear is an ominous,... Read more
VueScan 9.5.62 - Scanner software with a...
VueScan is a scanning program that works with most high-quality flatbed and film scanners to produce scans that have excellent color fidelity and color balance. VueScan is easy to use, and has... Read more
SpamSieve 2.9.27 - Robust spam filter fo...
SpamSieve is a robust spam filter for major email clients that uses powerful Bayesian spam filtering. SpamSieve understands what your spam looks like in order to block it all, but also learns what... Read more

Latest Forum Discussions

See All

Track Santa with these three festive app...
Christmas is fast approaching and that means it's time to prepare for Santa's yearly pilgrimage around the globe. Christmas Eve is an exciting time as parents help their kids get ready to welcome Santa. You've got the cookies and milk all planned... | Read more »
Galaxy on Fire 3 and four other fantasti...
Galaxy on Fire 3 - Manticore brings the series back for another round of daring space battles. It's familiar territory for folks who are familiar with the franchise. If you've beaten the game and are looking to broaden your horizons, might we... | Read more »
The best apps for your holiday gift exch...
What's that, you say? You still haven't started your holiday shopping? Don't beat yourself up over it -- a lot of people have been putting it off, too. It's become easier and easier to procrastinate gift shopping thanks to a number of apps that... | Read more »
Toca Hair Salon 3 (Education)
Toca Hair Salon 3 1.0 Device: iOS Universal Category: Education Price: $2.99, Version: 1.0 (iTunes) Description: | Read more »
Winter comes to Darkwood as Seekers Note...
MyTona, based in the chilly Siberian city of Yakutsk, has brought a little festive fun to its hidden object game Seekers Notes: Hidden Mystery. The Christmas update introduces some new inhabitants to players, and with them a chance to win plenty of... | Read more »
Bully: Anniversary Edition (Games)
Bully: Anniversary Edition 1.03.1 Device: iOS Universal Category: Games Price: $6.99, Version: 1.03.1 (iTunes) Description: *** PLEASE NOTE: This game is officially supported on the following devices: iPhone 5 and newer, iPod Touch... | Read more »
PINE GROVE (Games)
PINE GROVE 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: A pine grove where there are no footsteps of people due to continuous missing cases. The case is still unsolved and nothing has... | Read more »
Niantic teases new Pokémon announcement...
After rumors started swirling yesterday, it turns out there is an official Pokémon GO update on its way. We’ll find out what’s in store for us and our growing Pokémon collections tomorrow during the Starbucks event, but Niantic will be revealing... | Read more »
3 reasons why Nicki Minaj: The Empire is...
Nicki Minaj is as business-savvy as she is musically talented and she’s proved that by launching her own game. Designed by Glu, purveyors of other fine celebrity games like cult favorite Kim Kardashian: Hollywood, Nicki Minaj: The Empire launched... | Read more »
Clash of Clans is getting its own animat...
Riding on its unending wave of fame and success, Clash of Clans is getting an animated web series based on its Clash-A-Rama animated shorts.As opposed to the current shorts' 60 second run time, the new and improved Clash-A-Rama will be comprised of... | Read more »

Price Scanner via MacPrices.net

New 2016 13-inch Touch Bar MacBook Pros on sa...
B&H Photo the new 2016 Apple 13″ 2.9GHz/256GB Touch Bar MacBook Pros on sale for $50 off MSRP, each including free shipping plus NY sales tax only: - 13″ 2.9GHz/256GB Touch Bar MacBook Pro Space... Read more
12-inch 1.2GHz Space Gray Retina MacBook on s...
B&H Photo has dropped their price on the 2016 Apple 12″ 1.2GHz Space Gray Retina MacBook (MLH82LL/A) to $1399 including free shipping plus NY sales tax only. Their price is $200 off MSRP, and it’... Read more
Never Settle for Low Performing Wifi With iOS...
AppYogi Software has announced the release of WiFi Signal Strength Status App 1.0, the company’s new utility developed exclusively for macOS. WiFi Signal Strength Status App features a unique, single... Read more
New 2016 13-inch Touch Bar MacBook Pros in st...
B&H Photo has stock of new 2016 Apple 13″ Touch Bar MacBook Pro models, each including free shipping plus NY sales tax only: - 13″ 2.9GHz/512GB Touch Bar MacBook Pro Space Gray: $1999 - 13″ 2.... Read more
New 2016 15″ Touch Bar MacBook Pros in stock...
B&H Photo has new 2016 Apple 15″ Touch Bar MacBook Pro models in stock today including free shipping plus NY sales tax only: - 15″ 2.7GHz Touch Bar MacBook Pro Space Gray: $2799 - 15″ 2.7GHz... Read more
DietSensor App Targeting Diabetes and Obesity...
DietSensor, Inc., a developer of smart food and nutrition applications designed to fight diabetes and obesity and help improve overall fitness, has announced the launch of its DietSensor app for... Read more
Holiday 2016 13-inch 2.0GHz MacBook Pro sales...
B&H has the non-Touch Bar 13″ MacBook Pros in stock today for $50-$100 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.0GHz MacBook Pro Space Gray (MLL42LL/A): $1449 $... Read more
Holiday sale: Apple TVs for $51-$40 off MSRP,...
Best Buy has dropped their price on the 64GB Apple TV to $159.99 including free shipping. That’s $40 off MSRP. 32GB Apple TVs are on sale right now for $98 on Sams Club’s online store. That’s $51 off... Read more
12-inch Retina MacBooks, Apple refurbished, n...
Apple has restocked a full line of Certified Refurbished 2016 12″ Retina MacBooks, now available for $200-$260 off MSRP. Refurbished 2015 models are available starting at $929. Apple will include a... Read more
Holiday sale: 12-inch Retina MacBook for $100...
B&H has 12″ Retina MacBooks on sale for $100 off MSRP as part of their Holiday sale. Shipping is free, and B&H charges NY sales tax only: - 12″ 1.1GHz Space Gray Retina MacBook: $1199 $100... Read more

Jobs Board

Integration Technician, *Apple* - Zones, In...
…at Zones and for our customers each day. Position Overview The Apple Integration Technician will be responsible for performing customer specific configuration Read more
*Apple* Brand Ambassador (Macy's) - The...
…(T-ROC), is proud of its unprecedented relationship with our partner and client, APPLE ,in bringing amazing" APPLE ADVOCATES"to "non" Apple store locations. Read more
*Apple* Retail - Multiple Positions- Trumbul...
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 - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
US- *Apple* Store Leader Program - Apple (Un...
…Summary Learn and grow as you explore the art of leadership at the Apple Store. You'll master our retail business inside and out through training, hands-on Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.