Change comments table structure (drop foreign key on user_id)

3 files changed, 47 insertions, 3 deletions

diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php
index 21b7a4de..34a609cf 100644
--- a/app/Schema/Mysql.php
+++ b/app/Schema/Mysql.php
@@ -6,7 +6,15 @@ use PDO;
 use Core\Security;
 use Model\Link;
 
-const VERSION = 74;
+const VERSION = 75;
+
+function version_75($pdo)
+{
+    $pdo->exec('ALTER TABLE comments DROP FOREIGN KEY comments_ibfk_2');
+    $pdo->exec('ALTER TABLE comments MODIFY task_id INT NOT NULL');
+    $pdo->exec('ALTER TABLE comments CHANGE COLUMN `user_id` `user_id` INT DEFAULT 0');
+    $pdo->exec('ALTER TABLE comments CHANGE COLUMN `date` `date_creation` INT NOT NULL');
+}
 
 function version_74($pdo)
 {
diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php
index a8b76399..e32b0b9b 100644
--- a/app/Schema/Postgres.php
+++ b/app/Schema/Postgres.php
@@ -6,7 +6,16 @@ use PDO;
 use Core\Security;
 use Model\Link;
 
-const VERSION = 54;
+const VERSION = 55;
+
+function version_55($pdo)
+{
+    $pdo->exec('ALTER TABLE comments DROP CONSTRAINT IF EXISTS comments_user_id_fkey');
+    $pdo->exec("ALTER TABLE comments ALTER COLUMN task_id SET NOT NULL");
+    $pdo->exec("ALTER TABLE comments ALTER COLUMN user_id SET DEFAULT 0");
+    $pdo->exec('ALTER TABLE comments RENAME COLUMN "date" TO "date_creation"');
+    $pdo->exec("ALTER TABLE comments ALTER COLUMN date_creation SET NOT NULL");
+}
 
 function version_54($pdo)
 {
diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php
index 6ad7f34a..ebc3b064 100644
--- a/app/Schema/Sqlite.php
+++ b/app/Schema/Sqlite.php
@@ -6,7 +6,34 @@ use Core\Security;
 use PDO;
 use Model\Link;
 
-const VERSION = 71;
+const VERSION = 72;
+
+function version_72($pdo)
+{
+    $pdo->exec(
+        'ALTER TABLE comments RENAME TO comments_bak'
+    );
+
+    $pdo->exec(
+        'CREATE TABLE comments (
+            id INTEGER PRIMARY KEY,
+            task_id INTEGER NOT NULL,
+            user_id INTEGER DEFAULT 0,
+            date_creation INTEGER NOT NULL,
+            comment TEXT NOT NULL,
+            reference VARCHAR(50),
+            FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
+        )'
+    );
+
+    $pdo->exec(
+        'INSERT INTO comments SELECT * FROM comments_bak'
+    );
+
+    $pdo->exec(
+        'DROP TABLE comments_bak'
+    );
+}
 
 function version_71($pdo)
 {