By: Akash Mehta

August 28th, 2008

Reader Rating: 10

In our previous tutorial, we created a personal notes storage database using HTML, CSS, and JavaScript. In this tutorial, we’re going to explore the UI features of the AIR platform by building a simple Customer Relationship Management (CRM) application. We’ll use those same technologies, as well as a local SQLite database.

Just as we did with Part 1, there’ll be another quiz to test you on what you’ve learned [1]. Again, the first 100 people to undergo the quiz will receive, free-of-charge, the Adobe AIR For JavaScript Developers pocket guide in the post. Remember, the book can be downloaded free as a PDF [2] for a short time too, so get cracking!

Building User Interfaces in AIR

Our standard web technologies—HTML, CSS, JavaScript—provide a solid foundation to build rich interfaces within the browser. However, sometimes we need to extend beyond the browser; this way we can take advantage of the standard interactions users are accustomed to with their desktop applications. This is especially important for any kind of data entry and reporting—and what better way to explore this than with a business data entry application.

In this article, we’ll explore the user interface possibilities that AIR provides. We’ll implement drag-and-drop functionality, work with native windows, communicate between windows, construct menus, and more. Once we’re finished, we’ll have built a simple yet powerful application for managing a customer records database.

Assumed Knowledge

Throughout this tutorial, I’ll assume you’ve read through the first article in this series, Learn Adobe AIR, Part 1: Build a Note Storage App [3], or at least are comfortable with creating simple applications in Adobe AIR [4]. Once again, you’ll need the AIR SDK [5], Aptana, jQuery, the AIR menu builder, and the AIR Introspector. If you haven’t set these up, do so now. I covered how to do this in my first article, in the section: A Powerful Tool for Building AIR Apps [6].

Setting up the Example Project

To play along at home, download the skeleton AIR project for this tutorial [7] and import it into Aptana.

If you’d rather create your project from scratch, make sure that you import jQuery by checking the Import Javascript Library option in the New Project wizard. Once the project has been created, you can safely remove the following files:

• AIRLocalizer.js
• AIRSourceViewer.js
• jquery_sample (note there are two files with this name)
• CRMTest.html (this filename may differ, depending on what you called your project)
• LocalFile.txt
• sample.css

We’re using jQuery 1.2.3, but any newer 1.2.x release should work fine. Add the contents of the zip file to the project directory, refresh the folder (right click the project name in the Project view and select Refresh), and you’re good to go.

Running on AIR

Now that you’ve got the example project setup, let’s get started. As I mentioned, we’re going to build a simple CRM application. Here’s how the app looks in Windows Vista:

Here’s the plan of attack:

• construct our menus
• establish a database connection to an SQLite database
• read our initial customer records
• create a form to view each record
• create a form to create new records

Along the way, we’ll learn how to manually construct menu trees, read directly from the file system, use prepared statements on SQL databases, and even implement native drag-and-drop functionality (which will work on any AIR-supported platform).

Creating Menus

In our previous AIR article, we used the menu builder framework to automatically create menus from an XML document. However, there are some limitations to this approach. This time around, we‘ll make use of the native menu API to construct our menus.

The main factor to consider when building menus at this level is the location of the menu that you’ll create. Options include the window menu, which is available at the top of the window in a Windows application, and the application menu, which sits at the top of the screen in most OS X applications. We can also use the same native menu system to create context menus, menus for dock icons (OS X), and menus for system tray icons (Windows). Luckily, regardless of the type of menu we want to build on, the syntax is the same, thanks to JavaScript’s object oriented nature. In Aptana, open the menus.js file and add the following code:

$(document).ready(function(){
 if (air.NativeWindow.supportsMenu) {
   nativeWindow.menu = new air.NativeMenu();
   targetMenu = nativeWindow.menu;
 }
 
 if (air.NativeApplication.supportsMenu) {
   targetMenu = air.NativeApplication.nativeApplication.menu;
 }
});


The code above creates an object named targetMenu. This refers either to a menu on the nativeWindow (which, in a Win32 environment, is the current window), or the global application window (OS X). An AIR menu is a collection of objects for all menu items, governed by a main air.NativeMenu class. We can check the supportsMenu property of an AIR window or application object to determine what is supported by the current platform. Note that on OS X, the application menu already exists, regardless of whether a window exists or not. The Adobe AIR manual has further information on AIR menus [8].

Also remember that we’re using jQuery, which executes the following line of code whenever our page’s DOM has completed loading:

$(document).ready()

We’ve already created this function; let’s add to it now. Modify your menus.js file so that it looks as follows (the new lines are shown in bold below):

$(document).ready(function(){
 if (air.NativeWindow.supportsMenu) {
   nativeWindow.menu = new air.NativeMenu();
   targetMenu = nativeWindow.menu;
}
 
 if (air.NativeApplication.supportsMenu) {
   targetMenu = air.NativeApplication.nativeApplication.menu;
}
var fileMenu;
fileMenu = targetMenu.addItem(new air.NativeMenuItem("File"));
fileMenu.submenu = new air.NativeMenu();
newCustomer = fileMenu.submenu.addItem(new air.NativeMenuItem("Create   Record"));
newCustomer.addEventListener(air.Event.SELECT, Menu_NewCustomer);
newCustomer = fileMenu.submenu.addItem(new air.NativeMenuItem("Exit"));
newCustomer.addEventListener(air.Event.SELECT, Menu_Exit);
});


These few lines of code create a simple file menu, which contains two menu items: Create Record and Exit. We create new objects for each item manually, although this could be automated to suit the application.

There are a number of advantages to this approach, including the flexibility to add custom keyboard shortcuts, and perform custom actions when a menu item is selected. More importantly, however, we now have a basic infrastructure in place for our menu, to which we can easily add, remove, or modify items on the fly.

The last line specifies the callback function that will be executed when the Exit menu item is selected. (Note there’s also a DISPLAY event that is fired when the user opens the menu but hasn’t selected it yet). Those callback functions don’t exist yet, so let’s go ahead and write them now:

function Menu_NewCustomer() {
 document.location = "new_customer.html";
}

function Menu_Exit() {
 var event = new air.Event(air.Event.EXITING, false, true);
 air.NativeApplication.nativeApplication.dispatchEvent(event);
 if (!event.isDefaultPrevented()) {
   air.NativeApplication.nativeApplication.exit();
 }
}


The Menu_Exit method simply checks whether another part of the application (or the AIR runtime itself) is attempting to delay termination of the current application. In this way, it’s similar to the exit function in the previous article I wrote about Adobe AIR [9]. It’s possible to override any delay that’s detected by this method, but it’s not recommended; that’s because it’s best practice to respect any code that is executing alongside yours in such a high-level framework.

Connecting to the SQL Database

In the skeleton project archive, I’ve included a sample SQLite database, named crm.sqlite. This database consists of just one table, which contains the following sample data (to view this database, I recommend SQLite Manager [10], a Firefox extension):

The example application that we’ll build in this tutorial will provide the user with an interface for accessing this information. We’ll build functionality for viewing individual records, importing data into the Notes column, and more. While the completed application will be quite simple, it would certainly be feasible to use it as a base for building a fully-fledged CRM application.

As we saw in the first tutorial, connecting from a single window to a local database is relatively easy. In this application, however, we’re using multiple windows, and each will operate independently. This means we’ll need to share this database connection among the various scripts throughout our application.

With this in mind, let’s establish a standard database connection—add the following code to your database.js file:

function SetupDB() {
var db = new air.SQLConnection();
var dbFile = air.File.applicationStorageDirectory.resolvePath("crm.sqlite");
 
//if (!dbFile.exists) {
 var dbTemplate = air.File.applicationDirectory.resolvePath("crm.sqlite");
 dbTemplate.copyTo(dbFile, true);
//}
 
try
{
 db.open(dbFile, air.SQLMode.UPDATE);
}
catch (error)
{
 air.trace("DB error:", error.message);
 air.trace("Details:", error.details);
}
 
return db;
}


The code above defines a simple function, SetupDB(), which creates a connection to the local SQL database. As you’ll recall, the application directory is read-only (on a Windows machine this is the Program Files folder). You should therefore copy the skeleton database to the application storage directory (on Windows this is the AppData folder).

During the development phase, we’ll be making potentially destructive changes to our database, so it’s convenient to be able to recreate it each time we relaunch our application. When we deploy our app to production, we can simply uncomment the dbFile.exists condition, to prevent our database from being replaced.

Retrieving Our First Records

Now that we have an interface and a data source, it’s time to start reading records from our CRM database. The fun begins with the customers.html file, which is included in the skeleton code archive and contains some sample static output. We’ll use jQuery to remove this static data and replace it with real records from the database. Open your behaviour.js file and insert the following code:

$(document).ready(function(){  
 db = SetupDB();  
 read_customers();  
 $("#refresh").click(function(){ read_customers(); });  
});  
 
function read_customers() {  
 dbQuery = new air.SQLStatement();  
 dbQuery.sqlConnection = db;  
 dbQuery.text = "SELECT * FROM customers";  
   
 try { dbQuery.execute(); } catch (error) {  
   air.trace("Could not retrieve customers from DB.", error.message);  
 }  
   
 results = dbQuery.getResult();  
 $("#customerlist tbody").empty();  
 for (i = 0; i < results.data.length; i++) {  
   row = results.data[i];  
   $("#customerlist tbody").append('<tr><td>'+row.id+'</td><td>'  
     +row.name+'</td></tr>');  
 }  
 $("#customerlist tbody tr").each(function(){  
   $(this).click(function(){  
     view($(this).children("td:first").text())  
   })  
 });

The first part of this code is fairly self-explanatory:

1. Call the database connection function we defined earlier.


2. Read our list of customers.


3. For each record, instruct the click handler to invoke the view function.

Binding information to elements in the DOM like this is always a bit tricky, so we’ve used the td:first selector to grab the value of the first cell—the ID—and passed that as a parameter to the view function. This function will launch a new window and provide the user with greater detail about the selected record, including a phone number and some associated notes. Let’s define that function now; add the following code to the bottom of the behaviour.js file:

function view(id) {  
 current_id = id;  
 customerWindow = air.HTMLLoader.createRootWindow(true, null, true, new air.Rectangle(0,0,640,480));  
 customerWindow.addEventListener(air.Event.COMPLETE, passDbId);  
 customerWindow.load(new air.URLRequest("app:/view_customer.html"));  
}  
 
function create() {  
createWindow = air.HTMLLoader.createRootWindow(true, null, true, new air.Rectangle(0,0,640,480));  
createWindow.addEventListener(air.Event.COMPLETE, passDb);  
createWindow.load(new air.URLRequest("app:/new_customer.html"));  
}  
 
function passDbId(event) {  
event.target.window.receiveDb(db);  
event.target.window.display_customer(current_id);  
}  
 
function passDb(event) { event.target.window.receiveDb(db); }

In the code above, the view function (and the related create function), work with the passDb and passDbId functions; these then launch new windows that render an appropriate HTML page. Remember that we need to share our database connection across multiple pages, so we’ll define a receiveDb function within the target pages. However, as our custom JavaScript function for connecting to the database is called from within the page, we need to wait for the parent page to load first!

The HTML Loader APIs in AIR permit these kinds of window logistics quite easily. Let’s break it down:

As you can see in the code above, the createRootWindow constructor accepts a few parameters, including an air.Rectangle() object that represents the dimensions of the window to be created. This function provides us with a window object, to which we add a new event listener for the COMPLETE event. This event listener will be triggered whenever a new page has finished loading. You’ll note in the view method that we store the current ID temporarily before loading the page using a URL request. Once the page has loaded, the callback function passes along the database connection and instructs the new page to display the customer details.

You may recall from the previous article that we used a hash string in the URL to pass information around between pages (for example, view_customer.html#id/1). This is a reliable hack, given AIR is a controlled environment that does not make use of an address bar—at which point our magic would be revealed! That said, taking advantage of the HTML loader system—as we’ve done here—is a much more stable and useful approach, as it allows us to pass entire objects around as needed.

Viewing Records and Receiving Information

Right! We’ve set up a system to read records from the database, but now we need to view individual customer records. We already know we’ll need two functions—receiveDB and display_customer—both of which receive information from our main customers.html window. Our view_customer.html file is also populated with sample data, but once again we’ll use jQuery to replace this with real information from the database.

Edit your view_behaviour.js file so that it looks like this:

dbConn = null;  
function receiveDb(dbFromParent) {  
 dbConn = dbFromParent;  
}  
 
function display_customer(id) {  
 dbQuery = new air.SQLStatement();  
 dbQuery.sqlConnection = dbConn;  
 dbQuery.text = "SELECT * FROM customers WHERE id=:id";  
 dbQuery.parameters[":id"] = id;  
   
 try {  
   dbQuery.execute();  
   dbQuery.clearParameters();  
 }  
 catch (error) {  
   air.trace("Error retrieving notes from DB:", error);  
   air.trace(error.message);  
   return;  
 }  
   
 var customerData = dbQuery.getResult();  
 $("#cust_name").val(customerData.data[0].name);  
 $("#cust_phone").val(customerData.data[0].phone);  
 $("#cust_notes").text(customerData.data[0].notes);  
}

Here we’ve defined a receiveDb function, which simply accepts the handle to the database connection as a parameter and stores it in a local variable. Our display_customer function is where the real work happens, retrieving the record in question and outputting it to the page.

Note that we’re using a textarea for the notes information, as opposed to creating input fields for the individual name and phone fields. While I won’t cover how to add “edit” functionality in this tutorial, the logic is much the same as for the record retrieval we just wrote; the only difference, is you would obviously use an UPDATE statement instead of a SELECT statement.

Note: Limitations with AIR and SQL
If you ever find you have to do some serious information manipulating in local SQL databases, keep in mind that there are many constraints to the AIR implementation—the Adobe AIR Language Reference has more information [11].

The database logic is quite similar to our logic that retrieved all customer records, only this time we’re making use of parameterized statements—as demonstrated by the following two lines:

dbQuery.text = "SELECT * FROM customers WHERE id=:id";  
dbQuery.parameters[":id"] = id;  


This is fairly straightforward; first we define a named parameter, :id, then we supply the value of that parameter separately. While we could achieve the same thing using literal values in SQL queries, prepared statements like this are much more reliable, so I prefer them. While it may be slightly overkill for this tutorial, using a prepared statement is safer (it provides SQL injection-proofing out of the box) and faster (queries can be reused without having to recompile them each time). For further details, consult the AIR manual, specifically the section on using parameters in statements [12].

Creating New Records

Now that we have a stable interface with which to view our data, we need to be able to create records. As a business, it’s important to be able to attach existing data to a customer record. AIR’s native drag-and-drop functionality is great for this—using pure JavaScript, our application can accept multiple files quite easily. Once we have the files, we can copy or move them, read them, upload them to a server, or even display them in our application. In this example, we’re just reading a text file, but we could easily extend this as needed for more complex file types.

In the previous article, we built a simple creation form as part of our Note Storage application, and the logic is much the same for this app. We’ll read data from text fields, construct an SQL INSERT statement, and execute it against our database. To implement the drag-and-drop functionality, though, we need to specify a target area, or drop zone, for our files. A drop zone is an area on the window that responds to certain mouse events (such as a group of files being dropped onto it). We’ll build this drop zone so that it stores the data received in a variable for later use.

The AIR APIs allow us to explicitly check for files being dropped onto the target area (as opposed to plain text, URLs, HTML, or other Clipboard data). These files then become available for us to manipulate as air.File objects. Similar to the way in which we manipulated our database file, we can manipulate these “dropped” files using a set of generic interfaces.

Open the new_behaviour.js file in your editor, and add the following:

notes = "";  
dbConn = null;  
 
function receiveDb(dbFromParent) {  
 dbConn = dbFromParent;  
}  
 
$(document).ready(function(){  
 $("#create").click(function(){ create(); });  
 $("#clear").click(function(){ clear(); });  
   
 var dropbox = document.getElementById("filedrop");  
 dropbox.addEventListener("dragenter", dragEnterOver);  
 dropbox.addEventListener("dragover", dragEnterOver);  
 dropbox.addEventListener("drop", dropHandler);  
});

The important part of this code is the various interface-related functions that we’ve bound to specific events. We’ll create those functions in a moment; for now let’s build our drop zone.

Open up the new_customer.html file, and take a quick look at the structure of the document. You’ll see that we have a div with an id of ”filedrop” that contains an empty span element. As a user drags files onto the application page, AIR asks each of the DOM elements that form part of the page if they will accept a file drop; this gives our div the opportunity to tell AIR that it will!

There are actually three events here, but the first two (dragenter and dragover) can be handled by the same event handler. Here’s the code for that function:

function dragEnterOver(event) {  
 for (var t = 0; event.dataTransfer.types && t < event.dataTransfer.types.length; t++)  
 {  
   if (event.dataTransfer.types[t] == "application/x-vnd.adobe.air.file-list")  
   {  
     event.preventDefault();  
   }  
 }  
}

The code above will simply confirm that the drop zone is receiving a list of files and using the appropriate MIME type (there are a few other options documented on the Adobe help pages [13]). If the drop zone is indeed going to accept any files dragged onto it, we need to first disable the default event that rejects the file drop. This will allow the operating system to display a nice “drop allowed” visual indicator: on Windows Vista, this indicator is a blue plus symbol (+), while a red X indicates that the drop is not allowed.

Next we need to handle the actual drop:

function dropHandler(event) {  
 var files = event.dataTransfer.getData("application/x-vnd.adobe.air.file-list");  
 for (var num = 0; num < files.length; num++) {  
   fileStream = new air.FileStream();  
   fileStream.addEventListener(air.Event.COMPLETE, readFile);  
   fileStream.openAsync(files[num], air.FileMode.READ);  
 }  
}  
 
function readFile(event) {  
 notes = fileStream.readMultiByte(fileStream.bytesAvailable, "iso-8859-1");  
 $("#filedrop span").text(notes.substr(0,100) + "...");  
}

Just as we did for the HTMLLoader COMPLETE event, we’ve used a handler for the FileStream event that indicates that the opening of a file is complete. As you can see from the code above, we’re listening for this event asynchronously. We’re taking this approach because reading a file can take a while, and we don’t want to lock up the application in the meantime.

As you can see, we use the callback for the event listener to read data directly from the file into a string object. The manual has further information on file read/write workflow [14] and FileStream’s open modes [15].

We need to specify a character encoding type here—for plain text, the option we’ve used (Western European – ISO) is usually appropriate; check out the AIR manual’s list of supported character sets [16].

It’s always good to give the user some indication that their action has been successful. In this case, we’re displaying a sample of the file that they dropped (the first 100 characters) to let them know that their files were dropped successfully.

Finally, we need our actual record creation routine, and the clear method to which we referred earlier. Add the following code to the end of your new_behaviour.js file:

function create() {  
dbQuery = new air.SQLStatement();  
dbQuery.sqlConnection = dbConn;  
dbQuery.text = "INSERT INTO customers (name,phone,notes) VALUES (:name,:phone,:notes)";  
dbQuery.parameters[":name"] = $("#name").val();  
dbQuery.parameters[":phone"] = $("#phone").val();  
dbQuery.parameters[":notes"] = notes;  
   
try { dbQuery.execute(); } catch (error) {  
 air.trace("Could not create new customer record.");  
}  
}  
 
function clear() {  
notes = ""; $("#filedrop span").text("");  
}  


The majority of this code should be fairly familiar to you by now—an SQL INSERT statement, with named parameters. Note that we used a JavaScript escaping routine in our previous AIR article; such steps are not necessary here, as this functionality is taken care of by using named parameters.

To test the app, try dragging a sample data file onto the drop zone: customerdata.txt, which exists in the project folder, has been included in the skeleton code archive for this purpose.

And We’re Done!

Save all open files in Aptana and select your project name from the Run button menu, and your CRM app should appear. Try adding an entry (via the File menu) and refreshing the main screen to view your newly-created record.

You can download my completed project files (including Aptana project data) here. [17]

Further Reading

If you’re interested in further exploring the topics covered in this article, Adobe has a number of fantastic online resources that you may find handy:

• Drag and drop [18]
• Working with native windows [19]
• Reading and writing files [20]
• AIR 1.1 HTML/AJAX guide for developers [21]
• AIR 1.1 HTML/AJAX Language Reference [22]

If you aren’t quite sure about any of the code samples in this article, feel free to post a comment below and I’ll see how I can help.

Test Yourself

You can test your comprehension of this tutorial with a short quiz [23], and stand to receive a FREE copy of the pocket guide, Adobe AIR For JavaScript Developers, for your efforts. The guide will be delivered FREE, courtesy of Adobe Systems, but this offer is only available to the first 100 people, so get in quick! (If you do miss out, you can still download the book in PDF format for free [24] for a limited time only.)

Take the quiz [25]!

[1] http://www.sitepoint.com/quiz/air/part2/
[2] http://www.sitepoint.com/quiz/air/part2/
[3] http://www.sitepoint.com/article/learn-adobe-air-part-1
[4] http://ad.doubleclick.net/clk;205852848;28495102;m
[5] http://ad.doubleclick.net/clk;205852848;28495102;m
[6] http://www.sitepoint.com/article/learn-adobe-air-part-1
[7] http://www.sitepoint.com/examples/AdobeAIR/air2-crm-skeleton.zip
[8] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7f53.html
[9] http://www.sitepoint.com/article/learn-adobe-air-part-1
[10] https://addons.mozilla.org/en-US/firefox/addon/5817
[11] http://help.adobe.com/en_US/AIR/1.1/jslr/index.html?localDatabaseSQLSupport.html
[12] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7d42.html
[13] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS7709855E-7162-45d1-8224-3D4DADC1B2D7.html
[14] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7dc8.html
[15] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7db2.html
[16] http://livedocs.adobe.com/flex/2/langref/charset-codes.html
[17] http://www.sitepoint.com/examples/AdobeAIR/air2-crm-final.zip
[18] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7e8a.html
[19] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7e17.html
[20] http://help.adobe.com/en_US/AIR/1.1/devappshtml/WS5b3ccc516d4fbf351e63e3d118666ade46-7dc2.html
[21] http://help.adobe.com/en_US/AIR/1.1/devappshtml/
[22] http://help.adobe.com/en_US/AIR/1.1/jslr/index.html
[23] http://www.sitepoint.com/quiz/air/part2/
[24] http://www.sitepoint.com/quiz/air/part2/
[25] http://www.sitepoint.com/quiz/air/part2/