| 1 |
<?php |
| 2 |
|
| 3 |
namespace WPDataAccess\API; |
| 4 |
|
| 5 |
use WPDataAccess\Connection\WPDADB; |
| 6 |
use WPDataAccess\WPDA; |
| 7 |
class WPDA_Tree extends WPDA_API_Core { |
| 8 |
public function register_rest_routes() { |
| 9 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/dbs', array( |
| 10 |
'methods' => array('POST'), |
| 11 |
'callback' => array($this, 'tree_dbs'), |
| 12 |
'permission_callback' => '__return_true', |
| 13 |
) ); |
| 14 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/tbl', array( |
| 15 |
'methods' => array('POST'), |
| 16 |
'callback' => array($this, 'tree_tbl'), |
| 17 |
'permission_callback' => '__return_true', |
| 18 |
'args' => array( |
| 19 |
'dbs' => $this->get_param( 'dbs' ), |
| 20 |
), |
| 21 |
) ); |
| 22 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/vws', array( |
| 23 |
'methods' => array('POST'), |
| 24 |
'callback' => array($this, 'tree_vws'), |
| 25 |
'permission_callback' => '__return_true', |
| 26 |
'args' => array( |
| 27 |
'dbs' => $this->get_param( 'dbs' ), |
| 28 |
), |
| 29 |
) ); |
| 30 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/cls', array( |
| 31 |
'methods' => array('POST'), |
| 32 |
'callback' => array($this, 'tree_cls'), |
| 33 |
'permission_callback' => '__return_true', |
| 34 |
'args' => array( |
| 35 |
'dbs' => $this->get_param( 'dbs' ), |
| 36 |
'tbl' => $this->get_param( 'tbl' ), |
| 37 |
), |
| 38 |
) ); |
| 39 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/idx', array( |
| 40 |
'methods' => array('POST'), |
| 41 |
'callback' => array($this, 'tree_idx'), |
| 42 |
'permission_callback' => '__return_true', |
| 43 |
'args' => array( |
| 44 |
'dbs' => $this->get_param( 'dbs' ), |
| 45 |
'tbl' => $this->get_param( 'tbl' ), |
| 46 |
), |
| 47 |
) ); |
| 48 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/trg', array( |
| 49 |
'methods' => array('POST'), |
| 50 |
'callback' => array($this, 'tree_trg'), |
| 51 |
'permission_callback' => '__return_true', |
| 52 |
'args' => array( |
| 53 |
'dbs' => $this->get_param( 'dbs' ), |
| 54 |
'tbl' => $this->get_param( 'tbl' ), |
| 55 |
), |
| 56 |
) ); |
| 57 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/frk', array( |
| 58 |
'methods' => array('POST'), |
| 59 |
'callback' => array($this, 'tree_frk'), |
| 60 |
'permission_callback' => '__return_true', |
| 61 |
'args' => array( |
| 62 |
'dbs' => $this->get_param( 'dbs' ), |
| 63 |
'tbl' => $this->get_param( 'tbl' ), |
| 64 |
), |
| 65 |
) ); |
| 66 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/fnc', array( |
| 67 |
'methods' => array('POST'), |
| 68 |
'callback' => array($this, 'tree_fnc'), |
| 69 |
'permission_callback' => '__return_true', |
| 70 |
'args' => array( |
| 71 |
'dbs' => $this->get_param( 'dbs' ), |
| 72 |
), |
| 73 |
) ); |
| 74 |
register_rest_route( WPDA_API::WPDA_NAMESPACE, 'tree/prc', array( |
| 75 |
'methods' => array('POST'), |
| 76 |
'callback' => array($this, 'tree_prc'), |
| 77 |
'permission_callback' => '__return_true', |
| 78 |
'args' => array( |
| 79 |
'dbs' => $this->get_param( 'dbs' ), |
| 80 |
), |
| 81 |
) ); |
| 82 |
} |
| 83 |
|
| 84 |
public function tree_dbs( $request ) { |
| 85 |
if ( !$this->current_user_can_access() ) { |
| 86 |
return $this->unauthorized(); |
| 87 |
} |
| 88 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 89 |
return $this->invalid_nonce(); |
| 90 |
} |
| 91 |
return $this->get_dbs(); |
| 92 |
} |
| 93 |
|
| 94 |
public function tree_tbl( $request ) { |
| 95 |
if ( !$this->current_user_can_access() ) { |
| 96 |
return $this->unauthorized(); |
| 97 |
} |
| 98 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 99 |
return $this->invalid_nonce(); |
| 100 |
} |
| 101 |
$dbs = $request->get_param( 'dbs' ); |
| 102 |
return $this->get_tbl( $dbs ); |
| 103 |
} |
| 104 |
|
| 105 |
public function tree_vws( $request ) { |
| 106 |
if ( !$this->current_user_can_access() ) { |
| 107 |
return $this->unauthorized(); |
| 108 |
} |
| 109 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 110 |
return $this->invalid_nonce(); |
| 111 |
} |
| 112 |
$dbs = $request->get_param( 'dbs' ); |
| 113 |
return $this->get_vws( $dbs ); |
| 114 |
} |
| 115 |
|
| 116 |
public function tree_cls( $request ) { |
| 117 |
if ( !$this->current_user_can_access() ) { |
| 118 |
return $this->unauthorized(); |
| 119 |
} |
| 120 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 121 |
return $this->invalid_nonce(); |
| 122 |
} |
| 123 |
$dbs = $request->get_param( 'dbs' ); |
| 124 |
$tbl = $request->get_param( 'tbl' ); |
| 125 |
return $this->get_cls( $dbs, $tbl ); |
| 126 |
} |
| 127 |
|
| 128 |
public function tree_idx( $request ) { |
| 129 |
if ( !$this->current_user_can_access() ) { |
| 130 |
return $this->unauthorized(); |
| 131 |
} |
| 132 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 133 |
return $this->invalid_nonce(); |
| 134 |
} |
| 135 |
$dbs = $request->get_param( 'dbs' ); |
| 136 |
$tbl = $request->get_param( 'tbl' ); |
| 137 |
return $this->get_idx( $dbs, $tbl ); |
| 138 |
} |
| 139 |
|
| 140 |
public function tree_trg( $request ) { |
| 141 |
if ( !$this->current_user_can_access() ) { |
| 142 |
return $this->unauthorized(); |
| 143 |
} |
| 144 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 145 |
return $this->invalid_nonce(); |
| 146 |
} |
| 147 |
$dbs = $request->get_param( 'dbs' ); |
| 148 |
$tbl = $request->get_param( 'tbl' ); |
| 149 |
return $this->get_trg( $dbs, $tbl ); |
| 150 |
} |
| 151 |
|
| 152 |
public function tree_frk( $request ) { |
| 153 |
if ( !$this->current_user_can_access() ) { |
| 154 |
return $this->unauthorized(); |
| 155 |
} |
| 156 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 157 |
return $this->invalid_nonce(); |
| 158 |
} |
| 159 |
$dbs = $request->get_param( 'dbs' ); |
| 160 |
$tbl = $request->get_param( 'tbl' ); |
| 161 |
return $this->get_frk( $dbs, $tbl ); |
| 162 |
} |
| 163 |
|
| 164 |
public function tree_fnc( $request ) { |
| 165 |
if ( !$this->current_user_can_access() ) { |
| 166 |
return $this->unauthorized(); |
| 167 |
} |
| 168 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 169 |
return $this->invalid_nonce(); |
| 170 |
} |
| 171 |
$dbs = $request->get_param( 'dbs' ); |
| 172 |
return $this->get_fnc( $dbs ); |
| 173 |
} |
| 174 |
|
| 175 |
public function tree_prc( $request ) { |
| 176 |
if ( !$this->current_user_can_access() ) { |
| 177 |
return $this->unauthorized(); |
| 178 |
} |
| 179 |
if ( !$this->current_user_token_valid( $request ) ) { |
| 180 |
return $this->invalid_nonce(); |
| 181 |
} |
| 182 |
$dbs = $request->get_param( 'dbs' ); |
| 183 |
return $this->get_prc( $dbs ); |
| 184 |
} |
| 185 |
|
| 186 |
public function get_dbs( $include_disabled = false ) { |
| 187 |
// Local databases |
| 188 |
global $wpdb; |
| 189 |
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 190 |
$local = $wpdb->get_results( "\n\t\t\t\t\tSELECT schema_name AS dbs,\n\t\t\t\t\t\t\t'local' AS dbs_type,\n\t\t\t\t\t\t\t'false' AS pds\n\t\t\t\t\t FROM information_schema.schemata\n\t\t\t\t\t ORDER BY schema_name\n\t\t\t \t", 'ARRAY_A' ); |
| 191 |
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching |
| 192 |
for ($i = 0; $i < count( $local ); $i++) { |
| 193 |
if ( $wpdb->dbname === $local[$i]['dbs'] ) { |
| 194 |
// WordPress database |
| 195 |
$local[$i]['dbs_type'] = 'wp'; |
| 196 |
} |
| 197 |
// System databases |
| 198 |
if ( 'information_schema' === $local[$i]['dbs'] || 'mysql' === $local[$i]['dbs'] || 'performance_schema' === $local[$i]['dbs'] || 'sys' === $local[$i]['dbs'] ) { |
| 199 |
// WordPress database |
| 200 |
$local[$i]['dbs_type'] = 'system'; |
| 201 |
} |
| 202 |
$local[$i]['pds'] = false; |
| 203 |
} |
| 204 |
// Remote databases |
| 205 |
$remote = array(); |
| 206 |
$pds = array(); |
| 207 |
$rdb = WPDADB::get_remote_databases( $include_disabled ); |
| 208 |
foreach ( $rdb as $key => $val ) { |
| 209 |
$remote[] = array( |
| 210 |
'dbs' => $key, |
| 211 |
'dbs_type' => 'remote', |
| 212 |
'pds' => isset( $pds[substr( $key, 4 )] ), |
| 213 |
); |
| 214 |
} |
| 215 |
global $wpdb; |
| 216 |
$context = array( |
| 217 |
'env' => $this->get_env(), |
| 218 |
'wp' => [ |
| 219 |
'roles' => $this->get_wp_roles(), |
| 220 |
'users' => $this->get_wp_users(), |
| 221 |
'home' => admin_url( 'admin.php' ), |
| 222 |
'tables' => array_values( $wpdb->tables() ), |
| 223 |
'nonce' => implode( '-', array(wp_create_nonce( 'wpda-export-' . WPDA::get_current_user_login() )) ), |
| 224 |
'zip' => class_exists( '\\ZipArchive' ), |
| 225 |
'upload' => @ini_get( 'upload_max_filesize' ), |
| 226 |
], |
| 227 |
); |
| 228 |
return $this->WPDA_Rest_Response( |
| 229 |
'', |
| 230 |
array_merge( $local, $remote ), |
| 231 |
// phpcs:ignore -- 8.1 proof |
| 232 |
$context |
| 233 |
); |
| 234 |
} |
| 235 |
|
| 236 |
public function get_tbl_vws( $dbs ) { |
| 237 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 238 |
if ( $wpdadb === null ) { |
| 239 |
return $this->WPDA_Rest_Response( '', array() ); |
| 240 |
} |
| 241 |
$query = $wpdadb->prepare( "\n\t\t\t\t\tselect table_name as table_name\n\t\t\t\t\t from information_schema.tables\n\t\t\t\t\t where table_schema = %s\n\t\t\t\t\t order by table_name\n\t\t\t\t", array($wpdadb->dbname) ); |
| 242 |
return $this->WPDA_Rest_Response( '', array_column( $wpdadb->get_results( $query, 'ARRAY_A' ), 'table_name' ) ); |
| 243 |
} |
| 244 |
|
| 245 |
private function get_tbl( $dbs ) { |
| 246 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 247 |
if ( $wpdadb === null ) { |
| 248 |
return $this->WPDA_Rest_Response( '', array() ); |
| 249 |
} |
| 250 |
$query = $wpdadb->prepare( "\n\t\t\t\t\tselect table_name as table_name\n\t\t\t\t\t from information_schema.tables\n\t\t\t\t\t where table_schema = %s\n\t\t\t\t\t and table_type not in ('VIEW', 'SYSTEM VIEW')\n\t\t\t\t\t order by table_name\n\t\t\t\t", array($wpdadb->dbname) ); |
| 251 |
$context = null; |
| 252 |
global $wpdb; |
| 253 |
if ( $wpdadb->dbname === $wpdb->dbname ) { |
| 254 |
$context = array( |
| 255 |
'wp_tables' => array_keys( WPDA::get_wp_tables() ), |
| 256 |
'wpda_tables' => WPDA::get_wpda_tables(), |
| 257 |
); |
| 258 |
} |
| 259 |
return $this->WPDA_Rest_Response( '', array_column( $wpdadb->get_results( $query, 'ARRAY_A' ), 'table_name' ), $context ); |
| 260 |
} |
| 261 |
|
| 262 |
private function get_vws( $dbs ) { |
| 263 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 264 |
if ( $wpdadb === null ) { |
| 265 |
return $this->WPDA_Rest_Response( '', array() ); |
| 266 |
} |
| 267 |
$query = $wpdadb->prepare( "\n\t\t\t\t\tselect table_name as view_name\n\t\t\t\t\t from information_schema.tables\n\t\t\t\t\t where table_schema = %s\n\t\t\t\t\t and table_type in ('VIEW', 'SYSTEM VIEW')\n\t\t\t\t\t order by table_name\n\t\t\t\t", array($wpdadb->dbname) ); |
| 268 |
return $this->WPDA_Rest_Response( '', array_column( $wpdadb->get_results( $query, 'ARRAY_A' ), 'view_name' ) ); |
| 269 |
} |
| 270 |
|
| 271 |
public function get_cls( $dbs, $tbl ) { |
| 272 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 273 |
if ( $wpdadb === null ) { |
| 274 |
return $this->WPDA_Rest_Response( '', array() ); |
| 275 |
} |
| 276 |
$query = $wpdadb->prepare( "\n\t SELECT column_name AS column_name,\n\t extra AS extra,\n\t column_type AS column_type,\n\t is_nullable AS is_nullable,\n\t IF(LEFT(column_default,1)='\\'' AND RIGHT(column_default,1)='\\'', SUBSTR(column_default,2,LENGTH(column_default)-2), column_default) AS column_default,\n\t \t\t character_maximum_length AS character_maximum_length,\n\t numeric_scale AS numeric_scale,\n\t numeric_precision AS numeric_precision,\n\t ordinal_position AS ordinal_position\n\t FROM information_schema.columns\n\t WHERE table_schema = %s\n\t AND table_name = %s\n\t ORDER BY ordinal_position\n\t ", array($wpdadb->dbname, $tbl) ); |
| 277 |
return $this->WPDA_Rest_Response( '', $wpdadb->get_results( $query, 'ARRAY_A' ) ); |
| 278 |
} |
| 279 |
|
| 280 |
private function get_idx( $dbs, $tbl ) { |
| 281 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 282 |
if ( $wpdadb === null ) { |
| 283 |
return $this->WPDA_Rest_Response( '', array() ); |
| 284 |
} |
| 285 |
$query = $wpdadb->prepare( "\n\t\t\t\t\tSELECT index_name AS index_name,\n\t\t\t\t\t\t IF(non_unique=0, 'YES', 'NO') AS non_unique,\n\t\t\t\t\t\t seq_in_index AS seq_in_index,\n\t\t\t\t\t\t column_name AS column_name,\n\t\t\t\t\t\t collation AS collation,\n\t\t\t\t\t\t nullable AS nullable,\n\t\t\t\t\t\t index_type AS index_type,\n\t\t\t\t\t\t cardinality AS cardinality\n\t\t\t\t\tFROM information_schema.statistics\n\t\t\t\t\tWHERE table_schema = %s\n\t\t\t\t\t AND table_name = %s\n\t\t\t\t\tORDER BY index_name, seq_in_index\n\t\t\t\t", array($wpdadb->dbname, $tbl) ); |
| 286 |
return $this->WPDA_Rest_Response( '', $wpdadb->get_results( $query, 'ARRAY_A' ) ); |
| 287 |
} |
| 288 |
|
| 289 |
private function get_trg( $dbs, $tbl ) { |
| 290 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 291 |
if ( $wpdadb === null ) { |
| 292 |
return $this->WPDA_Rest_Response( '', array() ); |
| 293 |
} |
| 294 |
$query = $wpdadb->prepare( ' |
| 295 |
SELECT trigger_name AS trigger_name, |
| 296 |
event_manipulation AS event_manipulation, |
| 297 |
action_timing AS action_timing, |
| 298 |
action_statement AS action_statement |
| 299 |
FROM information_schema.triggers |
| 300 |
WHERE event_object_schema = %s |
| 301 |
AND event_object_table = %s |
| 302 |
ORDER BY trigger_name |
| 303 |
', array($wpdadb->dbname, $tbl) ); |
| 304 |
return $this->WPDA_Rest_Response( '', $wpdadb->get_results( $query, 'ARRAY_A' ) ); |
| 305 |
} |
| 306 |
|
| 307 |
private function get_frk( $dbs, $tbl ) { |
| 308 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 309 |
if ( $wpdadb === null ) { |
| 310 |
return $this->WPDA_Rest_Response( '', array() ); |
| 311 |
} |
| 312 |
$query = $wpdadb->prepare( ' |
| 313 |
select constraint_name AS constraint_name, |
| 314 |
column_name AS column_name, |
| 315 |
referenced_table_name AS referenced_table_name, |
| 316 |
referenced_column_name AS referenced_column_name |
| 317 |
from information_schema.key_column_usage |
| 318 |
where table_schema = %s |
| 319 |
and table_name = %s |
| 320 |
and referenced_table_name is not null |
| 321 |
order by ordinal_position |
| 322 |
', array($wpdadb->dbname, $tbl) ); |
| 323 |
return $this->WPDA_Rest_Response( '', $wpdadb->get_results( $query, 'ARRAY_A' ) ); |
| 324 |
} |
| 325 |
|
| 326 |
private function get_prc( $dbs ) { |
| 327 |
return $this->get_rtn( $dbs, 'PROCEDURE' ); |
| 328 |
} |
| 329 |
|
| 330 |
private function get_fnc( $dbs ) { |
| 331 |
return $this->get_rtn( $dbs, 'FUNCTION' ); |
| 332 |
} |
| 333 |
|
| 334 |
private function get_rtn( $dbs, $typ ) { |
| 335 |
$wpdadb = WPDADB::get_db_connection( $dbs ); |
| 336 |
if ( $wpdadb === null ) { |
| 337 |
return $this->WPDA_Rest_Response( '', array() ); |
| 338 |
} |
| 339 |
$query = $wpdadb->prepare( ' |
| 340 |
SELECT routine_name AS routine_name, |
| 341 |
routine_definition AS routine_definition |
| 342 |
FROM information_schema.routines |
| 343 |
WHERE routine_schema = %s |
| 344 |
AND routine_type = %s |
| 345 |
', array($wpdadb->dbname, $typ) ); |
| 346 |
return $this->WPDA_Rest_Response( '', $wpdadb->get_results( $query, 'ARRAY_A' ) ); |
| 347 |
} |
| 348 |
|
| 349 |
} |
| 350 |
|
