assigning incompatible types to properties.
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(). |
||
333 | |||
334 | $param_num++; |
||
335 | } // End foreach(). |
||
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(); |
||
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; |
||
0 ignored issues
–
show
|
|||
770 | $this->columns = array(); |
||
771 | $this->comment = false; |
||
772 | $this->defining_sql = false; |
||
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(). |
||
1182 | } // End foreach(). |
||
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 |
Our type inference engine has found an assignment to a property that is incompatible with the declared type of that property.
Either this assignment is in error or the assigned type should be added to the documentation/type hint for that property..