summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/migrations/2019_11_29_000000_create_questions_table.php145
-rw-r--r--db/migrations/Reference.php8
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;
}
}