Use PHPUnit/CakePHP Fixtures with Point type

Question

I've created an API for preprocessing POIs and store the coordinates of the nodes in a "point"-type-column (MySQL).

As the API is finished and runs without errors, you can guess that I already implemented the Point-Type for CakePHP.

But now the problem: I want to test the API with PHPUnit, but the Cake\Database\Schema\Table::createSql-Method messed up the sql.

Until there the Object contains the point type, but the sql created by this function misses the data-type.

Have you any ideas how I can solve the missing type? Or how I can execute a raw sql-query in a fixture?

Below are the snippets & data

---

$fields of the Fixture for "nodes":

public $fields = [
    'id' => ['type' => 'integer', 'length' => 11, 'unsigned' => false, 'null' => false, 'default' => null, 'comment' => '', 'autoIncrement' => true, 'precision' => null],
    'osm_id' => ['type' => 'biginteger', 'length' => 20, 'unsigned' => false, 'null' => false, 'default' => null, 'comment' => '', 'precision' => null, 'autoIncrement' => null],
    'version' => ['type' => 'biginteger', 'length' => 20, 'unsigned' => false, 'null' => false, 'default' => null, 'comment' => '', 'precision' => null, 'autoIncrement' => null],
    'coordinates' => ['type' => 'point', 'length' => null, 'null' => false, 'default' => null, 'collate' => null, 'comment' => '', 'precision' => null],
    'category' => ['type' => 'string', 'length' => 255, 'null' => false, 'default' => null, 'collate' => 'utf8_general_ci', 'comment' => '', 'precision' => null, 'fixed' => null],
    'created' => ['type' => 'timestamp', 'length' => null, 'null' => false, 'default' => 'CURRENT_TIMESTAMP', 'comment' => '', 'precision' => null],
    'modified' => ['type' => 'timestamp', 'length' => null, 'null' => true, 'default' => null, 'comment' => '', 'precision' => null],
    '_constraints' => [
        'primary' => ['type' => 'primary', 'columns' => ['id'], 'length' => []],
    ],
    '_options' => [
        'engine' => 'InnoDB',
        'collation' => 'utf8_general_ci'
    ],
];

debug of $this->_schema in vendor/cakephp/cakephp/src/TestSuite/Fixture/TestFixture.php /src/TestSuite/Fixture/TestFixture.php (line 294)

object(Cake\Database\Schema\Table) {
    [protected] _table => 'nodes'
    [protected] _columns => [
            'id' => [
                    'type' => 'integer',
                    'length' => (int) 11,
                    'unsigned' => false,
                    'null' => false,
                    'default' => null,
                    'comment' => '',
                    'autoIncrement' => true,
                    'precision' => null,
                    'baseType' => null
            ],
            'osm_id' => [
                    'type' => 'biginteger',
                    'length' => (int) 20,
                    'unsigned' => false,
                    'null' => false,
                    'default' => null,
                    'comment' => '',
                    'precision' => null,
                    'autoIncrement' => null,
                    'baseType' => null
            ],
            'version' => [
                    'type' => 'biginteger',
                    'length' => (int) 20,
                    'unsigned' => false,
                    'null' => false,
                    'default' => null,
                    'comment' => '',
                    'precision' => null,
                    'autoIncrement' => null,
                    'baseType' => null
            ],
            'coordinates' => [
                    'type' => 'point',
                    'length' => null,
                    'null' => false,
                    'default' => null,
                    'comment' => '',
                    'precision' => null,
                    'baseType' => null
            ],
            'category' => [
                    'type' => 'string',
                    'length' => (int) 255,
                    'null' => false,
                    'default' => null,
                    'collate' => 'utf8_general_ci',
                    'comment' => '',
                    'precision' => null,
                    'fixed' => null,
                    'baseType' => null
            ],
            'created' => [
                    'type' => 'timestamp',
                    'length' => null,
                    'null' => false,
                    'default' => 'CURRENT_TIMESTAMP',
                    'comment' => '',
                    'precision' => null,
                    'baseType' => null
            ],
            'modified' => [
                    'type' => 'timestamp',
                    'length' => null,
                    'null' => true,
                    'default' => null,
                    'comment' => '',
                    'precision' => null,
                    'baseType' => null
            ]
    ]
    [protected] _typeMap => [
            'id' => 'integer',
            'osm_id' => 'biginteger',
            'version' => 'biginteger',
            'coordinates' => 'point',
            'category' => 'string',
            'created' => 'timestamp',
            'modified' => 'timestamp'
    ]
    [protected] _indexes => []
    [protected] _constraints => [
            'primary' => [
                    'type' => 'primary',
                    'columns' => [
                            (int) 0 => 'id'
                    ],
                    'length' => []
            ]
    ]
    [protected] _options => [
            'engine' => 'InnoDB',
            'collation' => 'utf8_general_ci'
    ]
    [protected] _temporary => false
    [protected] _columnKeys => [
            'type' => null,
            'baseType' => null,
            'length' => null,
            'precision' => null,
            'null' => null,
            'default' => null,
            'comment' => null
    ]
    [protected] _columnExtras => [
            'string' => [
                    'fixed' => null,
                    'collate' => null
            ],
            'text' => [
                    'collate' => null
            ],
            'integer' => [
                    'unsigned' => null,
                    'autoIncrement' => null
            ],
            'biginteger' => [
                    'unsigned' => null,
                    'autoIncrement' => null
            ],
            'decimal' => [
                    'unsigned' => null
            ],
            'float' => [
                    'unsigned' => null
            ]
    ]
    [protected] _indexKeys => [
            'type' => null,
            'columns' => [],
            'length' => [],
            'references' => [],
            'update' => 'restrict',
            'delete' => 'restrict'
    ]
    [protected] _validIndexTypes => [
            (int) 0 => 'index',
            (int) 1 => 'fulltext'
    ]
    [protected] _validConstraintTypes => [
            (int) 0 => 'primary',
            (int) 1 => 'unique',
            (int) 2 => 'foreign'
    ]
    [protected] _validForeignKeyActions => [
            (int) 0 => 'cascade',
            (int) 1 => 'setNull',
            (int) 2 => 'setDefault',
            (int) 3 => 'noAction',
            (int) 4 => 'restrict'
    ]
}

and the errornous sql-query that is generatet by the above shemas createSql-method:

object(Cake\Database\Statement\MysqlStatement) {
        [protected] _statement => object(PDOStatement) {
                queryString => 'CREATE TABLE `nodes` (
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
`osm_id` BIGINT NOT NULL,
`version` BIGINT NOT NULL,
`coordinates` NOT NULL,
`category` VARCHAR(255) COLLATE utf8_general_ci NOT NULL,
`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` TIMESTAMP NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB'
        }
        [protected] _driver => object(Cake\Database\Driver\Mysql) {

                'connected' => true

        }
        [protected] _hasExecuted => false
        [protected] _bufferResults => true
}

As you can see the data-type is missing for 'coordinates'.

---

Versions:

PHP:

PHP 5.6.30-12~ubuntu16.04.1+deb.sury.org+1

Cake: 3.3.16

PHPUnit: 4.8.36

Answer 1

The supported types for table creation SQL are hardcoded in the respective schema classes. There might be room for improvement, starting with throwing an exception for unsupported types.

• https://github.com/cakephp/.../src/Database/Schema/MysqlSchema.php#L293
• https://github.com/cakephp/.../src/Database/Schema/PostgresSchema.php#L349
• https://github.com/cakephp/.../src/Database/Schema/SqliteSchema.php#L276
• https://github.com/cakephp/.../src/Database/Schema/SqlserverSchema.php#L329

Custom column creation SQL via schema classes

A clean way would probably be to create extended schema classes and overwrite the \Cake\Database\Schema\BaseSchema::columnSql() method and create appropriate SQL accordingly for the point type.

That would also require to use extended driver classes that overwrite the schemaDialect() method that creates the respective schema class instances.

Here's a quick example for MySQL to demonstrate the principle:

// src/Database/Driver/Mysql.php
namespace App\Database\Driver;

use App\Database\Schema\MysqlSchema;
use Cake\Database\Driver\Mysql as BaseMysql;

class Mysql extends BaseMysql
{
    public function schemaDialect()
    {
        if (!$this->_schemaDialect) {
            $this->_schemaDialect = new MysqlSchema($this);
        }

        return $this->_schemaDialect;
    }
}

// src/Database/Schema/MysqlSchema.php
namespace App\Database\Schema;

use Cake\Database\Schema\MysqlSchema as BaseMysqlSchema;
use Cake\Database\Schema\TableSchema; // as of CakePHP 3.4
// use Cake\Database\Schema\Table as TableSchema; // before CakePHP 3.4

class MysqlSchema extends BaseMysqlSchema
{
    public function columnSql(TableSchema $schema, $name)
    {
        $data = $schema->column($name);
        if ($data['type'] === 'point') {
            $out = $this->_driver->quoteIdentifier($name);

            $out .= ' POINT';

            if (isset($data['null']) && $data['null'] === false) {
                $out .= ' NOT NULL';
            }

            if (isset($data['comment']) && $data['comment'] !== '') {
                $out .= ' COMMENT ' . $this->_driver->schemaValue($data['comment']);
            }

            return $out;
        }

        return parent::columnSql($schema, $name);
    }
}

And then just configure the datasources driver option in your config/app.php accordingly:

'driver' => \App\Database\Driver\Mysql::class,

Custom table creation SQL via fixture classes

Something more simple, but also less DRY, would be to overwrite the TestFixture::create() method in your NodesFixture class, and execute completely custom table creation SQL in there.

public function create(ConnectionInterface $db)
{
    try {
        $query = 'CREATE TABLE ...';

        $stmt = $db->prepare($query);
        $stmt->execute();
        $stmt->closeCursor();
    } catch (Exception $e) {
        $msg = sprintf(
            'Fixture creation for "%s" failed "%s"',
            $this->table,
            $e->getMessage()
        );
        Log::error($msg);
        trigger_error($msg, E_USER_WARNING);

        return false;
    }

    return true;
}

If you'd need to supporte different dialects, you could check $db->driver() and create appropriate SQL accordingly.