Feature to not append database prefix in QueryBuilder

Currently I'm not aware of the means to prevent query builder from appending the database prefix, so which you need to run a query where you do not want the prefix appended, you either can't use QueryBuilder or you have to use acrobatics to make it work. Please consider adding a parameter to QueryBuilder turning off the appending of the database prefix.

Take this code as an example of what I mean

PHP Code:

function dropForeignKeyConstraints(array $foreignKeys, string $table): void {
    $db = Database::connect();

    $prefixName = $db->getPrefix();
    $db->setPrefix('');
    $database_name = $db->database;

    foreach ($foreignKeys as $fk)
    {
        $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS');
        $builder->select('CONSTRAINT_NAME');
        $builder->where('TABLE_SCHEMA', $database_name);
        $builder->where('TABLE_NAME', $table);
        $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
        $builder->where('CONSTRAINT_NAME', $fk);
        $query = $builder->get();

        if($query->getNumRows() > 0)
        {
            $db->query("ALTER TABLE `$table` DROP FOREIGN KEY `$fk`");
        }
    }

    $db->setPrefix($prefixName);
} 


To prevent the query builder from appending the dbPrefix to INFORMATION_SCHEMA.TABLE_CONSTRAINTS, which would break the query, I need to capture the prefix, set it to an empty string, then put it back after I'm done with the QueryBuilder so that code below resumes the prefix appending. While this isn't needed all the time, it's better if I don't pepper the code with these extra three lines. I could write $prefixName = removePrefix() and restorePrefix($prefixName) helper functions, but that really only saves me one line of code. It would be better if I could call something like $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS', false); where the false parameter in the table() function returned the query without the prefix appended.

If you need to create a query with QueryBuilder without a prefix, create an additional connection group and use it in these cases.

For this particular example, you can use the Forge class, which supports dropping foreign keys: https://codeigniter.com/user_guide/dbmgm...oreign-key

@michalsn  Thank you for the tip on using forge to drop a foreign key, but it's missing the point.  The point is that builder doesn't have means of turning off the appending of the prefix but sometimes it's needed.  For example, if I extracted the builder code into a function foreignKeyExists(string $constraintName, string $tableName, string $databaseName): bool then the foreach becomes

PHP Code:

    foreach ($foreignKeys as $fk) {
        if(foreignKeyExists($fk, $tableName, $databaseName)) {
            $db->query("ALTER TABLE `$table` DROP FOREIGN KEY `$fk`"); //This could be changed to use forge, but the problem still exists
        }
    } 


Now my foreignKeyExists() function has the same problem and it has nothing to do with forge.

PHP Code:

fuction foreignKeyExists(string $constraintName, string $tableName, string $databaseName): bool {
        $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS');
        $builder->select('CONSTRAINT_NAME');
        $builder->where('TABLE_SCHEMA', $databaseName);
        $builder->where('TABLE_NAME', $tableName);
        $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
        $builder->where('CONSTRAINT_NAME', $constraintName);
        $query = $builder->get();

        return $query->getNumRows() > 0;
    } 


Adding the ability in table() to not append the prefix saves code and is useful when you don't want to append table names.

Why do you even need to skip DBPrefix if you have set it in the connection group?

I don't see the point of temporarily changing the DBPrefix during the connection.

As I said, you can create another connection group without a prefix and use it when you need it.

Why do you even need to skip DBPrefix if you have set it in the connection group?

I don't see the point of temporarily changing the DBPrefix during the connection.

As I said, you can create another connection group without a prefix and use it when you need it.

If I understand what you are suggesting correctly, you want me to create a custom connection group to handle no db prefix like this:

PHP Code:

$noPrefix = [
    'DSN'      => '',
    'hostname' => 'localhost',
    'username' => 'foo',
    'password' => 'bar',
    'database' => 'baz',
    'DBDriver' => 'MySQLi',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => true,
    'charset'  => 'utf8mb4',
    'DBCollat' => 'utf8mb4_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 3306,
]; 


Then call $db = \Config\Database::connect($noPrefix); when needed. While generally that would resolve the problem, it doesn't resolve the problem where you need to selectively not add the prefix. For example, here is my current workaround

PHP Code:

function foreignKeyExists(string $constraintName, string $tableName): bool {

    $prefix = overridePrefix();

    $db = Database::connect();
    $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS');
    $builder->select('CONSTRAINT_NAME');
    $builder->where('TABLE_SCHEMA', $db->database);
    $builder->where('TABLE_NAME', $prefix . $tableName);
    $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
    $builder->where('CONSTRAINT_NAME', $constraintName);
    $query = $builder->get();

    overridePrefix($prefix);

    return $query->getNumRows() > 0;
} 


You are suggesting this change:

PHP Code:

function foreignKeyExists(string $constraintName, string $tableName): bool {
    $db = Database::connect('noPrefix');
    $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS');
    $builder->select('CONSTRAINT_NAME');
    $builder->where('TABLE_SCHEMA', $db->database);
    $builder->where('TABLE_NAME', $prefix . $tableName);
    $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
    $builder->where('CONSTRAINT_NAME', $constraintName);
    $query = $builder->get();

    return $query->getNumRows() > 0;
} 


The problem with this is that I don't need to remove the prefix in the entire query, just in the table. Notice my 2nd where statement depends on the tableName with a prefix. I'm requesting the feature to have table() allow not appending the prefix so the code becomes

PHP Code:

function foreignKeyExists(string $constraintName, string $tableName): bool {
    $db = Database::connect();
    $builder = $db->table('INFORMATION_SCHEMA.TABLE_CONSTRAINTS', false);
    $builder->select('CONSTRAINT_NAME');
    $builder->where('TABLE_SCHEMA', $db->database);
    $builder->where('TABLE_NAME', $tableName);
    $builder->where('CONSTRAINT_TYPE', 'FOREIGN KEY');
    $builder->where('CONSTRAINT_NAME', $constraintName);
    $query = $builder->get();

    return $query->getNumRows() > 0;
} 


And the signature to table() becomes

PHP Code:

public function table(string $tableName, string $appendPrefix = true) 


This would be a non-breaking change because the default value when $appendPrefix is not passed is true. That said, I respect that this may not be a straightforward change, but it would a useful change.

This seems like a very specific use case.

Even if we were to add a second parameter (which I'm not a fan of), the table name in the WHERE clause would still need to be manually prefixed, since the Query Builder cannot determine whether the given value refers to a table name that should be prefixed.