[fdo-internals] Use of SQL pass through

[Greg Boone]

Where are we on this thread?  Haris, can you summarize the remaining issues you have with RFC 33? We have to come to some agreement on how to proceed.

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Orest Halustchak
Sent: Thursday, June 25, 2009 1:35 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi,

Well, the goal is to get back one reader, a feature reader in this case, so that the client code is simplified. We're not specifically trying to replace both the data reader and feature reader with one reader as in your first bullet, however, maybe that is an alternative. What suggestions would you have around that? I don't want to lose the concept that a feature reader is returning "objects" based on some class definition.

Thanks,
Orest.

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
Sent: Thursday, June 25, 2009 1:15 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi Alan,
By extra processing and overhead I mean code to be executed to try to create FDO class from arbitrary SQL statement.

I am suggesting to add functions ((if missing) to retrieve column info about executed sql statement.

Most of current code won't work. You give could example: "Would select primary key" - no normal way for provider to understand it is primary key selected in arbitrary SQL statement and set it as such in FDO class.

If we are talking about use case where application would allow end-user to create arbitrary SQL to be executed and display I don't see any problem with executing SQL statement, Iterating trough columns, getting info about columns and display geometry and attributes. I really don't see point in provider in name of application iterating trough columns and try to create FDO class.


I may not understand well what is your goal but :

- if it is to have only one type of reader then I think different approach is needed.

- if it is to create application which would allow end-user to enter specific select sql statement and create fdo class for it and  later use it as other fdo classes, I think then it is up to application to create such fdo class.   Such class would be created using column info returned by reader and  in some cases with some additional interaction with end-user.

I think it is very important for FDO to be simple and exact as much as possible. Complicating FDO and providers with some specific use cases is something to be avoided.

Haris

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Alan Quinton
Sent: Thursday, June 25, 2009 6:20 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi,
   I don't understand what you mean by "extra processing" or "overhead" - do you mean memory overhead or performance or both? Because if you don't call GetClassDefinition on the feature reader, then you are not incurring anything, other having to take the trouble to write the function. And I think there is some guidance there as to how to do this, even for arbitrary columns. So yes, you would have to create this class definition object on the fly, if the client called the function, which they would only need to do once presumably, when they get the reader. So I'm not understanding where the performance problem is. Are you saying that building this class def once would be a performance problem?

As far as I'm aware, column metadata is only available from class definitions, so instead of doing that, are you suggesting FDO define an entirely new way to get column metadata? That's how I read: "My suggestion is to enable application to get info about returned columns trough feature readers".

The point is, as Orest stated, to process the results of both fdo selects and sql selects with a common reader.  Downstream code that draws or edits features and which works off FDO feature readers would not need to be modified. It would 'just work". For example, you might define an SQL select which selects the primary key and geometry from one table, then joins to other tables, using sub-selects and any features of SQL that the data store supports. If you produced a feature reader for this data, clients could draw this data, stylize it, and even potentially edit it without having to modify their code that does this since it just works off a feature reader with a "pseudo" class definition. This is a powerful capability since it essentially lets you define dynamic views of data using SQL.

Thanks,
Alan

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
Sent: Thursday, June 25, 2009 8:05 AM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Orest,

Provider would return primary key from arbitrary SQL statement ? This RFC is about executing any type of SQL statement and returning some type of FDO class for it, if so then no primary key there.

" don't need to use it if they don't want", but provider would need to do some processing for it and it eventually would not be used.

Class definition can include information which it can extract from database after executing SQL statement. Objects to be created can include this information.
Again, I don't see additional value if those information about executed SQL statement would be put inside FDO object (in many cases it wouldn't be even possible or even desirable) over what is currently posible with "regular" database drivers. I can see lot of additional work to make it possible and even than in many cases those won't be used. There is also performance penalty.

Haris

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Orest Halustchak
Sent: Thursday, June 25, 2009 4:35 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi,

But the class definition includes additional information that you wouldn't find just by getting information a property at a time. The idea with the feature reader is that it is returning "objects" based on a class definition rather than arbitrary collections of columns. The addition of getting a feature reader from the SQL command is intended to be optional for the caller, they don't need to use it if they don't want. But the class definition includes extra information such as the identity properties (primary key) so that the caller can determine the unique identity of these objects, which is important if the caller is going to draw these objects and then enable a user to edit them or go back to the data store for other information about the object.

Thanks,
Orest.

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
Sent: Thursday, June 25, 2009 10:10 AM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi,

To return FDO class from any SQL will require extra processing from provider. This processing will in best case add unnecessary overhead to provider.
My suggestion is to enable application to get info about returned columns trough feature readers. Same way as non-fdo applications are doing it right now.
I don't see any advantage in trying to squeeze SQL statement result into FDO class and I can see many problems there.

Even opposite, I would prefer to optimize FDO commands. Instead trying to guess what type of SLQ statement application has executed or parsing current FDO filters it would be better that we would have very simple "query window of data" type of command in FDO. That is something not directly related to this RFC but it is direction where I think FDO could go. Less overhead and simpler commands for most usual  tasks.

Haris

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Orest Halustchak
Sent: Thursday, June 25, 2009 3:17 PM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi,

There hasn't been too much feedback on this.

Haris, for SQL selects, to return data in a feature reader, we either can simply describe the results of the select and produce a temporary fdo class for the reader or if the SQL select happens to correspond cleanly to an existing feature class (e.g. something as simple as select * from roads), then we can use that fdo class instead. The goal is to make it easier for application developers that can use either fdo selects or sql selects and process the results with a common reader.

Anybody else have comments? I know we got some feedback from Jackie.

Thanks,
Orest.


From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Greg Boone
Sent: Thursday, June 04, 2009 11:39 AM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

Hi Haris,

Can you provide some examples of non-DDL use cases that would make the proposal too complex? I also feel we should limit the discussion at this time to read only DML statements such as Select.

Greg

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
Sent: Thursday, June 04, 2009 5:41 AM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] Use of SQL pass through

I would like to divide this and answer in two parts.

Part with Parameters direction is something we need. As it is already written , mostly to be able to execute and get results from stored procedures.

About executing SQL statements and trying to squeeze result of it inside FDO class/schema, I think it is too complex and in my mind without chance to be successful. There is so many cases in which it can't be done properly. If we are missing some info about result of execution of SQL we could look into existing specs like ODBC and add those. I agree FDO application should be able to get all necessary info about executed SQL so app can be written in generic way but I don't see putting that info in FDO class or reengineering sql etc.. I believe what can be done with api's like odbc is ok for fdo api too.

Haris

From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Orest Halustchak
Sent: Wednesday, June 03, 2009 3:28 PM
To: FDO Internals Mail List
Subject: [fdo-internals] Use of SQL pass through

Hi,

I would like to solicit feedback, discussion, and suggestions on requirements and issues that we are seeing with the FdoISQLCommand which is used for the purpose of SQL pass through for some of the RDBMS-based providers.

Please see the discussion below.

Thanks,
Orest.

... snipped ...

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-internals/attachments/20090708/3348ab65/attachment-0001.html