If you are a new visitor to our site, please read the Getting Started guide and the tableView Workshop series.
The main goal of this tutorial series is to show how to use the SQLite engine in an iOS application to execute these fundamental SQL commands:
CREATE TABLE – This command is for creating a table in a SQLite database file.
SELECT – This command is for fetching one or more records from a single or multiple tables.
INSERT – This command is for inserting new records in the database table.
UPDATE – This command is for updating the database’s records.
DELETE – This command is for deleting a single record or all records from the database.
JOIN – This command is for fetching rows or records from two or more tables.
Each post in the tutorial series contain these sections:
- An expository section which contains reading material, diagrams and examples designed to introduce and explain SQLite concepts and commands.
- Code which you will make the application’s views function.
Let us start by explaining what a database, SQLite, and SQL is.
What is a Database?
A database is the storage and retrieval of large quantities of related data. You can create a database using a database management application such as the SQLiteManager application, or programmatically, using the SQLite engine.
A database can contain a single table, such as a the one shown in the image below. A database table consist of columns and rows. Each column contains a different type of attribute and each row corresponds to a single record or row.
A database can contain multiple tables as shown in this diagram. This kind of database is known as a Relational Database Management System (RDBMS). A Relational Database Management System enables an iOS application to store and manage the application’s data in one or more tables, and process simple to complex queries using functions of the SQLite engine.
You establish a relationship between the database tables by a Primary Key – Foreign Key pair.
Primary Key
A primary key is used to uniquely identify each record in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record).
Foreign Key
A foreign key is a field that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
There are three forms of table relationships: one-to-one, one-to-many, and many-to-many. The following section explains each one.
One-to-One Relationship
A one-to-one relationship occurs between two tables where the primary key (Emp Id) only appear once in another table. For example, each employee is assigned only one computer within a company.
One-to-Many Relationship
A one-to-many relationship is the most common type of relationship. This type of relationship occurs between two tables where the primary key (Phone Number) in Table 1 can appear multiple times in Table 2. For example, a customer can rent one or more videos.
Many-to-Many Relationship
In a many-to-many relationship, the primary key in Table 1 can appear many times in Table 2. On the flip side, the primary key from Table 2 can also appear many times in Table 1. You create such a relationship by defining a third table, called a junction table (rentals), whose primary key consists of the foreign keys from both Table 1 and Table 2. For example, many customers can rent many videos. The flip side is, many videos are rented by many customers.
When you write code to pull records (rows) from multiple tables, you make use of above mentioned relationships. This operation is known as a SQL JOIN condition.
What is SQLite
SQLite is a standalone, database engine that provides the ability to create and manage a Relational Database Management System (RDMS). The neat thing about the SQLite engine is that, it packages the entire RDMS in a single file. The single file contains the database layout and records held in one or more tables. The SQLite engine is designed to be embedded directly into an iOS App.
SQLite does not required a separate server system to operate. The engine access the database file directly. If you need to move or back up the database, you simply copy the file. By the way, the SQLite engine contain many C data structures and C functions you can use in an iOS application. However you’ll only need to learn how to use a handful of them.
What is SQL?
SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is an English-like language functions of the SQLite engine use to execute SQL commands. SQL commands can be used to search a database and perform other tasks such as adding, updating, and deleting records stored in a SQLite database file. SQL commands are grouped into four major categories depending on their functionality:
- Data Definition Language (DDL) – These SQL commands are used for creating, modifying, and dropping the structure of a SQLite database file. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
- Data Manipulation Language (DML) – These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
- Transaction Control Language (TCL) – These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
- Data Control Language (DCL) – These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.
Here is a list of SQL commands the SQLite engine does not implement.
| LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN. |
| Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are implemented. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth aren’t implemented. |
| FOR EACH ROW triggers are implemented but not FOR EACH STATEMENT triggers. |
| VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger. |
| The GRANT and REVOKE commands aren’t implemented because they would be meaningless, since the SQLite engine reads and writes an ordinary disk file. |
The Xcode Project
I’ve created an Xcode project for this tutorial series; so click the link below to download it. After unzipping the file drag and drop the project’s folder in the iOS7 Projects folder.
Add this image in the application’s Images.xcassets folder.
Next, add these images in the Simulator’s Photos app.
 |
 |
 |
 |
 |
 |
 |
If you forgot how to do that, then read the section: Add Photos in The Device’s Photo Library of this post.
That’s all for this week. In next week’s post you will begin your journey on learning how to use the SQLite engine’s constants and functions in the Xcode project you’ve downloaded on your Mac.