dOPF - Object persistence framework / ORM lazarus / 16-Databases ---------------------------------------------------------------------- Renat.Su · 2024-11-17 18:44 I just want to inform about the dOPF library (author Silvio Clécio  https://github.com/silvioprog ). A very convenient, intuitive and lightweight ORM. Previously, it was in the BrookFreePascal plugins folder, although it was a self-contained library and has no dependency on Brook. Now it's got a separate location here https://github.com/pascal-libs/dopf P.S. When I need to get a fast and convenient database operations (especially not in GUI applications) this library is my most frequent choice. ---------------------------------------------------------------------- Zoran · 2024-11-17 21:16 Is there any help or tutorial for it? ---------------------------------------------------------------------- Renat.Su · 2024-11-17 21:38 Quote from: Zoran on November 17, 2024, 10:16:15 pmIs there any help or tutorial for it? The best tutorial is demo folder. There are some main ways to use the lib. It has different generic classes for working with databases. The most basic way to CRUD operations with maximum approximation is to work with DB engines through objects of your classes. Published properties are fields. Standard for ORM encapsulation of work with SQL engines (MySQL, SQLite, Firebird etc - connecting via ***conn.pas from native sqldb packages and driver property). And many other features. ---------------------------------------------------------------------- Renat.Su · 2024-11-17 21:45 For example, https://github.com/pascal- libs/dopf/tree/main/demos/opf/entity Work with storage SQLite3 via entity class: 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 program demo1; {$mode objfpc}{$H+} uses  dOpf, dSQLdbBroker, dbutils, person, sysutils; type  Topf = specialize TdGSQLdbEntityOpf; var  i: TPerson;  pers: Topf.TEntities;  opf: Topf;begin  opf := Topf.Create(dbutils.con, 'person');  pers := Topf.TEntities.Create;  try    WriteLn('Empty table');    opf.Empty;    opf.Apply;    WriteLn('Done.');     WriteLn('Add Silvio Clécio');    opf.Entity.Name := 'Silvio Clécio';    opf.Add;    WriteLn('Done.');     WriteLn('Add Anonymous');    opf.Entity.Id := 1000;    opf.Entity.Name := 'Anonymous';    opf.Add(False);    WriteLn('Done.');     WriteLn('Add Waldir');    opf.Entity.Id := 1001;    opf.Entity.Name := 'Waldir';  opf.Add(False);    WriteLn('Done.');     WriteLn('Add João Morais');  opf.Entity.Name := 'João Morais';    opf.Add;    WriteLn('Done.');   WriteLn('Add Sven Barth');    opf.Entity.Name := 'Sven Barth';    opf.Add;    WriteLn('Done.');     WriteLn('Modify name of Waldir to Waldir Paim');    opf.Entity.Id := 1001;    opf.Entity.Name := 'Waldir Paim';    opf.Modify;    WriteLn('Done.');     WriteLn('Remove Anonymous');    opf.Entity.Id := 1000;    opf.Remove;    WriteLn('Done.');     WriteLn('Get Waldir Paim');    opf.Entity.Id := 1001;    opf.Get;    WriteLn(opf.Entity.Id, ', ', opf.Entity.Name);  WriteLn('Done.');     WriteLn('Find Silvio Clécio by name');    opf.Entity.Name := 'Silvio Clécio';    opf.Find('name = :name');    WriteLn(opf.Entity.Id, ', ', opf.Entity.Name);    WriteLn('Done.');   WriteLn('Search for names containing "a"');    opf.Entity.Name := '%a%';    opf.Find(pers, 'name like (:name)');    for i in pers do    WriteLn(i.Id, ', ', i.Name);    pers.Clear;    WriteLn('Done.');     WriteLn('List all');    opf.List(pers);    for i in pers do      WriteLn(i.Id, ', ', i.Name);    pers.Clear;    WriteLn('Done.');     WriteLn('Search for names containing "a" (order by id DESC)');    opf.Entity.Name := '%a%';    opf.Search(pers, nil,      'select * from person where name like (:name) order by id desc');    for i in pers do      WriteLn(i.Id, ', ', i.Name);    pers.Clear;    WriteLn('Done.');     opf.Apply;  finally    pers.Free;    opf.Free;  end;   ReadLn;end.Connection to DB in dbutils.pas 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 unit dbutils; {$mode objfpc}{$H+} interface uses  dSQLdbBroker, sqlite3conn, SysUtils; function con: TdSQLdbConnector; implementation var  _con: TdSQLdbConnector = nil; function con: TdSQLdbConnector;begin  if not Assigned(_con) then  begin    _con := TdSQLdbConnector.Create(nil);    _con.Logger.Active := True;    _con.Logger.FileName := 'OUTPUT.LOG';    _con.Driver := 'sqlite3';    _con.Database := '../../data.sqlite3';  end;  Result := _con;end; finalization  FreeAndNil(_con); end. ---------------------------------------------------------------------- Zoran · 2024-11-17 22:05 Thanks. Apparently, it's taken from the original author to a new maintainer. Can we expect it to be actively maintained? ---------------------------------------------------------------------- Renat.Su · 2024-11-17 22:10 The authorship of the library has not changed. The author and I discussed the expediency of the transfer. This is a joint decision. I would also like to draw your attention to the fact that he also created the pascal-lib organization https://github.com/pascal-libs / and I also made the last commits to this library when it was part of BrookFreePascal, so I am aware of the affairs of this library. ---------------------------------------------------------------------- Zoran · 2024-11-17 22:35 Thank you! ---------------------------------------------------------------------- Renat.Su · 2024-11-18 11:00 Quote from: Zoran on November 17, 2024, 11:35:20 pmThank you! Thanks for the questions. I actively use it for non-visual work with databases and creating layers between business logic and storages ---------------------------------------------------------------------- wp · 2024-11-18 13:46 dOPF is in OPM now. ---------------------------------------------------------------------- delphius · 2024-11-18 20:58 Another simple example...(empty db is in attach) TdGSQLdbOpf: This is a generic ORM (Object-Relational Mapping) operator that simplifies database operations. By specializing it with TPerson, it manages CRUD operations for the Person table directly, reducing boilerplate SQL code. Empty and Apply: Empty clears all data in the associated table, useful for resetting test environments. Apply commits any pending changes (e.g., inserts, updates, or deletes) to the database. Add, Modify, Remove, and List: Add: Adds a new entity to the database. Modify: Updates an existing record based on the entity's ID. Remove: Deletes a record identified by the entity's ID. List: Retrieves all records from the table into a collection. Entities: Individual records are represented as instances of TPerson. Multiple records are stored in a collection (Topf.TEntities), which is iterated using for..in. Memory Management: Entities (TPerson) and collections (Topf.TEntities) must be explicitly created and freed to avoid memory leaks. 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 program example; {$mode objfpc}{$H+} uses  dbutils, dSQLdbBroker, SysUtils, person; type  // Specialization of TdGSQLdbOpf for handling TPerson entities  Topf = specialize TdGSQLdbOpf; var  opf: Topf; // Object for managing operations on the `person` table  choice: Integer;  name: string;  age, id, newAge: Integer;  newName: string;  per: TPerson; // A single person entity  pers: Topf.TEntities; // A collection of person entities begin  // Create an instance of the ORM operator for the `person` table  // `dbutils.con` provides the database connection, and 'person' is the table name  opf := Topf.Create(dbutils.con, 'person');    try    // Ensures the database table is ready for use    // `Empty` clears all data in the table (useful for testing scenarios)    opf.Empty;     opf.Apply; // Applies the changes to the database     // Main menu for interacting with the database    repeat      Writeln('1. Add Person');      Writeln('2. List Persons');      Writeln('3. Update Person');      Writeln('4. Delete Person');      Writeln('0. Exit');      Write('Enter your choice: ');      Readln(choice);       case choice of        1: // Add a new person to the database          begin            Write('Enter Name: ');            Readln(name);            Write('Enter Age: ');          Readln(age);             per := TPerson.Create; // Create a new entity instance            try              per.Name := name;            per.Age := age;              opf.Add(per); // Adds the entity to the database through the ORM              opf.Apply; // Persists the changes in the database              Writeln('Person added.');          finally              per.Free; // Free the entity after use          end;          end;         2: // Retrieve and list all persons in the database          begin            pers := Topf.TEntities.Create; // Initialize a collection for entities          Writeln('Listing all persons:');            opf.List(pers); // Fetch all records into the collection            for per in pers do            Writeln(Format('ID: %d | Name: %s | Age: %d', [per.ID, per.Name, per.Age]));            Writeln('Done.');            pers.Free; // Free the collection after use          end;         3: // Update an existing person's details          begin            Write('Enter ID to Update: ');            Readln(id);            Write('Enter New Name: ');            Readln(newName);            Write('Enter New Age: ');            Readln(newAge);             per := TPerson.Create; // Create a temporary entity to hold changes          try              per.ID := id;              per.Name := newName;            per.Age := newAge;              opf.Modify(per); // Update the record in the database              opf.Apply; // Commit the updates              Writeln('Person updated.');            finally            per.Free; // Free the entity after use            end;        end;         4: // Delete a person by their ID          begin          Write('Enter ID to Delete: ');            Readln(id);           per := TPerson.Create; // Create a temporary entity for deletion          try              per.ID := id;              opf.Remove(per); // Remove the record from the database              opf.Apply; // Commit the deletion              Writeln('Person deleted.');            finally              per.Free; // Free the entity after use          end;          end;      end;     until choice = 0; // Exit the program when the user chooses option 0   finally    opf.Free; // Release the ORM operator and associated resources  end;   Writeln('Exiting...');end.  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 unit person; {$mode objfpc}{$H+} interface type   { TPerson }    TPerson = class(TObject)  private    FID: Integer;    FName: string;    FAge: Integer;  published    property ID: Integer read FID write FID;    property Name: string read FName write FName;    property Age: Integer read FAge write FAge;  end; implementation end. ---------------------------------------------------------------------- Thaddy · 2024-11-18 21:15 Quote from: delphius on November 18, 2024, 09:58:34 pmMemory Management: Entities (TPerson) and collections (Topf.TEntities) must be explicitly created and freed to avoid memory leaks. Not if they would be derived from TInterfacedObject... In that case it can be instantiated to IInterface and AS and IS provides access as the object and the memory management would be automatic. I have written an idea FR on the bug tracker and Warfley already wrote a MR for his implementation for my idea for a generic class that can add interface support to classes that are not derived from TInterfacedObject. But for now, deriving from TInterfacedObject would provide automatic memory management too. ---------------------------------------------------------------------- Renat.Su · 2024-11-18 21:22 Quote from: Thaddy on November 18, 2024, 10:15:11 pmQuote from: delphius on November 18, 2024, 09:58:34 pmMemory Management: Entities (TPerson) and collections (Topf.TEntities) must be explicitly created and freed to avoid memory leaks. Not if they would be derived from TInterfacedObject... In that case it can be instantiated to IInterface and AS and IS provides access as the object and the memory management would be automatic. I have written an idea FR on the bug tracker and Warfley already wrote a MR for his implementation for my idea for a generic class that can add interface support to classes that are not derived from TInterfacedObject. But for now, deriving from TInterfacedObject would provide automatic memory management too. Right but an entity class for the dOPF library can be any, not necessarily a descendant of IInterfacedObject or TComponent, but for example, just TObject class ---------------------------------------------------------------------- Renat.Su · 2024-11-18 21:24 Quote from: delphius on November 18, 2024, 09:58:34 pmAnother simple example...(empty db is in attach) TdGSQLdbOpf: This is a generic ORM (Object-Relational Mapping) operator that simplifies database operations. By specializing it with TPerson, it manages CRUD operations for the Person table directly, reducing boilerplate SQL code. Empty and Apply: Empty clears all data in the associated table, useful for resetting test environments. Apply commits any pending changes (e.g., inserts, updates, or deletes) to the database. Add, Modify, Remove, and List: Add: Adds a new entity to the database. Modify: Updates an existing record based on the entity's ID. Remove: Deletes a record identified by the entity's ID. List: Retrieves all records from the table into a collection. Entities: Individual records are represented as instances of TPerson. Multiple records are stored in a collection (Topf.TEntities), which is iterated using for..in. Memory Management: Entities (TPerson) and collections (Topf.TEntities) must be explicitly created and freed to avoid memory leaks. 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 program example; {$mode objfpc}{$H+} uses  dbutils, dSQLdbBroker, SysUtils, person; type  // Specialization of TdGSQLdbOpf for handling TPerson entities  Topf = specialize TdGSQLdbOpf; var  opf: Topf; // Object for managing operations on the `person` table  choice: Integer;  name: string;  age, id, newAge: Integer;  newName: string;  per: TPerson; // A single person entity  pers: Topf.TEntities; // A collection of person entities begin  // Create an instance of the ORM operator for the `person` table  // `dbutils.con` provides the database connection, and 'person' is the table name  opf := Topf.Create(dbutils.con, 'person');    try    // Ensures the database table is ready for use    // `Empty` clears all data in the table (useful for testing scenarios)    opf.Empty;     opf.Apply; // Applies the changes to the database     // Main menu for interacting with the database    repeat      Writeln('1. Add Person');      Writeln('2. List Persons');      Writeln('3. Update Person');      Writeln('4. Delete Person');      Writeln('0. Exit');      Write('Enter your choice: ');      Readln(choice);       case choice of        1: // Add a new person to the database          begin            Write('Enter Name: ');            Readln(name);            Write('Enter Age: ');          Readln(age);             per := TPerson.Create; // Create a new entity instance            try              per.Name := name;            per.Age := age;              opf.Add(per); // Adds the entity to the database through the ORM              opf.Apply; // Persists the changes in the database              Writeln('Person added.');          finally              per.Free; // Free the entity after use          end;          end;         2: // Retrieve and list all persons in the database          begin            pers := Topf.TEntities.Create; // Initialize a collection for entities          Writeln('Listing all persons:');            opf.List(pers); // Fetch all records into the collection            for per in pers do            Writeln(Format('ID: %d | Name: %s | Age: %d', [per.ID, per.Name, per.Age]));            Writeln('Done.');            pers.Free; // Free the collection after use          end;         3: // Update an existing person's details          begin            Write('Enter ID to Update: ');            Readln(id);            Write('Enter New Name: ');            Readln(newName);            Write('Enter New Age: ');            Readln(newAge);             per := TPerson.Create; // Create a temporary entity to hold changes          try              per.ID := id;              per.Name := newName;            per.Age := newAge;              opf.Modify(per); // Update the record in the database              opf.Apply; // Commit the updates              Writeln('Person updated.');            finally            per.Free; // Free the entity after use            end;        end;         4: // Delete a person by their ID          begin          Write('Enter ID to Delete: ');            Readln(id);           per := TPerson.Create; // Create a temporary entity for deletion          try              per.ID := id;              opf.Remove(per); // Remove the record from the database              opf.Apply; // Commit the deletion              Writeln('Person deleted.');            finally              per.Free; // Free the entity after use          end;          end;      end;     until choice = 0; // Exit the program when the user chooses option 0   finally    opf.Free; // Release the ORM operator and associated resources  end;   Writeln('Exiting...');end.  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 unit person; {$mode objfpc}{$H+} interface type   { TPerson }    TPerson = class(TObject)  private    FID: Integer;    FName: string;    FAge: Integer;  published    property ID: Integer read FID write FID;    property Name: string read FName write FName;    property Age: Integer read FAge write FAge;  end; implementation end. An excellent description with an example. I will add this to README https://github.com/pascal-libs/dopf/blob/main/README.md ? ---------------------------------------------------------------------- Thaddy · 2024-11-18 21:34 Quote from: Renat.Su on November 18, 2024, 10:22:52 pmRight but an entity class for the dOPF library can be any, not necessarily a descendant of IInterfacedObject or TComponent, but for example, just TObject class And that is exactly what I wanted and Warfley implemented. It is still a merge request, no warranty, but you can try it, if you know how to patch. https://gitlab.com/freepascal.org/fpc/source/-/merge_requests/851 Warfley's implementation https://gitlab.com/freepascal.org/fpc/source/-/issues/40983 my report BTW Delphi does not support that either and my imagined code contains 1 small bug. ---------------------------------------------------------------------- egsuh · 2024-11-20 05:35 Does this operate over the Internet? Any other way to use DB over the Internet, not via WebService. Direct accessing DB (e.g. Firebird) over HTTP server? Currently I have implemented sending/receiving dataset as a file over the web (not web service, normal web server). ---------------------------------------------------------------------- Renat.Su · 2024-11-20 07:13 Quote from: egsuh on November 20, 2024, 06:35:51 amDoes this operate over the Internet? Any other way to use DB over the Internet, not via WebService. Direct accessing DB (e.g. Firebird) over HTTP server? Currently I have implemented sending/receiving dataset as a file over the web (not web service, normal web server). It works as programmed. ORM is just a wrapper around working with a database using regular classes in Pascal. ---------------------------------------------------------------------- Renat.Su · 2024-11-20 07:17 For example, if a remote firebird database server is used, then the appropriate connection parameters are simply specified. The direct function of connecting to the database is performed by native classes from db, dbsql, etc.