diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/migrations/2019_07_21_000002_create_teams_roles_and_permissions_tables.php | 464 |
1 files changed, 464 insertions, 0 deletions
diff --git a/db/migrations/2019_07_21_000002_create_teams_roles_and_permissions_tables.php b/db/migrations/2019_07_21_000002_create_teams_roles_and_permissions_tables.php new file mode 100644 index 00000000..80f49841 --- /dev/null +++ b/db/migrations/2019_07_21_000002_create_teams_roles_and_permissions_tables.php @@ -0,0 +1,464 @@ +<?php + +namespace Engelsystem\Migrations; + +use Engelsystem\Database\Migration\Migration; +use Engelsystem\Models\Auth\Permission; +use Engelsystem\Models\Auth\Role; +use Engelsystem\Models\Team; +use Engelsystem\Models\User\User; +use Illuminate\Database\Eloquent\Collection; +use Illuminate\Database\Schema\Blueprint; +use stdClass; + +class CreateTeamsRolesAndPermissionsTables extends Migration +{ + use ChangesReferences; + use Reference; + + /** + * Run the migration + */ + public function up() + { + $this->schema->create('teams', function (Blueprint $table) { + $table->increments('id'); + + $table->string('name')->unique(); + $table->text('description')->nullable(); + + $table->string('contact_name')->nullable()->default(null); + $table->string('contact_email')->nullable()->default(null); + $table->string('contact_dect', 40)->nullable()->default(null); + + $table->boolean('restricted')->default(false); + $table->boolean('self_signup')->default(true); + $table->boolean('requires_drivers_license')->default(false); + $table->boolean('show_on_frontend')->default(true); + $table->boolean('show_on_dashboard')->default(true); + + $table->timestamps(); + }); + + $this->schema->create('roles', function (Blueprint $table) { + $table->increments('id'); + + $table->string('name')->unique(); + $table->text('description')->nullable(); + + $table->timestamps(); + }); + + $this->schema->create('permissions', function (Blueprint $table) { + $table->increments('id'); + + $table->string('name')->unique(); + $table->text('description')->nullable(); + + $table->timestamps(); + }); + + $this->schema->create('team_user', function (Blueprint $table) { + $table->increments('id'); + + $this->referencesUser($table); + $this->references($table, 'teams', 'team_id'); + $table->boolean('confirmed')->default(true); + + $table->timestamps(); + + $table->unique(['team_id', 'user_id']); + }); + + $this->schema->create('supporter_team', function (Blueprint $table) { + $table->increments('id'); + + $this->referencesUser($table); + $this->references($table, 'teams', 'team_id'); + + $table->timestamps(); + + $table->unique(['team_id', 'user_id']); + }); + + $this->schema->create('role_team', function (Blueprint $table) { + $table->increments('id'); + + $this->references($table, 'teams', 'team_id'); + $this->references($table, 'roles', 'role_id'); + + $table->timestamps(); + + $table->unique(['role_id', 'team_id']); + }); + + $this->schema->create('permission_role', function (Blueprint $table) { + $table->increments('id'); + + $this->references($table, 'roles', 'role_id'); + $this->references($table, 'permissions', 'permission_id'); + + $table->timestamps(); + + $table->unique(['permission_id', 'role_id']); + }); + + if ($this->schema->hasTable('AngelTypes')) { + $connection = $this->schema->getConnection(); + $connection + ->table('Groups') + ->update([ + 'UID' => $connection->raw('UID / 10') + ]); + + /** @var Team[] $teams */ + $teams = []; + /** @var stdClass[] $users */ + $angelTypes = $connection->table('AngelTypes')->get(); + foreach ($angelTypes as $data) { + $team = new Team([ + 'name' => $data->name, + 'description' => $data->description ?: null, + 'contact_name' => $data->contact_name ?: null, + 'contact_email' => $data->contact_email ?: null, + 'contact_dect' => $data->contact_dect ?: null, + 'restricted' => (bool)$data->restricted, + 'self_signup' => !(bool)$data->no_self_signup, + 'requires_drivers_license' => (bool)$data->requires_driver_license, + 'show_on_dashboard' => (bool)$data->show_on_dashboard, + ]); + $team->setAttribute('id', $data->id); + + $team->save(); + + $teams[$team->id] = $team; + $teams[$team->name] = $team; + } + + /** @var Role[] $roles */ + $roles = []; + /** @var stdClass[] $groups */ + $groups = $connection->table('Groups')->get(); + foreach ($groups as $data) { + $role = new Role([ + 'name' => $data->Name, + 'description' => null, + ]); + $role->setAttribute('id', $data->UID); + + $role->save(); + + $roles[$role->id] = $role; + } + + /** @var Permission[] $permissions */ + $permissions = []; + /** @var stdClass[] $privileges */ + $privileges = $connection->table('Privileges')->get(); + foreach ($privileges as $data) { + $permission = new Permission([ + 'name' => $data->name, + 'description' => $data->desc ?: null, + ]); + $permission->setAttribute('id', $data->id); + + $permission->save(); + + $permissions[$permission->id] = $permission; + } + + $this->changeReferences( + 'AngelTypes', + 'id', + 'teams', + 'id' + ); + + $this->changeReferences( + 'Groups', + 'UID', + 'roles', + 'id' + ); + + $this->changeReferences( + 'Privileges', + 'id', + 'permissions', + 'id' + ); + + /** @var stdClass[] $groupPrivileges */ + $groupPrivileges = $connection->table('GroupPrivileges')->get(); + foreach ($groupPrivileges as $data) { + $roles[$data->group_id] + ->permissions() + ->attach($permissions[$data->privilege_id]); + } + + foreach ($roles as $role) { + if ($role->name == 'Guest' || Team::whereName($role->name)->count()) { + continue; + } + + $team = new Team([ + 'name' => $role->name, + 'description' => $role->description, + 'restricted' => $role->name != 'Angel', + 'show_on_frontend' => !in_array($role->name, ['Developer']), + 'show_on_dashboard' => !in_array($role->name, ['Developer', 'News Admin']), + ]); + + $team->save(); + + $teams[$team->id] = $team; + $teams[$team->name] = $team; + } + + /** @var stdClass $userAngelTypes */ + $userAngelTypes = $connection + ->table('UserAngelTypes') + ->orderBy('user_id') + ->orderBy('angeltype_id') + ->get(); + foreach ($userAngelTypes as $userAngelType) { + $teams[$userAngelType->angeltype_id]->users()->attach( + $userAngelType->user_id, + ['confirmed' => (bool)$userAngelType->confirm_user_id] + ); + + if ($userAngelType->supporter) { + $teams[$userAngelType->angeltype_id]->supporters()->attach($userAngelType->user_id); + } + } + + /** @var stdClass[] $userGroups */ + $userGroups = $connection + ->table('UserGroups') + ->leftJoin('Groups', 'group_id', '=', 'Groups.UID') + ->orderBy('UserGroups.uid') + ->orderBy('Groups.Name') + ->get(['Groups.Name AS group_name', 'UserGroups.uid AS user_id']); + foreach ($userGroups as $userGroup) { + $teams[$userGroup->group_name]->users()->attach($userGroup->user_id); + } + + /** @var stdClass[] $teamRoles */ + $teamRoles = $connection + ->table('roles') + ->join('teams', 'roles.name', '=', 'teams.name') + ->get(['roles.id AS role_id', 'teams.id AS team_id']); + foreach ($teamRoles as $teamRole) { + $teams[$teamRole->team_id]->roles()->attach($teamRole->role_id); + } + + $this->schema->table('GroupPrivileges', function (Blueprint $table) { + $table->unsignedInteger('id')->unsigned()->autoIncrement()->change(); + }); + } + + $this->schema->dropIfExists('GroupPrivileges'); + $this->schema->dropIfExists('UserGroups'); + $this->schema->dropIfExists('UserAngelTypes'); + $this->schema->dropIfExists('Privileges'); + $this->schema->dropIfExists('Groups'); + $this->schema->dropIfExists('AngelTypes'); + } + + /** + * Reverse the migration + */ + public function down() + { + $connection = $this->schema->getConnection(); + + $this->schema->create('AngelTypes', function (Blueprint $table) { + $table->increments('id'); + + $table->string('name', 50); + $table->boolean('restricted'); + $table->text('description'); + $table->boolean('requires_driver_license'); + $table->boolean('no_self_signup'); + $table->string('contact_name', 250)->nullable(); + $table->string('contact_dect', 40)->nullable(); + $table->string('contact_email', 250)->nullable(); + $table->boolean('show_on_dashboard'); + + $table->unique('name'); + }); + + $this->schema->create('Groups', function (Blueprint $table) { + $table->string('Name', 35); + + $table->integer('UID')->primary(); + }); + + $this->schema->create('Privileges', function (Blueprint $table) { + $table->increments('id'); + + $table->string('name', 128)->unique(); + $table->string('desc', 1024); + }); + + $this->schema->create('UserAngelTypes', function (Blueprint $table) { + $table->increments('id'); + + $this->referencesUser($table); + $this->references($table, 'teams', 'angeltype_id'); + $this->references($table, 'users', 'confirm_user_id')->nullable(); + $table->boolean('supporter')->nullable(); + + $table->index(['user_id', 'angeltype_id', 'confirm_user_id']); + $table->index(['angeltype_id']); + $table->index(['confirm_user_id']); + $table->index(['supporter']); + }); + + $this->schema->create('UserGroups', function (Blueprint $table) { + $table->increments('id'); + + $this->references($table, 'users', 'uid'); + $this->references($table, 'roles', 'group_id'); + + $table->index(['uid', 'group_id']); + $table->index(['group_id']); + }); + + $this->schema->create('GroupPrivileges', function (Blueprint $table) { + $table->increments('id'); + + $this->references($table, 'roles', 'group_id'); + $this->references($table, 'permissions', 'privilege_id'); + + $table->index(['group_id', 'privilege_id']); + $table->index(['privilege_id']); + }); + + $roleNames = Role::all('name')->pluck('name')->toArray(); + $teams = Team::query() + ->whereNotIn('name', $roleNames) + ->get(); + foreach ($teams as $team) { + /** @var Team $team */ + $connection + ->table('AngelTypes') + ->insert([ + 'id' => $team->id, + 'name' => $team->name, + 'description' => $team->description ?: '', + 'restricted' => $team->restricted, + 'requires_driver_license' => $team->requires_drivers_license, + 'no_self_signup' => !$team->self_signup, + 'contact_name' => $team->contact_name, + 'contact_dect' => $team->contact_dect, + 'contact_email' => $team->contact_email, + 'show_on_dashboard' => $team->show_on_dashboard + ]); + } + + foreach (Role::all() as $role) { + $connection + ->table('Groups') + ->insert([ + 'Name' => $role->name, + 'UID' => $role->id, + ]); + } + + foreach (Permission::all() as $permission) { + $connection + ->table('Privileges') + ->insert([ + 'id' => $permission->id, + 'name' => $permission->name, + 'desc' => $permission->description + ]); + } + + + $teams = Team::with('users', 'supporters') + ->whereNotIn('name', $roleNames) + ->get(); + foreach ($teams as $team) { + /** @var User[]|Collection $supporters */ + $supporters = $team->supporters; + foreach ($team->users as $user) { + /** @var User $user */ + $connection + ->table('UserAngelTypes') + ->insert([ + 'user_id' => $user->id, + 'angeltype_id' => $team->id, + 'confirm_user_id' => $user->pivot->confirmed ?: null, + 'supporter' => $supporters->where('id', '=', $user->id)->count(), + ]); + } + } + + $teams = Team::with('users') + ->whereIn('name', $roleNames) + ->get(); + foreach ($teams as $team) { + foreach ($team->users as $user) { + $role = Role::whereName($team->name)->first(); + /** @var User $user */ + $connection + ->table('UserGroups') + ->insert([ + 'uid' => $user->id, + 'group_id' => $role ? $role->id : $team->id, + ]); + } + } + + foreach (Permission::with('roles')->get() as $permission) { + foreach ($permission->roles as $role) { + $connection + ->table('GroupPrivileges') + ->insert([ + 'group_id' => $role->id, + 'privilege_id' => $permission->id, + ]); + } + } + + $this->schema->drop('permission_role'); + $this->schema->drop('role_team'); + $this->schema->drop('supporter_team'); + $this->schema->drop('team_user'); + + $this->changeReferences( + 'teams', + 'id', + 'AngelTypes', + 'id', + 'unsignedInteger' + ); + + $this->changeReferences( + 'roles', + 'id', + 'Groups', + 'UID', + 'integer' + ); + + $this->changeReferences( + 'permissions', + 'id', + 'Privileges', + 'id' + ); + + $this->schema->drop('permissions'); + $this->schema->drop('roles'); + $this->schema->drop('teams'); + + $connection + ->table('Groups') + ->update([ + 'UID' => $connection->raw('UID * 10') + ]); + } +} |