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

Arq 5.7.2 - Online backup to Google Driv...
Arq is super-easy online backup for Mac and Windows computers. Back up to your own cloud account (Amazon Cloud Drive, Google Drive, Dropbox, OneDrive, Google Cloud Storage, any S3-compatible server... Read more
Spotify 1.0.47.13. - Stream music, creat...
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
Together 3.7.3 - 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. Features Smart storage. With simple drag-and-drop... Read more
Cocktail 10.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
Sierra Cache Cleaner 11.0.3 - Clear cach...
Sierra Cache Cleaner is an award-winning general purpose tool for macOS X. SCC makes system maintenance simple with an easy point-and-click interface to many macOS X functions. Novice and expert... Read more
NeoFinder 7.0.1 - Catalog your external...
NeoFinder (formerly CDFinder) rapidly organizes your data, either on external or internal disks, or any other volumes. It catalogs all your data, so you stay in control of your data archive or disk... Read more
Sketch 42 - Design app for UX/UI designe...
Sketch is an innovative and fresh look at vector drawing for the Mac. Its intentionally minimalist design is based upon a drawing space of unlimited size and layers, free of palettes, panels, menus,... Read more
Quicken 4.4.3 - Complete personal financ...
Quicken makes managing your money easier than ever. Whether paying bills, upgrading from Windows, enjoying more reliable downloads, or getting expert product help, Quicken's new and improved features... Read more
Apple Safari 10.0.3 - Apple's Web b...
Note: The direct download link is currently unavailable. It is available in the OS X 10.11.6 release, as well as in the Apple Security Updates. Apple Safari is Apple's web browser that comes with OS... Read more
Apple iTunes 12.5.5 - Play Apple Music a...
Apple iTunes lets you organize and stream Apple Music, download and watch video and listen to Podcasts. It can automatically download new music, app, and book purchases across all your devices and... Read more

Clash Royale gets some serious balance u...
| Read more »
Ironhide Game Studio prepares for a busy...
Kingdom Rush breathed fresh life into the tired tower defense genre way back in 2012. The game was a robust challenge that somehow managed to lift you up, rather than leaving you feeling crushed and hopeless. The rich array of unit types and... | Read more »
Collect pets and sling arrows in Arcane...
Mobile gaming is a crowded market, but regular updates are a good way to keep us attention-short players keen. The brand new content in Arcane Online is a prime example. Published by Japanese developer Gala, Arcane Online is a fantasy MMO that... | Read more »
Super Mario Run dashes onto Android in M...
Super Mario Run was one of the biggest mobile launches in 2016 before it was met with a lukewarm response by many. While the game itself plays a treat, it's pretty hard to swallow the steep price for the full game. With that said, Android users... | Read more »
WarFriends Beginner's Guide: How to...
Chillingo's new game, WarFriends, is finally available world wide, and so far it's a refreshing change from common mobile game trends. The game's a mix of tower defense, third person shooter, and collectible card game. There's a lot to unpack here... | Read more »
Super Gridland (Entertainment)
Super Gridland 1.0 Device: iOS Universal Category: Entertainment Price: $1.99, Version: 1.0 (iTunes) Description: Match. Build. Survive. "exquisitely tuned" - Rock Paper Shotgun No in-app purches, and no ads! | Read more »
Red's Kingdom (Games)
Red's Kingdom 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Mad King Mac has kidnapped your father and stolen your golden nut! Solve puzzles and battle goons as you explore and battle your... | Read more »
Turbo League Guide: How to tame the cont...
| Read more »
Fire Emblem: Heroes coming to Google Pla...
Nintendo gave us our first look at Fire Emblem: Heroes, the upcoming mobile Fire Emblem game the company hinted at last year. Revealed at the Fire Emblem Direct event held today, the game will condense the series' tactical RPG combat into bite-... | Read more »
ReSlice (Music)
ReSlice 1.0 Device: iOS Universal Category: Music Price: $9.99, Version: 1.0 (iTunes) Description: Audio Slice Machine Slice your audio samples with ReSlice and create flexible musical atoms which can be triggered by MIDI notes or... | Read more »

Price Scanner via MacPrices.net

HDRtist NX 1.0 For macOS Next Generation HDR...
Ohanaware Co., Ltd. have announced the immediate release of their latest High Dynamic Range Imaging software “HDRtist NX”. HDRtist NX is the third installment in the HDRtist series of applications... Read more
Built To Last Maverick Leather Laptop Messeng...
Wild West melds with modern in San Francisco designer-manufacturer of custom laptop sleeves, bags and cases WaterField Designs’ new Maverick Leather Laptop Messenger. This luxury laptop bag blends... Read more
Logitech Sets Company Record With 15 GOOD DES...
Logitech has announced that it has won 15 GOOD DESIGN 2016 awards. This marks the sixth consecutive year Logitech products have been recognized by the GOOD DESIGN awards, and the most award wins for... Read more
13-inch Touch Bar MacBook Pros in stock for $...
B&H Photo has the new 2016 13″ Touch Bar MacBook Pros in stock today and on sale for up to $150 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.9GHz/512GB Touch Bar... Read more
9-inch 32GB Space Gray iPad Pro on sale for $...
B&H Photo has the 9.7″ 32GB Space Gray Apple iPad Pro on sale for $549 for a limited time. Shipping is free, and B&H charges NY sales tax only. Read more
15-inch 2.2GHz Retina MacBook Pro on sale for...
Amazon has 2015 15″ 2.2GHz Retina MacBook Pros (MJLQ2LL/A) on sale for $1749.99 including free shipping. Apple charges $1999 for this model, so Amazon’s price is represents a $250 savings. Read more
Deal alert! 13-inch 2.0GHz MacBook Pros for $...
B&H Photo has the new 2016 13″ 2.0GHz non-Touch Bar MacBook Pros in stock today and on sale for $225 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 13″ 2.0GHz MacBook Pro... Read more
Free LibreOffice Portable 5.2.4 Complete Offi...
PortableApps.com and The Document Foundation have announce the release of LibreOffice Portable 5.2.4. LibreOffice Portable is an Open Source full-featured office suite — including a word processor,... Read more
Apple Planning Three New Tablets For 2017 – D...
Digitimes’ Rebecca Kuo and Joseph Tsai say that unnamed insider sources report Apple having three new tablets in the pipeline for 2017 release: a 9.7-inch model with a friendly price range, a new mid... Read more
Roundup of 15-inch Touch Bar MacBook Pro sale...
B&H Photo has the new 2016 15″ Apple Touch Bar MacBook Pros in stock today and on sale for up to $150 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.7GHz Touch Bar... 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* & PC Desktop Support Technician...
Apple & PC Desktop Support Technician job in Manhattan, NY Introduction: We have immediate job openings for several Desktop Support Technicians with one of our most Read more
*Apple* & PC Desktop Support Technician...
Apple & PC Desktop Support Technician job in Stamford, CT We have immediate job openings for several Desktop Support Technicians with one of our most well-known 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
*Apple* Site Security Manager - Apple (Unite...
# Apple Site Security Manager Job Number: 54692472 Culver City, California, United States Posted: Jan. 19, 2017 Weekly Hours: 40.00 **Job Summary** The Apple Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.