.(dot) character is not being accepted in database name

.(dot) character is not being accepted in database name - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: .(dot) character is not being accepted in database name (/showthread.php?tid=75745)

.(dot) character is not being accepted in database name - cijagani - 03-12-2020

CodeIgniter 4.02 version

Direction
set databasename in .env file. for example

Code:
database.default.hostname = localhost

database.default.database = 'ci4.0.2'

database.default.username = root

database.default.password = root

database.default.DBDriver = MySQLi

Created : users table

PHP Code:
<?php namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class Users extends Migration
{
public function up()
{
$this->forge->addField([
            'id'          => ['type' => 'INT', 'constraint' => 5, 'unsigned' => true, 'auto_increment' => true],
'name' => ['type' => 'VARCHAR', 'constraint' => 50],
'address'      => ['type' => 'VARCHAR', 'constraint' => 100],
'email'      => ['type' => 'VARCHAR', 'constraint' => 50],
            'contact'      => ['type' => 'VARCHAR', 'constraint' => 10],
            'created_at'  => ['type' => 'datetime', 'null' => true],
            'updated_at'  => ['type' => 'datetime', 'null' => true],
            'deleted_at'  => ['type' => 'datetime', 'null' => true],
        ]);

        $this->forge->addPrimaryKey('id');
        $this->forge->createTable('users', false, ['ENGINE' => 'InnoDB']);
}

//--------------------------------------------------------------------

public function down()
{
//
}
} 

Describe the bug

Code:
E:\work\htdocs\ci4\public>php spark migrate

CodeIgniter CLI Tool - Version 4.0.2 - Server-Time: 2020-03-12 02:36:40am

Running all new migrations...

> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`0`.`2`' at line 1

> E:\work\htdocs\ci4\public\vendor\codeigniter4\framework\system\Database\MySQLi\Connection.php - 330

Expected behavior
.(dot) character should be allowed in database.default.database

OS: [Windows 8.1]
Web server [Apache 2.4.1] (wampp)
PHP version [7.4.3]

RE: .(dot) character is not being accepted in database name - zahhar - 03-12-2020

Strictly speaking, dot character in database names might not be supported by all database engines and versions. Indeed it is supported in latest mySQL/MariaDB, but it was not like that before: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

Also, dot character is used in fully qualified names (my_db.my_table.my_column), that should be quoted separately: `my_db`.`my_table`.`my_column`. This is exactly wha CI4 is doing when finds a dot in DB config: treats it as a qualifier separator, and quotes parts before and after it separately.

Interesting thought, that database name in mySQL does not have a qualifier - this is covered in docs, https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html

However, in other DB engines (e.g. MS SQL, Oracle come to my mind) situation might be different: my_server.my_db.my_schema for instance.

So we could change it for particular DB Driver, if it makes sense at all.

RE: .(dot) character is not being accepted in database name - cijagani - 03-12-2020

(03-12-2020, 07:05 AM)zahhar Wrote: Strictly speaking, dot character in database names might not be supported by all database engines and versions. Indeed it is supported in latest mySQL/MariaDB, but it was not like that before: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

Also, dot character is used in fully qualified names (my_db.my_table.my_column), that should be quoted separately: `my_db`.`my_table`.`my_column`. This is exactly wha CI4 is doing when finds a dot in DB config: treats it as a qualifier separator, and quotes parts before and after it separately.

Interesting thought, that database name in mySQL does not have a qualifier - this is covered in docs, https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html

However, in other DB engines (e.g. MS SQL, Oracle come to my mind) situation might be different: my_server.my_db.my_schema for instance.

So we could change it for particular DB Driver, if it makes sense at all.

thanks for the reply. i got your point. i am using maria db with codeigniter 4. here this point needs to remember that in codeigniter 3 version dot character is allowed in db name, while in codeigniter 4 is not being accpted.