Change comments table structure (drop foreign key on user_id)

8 files changed, 96 insertions, 14 deletions

diff --git a/app/Model/Comment.php b/app/Model/Comment.php
index 3aa9c027..e3ffc1be 100644
--- a/app/Model/Comment.php
+++ b/app/Model/Comment.php
@@ -42,7 +42,7 @@ class Comment extends Base
             ->table(self::TABLE)
             ->columns(
                 self::TABLE.'.id',
-                self::TABLE.'.date',
+                self::TABLE.'.date_creation',
                 self::TABLE.'.task_id',
                 self::TABLE.'.user_id',
                 self::TABLE.'.comment',
@@ -51,7 +51,7 @@ class Comment extends Base
                 User::TABLE.'.email'
             )
             ->join(User::TABLE, 'id', 'user_id')
-            ->orderBy(self::TABLE.'.date', 'ASC')
+            ->orderBy(self::TABLE.'.date_creation', 'ASC')
             ->eq(self::TABLE.'.task_id', $task_id)
             ->findAll();
     }
@@ -71,7 +71,7 @@ class Comment extends Base
                 self::TABLE.'.id',
                 self::TABLE.'.task_id',
                 self::TABLE.'.user_id',
-                self::TABLE.'.date',
+                self::TABLE.'.date_creation',
                 self::TABLE.'.comment',
                 User::TABLE.'.username',
                 User::TABLE.'.name'
@@ -105,7 +105,7 @@ class Comment extends Base
      */
     public function create(array $values)
     {
-        $values['date'] = time();
+        $values['date_creation'] = time();
         $comment_id = $this->persist(self::TABLE, $values);
 
         if ($comment_id) {
@@ -158,7 +158,6 @@ class Comment extends Base
     public function validateCreation(array $values)
     {
         $rules = array(
-            new Validators\Required('user_id', t('This value is required')),
             new Validators\Required('task_id', t('This value is required')),
         );
 
diff --git a/app/Model/User.php b/app/Model/User.php
index 83cf065b..726978e5 100644
--- a/app/Model/User.php
+++ b/app/Model/User.php
@@ -302,11 +302,21 @@ class User extends Base
     {
         return $this->db->transaction(function ($db) use ($user_id) {
 
-            // All assigned tasks are now unassigned
+            // All assigned tasks are now unassigned (no foreign key)
             if (! $db->table(Task::TABLE)->eq('owner_id', $user_id)->update(array('owner_id' => 0))) {
                 return false;
             }
 
+            // All assigned subtasks are now unassigned (no foreign key)
+            if (! $db->table(Subtask::TABLE)->eq('user_id', $user_id)->update(array('user_id' => 0))) {
+                return false;
+            }
+
+            // All comments are not assigned anymore (no foreign key)
+            if (! $db->table(Comment::TABLE)->eq('user_id', $user_id)->update(array('user_id' => 0))) {
+                return false;
+            }
+
             // All private projects are removed
             $project_ids = $db->table(Project::TABLE)
                            ->eq('is_private', 1)
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)
 {
diff --git a/app/Template/comment/show.php b/app/Template/comment/show.php
index 35394ccb..84077668 100644
--- a/app/Template/comment/show.php
+++ b/app/Template/comment/show.php
@@ -4,7 +4,12 @@
         <?php if (! empty($comment['email'])): ?>
             <?= $this->user->avatar($comment['email'], $comment['name'] ?: $comment['username']) ?>
         <?php endif ?>
-        <span class="comment-username"><?= $this->e($comment['name'] ?: $comment['username']) ?></span> @ <span class="comment-date"><?= dt('%B %e, %Y at %k:%M %p', $comment['date']) ?></span>
+
+        <?php if (! empty($comment['username'])): ?>
+            <span class="comment-username"><?= $this->e($comment['name'] ?: $comment['username']) ?></span> @
+        <?php endif ?>
+
+        <span class="comment-date"><?= dt('%B %e, %Y at %k:%M %p', $comment['date_creation']) ?></span>
     </p>
     <div class="comment-inner">
 
diff --git a/tests/units/CommentTest.php b/tests/units/CommentTest.php
index 36ff1352..295ac60e 100644
--- a/tests/units/CommentTest.php
+++ b/tests/units/CommentTest.php
@@ -17,16 +17,24 @@ class CommentTest extends Base
 
         $this->assertEquals(1, $p->create(array('name' => 'test1')));
         $this->assertEquals(1, $tc->create(array('title' => 'test', 'project_id' => 1, 'column_id' => 3, 'owner_id' => 1)));
-        $this->assertNotFalse($c->create(array('task_id' => 1, 'comment' => 'bla bla', 'user_id' => 1)));
+        $this->assertEquals(1, $c->create(array('task_id' => 1, 'comment' => 'bla bla', 'user_id' => 1)));
+        $this->assertEquals(2, $c->create(array('task_id' => 1, 'comment' => 'bla bla')));
 
         $comment = $c->getById(1);
-
         $this->assertNotEmpty($comment);
         $this->assertEquals('bla bla', $comment['comment']);
         $this->assertEquals(1, $comment['task_id']);
         $this->assertEquals(1, $comment['user_id']);
         $this->assertEquals('admin', $comment['username']);
-        $this->assertNotEmpty($comment['date']);
+        $this->assertEquals(time(), $comment['date_creation'], '', 3);
+
+        $comment = $c->getById(2);
+        $this->assertNotEmpty($comment);
+        $this->assertEquals('bla bla', $comment['comment']);
+        $this->assertEquals(1, $comment['task_id']);
+        $this->assertEquals(0, $comment['user_id']);
+        $this->assertEquals('', $comment['username']);
+        $this->assertEquals(time(), $comment['date_creation'], '', 3);
     }
 
     public function testGetAll()
@@ -103,7 +111,7 @@ class CommentTest extends Base
         $this->assertFalse($result[0]);
 
         $result = $c->validateCreation(array('task_id' => 1, 'comment' => 'bla'));
-        $this->assertFalse($result[0]);
+        $this->assertTrue($result[0]);
 
         $result = $c->validateCreation(array('comment' => 'bla'));
         $this->assertFalse($result[0]);
diff --git a/tests/units/UserTest.php b/tests/units/UserTest.php
index a596918e..fcdf3934 100644
--- a/tests/units/UserTest.php
+++ b/tests/units/UserTest.php
@@ -3,6 +3,8 @@
 require_once __DIR__.'/Base.php';
 
 use Model\User;
+use Model\Subtask;
+use Model\Comment;
 use Model\Task;
 use Model\TaskCreation;
 use Model\TaskFinder;
@@ -133,10 +135,14 @@ class UserTest extends Base
         $tc = new TaskCreation($this->container);
         $tf = new TaskFinder($this->container);
         $p = new Project($this->container);
+        $s = new Subtask($this->container);
+        $c = new Comment($this->container);
 
         $this->assertNotFalse($u->create(array('username' => 'toto', 'password' => '123456', 'name' => 'Toto')));
         $this->assertEquals(1, $p->create(array('name' => 'Project #1')));
         $this->assertEquals(1, $tc->create(array('title' => 'Task #1', 'project_id' => 1, 'owner_id' => 2)));
+        $this->assertEquals(1, $s->create(array('title' => 'Subtask #1', 'user_id' => 2, 'task_id' => 1)));
+        $this->assertEquals(1, $c->create(array('comment' => 'foobar', 'user_id' => 2, 'task_id' => 1)));
 
         $task = $tf->getById(1);
         $this->assertEquals(1, $task['id']);
@@ -152,6 +158,16 @@ class UserTest extends Base
         $this->assertEquals(1, $task['id']);
         $this->assertEquals(0, $task['owner_id']);
 
+        // Make sure that assigned subtasks are unassigned after removing the user
+        $subtask = $s->getById(1);
+        $this->assertEquals(1, $subtask['id']);
+        $this->assertEquals(0, $subtask['user_id']);
+
+        // Make sure that comments are not related to the user anymore
+        $comment = $c->getById(1);
+        $this->assertEquals(1, $comment['id']);
+        $this->assertEquals(0, $comment['user_id']);
+
         // Make sure that private projects are also removed
         $user_id1 = $u->create(array('username' => 'toto1', 'password' => '123456', 'name' => 'Toto'));
         $user_id2 = $u->create(array('username' => 'toto2', 'password' => '123456', 'name' => 'Toto'));