Text Columns in Magento 2 DDL

I’m working on adding DDL (data definition language) code generation to pestle – i.e. the code that ultimately creates your database tables that lives in the InstallSchema or UpgradeSchema classes. I came across this bit of weirdness

#File: vendor/magento/module-cms/Setup/InstallSchema.php

    )->addColumn(
        'identifier',
        MagentoFrameworkDBDdlTable::TYPE_TEXT,
        255,
        ['nullable' => false],
        'Block String Identifier'
    )->addColumn(
        'content',
        MagentoFrameworkDBDdlTable::TYPE_TEXT,
        '2M',
        [],
        'Block Content'
    )

This is part of the code that creates the database table Magento stores CMS block data in.

CREATE TABLE `cms_block` (
  `block_id` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Block ID',
  `title` varchar(255) NOT NULL COMMENT 'Block Title',
  `identifier` varchar(255) NOT NULL COMMENT 'Block String Identifier',
  `content` mediumtext COMMENT 'Block Content',
  `creation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Block Creation Time',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Block Modification Time',
  `is_active` smallint(6) NOT NULL DEFAULT '1' COMMENT 'Is Block Active',
  PRIMARY KEY (`block_id`),
  FULLTEXT KEY `CMS_BLOCK_TITLE_IDENTIFIER_CONTENT` (`title`,`identifier`,`content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='CMS Block Table';

Some of you may have already spotted the weirdness. Magento stores a block identifier as a varchar. Magento stores the block content as a mediumtext. This is a pretty common setup in PHP information systems that use MySQL backends. What’s weird is the DDL

)->addColumn(
    'identifier',
    MagentoFrameworkDBDdlTable::TYPE_TEXT,
    255,
    ['nullable' => false],
    'Block String Identifier'
)->addColumn(
    'content',
    MagentoFrameworkDBDdlTable::TYPE_TEXT,
    '2M',
    [],
    'Block Content'
)

In Magento’s view of the world – both these columns are MagentoFrameworkDBDdlTable::TYPE_TEXT types. The only difference being one is 255 (characters? bytes?) long

MagentoFrameworkDBDdlTable::TYPE_TEXT,
255,

and the other is “2M” long

MagentoFrameworkDBDdlTable::TYPE_TEXT,
'2M',    

Rather than directly map their DDL types to database types, Magento’s created an abstract type of “text”, and requires the user to indicate how big the text can be. Then Magento’s code decides what sort of database column it should be.

I see the intent here – planning for the possibility of different databases with slightly different column types (even if Magento 2 only supports MySQL at the moment, which is another story). The implementation though doesn’t serve anyone well though. One of the goals of any good DDL language is to hide these sorts of implementation details from the programmer so they can pick a column type and get on with their life. Laravel’s migrations provide a better example of this – there’s an abstract hierarchy, but it’s not possible to accidentally create a varchar when you wanted a text field, or vice versa.

There’s also that 2M syntax. This isn’t documented anywhere other than code. The string’s expanded

#File: vendor/magento/framework/DB/Adapter/Pdo/Mysql.php
protected function _parseTextSize($size)
{
    $size = trim($size);
    $last = strtolower(substr($size, -1));

    switch ($last) {
        case 'k':
            $size = intval($size) * 1024;
            break;
        case 'm':
            $size = intval($size) * 1024 * 1024;
            break;
        case 'g':
            $size = intval($size) * 1024 * 1024 * 1024;
            break;
    }

    if (empty($size)) {
        return Table::DEFAULT_TEXT_SIZE;
    }
    if ($size >= Table::MAX_TEXT_SIZE) {
        return Table::MAX_TEXT_SIZE;
    }

    return intval($size);
}

Then, depending on the result of that expansion, Magento picks an appropriate column type

#File: vendor/magento/framework/DB/Adapter/Pdo/Mysql.php
if ($length <= 255) {
    $cType = $ddlType == Table::TYPE_TEXT ? 'varchar' : 'varbinary';
    $cType = sprintf('%s(%d)', $cType, $length);
} elseif ($length > 255 && $length <= 65536) {
    $cType = $ddlType == Table::TYPE_TEXT ? 'text' : 'blob';
} elseif ($length > 65536 && $length <= 16777216) {
    $cType = $ddlType == Table::TYPE_TEXT ? 'mediumtext' : 'mediumblob';
} else {
    $cType = $ddlType == Table::TYPE_TEXT ? 'longtext' : 'longblob';
}     

So, the M stands for megabytes. This system is similar, but not identical to, PHP’s system for setting a memory limit.

Like so much of Magento 2, this is a small/weird bit of code that looks more like a developer trying to keep themselves engaged on a project they knew wouldn’t ship for years, if at all.