Recent

Author Topic: Indexes in FPSpreadsheetDataset  (Read 2544 times)

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Indexes in FPSpreadsheetDataset
« on: September 05, 2021, 01:32:52 am »
Hi, @wp!

Would it be possible to add IndexName and IndexFieldNames properties to the FPSpreadsheetDataset?

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8877
Re: Indexes in FPSpreadsheetDataset
« Reply #1 on: September 09, 2021, 11:21:41 am »
TDataset has only rudimentary support of indexes. When a descendant has indexes it gets them from the underlying database engine, or, as in case of TBufDataset, creates them by itself.

Unfortunately I had built TsWorksheetDataset similar to TMemDataset because it is very simple, and therefore, providing an index is rather difficult.

On the other hand, TBufDataset has a very flexible infrastructure regarding the format of the data files based on so-called "packet readers". Therefore, I have the idea to speeze in fpspreadsheet as a new packet reader class. This way, TsWorksheetDataset would have the same functionality as TBufdataset, but would work now with worksheets rather than with xml files. The only problem is that the packet readers have a stream-based interface to read (and write) the data, but I am rather sure that TsWorksheetDataset must provide an already loaded workbook/worksheet.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #2 on: September 09, 2021, 12:46:38 pm »
Dear @wp,

Thanks for your answer. No problem - in fact, indexes would not be really useful to me anyway because I have been successful in using TsWorksheetDataSet in combination with the TZMSQL (https://wiki.freepascal.org/ZMSQL), so that I can now fire (simple) SQL queries against the data stored in a spreadsheet, with the invaluable help of @paweld. See the attached demo application (notice that the SQL query in the demo will only work correctly with the fixed version of TZMSQL provided by @paweld and available here: https://github.com/paweld/zmsql/tree/master). That's what I wanted from the beginning!

Thank you very much!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8877
Re: Indexes in FPSpreadsheetDataset
« Reply #3 on: September 12, 2021, 12:34:45 am »
Implemented a sorting method to the old TsWorksheetDataset based on what is already available in TsWorksheet - this was very simple. Still no "true" IndexDefs etc, but I thinks this is enough and stop it here. Some more tests, and I'll move the repo to the "official" fpspreadsheet, probably as a separate package, laz_fpspreadsheet_dataset, or so.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #4 on: September 12, 2021, 12:38:44 am »
@wp,

I will install the new version right now. The new sort feature should really be quite useful.

Thanks a lot!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #5 on: September 12, 2021, 01:27:33 pm »
Dear @wp,

I just saw that when attempting to compile the "visual" demo which comes with the FPSpreadsheetDataset component, I got the following error:

Code: Pascal  [Select][+][-]
  1. unit2.pas(321,62) Error: Incompatible type for arg no. 3: Got "{Array Of Const/Constant Open} Array of Boolean", expected "TsBooleanArray"

BTW, would it be possible to implement a "Sort" property, so that I could use code like that:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.DBGrid1TitleClick(Column: TColumn);
  2. var
  3.   cFieldName: string;
  4. begin
  5.   FPDataset1.Sort := Column.Field.FieldName;
  6. end;
  7.  
to sort a DBGrid by clicking a column's title?

Thanks in advance!

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #6 on: September 12, 2021, 03:30:31 pm »
@wp,

Please do not consider my question concerning the implementation of a 'Sort' property, because I have just seen that sorting can be easily performed with the SortOnFields method implemented in the latest release of the FPSpreadsheetDataset component.

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8877
Re: Indexes in FPSpreadsheetDataset
« Reply #7 on: September 12, 2021, 07:19:16 pm »
Added a demo for sorting and filtering. Note that there is an issue with filtering based on the Filter property because the expression parser "borrowed" from TBufDataset cannot handle widestring fields, but the automatic field detection converts worksheet text cells to widestring for easier field width control. Guess I'll have to look into this again.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #8 on: September 12, 2021, 08:43:37 pm »
Dear @wp,

Thanks again, I will look into the added demos.

So far I have not found issues with filtering, which seems to work fine. Anyway, in my application I treat all data as text (converting if necessary, and this is not very much the case).

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #9 on: September 15, 2021, 11:41:28 pm »
Dear @wp,

Hi. When attempting to compile the latest version of FPSpreadSheetDataset I got the errors below:

Code: Pascal  [Select][+][-]
  1. fpsdataset.pas(1786,33) Error: identifier idents no member "CodePage"
  2. fpsdataset.pas(1931,44) Error: Incompatible type for arg no. 2: Got "{Array Of Const/Constant Open} Array of TsSortOptions", expected "TsSortOptionsArray"
  3.  

Hope this helps.

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 8877
Re: Indexes in FPSpreadsheetDataset
« Reply #10 on: September 16, 2021, 06:18:28 pm »
Fixed. But note that although the package can be compiled with FPC 3.0.4 now the unit test compiled with this compiler crashes - there are some particularly ugly pointer constructs inside TDataset. I don't know yet if I should put much work into fixing this for an out-dated compiler.

The first error message about CodePage reminds me to tell you that your compiler does not support the CodePage property in the FieldDefs. As a consequence all text worksheet columns are imported as StringFields using the default codepage of your system, on windows CP_ACP; but since the dataset does not do any codepage conversions the import of UTF8 strings will be correct as well. With one exception: The UTF8 string can be longer than a pure ANSI string because characters may be composed of up to four bytes, and the automatic FieldDef detection counts only the bytes and adjusts the field width to be 8, 16, 32, 64, 128 or 256 depending on the longest string in the column. As a consequence strings containing UTF8 code points and with a length close to these limits may be truncated. To avoid this I added a new property AutoFieldDefStringSize. When you set this to some reasonably large value this value will be used for all text fields. Of course, memory consumption of the internal buffers will go up accordingly (but who cares about memory nowadays...)
« Last Edit: September 16, 2021, 06:45:20 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

maurobio

  • Sr. Member
  • ****
  • Posts: 294
  • Ecology is everything.
    • GitHub
Re: Indexes in FPSpreadsheetDataset
« Reply #11 on: September 16, 2021, 09:48:25 pm »
@wp,

Thanks again for your great effort (and still greater patience)! I will try the updated code ASAP. Right now, making the FSpreadsheetDataset code compatible with earlier versions of FPC by using proper defines has been very helpful to me. I understand that my version of the compiler may not be supported in the future, but at this time I have not enough time to upgrade all my applications to a newer version, and besides I am afraid that this move may break some of the other components I have in use %).

With best wishes,
UCSD Pascal / Burroughs 6700 / Master Control Program
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19, GNU/Linux Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

 

TinyPortal © 2005-2018