Once upon a time, in what now seems to be a prior life, I became a published author when my thesis hit the university press. That time has come again. After much effort, the second longest thing I have ever written has been published by InsideRIA. I wrote a two-part article titled Getting Real with LCDS 3 (Part 1 & Part 2) that covers the basics of model driven development with LCDS 3. Yeah me.

More Fun with LCDS

Since I’m such a new-stuff-loving guy I can’t help but return to the backend after a few months on the frontend with various Axiis visualizations (here & here) and a GTween animation (here). So, it’s back to LCDS. Of course, since I last wrote about LCDS, Adobe has shipped LCDS 3, but thankfully they still provide a free developer trial. This time around, I’ll look at LCDS and MySQL Views.

Create Database and Tables

First, let’s set the scene by constructing a new database and some tables. Here’s the database:

CREATE DATABASE football;
CREATE USER 'baller'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON football.* TO baller@localhost;

And here are the tables:

CREATE TABLE teams (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255),
  PRIMARY KEY (id)
);
 
CREATE TABLE players (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255),
  salary int(11),
  team_id int(11),
  PRIMARY KEY (id)
);
 
CREATE TABLE games (
  id int(11) NOT NULL AUTO_INCREMENT,
  team_id int(11),
  opp_id int(11),
  points int(11),
  win tinyint(1),
  PRIMARY KEY (id)
);

Lastly, we insert some sample data into the database via a script. The final result is our familiar teams-and-players database, but this time with an additional games table.

MySQL Views

A database view is one way to push logic and computations down from the application layer into the database. A judicious use of views can really help keep the application layer nice and clean. But much like database de-normalization, adding views to the database often feels like premature optimization, so be careful. LCDS and the Modeler plugin treat database views just like tables, so everything just works (sort of).

MySQL is a full-featured database that provides a simple CREATE VIEW syntax. For our purposes, a view can be thought of as nothing more than another table that is constructed from other tables via a SELECT statement. Because of this, database views are sometimes called virtual tables. The SELECT statement used to construct the view is often very complicated, involving multiple tables, joins, functions, expressions, and more.

For our example, we will construct two views. First, the team_summary view packages up information from the teams and players tables:

CREATE VIEW team_summary AS SELECT
  t.id,
  t.name,
  COUNT(p.id) AS num_players,
  ROUND(SUM(p.salary)/1000000,2) AS payroll,
  ROUND(AVG(p.salary), 0) AS avg_salary,
  MIN(p.salary) AS lowest_salary,
  MAX(p.salary) AS highest_salary
FROM teams AS t, players AS p WHERE t.id = p.team_id GROUP BY t.id;

The CREATE VIEW above joins the teams and players tables to create seven columns. Here’s a brief description of the generated columns:

Next, we create the game_summary view to collect data from the teams and games tables:

CREATE VIEW game_summary AS SELECT
  t.id,
  t.name,
  CAST(CONCAT(SUM(g.win), '-', COUNT(g.id) - SUM(g.win)) AS CHAR) AS record,
  CAST(SUM(g.points) AS SIGNED) AS points_for
FROM teams AS t, games AS g WHERE t.id = g.team_id GROUP BY t.id;

And a brief description of the generated columns:

Digging Deeper: The CAST statements are not necessary to construct the game_summary view as far as MySQL is concerned, but I need them to force the correct column types to make LCDS happy.

LCDS 3 and the Modeler Plugin

Now that our database is happily configured, fire up Flash Builder 4 and switch to the Modeler plugin. Create and configure a new LCDS webapp, and then take a look at the RDS Dataview panel:

You can see both views and tables. Next, simply drag whatever tables or views you want over to the Modeler. Here’s the final model for our sample application:

LCDS Modeler Tricks & Tips

There are a couple of tricks to getting views working correctly in LCDS:

1. Mark each view entity as persistent in the Properties panel (in theory, views can support updates to only those columns that are one-to-one mapped to a table)
2. Set the id column as the ID property (this is required because views don’t have primary keys, so you have to tell LCDS what to do)
3. Verify that the database types have correctly come across to LCDS, if not add a CAST to the view column to force the correct type (see table below)

Here’s a simple table showing how the types move from the MySQL database to LCDS, and then from LCDS to PASOs (aka Plain old ActionScript Objects):

Data

We can view the data in the database by using the RDS Query Viewer. Here is the team_summary data:

And here is the game_summary data:

The App

Alas, since this is an LCDS app, no running demo is possible. Here’s a screenshot instead, showing a selected team and the associated team & game summary data.

Our sample app closely follows the plan I set forward in Getting Real with LCDS, Part 2. We define our services and associated CallResponder‘s in MXML, and event handlers in ActionScript.

Here is the relevant section of code showing the various event handlers:

private function teamChangeHandler():void {
	var teamId:int = list.selectedItem.id
	getTeamSummary.token = teamSummaryService.getById(teamId);
	getGameSummary.token = gameSummaryService.getById(teamId);
}
 
private function getTeamSummaryResult(event:ResultEvent):void {
	var summary:TeamSummary = (event.result as IList).getItemAt(0) as TeamSummary;
	numPlayers.text = summary.numPlayers.toString();
	payroll.text = cf2.format(summary.payroll) + ' million';
	avgSalary.text = cf.format(summary.avgSalary);
	lowestSalary.text = cf.format(summary.lowestSalary);
	highestSalary.text = cf.format(summary.highestSalary);
}
 
private function getGameSummaryResult(event:ResultEvent):void {
	var summary:GameSummary = (event.result as IList).getItemAt(0) as GameSummary;
	record.text = summary.record;
	pointsFor.text = summary.pointsFor.toString();
}

And here is the MXML Declarations block:

<fx:Declarations>
	<s:CallResponder id="getAllTeams" />
	<s:CallResponder id="getTeamSummary" result="getTeamSummaryResult(event)" />
	<s:CallResponder id="getGameSummary" result="getGameSummaryResult(event)" />
 
	<football:TeamService id="teamService" />
	<football:TeamSummaryService id="teamSummaryService" />
	<football:GameSummaryService id="gameSummaryService" />
</fx:Declarations>

The application is very simple. When a user clicks on a team, the teamChangeHandler() is called. The handler queries the LCDS backend and sets the CallResponder‘s token property. When the asynchronous backend eventually returns some data, the data is forwarded to the appropriate result handler. In the result handler, the incoming PASO has it’s properties are formatted and then assigned to the matching form element for display.

Files

• Football.zip
• create_db.sql
• create_tables.sql
• create_views.sql
• data.sql