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;
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;
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;
Class: TPerson; Name: John Person Class: TEmployee; Name: James Employee Class: TEmployee; Name: James Employee; Salary: 1999.99 nil
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);
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
[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;
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));
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;
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;
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;
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);
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;
| 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;
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;
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;
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)
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)
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;
uses
{…}, Aurelius.Drivers.SQLite, Aurelius.SQL.SQLite;
Connection := TSQLiteNativeConnectionAdapter.Create('test.db');
SaveCustomer(Connection, 'Jack');
uses
{…}, Aurelius.Drivers.dbExpress, Aurelius.SQL.MySQL;
Connection := TDBExpressConnectionAdapter.Create(SQLConnection1, 'MySQL', False);
SaveCustomer(Connection, 'Joe');
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');
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.
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.
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;
| Bookmarks: |
Wagner Landgraf
This blog post has received 8 comments. Add a comment.
Previous | Next | Index




