Bug: $forge->createTable('table', true) fails when table has indexes

[sclubricants]

PHP Version

8.0

CodeIgniter4 Version

4.2.1

CodeIgniter4 Installation Method

Git

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

SQLite and others

What happened?

When creating a table with the optional parameter IF NOT EXISTS the execution fails if the table already exists and there are indexes to be created as well. If fails for the databases that create the indexes after the table creation command.

Steps to Reproduce

Run the following on SQLite, Postgre, SQLSRV and maybe others:

$forge = \Config\Database::forge('tests');  

$forge->addField([
    'id'         => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
    'name'       => ['type' => 'VARCHAR', 'constraint' => 80],
    'email'      => ['type' => 'VARCHAR', 'constraint' => 100],
    'country'    => ['type' => 'VARCHAR', 'constraint' => 40],
    'created_at' => ['type' => 'DATETIME', 'null' => true],
    'updated_at' => ['type' => 'DATETIME', 'null' => true],
    'deleted_at' => ['type' => 'DATETIME', 'null' => true],
])->addKey('id', true)->addUniqueKey('email')->addKey('country')->createTable('user', true);

$forge->addField([
    'id'         => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
    'name'       => ['type' => 'VARCHAR', 'constraint' => 80],
    'email'      => ['type' => 'VARCHAR', 'constraint' => 100],
    'country'    => ['type' => 'VARCHAR', 'constraint' => 40],
    'created_at' => ['type' => 'DATETIME', 'null' => true],
    'updated_at' => ['type' => 'DATETIME', 'null' => true],
    'deleted_at' => ['type' => 'DATETIME', 'null' => true],
])->addKey('id', true)->addUniqueKey('email')->addKey('country')->createTable('user', true);

An error is thrown: SQLite3::exec(): index db_user_email already exists

Expected Output

Expected that table will be created if it doesn't exist and if it does exists nothing will change and no errors will be thrown.

Anything else?

No response

[kenjis]

Indeed, the error occurs because CREATE UNIQUE INDEX is executed after CREATE TABLE.

[ErrorException]

SQLite3::exec(): index db_user_email already exists

at SYSTEMPATH/Database/SQLite3/Connection.php:129

Backtrace:
  1    [internal function]
       CodeIgniter\Debug\Exceptions()->errorHandler(2, 'SQLite3::exec(): index db_user_email already exists', '/Users/kenji/work/codeigniter/CodeIgniter4/system/Database/SQLite3/Connection.php', 129)

  2    SYSTEMPATH/Database/SQLite3/Connection.php:129
       SQLite3()->exec('CREATE UNIQUE INDEX `db_user_email` ON `db_user` (`email`);')

  3    SYSTEMPATH/Database/BaseConnection.php:693
       CodeIgniter\Database\SQLite3\Connection()->execute('CREATE UNIQUE INDEX `db_user_email` ON `db_user` (`email`);')

  4    SYSTEMPATH/Database/BaseConnection.php:620
       CodeIgniter\Database\BaseConnection()->simpleQuery('CREATE UNIQUE INDEX `db_user_email` ON `db_user` (`email`);')

  5    SYSTEMPATH/Database/Forge.php:521
       CodeIgniter\Database\BaseConnection()->query('CREATE UNIQUE INDEX `db_user_email` ON `db_user` (`email`);')

  6    APPPATH/Controllers/Home.php:29
       CodeIgniter\Database\Forge()->createTable('db_user', true)

  7    SYSTEMPATH/CodeIgniter.php:894
       App\Controllers\Home()->index()

  8    SYSTEMPATH/CodeIgniter.php:466
       CodeIgniter\CodeIgniter()->runController(Object(App\Controllers\Home))

  9    SYSTEMPATH/CodeIgniter.php:349
       CodeIgniter\CodeIgniter()->handleRequest(null, Object(Config\Cache), false)

 10    FCPATH/index.php:55
       CodeIgniter\CodeIgniter()->run()