Login | Register
Displaying Large Datasets in an Interactive Table

Displaying Large Datasets in an Interactive Table

Working with large datasets can be quite cumbersome and somewhat painful if not done properly. You risk incurring the wrath of impatient users and unresponsive servers. On top of that, creating an interactive table in HTML/JavaScript code that works perfectly in every browser is invariably a challenging task.

Adobe Flex 4.5 offers a painless and elegant solution for displaying large datasets in a highly interactive, rich application. Flex 4.5 eliminates cross-browser frustrations, and the DataGroup container’s virtualization capabilities provide a lightweight and efficient-list solution. Using a virtualized DataGroup in combination with pagination, you can keep your users, servers, and—most importantly—yourself off of the ledge.

This article walks you through the process of creating a simple Flex portlet that uses the open source AMFPHP implementation to connect to a MySQL database. The data will be displayed in a paginated DataGroup with a few customizations.

FLEX/FLASH PLAYER BENEFITS

Developing rich and interactive content for the Web can be challenging. With HTML/cascading style sheet (CSS)/JavaScript development, for example, you face constant browser inconsistencies. Creating robust-custom components is tedious and the development toolsets are lacking.  JavaScript editors typically only offer basic syntax validation and code completion for core language features.  More complex capabilities such as refactoring, efficient syntax validation and thorough auto completion are difficult to find.  There also seems to be no other way to ensure that your code behaves and looks consistent in multiple browsers than opening instances of each and verifying manually. Flex 4.5 addresses all of these issues with the Adobe Flash Player runtime, the Flex component set, and Adobe Flash Builder 4.5 integrated development environment (IDE).

Flash Player penetration and cross-platform support

Perhaps one of the most frustrating aspects of Web development is browser compatibility. You can never assume that your users have adopted modern browsers, so it’s often difficult to predict the behavior of certain functionality on your site. Rather than rely on a browser’s potentially undesirable interpretation of your code, why not leverage a technology that behaves predictably and consistently regardless of the environment? Flex 4.5 does just that, and with Flash Player 10.2 having already achieved more than 97 percent penetration in most markets, it is a safe bet. Using Flex 4.5, you can write code once and know that that code will behave the same in any browser. Oh, and did I mention it’s open source?

Not only can Flex applications run in a browser, but in less than five minutes, you can convert your application into a desktop application using Adobe AIR. Mobile device support? Flash Player 10.2 has already reached the latest version of Android, Android 3.1—Honeycomb, and it’s on its way to a growing number of mobile platforms.

The Flex component set

Flex 4.5 features a revamped-component architecture that is lightweight and focuses on improved designer–developer workflows. Adobe trimmed a lot of the excess weight from the previous MX component set with the advent of the Spark component architecture. Out of the box, Flex 4.5 offers data grids, rich-text editors, a great charting package, primitive graphics support, and a lengthy list of other components. You would be hard-pressed to find a more thorough and customizable component set on the market.

Flash Builder IDE

Now, it’s time to get down to business. To get started, the first thing you need is a development environment. Flash Builder 4.5 (previously Adobe Flex Builder) is Adobe’s IDE for Flex. It can truly streamline development; it comes with a full-featured toolset that includes network monitoring, performance profiling, unit test integration, code generation, and much more. Flash Builder is built on the Eclipse platform, which gives you the flexibility of using a stand-alone version or an Eclipse plug-in. For the purpose of this example, you can download a 60-day trial of either version from Adobe’s site at no cost.

PREREQUISITES

So, you need to display a large dataset in an interactive table on a website. More specifically, assume that you have a client who wants to display a list on his/her website of every zip code, city, and state. The client wants to make some sort of distinction between odd- and even-numbered zip codes, and he/she wants it to be fast.

WinLAMP

The zip code data needs a home. In this case, it will be stored in a MySQL database. MySQL is a popular open source relational database management system (RDBMS). To connect to the database from Flex, you need some sort of middle tier. For this example, you’ll use PHP with Apache. Although this may seem to be a growing list of technology to set up, fear not: There’s WinLAMP.

WinLAMP encapsulates Apache, MySQL, and PHP (and Perl, although you won’t be using it in this example) with a single, painless installation. You can download WinLAMP at no cost from SourceForge, or, if you prefer to install more current versions of these packages (what is included with WinLAMP should suffice),  bundled installers such as FoxServ are also available.

The WinLAMP setup process lets you configure exactly what you want to install (see Figure 1). The WinLAMP site has links to the Apache, MySQL, and PHP documentation if you need additional assistance. For this example, you only need the following components (but feel free to install more for your own exploration):

  • Apache2 + PHP5. The Web server and scripting language
  • MySQL 5.0. The open source RDBMS
  • phpMyAdmin 2.8.1. A great Web-based MySQL management tool

image

Figure 1. WinLAMP setup options

When you have finished WinLAMP installation, you can easily validate your installation by accessing phpMyAdmin. Assuming that you configured Apache to run on port 80, you can access it at http://localhost/phpmyadmin. Figure 2 shows what the login page looks like if installation was successful.

image

Figure 2. The phpMyAdmin login page

MySQL database

After logging in to phpMyAdmin, you need to create a new database. Use the form on the home page to do so. Call the database fx4demo, as shown in Figure 3.

image

Figure 3. Creating the database in phpMyAdmin

Now, you’re ready to create the table that will store the zip codes. The table is simple and has only three fields: zip code, city, and state. Rather than create it manually, however, click the SQL tab, and paste the table-creation query below. Click Go to execute the query and create the zip code table.

  	CREATE TABLE `zipcode` (
`zip` VARCHAR( 5 ) NOT NULL ,
`city` VARCHAR( 50 ) NOT NULL ,
`state` VARCHAR( 2 ) NOT NULL ,
PRIMARY KEY ( `zip` )
) ENGINE = INNODB;

Next, open the zipcode table view by clicking the table name in the left pane. To import the data, you need the zipcodes.sql file included in the code download for this article. Click the Import tab, and browse for the zipcode.sql file on your hard drive. Figure 4 shows an example of the import configuration.

image

Figure 4. Importing zip code data into the table

This process should import 42,460 rows of data into the zip code table. As you can see, you’re definitely working with a large amount of data—much more than can be reasonably returned to the client in a single batch.

AMFPHP

I mentioned earlier that you’ll be using PHP with an Apache Web server as the middle tier to return data from the MySQL database to the Flex client. A common approach for transferring data to Flex from PHP is to have the PHP script generate XML that the client consumes. The major downside to doing so is that XML can be cumbersome. Certainly, from a data-transfer perspective, XML is not optimized for speed. Furthermore, it requires marshalling the data into XML on the server side and un-marshalling the data back into objects once it reaches the client.

So, instead of XML, you’ll use an open source tool called AMFPHP, which does the work of converting rows from your database into structured objects that are returned to the client through Adobe Action Message Format (AMF)—a compact, binary format that is perhaps one of the fastest ways to get data to and from the client. In addition, when the data arrives at the client, it is already represented in objects and available for use throughout the application.

Install AMFPHP (and check out the AMFPHP site for more information). Within the AMFPHP archive, locate the amfphp folder, and extract its contents into a new folder called amfphp in your Apache document root folder (typically, C:\Apache2\htdocs on a Windows machine). To verify that everything is up and running, go to the URL http://localhost/amfphp/browser. You should see a service folder named amfphp in the Service Browser on the left.

The PHP Zip Codes class

Now that AMFPHP is installed and ready to roll, you need to create a PHP service, which your client will leverage for data access. This service class will expose methods to the client that retrieve and return data from the MySQL database.

Browse to the amfphp/services directory within your AMFPHP installation, then create a file named zipcodes.php and open it for editing in your favorite text editor or IDE. Declare a class named ZipCodes, and add some class-level private variables to represent the database connection information. Be sure to update the bold values in the code below with your database connection information. The constructor then uses this information to establish a MySQL connection and select the appropriate database. Here is an example of the server variables and constructor:

localhost";
private $_database = "fx4demo"; 
private $_user = "root";
private $_password = "password";
 	
 	// Constructor – establishes DB connection
function ZipCodes() {
$this->conn = mysql_connect($this->_server, $this->_user, $this->_password);
mysql_select_db ($this->_database);
}

Next, add two simple methods to the ZipCodes class. The first method, getZipcodeCount, simply returns the total number of zip codes in the zipcode table. The associated query is straightforward, and you need this method to support paging—a tactic often used to retrieve manageable batches of a large dataset. For example, given a dataset with 1000 records, you could retrieve 100 records at a time (per page), meaning that you would have 10 total pages of data.

The second method, getZipcodes, retrieves a page of zip code data. Two required parameters—offset and rowcount—dictate which record to start with and the total number of rows to be retrieved, respectively.

Notice the JavaDoc style comments located above the methods in the following code. These comments should be included, and AMFPHP will interpret them to understand the methods. Description (@desc) is optional and simply describes the method. Access (@access) tells AMFPHP who can access it. To call the code from a Flex client, you must set the access to remote. Finally, returns (@returns) provides a description of what the method will return.

/**
* @desc Returns a count of the total number of zipcodes
*
* @access remote
* @returns the total number of zip codes
*/
function getZipcodeCount() {
return mysql_query("select count(zip) as numZipcodes from zipcode");
}

 	/**
* @desc Retrieves a page of zip code data
*
* @access remote
* @returns a page of zip code data
*/
function getZipcodes($offset, $rowcount) {
// ensure parameters are numeric and greater than zero
if (!is_numeric($offset) || $offset < 0 || !is_numeric($rowcount) || $rowcount < 0)
 	return;
return mysql_query("select * from zipcode order by zip limit ".$offset.",".$rowcount);
}

Notice the paging logic in the getZipcodes query that follows. This code uses the MySQL limit clause to specify which record to start with and how many total records to retrieve. It is also important that you specify an order by field to ensure that the results are always sorted the same for paging to work properly. The values of the variables will be substituted into the expressions to form a valid MySQL limit clause.

// first page limit clause: ... limit 0, 1000
// second page limit clause: ... limit 1000, 1000
select * from zipcode order by zip limit ".$offset.",".$rowcount

That’s all there is to creating the PHP service. Note that there may be slight variations in how AMFPHP interprets this class depending on the version. (For the sake of consistency, I used AMFPHP version 1.9.) Save and close the class, and you can move on to verifying that it works.

Return to the AMFPHP browser (http://localhost/amfphp/browser) and refresh the page. You should see a new service named zipcodes. Click the service; the right pane should show the two methods, getZipcodeCount and getZipcodes. When you click on either method, the data you put in the comments is displayed. You can see the description, return information, and inputs for parameters (if applicable).

Start by testing the simple getZipcodeCount method. Click the method name, and then click Call to execute the query. A results pane is displayed at the bottom of the window in which the resulting objects are described. In this case, an ArrayCollection with a single object should have been returned. The object should have a property named numZipcodes with a value of 42192 (the total number of zip codes in the zipcode table).

Next, verify that the getZipcodes method is functioning properly. Click the method name, and then enter 0 and 10 for the offset and rowcount parameters, respectively. Doing so retrieves the first 10 zip codes in the table. Click Call to execute the query, and verify that an ArrayCollection of 10 objects containing zip code data is returned, as shown in Figure 5.

image

Figure 5. Results of getZipcodes in the AMFPHP browser

FLASHBUILDER PROJECT SETUP

Next, you cross over into the Flex world. The first step is to create the Flex 4 project. After you have created the project, you configure it to point to the AMFPHP service you created.

Flex 4 project creation

You’re finally ready to use the Flash Builder IDE that you installed at the beginning of this article. To create your Flex 4 project, click File > New > Flex Project. Call the project zipcode-manager, and store it in the default location. For this example, you’re creating a Web application, but I would certainly encourage experimenting with AIR applications some other time. Be sure to select the PHP application server type before clicking Next. Figure 6 shows the configuration page for this first step.

image

Figure 6. Flex project creation step 1

The next step is PHP server configuration. Specify the Web root for your Web server (again, something like C:\Apache2\htdocs, depending on your setup). The root URL should be http://localhost. The Flash Builder IDE automatically compiles your application and saves it to the output folder of your choice. For simplicity’s sake, I’ve chosen a folder called zipcodeManager in the Web root. Flash Builder 4.5 saves you potential downstream frustration by making you validate this configuration before proceeding. Figure 7 provides a look at this step. When validation has succeeded, click Finish, and the project and necessary files will be auto-magically created.

image

Figure 7. Flex project creation step 2

Service configuration

You need to give your Flex project some information about the AMFPHP service it will be connecting to. Although Flash Builder 4.5 has built-in support for such wiring, I have elected to go the manual route for this example.

Right-click in the src folder of the zipcode-manager project, and click New to create a new file named services-config.xml. Copy the contents of the code that follows into this new file, and be sure that the endpoint URI (in bold) points to the gateway.php file included in the AMFPHP installation on your computer.










*









 

 

 

 

 

 

Save and close the services-config.xml file. Now, right click the zipcode-manager project, and then click Properties. Then, click the Flex Compiler page, and paste the text from the code that follows in the additional compiler arguments text box. Be sure to append this code to the existing text. Click OK, and at last you’re ready to write some code.

-services services-config.xml

CREATE THE ZIPCODE MANAGER FLEX APPLICATION

Now that you have created the Flex project, you can finally start coding. One quick caveat before you begin: for future projects, as the Flex application grows in scale, you certainly should not put all of the code in only one or two files, as I have done. Look for ways to “componentize” and better organize your code. Also, consider leveraging a design pattern such as PureMVC, Cairngorm, or RobotLegs. Let’s proceed.

The main application file

Open the ZipcodeManager.mxml application file, which is the main-application file, and for the purpose of this example, the home of all of your Flex code. You see a bit of auto-generated code already present: You’ll need to make a few slight modifications to accommodate your requirements. This application is essentially supposed to be portlet sized, as it is going to be embedded in a client’s website. So, you must specify a width and height for the application. To do so, replace the minWidth and minHeight attributes with width and height, as shown in bold here:

Paging

Paging is often implemented with the Prev and Next buttons used to swap out the current page of data displayed in the table. This tactic seems a bit archaic, and it comes with several downfalls—the biggest being that you can only view one page of data at a time. What if a user wanted to scroll through a few thousand rows at a time? Too bad: It won’t happen with that approach.

Because Flex 4.5 offers lightweight and high-performing controls, it can actually handle all 42,000 records in a single control on the client at once, and it will allow the user to scroll through the records seamlessly. Therefore, rather than implement the old-school style of paging, you’re going to leverage the new AsyncListView class to do things a bit differently.

Basically, you create an empty collection with placeholders for all 42,000 zip codes. As the user scrolls through the list, the missing items to be displayed are requested, and an ItemPendingError will be dispatched (because the items have not yet been retrieved). The AsyncListView class wraps the collection and listens for these ItemPendingErrors. When one is caught, you can handle it with a function that inserts the missing page of data into the list for display.

Zip code and paging variables

To implement the scrolling style of paging, you must declare a few variables. The first variable, zipcodeData, is a PagedArrayCollection (more on that in a bit) and used to store your zip code data. The PAGE_SIZE constant indicates that you will retrieve zip codes in batches of 100. Finally, retrievedPages is a dictionary used to keep track of which pages of data you have already retrieved. Whenever a new page is retrieved, an entry for that page index is added to the dictionary.

// array collection to hold the zip code data
[Bindable] private var zipcodeData:PagedArrayCollection;

// a constant specifying the number of records per page
private static const PAGE_SIZE:Number = 100;
 	
 	// a dictionary for storing which pages have already been retrieved 
private var retrievedPages:Dictionary = new Dictionary();

The PagedArrayCollection class

As mentioned, you need to create a collection for the zip code data, with placeholders for all 42,000 or so zip codes (the zipcodeData variable mentioned earlier). You’re going to use an ArrayCollection as the base class for this example (note that there are certainly lighter-weight alternatives). This collection will ultimately be wrapped inside an AsyncListView and serve as the data provider for your list.

Create a new class called PagedArrayCollection that extends mx.collections.ArrayCollection. Add a private member variable of type Dictionary, and instantiate it in the constructor. This variable will be used to keep a record of which items have already been set.

The first method you need to add to the class is a setter for length. When the length is set, this setter will create the underlying ArrayList, with a placeholder for every item in the collection.

Next, override a few public ArrayCollection methods. When the list tries to retrieve an item from the collection for rendering in the view, it will invoke the ArrayCollection’s getItemAt method. If you have yet to retrieve the item in question from the server, the code will throw an ItemPendingError that the AsyncListView wrapper will catch.

Finally, override the setItemAt method. Whenever an item in the collection is set, you want to add an entry to the dictionary indicating that is has been set. Doing so prevents downstream ItemPendingErrors from being thrown when that item is accessed. The following code shows the implementation of the PagedArrayCollection class:

public class PagedArrayCollection extends ArrayCollection
{
// a dictionary for storing which items have already been set 
private var accessedItems:Dictionary;

public function PagedArrayCollection(source:Array=null)
{
super(source);
accessedItems = new Dictionary();
}

// setting the list creates a list with placeholders for each item
public function set length(length:int):void {
list = new ArrayList(new Array(length));
}

// throws an ItemPendingError if an item has not yet been set
override public function getItemAt(index:int, prefetch:int=0):Object {
if (accessedItems[index] == undefined) {
throw new ItemPendingError("itemPending");
}
return list.getItemAt(index, prefetch);
}

// sets the item and indicates that it has been set in the dictionary
override public function setItemAt(item:Object, index:int):Object {
accessedItems[index] = true;
return super.setItemAt(item, index);
}  
}

RemoteObject

Another code block that is actually new to Flex 4 is fx:Declarations, which is used to declare non-visual items such as effects and services. In this case, you’re going to create a RemoteObject in the declarations block to connect to your PHP class with the help of AMFPHP. Notice that the source (zipcodes) corresponds to the file name of the PHP class you created. Also note that the destination (amfphp) maps to the name of the destination are defined in your services-config.xml file. You’re also going to declare the two methods you defined in the PHP Service (zipcodes.php), as you’ll need to access both for this application.









SQL result and error handlers

Notice that in the previous code listing you specified one function to handle faults, or errors, for both methods. Because you aren’t too worried about error handling for this example, you can share a single error handling function between both queries. All you want to do if an error occurs is pop up an error message. The corresponding function is shown here:

// shared error handler function for both queries
private function handleZipcodeServiceError(event:FaultEvent):void {
Alert.show("Error: " + event.fault.toString());
}

When your application starts, you’ll first determine how many total records exist in the zipcode table. Therefore, you must execute the getZipcodeCount method and process the result with the handleZipcodeCountResult function. To execute this query when the application first starts, leverage the creationComplete event handler, shown in bold here:

The zip code count result handler function can extract the count from the first item in the result object. You can then instantiate the zipcodeData PagedArrayCollection and set its length to the total number of zip codes. The data provider of the list you’re displaying is bound to this collection (more on this later), so it will immediately try to render the first few items. Because no items have been set, any item accessed will throw an ItemPendingError. The AsyncListView wrapper catches the ItemPendingError, and you can then retrieve the necessary data—starting with the first page (records 0 to 99).

Once the page of data is returned, the zip code data result handler function simply inserts the zip code data into the proper location in the PagedArrayCollection, where it can then be rendered and displayed. You know where the data needs to go in the list, because the offset was stored in the token that was sent with the request and returned with the response.

The methods required to accomplish this four-step process are shown sequentially here:

// (1) result handler function for the zip code count query
private function handleZipcodeCountResult(event:ResultEvent):void {
var result:ArrayCollection = event.result as ArrayCollection;

if (result != null && result.length == 1) {
// extract the count from the first item in the result object
var countResultObj:Object = result.getItemAt(0);
var numZipcodes:Number = Number(countResultObj.numZipcodes);

// instantiate the paged array collection and set its length
// to the total number of zip codes
zipcodeData = new PagedArrayCollection();
zipcodeData.length = numZipcodes;
}
else {
Alert.show("Error retrieving zipcode count");
}
}
 	
 	// (2) handles pending item by getting the associated page of data
private function handleCreatePendingItem(index:int, ipe:ItemPendingError):Object {
// determine which page is needed and call get zipcodes for that page
callGetZipcodes(Math.floor(index / PAGE_SIZE));
return null;
}

// (3) helper to call the getZipcodes service method and pass the required parameters
private function callGetZipcodes(pageNumber:Number):void {
// only get the requested page if it has not yet been retrieved
if (retrievedPages[pageNumber] == undefined) {
// indicate that the page has been retrieved
retrievedPages[pageNumber] = true;

// determine the offset and package it in the token so the data
// can be added to the dataset at the proper location
var offset:Number = pageNumber * PAGE_SIZE;
var token:AsyncToken = zipcodeService.getZipcodes(offset, PAGE_SIZE);
token.offset = offset;
}
}

// (4) result handler function for the zip code page selection query
private function handleZipcodeResult(event:ResultEvent):void {
// extract the offset from the token
var offset:Number = Number(event.token.offset);
var data:ArrayCollection = event.result as ArrayCollection;

 	// iterate over each item in the result data set and add
// it to the zip code data set at the proper location
for (var i:Number = 0; i < data.length; i++) {
zipcodeData.setItemAt(data.getItemAt(i), i + offset); 
}
}

Virtualized DataGroup

To display the list of data, you need to use the most efficient means possible. The Flex DataGrid component is great, but its use of item renderers is not as efficient as it could be. Because you’re going to display up to 40,000 zip codes in a list with custom-item renderers, use the Spark DataGroup control, instead. You’ll use this component to display data in a list format; more importantly, it can be virtualized. List virtualization optimizes layout and rendering for containers with many items. It also decreases initialization time and lowers the memory footprint of the list container.

To further improve the performance with list virtualization, you can describe what a typical item looks like in the DataGroup. Doing so helps the DataGroup define the measurements of each item for rendering. Typical items in this example are simple: an object with a zip code, city, and state. The DataGroup, layout, and list virtualization are all defined here:











Zip code item renderer

I mentioned earlier that the client had some rather odd requirements. For example, they want you to show the zip code, city, and state with an orange background if the zip code is numerically even or a gray background if it is odd. They also want the text color to change from white to black as you hover over an item. The item renderer below accomplishes these requirements using two-linear gradients, a rectangle, and a label with two simple states. Although this item renderer is relatively simple, the list virtualization functionality you’re leveraging becomes increasingly important with more complex implementations:




























      









CONCLUSION

You have come to the end of your journey. You started by setting up PHP, MySQL and Apache. After that, you created your database and loaded it with data. Next, you installed AMFPHP and created a simple PHP class to enable data access, then created a Flex project and got your application connected to the PHP service. After wiring up on-demand scroll-paging, you added a high-performance virtualized DataGroup with a custom-item renderer for displaying the zip code data. To see the result of this adventure, run the application from the Flash Builder IDE (see Figure 8). Notice how smooth and efficient it is:

image

Figure 8. The final product

Thanks to the lightweight DataGroup container with list virtualization, you can scroll through the list easily. As soon as you reach a point in the list for which you need additional data, a call is automatically made to the server, and the data is inserted into the list. The item renderers are recycled seamlessly as you roll through countless rows, which is a nice contrast to the typical memory-depleted, painful experiences so often encountered with large lists of data.

You may also notice how quickly each additional page of data is retrieved. In a second or less, a query is being executed on the MySQL database, and 100 rows are converted into objects and transferred to the Flex client via AMF. I can’t imagine the client being unhappy about those types of response times. You can now easily take the generated code used to embed the Flex application (SWF file) on this page to drop it in to the client’s website as a portlet. Check out SWFObject to make this process even easier.

You have created an incredibly optimized, high-performing, rich Internet application without a substantial development effort. Using integrated paging and list virtualization within Flex 4.5, your application both quickly and efficiently displays data from a MySQL database. You can download the complete source files for this tutorial from here.

FOR MORE INFORMATION

 

Get new stories first

Click to follow us on Twitter!

 

Comments

No comments for this page.

Submit a comment

Only registered members can comment. Click here to login or here to register