New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Start learning with our library of video tutorials taught by experts. Get started

iOS SDK and SQLite: Building Data-Driven Apps
Illustration by Don Barnett

Designing a database schema


From:

iOS SDK and SQLite: Building Data-Driven Apps

with Bill Weinman

Video: Designing a database schema

Because we're using a database for this application, we must start with a database schema. A schema is simply a description of the structure of the database that contains descriptions of tables, columns, indexes, and other associated constructs that are part of the database. There are tools available for defining a schema for a database. But this database is simple, and we'll be writing it in SQL in a text editor, and creating the database using the SQLite 3 Command Line client. So, in your Chapter 2 folder in exercise files, you'll find a file called bwrss.sql, and I'm going to open that in my text editor.
Expand all | Collapse all
  1. 8m 29s
    1. Welcome
      57s
    2. Exercise files
      2m 17s
    3. Course overview
      3m 4s
    4. Application overview
      2m 11s
  2. 14m 49s
    1. Prototyping in a testbed
      1m 27s
    2. Building the view controller
      3m 45s
    3. Coding the testbed
      7m 56s
    4. Using the testbed
      1m 41s
  3. 37m 49s
    1. Understanding SQLite in iOS
      1m 41s
    2. Creating an Objective-C interface for SQLite
      9m 57s
    3. Testing the BWDB interface in the sandbox
      7m 1s
    4. Designing a database schema
      8m 7s
    5. Supporting the application with a specific interface
      7m 7s
    6. Using C pointers with automatic reference counting (ARC)
      3m 56s
  4. 21m 18s
    1. Understanding the table view
      1m 33s
    2. Creating the view controller
      6m 39s
    3. Reading from the database
      13m 6s
  5. 33m 50s
    1. Understanding the parsing process
      1m 57s
    2. Creating the item view controller
      12m 25s
    3. Reading data from the internet
      5m 30s
    4. Parsing the feed with NSXMLParser
      8m 2s
    5. Updating the item view with the feed items
      5m 56s
  6. 40m 14s
    1. Understanding the modal view
      1m 47s
    2. Constructing the view controller
      15m 5s
    3. Finding a feed link in a web page
      8m 55s
    4. Parsing the feed with NSXMLParser
      5m 4s
    5. Delegating back to the parent view
      6m 11s
    6. Deleting feeds
      3m 12s
  7. 21m 5s
    1. Creating the web view class
      12m 33s
    2. Coding the web view
      5m 25s
    3. Viewing pages in Safari
      3m 7s
  8. 14m 3s
    1. Understanding the iOS preferences system
      1m 23s
    2. Creating the preferences plist in Xcode
      7m 20s
    3. Reading preferences in your application
      5m 20s
  9. 6m 15s
    1. Adding pull-to-refresh functionality
      2m 34s
    2. Implementing the pull-to-refresh gesture for iOS 6
      3m 41s
  10. 27m 1s
    1. Understanding split view
      1m 4s
    2. Coding the table views
      11m 24s
    3. Implementing the iPad detail view
      6m 35s
    4. Implementing the iPad modal view
      7m 58s
  11. 35s
    1. Goodbye
      35s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
iOS SDK and SQLite: Building Data-Driven Apps
3h 45m Intermediate Jan 16, 2013

Viewers: in countries Watching now:

The iOS software development kit (SDK) includes the popular SQLite library, a lightweight yet powerful relational database engine that is easily embedded into an application. In this course, Bill Weinman teaches you how to build an RSS reader for iOS devices, integrating XML data and a streamlined interface. He explains how to use the SQLite database, display information in a table view, code view controllers, and create a preferences pane for your app. The resulting application is optimized for all iPhone and iPad displays.

Topics include:
  • Prototyping the app
  • Coding and working with a testbed
  • Creating an Objective-C interface for SQLite
  • Designing a database schema
  • Creating the view controllers
  • Reading and writing to the database
  • Parsing the RSS feed with NSXMLParser
  • Updating the item view with feed items
  • Implementing the pull to refresh gesture for iOS 6
  • Creating a universal application with multiple views
Subjects:
Developer Mobile Apps Databases
Software:
iOS
Author:
Bill Weinman

Designing a database schema

Because we're using a database for this application, we must start with a database schema. A schema is simply a description of the structure of the database that contains descriptions of tables, columns, indexes, and other associated constructs that are part of the database. There are tools available for defining a schema for a database. But this database is simple, and we'll be writing it in SQL in a text editor, and creating the database using the SQLite 3 Command Line client. So, in your Chapter 2 folder in exercise files, you'll find a file called bwrss.sql, and I'm going to open that in my text editor.

I'm using TextWrangler which is free, and it is a great text editor. You may use whatever text editor you like just make sure it is not a word processor. This is a very simple database. It has two tables, one for Feeds and one for Items. You'll notice here, at the top, we do DROP TABLE IF EXISTS feed, and DROP TABLE IF EXISTS item. This is SQL, and if you need a premier on SQL, I have a course on the lynda.com online training library called SQL Essential Training, which would help you in SQL.

There is also one specifically for SQLite called SQLite Essential Training with PHP, and that will give you a really good introduction to the SQLite Database engine. So we drop the tables if they exist, and then we create the tables. And each of these tables again is very simple, it has a unique ID for the record and they both have that URL for the data, title, description, the date of the last time it was updated, and a timestamp. For the Item, it is the same thing. There is a feed id which links back to the ID in the feed table that makes these two tables related to each other, and there's the URL of the item, title, description, publication date, and stamp.

They're really very simple tables. We create a UNIQUE INDEX for the feed so that we look them up by URL, which we actually do. And then we insert into the database Default feeds, one for the lynda.blog, one for the lynda.com New Releases, and one for my Technology Blog, which I update about once a year. So, that's the SQL for creating the database. It is really very simple. We call this a database schema because it describes the database itself, and it is also SQL so we can use it to actually create the database.

We are going to do that right now. I am going to quit my text editor, and I'm going to open up a terminal session. This is a terminal session on a Mac. It is basically a Unix Command Line, so I am going to be typing Unix commands. The Unix command cd stands for Change Directory, that allows me to navigate to a particular location as if I was clicking around in the Finder. So I am going to say cd Desktop/, and you'll notice that I just typed a couple of letters, and I pressed the Tab key, because this is the bash shell on Unix works like that and so it has something called Command Completion.

I can just type a few letters and press Tab, and it will complete things for me. And so it puts in that slash for the subdirectory, and I can type E-X-E and press Tab, and it will get my exercise files on the Desktop, which is what's right over here. And then I'm going to type capital C-H-A-- these are case-sensitive, so I'm actually typing the capital letters--and press Tab, and it takes me to Chap0 because there are several of them if I press Tab again you'll see I'll get a choice. So I can press the 2 and Tab again. It gets me to my Chapter 2 folder press Return, and there I am, if I type Pwd it will tell me where I am in the File System.

L-S will give me a listing of the folder or directory, and you notice it's got the same files here. At this point what I want to do is I want to take this SQL File, which is this one here that we're just looking at in our editor. I want to read that into the SQLite 3 Command Line Utility as a script to create a database. So the way I do that is I type sqlite3, which is the name of the Command Line Utility for SQLite 3, and it comes with your Mac. It is built-in.

I am going to give it the name of the database, I'm going to call it bwrss.db. You'll notice that we don't have that yet. It is not listed here in the files. It is not listed here in the files. There is a done version of it, which is exactly what we are going to be creating. Then I used the left angle bracket, which is a Pipe Command in Unix shell, which means to read-in this file I'm about to name and use that as if I were typing it. The file I am going to read-in is bwrss.sql.

This command here sqlite3 bwrss.db the left angle bracket, and bwrss.sql. That will read-in this sql file into this command. This command here is sqlite3 bwrss.db that will that will run the SQLite 3 Utility, it create this database file if it does not already exist and then read-in these commands and exit. I am going to press Enter, and you notice this doesn't take very long at all. If you saw over here, we now have this file bwrss.db.

If I type L-S here, you notice we now have that file here we did not have before bwrss.db. If I type L-S dash L bwrss.db, it will give us some statistics about this file, and you notice that it's 16,384 bytes in size. In fact, if I say ls-l bwrss-done. db, it is exactly the same size. That is the one that I created earlier.

The thing about SQLite is that the entire database is stored in this one file, unlike other database management systems where they have various files for indexes and for the different tables and sometimes even for columns. Everything is contained within this one file in SQLite 3, which is part of the beauty of it. It is in fact a platform agnostic file, which means that I can take this same file, and I can use it in Unix, I can use it in Windows, I can use it in any other platform, and be able to actually read it and write to it and use it with any SQLite 3 application.

Right now, I'm just going to use this Command Line Utility, sqlite3 bwrss.db, press Enter. I am going type in an sql statement, select * from sqlite_master. Semicolon terminates a command in SQL. I'm going to press Enter, and it describes what's in this database. So I have these two tables.

There is the actual SQL that was used to create them. I have this unique index. Also, I can look at the feed table. I can say select * from feed and a Semicolon, and there is the data inside that feed table. That's the data that we inserted using the SQL. If I come over here and open that up again, and you see down here at the bottom, we inserted these rows into the table. There we have them in our Command Line Utility.

So I type .quit, and that will exit the SQLite 3 Command Line Utility. Type exit here at Shell Prompt, and I can Quit Terminal now. I can quit TextWrangler. There we have it. We have created our database, and there it is in our bwrss.db file. That's the complete working SQLite 3 database. It is really all there is to it, the entire database is contained in this one file.

The file was completely cross-platform, so no matter what operating system you use to create it, it will work wherever you want to use it. All we need to do now is copy this file into the App and use it, and we'll do that in the next movie.

Find answers to the most frequently asked questions about iOS SDK and SQLite: Building Data-Driven Apps.


Expand all | Collapse all
please wait ...
Q: Why is the RSSDB library in the exercise files different than the one in the videos?

A: The RSSDB library had to be updated to work around a bug in the iOS 7 SDK.

There is a bug in the iOS 7 SDK that prevents the BWDB fast enumeration implementation from working on a device. The symptom is code that runs fine on the emulator, but not on a device. iOS devices use an ARM processor, while the emulator runs on your Mac's Intel processor. This points to the LLVM ARM code generator as the source of the bug. Because the bug appears to be in the LLVM compiler, it may be some time before it is fixed.

As a workaround we have changed the getFeedIDs and getItemIDs methods in the RSSDB library so they don't use Objective C fast enumeration. 

Please note that this same bug also affects some of the BWDB testbed code in Chapter 2. The result is that it will run on the emulator but not on a device.

Q: After upgrading to Xcode 5.1 I get an error that says:

"Used type va_list (aka_builtin_va_list) where arithmetic or pointer type is required"

A: Please download the exercise files again to get the latest version of the BWDB library.
 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

join now Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed iOS SDK and SQLite: Building Data-Driven Apps.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.