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.83, at WPDataAccess/API/WPDA_Table.php

1,492 lines 59.8 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	$dynamic_allowed = array();
440	$dynamic_table = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
441	$dynamic_columns = $dynamic_table->get_table_columns();
442	foreach ( $dynamic_columns as $column ) {
443	if ( isset( $column['column_name'] ) ) {
444	$dynamic_allowed[] = $column['column_name'];
445	}
446	}
447	foreach ( $column_dynamic_values as $key => $value ) {
448	if ( !in_array( $key, $dynamic_allowed, true ) ) {
449	continue;
450	}
451	$dynamic_where[] = $wpdadb->prepare( " %i = %s ", array($key, $value) );
452	}
453	$where .= (( '' === $where ? ' where ' : ' and ' )) . ' (' . implode( ' and ', $dynamic_where ) . ') ';
454	}
455	$column_count = ( '' === $subquery ? '' : ", stats.total_rows as 'count'" );
456	if ( strpos( $column_value, ',' ) !== false ) {
457	$columns = explode( ',', $column_value );
458	$columns = array_map( function ( $column ) use($wpdadb, $tbl) {
459	return $wpdadb->prepare( "`%1s`.`%1s`", [$tbl, $column] );
460	}, $columns );
461	$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(
462	$tbl,
463	$column_key,
464	implode( ',', $columns ),
465	$tbl
466	) );
467	} else {
468	$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(
469	$tbl,
470	$column_key,
471	$tbl,
472	$column_value,
473	$tbl
474	) );
475	}
476	$orderby = ' order by 2 ';
477	$sql .= " {$where} {$orderby} ";
478	// $where and $orderby already sanitized and prepared
479	$dataset = $wpdadb->get_results( $sql, 'OBJECT' );
480	$wpdadb->suppress_errors( $suppress );
481	// Send response.
482	if ( '' === $wpdadb->last_error ) {
483	// Prepare debug info.
484	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
485	$debug = array(
486	'debug' => array(
487	'sql' => preg_replace( "/\\s+/", " ", $sql ),
488	'where' => $where ?? '',
489	),
490	);
491	} else {
492	$debug = null;
493	}
494	// Add context node to response.
495	$context = array();
496	if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
497	$context['debug'] = $debug['debug'];
498	}
499	return $this->WPDA_Rest_Response( '', $dataset, $context );
500	} else {
501	return new \WP_Error('error', $wpdadb->last_error, array(
502	'status' => 420,
503	));
504	}
505	}
506	}
507
508	/**
509	* Perform query and return result as JSON response.
510	*
511	* @param string $dbs Schema name (database).
512	* @param string $tbl Table Name.
513	* @param array $primary Primary (key\|value pairs.
514	* @param array $media_columns Media columns.
515	* @param array $column_names Just a plain array containing the column names.
516	* @return \WP_Error\|\WP_REST_Response
517	*/
518	public function get(
519	$dbs,
520	$tbl,
521	$primary_key,
522	$media_columns = array(),
523	$column_names = array(),
524	$default_where = '',
525	$docs = array()
526	) {
527	$wpdadb = WPDADB::get_db_connection( $dbs );
528	if ( null === $wpdadb ) {
529	// Error connecting.
530	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
531	'status' => 420,
532	));
533	} else {
534	// Connected, perform queries.
535	$suppress = $wpdadb->suppress_errors( true );
536	$where = '';
537	foreach ( $primary_key as $primary_key_column => $primary_key_value ) {
538	$where = ( '' === $where ? ' where ' : $where . ' and ' );
539	$where .= $wpdadb->prepare( " `%1s` = %s ", array($primary_key_column, $primary_key_value) );
540	}
541	if ( '' !== $default_where ) {
542	if ( '' === $where ) {
543	$where = $default_where;
544	} else {
545	$where .= " and {$default_where} ";
546	}
547	}
548	// Get table column data types
549	$column_list = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
550	$table_columns = $column_list->get_table_columns();
551	// Prepare selected column list
552	$columns_selected = array();
553	$search_data_types = array();
554	foreach ( $table_columns as $table_column ) {
555	if ( isset( $table_column['column_name'], $table_column['data_type'] ) && (in_array( $table_column['column_name'], $column_names ) \|\| empty( $column_names )) ) {
556	$columns_selected[$table_column['column_name']] = true;
557	$search_data_types[$table_column['column_name']] = $table_column['data_type'];
558	}
559	}
560	$selected_columns = $this->get_selected_columns( $columns_selected, $search_data_types );
561	$sql = $wpdadb->prepare( "\n select {$selected_columns}\n from `%1s`\n {$where}\n ", array($tbl) );
562	$dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
563	// Prepare debug info.
564	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
565	$debug = array(
566	'debug' => array(
567	'sql' => $sql,
568	'where' => $where,
569	),
570	);
571	} else {
572	$debug = null;
573	}
574	$wpdadb->suppress_errors( $suppress );
575	// Send response.
576	$media = array();
577	if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
578	foreach ( $media_columns as $media_column_name => $media_column_type ) {
579	if ( isset( $dataset[0][$media_column_name] ) ) {
580	if ( in_array( $media_column_type, [
581	'WP-Image',
582	'WP-Attachment',
583	'WP-Audio',
584	'WP-Video'
585	] ) ) {
586	$media[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[0][$media_column_name] );
587	}
588	}
589	}
590	}
591	$context = array();
592	// Add media
593	$context['media'] = $media;
594	if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
595	$context['debug'] = $debug['debug'];
596	}
597	if ( 0 === count( $dataset ) ) {
598	return $this->WPDA_Rest_Response( 'No data found', $dataset, $context );
599	} else {
600	if ( 1 === count( $dataset ) ) {
601	return $this->WPDA_Rest_Response( '', $dataset, $context );
602	} else {
603	return $this->WPDA_Rest_Response( 'Query returned more than one row', $dataset, $context );
604	}
605	}
606	}
607	}
608
609	public function insert( $dbs, $tbl, $column_values ) {
610	$wpdadb = WPDADB::get_db_connection( $dbs );
611	if ( null === $wpdadb ) {
612	// Error connecting.
613	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
614	'status' => 420,
615	));
616	} else {
617	// Get column default values
618	$column_list = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
619	$table_columns = $column_list->get_table_columns();
620	foreach ( $table_columns as $table_column_type ) {
621	if ( isset( $column_values[$table_column_type['column_name']] ) && $column_values[$table_column_type['column_name']] === $table_column_type['column_default'] ) {
622	// Remove default values if send values equals column default to support defaults using functions
623	unset($column_values[$table_column_type['column_name']]);
624	}
625	}
626	// Sanitize column names and values.
627	$sanitized_column_values = self::sanitize_column_values( $dbs, $tbl, $column_values );
628	if ( false === $sanitized_column_values ) {
629	return new \WP_Error('error', "Invalid arguments", array(
630	'status' => 420,
631	));
632	}
633	// Insert row.
634	$rows_inserted = $wpdadb->insert( $tbl, $sanitized_column_values );
635	// Send response.
636	if ( 1 === $rows_inserted ) {
637	return $this->WPDA_Rest_Response( __( 'Row successfully inserted', 'wp-data-access' ), null, array(
638	'insert_id' => $wpdadb->insert_id,
639	) );
640	} else {
641	if ( '' !== $wpdadb->last_error ) {
642	return new \WP_Error('error', $wpdadb->last_error, array(
643	'status' => 420,
644	));
645	} else {
646	return new \WP_Error('error', 'Insert failed', array(
647	'status' => 420,
648	));
649	}
650	}
651	}
652	}
653
654	public function update(
655	$dbs,
656	$tbl,
657	$primary_key,
658	$column_values,
659	$column_names = array(),
660	$code_columns = array(),
661	$html_columns = array()
662	) {
663	$wpdadb = WPDADB::get_db_connection( $dbs );
664	if ( null === $wpdadb ) {
665	// Error connecting.
666	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
667	'status' => 420,
668	));
669	} else {
670	// Sanitize column names and values.
671	$sanitized_column_values = self::sanitize_column_values(
672	$dbs,
673	$tbl,
674	$column_values,
675	$code_columns,
676	$html_columns
677	);
678	if ( false === $sanitized_column_values ) {
679	return new \WP_Error('error', "Invalid arguments", array(
680	'status' => 420,
681	));
682	}
683	// Update row.
684	$rows_inserted = $wpdadb->update( $tbl, $sanitized_column_values, $primary_key );
685	// Send response.
686	if ( 0 === $rows_inserted ) {
687	return $this->WPDA_Rest_Response_Info( 'Nothing to update' );
688	} elseif ( 1 === $rows_inserted ) {
689	$context = null;
690	if ( 0 < count( $column_names ) ) {
691	// Return updated values
692	$updated_row = $this->get(
693	$dbs,
694	$tbl,
695	$primary_key,
696	$column_names
697	);
698	if ( isset( $updated_row->data['data'][0] ) ) {
699	$updated_values = $updated_row->data['data'][0];
700	$updated_context = array();
701	foreach ( $updated_values as $key => $value ) {
702	if ( !isset( $column_values[$key] ) ) {
703	$updated_context[$key] = $value;
704	}
705	}
706	if ( 0 < count( $updated_context ) ) {
707	$context = array(
708	'updated' => $updated_context,
709	);
710	}
711	}
712	}
713	return $this->WPDA_Rest_Response( __( 'Row successfully updated', 'wp-data-access' ), null, $context );
714	} else {
715	if ( '' !== $wpdadb->last_error ) {
716	return new \WP_Error('error', $wpdadb->last_error, array(
717	'status' => 420,
718	));
719	} else {
720	return new \WP_Error('error', 'Update failed', array(
721	'status' => 420,
722	));
723	}
724	}
725	}
726	}
727
728	public function delete( $dbs, $tbl, $primary_key ) {
729	$wpdadb = WPDADB::get_db_connection( $dbs );
730	if ( null === $wpdadb ) {
731	// Error connecting.
732	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
733	'status' => 420,
734	));
735	} else {
736	// Delete row.
737	$rows_deleted = $wpdadb->delete( $tbl, $primary_key );
738	// Send response.
739	if ( 0 === $rows_deleted ) {
740	return $this->WPDA_Rest_Response_Info( __( 'No data found', 'wp-data-access' ) );
741	} elseif ( 1 === $rows_deleted ) {
742	return $this->WPDA_Rest_Response( __( 'Row successfully deleted', 'wp-data-access' ) );
743	} else {
744	if ( '' !== $wpdadb->last_error ) {
745	return new \WP_Error('error', $wpdadb->last_error, array(
746	'status' => 420,
747	));
748	} else {
749	return new \WP_Error('error', 'Delete failed', array(
750	'status' => 420,
751	));
752	}
753	}
754	}
755	}
756
757	private function generate_lookup_condition(
758	$wpdadb,
759	$lookups,
760	$column_name,
761	$search_values,
762	$search_column_fns,
763	$filter_mode = null,
764	$filter_key = false
765	) {
766	$lookup = $lookups[$column_name];
767	$lookup_table = $lookup['tbl'];
768	$lookup_key = $lookup['key'];
769	$lookup_columns = explode( ',', $lookup['value'] );
770	$lookup_where = array();
771	if ( $filter_key ) {
772	$filter_columns = array($lookup_key);
773	} else {
774	$filter_columns = $lookup_columns;
775	}
776	foreach ( $filter_columns as $lookup_column ) {
777	foreach ( $search_values as $search_value ) {
778	$lookup_where[] = $this->add_filter(
779	$wpdadb,
780	$lookup_column,
781	( $filter_mode !== null ? $filter_mode : $search_column_fns[$column_name] ),
782	$search_value
783	);
784	}
785	}
786	if ( 0 < count( $lookup_where ) ) {
787	return $wpdadb->prepare( ' `%1s` in ( select `%1s` from `%1s` where (' . implode( ' or ', $lookup_where ) . ') ) ', array(
788	$column_name,
789	$lookup_key,
790	$lookup_table,
791	$lookup_columns[0],
792	"%{$search_values[0]}%"
793	) );
794	} else {
795	return null;
796	}
797	}
798
799	public static function remove_where_from_sql( $sql ) {
800	if ( 'where' === substr( trim( $sql ), 0, 5 ) ) {
801	$pos = strpos( $sql, 'where' );
802	if ( false !== $pos ) {
803	$sql = substr_replace(
804	$sql,
805	'',
806	$pos,
807	5
808	);
809	}
810	}
811	return $sql;
812	}
813
814	private function get_md( $md, $wpdadb, $m2m_relationship ) {
815	return null;
816	}
817
818	private function get_global_filter(
819	$wpdadb,
820	$search,
821	$column_names,
822	$lookups,
823	$m2m_relationship
824	) {
825	$where_global = array();
826	if ( null !== $search && "" !== $search ) {
827	foreach ( $column_names as $column_name => $queryable ) {
828	if ( $queryable ) {
829	if ( isset( $lookups[$column_name] ) ) {
830	// Perform look search.
831	$condition = $this->generate_lookup_condition(
832	$wpdadb,
833	$lookups,
834	$column_name,
835	array($search),
836	array(),
837	'contains'
838	);
839	if ( null !== $condition ) {
840	$where_global[] = $condition;
841	}
842	} else {
843	$where_global[] = $wpdadb->prepare( " `%1s` like '%s' ", array($this->convert_column_name( $m2m_relationship, $column_name ), '%' . esc_sql( $search ) . '%') );
844	}
845	}
846	}
847	}
848	return $where_global;
849	}
850
851	private function get_column_filters(
852	$wpdadb,
853	$search_columns,
854	$search_column_fns,
855	$lookups,
856	$m2m_relationship,
857	$search_data_types
858	) {
859	return null;
860	}
861
862	private function get_where(
863	$wpdadb,
864	$default_where,
865	$md,
866	$m2m_relationship,
867	$search,
868	$column_names,
869	$lookups,
870	$search_columns,
871	$search_column_fns,
872	$search_data_types,
873	$geo_radius = array(),
874	$operator = 'and'
875	) {
876	// Default where.
877	if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) {
878	$where = "where {$default_where}";
879	} else {
880	$where = $default_where;
881	}
882	// Global filter.
883	$where_global = $this->get_global_filter(
884	$wpdadb,
885	$search,
886	$column_names,
887	$lookups,
888	$m2m_relationship
889	);
890	if ( 0 < count( $where_global ) ) {
891	$where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' );
892	}
893	if ( is_array( $geo_radius ) && 0 < count( $geo_radius ) ) {
894	// Add geo radius to query
895	// Variable $geo_radius already sanitized in REST API
896	$unit = ( "km" == $geo_radius['unit'] ? 1000 : 1609.344 );
897	// km versus miles
898	if ( $geo_radius['col']['lat'] === $geo_radius['col']['lng'] ) {
899	// Location stored in GEOMETRY or POINT data type
900	$geocol = $geo_radius['col']['lat'];
901	$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']} ";
902	} else {
903	// Latitude and longitude stored separately
904	$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']} ";
905	}
906	if ( '' === $where ) {
907	$where = " where {$geo_where} ";
908	} else {
909	$where .= " and {$geo_where} ";
910	}
911	}
912	return $where;
913	}
914
915	private function get_selected_columns( $column_names, $search_data_types ) {
916	if ( !is_array( $column_names ) ) {
917	return '*';
918	// select all columns
919	}
920	if ( 0 === count( $column_names ) ) {
921	return '*';
922	// select all columns
923	}
924	// Check for geo columns
925	$geometryColumns = array();
926	if ( is_array( $search_data_types ) ) {
927	foreach ( $search_data_types as $column_name => $search_data_type ) {
928	if ( 'geometry' === strtolower( $search_data_type ) \|\| 'point' === strtolower( $search_data_type ) ) {
929	$geometryColumns[] = $column_name;
930	}
931	}
932	}
933	return implode( ",", array_map( function ( $column_name ) use($geometryColumns) {
934	if ( in_array( $column_name, $geometryColumns ) ) {
935	return 'ST_AsText(`' . WPDA::remove_backticks( $column_name ) . '`) ' . " as `{$column_name}` ";
936	// Convert geo data to string
937	} else {
938	return '`' . WPDA::remove_backticks( $column_name ) . '`';
939	}
940	}, array_keys( $column_names ) ) );
941	}
942
943	/**
944	* Perform query and return result as JSON response.
945	*
946	* @param string $dbs Schema name (database).
947	* @param string $tbl Table Name.
948	* @param string $column_names Column Names.
949	* @param string $page_index Page number.
950	* @param string $page_size Rows per page.
951	* @param string $search Filter.
952	* @param string $search_columns Column search filters.
953	* @param string $search_column_fns Column search filter modes.
954	* @param string $Sorting Order by.
955	* @param integer $last_row_count Row count previous request.
956	* @param string $row_count_estimate Indicates if row count estimate should be used.
957	* @param string $media_columns Media columns.
958	* @param string $default_where Defaul where clause
959	* @param string $default_orderby Defaul order by clause
960	* @return \WP_Error\|\WP_REST_Response
961	*/
962	public function select(
963	$dbs,
964	$tbl,
965	$column_names,
966	$page_index,
967	$page_size,
968	$search,
969	$search_columns,
970	$search_column_fns,
971	$sorting,
972	$last_row_count,
973	$row_count_estimate,
974	$media_columns = array(),
975	$default_where = '',
976	$default_orderby = '',
977	$lookups = array(),
978	$md = array(),
979	$m2m_relationship = array(),
980	$search_data_types = array(),
981	$client_side = false,
982	$geo_radius = array(),
983	$docs = array(),
984	$search_global = null
985	) {
986	$wpdadb = WPDADB::get_db_connection( $dbs );
987	if ( null === $wpdadb ) {
988	// Error connecting.
989	return new \WP_Error('error', "Error connecting to database {$dbs}", array(
990	'status' => 420,
991	));
992	} else {
993	$suppress = $wpdadb->suppress_errors( true );
994	// Build where clause.
995	$where = $this->get_where(
996	$wpdadb,
997	$default_where,
998	$md,
999	$m2m_relationship,
1000	$search,
1001	$column_names,
1002	$lookups,
1003	$search_columns,
1004	$search_column_fns,
1005	$search_data_types,
1006	$geo_radius,
1007	'and'
1008	);
1009	if ( $this->current_user_can_access() && isset( $search_global['s'], $search_global['c'] ) ) {
1010	// Perform global search (admins only)
1011	// ???
1012	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1013	$table_columns = $wpda_list_columns->get_table_columns();
1014	$where_global = WPDA::construct_where_clause(
1015	$dbs,
1016	$tbl,
1017	$table_columns,
1018	$search_global['s'],
1019	'false' !== $search_global['c']
1020	);
1021	if ( trim( $where_global ) !== '' ) {
1022	if ( '' !== trim( $where ) && 'where' !== strtolower( substr( trim( $where ), 0, 5 ) ) ) {
1023	$where .= " and {$where_global} ";
1024	} else {
1025	$where .= " where {$where_global} ";
1026	}
1027	}
1028	}
1029	// Build order by.
1030	$sqlorder = '';
1031	if ( is_array( $sorting ) && 0 < count( $sorting ) ) {
1032	foreach ( $sorting as $sort ) {
1033	if ( '' === $sqlorder ) {
1034	$sqlorder = 'order by ';
1035	} else {
1036	$sqlorder .= ',';
1037	}
1038	if ( !$client_side && isset( $lookups[$sort['id']] ) ) {
1039	// Use lookup table to sort
1040	$lookup = $lookups[$sort['id']];
1041	$lookup_dbs = $lookup['dbs'];
1042	$lookup_wpdadb = ( $dbs === $lookup_dbs ? $wpdadb : WPDADB::get_db_connection( $lookup_dbs ) );
1043	if ( $lookup_wpdadb !== null ) {
1044	$lookup_tbl = $lookup['tbl'];
1045	$lookup_key = $lookup['key'];
1046	$lookup_value = $lookup['value'];
1047	$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' );
1048	$lookup_orderby = 'case `' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ';
1049	foreach ( $lookup_dataset as $index => $value ) {
1050	$lookup_orderby .= $lookup_wpdadb->prepare( 'when %s then %d ', array($value[0], $index) );
1051	}
1052	$lookup_orderby .= 'else `' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` end ' . (( $sort['desc'] ? 'desc' : 'asc' ));
1053	$sqlorder .= $lookup_orderby;
1054	} else {
1055	$sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
1056	}
1057	} else {
1058	// Normal sort
1059	$sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' ));
1060	}
1061	}
1062	}
1063	if ( '' === $sqlorder && '' !== trim( $default_orderby ) ) {
1064	$sqlorder = $default_orderby;
1065	}
1066	// Add pagination.
1067	if ( !is_numeric( $page_size ) ) {
1068	$page_size = 10;
1069	}
1070	$offset = $page_index * $page_size;
1071	// Calculate offset.
1072	if ( !is_numeric( $offset ) ) {
1073	$offset = 0;
1074	}
1075	// Prepare query.
1076	$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";
1077	$sql_tables = array($tbl);
1078	// Perpare query.
1079	$sql = $wpdadb->prepare( ( true === $client_side ? $sql : $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )) ), $sql_tables );
1080	// Prepare debug info.
1081	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
1082	$debug = array(
1083	'sql' => preg_replace( "/\\s+/", " ", $sql ),
1084	'where' => $where,
1085	'order by' => $sqlorder,
1086	);
1087	} else {
1088	$debug = null;
1089	}
1090	// Perform query.
1091	$dataset = $wpdadb->get_results( $sql, 'ARRAY_A' );
1092	if ( $wpdadb->last_error ) {
1093	// Handle SQL errors.
1094	return new \WP_Error('error', $wpdadb->last_error, array(
1095	'status' => 420,
1096	'debug' => $debug,
1097	));
1098	}
1099	if ( is_numeric( $last_row_count ) and 0 <= $last_row_count ) {
1100	// Prevents additional unnecessary queries.
1101	$rowcount = $last_row_count;
1102	} else {
1103	if ( true === $client_side ) {
1104	$rowcount = 0;
1105	} else {
1106	$estimate = false;
1107	if ( '1' === $row_count_estimate && '' === $where ) {
1108	// Perform row count estimate
1109	$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' );
1110	if ( isset( $countrows[0]['rowcount'] ) && 0 != $countrows[0]['rowcount'] ) {
1111	$estimate = true;
1112	}
1113	}
1114	if ( !$estimate ) {
1115	if ( !$estimate ) {
1116	// (Re)Count rows.
1117	$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' );
1118	}
1119	}
1120	if ( $wpdadb->last_error ) {
1121	// Handle SQL errors.
1122	return new \WP_Error('error', $wpdadb->last_error, array(
1123	'status' => 420,
1124	));
1125	}
1126	if ( isset( $countrows[0]['rowcount'] ) ) {
1127	$rowcount = $countrows[0]['rowcount'];
1128	} else {
1129	$rowcount = 0;
1130	}
1131	}
1132	}
1133	// Add context node to response
1134	$context = array();
1135	if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) {
1136	$context['debug'] = $debug;
1137	}
1138	if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) {
1139	// Handle WP media library
1140	$media = array();
1141	for ($i = 0; $i < count( $dataset ); $i++) {
1142	$media_row = array();
1143	foreach ( $media_columns as $media_column_name => $media_column_type ) {
1144	if ( isset( $dataset[$i][$media_column_name] ) ) {
1145	$media_row[$media_column_name] = WPDA_WP_Media::get_media_url( $dataset[$i][$media_column_name] );
1146	}
1147	}
1148	$media[] = $media_row;
1149	}
1150	// Add media to context node
1151	$context['media'] = $media;
1152	}
1153	$wpdadb->suppress_errors( $suppress );
1154	// Send response.
1155	$response = $this->WPDA_Rest_Response(
1156	'',
1157	$dataset,
1158	$context,
1159	array(
1160	'rowCount' => $rowcount,
1161	)
1162	);
1163	$response->header( 'X-WP-Total', $rowcount );
1164	// Total rows for this query.
1165	if ( 0 < $page_size ) {
1166	$pagecount = floor( $rowcount / $page_size );
1167	if ( $pagecount != $rowcount / $page_size ) {
1168	// phpcs:ignore WordPress.PHP.StrictComparisons
1169	$pagecount++;
1170	}
1171	} else {
1172	// Prevent division by zero
1173	$pagecount = 0;
1174	}
1175	$response->header( 'X-WP-TotalPages', $pagecount );
1176	// Total pages for this query.
1177	return $response;
1178	}
1179	}
1180
1181	private function convert_column_name( $m2m_relationship, $column_name ) {
1182	// Return plain column name.
1183	return $this->sanitize_db_identifier( $column_name );
1184	}
1185
1186	private function map_columns( $prefix, $column_names ) {
1187	return implode( ",", array_map( function ( $v ) use($prefix) {
1188	$c = $this->sanitize_db_identifier( $v );
1189	$r = ( 'd' === $prefix ? static::RELATIONTABLEPREFIX . $c : $c );
1190	return "`{$prefix}`.`{$c}` as \"{$r}\"";
1191	}, array_keys( $column_names ) ) );
1192	}
1193
1194	public function add_filter(
1195	$wpdadb,
1196	$search_column,
1197	$search_column_fns,
1198	$search_value,
1199	$m2m_relationship = array(),
1200	$search_data_types = array()
1201	) {
1202	return null;
1203	}
1204
1205	public static function add_condition( $where_lines, $operand = 'and' ) {
1206	if ( 0 < count( array_filter( $where_lines ) ) ) {
1207	// Apply all searches.
1208	return ' ( (' . implode( ") {$operand} (", array_filter( $where_lines ) ) . ') ) ';
1209	} else {
1210	return "";
1211	}
1212	}
1213
1214	/**
1215	* Get table meta data.
1216	*
1217	* @param string $dbs Database schema name.
1218	* @param string $tbl Database table name.
1219	* @param string $waa With admin actions.
1220	* @return array \| object
1221	*/
1222	public function get_table_meta_data( $dbs, $tbl, $waa ) {
1223	$sql_create_table = '';
1224	if ( WPDA::current_user_is_admin() ) {
1225	// Admin user has access to all resources
1226	$access = array(
1227	'select' => array('POST'),
1228	'insert' => array('POST'),
1229	'update' => array('POST'),
1230	'delete' => array('POST'),
1231	);
1232	// Get create table script
1233	$wpdadb = WPDADB::get_db_connection( $dbs );
1234	if ( null !== $wpdadb ) {
1235	$suppress_errors = $wpdadb->suppress_errors;
1236	$wpdadb->suppress_errors = true;
1237	// NO_TABLE_OPTIONS is deprecated in V8
1238	// $wpdadb->query( "SET sql_mode = 'NO_TABLE_OPTIONS'" );
1239	$sql = $wpdadb->get_results( $wpdadb->prepare( 'show create table `%1s`', array($tbl) ), 'ARRAY_N' );
1240	if ( isset( $sql[0][1] ) ) {
1241	$sql_create_table = $sql[0][1];
1242	}
1243	$wpdadb->suppress_errors = $suppress_errors;
1244	}
1245	} else {
1246	$access = $this->get_table_access( $dbs, $tbl );
1247	}
1248	$settings = new stdClass();
1249	if ( null !== $access ) {
1250	$columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1251	$settings_db = WPDA_Table_Settings_Model::query( $tbl, $dbs );
1252	if ( isset( $settings_db[0]['wpda_table_settings'] ) ) {
1253	$settings = json_decode( $settings_db[0]['wpda_table_settings'] );
1254	// Remove old settings from response.
1255	unset($settings->form_labels);
1256	unset($settings->list_labels);
1257	unset($settings->custom_settings);
1258	unset($settings->search_settings);
1259	}
1260	$settings->ui = WPDA_Settings::get_admin_settings( $dbs, $tbl );
1261	$rest_api = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
1262	if ( isset( $rest_api[$dbs][$tbl] ) ) {
1263	$settings->rest_api = $rest_api[$dbs][$tbl];
1264	}
1265	$settings->env = $this->get_env();
1266	$wp_nonce_action_alter = "wpda-alter-{$tbl}";
1267	$wp_nonce_alter = wp_create_nonce( $wp_nonce_action_alter );
1268	$wp_nonce_refresh = null;
1269	$connect = null;
1270	global $wpdb;
1271	$settings->wp = [
1272	'roles' => $this->get_wp_roles(),
1273	'users' => $this->get_wp_users(),
1274	'home' => admin_url( 'admin.php' ),
1275	'homea' => admin_url( 'admin-ajax.php' ),
1276	'tables' => array_values( $wpdb->tables() ),
1277	'date_format' => get_option( 'date_format' ),
1278	'time_format' => get_option( 'time_format' ),
1279	'alter' => $wp_nonce_alter,
1280	'refresh' => $wp_nonce_refresh,
1281	'connect' => $connect,
1282	'copyinprogress' => WPDA_Actions::copy_in_progress(),
1283	'scroll_offset' => WPDA::get_option( WPDA::OPTION_APPS_SCROLL_OFFSET ),
1284	'upload' => @ini_get( 'upload_max_filesize' ),
1285	];
1286	if ( true === $waa ) {
1287	$settings->wp['aonce'] = implode( '-', array(
1288	wp_create_nonce( 'wpda-export-' . json_encode( $tbl ) ),
1289	// Table export
1290	wp_create_nonce( 'wpda-rename-' . $tbl ),
1291	) );
1292	}
1293	$table_columns = $columns->get_table_columns();
1294	$media = $this->get_media( $dbs, $tbl, $table_columns );
1295	$columns_sorted = array();
1296	foreach ( $table_columns as $column ) {
1297	if ( isset( $column['column_name'] ) ) {
1298	$columns_sorted[$column['column_name']] = $column;
1299	}
1300	}
1301	}
1302	return array(
1303	'columns' => $table_columns,
1304	'columns_sorted' => $columns_sorted,
1305	'table_labels' => $columns->get_table_header_labels(),
1306	'form_labels' => $columns->get_table_column_headers(),
1307	'primary_key' => $columns->get_table_primary_key(),
1308	'access' => $access,
1309	'settings' => $settings,
1310	'media' => $media['media'],
1311	'wp_media' => $media['wp_media'],
1312	'table_info' => $this->get_table_info( $dbs, $tbl ),
1313	'create' => $sql_create_table,
1314	);
1315	}
1316
1317	private function get_table_access( $dbs, $tbl ) {
1318	if ( WPDA::current_user_is_admin() ) {
1319	// Check administrator rights
1320	if ( is_admin() ) {
1321	$access = WPDA_Dictionary_Access::check_table_access_backend( $dbs, $tbl, $done );
1322	} else {
1323	$access = WPDA_Dictionary_Access::check_table_access_frontend( $dbs, $tbl, $done );
1324	}
1325	if ( $access ) {
1326	// Administrator access granted
1327	return array(
1328	'select' => array('POST'),
1329	'insert' => array('POST'),
1330	'update' => array('POST'),
1331	'delete' => array('POST'),
1332	);
1333	}
1334	}
1335	$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
1336	if ( false !== $tables && isset( $tables[$dbs][$tbl] ) && is_array( $tables[$dbs][$tbl] ) ) {
1337	$table = $tables[$dbs][$tbl];
1338	$table_access = new \stdClass();
1339	$table_access->select = $this->get_table_access_action( $table, 'select' );
1340	$table_access->insert = $this->get_table_access_action( $table, 'insert' );
1341	$table_access->update = $this->get_table_access_action( $table, 'update' );
1342	$table_access->delete = $this->get_table_access_action( $table, 'delete' );
1343	return $table_access;
1344	}
1345	return false;
1346	}
1347
1348	private function get_table_access_action( $table, $action ) {
1349	if ( isset( $table[$action]['authorization'], $table[$action]['methods'] ) && is_array( $table[$action]['methods'] ) && 0 < count( $table[$action]['methods'] ) ) {
1350	if ( 'anonymous' === $table[$action]['authorization'] ) {
1351	return $table[$action]['methods'];
1352	} else {
1353	// Check authorized users
1354	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'] ) ) {
1355	return $table[$action]['methods'];
1356	}
1357	// Check authorized roles
1358	if ( isset( $table[$action]['authorized_roles'] ) && is_array( $table[$action]['authorized_roles'] ) && 0 < count( $table[$action]['authorized_roles'] ) && 0 < count( array_intersect( ( is_array( $this->get_user_roles() ) ? $this->get_user_roles() : array() ), $table[$action]['authorized_roles'] ) ) ) {
1359	return $table[$action]['methods'];
1360	}
1361	}
1362	}
1363	return array();
1364	}
1365
1366	/**
1367	* Check if access is grant for requested database/table.
1368	*
1369	* @param string $dbs Remote or local database connection string.
1370	* @param string $tbl Database table name.
1371	* @param object $request Request object.
1372	* @param string $action Possible values: select, insert, update, delete.
1373	* @return bool
1374	*/
1375	private function check_table_access(
1376	$dbs,
1377	$tbl,
1378	$request,
1379	$action,
1380	&$msg = ''
1381	) {
1382	if ( WPDA::current_user_is_admin() ) {
1383	// Grant access to administrators always.
1384	return true;
1385	}
1386	$tables = get_option( WPDA_API::WPDA_REST_API_TABLE_ACCESS );
1387	if ( false === $tables ) {
1388	// No tables.
1389	$msg = __( 'Unauthorized', 'wp-data-access' );
1390	return false;
1391	}
1392	if ( !(isset( $tables[$dbs][$tbl][$action]['methods'] ) && is_array( $tables[$dbs][$tbl][$action]['methods'] )) ) {
1393	// No methods.
1394	$msg = __( 'Unauthorized', 'wp-data-access' );
1395	return false;
1396	} else {
1397	if ( !in_array( $request->get_method(), $tables[$dbs][$tbl][$action]['methods'] ) ) {
1398	// phpcs:ignore -- 8.1 proof
1399	$msg = __( 'Unauthorized', 'wp-data-access' );
1400	return false;
1401	}
1402	}
1403	if ( !isset( $tables[$dbs][$tbl][$action]['authorization'] ) ) {
1404	// No authorization.
1405	$msg = __( 'Unauthorized', 'wp-data-access' );
1406	return false;
1407	} else {
1408	if ( 'anonymous' === $tables[$dbs][$tbl][$action]['authorization'] ) {
1409	// Access granted to all users.
1410	return true;
1411	}
1412	}
1413	global $wp_rest_auth_cookie;
1414	if ( true !== $wp_rest_auth_cookie ) {
1415	// No anonymous access.
1416	$msg = __( 'Unauthorized', 'wp-data-access' );
1417	return false;
1418	} else {
1419	if ( 'authorized' !== $tables[$dbs][$tbl][$action]['authorization'] ) {
1420	// Authorization check.
1421	$msg = __( 'Unauthorized', 'wp-data-access' );
1422	return false;
1423	}
1424	// Authorized access requires a valid nonce.
1425	if ( !wp_verify_nonce( $request->get_header( 'X-WP-Nonce' ), 'wp_rest' ) ) {
1426	$msg = 'rest_cookie_invalid_nonce';
1427	return false;
1428	}
1429	if ( !(isset( $tables[$dbs][$tbl][$action]['authorized_users'] ) && is_array( $tables[$dbs][$tbl][$action]['authorized_users'] )) ) {
1430	// No users.
1431	$msg = __( 'Unauthorized', 'wp-data-access' );
1432	return false;
1433	} else {
1434	$requesting_user_login = $this->get_user_login();
1435	if ( 0 < count( $tables[$dbs][$tbl][$action]['authorized_users'] ) && in_array( $requesting_user_login, $tables[$dbs][$tbl][$action]['authorized_users'] ) ) {
1436	return true;
1437	}
1438	}
1439	if ( !(isset( $tables[$dbs][$tbl][$action]['authorized_roles'] ) && is_array( $tables[$dbs][$tbl][$action]['authorized_roles'] )) ) {
1440	// No roles.
1441	$msg = __( 'Unauthorized', 'wp-data-access' );
1442	return false;
1443	} else {
1444	$requesting_user_roles = $this->get_user_roles();
1445	if ( false === $requesting_user_roles ) {
1446	$requesting_user_roles = array();
1447	}
1448	if ( 0 < count( $tables[$dbs][$tbl][$action]['authorized_roles'] ) && 0 < count( array_intersect( $requesting_user_roles, $tables[$dbs][$tbl][$action]['authorized_roles'] ) ) ) {
1449	return true;
1450	}
1451	}
1452	$msg = __( 'Unauthorized', 'wp-data-access' );
1453	return false;
1454	}
1455	}
1456
1457	private function sanitize_column_values(
1458	$dbs,
1459	$tbl,
1460	$column_values,
1461	$code_columns = array(),
1462	$html_columns = array()
1463	) {
1464	$wpda_list_columns = WPDA_List_Columns_Cache::get_list_columns( $dbs, $tbl );
1465	$sanitized_column_values = [];
1466	foreach ( $column_values as $column_name => $column_value ) {
1467	$column_value = $column_values[$column_name];
1468	switch ( $wpda_list_columns->get_column_data_type( $column_name ) ) {
1469	case 'tinytext':
1470	case 'text':
1471	case 'mediumtext':
1472	case 'longtext':
1473	if ( null !== $column_value ) {
1474	if ( in_array( $column_name, $html_columns ) ) {
1475	$column_value = sanitize_textarea_field( $column_value );
1476	} else {
1477	$column_value = wp_kses_post( $column_value );
1478	}
1479	}
1480	break;
1481	default:
1482	if ( null !== $column_value ) {
1483	$column_value = sanitize_text_field( $column_value );
1484	}
1485	}
1486	$sanitized_column_values[$this->sanitize_db_identifier( $column_name )] = $column_value;
1487	}
1488	return $sanitized_column_values;
1489	}
1490
1491	}
1492