Writing ORM for Delphi

Hello!
Today I will tell you about my experience of writing ORM for Delphi using RTTI influenced by the practices of working with Doctrine and Java EE.

What for?

The old project on Delphi7 has recently come under my power, in which the active work with the database under Interbase 2009 is underway. The code in this project was pleasing, but exactly until the discussion turned to the interaction with the database itself. Sampling data, updating, adding new records, deleting - all this took quite a few lines in the logic of the application, which made it sometimes difficult to understand the code (salvation in a bona fide developer who answered my stupid questions around the clock). The project was transferred to my hands in order to eliminate old misfortunes and add a new module to it, the task of which is to cover the new database tables.

I like the MVC approach and really wanted to share the logic code with the model code. And even if it’s for cleanliness, I didn’t want to rewrite all get / set methods for each new table with a new one. A couple of years ago I became acquainted with the concept of ORM and I liked it. I liked the principle and I was delighted to apply it in my work.
At the same time I rushed to look in Delphi7 at least something similar to Doctrine, or maybe the generators of the Entity / Facade classes for tables ... Neither one nor the other. But in the search results there were several ready-made solutions. For example DORM . In general, a great thing and, in fact, what you need!

But, I don’t know if it happens to you, I refused the ready-made solution, since I need very limited functionality, and I don’t need to drag all the contents of DORM or tiOPF. With the knowledge of what I want, with the understanding of all the flaws, I began this slippery slope and, it seems, came to ...
')

Reflections

The absence of at least some similarity on the ORM is a headache. What I am talking about is that in Java out of the box it is possible to create a set of entity classes and facades to work with the created entities using a ready-made database. The purpose of these classes is to provide the developer with a ready-made tool for interacting with some database, clearing the main application logic code from the query texts and parsing the results of their execution. These same things are used in all popular PHP frameworks, in Qt (if memory serves me) in one form or another.

What was the difficulty in implementing a high-quality library for object mapping and including it in the IDE? The task is to connect to the database, ask the user what tables he needs in the application, read the table fields and the relationships between them (on foreign keys), clarify whether all the links were correctly understood and generate classes from the collected data. By generation, I mean the creation of classes of entities whose task is to be the repository of a single record from some table. Knowing the name of the table, you can find out all its fields, field types and use this information to announce the necessary information, generate the published section, add the necessary setters and getters ... In general, the task is time-consuming, but realizable.
After the generation of entity classes, IDE could start generating facade classes (or, as I call them, Adapters). An adapter is an interlayer between a programmer and a database and its main task is to be able to receive an entity corresponding to a certain key, save changes to it, delete it. In general, the essence of the Adapter is to provide the developer with methods for working with the database, the results of which will be represented as objects of their corresponding entities.

I didn’t like this aspect of Delphi development. My experience with him is already relatively large, I see many advantages in this difficult task, but the more I learn about new languages ​​and environments, I feel that Delphi is a suitable tool, but does not reach the required level, when it’s difficult dreary routine I do not have to spend so much time.

I am ready to pass on the generation of entities on the shoulders of heroes. Perhaps someone will even be able to inject this into the IDE itself as Castalia. But I do not see any point in writing separately for each entity the methods of sampling, updating, deleting. I do not want. I want a class to which I will pass the name of the entity, which will call the findAll method and get all the records from the desired table. Or write find (5) and get a record with the numeric key 5.

Process

We develop the TUAdapter class.
What the Adapter should be able to do as a result:

1. Creates an object by class name
2. Able to receive class fields
3. Can get field value by field name
4. Able to sample all data
5. Able to get the entity by key
6. Is able to update the data of the entity in the database
7. Able to remove the entity from the database
8. Able to add a new entity from the database.

My limitations:

1. No PDO - development for a single database - Interbase
2. Delphi7 is still the old version of RTTI. (in Rad 2010 RTTI has been greatly improved). Only published fields can be obtained.
3. Relationships and retrieval of entities by connections (for some internal reasons) will not be implemented.

0. Abstract class TUEntity is the parent of all Entity

Must be inherited from TPersistent, otherwise we will not be able to fully apply RTTI. In it we regulate the interface of entities. In the course of its work, the Adapter will ask the entity the name of the table that it corresponds to, provide the name of the key field that will be searched, the value of this field, as well as the method for string representation of the entity (for Logs, for example).

1. Creating an object by its name

It has already been stated above that entities are inherited from the TPersistent class, but in order for an entity to be created by name, care must be taken to register the class of all necessary entities. I do this in the constructor TUAdapter.Create () in the first line.

The very same method of creation looks like this. Why am I not passing the name of an entity as an argument? Because it is in the context of my task, I do not see the point of doing this, because in the course of the work objects are additionally created, and the name of the entity always remains the same - transferred when the Adapter is created

2. Getting class fields

I think this is a question that developers rarely ask for Delphi. The main “feature” is that we cannot get all the fields as we would like, but only the property fields from the published section. In fact, this is very good, because the properties in our task are very convenient to use.

3. Getting the value of the field object by the field name

To do this, you can use the GetPropValue method. I’ll dwell on the PreferStrings parameter - it influences how the result of fields like tkEnumeration and tkSet will be returned. If it is set to True, then enum will return from tkEnumeration, and SetProp will return from tkSet.

 (Instance: TObject; const PropName: string; PreferStrings: Boolean): Variant;. 

4,5,6 ... Working with the database

I think giving the whole code is a bad form (and its place at the end of the article). And here I will only give a part, on the example of forming a request to select all data.
To fetch the data, a read transaction is formed, a request is created. We connect the query and the transaction, after which we run them and get all the values ​​in TIbSQL. Using TIbSQL.EoF and TIbSQL, Next you can loop through all the records, which we are doing - by creating a new entity in turn, we put it into an array and fill its fields.

In other matters, I will not forget to mention a few difficulties. First, the encoding. If your database is created with WIN1251 encoding and Collation is installed win1251 and you will have to work with this DB from Delphi - you cannot simply take and add an entry with Cyrillic characters. In this case, read the information on the link IBase.ru Rus FAQ . Then they will teach you and poke your finger in all the pitfalls.

My read aggregation looks like the following sequence of actions:

1. Run bdeAdmin.exe from the Borland Shared \ BDE \ folder
2. In Configuration -> System -> Init, select the default Paradox driver and Langdriver = Pdox Ansi Cyrrilic
3. In Configuration -> Drivers -> Native, put Langdriver = Pdox Ansi Cyrrilic in the drivers: Microsfot Paradox Driver, Data Direct ODBC to Interbase, Microsoft dBase Driver.
4. Save the changes, staying on the changed items in the Object main menu by pressing Apply.

Such a sequence of actions helps to have no problems when querying for Update or Insert. (and at Select there are no problems with Cyrillic).
In some cases, it also helps instead of:
UPDATE tablename SET field = '';
write:
UPDATE tablename SET field = _win1251'';
But this will not work if you use a query with parameters, since TIbSQL is not familiar with the _win1251 function.
For example, such a code will not work and will trigger an exception.

 IbSQL.SQL.Add("UPDATE tablename SET field = _win1251 :field"); IbSQL.Prepare(); // <- Exception IbSQL.Params.byName('field').asString := ''; 

In other matters, after you have done the above 4 steps - you do not need to use _win1251 and you are free to make a request. Without realizing it, I chose a complex path and decided to independently form a request. I did not take into account that the parameterization would take on some of the burden of filtering the transmitted parameters. Do not understand what I mean?

I encountered a problem when there is a quotation mark or a line break in the text field value. And I had to write a method to replace these characters with valid ones:

results

In general, we have developed requirements for the Enitity classes:

1. describe private fields
2. describe the fields corresponding to the columns of the table as a property in the published section
3. property names must match the corresponding column names
4. if necessary, implement Get / Set methods for fields (for Boolean, TDateTime, for Blob fields)

So, let's say we have the following DB

Create two Entity classes TUser and TPost.

And the use in the code paired with the adapter will look like this:

 var Adapter : TUAdapter; users: TUsersArray; i: integer; begin Adapter := TUAdapter.Create(db, 'User'); try users:= TUsersArray(Adapter.FindAll()); for i:=0 to Length(users) -1 do begin Grid.Cells[0, i+1] := VarToStr(users[i].id); Grid.Cells[1, i+1] := VarToStr(users[i].name); Grid.Cells[2, i+1] := VarToStr(users[i].email); Grid.Cells[3, i+1] := VarToStr(users[i].password); SetRateStars(i, VarToStr(users[i].rate)); Grid.Cells[5, i+1] := VarToStr(users[i].last_login); end; finally Adapter.Destroy; end; end; 

findings

I would like to focus on code performance using RTTI. Experience suggests that frequent access to RTTI methods will slow down the application, but in my reality the speed of the developed class is enough. I believe that the goal has been achieved and as a result we have got some semblance of an ORM with a small functionality, but honestly solving the tasks assigned to it.

Project on BitBucket .

PS
Let me remind you that the reader who has a predisposition to the eruption of negative thoughts in the direction of Delphi is not obliged to tell everyone about it. So guys, keep yourself in hand.
Sorry, I actually call MessageBox on an error, instead of throwing an Exception. But I will correct, I promise.

UPD:
No more MessageBox in code.