Adding database support to AirTube

Now that we’ve learned all the basic skills for working with databases, and we’ve even put them to work in a ToDo application, we can update our AirTube application with a database-dependent feature. In chapter 3, we allowed the user to download a video file locally. But at that time we didn’t know how to work with databases in AIR. There- fore we didn’t add the functionality that would allow users to also store the data for the video and search and play back offline videos within the application. That is what we’ll do in the following sections. To accomplish this, we’ll need to do the following:

■ Add online property to ApplicationData.

■ Add UI button to toggle online/offline.

■ Add service methods to handle offline mode.

We’ll start with the first step: updating ApplicationData. 5.7.1 Updating ApplicationData to support online/offline modes

Up to now, the AirTube application has only had one mode: online. We’d like to allow the user to select between online or offline mode. In order to support this, we need to add a property to the ApplicationData class. This property, which we’ll call online, is a Boolean value indicating whether the application should run in online or offline mode. Listing 5.8 shows what ApplicationData looks like with this added property.

package com.manning.airtube.data { import flash.events.Event;

import flash.events.EventDispatcher;

public class ApplicationData extends EventDispatcher { static private var _instance:ApplicationData;

private var _videos:Array;

private var _currentVideo:AirTubeVideo;

private var _downloadProgress:Number;

Listing 5.8 Adding an online property to ApplicationData

private var _online:Boolean;

[Bindable(event="videosChanged")]

public function set videos(value:Array):void { _videos = value;

dispatchEvent(new Event("videosChanged"));

}

public function get videos():Array { return _videos;

}

[Bindable(event="currentVideoChanged")]

public function set currentVideo(value:AirTubeVideo):void { _currentVideo = value;

dispatchEvent(new Event("currentVideoChanged"));

}

public function get currentVideo():AirTubeVideo { return _currentVideo;

}

[Bindable(event="downloadProgressChanged")]

public function set downloadProgress(value:Number):void { _downloadProgress = value;

dispatchEvent(new Event("downloadProgressChanged"));

}

public function get downloadProgress():Number { return _downloadProgress;

}

[Bindable(event="onlineChanged")]

public function set online(value:Boolean):void { _online = value;

dispatchEvent(new Event("onlineChanged"));

}

public function get online():Boolean { return _online;

}

public function ApplicationData() { }

static public function getInstance():ApplicationData { if(_instance == null) {

_instance = new ApplicationData();

}

return _instance;

} } }

The online property is straightforward. We merely create a private Boolean property and then create a standard accessor and mutator for it along with typical Flex data- binding metadata. Now that we’ve added the property, we next need to create a way for the user to toggle between modes, which we’ll do in the next section.

225 Adding database support to AirTube

5.7.2 Adding a button to toggle online/offline modes

We can now edit AirTube.mxml, adding to it a button that allows the user to toggle the mode between online and offline. Listing 5.9 shows this code.

<?xml version="1.0" encoding="utf-8"?>

<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml"

layout="absolute" width="800" height="600"

creationComplete="creationCompleteHandler();"

closing="closingHandler();">

<mx:Script>

<![CDATA[

import com.manning.airtube.data.AirTubeVideo;

import com.manning.airtube.windows.HTMLWindow;

import com.manning.airtube.windows.VideoWindow;

import com.manning.airtube.services.AirTubeService;

import com.manning.airtube.data.ApplicationData;

static private var _instance:AirTube;

private var _service:AirTubeService;

private var _videoWindow:VideoWindow;

private var _htmlWindow:HTMLWindow;

static public function getInstance():AirTube { return _instance;

}

private function creationCompleteHandler():void { _service = AirTubeService.getInstance();

_service.key = "YourAPIKey";

_instance = this;

_videoWindow = new VideoWindow();

_htmlWindow = new HTMLWindow();

}

private function getVideosByTags():void { _service.getVideosByTags(tags.text);

}

private function playVideo():void { var video:AirTubeVideo =

➥videoList.selectedItem as AirTubeVideo;

_service.configureVideoForPlayback(video);

if(_videoWindow.nativeWindow == null) { _videoWindow.open();

} else {

_videoWindow.activate();

} }

public function launchHTMLWindow(url:String):void { if(_htmlWindow.nativeWindow == null) {

_htmlWindow.open();

}

Listing 5.9 Updating AirTube.mxml with a button to toggle modes

else {

_htmlWindow.activate();

} }

private function closingHandler():void { for(var i:Number = 0; i <

➥nativeApplication.openedWindows.length; i++) { nativeApplication.openedWindows[i].close();

} }

private function changeOnlineStatus():void { ApplicationData.getInstance().online =

➥!ApplicationData.getInstance().online;

}

]]>

</mx:Script>

<mx:VBox width="100%">

<mx:Label text="AirTube: Adobe AIR and YouTube" />

<mx:HBox>

<mx:Label text="tags:" />

<mx:TextInput id="tags" text="Adobe AIR" />

<mx:Button label="Search For Videos"

click="getVideosByTags();" />

<mx:Button label="Online" toggle="true"

selected="{ApplicationData.getInstance().online}"

click="changeOnlineStatus();" />

</mx:HBox>

<mx:TileList id="videoList"

dataProvider="{ApplicationData.getInstance().videos}"

width="100%" height="400"

columnCount="2" horizontalScrollPolicy="off" />

<mx:Button label="Play Selected Video" click="playVideo();"

enabled="{videoList.selectedItem != null}" />

</mx:VBox>

</mx:WindowedApplication>

The preceding code adds just one button component and one method. The button is a toggle button in which the selected state is bound to the online property of Appli- cationData. When the user clicks the button, the event handler method merely tog- gles the value of the ApplicationData instance’s online property.

That’s all that we need to do as far as the user interface is concerned. Next we’ll update the service code to support both online and offline modes.

5.7.3 Supporting offline saving and searching

The majority of the new code we need to write to support online and offline modes is in the AirTubeService class. Listing 5.10 shows the code. Although there’s a fair amount of new code, don’t be concerned. We’ll explain it all in just a minute. All we’re adding is basic database code for creating a connection, creating a table, and adding and retrieving data.

227 Adding database support to AirTube

package com.manning.airtube.services {

import com.adobe.webapis.youtube.YouTubeService;

import com.adobe.webapis.youtube.events.YouTubeServiceEvent;

import com.manning.airtube.data.AirTubeVideo;

import com.manning.airtube.data.ApplicationData;

import com.manning.airtube.utilities.YouTubeFlvUrlRetriever;

import com.adobe.webapis.youtube.Video;

import flash.events.Event;

import flash.events.ProgressEvent;

import flash.filesystem.File;

import flash.filesystem.FileMode;

import flash.filesystem.FileStream;

import flash.net.URLRequest;

import flash.net.URLStream;

import flash.utils.ByteArray;

import flash.events.SQLEvent;

import flash.data.SQLConnection;

import flash.data.SQLMode;

import flash.data.SQLResult;

import flash.data.SQLStatement;

public class AirTubeService {

static private var _instance:AirTubeService;

private var _proxied:YouTubeService;

private var _flvFile:File;

private var _imageFile:File;

private var _downloadingVideo:AirTubeVideo;

private var _connection:SQLConnection;

public function set key(value:String):void { _proxied.apiKey = value;

}

public function AirTubeService() { _proxied = new YouTubeService();

_proxied.addEventListener(

➥YouTubeServiceEvent.VIDEOS_LIST_BY_TAG, getVideosByTagsResultHandler);

var databaseFile:File =

➥File.applicationStorageDirectory.resolvePath("AirTube.db");

_connection = new SQLConnection();

_connection.addEventListener(SQLEvent.OPEN, databaseOpenHandler);

_connection.openAsync(databaseFile, SQLMode.CREATE);

}

static public function getInstance():AirTubeService { if(_instance == null) {

_instance = new AirTubeService();

}

return _instance;

}

Listing 5.10 Updating AirTubeService to include offline support

Create database connection

B

private function databaseOpenHandler(event:Event):void { var sql:SQLStatement = new SQLStatement();

sql.sqlConnection = _connection;

sql.text = "CREATE TABLE IF NOT EXISTS videos(" + "id TEXT PRIMARY KEY, title TEXT, " +

"url TEXT, tags TEXT)";

sql.execute();

}

public function getVideosByTags(tags:String):void { if(_proxied.apiKey.length == 0) {

throw Error("YouTube API key not set");

}

if(ApplicationData.getInstance().online) { _proxied.videos.listByTag(tags);

} else {

var sql:SQLStatement = new SQLStatement();

sql.addEventListener(SQLEvent.RESULT,

getOfflineVideosResultHandler);

sql.sqlConnection = _connection;

var text:String = "SELECT * FROM videos WHERE 1 = 0";

var tagsItems:Array = tags.split(" ");

for(var i:Number = 0; i < tagsItems.length; i++) { text += " OR tags LIKE ?";

sql.parameters[i] = "%" + tagsItems[i] + "%";

}

sql.text = text;

sql.itemClass = Video;

sql.execute();

} }

private function getVideosByTagsResultHandler(

➥event:YouTubeServiceEvent):void {

var videos:Array = event.data.videoList as Array;

for(var i:Number = 0; i < videos.length; i++) { videos[i] = new AirTubeVideo(videos[i]);

}

ApplicationData.getInstance().videos = videos;

}

private function getOfflineVideosResultHandler(event:SQLEvent):

➥void {

var statement:SQLStatement = event.target as SQLStatement;

var result:SQLResult = statement.getResult();

var videos:Array = new Array();

var video:AirTubeVideo;

if(result != null && result.data != null) {

for(var i:Number = 0; i < result.data.length; i++) { video = new AirTubeVideo(result.data[i]);

video.offline = true;

video.flvUrl =

➥File.applicationStorageDirectory.resolvePath("videos/" +

➥video.video.id + ".flv").nativePath;

Create table

C

Test for mode

D

Compose offline SELECT

E

Wrap result in AirTubeVideo F

Retrieve file references

229 Adding database support to AirTube

video.video.thumbnailUrl =

➥File.applicationStorageDirectory.resolvePath("thumbnails/" +

➥video.video.id + ".jpg").nativePath;

videos.push(video);

} }

ApplicationData.getInstance().videos = videos;

}

public function configureVideoForPlayback(video:AirTubeVideo):

➥void {

ApplicationData.getInstance().currentVideo = video;

if(video.flvUrl == null) {

new YouTubeFlvUrlRetriever().getUrl(video);

} }

public function saveToOffline(video:AirTubeVideo):void { _downloadingVideo = video;

_flvFile =

➥File.applicationStorageDirectory.resolvePath("videos/" +

➥video.video.id + ".flv");

var videoLoader:URLStream = new URLStream();

videoLoader.load(new URLRequest(video.flvUrl));

videoLoader.addEventListener(Event.COMPLETE, videoDownloadCompleteHandler);

videoLoader.addEventListener(ProgressEvent.PROGRESS,

➥videoDownloadProgressHandler);

_imageFile =

➥File.applicationStorageDirectory.resolvePath("thumbnails/" +

➥video.video.id + ".jpg");

var imageLoader:URLStream = new URLStream();

imageLoader.load(new URLRequest(video.video.thumbnailUrl));

imageLoader.addEventListener(ProgressEvent.PROGRESS, imageDownloadProgressHandler);

}

private function videoDownloadProgressHandler(event:ProgressEvent):

➥void {

var loader:URLStream = event.target as URLStream;

var bytes:ByteArray = new ByteArray();

loader.readBytes(bytes);

var writer:FileStream = new FileStream();

writer.open(_flvFile, FileMode.APPEND);

writer.writeBytes(bytes);

writer.close();

var ratio:Number = event.bytesLoaded / event.bytesTotal;

ApplicationData.getInstance().downloadProgress = ratio;

}

private function videoDownloadCompleteHandler(event:Event):void { _downloadingVideo.offline = true;

ApplicationData.getInstance().downloadProgress = 0;

var sql:SQLStatement = new SQLStatement();

sql.sqlConnection = _connection;

Retrieve file references

sql.text = "INSERT INTO videos(" + "title, id, url, tags) VALUES(" +

"@title, @id, @url, @tags)";

sql.parameters["@title"] = _downloadingVideo.video.title;

sql.parameters["@id"] = _downloadingVideo.video.id;

sql.parameters["@url"] = _downloadingVideo.video.url;

sql.parameters["@tags"] = _downloadingVideo.video.tags;

sql.execute();

}

private function imageDownloadProgressHandler(event:ProgressEvent):

➥void {

var loader:URLStream = event.target as URLStream;

var bytes:ByteArray = new ByteArray();

loader.readBytes(bytes);

var writer:FileStream = new FileStream();

writer.open(_imageFile, FileMode.APPEND);

writer.writeBytes(bytes);

writer.close();

} } }

Although we’ve added a lot of code, it should mostly be clear to you now that you’ve worked with AIR databases throughout the chapter. Initially we need to create a con- nection to the database B. Once we’ve connected to the database, we need to create the table for the data if it doesn’t already exist C. In this case, we’re creating just one table with id, title, url, and tags as the columns. Next, in the method that searches vid- eos, we need to test for the current mode D. If the mode is online, then we can search online videos as normal. Otherwise, we now want to search all the offline vid- eos. We compose a SELECT statement E based on the keywords that the user has spec- ified. Once the results are returned, we loop through each of the records (which we’ve typed as com.adobe.webapis.youtube.Video objects) and wrap them in Air- TubeVideo objects F. On the flip side, when the user saves a video to offline, we now need to do more than just save the video file. We also need to save the data for the video to the database G.

And that’s all there is to this stage of the AirTube application. When you test the application now, you should be able to save videos locally, and then toggle to offline mode and search for those videos (and play them back).