From 22b26d0b4d68f0492275ec93eb4df1a975a8fc80 Mon Sep 17 00:00:00 2001 From: Frederic Guillot Date: Sat, 20 Jun 2015 17:53:49 -0400 Subject: Change comments table structure (drop foreign key on user_id) --- app/Model/Comment.php | 9 ++++----- app/Model/User.php | 12 +++++++++++- app/Schema/Mysql.php | 10 +++++++++- app/Schema/Postgres.php | 11 ++++++++++- app/Schema/Sqlite.php | 29 ++++++++++++++++++++++++++++- app/Template/comment/show.php | 7 ++++++- tests/units/CommentTest.php | 16 ++++++++++++---- tests/units/UserTest.php | 16 ++++++++++++++++ 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 @@ user->avatar($comment['email'], $comment['name'] ?: $comment['username']) ?> - e($comment['name'] ?: $comment['username']) ?> @ + + + e($comment['name'] ?: $comment['username']) ?> @ + + +

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')); -- cgit v1.2.3