Recent

Author Topic: What way of interaction with database is better?  (Read 4166 times)

Mr.Madguy

  • Hero Member
  • *****
  • Posts: 844
What way of interaction with database is better?
« on: March 23, 2017, 12:45:55 pm »
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][+][-]
  1. procedure TProcessorsQuery.Refresh;
  2.   var TempStr, ResultSQL:String;
  3. begin
  4.   inherited Refresh;
  5.   ResultSQL := 'SELECT processors.*,hardware_developers.name AS processor_developer_name ' +
  6.     'FROM processors LEFT OUTER JOIN hardware_developers ON ' +
  7.     'hardware_developers.id=processor_developer_id';
  8.   TempStr := PrepareFiltersInt(
  9.     ['processor_developer_id'],
  10.     [Filter.DeveloperId]
  11.   );
  12.   TempStr := PrepareFiltersStr(
  13.     ['model'],
  14.     [Filter.Model],
  15.     TempStr
  16.   );
  17.   TempStr := PrepareFiltersInt(
  18.     ['cores', 'frequency'],
  19.     [Filter.Cores, Filter.Frequency],
  20.     TempStr
  21.   );
  22.   if TempStr <> '' then begin
  23.     ResultSQL := ResultSQL + ' WHERE ' + TempStr;
  24.   end;
  25.   ResultSQL := ResultSQL + ' ORDER BY processor_developer_name,model';
  26.   TempStr := PrepareLimit;
  27.   if TempStr <> '' then begin
  28.     ResultSQL := ResultSQL + ' ' + TempStr;
  29.   end;
  30.   SQL := ResultSQL;
  31.   ApplyFiltersInt(
  32.     ['processor_developer_id'],
  33.     [Filter.DeveloperId]
  34.   );
  35.   ApplyFiltersStr(
  36.     ['model'],
  37.     [Filter.Model]
  38.   );
  39.   ApplyFiltersInt(
  40.     ['cores', 'frequency'],
  41.     [Filter.Cores, Filter.Frequency]
  42.   );
  43.   ApplyLimit;
  44.   Open;
  45.   Query.First;
  46.   while not Query.EOF do begin
  47.     Items.AddObject(
  48.       ProcessorToString(
  49.         DataToProcessor(
  50.           FieldIntNull['processor_developer_id'],
  51.           FieldStrNull['processor_developer_name'],
  52.           FieldStr['model'],
  53.           FieldInt['cores'],
  54.           FieldInt['frequency']
  55.         )
  56.       )
  57.     ,
  58.       TObject(Pointer(FieldInt['id']))
  59.     );
  60.     Query.Next;
  61.   end;
  62.   Close;
  63. end;
  64.  

All properties, like "FieldInt" - are only about accessing standard TSQLQuery properties:
Code: Pascal  [Select][+][-]
  1. function TQuery.GetFieldBool(const AName:String):TSQLBoolean;
  2.   var Field:TField;
  3. begin
  4.   Field := Query.FieldByName(AName);
  5.   if Field.IsNull then begin
  6.     Result := blUnspecified;
  7.   end
  8.   else begin
  9.     if Field.AsBoolean then begin
  10.       Result := blTrue;
  11.     end
  12.     else begin
  13.       Result := blFalse;
  14.     end;
  15.   end;
  16. end;
  17.  
  18. procedure TQuery.SetFieldBool(const AName:String;AParam:TSQLBoolean);
  19.   var Field:TField;
  20. begin
  21.   Field := Query.FieldByName(AName);
  22.   case AParam of
  23.     blUnspecified:Field.Clear;
  24.     blFalse:Field.AsBoolean := False;
  25.     blTrue:Field.AsBoolean := True;
  26.   end;
  27. end;
  28.  
« Last Edit: March 23, 2017, 01:07:42 pm by Mr.Madguy »
Is it healthy for project not to have regular stable releases?
Just for fun: Code::Blocks, GCC 13 and DOS - is it possible?

Leledumbo

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: What way of interaction with database is better?
« Reply #1 on: March 23, 2017, 06:04:43 pm »
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:
  • tiOPF
  • dOPF
  • Greyhound
At least, you can hold on to the concept of model as abstraction layer for data access.

Mr.Madguy

  • Hero Member
  • *****
  • Posts: 844
Re: What way of interaction with database is better?
« Reply #2 on: March 23, 2017, 07:43:26 pm »
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:
  • tiOPF
  • dOPF
  • Greyhound
At 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][+][-]
  1.       SQL := 'SELECT computers.*,' +
  2.         //Type name
  3.         'computer_types.name AS computer_type_name,' +
  4.         //Hardware developer name
  5.         'computer_hardware_developers.name AS computer_developer_name,' +
  6.         //Kind name
  7.         'computer_kinds.name AS computer_kind_name,' +
  8.         //Processor name
  9.         'processors.processor_developer_id,' +
  10.         'processors.model AS processor_model,' +
  11.         'processors.cores AS processor_cores,' +
  12.         'processors.frequency AS processor_frequency,' +
  13.         //Processor hardware developer name
  14.         'processor_hardware_developers.name AS processor_developer_name,' +
  15.         //Mointor account name
  16.         'monitor_accounts.monitor_id AS monitor_account_monitor_id,' +
  17.         'monitor_accounts.account_number AS monitor_account_number,' +
  18.         //Monitor name
  19.         'monitors.monitor_developer_id,' +
  20.         'monitors.monitor_type_id,' +
  21.         'monitors.model AS monitor_model,' +
  22.         'monitors.diagonal AS monitor_diagonal,' +
  23.         'monitors.wide_screen AS monitor_wide_screen,' +
  24.         //Monitor hardware developer name
  25.         'monitor_hardware_developers.name AS monitor_developer_name,' +
  26.         //Monitor type name
  27.         'monitor_types.name AS monitor_type_name,' +
  28.         //Floppy drive type name
  29.         'floppy_drive_types.name AS floppy_drive_type_name,' +
  30.         //Optical drive type name
  31.         'optical_drive_types.name AS optical_drive_type_name,' +
  32.         //Operation system license name
  33.         'os_licenses.os_id,' +
  34.         'os_licenses.license_number AS os_license_number,' +
  35.         'os_licenses.purchase_date AS os_purchase_date,' +
  36.         //Operation system name
  37.         'operation_systems.os_developer_id,' +
  38.         'operation_systems.name AS os_name,' +
  39.         'operation_systems.version AS os_version,' +
  40.         'operation_systems.subversion AS os_subversion,' +
  41.         'operation_systems.bits AS os_bits,' +
  42.         //Operation system software developer name
  43.         'os_software_developers.name AS os_developer_name,' +
  44.         //Filter name
  45.         'filter_programs.program_developer_id AS computer_filter_developer_id,' +
  46.         'filter_programs.name AS computer_filter_name,' +
  47.         'filter_programs.version AS computer_filter_version,' +
  48.         //Filter software developer name
  49.         'filter_software_developers.name AS computer_filter_developer_name,' +
  50.         //Filter type name
  51.         'filter_types.name AS computer_filter_type_name,' +
  52.         //Projector type name
  53.         'projector_types.name AS computer_projector_type_name,' +
  54.         //Program count
  55.         '(SELECT COUNT(*) FROM computer_program_licenses ' +
  56.         'WHERE computer_program_licenses.program_computer_id=computers.id) ' +
  57.         'AS computer_program_count,' +
  58.         //Device count
  59.         '(SELECT COUNT(*) FROM computer_device_accounts ' +
  60.         'WHERE computer_device_accounts.device_computer_id=computers.id) ' +
  61.         'AS computer_device_count ' +
  62.         //From
  63.         'FROM computers ' +
  64.         //Type name
  65.         'LEFT OUTER JOIN computer_types ON computer_types.id=computer_type ' +
  66.         //Hardware developer name
  67.         'LEFT OUTER JOIN hardware_developers AS computer_hardware_developers ' +
  68.         'ON computer_hardware_developers.id=computer_developer_id ' +
  69.         //Kind name
  70.         'LEFT OUTER JOIN computer_kinds ON computer_kinds.id=computer_kind ' +
  71.         //Processor name
  72.         'LEFT OUTER JOIN processors ON processors.id=computer_processor_id ' +
  73.         //Processor hardware developer name
  74.         'LEFT OUTER JOIN hardware_developers AS processor_hardware_developers ' +
  75.         'ON processor_hardware_developers.id=processors.processor_developer_id ' +
  76.         //Monitor account name
  77.         'LEFT OUTER JOIN monitor_accounts ' +
  78.         'ON monitor_accounts.id=computer_monitor_account_id ' +
  79.         //Monitor name
  80.         'LEFT OUTER JOIN monitors ' +
  81.         'ON monitors.id=monitor_accounts.monitor_id ' +
  82.         //Monitor hardware developer name
  83.         'LEFT OUTER JOIN hardware_developers AS monitor_hardware_developers ' +
  84.         'ON monitor_hardware_developers.id=monitors.monitor_developer_id ' +
  85.         //Monitor type name
  86.         'LEFT OUTER JOIN monitor_types ' +
  87.         'ON monitor_types.id=monitors.monitor_type_id ' +
  88.         //Floppy drive type name
  89.         'LEFT OUTER JOIN floppy_drive_types ' +
  90.         'ON floppy_drive_types.id=computer_floppy_drive_type_id ' +
  91.         //Optical drive type name
  92.         'LEFT OUTER JOIN optical_drive_types ' +
  93.         'ON optical_drive_types.id=computer_optical_drive_type_id ' +
  94.         //Operation system license name
  95.         'LEFT OUTER JOIN os_licenses ' +
  96.         'ON os_licenses.id=computer_os_license_id ' +
  97.         //Operation system name
  98.         'LEFT OUTER JOIN operation_systems ' +
  99.         'ON operation_systems.id=os_licenses.os_id ' +
  100.         //Operation system software developer name
  101.         'LEFT OUTER JOIN software_developers AS os_software_developers ' +
  102.         'ON os_software_developers.id=operation_systems.os_developer_id ' +
  103.         //Filter name
  104.         'LEFT OUTER JOIN programs AS filter_programs ' +
  105.         'ON filter_programs.id=computer_filter_id ' +
  106.         //Filter software developer name
  107.         'LEFT OUTER JOIN software_developers AS filter_software_developers ' +
  108.         'ON filter_software_developers.id=filter_programs.program_developer_id ' +
  109.         //Filter type name
  110.         'LEFT OUTER JOIN filter_types ON filter_types.id=computer_filter_type_id ' +
  111.         //Projector type name
  112.         'LEFT OUTER JOIN projector_types ON projector_types.id=computer_projector_type_id ' +
  113.         //Where
  114.         'WHERE computers.id=:id';
  115.       ParamInt['id'] := Id;
  116.       Open;
  117.  
« Last Edit: March 23, 2017, 07:56:13 pm by Mr.Madguy »
Is it healthy for project not to have regular stable releases?
Just for fun: Code::Blocks, GCC 13 and DOS - is it possible?

DonAlfredo

  • Hero Member
  • *****
  • Posts: 1739
Re: What way of interaction with database is better?
« Reply #3 on: March 23, 2017, 08:03:45 pm »
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

  • Sr. Member
  • ****
  • Posts: 270
Re: What way of interaction with database is better?
« Reply #4 on: March 23, 2017, 11:58:35 pm »
Not wanting to repeat myself, but "the mORMot" ?
...
Disclaimer: I like the mORMot !

+1

mORMot is abnormal ORM. Is very fast.  8)
Checkout NewPascal initiative and donate beer - ready to use tuned FPC compiler + Lazarus for mORMot project

best regards,
Maciej Izak

Leledumbo

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: What way of interaction with database is better?
« Reply #5 on: March 24, 2017, 02:59:45 am »
And 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.
What 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.
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.
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

  • Hero Member
  • *****
  • Posts: 844
Re: What way of interaction with database is better?
« Reply #6 on: March 24, 2017, 08:07:57 am »
Ok, I'll check it. At least I have a good news - I haven't invented a bicycle.
Is it healthy for project not to have regular stable releases?
Just for fun: Code::Blocks, GCC 13 and DOS - is it possible?

 

TinyPortal © 2005-2018