Announcement

Collapse
No announcement yet.

Ability to use functions convertToDatabaseValueSQL and convertToPHPValueSQL in ORM

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • dimyy
    commented on 's reply
    accepted, thanks

  • yuri
    commented on 's reply
    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
    replied
    With Doctrine\DBAL no problem. Little crutch for default value and build / rebuild / insert with default value ok.
    Attached Files

    Leave a comment:


  • yuri
    commented on 's reply
    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
    replied
    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

    Leave a comment:


  • dimyy
    commented on 's reply
    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)"
    Last edited by dimyy; 06-11-2024, 10:12 AM.

  • yuri
    replied
    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.

    Leave a comment:


  • yuri
    commented on 's reply
    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.
    Last edited by yuri; 06-11-2024, 09:46 AM.

  • dimyy
    commented on 's reply
    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
    commented on 's reply
    It's already possible to define 'ST_GeomFromText', 'ST_X' functions for the ORM.

  • yuri
    replied
    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.

    Leave a comment:


  • Ability to use functions convertToDatabaseValueSQL and convertToPHPValueSQL in ORM

    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
    Code:
    "indexes": {
            "geometry": {
                "columns": ["geometry"],
                "flags": ["spatial"]
            }
        }​
    Last edited by dimyy; 06-11-2024, 04:19 AM.
Working...
X