setDescription('Delete old WorkerRunningJob') ->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) { $dry = false; $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 // published : date the message are published in phraseanet $clause = "`published` < '" . $matches[1] ."'"; } elseif ($n === 4 && empty($matches[1])) { // 1-day ; 2-weeks ; ... $expr = (int)$matches[2]; $unit = strtoupper($matches[3]); $clause = sprintf("`published` < DATE_SUB(NOW(), INTERVAL %d %s)", $expr, $unit); } else { $output->writeln("invalid value form '--older_than' option"); return 1; } if ($input->getOption('dry')) { $dry = true; } $sql_0 = "FROM `WorkerRunningJob` WHERE " . $clause; $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 WorkerRunningJob 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 WorkerRunningJob have been be deleted.", $count)); } else { $output->writeln("Dry mode: Not executed."); } return 0; } }