I'm definitely marking this down in the "I should have known better" category. If you use MSSQL, you probably know by know that "user" is a reserved word. So, unless you use [user] as a table identifier it's imperative that you name your table something like "tbl_user" or use the plural "users". I don't like plural table names, so I opted for tbl_user. I just ran into a gotcha with my TQL for a query against tbl_user. Why? Here's a snippet from my method to check the uniqueness of a user e-mail.

// local vars struct
var local = StructNew();
// default result
local.result = false;
// tql for list
local.tql = "from user.User as user where user.Email = :Email AND user.Id != :Id";
//create a query object
local.query = getTransfer().createQuery(local.tql);
//set the named parameters
local.query.setParam("Email", getEmail(), "string");
local.query.setParam("Id", getId(), "numeric");
//run it
local.userList = getTransfer().listByQuery(local.query);
// if no records exist e-mail passes unique test
if (local.userList.recordcount eq 0)
{
	local.result = true;
}	
return local.result;

My transfer object class name is user.User, so why not alias that as "user"? That's a logical alias, right? Wrong!

After pulling my hair out debugging the ever-so-descriptive error message, [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '.'.

I finally decided to write up a unit test to try and get some meaningful debugging information. From there I pulled a query, ran that in the Query Analyzer. Scratched head a bit more, then...voila! Reserved word issue. How silly....

Of course, this is not purely a Transfer or even TQL problem, merely a cautionary tale regarding reserved words in MSSQL that I happened upon via Transfer TQL.

For the record, the new TQL query reads:

local.tql = "from user.User where user.User.Email = :Email AND user.User.Id != :Id";			

The other big a-ha moment for me is that if an error occurs within a method invocation, I should debug by unit testing, not re-initializing, rinsing and repeating on the client side.

Before we can scaffold screens for our generic admin, we'll need a data model. Below is a E/R diagram for the trivia game data model, which contains the following entities: User, Theme, Category, Game, Question, Answer and Response. This is a simple data model with several one-to-many relationships and no many-to-many relationships.

Briefly, a Game has a parent User and Theme. A Question has a parent Game and Category. An Answer has a parent Question. And a Response has a parent User, Question and Answer.

There's nothing too complex about this data model, so I'll focus on the conventions used to name the table primary and foreign key attributes. Each table has a PK named simply "Id" and each foreign key is prefaced by the parent entity name and "Id". For example, the Game entity has foreign key attributes "ThemeId" and "UserId".

To keep things simple and perhaps facilitate reporting or statistics down the line, all PKs and FKs are of type integer.

You can download the enclosed sql scripts to create the tables in either MSSQL or MySQL format. I chose to create proper FK constraints for the tables. If you prefer not to use foreign keys, feel free to remove them from the sql prior to running the scripts.

Next, we'll look at an Illudium template for generating a service, controller and Transfer Object decorator to support create, read, update and delete (CRUD) operations.