Writing an ORM for Delphi

Hello!
Today I will tell you about my experience writing an ORM for Delphi using RTTI under the influence of Doctrine and Java EE practices .

What for?

Recently, an old project on Delphi7 came under my authority, which is actively working with the database under Interbase 2009. The code in this project was pleasing, but exactly until it came to the interaction with the database itself. Fetching data, updating, making new entries, deleting - all this took up a lot of lines in the application logic, which made it quite difficult to figure out the code (salvation is in a conscientious developer who answered my silly questions around the clock). The project was handed over to me in order to eliminate old troubles and add a new module to it, the task of which is to cover new database tables.

I like the MVC approach and really wanted to share the logic code with the model code. And even for the sake of cleanliness - I did not want to rewrite all get / set methods for a new table. A couple of years ago I got to know the concept of ORM and I liked it. I liked the principle and I was delighted to apply it in my work.
At that very moment, I rushed to look in Delphi7 for at least something similar to Doctrine or maybe generators of 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 with you, I refused a ready-made solution, since I need very limited functionality, and there’s no need to drag along the entire contents of DORM or tiOPF. With the realization of what I want, with the understanding of all the shortcomings, I took this slippery slope and, it seems, reached ...

Reflections

The absence of at least some similarity in ORM is a pain in the neck. I’m talking about this - in Java, out of the box, you can create a set of entity classes and facades for working with 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, to clear the main code of the application logic from query texts and parsing the results of their execution. The same things are used in all popular PHP frameworks, in Qt (if memory serves me well) in one form or another.

What was the difficulty of 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 fields of the tables and the relationships between them (by foreign keys), clarify whether all the relationships were correctly understood and generate classes from the data collected. By generation, I mean creating 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 from this information declare the necessary information, generate a published section, append the necessary setters and getters ... In general, the task is time-consuming, but realizable.
After generating the entity classes, the IDE could start generating facade classes (or, as I call them, Adapters). The adapter is a layer between the programmer and the database and its main task is to be able to receive an entity corresponding to a certain key, save changes in it, delete it. In general, the essence of the Adapter is to present to the developer methods for working with the database, the results of which will be presented in the form of objects of the entities corresponding to them.

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

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

Process

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

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

My limitations:

1. No PDO - development for one database - Interbase
2. Delphi7 still has an older version of RTTI. (Rad 2010 RTTI has been greatly improved). You can get only published fields
3. Relations and getting entities by relationships (due to some internal reasons) will not be realized.

0. Abstract class TUEntity - parent of all Entity

Must inherit from TPersistent, otherwise we will not be able to fully apply RTTI. In it, we also regulate the interface of entities. Adapter in the course of its work will ask the entity the name of the table to which it corresponds, provide the name of the key field by which the search will take place, the value of this field, as well as a method for the string representation of the entity (for Logs, for example).

1. Creating an object by its name

It was already indicated 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 TUAdapter.Create () constructor on the first line.

The creation method itself looks like this. Why am I not passing an entity name with an argument? Because it’s in the context of my task, I don’t see the point of doing this, since objects are additionally created during the course of work, and the name of the entity always remains the same - passed when creating the Adapter

2. Getting class fields

I think this is a question that is often asked by developers under 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. Actually, this is very good, because it’s very convenient to use properties in our task.

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

To do this, you can use the GetPropValue method. I will dwell on the PreferStrings parameter - it affects how the result of fields of the tkEnumeration and tkSet type will be returned. If it stands as True, then enum will return from tkEnumeration, and SetProp will return from tkSet.

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

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

I think that citing the whole code is bad form (and its place at the end of the article). And here I’ll just give a part, on the example of the formation of a request for a selection of all data.
For data sampling, a read transaction is generated, a request is created. We bind the query and transaction, then run them and get all the values ​​in TIbSQL. Using TIbSQL.EoF and TIbSQL, Next you can iterate over all the records, which we do - alternately creating a new entity, put it in an array and fill its fields.

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

My aggregation of what I read looks like the following sequence of actions:

1. Run bdeAdmin.exe from the folder Borland Shared \ BDE \
2. In Configuration -> System -> Init, select the default driver Paradox 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, remaining on the changed elements in the main menu of the Object by clicking Apply.

This sequence of actions helps to avoid problems with requests for Update or Insert. (and with Select, there are no problems with the Cyrillic alphabet).
In some cases, it also helps instead of:
UPDATE tablename SET field = 'июнь';
writing:
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 throw an exception.

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

In other matters, after you complete the above 4 steps, you do not need to use _win1251 and you are free to make the request. I, without realizing it, chose a difficult path and decided to independently form a request. I didn’t take into account that the parameterization would take on some of the burdens with filtering the transmitted parameters. It’s not clear what I mean?

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

results

In general, we have developed requirements for Enitity classes:

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

So, let's say we have the following database.

We create two Entity classes TUser and TPost.

And use in the code together 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;

conclusions

I would like to focus on the speed of code using RTTI. Experience suggests that frequent use of 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 the result is some kind of ORM with little functionality, but honestly solving the tasks assigned to it.

Project on BitBucket .

PS
Let me remind you that a reader who has a predisposition to erupt negative thoughts towards Delphi is not required to tell everyone about this. So guys, keep yourself in control.
Sorry, I'm actually calling MessageBox on error, instead of throwing an Exception. But I will correct myself, I promise.

UPD:
No more MessageBox in the code.