DBUtil cannot create table with SQLite

Art4 commented 8 years ago

I have created and configurate a SQLite database connection and want to run the migrations.

If I execute the migration with

env FUEL_ENV=test php oil refine migrate:current

I got this error:

Uncaught exception Fuel\Core\Database_Exception: SQLSTATE[HY000]: General error: 1 near "DEFAULT": syntax error with query: "CREATE TABLE IF NOT EXISTS `fuel_migration` (
    `type` varchar(25) NOT NULL,
    `name` varchar(50) NOT NULL,
    `migration` varchar(100) DEFAULT '' NOT NULL
)DEFAULT CHARACTER SET utf8;"
Callstack: 
#0 .../fuel/core/classes/database/query.php(305): Fuel\Core\Database_PDO_Connection->query(0, 'CREATE TABLE IF...', false)
#1 .../fuel/core/classes/dbutil.php(149): Fuel\Core\Database_Query->execute(NULL)
#2 .../fuel/core/classes/migrate.php(611): Fuel\Core\DBUtil::create_table('fuel_migration', Array)
#3 .../fuel/core/classes/migrate.php(74): Fuel\Core\Migrate::table_version_check()
#4 [internal function]: Fuel\Core\Migrate::_init()
#5 .../fuel/core/classes/autoloader.php(375): call_user_func('Migrate::_init')
#6 .../fuel/core/classes/autoloader.php(249): Fuel\Core\Autoloader::init_class('Migrate')
#7 [internal function]: Fuel\Core\Autoloader::load('Migrate')
#8 .../fuel/core/tasks/migrate.php(282): spl_autoload_call('Migrate')
#9 .../fuel/core/tasks/migrate.php(161): Fuel\Tasks\Migrate::_current('default', 'app')
#10 .../fuel/core/base.php(455): Fuel\Tasks\Migrate->__call('current', Array)
#11 .../fuel/core/base.php(455): Fuel\Tasks\Migrate->current()
#12 .../fuel/packages/oil/classes/refine.php(108): call_fuel_func_array(Array, Array)
#13 [internal function]: Oil\Refine::run('migrate:current', Array)
#14 .../fuel/packages/oil/classes/command.php(126): call_user_func('Oil\\Refine::run', 'migrate:current', Array)
#15 /oil(65): Oil\Command::init(Array)
#16 {main}

If I excecuted the query directly in sqlite3 without the charset:

CREATE TABLE IF NOT EXISTS `fuel_migration` (
    `type` varchar(25) NOT NULL,
    `name` varchar(50) NOT NULL,
    `migration` varchar(100) DEFAULT '' NOT NULL
);

and the table was created. Something seems to be wrong with the charset.

WanWizard commented 8 years ago

The current DBUtil, and most of the query builder, generates MySQL type ANSI SQL. Which isn't compatible with most other platforms.

I've started working on this with the goal to release full multi-platform support with 1.8.

WanWizard commented 8 years ago

Didn't get around to it before releasing 1.8 (which was needed because of PHP7 compatibility).

But there is now a schema class which is used by DBUtil, which can be overloaded per driver type, so it is possible to implement this. I'll see if I can get around to doing this later.

fuel / core

DBUtil cannot create table with SQLite #1985