These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more
1 | <?php |
||
2 | /** |
||
3 | * The Table class. |
||
4 | * |
||
5 | * @file |
||
6 | * @package Tabulate |
||
7 | */ |
||
8 | |||
9 | namespace WordPress\Tabulate\DB; |
||
10 | |||
11 | use WordPress\Tabulate\Util; |
||
12 | |||
13 | /** |
||
14 | * The Table class encapsulates all the work that can be done on a database table. |
||
15 | */ |
||
16 | class Table { |
||
17 | |||
18 | /** |
||
19 | * A base table. |
||
20 | */ |
||
21 | const TYPE_TABLE = 'table'; |
||
22 | |||
23 | /** |
||
24 | * A database view, possibly of multiple base tables. |
||
25 | */ |
||
26 | const TYPE_VIEW = 'view'; |
||
27 | |||
28 | /** |
||
29 | * The database to which this table belongs. |
||
30 | * |
||
31 | * @var \WordPress\Tabulate\DB\Database |
||
32 | */ |
||
33 | protected $database; |
||
34 | |||
35 | /** |
||
36 | * The name of this table. |
||
37 | * |
||
38 | * @var string |
||
39 | */ |
||
40 | protected $name; |
||
41 | |||
42 | /** |
||
43 | * This table's comment. False until initialised. |
||
44 | * |
||
45 | * @var string |
||
46 | */ |
||
47 | protected $comment = false; |
||
48 | |||
49 | /** |
||
50 | * Either self::TYPE_TABLE or self::TYPE_VIEW. |
||
51 | * |
||
52 | * @var string |
||
53 | */ |
||
54 | protected $type; |
||
55 | |||
56 | /** |
||
57 | * The SQL statement used to create this table. |
||
58 | * |
||
59 | * @var string |
||
60 | */ |
||
61 | protected $defining_sql; |
||
62 | |||
63 | /** |
||
64 | * The SQL statement most recently saved by $this->get_records() |
||
65 | * |
||
66 | * @var string |
||
67 | */ |
||
68 | protected $saved_sql; |
||
69 | |||
70 | /** |
||
71 | * The statement parameters most recently saved by $this->get_records() |
||
72 | * |
||
73 | * @var string[] |
||
74 | */ |
||
75 | protected $saved_parameters; |
||
76 | |||
77 | /** |
||
78 | * Array of tables referred to by columns in this one. |
||
79 | * |
||
80 | * @var \WordPress\Tabulate\DB\Table[] |
||
81 | */ |
||
82 | protected $referenced_tables; |
||
83 | |||
84 | /** |
||
85 | * The names (only) of tables referenced by columns in this one. |
||
86 | * |
||
87 | * @var string[] |
||
88 | */ |
||
89 | protected $referenced_table_names; |
||
90 | |||
91 | /** |
||
92 | * Each joined table gets a unique alias, based on this. |
||
93 | * |
||
94 | * @var int |
||
95 | */ |
||
96 | protected $alias_count = 1; |
||
97 | |||
98 | /** |
||
99 | * Array of column names and objects for all of the columns in this table. |
||
100 | * |
||
101 | * @var \WordPress\Tabulate\DB\Column[] |
||
102 | */ |
||
103 | protected $columns = array(); |
||
104 | |||
105 | /** |
||
106 | * The filters to be applied. |
||
107 | * |
||
108 | * @var array |
||
109 | */ |
||
110 | protected $filters = array(); |
||
111 | |||
112 | /** |
||
113 | * Permitted operators and their names. |
||
114 | * |
||
115 | * @var array |
||
116 | */ |
||
117 | protected $operators = array( |
||
118 | 'like' => 'contains', |
||
119 | 'not like' => 'does not contain', |
||
120 | '=' => 'is', |
||
121 | '!=' => 'is not', |
||
122 | 'empty' => 'is empty', |
||
123 | 'not empty' => 'is not empty', |
||
124 | 'in' => 'is one of', |
||
125 | 'not in' => 'is not one of', |
||
126 | '>=' => 'is greater than or equal to', |
||
127 | '>' => 'is greater than', |
||
128 | '<=' => 'is less than or equal to', |
||
129 | '<' => 'is less than', |
||
130 | ); |
||
131 | |||
132 | /** |
||
133 | * The name of the column by which to order, or false if no column has been |
||
134 | * set. |
||
135 | * |
||
136 | * @var string|false |
||
137 | */ |
||
138 | protected $order_by = false; |
||
139 | |||
140 | /** |
||
141 | * The direction in which results should be ordered. Either ASC or DESC. |
||
142 | * |
||
143 | * @var string |
||
144 | */ |
||
145 | protected $order_dir = 'ASC'; |
||
146 | |||
147 | /** |
||
148 | * The RecordCounter. |
||
149 | * |
||
150 | * @var RecordCounter |
||
151 | */ |
||
152 | protected $record_counter; |
||
153 | |||
154 | /** |
||
155 | * The current page number. |
||
156 | * |
||
157 | * @var integer |
||
158 | */ |
||
159 | protected $current_page_num = 1; |
||
160 | |||
161 | /** |
||
162 | * The number of records to show on each page. |
||
163 | * |
||
164 | * @var integer |
||
165 | */ |
||
166 | protected $records_per_page = 30; |
||
167 | |||
168 | /** |
||
169 | * Create a new database table object. |
||
170 | * |
||
171 | * @param \WordPress\Tabulate\DB\Database $database The database to which this table belongs. |
||
172 | * @param string $name The name of the table. |
||
173 | */ |
||
174 | public function __construct( $database, $name ) { |
||
175 | $this->database = $database; |
||
176 | $this->name = $name; |
||
177 | $this->record_counter = new RecordCounter( $this ); |
||
178 | } |
||
179 | |||
180 | /** |
||
181 | * Add a filter. |
||
182 | * |
||
183 | * @param string|\WordPress\Tabulate\DB\Column $column Column name or object. |
||
184 | * @param string $operator The operator. |
||
185 | * @param string $value The value or values. |
||
186 | * @param boolean $force Whether to transform the value, for FKs. |
||
187 | * @throws Exception If there's anything wrong with the filter. |
||
188 | */ |
||
189 | public function add_filter( $column, $operator, $value, $force = false ) { |
||
190 | // Allow Column objects to be passed in. |
||
191 | if ( $column instanceof Column ) { |
||
192 | $column = $column->get_name(); |
||
193 | } |
||
194 | // Validate the column name. |
||
195 | $valid_columm = in_array( $column, array_keys( $this->get_columns() ), true ); |
||
196 | if ( ! $valid_columm ) { |
||
197 | // translators: Error message shown when a filter is passed an invalid field name. |
||
198 | $msg = __( '"%1$s" is not a valid column of table "%2$s".', 'tabulate' ); |
||
199 | throw new Exception( sprintf( $msg, $column, $this->get_name() ) ); |
||
200 | } |
||
201 | // Validate the operator. |
||
202 | $valid_operator = in_array( $operator, array_keys( $this->operators ), true ); |
||
203 | View Code Duplication | if ( ! $valid_operator ) { |
|
204 | // translators: Error message shown when a filter is passed an invalid operator. |
||
205 | $msg = __( '"%s" is not a valid operator.', 'tabulate' ); |
||
206 | throw new Exception( sprintf( $msg, $operator ) ); |
||
207 | } |
||
208 | // Validate the value. |
||
209 | $empty_value_allowed = ( strpos( $operator, 'empty' ) === false && ! empty( $value ) ); |
||
210 | $valid_value = (strpos( $operator, 'empty' ) !== false) || $empty_value_allowed; |
||
211 | View Code Duplication | if ( ! $valid_operator ) { |
|
212 | // translators: Error message shown when a filter is passed an invalid value. |
||
213 | $msg = __( '"%s" is not a valid value.', 'tabulate' ); |
||
214 | throw new Exception( sprintf( $msg, $value ) ); |
||
215 | } |
||
216 | // Save the filter for later application (see Table::apply_filters()). |
||
217 | if ( $valid_columm && $valid_operator && $valid_value ) { |
||
218 | $this->filters[] = array( |
||
219 | 'column' => $column, |
||
220 | 'operator' => $operator, |
||
221 | 'value' => $value, |
||
222 | 'force' => $force, |
||
223 | ); |
||
224 | } |
||
225 | } |
||
226 | |||
227 | /** |
||
228 | * Add multiple filters. |
||
229 | * |
||
230 | * @param string[] $filters the filters to add. |
||
231 | */ |
||
232 | public function add_filters( $filters ) { |
||
233 | foreach ( $filters as $filter ) { |
||
234 | $column = (isset( $filter['column'] )) ? $filter['column'] : false; |
||
235 | $operator = (isset( $filter['operator'] )) ? $filter['operator'] : false; |
||
236 | $value = (isset( $filter['value'] )) ? $filter['value'] : false; |
||
237 | $this->add_filter( $column, $operator, $value ); |
||
238 | } |
||
239 | } |
||
240 | |||
241 | /** |
||
242 | * Get the current filters. |
||
243 | * |
||
244 | * @param boolean $append_blank Whether to append a blank filter or not. |
||
245 | * @return string[] |
||
246 | */ |
||
247 | public function get_filters( $append_blank = false ) { |
||
248 | $out = $this->filters; |
||
249 | if ( $append_blank ) { |
||
250 | // Add an empty default filter to start with. |
||
251 | $title_col = $this->get_title_column(); |
||
252 | $first_filter = ( $title_col ) ? $title_col->get_name() : ''; |
||
253 | $out[] = array( |
||
254 | 'column' => $first_filter, |
||
255 | 'operator' => 'like', |
||
256 | 'value' => '', |
||
257 | ); |
||
258 | } |
||
259 | return $out; |
||
260 | } |
||
261 | |||
262 | /** |
||
263 | * Get the SQL join clause for joining to a foreign table. |
||
264 | * |
||
265 | * @param Table $table The foreign table to join to. |
||
266 | * @param string $alias The alias to use for the table. |
||
267 | * @param Column $column The column to join on. |
||
268 | * @return string |
||
269 | */ |
||
270 | protected function get_fk_join_clause( $table, $alias, $column ) { |
||
271 | return 'LEFT OUTER JOIN `' . $table->get_name() . '` AS f' . $alias |
||
272 | . ' ON (`' . $this->get_name() . '`.`' . $column->get_name() . '` ' |
||
273 | . ' = `f' . $alias . '`.`' . $table->get_pk_column()->get_name() . '`)'; |
||
274 | } |
||
275 | |||
276 | /** |
||
277 | * Apply the stored filters to the supplied SQL. |
||
278 | * |
||
279 | * @param string $sql The SQL to modify. |
||
280 | * @return array Parameter values, in the order of their occurence in $sql |
||
281 | */ |
||
282 | public function apply_filters( &$sql ) { |
||
283 | |||
284 | $params = array(); |
||
285 | $param_num = 1; // Incrementing parameter suffix, to permit duplicate columns. |
||
286 | $where_clause = ''; |
||
287 | $join_clause = ''; |
||
288 | foreach ( $this->filters as $filter_idx => $filter ) { |
||
289 | $f_column = $filter['column']; |
||
290 | $param_name = $filter['column'] . $param_num; |
||
291 | |||
292 | // Filters on foreign keys need to work on the FKs title column. |
||
293 | $column = $this->columns[ $f_column ]; |
||
294 | if ( $column->is_foreign_key() && ! $filter['force'] ) { |
||
295 | $join = $this->join_on( $column ); |
||
296 | $f_column = $join['column_alias']; |
||
297 | $join_clause .= $join['join_clause']; |
||
298 | } else { |
||
299 | // The result of join_on() above is quoted, so this must also be. |
||
300 | $f_column = "`" . $this->get_name() . "`.`$f_column`"; |
||
301 | } |
||
302 | |||
303 | if ( 'like' === $filter['operator'] || 'not like' === $filter['operator'] ) { |
||
304 | // LIKE or NOT LIKE. |
||
305 | $where_clause .= " AND CONVERT($f_column, CHAR) " . strtoupper( $filter['operator'] ) . " %s "; |
||
306 | $params[ $param_name ] = '%' . trim( $filter['value'] ) . '%'; |
||
307 | } elseif ( '=' === $filter['operator'] || '!=' === $filter['operator'] ) { |
||
308 | // Equals or does-not-equal. |
||
309 | $where_clause .= " AND $f_column " . $filter['operator'] . " %s "; |
||
310 | $params[ $param_name ] = trim( $filter['value'] ); |
||
311 | } elseif ( 'empty' === $filter['operator'] ) { |
||
312 | // IS EMPTY. |
||
313 | $where_clause .= " AND ($f_column IS NULL OR $f_column = '')"; |
||
314 | } elseif ( 'not empty' === $filter['operator'] ) { |
||
315 | // IS NOT EMPTY. |
||
316 | $where_clause .= " AND ($f_column IS NOT NULL AND $f_column != '')"; |
||
317 | } elseif ( 'in' === $filter['operator'] || 'not in' === $filter['operator'] ) { |
||
318 | // IN or NOT IN. |
||
319 | $placeholders = array(); |
||
320 | foreach ( Util::split_newline( $filter['value'] ) as $vid => $val ) { |
||
321 | $placeholders[] = "%s"; |
||
322 | $params[ $param_name . '_' . $vid ] = $val; |
||
323 | // Save the separated filter values for later. |
||
324 | $this->filters[ $filter_idx ]['values'][] = $val; |
||
325 | } |
||
326 | $negate = ( 'not in' === $filter['operator'] ) ? 'NOT' : ''; |
||
327 | $where_clause .= " AND ($f_column $negate IN (" . join( ", ", $placeholders ) . "))"; |
||
328 | } else { |
||
329 | // Other operators. They're already validated in self::addFilter(). |
||
330 | $where_clause .= " AND ($f_column " . $filter['operator'] . " %s)"; |
||
331 | $params[ $param_name ] = trim( $filter['value'] ); |
||
332 | } // End if(). |
||
0 ignored issues
–
show
|
|||
333 | |||
334 | $param_num++; |
||
335 | } // End foreach(). |
||
0 ignored issues
–
show
Unused Code
Comprehensibility
introduced
by
43% of this comment could be valid code. Did you maybe forget this after debugging?
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it. The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production. This check looks for comments that seem to be mostly valid code and reports them.
Loading history...
|
|||
336 | |||
337 | // Add clauses into SQL. |
||
338 | if ( ! empty( $where_clause ) ) { |
||
339 | $where_clause_pattern = '/^(.* FROM .*?)((?:GROUP|HAVING|ORDER|LIMIT|$).*)$/m'; |
||
340 | $where_clause = substr( $where_clause, 5 ); // Strip leading ' AND'. |
||
341 | $where_clause = "$1 $join_clause WHERE $where_clause $2"; |
||
342 | $sql = preg_replace( $where_clause_pattern, $where_clause, $sql ); |
||
343 | } |
||
344 | |||
345 | return $params; |
||
346 | } |
||
347 | |||
348 | /** |
||
349 | * Get the name of the column by which this table should be ordered. |
||
350 | * |
||
351 | * There is no default for this, as some orderings can result in quite slow |
||
352 | * queries and it's best to let the user request this. It used to order by |
||
353 | * the title column by default. |
||
354 | * |
||
355 | * @return string |
||
356 | */ |
||
357 | public function get_order_by() { |
||
358 | return $this->order_by; |
||
359 | } |
||
360 | |||
361 | /** |
||
362 | * Change the column by which this table is ordered. |
||
363 | * |
||
364 | * @param string $order_by The name of the column to order by. |
||
365 | */ |
||
366 | public function set_order_by( $order_by ) { |
||
367 | if ( $this->get_column( $order_by ) ) { |
||
368 | $this->order_by = $order_by; |
||
369 | } |
||
370 | } |
||
371 | |||
372 | /** |
||
373 | * Get the current order direction. |
||
374 | * |
||
375 | * @return string Either ASC or DESC. |
||
376 | */ |
||
377 | public function get_order_dir() { |
||
378 | if ( empty( $this->order_dir ) ) { |
||
379 | $this->order_dir = 'ASC'; |
||
380 | } |
||
381 | return $this->order_dir; |
||
382 | } |
||
383 | |||
384 | /** |
||
385 | * Set the direction of ordering. |
||
386 | * |
||
387 | * @param string $order_dir Either 'ASC' or 'DESC' (case insensitive). |
||
388 | */ |
||
389 | public function set_order_dir( $order_dir ) { |
||
390 | if ( in_array( strtoupper( $order_dir ), array( 'ASC', 'DESC' ), true ) ) { |
||
391 | $this->order_dir = $order_dir; |
||
392 | } |
||
393 | } |
||
394 | |||
395 | /** |
||
396 | * For a given foreign key column, get an alias and join clause for selecting |
||
397 | * against that column's foreign values. If the column is not a foreign key, |
||
398 | * the alias will just be the qualified column name, and the join clause will |
||
399 | * be the empty string. |
||
400 | * |
||
401 | * @param Column $column The FK column. |
||
402 | * @return array Array with 'join_clause' and 'column_alias' keys |
||
403 | */ |
||
404 | public function join_on( $column ) { |
||
405 | $join_clause = ''; |
||
406 | $column_alias = '`' . $this->get_name() . '`.`' . $column->get_name() . '`'; |
||
407 | if ( $column->is_foreign_key() ) { |
||
408 | $fk1_table = $column->get_referenced_table(); |
||
409 | $fk1_title_column = $fk1_table->get_title_column(); |
||
410 | $join_clause .= ' LEFT OUTER JOIN `' . $fk1_table->get_name() . '` AS f' . $this->alias_count |
||
411 | . ' ON (`' . $this->get_name() . '`.`' . $column->get_name() . '` ' |
||
412 | . ' = `f' . $this->alias_count . '`.`' . $fk1_table->get_pk_column()->get_name() . '`)'; |
||
413 | $column_alias = "`f$this->alias_count`.`" . $fk1_title_column->get_name() . "`"; |
||
414 | // FK is also an FK? |
||
415 | if ( $fk1_title_column->is_foreign_key() ) { |
||
416 | $fk2_table = $fk1_title_column->get_referenced_table(); |
||
417 | $fk2_title_column = $fk2_table->get_title_column(); |
||
418 | $join_clause .= ' LEFT OUTER JOIN `' . $fk2_table->get_name() . '` AS ff' . $this->alias_count |
||
419 | . ' ON (f' . $this->alias_count . '.`' . $fk1_title_column->get_name() . '` ' |
||
420 | . ' = ff' . $this->alias_count . '.`' . $fk1_table->get_pk_column()->get_name() . '`)'; |
||
421 | $column_alias = "`ff$this->alias_count`.`" . $fk2_title_column->get_name() . "`"; |
||
422 | } |
||
423 | $this->alias_count++; |
||
424 | } |
||
425 | return array( |
||
426 | 'join_clause' => $join_clause, |
||
427 | 'column_alias' => $column_alias, |
||
428 | ); |
||
429 | } |
||
430 | |||
431 | /** |
||
432 | * Get rows, optionally with pagination. |
||
433 | * |
||
434 | * @param boolean $with_pagination Whether to only return the top N results. |
||
435 | * @param boolean $save_sql Whether to store the SQL for later use. |
||
436 | * @return \WordPress\Tabulate\DB\Record[] |
||
437 | */ |
||
438 | public function get_records( $with_pagination = true, $save_sql = false ) { |
||
439 | // Build basic SELECT statement. |
||
440 | $sql = 'SELECT ' . $this->columns_sql_select() . ' FROM `' . $this->get_name() . '`'; |
||
441 | |||
442 | // Ordering. |
||
443 | if ( false !== $this->get_order_by() ) { |
||
444 | $order_by = $this->get_column( $this->get_order_by() ); |
||
445 | if ( $order_by ) { |
||
446 | $order_by_join = $this->join_on( $order_by ); |
||
447 | $sql .= $order_by_join['join_clause'] . ' ORDER BY ' . $order_by_join['column_alias'] . ' ' . $this->get_order_dir(); |
||
448 | } |
||
449 | } |
||
450 | |||
451 | $params = $this->apply_filters( $sql ); |
||
452 | |||
453 | // Then limit to the ones on the current page. |
||
454 | if ( $with_pagination ) { |
||
455 | $records_per_page = $this->get_records_per_page(); |
||
456 | $sql .= ' LIMIT ' . $records_per_page; |
||
457 | if ( $this->get_current_page_num() > 1 ) { |
||
458 | $sql .= ' OFFSET ' . ($records_per_page * ($this->get_current_page_num() - 1)); |
||
459 | } |
||
460 | } |
||
461 | |||
462 | // Run query and save SQL. |
||
463 | if ( ! empty( $params ) ) { |
||
464 | $sql = $this->database->get_wpdb()->prepare( $sql, $params ); |
||
465 | } |
||
466 | $rows = $this->database->get_wpdb()->get_results( $sql ); |
||
467 | |||
468 | $records = array(); |
||
469 | foreach ( $rows as $row ) { |
||
470 | $records[] = new Record( $this, $row ); |
||
471 | } |
||
472 | |||
473 | if ( $save_sql ) { |
||
474 | $this->saved_sql = $sql; |
||
475 | $this->saved_parameters = $params; |
||
476 | } |
||
477 | |||
478 | return $records; |
||
479 | } |
||
480 | |||
481 | /** |
||
482 | * Get the current page number. |
||
483 | * |
||
484 | * @return integer |
||
485 | */ |
||
486 | public function get_current_page_num() { |
||
487 | return $this->current_page_num; |
||
488 | } |
||
489 | |||
490 | /** |
||
491 | * Set the current page number (the first page is page 1). |
||
492 | * If you set this to be greater than the total page count, |
||
493 | * it will be reduced to that number. |
||
494 | * |
||
495 | * @param integer $new_page_num The new page number. |
||
496 | */ |
||
497 | public function set_current_page_num( $new_page_num ) { |
||
498 | if ( $this->current_page_num > $this->get_page_count() ) { |
||
499 | $this->current_page_num = $this->get_page_count(); |
||
0 ignored issues
–
show
The property
$current_page_num was declared of type integer , but $this->get_page_count() is of type double . Maybe add a type cast?
This check looks for assignments to scalar types that may be of the wrong type. To ensure the code behaves as expected, it may be a good idea to add an explicit type cast. $answer = 42;
$correct = false;
$correct = (bool) $answer;
Loading history...
|
|||
500 | } else { |
||
501 | $this->current_page_num = $new_page_num; |
||
502 | } |
||
503 | } |
||
504 | |||
505 | /** |
||
506 | * Get the number of records that are included in each page. |
||
507 | * |
||
508 | * @return integer |
||
509 | */ |
||
510 | public function get_records_per_page() { |
||
511 | return $this->records_per_page; |
||
512 | } |
||
513 | |||
514 | /** |
||
515 | * Set the number of records that will be fetched per page. |
||
516 | * |
||
517 | * @param integer $records_per_page The new number of records per page. |
||
518 | */ |
||
519 | public function set_records_per_page( $records_per_page ) { |
||
520 | $this->records_per_page = $records_per_page; |
||
521 | } |
||
522 | |||
523 | /** |
||
524 | * Get the saved SQL and its parameters. |
||
525 | * |
||
526 | * @return string[] |
||
527 | */ |
||
528 | public function get_saved_query() { |
||
529 | return array( |
||
530 | 'sql' => $this->saved_sql, |
||
531 | 'parameters' => $this->saved_parameters, |
||
532 | ); |
||
533 | } |
||
534 | |||
535 | /** |
||
536 | * Get the SQL for SELECTing all columns in this table. |
||
537 | * |
||
538 | * @return string |
||
539 | */ |
||
540 | private function columns_sql_select() { |
||
541 | $select = array(); |
||
542 | $table_name = $this->get_name(); |
||
543 | foreach ( $this->get_columns() as $col_name => $col ) { |
||
544 | if ( 'point' === $col->get_type() ) { |
||
545 | $select[] = "AsText(`$table_name`.`$col_name`) AS `$col_name`"; |
||
546 | } else { |
||
547 | $select[] = "`$table_name`.`$col_name`"; |
||
548 | } |
||
549 | } |
||
550 | return join( ', ', $select ); |
||
551 | } |
||
552 | |||
553 | /** |
||
554 | * Get a single record as an associative array. |
||
555 | * |
||
556 | * @param string $pk_val The value of the PK of the record to get. |
||
557 | * @return Record|false The record object, or false if it wasn't found. |
||
558 | */ |
||
559 | public function get_record( $pk_val ) { |
||
560 | $pk_column = $this->get_pk_column(); |
||
561 | if ( ! $pk_column ) { |
||
562 | return false; |
||
563 | } |
||
564 | $sql = "SELECT " . $this->columns_sql_select() . " " |
||
565 | . "FROM `" . $this->get_name() . "` " |
||
566 | . "WHERE `" . $pk_column->get_name() . "` = %s " |
||
567 | . "LIMIT 1"; |
||
568 | $params = array( $pk_val ); |
||
569 | $stmt = $this->database->get_wpdb()->prepare( $sql, $params ); |
||
570 | $row = $this->database->get_wpdb()->get_row( $stmt ); |
||
571 | return ( $row ) ? new Record( $this, $row ) : false; |
||
572 | } |
||
573 | |||
574 | /** |
||
575 | * Get a bare record with only default values. |
||
576 | * |
||
577 | * @return Record |
||
578 | */ |
||
579 | public function get_default_record() { |
||
580 | $row = array(); |
||
581 | foreach ( $this->get_columns() as $col ) { |
||
582 | $row[ $col->get_name() ] = $col->get_default(); |
||
583 | } |
||
584 | $record = new Record( $this, $row ); |
||
585 | return $record; |
||
586 | } |
||
587 | |||
588 | /** |
||
589 | * Whether this table should have changes recorded or not. |
||
590 | * The change-tracking tables themselves do not. |
||
591 | * |
||
592 | * @return boolean |
||
593 | */ |
||
594 | public function has_changes_recorded() { |
||
595 | return ! in_array( $this->get_name(), ChangeTracker::table_names(), true ); |
||
596 | } |
||
597 | |||
598 | /** |
||
599 | * Get this table's name. |
||
600 | * |
||
601 | * @return string The name of this table. |
||
602 | */ |
||
603 | public function get_name() { |
||
604 | return $this->name; |
||
605 | } |
||
606 | |||
607 | /** |
||
608 | * Whether this is a base table or a view. |
||
609 | * |
||
610 | * @return string Either `Table::TYPE_TABLE` or `Table::TYPE_VIEW`. |
||
611 | */ |
||
612 | public function get_type() { |
||
613 | if ( ! $this->type ) { |
||
614 | $this->get_defining_sql(); |
||
615 | } |
||
616 | return $this->type; |
||
617 | } |
||
618 | |||
619 | /** |
||
620 | * Whether this table is a table (as opposed to a view). |
||
621 | * |
||
622 | * @return boolean |
||
623 | */ |
||
624 | public function is_table() { |
||
625 | return $this->get_type() === self::TYPE_TABLE; |
||
626 | } |
||
627 | |||
628 | /** |
||
629 | * Whether this table is a view. |
||
630 | * |
||
631 | * @return boolean |
||
632 | */ |
||
633 | public function is_view() { |
||
634 | return $this->get_type() === self::TYPE_VIEW; |
||
635 | } |
||
636 | |||
637 | /** |
||
638 | * Whether this view is updatable. Always true for base tables. Currently |
||
639 | * always false for all views. |
||
640 | * |
||
641 | * @link https://dev.mysql.com/doc/refman/5.6/en/view-updatability.html |
||
642 | */ |
||
643 | public function is_updatable() { |
||
644 | if ( $this->is_table() ) { |
||
645 | return true; |
||
646 | } |
||
647 | return false; |
||
648 | } |
||
649 | |||
650 | /** |
||
651 | * Get this table's title. This is the title-cased name, if not otherwise |
||
652 | * defined. |
||
653 | * |
||
654 | * @return string The title |
||
655 | */ |
||
656 | public function get_title() { |
||
657 | return \WordPress\Tabulate\Text::titlecase( $this->get_name() ); |
||
658 | } |
||
659 | |||
660 | /** |
||
661 | * Get a list of permitted operators. |
||
662 | * |
||
663 | * @return string[] List of operators. |
||
664 | */ |
||
665 | public function get_operators() { |
||
666 | return $this->operators; |
||
667 | } |
||
668 | |||
669 | /** |
||
670 | * Get a count of the number of pages in the currently filtered record set. |
||
671 | * |
||
672 | * @return integer The page count. |
||
673 | */ |
||
674 | public function get_page_count() { |
||
675 | return ceil( $this->count_records() / $this->get_records_per_page() ); |
||
676 | } |
||
677 | |||
678 | /** |
||
679 | * Get the number of rows in the current filtered set. |
||
680 | * |
||
681 | * @return integer |
||
682 | */ |
||
683 | public function count_records() { |
||
684 | return $this->record_counter->get_count(); |
||
685 | } |
||
686 | |||
687 | /** |
||
688 | * Export this table's data (with filters applied) to a file on disk. |
||
689 | * |
||
690 | * @return string Full filesystem path to resulting temporary file. |
||
691 | */ |
||
692 | public function export() { |
||
693 | |||
694 | $columns = array(); |
||
695 | $column_headers = array(); |
||
696 | $join_clause = ''; |
||
697 | foreach ( $this->columns as $col_name => $col ) { |
||
698 | if ( $col->is_foreign_key() ) { |
||
699 | $col_join = $this->join_on( $col ); |
||
700 | $column_name = $col_join['column_alias']; |
||
701 | $join_clause .= $col_join['join_clause']; |
||
702 | } elseif ( 'point' === $col->get_type() ) { |
||
703 | $columns[] = "IF(`$this->name`.`$col_name` IS NOT NULL, AsText(`$this->name`.`$col_name`), '') AS `$col_name`"; |
||
704 | } else { |
||
705 | $column_name = "`$this->name`.`$col_name`"; |
||
706 | } |
||
707 | if ( 'point' !== $col->get_type() && isset( $column_name ) ) { |
||
708 | $columns[] = "REPLACE(IFNULL($column_name, ''),CONCAT(CHAR(13),CHAR(10)),CHAR(10))"; // 13 = \r and 10 = \n |
||
709 | } |
||
710 | $column_headers[] = $col->get_title(); |
||
711 | } |
||
712 | |||
713 | // Build basic SELECT statement. |
||
714 | $sql = 'SELECT ' . join( ',', $columns ) |
||
715 | . ' FROM `' . $this->get_name() . '` ' . $join_clause; |
||
716 | |||
717 | $params = $this->apply_filters( $sql ); |
||
718 | |||
719 | $fs = $this->get_database()->get_filesystem(); |
||
720 | $filename = $this->get_database()->get_tmp_dir() . uniqid( 'tabulate_' ) . '.csv'; |
||
721 | if ( DIRECTORY_SEPARATOR === '\\' ) { |
||
722 | // Clean Windows slashes, for MySQL's benefit. |
||
723 | $filename = str_replace( '\\', '/', $filename ); |
||
724 | } |
||
725 | // Clear out any old copy (the delete method will check for existence). |
||
726 | $fs->delete( $filename ); |
||
727 | // Build the final SQL, prepending the column headers in a UNION. |
||
728 | $sql = 'SELECT "' . join( '", "', $column_headers ) . '"' |
||
729 | . ' UNION ' . $sql |
||
730 | . ' INTO OUTFILE "' . $filename . '" ' |
||
731 | . ' FIELDS TERMINATED BY ","' |
||
732 | . ' ENCLOSED BY \'"\'' |
||
733 | . ' ESCAPED BY \'"\'' |
||
734 | . ' LINES TERMINATED BY "\r\n"'; |
||
735 | // Execute the SQL (hiding errors for now). |
||
736 | $wpdb = $this->database->get_wpdb(); |
||
737 | if ( ! empty( $params ) ) { |
||
738 | $sql = $wpdb->prepare( $sql, $params ); |
||
739 | } |
||
740 | $wpdb->hide_errors(); |
||
741 | $wpdb->query( $sql ); |
||
742 | // Make sure it exported. |
||
743 | if ( ! $fs->exists( $filename ) ) { |
||
744 | // Note that this error message is quoted in the documentation. |
||
745 | $msg = "Unable to create temporary export file:<br /><code>$filename</code>"; |
||
746 | Exception::wp_die( $msg, 'Export failed', $wpdb->last_error, $sql ); |
||
747 | } |
||
748 | $wpdb->show_errors(); |
||
749 | // Give the filename back to the controller, to send to the client. |
||
750 | return $filename; |
||
751 | } |
||
752 | |||
753 | /** |
||
754 | * Get one of this table's columns. |
||
755 | * |
||
756 | * @param string $name The column name. |
||
757 | * @return \WordPress\Tabulate\DB\Column|false The column, or false if it's not found. |
||
758 | */ |
||
759 | public function get_column( $name ) { |
||
760 | $columns = $this->get_columns(); |
||
761 | return ( isset( $columns[ $name ] ) ) ? $columns[ $name ] : false; |
||
762 | } |
||
763 | |||
764 | /** |
||
765 | * Reset the column, comment, and defining SQL of this table. This forces |
||
766 | * them to be re-read from the databaes when next required. |
||
767 | */ |
||
768 | public function reset() { |
||
769 | $this->referenced_tables = false; |
||
770 | $this->columns = array(); |
||
771 | $this->comment = false; |
||
0 ignored issues
–
show
The property
$comment was declared of type string , but false is of type false . Maybe add a type cast?
This check looks for assignments to scalar types that may be of the wrong type. To ensure the code behaves as expected, it may be a good idea to add an explicit type cast. $answer = 42;
$correct = false;
$correct = (bool) $answer;
Loading history...
|
|||
772 | $this->defining_sql = false; |
||
0 ignored issues
–
show
The property
$defining_sql was declared of type string , but false is of type false . Maybe add a type cast?
This check looks for assignments to scalar types that may be of the wrong type. To ensure the code behaves as expected, it may be a good idea to add an explicit type cast. $answer = 42;
$correct = false;
$correct = (bool) $answer;
Loading history...
|
|||
773 | $this->record_counter->clear(); |
||
774 | } |
||
775 | |||
776 | /** |
||
777 | * Get a list of this table's columns, optionally constrained by their type. |
||
778 | * |
||
779 | * @param string $type Only return columns of this type. |
||
780 | * @return \WordPress\Tabulate\DB\Column[] Array of this table's columns, keyed by the column names. |
||
781 | */ |
||
782 | public function get_columns( $type = null ) { |
||
783 | if ( empty( $this->columns ) ) { |
||
784 | $this->columns = array(); |
||
785 | $sql = "SHOW FULL COLUMNS FROM `" . $this->get_name() . "`"; |
||
786 | $columns = $this->get_database()->get_wpdb()->get_results( $sql, ARRAY_A ); |
||
787 | foreach ( $columns as $column_info ) { |
||
788 | $column = new Column( $this, $column_info ); |
||
789 | $this->columns[ $column->get_name() ] = $column; |
||
790 | } |
||
791 | } |
||
792 | if ( is_null( $type ) ) { |
||
793 | return $this->columns; |
||
794 | } |
||
795 | $out = array(); |
||
796 | foreach ( $this->get_columns() as $col ) { |
||
797 | if ( $col->get_type() === $type ) { |
||
798 | $out[ $col->get_name() ] = $col; |
||
799 | } |
||
800 | } |
||
801 | return $out; |
||
802 | } |
||
803 | |||
804 | /** |
||
805 | * Add a new column to this table. |
||
806 | * |
||
807 | * @param string $name Table name. |
||
808 | * @param string $xtype_name Which 'xtype' to use. |
||
809 | * @param integer $size The length of the column. |
||
810 | * @param boolean $nullable Whether null values are allowed. |
||
811 | * @param string $default The default value. |
||
812 | * @param boolean $auto_increment Whether it shall be an auto-inrementing column. |
||
813 | * @param boolean $unique Whether a unique constraint shall be applied. |
||
814 | * @param string $comment The table comment. |
||
815 | * @param Table $target_table For 'cross-reference' types, the name of the foreign table. |
||
816 | * @param string $after The name of the column after which this one shall be added. |
||
817 | * @throws Exception If the column already exists or is unable to be added. |
||
818 | */ |
||
819 | public function add_column( $name, $xtype_name, $size = null, $nullable = null, $default = null, $auto_increment = null, $unique = null, $comment = null, $target_table = null, $after = null ) { |
||
820 | // Can it be done? |
||
821 | if ( ! current_user_can( 'promote_users' ) ) { |
||
822 | throw new Exception( 'Only administrators are allowed to add columns to tables' ); |
||
823 | } |
||
824 | if ( $this->get_column( $name ) ) { |
||
825 | throw new Exception( "Column '$name' already exists on table '" . $this->get_name() . "'" ); |
||
826 | } |
||
827 | |||
828 | // Build SQL statement. |
||
829 | $col_def = Column::get_column_definition( $name, $xtype_name, $size, $nullable, $default, $auto_increment, $unique, $comment, $target_table, $after ); |
||
830 | |||
831 | $sql = "ALTER TABLE `" . $this->get_name() . "` ADD COLUMN $col_def"; |
||
832 | |||
833 | // Execute the SQL and reset the cache. |
||
834 | $query = $this->get_database()->query( $sql ); |
||
835 | if ( false === $query ) { |
||
836 | throw new Exception( "Unable to add column '$name'. SQL was: <code>$sql</code>" ); |
||
837 | } |
||
838 | $this->reset(); |
||
839 | } |
||
840 | |||
841 | /** |
||
842 | * Get the table comment text; for views, this returns '(View)'. |
||
843 | * |
||
844 | * @return string |
||
845 | */ |
||
846 | public function get_comment() { |
||
847 | if ( ! $this->comment ) { |
||
848 | $sql = $this->get_defining_sql(); |
||
849 | $comment_pattern = '/.*\)(?:.*COMMENT[\w=]*\'(.*)\')?/si'; |
||
850 | preg_match( $comment_pattern, $sql, $matches ); |
||
851 | $this->comment = ( isset( $matches[1] ) ) ? $matches[1] : ''; |
||
852 | $this->comment = str_replace( "''", "'", $this->comment ); |
||
853 | } |
||
854 | if ( empty( $this->comment ) && $this->is_view() ) { |
||
855 | $this->comment = '(View)'; |
||
856 | } |
||
857 | return $this->comment; |
||
858 | } |
||
859 | |||
860 | /** |
||
861 | * Get a list of all the unique columns in this table. |
||
862 | * |
||
863 | * @return \WordPress\Tabulate\DB\Column[] |
||
864 | */ |
||
865 | public function get_unique_columns() { |
||
866 | $cols = array(); |
||
867 | foreach ( $this->get_columns() as $column ) { |
||
868 | if ( $column->is_unique() ) { |
||
869 | $cols[] = $column; |
||
870 | } |
||
871 | } |
||
872 | return $cols; |
||
873 | } |
||
874 | |||
875 | /** |
||
876 | * Get the first unique-keyed column. |
||
877 | * If there is no unique non-PK column then just use the PK. |
||
878 | * |
||
879 | * @return \WordPress\Tabulate\DB\Column |
||
880 | */ |
||
881 | public function get_title_column() { |
||
882 | // Try to get the first non-PK unique key. |
||
883 | foreach ( $this->get_columns() as $column ) { |
||
884 | if ( $column->is_unique() && ! $column->is_primary_key() ) { |
||
885 | return $column; |
||
886 | } |
||
887 | } |
||
888 | // But if that fails, just use the primary key. |
||
889 | return $this->get_pk_column(); |
||
890 | } |
||
891 | |||
892 | /** |
||
893 | * Get the SQL statement used to create this table, as given by the 'SHOW |
||
894 | * CREATE TABLE' command. |
||
895 | * |
||
896 | * @return string The SQL statement used to create this table. |
||
897 | * @throws Exception If the table or view is not found. |
||
898 | */ |
||
899 | public function get_defining_sql() { |
||
900 | if ( empty( $this->defining_sql ) ) { |
||
901 | $defining_sql = $this->database->get_wpdb()->get_row( "SHOW CREATE TABLE `$this->name`" ); |
||
902 | if ( isset( $defining_sql->{'Create Table'} ) ) { |
||
903 | $defining_sql = $defining_sql->{'Create Table'}; |
||
904 | $this->type = self::TYPE_TABLE; |
||
905 | } elseif ( isset( $defining_sql->{'Create View'} ) ) { |
||
906 | $defining_sql = $defining_sql->{'Create View'}; |
||
907 | $this->type = self::TYPE_VIEW; |
||
908 | } else { |
||
909 | throw new Exception( 'Table or view not found: ' . $this->name ); |
||
910 | } |
||
911 | $this->defining_sql = $defining_sql; |
||
912 | } |
||
913 | return $this->defining_sql; |
||
914 | } |
||
915 | |||
916 | /** |
||
917 | * Get this table's Primary Key column. |
||
918 | * |
||
919 | * @return \WordPress\Tabulate\DB\Column|false The PK column or false if there isn't one. |
||
920 | */ |
||
921 | public function get_pk_column() { |
||
922 | foreach ( $this->get_columns() as $column ) { |
||
923 | if ( $column->is_primary_key() ) { |
||
924 | return $column; |
||
925 | } |
||
926 | } |
||
927 | return false; |
||
928 | } |
||
929 | |||
930 | /** |
||
931 | * Get a list of this table's foreign keys and the tables to which they refer. |
||
932 | * This does *not* take into account a user's permissions (i.e. the |
||
933 | * name of a table which the user is not allowed to read may be returned). |
||
934 | * |
||
935 | * @param boolean $instantiate Whether to instantiate the Table objects (or just return their names). |
||
936 | * @return string[]|Table[] The list of <code>column_name => table_name|Table</code> pairs. |
||
937 | */ |
||
938 | public function get_referenced_tables( $instantiate = false ) { |
||
939 | |||
940 | // Extract the FK info from the CREATE TABLE statement. |
||
941 | if ( ! is_array( $this->referenced_tables ) ) { |
||
942 | $this->referenced_table_names = array(); |
||
943 | $defining_sql = $this->get_defining_sql(); |
||
944 | $fk_pattern = '|FOREIGN KEY \(`(.*?)`\) REFERENCES `(.*?)`|'; |
||
945 | preg_match_all( $fk_pattern, $defining_sql, $matches ); |
||
946 | if ( isset( $matches[1] ) && count( $matches[1] ) > 0 ) { |
||
947 | foreach ( array_combine( $matches[1], $matches[2] ) as $col_name => $tab_name ) { |
||
948 | $this->referenced_table_names[ $col_name ] = $tab_name; |
||
949 | } |
||
950 | } |
||
951 | } |
||
952 | |||
953 | if ( $instantiate ) { |
||
954 | $this->referenced_tables = array(); |
||
955 | foreach ( $this->referenced_table_names as $ref_col => $ref_tab ) { |
||
956 | $this->referenced_tables[ $ref_col ] = new Table( $this->get_database(), $ref_tab ); |
||
957 | } |
||
958 | } |
||
959 | |||
960 | return $instantiate ? $this->referenced_tables : $this->referenced_table_names; |
||
961 | } |
||
962 | |||
963 | /** |
||
964 | * Get a list of tables with foreign keys referring here, and which of their columns are the FKs. |
||
965 | * |
||
966 | * @return array With keys 'table' and 'column'. |
||
967 | */ |
||
968 | public function get_referencing_tables() { |
||
969 | $out = array(); |
||
970 | // For all tables in the Database... |
||
971 | foreach ( $this->get_database()->get_tables() as $table ) { |
||
972 | // ...get a list of the tables they reference. |
||
973 | $foreign_tables = $table->get_referenced_tables(); |
||
974 | foreach ( $foreign_tables as $foreign_column => $referenced_table_name ) { |
||
975 | // If this table is a referenced table, collect the table from which it's referenced. |
||
976 | if ( $referenced_table_name === $this->get_name() ) { |
||
977 | $out[ $table->get_name() . '.' . $foreign_column ] = array( |
||
978 | 'table' => $table, |
||
979 | 'column' => $foreign_column, |
||
980 | ); |
||
981 | } |
||
982 | } |
||
983 | } |
||
984 | return $out; |
||
985 | } |
||
986 | |||
987 | /** |
||
988 | * Get a list of the names of the foreign keys in this table. |
||
989 | * |
||
990 | * @return string[] Names of foreign key columns in this table. |
||
991 | */ |
||
992 | public function get_foreign_key_names() { |
||
993 | return array_keys( $this->get_referenced_tables( false ) ); |
||
994 | } |
||
995 | |||
996 | /** |
||
997 | * Get the database to which this table belongs. |
||
998 | * |
||
999 | * @return \WordPress\Tabulate\DB\Database The database object. |
||
1000 | */ |
||
1001 | public function get_database() { |
||
1002 | return $this->database; |
||
1003 | } |
||
1004 | |||
1005 | /** |
||
1006 | * Get a string representation of this table; a succinct summary of its |
||
1007 | * columns and their types, keys, etc. |
||
1008 | * |
||
1009 | * @return string A summary of this table. |
||
1010 | */ |
||
1011 | public function __toString() { |
||
1012 | $col_count = count( $this->get_columns() ); |
||
1013 | $out = "\n"; |
||
1014 | $out .= '+-----------------------------------------+' . "\n"; |
||
1015 | $out .= '| ' . $this->get_name() . ' (' . $col_count . ' columns)' . "\n"; |
||
1016 | $out .= '+-----------------------------------------+' . "\n"; |
||
1017 | foreach ( $this->get_columns() as $column ) { |
||
1018 | $out .= "| $column \n"; |
||
1019 | } |
||
1020 | $out .= '+-----------------------------------------+' . "\n\n"; |
||
1021 | return $out; |
||
1022 | } |
||
1023 | |||
1024 | /** |
||
1025 | * Get an XML representation of the structure of this table. |
||
1026 | * |
||
1027 | * @return DOMElement The XML 'table' node. |
||
1028 | */ |
||
1029 | public function to_xml() { |
||
1030 | $dom = new DOMDocument( '1.0', 'UTF-8' ); |
||
1031 | $table = $dom->createElement( 'table' ); |
||
1032 | $dom->appendChild( $table ); |
||
1033 | $name = $dom->createElement( 'name' ); |
||
1034 | $name->appendChild( $dom->createTextNode( $this->name ) ); |
||
1035 | $table->appendChild( $name ); |
||
1036 | foreach ( $this->get_columns() as $column ) { |
||
1037 | $table->appendChild( $dom->importNode( $column->toXml(), true ) ); |
||
1038 | } |
||
1039 | return $table; |
||
1040 | } |
||
1041 | |||
1042 | /** |
||
1043 | * Get a JSON representation of the structure of this table. |
||
1044 | * |
||
1045 | * @return string |
||
1046 | */ |
||
1047 | public function to_json() { |
||
1048 | $json = new Services_JSON(); |
||
1049 | $metadata = array(); |
||
1050 | foreach ( $this->get_columns() as $column ) { |
||
1051 | $metadata[] = array( |
||
1052 | 'name' => $column->get_name(), |
||
1053 | ); |
||
1054 | } |
||
1055 | return $json->encode( $metadata ); |
||
1056 | } |
||
1057 | |||
1058 | /** |
||
1059 | * Remove all filters. |
||
1060 | * |
||
1061 | * @return void |
||
1062 | */ |
||
1063 | public function reset_filters() { |
||
1064 | $this->filters = array(); |
||
1065 | } |
||
1066 | |||
1067 | /** |
||
1068 | * Delete a record and its associated change-tracker records. |
||
1069 | * |
||
1070 | * @param string $pk_value The value of the primary key of the record to delete. |
||
1071 | * @return void |
||
1072 | * @throws Exception When the user doesn't have permission, or any error occurs deleting the record. |
||
1073 | */ |
||
1074 | public function delete_record( $pk_value ) { |
||
1075 | // Check permission. |
||
1076 | if ( ! Grants::current_user_can( Grants::DELETE, $this->get_name() ) ) { |
||
1077 | throw new Exception( 'You do not have permission to delete data from this table.' ); |
||
1078 | } |
||
1079 | $rec = $this->get_record( $pk_value ); |
||
1080 | $wpdb = $this->database->get_wpdb(); |
||
1081 | $wpdb->hide_errors(); |
||
1082 | $del = $wpdb->delete( $this->get_name(), array( |
||
1083 | $this->get_pk_column()->get_name() => $pk_value, |
||
1084 | ) ); |
||
1085 | if ( false === $del ) { |
||
1086 | throw new Exception( $wpdb->last_error ); |
||
1087 | } |
||
1088 | foreach ( $rec->get_changes() as $change ) { |
||
1089 | $where_1 = array( |
||
1090 | 'changeset_id' => $change->changeset_id, |
||
1091 | ); |
||
1092 | $del_changes = $wpdb->delete( ChangeTracker::changes_name(), $where_1 ); |
||
1093 | if ( false === $del_changes ) { |
||
1094 | throw new Exception( $wpdb->last_error ); |
||
1095 | } |
||
1096 | $where_2 = array( |
||
1097 | 'id' => $change->changeset_id, |
||
1098 | ); |
||
1099 | $del_changesets = $wpdb->delete( ChangeTracker::changesets_name(), $where_2 ); |
||
1100 | if ( false === $del_changesets ) { |
||
1101 | throw new Exception( $wpdb->last_error ); |
||
1102 | } |
||
1103 | } |
||
1104 | $wpdb->show_errors(); |
||
1105 | $this->record_counter->clear(); |
||
1106 | } |
||
1107 | |||
1108 | /** |
||
1109 | * Save data to this table. If a primary key value is given, that row will be |
||
1110 | * updated; otherwise, a new row will be inserted. |
||
1111 | * |
||
1112 | * @param array $data The data to insert. |
||
1113 | * @param string $pk_value The value of the record's PK. Null if the record doesn't exist. |
||
1114 | * @return \WordPress\Tabulate\DB\Record The updated or inserted record. |
||
1115 | * @throws Exception If the user doesn't have permission, or something else has gone wrong. |
||
1116 | */ |
||
1117 | public function save_record( $data, $pk_value = null ) { |
||
1118 | // Changeset only created here if not already in progress. |
||
1119 | $changeset_comment = isset( $data['changeset_comment'] ) ? $data['changeset_comment'] : null; |
||
1120 | $change_tracker = new ChangeTracker( $this->get_database()->get_wpdb(), $changeset_comment ); |
||
1121 | |||
1122 | $columns = $this->get_columns(); |
||
1123 | |||
1124 | /* |
||
1125 | * Go through all data and clean it up before saving. |
||
1126 | */ |
||
1127 | $sql_values = array(); |
||
1128 | foreach ( $data as $field => $value ) { |
||
1129 | // Make sure this column exists in the DB. |
||
1130 | if ( ! isset( $columns[ $field ] ) ) { |
||
1131 | unset( $data[ $field ] ); |
||
1132 | continue; |
||
1133 | } |
||
1134 | $column = $this->get_column( $field ); |
||
1135 | |||
1136 | if ( $column->is_auto_increment() ) { |
||
1137 | // Auto-incrementing columns. |
||
1138 | ; // Do nothing (don't set $sql_values item). |
||
1139 | |||
1140 | } elseif ( $column->is_boolean() ) { |
||
1141 | $val_is_falseish = in_array( strtoupper( $value ), array( '0', 'FALSE', 'OFF', 'NO' ), true ); |
||
1142 | // Boolean values. |
||
1143 | if ( $column->nullable() && ( is_null( $value ) || '' === $value ) ) { |
||
1144 | // Null. |
||
1145 | $data[ $field ] = null; |
||
1146 | $sql_values[ $field ] = 'NULL'; |
||
1147 | View Code Duplication | } elseif ( ! $column->nullable() && ( is_null( $value ) || '' === $value ) ) { |
|
1148 | // Not nullable, set to default (don't set $sql_values item). |
||
1149 | $data[ $field ] = null; |
||
1150 | } elseif ( false === $value || $val_is_falseish ) { |
||
1151 | // False. |
||
1152 | $data[ $field ] = false; |
||
1153 | $sql_values[ $field ] = '0'; |
||
1154 | } else { |
||
1155 | // True. |
||
1156 | $data[ $field ] = true; |
||
1157 | $sql_values[ $field ] = '1'; |
||
1158 | } |
||
1159 | } elseif ( ! $column->allows_empty_string() && '' === $value && $column->nullable() ) { |
||
1160 | // Empty strings. |
||
1161 | $data[ $field ] = null; |
||
1162 | $sql_values[ $field ] = 'NULL'; |
||
1163 | |||
1164 | View Code Duplication | } elseif ( is_null( $value ) && $column->nullable() ) { |
|
1165 | // Nulls. |
||
1166 | $data[ $field ] = null; |
||
1167 | $sql_values[ $field ] = 'NULL'; |
||
1168 | |||
1169 | } elseif ( 'point' === $column->get_type() ) { |
||
1170 | // POINT columns. |
||
1171 | $sql_values[ $field ] = "GeomFromText('" . esc_sql( $value ) . "')"; |
||
1172 | |||
1173 | } elseif ( $column->is_numeric() ) { |
||
1174 | // Numeric values. |
||
1175 | $sql_values[ $field ] = (float) $value; |
||
1176 | |||
1177 | } else { |
||
1178 | // Everything else. |
||
1179 | $sql_values[ $field ] = "'" . esc_sql( $value ) . "'"; |
||
1180 | |||
1181 | } // End if(). |
||
0 ignored issues
–
show
Unused Code
Comprehensibility
introduced
by
43% of this comment could be valid code. Did you maybe forget this after debugging?
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it. The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production. This check looks for comments that seem to be mostly valid code and reports them.
Loading history...
|
|||
1182 | } // End foreach(). |
||
0 ignored issues
–
show
Unused Code
Comprehensibility
introduced
by
43% of this comment could be valid code. Did you maybe forget this after debugging?
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it. The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production. This check looks for comments that seem to be mostly valid code and reports them.
Loading history...
|
|||
1183 | |||
1184 | // Find the PK, and hide errors (for now). |
||
1185 | $pk_name = $this->get_pk_column()->get_name(); |
||
1186 | $this->database->get_wpdb()->hide_errors(); |
||
1187 | |||
1188 | // Compile SQL for insert and update statements. |
||
1189 | // This is a workaround for NULL support in \wpdb::update and \wpdb::insert. |
||
1190 | // Can probably be removed when https://core.trac.wordpress.org/ticket/15158 is resolved. |
||
1191 | $set_items = array(); |
||
1192 | foreach ( $sql_values as $field => $escd_datum ) { |
||
1193 | $set_items[] = "`$field` = $escd_datum"; |
||
1194 | } |
||
1195 | $set_clause = 'SET ' . join( ', ', $set_items ); |
||
1196 | |||
1197 | // Prevent PK from being set to empty. |
||
1198 | if ( isset( $data[ $pk_name ] ) && empty( $data[ $pk_name ] ) ) { |
||
1199 | unset( $data[ $pk_name ] ); |
||
1200 | } |
||
1201 | |||
1202 | $change_tracker->before_save( $this, $pk_value ); |
||
1203 | if ( ! empty( $pk_value ) ) { |
||
1204 | /* |
||
1205 | * Update? |
||
1206 | */ |
||
1207 | if ( ! Grants::current_user_can( Grants::UPDATE, $this->get_name() ) ) { |
||
1208 | throw new Exception( 'You do not have permission to update data in this table.' ); |
||
1209 | } |
||
1210 | $where_clause = $this->database->get_wpdb()->prepare( "WHERE `$pk_name` = %s", $pk_value ); |
||
1211 | $sql = 'UPDATE ' . $this->get_name() . " $set_clause $where_clause"; |
||
1212 | $this->get_database()->query( $sql, null, 'Unable to update a record' ); |
||
1213 | $new_pk_value = (isset( $data[ $pk_name ] ) ) ? $data[ $pk_name ] : $pk_value; |
||
1214 | |||
1215 | } else { |
||
1216 | /* |
||
1217 | * Or insert? |
||
1218 | */ |
||
1219 | if ( ! Grants::current_user_can( Grants::CREATE, $this->get_name() ) ) { |
||
1220 | throw new Exception( 'You do not have permission to insert records into this table.' ); |
||
1221 | } |
||
1222 | $sql = 'INSERT INTO ' . $this->get_name() . ' ' . $set_clause; |
||
1223 | $this->get_database()->query( $sql, null, 'Unable to create new record' ); |
||
1224 | if ( $this->get_pk_column()->is_auto_increment() ) { |
||
1225 | // Use the last insert ID. |
||
1226 | $new_pk_value = $this->database->get_wpdb()->insert_id; |
||
1227 | } elseif ( isset( $data[ $pk_name ] ) ) { |
||
1228 | // Or the PK value provided in the data. |
||
1229 | $new_pk_value = $data[ $pk_name ]; |
||
1230 | } else { |
||
1231 | // If neither of those work, how can we find out the new PK value? |
||
1232 | throw new Exception( "Unable to determine the value of the new record's prmary key. SQL was <code>$sql</code>" ); |
||
1233 | } |
||
1234 | } |
||
1235 | $new_record = $this->get_record( $new_pk_value ); |
||
1236 | if ( ! $new_record instanceof Record ) { |
||
1237 | throw new Exception( "Unable to fetch record with PK of: <code>$new_pk_value</code>. SQL was <code>$sql</code>" ); |
||
1238 | } |
||
1239 | |||
1240 | // Save the changes. |
||
1241 | $change_tracker->after_save( $this, $new_record ); |
||
1242 | |||
1243 | // Show errors again, reset the record count, |
||
1244 | // and return the new or updated record. |
||
1245 | $this->database->get_wpdb()->show_errors(); |
||
1246 | $this->record_counter->clear(); |
||
1247 | return $new_record; |
||
1248 | } |
||
1249 | |||
1250 | /** |
||
1251 | * Get a fully-qualified URL to a Back End page for this table. |
||
1252 | * |
||
1253 | * @param string $action Which action to use ('index', 'import', etc.). |
||
1254 | * @param string[]|boolean $extra_params Other query string parameters to add. |
||
1255 | * @param string $controller Which controller to use ('table', 'record', etc.). |
||
1256 | * @return string The full URL. |
||
1257 | */ |
||
1258 | public function get_url( $action = 'index', $extra_params = false, $controller = 'table' ) { |
||
1259 | $params = array( |
||
1260 | 'page' => 'tabulate', |
||
1261 | 'controller' => $controller, |
||
1262 | 'action' => $action, |
||
1263 | 'table' => $this->get_name(), |
||
1264 | ); |
||
1265 | if ( is_array( $extra_params ) ) { |
||
1266 | $params = array_merge( $_GET, $params, $extra_params ); // WPCS OK. |
||
1267 | } |
||
1268 | return admin_url( 'admin.php?' . http_build_query( $params ) ); |
||
1269 | } |
||
1270 | |||
1271 | /** |
||
1272 | * Rename this table and all of its change-tracker entries. |
||
1273 | * |
||
1274 | * @param string $new_name The new table name. |
||
1275 | * @throws Exception If the derired name already exists or some other error occurs. |
||
1276 | */ |
||
1277 | public function rename( $new_name ) { |
||
1278 | if ( $this->get_name() === $new_name ) { |
||
1279 | // Do nothing, we're trying to rename to the current name. |
||
1280 | return; |
||
1281 | } |
||
1282 | if ( $this->get_database()->get_table( $new_name ) ) { |
||
1283 | throw new Exception( "Table '$new_name' already exists" ); |
||
1284 | } |
||
1285 | $wpdb = $this->get_database()->get_wpdb(); |
||
1286 | $old_name = $this->get_name(); |
||
1287 | $wpdb->query( "RENAME TABLE `$old_name` TO `$new_name`;" ); |
||
1288 | $this->get_database()->reset(); |
||
1289 | $new = $this->get_database()->get_table( $new_name, false ); |
||
1290 | if ( ! $new ) { |
||
1291 | throw new Exception( "Table '$old_name' was not renamed to '$new_name'" ); |
||
1292 | } |
||
1293 | $this->name = $new->get_name(); |
||
1294 | $sql = "UPDATE `" . ChangeTracker::changes_name() . "`" |
||
1295 | . " SET `table_name` = '$new_name' " |
||
1296 | . " WHERE `table_name` = '$old_name';"; |
||
1297 | $wpdb->query( $sql ); |
||
1298 | } |
||
1299 | |||
1300 | /** |
||
1301 | * Set the table's comment. |
||
1302 | * |
||
1303 | * @param string $new_comment The comment to set. |
||
1304 | */ |
||
1305 | public function set_comment( $new_comment ) { |
||
1306 | if ( $new_comment === $this->get_comment() ) { |
||
1307 | // No need to do anything if the comment isn't changing. |
||
1308 | return; |
||
1309 | } |
||
1310 | $sql = "ALTER TABLE `" . $this->get_name() . "` COMMENT = '$new_comment'"; |
||
1311 | $this->get_database()->get_wpdb()->query( $sql ); |
||
1312 | $this->reset(); |
||
1313 | } |
||
1314 | |||
1315 | /** |
||
1316 | * Drop this table and all its history. |
||
1317 | */ |
||
1318 | public function drop() { |
||
1319 | $drop_table = 'DROP TABLE IF EXISTS `' . $this->get_name() . '`'; |
||
1320 | $this->get_database()->get_wpdb()->query( $drop_table ); |
||
1321 | $delete_history = "DELETE FROM `" . ChangeTracker::changes_name() . "` " |
||
1322 | . "WHERE table_name = '" . $this->get_name() . "'"; |
||
1323 | $this->get_database()->get_wpdb()->query( $delete_history ); |
||
1324 | $this->get_database()->reset(); |
||
1325 | } |
||
1326 | } |
||
1327 |
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.
The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.
This check looks for comments that seem to be mostly valid code and reports them.