keyTMS ALL-ACCESS
local_offerOne money and time saving bundle
mouseCross Platform UI
bar_chartReporting
languageWeb
swap_horizCommunication
storageData
mouseWindows UI
  •  
  •  
  •  
  1. Home
  2. Blog

Blog

All Blog Posts  |  Next Post  |  Previous Post

Crash Course TMS Aurelius - Associations (Foreign Keys)

Monday, February 18, 2013

Besides mapping tables to classes and table columns to fields/properties, Aurelius also maps relationships (foreign keys) to object associations. One nice thing about Aurelius is that such associations are defined in a very simple way: just references to other objects. Consider the following classes with respective mapping: 
type
  [Entity, Automapping]
  TCountry = class
  private
    FId: integer;
    FName: string;
  public
    property Id: integer read FId write FId;
    property Name: string read FName write FName;
  end;

  [Entity, Automapping]
  TCustomer = class
  private
    FId: integer;
    FName: string;
    FCountry: TCountry;
  public
    property Id: integer read FId write FId;
    property Name: string read FName write FName;
    property Country: TCountry read FCountry write FCountry;
  end;
Note that TCustomer has an association to TCountry, meaning that every customer has a country associated to it. The following code should how you would save a TCustomer object with an associated TCountry object: 
function CreateCustomerWithCountry(Manager: TObjectManager): integer;
var
  Customer: TCustomer;
  USACountry: TCountry;
begin
  USACountry := TCountry.Create;
  USACountry.Name := 'USA';
  Customer := TCustomer.Create;
  Customer.Name := 'John';
  Customer.Country := USACountry;
  Manager.Save(Customer);
  Result := Customer.Id;
end;
Very simple and straightforward. Note that we didn't need to save Country object - when Customer is saved, Country is automatically saved because it's associated to it (this is the default behavior of automapping. You can fully configure the mapping to avoid Country to be saved automatically, if you want to).

It's also very simple to retrieve an object and its associations from database. Consider the following code that takes a customer id and returns the name of the country associated with the customer: 
function GetCountryNameFromCustomer(Manager: TObjectManager; CustomerId: integer): string;
var
  Customer: TCustomer;
begin
  Customer := Manager.Find<TCustomer>(CustomerId);
  if Customer <> nil then
    Result := Customer.Country.Name
  else
    Result := '';
end;
The code retrieves a TCustomer object instance based on the id (such Find method will be topic for another blog post). To obtain the name of the country, all we have to do is to get the associated TCountry object instance (through the TCustomer.Country property) and return its Name property. Also very simple. When TCustomer instance was retrieved, its associated objects were also retrieved. You can also fully configure this, and you can even set up things so that the TCountry object is only retrieved when needed (also a topic for another post).

Associations are a core feature of any ORM framework and this small example is a very simple one. Aurelius has many features related to associations, many ways of dealing with them, saving, retrieving, etc. But the purpose of this blog post is just to explain the concept. Feel free to ask questions in comment about what else you would like to be better explained in a future blog post.

To make it even more clear, I will post here the SQL statements executed by Aurelius when the code above was executed, so you can easily relate the objects with the underlying database. The statements used here were executed in an SQL Server database (syntax will be different if using another database).

The following statements were executed to create the tables so you can have an idea of the database structure (code to create the database is not explicit in this post): 
CREATE TABLE COUNTRY (
  ID INTEGER IDENTITY(1,1) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  CONSTRAINT PK_COUNTRY PRIMARY KEY (ID));

CREATE TABLE CUSTOMER (
  ID INTEGER IDENTITY(1,1) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  COUNTRY_ID INTEGER NULL,
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID));

ALTER TABLE CUSTOMER ADD CONSTRAINT 
  FK_CUSTOMER_COUNTRY_COUNTRY_ID FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (ID)
When saving the TCustomer object instance (function CreateCustomerWithCountry), the following statements were executed (the content of parameters is displayed): 
INSERT INTO COUNTRY (NAME) VALUES (:A_NAME);
A_NAME = "USA" (ftString)

SELECT CAST(IDENT_CURRENT('COUNTRY') AS INT);

INSERT INTO CUSTOMER (
  NAME, COUNTRY_ID)
VALUES (
  :A_NAME, :A_COUNTRY_ID);

A_NAME = "John" (ftString)
A_COUNTRY_ID = "1" (ftInteger)

SELECT CAST(IDENT_CURRENT('CUSTOMER') AS INT)
Finally, and the most interested one in my opinion, this is the SQL statement executed to retrieve back the TCustomer obejct instance. Note that in this example two different TObjectManager objects were used to force the SELECT execution. If a single manager had been used, manager would have retrieved the object directly from manager and would not need to execute an extra SELECT statement to retrieve the data. 
SELECT A.ID AS A_ID, A.NAME AS A_NAME, A.COUNTRY_ID AS A_COUNTRY_ID, B.ID AS B_ID, B.NAME AS B_NAME
FROM CUSTOMER A
  LEFT JOIN COUNTRY B ON (B.ID = A.COUNTRY_ID)
WHERE  A.ID = :p_0

p_0 = "1" (ftInteger)


Wagner Landgraf




This blog post has not received any comments yet.



Add a new comment

You will receive a confirmation mail with a link to validate your comment, please use a valid email address.
All fields are required.



All Blog Posts  |  Next Post  |  Previous Post