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 Chrome 63.0.3239.108 - Modern and...
Google Chrome is a Web browser by Google, created to be a modern platform for Web pages and applications. It utilizes very fast loading of Web pages and has a V8 engine, which is a custom built... Read more
Paparazzi! 1.0b7 - Make user-defined siz...
Paparazzi! is a small utility for OS X that makes screenshots of webpages. This very simple tool takes screenshots of websites which do not fit on one screen. You specify the desired width, minimal... Read more
Remotix 5.0.4 - Access all your computer...
Remotix is a fast and powerful application to easily access multiple Macs (and PCs) from your own Mac. Features Complete Apple Screen Sharing support - including Mac OS X login, clipboard... Read more
Apple Configurator 2.6 - Configure and d...
Apple Configurator makes it easy to deploy iPad, iPhone, iPod touch, and Apple TV devices in your school or business. Use Apple Configurator to quickly configure large numbers of devices connected to... Read more
Amadeus Pro 2.4.4 - Multitrack sound rec...
Amadeus Pro lets you use your Mac for any audio-related task, such as live audio recording, digitizing tapes and records, converting between a variety of sound formats, etc. Thanks to its outstanding... Read more
Mellel 4.0.3 - The word processor for sc...
Mellel is the leading word processor for OS X and has been widely considered the industry standard for long form documents since its inception. Mellel focuses on writers and scholars for technical... Read more
Suitcase Fusion 8 19.0.3 - Font manageme...
Suitcase Fusion 8 is the creative professional's font manager. Every professional font manager should deliver the basics: spectacular previews, powerful search tools, and efficient font organization... Read more
Final Cut Pro X 10.4 - Professional vide...
Final Cut Pro X is a professional video editing solution. Completely redesigned from the ground up, Final Cut Pro adds extraordinary speed, quality, and flexibility to every part of the post-... Read more
Compressor 4.4 - Adds power and flexibil...
Compressor adds power and flexibility to Final Cut Pro X export. Customize output settings, work faster with distributed encoding, and tap into a comprehensive set of delivery features. Features... Read more
Cocktail 11.2 - General maintenance and...
Cocktail is a general purpose utility for macOS 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

Latest Forum Discussions

See All

Match blocks to pull off dance moves in...
Ferdinand: Unstoppabull is a brand new match three puzzler based on the animated movie of (almost) the same name. As you can expect, you have to match blocks together to complete a bunch of puzzling levels and earn a high score. [Read more] | Read more »
Lineage 2: Revolution’s end of year upda...
Now available in 54 countries worldwide, Lineage 2: Revolution is continuing its global quest to be the most popular mobile MMORPG by launching a jam-packed end of year update. Complete with many subtle tweaks to help improve users’ online... | Read more »
The 5 best Star Wars games on iOS
The time has almost come.Star Wars: The Last Jedifinally hits theaters in the cinematic event that might be bigger than Christmas. To celebrate, we're taking a look at the best--and only the best--Star Warsmobile games to date. [Read more] | Read more »
Life Is Strange (Games)
Life Is Strange 1.1 Device: iOS Universal Category: Games Price: $2.99, Version: 1.1 (iTunes) Description: Life Is Strange is a five part episodic game that sets out to revolutionize story-based choice and consequence games by... | Read more »
Oddworld: New 'n' Tasty (Game...
Oddworld: New 'n' Tasty 1.0 Device: iOS Universal Category: Games Price: $7.99, Version: 1.0 (iTunes) Description: ** PLEASE NOTE: Requires 3.6GB free space to install. Runs at variable resolutions based on device capabilities.... | Read more »
Gorogoa (Games)
Gorogoa 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Gorogoa is an elegant evolution of the puzzle genre, told through a beautifully hand-drawn story designed and illustrated by Jason... | Read more »
Why Guns of Boom will be big for mobile...
Earlier this week, Game Insight, the minds that brought you Guns of Boom, revealed plans for an esports mode in the popular FPS title, with big implications for the game's future. Guns of Boom has been quite popular for some time now, so it's... | Read more »
The best mobile games to play on lazy ho...
With the holidays in full swing, there's hopefully going to be a lot of time off work lazing around the house. With all of that free time, it's a perfect opportunity to catch up on some mobile games that you might have missed out on earlier this... | Read more »
Rules of Survival guide - how to boost y...
It's not easy surviving in the "every-man-for-himself" world of Rules of Survival. You'll be facing off against many other players who might be more skilled than you, or are luckier than you. There are a lot of factors weighing against you. With... | Read more »
FEZ Pocket Edition (Games)
FEZ Pocket Edition 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: | Read more »

Price Scanner via MacPrices.net

Updated Price Trackers: Macs, iPads, iPhones,...
Scan our Apple Price Trackers for the latest information on sales, bundles, and availability on systems from Apple’s authorized internet/catalog resellers. We update the trackers continuously: – 15″... Read more
How to preorder a new iMac Pro and pay zero s...
B&H Photo and Adorama are accepting preorders on multiple configurations of the new Apple iMac Pro. Both resellers charge sales tax for residents of NY & NJ only, and shipping is free.... Read more
Apple Macs back in stock at Amazon with model...
Amazon has MacBook Pros, MacBook Airs, MacBooks, and iMacs on sale for up to $200 off MSRP as part of their Holiday/Christmas sale. Shipping is free. Note that stock of some Macs may come and go (and... Read more
Apple offering free overnight delivery on all...
Apple is now offering free overnight delivery on all in stock products until 3pm local time on December 22nd. This includes new as well as refurbished computers. Click here for more information. Read more
Beats Holiday sale at B&H, headphones and...
B&H Photo has Beats by Dr. Dre headphones, earphones, and speakers on sale for up to $80 off MSRP as part of their Holiday sale. Expedited shipping is free, and B&H charges sales tax to NY... Read more
Holiday sale: Apple resellers offer 2017 15″...
MacMall has 15″ MacBook Pros on sale for $220-$300 off MSRP, each including free shipping: – 15″ 2.8GHz MacBook Pro Space Gray (MPTR2LL/A): $2179, $220 off MSRP – 15″ 2.8GHz MacBook Pro Silver (... Read more
Holiday sale: Apple resellers offer 13″ MacBo...
B&H Photo has 13″ MacBook Pros on sale for up to $150 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13-inch 2.3GHz/128GB Space Gray MacBook Pro (... Read more
Apple Watch Series 2, Certified Refurbished,...
Apple has Certified Refurbished Apple Watch Nike+ Series 2s, 42mm Space Gray Aluminum Case with Anthracite/Black Nike Sport Bands, available for $249 (38mm) or $279 (42mm). The 38mm model was out of... Read more
Apple offers Certified Refurbished 2016 12″ R...
Apple has Certified Refurbished 2016 12″ Retina MacBooks available starting at $949. Apple will include a standard one-year warranty with each MacBook, and shipping is free. The following... Read more
B&H drops price on 13″ 256GB MacBook Air...
B&H has the 13″ 1.8GHz/256GB Apple MacBook Air (MQD42LL/A) now on sale for $1079 including free shipping plus NY & NJ sales tax only. Their price is $120 off MSRP, and it’s the lowest price... Read more

Jobs Board

*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
*Apple* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, 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
Payments Counsel, *Apple* Pay (payments, cr...
# Payments Counsel, Apple Pay (payments, credit/debit) Job Number: 112941729 Santa Clara Valley, California, United States Posted: 13-Dec-2017 Weekly Hours: 40.00 Read more
*Apple* Solutions Consultant - Apple (United...
# Apple Solutions Consultant Job Number: 113124408 Waterford, CT, Connecticut, United States Posted: 17-Oct-2017 Weekly Hours: 40.00 **Job Summary** Are you Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.