Ability to use functions convertToDatabaseValueSQL and convertToPHPValueSQL in ORM

[dimyy]

To use spatial DB functions, you need the ability to save values

1. Use default values defined by functions: for example, "default" : "POINT(1,1)". This is currently impossible - values are necessarily escaped (except for boolean, integer and real) https://github.com/espocrm/espocrm/b...oser.php#L2266

(external example of using the specified functions https://github.com/jsor/doctrine-pos...ostGISType.php )

2. Use the corresponding functions in selects by default (I believe this can be bypassed by defining the list of functions and specifying the select in entityDefs) - I have not tested this for stored fields

I believe it would be logical to pass this through the corresponding functions as is done in doctrine/dbal, but it is not currently used at the BaseQueryComposer level.
Now it is possible to redefine the function quote but the field parameter is not passed to it and the only option is to determine this empirically by the presence of something in the passed value, which is clearly wrong from a security point of view



Addition: To use a spatial index, you don't need to add anything - just specify the flag in the index definition

"indexes": {
        "geometry": {
            "columns": ["geometry"],
            "flags": ["spatial"]
        }
    }​

[yuri]

Maybe we need a mechanism of mapping of entity's attributes to specific complex expressions. E.g. an entity contains a value 'POINT(1,1)', it maps to a needed complex expression whenever an attribute is saved. This is for having the ability to store spatial data directly from entities. But needs more investigation.

[yuri]

It's already possible to define 'ST_GeomFromText', 'ST_X' functions for the ORM.

[dimyy]

Off course I can define custom functions.
How to use it by default when we try save entity?
How can I define cdefault value using function?
I don't find this ability

[yuri]

My comment was about this ability missing and that we would need to design it first. The ability to map an attribute value to an expression that will be used in an SQL.

[yuri]

As spatial data values may be represented as strings, an entity attribute could be a regular string. But some parameter of that attribute will indicate the Query Composer that the value should be converted to a specific complex expression.

[dimyy]

But if the spatial data is a string (or JSON) in the database, we can't use a spatial index. And the spatial index is the key point.

Entity attribute of course may be string or text. And also this attribute
And also this attribute will most likely be read-only, since we receive data from geocoding services or from map data and do not directly form strings like "point(0 0)"

[yuri]

In database it will a spatial type (if Doctrine\DBAL supports it, I don't remember).

> And also this attribute will most likely be read-only, since we receive data from geocoding services or from map data and do not directly form strings like "point(0 0)"

I think there may be use cases where update is required.

[dimyy]

We integrated the leaflet, added geocoding at the back-end level, and perhaps a couple of things are missing: geoindices and expression-based indices. If you can live without the second, then without the first it is very difficult to build full-fledged work with geodata

[dimyy]

With Doctrine\DBAL no problem. Little crutch for default value and build / rebuild / insert with default value ok.

[yuri]

BTW I'd recommend to use another names for custom DB types. E.g. myGeometry, myPoint. For the case we ever add a geometry type, to avoid your code breaking due the conflict.

[dimyy]

accepted, thanks