You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Inayah Max <su...@gmx.de> on 2021/01/23 20:18:48 UTC
Spatialite Integration in Cayenne
Hi all,
my issue is related to the recently added support for geospatial types in Cayenne. As I understand it, Mysql and Postgres spatial extensions are already integrated in 4.2M2.
This is great but doesn't fit to my tech stack. My lightweighted application has to use a filebased database (spatialite) and can't rely on a server based solution.
Spatialite is an SQlite extention that adds spatial functionality to SQLite in the same way like Postgis is doing for Postgres.
My current progress is that Cayenne can use connect to a spatialite database via JDBC (using jdbcUrl: jdbc:sqlite:file.db?enable_load_extension=true and SQLSelect.dataRowQuery("SELECT load_extension('mod_spatialite');").select(context);). But all queries fail since the ST-Functions are not implemented yet.
When looking at Cayenne spatial implementation, WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor.
Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required.
In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor. Having no registerColumnProcess, the WKT convert can't take place in the same way like before.
Let me know if someone has an idea how to overcome this problem.
Thanks.
Re: Spatialite Integration in Cayenne
Posted by Andrus Adamchik <an...@objectstyle.org>.
We could switch all processors to the TypeAwareSQLTreeProcessor, there
> shouldn't be any issues.
> It's all about some free time to do it :)
Awesome! I am +1 to do it when we have a chance.
And eager to hear from Max on how it ultimately worked with SQLite. Hoping we'll be able to integrate the full solution in Cayenne.
Andrus
> On Jan 26, 2021, at 1:57 PM, Nikita Timofeev <nt...@objectstyle.com> wrote:
>
> Hi Inayah,
>
> You could wrap the SRID value to ValueNode in order for the SQL
> translator to generate a binding for the prepared statement.
> It would look something like this if you are using TypeAwareSQLTreeProcessor:
>
> registerValueProcessor(Wkt.class, (parent, child, i) -> {
> Node geomFromText = FunctionNode.wrap(child, "GeomFromText");
> geomFromText.addChild(new ValueNode(SRID, false, null));
> return Optional.of(geomFromText);
> }
>
> @Andrus
> We could switch all processors to the TypeAwareSQLTreeProcessor, there
> shouldn't be any issues.
> It's all about some free time to do it :)
>
> On Mon, Jan 25, 2021 at 6:40 PM Inayah Max <su...@gmx.de> wrote:
>>
>> Hi Andrus,
>> thanks for your help. I made some progress by adding an own SpatialiteProcessor. But now a new challenge is coming up.
>> Spatialite forces to define a SRID within a GeomFromText-Functioncall.
>>
>>
>> The current wrapInFunction-method is implemented like this:
>>
>> "INSERT INTO tabname (geom) VALUES (GeomFromText (?))";
>>
>> To put the SRID inside the WRT-String is not working since the SQL engine is unaware that we intend using a SQL function.
>> Consequently it will handle the corresponding argument just as a plain text string.
>> Next an example insert query (SRID = 4236) that should work:
>>
>> "INSERT INTO tabname (geom) VALUES (GeomFromText (?,4236))";
>> "INSERT INTO tabname (geom) VALUES (GeomFromText (?, ?),4236)"
>>
>>
>> Is it possible to add a second numeric parameter to the prepared statement?
>>
>> Kind regards,
>> Max
>>
>>
>> Gesendet: Sonntag, 24. Januar 2021 um 08:17 Uhr
>> Von: "Andrus Adamchik" <an...@objectstyle.org>
>> An: user@cayenne.apache.org
>> Betreff: Re: Spatialite Integration in Cayenne
>> Hi Inayah,
>>
>> As you noticed, spatial features are still new in Cayenne, and we will need to fill more than a few gaps. So thanks for your feedback. This will help us to prioritize our effort in this area.
>>
>>> WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor. Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required. In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor.
>>
>> Good point.
>>
>> @Nikita: Is there a reason not to use TypeAwareSQLTreeProcessor as a superclass in all adapters? IIRC there's a minor performance hit, but looks like still worth it.
>>
>> Andrus
>>
>>> On Jan 23, 2021, at 11:18 PM, Inayah Max <su...@gmx.de> wrote:
>>>
>>> Hi all,
>>> my issue is related to the recently added support for geospatial types in Cayenne. As I understand it, Mysql and Postgres spatial extensions are already integrated in 4.2M2.
>>> This is great but doesn't fit to my tech stack. My lightweighted application has to use a filebased database (spatialite) and can't rely on a server based solution.
>>> Spatialite is an SQlite extention that adds spatial functionality to SQLite in the same way like Postgis is doing for Postgres.
>>> My current progress is that Cayenne can use connect to a spatialite database via JDBC (using jdbcUrl: jdbc:sqlite:file.db?enable_load_extension=true and SQLSelect.dataRowQuery("SELECT load_extension('mod_spatialite');").select(context);). But all queries fail since the ST-Functions are not implemented yet.
>>>
>>> When looking at Cayenne spatial implementation, WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor.
>>> Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required.
>>> In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor. Having no registerColumnProcess, the WKT convert can't take place in the same way like before.
>>> Let me know if someone has an idea how to overcome this problem.
>>> Thanks.
>>
>
>
>
> --
> Best regards,
> Nikita Timofeev
Re: Re: Spatialite Integration in Cayenne
Posted by Nikita Timofeev <nt...@objectstyle.com>.
Hi Inayah,
You could wrap the SRID value to ValueNode in order for the SQL
translator to generate a binding for the prepared statement.
It would look something like this if you are using TypeAwareSQLTreeProcessor:
registerValueProcessor(Wkt.class, (parent, child, i) -> {
Node geomFromText = FunctionNode.wrap(child, "GeomFromText");
geomFromText.addChild(new ValueNode(SRID, false, null));
return Optional.of(geomFromText);
}
@Andrus
We could switch all processors to the TypeAwareSQLTreeProcessor, there
shouldn't be any issues.
It's all about some free time to do it :)
On Mon, Jan 25, 2021 at 6:40 PM Inayah Max <su...@gmx.de> wrote:
>
> Hi Andrus,
> thanks for your help. I made some progress by adding an own SpatialiteProcessor. But now a new challenge is coming up.
> Spatialite forces to define a SRID within a GeomFromText-Functioncall.
>
>
> The current wrapInFunction-method is implemented like this:
>
> "INSERT INTO tabname (geom) VALUES (GeomFromText (?))";
>
> To put the SRID inside the WRT-String is not working since the SQL engine is unaware that we intend using a SQL function.
> Consequently it will handle the corresponding argument just as a plain text string.
> Next an example insert query (SRID = 4236) that should work:
>
> "INSERT INTO tabname (geom) VALUES (GeomFromText (?,4236))";
> "INSERT INTO tabname (geom) VALUES (GeomFromText (?, ?),4236)"
>
>
> Is it possible to add a second numeric parameter to the prepared statement?
>
> Kind regards,
> Max
>
>
> Gesendet: Sonntag, 24. Januar 2021 um 08:17 Uhr
> Von: "Andrus Adamchik" <an...@objectstyle.org>
> An: user@cayenne.apache.org
> Betreff: Re: Spatialite Integration in Cayenne
> Hi Inayah,
>
> As you noticed, spatial features are still new in Cayenne, and we will need to fill more than a few gaps. So thanks for your feedback. This will help us to prioritize our effort in this area.
>
> > WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor. Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required. In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor.
>
> Good point.
>
> @Nikita: Is there a reason not to use TypeAwareSQLTreeProcessor as a superclass in all adapters? IIRC there's a minor performance hit, but looks like still worth it.
>
> Andrus
>
> > On Jan 23, 2021, at 11:18 PM, Inayah Max <su...@gmx.de> wrote:
> >
> > Hi all,
> > my issue is related to the recently added support for geospatial types in Cayenne. As I understand it, Mysql and Postgres spatial extensions are already integrated in 4.2M2.
> > This is great but doesn't fit to my tech stack. My lightweighted application has to use a filebased database (spatialite) and can't rely on a server based solution.
> > Spatialite is an SQlite extention that adds spatial functionality to SQLite in the same way like Postgis is doing for Postgres.
> > My current progress is that Cayenne can use connect to a spatialite database via JDBC (using jdbcUrl: jdbc:sqlite:file.db?enable_load_extension=true and SQLSelect.dataRowQuery("SELECT load_extension('mod_spatialite');").select(context);). But all queries fail since the ST-Functions are not implemented yet.
> >
> > When looking at Cayenne spatial implementation, WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor.
> > Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required.
> > In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor. Having no registerColumnProcess, the WKT convert can't take place in the same way like before.
> > Let me know if someone has an idea how to overcome this problem.
> > Thanks.
>
--
Best regards,
Nikita Timofeev
Aw: Re: Spatialite Integration in Cayenne
Posted by Inayah Max <su...@gmx.de>.
Hi Andrus,
thanks for your help. I made some progress by adding an own SpatialiteProcessor. But now a new challenge is coming up.
Spatialite forces to define a SRID within a GeomFromText-Functioncall.
The current wrapInFunction-method is implemented like this:
"INSERT INTO tabname (geom) VALUES (GeomFromText (?))";
To put the SRID inside the WRT-String is not working since the SQL engine is unaware that we intend using a SQL function.
Consequently it will handle the corresponding argument just as a plain text string.
Next an example insert query (SRID = 4236) that should work:
"INSERT INTO tabname (geom) VALUES (GeomFromText (?,4236))";
"INSERT INTO tabname (geom) VALUES (GeomFromText (?, ?),4236)"
Is it possible to add a second numeric parameter to the prepared statement?
Kind regards,
Max
Gesendet: Sonntag, 24. Januar 2021 um 08:17 Uhr
Von: "Andrus Adamchik" <an...@objectstyle.org>
An: user@cayenne.apache.org
Betreff: Re: Spatialite Integration in Cayenne
Hi Inayah,
As you noticed, spatial features are still new in Cayenne, and we will need to fill more than a few gaps. So thanks for your feedback. This will help us to prioritize our effort in this area.
> WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor. Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required. In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor.
Good point.
@Nikita: Is there a reason not to use TypeAwareSQLTreeProcessor as a superclass in all adapters? IIRC there's a minor performance hit, but looks like still worth it.
Andrus
> On Jan 23, 2021, at 11:18 PM, Inayah Max <su...@gmx.de> wrote:
>
> Hi all,
> my issue is related to the recently added support for geospatial types in Cayenne. As I understand it, Mysql and Postgres spatial extensions are already integrated in 4.2M2.
> This is great but doesn't fit to my tech stack. My lightweighted application has to use a filebased database (spatialite) and can't rely on a server based solution.
> Spatialite is an SQlite extention that adds spatial functionality to SQLite in the same way like Postgis is doing for Postgres.
> My current progress is that Cayenne can use connect to a spatialite database via JDBC (using jdbcUrl: jdbc:sqlite:file.db?enable_load_extension=true and SQLSelect.dataRowQuery("SELECT load_extension('mod_spatialite');").select(context);). But all queries fail since the ST-Functions are not implemented yet.
>
> When looking at Cayenne spatial implementation, WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor.
> Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required.
> In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor. Having no registerColumnProcess, the WKT convert can't take place in the same way like before.
> Let me know if someone has an idea how to overcome this problem.
> Thanks.
Re: Spatialite Integration in Cayenne
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Inayah,
As you noticed, spatial features are still new in Cayenne, and we will need to fill more than a few gaps. So thanks for your feedback. This will help us to prioritize our effort in this area.
> WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor. Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required. In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor.
Good point.
@Nikita: Is there a reason not to use TypeAwareSQLTreeProcessor as a superclass in all adapters? IIRC there's a minor performance hit, but looks like still worth it.
Andrus
> On Jan 23, 2021, at 11:18 PM, Inayah Max <su...@gmx.de> wrote:
>
> Hi all,
> my issue is related to the recently added support for geospatial types in Cayenne. As I understand it, Mysql and Postgres spatial extensions are already integrated in 4.2M2.
> This is great but doesn't fit to my tech stack. My lightweighted application has to use a filebased database (spatialite) and can't rely on a server based solution.
> Spatialite is an SQlite extention that adds spatial functionality to SQLite in the same way like Postgis is doing for Postgres.
> My current progress is that Cayenne can use connect to a spatialite database via JDBC (using jdbcUrl: jdbc:sqlite:file.db?enable_load_extension=true and SQLSelect.dataRowQuery("SELECT load_extension('mod_spatialite');").select(context);). But all queries fail since the ST-Functions are not implemented yet.
>
> When looking at Cayenne spatial implementation, WKT wrapper were added to the MySQLTreeProcessor and PostgreSQLTreeProcessor.
> Both Processors extend a TypeAwareSQLTreeProcessor and and add the "ST_"-Convert commands as required.
> In contrast the current SQLiteTreeProcessor extends BaseSQLTreeProcessor. Having no registerColumnProcess, the WKT convert can't take place in the same way like before.
> Let me know if someone has an idea how to overcome this problem.
> Thanks.