Re: Re: Spatialite Integration in Cayenne

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 <[email protected]> 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" <[email protected]>
> An: [email protected]
> 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 <[email protected]> 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