Designing a database schema
Video: Designing a database schemaBecause 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.
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.
- 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
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 (2013) .
Here are the FAQs that matched your search "" :
- 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.
- Q: I'm using Xcode 6. Why am I getting error messages with the exercise files?
- A: A lot has changed in iOS since this course was released. The author is in the process of rewriting the code and updating the course for iOS 8. In the meantime he has prepared a version of the app that works in iOS 8 and Xcode 6. Download it here:http://ios.bw.org/
Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.