Remove workaround for 'INSERT ON DUPLICATE KEY UPDATE...'

4 files changed, 103 insertions, 38 deletions

diff --git a/ChangeLog b/ChangeLog
index 92658cb7..acd09fdd 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -18,6 +18,7 @@ Improvements:
 * Use PHP7 function random_bytes() to generate tokens if available
 * CSV task export show the assignee name in addition to the assignee username
 * Add new hooks for plugins
+* Remove workaround for "INSERT ON DUPLICATE KEY UPDATE..."
 
 Internal code refactoring:
 
diff --git a/app/Model/ProjectDailyColumnStats.php b/app/Model/ProjectDailyColumnStats.php
index 8ed6137f..4b75fff2 100644
--- a/app/Model/ProjectDailyColumnStats.php
+++ b/app/Model/ProjectDailyColumnStats.php
@@ -34,40 +34,51 @@ class ProjectDailyColumnStats extends Base
     {
         $status = $this->config->get('cfd_include_closed_tasks') == 1 ? array(Task::STATUS_OPEN, Task::STATUS_CLOSED) : array(Task::STATUS_OPEN);
 
-        return $this->db->transaction(function (Database $db) use ($project_id, $date, $status) {
+        $this->db->startTransaction();
 
-            $column_ids = $db->table(Board::TABLE)->eq('project_id', $project_id)->findAllByColumn('id');
+        $column_ids = $this->db->table(Board::TABLE)->eq('project_id', $project_id)->findAllByColumn('id');
 
-            foreach ($column_ids as $column_id) {
+        foreach ($column_ids as $column_id) {
 
-                // This call will fail if the record already exists
-                // (cross database driver hack for INSERT..ON DUPLICATE KEY UPDATE)
-                $db->table(ProjectDailyColumnStats::TABLE)->insert(array(
-                    'day' => $date,
-                    'project_id' => $project_id,
-                    'column_id' => $column_id,
-                    'total' => 0,
-                    'score' => 0,
-                ));
+            $exists = $this->db->table(ProjectDailyColumnStats::TABLE)
+                ->eq('project_id', $project_id)
+                ->eq('column_id', $column_id)
+                ->eq('day', $date)
+                ->exists();
+
+            $score = $this->db->table(Task::TABLE)
+                ->eq('project_id', $project_id)
+                ->eq('column_id', $column_id)
+                ->eq('is_active', Task::STATUS_OPEN)
+                ->sum('score');
 
-                $db->table(ProjectDailyColumnStats::TABLE)
+            $total = $this->db->table(Task::TABLE)
+                ->eq('project_id', $project_id)
+                ->eq('column_id', $column_id)
+                ->in('is_active', $status)
+                ->count();
+
+            if ($exists) {
+                $this->db->table(ProjectDailyColumnStats::TABLE)
                     ->eq('project_id', $project_id)
                     ->eq('column_id', $column_id)
                     ->eq('day', $date)
-                    ->update(array(
-                        'score' => $db->table(Task::TABLE)
-                                      ->eq('project_id', $project_id)
-                                      ->eq('column_id', $column_id)
-                                      ->eq('is_active', Task::STATUS_OPEN)
-                                      ->sum('score'),
-                        'total' => $db->table(Task::TABLE)
-                                      ->eq('project_id', $project_id)
-                                      ->eq('column_id', $column_id)
-                                      ->in('is_active', $status)
-                                      ->count()
-                    ));
+                    ->update(array('score' => $score, 'total' => $total));
+
+            } else {
+                $this->db->table(ProjectDailyColumnStats::TABLE)->insert(array(
+                    'day' => $date,
+                    'project_id' => $project_id,
+                    'column_id' => $column_id,
+                    'total' => $total,
+                    'score' => $score,
+                ));
             }
-        });
+        }
+
+        $this->db->closeTransaction();
+
+        return true;
     }
 
     /**
diff --git a/app/Model/ProjectDailyStats.php b/app/Model/ProjectDailyStats.php
index 46ca0a4b..7ec1ee2c 100644
--- a/app/Model/ProjectDailyStats.php
+++ b/app/Model/ProjectDailyStats.php
@@ -29,27 +29,35 @@ class ProjectDailyStats extends Base
      */
     public function updateTotals($project_id, $date)
     {
-        $lead_cycle_time = $this->projectAnalytic->getAverageLeadAndCycleTime($project_id);
+        $this->db->startTransaction();
 
-        return $this->db->transaction(function (Database $db) use ($project_id, $date, $lead_cycle_time) {
+        $lead_cycle_time = $this->projectAnalytic->getAverageLeadAndCycleTime($project_id);
 
-            // This call will fail if the record already exists
-            // (cross database driver hack for INSERT..ON DUPLICATE KEY UPDATE)
-            $db->table(ProjectDailyStats::TABLE)->insert(array(
-                'day' => $date,
-                'project_id' => $project_id,
-                'avg_lead_time' => 0,
-                'avg_cycle_time' => 0,
-            ));
+        $exists = $this->db->table(ProjectDailyStats::TABLE)
+            ->eq('day', $date)
+            ->eq('project_id', $project_id)
+            ->exists();
 
-            $db->table(ProjectDailyStats::TABLE)
+        if ($exists) {
+            $this->db->table(ProjectDailyStats::TABLE)
                 ->eq('project_id', $project_id)
                 ->eq('day', $date)
                 ->update(array(
                     'avg_lead_time' => $lead_cycle_time['avg_lead_time'],
                     'avg_cycle_time' => $lead_cycle_time['avg_cycle_time'],
                 ));
-        });
+        } else {
+            $this->db->table(ProjectDailyStats::TABLE)->insert(array(
+                'day' => $date,
+                'project_id' => $project_id,
+                'avg_lead_time' => $lead_cycle_time['avg_lead_time'],
+                'avg_cycle_time' => $lead_cycle_time['avg_cycle_time'],
+            ));
+        }
+
+        $this->db->closeTransaction();
+
+        return true;
     }
 
     /**
diff --git a/tests/units/Model/ProjectDailyStatsTest.php b/tests/units/Model/ProjectDailyStatsTest.php
new file mode 100644
index 00000000..c17017c9
--- /dev/null
+++ b/tests/units/Model/ProjectDailyStatsTest.php
@@ -0,0 +1,45 @@
+<?php
+
+require_once __DIR__.'/../Base.php';
+
+use Kanboard\Model\Project;
+use Kanboard\Model\ProjectDailyStats;
+use Kanboard\Model\Task;
+use Kanboard\Model\TaskCreation;
+use Kanboard\Model\TaskStatus;
+
+class ProjectDailyStatsTest extends Base
+{
+    public function testUpdateTotals()
+    {
+        $p = new Project($this->container);
+        $pds = new ProjectDailyStats($this->container);
+        $tc = new TaskCreation($this->container);
+        $ts = new TaskStatus($this->container);
+
+        $this->assertEquals(1, $p->create(array('name' => 'UnitTest')));
+
+        $this->assertEquals(1, $tc->create(array('title' => 'Task #1', 'project_id' => 1, 'date_started' => strtotime('-1 day'))));
+        $this->assertEquals(2, $tc->create(array('title' => 'Task #1', 'project_id' => 1)));
+        $pds->updateTotals(1, date('Y-m-d', strtotime('-1 day')));
+
+        $this->assertTrue($ts->close(1));
+        $pds->updateTotals(1, date('Y-m-d'));
+
+        $metrics = $pds->getRawMetrics(1, date('Y-m-d', strtotime('-1days')), date('Y-m-d'));
+        $expected = array(
+            array(
+                'day' => '2015-11-20',
+                'avg_lead_time' => 0,
+                'avg_cycle_time' => 43200,
+            ),
+            array(
+                'day' => '2015-11-21',
+                'avg_lead_time' => 0,
+                'avg_cycle_time' => 43200,
+            )
+        );
+
+        $this->assertEquals($expected, $metrics);
+    }
+}