setDescription('BETA - Clean the webhook log') ->addOption('older_than', null, InputOption::VALUE_REQUIRED, 'delete older than ') ->addOption('dry-run', null, InputOption::VALUE_NONE, 'dry run, list and count') ->addOption('show_sql', null, InputOption::VALUE_NONE, 'show sql pre-selecting WebhookEvents') ->setHelp( "example: bin/maintenance clean:webhooklog --dry-run --older_than '10 month'\n" . "\ can be absolute or relative from now, e.g.:\n" . "- 2022-01-01\n" . "- 10 days\n" . "- 2 weeks\n" . "- 6 months\n" . "- 1 year\n" ); } public 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 $clauseWhere = "`created` < '" . $matches[1] ."'"; } elseif ($n === 4 && empty($matches[1])) { // 1-day ; 2-weeks ; ... $expr = (int)$matches[2]; $unit = strtoupper($matches[3]); $clauseWhere = sprintf("`created` < DATE_SUB(NOW(), INTERVAL %d %s)", $expr, $unit); } else { $output->writeln("invalid value form '--older_than' option"); return 1; } if ($input->getOption('dry-run')) { $dry = true; } $sql = 'SELECT id, `name`, `type`, created FROM WebhookEvents WHERE ' . $clauseWhere; $connection = $this->container->getApplicationBox()->get_connection(); $stmt = $connection->prepare($sql); $stmt->execute(); $webhookEventsList = $stmt->fetchAll(\PDO::FETCH_ASSOC); $stmt->closeCursor(); $webhookEventsId = array_column($webhookEventsList, 'id'); if (!empty($webhookEventsId)) { $sqlDelivery = "SELECT id, application_id, event_id, created FROM WebhookEventDeliveries WHERE event_id IN (" . implode(', ', $webhookEventsId) . ")"; $stmt = $connection->prepare($sqlDelivery); $stmt->execute(); $webhookEventDeliveryList = $stmt->fetchAll(\PDO::FETCH_ASSOC); $stmt->closeCursor(); $deliveryIds = array_column($webhookEventDeliveryList, 'id'); } if ($input->getOption('show_sql')) { $output->writeln($sql); } if ($dry) { $output->writeln(sprintf("\n \n dry-run , %d WebhookEvents entry to delete", count($webhookEventsList))); $eventEntryTable = $this->getHelperSet()->get('table'); $headers = ['id', 'name', 'type', 'created']; $eventEntryTable ->setHeaders($headers) ->setRows($webhookEventsList) ->render($output); if (!empty($webhookEventsId)) { $output->writeln(sprintf("\n \n dry-run , %d WebhookEventDelivery entry to delete", count($webhookEventDeliveryList))); $deliveryEntryTable = $this->getHelperSet()->get('table'); $headers = ['id', 'application_id', 'event_id', 'created']; $deliveryEntryTable ->setHeaders($headers) ->setRows($webhookEventDeliveryList) ->render($output); if (!empty($deliveryIds)) { $sqlPayload = "SELECT id, delivery_id, status FROM WebhookEventPayloads WHERE delivery_id IN (". implode(', ', $deliveryIds).")"; $stmt = $connection->prepare($sqlPayload); $stmt->execute(); $webhookEventPayloadsList = $stmt->fetchAll(\PDO::FETCH_ASSOC); $stmt->closeCursor(); $output->writeln(sprintf("\n \n dry-run , %d WebhookEventPayloads entry to delete", count($webhookEventPayloadsList))); $payloadEntryTable = $this->getHelperSet()->get('table'); $headers = ['id', 'delivery_id', 'status']; $payloadEntryTable ->setHeaders($headers) ->setRows($webhookEventPayloadsList) ->render($output); } } } else { if (!empty($deliveryIds)) { $sqlPayloadDelete = 'DELETE FROM WebhookEventPayloads WHERE delivery_id IN (' . implode(',', $deliveryIds) . ')'; $stmt = $connection->executeQuery($sqlPayloadDelete); $output->writeln(sprintf("%d WebhookEventPayloads entry deleted", $stmt->rowCount())); } if (!empty($webhookEventsId)) { $sqlDeliveryDelete = 'DELETE FROM WebhookEventDeliveries WHERE event_id IN ('. implode(',', $webhookEventsId) .')'; $stmt = $connection->executeQuery($sqlDeliveryDelete); $output->writeln(sprintf("%d WebhookEventDeliveries entry deleted", $stmt->rowCount())); } $sqlEventDelete = 'DELETE FROM WebhookEvents WHERE ' . $clauseWhere; $stmt = $connection->executeQuery($sqlEventDelete); $output->writeln(sprintf("%d WebhookEvents entry deleted", $stmt->rowCount())); } } }