search

2amigos / yii2-usuario

Highly customizable and extensible user management, authentication, and authorization Yii2 extension
https://github.com/2amigos/yii2-usuario
Other
294 stars 141 forks source link

dektrium/yii2-user existing database table structure and data migration #172

Open tsdogs opened 6 years ago

tsdogs commented 6 years ago

It would be nice to have a "script" which would migrate a production database from dektrium/yii2-user to yii2-usuario

Imho it needs a way to:

  1. avoid migrations which would conflict with the existing structure
  2. apply some special migrations which convert to a specific version of yii2-usuario
  3. let the additional new migrations be applied

The problem here is that if yii2-user will introduce new migrations the provided "script" would have to be updated too. (yii2-user seems to be "dead" that's why I'm migrating)

I might implement this as I'm interested in migrating my existing projects, but before I start to code any suggestions would be appreciated.

tsdogs commented 6 years ago

I have seen that basically the structure corresponds except that for the last 2 migrations, so the solution is to simply add the yii2-usuario migrations to the database.

IMHO documentation should be integrated with this information and maybe create a simple "yii2-user-migrate" console action command that would take care of this.

I'll try implement a PR

tsdogs commented 6 years ago

Thinking about this (though it works) it could simply be integrated into existing migrations. They should simply check for the existence of the table (or field) and apply only if it does not exist.

I'll try to create a better PR

tonydspaniard commented 6 years ago

Thanks @tsdogs for your hard work. I'll review everything when free of time.

tsdogs commented 6 years ago

Sure, no hurry, and thanks to you for your HARD work :-)

bryan1996akl commented 6 years ago

-Background:

I encountered problems with dektrium/yii2-user when updating yiisoft/yii2 above 2.0.11.2 However, I also encountered problems using 2amigos/usuario with yiisoft/yii2 2.0.11.2 My production server was running on 2.0.11.2 This documents my successful migration (seems to be successful). It's obviously advised to perform and test on a test server before production

ALTER TABLE user ADD COLUMN auth_tf_key varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL; ALTER TABLE user ADD COLUMN auth_tf_enabled tinyint(1) DEFAULT '0'; ALTER TABLE user ADD COLUMN password_changed_at int(11) DEFAULT NULL;

UPDATE user SET auth_tf_key = ''; UPDATE user SET auth_tf_enabled = 0; UPDATE user SET password_changed_at = NULL;

ALTER TABLE auth_assignment ADD KEY auth_assignment_user_id_idx (user_id);

ALTER TABLE social_account RENAME INDEX account_unique TO idx_social_account_provider_client_id; ALTER TABLE social_account RENAME INDEX account_unique_code TO idx_social_account_code; ALTER TABLE social_account RENAME INDEX fk_user_account TO fk_social_account_user;

ALTER TABLE token RENAME INDEX token_unique TO idx_token_user_id_code_type;

ALTER TABLE user RENAME INDEX user_unique_username TO idx_user_username; ALTER TABLE user RENAME INDEX user_unique_email TO idx_user_email;

ALTER TABLE profile MODIFY user_id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

MySQL said: Documentation

1832 - Cannot change column 'user_id': used in a foreign key constraint 'fk_user_profile'

*To fix the above, un-tick "Enable foreign key checks" (using Phpmyadmin)

ALTER TABLE user MODIFY id int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE profile DROP FOREIGN KEY fk_user_profile, ADD CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

ALTER TABLE social_account DROP FOREIGN KEY fk_user_account, ADD CONSTRAINT fk_social_account_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

ALTER TABLE token DROP FOREIGN KEY fk_user_token, ADD CONSTRAINT fk_token_user FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;

return [ ... 'modules' => [ 'user' => Da\User\Module::class, 'administratorPermissionName' => 'admin', // yours may be different ], ... 'controllerMap' => ... 'migrate' => [ 'class' => \yii\console\controllers\MigrateController::class, 'migrationPath' => [ '@app/migrations', '@yii/rbac/migrations', ], 'migrationNamespaces' => [ 'Da\User\Migration', ], ], ... ], 'components' => [ ... 'authManager' => [ //'class' => 'yii\rbac\DbManager', 'class' => 'Da\User\Component\AuthDbManagerComponent', ], ... ], ...
];

return [ ... 'modules' => [ 'user' => [ 'class' => Da\User\Module::class, 'administratorPermissionName' => 'admin', //yours may be different ], ], ...
];

use Yii; use Da\User\Model\User as BaseUser; //this line previously had the dektrium\user\models\User

class User extends BaseUser { ... }

You should see the following output: Yii Migration Tool (based on Yii v2.0.15.1)

Creating migration history table "migration"...Done. Total 9 new migrations to be applied: Da\User\Migration\m000000_000001_create_user_table Da\User\Migration\m000000_000002_create_profile_table Da\User\Migration\m000000_000003_create_social_account_table Da\User\Migration\m000000_000004_create_token_table Da\User\Migration\m000000_000005_add_last_login_at Da\User\Migration\m000000_000006_add_two_factor_fields Da\User\Migration\m000000_000007_enable_password_expiration m140506_102106_rbac_init m170907_052038_rbac_add_index_on_auth_assignment_user_id

Apply the above migrations? (yes|no) [no]:yes

yes *** applying Da\User\Migration\m000000_000001_create_user_table

create table {{%user}} ... done (time: 0.286s) create unique index idx_user_username on {{%user}} (username) ... done (time: 0.279s) create unique index idx_user_email on {{%user}} (email) ... done (time: 0.279s) *** applied Da\User\Migration\m000000_000001_create_user_table (time: 0.905s)

*** applying Da\User\Migration\m000000_000002_create_profile_table

create table {{%profile}} ... done (time: 0.357s) add foreign key fk_profile_user: {{%profile}} (user_id) references {{%user}} (id) ... done (time: 0.796s) *** applied Da\User\Migration\m000000_000002_create_profile_table (time: 1.198s)

*** applying Da\User\Migration\m000000_000003_create_social_account_table

create table {{%social_account}} ... done (time: 0.290s) create unique index idx_social_account_provider_client_id on {{%social_account}} (provider,client_id) ... done (time: 0.257s) create unique index idx_social_account_code on {{%social_account}} (code) ... done (time: 0.213s) add foreign key fk_social_account_user: {{%social_account}} (user_id) references {{%user}} (id) ... done (time: 0.896s) *** applied Da\User\Migration\m000000_000003_create_social_account_table (time: 1.701s)

*** applying Da\User\Migration\m000000_000004_create_token_table

create table {{%token}} ... done (time: 0.290s) create unique index idx_token_user_id_code_type on {{%token}} (user_id,code,type) ... done (time: 0.224s) add foreign key fk_token_user: {{%token}} (user_id) references {{%user}} (id) ... done (time: 0.763s) *** applied Da\User\Migration\m000000_000004_create_token_table (time: 1.322s)

*** applying Da\User\Migration\m000000_000005_add_last_login_at

add column last_login_at integer to table {{%user}} ... done (time: 0.615s) *** applied Da\User\Migration\m000000_000005_add_last_login_at (time: 0.649s)

*** applying Da\User\Migration\m000000_000006_add_two_factor_fields

add column auth_tf_key string(16) to table {{%user}} ... done (time: 0.592s) add column auth_tf_enabled boolean DEFAULT FALSE to table {{%user}} ... done (time: 0.661s) *** applied Da\User\Migration\m000000_000006_add_two_factor_fields (time: 1.288s)

*** applying Da\User\Migration\m000000_000007_enable_password_expiration

add column password_changed_at integer NULL DEFAULT NULL to table {{%user}} ... done (time: 0.537s) *** applied Da\User\Migration\m000000_000007_enable_password_expiration (time: 0.582s)

*** applying m140506_102106_rbac_init

create table {{%auth_rule}} ... done (time: 0.292s) create table {{%auth_item}} ... done (time: 0.358s) create index idx-auth_item-type on {{%auth_item}} (type) ... done (time: 0.223s) create table {{%auth_item_child}} ... done (time: 0.369s) create table {{%auth_assignment}} ... done (time: 0.291s) *** applied m140506_102106_rbac_init (time: 1.578s)

*** applying m170907_052038_rbac_add_index_on_auth_assignment_user_id

create index auth_assignment_user_id_idx on {{%auth_assignment}} (user_id) ... done (time: 0.268s) *** applied m170907_052038_rbac_add_index_on_auth_assignment_user_id (time: 0.313s)

9 migrations were applied.

Migrated up successfully.