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

Minecraft 302 - Popular sandbox building...
Minecraft allows players to build constructions out of textured cubes in a 3D procedurally generated world. Other activities in the game include exploration, gathering resources, crafting, and combat... Read more
FotoMagico 5.2b8 - Powerful slideshow cr...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more
TeamViewer 11.0.65452 - Establish remote...
TeamViewer gives you remote control of any computer or Mac over the Internet within seconds, or can be used for online meetings. Find out why more than 200 million users trust TeamViewer! Free for... Read more
Dropbox 9.4.49 - Cloud backup and synchr...
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
Geekbench 4.0.0 - Measure processor and...
Geekbench provides a comprehensive set of benchmarks engineered to quickly and accurately measure processor and memory performance. Designed to make benchmarks easy to run and easy to understand,... Read more
Nisus Writer Pro 2.1.5 - Multilingual wo...
Nisus Writer Pro is a powerful multilingual word processor, similar to its entry level products, but brings new features such as table of contents, indexing, bookmarks, widow and orphan control,... Read more
Default Folder X 5.0.6 - Enhances Open a...
Default Folder X attaches a toolbar to the right side of the Open and Save dialogs in any OS X-native application. The toolbar gives you fast access to various folders and commands. You just click on... Read more
Alfred 3.1 - Quick launcher for apps and...
Alfred is an award-winning productivity application for OS X. Alfred saves you time when you search for files online or on your Mac. Be more productive with hotkeys, keywords, and file actions at... Read more
MYStuff Pro 2.0.25 - Create inventories...
MYStuff Pro is the most flexible way to create detail-rich inventories for your home or small business. Add items to MYStuff by dragging and dropping existing information, uploading new images, or... Read more
OmniOutliner Pro 4.6 - Pro version of th...
OmniOutliner Pro is a flexible program for creating, collecting, and organizing information. Give your creativity a kick start by using an application that's actually designed to help you think. It's... Read more

Lifeline: Crisis Line (Games)
Lifeline: Crisis Line 1.0.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0.0 (iTunes) Description: | Read more »
BLUK (Games)
BLUK 1.1 Device: iOS Universal Category: Games Price: $1.99, Version: 1.1 (iTunes) Description: ** ‘Upcoming Game of the Year’ Finalist at NGF Awards 2015 ** ** ‘Big Indie Pitch’ Nominee at Pocket Gamer Connects 2016... | Read more »
Six! Tips, tricks, and hints for beginne...
There's nothing pretentious about Six!, the latest release from Gram Games. As puzzlers go, it's so unassuming that it took me some time to even realize why it was called what it was. Somewhere, my high school geometry teacher is now sobbing... | Read more »
Mobius Final Fantasy: Multiplayer update...
Up until now, Mobius Final Fantasy has been about as single-player an experience as an RPG can be. Not only do you play solo, but you are literally one among a seemingly unending wave of faceless warriors on the road toward the same goal. [Read... | Read more »
Find out the story behind League of Ange...
If you’re looking for a new thrilling MMORPG to play with your friends then you’ll be excited to hear that there is a sequel to one of the most well known titles in the genre – namely League of Angels 2. With a brand new 3D engine offering... | Read more »
Naruto Shippuden: Ultimate Ninja Blazing...
I'm not sure if it's possible to say you are an anime fan but also never have seen one episode of Naruto. If it is, then I resemble that remark, and if not, I offer a hearty apology. [Read more] | Read more »
5 mobile games that let you explore spac...
No Man's Sky hasn't exactly turned out to be everything it was promised. Though its core concept of exploring an unimaginably vast universe of different planets is an intriguing one, the execution has left many PS4 and PC gamers feeling like they... | Read more »
Mummy madness in new action game Tomb He...
Hot on the tail of Bump Hero, ZPlay is giving gamers another reason to get screen bashing with a brand new release. Tomb Heroes is a challenging action game in which you battle enemies in various tombs around the world. You can select from nine... | Read more »
Siralim 2 (RPG / Roguelike) (Games)
Siralim 2 (RPG / Roguelike) 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Siralim 2 is an old-school monster catching RPG. Summon and customize hundreds of creatures to fight for you as... | Read more »
Clean Text (Productivity)
Clean Text 1.0 Device: iOS Universal Category: Productivity Price: $3.99, Version: 1.0 (iTunes) Description: | Read more »

Price Scanner via MacPrices.net

That Upgrade Itch; How Often Do You Upgrade Y...
I have a quandary to resolve as we head into what is typically a major Apple product upgrade season. My mid-2013 MacBook Air will be three years old and my iPad Air 2 will be two come late November.... Read more
Apple refurbished 12-inch iPad Pros available...
Apple has Certified Refurbished 12″ iPad Pros available for up to $160 off the cost of new iPads. An Apple one-year warranty is included with each model, and shipping is free: - 32GB 12″ iPad Pro... Read more
Tim Cook Posts Open Letter To The Apple Commu...
Apple Inc. CEO Tim Cook has posted an open letter commenting on the European Commission’s bizarre demand that Apple pay more than $14 billion in allegedly underpaid back taxes on its Irish... Read more
Streetwise Drivers Club App Now Features Open...
Streetwise Drivers Club, an app that rewards drivers with deals on everything from dining and gift cards to tires and insurance, has announced the launch of a new enhancement connecting app users... Read more
15-inch Retina MacBook Pros on sale for up to...
B&H Photo has 15″ Retina Apple MacBook Pros on sale for up to $200 off MSRP. Shipping is free, and B&H charges NY tax only: - 15″ 2.2GHz Retina MacBook Pro: $1799 $200 off MSRP - 15″ 2.5GHz... Read more
RESCUECOM 2016 Semi-Annual Computer Reliabili...
The beginning of a new school year is upon us again, in which students and parents have some very important choices to make, often including the purchase of a computer or tablet. Whether you are... Read more
VRS Design Damda Glide Series iPhone 7 and 7...
What makes the Damda Glide Series for the iPhone 7 and iPhone 7 Plus special? Case maker VRS Design says its Damda Glide Series is the first mobile case to incorporate a semi-automatic mechanism for... 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
Clearance 2015 13-inch MacBook Airs available...
B&H Photo has clearance 2015 13″ MacBook Airs available for $350 off original MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 1.6GHz/4GB/128GB MacBook Air (MJVE2LL/A): $829... Read more
Check Apple prices on any device with the iTr...
MacPrices is proud to offer readers a free iOS app (iPhones, iPads, & iPod touch) and Android app (Google Play and Amazon App Store) called iTracx, which allows you to glance at today’s lowest... Read more

Jobs Board

*Apple* Retail - Multiple Positions Norfolk,...
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* Retail - Multiple Positions- Tampa,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
SW Engineer *Apple* TV - Apple Inc. (United...
The Apple TV team is looking for excellent software engineers with experience in hardware, media management, media playback, content delivery and a passion for Read more
*Apple* Solutions Consultant - Apple (United...
Apple Solutions ConsultantJob Number: 51218534Pleasant Hill, California, United StatesPosted: Aug. 18, 2016Weekly Hours: 40.00Job SummaryAs an Apple Solutions Read more
*Apple* Solutions Consultant - Apple (United...
# Apple Solutions Consultant Job Number: 51218354 Fredericksburg, Virginia, United States Posted: Aug. 18, 2016 Weekly Hours: 40.00 **Job Summary** As an Apple Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.