[help wanted] Reverse dataType mapping

[exodus4d]

I´m currently working on a database update/setup feature for my app. I would like to create a small UI that shows all Cortex models with their $fieldConf configuration, like type, index, default ,... information (something similar to PHPMyAdmin ). Moreover I want to compare the model $fieldConf settings with the "Current" existing table (MySQL) columns.

Some examples:

• If a column exists in the model but not in MySQL, it is marked with a red cross (X)
• If a column dataType has changed on an existing MySQL column (e.g. Schema::DT_VARCHAR128 to Schema::DT_VARCHAR256), the current dataType is marked in orange,...

My approach:

I have a static array with all model classes, which i loop over to get the New/Updated $fieldConf` for each model:

$requiredTables = [];

foreach($dbData['models'] as $model){
    $tableConfig =  call_user_func($model . '::resolveConfiguration');
    $requiredTables[$tableConfig['table']] = [
        'fieldConf' => $tableConfig['fieldConf'],
         'exists' => false
    ];
}

... and i have an other array with the Current tables in the database ...

$schema = new SQL\Schema($db);
$currentTables = $schema->getTables();

... now i can do a nested loop over both arrays and check if a table already exists. If it does, i can compare the column definition and check for changes...

foreach($requiredTables as $requiredTableName => $data){
    if(in_array($requiredTableName, $currentTables)){
        // table exists

        // get column definition
        $tableModifier = new SQL\TableModifier($requiredTableName, $schema);
        $currentColumns = $tableModifier->getCols(true);

        foreach($data['fieldConf'] as $columnName => $fieldConf){
            if(array_key_exists($columnName, $currentColumns)){
                // column exists -> check colum type for changes...
                // -> THE PROBLEM :)

            }else{
                // column not exists OR is not required e.g. "virtual Field"
            }
        }

    }
}

The Problem

For a column type check for changes, i could compare $currentColumns[$columnName]['type'] (which is a MySQL type definition e.g. "tinyint(1)" ) with $fieldConf['type'] (which is a DB\SQL\Schema::DT_BOOL type definition e.g. "BOOLEAN").

But the column type actually hasn´t changed at all :)
What i need at this point is some kind of "reverse Schema mapping" that either converts "tinyint(1)" into "BOOLEAN" (which is probably not always a good idea) OR "BOOLEAN" into "tinyint(1)" to make a column type check possible :)

The Question

1. Is there a simpler/more elegant way to achieve what i want?
2. Can i use something (already existing) from Schema Builder?

---

Sorry for the wall of text, I hope it is understandable ;) Great work at all!

Here is small preview of the current state:

[ikkez]

hi. very nice idea, looks great.
Your approach is totally right. I think way to go is to compare the resolved datatype value from BOOLEAN with the table's current datatype (tinyint(1)). To resolve the Schema BOOLEAN label, you could usually use the findQuery() method, but the BOOLEAN field is some kind of alias in mysql (and maybe others) that converts to tinyint(1). Well maybe it would be the easiest, if we just alter the type for mysql's boolean here. I guess findQuery would return tinyint then, which would be comparable.
For other fields which are no alias name in mysql, it should already work with the findQuery method:

$type_val = $schema->findQuery($schema->dataTypes[strtoupper($type)]);
if (!preg_match('/'.preg_quote($type_val).'/i',$current_type)) {
// different column datatype found
}

[ikkez]

I adjusted some data types to resolve any engine specific alias names, added a new isCompatible method for easy checking if a type matches, i.e: $schema->isCompatible('BOOLEAN','tinyint(1)'); and
added a bunch of tests to be sure that it works:

[exodus4d]

Very nice! I spend some hours with testing the new functions.
I thought it would be nice to improve the isCompatible() method and add support for detecting type length as well.
The current problem is, that VARCHAR256 is compatible to varchar(128).
The following is just tested on MySql but i tried to make the new Regex type-length check optional (it looks like that some of the length values are MySql specific, e.g. tinyint(1) ).

I changed your code L294:

public function isCompatible($colType,$colDef) {
    $raw_type=$this->findQuery($this->dataTypes[strtoupper($colType)]);
    preg_match('/(\w+(?:\s+\w+)*)/',$raw_type,$match);
    return (bool) preg_match('/'.preg_quote($match[0]).'/i',$colDef);
}

.. into this:

public function isCompatible($colType,$colDef) {
    $raw_type=$this->findQuery($this->dataTypes[strtoupper($colType)]);
    preg_match_all('/(?P<type>\w+)($|\((?P<length>(\d+|(.*)))\))/', $raw_type, $match);
    return (bool) preg_match_all('/'.preg_quote($match['type'][0]).'($|\(' . preg_quote($match['length'][0]) . '\))/i',$colDef);
}

• I changed both preg_match() to preg_match_all() in order to fetch the type-length options (if available)
• I labelled the found matches ('type' and 'length'). So they are always present in $match even if there is no length found.
• Now we can compare the two column data-types (both Regex expressions are almost identically). The ($| ... adds the type-length check as optional.

I have tested the regex with some types demo.

Problem

Schema::DT_INT is converted into INT4 L122 which is supposed to be a MySql type of int L48. But MySql always adds a "default" length of 11 to an int-column if there is no length given stackoverflow.

Solution

Can we change the MySql type from int to int(11) for INT4. We should add this as an extra array entry to the $dataTypes config, otherwise this will be changed for "mssql|dblib|sybase|odbc|sqlsrv" as well.

[ikkez]

sounds reasonable. committed! thanks.

[ikkez]

@exodus4d can this be closed now or is still something missing?

[exodus4d]

Thanks for all your help ;)