setDescription('Delete old user notifications') ->addOption('what', null, InputOption::VALUE_REQUIRED|InputOption::VALUE_IS_ARRAY, '"read" and/or "unread" (default both)') ->addOption('older_than', null, InputOption::VALUE_REQUIRED, 'delete older than \') ->addOption('dry', null, InputOption::VALUE_NONE, 'dry run, count but don\'t delete') ->addOption('show_sql', null, InputOption::VALUE_NONE, 'show sql pre-selecting records') ->setHelp( "\ can be absolute or relative from now, e.g.:\n" . "- 2022-01-01 (please use strict date format, do not add time)\n" . "- 10 days\n" . "- 2 weeks\n" . "- 6 months\n" . "- 1 year" ); } protected function doExecute(InputInterface $input, OutputInterface $output) { $clauses = []; // sql clauses $dry = false; // sanity check options // // --what $what = $input->getOption('what'); if(empty($what)) { $what = ['read', 'unread']; } switch (join('-', $what)) { case 'read' : $clauses[] = "`unread` = 0"; break; case 'unread' : $clauses[] = "`unread` = 1"; break; case 'read-unread' : case 'unread-read' : // no clause break; default: $output->writeln("invalid value form '--what' option"); return 1; } // --older_than $older_than = str_replace(['-', '/', ' '], '-', $input->getOption('older_than')); if($older_than === "") { $output->writeln("set '--older_than' option"); return 1; } $matches = []; preg_match("/(\d{4}-\d{2}-\d{2})|(\d+)-(day|week|month|year)s?/i", $older_than, $matches); $n = count($matches); if($n === 2) { // yyyy-mm-dd $clauses[] = "`created_on` < " . $matches[1]; } elseif($n === 4 && empty($matches[1])) { // 1-day ; 2-weeks ; ... $expr = (int)$matches[2]; $unit = strtoupper($matches[3]); $clauses[] = sprintf("`created_on` < DATE_SUB(NOW(), INTERVAL %d %s)", $expr, $unit); } else { $output->writeln("invalid value form '--older_than' option"); return 1; } // --dry if($input->getOption('dry')) { $dry = true; } // do the job // $sql_0 = "FROM `notifications` WHERE (" . join(") AND (", $clauses) . ")"; $sql_count = "SELECT COUNT(`id`) AS n " . $sql_0; $sql_delete = "DELETE " . $sql_0; if($input->getOption('show_sql')) { $output->writeln(sprintf("sql: \"%s\"", $sql_delete)); } $stmt = $this->container->getApplicationBox()->get_connection()->prepare($sql_count); $stmt->execute(); $n = $stmt->fetchColumn(0); $stmt->closeCursor(); $output->writeln(sprintf("%d notifications will be deleted.", $n)); if (!$dry) { $cnx = $this->container->getApplicationBox()->get_connection(); $count = 0; // group delete by 1000 $sql_delete = $sql_delete . ' LIMIT 1000'; do { $nbDeletedRow = $cnx->exec($sql_delete); $count += $nbDeletedRow; } while ($nbDeletedRow > 0); $output->writeln(sprintf("%d notifications have been be deleted.", $count)); } else { $output->writeln("Dry mode: Not executed."); } return 0; } }