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

MacUpdate Desktop 6.0.8 - Search and ins...
MacUpdate Desktop 6 brings seamless 1-click installs and version updates to your Mac. With a free MacUpdate account and MacUpdate Desktop 6, Mac users can now install almost any Mac app on macupdate.... Read more
BitTorrent Sync 2.1.1 - Sync files secur...
BitTorrent Sync allows you to sync unlimited files between your own devices, or share a folder with friends and family to automatically sync anything. File transfers are encrypted. Your information... Read more
Quicksilver 1.3.0 - Application launcher...
Quicksilver is a light, fast and free Mac application that gives you the power to control your Mac with keystrokes alone. Quicksilver allows you to find what you need quickly and easily, then act... Read more
iWatermark Pro 1.72 - Easily add waterma...
iWatermark Pro is the essential watermarking app for professional, business, and personal use. iWatermark, is the number 1 and only watermarking tool available for all 4 platforms iPhone/iPad, Mac,... Read more
RapidWeaver 6.3 - Create template-based...
RapidWeaver is a next-generation Web design application to help you easily create professional-looking Web sites in minutes. No knowledge of complex code is required, RapidWeaver will take care of... Read more
Adobe Photoshop CC 2015 16.0.1 - Profess...
Photoshop CC 2015 is available as part of Adobe Creative Cloud for as little as $19.99/month (or $9.99/month if you're a previous Photoshop customer). Photoshop CS6 is still available for purchase (... Read more
Together 3.4.6 - Store and organize all...
Together helps you organize your Mac, giving you the ability to store, edit and preview your files in a single clean, uncluttered interface. Smart storage. With simple drag-and-drop functionality,... Read more
Monosnap 3.1.0 - Versatile screenshot ut...
Monosnap lets you capture screenshots, share files, and record video and .gifs! Capture: Capture full screen, just part of the screen, or a selected window Make your crop area pixel perfect with... Read more
Cocktail 8.5.1 - General maintenance and...
Cocktail is a general purpose utility for OS X that lets you clean, repair and optimize your Mac. It is a powerful digital toolset that helps hundreds of thousands of Mac users around the world get... Read more
Vienna 3.0.6 :5eaf312: - RSS and Atom ne...
Vienna is a freeware and Open-Source RSS/Atom newsreader with article storage and management via a SQLite database, written in Objective-C and Cocoa, for the OS X operating system. It provides... Read more

Five Nights at Freddy's 4 has Been...
In keeping with tradition, Five Nights at Freddy's 4 has made its way to the App Store with zero warning fanfare. Honestly I'd be disappointed it if happened any other way at this point. [Read more] | Read more »
Doom & Destiny Advanced (Games)
Doom & Destiny Advanced 2.3.4.0 Device: iOS Universal Category: Games Price: $.99, Version: 2.3.4.0 (iTunes) Description: ---SUPER GIGA LAUNCH DISCOUNT! Get it now for cheap or pay more later!---An additional 143 MB download is... | Read more »
Five Nights at Freddys 4 (Games)
Five Nights at Freddys 4 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: This time, the terror has followed you home. | Read more »
This Week at 148Apps: July 27-31, 2015
Winding Down July With 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 »
You'll Want to Keep an Eye Out for...
If you're the kind of person who had fun hunting down and completing all the codex puzzles in Assassin's Creed 2, then are you ever in for a treat. The Guides looks like it's going to be a very robust collection of similarily odd, seemingly... | Read more »
Vivid Games has Announced Real Boxing 2...
The original Real Boxing was a pretty impressive bit of fisticuffs, but if the trailer Vivid Games is showing off for GamesCom is any indication Real Boxing 2 is going to be even better. [Read more] | Read more »
PAC-MAN Championship Edition DX - Tips,...
[Read more] | Read more »
Card King: Dragon Wars - Tips, Tricks an...
[Read more] | Read more »
Pac-Man Championship Edition DX has brou...
Bandai Namco has released Pac-Man Championship Edition DX on iOS and Android, which features the classic arcade gameplay that we've all grown to love. Pac-Man Championship Edition DX can be enjoyed in much shorter bursts than the arcade versions... | Read more »
Cosmonautica (Games)
Cosmonautica 1.1 Device: iOS Universal Category: Games Price: $6.99, Version: 1.1 (iTunes) Description: Cast off! Are you ready for some hilarious adventures in outer space? | Read more »

Price Scanner via MacPrices.net

Back to School Time: Outline for iPad Goes Fr...
Sacramento, California based Gorillized Corporation has announced a “Back to School” offer; the iPad version of Outline goes free for August 3-5. Outline is a smart digital notebook for iPad that... Read more
MacBook 12 Retina Pundit’s New Favorite Mac
If you’re eyeing the purchase of a new 12-inch Retina MacBook, but wondering if you can live with a single USB-C port, 2011-level CPU performance, and the unorthodox keyboard, you should find... Read more
15-inch 2.2GHz Retina MacBook Pro on sale for...
Adorama has the 2015 15″ 2.2GHz Retina MacBook Pro on sale for $184 off MSRP including free shipping plus NY & NJ sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $1815 $184 off Adorama will also... Read more
PDF Element Tool Kit For PDF For Windows 10,...
South Surrey, British Columbia based software developer Wondershare has posted an interesting infographic tracking the development of Microsoft’s flagship Windows operating system over the years,... Read more
27-inch 3.5GHz 5K iMac on sale for $81 off MS...
Adorama has the 27″ 3.5GHz 5K iMac on sale for $2218, $81 off MSRP, including a free copy of Apple’s 3-Year AppleCare Protection Plan. Shipping is free, and Adorama charges sales tax in NY & NJ... Read more
Back-to-School with Tablet and Smartphone Acc...
Belkin helps you prepare for the coming school year with a wide variety of the latest mobile and tablet accessories to outfit both grade school and college students. The line-up includes charging... Read more
11-inch MacBook Airs on sale for $100 off MSR...
Best Buy has 11-inch MacBook Airs on sale for $100 off MSRP. Choose free shipping or free local store pickup (if available). Sale prices for online orders only, in-store prices may vary: - 11″ 1.6GHz... Read more
iPad Air 2 on sale for up to $100 off MSRP
Best Buy has iPad Air 2s on sale for up to $100 off MSRP on their online store for a limited time. Choose free shipping or free local store pickup (if available). Sale prices available for online... Read more
Sale! 13-inch MacBook Pros on sale for $100 o...
B&H Photo has 13″ MacBook Pros on sale for $100 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.5GHz/500GB MacBook Pro: $999.99 save $100 - 13″ 2.7GHz/128GB Retina... Read more
Sale! Save $100 on 13-inch MacBook Airs this...
B&H Photo has the 13″ 1.6GHz/128GB MacBook Air on sale for $899.99 including free shipping plus NY tax only. Their price is $100 off MSRP, and it’s the lowest price available for this model.... 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
Infrastructure Engineer - *Apple* /Mac - Hil...
Infrastructure Engineer - Apple /Mac Job Code: 1608 # of openings: 1 Description Our fortune 500 client is looking to hire an experienced Infrastructure Engineer to join Read more
Executive Administrative Assistant, *Apple*...
…supporting presentation development for senior leadership. * User experience with Apple hardware and software is preferred. Additional Requirements The following list Read more
*Apple* Bus Company is now hirin - Apple Bus...
Apple Bus Company is now hiring school bus drivers in the Pettis County area. Class B CDL preferred. Free training provided. No nights or weekends required. Flexible Read more
*Apple* Certified Mac Technician - Updated 6...
…and friendly, hands-on technical support to customers troubleshooting and repairing Apple /Mac products with courtesy, speed and skill. Use your problem-solving skills Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.