[NFR]: Support POINT column type in MySQL

[jesugmz]

Describe the bug
Having a MySQL column with type POINT the reading of its value is not well retrieved.

To Reproduce

Phalcon Micro application:

<?php

use Phalcon\Db\Adapter\Pdo\Mysql;
use Phalcon\Db\RawValue;
use Phalcon\Di\FactoryDefault;
use Phalcon\Http\Response;
use Phalcon\Mvc\Micro;
use Phalcon\Mvc\Model;

class Items extends Model
{
    /**
     * @var integer
     * @Primary
     * @Identity
     * @Column(type="integer", length=3, nullable=false)
     */
    public $id;

    /**
     * @var string
     * @Column(type="string", length=60, nullable=false)
     */
    public $location;

    public function initialize()
    {
        $this->setSchema('test');
        $this->setSource('items');
    }
}

$di = new FactoryDefault();
$di->setShared('db', function () {
    return new Mysql([
        'host'     => 'my-host',
        'username' => 'root',
        'password' => 'root',
        'dbname'   => 'test',
        'charset'  => 'utf8',
    ]);
});

$app = new Micro($di);

// broken
$app->get('/items/{id:[\d]}', function ($id) use ($app) {
  $item = Items::findFirst('id = '.$id);
  return $this->response->setJsonContent($item);
});

// works
$app->post('/items', function () use ($app) {
  $item = new Items();
  $item->location = new RawValue('ST_GeomFromText("POINT(1.0 1.0)")');
  $created = $item->save();

  return $created ? $this->response->setStatusCode(201) : $this->response->setStatusCode(500);
});

// works
$app->put('/items/{id:[\d]}', function ($id) use ($app) {
  $item = Items::findFirst('id = '.$id);
  $item->location = new RawValue('ST_GeomFromText("POINT(5.0 5.0)")');
  //$item->location = $item->location; // this will break as the the reading is broken
  $updated = $item->update();

  return $updated ? $this->response->setStatusCode(200) : $this->response->setStatusCode(500);
});

$app->notFound(function() {
    $response = new Response();
    return $response->setStatusCode(404);
});

$app->handle();

Database schema:

CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test;

CREATE TABLE items (
  id MEDIUMINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  location POINT NOT NULL,
  PRIMARY KEY (id),
  SPATIAL INDEX (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

POST and PUT (writing a POINT column) operations will works but GET (reading a POINT column) will return a response like this:

{"id":"3","location":"\u0000\u0000\u0000\u0000\u0001\u0001\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0014@\u0000\u0000\u0000\u0000\u0000\u0000\u0014@"}

In Phalcon 3.4.4 the reading works well but not the writing which cannot modify the POINT column.

Details

• Phalcon version: 4.0.2
• PHP Version: 7.4.2
• Operating System: Debian 10.2
• Installation type: PECL
• Server: Nginx
• MySQL: 5.7.21-20

[ruudboon]

I was able to reproduce this locally.

[ruudboon]

The POINT datatype isn't in our Column definition so it defaults to varchar. Probably it should be handled differently. I was about that say this is a feature request but it looks like we had this in before. #1536
Not sure why this failing in 4 and was working in 3.4 don't see any changes related to this.

[ruudboon]

What would your expected outcome will look like @jesugmz ?
Not familiar with mysql spacial types but looks like this works.

unpack('x/x/x/x/corder/Ltype/dlat/dlon', $item->location)

[jesugmz]

It makes sense, POINT type is binary and works in that way so more than fine @ruudboon!

But still exists the issue when updating. Seems adding $this->useDynamicUpdate(true); in the initialize method of the model is ignored.

Note I have added a new field name:

DROP DATABASE test;
CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test;

CREATE TABLE items (
  id MEDIUMINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  location POINT NOT NULL,
  PRIMARY KEY (id),
  SPATIAL INDEX (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<?php

use Phalcon\Db\Adapter\Pdo\Mysql;
use Phalcon\Db\RawValue;
use Phalcon\Di\FactoryDefault;
use Phalcon\Http\Response;
use Phalcon\Mvc\Micro;
use Phalcon\Mvc\Model;

class Items extends Model
{
    /**
     * @var integer
     * @Primary
     * @Identity
     * @Column(type="integer", length=3, nullable=false)
     */
    public $id;

    /**
     * @var string
     * @Column(type="string", length=50, nullable=true)
     */
    public $name;

    /**
     * @var string
     * @Column(type="string", length=60, nullable=false)
     */
    public $location;

    public function initialize()
    {
        $this->setSchema('test');
        $this->setSource('items');
        $this->useDynamicUpdate(true);
    }
}

$di = new FactoryDefault();
$di->setShared('db', function () {
    return new Mysql([
        'host'     => 'my-host',
        'username' => 'root',
        'password' => 'root',
        'dbname'   => 'test',
        'charset'  => 'utf8',
    ]);
});

$app = new Micro($di);

$app->get('/items/{id:[\d]}', function ($id) use ($app) {
  $item = Items::findFirst('id = '.$id);
  $coordinates = unpack('x/x/x/x/corder/Ltype/dlat/dlon', $item->location);
  return $this->response->setJsonContent($coordinates);
});

$app->post('/items', function () use ($app) {
  $item = new Items();
  $item->location = new RawValue('ST_GeomFromText("POINT(1.0 1.0)")');
  $created = $item->save();

  return $created ? $this->response->setStatusCode(201) : $this->response->setStatusCode(500);
});

$app->put('/items/{id:[\d]}', function ($id) use ($app) {
  $item = Items::findFirst('id = '.$id);
  $item->name = 'test';
  $updated = $item->update();

  // will return 500 as seems to be doing internally $item->location = $item->location
  return $updated ? $this->response->setStatusCode(200) : $this->response->setStatusCode(500);
});

$app->notFound(function() {
    $response = new Response();
    return $response->setStatusCode(404);
});

$app->handle($_SERVER["REQUEST_URI"]);

but doing this, works:

$app->put('/items/{id:[\d]}', function ($id) use ($app) {
  $item = Items::findFirst('id = '.$id);
  $item->location = new RawValue($item->location);
  $item->name = 'test';
  $updated = $item->update();

  return $updated ? $this->response->setStatusCode(200) : $this->response->setStatusCode(500);
});

The problem with this is I have to do the POINT update manually even when I want just to update another field. As a patch works for me so far.

[ruudboon]

I'm gonna file this one as a NFR. The type column isn't in our known DB columns yet. Also need to check if the POINT type is available on other databases as well.
Need to check if we can add this to 4.0.x or that we need to push this to 4.1. Any ideas @niden ?