What way of interaction with database is better?
lazarus / 16-Databases

----------------------------------------------------------------------
Mr.Madguy · 2017-03-23 11:45

I've never liked to develop DB applications, cuz I think, that this
process is more technical and less creative, so I've never studied
closely this part of programming. But I know theory, SQL and recently
I had to develop DB application. It's one of these apps, that
shouldn't provide direct access to database, i.e. user shouldn't even
know, that he interacts with it - like online shop or something like
that. This application uses TMySQLConnection. I fetch records from
database manually, have custom UI controls to display them (limiting
and filtering records included) and use pure SQL via TSQLQuery to do
it and also to add/remove data. And now I started to think, that I
invent bicycle. The whole point in having high level of abstraction
over database, like Delphi, FPC or Java - is to minimize code and
automate everything as much, as possible, and to be able to develop as
universal application, as possible. I tried searching info about this
topic in Internet, but all examples usually provide direct access to
database via some DB controls, like TDBGrid, and I don't need this.
And I think, that in a future I will need to develop some DB
applications. So, I have a question. If user shouldn't have direct
access to DB records via some DB controls, then what way of
interaction with database is better? Pure SQL or some higher object
oriented level of abstraction?

Currently my code is something like that.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TProcessorsQuery.Refresh;  var TempStr, ResultSQL:String;begin 
inherited Refresh;  ResultSQL := 'SELECT
processors.*,hardware_developers.name AS processor_developer_name ' + 
  'FROM processors LEFT OUTER JOIN hardware_developers ON ' +   
'hardware_developers.id=processor_developer_id';  TempStr :=
PrepareFiltersInt(    ['processor_developer_id'],   
[Filter.DeveloperId]  );  TempStr := PrepareFiltersStr(    ['model'], 
  [Filter.Model],    TempStr  );  TempStr := PrepareFiltersInt(   
['cores', 'frequency'],    [Filter.Cores, Filter.Frequency],   
TempStr  );  if TempStr <> '' then begin    ResultSQL := ResultSQL + '
WHERE ' + TempStr;  end;  ResultSQL := ResultSQL + ' ORDER BY
processor_developer_name,model';  TempStr := PrepareLimit;  if TempStr
<> '' then begin    ResultSQL := ResultSQL + ' ' + TempStr;  end;  SQL
:= ResultSQL;  ApplyFiltersInt(    ['processor_developer_id'],   
[Filter.DeveloperId]  );  ApplyFiltersStr(    ['model'],   
[Filter.Model]  );  ApplyFiltersInt(    ['cores', 'frequency'],   
[Filter.Cores, Filter.Frequency]  );  ApplyLimit;  Open; 
Query.First;  while not Query.EOF do begin    Items.AddObject(     
ProcessorToString(        DataToProcessor(         
FieldIntNull['processor_developer_id'],         
FieldStrNull['processor_developer_name'],          FieldStr['model'], 
        FieldInt['cores'],          FieldInt['frequency']        )   
 )    ,      TObject(Pointer(FieldInt['id']))    );    Query.Next; 
end;  Close;end; 
All properties, like "FieldInt" - are only about accessing standard
TSQLQuery properties:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TQuery.GetFieldBool(const AName:String):TSQLBoolean;  var
Field:TField;begin  Field := Query.FieldByName(AName);  if
Field.IsNull then begin    Result := blUnspecified;  end  else begin 
 if Field.AsBoolean then begin      Result := blTrue;    end    else
begin      Result := blFalse;    end;  end;end; procedure
TQuery.SetFieldBool(const AName:String;AParam:TSQLBoolean);  var
Field:TField;begin  Field := Query.FieldByName(AName);  case AParam
of    blUnspecified:Field.Clear;    blFalse:Field.AsBoolean := False; 
  blTrue:Field.AsBoolean := True;  end;end;

----------------------------------------------------------------------
Leledumbo · 2017-03-23 17:04

ORM/OPF is what you're looking for. Your code most of the time will
look like normal object creation/access/destruction. There are several
solutions available already, each with its own plus and minus. Just to
name a few:
tiOPFdOPFGreyhoundAt least, you can hold on to the concept of model as
abstraction layer for data access.

----------------------------------------------------------------------
Mr.Madguy · 2017-03-23 18:43

Quote from: Leledumbo on March 23, 2017, 06:04:43 pmORM/OPF is what
you're looking for. Your code most of the time will look like normal
object creation/access/destruction. There are several solutions
available already, each with its own plus and minus. Just to name a
few:
tiOPFdOPFGreyhoundAt least, you can hold on to the concept of model as
abstraction layer for data access.
As you understand, I just want to get rid of direct SQL queries and
use more abstracted access to database, such as at least "add
field"/"remove field"/"fetch fields according to limits, filters and
relations between them". And I actually wanted to ask, whether
standard Delphi/FPC tools are suitable for this task or not? What
solution do FPC DB programmers usually use to perform direct access to
database? I don't like standard tools, cuz they seem to be tied to DB
UI controls and that complicates direct access a little bit, cuz lots
of stuff happen behind the scene. Your solution seems to be a little
bit complex for my task. I will review it, but I don't think, that I
need some complex and heavy framework for my tasks. I just need some
safe standard automation solution to avoid stuff like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i       SQL
:= 'SELECT computers.*,' +        //Type name       
'computer_types.name AS computer_type_name,' +        //Hardware
developer name        'computer_hardware_developers.name AS
computer_developer_name,' +        //Kind name       
'computer_kinds.name AS computer_kind_name,' +        //Processor
name        'processors.processor_developer_id,' +       
'processors.model AS processor_model,' +        'processors.cores AS
processor_cores,' +        'processors.frequency AS
processor_frequency,' +        //Processor hardware developer name   
   'processor_hardware_developers.name AS processor_developer_name,'
+        //Mointor account name        'monitor_accounts.monitor_id AS
monitor_account_monitor_id,' +        'monitor_accounts.account_number
AS monitor_account_number,' +        //Monitor name       
'monitors.monitor_developer_id,' +        'monitors.monitor_type_id,'
+        'monitors.model AS monitor_model,' +       
'monitors.diagonal AS monitor_diagonal,' +       
'monitors.wide_screen AS monitor_wide_screen,' +        //Monitor
hardware developer name        'monitor_hardware_developers.name AS
monitor_developer_name,' +        //Monitor type name       
'monitor_types.name AS monitor_type_name,' +        //Floppy drive
type name        'floppy_drive_types.name AS floppy_drive_type_name,'
+        //Optical drive type name        'optical_drive_types.name AS
optical_drive_type_name,' +        //Operation system license name   
   'os_licenses.os_id,' +        'os_licenses.license_number AS
os_license_number,' +        'os_licenses.purchase_date AS
os_purchase_date,' +        //Operation system name       
'operation_systems.os_developer_id,' +        'operation_systems.name
AS os_name,' +        'operation_systems.version AS os_version,' +   
   'operation_systems.subversion AS os_subversion,' +       
'operation_systems.bits AS os_bits,' +        //Operation system
software developer name        'os_software_developers.name AS
os_developer_name,' +        //Filter name       
'filter_programs.program_developer_id AS
computer_filter_developer_id,' +        'filter_programs.name AS
computer_filter_name,' +        'filter_programs.version AS
computer_filter_version,' +        //Filter software developer name  
    'filter_software_developers.name AS
computer_filter_developer_name,' +        //Filter type name       
'filter_types.name AS computer_filter_type_name,' +        //Projector
type name        'projector_types.name AS
computer_projector_type_name,' +        //Program count       
'(SELECT COUNT(*) FROM computer_program_licenses ' +        'WHERE
computer_program_licenses.program_computer_id=computers.id) ' +      
'AS computer_program_count,' +        //Device count        '(SELECT
COUNT(*) FROM computer_device_accounts ' +        'WHERE
computer_device_accounts.device_computer_id=computers.id) ' +       
'AS computer_device_count ' +        //From        'FROM computers '
+        //Type name        'LEFT OUTER JOIN computer_types ON
computer_types.id=computer_type ' +        //Hardware developer name 
     'LEFT OUTER JOIN hardware_developers AS
computer_hardware_developers ' +        'ON
computer_hardware_developers.id=computer_developer_id ' +       
//Kind name        'LEFT OUTER JOIN computer_kinds ON
computer_kinds.id=computer_kind ' +        //Processor name       
'LEFT OUTER JOIN processors ON processors.id=computer_processor_id '
+        //Processor hardware developer name        'LEFT OUTER JOIN
hardware_developers AS processor_hardware_developers ' +        'ON
processor_hardware_developers.id=processors.processor_developer_id '
+        //Monitor account name        'LEFT OUTER JOIN
monitor_accounts ' +        'ON
monitor_accounts.id=computer_monitor_account_id ' +        //Monitor
name        'LEFT OUTER JOIN monitors ' +        'ON
monitors.id=monitor_accounts.monitor_id ' +        //Monitor hardware
developer name        'LEFT OUTER JOIN hardware_developers AS
monitor_hardware_developers ' +        'ON
monitor_hardware_developers.id=monitors.monitor_developer_id ' +     
 //Monitor type name        'LEFT OUTER JOIN monitor_types ' +       
'ON monitor_types.id=monitors.monitor_type_id ' +        //Floppy
drive type name        'LEFT OUTER JOIN floppy_drive_types ' +       
'ON floppy_drive_types.id=computer_floppy_drive_type_id ' +       
//Optical drive type name        'LEFT OUTER JOIN optical_drive_types
' +        'ON optical_drive_types.id=computer_optical_drive_type_id '
+        //Operation system license name        'LEFT OUTER JOIN
os_licenses ' +        'ON os_licenses.id=computer_os_license_id ' + 
     //Operation system name        'LEFT OUTER JOIN operation_systems
' +        'ON operation_systems.id=os_licenses.os_id ' +       
//Operation system software developer name        'LEFT OUTER JOIN
software_developers AS os_software_developers ' +        'ON
os_software_developers.id=operation_systems.os_developer_id ' +      
//Filter name        'LEFT OUTER JOIN programs AS filter_programs ' + 
      'ON filter_programs.id=computer_filter_id ' +        //Filter
software developer name        'LEFT OUTER JOIN software_developers AS
filter_software_developers ' +        'ON
filter_software_developers.id=filter_programs.program_developer_id '
+        //Filter type name        'LEFT OUTER JOIN filter_types ON
filter_types.id=computer_filter_type_id ' +        //Projector type
name        'LEFT OUTER JOIN projector_types ON
projector_types.id=computer_projector_type_id ' +        //Where     
 'WHERE computers.id=:id';      ParamInt['id'] := Id;      Open;

----------------------------------------------------------------------
DonAlfredo · 2017-03-23 19:03

Not wanting to repeat myself, but "the mORMot" ?
https://synopse.info/fossil/wiki?name=SQLite3+Framework
https://github.com/synopse/mORMot/tree/master/SQLite3/Samples
https://synopse.info/forum/index.php

Persistence without a single line of SQL.
(AFAIK, the other ORM's are also capable of doing this)


Disclaimer: I like the mORMot !

----------------------------------------------------------------------
hnb · 2017-03-23 22:58

Quote from: DonAlfredo on March 23, 2017, 08:03:45 pmNot wanting to
repeat myself, but "the mORMot" ?
...
Disclaimer: I like the mORMot !

+1

mORMot is abnormal ORM. Is very fast.

----------------------------------------------------------------------
Leledumbo · 2017-03-24 01:59

Quote from: Mr.Madguy on March 23, 2017, 07:43:26 pmAnd I actually
wanted to ask, whether standard Delphi/FPC tools are suitable for this
task or not?
Nope, it's designed around DBAware controls indeed.
Quote from: Mr.Madguy on March 23, 2017, 07:43:26 pmWhat solution do
FPC DB programmers usually use to perform direct access to database?
The "standard tools" is modular, you can just use one of the
TXXXConnection or TdSQLdbConnector with specified connector name and
directly query from it, without the need of any single DBAware
control. Otherwise, if you like headaches, just directly use
corresponding low level units for your choice of dbms, look in fcl-db
folder for names.
Quote from: Mr.Madguy on March 23, 2017, 07:43:26 pmYour solution
seems to be a little bit complex for my task. I will review it, but I
don't think, that I need some complex and heavy framework for my
tasks.
No, it is not. In fact, it is exactly what you're looking for since
ORM will generate queries for you behind the scene. What you code at
application level is simple OOP (well, some ORM requires you to also
map table name and columns manually, but others are smart enough to do
so automatically from published properties). The only time you have to
deal with database thing is when setting the connection properties,
which is usually shared among the instances (but not always, some ORM
allow multiple non-shared connections) so you just need to do it once.
In short, learn the concept first. It's complex behind the scene,
which you shouldn't touch, but very simple in usage.

----------------------------------------------------------------------
Mr.Madguy · 2017-03-24 07:07

Ok, I'll check it. At least I have a good news - I haven't invented a
bicycle.