In last week’s workshop, you learned about three JOIN queries supported by the SQLite engine and they are: INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. In this workshop, you will learn how to perform an INNER JOIN in an Xcode project I’ve created for this workshop. So go ahead and download it.
Overview of The SqliteJoinQuery Application
Upon completion, the SqliteJoinQuery application will automatically rotate photos in the view’s Image View control for the category name you select from the Picker View control. I used Xcode’s Single View Application to create the application.
The Database File
When you run the application for the first time, it will create a an empty database in the application’s sandbox’s Documents fold. You’ll have to download and unzip this file,.
Now, run the SqliteJoinQuery application in the simulator. Use Finder to replace the sandbox’s database file with the one you downloaded. The database file contain tables shown in Figure 1 below. In this workshop, we’ll fetch records only from the photos and and albums table.
 |
| Figure 1: Tables in the vintageDatabase.sqlite |
The AppDelegate Class Files
I’ve set up these methods in the AppDelegate class files. They are for interacting with the application’s database file.
| – (NSString *)docPath- (void)openDatabase- (void)closeDatabase- (NSMutableArray *)fetchAllVideos | – (NSMutableArray *)fetchPhotosByAlbum:(int)albumId- (NSMutableArray *)fetchAlbums- (NSMutableArray *)joinQuery- (NSArray *)fetchVideoByTitle:(NSString *)title |
I’ve database enable the AppDelegate class and added the SQLite engine in the project’s Frameworks folder.
The Storyboard Scene
The application’s Main.storyboard file contain only one scene and Figure 2 below, it shows what it look like in the iPhone Simulator window.
 |
| Figure 2: The View Controller scene |
Soon you’ll add code in the ViewController.m file to:
- Load the Picker View control with the album names stored in the database’s albums table and the number of photos it contain.
- Automatically rotate images in the Image View control, for the album name selected in the Picker View control.
The ViewController Class Files
Take a look at the ViewController’s header and implementation file. As you can see, I’ve already added house keeping code in them; however, you’ll have to add additional code in the ViewController.m file to make the class’ scene functional.
The Picker View Control Code
Now that you’ve been introduced to the workshop’s application files, you are ready to add in the ViewController.m file to load the Picker View control with album names.
For each album that’s in the database, the system (the SqliteJoinQuery app) will do the follow:
- Fetch the album names from the database
- Count the total number of photos that’s in the album and store it in a variable called totalPhotos
- Display the album names and the totalPhotos variables value, in the Picker View control.
The third system task will produce 9 rows in the Picker View control. That’s because there are 9 albums in the database’s albums table-look at Figure 1 again to refresh your memory.
Since we’ll be using methods of the AppDelegate class, you’ll have to add this statement in the ViewDidLoad method. It initialize the appDelegate property.
- (void)viewDidLoad
{
[super viewDidLoad];
self.appDelegate = [[UIApplication sharedApplication] delegate];
}
Add these statements in the viewDidLoad method as well.
self.appDelegate openDatabase]; // Fetch rows from the database's album table self.albumList = [self.appDelegate fetchAlbums]; [self.appDelegate closeDatabase];
Above code open the database file, fetch all album names from the database and add them in a mutable array called albumList. The final statement close the database file. If you want to see content of the albumList array, you’ll have to add these statements in the viewDidLoad method, then run the application. The NSLog() function will display 9 album names in Xcode’s Debugger window.
Birthday Christmas Easter Fathers Day Halloween Mothers Day Nature Rememberance Day Valentines Day
Now, here is the code to implement above mentioned system tasks. Add it below the numberOfRowsInComponent: method.
#pragma mark - picker view delegate method
- (NSString *)pickerView:(UIPickerView *)pickerView titleForRow:(NSInteger)row forComponent:(NSInteger)component
{
NSArray *albums;
NSString *output;
[self.appDelegate openDatabase];
switch(row)
{
case 0: // Birthday
albums = [self.appDelegate fetchPhotosByAlbum:9];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%d)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 1: // Christmas
albums = [self.appDelegate fetchPhotosByAlbum:5];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%d)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 2: // Easter
albums = [self.appDelegate fetchPhotosByAlbum:7];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%d)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 3: // Fathers Day
albums = [self.appDelegate fetchPhotosByAlbum:1];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%d)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 4: // Halloween
albums = [self.appDelegate fetchPhotosByAlbum:4];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%i)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 5: // Mothers Day
albums = [self.appDelegate fetchPhotosByAlbum:8];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%i)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 6: // Nature
albums = [self.appDelegate fetchPhotosByAlbum:2];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%i)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 7: // Remembrance Day
albums = [self.appDelegate fetchPhotosByAlbum:6];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%i)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
case 8: // Valentines Day
albums = [self.appDelegate fetchPhotosByAlbum:3];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%i)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
}
[self.appDelegate closeDatabase];
return output;
}
Run the application and the Picker View control will now look like Figure 3. The number in brackets is telling you how many photos an album contain.
 |
| Figure 3: The Picker View control |
The switch() Case Code
The switch() case code is pretty simple, once you understand how it work. Each case represents an album’s id number and it is compared against the switch()’s row number. If they are the same, then code within the case: block is executed. For example, the first case block contain these statements:
case 0: // Birthday
albums = [self.appDelegate fetchPhotosByAlbum:9];
self.totalPhotos = [albums count];
output = [NSString stringWithFormat:@"%@ (%d)", [self.albumList objectAtIndex:row],self.totalPhotos];
break;
The first statement sends a message to the fetchPhotosByAlbum: method, which fetch records from the photos table for the Birthday album. Its id number is 9 and it is passed as a parameter in the fetchPhotosByAlbum: method. The method’s results are stored in the albums array. The second statement count elements of the albums array and assign the integer value in the totalPhotos variable. The third statement assemble a string which consist of the album name a the value of the the totalPhotos variable. The string is assigned to the output variable. Here’s what you’ll see in the Picker View control for the switch() case 0:
Birthday (2)
The last statement which consist of the keyword break followed by a semicolon, prevent other cases of the switch case from executing. Remaining cases in the switch() control structure works as described above to populate the Picker View control with rows.
You added code in the ViewController.m file to perform the first task listed below.
- Load the Picker View control with the album names stored in the database’s albums table and the number of photos it contain.
- Automatically rotate images in the Image View control, for the album name selected in the Picker View control.
To perform the second tasks listed above, you’ll have to declare and implement this method in the ViewController class files. Its job is to animate an album’s photos in the Image View control, when you select an album name from the Picker View control.
- (void)animateAlbumPhotos:(int)albumId
{
self.photoList = [[NSMutableArray alloc] init];
[self.appDelegate openDatabase];
self.photoList = [self.appDelegate.self fetchPhotosByAlbum:albumId];
[self.appDelegate closeDatabase];
if ([self.photoList count] > 0) {
// Set the image animation cycle, in seconds
self.imageView.animationDuration = 6;
// Create an object for storing images to be animated
NSMutableArray *images=[[NSMutableArray alloc] init];
// Holds binary data of an image
UIImage *imageFromServer;
for(NSString *imageFile in self.photoList) {
// Get the image from the server
imageFromServer = [UIImage imageWithData:[NSData dataWithContentsOfURL:
[NSURL URLWithString:imageFile]]];
[images addObject:imageFromServer];
}
// Load and animate images
self.imageView.animationImages=images;
[self.imageView startAnimating];
} else {
// The photoList array is empty
if(self.imageView.isAnimating) {
[self.imageView stopAnimating];
}
// Create and display an Alert View
UIAlertView *message = [[UIAlertView alloc] initWithTitle:@""
message:@"Sorry, this album has no photos."
delegate:self cancelButtonTitle:nil otherButtonTitles:@"OK", nil];
[message show];
}
}
Here is the Picker View method to that’s fired when you scroll the Picker View control to select a album.
-(void)pickerView:(UIPickerView *)pickerView didSelectRow:(NSInteger)row inComponent:(NSInteger)component
{
switch(row)
{
case 0:
// Birthday album selected, execute code placed in the animateAlbumPhotos: method
[self animateAlbumPhotos:9];
break;
case 1:
// Christmas album album selected
[self animateAlbumPhotos:5];
break;
case 2:
// Easter album selected
[self animateAlbumPhotos:7];
break;
case 3:
// Fathers Day album selected
[self animateAlbumPhotos:1];
break;
case 4:
// Halloween album selected
[self animateAlbumPhotos:4];
break;
case 5:
// Mothers Day album selected
[self animateAlbumPhotos:8];
break;
case 6:
// Nature album selected
[self animateAlbumPhotos:2];
break;
case 7:
// Remembrance Day album selected
[self animateAlbumPhotos:6];
break;
case 8:
// Valentines Day album selected
[self animateAlbumPhotos:3];
break;
}
}
Once you’ve entered above code in the ViewController.m file, run the application. Scroll the view to select an album. The Picker View control’s didSelectRow: method is fired. If the album you selected in the Picker View control contain photos, they are automatically rotated in the Image View control for your viewing pleasure; otherwise, you see an Alert View message.
By the way, an album’s images aren’t stored in the database file, only their url. Take a look at the photos table in this image to see what I mean.
This concludes the Using SQLite tutorial series. By doing this tutorial you can develop al kinds of iOS apps that uses an SQLite database file as the app’s backend data store. Next week I will post an “SQLite Cheat Sheet”. It will list all the SQLite functions and custom methods you’ve used in this tutorial’s series. You will even be able to download the SQLite Cheat Sheet.