PRODUCTS » Quick Links

FEATURED PRODUCT

With one subscription at over 50% discount price, you receive all current and future TMS VCL products + updates + support for 2 years

License only 695 EUR See More

LOGIN

Customer login to access products, support information & special benefits.

SEARCH

STAY IN TOUCH

Add your e-mail address here to receive the monthly TMS Software alerts.

 


Content Filter:
Product releases
Product articles
Technical articles
Website changes
Software development

Product Filter:

<< >>
May 2013





Friday, March 08, 2013

Crash Course TMS Aurelius – Inheritance and Polymorphism

Inheritance is one of my favorite features in Aurelius. One of benefits of using an ORM is abstracting the SQL and start thinking (almost) purely in OOP. Inheritance and polymorphism are fundamental features of Object-oriented programming, and if when designing your model you can't use it, then the "object-relational" mapping would just become a simple "property>column" mapping in the end.

Aurelius allows you to build a class hierarchy that can be persisted, and provides you with two strategies to persist it: joined tables and single table. The former will create a different table for each class and add the proper relationships, and the later will save the whole hierarchy in the same table. You can learn more about it reading the topic "Inheritance Strategies" in documentation.

Let me illustrate how it works. Considering the following classes and mapping:
type
  [Entity, Automapping]
  [Inheritance(TInheritanceStrategy.JoinedTables)]
  TPerson = class
  private
    FId: integer;
    FName: string;
  public
    property Id: integer read FId write FId;
    property Name: string read FName write FName;
  end;

  [Entity, Automapping]
  TEmployee = class(TPerson)
  private
    FSalary: Currency;
  public
    property Salary: Currency read FSalary write FSalary;
  end;
Note that mapping is also very straightforward, all you need to do is specify the strategy to be used in the base class of your hierarchy. Now you can save your objects in the same way we did in previous posts:
function SavePerson(Manager: TObjectManager): integer;
var
  Person: TPerson;
begin
  Person := TPerson.Create;
  Person.Name := 'John Person';
  Manager.Save(Person);
  Result := Person.Id;
end;

function SaveEmployee(Manager: TObjectManager): integer;
var
  Employee: TEmployee;
begin
  Employee := TEmployee.Create;
  Employee.Name := 'James Employee';
  Employee.Salary := 1999.99;
  Manager.Save(Employee);
  Result := Employee.Id;
end;
After calling the above methods, we have one TPerson object and one TEmployee object persisted in the database. We can use the following code to retrieve them using the generated id's:
procedure OutputPerson(Person: TPerson);
begin
  if Person <> nil then
    WriteLn(Format('Class: %s; Name: %s', [Person.ClassName, Person.Name]))
  else
    WriteLn('nil');
end;

procedure OutputEmployee(Employee: TEmployee);
begin
  if Employee <> nil then
    WriteLn(Format('Class: %s; Name: %s; Salary: %s',
      [Employee.ClassName, Employee.Name, FloatToStr(Employee.Salary)]))
  else
    WriteLn('nil');
end;

procedure CheckPersonAndEmployee(Manager: TObjectManager; PersonId, EmployeeId: integer);
var
  Person: TPerson;
  Employee: TEmployee;
begin
  Person := Manager.Find<TPerson>(PersonId);
  OutputPerson(Person);
  Person := Manager.Find<TPerson>(EmployeeId);
  OutputPerson(Person);
  Employee := Manager.Find<TEmployee>(EmployeeId);
  OutputEmployee(Employee);
  Employee := Manager.Find<TEmployee>(PersonId);
  OutputEmployee(Employee);
end;
This is what we get as the output:
Class: TPerson; Name: John Person
Class: TEmployee; Name: James Employee
Class: TEmployee; Name: James Employee; Salary: 1999.99
nil
Now you see polymorphism in action. The first two Find calls ask for a TPerson object. It happens that the first id is a TPerson object indeed, but the second is an id for a TEmployee object. Both are retrieved because a TEmployee is a TPerson. Also note that the retrieved object in second Find is actually a TEmployee object.

The last two Find calls ask for a TEmployee object. When the EmployeeId is provided, the correct TEmployee object is retrieved. But when we ask for a TEmployee object passing PersonId as Id, nil is returned - although the object is in database with that id, it's not returned because the object is not a TEmployee, but only a TPerson.

As in the previous posts, I will provide here some SQL statements generated by Aurelius, for a better understanding. When using joined tables strategy, Aurelius will create the following database structure (SQL Server syntax):
CREATE TABLE PERSON (
  ID INTEGER IDENTITY(1,1) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  CONSTRAINT PK_PERSON PRIMARY KEY (ID));

CREATE TABLE EMPLOYEE (
  ID INTEGER NOT NULL,
  SALARY NUMERIC(20, 4) NOT NULL,
  CONSTRAINT PK_EMPLOYEE PRIMARY KEY (ID));

ALTER TABLE EMPLOYEE ADD CONSTRAINT 
  FK_EMPLOYEE_PERSON_ID FOREIGN KEY (ID) REFERENCES PERSON (ID);
Each class will have its data saved in a different database, and retrieving a TEmployee object would execute the following statement:
SELECT A.ID AS A_ID, A.SALARY AS A_SALARY, B.ID AS B_ID, B.NAME AS B_NAME
FROM EMPLOYEE A
  LEFT JOIN PERSON B ON (B.ID = A.ID)
WHERE  B.ID = :p_0
To conclude this post, let's change the strategy to single table. This will make the mapping look like this (Salary property has to be nullable because all data will stay in a single table):
  [Entity, Automapping]
  [Inheritance(TInheritanceStrategy.SingleTable)]
  [DiscriminatorColumn('PERSON_TYPE', TDiscriminatorType.dtString)]
  [DiscriminatorValue('Person')]
  TPerson = class
  private
    FId: integer;
    FName: string;
  public
    property Id: integer read FId write FId;
    property Name: string read FName write FName;
  end;

  [Entity, Automapping]
  [DiscriminatorValue('Employee')]
  TEmployee = class(TPerson)
  private
    FSalary: Nullable<Currency>;
  public
    property Salary: Nullable read FSalary write FSalary;
  end;
Code will be exactly the same. Database structure will become just this:
CREATE TABLE PERSON (
  ID INTEGER IDENTITY(1,1) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  PERSON_TYPE VARCHAR(30) NOT NULL,
  SALARY NUMERIC(20, 4) NULL,
  CONSTRAINT PK_PERSON PRIMARY KEY (ID));
and this is how an employee is retrieved from database:
SELECT A.ID AS A_ID, A.NAME AS A_NAME, A.PERSON_TYPE AS A_PERSON_TYPE, A.SALARY AS A_SALARY
FROM PERSON A
WHERE A.PERSON_TYPE = :p_1
 AND A.ID = :p_0

p_0 = "1" (ftInteger)
p_1 = "Employee" (ftString)


Bookmarks: 

Wagner Landgraf




This blog post has not received any comments yet. Add a comment.



Thursday, February 28, 2013

Crash Course TMS Aurelius – Blobs

Using blobs in Aurelius is very straightforward and yet very powerful. In summary, all you have to do is declare your field/property as TBlob (declared in unit Aurelius.Types.Blob.pas). This is enough to map it to an existing blob field in your table, and you will be able to save/load the blob content is several many ways. Consider the following mapping:
  [Entity, Automapping]
  TCustomer = class
  private
    FId: integer;
    FName: string;
    FDocument: TBlob;
    [Column('Photo', [TColumnProp.Lazy])]
    FPhoto: TBlob;
    [Column('Descr_Field', [], 65536)]
    FDescription: string;
  public
    property Id: integer read FId write FId;
    property Name: string read FName write FName;
    property Document: TBlob read FDocument write FDocument;
    property Photo: TBlob read FPhoto write FPhoto;
    property Description: string read FDescription write FDescription;
  end;
We have declared three blob properties in our class: Document, Photo and Description. I have used those to show slightly different ways of using blobs. Document and Photo are declared as TBlob, which is the recommended way. Alternatively, Description is declared as string, but manually mapped to database using a size greater than 65535. This tells Aurelius to also consider this field as a blob (memo/cblob to be more specific) instead of VarChar. You could also declare the property as a dynamic array of byte, but it's not recommended, since you gain nothing from doing it. Note that I have also used an unusual field name for Description (Descr_Field) just to show you how manual mapping works.

There is another interesting feature about blobs: Photo is declared as lazy (TColumnProp.Lazy). This indicates that Aurelius will not bring the blob from database when Customer data is retrieved. The blob is only retrieved when your code explicitly reads the content of Photo property.

The following code shows different ways of dealing with blobs (saving and loading):
function SaveCustomerWithBlobs(Manager: TObjectManager): integer;
var
  Customer: TCustomer;
begin
  Customer := TCustomer.Create;
  Customer.Name := 'John';

  Customer.Photo := TFile.ReadAllBytes('picture.bmp');
  Customer.Document.AsBytes := TFile.ReadAllBytes('document.pdf');
  Customer.Description := TFile.ReadAllText('description.txt');

  Manager.Save(Customer);
  Result := Customer.Id;
end;

procedure LoadCustomerAndExportBlobs(Manager: TObjectManager; CustomerId: integer);
var
  Customer: TCustomer;
begin
  Customer := Manager.Find<TCustomer>(CustomerId);

  TFile.WriteAllText('description2.txt', Customer.Description);
  TFile.WriteAllBytes('document2.pdf', Customer.Document);
  TFile.WriteAllBytes('picture2.bmp', Customer.Photo.AsBytes);
end;
As you can see, you can use TBlob.AsBytes property explicitly, or rely on the implicit conversion to TBytes. You could also use AsString property and streams:
  Customer.Photo := TBlob.Create(TFile.ReadAllBytes('picture.bmp'));
  Customer.Document.AsString := 'Some document';
  Stream := TFile.Open('picture.bmp', TFileMode.fmOpen);
  Customer.Photo.LoadFromStream(Stream);
  Stream.Free;
TBlob type also offers methods like IsNull, Clear, and also provides direct access to raw data to improve performance if needed.

As a final note: we have added TColumnProp.Lazy to the Photo blob. We can verify if the blob is loaded using the Loaded property. We can change LoadCustomerAndExportBlobs function to check it:
  Customer := Manager.Find<TCustomer>(CustomerId);

  Assert(Customer.Document.Loaded);
  TFile.WriteAllBytes('document2.pdf', Customer.Document);

  Assert(not Customer.Photo.Loaded);
  TFile.WriteAllBytes('picture2.bmp', Customer.Photo.AsBytes);
  Assert(Customer.Photo.Loaded);
After TCustomer instance is loaded, Document property is loaded, but Photo is not loaded, because we set it as lazy. After we access content of Photo to save it to picture2.bmp file, Photo.Loaded becomes true. You can also see it happening when you check the generate SQL statements:
CREATE TABLE CUSTOMER (
  ID INTEGER NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  DOCUMENT BLOB,
  Photo BLOB,
  Descr_Field BLOB SUB_TYPE TEXT,
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID));

CREATE GENERATOR SEQ_CUSTOMER;

SELECT GEN_ID(SEQ_CUSTOMER, 1)
FROM RDB$DATABASE;

INSERT INTO CUSTOMER (
  ID, NAME, DOCUMENT, Photo, Descr_Field)
VALUES (
  :A_ID, :A_NAME, :A_DOCUMENT, :A_Photo, :A_Descr_Field);

SELECT A.ID AS A_ID, A.NAME AS A_NAME, A.DOCUMENT AS A_DOCUMENT, A.Descr_Field AS A_Descr_Field
FROM CUSTOMER A
WHERE  A.ID = :p_0;

SELECT A.Photo As f0_
FROM CUSTOMER A
WHERE  A.ID = :p_0;
The first statement shows how the table is created (Firebird in this example). The INSERT statement creates the customer in the database, and saves the blobs. The first SELECT statement retrieves only Document and Description, but not Photo. The last SELECT statement is executed later, to retrieve the content of Photo field, only when the content of Photo property is read from code.

Bookmarks: 

Wagner Landgraf




This blog post has not received any comments yet. Add a comment.



Monday, February 18, 2013

Crash Course TMS Aurelius – Associations (Foreign Keys)

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)


Bookmarks: 

Wagner Landgraf




This blog post has not received any comments yet. Add a comment.



Monday, February 11, 2013

Crash Course TMS Aurelius – AnyDAC or dbExpress?

In the example provided in the previous post, we saved a TCustomer instance in a local SQLite database which was accessed natively by TMS Aurelius. Let’s refactor that code a little bit:
procedure SaveCustomer(Connection: IDBconnection; CustomerName: string);
var
  Manager: TObjectManager;
  Customer: TCustomer;
begin
  Manager := TObjectManager.Create(Connection);
  Customer := TCustomer.Create;
  Customer.Name := CustomerName;
  Manager.Save(Customer);
  Manager.Free;
end;
With the procedure above, to save a customer in the SQLite database, we used a code similar to this (non-relevant lines removed):
uses
  {…}, Aurelius.Drivers.SQLite,   Aurelius.SQL.SQLite;

  Connection := TSQLiteNativeConnectionAdapter.Create('test.db');
  SaveCustomer(Connection, 'Jack');
What if we want to change it completely and save our objects in a MySQL database, using dbExpress to connect to it? That can be done this way:
uses
  {…}, Aurelius.Drivers.dbExpress,   Aurelius.SQL.MySQL;

  Connection := TDBExpressConnectionAdapter.Create(SQLConnection1, 'MySQL', False);
  SaveCustomer(Connection, 'Joe');
Note that besides the code that retrieves an IDBConnection interface, all other code remains the same. And that is true for any database you want to connect to, using any component, because all the object manager needs is an IDBConnection interface.

To retrieve that interface, we used a component adapter (TDBExpressConnectionAdapter, declared in unit Aurelius.Drivers.dbExpress) that takes our current dbExpress connection component (a TSQLConnection named SQLConnection1) and retrieves the interface. The second parameter indicates which database we are connecting to (more specifically, which SQL dialect Aurelius needs to use to execute SQL statements). That dialect, 'MySQL', is available after you use the unit Aurelius.SQL.MySQL. Finally, the third parameter (False) indicates that when the IDBConnection interface is destroyed, the adapted component (SQLConnection1) should not be destroyed. Optionally you can set it to true, which can be useful if you are creating the component only to be used by IDBConnection, so that the component is destroyed when interface is destroyed.

Now that Embarcadero has purchased AnyDac library and it will probably be provided natively in Delphi, using it instead of dbExpress will be a matter of changing a couple of lines:
uses
  {…}, Aurelius.Drivers.AnyDac,   Aurelius.SQL.MySQL;

  Connection := TDBExpressConnectionAdapter.Create(ADConnection1, False);
  SaveCustomer(Connection, 'Phil');
You might be missing the second parameter indicating that we are connecting to a MySQL database. This is because the adapters are able to automatically identify the database being connected to (Both TSQLConnection and TADConnection components have a property DriverName which Aurelius uses to identify the database). So the second parameter is optional.

Another thing is worth noting is that with this approach, code is very abstract and flexible. Aurelius doesn’t have any connection parameters that you need to configure like server name, password, etc. Everything is configured in the same components you already use. Any database connection configuration, including advanced ones, provided by each database access components, is still available.

So, if you don’t know if you should use AnyDac or dbExpress, you can use both and change them as you want to. Not only those, but at the current version (1.8) Aurelius also supports ADO components, Direct Oracle Access, ElevateDB, NexusDB, Absolute Database, FIBPlus, IBObjects, IBX components, SQL-Direct, UniDac and of course the native SQLite adapter. Aurelius documentation also provides the unit names and the name of adapter classes in its topic about component adapters.

As for the supported databases, you can use not only SQLite and MySQL, but also Firebird, MS SQL Server, Interbase, Oracle, PostgreSQL, Absolute Database, DB2, ElevateDB, NexusDB and SQLite. The names of units and SQL dialects are available in the topic “SQL Dialects” in documentation.

To conclude, I would like to mention that not only those databases and components are supported, but they are also extensively tested in each Aurelius release, with almost all possible combinations (dbExpress connecting to SQL Server, AnyDac connecting to PostgreSQL, and so on). You can check in the documentation which minimum versions of were used for tests for each combination. So most of little problems here are there with field types, SQL syntax, among other common problems that we usually find when switching components and databases are already solved, making Aurelius code effectively database/component agnostic, not only in theory, but also in practice.

Bookmarks: 

Wagner Landgraf




This blog post has received 11 comments. Add a comment.



Wednesday, February 06, 2013

Crash Course TMS Aurelius – Getting Started

Even though TMS Aurelius provides extensive documentation, I sometimes receive requests to provide more examples, sample codes and explanations about how to accomplish some daily tasks. Thus, I will start a series of posts about how to use TMS Aurelius. Everything is already covered in the documentation, but here I will try not to provide complete, technical, “official” coverage of a feature, but plainly explain what it is for instead by giving real-world examples etc. - in summary, a different, hands-on way of showing things.

I will start right from the beginning. I want to show a small application using TMS Aurelius. Having an entity/a model class TCustomer, mapped as follows:
unit Customer;
interface
uses
  Aurelius.Mapping.Attributes;

type
  [Entity, Automapping]
  TCustomer = class
  private
    FId: integer;
    FName: string;
  public
    property Id: integer read FId write FId;
    property Name: string read FName write FName;
  end;

implementation
end.
This is a very small application that saves a customer instance derived from TCustomer in a SQLite database (I've removed try..finally blocks to simplify code):
program GettingStarted;

{$APPTYPE CONSOLE}

uses
  Aurelius.Drivers.Interfaces,
  Aurelius.Drivers.SQLite,
  Aurelius.Engine.DatabaseManager,
  Aurelius.Engine.ObjectManager,
  Aurelius.SQL.SQLite,
  Customer;

var
  Connection: IDBConnection;
  Manager: TObjectManager;
  Customer: TCustomer;
begin
  Connection := TSQLiteNativeConnectionAdapter.Create('test.db');
  Manager := TObjectManager.Create(Connection);
  Customer := TCustomer.Create;
  Customer.Name := 'First customer';
  Manager.Save(Customer);
  Manager.Free;
  WriteLn('Customer saved.');
  ReadLn;
end.
The purpose here is to show the very basics of how to start using Aurelius. What you need is:

1. A class to be persisted. This is your TCustomer class.

2. A mapping between the class and the database. This is accomplished by the [Entity] and [Automapping] attributes. In this case properties are mapped automatically to database columns, but you can set up a custom mapping if you want to. I will call object instances managed by Aurelius “entities”.

3. A connection to a database. This is the Connection variable, which implements an IDBConnection interface. In this case, we are connecting to a local SQLite database and need to make use of the TSQLiteNativeConnectionAdapter. I.e. we always need an adapter that connects the database world to the object world. In Windows you have to make sure sqlite3.dll is in a directory Windows can find. In Mac OS X and iOS, SQLite is already available.

4. An object manager to persist and manage your entities. The second line creates an object manager, which stores objects in the database specified by IDBConnection.

With all that, the code just instantiates the TCustomer object, fills its properties and saves it to the database. That is your first TMS Aurelius application!

As an additional note with regard to the code sample, if the file “test.db” does not exist, Aurelius will create it for you. However, you have to explicitly ask it to create the database structure for you, using the following code:
procedure CreateDatabase(Connection: IDBConnection);
var
  DBManager: TDatabaseManager;
begin
  DBManager := TDatabaseManager.Create(Connection);
  DBManager.BuildDatabase;
  DBManager.Free;
end;
This will create the file holding the database and the proper tables you need in database (in this case, table “Customer”).

Bookmarks: 

Wagner Landgraf




This blog post has received 8 comments. Add a comment.




Previous  |  Next  |  Index

Copyright © 1995 - 2013 TMS Software v3.5