wp-data-access / WPDataAccess / Data_Dictionary / WPDA_List_Columns.php

WPDA_List_Columns.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.5.41, at WPDataAccess/Data_Dictionary/WPDA_List_Columns.php

484 lines 12.9 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
5	*
6	* @package WPDataAccess\Data_Dictionary
7	*/
8
9	namespace WPDataAccess\Data_Dictionary {
10
11	use WPDataAccess\Connection\WPDADB;
12	use WPDataAccess\Plugin_Table_Models\WPDA_Table_Settings_Model;
13	use WPDataAccess\WPDA;
14
15	/**
16	* Class WPDA_List_Columns
17	*
18	* @author Peter Schulz
19	* @since 1.0.0
20	*/
21	class WPDA_List_Columns {
22
23	/**
24	* Database connection
25	*
26	* @var null
27	*/
28	protected $wpdadb = null;
29
30	/**
31	* Database schema name
32	*
33	* @var string
34	*/
35	protected $schema_name;
36
37	/**
38	* Database table name
39	*
40	* @var string
41	*/
42	protected $table_name;
43
44	/**
45	* Columns of $this->table_name
46	*
47	* @var array
48	*/
49	protected $table_columns = array();
50
51	/**
52	* Columns of $this->searchable_table_columns
53	*
54	* @var array
55	*/
56	protected $searchable_table_columns = array();
57
58	/**
59	* Column data type sorted on column name
60	*
61	* @var array
62	*/
63	protected $table_column_data_type = array();
64
65	/**
66	* Column type sorted on column name
67	*
68	* @var array
69	*/
70	protected $table_column_type = array();
71
72	/**
73	* Primary key columns of $this->table_name
74	*
75	* @var array
76	*/
77	protected $table_primary_key = array();
78
79	/**
80	* Primary key columns of $this->table_name (named)
81	*
82	* @var array
83	*/
84	protected $table_primary_key_check = array();
85
86	/**
87	* Auto increment column name of $this->table_name or false
88	*
89	* @var bool\|string
90	*/
91	protected $auto_increment_column_name = false;
92
93	/**
94	* Column headers for $this->table_name
95	*
96	* @var array
97	*/
98	protected $table_column_headers = array();
99
100	/**
101	* Table settings as define in Data Explorer
102	*
103	* @var null\|\|Object
104	*/
105	protected $table_settings = null;
106
107	/**
108	* WPDA_List_Columns constructor
109	*
110	* @param string $schema_name Database schema name.
111	* @param string $table_name Database table name.
112	*
113	* @since 1.0.0
114	*/
115	public function __construct( $schema_name, $table_name ) {
116	// Set schema and table name.
117	if ( '' === $schema_name ) {
118	global $wpdb;
119	$this->schema_name = $wpdb->dbname;
120	} else {
121	$this->schema_name = $schema_name;
122	}
123
124	$this->wpdadb = WPDADB::get_db_connection( $this->schema_name );
125	if ( null === $this->wpdadb ) {
126	if ( is_admin() ) {
127	wp_die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name) ) );
128	} else {
129	die( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name ) ) );
130	}
131	}
132
133	if ( '' !== $table_name ) {
134	$this->table_name = $table_name;
135
136	// Get table settings
137	$table_settings_db = WPDA_Table_Settings_Model::query( $this->table_name, $this->schema_name );
138	if ( isset( $table_settings_db[0]['wpda_table_settings'] ) ) {
139	$this->table_settings = json_decode( $table_settings_db[0]['wpda_table_settings'] );
140	}
141
142	// Get dictionary info
143	$this->set_table_columns();
144	$this->set_table_primary_key();
145	$this->set_table_column_headers();
146	}
147	}
148
149	/**
150	* Get column label for list table
151	*
152	* Returns the label of a column according to a pre defined format. Call must contain column name.
153	* Column must be in $this->table_name.
154	*
155	* @param string $column_name Column name as defined in the data dictionary.
156	*
157	* @return string Label for $column_name.
158	* @since 1.0.0
159	*/
160	public function get_column_label( $column_name ) {
161
162	if ( isset( $this->table_settings->list_labels->$column_name ) ) {
163	return $this->table_settings->list_labels->$column_name;
164	} else {
165	return $this->get_default_column_label( $column_name );
166	}
167
168	}
169
170	public function get_table_header_labels() {
171	if ( isset( $this->table_settings->list_labels ) ) {
172	return $this->table_settings->list_labels;
173	} else {
174	if ( isset( $this->table_column_headers ) ) {
175	return $this->table_column_headers;
176	} else {
177	return null;
178	}
179	}
180	}
181
182	/**
183	* Return column data type
184	*
185	* @param $column_name Database column name
186	*
187	* @return string\|null
188	* @since 2.7.2
189	*/
190	public function get_column_data_type( $column_name ) {
191	return is_string( $column_name ) && isset( $this->table_column_data_type[ $column_name ] ) ?
192	$this->table_column_data_type[ $column_name ] : null;
193	}
194
195	/**
196	* Return column type
197	*
198	* @param $column_name Database column name
199	*
200	* @return string\|null
201	* @since 2.7.2
202	*/
203	public function get_column_type( $column_name ) {
204	return isset( $this->table_column_type[ (string) $column_name ] ) ? $this->table_column_type[ (string) $column_name ] : null;
205	}
206
207	/**
208	* Get default column label
209	*
210	* Returns the default label of a column according to a pre defined format. Call must contain column name.
211	* Column must be in $this->table_name.
212	*
213	* @param string $column_name Column name as defined in the data dictionary.
214	*
215	* @return string Default label for $column_name.
216	* @since 1.0.0
217	*/
218	public function get_default_column_label( $column_name ) {
219
220	return ucwords( str_replace( '_', ' ', $column_name ) );
221
222	}
223
224	/**
225	* Check if column is part of primary key
226	*
227	* @param string $column_name Column name as defined in the data dictionary.
228	*
229	* @return bool TRUE = column is part of primary key, FALSE = column is not part of primary key.
230	* @since 1.0.0
231	*/
232	public function is_primary_key_column( $column_name ) {
233
234	return ( isset( $this->table_primary_key_check[ $column_name ] ) );
235
236	}
237
238	/**
239	* Get columns
240	*
241	* @return array Column of $this->table_name.
242	* @since 1.0.0
243	*/
244	public function get_table_columns() {
245
246	return $this->table_columns;
247
248	}
249
250	/**
251	* Get searchable columns
252	*
253	* @return array Column of $this->table_name.
254	* @since 1.0.0
255	*/
256	public function get_searchable_table_columns() {
257
258	return $this->searchable_table_columns;
259
260	}
261
262	/**
263	* Set table columns
264	*
265	* Column info is taken from the MySQL data dictionary. For each column in $this->table_name the following
266	* column info is stored:
267	* + Column name
268	* + Data type (MySQL data type)
269	* + Extra (needed to find auto increment columns)
270	* + Column type (needed for columns with data type enum: column type holds allowed values)
271	* + Null values allowed?
272	*
273	* Since MariaDB 10.2.7 and higher handles default values different than other DBMSs we have to take care of
274	* the quotes it places at the beginning and the end. This involves the implication that users cannot use
275	* default values that start and end with a single quote. Which seems common sense for me.
276	*
277	* @since 1.0.0
278	*/
279	protected function set_table_columns() {
280	$query = $this->wpdadb->prepare(
281	'
282	SELECT column_name AS column_name,
283	data_type AS data_type,
284	extra AS extra,
285	column_type AS column_type,
286	is_nullable AS is_nullable,
287	IF(LEFT(column_default,1)=\'\'\'\' AND RIGHT(column_default,1)=\'\'\'\', SUBSTR(column_default,2,LENGTH(column_default)-2), column_default) AS column_default,
288	character_maximum_length AS character_maximum_length,
289	numeric_scale AS numeric_scale,
290	numeric_precision AS numeric_precision
291	FROM information_schema.columns
292	WHERE table_schema = %s
293	AND table_name = %s
294	ORDER BY ordinal_position
295	',
296	array(
297	$this->wpdadb->dbname,
298	$this->table_name,
299	)
300	);
301
302	$this->table_columns = $this->wpdadb->get_results( $query, 'ARRAY_A' ); // phpcs:ignore Standard.Category.SniffName.ErrorCode
303	$this->searchable_table_columns = $this->table_columns; // Contains all columns and is not modified
304
305	foreach ( $this->table_columns as $column ) {
306	if ( isset( $column['column_name'] ) && isset( $column['data_type'] ) ) {
307	$this->table_column_data_type[ $column['column_name'] ] = $column['data_type'];
308	}
309
310	if ( isset( $column['column_name'] ) && isset( $column['column_type'] ) ) {
311	$this->table_column_type[ $column['column_name'] ] = $column['column_type'];
312	}
313	}
314
315	}
316
317	/**
318	* Get primary key columns
319	*
320	* @return array Primary key columns of $this->table_name.
321	* @since 1.0.0
322	*/
323	public function get_table_primary_key() {
324	return $this->table_primary_key;
325	}
326
327	/**
328	* Set primary key columns
329	*
330	* Primary key columns are taken from the MySQL data dictionary.
331	*
332	* Newer versions of MariaDB no longer seem to support JOIN USING. Rewritten to old school join. MySQL supports
333	* both types of joins.
334	*
335	* @since 1.0.0
336	*/
337	protected function set_table_primary_key() {
338	$result = $this->get_table_unique_indexes();
339
340	if ( 0 < $this->wpdadb->num_rows ) {
341	// Check if there is a primary key
342	$has_primary_key = false;
343	foreach ( $result as $row ) {
344	if ( 'PRIMARY' === $row['Key_name'] ) {
345	$this->table_primary_key[] = $row['Column_name'];
346	$this->table_primary_key_check[ $row['Column_name'] ] = true;
347
348	foreach ( $this->table_columns as $table_column ) {
349	if ( $table_column['column_name'] === $row['Column_name'] &&
350	'auto_increment' === $table_column['extra']
351	) {
352	// Save auto_increment column name.
353	$this->auto_increment_column_name = $row['Column_name'];
354	}
355	}
356
357	$has_primary_key = true;
358	}
359	}
360
361	if ( ! $has_primary_key ) {
362	// Use non unique index as an alternative
363	$key_name = $result[0]['Key_name'];
364	$this->table_primary_key[] = $result[0]['Column_name'];
365	$this->table_primary_key_check[ $key_name ] = true;
366	$i = 1;
367	while ( $i < count( $result ) ) {//phpcs:ignore - 8.1 proof
368	if ( $key_name === $result[ $i ]['Key_name'] ) {
369	$this->table_primary_key[] = $result[ $i ]['Column_name'];
370	$this->table_primary_key_check[ $result[ $i ]['Column_name'] ] = true;
371	}
372	$i ++;
373	}
374	}
375	}
376	}
377
378	protected function get_table_unique_indexes() {
379	$suppress = $this->wpdadb->suppress_errors( true );
380	$query =
381	sprintf(
382	'SHOW INDEX FROM `%s`.`%s` WHERE non_unique = 0',
383	$this->wpdadb->dbname,
384	str_replace( '`', '', (string) $this->table_name )
385	);
386	$result = $this->wpdadb->get_results( $query, 'ARRAY_A' );
387	$this->wpdadb->suppress_errors( $suppress );
388
389	return $result;
390	}
391
392	public function get_table_alternative_keys() {
393	$result = $this->get_table_unique_indexes();
394	$return = array();
395
396	if ( 0 < $this->wpdadb->num_rows ) {
397	$index_name = '';
398	$index = array();
399	foreach ( $result as $row ) {
400	if ( $index_name !== $row['Key_name'] ) {
401	if ( '' !== $index_name ) {
402	array_push( $return, $row['Column_name'] );//phpcs:ignore - 8.1 proof
403	$index = array();
404	}
405	$index_name = $row['Key_name'];
406	}
407	array_push( $index, $row['Column_name'] );//phpcs:ignore - 8.1 proof
408	}
409	array_push( $return, $index );//phpcs:ignore - 8.1 proof
410	}
411
412	return $return;
413	}
414
415	/**
416	* Get column headers
417	*
418	* @return array
419	* @since 1.0.0
420	*/
421	public function get_table_column_headers() {
422
423	return $this->table_column_headers;
424
425	}
426
427	/**
428	* Set column headers (= column labels in data entry forms)
429	*
430	* For now column headers are defined equal to their names. If a column is part of the primary key, this is
431	* reflected in the column header.
432	*
433	* @since 1.0.0
434	*/
435	protected function set_table_column_headers() {
436
437	if ( ! isset( $this->table_columns ) ) {
438	wp_die( __( 'ERROR: Wrong arguments [no columns found]', 'wp-data-access' ) );
439	}
440
441	$primary_nr = 0;
442	$this->table_column_headers = array();
443	foreach ( $this->table_columns as $key => $value ) {
444
445	if ( isset( $this->table_settings->form_labels ) ) {
446	if ( isset( $this->table_settings->form_labels->{$value['column_name']} ) ) {
447	$label = $this->table_settings->form_labels->{$value['column_name']};
448	} else {
449	$label = $this->get_default_column_label( $value['column_name'] );
450	}
451	} else {
452	$label = $this->get_default_column_label( $value['column_name'] );
453
454	if ( $this->is_primary_key_column( $value['column_name'] ) ) {
455	$key_text = __( 'key', 'wp-data-access' );
456	if ( count( $this->table_primary_key ) > 1 ) {//phpcs:ignore - 8.1 proof
457	$label .= " ($key_text #" . ( ++ $primary_nr ) . ')';
458	} else {
459	$label .= " ($key_text)";
460	}
461	}
462	}
463
464	$this->table_column_headers[ $value['column_name'] ] = $label;
465	}
466
467	}
468
469	/**
470	* Get name of auto increment column
471	*
472	* @return bool\|string Name of auto increment column or false if no auto increment column exists
473	* @since 1.0.0
474	*/
475	public function get_auto_increment_column_name() {
476
477	return $this->auto_increment_column_name;
478
479	}
480
481	}
482
483	}
484