PluginProbe
WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards / 5.5.23
WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards v5.5.23
5.5.83 5.5.82 5.5.81 5.5.80 5.5.79 5.5.77 5.5.76 5.5.75 5.5.73 5.5.72 5.5.22 5.5.23 5.5.29 5.5.3 5.5.31 5.5.32 5.5.34 5.5.35 5.5.36 5.5.37 5.5.4 5.5.40 5.5.41 5.5.42 5.5.43 All 159 releases
wp-data-access / WPDataAccess / Plugin_Table_Models / WPDA_Design_Table_Model.php

WPDA_Design_Table_Model.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 5.5.23, at WPDataAccess/Plugin_Table_Models/WPDA_Design_Table_Model.php

537 lines 15.2 KB
No matching file
Up and down to move Enter to open Esc to close
Raw Download Zip
1 <?php
2
3 /**
4 * Suppress "error - 0 - No summary was found for this file" on phpdoc generation
5 *
6 * @package WPDataAccess\Plugin_Table_Models
7 */
8
9 namespace WPDataAccess\Plugin_Table_Models {
10
11 use WPDataAccess\WPDA;
12
13 /**
14 * Class WPDA_Design_Table_Model
15 *
16 * @author Peter Schulz
17 * @since 1.1.0
18 */
19 class WPDA_Design_Table_Model extends WPDA_Plugin_Table_Base_Model {
20
21 const BASE_TABLE_NAME = 'wpda_table_design';
22
23 /**
24 * Name of table where design is stored
25 *
26 * @var string|null
27 */
28 protected $table_name = null;
29
30 /**
31 * Design table name
32 *
33 * @var string|null
34 */
35 protected $wpda_table_name = null;
36
37 /**
38 * Original design table name
39 *
40 * Used to detect a table name update.
41 *
42 * @var string|null
43 */
44 protected $wpda_table_name_original = null;
45
46 /**
47 * Design database name
48 *
49 * @var string|null
50 */
51 protected $wpda_schema_name = null;
52
53 /**
54 * Original design database name
55 *
56 * Used to detect a database name update.
57 *
58 * @var string|null
59 */
60 protected $wpda_schema_name_original = null;
61
62 /**
63 * The actual table design
64 *
65 * @var string|null
66 */
67 protected $wpda_table_design = null;
68
69 /**
70 * WPDA_Design_Table_Model constructor
71 *
72 * @since 1.1.0
73 */
74 public function __construct() {
75 $this->table_name = self::get_base_table_name();
76
77 // Watch out for arrays! (array = starting export)
78 if ( isset( $_REQUEST['wpda_table_name'] ) && ! is_array( $_REQUEST['wpda_table_name'] ) ) {
79 $this->wpda_table_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name'] ) );
80 }
81
82 if ( isset( $_REQUEST['wpda_table_name_original'] ) ) {
83 $this->wpda_table_name_original = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name_original'] ) );
84 }
85
86 if ( isset( $_REQUEST['wpda_schema_name'] ) && ! is_array( $_REQUEST['wpda_schema_name'] ) ) {
87 $this->wpda_schema_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name'] ) );
88 }
89
90 if ( isset( $_REQUEST['wpda_schema_name_original'] ) ) {
91 $this->wpda_schema_name_original = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name_original'] ) );
92 }
93 }
94
95 /**
96 * Get design for a specific table name
97 *
98 * @param string|null $wpda_table_name_original Table name to be queried. If null query 'wpda_table_name'.
99 * @param string|null $wpda_schema_name_original Schema name to be queried. If null query 'wpda_schema_name'.
100 *
101 * @return int Number of rows.
102 */
103 public function query( $wpda_table_name_original = null, $wpda_schema_name_original = null ) {
104 if ( null === $this->wpda_table_name || null === $this->wpda_schema_name ) {
105 return false;
106 }
107
108 global $wpdb;
109 $wpda_table_design_raw = $wpdb->get_results(
110 $wpdb->prepare(
111 'SELECT wpda_table_design FROM `%1s` WHERE wpda_table_name = %s AND wpda_schema_name = %s', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
112 array(
113 WPDA::remove_backticks( $this->table_name ),
114 $wpda_table_name_original === null ? $this->wpda_table_name : $wpda_table_name_original,
115 $wpda_schema_name_original === null ? $this->wpda_schema_name : $wpda_schema_name_original,
116 )
117 ),
118 'ARRAY_A'
119 );
120
121 if ( 1 === $wpdb->num_rows ) {
122 $this->wpda_table_design = json_decode( $wpda_table_design_raw[0]['wpda_table_design'] );
123 }
124
125 return $wpdb->num_rows;
126 }
127
128 /**
129 * Return table design
130 *
131 * @return null|array
132 */
133 public function get_table_design() {
134
135 return $this->wpda_table_design;
136
137 }
138
139 /**
140 * Perform validations and return array containing errors
141 *
142 * @return array List of errors found.
143 */
144 public function validate() {
145 $structure_messages = array();
146
147 if ( ! isset( $this->wpda_table_design->design_mode ) ) {
148 $structure_messages[] = array( 'ERR', 'Invalid structure [missing element design_mode]' );
149 }
150 if ( ! isset( $this->wpda_table_design->engine )
151 && (
152 ! isset( $this->wpda_table_design->table_type )
153 ||
154 ( isset( $this->wpda_table_design->table_type ) && 'TABLE' === $this->wpda_table_design->table_type )
155 )
156 ) {
157 $structure_messages[] = array( 'ERR', 'Invalid structure [missing element engine]' );
158 }
159 if ( ! isset( $this->wpda_table_design->collation )
160 && (
161 ! isset( $this->wpda_table_design->table_type )
162 ||
163 ( isset( $this->wpda_table_design->table_type ) && 'TABLE' === $this->wpda_table_design->table_type )
164 )
165 ) {
166 $structure_messages[] = array( 'ERR', 'Invalid structure [missing element collation]' );
167 }
168 if ( ! isset( $this->wpda_table_design->table ) ) {
169 $structure_messages[] = array( 'ERR', 'Invalid structure [missing element table]' );
170 } else {
171 $unique_column_names = array();
172 foreach ( $this->wpda_table_design->table as $column ) {
173 $unique_column_names[ $column->column_name ] = true;
174 }
175 if ( count( $unique_column_names ) !== count( $this->wpda_table_design->table ) ) {//phpcs:ignore - 8.1 proof
176 $structure_messages[] = array( 'ERR', 'Column name must be unique within a table' );
177 }
178 }
179 if ( ! isset( $this->wpda_table_design->indexes ) ) {
180 $structure_messages[] = array( 'ERR', 'Invalid structure [missing element indexes]' );
181 } else {
182 $unique_index_names = array();
183 foreach ( $this->wpda_table_design->indexes as $index ) {
184 $unique_index_names[ $index->index_name ] = true;
185 }
186 if ( count( $unique_index_names ) !== count( $this->wpda_table_design->indexes ) ) {//phpcs:ignore - 8.1 proof
187 $structure_messages[] = array( 'ERR', 'Index name must be unique within a table' );
188 }
189 }
190 return $structure_messages;
191
192 }
193
194 /**
195 * Prepare insert statement (process arguments)
196 *
197 * @since 1.1.0
198 */
199 public function prepare_insert() {
200 $this->wpda_table_design = (object) null;
201
202 if ( isset( $_REQUEST['design_mode'] ) ) {
203 $this->wpda_table_design->design_mode = sanitize_text_field( wp_unslash( $_REQUEST['design_mode'] ) );
204 } else {
205 $this->wpda_table_design->design_mode = WPDA::get_option( WPDA::OPTION_BE_DESIGN_MODE );
206 }
207
208 if ( isset( $_REQUEST['engine'] ) ) {
209 $this->wpda_table_design->engine = sanitize_text_field( wp_unslash( $_REQUEST['engine'] ) );
210 } else {
211 $this->wpda_table_design->engine = '';
212 }
213
214 if ( isset( $_REQUEST['collation'] ) ) {
215 $this->wpda_table_design->collation = sanitize_text_field( wp_unslash( $_REQUEST['collation'] ) );
216 } else {
217 $this->wpda_table_design->collation = '';
218 }
219
220 $this->wpda_table_design->table = $this->get_table_structure();
221
222 $this->wpda_table_design->indexes = array();
223 }
224
225 /**
226 * Add a new table design
227 *
228 * @return bool TRUE = insert successful, FALSE ; insert failed.
229 * @since 1.1.0
230 */
231 public function insert() {
232 if ( null === $this->wpda_table_name || null === $this->wpda_schema_name ) {
233 return false;
234 }
235
236 $this->prepare_insert();
237
238 $this->wpda_table_design->table_type = self::get_table_type( $this->table_name, $this->wpda_schema_name );
239
240 global $wpdb;
241
242 return $wpdb->insert(
243 $this->table_name,
244 array(
245 'wpda_table_name' => $this->wpda_table_name,
246 'wpda_schema_name' => $this->wpda_schema_name,
247 'wpda_table_design' => json_encode( $this->wpda_table_design ),
248 )
249 );
250
251 }
252
253 /**
254 * Get structure of table design from post variables
255 *
256 * @return array Table design.
257 * @since 1.1.0
258 */
259 protected function get_table_structure() {
260 $table_structure = array();
261
262 if ( isset( $_REQUEST['column_name'] ) &&
263 is_array( $_REQUEST['column_name'] )) {//phpcs:ignore - 8.1 proof
264 $no_columns = count( $_REQUEST['column_name'] );//phpcs:ignore - 8.1 proof
265 for ( $i = 0; $i < $no_columns; $i ++ ) {
266 if ( isset( $_REQUEST['row_action'][ $i ] ) && 'd' === $_REQUEST['row_action'][ $i ] ) {
267 // Do not process deleted rows.
268 continue;
269 }
270
271 $column_name = sanitize_text_field( wp_unslash( $_REQUEST['column_name'][ $i ] ) );
272 $data_type = sanitize_text_field( wp_unslash( $_REQUEST['data_type'][ $i ] ) );
273 $type_attribute = sanitize_text_field( wp_unslash( $_REQUEST['type_attribute'][ $i ] ) );
274 $key = sanitize_text_field( wp_unslash( $_REQUEST['key'][ $i ] ) );
275 $mandatory = sanitize_text_field( wp_unslash( $_REQUEST['mandatory'][ $i ] ) );
276 $max_length = sanitize_text_field( wp_unslash( $_REQUEST['max_length'][ $i ] ) );
277 $extra = sanitize_text_field( wp_unslash( $_REQUEST['extra'][ $i ] ) );
278 $default = sanitize_text_field( wp_unslash( $_REQUEST['default'][ $i ] ) );
279 $list = sanitize_text_field( wp_unslash( $_REQUEST['list'][ $i ] ) );
280
281 $table_structure[ $i ]['column_name'] = $column_name;
282 $table_structure[ $i ]['data_type'] = $data_type;
283 $table_structure[ $i ]['type_attribute'] = $type_attribute;
284 $table_structure[ $i ]['key'] = $key;
285 $table_structure[ $i ]['mandatory'] = $mandatory;
286 $table_structure[ $i ]['max_length'] = $max_length;
287 $table_structure[ $i ]['extra'] = $extra;
288 $table_structure[ $i ]['default'] = $default;
289 $table_structure[ $i ]['list'] = $list;
290 }
291 }
292
293 return $table_structure;
294 }
295
296 /**
297 * Get table indexes from post variables
298 *
299 * @return array Index design.
300 * @since 1.1.0
301 */
302 protected function get_indexes() {
303 $indexes = array();
304
305 if ( isset( $_REQUEST['column_names'] )&&
306 is_array( $_REQUEST['column_names']) ) {
307 $no_columns = count( $_REQUEST['column_names'] );//phpcs:ignore - 8.1 proof
308 for ( $i = 0; $i < $no_columns; $i ++ ) {
309 if ( isset( $_REQUEST['row_action'][ $i ] ) && 'd' === $_REQUEST['row_action'][ $i ] ) {
310 // Do not process deleted rows.
311 continue;
312 }
313
314 $index_name = sanitize_text_field( wp_unslash( $_REQUEST['index_name'][ $i ] ) );
315 $unique = sanitize_text_field( wp_unslash( $_REQUEST['unique'][ $i ] ) );
316 $column_names = sanitize_text_field( wp_unslash( $_REQUEST['column_names'][ $i ] ) );
317
318 $indexes[ $i ]['index_name'] = $index_name;
319 $indexes[ $i ]['unique'] = $unique;
320 $indexes[ $i ]['column_names'] = $column_names;
321 }
322 }
323
324 return $indexes;
325 }
326
327 /**
328 * Prepare update statement
329 *
330 * @since 1.1.0
331 *
332 * Get table structure from database and update it using the arguments in the request.
333 */
334 public function prepare_update() {
335 $this->query(
336 $this->wpda_table_name_original === $this->wpda_table_name ? null : $this->wpda_table_name_original,
337 $this->wpda_schema_name_original === $this->wpda_schema_name ? null : $this->wpda_schema_name_original
338 );
339
340 if ( isset( $_REQUEST['design_mode'] ) ) {
341 $this->wpda_table_design->design_mode = sanitize_text_field( wp_unslash( $_REQUEST['design_mode'] ) );
342 }
343
344 if ( isset( $_REQUEST['engine'] ) ) {
345 $this->wpda_table_design->engine = sanitize_text_field( wp_unslash( $_REQUEST['engine'] ) );
346 }
347
348 if ( isset( $_REQUEST['collation'] ) ) {
349 $this->wpda_table_design->collation = sanitize_text_field( wp_unslash( $_REQUEST['collation'] ) );
350 }
351
352 if ( isset( $_REQUEST['column_name'] ) ) {
353 $this->wpda_table_design->table = $this->get_table_structure();
354 } else {
355 if ( isset( $_REQUEST['submitted_changes'] ) && 'table' === $_REQUEST['submitted_changes'] ) {
356 $this->wpda_table_design->table = array();
357 }
358 }
359
360 if ( isset( $_REQUEST['column_names'] ) ) {
361 $this->wpda_table_design->indexes = $this->get_indexes();
362 } else {
363 if ( isset( $_REQUEST['submitted_changes'] ) && 'indexes' === $_REQUEST['submitted_changes'] ) {
364 $this->wpda_table_design->indexes = array();
365 }
366 }
367 }
368
369 /**
370 * Update table design
371 *
372 * @return bool TRUE = update successful, FALSE ; update failed.
373 * @since 1.1.0
374 */
375 public function update() {
376 if ( null === $this->wpda_table_name || null === $this->wpda_schema_name ) {
377 return false;
378 }
379
380 $this->prepare_update();
381
382 global $wpdb;
383
384 return $wpdb->update(
385 $this->table_name,
386 array(
387 'wpda_table_name' => $this->wpda_table_name,
388 'wpda_schema_name' => $this->wpda_schema_name,
389 'wpda_table_design' => json_encode( $this->wpda_table_design ),
390 ),
391 array(
392 'wpda_table_name' => $this->wpda_table_name_original === $this->wpda_table_name ? $this->wpda_table_name : $this->wpda_table_name_original,
393 'wpda_schema_name' => $this->wpda_schema_name_original === $this->wpda_schema_name ? $this->wpda_schema_name : $this->wpda_schema_name_original,
394 )
395 );
396
397 }
398
399 /**
400 * Simple list containing all table names in data designer table
401 *
402 * @return array
403 */
404 public static function get_designer_table_list() {
405 global $wpdb;
406 return $wpdb->get_results(
407 $wpdb->prepare(
408 'SELECT `wpda_table_name` FROM `%1s`', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
409 array(
410 WPDA::remove_backticks( self::get_base_table_name() ),
411 )
412 ),
413 'ARRAY_A'
414 );
415 }
416
417 /**
418 * Reverse engineer table and store result in data designer table
419 *
420 * @param $wpda_table_name
421 * @param $wpda_schema_name
422 * @param $wpda_table_design
423 *
424 * @return bool
425 */
426 public static function insert_reverse_engineered( $wpda_table_name, $wpda_schema_name, $wpda_table_design ) {
427 global $wpdb;
428
429 $table_name = self::get_base_table_name();
430
431 $wpda_table_design['table_type'] = self::get_table_type( $wpda_table_name, $wpda_schema_name );
432
433 return (
434 1 === $wpdb->insert(
435 $table_name,
436 array(
437 'wpda_table_name' => $wpda_table_name,
438 'wpda_schema_name' => $wpda_schema_name,
439 'wpda_table_design' => json_encode( $wpda_table_design ),
440 )
441 )
442 );
443 }
444
445 /**
446 * Get table type from MySQL dictionary
447 *
448 * @param string $schema_name Schema name
449 * @param string $table_name Table name
450 *
451 * @return bool
452 */
453 public static function get_table_type( $table_name, $schema_name ) {
454 global $wpdb;
455 $result = $wpdb->get_results(
456 $wpdb->prepare(
457 '
458 SELECT table_type AS table_type
459 FROM information_schema.tables
460 WHERE table_schema = %s
461 AND table_name = %s
462 ',
463 array(
464 $schema_name,
465 $table_name,
466 )
467 ), // db call ok; no-cache ok.
468 'ARRAY_A'
469 ); // phpcs:ignore Standard.Category.SniffName.ErrorCode
470
471 if ( 1 !== $wpdb->num_rows ) {
472 return false;
473 } else {
474 if ( stripos( $result[0]['table_type'], 'view' ) === false ) {
475 return 'TABLE';
476 } else {
477 return 'VIEW';
478 }
479 }
480 }
481
482 /**
483 * Convert MySQL data type to basic data type (used in basic mode)
484 *
485 * @param string $arg MySQL data type.
486 *
487 * @return string Basic data type.
488 * @since 1.6.0
489 */
490 public static function datatype2basic( $arg ) {
491 switch ( $arg ) {
492 case 'tinyint':
493 case 'smallint':
494 case 'mediumint':
495 case 'int':
496 case 'integer':
497 case 'bigint':
498 case 'year':
499 return 'Integer';
500
501 case 'decimal':
502 case 'float':
503 case 'double':
504 case 'real':
505 return 'Real';
506
507 case 'bool':
508 case 'boolean':
509 return 'Boolean';
510
511 case 'date':
512 case 'time':
513 case 'datetime':
514 case 'timestamp':
515 return 'Datetime';
516
517 case 'enum':
518 case 'set':
519 return 'List';
520
521 case 'binary':
522 case 'varbinary':
523 case 'tinyblob':
524 case 'blob':
525 case 'mediumblob':
526 case 'longblob':
527 return 'Binary';
528
529 default:
530 return 'Text';
531 }
532 }
533
534 }
535
536 }
537