[FIXED] ALTERing a database table inside a test

Issue

For a seemingly good reason, I need to alter something in a table that was created from a fixture while a test runs.

Of many things I tried, here's what I have right now. Before I even get to the ALTER query, first I want to make sure I have access to that database where the temporary tables sit.

public function testFailingUpdateValidation()
{
    // i will run SQL against a connection, lets get it's exact name
    // from one of it's tables; first get the instance of that table
    // (the setup was blindly copied and edited from some docs)
    $config = TableRegistry::getTableLocator()->exists('External/Products')
        ? []
        : ['className' => 'External/Products'];
    $psTable = TableRegistry::getTableLocator()
        ->get('External/Products', $config);
    // get the connection name to work with from the table
    $connectionName = $psTable->getConnection()->configName();
    // get the connection instance to run the query
    $db = \Cake\Datasource\ConnectionManager::get($connectionName);
    // run the query
    $statement = $db->query('SHOW TABLES;');
    $statement->execute();
    debug($statement->fetchAll());
}

The output is empty.

########## DEBUG ##########
[]
###########################

I'm expecting it to have at least a name of that table I got the connection name from. Once I have the working connection, I'd run an ALTER query on a specific column.

How do I do that? Please help

Thought I'd clarify what I'm up to, if that's needed

This is a part of an integrated code test. The methods involved are already tested individually. The code inside the method I'm testing takes a bunch of values from one database table and copies them to another, then it immediately validates whether values in the two tables match. I need to simulate a case when they don't.

The best I could come up with is to change the schema of the table column in order to have it store values with low precision, and cause my code test to fail when it tries to match (validate) the values.

Solution

The problem was I didn't load the fixture for the table I wanted to ALTER. I know it's kind of obvious, but I'm still confused because then why didn't SHOW TABLES return the other tables that did have their fixtures loaded? It showed me no tables so I thought it was not working at all, and didn't even get to loading that exact fixture. Once I did, it now shows me the table of that fixture, and the others.

Anyway, here's the working code. Hope it helps someone figuring this out.

// get the table instance
$psTable = TableRegistry::getTableLocator()->get('External/Products');
// get the table's corresponding connection name
$connectionName = $psTable->getConnection()->configName();
// get the underlying connection instance by it's name
$connection = \Cake\Datasource\ConnectionManager::get($connectionName);
// get schemas available within connection
$schemaCollection = $connection->getSchemaCollection();
// describe the table schema before ALTERating it
$schemaDescription = $schemaCollection->describe($psTable->getTable());
// make sure the price column is initially DECIMAL
$this->assertEquals('decimal', $schemaDescription->getColumnType('price'));
// keep the initial column data for future reference
$originalColumn = $schemaDescription->getColumn('price');

// ALTER the price column to be TINYINT(2), as opposed to DECIMAL
$sql = sprintf(
    'ALTER TABLE %s '.
    'CHANGE COLUMN price price TINYINT(2) UNSIGNED NOT NULL', 
    $psTable->getTable()
);
$statement = $connection->query($sql);
$statement->closeCursor();
// describe the table schema after ALTERations
$schemaDescription = $schemaCollection->describe($psTable->getTable());
// make sure the price column is now TINYINT(2)
$this->assertEquals('tinyinteger', $schemaDescription->getColumnType('price'));

Note: after this test was done, I had to restore the column back to its initial type using the data from $originalColumn above.

Answered By - aexl