Added models for tags

7 files changed, 338 insertions, 3 deletions

diff --git a/app/Core/Base.php b/app/Core/Base.php
index 7b4462e2..6712cbce 100644
--- a/app/Core/Base.php
+++ b/app/Core/Base.php
@@ -86,6 +86,7 @@ use Pimple\Container;
  * @property \Kanboard\Model\SubtaskModel                        $subtaskModel
  * @property \Kanboard\Model\SubtaskTimeTrackingModel            $subtaskTimeTrackingModel
  * @property \Kanboard\Model\SwimlaneModel                       $swimlaneModel
+ * @property \Kanboard\Model\TagModel                            $tagModel
  * @property \Kanboard\Model\TaskModel                           $taskModel
  * @property \Kanboard\Model\TaskAnalyticModel                   $taskAnalyticModel
  * @property \Kanboard\Model\TaskCreationModel                   $taskCreationModel
@@ -96,6 +97,7 @@ use Pimple\Container;
  * @property \Kanboard\Model\TaskModificationModel               $taskModificationModel
  * @property \Kanboard\Model\TaskPositionModel                   $taskPositionModel
  * @property \Kanboard\Model\TaskStatusModel                     $taskStatusModel
+ * @property \Kanboard\Model\TaskTagModel                        $taskTagModel
  * @property \Kanboard\Model\TaskMetadataModel                   $taskMetadataModel
  * @property \Kanboard\Model\TimezoneModel                       $timezoneModel
  * @property \Kanboard\Model\TransitionModel                     $transitionModel
diff --git a/app/Model/TagModel.php b/app/Model/TagModel.php
new file mode 100644
index 00000000..1be05a66
--- /dev/null
+++ b/app/Model/TagModel.php
@@ -0,0 +1,139 @@
+<?php
+
+namespace Kanboard\Model;
+
+use Kanboard\Core\Base;
+
+/**
+ * Class TagModel
+ *
+ * @package Kanboard\Model
+ * @author  Frederic Guillot
+ */
+class TagModel extends Base
+{
+    /**
+     * SQL table name
+     *
+     * @var string
+     */
+    const TABLE = 'tags';
+
+    /**
+     * Get all tags
+     *
+     * @access public
+     * @return array
+     */
+    public function getAll()
+    {
+        return $this->db->table(self::TABLE)->asc('name')->findAll();
+    }
+
+    /**
+     * Get all tags by project
+     *
+     * @access public
+     * @param  integer $project_id
+     * @return array
+     */
+    public function getAllByProject($project_id)
+    {
+        return $this->db->table(self::TABLE)->eq('project_id', $project_id)->asc('name')->findAll();
+    }
+
+    /**
+     * Get one tag
+     *
+     * @access public
+     * @param  integer $tag_id
+     * @return array|null
+     */
+    public function getById($tag_id)
+    {
+        return $this->db->table(self::TABLE)->eq('id', $tag_id)->findOne();
+    }
+
+    /**
+     * Get tag id from tag name
+     *
+     * @access public
+     * @param  int    $project_id
+     * @param  string $tag
+     * @return integer
+     */
+    public function getIdByName($project_id, $tag)
+    {
+        return $this->db
+            ->table(self::TABLE)
+            ->beginOr()
+                ->eq('project_id', 0)
+                ->eq('project_id', $project_id)
+            ->closeOr()
+            ->ilike('name', $tag)
+            ->asc('project_id')
+            ->findOneColumn('id');
+    }
+
+    /**
+     * Return tag id and create a new tag if necessary
+     *
+     * @access public
+     * @param  int    $project_id
+     * @param  string $tag
+     * @return bool|int
+     */
+    public function findOrCreateTag($project_id, $tag)
+    {
+        $tag_id = $this->getIdByName($project_id, $tag);
+
+        if (empty($tag_id)) {
+            $tag_id = $this->create($project_id, $tag);
+        }
+
+        return $tag_id;
+    }
+
+    /**
+     * Add a new tag
+     *
+     * @access public
+     * @param  int    $project_id
+     * @param  string $tag
+     * @return bool|int
+     */
+    public function create($project_id, $tag)
+    {
+        return $this->db->table(self::TABLE)->persist(array(
+            'project_id' => $project_id,
+            'name' => $tag,
+        ));
+    }
+
+    /**
+     * Update a tag
+     *
+     * @access public
+     * @param  integer $tag_id
+     * @param  string  $tag
+     * @return bool
+     */
+    public function update($tag_id, $tag)
+    {
+        return $this->db->table(self::TABLE)->eq('id', $tag_id)->update(array(
+            'name' => $tag,
+        ));
+    }
+
+    /**
+     * Remove a tag
+     *
+     * @access public
+     * @param  integer $tag_id
+     * @return bool
+     */
+    public function remove($tag_id)
+    {
+        return $this->db->table(self::TABLE)->eq('id', $tag_id)->remove();
+    }
+}
diff --git a/app/Model/TaskTagModel.php b/app/Model/TaskTagModel.php
new file mode 100644
index 00000000..74d82539
--- /dev/null
+++ b/app/Model/TaskTagModel.php
@@ -0,0 +1,125 @@
+<?php
+
+namespace Kanboard\Model;
+
+use Kanboard\Core\Base;
+
+/**
+ * Class TaskTagModel
+ *
+ * @package Kanboard\Model
+ * @author  Frederic Guillot
+ */
+class TaskTagModel extends Base
+{
+    /**
+     * SQL table name
+     *
+     * @var string
+     */
+    const TABLE = 'task_has_tags';
+
+    /**
+     * Get all tags associated to a task
+     *
+     * @access public
+     * @param  integer $task_id
+     * @return array
+     */
+    public function getAll($task_id)
+    {
+        return $this->db->table(TagModel::TABLE)
+            ->columns(TagModel::TABLE.'.id', TagModel::TABLE.'.name')
+            ->eq(self::TABLE.'.task_id', $task_id)
+            ->join(self::TABLE, 'tag_id', 'id')
+            ->findAll();
+    }
+
+    /**
+     * Get dictionary of tags
+     *
+     * @access public
+     * @param  integer $task_id
+     * @return array
+     */
+    public function getList($task_id)
+    {
+        $tags = $this->getAll($task_id);
+        return array_column($tags, 'name', 'id');
+    }
+
+    /**
+     * Add or update a list of tags to a task
+     *
+     * @access public
+     * @param integer  $project_id
+     * @param integer  $task_id
+     * @param string[] $tags
+     * @return boolean
+     */
+    public function save($project_id, $task_id, array $tags)
+    {
+        $task_tags = $this->getList($task_id);
+
+        return $this->addTags($project_id, $task_id, $task_tags, $tags) &&
+            $this->removeTags($task_id, $task_tags, $tags);
+    }
+
+    /**
+     * Associate a tag to a task
+     *
+     * @access public
+     * @param  integer  $task_id
+     * @param  integer  $tag_id
+     * @return boolean
+     */
+    public function associate($task_id, $tag_id)
+    {
+        return $this->db->table(self::TABLE)->insert(array(
+            'task_id' => $task_id,
+            'tag_id' => $tag_id,
+        ));
+    }
+
+    /**
+     * Dissociate a tag from a task
+     *
+     * @access public
+     * @param  integer  $task_id
+     * @param  integer  $tag_id
+     * @return boolean
+     */
+    public function dissociate($task_id, $tag_id)
+    {
+        return $this->db->table(self::TABLE)
+            ->eq('task_id', $task_id)
+            ->eq('tag_id', $tag_id)
+            ->remove();
+    }
+
+    private function addTags($project_id, $task_id, $task_tags, $tags)
+    {
+        foreach ($tags as $tag) {
+            $tag_id = $this->tagModel->findOrCreateTag($project_id, $tag);
+
+            if (! isset($task_tags[$tag_id]) && ! $this->associate($task_id, $tag_id)) {
+                return false;
+            }
+        }
+
+        return true;
+    }
+
+    private function removeTags($task_id, $task_tags, $tags)
+    {
+        foreach ($task_tags as $tag_id => $tag) {
+            if (! in_array($tag, $tags)) {
+                if (! $this->dissociate($task_id, $tag_id)) {
+                    return false;
+                }
+            }
+        }
+
+        return true;
+    }
+}
diff --git a/app/Schema/Mysql.php b/app/Schema/Mysql.php
index 934b063f..82ccb8c8 100644
--- a/app/Schema/Mysql.php
+++ b/app/Schema/Mysql.php
@@ -6,7 +6,30 @@ use PDO;
 use Kanboard\Core\Security\Token;
 use Kanboard\Core\Security\Role;
 
-const VERSION = 110;
+const VERSION = 111;
+
+function version_111(PDO $pdo)
+{
+    $pdo->exec("
+        CREATE TABLE tags (
+            id INT NOT NULL AUTO_INCREMENT,
+            name VARCHAR(255) NOT NULL,
+            project_id INT NOT NULL,
+            UNIQUE(project_id, name),
+            PRIMARY KEY(id)
+        ) ENGINE=InnoDB CHARSET=utf8
+    ");
+
+    $pdo->exec("
+        CREATE TABLE task_has_tags (
+            task_id INT NOT NULL,
+            tag_id INT NOT NULL,
+            FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,
+            FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE,
+            UNIQUE(tag_id, task_id)
+        ) ENGINE=InnoDB CHARSET=utf8
+    ");
+}
 
 function version_110(PDO $pdo)
 {
diff --git a/app/Schema/Postgres.php b/app/Schema/Postgres.php
index 3ef49498..229cbd25 100644
--- a/app/Schema/Postgres.php
+++ b/app/Schema/Postgres.php
@@ -6,7 +6,29 @@ use PDO;
 use Kanboard\Core\Security\Token;
 use Kanboard\Core\Security\Role;
 
-const VERSION = 89;
+const VERSION = 90;
+
+function version_90(PDO $pdo)
+{
+    $pdo->exec("
+        CREATE TABLE tags (
+            id SERIAL PRIMARY KEY,
+            name VARCHAR(255) NOT NULL,
+            project_id INTEGER NOT NULL,
+            UNIQUE(project_id, name)
+        )
+    ");
+
+    $pdo->exec("
+        CREATE TABLE task_has_tags (
+            task_id INTEGER NOT NULL,
+            tag_id INTEGER NOT NULL,
+            FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,
+            FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE,
+            UNIQUE(tag_id, task_id)
+        )
+    ");
+}
 
 function version_89(PDO $pdo)
 {
diff --git a/app/Schema/Sqlite.php b/app/Schema/Sqlite.php
index 9ded7ed9..dac348d4 100644
--- a/app/Schema/Sqlite.php
+++ b/app/Schema/Sqlite.php
@@ -6,7 +6,29 @@ use Kanboard\Core\Security\Token;
 use Kanboard\Core\Security\Role;
 use PDO;
 
-const VERSION = 101;
+const VERSION = 102;
+
+function version_102(PDO $pdo)
+{
+    $pdo->exec("
+        CREATE TABLE tags (
+            id INTEGER PRIMARY KEY,
+            name TEXT NOT NULL,
+            project_id INTEGER NOT NULL,
+            UNIQUE(project_id, name)
+        )
+    ");
+
+    $pdo->exec("
+        CREATE TABLE task_has_tags (
+            task_id INTEGER NOT NULL,
+            tag_id INTEGER NOT NULL,
+            FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE,
+            FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE,
+            UNIQUE(tag_id, task_id)
+        )
+    ");
+}
 
 function version_101(PDO $pdo)
 {
diff --git a/app/ServiceProvider/ClassProvider.php b/app/ServiceProvider/ClassProvider.php
index 3e6efb02..778b4f9e 100644
--- a/app/ServiceProvider/ClassProvider.php
+++ b/app/ServiceProvider/ClassProvider.php
@@ -60,6 +60,7 @@ class ClassProvider implements ServiceProviderInterface
             'SubtaskModel',
             'SubtaskTimeTrackingModel',
             'SwimlaneModel',
+            'TagModel',
             'TaskModel',
             'TaskAnalyticModel',
             'TaskCreationModel',
@@ -71,6 +72,7 @@ class ClassProvider implements ServiceProviderInterface
             'TaskModificationModel',
             'TaskPositionModel',
             'TaskStatusModel',
+            'TaskTagModel',
             'TaskMetadataModel',
             'TimezoneModel',
             'TransitionModel',