Sqlite provider failed on view with litterals

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Sqlite provider failed on view with litterals

Bruno Scott
Hi all,

I want to use some king of view design like this:
CREATE TEMP VIEW foo AS
    SELECT *, 'a' AS origin FROM a.foo
    UNION
    SELECT *, 'b' AS origin FROM b.foo;
the 3.6 and 3.7 provider can't list columns for this view.
The problem is in the sqlite api
PRAGMA table_info(foo);
it failes returning an error "column a does not exist"

This api should work, see http://www.mail-archive.com/sqlite-users@sqlite.org/msg12590.html

This is maybe because the sqlite provider use an old version of the sqlite library!
I know the sqlite library used in the provider has been modified.
This meen it's not trivial to upgrade to a newer version.
Would it be possible to upgrade the sqlite library to a newer version or we gone to live with this old one for ever?

Bruno Scott

Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Romica Dascalescu
Hi Bruno,

SQLite 3.7 provider should work against this view and should return the class definition.
In case it's not, could you try to make the view not-temporary and try again?
In case it works then you might hit a bug in the provider with temp views...

Thanks,
Romy.
________________________________________
From: [hidden email] [[hidden email]] on behalf of Bruno Scott [[hidden email]]
Sent: Wednesday, June 20, 2012 5:11 AM
To: [hidden email]
Subject: [fdo-internals] Sqlite provider failed on view with litterals

Hi all,

I want to use some king of view design like this:
CREATE TEMP VIEW foo AS
    SELECT *, 'a' AS origin FROM a.foo
    UNION
    SELECT *, 'b' AS origin FROM b.foo;
the 3.6 and 3.7 provider can't list columns for this view.
The problem is in the sqlite api
PRAGMA table_info(foo);
it failes returning an error "column *a* does not exist"

This api should work, see
http://www.mail-archive.com/sqlite-users@.../msg12590.html
http://www.mail-archive.com/sqlite-users@.../msg12590.html

This is maybe because the sqlite provider use an old version of the sqlite
library!
I know the sqlite library used in the provider has been modified.
This meen it's not trivial to upgrade to a newer version.
Would it be possible to upgrade the sqlite library to a newer version or we
gone to live with this old one for ever?

Bruno Scott



--
View this message in context: http://osgeo-org.1560.n6.nabble.com/Sqlite-provider-failed-on-view-with-litterals-tp4982600.html
Sent from the FDO Internals mailing list archive at Nabble.com.
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Bruno Scott
Hi Romy
The problem is not related to temp view
Standard view has the same problem
CREATE VIEW foo AS
    SELECT *, 'a' AS origin FROM a.foo
    UNION
    SELECT *, 'b' AS origin FROM b.foo;
The problem is when using a fake litteral column like 'a' or 'b' in my example

I've tried with AIMS 2013 witch is using some 3.7 build and it does not even list the view in the available classes

Bruno
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Romica Dascalescu
Hi Bruno,

I generated a SQLite file and tested your view. I could see the view as a non-feature class.
In case I add a row in geometry_columns table, I can see it as a feature class and even I can select from it.
In order to see a view as a feature class you need to specify in geometry_columns the geometry column(s) and optional is to add in fdo_columns the primary key.
There is no problem with view properties specified as you did. I attached my sample, and you can try it out.

Thanks,
Romy.
________________________________________
From: [hidden email] [[hidden email]] on behalf of Bruno Scott [[hidden email]]
Sent: Wednesday, June 20, 2012 9:58 AM
To: [hidden email]
Subject: Re: [fdo-internals] Sqlite provider failed on view with litterals

Hi Romy
The problem is not related to temp view
Standard view has the same problem
CREATE VIEW foo AS
    SELECT *, 'a' AS origin FROM a.foo
    UNION
    SELECT *, 'b' AS origin FROM b.foo;
The problem is when using a fake litteral column like 'a' or 'b' in my
example

I've tried with AIMS 2013 witch is using some 3.7 build and it does not even
list the view in the available classes

Bruno

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/Sqlite-provider-failed-on-view-with-litterals-tp4982600p4982698.html
Sent from the FDO Internals mailing list archive at Nabble.com.
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals

Test.sqlite (262K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Bruno Scott
Hi Romy
I've just tried your sqlite test file in AIMS 2013
I created a new sqlite feature source with Autodesk Infrastructure Studio 2013
Test connection -> OK
Then i hit preview and refresh
I only see fc_a and fc_b

Maybe we are not using the same fdo version?

Thanks
Bruno
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Romica Dascalescu
Which version of FDO you are using?
I did test it on 3.8 :), however I expect 3.7 to be the same since there are no major changes between versions.

Romy.
________________________________________
From: [hidden email] [[hidden email]] on behalf of Bruno Scott [[hidden email]]
Sent: Wednesday, June 20, 2012 10:30 AM
To: [hidden email]
Subject: Re: [fdo-internals] Sqlite provider failed on view with litterals

Hi Romy
I've just tried your sqlite test file in AIMS 2013
I created a new sqlite feature source with Autodesk Infrastructure Studio
2013
Test connection -> OK
Then i hit preview and refresh
I only see fc_a and fc_b

Maybe we are not using the same fdo version?

Thanks
Bruno

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/Sqlite-provider-failed-on-view-with-litterals-tp4982600p4982716.html
Sent from the FDO Internals mailing list archive at Nabble.com.
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Bruno Scott
I'm using the one delivered with AIMS 2013,
Most of the files are tagged with 3.7.0.6001
but the basic one like fdo.dll,fdoCommon?dll,fdoGeometry.dll are tagged with 3.7.0.0
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Romica Dascalescu
That's really strange since I've tested with Map 3D using FDO 3.7 and it works, I can see the class and view content.
In addition I tested with a standalone application and I can see foo class.

Romy.
________________________________________
From: [hidden email] [[hidden email]] on behalf of Bruno Scott [[hidden email]]
Sent: Wednesday, June 20, 2012 10:40 AM
To: [hidden email]
Subject: Re: [fdo-internals] Sqlite provider failed on view with litterals

I'm using the one delivered with AIMS 2013,
Most of the files are tagged with 3.7.0.6001
but the basic one like fdo.dll,fdoCommon?dll,fdoGeometry.dll are tagged with
3.7.0.0

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/Sqlite-provider-failed-on-view-with-litterals-tp4982600p4982729.html
Sent from the FDO Internals mailing list archive at Nabble.com.
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Bruno Scott
Hi Romy
I've just tried with Map3D and it's true i can see the view
And it's the same FDO, that's strange.
Problem would be on Mapguide?
I will try a debug on the Map3D Fdo 3.7 just not to die dummy:)

Thanks for your support Romy,
I will continu investigating...

Bruno
Reply | Threaded
Open this post in threaded view
|

Re: Sqlite provider failed on view with litterals

Bruno Scott
Hi Romy, i have found the problem
The exact syntax for my view was

CREATE VIEW foo AS
    SELECT *, "a" AS origin FROM a.foo
    UNION
    SELECT *, "b" AS origin FROM b.foo;

instead of
CREATE VIEW foo AS
    SELECT *, 'a' AS origin FROM a.foo
    UNION
    SELECT *, 'b' AS origin FROM b.foo;

"a" means the column a, so this raise an error
The disturding thing is when using sqlite studio, it works fine without any warning...

Anyway, thanks a lot for your help Romy

Bruno Scott