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

Google Drive 1.20 - File backup and shar...
Google Drive is a place where you can create, share, collaborate, and keep all of your stuff. Whether you're working with a friend on a joint research project, planning a wedding with your fiancé, or... Read more
Simon 4.0.3 - Monitor changes and crashe...
Simon monitors websites and alerts you of crashes and changes. Select pages to monitor, choose your alert options, and customize your settings. Simon does the rest. Keep a watchful eye on your... Read more
Vitamin-R 2.23 - Personal productivity t...
Vitamin-R creates the optimal conditions for your brain to work at its best by structuring your work into short bursts of distraction-free, highly focused activity alternating with opportunities for... Read more
iDefrag 5.0.0 - Disk defragmentation and...
iDefrag helps defragment and optimize your disk for improved performance. Features include: Supports HFS and HFS+ (Mac OS Extended). Supports case sensitive and journaled filesystems. Supports... Read more
PCalc 4.2 - Full-featured scientific cal...
PCalc is a full-featured, scriptable scientific calculator with support for hexadecimal, octal, and binary calculations, as well as an RPN mode, programmable functions, and an extensive set of unit... Read more
FileZilla 3.10.2 - Fast and reliable FTP...
FileZilla (ported from Windows) is a fast and reliable FTP client and server with lots of useful features and an intuitive interface. Version 3.10.2: Note: Now requires a 64-bit Intel processor.... Read more
The Hit List 1.1.11 - Advanced reminder...
The Hit List manages the daily chaos of your modern life. It's easy to learn - it's as easy as making lists. And it's powerful enough to let you plan, then forget, then act when the time is right.... Read more
Bartender 1.2.32 - Organize your menu ba...
Bartender lets you organize your menu bar apps. Features: Lets you tidy your menu bar apps how you want. See your menu bar apps when you want. Hide the apps you need to run, but do not need to... Read more
ClamXav 2.7.5 - Free virus checker, base...
ClamXav is a free virus checker for OS X. It uses the tried, tested, and very popular ClamAV open source antivirus engine as a back end. I hope you like and use ClamXav a lot and that it helps keep... Read more
xScope 4.1.2 - Onscreen graphic measurem...
xScope is powerful set of tools that are ideal for measuring, inspecting, and testing on-screen graphics and layouts. Its tools float above your desktop windows and can be accessed via a toolbar,... Read more

Dungeon Hunter 5 Coming on March 12
Gameloft has excitedly announced that Dungeon Hunter 5 is on its way! Once again, you will adventure across the land of Valenthia exploring dungeons and fighting monsters. The game will have a new asynchronous multiplayer mode called Strongholds... | Read more »
It Came From Canada: Jurojin: Immortal N...
At this point it’s pretty safe to say that no MOBA is going to dethrone Dota 2 and League of Legends anytime soon. After all, if Batman can’t do it, nobody can. However, with a genre as popular and profitable as this one, there’s still room for... | Read more »
Tiny Farm – Animals, Tractors and Advent...
Tiny Farm – Animals, Tractors and Adventures! Review By Amy Solomon on March 3rd, 2015 Our Rating: :: LIVELY LANDSCAPESUniversal App - Designed for iPhone and iPad Tiny Farm – Animals, Tractors and Adventures! includes farm-related... | Read more »
This Week at 148Apps: February 23-27, 20...
Final February Fun at 148Apps   How do you know what apps are worth your time and money? Just look to the review team at 148Apps. We sort through the chaos and find the apps you’re looking for. The ones we love become Editor’s Choice, standing out... | Read more »
GDC 2015 – Does Not Commute is Definitel...
GDC 2015 – Does Not Commute is Definitely a Game You Should Keep an Eye on Posted by Rob Rich on March 2nd, 2015 [ permalink ] We were teased about Mediocre Games’ (Smash Hit, | Read more »
F84 Games & POW! Announce Stan Lee V...
F84 Games has announced that it is working with legendary comic creator Stan Lee and POW! Entertainment to produce Stan Lee’s Hero Command. The game will be a action adventure of heroic proportions. | Read more »
Setlyst Keeps Your Set Straight So You C...
Setlyst Keeps Your Set Straight So You Can Focus On Rocking Out. Posted by Jessica Fisher on March 2nd, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Space is Vast, So Space Agency Has a Vas...
Space is Vast, So Space Agency Has a Vast New Update! Posted by Jessica Fisher on March 2nd, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Size DOES Matter Review
Size DOES Matter Review By Campbell Bird on March 2nd, 2015 Our Rating: :: HARD TO BEATUniversal App - Designed for iPhone and iPad This rhythm game has a unique control scheme and performance system that make it feel like a true... | Read more »
The first ever action 3D card battler Al...
On the other hand, you probably haven’t played an action 3D card battler – until now. Step forward, All Star Legion. All Star Legion is a 3D QTE-based action RPG card battler, but fear not – the game itself isn’t as convoluted as its description.... | Read more »

Price Scanner via MacPrices.net

Another Tranche Of IBM MobileFirst For iOS Ap...
IBM has announced the next expansion phase for  its IBM MobileFirst for iOS portfolio, with a troika of new apps to address key priorities for the Banking and Financial Services, Airline and Retail... Read more
Sale! 15-inch Retina MacBook Pros for up to $...
B&H Photo has the new 2014 15″ Retina MacBook Pros on sale for up to $250 off MSRP for a limited time. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $... Read more
WaterField Designs Introduces the Minimalist...
With Apple Pay gaining popularity, Android Pay coming in May 2015, and loyalty cards and receipts that can be accessed from smartphones, San Francisco’s WaterField Designs observes that it may be... Read more
Sale! 15-inch 2.2GHz Retina MacBook Pro for $...
 Best Buy has the 15″ 2.2GHz Retina MacBook Pro on sale for $1774.99 $1799.99, or $225 off MSRP. Choose free home shipping or free local store pickup (if available). Price valid for online orders... Read more
13-inch 2.5GHz MacBook Pro (refurbished) avai...
The Apple Store has Apple Certified Refurbished 13″ 2.5GHz MacBook Pros available for $170 off the cost of new models. Apple’s one-year warranty is standard, and shipping is free: - 13″ 2.5GHz... Read more
13-inch 2.5GHz MacBook Pro on sale for $100 o...
B&H Photo has the 13″ 2.5GHz MacBook Pro on sale for $999.99 including free shipping plus NY sales tax only. Their price is $100 off MSRP. Read more
27-inch 3.5GHz 5K iMac in stock today and on...
 B&H Photo has the 27″ 3.5GHz 5K iMac in stock today and on sale for $2299 including free shipping plus NY sales tax only. Their price is $200 off MSRP, and it’s the lowest price available for... Read more
Apple Launches Free Web-Based Pages and Other...
Apple’s new Web-only access to iWork productivity apps is a free level of iCloud service available to anyone, including people who don’t own or use Apple devices. The service includes access to Apple... Read more
Survey Reveals Solid State Disk (SSD) Technol...
In a recent SSD technology use survey, Kroll Ontrack, a firm specializing in data recovery, found that while nearly 90 percent of respondents leverage the performance and reliability benefits of SSD... Read more
Save up to $600 with Apple refurbished Mac Pr...
The Apple Store is offering Apple Certified Refurbished Mac Pros 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... Read more

Jobs Board

*Apple* Solutions Consultant - Retail Sales...
**Job Summary** As an Apple Solutions Consultant (ASC) you are the link between our customers and our products. Your role is to drive the Apple business in a retail Read more
*Apple* Pay Automation Engineer - iOS System...
**Job Summary** At Apple , great ideas have a way of becoming great products, services, and customer experiences very quickly. Bring passion and dedication to your job Read more
Sr. Technical Services Consultant, *Apple*...
**Job Summary** Apple Professional Services (APS) has an opening for a senior technical position that contributes to Apple 's efforts for strategic and transactional Read more
Event Director, *Apple* Retail Marketing -...
…This senior level position is responsible for leading and imagining the Apple Retail Team's global engagement strategy and team. Delivering an overarching brand Read more
*Apple* Pay - Site Reliability Engineer - Ap...
**Job Summary** Imagine what you could do here. At Apple , great ideas have a way of becoming great products, services, and customer experiences very quickly. Bring Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.