All geek questions in one place

Use PHPUnit / CakePHP Tools with Point Type

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

Upon completion of the API, and it runs without errors, it can be assumed that I have already implemented Point-Type for CakePHP.

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

Until the object contains the point type, but the sql created by this function will skip the data type.

Do you have any idea how I can solve the missing type? Or how can I execute a raw sql query in a device?

Below are snippets and data

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

$this → _ ​​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'
    ]
}

sql-, createSql-:

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

+4
1

SQL . , .

SQL

, , \Cake\Database\Schema\BaseSchema::columnSql() SQL point.

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

datasources driver config/app.php :

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

SQL

- , 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 .

+4

Source: https://habr.com/ru/post/1680340/

More articles:


All Articles