quarta-feira, 7 de março de 2018

DROP Wiki (English) #2 - Connecting to DBMSs

Você pode ler este post em português clicando aqui.

Continuing the DROP Wiki Series, the current topic is the connection establishment with a data source (usually a DBMS) and the choice of the best class to perform the task.

Further on, we will see that for the DROP ORM to work, a few prerequisites are necessary. Of them all, the most important is to create an object of TAqDBConnection, and, trough it, to establish a connection with the data source.

The TAqDBConnection class (in unit AqDrop.DB.Connection) is almost entirely abstract. This class goal is to abstract the minimum required to execute commands in the supported DBMSs. Obviously, this abstraction exists to provide the framework extension to different data access engines, being that we already have implementations for DBX and FireDAC.

Basically, the interfaces provided by TAqDBConnection are:
  - Transaction manipulation routines;
  - Command preparing (Prepare e Unprepare);
  - Cursor opening routines;
  - Command execution routines (usually DMLs);

The routines of transaction manipulation, command preparation and execution are not so different from the routines provided by any other data access engine (including third party components). The most significant difference is that of the cursor opening routines, which were built to provide interfaces similar to the readers  in DBX Framework, providing a code that is more suitable to O.O. characteristics (summing up: no DataSets ;-)

Aiming to split the specific code to support DBX and FireDAC, two classes were implemented and, as VCL style, both of them have the prefix 'Custom' on their names (TAqDBXCustomConnection e TAqFDCustomConnection), doing the heavy work so as to achieve their respective goals. Two direct inheritances, omitting the 'Custom' from their names (TAqDBXConnection e TAqFDConnection), have only the job of publishing the parameters provided by DROP and the mapped engines, in order to allow the complete configuration to access the desired DBMS.

Our first example will use Interbase as the DBMS and DBX as the internal engine, so, we will use the generic class TAqDBXConnection, as shown in the example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
function TfrmDW0002.GetConnection
  (const pDatabasePath: string): TAqDBConnection;
var
  lConnection: TAqDBXConnection;
begin
  lConnection := TAqDBXConnection.Create;

  try
    lConnection.DriverName := 'InterBase';
    lConnection.VendorLib := 'GDS32.DLL';
    lConnection.LibraryName := 'dbxint.dll';
    lConnection.GetDriverFunc := 'getSQLDriverINTERBASE';
    lConnection.Properties[TDBXPropertyNames.Database] := pDatabasePath;
    lConnection.Properties[TDBXPropertyNames.Username] := 'SYSDBA';
    lConnection.Properties[TDBXPropertyNames.Password] := 'masterkey';
    lConnection.DBXAdapter := TAqDBXIBAdapter.Create;

    lConnection.Connect;
  except
    lConnection.Free;
    raise;
  end;

  Result := lConnection;
end;

The above example shows that to configuring a DROP connection or a TSQLConnection, for example, are similar tasks. However, a clear difference between the DROP connection and any other connection component provided by Delphi, is in the building of an adapter, which is necessary for a complete operation of the DROP features. The adapters tell DROP how to solve some tasks, or else, how to read some DBMS data types.

As the DROP aims to work more as a library  and less as a component, the above task can be simplified through the use of other set of classes, which easily wrap some distinctions of the supported DBMSs. Bellow we'll see the creation of a connection with the same database, however, using another class, which wraps DBX and the Interbase particularities.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
function TfrmDW0002.GetConnectionBySpecificClass
  (const pDatabasePath: string): TAqDBConnection;
var
  lConnection: TAqDBXIBConnection;
begin
  lConnection := TAqDBXIBConnection.Create;

  try
    lConnection.DataBase := pDatabasePath;
    lConnection.UserName := 'SYSDBA';
    lConnection.Password := 'masterkey';

    lConnection.Connect;
  except
    lConnection.Free;
    raise;
  end;

  Result := lConnection;
end;

In the above example we can notice that the class TAqDBXIBConnection takes responsibility of configuring the properties as standards defines for Interbase, and exposes properties that are specific to IB. These properties allow us to statically setup some data like the GDB path, user and password, unlike the first example, where this information was dynamically provided.

Well, this was the example using the Interbase, and examples with other DBMSs simply mean a little bit of the same, talking specifically of the code. However, it is important at least to provide the list of specialized classes already available in DROP, each of them setting up the connection as the known standards of the supported DBMSs, and providing their particular properties. Here goes the complete list of classes, grouped by the data access engine:

DBX:
 - TAqDBXIBConnection;
 - TAqDBXMSSQLConnection;
 - TAqDBXMySQLConnection;
 - TAqDBXFBConnection;
 - TAqDBXOraConnection;
 - TAqDBXSQLiteConnection;

FD:
 - TAqFDIBConnection;
 - TAqFDMSSQLConnection;
 - TAqFDMySQLConnection;
 - TAqFDFBConnection;
 - TAqFDOraConnection;
 - TAqFDSQLiteConnection;
 - TAqFDPGConnection;

You may notice that FireDAC has an additional variation, which is PostgreSQL.

And, for the time being, that will be all. As a follow-up, we will have the episode #3, which will show how to use these objects, and in future episodes, how to use them inside the ORM aspect.

Until the next post!

Nenhum comentário:

Postar um comentário

Você está fazendo isso errado! Tópico #2

 Fala galera! Como estão todos? Sejam bem-vindos ao primeiro post de 2021. Continuando o tema "Você está fazendo isso errado", va...