Changes between Version 21 and Version 22 of FDORfc33

Timestamp:

Apr 15, 2009, 12:19:40 PM (15 years ago)

Author:

gregboone

Comment:

--

FDORfc33

@@ -27 +27 @@
 == Overview ==
 
-In general, the intent of SQL pass-through is to deal with physical schemas. There is no parsing of the SQL statements, and what is identified are physical schema tables, columns, functions, commands, etc. The SQL statement can be any SQL command, not just select, but any DML (select, insert, update, delete) or any DDL (create, drop, alter, …). However, FDO feature readers deal with FDO logical schemas, which is at a different conceptual level. It’s mixing these levels that causes difficulty for applications using the FDO API. Applications are required to use different code pathways to handle select statement as opposed to direct SQL execution. If clients could use the result of both these types of operations in a generic routine or component, such as a Data Table or Symbolization packages, applications would be provided a much more seemless and dynamic mechanism on which they can can build and extend their applications. 
+In general, the intent of SQL pass-through is to deal with physical schemas. There is no parsing of the SQL statements, and what is identified are physical schema tables, columns, functions, commands, etc. The SQL statement can be any SQL command, not just select, but any DML (select, insert, update, delete) or any DDL (create, drop, alter, …). However, FDO feature readers deal with FDO logical schemas, which is at a different conceptual level. It’s mixing these levels that causes difficulty for applications using the FDO API. Applications are required to use different code pathways to handle select statement as opposed to direct SQL execution. If clients could use the result of both these types of operations in a generic routine or component, such as a Data Table or Symbolization packages, applications would be provided a much more seamless and dynamic mechanism on which they can build and extend their applications. 
 
-To a certain degree, the FDO feature reader assumes an FDO class definition is directly related to the properties being returned. With physical schema SQL (let’s say just Select command), there isn’t necessarily any FDO class definition that applies. This is why currently the SQL command's Execute method returns a data reader, which handles any genericly returned data. Note that the FDO select aggregates command doesn’t return a feature reader either, since it’s returning computed data.
+To a certain degree, the FDO feature reader assumes an FDO class definition is directly related to the properties being returned. With physical schema SQL (let’s say just Select command), there isn’t necessarily any FDO class definition that applies. This is why currently the SQL command's Execute method returns a data reader, which handles any generically returned data. Note that the FDO select aggregates command doesn’t return a feature reader either, since it’s returning computed data.
 
-We can break down the SQL pass through selects into a couple of categories. One category would be selecting data that really isn’t related to any of the FDO schema classes, e.g. selecting from system tables or views. For example: select from Oracle’s mdsys.cs_srs table, select stuff from dual or SQL Server’s sys.indexes, etc. Even if we consider cases where the select is from a table that contains feature or feature related data, what does it mean to return a class definition for that data? Do we reverse engineer the select results and try to find the FDO class definition that seems to match it, that is one of the class definitions that you would get back from FDO’s describe schema? Do we just make up a class definition that may not even exist in FDO’s Describe Schema? If the select is from a single table, we possibly could find the class definition that is based on that table and use it. However, then we have to match up the columns being selected with properties in the logical schema, which may be slightly different – then we’re mixing physical schema and logical schema together (e.g. class names may not be the same as table names, property names may not be the same as column names, there may be additional computed columns, the user may not have selected the pkey columns – should we add them automatically?, etc.). Granted that in many cases, these would be virtually identical, but we can’t guarantee it and have to be able to handle the general case. Maybe we just reverse engineer a class definition from the selected data and not try to match up any existing FDO class? We would have to handle this anyway since a select may not match up at all with an existing class, such as selecting from a table with an owner that is different from the connected data store (e.g. connect to data store called Denver and select from Boulder.Roads – the schema may or may not be similar to Denver.Roads. Even worse, what happens if the user turns around and tries to update one of the returned objects – they’d have to get back to Boulder.Roads and not just try to update the FDO Roads feature class. So, this would have to be a different feature class.) It sounds like we’d manufacture a new class definition unless we could determine with certainty that the select is on a table that matches one of the already defined fdo classes. (An interesting case is “select r.* from roads r where r.length > 50”.)
+We can break down the SQL pass through selects into a couple of categories. One category would be selecting data that really isn’t related to any of the FDO schema classes, e.g. selecting from system tables or views. For example: select from Oracle’s mdsys.cs_srs table, select from dual or SQL Server’s sys.indexes, etc. Even if we consider cases where the select is from a table that contains feature or feature related data, FDO does not provide a mechanism for returning some form of a class representation that identifies that feature data and makes operating on that data consistently with other feature data. 
 
-TODO:
+One suggestion has been to reverse engineer the select results and attempt to find the FDO class definition (coming from a describe schema request) that matches it. Other suggestions have been to construct a class definition on the fly, one that would not result from a call to describe schema. If the select is from a single table, providers could find the class definition that is based on that table and use it. However, then matching up the columns being selected with the properties in the logical schema, there may be some slight differences. This would result in the mixing of physical schema and logical schema elements together. For example, class names that are not the same as table names, property names that are not the same as column names, the use of additional computed columns, inclusion of pkey columns, etc. Granted that in many cases, the logical and physical views would be virtually identical. However, since that cannot be guaranteed, the design that is adopted will have to be able to handle the general case. 
 
--- Reword away from questions. 
+To adjust for the these scenarios, providers should be modified to reverse engineer a class definition from the selected data and not attempt to match the select request to an existing FDO class. FDO Providers would be required to handle this in any case since an SQL select may not match up at all with an existing class, such as selecting from a table with an owner that is different from the connected data store. For example, Connect to data store called Denver and select from Boulder.Roads – the schema may or may not be similar to Denver.Roads. Providers should manufacture a new class definition unless they could determine with certainty that the select is on a table that matches one of the already defined FDO classes.
 
-Therefore, in cases where the resulting columns come from an existing FDO feature table, a provider can return the !FdoClassDefinition corresponding to that table. In cases where the columns come from an unknown table, a class definition can be constructed on the fly. By definition, the FDO class definition returned by an FdoIFeatureReader does not necessarily correspond exactly to an existing FDO class even in existing providers. This is due to the fact that it may only contains the properties that were asked for in the Select command, plus additional computed properties. It is perfectly legal to return a constructed class definition, which is only valid for the select that was executed, and not usable for further updates or inserts.
+Therefore, in cases where the resulting columns come from an existing FDO feature table, a provider can return the class definition corresponding to that table. In cases where the columns come from an unknown table, a class definition can be constructed on the fly. By definition, the FDO class definition returned by an feature reader does not necessarily correspond exactly to an existing FDO class even in existing providers. This is due to the fact that it may only contains the properties that were asked for in the Select command, plus additional computed properties. It is perfectly legal to return a constructed class definition, which is only valid for the select that was executed, and not usable for further updates or inserts.
+
 
 TODO: