· 9 years ago · Nov 17, 2016, 02:14 AM
1<?php
2/*
3* Script to get all records from wp_lpt table
4* Check for duplicates
5* Create an array of all the correct records
6* Create a new table (with composite index if not exist)
7* Insert correct records into database
8*/
9class WP_LPT
10{
11 private $options;
12
13 function __construct($options = array()) {
14
15 self::console('WP_LPT init ...');
16
17 // Increase PHP limit
18 ini_set('memory_limit', '8G');
19
20 // show errors
21 ini_set('display_errors', 1);
22 ini_set('display_startup_errors', 1);
23 error_reporting(E_ALL);
24
25
26 self::console(ini_get('memory_limit'));
27
28 $this->options = $options;
29
30 $this->connectDB();
31 }
32
33 public static function console($msg) {
34 echo $msg . "\r\n";
35 }
36
37 public function error($msg) {
38 echo "Error: " . $msg . "\r\n";
39 exit;
40 }
41
42 private function connectDB() {
43
44 $to_check = ['host', 'user', 'pass', 'database'];
45
46 foreach ( $to_check as $key ) {
47
48 if ( empty($this->options[$key]) )
49 $this->error("Missing required option - " . $key);
50 }
51
52 extract($this->options);
53
54 $conn = new mysqli( $host, $user, $pass, $database );
55
56 if ( $conn->connect_errno )
57 $this->error("Failed to connect to MySQL: " . $conn->connect_error);
58 else
59 self::console('Successfully connected to ' . $database . ' on ' . $host);
60
61 /* ======================================================================= */
62
63 $result = $conn->query("SELECT count(*) from wp_lpt");
64 $start_count = $result->fetch_row()[0];
65 self::console('Start Count: ' . $start_count);
66
67
68 $query = "SELECT * from wp_lpt";
69
70 $unique_rows = [];
71
72 // if ($results = $conn->query($query, MYSQLI_USE_RESULT)) {
73 if ($results = $conn->query($query)) {
74
75 // fetch single row
76 while ($row = $results->fetch_row()) {
77
78 // ex. $result[{user_id}_{action}_{object_id}]
79 $composite_index = $row[1] . '_' . $row[2] . '_' . $row[3];
80
81 if (isset($unique_rows[$composite_index])) {
82
83 // keep higher learned value
84 if ($unique_rows[$composite_index][4] < $row[4]) {
85 $unique_rows[$composite_index][4] = $row[4];
86 }
87 // if same learned value, keep older created_at time
88 elseif ($unique_rows[$composite_index][4] == $row[4]) {
89
90 // if hash time is newer than current row time, replace hash value with current row
91 if (strtotime($unique_rows[$composite_index][5]) > strtotime($row[5])) {
92 $unique_rows[$composite_index][5] = $row[5];
93 }
94 }
95
96 }
97 else $unique_rows[$composite_index] = $row;
98 }
99
100 $results->close();
101 }
102
103 $end_count = count($unique_rows);
104 self::console('End Count: ' . $end_count);
105
106 $removed_count = $start_count - $end_count;
107 self::console('Duplicates Removed: ' . $removed_count);
108
109 $create_table_query = "CREATE TABLE IF NOT EXISTS `wp_lpt_duplicates_removed` (
110 `id` bigint(20) NOT NULL AUTO_INCREMENT,
111 `user_id` bigint(20) NOT NULL,
112 `action` varchar(255) NOT NULL,
113 `object_id` bigint(20) NOT NULL,
114 `learned` bigint(20) DEFAULT NULL,
115 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
116 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
117 UNIQUE KEY `id` (`id`),
118 UNIQUE KEY `lpt` (`user_id`,`action`,`object_id`),
119 KEY `user_id` (`user_id`),
120 KEY `action` (`action`),
121 KEY `object_id` (`object_id`),
122 KEY `learned` (`learned`),
123 KEY `time_idx` (`time`)
124 ) ENGINE=InnoDB AUTO_INCREMENT=6021852 DEFAULT CHARSET=utf8;";
125
126 self::console("Begin Inserting Process");
127
128 if ($conn->query($create_table_query) === TRUE) {
129
130 self::console("CREATE TABLE wp_lpt_duplicates_removed SUCCESS");
131
132 // attempt to bind values
133 // $sql = "INSERT INTO wp_lpt_duplicates_removed (id, user_id, action, object_id, learned, time, created_at) values (?,?,?,?,?,?,?)" . str_repeat(",(?,?,?,?,?,?,?)", $end_count-1);
134 // $stmt = $conn->prepare($sql);
135
136 $sql = "INSERT INTO wp_lpt_duplicates_removed (id, user_id, action, object_id, learned, time, created_at) values ";
137
138 $count = 0;
139
140 foreach ($unique_rows as $key => &$row) {
141
142 self::console("Preparing row id " . $row[0]);
143
144 /*
145 * Memory Usage: 1668736616
146 * Process Time: 863.522994041
147 */
148 // if ($conn->query("INSERT into wp_lpt_duplicates_removed (id, user_id, action, object_id, learned, time, created_at) values ('".$row[0]."', '".$row[1]."', '".$row[2]."', '".$row[3]."', '".$row[4]."', '".$row[5]."', '".$row[6]."')") === TRUE) {
149 // self::console("Inserted record with id: " . $row[0]);
150 // }
151 // else {
152 // $this->error("Error: " . $conn->error);
153 // }
154
155 /*
156 * 1 HUGE INSERT
157 */
158 // $stmt->bind_param("iisiiss", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5], $row[6]);
159
160 if ($count > 0)
161 $sql .= ", ";
162
163 $sql .= "(".$row[0].", ".$row[1].", '".$row[2]."', ".$row[3].", ".$row[4].", '".$row[5]."', '".$row[6]."')";
164
165 $count++;
166
167 // attempting to save PHP memory usage
168 unset($unique_rows[$key]);
169 }
170
171 if ($conn->query($sql) === TRUE) {
172 self::console("Big Insert Success");
173 }
174 else $this->error("Error: " . $conn->error);
175
176 }
177 else $this->error("Error: " . $conn->error);
178
179 $conn->close();
180
181
182 /* ======================================================================================================================== */
183
184 // $results = $conn->query($query)->fetch_all();
185
186 // $start_count = count($results);
187
188 // self::console('Start Count: ' . $start_count);
189
190 // $no_duplicates = $this->removeDuplicates($results);
191
192 // $end_count = count($no_duplicates);
193
194 // self::console('End Count: ' . $end_count);
195
196 // $removed_count = $start_count - $end_count;
197
198 // self::console('Duplicates Removed: ' . $removed_count);
199
200 // $unique_rows = $this->createUnique($conn);
201 // self::console(count($unique_rows));
202
203 // $conn->query($create_table_query);
204
205 self::console('Start Count: ' . $start_count);
206 self::console('End Count: ' . $end_count);
207 self::console('Duplicates Removed: ' . $removed_count);
208
209 $time = microtime(true) - $_SERVER["REQUEST_TIME_FLOAT"];
210 self::console("Memory Usage: " . memory_get_peak_usage());
211 self::console("Process Time: {$time}");
212 }
213
214 // private function removeDuplicates(array &$array) {
215
216 // $result = []; // our hashed array
217
218 // foreach ($array as $key => $row) {
219
220 // // ex. $result[{user_id}_{action}_{object_id}]
221 // $composite_index = $row[1] . '_' . $row[2] . '_' . $row[3];
222
223 // if (isset($result[$composite_index])) {
224
225 // // keep higher learned value
226 // if ($result[$composite_index][4] < $row[4]) {
227 // $result[$composite_index][4] = $row[4];
228 // }
229 // // if same learned value, keep older created_at time
230 // elseif ($result[$composite_index][4] == $row[4]) {
231
232 // // if hash time is newer than current row time, replace hash value with current row
233 // if (strtotime($result[$composite_index][5]) > strtotime($row[5])) {
234 // $result[$composite_index][5] = $row[5];
235 // }
236 // }
237
238 // }
239 // else $result[$composite_index] = $row;
240
241 // unset($array[$key]);
242 // }
243
244 // return $result;
245 // }
246
247 /*
248 * fetching result one at a time saves A LOT of PHP memory
249 */
250 // private function createUnique($conn) {
251
252 // $unique_rows = [];
253
254 // $query = "SELECT * from wp_lpt";
255
256 // if ($results = $conn->query($query)) {
257
258 // // fetch single row
259 // while ($row = $results->fetch_row()) {
260
261 // // ex. $result[{user_id}_{action}_{object_id}]
262 // $composite_index = $row[1] . '_' . $row[2] . '_' . $row[3];
263
264 // if (isset($unique_rows[$composite_index])) {
265
266 // // keep higher learned value
267 // if ($unique_rows[$composite_index][4] < $row[4]) {
268 // $unique_rows[$composite_index][4] = $row[4];
269 // }
270 // // if same learned value, keep older created_at time
271 // elseif ($unique_rows[$composite_index][4] == $row[4]) {
272
273 // // if hash time is newer than current row time, replace hash value with current row
274 // if (strtotime($unique_rows[$composite_index][5]) > strtotime($row[5])) {
275 // $unique_rows[$composite_index][5] = $row[5];
276 // }
277 // }
278
279 // }
280 // else $unique_rows[$composite_index] = $row;
281 // }
282 // }
283
284 // $results->close();
285
286 // return $unique_rows;
287 // }
288}
289
290$options = array(
291 'host' => 'localhost',
292 'user' => 'root',
293 'pass' => 'root',
294 'database' => 'mastery'
295);
296
297new WP_LPT( $options );
298?>