Ability to use functions convertToDatabaseValueSQL and convertToPHPValueSQL in ORM

Collapse
X
Collapse
+ More Options
Posts
 
  • Time
  • Show
Clear All
new posts
  • dimyy
    Active Community Member
    • Jun 2018
    • 569

    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.
  • yuri
    Member
    • Mar 2014
    • 8478

    #2
    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.
    If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

    Comment


    • yuri
      yuri commented
      Editing a comment
      It's already possible to define 'ST_GeomFromText', 'ST_X' functions for the ORM.

    • dimyy
      dimyy commented
      Editing a comment
      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
      yuri commented
      Editing a comment
      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.
  • yuri
    Member
    • Mar 2014
    • 8478

    #3
    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.
    If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

    Comment


    • dimyy
      dimyy commented
      Editing a comment
      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
      yuri commented
      Editing a comment
      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
    Active Community Member
    • Jun 2018
    • 569

    #4
    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

    Comment

    • dimyy
      Active Community Member
      • Jun 2018
      • 569

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

      Comment


      • yuri
        yuri commented
        Editing a comment
        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
        dimyy commented
        Editing a comment
        accepted, thanks
    Working...