One of the core elements of the Delphi REST extension project I'm working on (Relax) is its data access architecture, based on a few standard patterns. This is not the core of the project, but it is one of the foundations that opens up JavaScript mapping and everything else I'll cover in a later post. The TableModule and DataGateway classes are already available. I'll post the documentation ASAP.
The Data Access Layer in Relax implements (in a rather simplified way, that might be expanded) a couple of data access patterns. It is not compulsory to use those in your code, as you can follow different approaches, but the overall architecture and the JSON / JavaScript mapping layers are based on this data access model, so I really suggest you do adopt it (with the custom extensions you might deem useful). The Data Access Layer uses two different patterns, loosely modeled after those covered in Fowler's “Patterns of Enterprise Application Architecture” (http://martinfowler.com/books.html#eaa) official description, but tuned to Delphi's specific data model.
For manipulating individual records, it uses the ActiveRecord pattern. In this case the code relies heavily on Delphi's extended RTTI (and the RTTI.pas unit) and the system uses attributes for customizing the mapping of object properties to database fields. (This part is still not available, will be soon.)
For manipulating entire record sets, it uses the combined TableModule and DataGateway patterns, but providing read-only data access and relying on the ClientDataSet for the in-memory representation of record sets and their manipulation. These two patterns are used for generic table-oriented access, but also fine-tuned to offer a proper back end to the jqGrid component of jQuery.
Information on these three patterns can be found on Martin Fowler web site, but full details are in his book:
ActiveRecord: http://martinfowler.com/eaaCatalog/activeRecord.html
TableModule: http://martinfowler.com/eaaCatalog/tableModule.html
DataGateway: http://martinfowler.com/eaaCatalog/tableDataGateway.html
There are two core units, RlxTableModule and RlxActiveRecord, plus a few support ones, like RlxDatabaseAccess for managing dbExpress connection and data retrieval (which could be easily replaced by a different data access layer, since this is kept separate and minimal).
One last warning, a few elements of the code are currently tied to Firebird. The idea is to write some very simple database-specific drivers with the limited settings that are database specific. Right now these are mixed in the code and hard to separate.
TableModule and DataGateway (the RlxTableModule Unit)
The first two patterns are used to access entire tables. The TRlxTableModule component encapsulated a ClientDataSet, which is populated by accessing the database through a TRlxDataGateway, an abstract class having TRlxTableGateway and TRlxQueryGateway as concrete implementations.
In other words the TRlxTableModule uses a geteway for accessing data and exposes it in an internal ClientDataSet. It has also methods for converting the internal data to a couple of different JSON formats.
The TRlxDataGateway is an abstract class (well, a TComponent with abstract methods you'll never create instances of). It exposes a very simple interface:
function GetSQLQuery: string; virtual; abstract;
function GetCountQuery: string; virtual; abstract;
function CreateTableModule: TRlxTableModule;
The two virtual abstract functions return SQL statements, the last method creates a table module for the current data gateway.
The concrete implementations of the abstract class are TRlxQueryGateway and TRlxTableGateway. The first basically keeps a custom SQL query around (and has a couple of simple modifiers for helping paging records), the second creates SQL queries dynamically based on a number of simple properties (table name, fields list, where and order by clauses, and so on). This is the overall architecture:
