[fdo-internals] SQLite provider issues

Wed Jul 28 15:37:48 EDT 2010

2) you can change code so it will not set class to non-writable

1) whole view is cached in memory ? sounds quite a strange, still cant
clear cache on writes ?
   I may look into code and find a solution.

Haris

On Wed, Jul 28, 2010 at 9:18 PM, Romica Dascalescu
<Romica.Dascalescu at autodesk.com> wrote:
> Hi Haris,
>
> 1) There are many issues with views... We need a spatial index for views in order to be able to use spatial functions and SI is based on rowid, this is not easy to fix. This is one of the reasons views were cached. There can be views which cannot be easily made query able...
>
> 2) Not all view can be updated! Only views which have INSTEAD OF triggers. One way to fix this is to check if trigger exist.
> I did not known 3.4 exposed views, looking at code we did not supported views:
> https://svn.osgeo.org/fdo/branches/3.4/Providers/SQLite/Provider/SltMetadata.cpp
>    /* Locate the table in question */
>    m_table = sqlite3FindTable(db, name, 0);
>    if( !m_table || m_table->pSelect )
>    {
>        m_table = 0;
>        goto error_out;
>    }
>
> 3) This can be fixed.
>
> Thanks,
> Romy.
>
> -----Original Message-----
> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
> Sent: Wednesday, July 28, 2010 2:28 PM
> To: FDO Internals Mail List
> Subject: Re: [fdo-internals] SQLite provider issues
>
> Views from sqlite are exposed as fdo classes right now, so yes fdo
> sqlite suports views from sqlite.
> But there are at least three bugs in fdo sqlite right now regarding
> views which makes them unusable:
>
> 1. If underlaying table is changed  that is not seen in view. That was
> reported by my colleagues. Haven't yet looked what is reason here, I
> assume dual connection which is used in provider (perhaps combined
> with some caching)
>
> 2. FDO class generated from view is marked as non-writable. That is
> wrong assumption that views are not updatble in sqlite. Provider would
> just leave writable==true ( as it was in 3.4 version)
>
> 3. FDO class generated from view is marked as computed which is wrong,
> as I describe in emails before.
>
> Some of those bugs are introduced after 3.4 , in changeset which
> number I wrote in email before.
>
> Haris
>
>
> On Wed, Jul 28, 2010 at 8:08 PM, Greg Boone <greg.boone at autodesk.com> wrote:
>> Before we change anything, I would like further clarification on the view issue. It seems partially supported at the least. What is the intent here. Are we intending to support read from views? Should a user be attempting any action other than read?
>>
>> To me, if we support read from views, the update to the underlying table not being reflected in the view is a bug.
>>
>> Greg
>>
>> -----Original Message-----
>> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
>> Sent: Wednesday, July 28, 2010 1:03 PM
>> To: FDO Internals Mail List
>> Subject: Re: [fdo-internals] SQLite provider issues
>>
>> I don't understand your answers.
>>
>> If it is ok , I will fix sqlite so views are not marked as not
>> computed and non-writable.
>>
>> Haris
>>
>> On Wed, Jul 28, 2010 at 4:17 PM, Romica Dascalescu
>> <Romica.Dascalescu at autodesk.com> wrote:
>>> SQLite provider 3.5 don't really supports views.
>>>
>>> Romy
>>> -----Original Message-----
>>> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Romica Dascalescu
>>> Sent: Wednesday, July 28, 2010 10:16 AM
>>> To: FDO Internals Mail List
>>> Subject: RE: [fdo-internals] SQLite provider issues
>>>
>>> Views are cached in FDO 3.5 and are not updated when class gets updated.
>>>
>>> Romy.
>>> -----Original Message-----
>>> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
>>> Sent: Wednesday, July 28, 2010 10:14 AM
>>> To: FDO Internals Mail List
>>> Subject: Re: [fdo-internals] SQLite provider issues
>>>
>>> Hi Romica
>>>
>>> 1) Computed class as described in FDO
>>>     "Computed classes cannot be made persistent or added to FDO
>>> schema. The computed classes are used as a transient classes that can
>>> be returned by a feature or data reader."
>>>     I thin it is pretty clear from above that it is bug in sqlite provider.
>>>
>>> 2) It is not limitation of provider but bug.
>>>    If sqlte contains updatable views they will be reported as
>>> read-only buy provider. Proivder will wrongly limit sqlite.
>>>
>>>
>>> 3) It seems even bigger problems with views (Simon is testing this).
>>> If original table is updated view is not. Provider returns wrong
>>> results. I suspect some issue with dual-connection nature of sqlite
>>> provider and/or caching.
>>>
>>> If that will be case for non-sql providers, to update even for
>>> non-writable classes, file would be updated trough provider which
>>> would be wrong while provider said it is not updateble
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Jul 28, 2010 at 3:48 PM, Romica Dascalescu
>>> <Romica.Dascalescu at autodesk.com> wrote:
>>>> Hi Haris,
>>>>
>>>> 1) SetIsComputed(), there is a thin line here since a view is a computed/generated class. Why this would be a problems in this case?
>>>>
>>>> 2) You can say this is a limitation of the provider and we cannot update views. However there is a way to update a view if Id of the view and geometry is from the same class and those are defined in (fdo) metadata (but this is in FDO 3.6 only).
>>>> All the time SupporsWrite()=false for views and I don't see a problems since the provider is not meant to implement everything.
>>>>
>>>> SupporsWrite() will be false in case tythe file is read only and all classes will have this flag set to false. This flag should be checked by the application and take care of this otherwise exception will be thrown but only later at execute time.
>>>>
>>>> SQLite provider do not enforce too many things (starting with string length and ending with read-only flag), and in case an application ignore this flag and use a SQL commands to update database there is nothing provider will do, since provider will not parse the SQL! The sqlite engine for sure will throw an exception at execution time.
>>>>
>>>> Thanks,
>>>> Romy.
>>>>
>>>> -----Original Message-----
>>>> From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Haris Kurtagic
>>>> Sent: Wednesday, July 28, 2010 9:25 AM
>>>> To: FDO Internals Mail List
>>>> Subject: [fdo-internals] SQLite provider issues
>>>>
>>>> Hi,
>>>>
>>>> FDO Sqlite provider has changed in revision 4823 ( from 3.4 to 3.5 )
>>>> regarding views in sqlite.
>>>>
>>>> I think it introduced two issues:
>>>>
>>>> 1. Provider will mark fdo class generated from view as computed
>>>> "SetIsComputed(true)" which is wrong by fdo definition of computed
>>>> class.
>>>>
>>>> 2. SupportsWrite is set to false - which is wrong. SQlite suports
>>>> write to views when they have "instead of" trigger
>>>>
>>>>
>>>> Also there is yet another problem of understanding what SupporsWrite
>>>> means in FDO.
>>>> Is it just informative or will enforce it.
>>>> Right now, sqlite provider will pass trough update commands to sqlite
>>>> even if provider marked some class as "doesn't support write".
>>>> I think if class is marked as non-writable by provider shouldn't send
>>>> update commands to underlaying storage.
>>>>
>>>> Haris
>>>> _______________________________________________
>>>> fdo-internals mailing list
>>>> fdo-internals at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>>>> _______________________________________________
>>>> fdo-internals mailing list
>>>> fdo-internals at lists.osgeo.org
>>>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>>>>
>>> _______________________________________________
>>> fdo-internals mailing list
>>> fdo-internals at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>>> _______________________________________________
>>> fdo-internals mailing list
>>> fdo-internals at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>>> _______________________________________________
>>> fdo-internals mailing list
>>> fdo-internals at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>>>
>> _______________________________________________
>> fdo-internals mailing list
>> fdo-internals at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>> _______________________________________________
>> fdo-internals mailing list
>> fdo-internals at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>>
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
> _______________________________________________
> fdo-internals mailing list
> fdo-internals at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/fdo-internals
>