I installed advanced application template, created two databases yii2advanced: one in the local MySQL another in the remote MS SQL Server:
'components' => [
'db' => [
'class' => '\yii\db\Connection',
'dsn' => 'mysql:host=127.0.0.1;dbname=yii2advanced',
'username' => 'root',
'password' => 'root',
// 'dsn' => 'sqlsrv:Server=..;Database=yii2advanced;MultipleActiveResultSets=false',
// 'username' => 'sa',
// 'password' => 'sa',
'charset' => 'utf8',
'tablePrefix' => 'tbl_',
],
],
Applied the migration on both the databases:
yii migrate
Now both databases have tables named tbl_user and tbl_migration.
CREATE TABLEstatement for MS SQL Server:
CREATE TABLE [dbo].[tbl_user](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](255) NOT NULL,
[auth_key] [nvarchar](32) NOT NULL,
[password_hash] [nvarchar](255) NOT NULL,
[password_reset_token] [nvarchar](255) NULL,
[email] [nvarchar](255) NOT NULL,
[status] [smallint] NOT NULL,
[created_at] [int] NOT NULL,
[updated_at] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[password_reset_token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE statement for MySQL:
CREATE TABLE `tbl_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`auth_key` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`password_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`password_reset_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`status` smallint(6) NOT NULL DEFAULT '10',
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `password_reset_token` (`password_reset_token`)
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Next, I loaded fixture into a MySQL table tbl_user:
C:\php-projects\advanced\tests\codeception\bin>yii fixture/load User
Fixtures namespace is:
tests\codeception\common\fixtures
Global fixtures will be used:
1. yii\test\InitDb
Fixtures below will be loaded:
1. User
Be aware that:
Applying leads to purging of certain data in the database!
Load above fixtures? (yes|no) [no]:yes
Fixtures were successfully loaded from namespace:
"tests\codeception\common\fixtures"
1. yii\test\InitDbFixture
2. tests\codeception\common\fixtures\UserFixture
Tried to load fixture into the MS SQL Server’s table [dbo].[tbl_user] but get exception:
C:\php-projects\advanced\tests\codeception\bin>yii fixture/load User
Fixtures namespace is:
tests\codeception\common\fixtures
Global fixtures will be used:
1. yii\test\InitDb
Fixtures below will be loaded:
1. User
Be aware that:
Applying leads to purging of certain data in the database!
Load above fixtures? (yes|no) [no]:yes
Exception 'yii\base\InvalidParamException' with message 'Table not found: []'
in C:\php-projects\advanced\vendor\yiisoft\yii2\db\mssql\QueryBuilder.php:182
Stack trace:
0 C:\php-projects\advanced\vendor\yiisoft\yii2\db\Command.php(754): yii\db\mssql\QueryBuilder->checkIntegrity(false, '', '')
1 C:\php-projects\advanced\vendor\yiisoft\yii2\test\InitDbFixture.php(94): yii\db\Command->checkIntegrity(false, '')
2 C:\php-projects\advanced\vendor\yiisoft\yii2\test\InitDbFixture.php(76): yii\test\InitDbFixture->checkIntegrity(false)
3 C:\php-projects\advanced\vendor\yiisoft\yii2\test\FixtureTrait.php(114): yii\test\InitDbFixture->beforeUnload()
4 C:\php-projects\advanced\vendor\yiisoft\yii2\console\controllers\FixtureController.php(159): yii\console\controllers\FixtureController->unloadFixtures(Array)
5 [internal function]: yii\console\controllers\FixtureController->actionLoad('User')
6 C:\php-projects\advanced\vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array)
7 C:\php-projects\advanced\vendor\yiisoft\yii2\base\Controller.php(154): yii\base\InlineAction->runWithParams(Array)
8 C:\php-projects\advanced\vendor\yiisoft\yii2\console\Controller.php(119): yii\base\Controller->runAction('load', Array)
9 C:\php-projects\advanced\vendor\yiisoft\yii2\base\Module.php(454): yii\console\Controller->runAction('load', Array)
10 C:\php-projects\advanced\vendor\yiisoft\yii2\console\Application.php(176): yii\base\Module->runAction('fixture/load', Array)
11 C:\php-projects\advanced\vendor\yiisoft\yii2\console\Application.php(143): yii\console\Application->runAction('fixture/load', Array)
12 C:\php-projects\advanced\vendor\yiisoft\yii2\base\Application.php(375): yii\console\Application->handleRequest(Object(yii\console\Request))
13 C:\php-projects\advanced\tests\codeception\bin\yii(23): yii\base\Application->run()
14 {main}
Why the User fixture not loaded into a table in MS SQL Server? Maybe the reason is in the [dbo] scheme?