BookStack Code Mirror - bookstack/blob - database/migrations/2018_08_04_115700_create_bookshelves_table.php

   1 <?php
   2
   3 use Illuminate\Database\Migrations\Migration;
   4 use Illuminate\Database\Schema\Blueprint;
   5 use Illuminate\Support\Facades\DB;
   6 use Illuminate\Support\Facades\Schema;
   7
   8 return new class extends Migration
   9 {
  10     /**
  11      * Run the migrations.
  12      *
  13      * @return void
  14      */
  15     public function up()
  16     {
  17
  18         // Convert the existing entity tables to InnoDB.
  19         // Wrapped in try-catch just in the event a different database system is used
  20         // which does not support InnoDB but does support all required features
  21         // like foreign key references.
  22         try {
  23             $prefix = DB::getTablePrefix();
  24             DB::statement("ALTER TABLE {$prefix}pages ENGINE = InnoDB;");
  25             DB::statement("ALTER TABLE {$prefix}chapters ENGINE = InnoDB;");
  26             DB::statement("ALTER TABLE {$prefix}books ENGINE = InnoDB;");
  27         } catch (Exception $exception) {
  28         }
  29
  30         // Here we have table drops before the creations due to upgrade issues
  31         // people were having due to the bookshelves_books table creation failing.
  32         if (Schema::hasTable('bookshelves_books')) {
  33             Schema::drop('bookshelves_books');
  34         }
  35
  36         if (Schema::hasTable('bookshelves')) {
  37             Schema::drop('bookshelves');
  38         }
  39
  40         Schema::create('bookshelves', function (Blueprint $table) {
  41             $table->increments('id');
  42             $table->string('name', 180);
  43             $table->string('slug', 180);
  44             $table->text('description');
  45             $table->integer('created_by')->nullable()->default(null);
  46             $table->integer('updated_by')->nullable()->default(null);
  47             $table->boolean('restricted')->default(false);
  48             $table->integer('image_id')->nullable()->default(null);
  49             $table->timestamps();
  50
  51             $table->index('slug');
  52             $table->index('created_by');
  53             $table->index('updated_by');
  54             $table->index('restricted');
  55         });
  56
  57         Schema::create('bookshelves_books', function (Blueprint $table) {
  58             $table->integer('bookshelf_id')->unsigned();
  59             $table->integer('book_id')->unsigned();
  60             $table->integer('order')->unsigned();
  61
  62             $table->primary(['bookshelf_id', 'book_id']);
  63
  64             $table->foreign('bookshelf_id')->references('id')->on('bookshelves')
  65                 ->onUpdate('cascade')->onDelete('cascade');
  66             $table->foreign('book_id')->references('id')->on('books')
  67                 ->onUpdate('cascade')->onDelete('cascade');
  68         });
  69
  70         // Delete old bookshelf permissions
  71         // Needed to to issues upon upgrade.
  72         DB::table('role_permissions')->where('name', 'like', 'bookshelf-%')->delete();
  73
  74         // Copy existing role permissions from Books
  75         $ops = ['View All', 'View Own', 'Create All', 'Create Own', 'Update All', 'Update Own', 'Delete All', 'Delete Own'];
  76         foreach ($ops as $op) {
  77             $dbOpName = strtolower(str_replace(' ', '-', $op));
  78             $roleIdsWithBookPermission = DB::table('role_permissions')
  79                 ->leftJoin('permission_role', 'role_permissions.id', '=', 'permission_role.permission_id')
  80                 ->leftJoin('roles', 'roles.id', '=', 'permission_role.role_id')
  81                 ->where('role_permissions.name', '=', 'book-' . $dbOpName)->get(['roles.id'])->pluck('id');
  82
  83             $permId = DB::table('role_permissions')->insertGetId([
  84                 'name'         => 'bookshelf-' . $dbOpName,
  85                 'display_name' => $op . ' ' . 'BookShelves',
  86                 'created_at'   => \Carbon\Carbon::now()->toDateTimeString(),
  87                 'updated_at'   => \Carbon\Carbon::now()->toDateTimeString(),
  88             ]);
  89
  90             $rowsToInsert = $roleIdsWithBookPermission->filter(function ($roleId) {
  91                 return !is_null($roleId);
  92             })->map(function ($roleId) use ($permId) {
  93                 return [
  94                     'role_id'       => $roleId,
  95                     'permission_id' => $permId,
  96                 ];
  97             })->toArray();
  98
  99             // Assign view permission to all current roles
 100             DB::table('permission_role')->insert($rowsToInsert);
 101         }
 102     }
 103
 104     /**
 105      * Reverse the migrations.
 106      *
 107      * @return void
 108      */
 109     public function down()
 110     {
 111         // Drop created permissions
 112         $ops = ['bookshelf-create-all', 'bookshelf-create-own', 'bookshelf-delete-all', 'bookshelf-delete-own', 'bookshelf-update-all', 'bookshelf-update-own', 'bookshelf-view-all', 'bookshelf-view-own'];
 113
 114         $permissionIds = DB::table('role_permissions')->whereIn('name', $ops)
 115             ->get(['id'])->pluck('id')->toArray();
 116         DB::table('permission_role')->whereIn('permission_id', $permissionIds)->delete();
 117         DB::table('role_permissions')->whereIn('id', $permissionIds)->delete();
 118
 119         // Drop shelves table
 120         Schema::dropIfExists('bookshelves_books');
 121         Schema::dropIfExists('bookshelves');
 122
 123         // Drop related polymorphic items
 124         DB::table('activities')->where('entity_type', '=', 'BookStack\Entities\Models\Bookshelf')->delete();
 125         DB::table('views')->where('viewable_type', '=', 'BookStack\Entities\Models\Bookshelf')->delete();
 126         DB::table('entity_permissions')->where('restrictable_type', '=', 'BookStack\Entities\Models\Bookshelf')->delete();
 127         DB::table('tags')->where('entity_type', '=', 'BookStack\Entities\Models\Bookshelf')->delete();
 128         DB::table('search_terms')->where('entity_type', '=', 'BookStack\Entities\Models\Bookshelf')->delete();
 129         DB::table('comments')->where('entity_type', '=', 'BookStack\Entities\Models\Bookshelf')->delete();
 130     }
 131 };