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 <ntimof...@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 <suppenkaspar...@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" <and...@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 <suppenkaspar...@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
