· 6 years ago · Aug 07, 2019, 06:36 PM
1<?php
2
3/**
4 * Created by PhpStorm.
5 * User: denis
6 * Date: 06.06.17
7 * Time: 14:30
8 */
9class Ultimate_Ads_Manager_DB_Optimizer
10{
11
12 private static $columns_str = 'id, time, type, uuid, ip, ad_id, ad_slide_id, place_id, value';
13 private static $transient_id = 'codeneric/uam/optimization_started';
14
15 public static function optimize_with_settings(){
16 global $cc_uam_config;
17
18 $options = get_option($cc_uam_config['general_settings_key'], array());
19 $db_opti = isset($options['db_optimization']) ? $options['db_optimization'] : array();
20 $db_opti['enabled'] = isset($db_opti['enabled']) ? $db_opti['enabled'] : false;
21 $db_opti['forget'] = isset($db_opti['forget']) ? $db_opti['forget'] : array();
22 $db_opti['optimize_older_than'] = isset($db_opti['optimize_older_than']) &&
23 is_numeric($db_opti['optimize_older_than']) ?
24 $db_opti['optimize_older_than'] : $cc_uam_config['optimize_older_than'];
25 $db_opti['delete_older_than'] = isset($db_opti['delete_older_than']) &&
26 is_numeric($db_opti['delete_older_than']) ?
27 $db_opti['delete_older_than'] : time();
28
29 if(!self::is_running()){
30
31
32
33// $original_end = get_transient('codeneric/uam/original_end');
34// $original_end = $original_end === false ? $opti_interval['end'] : $original_end;
35
36 $time_x_days_ago = self::time_x_day_ago($db_opti['optimize_older_than']);
37 $original_end_id = self::get_original_end($time_x_days_ago);
38 self::update_original_end($original_end_id);
39// set_transient('codeneric/uam/original_end', $original_end, $cc_uam_config['optimization_timeout']);
40
41
42 $delete_interval = self::calculate_id_interval($db_opti['delete_older_than']);
43 self::delete_entries($delete_interval);
44
45 $opti_interval = array();
46 $opti_interval['start'] = self::get_start_of_batch();
47 $opti_interval['end'] = self::get_end_of_batch($original_end_id, $cc_uam_config['max_db_opti_batch_size']);
48 $suc = self::optimize_table($db_opti['forget'], $opti_interval);
49// error_log(print_r($opti_interval, true));
50
51 if($cc_uam_config['env'] === 'development'){
52 error_log('Current start: '.$opti_interval['start']);
53 error_log('Original end: '.$original_end_id);
54 error_log('Current end : '.$opti_interval['end']);
55 }
56
57 $termination_condition = $original_end_id <= $opti_interval['end']
58 || $opti_interval['start'] >= $opti_interval['end'];
59
60
61
62 if($termination_condition){ //nothing left to optimize
63 if($cc_uam_config['env'] === 'development'){
64 error_log('Terminate optimization!');
65 }
66// delete_option('codeneric/uam/original_end');
67 self::delete_original_end();
68 delete_option('codeneric/uam/current_start');
69 }elseif($suc){
70 if($cc_uam_config['env'] === 'development'){
71 error_log('Continue optimization!');
72 }
73 update_option('codeneric/uam/current_start', $opti_interval['end']);
74 do_action('codeneric/uam/schedule_db_opti_register',
75 array(
76 'singleton' => false,
77 'in' => 0
78 ) );
79 }else{
80 // optimize_table didn't succeed, probably already running...
81 }
82 }
83 }
84
85 private static function get_start_of_batch(){
86// global $cc_uam_config;
87// global $wpdb;
88// $start = $time_interval['start'];
89// $table_name = $cc_uam_config['table_name_events'];
90//
91// $start_time_query = "SELECT MAX(time) as time from $table_name where value >= 2";
92// $start_time = $wpdb->get_var( $start_time_query );
93// $start_time = $start_time === null ? 0 : strtotime($start_time);
94// return max($start, $start_time);
95 return get_option('codeneric/uam/current_start', 0);
96
97 }
98
99 private static function get_end_of_batch($original_end_id, $size){
100 global $cc_uam_config;
101 global $wpdb;
102// $start = $time_interval['start'];
103// $end = $time_interval['end'];
104 $table_name = $cc_uam_config['table_name_events'];
105// $s_t = self::get_db_time_format($start);
106// $e_t = self::get_db_time_format($end);
107 $s_id = self::get_start_of_batch();
108 $o_e_id = $original_end_id;
109
110// $the_query = "SELECT MAX(t.time) FROM (SELECT MIN(time) as time from $table_name where time BETWEEN '$s_t' AND '$e_t' GROUP BY $table_name.id LIMIT $size) t";
111 $the_query = "SELECT MAX(t.id) FROM (SELECT MIN(id) as id from $table_name where id BETWEEN '$s_id' AND '$o_e_id' GROUP BY $table_name.id LIMIT $size) t";
112// $original_end_time = $wpdb->get_var( $the_query );
113// $original_end_time = $original_end_time === null ? time() : strtotime($original_end_time);
114 $end_id = $wpdb->get_var( $the_query );
115 $end_id = $end_id === null ? 0 : intval($end_id);
116
117 return $end_id;
118
119 }
120
121 private static function get_original_end($end_time){
122 global $cc_uam_config;
123 global $wpdb;
124
125 $original_end = get_transient('codeneric/uam/original_end');
126 if($original_end === false){
127 return self::_get_id_by_time($end_time);
128 }else{
129 return $original_end;
130 }
131 }
132
133 private static function update_original_end($end_id){
134 global $cc_uam_config;
135 set_transient('codeneric/uam/original_end', $end_id, $cc_uam_config['optimization_timeout']);
136
137 }
138
139 private static function delete_original_end(){
140 global $cc_uam_config;
141 delete_transient('codeneric/uam/original_end');
142
143 }
144
145 private static function _get_id_by_time($time){
146 global $cc_uam_config;
147 global $wpdb;
148
149 $table_name = $cc_uam_config['table_name_events'];
150 $time = self::get_db_time_format($time);
151
152 $the_query = "SELECT MAX(id) as id from $table_name where time <= '$time'";
153 $end_id = $wpdb->get_var( $the_query );
154 $end_id = $end_id === null ? 0 : intval($end_id);
155 return $end_id;
156
157 }
158
159
160
161 public static function time_x_day_ago($days){
162 $end = time() - DAY_IN_SECONDS*$days;
163 $end = $end < 0 ? 0 : $end;
164 return $end;
165 }
166
167 public static function calculate_time_interval($older_than_days){
168 $end = time() - DAY_IN_SECONDS*$older_than_days;
169 $end = $end < 0 ? 0 : $end;
170 $t_i = array('start' => 0, 'end' => intval($end));
171 return $t_i;
172 }
173
174 public static function calculate_id_interval($older_than_days){
175 $end = time() - DAY_IN_SECONDS*$older_than_days;
176 $end = self::_get_id_by_time($end);
177 $t_i = array('start' => 0, 'end' => intval($end));
178 return $t_i;
179 }
180
181 private static function get_temp_table_name(){
182 global $cc_uam_config;
183 $table_name = $cc_uam_config['table_name_events'];
184 return $table_name . '_temp';
185 }
186
187 public static function optimization_is_running(){
188 $running = get_transient('codeneric/uam/original_end');
189 return $running !== false;
190 }
191
192 private static function is_running(){
193 $optimization_started = get_transient( self::$transient_id );
194 return $optimization_started !== false;
195 }
196
197 private static function start_running(){
198 global $cc_uam_config;
199 set_transient( self::$transient_id, time(), $cc_uam_config['optimization_timeout'] );
200 }
201
202 private static function end_running(){
203 delete_transient( self::$transient_id );
204 }
205
206
207 public static function optimize_table($forget_properties, $id_interval){
208 self::start_running();
209 global $cc_uam_config;
210 global $wpdb;
211 $table_name = $cc_uam_config['table_name_events'];
212 $temp_table_name = self::get_temp_table_name();
213 $columns = self::$columns_str;
214
215 if(self::table_exists($temp_table_name)){ //probably crashed...
216 error_log('UAM Premium: temp table exists, optimization timed out! Try to rescue if necessary...');
217 self::rescue();
218 error_log('UAM Premium: rescue finished.');
219 }
220
221// $would_eliminate = self::calculate_row_eliminations($forget_properties, $id_interval);
222// if($would_eliminate === 0){
223// error_log('Would not eliminate any rows, bail out...');
224// self::end_running();
225// return true;
226// }
227
228
229// $now = time();
230// update_option($cc_uam_config['db_opti_state_option'], array(
231// 'time_interval' => $id_interval,
232// 'time' => $now,
233// 'forget_properties' => $forget_properties,
234// 'status' => 'running'
235// ));
236
237
238// if(function_exists('set_time_limit')){
239// set_time_limit ( $cc_uam_config['optimization_timeout'] ); //try to get more time
240// }
241
242 $succ_created = self::create_empty_events_table($temp_table_name);
243 if($succ_created === false){
244 return false;
245 }
246
247
248 $grouping_query = self::generate_grouping_query($table_name, $forget_properties, $id_interval);
249
250 $insert_into_temp_query = "INSERT INTO $temp_table_name ($columns) $grouping_query";
251
252 $wpdb->query($insert_into_temp_query);
253 //delete entries from persistent table which are contained in optimization
254 self::delete_entries($id_interval);
255
256// if($cc_uam_config['env'] === 'development')exit; //simulate error
257
258 //insert groups
259 $insert_into_persistent_query = self::insert_into_persistent_query();
260 $wpdb->query($insert_into_persistent_query);
261 //delete temp table
262 if(!self::needs_rescue()){
263 self::drop_table($temp_table_name);
264 }else{
265 error_log('UAM Premium: table seems to need rescue because temp table contains more data.');
266 error_log('UAM Premium: did not delete temp table.');
267 }
268
269
270// delete_transient( $transient_id );
271 self::end_running();
272
273 return true;
274
275 }
276
277 private static function calculate_row_eliminations($forget_properties, $id_interval){
278 global $cc_uam_config;
279 global $wpdb;
280 $table_name = $cc_uam_config['table_name_events'];
281 $start = $id_interval['start'];
282 $end = $id_interval['end'];
283
284 $grouping_query = self::generate_grouping_query($table_name, $forget_properties, $id_interval);
285
286 $count_groups_query = "SELECT COUNT(*) AS count_val FROM ($grouping_query) t0";
287 $number_of_groups = $wpdb->get_var( $count_groups_query );
288
289 $count_rows_query = "SELECT COUNT(*) AS count_val FROM $table_name WHERE id BETWEEN '$start' AND '$end'";
290 $number_of_rows = $wpdb->get_var( $count_rows_query );
291
292 return $number_of_rows - $number_of_groups;
293 }
294
295 public static function calculate_space_savings($forget_properties, $id_interval){
296 global $cc_uam_config;
297 global $wpdb;
298 $table_name = $cc_uam_config['table_name_events'];
299 $start = $id_interval['start'];
300 $end = $id_interval['end'];
301
302 $grouping_query = self::generate_grouping_query($table_name, $forget_properties, $id_interval);
303
304 $count_groups_query = "SELECT COUNT(*) AS count_val FROM ($grouping_query) t0";
305 $number_of_groups = $wpdb->get_var( $count_groups_query );
306
307 $count_rows_query = "SELECT COUNT(*) AS count_val FROM $table_name WHERE id BETWEEN '$start' AND '$end'";
308 $number_of_rows = $wpdb->get_var( $count_rows_query );
309
310 return 1 - $number_of_groups / $number_of_rows;
311 }
312
313 private static function get_db_time_format($int){
314 return date("Y-m-d G:i:s", $int);
315 }
316
317
318 public static function delete_entries($id_interval){
319 global $cc_uam_config;
320 global $wpdb;
321 //216000 = 60*60*24 = a day
322 $table_name = $cc_uam_config['table_name_events'];
323 $start = $id_interval['start'];
324 $end = $id_interval['end'];
325
326 $delete_query =
327 "delete from $table_name
328where id BETWEEN '$start' AND '$end'";
329
330 $wpdb->query($delete_query);
331 }
332
333 private static function create_empty_events_table($new_table_name){
334 global $wpdb;
335 global $cc_uam_config;
336 $charset_collate = $cc_uam_config['db_charset'];
337 $events_table_structure = $cc_uam_config['events_table_structure'];
338 $sql = "CREATE TABLE $new_table_name ( $events_table_structure ) $charset_collate;";
339 return $wpdb->query($sql);
340 }
341
342 private static function table_exists($name){
343 global $wpdb;
344 $table_name = $name;
345 $q_res = $wpdb->get_var("SHOW TABLES LIKE '$table_name'");
346 $res = $q_res === $table_name;
347 return $res;
348 }
349
350 private static function drop_table($table_name){
351 global $wpdb;
352 $wpdb->query("DROP TABLE $table_name");
353 }
354
355 private static function generate_grouping_query($table_name, $forget_properties, $id_interval){
356 //216000 = 60*60*24 = a day
357 global $cc_uam_config;
358 $start = $id_interval['start'];
359 $end = $id_interval['end'];
360
361 $forgettable_properties = $cc_uam_config['forgettable_properties'];
362 $unforgettable_properties = array('type', 'ad_id');
363 $properties = array_diff($forgettable_properties, $forget_properties);
364 $properties = array_merge($properties, $unforgettable_properties);
365 $processed_props = array();
366 foreach ($properties as $property){
367 $processed_props[] = "t1.$property";
368 }
369 $props_str = implode(', ', $properties);
370
371// $temp_table_name = $table_name . '_' . time();
372// self::create_empty_events_table($temp_table_name);
373
374 return
375 "select MIN(t1.id) as id, cast(t1.time As date) as time, t1.type, t1.uuid, t1.ip, t1.ad_id, t1.ad_slide_id, t1.place_id, sum(t1.value) as value
376from $table_name t1
377where t1.id BETWEEN '$start' AND '$end'
378group by $props_str, cast(t1.time As date)";
379 }
380
381 private static function insert_into_persistent_query($sub_query=null){
382 global $cc_uam_config;
383 $table_name = $cc_uam_config['table_name_events'];
384 $temp_table_name = self::get_temp_table_name();
385 $columns = 'id, time, type, uuid, ip, ad_id, ad_slide_id, place_id, value';
386 if($sub_query === null)
387 $sub_query = "SELECT $columns FROM $temp_table_name";
388 $insert_into_persistent_query = "INSERT INTO $table_name ($columns) $sub_query";
389 return $insert_into_persistent_query;
390 }
391
392 private static function table_count($table_name){
393 global $wpdb;
394
395 $res = $wpdb->get_var("SELECT COUNT(*) FROM $table_name");
396 return is_numeric($res) ? intval($res) : 0;
397 }
398
399 private static function tables_join_count($table_name_0, $table_name_1, $col_0, $col_1){
400 global $wpdb;
401 $query = "SELECT COUNT(*) FROM $table_name_0 t0 JOIN $table_name_1 t1 ON t0.$col_0=t1.$col_1";
402 $res = $wpdb->get_var($query);
403 return is_numeric($res) ? intval($res) : 0;
404 }
405
406 public static function needs_rescue(){
407 global $cc_uam_config;
408 $table_name = $cc_uam_config['table_name_events'];
409 $temp_table_name = self::get_temp_table_name();
410 $exists = self::table_exists($temp_table_name);
411 if($exists){
412 $tt_count = self::table_count($temp_table_name);
413 $tsj_count = self::tables_join_count($table_name, $temp_table_name, 'id', 'id');
414 return $tt_count > $tsj_count;
415 }
416 return false; //temp does not exist, can not rescue whatsoever
417 }
418
419 private static function rescue(){
420 global $cc_uam_config;
421 global $wpdb;
422 $table_name = $cc_uam_config['table_name_events'];
423 $temp_table_name = self::get_temp_table_name();
424
425 $unknown_rows_query = "SELECT t1.* FROM
426$table_name t0
427RIGHT JOIN $temp_table_name t1 ON t0.id=t1.id WHERE t0.id IS NULL"; //see: https://stackoverflow.com/questions/20602826/sql-server-need-join-but-where-not-equal-to
428
429 $query = self::insert_into_persistent_query($unknown_rows_query);
430
431 $wpdb->query($query);
432 self::drop_table($temp_table_name);
433 }
434
435
436}
437
438/*
439
440
441select t1.time, t1.type, t1.uuid, t1.ip, t1.ad_id, t1.ad_slide_id, t1.place_id, sum(t1.value) as value
442from wp_codeneric_uam_events t1
443where time BETWEEN '2017-06-06 11:54:26' AND '2017-06-06 15:54:26'
444group by t1.uuid, t1.type, t1.ad_id, t1.place_id, cast(t1.time As date)