[gdal-dev] OGR ODBC Driver and Default Enumeration

[Simon Greener]

Frank,

What sort of SQL are you using when you query Oracle and SQL Server?

What role does the INFORMATION_SCHEMA play in you changed ODBC driver? I noticed that, with the current version if I created an INFORMATION_SCHEMA and the appropriate views, ogrinfo started to work.

With Oracle, if you use the DBA_* views (eg DBA_OBJECTS) you will see tables in schemas that you may not actually have permission to read/update.
ALL_* (eg ALL_OBJECTS) will show you those the connecting user can see (eg SELECT) but may not  be able to update.

My point is this: you should really only show the tables that the connecting user can actually read/update. No point in being able to see all sorts of other table/columns if you can't actually use them.

regards
Simon

On Thu, 22 Oct 2009 16:06:11 +1100, Frank Warmerdam <warmerdam at pobox.com> wrote:

> Folks,
>
> This evening I have done some long outstanding work on the OGR ODBC driver
> to support tables within schemas reasonable.
>
>    http://trac.osgeo.org/gdal/ticket/1969
>
> Previously the default behavior of the driver was to treat all tables and
> views in the ODBC datasource as layers.  For heavy duty RDBMSes like Oracle,
> SQL Server this included all the system tables though due to the lack of
> proper schema handling the system table "layers" were not actually readable
> as normal layers.  That is now fixed.
>
> Enumerating all tables and views as layers can be quite expensive at runtime
> - it just takes several seconds to query all the field information for all
> these tables and turn it into OGRLayer objects.  In the past my advice to
> people has been to list only the tables they need in the OGR datasource
> name they connect with.
>
> eg.
> ODBC:MyDB,table1,table
>
> This ensures that only the listed tables are queried for details and
> speeds things up substantially.  However, I am now wondering if it would
> be better to only enumerate tables in the empty/default schema by
> default if no table list is provided in the datasource string.  This would
> dramatically speed up the connection speed for Oracle, SQL Server, etc,
> and also keep huge numbers of useless layers out of people faces in GUI
> apps like QGIS.  The main downside is that it would be substantially harder
> to find out about potentially useful system tables via the OGR API though
> they could still be accessed by listing them in the table list, or
> pass through SQL queries.
>
> Does anyone have any opinion on this?  Feel free to reply here, drop me a
> note, or add a note to the ticket.
>
> Best regards,


-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
   Email: simon at spatialdbadvisor.com
   Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3