Add command to remove project activities after one year

11 files changed, 45 insertions, 32 deletions

diff --git a/app/Console/BaseCommand.php b/app/Console/BaseCommand.php
index 09059ecf..8ea67ae4 100644
--- a/app/Console/BaseCommand.php
+++ b/app/Console/BaseCommand.php
@@ -18,6 +18,7 @@ use Symfony\Component\Console\Command\Command;
  * @property \Kanboard\Export\TransitionExport                  $transitionExport
  * @property \Kanboard\Model\NotificationModel                  $notificationModel
  * @property \Kanboard\Model\ProjectModel                       $projectModel
+ * @property \Kanboard\Model\ProjectActivityModel               $projectActivityModel
  * @property \Kanboard\Model\ProjectPermissionModel             $projectPermissionModel
  * @property \Kanboard\Model\ProjectDailyColumnStatsModel       $projectDailyColumnStatsModel
  * @property \Kanboard\Model\ProjectDailyStatsModel             $projectDailyStatsModel
diff --git a/app/Console/ProjectActivityArchiveCommand.php b/app/Console/ProjectActivityArchiveCommand.php
new file mode 100644
index 00000000..18a4d7d7
--- /dev/null
+++ b/app/Console/ProjectActivityArchiveCommand.php
@@ -0,0 +1,21 @@
+<?php
+
+namespace Kanboard\Console;
+
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Output\OutputInterface;
+
+class ProjectActivityArchiveCommand extends BaseCommand
+{
+    protected function configure()
+    {
+        $this
+            ->setName('projects:archive-activities')
+            ->setDescription('Remove project activities after one year');
+    }
+
+    protected function execute(InputInterface $input, OutputInterface $output)
+    {
+        $this->projectActivityModel->cleanup(strtotime('-1 year'));
+    }
+}
diff --git a/app/Model/ProjectActivityModel.php b/app/Model/ProjectActivityModel.php
index 17cf6485..739fd787 100644
--- a/app/Model/ProjectActivityModel.php
+++ b/app/Model/ProjectActivityModel.php
@@ -33,17 +33,14 @@ class ProjectActivityModel extends Base
      */
     public function createEvent($project_id, $task_id, $creator_id, $event_name, array $data)
     {
-        $values = array(
+        return $this->db->table(self::TABLE)->insert(array(
             'project_id' => $project_id,
             'task_id' => $task_id,
             'creator_id' => $creator_id,
             'event_name' => $event_name,
             'date_creation' => time(),
             'data' => json_encode($data),
-        );
-
-        $this->cleanup(PROJECT_ACTIVITIES_MAX_EVENTS - 1);
-        return $this->db->table(self::TABLE)->insert($values);
+        ));
     }
 
     /**
@@ -73,15 +70,10 @@ class ProjectActivityModel extends Base
      * Remove old event entries to avoid large table
      *
      * @access public
-     * @param  integer    $max    Maximum number of items to keep in the table
+     * @param  integer $ts Timestamp
      */
-    public function cleanup($max)
+    public function cleanup($ts)
     {
-        $total = $this->db->table(self::TABLE)->count();
-
-        if ($total > $max) {
-            $ids = $this->db->table(self::TABLE)->asc('id')->limit($total - $max)->findAllByColumn('id');
-            $this->db->table(self::TABLE)->in('id', $ids)->remove();
-        }
+        $this->db->table(self::TABLE)->lt('date_creation', $ts)->remove();
     }
 }
diff --git a/app/ServiceProvider/CommandProvider.php b/app/ServiceProvider/CommandProvider.php
index 0c6ca5ee..70968306 100644
--- a/app/ServiceProvider/CommandProvider.php
+++ b/app/ServiceProvider/CommandProvider.php
@@ -11,6 +11,7 @@ use Kanboard\Console\LocaleSyncCommand;
 use Kanboard\Console\PluginInstallCommand;
 use Kanboard\Console\PluginUninstallCommand;
 use Kanboard\Console\PluginUpgradeCommand;
+use Kanboard\Console\ProjectActivityArchiveCommand;
 use Kanboard\Console\ProjectArchiveCommand;
 use Kanboard\Console\ProjectDailyColumnStatsExportCommand;
 use Kanboard\Console\ProjectDailyStatsCalculationCommand;
@@ -48,6 +49,7 @@ class CommandProvider implements ServiceProviderInterface
         $application->add(new SubtaskExportCommand($container));
         $application->add(new TaskExportCommand($container));
         $application->add(new ProjectArchiveCommand($container));
+        $application->add(new ProjectActivityArchiveCommand($container));
         $application->add(new ProjectDailyStatsCalculationCommand($container));
         $application->add(new ProjectDailyColumnStatsExportCommand($container));
         $application->add(new TransitionExportCommand($container));
diff --git a/app/constants.php b/app/constants.php
index 591d433a..7029ce03 100644
--- a/app/constants.php
+++ b/app/constants.php
@@ -148,7 +148,5 @@ defined('HTTP_VERIFY_SSL_CERTIFICATE') or define('HTTP_VERIFY_SSL_CERTIFICATE',
 
 defined('TOTP_ISSUER') or define('TOTP_ISSUER', 'Kanboard');
 
-defined('PROJECT_ACTIVITIES_MAX_EVENTS') or define('PROJECT_ACTIVITIES_MAX_EVENTS', 10000);
-
 // Comma separated list of fields to not synchronize when using external authentication providers
 defined('EXTERNAL_AUTH_EXCLUDE_FIELDS') or define('EXTERNAL_AUTH_EXCLUDE_FIELDS', 'username');
diff --git a/config.default.php b/config.default.php
index ff411480..cc706306 100644
--- a/config.default.php
+++ b/config.default.php
@@ -238,8 +238,5 @@ define('HTTP_VERIFY_SSL_CERTIFICATE', true);
 // TOTP (2FA) issuer name
 define('TOTP_ISSUER', 'Kanboard');
 
-// Maximum number of events stored in the table "project_activities"
-define('PROJECT_ACTIVITIES_MAX_EVENTS', 10000);
-
 // Comma separated list of fields to not synchronize when using external authentication providers
 define('EXTERNAL_AUTH_EXCLUDE_FIELDS', 'username');
diff --git a/doc/en_US/cli.markdown b/doc/en_US/cli.markdown
index c85b0cff..b8d999c6 100644
--- a/doc/en_US/cli.markdown
+++ b/doc/en_US/cli.markdown
@@ -53,6 +53,7 @@ Available commands:
   plugin:upgrade                     Update all installed plugins
  projects
   projects:archive                   Disable projects not touched during one year
+  projects:archive-activities        Remove project activities after one year
   projects:daily-stats               Calculate daily statistics for all projects
  trigger
   trigger:tasks                      Trigger scheduler event for all tasks
diff --git a/doc/en_US/config.markdown b/doc/en_US/config.markdown
index 103c859b..20ef03b3 100644
--- a/doc/en_US/config.markdown
+++ b/doc/en_US/config.markdown
@@ -336,9 +336,6 @@ define('API_AUTHENTICATION_TOKEN', 'My unique API Token');
 // TOTP (2FA) issuer name
 define('TOTP_ISSUER', 'Kanboard');
 
-// Maximum number of events stored in the table "project_activities"
-define('PROJECT_ACTIVITIES_MAX_EVENTS', 10000);
-
 // Comma separated list of fields to not synchronize when using external authentication providers
 define('EXTERNAL_AUTH_EXCLUDE_FIELDS', 'username');
 ```
diff --git a/tests/units/Model/ProjectActivityTest.php b/tests/units/Model/ProjectActivityTest.php
index 0b87b515..f3d37acb 100644
--- a/tests/units/Model/ProjectActivityTest.php
+++ b/tests/units/Model/ProjectActivityTest.php
@@ -43,23 +43,23 @@ class ProjectActivityTest extends Base
         $this->assertEquals(1, $projectModel->create(array('name' => 'Project #1')));
         $this->assertEquals(1, $taskCreation->create(array('title' => 'Task #1', 'project_id' => 1)));
 
-        $max = 15;
-        $nb_events = 100;
+        $nbEvents = 100;
         $task = $taskFinder->getById(1);
 
-        for ($i = 0; $i < $nb_events; $i++) {
+        for ($i = 0; $i < $nbEvents; $i++) {
             $this->assertTrue($projectActivity->createEvent(1, 1, 1, TaskModel::EVENT_CLOSE, array('task' => $task)));
         }
 
-        $this->assertEquals($nb_events, $this->container['db']->table('project_activities')->count());
-        $projectActivity->cleanup($max);
+        $this->assertEquals($nbEvents, $this->container['db']->table('project_activities')->count());
+        $projectActivity->cleanup(strtotime('-12 months'));
+        $this->assertEquals($nbEvents, $this->container['db']->table('project_activities')->count());
+
+        $this->container['db']->table('project_activities')->in('id', array(1, 2, 3))->update(array(
+            'date_creation' => strtotime('-13 months')
+        ));
 
-        $events = $projectActivity->getQuery()->desc('id')->findAll();
 
-        $this->assertNotEmpty($events);
-        $this->assertCount($max, $events);
-        $this->assertEquals(100, $events[0]['id']);
-        $this->assertEquals(99, $events[1]['id']);
-        $this->assertEquals(86, $events[14]['id']);
+        $projectActivity->cleanup(strtotime('-12 months'));
+        $this->assertEquals($nbEvents - 3, $this->container['db']->table('project_activities')->count());
     }
 }
diff --git a/vendor/composer/autoload_classmap.php b/vendor/composer/autoload_classmap.php
index c8296065..867e4aed 100644
--- a/vendor/composer/autoload_classmap.php
+++ b/vendor/composer/autoload_classmap.php
@@ -154,6 +154,8 @@ return array(
     'Kanboard\\Console\\PluginInstallCommand' => $baseDir . '/app/Console/PluginInstallCommand.php',
     'Kanboard\\Console\\PluginUninstallCommand' => $baseDir . '/app/Console/PluginUninstallCommand.php',
     'Kanboard\\Console\\PluginUpgradeCommand' => $baseDir . '/app/Console/PluginUpgradeCommand.php',
+    'Kanboard\\Console\\ProjectActivityArchiveCommand' => $baseDir . '/app/Console/ProjectActivityArchiveCommand.php',
+    'Kanboard\\Console\\ProjectArchiveCommand' => $baseDir . '/app/Console/ProjectArchiveCommand.php',
     'Kanboard\\Console\\ProjectDailyColumnStatsExportCommand' => $baseDir . '/app/Console/ProjectDailyColumnStatsExportCommand.php',
     'Kanboard\\Console\\ProjectDailyStatsCalculationCommand' => $baseDir . '/app/Console/ProjectDailyStatsCalculationCommand.php',
     'Kanboard\\Console\\ResetPasswordCommand' => $baseDir . '/app/Console/ResetPasswordCommand.php',
diff --git a/vendor/composer/autoload_static.php b/vendor/composer/autoload_static.php
index 75910f92..d20a0a0d 100644
--- a/vendor/composer/autoload_static.php
+++ b/vendor/composer/autoload_static.php
@@ -290,6 +290,8 @@ class ComposerStaticInit6edea6294a88689e3f5c56484bb70c9b
         'Kanboard\\Console\\PluginInstallCommand' => __DIR__ . '/../..' . '/app/Console/PluginInstallCommand.php',
         'Kanboard\\Console\\PluginUninstallCommand' => __DIR__ . '/../..' . '/app/Console/PluginUninstallCommand.php',
         'Kanboard\\Console\\PluginUpgradeCommand' => __DIR__ . '/../..' . '/app/Console/PluginUpgradeCommand.php',
+        'Kanboard\\Console\\ProjectActivityArchiveCommand' => __DIR__ . '/../..' . '/app/Console/ProjectActivityArchiveCommand.php',
+        'Kanboard\\Console\\ProjectArchiveCommand' => __DIR__ . '/../..' . '/app/Console/ProjectArchiveCommand.php',
         'Kanboard\\Console\\ProjectDailyColumnStatsExportCommand' => __DIR__ . '/../..' . '/app/Console/ProjectDailyColumnStatsExportCommand.php',
         'Kanboard\\Console\\ProjectDailyStatsCalculationCommand' => __DIR__ . '/../..' . '/app/Console/ProjectDailyStatsCalculationCommand.php',
         'Kanboard\\Console\\ResetPasswordCommand' => __DIR__ . '/../..' . '/app/Console/ResetPasswordCommand.php',