Completed
Push — master ( 43d4c6...de6a17 )
by Sam
02:59
created

Table::join_on()   B

Complexity

Conditions 3
Paths 3

Size

Total Lines 26
Code Lines 21

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 26
rs 8.8571
c 0
b 0
f 0
cc 3
eloc 21
nc 3
nop 1
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 ) {
0 ignored issues
show
Duplication introduced by Sam Wilson
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
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 ) {
0 ignored issues
show
Duplication introduced by Sam Wilson
This code seems to be duplicated across your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
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->page() > 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
	 *
493
	 * @param integer $new_page_num The new page number.
494
	 */
495
	public function set_current_page_num( $new_page_num ) {
496
		$this->current_page_num = $new_page_num;
497
	}
498
499
	/**
500
	 * Get the number of records that are included in each page.
501
	 *
502
	 * @return integer
503
	 */
504
	public function get_records_per_page() {
505
		return $this->records_per_page;
506
	}
507
508
	/**
509
	 * Set the number of records that will be fetched per page.
510
	 *
511
	 * @param integer $records_per_page The new number of records per page.
512
	 */
513
	public function set_records_per_page( $records_per_page ) {
514
		$this->records_per_page = $records_per_page;
515
	}
516
517
	/**
518
	 * Get the saved SQL and its parameters.
519
	 *
520
	 * @return string[]
521
	 */
522
	public function get_saved_query() {
523
		return array(
524
			'sql' => $this->saved_sql,
525
			'parameters' => $this->saved_parameters,
526
		);
527
	}
528
529
	/**
530
	 * Get the SQL for SELECTing all columns in this table.
531
	 *
532
	 * @return string
533
	 */
534
	private function columns_sql_select() {
535
		$select = array();
536
		$table_name = $this->get_name();
537
		foreach ( $this->get_columns() as $col_name => $col ) {
538
			if ( 'point' === $col->get_type() ) {
539
				$select[] = "AsText(`$table_name`.`$col_name`) AS `$col_name`";
540
			} else {
541
				$select[] = "`$table_name`.`$col_name`";
542
			}
543
		}
544
		return join( ', ', $select );
545
	}
546
547
	/**
548
	 * Get a single record as an associative array.
549
	 *
550
	 * @param string $pk_val The value of the PK of the record to get.
551
	 * @return Record|false The record object, or false if it wasn't found.
552
	 */
553
	public function get_record( $pk_val ) {
554
		$pk_column = $this->get_pk_column();
555
		if ( ! $pk_column ) {
556
			return false;
557
		}
558
		$sql = "SELECT " . $this->columns_sql_select() . " "
559
				. "FROM `" . $this->get_name() . "` "
560
				. "WHERE `" . $pk_column->get_name() . "` = %s "
561
				. "LIMIT 1";
562
		$params = array( $pk_val );
563
		$stmt = $this->database->get_wpdb()->prepare( $sql, $params );
564
		$row = $this->database->get_wpdb()->get_row( $stmt );
565
		return ( $row ) ? new Record( $this, $row ) : false;
566
	}
567
568
	/**
569
	 * Get a bare record with only default values.
570
	 *
571
	 * @return Record
572
	 */
573
	public function get_default_record() {
574
		$row = array();
575
		foreach ( $this->get_columns() as $col ) {
576
			$row[ $col->get_name() ] = $col->get_default();
577
		}
578
		$record = new Record( $this, $row );
579
		return $record;
580
	}
581
582
	/**
583
	 * Whether this table should have changes recorded or not.
584
	 * The change-tracking tables themselves do not.
585
	 *
586
	 * @return boolean
587
	 */
588
	public function has_changes_recorded() {
589
		return ! in_array( $this->get_name(), ChangeTracker::table_names(), true );
590
	}
591
592
	/**
593
	 * Get this table's name.
594
	 *
595
	 * @return string The name of this table.
596
	 */
597
	public function get_name() {
598
		return $this->name;
599
	}
600
601
	/**
602
	 * Whether this is a base table or a view.
603
	 *
604
	 * @return string Either `Table::TYPE_TABLE` or `Table::TYPE_VIEW`.
605
	 */
606
	public function get_type() {
607
		if ( ! $this->type ) {
608
			$this->get_defining_sql();
609
		}
610
		return $this->type;
611
	}
612
613
	/**
614
	 * Whether this table is a table (as opposed to a view).
615
	 *
616
	 * @return boolean
617
	 */
618
	public function is_table() {
619
		return $this->get_type() === self::TYPE_TABLE;
620
	}
621
622
	/**
623
	 * Whether this table is a view.
624
	 *
625
	 * @return boolean
626
	 */
627
	public function is_view() {
628
		return $this->get_type() === self::TYPE_VIEW;
629
	}
630
631
	/**
632
	 * Whether this view is updatable. Always true for base tables. Currently
633
	 * always false for all views.
634
	 *
635
	 * @link https://dev.mysql.com/doc/refman/5.6/en/view-updatability.html
636
	 */
637
	public function is_updatable() {
638
		if ( $this->is_table() ) {
639
			return true;
640
		}
641
		return false;
642
	}
643
644
	/**
645
	 * Get this table's title. This is the title-cased name, if not otherwise
646
	 * defined.
647
	 *
648
	 * @return string The title
649
	 */
650
	public function get_title() {
651
		return \WordPress\Tabulate\Text::titlecase( $this->get_name() );
652
	}
653
654
	/**
655
	 * Get a list of permitted operators.
656
	 *
657
	 * @return string[] List of operators.
658
	 */
659
	public function get_operators() {
660
		return $this->operators;
661
	}
662
663
	/**
664
	 * Get a count of the number of pages in the currently filtered record set.
665
	 *
666
	 * @return integer The page count.
667
	 */
668
	public function get_page_count() {
669
		return ceil( $this->count_records() / $this->get_records_per_page() );
670
	}
671
672
	/**
673
	 * Get or set the current page.
674
	 *
675
	 * @param integer $page The page number.
676
	 * @return integer Current page
677
	 */
678
	public function page( $page = false ) {
679
		if ( false !== $page ) {
680
			$this->current_page_num = $page;
681
		} else {
682
			return $this->current_page_num;
683
		}
684
	}
685
686
	/**
687
	 * Get the number of rows in the current filtered set.
688
	 *
689
	 * @return integer
690
	 */
691
	public function count_records() {
692
		return $this->record_counter->get_count();
693
	}
694
695
	/**
696
	 * Export this table's data (with filters applied) to a file on disk.
697
	 *
698
	 * @return string Full filesystem path to resulting temporary file.
699
	 */
700
	public function export() {
701
702
		$columns = array();
703
		$column_headers = array();
704
		$join_clause = '';
705
		foreach ( $this->columns as $col_name => $col ) {
706
			if ( $col->is_foreign_key() ) {
707
				$col_join = $this->join_on( $col );
708
				$column_name = $col_join['column_alias'];
709
				$join_clause .= $col_join['join_clause'];
710
			} elseif ( 'point' === $col->get_type() ) {
711
				$columns[] = "IF(`$this->name`.`$col_name` IS NOT NULL, AsText(`$this->name`.`$col_name`), '') AS `$col_name`";
712
			} else {
713
				$column_name = "`$this->name`.`$col_name`";
714
			}
715
			if ( 'point' !== $col->get_type() && isset( $column_name ) ) {
716
				$columns[] = "REPLACE(IFNULL($column_name, ''),CONCAT(CHAR(13),CHAR(10)),CHAR(10))"; // 13 = \r and 10 = \n
717
			}
718
			$column_headers[] = $col->get_title();
719
		}
720
721
		// Build basic SELECT statement.
722
		$sql = 'SELECT ' . join( ',', $columns )
723
			. ' FROM `' . $this->get_name() . '` ' . $join_clause;
724
725
		$params = $this->apply_filters( $sql );
726
727
		$filename = $this->get_database()->get_tmp_dir() . uniqid( 'tabulate_' ) . '.csv';
728
		if ( DIRECTORY_SEPARATOR === '\\' ) {
729
			// Clean Windows slashes, for MySQL's benefit.
730
			$filename = str_replace( '\\', '/', $filename );
731
		}
732
		// Clear out any old copy.
733
		if ( file_exists( $filename ) ) {
734
			unlink( $filename );
735
		}
736
		// Build the final SQL, appending the column headers in a UNION.
737
		$sql = 'SELECT "' . join( '", "', $column_headers ) . '"'
738
			. ' UNION ' . $sql
739
			. ' INTO OUTFILE "' . $filename . '" '
740
			. ' FIELDS TERMINATED BY ","'
741
			. ' ENCLOSED BY \'"\''
742
			. ' ESCAPED BY \'"\''
743
			. ' LINES TERMINATED BY "\r\n"';
744
		// Execute the SQL (hiding errors for now).
745
		$wpdb = $this->database->get_wpdb();
746
		if ( ! empty( $params ) ) {
747
			$sql = $wpdb->prepare( $sql, $params );
748
		}
749
		$wpdb->hide_errors();
750
		$wpdb->query( $sql );
751
		// Make sure it exported.
752
		if ( ! file_exists( $filename ) ) {
753
			// Note that this error message is quoted in the documentation.
754
			$msg = "Unable to create temporary export file:<br /><code>$filename</code>";
755
			Exception::wp_die( $msg, 'Export failed', $wpdb->last_error, $sql ); // WPCS: XSS OK.
756
		}
757
		$wpdb->show_errors();
758
		// Give the filename back to the controller, to send to the client.
759
		return $filename;
760
	}
761
762
	/**
763
	 * Get one of this table's columns.
764
	 *
765
	 * @param string $name The column name.
766
	 * @return \WordPress\Tabulate\DB\Column|false The column, or false if it's not found.
767
	 */
768
	public function get_column( $name ) {
769
		$columns = $this->get_columns();
770
		return ( isset( $columns[ $name ] ) ) ? $columns[ $name ] : false;
771
	}
772
773
	/**
774
	 * Reset the column, comment, and defining SQL of this table. This forces
775
	 * them to be re-read from the databaes when next required.
776
	 */
777
	public function reset() {
778
		$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...
779
		$this->columns = array();
780
		$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...
781
		$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...
782
		$this->record_counter->clear();
783
	}
784
785
	/**
786
	 * Get a list of this table's columns, optionally constrained by their type.
787
	 *
788
	 * @param string $type Only return columns of this type.
789
	 * @return \WordPress\Tabulate\DB\Column[] Array of this table's columns, keyed by the column names.
790
	 */
791
	public function get_columns( $type = null ) {
792
		if ( empty( $this->columns ) ) {
793
			$this->columns = array();
794
			$sql = "SHOW FULL COLUMNS FROM `" . $this->get_name() . "`";
795
			$columns = $this->get_database()->get_wpdb()->get_results( $sql, ARRAY_A );
796
			foreach ( $columns as $column_info ) {
797
				$column = new Column( $this, $column_info );
798
				$this->columns[ $column->get_name() ] = $column;
799
			}
800
		}
801
		if ( is_null( $type ) ) {
802
			return $this->columns;
803
		}
804
		$out = array();
805
		foreach ( $this->get_columns() as $col ) {
806
			if ( $col->get_type() === $type ) {
807
				$out[ $col->get_name() ] = $col;
808
			}
809
		}
810
		return $out;
811
	}
812
813
	/**
814
	 * Add a new column to this table.
815
	 *
816
	 * @param string  $name Table name.
817
	 * @param string  $xtype_name Which 'xtype' to use.
818
	 * @param integer $size The length of the column.
819
	 * @param boolean $nullable Whether null values are allowed.
820
	 * @param string  $default The default value.
821
	 * @param boolean $auto_increment Whether it shall be an auto-inrementing column.
822
	 * @param boolean $unique Whether a unique constraint shall be applied.
823
	 * @param string  $comment The table comment.
824
	 * @param Table   $target_table For 'cross-reference' types, the name of the foreign table.
825
	 * @param string  $after The name of the column after which this one shall be added.
826
	 * @throws Exception If the column already exists or is unable to be added.
827
	 */
828
	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 ) {
829
		// Can it be done?
830
		if ( ! current_user_can( 'promote_users' ) ) {
831
			throw new Exception( 'Only administrators are allowed to add columns to tables' );
832
		}
833
		if ( $this->get_column( $name ) ) {
834
			throw new Exception( "Column '$name' already exists on table '" . $this->get_name() . "'" );
835
		}
836
837
		// Build SQL statement.
838
		$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 828 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...
839
840
		$sql = "ALTER TABLE `" . $this->get_name() . "` ADD COLUMN $col_def";
841
842
		// Execute the SQL and reset the cache.
843
		$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...
844
		if ( false === $query ) {
845
			throw new Exception( "Unable to add column '$name'. SQL was: <code>$sql</code>" );
846
		}
847
		$this->reset();
848
	}
849
850
	/**
851
	 * Get the table comment text; for views, this returns '(View)'.
852
	 *
853
	 * @return string
854
	 */
855
	public function get_comment() {
856
		if ( ! $this->comment ) {
857
			$sql = $this->get_defining_sql();
858
			$comment_pattern = '/.*\)(?:.*COMMENT[\w=]*\'(.*)\')?/si';
859
			preg_match( $comment_pattern, $sql, $matches );
860
			$this->comment = ( isset( $matches[1] ) ) ? $matches[1] : '';
861
			$this->comment = str_replace( "''", "'", $this->comment );
862
		}
863
		if ( empty( $this->comment ) && $this->is_view() ) {
864
			$this->comment = '(View)';
865
		}
866
		return $this->comment;
867
	}
868
869
	/**
870
	 * Get a list of all the unique columns in this table.
871
	 *
872
	 * @return \WordPress\Tabulate\DB\Column[]
873
	 */
874
	public function get_unique_columns() {
875
		$cols = array();
876
		foreach ( $this->get_columns() as $column ) {
877
			if ( $column->is_unique() ) {
878
				$cols[] = $column;
879
			}
880
		}
881
		return $cols;
882
	}
883
884
	/**
885
	 * Get the first unique-keyed column.
886
	 * If there is no unique non-PK column then just use the PK.
887
	 *
888
	 * @return \WordPress\Tabulate\DB\Column
889
	 */
890
	public function get_title_column() {
891
		// Try to get the first non-PK unique key.
892
		foreach ( $this->get_columns() as $column ) {
893
			if ( $column->is_unique() && ! $column->is_primary_key() ) {
894
				return $column;
895
			}
896
		}
897
		// But if that fails, just use the primary key.
898
		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 898 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...
899
	}
900
901
	/**
902
	 * Get the SQL statement used to create this table, as given by the 'SHOW
903
	 * CREATE TABLE' command.
904
	 *
905
	 * @return string The SQL statement used to create this table.
906
	 * @throws Exception If the table or view is not found.
907
	 */
908
	public function get_defining_sql() {
909
		if ( empty( $this->defining_sql ) ) {
910
			$defining_sql = $this->database->get_wpdb()->get_row( "SHOW CREATE TABLE `$this->name`" );
911
			if ( isset( $defining_sql->{'Create Table'} ) ) {
912
				$defining_sql = $defining_sql->{'Create Table'};
913
				$this->type = self::TYPE_TABLE;
914
			} elseif ( isset( $defining_sql->{'Create View'} ) ) {
915
				$defining_sql = $defining_sql->{'Create View'};
916
				$this->type = self::TYPE_VIEW;
917
			} else {
918
				throw new Exception( 'Table or view not found: ' . $this->name );
919
			}
920
			$this->defining_sql = $defining_sql;
921
		}
922
		return $this->defining_sql;
923
	}
924
925
	/**
926
	 * Get this table's Primary Key column.
927
	 *
928
	 * @return \WordPress\Tabulate\DB\Column|false The PK column or false if there isn't one.
929
	 */
930
	public function get_pk_column() {
931
		foreach ( $this->get_columns() as $column ) {
932
			if ( $column->is_primary_key() ) {
933
				return $column;
934
			}
935
		}
936
		return false;
937
	}
938
939
	/**
940
	 * Get a list of this table's foreign keys and the tables to which they refer.
941
	 * This does *not* take into account a user's permissions (i.e. the
942
	 * name of a table which the user is not allowed to read may be returned).
943
	 *
944
	 * @param boolean $instantiate Whether to instantiate the Table objects (or just return their names).
945
	 * @return string[]|Table[] The list of <code>column_name => table_name|Table</code> pairs.
946
	 */
947
	public function get_referenced_tables( $instantiate = false ) {
948
949
		// Extract the FK info from the CREATE TABLE statement.
950
		if ( ! is_array( $this->referenced_tables ) ) {
951
			$this->referenced_table_names = array();
952
			$defining_sql = $this->get_defining_sql();
953
			$fk_pattern = '|FOREIGN KEY \(`(.*?)`\) REFERENCES `(.*?)`|';
954
			preg_match_all( $fk_pattern, $defining_sql, $matches );
955
			if ( isset( $matches[1] ) && count( $matches[1] ) > 0 ) {
956
				foreach ( array_combine( $matches[1], $matches[2] ) as $col_name => $tab_name ) {
957
					$this->referenced_table_names[ $col_name ] = $tab_name;
958
				}
959
			}
960
		}
961
962
		if ( $instantiate ) {
963
			$this->referenced_tables = array();
964
			foreach ( $this->referenced_table_names as $ref_col => $ref_tab ) {
965
				$this->referenced_tables[ $ref_col ] = new Table( $this->get_database(), $ref_tab );
966
			}
967
		}
968
969
		return $instantiate ? $this->referenced_tables : $this->referenced_table_names;
970
	}
971
972
	/**
973
	 * Get a list of tables with foreign keys referring here, and which of their columns are the FKs.
974
	 *
975
	 * @return array With keys 'table' and 'column'.
976
	 */
977
	public function get_referencing_tables() {
978
		$out = array();
979
		// For all tables in the Database...
980
		foreach ( $this->get_database()->get_tables() as $table ) {
981
			// ...get a list of the tables they reference.
982
			$foreign_tables = $table->get_referenced_tables();
983
			foreach ( $foreign_tables as $foreign_column => $referenced_table_name ) {
984
				// If this table is a referenced table, collect the table from which it's referenced.
985
				if ( $referenced_table_name === $this->get_name() ) {
986
					$out[ $table->get_name() . '.' . $foreign_column ] = array(
987
						'table' => $table,
988
						'column' => $foreign_column,
989
					);
990
				}
991
			}
992
		}
993
		return $out;
994
	}
995
996
	/**
997
	 * Get a list of the names of the foreign keys in this table.
998
	 *
999
	 * @return string[] Names of foreign key columns in this table.
1000
	 */
1001
	public function get_foreign_key_names() {
1002
		return array_keys( $this->get_referenced_tables( false ) );
1003
	}
1004
1005
	/**
1006
	 * Get the database to which this table belongs.
1007
	 *
1008
	 * @return \WordPress\Tabulate\DB\Database The database object.
1009
	 */
1010
	public function get_database() {
1011
		return $this->database;
1012
	}
1013
1014
	/**
1015
	 * Get a string representation of this table; a succinct summary of its
1016
	 * columns and their types, keys, etc.
1017
	 *
1018
	 * @return string A summary of this table.
1019
	 */
1020
	public function __toString() {
1021
		$col_count = count( $this->get_columns() );
1022
		$out = "\n";
1023
		$out .= '+-----------------------------------------+' . "\n";
1024
		$out .= '| ' . $this->get_name() . ' (' . $col_count . ' columns)' . "\n";
1025
		$out .= '+-----------------------------------------+' . "\n";
1026
		foreach ( $this->get_columns() as $column ) {
1027
			$out .= "| $column \n";
1028
		}
1029
		$out .= '+-----------------------------------------+' . "\n\n";
1030
		return $out;
1031
	}
1032
1033
	/**
1034
	 * Get an XML representation of the structure of this table.
1035
	 *
1036
	 * @return DOMElement The XML 'table' node.
1037
	 */
1038
	public function to_xml() {
1039
		$dom = new DOMDocument( '1.0', 'UTF-8' );
1040
		$table = $dom->createElement( 'table' );
1041
		$dom->appendChild( $table );
1042
		$name = $dom->createElement( 'name' );
1043
		$name->appendChild( $dom->createTextNode( $this->name ) );
1044
		$table->appendChild( $name );
1045
		foreach ( $this->get_columns() as $column ) {
1046
			$table->appendChild( $dom->importNode( $column->toXml(), true ) );
1047
		}
1048
		return $table;
1049
	}
1050
1051
	/**
1052
	 * Get a JSON representation of the structure of this table.
1053
	 *
1054
	 * @return string
1055
	 */
1056
	public function to_json() {
1057
		$json = new Services_JSON();
1058
		$metadata = array();
1059
		foreach ( $this->get_columns() as $column ) {
1060
			$metadata[] = array(
1061
				'name' => $column->get_name(),
1062
			);
1063
		}
1064
		return $json->encode( $metadata );
1065
	}
1066
1067
	/**
1068
	 * Remove all filters.
1069
	 *
1070
	 * @return void
1071
	 */
1072
	public function reset_filters() {
1073
		$this->filters = array();
1074
	}
1075
1076
	/**
1077
	 * Delete a record and its associated change-tracker records.
1078
	 *
1079
	 * @param string $pk_value The value of the primary key of the record to delete.
1080
	 * @return void
1081
	 * @throws Exception When the user doesn't have permission, or any error occurs deleting the record.
1082
	 */
1083
	public function delete_record( $pk_value ) {
1084
		// Check permission.
1085
		if ( ! Grants::current_user_can( Grants::DELETE, $this->get_name() ) ) {
1086
			throw new Exception( 'You do not have permission to delete data from this table.' );