Table::get_records_per_page()   A
last analyzed

Complexity

Conditions 1
Paths 1

Size

Total Lines 3

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 3
rs 10
c 0
b 0
f 0
cc 1
nc 1
nop 0
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 );
0 ignored issues
show
Security Bug introduced by Sam Wilson
It seems like $column defined by isset($filter['column'])...ilter['column'] : false on line 234 can also be of type false; however, WordPress\Tabulate\DB\Table::add_filter() does only seem to accept string|object<WordPress\Tabulate\DB\Column>, did you maybe forget to handle an error condition?

This check looks for type mismatches where the missing type is false. This is usually indicative of an error condtion.

Consider the follow example

<?php

function getDate($date)
{
    if ($date !== null) {
        return new DateTime($date);
    }

    return false;
}

This function either returns a new DateTime object or false, if there was an error. This is a typical pattern in PHP programming to show that an error has occurred without raising an exception. The calling code should check for this returned false before passing on the value to another function or method that may not be able to handle a false.

Loading history...
Security Bug introduced by Sam Wilson
It seems like $operator defined by isset($filter['operator'...ter['operator'] : false on line 235 can also be of type false; however, WordPress\Tabulate\DB\Table::add_filter() does only seem to accept string, did you maybe forget to handle an error condition?

This check looks for type mismatches where the missing type is false. This is usually indicative of an error condtion.

Consider the follow example

<?php

function getDate($date)
{
    if ($date !== null) {
        return new DateTime($date);
    }

    return false;
}

This function either returns a new DateTime object or false, if there was an error. This is a typical pattern in PHP programming to show that an error has occurred without raising an exception. The calling code should check for this returned false before passing on the value to another function or method that may not be able to handle a false.

Loading history...
Security Bug introduced by Sam Wilson
It seems like $value defined by isset($filter['value']) ...filter['value'] : false on line 236 can also be of type false; however, WordPress\Tabulate\DB\Table::add_filter() does only seem to accept string, did you maybe forget to handle an error condition?

This check looks for type mismatches where the missing type is false. This is usually indicative of an error condtion.

Consider the follow example

<?php

function getDate($date)
{
    if ($date !== null) {
        return new DateTime($date);
    }

    return false;
}

This function either returns a new DateTime object or false, if there was an error. This is a typical pattern in PHP programming to show that an error has occurred without raising an exception. The calling code should check for this returned false before passing on the value to another function or method that may not be able to handle a false.

Loading history...
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
Unused Code Comprehensibility introduced by Sam Wilson
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...
333
334
			$param_num++;
335
		} // End foreach().
0 ignored issues
show
Unused Code Comprehensibility introduced by Sam Wilson
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
Documentation Bug introduced by Sam Wilson
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;
0 ignored issues
show
Documentation Bug introduced by Sam Wilson
It seems like false of type false is incompatible with the declared type array<integer,object<Wor...ess\Tabulate\DB\Table>> of property $referenced_tables.

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..

Loading history...
770
		$this->columns = array();
771
		$this->comment = false;
0 ignored issues
show
Documentation Bug introduced by Sam Wilson
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
Documentation Bug introduced by Sam Wilson
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 );
0 ignored issues
show
Bug introduced by Sam Wilson
It seems like $nullable defined by parameter $nullable on line 819 can also be of type null; however, WordPress\Tabulate\DB\Co...get_column_definition() does only seem to accept boolean, maybe add an additional type check?

This check looks at variables that have been passed in as parameters and are passed out again to other methods.

If the outgoing method call has stricter type requirements than the method itself, an issue is raised.

An additional type check may prevent trouble.

Loading history...
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 );
0 ignored issues
show
Bug introduced by Sam Wilson
Are you sure the assignment to $query is correct as $this->get_database()->query($sql) (which targets WordPress\Tabulate\DB\Database::query()) seems to always return null.

This check looks for function or method calls that always return null and whose return value is assigned to a variable.

class A
{
    function getObject()
    {
        return null;
    }

}

$a = new A();
$object = $a->getObject();

The method getObject() can return nothing but null, so it makes no sense to assign that value to a variable.

The reason is most likely that a function or method is imcomplete or has been reduced for debug purposes.

Loading history...
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();
0 ignored issues
show
Comprehensibility Best Practice introduced by Sam Wilson
The expression $this->get_pk_column(); of type WordPress\Tabulate\DB\Column|false adds false to the return on line 889 which is incompatible with the return type documented by WordPress\Tabulate\DB\Table::get_title_column of type WordPress\Tabulate\DB\Column. It seems like you forgot to handle an error condition.
Loading history...
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-&g