Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98552 Viewers
61 Video lessons · 85838 Viewers
71 Video lessons · 69736 Viewers
56 Video lessons · 102043 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.
Your file was successfully uploaded.