Pruner.php
98 lines
| 1 | <?php |
| 2 | |
| 3 | namespace IAWP\Data_Pruning; |
| 4 | |
| 5 | use IAWP\Illuminate_Builder; |
| 6 | use IAWP\Query; |
| 7 | use IAWP\Tables; |
| 8 | /** @internal */ |
| 9 | class Pruner |
| 10 | { |
| 11 | private $cutoff_date; |
| 12 | private function __construct() |
| 13 | { |
| 14 | $this->cutoff_date = (new \IAWP\Data_Pruning\Pruning_Scheduler())->get_pruning_cutoff_as_datetime(); |
| 15 | } |
| 16 | private function run() |
| 17 | { |
| 18 | if (\is_null($this->cutoff_date)) { |
| 19 | return; |
| 20 | } |
| 21 | $db = Illuminate_Builder::get_connection(); |
| 22 | $db->transaction(function () { |
| 23 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 24 | Illuminate_Builder::new()->from($sessions_table)->where('created_at', '<', $this->cutoff_date->format('Y-m-d\\TH:i:s'))->delete(); |
| 25 | // Delete orphaned views |
| 26 | $views_table = Query::get_table_name(Query::VIEWS); |
| 27 | $this->delete_session_orphans($views_table, 'session_id'); |
| 28 | // Delete orphaned form submissions |
| 29 | $form_submissions_table = Query::get_table_name(Query::FORM_SUBMISSIONS); |
| 30 | $this->delete_session_orphans($form_submissions_table, 'session_id'); |
| 31 | // Delete orphaned WooCommerce orders |
| 32 | $orders_table = Query::get_table_name(Query::ORDERS); |
| 33 | $this->delete_view_orphans($orders_table, 'view_id', 'id'); |
| 34 | // Delete orphaned visitors |
| 35 | $visitors_table = Query::get_table_name(Query::VISITORS); |
| 36 | $this->delete_session_orphans($visitors_table, 'visitor_id'); |
| 37 | // Delete orphaned resources |
| 38 | $resources_table = Query::get_table_name(Query::RESOURCES); |
| 39 | $this->delete_view_orphans($resources_table, 'id', 'resource_id'); |
| 40 | // Delete orphaned referrers |
| 41 | $referrers_table = Query::get_table_name(Query::REFERRERS); |
| 42 | $this->delete_session_orphans($referrers_table, 'id', 'referrer_id'); |
| 43 | // Delete orphaned forms |
| 44 | $forms_table = Query::get_table_name(Query::FORMS); |
| 45 | Illuminate_Builder::new()->from($forms_table, 'orphans')->leftJoin("{$form_submissions_table} AS form_submissions", "orphans.form_id", '=', "form_submissions.form_id")->whereNull('form_submissions.form_id')->delete(); |
| 46 | // Delete orphaned device browsers |
| 47 | $device_browsers_table = Query::get_table_name(Query::DEVICE_BROWSERS); |
| 48 | $this->delete_session_orphans($device_browsers_table, 'device_browser_id'); |
| 49 | // Delete orphaned device oss |
| 50 | $device_oss_table = Query::get_table_name(Query::DEVICE_OSS); |
| 51 | $this->delete_session_orphans($device_oss_table, 'device_os_id'); |
| 52 | // Delete orphaned device types |
| 53 | $device_types_table = Query::get_table_name(Query::DEVICE_TYPES); |
| 54 | $this->delete_session_orphans($device_types_table, 'device_type_id'); |
| 55 | // Don't touch cities or countries for now |
| 56 | // This would be a very different custom query as cities reference countries |
| 57 | // Delete orphaned campaigns |
| 58 | $campaigns_table = Query::get_table_name(Query::CAMPAIGNS); |
| 59 | $this->delete_session_orphans($campaigns_table, 'campaign_id'); |
| 60 | // Delete orphaned clicks |
| 61 | $this->delete_view_orphans(Tables::clicks(), 'view_id', 'id'); |
| 62 | // Delete orphaned clicked links |
| 63 | $clicks_table = Tables::clicks(); |
| 64 | Illuminate_Builder::new()->from(Tables::clicked_links(), 'orphans')->leftJoin("{$clicks_table} AS clicks", 'orphans.click_id', '=', 'clicks.click_id')->whereNull('clicks.click_id')->delete(); |
| 65 | // Delete orphaned links |
| 66 | $clicked_links_table = Tables::clicked_links(); |
| 67 | Illuminate_Builder::new()->from(Tables::links(), 'links')->leftJoin("{$clicked_links_table} AS clicked_links", 'clicked_links.link_id', '=', 'links.id')->whereNull('clicked_links.click_id')->delete(); |
| 68 | // Delete orphaned click targets |
| 69 | $links_table = Tables::links(); |
| 70 | Illuminate_Builder::new()->from(Tables::click_targets(), 'orphans')->leftJoin("{$links_table} AS links", 'orphans.click_target_id', '=', 'links.click_target_id')->whereNull('links.id')->delete(); |
| 71 | }); |
| 72 | \delete_option('iawp_beginning_of_time'); |
| 73 | } |
| 74 | private function delete_session_orphans(string $table, string $column, ?string $sessions_column = null) : int |
| 75 | { |
| 76 | $sessions_table = Query::get_table_name(Query::SESSIONS); |
| 77 | $sessions_column = $sessions_column ?? $column; |
| 78 | return Illuminate_Builder::new()->from($table, 'orphans')->leftJoin("{$sessions_table} AS sessions", "orphans.{$column}", '=', "sessions.{$sessions_column}")->whereNull('sessions.session_id')->delete(); |
| 79 | } |
| 80 | private function delete_view_orphans(string $table, string $column, ?string $views_column = null) : int |
| 81 | { |
| 82 | $views_table = Query::get_table_name(Query::VIEWS); |
| 83 | $views_column = $views_column ?? $column; |
| 84 | return Illuminate_Builder::new()->from($table, 'orphans')->leftJoin("{$views_table} AS views", "orphans.{$column}", '=', "views.{$views_column}")->whereNull('views.id')->delete(); |
| 85 | } |
| 86 | public static function register_hook() |
| 87 | { |
| 88 | \add_action('iawp_prune', function () { |
| 89 | self::prune(); |
| 90 | }); |
| 91 | } |
| 92 | public static function prune() |
| 93 | { |
| 94 | $pruner = new self(); |
| 95 | $pruner->run(); |
| 96 | } |
| 97 | } |
| 98 |