Project

General

Profile

Actions
Copy link

Bug #79990

closed

Database migration: spatial column POINT NOT NULL not detected correctly

Added by Christian Weiske over 7 years ago. Updated almost 4 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2017-02-24
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
7
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

I'm trying to add a non-null POINT column to a database table. ext_tables.sql contains:

coordinates point NOT NULL,

The install tool database analyzer adds this field but then shows it again:

Change fields
 ALTER TABLE tx_ybpn_branches CHANGE coordinates coordinates point NOT NULL; 
Current value: point NOT NULL default NULL

The NOT NULL DEFAULT NULL is invalid here. In at least MySQL 5.7, spatial columns cannot have a default value:

BLOB, TEXT, GEOMETRY or JSON column 'coordinates' can't have a default value
Actions
Copy link
 #1

Updated by Thomas Hohn over 7 years ago

The basic problem seems to be that Doctrine does not support this data type.
I suggest you create an issues at https://github.com/doctrine/dbal

Actions
Copy link
 #2

Updated by Riccardo De Contardi over 7 years ago

@Thomas Hohn: the issue reports TYPO3 7 which does not use Doctrine.

Actions
Copy link
 #3

Updated by Christian Eßl about 5 years ago

  • Category set to Database API (Doctrine DBAL)
Actions
Copy link
 #4

Updated by Susanne Moog over 4 years ago

  • Status changed from New to Closed

POINT is not an ANSI SQL field type and therefor not supported by the TYPO3 core feature set (as that should work cross-databases, with doctrine powering that). Closing the issue as it is not a bug - you could request the implementation of POINT in doctrine as Thomas pointed out, or register a custom type mapping in your installation via `$connection->registerDoctrineTypeMapping($type, $type);`

Actions
Copy link
 #5

Updated by Christian Weiske almost 4 years ago

This is about TYPO3 v8, v9 and v10:

Even when registering a custom PointType in TYPO3\CMS\Core\Database\ConnectionPool, the database analyzer will still give an error:

[SQL Error] line 0, col 795: Error: Expected BIT, TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT, REAL, DOUBLE, FLOAT, DECIMAL, NUMERIC, DATE, TIME, TIMESTAMP, DATETIME, YEAR, CHAR, VARCHAR, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET, or JSON, got 'point' in statement:
CREATE TABLE ...

This is because TYPO3\CMS\Core\Database\Schema\Parser\Parser::columnDataType does not care about manually defined types - it has a hard-coded list of supported SQL column data types.

In the end, 6 files need to be modified/created:

typo3/sysext/core/Classes/Database/ConnectionPool.php
typo3/sysext/core/Classes/Database/Schema/Parser/AST/DataType/PointDataType.php
typo3/sysext/core/Classes/Database/Schema/Parser/Lexer.php
typo3/sysext/core/Classes/Database/Schema/Parser/Parser.php
typo3/sysext/core/Classes/Database/Schema/Parser/TableBuilder.php
typo3/sysext/core/Classes/Database/Schema/Types/PointType.php

Actions
Copy link

Also available in: Atom PDF