diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/migrations/2019_11_29_000000_create_questions_table.php | 145 | ||||
| -rw-r--r-- | db/migrations/Reference.php | 8 |
2 files changed, 151 insertions, 2 deletions
diff --git a/db/migrations/2019_11_29_000000_create_questions_table.php b/db/migrations/2019_11_29_000000_create_questions_table.php new file mode 100644 index 00000000..1d73aaeb --- /dev/null +++ b/db/migrations/2019_11_29_000000_create_questions_table.php @@ -0,0 +1,145 @@ +<?php + +declare(strict_types=1); + +namespace Engelsystem\Migrations; + +use Engelsystem\Database\Migration\Migration; +use Illuminate\Database\Eloquent\Collection; +use Illuminate\Database\Schema\Blueprint; +use stdClass; + +/** + * This migration creates the "questions" table and migrates the existing "Questions" records. + */ +class CreateQuestionsTable extends Migration +{ + use ChangesReferences; + use Reference; + + /** + * @return void + */ + public function up(): void + { + $hasPreviousQuestionsTable = $this->schema->hasTable('Questions'); + + if ($hasPreviousQuestionsTable) { + // Rename because some SQL DBMS handle identifiers case insensitive + $this->schema->rename('Questions', 'PreviousQuestions'); + } + + $this->createNewQuestionsTable(); + + if ($hasPreviousQuestionsTable) { + $this->copyPreviousToNewQuestionsTable(); + $this->changeReferences( + 'PreviousQuestions', + 'QID', + 'questions', + 'id', + 'unsignedInteger' + ); + $this->schema->drop('PreviousQuestions'); + } + } + + /** + * @return void + */ + public function down(): void + { + // Rename as some SQL DBMS handle identifiers case insensitive + $this->schema->rename('questions', 'new_questions'); + + $this->createPreviousQuestionsTable(); + $this->copyNewToPreviousQuestionsTable(); + $this->changeReferences( + 'new_questions', + 'id', + 'Questions', + 'QID', + 'unsignedInteger' + ); + + $this->schema->drop('new_questions'); + } + + /** + * @return void + */ + private function createNewQuestionsTable(): void + { + $this->schema->create('questions', + function (Blueprint $table) { + $table->increments('id'); + $this->referencesUser($table, false); + $table->text('text'); + $table->text('answer') + ->nullable(); + $this->references($table, 'users', 'answerer_id') + ->nullable(); + }); + } + + /** + * @return void + */ + private function copyPreviousToNewQuestionsTable(): void + { + $connection = $this->schema->getConnection(); + /** @var stdClass[] $previousQuestionsRecords */ + $previousQuestionsRecords = $connection + ->table('PreviousQuestions') + ->get(); + + foreach ($previousQuestionsRecords as $previousQuestionRecord) { + $connection->table('questions')->insert([ + 'id' => $previousQuestionRecord->QID, + 'user_id' => $previousQuestionRecord->UID, + 'text' => $previousQuestionRecord->Question, + 'answerer_id' => $previousQuestionRecord->AID, + 'answer' => $previousQuestionRecord->Answer, + ]); + } + } + + /** + * @return void + */ + private function createPreviousQuestionsTable(): void + { + $this->schema->create('Questions', + function (Blueprint $table) { + $table->increments('QID'); + $this->references($table, 'users', 'UID'); + $table->text('Question'); + $this->references($table, 'users', 'AID') + ->nullable(); + $table->text('Answer') + ->nullable(); + }); + } + + /** + * @return void + */ + private function copyNewToPreviousQuestionsTable(): void + { + $connection = $this->schema->getConnection(); + /** @var Collection|stdClass[] $questionRecords */ + $questionRecords = $connection + ->table('new_questions') + ->get(); + + foreach ($questionRecords as $questionRecord) { + $connection->table('Questions')->insert([ + 'QID' => $questionRecord->id, + 'UID' => $questionRecord->user_id, + 'Question' => $questionRecord->text, + 'AID' => $questionRecord->answerer_id, + 'Answer' => $questionRecord->answer, + ]); + } + } +} diff --git a/db/migrations/Reference.php b/db/migrations/Reference.php index 719bee76..90211bc1 100644 --- a/db/migrations/Reference.php +++ b/db/migrations/Reference.php @@ -3,6 +3,7 @@ namespace Engelsystem\Migrations; use Illuminate\Database\Schema\Blueprint; +use Illuminate\Database\Schema\ColumnDefinition; trait Reference { @@ -20,10 +21,11 @@ trait Reference * @param string $targetTable * @param string $fromColumn * @param bool $setPrimary + * @return ColumnDefinition */ - protected function references(Blueprint $table, $targetTable, $fromColumn, $setPrimary = false) + protected function references(Blueprint $table, $targetTable, $fromColumn, $setPrimary = false): ColumnDefinition { - $table->unsignedInteger($fromColumn); + $col = $table->unsignedInteger($fromColumn); if ($setPrimary) { $table->primary($fromColumn); @@ -33,5 +35,7 @@ trait Reference ->references('id')->on($targetTable) ->onUpdate('cascade') ->onDelete('cascade'); + + return $col; } } |