[fdo-internals] Use of SQL pass through

Thu Jun 25 13:15:18 EDT 2009

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/20090625/7bbe56bf/attachment-0001.html