使用带Point类型的PHPUnit/CakePHPFixtures

我已经创建了一个用于预处理POI的API,并将节点的坐标存储在"point"-type-column(MySQL)中.

由于API已完成并且运行没有错误,您可以猜测我已经为CakePHP实现了Point-Type.

但现在问题是:我想用PHPUnit测试API,但Cake\Database\Schema\Table :: createSql-Method搞砸了sql.

直到Object包含点类型,但此函数创建的sql错过了数据类型.

您有什么想法可以解决丢失的类型吗？或者我如何在夹具中执行原始sql查询？

以下是摘要和数据

"节点"夹具的$字段:

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'
    ],
];

调试$ this - > _ schema in vendor/cakephp/cakephp/src/TestSuite/Fixture/TestFixture.php /src/TestSuite/Fixture/TestFixture.php(第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'
    ]
}

以及由上面的shemas createSql-method生成的错误的sql-query:

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
}

如您所见,"坐标"缺少数据类型.

版本:

PHP:

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

蛋糕:3.3.16

PHPUnit:4.8.36

表创建SQL支持的类型在相应的模式类中进行了硬编码.可能还有改进的余地,从为不支持的类型抛出异常开始.

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

通过模式类自定义列创建SQL

一种干净的方法可能是创建扩展的模式类并覆盖该\Cake\Database\Schema\BaseSchema::columnSql()方法并相应地为该point类型创建适当的SQL .

这还需要使用覆盖schemaDialect()创建相应模式类实例的方法的扩展驱动程序类.

这是MySQL演示原理的一个简单示例:

// 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);
    }
}

然后只需相应地配置数据源driver选项config/app.php:

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

自定义表创建SQL via fixture类

更简单,但也更少DRY,将覆盖TestFixture::create()您的NodesFixture类中的方法,并在那里执行完全自定义表创建SQL.

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;
}

如果您需要支持不同的方言,则可以$db->driver()相应地检查并创建适当的SQL.