Recent

Author Topic: SQL queries in FPSpreadSheet  (Read 9514 times)

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
SQL queries in FPSpreadSheet
« on: July 29, 2021, 02:31:56 pm »
Dear ALL,

FPSpreadSheet (https://wiki.lazarus.freepascal.org/FPSpreadsheet) is a really great Lazarus library for reading, writing, and handling spreadsheets in many formats. I offers a lot of functionality, but I could not find any obvious way of filtering rows in a FPSpreadSheet, preferentially using SQL queries. Is there any (not necessarily simple) way of achieving that?

Thanks in advance!

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

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: SQL queries in FPSpreadSheet
« Reply #1 on: July 29, 2021, 03:55:25 pm »
This feature is not planned. You probably can inherit from the fpspreadsheet SearchEngine (unit fpssearch) to introduce an SQL parser. Maybe some methods must be declared as virtual or moved to the public section. Just tell me what you need - I am open to such conservative changes, but I will definitely not add an SQL engine to fpspreadsheet, the library is too large already now.

Or you export the worksheet(s) to an SQL database (by using the fpspreadsheet export components) when you can apply SQL immediately.

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #2 on: July 29, 2021, 04:17:49 pm »
Dear @wp,

Thanks for your reply. In fact, I did not have in mind the inclusion of an SQL engine to the already superb FPSpreadSheet library. I just need a way of filtering records (rows in an spreadsheet) using relatively simple SQL queries with the most basic select's (no joins, etc.). But I would prefer to be able to do that in memory instead of exporting the spreadsheet data to disk, reading the exported data, and performing the queries.

I think that something like that should do:

  • read the spreadsheet from disk and display it into a TsWorkSheet grid;
  • copy the spreadsheet data to an in-memory dataset (using TMemDataset or TBufDataset);
  • perform the SQL query on the in-memory dataset;
  • load the query results in the TsWorkSheet grid;

Any examples along the above lines would be helpful.

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

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #3 on: July 29, 2021, 04:42:19 pm »
@wp,

In fact, ideally what should be really great would be a TDataSet descendent which would accept a spreadsheet, just like other data access components as TCSVDataset, TDbf, etc. This could then be associated to a datasource and to a DBGrid for display.

Just food for thought.

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

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: SQL queries in FPSpreadSheet
« Reply #4 on: July 29, 2021, 05:19:45 pm »
In fact, ideally what should be really great would be a TDataSet descendent which would accept a spreadsheet, just like other data access components as TCSVDataset, TDbf, etc. This could then be associated to a datasource and to a DBGrid for display.
Interesting idea. You mean something like a TWorksheetDataset? On the other hand, I see the basic problem that a TDataset-descendant needs strictly column-oriented data types ("fields"), but in a spreadsheet cells can contain any arbitrary data types at any place.

I think that something like that should do:

  • read the spreadsheet from disk and display it into a TsWorkSheet grid;
  • copy the spreadsheet data to an in-memory dataset (using TMemDataset or TBufDataset);
  • perform the SQL query on the in-memory dataset;
  • load the query results in the TsWorkSheet grid;
There is package ZMSQL on ccr and available also via Online-Package-Manager, which implements a descendant of TBufDataset supporting SQL. If your spreadsheet fulfills the requirements of a dataset (i.e. constant data type through-out a column) you could write the worksheet to this ZMBufDataset (in memory), perform the SQL and read the result back into a worksheet. My experience with ZMSQL, however, is very limited, and in fact it did not work for me at all in my first tests some time ago - but this does not mean anything. The author, Tatamata, is an occasional user of this forum and you certainly can contact him if you have problems.

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #5 on: July 29, 2021, 09:36:23 pm »
@wp,

Yes, something like a TWorksheetDataset would be really great! Developers should of course be informed of its requirement that values in a column would need strictly column-oriented data types (but that is a normal requirement of any descendant of TDataset anyway). If you are considering this idea, please count me in as a committed beta-tester!

I have seen a number of comments and suggestions regarding ZMSQL, but in fact I have not yet used it and therefore I am not sure that it would really be adequate to my requirements. Indeed, if possible I would prefer to stick to FPSpreadSheet, as this component is almost perfect for my application.

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

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: SQL queries in FPSpreadSheet
« Reply #6 on: August 27, 2021, 02:34:42 pm »
I began writing a TsWorksheetDataset on my github repository (https://github.com/wp-xyz/FPSpreadsheetDataset). It is some kind of adventure, I don't where it will be going, and there is some chance that I'll stop along the way and give up. At the moment the dataset can read workbooks and link to visual controls (DBGrid). Editing is possible, inserting and deleting not yet. Lookup and Locate are working, filtering not yet (and in the end it will probably be a very primitive filter using the OnFilter event). So follow the repository to see the progress, but do not report bugs at the moment, I know that there are many.

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: SQL queries in FPSpreadSheet
« Reply #7 on: August 27, 2021, 02:38:18 pm »
On a sidenote: Any spreadsheet worth its salt supports merged cells.
How would you reflect that in a TDataSet?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: SQL queries in FPSpreadSheet
« Reply #8 on: August 27, 2021, 03:14:39 pm »
Merged cells are supported by fpspreadsheet, but will not be supported in the dataset, it simply won't make sense.

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #9 on: August 27, 2021, 04:08:45 pm »
@wp,

Thanks, I will give it a try, although it seems not to be exactly (at least in its present state) what I need.

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

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #10 on: August 27, 2021, 04:23:02 pm »
@wp,

What I really would love to see implemented in FPSpreadsheet would be a filtering facility identical to that of LibreOffice (see attached screenshot), in which spreadsheet rows meeting the (multiple) filtering criteria would be displayed, while those rows not meeting such criteria would be hidden (that is, the filtering operation would be performed in the current spreadsheet, without the need of 'copying' filtered rows to another worksheet, etc.).

Could something like this be implemented in practice?

Best regards,
UCSD Pascal / Burroughs 6700 / Master Control Program
Delphi 7.0 Personal Edition
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19.1, Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: SQL queries in FPSpreadSheet
« Reply #11 on: August 27, 2021, 11:31:31 pm »
Yes, something like a TWorksheetDataset would be really great! [...] If you are considering this idea, please count me in as a committed beta-tester!

I will give it a try, although it seems not to be exactly (at least in its present state) what I need.

How do these statements fit together?

Anyway, it is a challenging programming task. Marco Cantu wrote in his "Mastering Delphi 7" that "writing a custom dataset is one of the most complex tasks for a component developer", and I want to see if it's beyond my limits. However, after having read his description in his book (http://etutorials.org/Programming/mastering+delphi+7/Part+III+Delphi+Database-Oriented+Architectures/Chapter+17+Writing+Database+Components/Building+Custom+Datasets/) and having the TMemDataset as a "template" makes it not so extremely difficult.

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #12 on: August 27, 2021, 11:40:10 pm »
@wp,

I surely want to give your new component a try, but I do not see how I can help - and how the new component can help me - at this time, since you stated that filtering is not yet implement (and filtering is what I mostly need).

Best regards,
UCSD Pascal / Burroughs 6700 / Master Control Program
Delphi 7.0 Personal Edition
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19.1, Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: SQL queries in FPSpreadSheet
« Reply #13 on: August 27, 2021, 11:56:40 pm »
What I really would love to see implemented in FPSpreadsheet would be a filtering facility identical to that of LibreOffice (see attached screenshot), in which spreadsheet rows meeting the (multiple) filtering criteria would be displayed, while those rows not meeting such criteria would be hidden (that is, the filtering operation would be performed in the current spreadsheet, without the need of 'copying' filtered rows to another worksheet, etc.).
I will definitely not add forms to the basic fpspreadsheet library. Imagine a user wants to change the arrangements of the controls on the form. Or another user cannot use the LCL controls because his entire application is based on some commercial control library and this form would break a consistent look and feel of the application.

What could be done, however, is to prepare a demo which shows how filtering could be implemented; then the user can copy the "filter dialog" form over to his application and modify it as required.

And beyond that, I am not sure if a filtering procedure should be in fpspreadsheet at all. As you saw in my examples, it is a simple programming task, and it can be further simplified by employing the fpspreadsheet search engine. If you do it yourself you can decide how to handle the filtered (hidden) rows: hide them in the originating sheet, or copy the non-filtered rows to another sheet. When filtering is a built-in function, however, all such cases must be considered and prepared. This will add code which many users will never be applying. Another sideeffect is that a filter procedure must store the information about which rows will have to be hidden.  This will further add to the memory consumption of fpspreadsheet. Or: should filtering be a method of the basic TsWorksheet, of the TsWorkbookSource, or of the grid?

So, there is a lot to consider...

maurobio

  • Hero Member
  • *****
  • Posts: 623
  • Ecology is everything.
    • GitHub
Re: SQL queries in FPSpreadSheet
« Reply #14 on: August 28, 2021, 12:06:17 am »
@wp,

I did not mean that a form should be added to the library. In fact, any query building forms should be provided by the developer of each particular application. I just offered the LibreOffice form as an example of one working filtering engine for a spreadsheet software.

What I had in mind was that a single new property ('Filter') could be added to a TWorksheet, storing an SQL-like statement which a properly implemented method would use to filter the records according to the criteria included in the filter.

Best regards,
UCSD Pascal / Burroughs 6700 / Master Control Program
Delphi 7.0 Personal Edition
Lazarus 2.0.12 - FPC 3.2.0 on GNU/Linux Mint 19.1, Lubuntu 18.04, Windows XP SP3, Windows 7 Professional, Windows 10 Home

 

TinyPortal © 2005-2018