| @@ -60,8 +60,25 @@ | ||
| 60 | 60 | 'row_count' => $this->get_param( 'row_count' ), |
| 61 | 61 | 'row_count_estimate' => $this->get_param( 'row_count_estimate' ), |
| 62 | 62 | 'media' => $this->get_param( 'media' ), |
| 63 | 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 | + ), | |
| 64 | 81 | ), |
| 65 | 82 | ) ); |
| 66 | 83 | register_rest_route( WPDA_API::WPDA_NAMESPACE, 'table/get', array( |
| 67 | 84 | 'methods' => array('GET', 'POST'), |
| @@ -119,9 +136,9 @@ | ||
| 119 | 136 | |
| 120 | 137 | /** |
| 121 | 138 | * Get table meta info. |
| 122 | 139 | * |
| 123 | - * @param WP_REST_Request $request Rest API request. | |
| 140 | + * @param \WP_REST_Request $request Rest API request. | |
| 124 | 141 | * @return \WP_Error|\WP_REST_Response |
| 125 | 142 | */ |
| 126 | 143 | public function table_meta( $request ) { |
| 127 | 144 | $dbs = $request->get_param( 'dbs' ); |
| @@ -148,9 +165,9 @@ | ||
| 148 | 165 | |
| 149 | 166 | /** |
| 150 | 167 | * Database table query using the full primary key. Must return exactly one row. |
| 151 | 168 | * |
| 152 | - * @param WP_REST_Request $request Rest API request. | |
| 169 | + * @param \WP_REST_Request $request Rest API request. | |
| 153 | 170 | * @return \WP_Error|\WP_REST_Response |
| 154 | 171 | */ |
| 155 | 172 | public function table_get( $request ) { |
| 156 | 173 | $dbs = $request->get_param( 'dbs' ); |
| @@ -183,9 +200,9 @@ | ||
| 183 | 200 | |
| 184 | 201 | /** |
| 185 | 202 | * Insert one row. |
| 186 | 203 | * |
| 187 | - * @param WP_REST_Request $request Rest API request. | |
| 204 | + * @param \WP_REST_Request $request Rest API request. | |
| 188 | 205 | * @return \WP_Error|\WP_REST_Response |
| 189 | 206 | */ |
| 190 | 207 | public function table_insert( $request ) { |
| 191 | 208 | $dbs = $request->get_param( 'dbs' ); |
| @@ -212,9 +229,9 @@ | ||
| 212 | 229 | |
| 213 | 230 | /** |
| 214 | 231 | * Update uses primary key. Must return exactly one row. |
| 215 | 232 | * |
| 216 | - * @param WP_REST_Request $request Rest API request. | |
| 233 | + * @param \WP_REST_Request $request Rest API request. | |
| 217 | 234 | * @return \WP_Error|\WP_REST_Response |
| 218 | 235 | */ |
| 219 | 236 | public function table_update( $request ) { |
| 220 | 237 | $dbs = $request->get_param( 'dbs' ); |
| @@ -247,9 +264,9 @@ | ||
| 247 | 264 | |
| 248 | 265 | /** |
| 249 | 266 | * Delete uses primary key. Must return exactly one row. |
| 250 | 267 | * |
| 251 | - * @param WP_REST_Request $request Rest API request. | |
| 268 | + * @param \WP_REST_Request $request Rest API request. | |
| 252 | 269 | * @return \WP_Error|\WP_REST_Response |
| 253 | 270 | */ |
| 254 | 271 | public function table_delete( $request ) { |
| 255 | 272 | $dbs = $request->get_param( 'dbs' ); |
| @@ -276,12 +293,13 @@ | ||
| 276 | 293 | |
| 277 | 294 | /** |
| 278 | 295 | * Database table query to populate a list of values for a specific table/column. |
| 279 | 296 | * |
| 280 | - * @param WP_REST_Request $request Rest API request. | |
| 297 | + * @param \WP_REST_Request $request Rest API request. | |
| 281 | 298 | * @return \WP_Error|\WP_REST_Response |
| 282 | 299 | */ |
| 283 | 300 | public function table_lov( $request ) { |
| 301 | + return null; | |
| 284 | 302 | } |
| 285 | 303 | |
| 286 | 304 | /** |
| 287 | 305 | * Database table query. |
| @@ -287,9 +305,9 @@ | ||
| 287 | 305 | * Database table query. |
| 288 | 306 | * |
| 289 | 307 | * Supports: searching, ordering and pagination. |
| 290 | 308 | * |
| 291 | - * @param WP_REST_Request $request Rest API request. | |
| 309 | + * @param \WP_REST_Request $request Rest API request. | |
| 292 | 310 | * @return \WP_Error|\WP_REST_Response |
| 293 | 311 | */ |
| 294 | 312 | public function table_select( $request ) { |
| 295 | 313 | $dbs = $request->get_param( 'dbs' ); |
| @@ -299,13 +317,15 @@ | ||
| 299 | 317 | $page_size = $request->get_param( 'page_size' ); |
| 300 | 318 | $search = $request->get_param( 'search' ); |
| 301 | 319 | $search_columns = $request->get_param( 'search_columns' ); |
| 302 | 320 | $search_column_fns = $request->get_param( 'search_column_fns' ); |
| 321 | + $search_data_types = $request->get_param( 'search_data_types' ); | |
| 303 | 322 | $sorting = $request->get_param( 'sorting' ); |
| 304 | 323 | $row_count = $request->get_param( 'row_count' ); |
| 305 | 324 | $row_count_estimate = $request->get_param( 'row_count_estimate' ); |
| 306 | 325 | $media = $request->get_param( 'media' ); |
| 307 | 326 | $client_side = '1' === $request->get_param( 'client_side' ); |
| 327 | + $global_search = $request->get_param( 'global_search' ); | |
| 308 | 328 | if ( $this->check_table_access( |
| 309 | 329 | $dbs, |
| 310 | 330 | $tbl, |
| 311 | 331 | $request, |
| @@ -329,10 +349,13 @@ | ||
| 329 | 349 | '', |
| 330 | 350 | array(), |
| 331 | 351 | array(), |
| 332 | 352 | array(), |
| 353 | + $search_data_types, | |
| 354 | + $client_side, | |
| 333 | 355 | array(), |
| 334 | - $client_side | |
| 356 | + array(), | |
| 357 | + $global_search | |
| 335 | 358 | ); |
| 336 | 359 | } else { |
| 337 | 360 | if ( 'rest_cookie_invalid_nonce' === $msg ) { |
| 338 | 361 | return $this->invalid_nonce(); |
| @@ -369,8 +392,9 @@ | ||
| 369 | 392 | $md = array(), |
| 370 | 393 | $m2m_relationship = array(), |
| 371 | 394 | $search_data_types = array() |
| 372 | 395 | ) { |
| 396 | + return null; | |
| 373 | 397 | } |
| 374 | 398 | |
| 375 | 399 | public function lookup( |
| 376 | 400 | $dbs, |
| @@ -411,21 +435,48 @@ | ||
| 411 | 435 | } |
| 412 | 436 | } |
| 413 | 437 | $dynamic_where = array(); |
| 414 | 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 | + } | |
| 415 | 447 | foreach ( $column_dynamic_values as $key => $value ) { |
| 416 | - $dynamic_where[] = $wpdadb->prepare( " `{$key}` = %s ", $value ); | |
| 448 | + if ( !in_array( $key, $dynamic_allowed, true ) ) { | |
| 449 | + continue; | |
| 450 | + } | |
| 451 | + $dynamic_where[] = $wpdadb->prepare( " %i = %s ", array($key, $value) ); | |
| 417 | 452 | } |
| 418 | 453 | $where .= (( '' === $where ? ' where ' : ' and ' )) . ' (' . implode( ' and ', $dynamic_where ) . ') '; |
| 419 | 454 | } |
| 455 | + $column_count = ( '' === $subquery ? '' : ", stats.total_rows as 'count'" ); | |
| 420 | 456 | if ( strpos( $column_value, ',' ) !== false ) { |
| 421 | 457 | $columns = explode( ',', $column_value ); |
| 422 | - $sql = $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s` as 'key'\n\t\t\t\t\t\t\t, `%1s`\n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t", array($column_key, implode( '`,`', $columns ), $tbl) ); | |
| 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 | + ) ); | |
| 423 | 467 | } else { |
| 424 | - $sql = $wpdadb->prepare( "\n\t\t\t\t\t\t\tselect distinct `%1s` as 'key'\n\t\t\t\t\t\t\t, `%1s` as 'value' \n\t\t\t\t\t\t\tfrom `%1s`\n\t\t\t\t\t\t", array($column_key, $column_value, $tbl) ); | |
| 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 | + ) ); | |
| 425 | 475 | } |
| 426 | - $sql .= " {$where} order by 2 "; | |
| 427 | - // $where already sanitized | |
| 476 | + $orderby = ' order by 2 '; | |
| 477 | + $sql .= " {$where} {$orderby} "; | |
| 478 | + // $where and $orderby already sanitized and prepared | |
| 428 | 479 | $dataset = $wpdadb->get_results( $sql, 'OBJECT' ); |
| 429 | 480 | $wpdadb->suppress_errors( $suppress ); |
| 430 | 481 | // Send response. |
| 431 | 482 | if ( '' === $wpdadb->last_error ) { |
| @@ -467,11 +518,12 @@ | ||
| 467 | 518 | public function get( |
| 468 | 519 | $dbs, |
| 469 | 520 | $tbl, |
| 470 | 521 | $primary_key, |
| 471 | - $media_columns, | |
| 522 | + $media_columns = array(), | |
| 472 | 523 | $column_names = array(), |
| 473 | - $default_where = '' | |
| 524 | + $default_where = '', | |
| 525 | + $docs = array() | |
| 474 | 526 | ) { |
| 475 | 527 | $wpdadb = WPDADB::get_db_connection( $dbs ); |
| 476 | 528 | if ( null === $wpdadb ) { |
| 477 | 529 | // Error connecting. |
| @@ -492,15 +544,22 @@ | ||
| 492 | 544 | } else { |
| 493 | 545 | $where .= " and {$default_where} "; |
| 494 | 546 | } |
| 495 | 547 | } |
| 496 | - $selected_columns = '*'; | |
| 497 | - if ( 0 < count( $column_names ) ) { | |
| 498 | - $selected_columns = '`' . implode( '`,`', array_map( function ( $column_name ) { | |
| 499 | - return WPDA::remove_backticks( $column_name ); | |
| 500 | - }, $column_names ) ) . '`'; | |
| 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 | + } | |
| 501 | 559 | } |
| 502 | - $sql = $wpdadb->prepare( "select {$selected_columns} from `%1s` {$where}", array($tbl) ); | |
| 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) ); | |
| 503 | 562 | $dataset = $wpdadb->get_results( $sql, 'ARRAY_A' ); |
| 504 | 563 | // Prepare debug info. |
| 505 | 564 | if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) { |
| 506 | 565 | $debug = array( |
| @@ -514,9 +573,9 @@ | ||
| 514 | 573 | } |
| 515 | 574 | $wpdadb->suppress_errors( $suppress ); |
| 516 | 575 | // Send response. |
| 517 | 576 | $media = array(); |
| 518 | - if ( 0 < count( $media_columns ) ) { | |
| 577 | + if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) { | |
| 519 | 578 | foreach ( $media_columns as $media_column_name => $media_column_type ) { |
| 520 | 579 | if ( isset( $dataset[0][$media_column_name] ) ) { |
| 521 | 580 | if ( in_array( $media_column_type, [ |
| 522 | 581 | 'WP-Image', |
| @@ -529,23 +588,20 @@ | ||
| 529 | 588 | } |
| 530 | 589 | } |
| 531 | 590 | } |
| 532 | 591 | $context = array(); |
| 592 | + // Add media | |
| 533 | 593 | $context['media'] = $media; |
| 534 | 594 | if ( isset( $debug['debug'] ) && 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) { |
| 535 | 595 | $context['debug'] = $debug['debug']; |
| 536 | 596 | } |
| 537 | 597 | if ( 0 === count( $dataset ) ) { |
| 538 | - return $this->WPDA_Rest_Response( 'No data found', $dataset, array( | |
| 539 | - 'debug' => $debug['debug'], | |
| 540 | - ) ); | |
| 598 | + return $this->WPDA_Rest_Response( 'No data found', $dataset, $context ); | |
| 541 | 599 | } else { |
| 542 | 600 | if ( 1 === count( $dataset ) ) { |
| 543 | 601 | return $this->WPDA_Rest_Response( '', $dataset, $context ); |
| 544 | 602 | } else { |
| 545 | - return $this->WPDA_Rest_Response( 'Query returned more than one row', $dataset, array( | |
| 546 | - 'debug' => $debug['debug'], | |
| 547 | - ) ); | |
| 603 | + return $this->WPDA_Rest_Response( 'Query returned more than one row', $dataset, $context ); | |
| 548 | 604 | } |
| 549 | 605 | } |
| 550 | 606 | } |
| 551 | 607 | } |
| @@ -755,8 +811,9 @@ | ||
| 755 | 811 | return $sql; |
| 756 | 812 | } |
| 757 | 813 | |
| 758 | 814 | private function get_md( $md, $wpdadb, $m2m_relationship ) { |
| 815 | + return null; | |
| 759 | 816 | } |
| 760 | 817 | |
| 761 | 818 | private function get_global_filter( |
| 762 | 819 | $wpdadb, |
| @@ -798,8 +855,9 @@ | ||
| 798 | 855 | $lookups, |
| 799 | 856 | $m2m_relationship, |
| 800 | 857 | $search_data_types |
| 801 | 858 | ) { |
| 859 | + return null; | |
| 802 | 860 | } |
| 803 | 861 | |
| 804 | 862 | private function get_where( |
| 805 | 863 | $wpdadb, |
| @@ -810,9 +868,11 @@ | ||
| 810 | 868 | $column_names, |
| 811 | 869 | $lookups, |
| 812 | 870 | $search_columns, |
| 813 | 871 | $search_column_fns, |
| 814 | - $search_data_types | |
| 872 | + $search_data_types, | |
| 873 | + $geo_radius = array(), | |
| 874 | + $operator = 'and' | |
| 815 | 875 | ) { |
| 816 | 876 | // Default where. |
| 817 | 877 | if ( '' !== trim( $default_where ) && 'where' !== strtolower( substr( trim( $default_where ), 0, 5 ) ) ) { |
| 818 | 878 | $where = "where {$default_where}"; |
| @@ -829,11 +889,58 @@ | ||
| 829 | 889 | ); |
| 830 | 890 | if ( 0 < count( $where_global ) ) { |
| 831 | 891 | $where .= (( '' === trim( $where ) ? ' where ' : ' and ' )) . $this->add_condition( $where_global, 'or' ); |
| 832 | 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 | + } | |
| 833 | 912 | return $where; |
| 834 | 913 | } |
| 835 | 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 | + | |
| 836 | 943 | /** |
| 837 | 944 | * Perform query and return result as JSON response. |
| 838 | 945 | * |
| 839 | 946 | * @param string $dbs Schema name (database). |
| @@ -863,9 +970,9 @@ | ||
| 863 | 970 | $search_column_fns, |
| 864 | 971 | $sorting, |
| 865 | 972 | $last_row_count, |
| 866 | 973 | $row_count_estimate, |
| 867 | - $media_columns, | |
| 974 | + $media_columns = array(), | |
| 868 | 975 | $default_where = '', |
| 869 | 976 | $default_orderby = '', |
| 870 | 977 | $lookups = array(), |
| 871 | 978 | $md = array(), |
| @@ -870,9 +977,12 @@ | ||
| 870 | 977 | $lookups = array(), |
| 871 | 978 | $md = array(), |
| 872 | 979 | $m2m_relationship = array(), |
| 873 | 980 | $search_data_types = array(), |
| 874 | - $client_side = false | |
| 981 | + $client_side = false, | |
| 982 | + $geo_radius = array(), | |
| 983 | + $docs = array(), | |
| 984 | + $search_global = null | |
| 875 | 985 | ) { |
| 876 | 986 | $wpdadb = WPDADB::get_db_connection( $dbs ); |
| 877 | 987 | if ( null === $wpdadb ) { |
| 878 | 988 | // Error connecting. |
| @@ -891,10 +1001,32 @@ | ||
| 891 | 1001 | $column_names, |
| 892 | 1002 | $lookups, |
| 893 | 1003 | $search_columns, |
| 894 | 1004 | $search_column_fns, |
| 895 | - $search_data_types | |
| 1005 | + $search_data_types, | |
| 1006 | + $geo_radius, | |
| 1007 | + 'and' | |
| 896 | 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 | + } | |
| 897 | 1029 | // Build order by. |
| 898 | 1030 | $sqlorder = ''; |
| 899 | 1031 | if ( is_array( $sorting ) && 0 < count( $sorting ) ) { |
| 900 | 1032 | foreach ( $sorting as $sort ) { |
| @@ -902,9 +1034,31 @@ | ||
| 902 | 1034 | $sqlorder = 'order by '; |
| 903 | 1035 | } else { |
| 904 | 1036 | $sqlorder .= ','; |
| 905 | 1037 | } |
| 906 | - $sqlorder .= '`' . $this->convert_column_name( $m2m_relationship, $sort['id'] ) . '` ' . (( $sort['desc'] ? 'desc' : 'asc' )); | |
| 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 | + } | |
| 907 | 1061 | } |
| 908 | 1062 | } |
| 909 | 1063 | if ( '' === $sqlorder && '' !== trim( $default_orderby ) ) { |
| 910 | 1064 | $sqlorder = $default_orderby; |
| @@ -918,9 +1072,9 @@ | ||
| 918 | 1072 | if ( !is_numeric( $offset ) ) { |
| 919 | 1073 | $offset = 0; |
| 920 | 1074 | } |
| 921 | 1075 | // Prepare query. |
| 922 | - $sql = "\n\t\t\t\t\tselect `" . implode( "`,`", array_keys( $column_names ) ) . "`\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"; | |
| 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"; | |
| 923 | 1077 | $sql_tables = array($tbl); |
| 924 | 1078 | // Perpare query. |
| 925 | 1079 | $sql = $wpdadb->prepare( ( true === $client_side ? $sql : $sql . (( 0 < $page_size ? " limit {$page_size} offset {$offset} " : '' )) ), $sql_tables ); |
| 926 | 1080 | // Prepare debug info. |
| @@ -980,9 +1134,9 @@ | ||
| 980 | 1134 | $context = array(); |
| 981 | 1135 | if ( 'on' === WPDA::get_option( WPDA::OPTION_PLUGIN_DEBUG ) ) { |
| 982 | 1136 | $context['debug'] = $debug; |
| 983 | 1137 | } |
| 984 | - if ( 0 < count( $media_columns ) ) { | |
| 1138 | + if ( is_array( $media_columns ) && 0 < count( $media_columns ) ) { | |
| 985 | 1139 | // Handle WP media library |
| 986 | 1140 | $media = array(); |
| 987 | 1141 | for ($i = 0; $i < count( $dataset ); $i++) { |
| 988 | 1142 | $media_row = array(); |
| @@ -1044,8 +1198,9 @@ | ||
| 1044 | 1198 | $search_value, |
| 1045 | 1199 | $m2m_relationship = array(), |
| 1046 | 1200 | $search_data_types = array() |
| 1047 | 1201 | ) { |
| 1202 | + return null; | |
| 1048 | 1203 | } |
| 1049 | 1204 | |
| 1050 | 1205 | public static function add_condition( $where_lines, $operand = 'and' ) { |
| 1051 | 1206 | if ( 0 < count( array_filter( $where_lines ) ) ) { |
| @@ -1061,9 +1216,9 @@ | ||
| 1061 | 1216 | * |
| 1062 | 1217 | * @param string $dbs Database schema name. |
| 1063 | 1218 | * @param string $tbl Database table name. |
| 1064 | 1219 | * @param string $waa With admin actions. |
| 1065 | - * @return array\object | |
| 1220 | + * @return array | object | |
| 1066 | 1221 | */ |
| 1067 | 1222 | public function get_table_meta_data( $dbs, $tbl, $waa ) { |
| 1068 | 1223 | $sql_create_table = ''; |
| 1069 | 1224 | if ( WPDA::current_user_is_admin() ) { |
| @@ -1113,18 +1268,21 @@ | ||
| 1113 | 1268 | $wp_nonce_refresh = null; |
| 1114 | 1269 | $connect = null; |
| 1115 | 1270 | global $wpdb; |
| 1116 | 1271 | $settings->wp = [ |
| 1117 | - 'roles' => $this->get_wp_roles(), | |
| 1118 | - 'users' => $this->get_wp_users(), | |
| 1119 | - 'home' => admin_url( 'admin.php' ), | |
| 1120 | - 'homea' => admin_url( 'admin-ajax.php' ), | |
| 1121 | - 'tables' => array_values( $wpdb->tables() ), | |
| 1122 | - 'date_format' => get_option( 'date_format' ), | |
| 1123 | - 'time_format' => get_option( 'time_format' ), | |
| 1124 | - 'alter' => $wp_nonce_alter, | |
| 1125 | - 'refresh' => $wp_nonce_refresh, | |
| 1126 | - 'connect' => $connect, | |
| 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' ), | |
| 1127 | 1285 | ]; |
| 1128 | 1286 | if ( true === $waa ) { |
| 1129 | 1287 | $settings->wp['aonce'] = implode( '-', array( |
| 1130 | 1288 | wp_create_nonce( 'wpda-export-' . json_encode( $tbl ) ), |
| @@ -1131,26 +1289,34 @@ | ||
| 1131 | 1289 | // Table export |
| 1132 | 1290 | wp_create_nonce( 'wpda-rename-' . $tbl ), |
| 1133 | 1291 | ) ); |
| 1134 | 1292 | } |
| 1135 | - $media = $this->get_media( $dbs, $tbl, $columns->get_table_columns() ); | |
| 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 | + } | |
| 1136 | 1301 | } |
| 1137 | 1302 | return array( |
| 1138 | - 'columns' => $columns->get_table_columns(), | |
| 1139 | - 'table_labels' => $columns->get_table_header_labels(), | |
| 1140 | - 'form_labels' => $columns->get_table_column_headers(), | |
| 1141 | - 'primary_key' => $columns->get_table_primary_key(), | |
| 1142 | - 'access' => $access, | |
| 1143 | - 'settings' => $settings, | |
| 1144 | - 'media' => $media['media'], | |
| 1145 | - 'wp_media' => $media['wp_media'], | |
| 1146 | - 'table_info' => $this->get_table_info( $dbs, $tbl ), | |
| 1147 | - 'create' => $sql_create_table, | |
| 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, | |
| 1148 | 1314 | ); |
| 1149 | 1315 | } |
| 1150 | 1316 | |
| 1151 | 1317 | private function get_table_access( $dbs, $tbl ) { |
| 1152 | - if ( current_user_can( 'manage_options' ) ) { | |
| 1318 | + if ( WPDA::current_user_is_admin() ) { | |
| 1153 | 1319 | // Check administrator rights |
| 1154 | 1320 | if ( is_admin() ) { |
| 1155 | 1321 | $access = WPDA_Dictionary_Access::check_table_access_backend( $dbs, $tbl, $done ); |
| 1156 | 1322 | } else { |
| @@ -1188,9 +1354,9 @@ | ||
| 1188 | 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'] ) ) { |
| 1189 | 1355 | return $table[$action]['methods']; |
| 1190 | 1356 | } |
| 1191 | 1357 | // Check authorized roles |
| 1192 | - 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'] ) ) ) { | |
| 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'] ) ) ) { | |
| 1193 | 1359 | return $table[$action]['methods']; |
| 1194 | 1360 | } |
| 1195 | 1361 | } |
| 1196 | 1362 | } |
| @@ -1201,9 +1367,9 @@ | ||
| 1201 | 1367 | * Check if access is grant for requested database/table. |
| 1202 | 1368 | * |
| 1203 | 1369 | * @param string $dbs Remote or local database connection string. |
| 1204 | 1370 | * @param string $tbl Database table name. |
| 1205 | - * @param onject $request Request object. | |
| 1371 | + * @param object $request Request object. | |
| 1206 | 1372 | * @param string $action Possible values: select, insert, update, delete. |
| 1207 | 1373 | * @return bool |
| 1208 | 1374 | */ |
| 1209 | 1375 | private function check_table_access( |
| @@ -1228,9 +1394,9 @@ | ||
| 1228 | 1394 | $msg = __( 'Unauthorized', 'wp-data-access' ); |
| 1229 | 1395 | return false; |
| 1230 | 1396 | } else { |
| 1231 | 1397 | if ( !in_array( $request->get_method(), $tables[$dbs][$tbl][$action]['methods'] ) ) { |
| 1232 | - //phpcs:ignore - 8.1 proof | |
| 1398 | + // phpcs:ignore -- 8.1 proof | |
| 1233 | 1399 | $msg = __( 'Unauthorized', 'wp-data-access' ); |
| 1234 | 1400 | return false; |
| 1235 | 1401 | } |
| 1236 | 1402 | } |
