wp-data-access / WPDataAccess / API / WPDA_Table.php

WPDA_Table.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.5.82, at WPDataAccess/API/WPDA_Table.php

1,481 lines 59.1 KB

Raw Download Zip

1	<?php
2
3	namespace WPDataAccess\API;
4
5	use stdClass;
6	use WPDataAccess\Connection\WPDADB;
7	use WPDataAccess\Data_Dictionary\WPDA_Dictionary_Access;
8	use WPDataAccess\Data_Dictionary\WPDA_List_Columns_Cache;
9	use WPDataAccess\Plugin_Table_Models\WPDA_Table_Settings_Model;
10	use WPDataAccess\Utilities\WPDA_WP_Media;
11	use WPDataAccess\WPDA;
12	class WPDA_Table extends WPDA_API_Core {
13	const WPDA_SEARCH_MODES = array(
14	'contains',
15	'startsWith',
16	'endsWith',
17	'equals',
18	'notEquals',
19	'empty',
20	'notEmpty',
21	'between',
22	'betweenInclusive',
23	'greaterThan',
24	'greaterThanOrEqualTo',
25	'lessThan',
26	'lessThanOrEqualTo'
27	);
28
29	const RELATIONTABLEPREFIX = 'relationTableColumn___';
30
31	public function register_rest_routes() {
32	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/meta', array(
33	'methods' => array('POST'),
34	'callback' => array($this, 'table_meta'),
35	'permission_callback' => '__return_true',
36	'args' => array(
37	'dbs' => $this->get_param( 'dbs' ),
38	'tbl' => $this->get_param( 'tbl' ),
39	'waa' => array(
40	'required' => false,
41	'type' => 'boolean',
42	'description' => __( 'With admin actions (to support table exports)', 'wp-data-access' ),
43	),
44	),
45	) );
46	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/select', array(
47	'methods' => array('GET', 'POST'),
48	'callback' => array($this, 'table_select'),
49	'permission_callback' => '__return_true',
50	'args' => array(
51	'dbs' => $this->get_param( 'dbs' ),
52	'tbl' => $this->get_param( 'tbl' ),
53	'col' => $this->get_param( 'cols' ),
54	'page_index' => $this->get_param( 'page_index' ),
55	'page_size' => $this->get_param( 'page_size' ),
56	'search' => $this->get_param( 'search' ),
57	'search_columns' => $this->get_param( 'search_columns' ),
58	'search_column_fns' => $this->get_param( 'search_column_fns' ),
59	'sorting' => $this->get_param( 'sorting' ),
60	'row_count' => $this->get_param( 'row_count' ),
61	'row_count_estimate' => $this->get_param( 'row_count_estimate' ),
62	'media' => $this->get_param( 'media' ),
63	'client_side' => $this->get_param( 'client_side' ),
64	'global_search' => array(
65	'required' => false,
66	'type' => 'mixed',
67	'description' => __( 'Global search', 'wp-data-access' ),
68	'sanitize_callback' => function ( $param ) {
69	$global_search = array();
70	foreach ( $param as $key => $value ) {
71	if ( $key === 's' \|\| $key === 'c' ) {
72	$global_search[sanitize_text_field( wp_unslash( $key ) )] = sanitize_text_field( wp_unslash( $value ) );
73	}
74	}
75	return $global_search;
76	},
77	'validate_callback' => function ( $param ) {
78	return is_array( $param ) && isset( $param['s'], $param['c'] );
79	},
80	),
81	),
82	) );
83	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/get', array(
84	'methods' => array('GET', 'POST'),
85	'callback' => array($this, 'table_get'),
86	'permission_callback' => '__return_true',
87	'args' => array(
88	'dbs' => $this->get_param( 'dbs' ),
89	'tbl' => $this->get_param( 'tbl' ),
90	'key' => $this->get_param( 'key' ),
91	'media' => $this->get_param( 'media' ),
92	),
93	) );
94	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/insert', array(
95	'methods' => array('GET', 'POST'),
96	'callback' => array($this, 'table_insert'),
97	'permission_callback' => '__return_true',
98	'args' => array(
99	'dbs' => $this->get_param( 'dbs' ),
100	'tbl' => $this->get_param( 'tbl' ),
101	'val' => $this->get_param( 'val' ),
102	),
103	) );
104	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/update', array(
105	'methods' => array('GET', 'POST'),
106	'callback' => array($this, 'table_update'),
107	'permission_callback' => '__return_true',
108	'args' => array(
109	'dbs' => $this->get_param( 'dbs' ),
110	'tbl' => $this->get_param( 'tbl' ),
111	'key' => $this->get_param( 'key' ),
112	'val' => $this->get_param( 'val' ),
113	),
114	) );
115	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/delete', array(
116	'methods' => array('GET', 'POST'),
117	'callback' => array($this, 'table_delete'),
118	'permission_callback' => '__return_true',
119	'args' => array(
120	'dbs' => $this->get_param( 'dbs' ),
121	'tbl' => $this->get_param( 'tbl' ),
122	'key' => $this->get_param( 'key' ),
123	),
124	) );
125	register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/lov', array(
126	'methods' => array('GET', 'POST'),
127	'callback' => array($this, 'table_lov'),
128	'permission_callback' => '__return_true',
129	'args' => array(
130	'dbs' => $this->get_param( 'dbs' ),
131	'tbl' => $this->get_param( 'tbl' ),
132	'col' => $this->get_param( 'col' ),
133	),
134	) );
135	}
136
137	/**
138	* Get table meta info.
139	*
140	* @param \WP_REST_Request $request Rest API request.
141	* @return \WP_Error\|\WP_REST_Response
142	*/
143	public function table_meta( $request ) {
144	$dbs = $request->get_param( 'dbs' );
145	$tbl = $request->get_param( 'tbl' );
146	$waa = $request->get_param( 'waa' );
147	if ( $this->check_table_access(
148	$dbs,
149	$tbl,
150	$request,
151	'select',
152	$msg
153	) ) {
154	return $this->WPDA_Rest_Response( '', $this->get_table_meta_data( $dbs, $tbl, $waa ) );
155	} else {
156	if ( 'rest_cookie_invalid_nonce' === $msg ) {
157	return $this->invalid_nonce();
158	} else {
159	return new \WP_Error('error', $msg, array(
160	'status' => 401,
161	));
162	}
163	}
164	}
165
166	/**
167	* Database table query using the full primary key. Must return exactly one row.
168	*
169	* @param \WP_REST_Request $request Rest API request.
170	* @return \WP_Error\|\WP_REST_Response
171	*/
172	public function table_get( $request ) {
173	$dbs = $request->get_param( 'dbs' );
174	$tbl = $request->get_param( 'tbl' );
175	$key = $request->get_param( 'key' );
176	$media = $request->get_param( 'media' );
177	if ( $this->check_table_access(
178	$dbs,
179	$tbl,
180	$request,
181	'select',
182	$msg
183	) ) {
184	return $this->get(
185	$dbs,
186	$tbl,
187	$key,
188	$media
189	);
190	} else {
191	if ( 'rest_cookie_invalid_nonce' === $msg ) {
192	return $this->invalid_nonce();
193	} else {
194	return new \WP_Error('error', $msg, array(
195	'status' => 401,
196	));
197	}
198	}
199	}
200
201	/**
202	* Insert one row.
203	*
204	* @param \WP_REST_Request $request Rest API request.
205	* @return \WP_Error\|\WP_REST_Response
206	*/
207	public function table_insert( $request ) {
208	$dbs = $request->get_param( 'dbs' );
209	$tbl = $request->get_param( 'tbl' );
210	$val = $request->get_param( 'val' );
211	if ( $this->check_table_access(
212	$dbs,
213	$tbl,
214	$request,
215	'insert',
216	$msg
217	) ) {
218	return $this->insert( $dbs, $tbl, $val );
219	} else {
220	if ( 'rest_cookie_invalid_nonce' === $msg ) {
221	return $this->invalid_nonce();
222	} else {
223	return new \WP_Error('error', $msg, array(
224	'status' => 401,
225	));
226	}
227	}
228	}
229
230	/**
231	* Update uses primary key. Must return exactly one row.
232	*
233	* @param \WP_REST_Request $request Rest API request.
234	* @return \WP_Error\|\WP_REST_Response
235	*/
236	public function table_update( $request ) {
237	$dbs = $request->get_param( 'dbs' );
238	$tbl = $request->get_param( 'tbl' );
239	$key = $request->get_param( 'key' );
240	$val = $request->get_param( 'val' );
241	if ( $this->check_table_access(
242	$dbs,
243	$tbl,
244	$request,
245	'update',
246	$msg
247	) ) {
248	return $this->update(
249	$dbs,
250	$tbl,
251	$key,
252	$val
253	);
254	} else {
255	if ( 'rest_cookie_invalid_nonce' === $msg ) {
256	return $this->invalid_nonce();
257	} else {
258	return new \WP_Error('error', $msg, array(
259	'status' => 401,
260	));
261	}
262	}
263	}
264
265	/**
266	* Delete uses primary key. Must return exactly one row.
267	*
268	* @param \WP_REST_Request $request Rest API request.
269	* @return \WP_Error\|\WP_REST_Response
270	*/
271	public function table_delete( $request ) {
272	$dbs = $request->get_param( 'dbs' );
273	$tbl = $request->get_param( 'tbl' );
274	$key = $request->get_param( 'key' );
275	if ( $this->check_table_access(
276	$dbs,
277	$tbl,
278	$request,
279	'delete',
280	$msg
281	) ) {
282	return $this->delete( $dbs, $tbl, $key );
283	} else {
284	if ( 'rest_cookie_invalid_nonce' === $msg ) {
285	return $this->invalid_nonce();
286	} else {
287	return new \WP_Error('error', $msg, array(
288	'status' => 401,
289	));
290	}
291	}
292	}
293
294	/**
295	* Database table query to populate a list of values for a specific table/column.
296	*
297	* @param \WP_REST_Request $request Rest API request.
298	* @return \WP_Error\|\WP_REST_Response
299	*/
300	public function table_lov( $request ) {
301	return null;
302	}
303
304	/**
305	* Database table query.
306	*
307	* Supports: searching, ordering and pagination.
308	*
309	* @param \WP_REST_Request $request Rest API request.
310	* @return \WP_Error\|\WP_REST_Response
311	*/
312	public function table_select( $request ) {
313	$dbs = $request->get_param( 'dbs' );
314	$tbl = $request->get_param( 'tbl' );
315	$col = $request->get_param( 'col' );
316	$page_index = $request->get_param( 'page_index' );
317	$page_size = $request->get_param( 'page_size' );
318	$search = $request->get_param( 'search' );
319	$search_columns = $request->get_param( 'search_columns' );
320	$search_column_fns = $request->get_param( 'search_column_fns' );
321	$search_data_types = $request->get_param( 'search_data_types' );
322	$sorting = $request->get_param( 'sorting' );
323	$row_count = $request->get_param( 'row_count' );
324	$row_count_estimate = $request->get_param( 'row_count_estimate' );
325	$media = $request->get_param( 'media' );
326	$client_side = '1' === $request->get_param( 'client_side' );
327	$global_search = $request->get_param( 'global_search' );
328	if ( $this->check_table_access(
329	$dbs,
330	$tbl,
331	$request,
332	'select',
333	$msg
334	) ) {
335	return $this->select(
336	$dbs,
337	$tbl,
338	$col,
339	$page_index,
340	$page_size,
341	$search,
342	$search_columns,
343	$search_column_fns,
344	$sorting,
345	$row_count,
346	$row_count_estimate,
347	$media,
348	'',
349	'',
350	array(),
351	array(),
352	array(),
353	$search_data_types,
354	$client_side,
355	array(),
356	array(),
357	$global_search
358	);
359	} else {
360	if ( 'rest_cookie_invalid_nonce' === $msg ) {
361	return $this->invalid_nonce();
362	} else {
363	return new \WP_Error('error', $msg, array(
364	'status' => 401,
365	));
366	}
367	}
368	}
369
370	/**
371	* Perform query and return result as JSON response.
372	*
373	* @param string $dbs Schema name (database).
374	* @param string $tbl Table Name.
375	* @param array $column_name Column name.
376	* @param array $search Global search.
377	* @param array $search_columns Column filters.
378	* @param array $search_column_fns Column filter fns.
379	* @return \WP_Error\|\WP_REST_Response
380	*/
381	public function lov(
382	$dbs,
383	$tbl,
384	$column_name,
385	$cascade = false,
386	$default_where = '',
387	$search = '',
388	$column_names = array(),
389	$search_columns = array(),
390	$search_column_fns = array(),
391	$lookups = array(),
392	$md = array(),
393	$m2m_relationship = array(),
394	$search_data_types = array()
395	) {
396	return null;
397	}
398
399	public function lookup(
400	$dbs,
401	$tbl,
402	$column_key,
403	$column_value,
404	$column_dynamic_values,
405	$default_where,
406	$cascade = false,
407	$cascade_table = '',
408	$cascade_column = '',
409	$cascade_where = '',
410	$search = '',
411	$column_names = array(),
412	$search_columns = array(),
413	$search_column_fns = array(),
414	$lookups = array(),
415	$md = array(),
416	$m2m_relationship = array(),
417	$search_data_types = array()
418	) {
419	$wpdadb = WPDADB::get_db_connection( $dbs );
420	if ( null === $wpdadb ) {
421	// Error connecting.
422	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
423	'status' => 420,
424	));
425	} else {
426	// Connected, perform queries.
427	$suppress = $wpdadb->suppress_errors( true );
428	$subquery = '';
429	$where = '';
430	if ( '' !== trim( $default_where ) ) {
431	if ( 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
432	$where = "where {$default_where}";
433	} else {
434	$where = $default_where;
435	}
436	}
437	$dynamic_where = array();
438	if ( is_array( $column_dynamic_values ) && 0 < count( $column_dynamic_values ) ) {
439	foreach ( $column_dynamic_values as $key => $value ) {
440	$dynamic_where[] = $wpdadb->prepare( " `{$key}` = %s ", $value );
441	}
442	$where .= (( '' === $where ? ' where ' : ' and ' )) . ' (' . implode( ' and ', $dynamic_where ) . ') ';
443	}
444	$column_count = ( '' === $subquery ? '' : ", stats.total_rows as 'count'" );
445	if ( strpos( $column_value, ',' ) !== false ) {
446	$columns = explode( ',', $column_value );
447	$columns = array_map( function ( $column ) use($wpdadb, $tbl) {
448	return $wpdadb->prepare( "`%1s`.`%1s`", [$tbl, $column] );
449	}, $columns );
450	$sql = $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s`.`%1s` as 'key'\n\t\t\t\t\t\t\t, %1s\n\t\t\t\t\t\t\t{$column_count}\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t", array(
451	$tbl,
452	$column_key,
453	implode( ',', $columns ),
454	$tbl
455	) );
456	} else {
457	$sql = $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s`.`%1s` as 'key'\n\t\t\t\t\t\t\t, `%1s`.`%1s` as 'value'\n\t\t\t\t\t\t\t{$column_count}\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t", array(
458	$tbl,
459	$column_key,
460	$tbl,
461	$column_value,
462	$tbl
463	) );
464	}
465	$orderby = ' order by 2 ';
466	$sql .= " {$where} {$orderby} ";
467	// $where and $orderby already sanitized and prepared
468	$dataset = $wpdadb->get_results( $sql, 'OBJECT' );
469	$wpdadb->suppress_errors( $suppress );
470	// Send response.
471	if ( '' === $wpdadb->last_error ) {
472	// Prepare debug info.
473	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
474	$debug = array(
475	'debug' => array(
476	'sql' => preg_replace( "/\\s+/", " ", $sql ),
477	'where' => $where ?? '',
478	),
479	);
480	} else {
481	$debug = null;
482	}
483	// Add context node to response.
484	$context = array();
485	if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
486	$context['debug'] = $debug['debug'];
487	}
488	return $this->WPDA_Rest_Response( '', $dataset, $context );
489	} else {
490	return new \WP_Error('error', $wpdadb->last_error, array(
491	'status' => 420,
492	));
493	}
494	}
495	}
496
497	/**
498	* Perform query and return result as JSON response.
499	*
500	* @param string $dbs Schema name (database).
501	* @param string $tbl Table Name.
502	* @param array $primary Primary (key\|value pairs.
503	* @param array $media_columns Media columns.
504	* @param array $column_names Just a plain array containing the column names.
505	* @return \WP_Error\|\WP_REST_Response
506	*/
507	public function get(
508	$dbs,
509	$tbl,
510	$primary_key,
511	$media_columns = array(),
512	$column_names = array(),
513	$default_where = '',
514	$docs = array()
515	) {
516	$wpdadb = WPDADB::get_db_connection( $dbs );
517	if ( null === $wpdadb ) {
518	// Error connecting.
519	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
520	'status' => 420,
521	));
522	} else {
523	// Connected, perform queries.
524	$suppress = $wpdadb->suppress_errors( true );
525	$where = '';
526	foreach ( $primary_key as $primary_key_column => $primary_key_value ) {
527	$where = ( '' === $where ? ' where ' : $where . ' and ' );
528	$where .= $wpdadb->prepare( " `%1s` = %s ", array($primary_key_column, $primary_key_value) );
529	}
530	if ( '' !== $default_where ) {
531	if ( '' === $where ) {
532	$where = $default_where;
533	} else {
534	$where .= " and {$default_where} ";
535	}
536	}
537	// Get table column data types
538	$column_list = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
539	$table_columns = $column_list->get_table_columns();
540	// Prepare selected column list
541	$columns_selected = array();
542	$search_data_types = array();
543	foreach ( $table_columns as $table_column ) {
544	if ( isset( $table_column['column_name'], $table_column['data_type'] ) ) {
545	$columns_selected[$table_column['column_name']] = true;
546	$search_data_types[$table_column['column_name']] = $table_column['data_type'];
547	}
548	}
549	$selected_columns = $this->get_selected_columns( $columns_selected, $search_data_types );
550	$sql = $wpdadb->prepare( "\n select {$selected_columns}\n from `%1s`\n {$where}\n ", array($tbl) );
551	$dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
552	// Prepare debug info.
553	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
554	$debug = array(
555	'debug' => array(
556	'sql' => $sql,
557	'where' => $where,
558	),
559	);
560	} else {
561	$debug = null;
562	}
563	$wpdadb->suppress_errors( $suppress );
564	// Send response.
565	$media = array();
566	if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
567	foreach ( $media_columns as $media_column_name => $media_column_type ) {
568	if ( isset( $dataset[0][$media_column_name] ) ) {
569	if ( in_array( $media_column_type, [
570	'WP-Image',
571	'WP-Attachment',
572	'WP-Audio',
573	'WP-Video'
574	] ) ) {
575	$media[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[0][$media_column_name] );
576	}
577	}
578	}
579	}
580	$context = array();
581	// Add media
582	$context['media'] = $media;
583	if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
584	$context['debug'] = $debug['debug'];
585	}
586	if ( 0 === count( $dataset ) ) {
587	return $this->WPDA_Rest_Response( 'No data found', $dataset, $context );
588	} else {
589	if ( 1 === count( $dataset ) ) {
590	return $this->WPDA_Rest_Response( '', $dataset, $context );
591	} else {
592	return $this->WPDA_Rest_Response( 'Query returned more than one row', $dataset, $context );
593	}
594	}
595	}
596	}
597
598	public function insert( $dbs, $tbl, $column_values ) {
599	$wpdadb = WPDADB::get_db_connection( $dbs );
600	if ( null === $wpdadb ) {
601	// Error connecting.
602	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
603	'status' => 420,
604	));
605	} else {
606	// Get column default values
607	$column_list = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
608	$table_columns = $column_list->get_table_columns();
609	foreach ( $table_columns as $table_column_type ) {
610	if ( isset( $column_values[$table_column_type['column_name']] ) && $column_values[$table_column_type['column_name']] === $table_column_type['column_default'] ) {
611	// Remove default values if send values equals column default to support defaults using functions
612	unset($column_values[$table_column_type['column_name']]);
613	}
614	}
615	// Sanitize column names and values.
616	$sanitized_column_values = self::sanitize_column_values( $dbs, $tbl, $column_values );
617	if ( false === $sanitized_column_values ) {
618	return new \WP_Error('error', "Invalid arguments", array(
619	'status' => 420,
620	));
621	}
622	// Insert row.
623	$rows_inserted = $wpdadb->insert( $tbl, $sanitized_column_values );
624	// Send response.
625	if ( 1 === $rows_inserted ) {
626	return $this->WPDA_Rest_Response( __( 'Row successfully inserted', 'wp-data-access' ), null, array(
627	'insert_id' => $wpdadb->insert_id,
628	) );
629	} else {
630	if ( '' !== $wpdadb->last_error ) {
631	return new \WP_Error('error', $wpdadb->last_error, array(
632	'status' => 420,
633	));
634	} else {
635	return new \WP_Error('error', 'Insert failed', array(
636	'status' => 420,
637	));
638	}
639	}
640	}
641	}
642
643	public function update(
644	$dbs,
645	$tbl,
646	$primary_key,
647	$column_values,
648	$column_names = array(),
649	$code_columns = array(),
650	$html_columns = array()
651	) {
652	$wpdadb = WPDADB::get_db_connection( $dbs );
653	if ( null === $wpdadb ) {
654	// Error connecting.
655	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
656	'status' => 420,
657	));
658	} else {
659	// Sanitize column names and values.
660	$sanitized_column_values = self::sanitize_column_values(
661	$dbs,
662	$tbl,
663	$column_values,
664	$code_columns,
665	$html_columns
666	);
667	if ( false === $sanitized_column_values ) {
668	return new \WP_Error('error', "Invalid arguments", array(
669	'status' => 420,
670	));
671	}
672	// Update row.
673	$rows_inserted = $wpdadb->update( $tbl, $sanitized_column_values, $primary_key );
674	// Send response.
675	if ( 0 === $rows_inserted ) {
676	return $this->WPDA_Rest_Response_Info( 'Nothing to update' );
677	} elseif ( 1 === $rows_inserted ) {
678	$context = null;
679	if ( 0 < count( $column_names ) ) {
680	// Return updated values
681	$updated_row = $this->get(
682	$dbs,
683	$tbl,
684	$primary_key,
685	$column_names
686	);
687	if ( isset( $updated_row->data['data'][0] ) ) {
688	$updated_values = $updated_row->data['data'][0];
689	$updated_context = array();
690	foreach ( $updated_values as $key => $value ) {
691	if ( !isset( $column_values[$key] ) ) {
692	$updated_context[$key] = $value;
693	}
694	}
695	if ( 0 < count( $updated_context ) ) {
696	$context = array(
697	'updated' => $updated_context,
698	);
699	}
700	}
701	}
702	return $this->WPDA_Rest_Response( __( 'Row successfully updated', 'wp-data-access' ), null, $context );
703	} else {
704	if ( '' !== $wpdadb->last_error ) {
705	return new \WP_Error('error', $wpdadb->last_error, array(
706	'status' => 420,
707	));
708	} else {
709	return new \WP_Error('error', 'Update failed', array(
710	'status' => 420,
711	));
712	}
713	}
714	}
715	}
716
717	public function delete( $dbs, $tbl, $primary_key ) {
718	$wpdadb = WPDADB::get_db_connection( $dbs );
719	if ( null === $wpdadb ) {
720	// Error connecting.
721	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
722	'status' => 420,
723	));
724	} else {
725	// Delete row.
726	$rows_deleted = $wpdadb->delete( $tbl, $primary_key );
727	// Send response.
728	if ( 0 === $rows_deleted ) {
729	return $this->WPDA_Rest_Response_Info( __( 'No data found', 'wp-data-access' ) );
730	} elseif ( 1 === $rows_deleted ) {
731	return $this->WPDA_Rest_Response( __( 'Row successfully deleted', 'wp-data-access' ) );
732	} else {
733	if ( '' !== $wpdadb->last_error ) {
734	return new \WP_Error('error', $wpdadb->last_error, array(
735	'status' => 420,
736	));
737	} else {
738	return new \WP_Error('error', 'Delete failed', array(
739	'status' => 420,
740	));
741	}
742	}
743	}
744	}
745
746	private function generate_lookup_condition(
747	$wpdadb,
748	$lookups,
749	$column_name,
750	$search_values,
751	$search_column_fns,
752	$filter_mode = null,
753	$filter_key = false
754	) {
755	$lookup = $lookups[$column_name];
756	$lookup_table = $lookup['tbl'];
757	$lookup_key = $lookup['key'];
758	$lookup_columns = explode( ',', $lookup['value'] );
759	$lookup_where = array();
760	if ( $filter_key ) {
761	$filter_columns = array($lookup_key);
762	} else {
763	$filter_columns = $lookup_columns;
764	}
765	foreach ( $filter_columns as $lookup_column ) {
766	foreach ( $search_values as $search_value ) {
767	$lookup_where[] = $this->add_filter(
768	$wpdadb,
769	$lookup_column,
770	( $filter_mode !== null ? $filter_mode : $search_column_fns[$column_name] ),
771	$search_value
772	);
773	}
774	}
775	if ( 0 < count( $lookup_where ) ) {
776	return $wpdadb->prepare( ' `%1s` in ( select `%1s` from `%1s` where (' . implode( ' or ', $lookup_where ) . ') ) ', array(
777	$column_name,
778	$lookup_key,
779	$lookup_table,
780	$lookup_columns[0],
781	"%{$search_values[0]}%"
782	) );
783	} else {
784	return null;
785	}
786	}
787
788	public static function remove_where_from_sql( $sql ) {
789	if ( 'where' === substr( trim( $sql ), 0, 5 ) ) {
790	$pos = strpos( $sql, 'where' );
791	if ( false !== $pos ) {
792	$sql = substr_replace(
793	$sql,
794	'',
795	$pos,
796	5
797	);
798	}
799	}
800	return $sql;
801	}
802
803	private function get_md( $md, $wpdadb, $m2m_relationship ) {
804	return null;
805	}
806
807	private function get_global_filter(
808	$wpdadb,
809	$search,
810	$column_names,
811	$lookups,
812	$m2m_relationship
813	) {
814	$where_global = array();
815	if ( null !== $search && "" !== $search ) {
816	foreach ( $column_names as $column_name => $queryable ) {
817	if ( $queryable ) {
818	if ( isset( $lookups[$column_name] ) ) {
819	// Perform look search.
820	$condition = $this->generate_lookup_condition(
821	$wpdadb,
822	$lookups,
823	$column_name,
824	array($search),
825	array(),
826	'contains'
827	);
828	if ( null !== $condition ) {
829	$where_global[] = $condition;
830	}
831	} else {
832	$where_global[] = $wpdadb->prepare( " `%1s` like '%s' ", array($this->convert_column_name( $m2m_relationship, $column_name ), '%' . esc_sql( $search ) . '%') );
833	}
834	}
835	}
836	}
837	return $where_global;
838	}
839
840	private function get_column_filters(
841	$wpdadb,
842	$search_columns,
843	$search_column_fns,
844	$lookups,
845	$m2m_relationship,
846	$search_data_types
847	) {
848	return null;
849	}
850
851	private function get_where(
852	$wpdadb,
853	$default_where,
854	$md,
855	$m2m_relationship,
856	$search,
857	$column_names,
858	$lookups,
859	$search_columns,
860	$search_column_fns,
861	$search_data_types,
862	$geo_radius = array(),
863	$operator = 'and'
864	) {
865	// Default where.
866	if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
867	$where = "where {$default_where}";
868	} else {
869	$where = $default_where;
870	}
871	// Global filter.
872	$where_global = $this->get_global_filter(
873	$wpdadb,
874	$search,
875	$column_names,
876	$lookups,
877	$m2m_relationship
878	);
879	if ( 0 < count( $where_global ) ) {
880	$where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' );
881	}
882	if ( is_array( $geo_radius ) && 0 < count( $geo_radius ) ) {
883	// Add geo radius to query
884	// Variable $geo_radius already sanitized in REST API
885	$unit = ( "km" == $geo_radius['unit'] ? 1000 : 1609.344 );
886	// km versus miles
887	if ( $geo_radius['col']['lat'] === $geo_radius['col']['lng'] ) {
888	// Location stored in GEOMETRY or POINT data type
889	$geocol = $geo_radius['col']['lat'];
890	$geo_where = " ( st_distance_sphere(point(st_y(`{$geocol}`), st_x(`{$geocol}`)), point({$geo_radius['loc']['lng']}, {$geo_radius['loc']['lat']})) / {$unit} ) < {$geo_radius['radius']} ";
891	} else {
892	// Latitude and longitude stored separately
893	$geo_where = " ( st_distance_sphere(point(`{$geo_radius['col']['lng']}`, `{$geo_radius['col']['lat']}`), point({$geo_radius['loc']['lng']}, {$geo_radius['loc']['lat']})) / {$unit} ) < {$geo_radius['radius']} ";
894	}
895	if ( '' === $where ) {
896	$where = " where {$geo_where} ";
897	} else {
898	$where .= " and {$geo_where} ";
899	}
900	}
901	return $where;
902	}
903
904	private function get_selected_columns( $column_names, $search_data_types ) {
905	if ( !is_array( $column_names ) ) {
906	return '*';
907	// select all columns
908	}
909	if ( 0 === count( $column_names ) ) {
910	return '*';
911	// select all columns
912	}
913	// Check for geo columns
914	$geometryColumns = array();
915	if ( is_array( $search_data_types ) ) {
916	foreach ( $search_data_types as $column_name => $search_data_type ) {
917	if ( 'geometry' === strtolower( $search_data_type ) \|\| 'point' === strtolower( $search_data_type ) ) {
918	$geometryColumns[] = $column_name;
919	}
920	}
921	}
922	return implode( ",", array_map( function ( $column_name ) use($geometryColumns) {
923	if ( in_array( $column_name, $geometryColumns ) ) {
924	return 'ST_AsText(`' . WPDA::remove_backticks( $column_name ) . '`) ' . " as `{$column_name}` ";
925	// Convert geo data to string
926	} else {
927	return '`' . WPDA::remove_backticks( $column_name ) . '`';
928	}
929	}, array_keys( $column_names ) ) );
930	}
931
932	/**
933	* Perform query and return result as JSON response.
934	*
935	* @param string $dbs Schema name (database).
936	* @param string $tbl Table Name.
937	* @param string $column_names Column Names.
938	* @param string $page_index Page number.
939	* @param string $page_size Rows per page.
940	* @param string $search Filter.
941	* @param string $search_columns Column search filters.
942	* @param string $search_column_fns Column search filter modes.
943	* @param string $Sorting Order by.
944	* @param integer $last_row_count Row count previous request.
945	* @param string $row_count_estimate Indicates if row count estimate should be used.
946	* @param string $media_columns Media columns.
947	* @param string $default_where Defaul where clause
948	* @param string $default_orderby Defaul order by clause
949	* @return \WP_Error\|\WP_REST_Response
950	*/
951	public function select(
952	$dbs,
953	$tbl,
954	$column_names,
955	$page_index,
956	$page_size,
957	$search,
958	$search_columns,
959	$search_column_fns,
960	$sorting,
961	$last_row_count,
962	$row_count_estimate,
963	$media_columns = array(),
964	$default_where = '',
965	$default_orderby = '',
966	$lookups = array(),
967	$md = array(),
968	$m2m_relationship = array(),
969	$search_data_types = array(),
970	$client_side = false,
971	$geo_radius = array(),
972	$docs = array(),
973	$search_global = null
974	) {
975	$wpdadb = WPDADB::get_db_connection( $dbs );
976	if ( null === $wpdadb ) {
977	// Error connecting.
978	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
979	'status' => 420,
980	));
981	} else {
982	$suppress = $wpdadb->suppress_errors( true );
983	// Build where clause.
984	$where = $this->get_where(
985	$wpdadb,
986	$default_where,
987	$md,
988	$m2m_relationship,
989	$search,
990	$column_names,
991	$lookups,
992	$search_columns,
993	$search_column_fns,
994	$search_data_types,
995	$geo_radius,
996	'and'
997	);
998	if ( $this->current_user_can_access() && isset( $search_global['s'], $search_global['c'] ) ) {
999	// Perform global search (admins only)
1000	// ???
1001	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1002	$table_columns = $wpda_list_columns->get_table_columns();
1003	$where_global = WPDA::construct_where_clause(
1004	$dbs,
1005	$tbl,
1006	$table_columns,
1007	$search_global['s'],
1008	'false' !== $search_global['c']
1009	);
1010	if ( trim( $where_global ) !== '' ) {
1011	if ( '' !== trim( $where ) && 'where' !== strtolower( substr( trim( $where ), 0, 5 ) ) ) {
1012	$where .= " and {$where_global} ";
1013	} else {
1014	$where .= " where {$where_global} ";
1015	}
1016	}
1017	}
1018	// Build order by.
1019	$sqlorder = '';
1020	if ( is_array( $sorting ) && 0 < count( $sorting ) ) {
1021	foreach ( $sorting as $sort ) {
1022	if ( '' === $sqlorder ) {
1023	$sqlorder = 'order by ';
1024	} else {
1025	$sqlorder .= ',';
1026	}
1027	if ( !$client_side && isset( $lookups[$sort['id']] ) ) {
1028	// Use lookup table to sort
1029	$lookup = $lookups[$sort['id']];
1030	$lookup_dbs = $lookup['dbs'];
1031	$lookup_wpdadb = ( $dbs === $lookup_dbs ? $wpdadb : WPDADB::get_db_connection( $lookup_dbs ) );
1032	if ( $lookup_wpdadb !== null ) {
1033	$lookup_tbl = $lookup['tbl'];
1034	$lookup_key = $lookup['key'];
1035	$lookup_value = $lookup['value'];
1036	$lookup_dataset = $lookup_wpdadb->get_results( $lookup_wpdadb->prepare( "select `%1s`, `%1s` from `%1s` order by 2", array($lookup_key, $lookup_value, $lookup_tbl) ), 'ARRAY_N' );
1037	$lookup_orderby = 'case `' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ';
1038	foreach ( $lookup_dataset as $index => $value ) {
1039	$lookup_orderby .= $lookup_wpdadb->prepare( 'when %s then %d ', array($value[0], $index) );
1040	}
1041	$lookup_orderby .= 'else `' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` end ' . (( $sort['desc'] ? 'desc' : 'asc' ));
1042	$sqlorder .= $lookup_orderby;
1043	} else {
1044	$sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
1045	}
1046	} else {
1047	// Normal sort
1048	$sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
1049	}
1050	}
1051	}
1052	if ( '' === $sqlorder && '' !== trim( $default_orderby ) ) {
1053	$sqlorder = $default_orderby;
1054	}
1055	// Add pagination.
1056	if ( !is_numeric( $page_size ) ) {
1057	$page_size = 10;
1058	}
1059	$offset = $page_index * $page_size;
1060	// Calculate offset.
1061	if ( !is_numeric( $offset ) ) {
1062	$offset = 0;
1063	}
1064	// Prepare query.
1065	$sql = "\n\t\t\t\t\tselect " . $this->get_selected_columns( $column_names, $search_data_types ) . "\n\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t{$where}\n\t\t\t\t\t{$sqlorder}\n\t\t\t\t";
1066	$sql_tables = array($tbl);
1067	// Perpare query.
1068	$sql = $wpdadb->prepare( ( true === $client_side ? $sql : $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )) ), $sql_tables );
1069	// Prepare debug info.
1070	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
1071	$debug = array(
1072	'sql' => preg_replace( "/\\s+/", " ", $sql ),
1073	'where' => $where,
1074	'order by' => $sqlorder,
1075	);
1076	} else {
1077	$debug = null;
1078	}
1079	// Perform query.
1080	$dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
1081	if ( $wpdadb->last_error ) {
1082	// Handle SQL errors.
1083	return new \WP_Error('error', $wpdadb->last_error, array(
1084	'status' => 420,
1085	'debug' => $debug,
1086	));
1087	}
1088	if ( is_numeric( $last_row_count ) and 0 <= $last_row_count ) {
1089	// Prevents additional unnecessary queries.
1090	$rowcount = $last_row_count;
1091	} else {
1092	if ( true === $client_side ) {
1093	$rowcount = 0;
1094	} else {
1095	$estimate = false;
1096	if ( '1' === $row_count_estimate && '' === $where ) {
1097	// Perform row count estimate
1098	$countrows = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\t\t\tselect table_rows as rowcount\n\t\t\t\t\t\t\t\t\t from information_schema.tables\n\t\t\t\t\t\t\t\t\twhere table_schema = %s\n\t\t\t\t\t\t\t\t\t and table_name = %s\n\t\t\t\t\t\t\t\t", [$wpdadb->dbname, $tbl] ), 'ARRAY_A' );
1099	if ( isset( $countrows[0]['rowcount'] ) && 0 != $countrows[0]['rowcount'] ) {
1100	$estimate = true;
1101	}
1102	}
1103	if ( !$estimate ) {
1104	if ( !$estimate ) {
1105	// (Re)Count rows.
1106	$countrows = $wpdadb->get_results( $wpdadb->prepare( "\n\t\t\t\t\t\t\t\t\t\tselect count(1) as rowcount\n\t\t\t\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t\t\t\t\t{$where}\n\t\t\t\t\t\t\t\t\t", array($tbl) ), 'ARRAY_A' );
1107	}
1108	}
1109	if ( $wpdadb->last_error ) {
1110	// Handle SQL errors.
1111	return new \WP_Error('error', $wpdadb->last_error, array(
1112	'status' => 420,
1113	));
1114	}
1115	if ( isset( $countrows[0]['rowcount'] ) ) {
1116	$rowcount = $countrows[0]['rowcount'];
1117	} else {
1118	$rowcount = 0;
1119	}
1120	}
1121	}
1122	// Add context node to response
1123	$context = array();
1124	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
1125	$context['debug'] = $debug;
1126	}
1127	if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
1128	// Handle WP media library
1129	$media = array();
1130	for ($i = 0; $i < count( $dataset ); $i++) {
1131	$media_row = array();
1132	foreach ( $media_columns as $media_column_name => $media_column_type ) {
1133	if ( isset( $dataset[$i][$media_column_name] ) ) {
1134	$media_row[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[$i][$media_column_name] );
1135	}
1136	}
1137	$media[] = $media_row;
1138	}
1139	// Add media to context node
1140	$context['media'] = $media;
1141	}
1142	$wpdadb->suppress_errors( $suppress );
1143	// Send response.
1144	$response = $this->WPDA_Rest_Response(
1145	'',
1146	$dataset,
1147	$context,
1148	array(
1149	'rowCount' => $rowcount,
1150	)
1151	);
1152	$response->header( 'X-WP-Total', $rowcount );
1153	// Total rows for this query.
1154	if ( 0 < $page_size ) {
1155	$pagecount = floor( $rowcount / $page_size );
1156	if ( $pagecount != $rowcount / $page_size ) {
1157	// phpcs:ignore WordPress.PHP.StrictComparisons
1158	$pagecount++;
1159	}
1160	} else {
1161	// Prevent division by zero
1162	$pagecount = 0;
1163	}
1164	$response->header( 'X-WP-TotalPages', $pagecount );
1165	// Total pages for this query.
1166	return $response;
1167	}
1168	}
1169
1170	private function convert_column_name( $m2m_relationship, $column_name ) {
1171	// Return plain column name.
1172	return $this->sanitize_db_identifier( $column_name );
1173	}
1174
1175	private function map_columns( $prefix, $column_names ) {
1176	return implode( ",", array_map( function ( $v ) use($prefix) {
1177	$c = $this->sanitize_db_identifier( $v );
1178	$r = ( 'd' === $prefix ? static::RELATIONTABLEPREFIX . $c : $c );
1179	return "`{$prefix}`.`{$c}` as \"{$r}\"";
1180	}, array_keys( $column_names ) ) );
1181	}
1182
1183	public function add_filter(
1184	$wpdadb,
1185	$search_column,
1186	$search_column_fns,
1187	$search_value,
1188	$m2m_relationship = array(),
1189	$search_data_types = array()
1190	) {
1191	return null;
1192	}
1193
1194	public static function add_condition( $where_lines, $operand = 'and' ) {
1195	if ( 0 < count( array_filter( $where_lines ) ) ) {
1196	// Apply all searches.
1197	return ' ( (' . implode( ") {$operand} (", array_filter( $where_lines ) ) . ') ) ';
1198	} else {
1199	return "";
1200	}
1201	}
1202
1203	/**
1204	* Get table meta data.
1205	*
1206	* @param string $dbs Database schema name.
1207	* @param string $tbl Database table name.
1208	* @param string $waa With admin actions.
1209	* @return array \| object
1210	*/
1211	public function get_table_meta_data( $dbs, $tbl, $waa ) {
1212	$sql_create_table = '';
1213	if ( WPDA::current_user_is_admin() ) {
1214	// Admin user has access to all resources
1215	$access = array(
1216	'select' => array('POST'),
1217	'insert' => array('POST'),
1218	'update' => array('POST'),
1219	'delete' => array('POST'),
1220	);
1221	// Get create table script
1222	$wpdadb = WPDADB::get_db_connection( $dbs );
1223	if ( null !== $wpdadb ) {
1224	$suppress_errors = $wpdadb->suppress_errors;
1225	$wpdadb->suppress_errors = true;
1226	// NO_TABLE_OPTIONS is deprecated in V8
1227	// $wpdadb->query( "SET sql_mode = 'NO_TABLE_OPTIONS'" );
1228	$sql = $wpdadb->get_results( $wpdadb->prepare( 'show create table `%1s`', array($tbl) ), 'ARRAY_N' );
1229	if ( isset( $sql[0][1] ) ) {
1230	$sql_create_table = $sql[0][1];
1231	}
1232	$wpdadb->suppress_errors = $suppress_errors;
1233	}
1234	} else {
1235	$access = $this->get_table_access( $dbs, $tbl );
1236	}
1237	$settings = new stdClass();
1238	if ( null !== $access ) {
1239	$columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1240	$settings_db = WPDA_Table_Settings_Model::query( $tbl, $dbs );
1241	if ( isset( $settings_db[0]['wpda_table_settings'] ) ) {
1242	$settings = json_decode( $settings_db[0]['wpda_table_settings'] );
1243	// Remove old settings from response.
1244	unset($settings->form_labels);
1245	unset($settings->list_labels);
1246	unset($settings->custom_settings);
1247	unset($settings->search_settings);
1248	}
1249	$settings->ui = WPDA_Settings::get_admin_settings( $dbs, $tbl );
1250	$rest_api = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
1251	if ( isset( $rest_api[$dbs][$tbl] ) ) {
1252	$settings->rest_api = $rest_api[$dbs][$tbl];
1253	}
1254	$settings->env = $this->get_env();
1255	$wp_nonce_action_alter = "wpda-alter-{$tbl}";
1256	$wp_nonce_alter = wp_create_nonce( $wp_nonce_action_alter );
1257	$wp_nonce_refresh = null;
1258	$connect = null;
1259	global $wpdb;
1260	$settings->wp = [
1261	'roles' => $this->get_wp_roles(),
1262	'users' => $this->get_wp_users(),
1263	'home' => admin_url( 'admin.php' ),
1264	'homea' => admin_url( 'admin-ajax.php' ),
1265	'tables' => array_values( $wpdb->tables() ),
1266	'date_format' => get_option( 'date_format' ),
1267	'time_format' => get_option( 'time_format' ),
1268	'alter' => $wp_nonce_alter,
1269	'refresh' => $wp_nonce_refresh,
1270	'connect' => $connect,
1271	'copyinprogress' => WPDA_Actions::copy_in_progress(),
1272	'scroll_offset' => WPDA::get_option( WPDA::OPTION_APPS_SCROLL_OFFSET ),
1273	'upload' => @ini_get( 'upload_max_filesize' ),
1274	];
1275	if ( true === $waa ) {
1276	$settings->wp['aonce'] = implode( '-', array(
1277	wp_create_nonce( 'wpda-export-' . json_encode( $tbl ) ),
1278	// Table export
1279	wp_create_nonce( 'wpda-rename-' . $tbl ),
1280	) );
1281	}
1282	$table_columns = $columns->get_table_columns();
1283	$media = $this->get_media( $dbs, $tbl, $table_columns );
1284	$columns_sorted = array();
1285	foreach ( $table_columns as $column ) {
1286	if ( isset( $column['column_name'] ) ) {
1287	$columns_sorted[$column['column_name']] = $column;
1288	}
1289	}
1290	}
1291	return array(
1292	'columns' => $table_columns,
1293	'columns_sorted' => $columns_sorted,
1294	'table_labels' => $columns->get_table_header_labels(),
1295	'form_labels' => $columns->get_table_column_headers(),
1296	'primary_key' => $columns->get_table_primary_key(),
1297	'access' => $access,
1298	'settings' => $settings,
1299	'media' => $media['media'],
1300	'wp_media' => $media['wp_media'],
1301	'table_info' => $this->get_table_info( $dbs, $tbl ),
1302	'create' => $sql_create_table,
1303	);
1304	}
1305
1306	private function get_table_access( $dbs, $tbl ) {
1307	if ( WPDA::current_user_is_admin() ) {
1308	// Check administrator rights
1309	if ( is_admin() ) {
1310	$access = WPDA_Dictionary_Access::check_table_access_backend( $dbs, $tbl, $done );
1311	} else {
1312	$access = WPDA_Dictionary_Access::check_table_access_frontend( $dbs, $tbl, $done );
1313	}
1314	if ( $access ) {
1315	// Administrator access granted
1316	return array(
1317	'select' => array('POST'),
1318	'insert' => array('POST'),
1319	'update' => array('POST'),
1320	'delete' => array('POST'),
1321	);
1322	}
1323	}
1324	$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
1325	if ( false !== $tables && isset( $tables[$dbs][$tbl] ) && is_array( $tables[$dbs][$tbl] ) ) {
1326	$table = $tables[$dbs][$tbl];
1327	$table_access = new \stdClass();
1328	$table_access->select = $this->get_table_access_action( $table, 'select' );
1329	$table_access->insert = $this->get_table_access_action( $table, 'insert' );
1330	$table_access->update = $this->get_table_access_action( $table, 'update' );
1331	$table_access->delete = $this->get_table_access_action( $table, 'delete' );
1332	return $table_access;
1333	}
1334	return false;
1335	}
1336
1337	private function get_table_access_action( $table, $action ) {
1338	if ( isset( $table[$action]['authorization'], $table[$action]['methods'] ) && is_array( $table[$action]['methods'] ) && 0 < count( $table[$action]['methods'] ) ) {
1339	if ( 'anonymous' === $table[$action]['authorization'] ) {
1340	return $table[$action]['methods'];
1341	} else {
1342	// Check authorized users
1343	if ( isset( $table[$action]['authorized_users'] ) && is_array( $table[$action]['authorized_users'] ) && 0 < count( $table[$action]['authorized_users'] ) && in_array( (string) $this->get_user_login(), $table[$action]['authorized_users'] ) ) {
1344	return $table[$action]['methods'];
1345	}
1346	// Check authorized roles
1347	if ( isset( $table[$action]['authorized_roles'] ) && is_array( $table[$action]['authorized_roles'] ) && 0 < count( $table[$action]['authorized_roles'] ) && 0 < count( array_intersect( $this->get_user_roles(), $table[$action]['authorized_roles'] ) ) ) {
1348	return $table[$action]['methods'];
1349	}
1350	}
1351	}
1352	return array();
1353	}
1354
1355	/**
1356	* Check if access is grant for requested database/table.
1357	*
1358	* @param string $dbs Remote or local database connection string.
1359	* @param string $tbl Database table name.
1360	* @param object $request Request object.
1361	* @param string $action Possible values: select, insert, update, delete.
1362	* @return bool
1363	*/
1364	private function check_table_access(
1365	$dbs,
1366	$tbl,
1367	$request,
1368	$action,
1369	&$msg = ''
1370	) {
1371	if ( WPDA::current_user_is_admin() ) {
1372	// Grant access to administrators always.
1373	return true;
1374	}
1375	$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
1376	if ( false === $tables ) {
1377	// No tables.
1378	$msg = __( 'Unauthorized', 'wp-data-access' );
1379	return false;
1380	}
1381	if ( !(isset( $tables[$dbs][$tbl][$action]['methods'] ) && is_array( $tables[$dbs][$tbl][$action]['methods'] )) ) {
1382	// No methods.
1383	$msg = __( 'Unauthorized', 'wp-data-access' );
1384	return false;
1385	} else {
1386	if ( !in_array( $request->get_method(), $tables[$dbs][$tbl][$action]['methods'] ) ) {
1387	// phpcs:ignore -- 8.1 proof
1388	$msg = __( 'Unauthorized', 'wp-data-access' );
1389	return false;
1390	}
1391	}
1392	if ( !isset( $tables[$dbs][$tbl][$action]['authorization'] ) ) {
1393	// No authorization.
1394	$msg = __( 'Unauthorized', 'wp-data-access' );
1395	return false;
1396	} else {
1397	if ( 'anonymous' === $tables[$dbs][$tbl][$action]['authorization'] ) {
1398	// Access granted to all users.
1399	return true;
1400	}
1401	}
1402	global $wp_rest_auth_cookie;
1403	if ( true !== $wp_rest_auth_cookie ) {
1404	// No anonymous access.
1405	$msg = __( 'Unauthorized', 'wp-data-access' );
1406	return false;
1407	} else {
1408	if ( 'authorized' !== $tables[$dbs][$tbl][$action]['authorization'] ) {
1409	// Authorization check.
1410	$msg = __( 'Unauthorized', 'wp-data-access' );
1411	return false;
1412	}
1413	// Authorized access requires a valid nonce.
1414	if ( !wp_verify_nonce( $request->get_header( 'X-WP-Nonce' ), 'wp_rest' ) ) {
1415	$msg = 'rest_cookie_invalid_nonce';
1416	return false;
1417	}
1418	if ( !(isset( $tables[$dbs][$tbl][$action]['authorized_users'] ) && is_array( $tables[$dbs][$tbl][$action]['authorized_users'] )) ) {
1419	// No users.
1420	$msg = __( 'Unauthorized', 'wp-data-access' );
1421	return false;
1422	} else {
1423	$requesting_user_login = $this->get_user_login();
1424	if ( 0 < count( $tables[$dbs][$tbl][$action]['authorized_users'] ) && in_array( $requesting_user_login, $tables[$dbs][$tbl][$action]['authorized_users'] ) ) {
1425	return true;
1426	}
1427	}
1428	if ( !(isset( $tables[$dbs][$tbl][$action]['authorized_roles'] ) && is_array( $tables[$dbs][$tbl][$action]['authorized_roles'] )) ) {
1429	// No roles.
1430	$msg = __( 'Unauthorized', 'wp-data-access' );
1431	return false;
1432	} else {
1433	$requesting_user_roles = $this->get_user_roles();
1434	if ( false === $requesting_user_roles ) {
1435	$requesting_user_roles = array();
1436	}
1437	if ( 0 < count( $tables[$dbs][$tbl][$action]['authorized_roles'] ) && 0 < count( array_intersect( $requesting_user_roles, $tables[$dbs][$tbl][$action]['authorized_roles'] ) ) ) {
1438	return true;
1439	}
1440	}
1441	$msg = __( 'Unauthorized', 'wp-data-access' );
1442	return false;
1443	}
1444	}
1445
1446	private function sanitize_column_values(
1447	$dbs,
1448	$tbl,
1449	$column_values,
1450	$code_columns = array(),
1451	$html_columns = array()
1452	) {
1453	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1454	$sanitized_column_values = [];
1455	foreach ( $column_values as $column_name => $column_value ) {
1456	$column_value = $column_values[$column_name];
1457	switch ( $wpda_list_columns->get_column_data_type( $column_name ) ) {
1458	case 'tinytext':
1459	case 'text':
1460	case 'mediumtext':
1461	case 'longtext':
1462	if ( null !== $column_value ) {
1463	if ( in_array( $column_name, $html_columns ) ) {
1464	$column_value = sanitize_textarea_field( $column_value );
1465	} else {
1466	$column_value = wp_kses_post( $column_value );
1467	}
1468	}
1469	break;
1470	default:
1471	if ( null !== $column_value ) {
1472	$column_value = sanitize_text_field( $column_value );
1473	}
1474	}
1475	$sanitized_column_values[$this->sanitize_db_identifier( $column_name )] = $column_value;
1476	}
1477	return $sanitized_column_values;
1478	}
1479
1480	}
1481