Add questions table migration

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;
     }
 }