Create New Item
Item Type
File
Folder
Item Name
Search file in folder and subfolders...
Are you sure want to rename?
optometrists
/
cache
/
cache
/
cache
/
cache
/
.wp-cli
/
wp-content
/
plugins
/
wp-data-access
/
WPDataAccess
/
Plugin_Table_Models
:
WPDA_Design_Table_Model.php
Advanced Search
Upload
New Item
Settings
Back
Back Up
Advanced Editor
Save
<?php /** * Suppress "error - 0 - No summary was found for this file" on phpdoc generation * * @package WPDataAccess\Plugin_Table_Models */ namespace WPDataAccess\Plugin_Table_Models { use WPDataAccess\WPDA; /** * Class WPDA_Design_Table_Model * * @author Peter Schulz * @since 1.1.0 */ class WPDA_Design_Table_Model extends WPDA_Plugin_Table_Base_Model { const BASE_TABLE_NAME = 'wpda_table_design'; /** * Name of table where design is stored * * @var string|null */ protected $table_name = null; /** * Design table name * * @var string|null */ protected $wpda_table_name = null; /** * Original design table name * * Used to detect a table name update. * * @var string|null */ protected $wpda_table_name_original = null; /** * Design database name * * @var string|null */ protected $wpda_schema_name = null; /** * Original design database name * * Used to detect a database name update. * * @var string|null */ protected $wpda_schema_name_original = null; /** * The actual table design * * @var string|null */ protected $wpda_table_design = null; /** * WPDA_Design_Table_Model constructor * * @since 1.1.0 */ public function __construct() { $this->table_name = self::get_base_table_name(); // Watch out for arrays! (array = starting export) if ( isset( $_REQUEST['wpda_table_name'] ) && ! is_array( $_REQUEST['wpda_table_name'] ) ) { $this->wpda_table_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name'] ) ); } if ( isset( $_REQUEST['wpda_table_name_original'] ) ) { $this->wpda_table_name_original = sanitize_text_field( wp_unslash( $_REQUEST['wpda_table_name_original'] ) ); } if ( isset( $_REQUEST['wpda_schema_name'] ) && ! is_array( $_REQUEST['wpda_schema_name'] ) ) { $this->wpda_schema_name = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name'] ) ); } if ( isset( $_REQUEST['wpda_schema_name_original'] ) ) { $this->wpda_schema_name_original = sanitize_text_field( wp_unslash( $_REQUEST['wpda_schema_name_original'] ) ); } } /** * Get design for a specific table name * * @param string|null $wpda_table_name_original Table name to be queried. If null query 'wpda_table_name'. * @param string|null $wpda_schema_name_original Schema name to be queried. If null query 'wpda_schema_name'. * * @return int Number of rows. */ public function query( $wpda_table_name_original = null, $wpda_schema_name_original = null ) { if ( null === $this->wpda_table_name || null === $this->wpda_schema_name ) { return false; } global $wpdb; $wpda_table_design_raw = $wpdb->get_results( $wpdb->prepare( 'SELECT wpda_table_design FROM `%1s` WHERE wpda_table_name = %s AND wpda_schema_name = %s', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders array( WPDA::remove_backticks( $this->table_name ), $wpda_table_name_original === null ? $this->wpda_table_name : $wpda_table_name_original, $wpda_schema_name_original === null ? $this->wpda_schema_name : $wpda_schema_name_original, ) ), 'ARRAY_A' ); if ( 1 === $wpdb->num_rows ) { $this->wpda_table_design = json_decode( $wpda_table_design_raw[0]['wpda_table_design'] ); } return $wpdb->num_rows; } /** * Return table design * * @return null|array */ public function get_table_design() { return $this->wpda_table_design; } /** * Perform validations and return array containing errors * * @return array List of errors found. */ public function validate() { $structure_messages = array(); if ( ! isset( $this->wpda_table_design->design_mode ) ) { $structure_messages[] = array( 'ERR', 'Invalid structure [missing element design_mode]' ); } if ( ! isset( $this->wpda_table_design->engine ) && ( ! isset( $this->wpda_table_design->table_type ) || ( isset( $this->wpda_table_design->table_type ) && 'TABLE' === $this->wpda_table_design->table_type ) ) ) { $structure_messages[] = array( 'ERR', 'Invalid structure [missing element engine]' ); } if ( ! isset( $this->wpda_table_design->collation ) && ( ! isset( $this->wpda_table_design->table_type ) || ( isset( $this->wpda_table_design->table_type ) && 'TABLE' === $this->wpda_table_design->table_type ) ) ) { $structure_messages[] = array( 'ERR', 'Invalid structure [missing element collation]' ); } if ( ! isset( $this->wpda_table_design->table ) ) { $structure_messages[] = array( 'ERR', 'Invalid structure [missing element table]' ); } else { $unique_column_names = array(); foreach ( $this->wpda_table_design->table as $column ) { $unique_column_names[ $column->column_name ] = true; } if ( count( $unique_column_names ) !== count( $this->wpda_table_design->table ) ) {//phpcs:ignore - 8.1 proof $structure_messages[] = array( 'ERR', 'Column name must be unique within a table' ); } } if ( ! isset( $this->wpda_table_design->indexes ) ) { $structure_messages[] = array( 'ERR', 'Invalid structure [missing element indexes]' ); } else { $unique_index_names = array(); foreach ( $this->wpda_table_design->indexes as $index ) { $unique_index_names[ $index->index_name ] = true; } if ( count( $unique_index_names ) !== count( $this->wpda_table_design->indexes ) ) {//phpcs:ignore - 8.1 proof $structure_messages[] = array( 'ERR', 'Index name must be unique within a table' ); } } return $structure_messages; } /** * Prepare insert statement (process arguments) * * @since 1.1.0 */ public function prepare_insert() { $this->wpda_table_design = (object) null; if ( isset( $_REQUEST['design_mode'] ) ) { $this->wpda_table_design->design_mode = sanitize_text_field( wp_unslash( $_REQUEST['design_mode'] ) ); } else { $this->wpda_table_design->design_mode = WPDA::get_option( WPDA::OPTION_BE_DESIGN_MODE ); } if ( isset( $_REQUEST['engine'] ) ) { $this->wpda_table_design->engine = sanitize_text_field( wp_unslash( $_REQUEST['engine'] ) ); } else { $this->wpda_table_design->engine = ''; } if ( isset( $_REQUEST['collation'] ) ) { $this->wpda_table_design->collation = sanitize_text_field( wp_unslash( $_REQUEST['collation'] ) ); } else { $this->wpda_table_design->collation = ''; } $this->wpda_table_design->table = $this->get_table_structure(); $this->wpda_table_design->indexes = array(); } /** * Add a new table design * * @return bool TRUE = insert successful, FALSE ; insert failed. * @since 1.1.0 */ public function insert() { if ( null === $this->wpda_table_name || null === $this->wpda_schema_name ) { return false; } $this->prepare_insert(); $this->wpda_table_design->table_type = self::get_table_type( $this->table_name, $this->wpda_schema_name ); global $wpdb; return $wpdb->insert( $this->table_name, array( 'wpda_table_name' => $this->wpda_table_name, 'wpda_schema_name' => $this->wpda_schema_name, 'wpda_table_design' => json_encode( $this->wpda_table_design ), ) ); } /** * Get structure of table design from post variables * * @return array Table design. * @since 1.1.0 */ protected function get_table_structure() { $table_structure = array(); if ( isset( $_REQUEST['column_name'] ) && is_array( $_REQUEST['column_name'] )) {//phpcs:ignore - 8.1 proof $no_columns = count( $_REQUEST['column_name'] );//phpcs:ignore - 8.1 proof for ( $i = 0; $i < $no_columns; $i ++ ) { if ( isset( $_REQUEST['row_action'][ $i ] ) && 'd' === $_REQUEST['row_action'][ $i ] ) { // Do not process deleted rows. continue; } $column_name = sanitize_text_field( wp_unslash( $_REQUEST['column_name'][ $i ] ) ); $data_type = sanitize_text_field( wp_unslash( $_REQUEST['data_type'][ $i ] ) ); $type_attribute = sanitize_text_field( wp_unslash( $_REQUEST['type_attribute'][ $i ] ) ); $key = sanitize_text_field( wp_unslash( $_REQUEST['key'][ $i ] ) ); $mandatory = sanitize_text_field( wp_unslash( $_REQUEST['mandatory'][ $i ] ) ); $max_length = sanitize_text_field( wp_unslash( $_REQUEST['max_length'][ $i ] ) ); $extra = sanitize_text_field( wp_unslash( $_REQUEST['extra'][ $i ] ) ); $default = sanitize_text_field( wp_unslash( $_REQUEST['default'][ $i ] ) ); $list = sanitize_text_field( wp_unslash( $_REQUEST['list'][ $i ] ) ); $table_structure[ $i ]['column_name'] = $column_name; $table_structure[ $i ]['data_type'] = $data_type; $table_structure[ $i ]['type_attribute'] = $type_attribute; $table_structure[ $i ]['key'] = $key; $table_structure[ $i ]['mandatory'] = $mandatory; $table_structure[ $i ]['max_length'] = $max_length; $table_structure[ $i ]['extra'] = $extra; $table_structure[ $i ]['default'] = $default; $table_structure[ $i ]['list'] = $list; } } return $table_structure; } /** * Get table indexes from post variables * * @return array Index design. * @since 1.1.0 */ protected function get_indexes() { $indexes = array(); if ( isset( $_REQUEST['column_names'] )&& is_array( $_REQUEST['column_names']) ) { $no_columns = count( $_REQUEST['column_names'] );//phpcs:ignore - 8.1 proof for ( $i = 0; $i < $no_columns; $i ++ ) { if ( isset( $_REQUEST['row_action'][ $i ] ) && 'd' === $_REQUEST['row_action'][ $i ] ) { // Do not process deleted rows. continue; } $index_name = sanitize_text_field( wp_unslash( $_REQUEST['index_name'][ $i ] ) ); $unique = sanitize_text_field( wp_unslash( $_REQUEST['unique'][ $i ] ) ); $column_names = sanitize_text_field( wp_unslash( $_REQUEST['column_names'][ $i ] ) ); $indexes[ $i ]['index_name'] = $index_name; $indexes[ $i ]['unique'] = $unique; $indexes[ $i ]['column_names'] = $column_names; } } return $indexes; } /** * Prepare update statement * * @since 1.1.0 * * Get table structure from database and update it using the arguments in the request. */ public function prepare_update() { $this->query( $this->wpda_table_name_original === $this->wpda_table_name ? null : $this->wpda_table_name_original, $this->wpda_schema_name_original === $this->wpda_schema_name ? null : $this->wpda_schema_name_original ); if ( isset( $_REQUEST['design_mode'] ) ) { $this->wpda_table_design->design_mode = sanitize_text_field( wp_unslash( $_REQUEST['design_mode'] ) ); } if ( isset( $_REQUEST['engine'] ) ) { $this->wpda_table_design->engine = sanitize_text_field( wp_unslash( $_REQUEST['engine'] ) ); } if ( isset( $_REQUEST['collation'] ) ) { $this->wpda_table_design->collation = sanitize_text_field( wp_unslash( $_REQUEST['collation'] ) ); } if ( isset( $_REQUEST['column_name'] ) ) { $this->wpda_table_design->table = $this->get_table_structure(); } else { if ( isset( $_REQUEST['submitted_changes'] ) && 'table' === $_REQUEST['submitted_changes'] ) { $this->wpda_table_design->table = array(); } } if ( isset( $_REQUEST['column_names'] ) ) { $this->wpda_table_design->indexes = $this->get_indexes(); } else { if ( isset( $_REQUEST['submitted_changes'] ) && 'indexes' === $_REQUEST['submitted_changes'] ) { $this->wpda_table_design->indexes = array(); } } } /** * Update table design * * @return bool TRUE = update successful, FALSE ; update failed. * @since 1.1.0 */ public function update() { if ( null === $this->wpda_table_name || null === $this->wpda_schema_name ) { return false; } $this->prepare_update(); global $wpdb; return $wpdb->update( $this->table_name, array( 'wpda_table_name' => $this->wpda_table_name, 'wpda_schema_name' => $this->wpda_schema_name, 'wpda_table_design' => json_encode( $this->wpda_table_design ), ), array( 'wpda_table_name' => $this->wpda_table_name_original === $this->wpda_table_name ? $this->wpda_table_name : $this->wpda_table_name_original, 'wpda_schema_name' => $this->wpda_schema_name_original === $this->wpda_schema_name ? $this->wpda_schema_name : $this->wpda_schema_name_original, ) ); } /** * Simple list containing all table names in data designer table * * @return array */ public static function get_designer_table_list() { global $wpdb; return $wpdb->get_results( $wpdb->prepare( 'SELECT `wpda_table_name` FROM `%1s`', // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders array( WPDA::remove_backticks( self::get_base_table_name() ), ) ), 'ARRAY_A' ); } /** * Reverse engineer table and store result in data designer table * * @param $wpda_table_name * @param $wpda_schema_name * @param $wpda_table_design * * @return bool */ public static function insert_reverse_engineered( $wpda_table_name, $wpda_schema_name, $wpda_table_design ) { global $wpdb; $table_name = self::get_base_table_name(); $wpda_table_design['table_type'] = self::get_table_type( $wpda_table_name, $wpda_schema_name ); return ( 1 === $wpdb->insert( $table_name, array( 'wpda_table_name' => $wpda_table_name, 'wpda_schema_name' => $wpda_schema_name, 'wpda_table_design' => json_encode( $wpda_table_design ), ) ) ); } /** * Get table type from MySQL dictionary * * @param string $schema_name Schema name * @param string $table_name Table name * * @return bool */ public static function get_table_type( $table_name, $schema_name ) { global $wpdb; $result = $wpdb->get_results( $wpdb->prepare( ' SELECT table_type AS table_type FROM information_schema.tables WHERE table_schema = %s AND table_name = %s ', array( $schema_name, $table_name, ) ), // db call ok; no-cache ok. 'ARRAY_A' ); // phpcs:ignore Standard.Category.SniffName.ErrorCode if ( 1 !== $wpdb->num_rows ) { return false; } else { if ( stripos( $result[0]['table_type'], 'view' ) === false ) { return 'TABLE'; } else { return 'VIEW'; } } } /** * Convert MySQL data type to basic data type (used in basic mode) * * @param string $arg MySQL data type. * * @return string Basic data type. * @since 1.6.0 */ public static function datatype2basic( $arg ) { switch ( $arg ) { case 'tinyint': case 'smallint': case 'mediumint': case 'int': case 'integer': case 'bigint': case 'year': return 'Integer'; case 'decimal': case 'float': case 'double': case 'real': return 'Real'; case 'bool': case 'boolean': return 'Boolean'; case 'date': case 'time': case 'datetime': case 'timestamp': return 'Datetime'; case 'enum': case 'set': return 'List'; case 'binary': case 'varbinary': case 'tinyblob': case 'blob': case 'mediumblob': case 'longblob': return 'Binary'; default: return 'Text'; } } } }