Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 24/2/23 a les 11:10, Michael Van Canneyt via lazarus ha escrit: No. RecalcBufListSize does not necessarily fetch all the records. It fetches as much records as needed: 10 by default, but classes such as TDBGrid may set the number of needed records to the number of visible grid lines. It can be that TBufDataset fetches all records on open, there is a property that controls this. But it can also fetch on an as-neede basis. FEOF is simply initialized from the first batch. But it is maintained based on the result of GetNextRecord. Check the MoveBy procedure. Only when GetNextRecord returns false, is FEOF set to True (line 2005). Thank you! With these insights (and a lot of sweat, blood and tears ;-)) I managed to write a descendant dataset that does what I want. Not 100% complete yet but getting there. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
On Fri, 24 Feb 2023, Luca Olivetti via lazarus wrote:
El 23/2/23 a les 17:16, Michael Van Canneyt ha escrit:
write a TDataset descendant tailor made for this
application, any pointer on how to write a minimal descendant?
I'm looking at the TMemDataset implementation as an example, is it a good
one?
As good as any other.
The idea is to substitute open and scrolling (next/prior/locate/etc.) with
the appropriate sql statements to just retrieve a small buffer of data
around the current record (I can do that easily enough), though I'm not
sure that would be enough to fool the TDBGrid.
I don't know what the grid exactly does, so I cannot advise.
I did some tests and the grid is not a problem (it only fetches the records
that are visible), the TDataset is: either I am blind or the only way for
the TDataset to realize there is data is to fetch every record and allocate
the corresponding buffers.
That's because FRecordCount/FBof/FEof are private
FRecordCount is the number of records in internal buffers,
and has nothing to do with the total number of records.
The total RecordCount must be given by TDataset descendents,
but defaults to FRecordCount in TDataset itself.
The terminology is confusing, I know.
We should probably rename FRecordCount to FBufferRecordCount.
procedure TDataSet.DoInternalOpen;
begin
InternalOpen;
FInternalOpenComplete := True;
{$ifdef dsdebug}
Writeln ('Calling internal open');
{$endif}
{$ifdef dsdebug}
Writeln ('Calling RecalcBufListSize');
{$endif}
FRecordCount := 0;
RecalcBufListSize; <-- this will fetch all the records
FBOF := True;
FEOF := (FRecordCount = 0); <--- FRecordCount must be set
end;
No.
RecalcBufListSize does not necessarily fetch all the records. It fetches as
much records as needed: 10 by default, but classes such as TDBGrid may set
the number of needed records to the number of visible grid lines.
It can be that TBufDataset fetches all records on open, there is a property that
controls this. But it can also fetch on an as-neede basis.
FEOF is simply initialized from the first batch.
But it is maintained based on the result of GetNextRecord.
Check the MoveBy procedure.
Only when GetNextRecord returns false, is FEOF set to True (line 2005).
There's no point overriding
GetNextRecords/GetNextRecord/GetPriorRecords/GetPriorRecord (called by
RecalcBufListSize) since I cannot directly modify the private fields of the
TDataSet.
You don't need to modify them. They are automatically maintained based on
the result of GetNextRecord. TDataset is responsible for maintaining a
correct state based on what descendants return in the methods they override.
Exposing these internal state fields for modification means TDataset cannot
guarantee the correct state, and therefore that will not happen.
Any idea?
Personally, I believe you are on the wrong path by wanting to modify TDataset or
its descendants.
But if you want to pursue that track, study TDataset a little more and ask questions.
It is a complex class, one of the most complex in the whole FCL.
Michael.
--
___
lazarus mailing list
lazarus@lists.lazarus-ide.org
https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 17:16, Michael Van Canneyt ha escrit:
write a TDataset descendant tailor made for this
application, any pointer on how to write a minimal descendant?
I'm looking at the TMemDataset implementation as an example, is it a
good one?
As good as any other.
The idea is to substitute open and scrolling (next/prior/locate/etc.)
with the appropriate sql statements to just retrieve a small buffer of
data around the current record (I can do that easily enough), though
I'm not sure that would be enough to fool the TDBGrid.
I don't know what the grid exactly does, so I cannot advise.
I did some tests and the grid is not a problem (it only fetches the
records that are visible), the TDataset is: either I am blind or the
only way for the TDataset to realize there is data is to fetch every
record and allocate the corresponding buffers.
That's because FRecordCount/FBof/FEof are private
procedure TDataSet.DoInternalOpen;
begin
InternalOpen;
FInternalOpenComplete := True;
{$ifdef dsdebug}
Writeln ('Calling internal open');
{$endif}
{$ifdef dsdebug}
Writeln ('Calling RecalcBufListSize');
{$endif}
FRecordCount := 0;
RecalcBufListSize; <-- this will fetch all the records
FBOF := True;
FEOF := (FRecordCount = 0); <--- FRecordCount must be set
end;
There's no point overriding
GetNextRecords/GetNextRecord/GetPriorRecords/GetPriorRecord (called by
RecalcBufListSize) since I cannot directly modify the private fields of
the TDataSet.
Any idea?
Bye
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007
--
___
lazarus mailing list
lazarus@lists.lazarus-ide.org
https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 17:16, Michael Van Canneyt ha escrit: Even if such a component existed, I don't think it would fit my use-case. Why do you think so ? Paging is exactly what you want to do. Not exactly, I mean, yes, it is paging but it has to be "transparent", I don't want to add "previous page" and "next page" buttons. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
On Thu, 23 Feb 2023, Luca Olivetti via lazarus wrote: El 23/2/23 a les 13:52, Michael Van Canneyt via lazarus ha escrit: On Thu, 23 Feb 2023, Luca Olivetti via lazarus wrote: El 23/2/23 a les 11:11, Michael Van Canneyt via lazarus ha escrit: But IMO: in the first place you should ask yourself if displaying 10.000 records is what you actually want to do. No, I just need to display 20 or so records, the problem is I'd like the user to be able to scroll over the whole table, so I'd have to hook a lot of events in the TDBGrid (if that's even possible, otherwise I'd have to implement a replacement) and get rid of the TDBNavigator and implement a custom one in its place. The LCL could do with a pager component. Seems to be standard when Web pages display grids/tables, so users should be used to it :-) Even if such a component existed, I don't think it would fit my use-case. Why do you think so ? Paging is exactly what you want to do. Maybe I should just write a TDataset descendant tailor made for this application, any pointer on how to write a minimal descendant? I'm looking at the TMemDataset implementation as an example, is it a good one? As good as any other. The idea is to substitute open and scrolling (next/prior/locate/etc.) with the appropriate sql statements to just retrieve a small buffer of data around the current record (I can do that easily enough), though I'm not sure that would be enough to fool the TDBGrid. I don't know what the grid exactly does, so I cannot advise. Michael. -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 13:52, Michael Van Canneyt via lazarus ha escrit: On Thu, 23 Feb 2023, Luca Olivetti via lazarus wrote: El 23/2/23 a les 11:11, Michael Van Canneyt via lazarus ha escrit: But IMO: in the first place you should ask yourself if displaying 10.000 records is what you actually want to do. No, I just need to display 20 or so records, the problem is I'd like the user to be able to scroll over the whole table, so I'd have to hook a lot of events in the TDBGrid (if that's even possible, otherwise I'd have to implement a replacement) and get rid of the TDBNavigator and implement a custom one in its place. The LCL could do with a pager component. Seems to be standard when Web pages display grids/tables, so users should be used to it :-) Even if such a component existed, I don't think it would fit my use-case. Maybe I should just write a TDataset descendant tailor made for this application, any pointer on how to write a minimal descendant? I'm looking at the TMemDataset implementation as an example, is it a good one? The idea is to substitute open and scrolling (next/prior/locate/etc.) with the appropriate sql statements to just retrieve a small buffer of data around the current record (I can do that easily enough), though I'm not sure that would be enough to fool the TDBGrid. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
On Thu, 23 Feb 2023, Luca Olivetti via lazarus wrote: El 23/2/23 a les 11:11, Michael Van Canneyt via lazarus ha escrit: But IMO: in the first place you should ask yourself if displaying 10.000 records is what you actually want to do. No, I just need to display 20 or so records, the problem is I'd like the user to be able to scroll over the whole table, so I'd have to hook a lot of events in the TDBGrid (if that's even possible, otherwise I'd have to implement a replacement) and get rid of the TDBNavigator and implement a custom one in its place. The LCL could do with a pager component. Seems to be standard when Web pages display grids/tables, so users should be used to it :-) Michael. -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 12:24, Jean SUZINEAU via lazarus ha escrit: Le 23/02/2023 à 11:37, Luca Olivetti via lazarus a écrit : No, that's not possible, in any case polling isn't a problem: it's the same application that modifies the table, and it only does the "refresh" when necessary. That's exactly a use case for my orm ( https://github.com/jsuzineau/pascal_o_r_mapping/tree/TjsDataContexte ) It's working with the Observer design pattern, no need to poll Thank you, but from a quick look it doesn't seem to solve the problem at hand. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
Le 23/02/2023 à 11:37, Luca Olivetti via lazarus a écrit : No, that's not possible, in any case polling isn't a problem: it's the same application that modifies the table, and it only does the "refresh" when necessary. That's exactly a use case for my orm ( https://github.com/jsuzineau/pascal_o_r_mapping/tree/TjsDataContexte ) It's working with the Observer design pattern, no need to poll Unfortunately, I don't have written any documentation. It works with SQLite, there is even a source code generator that can create all the boiler plate code from the SQLite database. All the templates of the source code generator can be customized. Two of my last releases that uses this orm: https://github.com/jsuzineau/pascal_o_r_mapping/releases/tag/2023_02_20_jsWorks https://github.com/jsuzineau/pascal_o_r_mapping/releases/tag/2023_02_17_jsCompta -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 11:25, Marco van de Voort via lazarus ha escrit: Maybe upgrade to a DBMS (like firebird, mssql,postgres) that supports notifications so that you don't have to poll, but get a notification if something changes? No, that's not possible, in any case polling isn't a problem: it's the same application that modifies the table, and it only does the "refresh" when necessary. The problem is that it happens quite frequently (mind me, in a test environment, in the field the updates happen once or twice a minute, but I'm still not comfortable with the refresh taking too long). Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 11:11, Michael Van Canneyt via lazarus ha escrit: But IMO: in the first place you should ask yourself if displaying 10.000 records is what you actually want to do. No, I just need to display 20 or so records, the problem is I'd like the user to be able to scroll over the whole table, so I'd have to hook a lot of events in the TDBGrid (if that's even possible, otherwise I'd have to implement a replacement) and get rid of the TDBNavigator and implement a custom one in its place. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
On 23-2-2023 11:02, Luca Olivetti via lazarus wrote: OTOH a TDBGrid is a very convenient way to display data. I my application I just use it to display (in read-only mode) an sqlite table that holds less than 1000 records and it could be updated quite frequently (hence the need for the frequent TDataset.refresh), and it works fast enough with that limited amount of records. Now I need to expand the table to 1 records and the performance is unacceptable. Maybe upgrade to a DBMS (like firebird, mssql,postgres) that supports notifications so that you don't have to poll, but get a notification if something changes? -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
On Thu, 23 Feb 2023, Luca Olivetti via lazarus wrote: El 23/2/23 a les 9:43, Michael Van Canneyt via lazarus ha escrit: Something like this: https://docwiki.embarcadero.com/RADStudio/Rio/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode This is not something that the grid handles. This is a dataset feature. Yes, I supposed so At this moment I know of no FPC/Lazarus dataset component that handles this automatically. bummer :-( Note the remark on that page: "Although FireDAC minimizes the number of generated and executed SQL commands in LDW mode, it still produces a heavier DB load than TFDQuery. So, application developers should carefully choose when to use TFDTable and LDW mode. " This is exactly why FPC does not have a database table component. It's horribly inefficient. OTOH a TDBGrid is a very convenient way to display data. I my application I just use it to display (in read-only mode) an sqlite table that holds less than 1000 records and it could be updated quite frequently (hence the need for the frequent TDataset.refresh), and it works fast enough with that limited amount of records. Now I need to expand the table to 1 records and the performance is unacceptable. The best way is to use "limit N offset M" in your query and use a paging mechanism. Make sure you sort the records, obviously. But IMO: in the first place you should ask yourself if displaying 10.000 records is what you actually want to do. Maybe your application is better off with some filtering ? Michael.-- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 9:43, Michael Van Canneyt via lazarus ha escrit: Something like this: https://docwiki.embarcadero.com/RADStudio/Rio/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode This is not something that the grid handles. This is a dataset feature. Yes, I supposed so At this moment I know of no FPC/Lazarus dataset component that handles this automatically. bummer :-( Note the remark on that page: "Although FireDAC minimizes the number of generated and executed SQL commands in LDW mode, it still produces a heavier DB load than TFDQuery. So, application developers should carefully choose when to use TFDTable and LDW mode. " This is exactly why FPC does not have a database table component. It's horribly inefficient. OTOH a TDBGrid is a very convenient way to display data. I my application I just use it to display (in read-only mode) an sqlite table that holds less than 1000 records and it could be updated quite frequently (hence the need for the frequent TDataset.refresh), and it works fast enough with that limited amount of records. Now I need to expand the table to 1 records and the performance is unacceptable. Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
May be making a custom component based on VirtualTreeView ? -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
On Thu, 23 Feb 2023, Luca Olivetti via lazarus wrote: El 23/2/23 a les 9:04, Luca Olivetti via lazarus ha escrit: Hello, do you know of any component/dataset combo that works like a TDBGrid but doesn't load all the records from the db when doing a refresh, just the ones that are currently visible, yet offering seamless scrolling? I looked in the wiki but I couldn't find any. Something like this: https://docwiki.embarcadero.com/RADStudio/Rio/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode This is not something that the grid handles. This is a dataset feature. At this moment I know of no FPC/Lazarus dataset component that handles this automatically. Note the remark on that page: "Although FireDAC minimizes the number of generated and executed SQL commands in LDW mode, it still produces a heavier DB load than TFDQuery. So, application developers should carefully choose when to use TFDTable and LDW mode. " This is exactly why FPC does not have a database table component. It's horribly inefficient. Michael. -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
Re: [Lazarus] db grid/dataset components that only load currently visible records?
El 23/2/23 a les 9:04, Luca Olivetti via lazarus ha escrit: Hello, do you know of any component/dataset combo that works like a TDBGrid but doesn't load all the records from the db when doing a refresh, just the ones that are currently visible, yet offering seamless scrolling? I looked in the wiki but I couldn't find any. Something like this: https://docwiki.embarcadero.com/RADStudio/Rio/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode Bye -- Luca Olivetti Wetron Automation Technology http://www.wetron.es/ Tel. +34 93 5883004 (Ext.3010) Fax +34 93 5883007 -- ___ lazarus mailing list lazarus@lists.lazarus-ide.org https://lists.lazarus-ide.org/listinfo/lazarus
