Live bindings to Firebird table

[Wargan]

Hello, friends!
I recently started getting to know Firebird 3.0.2.

However, in the tools "out of the box", I did not find things that were extremely important to me, such as binding to table classes from the database.

Here is my example:
I use Virtual String Tree to display data from a database (this is a list of streets). To improve performance, I used records.

Code: Pascal  [Select][+][-]

1. TStreet = record
2.     Id: integer;
3.     Name: string;
4.   end;

_{However, in the future, everything changed in my work}

Firebird database has a table called Streets with the following parameters:

Code: MySQL  [Select][+][-]

1.  Id INTEGER PRIMARY KEY
2.  Name VARCHAR (32) UNIQUE NOT NULL

In the future, I still plan to create a separate class using the MVC pattern, like this template:

Code: Pascal  [Select][+][-]

1.   TStreetEx = class
2.   private
3.     FId: integer;
4.     FName: string;
5.     procedure SetId (AValue: integer);
6.     procedure SetName (AValue: string);
7.   public
8.     property Id: integer read FId write SetId;
9.     property Name: string read FName write SetName;
10.   end;

So, can I bind the fields of the Streets table to the fields of my TStreetEx? For them to be loaded automatically (for example, by binding to TSQLQuery), then they were changed through the program interface, and then saved.

p.s. so I understand, to use several TStreetEx I need to create a separate class TStreetsList with the methods BeginUpdate and EndUpdate, and then shove them into the VST...

[mangakissa]

Well, something like mORMot or tiOPF shall do the trick. MORMot use SQLite as internal database.
I haven't read MVC pattern book yet, but you have to create a link by yourself.
Filling the class is easy:

Code: Pascal  [Select][+][-]

1. uses fgl;
2.  
3. TStreetEx = class
4.   private
5.     FId: integer;
6.     FName: string;
7.     procedure SetId (AValue: integer);
8.     procedure SetName (AValue: string);
9.   public
10.     property Id: integer read FId write SetId;
11.     property Name: string read FName write SetName;
12.   end;
13.  
14. TStreetlist = specialize Tobjectlist<TStreetEx>;
15.  
16. TStreets = class
17. private
18.   fStreetlist : TStreetlist;
19. public
20.   constructor create;
21.   destructor  Destroy; override;
22.  
23.   procedure load;
24. end;
25.  
26. constructor TStreets.create;
27. begin
28.   fStreetlist := TStreetlist.create;
29. end;
30.  
31. destructor Tstreets.destroy;
32. begin
33.   fStreetlist.free;
34. end;
35.  
36. procedure TStreets.load;
37. var SQLQuery : TSQLQuery;
38.     FStreetEx: TStreetEx;
39. begin
40.   SQLQuery := TSQLQuery.create(nil);
41.   try
42.     /* connect your table to SQLQuery */
43.     while not SQLQuery.eof do
44.     begin
45.       FStreetEx      := TStreetEx.create;
46.       FStreetEx.Id   := SQLQuery.fields[0].AsInteger;  
47.       FStreetEx.Name := SQLQuery.fields[1].AsString;
48.       fStreetlist.Add(FStreetEx);
49.       SQLQuery.next;
50.     end;
51.   finally
52.     SQLQuery.free;
53.   end;
54. end;
55.  

Delphi has his own livebindings and use the same method underwater.

It's not perfect, but it answers your question.
For generics: see http://wiki.freepascal.org/Generics

[Thaddy]

Hello, friends!
I recently started getting to know Firebird 3.0.2.
p.s. so I understand, to use several TStreetEx I need to create a separate class TStreetsList with the methods BeginUpdate and EndUpdate, and then shove them into the VST...

You are using the wrong string type in your record.

Code: Pascal  [Select][+][-]

1. TStreet = packed record
2.     Id: integer;
3.     Name: Array[0..31] of AnsiChar;
4.   end;

Maps to

Code: MySQL  [Select][+][-]

1.  Id INTEGER PRIMARY KEY
2.  Name VARCHAR (32) UNIQUE NOT NULL

Note if your database is created in UTF8 it needs to be array[0..127] of AnsiChar, four times the size!
Subsequent conversions are otherwise easy.

Reason is the database stores no string length inside the record, but in the table and field meta data, so you need to use an array of a suitable fixed length char type. (Ansi, UCS2, or UTF32).
UTF8, UTF16 are variable length char types (from 1 to 4 bytes).
Pascal strings always contain string length...The database does not.

Note your record can have e.g. an added ToString function to do that. Advanced records... Does not matter for mapping or storage.

[Graeme]

Take a look at tiOPF [http://tiopf.sourceforge.net], it does exactly what you need, and has been open source and continuously maintained and  developed since 1999. One the "tiopf_apps" repository, there is also a db mapping tool that will generate fully working tiOPF based units for you - from simple XML mapping information. This will save you tons of boiler-plate coding. tiOPF also include an implementation of MGM (Model-GUI-Mediator) which is very similar to MVC, except that it reused the events of modern GUI toolkits - no need to reinvent the wheel again - what MVC requires.

You are welcome to visit the tiopf.support newsgroup for any additional questions. Simply follow the Support link on the website.