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.84, at WPDataAccess/Data_Dictionary/WPDA_List_Columns.php

486 lines 13.0 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	/* translators: %s = database name */
128	wp_die( esc_attr( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name) ) ) );
129	} else {
130	/* translators: %s = database name */
131	die( esc_attr( sprintf( __( 'ERROR - Remote database %s not available', 'wp-data-access' ), esc_attr( $this->schema_name ) ) ) );
132	}
133	}
134
135	if ( '' !== $table_name ) {
136	$this->table_name = $table_name;
137
138	// Get table settings
139	$table_settings_db = WPDA_Table_Settings_Model::query( $this->table_name, $this->schema_name );
140	if ( isset( $table_settings_db[0]['wpda_table_settings'] ) ) {
141	$this->table_settings = json_decode( $table_settings_db[0]['wpda_table_settings'] );
142	}
143
144	// Get dictionary info
145	$this->set_table_columns();
146	$this->set_table_primary_key();
147	$this->set_table_column_headers();
148	}
149	}
150
151	/**
152	* Get column label for list table
153	*
154	* Returns the label of a column according to a pre defined format. Call must contain column name.
155	* Column must be in $this->table_name.
156	*
157	* @param string $column_name Column name as defined in the data dictionary.
158	*
159	* @return string Label for $column_name.
160	* @since 1.0.0
161	*/
162	public function get_column_label( $column_name ) {
163
164	if ( isset( $this->table_settings->list_labels->$column_name ) ) {
165	return $this->table_settings->list_labels->$column_name;
166	} else {
167	return $this->get_default_column_label( $column_name );
168	}
169
170	}
171
172	public function get_table_header_labels() {
173	if ( isset( $this->table_settings->list_labels ) ) {
174	return $this->table_settings->list_labels;
175	} else {
176	if ( isset( $this->table_column_headers ) ) {
177	return $this->table_column_headers;
178	} else {
179	return null;
180	}
181	}
182	}
183
184	/**
185	* Return column data type
186	*
187	* @param $column_name string Database column name
188	*
189	* @return string\|null
190	* @since 2.7.2
191	*/
192	public function get_column_data_type( $column_name ) {
193	return is_string( $column_name ) && isset( $this->table_column_data_type[ $column_name ] ) ?
194	$this->table_column_data_type[ $column_name ] : null;
195	}
196
197	/**
198	* Return column type
199	*
200	* @param $column_name string Database column name
201	*
202	* @return string\|null
203	* @since 2.7.2
204	*/
205	public function get_column_type( $column_name ) {
206	return isset( $this->table_column_type[ (string) $column_name ] ) ? $this->table_column_type[ (string) $column_name ] : null;
207	}
208
209	/**
210	* Get default column label
211	*
212	* Returns the default label of a column according to a pre defined format. Call must contain column name.
213	* Column must be in $this->table_name.
214	*
215	* @param string $column_name Column name as defined in the data dictionary.
216	*
217	* @return string Default label for $column_name.
218	* @since 1.0.0
219	*/
220	public function get_default_column_label( $column_name ) {
221
222	return ucwords( str_replace( '_', ' ', $column_name ) );
223
224	}
225
226	/**
227	* Check if column is part of primary key
228	*
229	* @param string $column_name Column name as defined in the data dictionary.
230	*
231	* @return bool TRUE = column is part of primary key, FALSE = column is not part of primary key.
232	* @since 1.0.0
233	*/
234	public function is_primary_key_column( $column_name ) {
235
236	return ( isset( $this->table_primary_key_check[ $column_name ] ) );
237
238	}
239
240	/**
241	* Get columns
242	*
243	* @return array Column of $this->table_name.
244	* @since 1.0.0
245	*/
246	public function get_table_columns() {
247
248	return $this->table_columns;
249
250	}
251
252	/**
253	* Get searchable columns
254	*
255	* @return array Column of $this->table_name.
256	* @since 1.0.0
257	*/
258	public function get_searchable_table_columns() {
259
260	return $this->searchable_table_columns;
261
262	}
263
264	/**
265	* Set table columns
266	*
267	* Column info is taken from the MySQL data dictionary. For each column in $this->table_name the following
268	* column info is stored:
269	* + Column name
270	* + Data type (MySQL data type)
271	* + Extra (needed to find auto increment columns)
272	* + Column type (needed for columns with data type enum: column type holds allowed values)
273	* + Null values allowed?
274	*
275	* Since MariaDB 10.2.7 and higher handles default values different than other DBMSs we have to take care of
276	* the quotes it places at the beginning and the end. This involves the implication that users cannot use
277	* default values that start and end with a single quote. Which seems common sense for me.
278	*
279	* @since 1.0.0
280	*/
281	protected function set_table_columns() {
282	$query = $this->wpdadb->prepare(
283	'
284	SELECT column_name AS column_name,
285	data_type AS data_type,
286	extra AS extra,
287	column_type AS column_type,
288	is_nullable AS is_nullable,
289	IF(LEFT(column_default,1)=\'\'\'\' AND RIGHT(column_default,1)=\'\'\'\', SUBSTR(column_default,2,LENGTH(column_default)-2), column_default) AS column_default,
290	character_maximum_length AS character_maximum_length,
291	numeric_scale AS numeric_scale,
292	numeric_precision AS numeric_precision
293	FROM information_schema.columns
294	WHERE table_schema = %s
295	AND table_name = %s
296	ORDER BY ordinal_position
297	',
298	array(
299	$this->wpdadb->dbname,
300	$this->table_name,
301	)
302	);
303
304	$this->table_columns = $this->wpdadb->get_results( $query, 'ARRAY_A' );
305	$this->searchable_table_columns = $this->table_columns; // Contains all columns and is not modified
306
307	foreach ( $this->table_columns as $column ) {
308	if ( isset( $column['column_name'] ) && isset( $column['data_type'] ) ) {
309	$this->table_column_data_type[ $column['column_name'] ] = $column['data_type'];
310	}
311
312	if ( isset( $column['column_name'] ) && isset( $column['column_type'] ) ) {
313	$this->table_column_type[ $column['column_name'] ] = $column['column_type'];
314	}
315	}
316
317	}
318
319	/**
320	* Get primary key columns
321	*
322	* @return array Primary key columns of $this->table_name.
323	* @since 1.0.0
324	*/
325	public function get_table_primary_key() {
326	return $this->table_primary_key;
327	}
328
329	/**
330	* Set primary key columns
331	*
332	* Primary key columns are taken from the MySQL data dictionary.
333	*
334	* Newer versions of MariaDB no longer seem to support JOIN USING. Rewritten to old school join. MySQL supports
335	* both types of joins.
336	*
337	* @since 1.0.0
338	*/
339	protected function set_table_primary_key() {
340	$result = $this->get_table_unique_indexes();
341
342	if ( 0 < $this->wpdadb->num_rows ) {
343	// Check if there is a primary key
344	$has_primary_key = false;
345	foreach ( $result as $row ) {
346	if ( 'PRIMARY' === $row['Key_name'] ) {
347	$this->table_primary_key[] = $row['Column_name'];
348	$this->table_primary_key_check[ $row['Column_name'] ] = true;
349
350	foreach ( $this->table_columns as $table_column ) {
351	if ( $table_column['column_name'] === $row['Column_name'] &&
352	'auto_increment' === $table_column['extra']
353	) {
354	// Save auto_increment column name.
355	$this->auto_increment_column_name = $row['Column_name'];
356	}
357	}
358
359	$has_primary_key = true;
360	}
361	}
362
363	if ( ! $has_primary_key ) {
364	// Use non unique index as an alternative
365	$key_name = $result[0]['Key_name'];
366	$this->table_primary_key[] = $result[0]['Column_name'];
367	$this->table_primary_key_check[ $key_name ] = true;
368	$i = 1;
369	while ( $i < count( $result ) ) { // phpcs:ignore -- 8.1 proof
370	if ( $key_name === $result[ $i ]['Key_name'] ) {
371	$this->table_primary_key[] = $result[ $i ]['Column_name'];
372	$this->table_primary_key_check[ $result[ $i ]['Column_name'] ] = true;
373	}
374	$i ++;
375	}
376	}
377	}
378	}
379
380	protected function get_table_unique_indexes() {
381	$suppress = $this->wpdadb->suppress_errors( true );
382	$query =
383	sprintf(
384	'SHOW INDEX FROM `%s`.`%s` WHERE non_unique = 0',
385	$this->wpdadb->dbname,
386	str_replace( '`', '', (string) $this->table_name )
387	);
388	$result = $this->wpdadb->get_results( $query, 'ARRAY_A' );
389	$this->wpdadb->suppress_errors( $suppress );
390
391	return $result;
392	}
393
394	public function get_table_alternative_keys() {
395	$result = $this->get_table_unique_indexes();
396	$return = array();
397
398	if ( 0 < $this->wpdadb->num_rows ) {
399	$index_name = '';
400	$index = array();
401	foreach ( $result as $row ) {
402	if ( $index_name !== $row['Key_name'] ) {
403	if ( '' !== $index_name ) {
404	array_push( $return, $row['Column_name'] ); // phpcs:ignore -- 8.1 proof
405	$index = array();
406	}
407	$index_name = $row['Key_name'];
408	}
409	array_push( $index, $row['Column_name'] ); // phpcs:ignore -- 8.1 proof
410	}
411	array_push( $return, $index ); // phpcs:ignore -- 8.1 proof
412	}
413
414	return $return;
415	}
416
417	/**
418	* Get column headers
419	*
420	* @return array
421	* @since 1.0.0
422	*/
423	public function get_table_column_headers() {
424
425	return $this->table_column_headers;
426
427	}
428
429	/**
430	* Set column headers (= column labels in data entry forms)
431	*
432	* For now column headers are defined equal to their names. If a column is part of the primary key, this is
433	* reflected in the column header.
434	*
435	* @since 1.0.0
436	*/
437	protected function set_table_column_headers() {
438
439	if ( ! isset( $this->table_columns ) ) {
440	wp_die( esc_attr__( 'ERROR: Wrong arguments [no columns found]', 'wp-data-access' ) );
441	}
442
443	$primary_nr = 0;
444	$this->table_column_headers = array();
445	foreach ( $this->table_columns as $key => $value ) {
446
447	if ( isset( $this->table_settings->form_labels ) ) {
448	if ( isset( $this->table_settings->form_labels->{$value['column_name']} ) ) {
449	$label = $this->table_settings->form_labels->{$value['column_name']};
450	} else {
451	$label = $this->get_default_column_label( $value['column_name'] );
452	}
453	} else {
454	$label = $this->get_default_column_label( $value['column_name'] );
455
456	if ( $this->is_primary_key_column( $value['column_name'] ) ) {
457	$key_text = __( 'key', 'wp-data-access' );
458	if ( count( $this->table_primary_key ) > 1 ) { // phpcs:ignore -- 8.1 proof
459	$label .= " ($key_text #" . ( ++ $primary_nr ) . ')';
460	} else {
461	$label .= " ($key_text)";
462	}
463	}
464	}
465
466	$this->table_column_headers[ $value['column_name'] ] = $label;
467	}
468
469	}
470
471	/**
472	* Get name of auto increment column
473	*
474	* @return bool\|string Name of auto increment column or false if no auto increment column exists
475	* @since 1.0.0
476	*/
477	public function get_auto_increment_column_name() {
478
479	return $this->auto_increment_column_name;
480
481	}
482
483	}
484
485	}
486