Fancy Bread

Maintaining referential integrity in your database is a good thing. Any decent RDBMS will include ACID (Atomicity, Consistency, Isolation, Durability) transaction support for ensuring consistency and recoverability of your database. With the Transfer ORM Transaction object, one can apply transactions easily to a Service or Gateway. Here's how one can use it to wrap all "save" and "delete" methods for a Service.

Read more...

Confidently unit testing code that relies on a database is troublesome. There are many different approaches and many opinions on the matter. I've previously written about using transactions to safely rollback the database after every test. This worked well for me at the time, but there are pitfalls in using that approach. Recently, I've done some more research on the subject and even attempted, without success, to integrate DBUnit into my database reliant unit tests. After some trial and error, I landed on the following approach that is a culmination of a few ideas I'd previously heard of, but never tried. The goal of this approach is to:

1. Run tests against a known data set.
2. Reset the entire database prior to each test.

The example code that follows requires a test database, test database user and a test datasource targeting the test database.

Read more...

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.

To go along with my previous woolgathering on the correlation between a database schema and a class package, here is another observation regarding class inheritance and sub/supertype tables. In object modeling, we describe the relationship between a subclass and superclass as "is a". The same goes for tables in a data model, where the subtype "is a" supertype. The obvious benefit of using inheritance in object modeling is code reuse. When we add, remove or alter a method of the superclass, the change is automatically inherited by the subclass. In a data model the benefit of using subtypes is arguable. The benefit are that your model better represents the domain, reduces data duplication and (depending on the extent of your normalization) eliminates NULL values. The trade-off is that you are thereafter required to use joins when reading records and transactions when creating, updating or deleting records.

Read more...