Context Navigation

← Previous Changeset
Next Changeset →

Changeset 2352101

Timestamp:

08/04/2020 03:52:48 AM (6 years ago)

Author:

pressmaninc

Message:

v2.0.0

Location:

Files:

: 18 added
: 10 edited
: 1 copied

tags/2.0.0 (copied) (copied from beyond-wpdb/trunk)
tags/2.0.0/beyond-wpdb.php (modified) (2 diffs)
tags/2.0.0/class/admin-ajax.php (added)
tags/2.0.0/class/information.php (added)
tags/2.0.0/class/meta-query.php (modified) (1 diff)
tags/2.0.0/class/options.php (modified) (14 diffs)
tags/2.0.0/class/register.php (added)
tags/2.0.0/class/sql.php (modified) (25 diffs)
tags/2.0.0/js (added)
tags/2.0.0/js/beyond-wpdb.js (added)
tags/2.0.0/lang (added)
tags/2.0.0/lang/beyond-wpdb-ja.po (added)
tags/2.0.0/lang/beyond-wpdb.pot (added)
tags/2.0.0/lang/ja.mo (added)
tags/2.0.0/readme.txt (modified) (8 diffs)
trunk/beyond-wpdb.php (modified) (2 diffs)
trunk/class/admin-ajax.php (added)
trunk/class/information.php (added)
trunk/class/meta-query.php (modified) (1 diff)
trunk/class/options.php (modified) (14 diffs)
trunk/class/register.php (added)
trunk/class/sql.php (modified) (25 diffs)
trunk/js (added)
trunk/js/beyond-wpdb.js (added)
trunk/lang (added)
trunk/lang/beyond-wpdb-ja.po (added)
trunk/lang/beyond-wpdb.pot (added)
trunk/lang/ja.mo (added)
trunk/readme.txt (modified) (8 diffs)

Legend:

: Unmodified
: Added
: Removed

beyond-wpdb/tags/2.0.0/beyond-wpdb.php

-                      r2337801
+                      r2352101
 Plugin Name: Beyond Wpdb
 Plugin URI:
 Description: Create a new table and register the meta data in a single row of JSON columns.Registration is done by mysql's trigger function.The data in WordPress is not affected.Convert sql statement, when searching for meta data.
 Version: 1.1.0
+Description: Speed up your WordPress database by making use of JSON type columns in MySQL.
+Version: 2.0.0
 Author: PRESSMAN
 Author URI: https://www.pressman.ne.jp/
 …
 // Deifne Beyond_table name.
 foreach( array_keys( BEYOND_WPDB_PRIMARYS ) as $primary ) {
     define( beyond_wpdb_get_define_table_name( $primary ), $wpdb->prefix . $primary . 'meta_json' );
+    define( beyond_wpdb_get_define_table_name( $primary ), $wpdb->prefix . $primary . 'meta_beyond' );
+}
 // Require files.
+require_once( plugin_dir_path( __FILE__ ) . 'class/register-hook.php' );
+require_once( plugin_dir_path( __FILE__ ) . 'class/register.php' );
+require_once( plugin_dir_path( __FILE__ ) . 'class/information.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/sql.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/meta-query.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/wp-orderby.php' );
-require_once( plugin_dir_path( __FILE__ ) . 'class/column.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/options.php' );
+// Plugin activation hook.
+register_activation_hook( __FILE__, ['Beyond_Wpdb_Register_Hook', 'activation'] );
+register_deactivation_hook( __FILE__, ['Beyond_Wpdb_Register_Hook', 'deactivation'] );
+register_uninstall_hook( __FILE__, ['Beyond_Wpdb_Register_Hook', 'uninstall'] );
+require_once( plugin_dir_path( __FILE__ ) . 'class/admin-ajax.php' );

beyond-wpdb/tags/2.0.0/class/meta-query.php

-                      r2337801
+                      r2352101
      */
     public function virtual_column_exists( $key, $type ) {
+        $beyond_wpdb_column = new Beyond_Wpdb_Column();
+        $beyond_wpdb_column->set_columns();
+        $virtual_columns = $beyond_wpdb_column->get_columns();
+        return in_array( $key, $virtual_columns[$type] );
+        $beyond_wpdb_info = new Beyond_Wpdb_Information();
+        $beyond_wpdb_info->set_columns();
+        $virtual_columns = $beyond_wpdb_info->get_columns();
+        $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $type ) ) );
+        return in_array( $key, $virtual_columns[$table_name] );
+    }

beyond-wpdb/tags/2.0.0/class/options.php

-                      r2337802
+                      r2352101
  */
 class Beyond_Wpdb_Settings_page {
     private $columns = array();
+    private $privilege_error = False;
     /**
 …
     public function __construct()
+    {
+        load_theme_textdomain( 'beyond-wpdb', plugin_dir_path( __FILE__ ) . '../lang' );
+        add_action( 'admin_notices', array( $this, 'notice__warnig_or_error' ) );
         add_action( 'admin_menu', array( $this, 'add_beyond_wpdb_settings_page' ) );
         add_action( 'admin_init', array( $this, 'page_init' ) );
+        $beyond_wpdb_column = new Beyond_Wpdb_Column();
+        $beyond_wpdb_column->set_columns();
+        $this->columns = $beyond_wpdb_column->get_columns();
+        // add option beyond_wpdb_virtual_column_name
+        add_action( 'add_option_beyond_wpdb_virtual_column_name', array( $this, 'do_after_add_virtual_column' ) ,10, 2 );
+        // add option beyond_wpdb_data_init_name
+        add_action( 'add_option_beyond_wpdb_data_init_name', array( $this, 'do_after_add_data_init' ) ,10, 2 );
+        // update option beyond_wpdb_virtual_column_name
+        add_action( 'update_option_beyond_wpdb_virtual_column_name', array( $this, 'do_after_update_virtual_column' ) ,10, 3 );
+        // update option beyond_wpdb_data_init_name
+        add_action( 'update_option_beyond_wpdb_data_init_name', array ( $this, 'do_after_update_data_init' ) ,10, 3 );
+    }
+    /**
+     * Display warnig or error
+     */
+    function notice__warnig_or_error() {
+        global $wpdb, $pagenow;
+        $current_db = $wpdb->get_var( "SELECT DATABASE()" );
+        $current_user = explode('@', $wpdb->get_var( "SELECT USER()" ));
+        $grants = $wpdb->get_results( "SHOW GRANTS FOR '{$current_user[0]}'@'{$current_user[1]}'", ARRAY_A );
+        $grant_for_mysql_db = False;
+        $current_privileges = array();
+        foreach ( $grants as $value ) {
+            $value = array_values( $value )[0];
+            if ( strpos( $value, "ON `mysql`." ) || strpos( $value, "root" ) ) {
+                $grant_for_mysql_db = True;
+            }
+        }
+        if ( $grant_for_mysql_db ) {
+            $result_privileges = $wpdb->get_results( "SELECT * FROM mysql.db WHERE Db = '{$current_db}' AND User = '{$current_user[0]}'", ARRAY_A );
+            foreach ( $result_privileges as $val ) {
+                $keys = array_keys( $val );
+                $values = array_values( $val );
+                // If $v is Y, $keys[$idx] is assumed to be authorized.
+                foreach ( $values as $idx => $v ) {
+                    if ( $v === 'Y' ) {
+                        array_push( $current_privileges, $keys[$idx] );
+                    }
+                }
+            }
+        }
+        $privileges_error = '';
+        $expected_privileges = array(
+            'Trigger_priv',
+            'Select_priv',
+            'Insert_priv',
+            'Update_priv',
+            'Delete_priv',
+            'Create_priv',
+            'Drop_priv'
+        );
+        if ( count( $current_privileges ) > 0 ) {
+            foreach ( $expected_privileges as $privilege ) {
+                if ( ! in_array( $privilege, $current_privileges ) ) {
+                    switch ( $privilege ) {
+                        case 'Trigger_priv':
+                            $privileges_error .= "<p>" . __("You do not have permission to create triggers.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Select_priv':
+                            $privileges_error .= "<p>" . __("You don't have SELECT permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Insert_priv':
+                            $privileges_error .= "<p>" . __("You don't have INSERT permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Update_priv':
+                            $privileges_error .= "<p>" . __("You don't have UPDATE permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Delete_priv':
+                            $privileges_error .= "<p>" . __("You don't have DELETE permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Create_priv':
+                            $privileges_error .= "<p>" . __("You don't have CREATE permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        default :
+                            $privileges_error .= "<p>" . __("You don't have DROP permission.", "beyond-wpdb") . "</p>";
+                            break;
+                    }
+                    $this->privilege_error = True;
+                }
+            }
+        }
+        if ( isset( $_GET['page'] ) && ! $_GET['page'] && $pagenow === 'options-general.php' ) {
+            if ( ! $grant_for_mysql_db ) {
+                $grants_message = __("You do not have permission to the mysql database.<br>We access the db table in the mysql database to find out what permissions we have to use each feature.<br>There is a possibility that each function will not be available.", "beyond-wpdb" );
+                print "
+                <div class='notice notice-warning is-dismissible'>
+                    <p>
+                    {$grants_message}
+                    </p>
+                </div>
+                ";
+            }
+            if ( $privileges_error ) {
+                $priviles_message = __( "You do not have the following permissions.Please use each function after granting permission.", "beyond-wpdb" );
+                print "
+                <div class='notice notice-error is-dismissible'>
+                    <p>{$priviles_message}</p>
+                    {$privileges_error}
+                </div>";
+            }
+        }
+    }
 …
     public function create_beyond_wpdb_settings_page()
+    {
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        $features = __( "Speed up database loading by creating your own tables that aggregate meta information.<br>This is especially useful when you have a large number of records and for complex meta-query data calls.", "beyond-wpdb" );
 ?>
+        <style>
+            .d-block {
+                display: block;
+            }
+            .d-inline-block {
+                display: inline-block;
+            }
+            .d-none {
+                display: none;
+            }
+            .table_not_exists {
+                opacity:0.3;
+                pointer-envet:none;
+            }
+        </style>
         <div class='beyond-wpdb-settings-wrap'>
             <!-- title -->
+            <h1 style="margin-bottom: 30px;">Beyond WPDB Settings</h1>
+            <!-- form -->
+            <form action='options.php' method='post'>
+                <!-- Group Concat -->
+                <div>
+                    <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                    <?php do_settings_sections( 'croup_concat_section' ); ?>
+                </div>
+                <div>
+                    <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                    <?php do_settings_sections( 'data_init_section' ); ?>
+                </div>
+                <!-- Virtual Column Settings -->
+                <div>
+                    <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                    <?php do_settings_sections( 'virtual_columns_section' ); ?>
+                </div>
+            <div style="margin-bottom: 30px;">
+                <h1 style="margin-bottom: 30px;">Beyond WPDB Settings</h1>
+                <p>
+                    <?php echo $features; ?>
+                </p>
+                <h2><?php echo __( "Settings", "beyond-wpdb" ) ?></h2>
+            </div>
+            <!-- data init -->
+            <div class="data_init_section">
+                <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                <?php do_settings_sections( 'data_init_section' ); ?>
                 <!-- Submit Button -->
+                <?php submit_button(); ?>
+            </form>
+                <p class="submit">
+                    <button id="beyond-wpdb-init-btn" class="button button-primary" <?php echo $disabled; ?>>Update</button>
+                </p>
+            </>
+            <!-- Virtual Column Settings -->
+            <div>
+                <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                <?php do_settings_sections( 'virtual_columns_section' ); ?>
+                <!-- Submit Button -->
+                <p class="submit">
+                    <button id="beyond-wpdb-virtual-columns-btn" class="button button-primary" <?php echo $disabled; ?>>Update</button>
+                </p>
+            </div>
+            <!-- notice -->
+            <div>
+                <h2><?php echo __( "NOTICE", "beyond-wpdb" ) ?></h2>
+                <p><?php echo __( "This plugin sets 4294967295 to group_concat_max_len when it createa an original table with JSON type column.<br>You can change the number by using filter 'beyond_group_concat_max_len'.", "beyond-wpdb" ) ?></p>
+            </div>
         </div>
 <?php
 …
     public function page_init()
+    {
+        // group concat
+        add_settings_section(
+            'setting_section_croup_concat',
+            '<h2>Group Concat</h2>',
+            array( $this, 'print_group_concat_section_info' ),
+            'croup_concat_section'
+        );
+        add_settings_field(
+            '',
+            "group_concat_max_len",
+            array( $this, 'print_croup_concat_field' ),
+            'croup_concat_section',
+            'setting_section_croup_concat'
+        );
+        register_setting(
+            'beyond_wpdb_group',
+            '',
+            'sanitize_text_field'
+        );
+        global $wpdb;
         // data init
         add_settings_section(
             'setting_section_data_init',
             '<h2>Data Init</h2>',
+            '<h2>' . __( "Creating and deleting custom metatables", "beyond-wpdb" ) . '</h2>',
             array( $this, 'print_data_init_section_info' ),
             'data_init_section'
 …
         add_settings_field(
             'data_init',
             "Data Init",
+            'data_init_postmeta_json',
+            __( "Table for postmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: postmeta_beyond</span>",
             array( $this, 'print_data_init_field' ),
             'data_init_section',
+            'setting_section_data_init'
+            'setting_section_data_init',
+            array(
+                'id' => "data_init_{$wpdb->prefix}postmeta_beyond",
+                'class' => "{$wpdb->prefix}postmeta_beyond"
+            )
+        );
+        add_settings_field(
+            'data_init_usermeta_json',
+            __( "Table for usermeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: usermeta_beyond</span>",
+            array( $this, 'print_data_init_field' ),
+            'data_init_section',
+            'setting_section_data_init',
+            array(
+                'id' => "data_init_{$wpdb->prefix}usermeta_beyond",
+                'class' => "{$wpdb->prefix}usermeta_beyond"
+            )
+        );
+        add_settings_field(
+            'data_init_commentmeta_json',
+            __( "Table for commentmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: commentmeta_beyond</span>",
+            array( $this, 'print_data_init_field' ),
+            'data_init_section',
+            'setting_section_data_init',
+            array(
+                'id' => "data_init_{$wpdb->prefix}commentmeta_beyond",
+                'class' => "{$wpdb->prefix}commentmeta_beyond"
+            )
         );
 …
             'beyond_wpdb_group',
             'beyond_wpdb_data_init_name',
             array( $this, 'sanitize_input_columns' )
+            array()
         );
 …
         add_settings_section(
             'virtual_column_section_id',
             '<h2>Virtual Column Settings</h2>',
+            '<h2>' . __( "Virtual Column Settings", "beyond-wpdb" ) . '</h2>',
             array( $this, 'print_virtual_column_section_info' ),
             'virtual_columns_section'
 …
         add_settings_field(
             'postmeta_json', // id
             "postmeta_json", // title
+            __( "Table for postmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: postmeta_beyond</span>", // title
             array( $this, 'print_postmeta_json_field' ), // callback
             'virtual_columns_section', // section page
+            'virtual_column_section_id' // section id
+            'virtual_column_section_id', // section id,
+            array(
+                'id' => "virtual_column_{$wpdb->prefix}postmeta_beyond",
+                'class' => "virtual_column_{$wpdb->prefix}postmeta_beyond"
+            )
         );
         add_settings_field(
             'usermeta_json',
             "usermeta_json",
+            __( "Table for usermeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: usermeta_beyond</span>",
             array( $this, 'print_usermeta_json_field' ),
             'virtual_columns_section', // section page
+            'virtual_column_section_id' // section id
+            'virtual_column_section_id', // section id
+            array(
+                'id' => "virtual_column_{$wpdb->prefix}usermeta_beyond",
+                'class' => "virtual_column_{$wpdb->prefix}usermeta_beyond"
+            )
         );
         add_settings_field(
             'commentmeta_json', // id
             "commentmeta_json", // title
+            __( "Table for commentmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: commentmeta_beyond</span>", // title
             array( $this, 'print_commentmeta_json_field' ), // callback
             'virtual_columns_section', // section id
+            'virtual_column_section_id'
+            'virtual_column_section_id',
+            array(
+                'id' => "virtual_column_{$wpdb->prefix}commentmeta_beyond",
+                'class' => "virtual_column_{$wpdb->prefix}commentmeta_beyond"
+            )
         );
 …
             'beyond_wpdb_group',
             'beyond_wpdb_virtual_column_name',
+            array( $this, 'sanitize_input_columns' )
+        );
+    }
+    /**
+     * @param $option
+     * @param $value
+     */
+    public function do_after_add_virtual_column( $option, $value ) {
+        $this->do_virtual_column_processing( $option );
+    }
+    /**
+     * @param $option
+     * @param $value
+     */
+    public function do_after_add_data_init( $option, $value ) {
+        $this->do_data_init_processing( $option );
+    }
+    /**
+     * @param $old
+     * @param $value
+     * @param $option
+     */
+    public function do_after_update_virtual_column( $old, $value, $option ) {
+        $this->do_virtual_column_processing( $option );
+    }
+    /**
+     * @param $old
+     * @param $value
+     * @param $option
+     */
+    public function do_after_update_data_init( $old, $value, $option ) {
+        $this->do_data_init_processing( $option );
+    }
+    /**
+     * @param $option
+     * create virtual column and delete virtual column and delete option
+     */
+    public function do_virtual_column_processing( $option ) {
+        $this->create_virtual_column_and_index( get_option( $option ) );
+        $this->delete_virtual_column( get_option( $option ) );
+        delete_option( $option );
+    }
+    /**
+     * @param $option
+     * data init
+     */
+    public function do_data_init_processing( $option ) {
+        $this->data_init( get_option($option) );
+        delete_option( $option );
+    }
+    /**
+     * @param $input
+     *
+     * @return array
+     */
+    public function sanitize_input_columns( $input )
+    {
+        $new_input = array();
+        if( isset( $input['postmeta_json'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['postmeta_json'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['postmeta_json'] = implode( PHP_EOL, $input_columns );
+        }
+        if( isset( $input['usermeta_json'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['usermeta_json'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['usermeta_json'] = implode( PHP_EOL, $input_columns );
+        }
+        if( isset( $input['commentmeta_json'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['commentmeta_json'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['commentmeta_json'] = implode( PHP_EOL, $input_columns );
+        }
+        if( isset( $input['data_init'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['data_init'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['data_init'] = implode( PHP_EOL, $input_columns );
+        }
+        return $new_input;
+    }
+    /**
+     * @param $option
+     * data init
+     */
+    public function data_init( $option )
+    {
+        if ( isset( $option['data_init'] ) && $option['data_init'] === '1' ) {
+            $beyond_wpdb_sql = new Beyond_Wpdb_Sql();
+            $beyond_wpdb_sql->data_init();
+        }
+    }
+    /**
+     * @param $input_columns
+     *
+     * @return string
+     */
+    public function sanitize( $input_columns )
+    {
+        foreach ( $input_columns as $key => $value ) {
+            $input_columns[$key] = sanitize_text_field( $value );
+        }
+        return $input_columns;
+    }
+    /**
+     * @param $options
+     * create virtual column
+     */
+    public function create_virtual_column_and_index( $options ) {
+        global $wpdb;
+        if ( is_array( $options ) ) {
+            foreach ( $options as $key => $option ) {
+                if ( 'postmeta_json' === $key ) {
+                    $type = 'post';
+                } elseif ( 'usermeta_json' === $key ) {
+                    $type = 'user';
+                } else {
+                    $type = 'comment';
+                }
+                $table_name = $this->get_json_table_name( $type );
+                $exist_columns = $this->columns[$type];
+                $option = explode( PHP_EOL, $option );
+                foreach ( $option as $value ) {
+                    $value = esc_sql( $value );
+                    // If $value already exists, continue
+                    if ( in_array( $value, $exist_columns ) ) {
+                        continue;
+                    }
+                    $json_key = '$.' . $value;
+                    // create virtual column
+                    $sql = "ALTER TABLE {$table_name} ADD {$value} VARCHAR(255) GENERATED ALWAYS AS ( JSON_UNQUOTE( JSON_EXTRACT( json, '$json_key' ) ) )";
+                    $wpdb->query( $sql );
+                    // create index
+                    $sql = "ALTER TABLE {$table_name} ADD INDEX ({$value})";
+                    $wpdb->query( $sql );
+                }
+            }
+        }
+    }
+    /**
+     * @param $options
+     * delete virtual columns
+     * @return mixed
+     */
+    public function delete_virtual_column( $options ) {
+        global $wpdb;
+        if ( is_array( $options ) ) {
+            foreach ( $options as $key => $option ) {
+                if ( 'postmeta_json' === $key ) {
+                    $type = 'post';
+                } elseif ( 'usermeta_json' === $key ) {
+                    $type = 'user';
+                } else {
+                    $type = 'comment';
+                }
+                $table_name = $this->get_json_table_name( $type );
+                $exist_columns = $this->columns[$type];
+                $option = explode( PHP_EOL, $option );
+                foreach ( $exist_columns as $column ) {
+                    if ( ! in_array( $column, $option ) ) {
+                        $sql = "ALTER TABLE {$table_name} DROP COLUMN {$column}";
+                        $wpdb->query( $sql );
+                    }
+                }
+            }
+        }
+    }
+    public function get_json_table_name( $type ) {
+        return esc_sql( constant( beyond_wpdb_get_define_table_name( $type ) ) );
+            array()
+        );
+    }
 …
     public function print_virtual_column_section_info()
+    {
         print '<p>
                  Enter a list metakeys for which you want to create a virtual column.Each metakey should be separated by return enter key.<br>
                  The virtual column is indexed and the search uses the virtual column, which makes it faster.
                </p>';
+        $info = __( "When you enter a key of meta information separated by a new line,<br>the specified meta key from the JSON type column is set as a virtual column and the index is pasted.<br>You can further speed up data calls by specifying the most commonly used meta keys for meta queries.", "beyond-wpdb" );
+        print "<p>
+                 {$info}
+               </p>";
+    }
 …
      * print postmeta json field
      */
+    public function print_postmeta_json_field()
+    {
+        $value = implode( PHP_EOL, $this->columns['post'] );
+        printf(
+            '<textarea rows="3" cols="40" id="postmeta_json" name="beyond_wpdb_virtual_column_name[postmeta_json]">%s</textarea>',
+            $value
+        );
+    public function print_postmeta_json_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <textarea class='create_virtualColumns_text-area d-none' rows='3' cols='40' id='{$id}_textarea' name='{$id}' {$disabled}></textarea>
+        <span class='create_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+        <span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
 …
      * print usermeta json field
      */
+    public function print_usermeta_json_field()
+    {
+        $value = implode( PHP_EOL, $this->columns['user'] );
+        printf(
+            '<textarea rows="3" cols="40" id="usermeta_json" name="beyond_wpdb_virtual_column_name[usermeta_json]">%s</textarea>',
+            $value
+        );
+    public function print_usermeta_json_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <textarea class='create_virtualColumns_text-area d-none' rows='3' cols='40' id='{$id}_textarea' name='{$id}' {$disabled}></textarea>
+        <span class='create_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+        <span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
 …
      * print commentmeta json field
      */
+    public function print_commentmeta_json_field()
+    {
+        $value = implode( PHP_EOL, $this->columns['comment'] );
+        printf(
+            '<textarea rows="3" cols="40" id="commentmeta_json" name="beyond_wpdb_virtual_column_name[commentmeta_json]">%s</textarea>',
+            $value
+        );
+    }
+    /**
+     * print group concat section info
+     */
+    public function print_group_concat_section_info()
+    {
+        print '<p>Use GROUP_CONCAT in data init.If the json string consisting of all metas is longer than this value, an error occurs.</p>';
+    }
+    /**
+     * print croup_concat field
+     */
+    public function print_croup_concat_field()
+    {
+        global $wpdb;
+        $group_concat_max_len = $wpdb->get_results( "show variables like 'group_concat_max_len'" );
+        printf(
+            '<p>%s</p>',
+            $group_concat_max_len[0]->Value
+        );
+    public function print_commentmeta_json_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <textarea class='create_virtualColumns_text-area d-none' rows='3' cols='40' id='{$id}_textarea' name='{$id}' {$disabled}></textarea>
+        <span class='create_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+        <span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
 …
     public function print_data_init_section_info()
+    {
+        print '<p>Collect all meta information and re-register it in the json table.</p>';
+    }
+    /**
+     *  print data_init field
+     */
+    public function print_data_init_field()
+    {
+        print '<input type="checkbox" id="data_init" name="beyond_wpdb_data_init_name[data_init]" value="1">';
+        $info = __( "Create and delete your own tables that aggregate the metadata. (Enabled to create, disabled to delete)<br>As long as it is enabled, all meta information will continue to be automatically registered, updated, and deleted in the JSON type columns of the relevant table.<br>When enabled, a new table will be created or initialized, which will take some time. When disabled, the table will be deleted.", "beyond-wpdb" );
+        print $info;
+    }
+    /**
+     * Check to see if each feature is available
+     * @return bool
+     */
+    public function checkDisabled() {
+        return ! $this->privilege_error;
+    }
+    /**
+     * @param $args
+     * print data_init field
+     */
+    public function print_data_init_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <div class='data-init-input-radio d-none'><input type='radio' id='{$id}_active' name='name_{$id}' value='1' {$disabled}>" . "<label for='{$id}_active'>" . __( "activation", "beyond-wpdb" ) . "</label>" .
+        "<input type='radio' id='{$id}_deactive' name='name_{$id}' value='0' {$disabled} style='margin-left: 25px;'>" . "<label for='{$id}_deactive'>" . __( "deactivation", "beyond-wpdb" ) . "</label>" .
+        "<span class='activate_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+         <span class='success_{$id}' style='margin-left: 20px; display: none;'>Success.</span></div><span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
+}

beyond-wpdb/tags/2.0.0/class/sql.php

-                      r2330366
+                      r2352101
  */
 class Beyond_Wpdb_Sql {
     function __construct() {
         global $wpdb;
         foreach( array_keys( BEYOND_WPDB_PRIMARYS ) as $primary ) {
+            $this->triggers[$primary] = array();
             $insert_trigger = 'insert_' . $primary . '_trigger';
             $this->$insert_trigger = esc_sql( $wpdb->prefix . 'insert_' . $primary . '_trigger' );
+            array_push( $this->triggers[$primary], $this->$insert_trigger );
             $delete_trigger = 'delete_' . $primary . '_trigger';
             $this->$delete_trigger = esc_sql( $wpdb->prefix . 'delete_' . $primary . '_trigger' );
+            array_push( $this->triggers[$primary], $this->$delete_trigger );
             $insert_meta_trigger = 'insert_' . $primary . 'meta_trigger';
             $this->$insert_meta_trigger = esc_sql( $wpdb->prefix . 'insert_' . $primary . 'meta_trigger' );
+            array_push( $this->triggers[$primary], $this->$insert_meta_trigger );
             $update_meta_trigger = 'update_' . $primary . 'meta_trigger';
             $this->$update_meta_trigger = esc_sql( $wpdb->prefix . 'update_' . $primary . 'meta_trigger' );
+            array_push( $this->triggers[$primary], $this->$update_meta_trigger );
             $delete_meta_trigger = 'delete_' . $primary . 'meta_trigger';
             $this->$delete_meta_trigger = esc_sql( $wpdb->prefix . 'delete_' . $primary . 'meta_trigger' );
+            array_push( $this->triggers[$primary], $this->$delete_meta_trigger );
+        }
+    }
 …
      * Create table
+     *
      * @return void
      */
     function create_table() : void {
         foreach( BEYOND_WPDB_PRIMARYS as $primary => $values ) {
             $this->create_table_sql( $primary, $values['meta_table_key'] );
+        }
+     * @param string $type
+     * @return void
+     */
+    function create_table( $primary ) : void {
+        $values = BEYOND_WPDB_PRIMARYS[$primary];
+        $this->create_table_sql( $primary, $values['meta_table_key'] );
+    }
 …
      * Drop table
+     *
      * @return void
      */
     function drop_table() {
         foreach( array_keys( BEYOND_WPDB_PRIMARYS ) as $primary ) {
             $this->drop_table_sql( $primary );
+        }
+     * @param $primary
+     * @return void
+     * @throws Exception
+     */
+    function drop_table( $primary ) {
+        $this->drop_table_sql( $primary );
+    }
 …
      * @param string $primary
      * @return void
+     * @throws Exception
      */
     protected function drop_table_sql( $primary ) {
         global $wpdb;
+        $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
+        $sql = 'DROP TABLE ' . $table_name;
+        $wpdb->query( $sql );
+        $exist_tables = $this->get_exist_json_tables();
+        $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
+        if ( in_array( $table_name, $exist_tables ) ) {
+            $sql = 'DROP TABLE ' . $table_name;
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * Insert/Update/Delete postmeta trigger
+     *
      * @return void
      */
     function create_trigger() {
         foreach( BEYOND_WPDB_PRIMARYS as $primary => $values ) {
             $this->insert_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
             $this->delete_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
             $this->insert_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
             $this->update_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
             $this->delete_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+        }
+     * @param string $primary
+     * @return void
+     * @throws Exception
+     */
+    function create_trigger( $primary ) {
+        $values = BEYOND_WPDB_PRIMARYS[$primary];
+        $this->insert_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
+        $this->delete_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
+        $this->insert_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+        $this->update_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+        $this->delete_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function insert_primary_trigger( $primary, $primaty_table_name, $primary_table_key, $meta_table_key ) {
 …
         $insert_trigger = 'insert_' . $primary . '_trigger';
+        $exist_triggers = $this->get_exist_triggers();
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
         $primaty_table_name = esc_sql( $primaty_table_name );
 …
         $primary_table_key = esc_sql( $primary_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$insert_trigger . ' AFTER
+        if ( ! in_array( $this->$insert_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$insert_trigger . ' AFTER
                 INSERT ON
                 ' . $primaty_table_name . '
 …
                 VALUES
                     (NEW.' . $primary_table_key . ', "{}" )';
+        $wpdb->query( $sql );
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function delete_primary_trigger( $primary, $primaty_table_name, $primary_table_key, $meta_table_key ) {
 …
         $delete_trigger = 'delete_' . $primary . '_trigger';
+        $exist_triggers = $this->get_exist_triggers();
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
         $primaty_table_name = esc_sql( $primaty_table_name );
 …
         $primary_table_key = esc_sql( $primary_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$delete_trigger . ' BEFORE
+        DELETE ON
+        ' . $primaty_table_name . '
+        FOR
+        EACH
+        ROW
+        DELETE FROM ' . $table_name . '
+        WHERE ' . $meta_table_key . ' = OLD.' . $primary_table_key;
+        $wpdb->query( $sql );
+        if ( ! in_array( $this->$delete_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$delete_trigger . ' BEFORE
+            DELETE ON
+            ' . $primaty_table_name . '
+            FOR
+            EACH
+            ROW
+            DELETE FROM ' . $table_name . '
+            WHERE ' . $meta_table_key . ' = OLD.' . $primary_table_key;
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function insert_meta_trigger( $primary, $meta_table_name, $meta_table_key ) {
         global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
         $insert_meta_trigger = 'insert_' . $primary . 'meta_trigger';
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
 …
         $meta_table_key = esc_sql( $meta_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$insert_meta_trigger . ' AFTER
+        if ( ! in_array( $this->$insert_meta_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$insert_meta_trigger . ' AFTER
                 INSERT ON
                 ' . $meta_table_name . '
 …
                 (`json`, CONCAT
                 ("$.",NEW.meta_key), NEW.meta_value) WHERE ' . $meta_table_key . ' = NEW.'.$meta_table_key;
+        $wpdb->query( $sql );
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function update_meta_trigger( $primary, $meta_table_name, $meta_table_key ) {
         global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
         $update_meta_trigger = 'update_' . $primary . 'meta_trigger';
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
 …
         $meta_table_key = esc_sql( $meta_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$update_meta_trigger . ' AFTER
+        if ( ! in_array( $this->$update_meta_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$update_meta_trigger . ' AFTER
                 UPDATE ON
                 ' . $meta_table_name . '
 …
                 (`json`, CONCAT
                 ("$.",NEW.meta_key), NEW.meta_value) WHERE ' . $meta_table_key . ' = NEW.' . $meta_table_key;
+        $wpdb->query( $sql );
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function delete_meta_trigger( $primary, $meta_table_name, $meta_table_key ) {
         global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
         $delete_meta_trigger = 'delete_' . $primary . 'meta_trigger';
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
 …
         $meta_table_key = esc_sql( $meta_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$delete_meta_trigger . ' AFTER
+        if ( ! in_array( $this->$delete_meta_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$delete_meta_trigger . ' AFTER
                 DELETE ON
                 ' . $meta_table_name . '
 …
                 `json` = JSON_REMOVE(`json`, CONCAT
                 ("$.",OLD.meta_key)) WHERE ' . $meta_table_key . ' = OLD.'. $meta_table_key;
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
+    /**
+     * Drop triggers
+     *
+     * @param $primary
+     * @return void
+     * @throws Exception
+     */
+    function drop_triggers( $primary ) {
+        global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
+        foreach ( $this->triggers[$primary] as $value ) {
+            if ( in_array( $value, $exist_triggers ) ) {
+                $sql = 'DROP TRIGGER ' . esc_sql( $value );
+                $wpdb->query( $sql );
+            }
+        }
+        if ( $wpdb->last_error ) {
+            throw new Exception( $wpdb->last_error );
+        }
+    }
+    /**
+     * Update table from post, postmeta table.
+     *
+     * @param $primary
+     *
+     * @throws Exception
+     */
+    function data_init( $primary ) {
+        global $wpdb;
+        $values = BEYOND_WPDB_PRIMARYS[$primary];
+        $maxlen = 4294967295;
+        $maxlen = apply_filters( 'beyond_group_concat_max_len', $maxlen );
+        $sql = 'SET SESSION group_concat_max_len = ' . $maxlen;
         $wpdb->query( $sql );
+    }
+    /**
+     * Drop triggers
+     *
+     * @return void
+     */
+    function drop_triggers() {
+        global $wpdb;
+        foreach( get_object_vars( $this ) as $value ) {
+            $sql = 'DROP TRIGGER ' . esc_sql( $value );
+            $wpdb->query( $sql );
+        }
+    }
+    /**
+     * Update table from post, postmeta table.
+     *
+     * @return void
+     */
+    function data_init() {
+        foreach( BEYOND_WPDB_PRIMARYS as $primary => $values ) {
+            $this->data_init_sql( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_name'], $values['meta_table_key'] );
+            $this->delete_non_existent_data_from_json( $primary, $values['primary_table_name'], $values['primary_table_key'] );
+        }
+        $this->data_init_sql( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_name'], $values['meta_table_key'] );
+        $this->delete_non_existent_data_from_json( $primary, $values['primary_table_name'], $values['primary_table_key'] );
+    }
 …
      * data init sql
+     *
+     * @param string $primary
+     * @param string $primary_table_name
+     * @param string $primary_table_key
+     * @param string $meta_table_name
+     * @param string $meta_table_key
+     * @return void
+     * @param $primary
+     * @param $primary_table_name
+     * @param $primary_table_key
+     * @param $meta_table_name
+     * @param $meta_table_key
+     *
+     * @throws Exception
      */
     protected function data_init_sql( $primary, $primary_table_name, $primary_table_key, $meta_table_name, $meta_table_key ) {
 …
                 UPDATE json = VALUES(json)';
         $wpdb->query( $sql );
+        if ( $wpdb->last_error ) {
+            throw new Exception( $wpdb->last_error );
+        }
+    }
 …
      * @param $primary_table_key
      * @return void
+     * @throws Exception
      */
     protected function delete_non_existent_data_from_json( $primary, $primary_table_name, $primary_table_key ) {
 …
         $wpdb->query( $sql );
+        if ( $wpdb->last_error ) {
+            throw new Exception( $wpdb->last_error );
+        }
+    }
+    /**
+     * Get exist virtual columns
+     * @return array
+     */
+    protected function get_exist_triggers() {
+        $beyond_wpdb_info = new Beyond_Wpdb_Information();
+        $beyond_wpdb_info->set_triggers();
+        return $beyond_wpdb_info->get_triggers();
+    }
+    /**
+     * Get exist json tables
+     * @return array
+     */
+    protected function get_exist_json_tables(){
+        $beyond_wpdb_info = new Beyond_Wpdb_Information();
+        $beyond_wpdb_info->set_tables();
+        return $beyond_wpdb_info->get_tables();
+    }
+}

beyond-wpdb/tags/2.0.0/readme.txt

-                      r2337807
+                      r2352101
 Contributors: pressmaninc,kazunao,hiroshisekiguchi,hommakoharu,pmhirotaka
 Tags: pressman,pressmaninc,json,meta_query,wpdb,database,fast,speed,speed up,sql,replace sql
 Stable tag: 1.1.0
+Stable tag: 2.0.0
 License: GPLv2 or later
 License URI: http://www.gnu.org/licenses/gpl-2.0.html
 …
 Requires MariaDB: 10.2
 Tested up to: 5.4
+※ Table names have been changed since v2.0.Please delete the meta_json table and reactivate it in the options screen.
 Are you having trouble with slow queries due to WordPress table structure?
 …
 == Actual Behavior ==
+* You can create and delete json tables and triggers and data for each json table on the configuration page.
+* Create virtual columns and indexes for the json table you created in the configuration page.
+* If you add a virtual column to the json table in the configuration page, the SQL is converted to face the virtual column.
 * Converts the SQL that WordPress auto-generates when using the default APIs of get_posts, Wp_Query, and etc. into a simple SQL that combines the posts/users/comment table with its own meta table.
 * The custom meta table automatically stores data using MySQL and MariaDB triggers.
 …
 == Custom meta tables generated by plugin ==
+* postmeta_json (for posts table)
+* usermeta_json (for users table)
+* commentmeta_json (for comment table)
+The actual table names are prefixed with $table_prefix.
+* postmeta_beyond (for posts table)
+* usermeta_beyond (for users table)
+* commentmeta_beyond (for comment table)
 == Columns in each table ==
+* postmeta_json
+The actual table names are prefixed with $table_prefix.
+* postmeta_beyond
     * post_id(int)
     * json(json)
 * usermeta_json
+* usermeta_beyond
     * post_id(int)
     * json(json)
 * commentmeta_json
+* commentmeta_beyond
     * post_id(int)
     * json(json)
 …
 ex1 ) Create 100 posts and 50 meta data for each post. Combine them 9 times.
 * postmeta_json
+* postmeta_beyond
     * Equal:0.04
     * IN:0.09
 …
     * BETWEEN:0.04
     * NOT BETWEEN:0.04
 * postmeta
     * Equal:4.72
 …
     * NOT BETWEEN:0.63
+ex2 ) 10000 posts, 1 meta(category) per post.If you add a virtual column and put an index in Category, or if you add a virtual column and put an index in Category
+* postmeta_json(without virtual column)
+ex2 ) 10,000 posts, 1 meta per post. meta_key is category.
+      Add a virtual column(category) to postmeta_beyond and put an index,
+      and compare it to the case without adding a virtual column.
+* postmeta_beyond(with virtual column)
+    * Equal:0.02
+    * IN:0.35
+    * BETWEEN:0.12
+    * NOT BETWEEN:0.09
+* postmeta_beyond(without virtual column)
     * Equal:0.15
     * IN:1.92
     * BETWEEN:0.37
     * NOT BETWEEN:0.29
-* postmeta_json(with virtual column)
-    * Equal:0.02
-    * IN:0.35
-    * BETWEEN:0.12
-    * NOT BETWEEN:0.09
 == Installation ==
 …
 * You'll need TRIGGER, SELECT, and UPDATE permissions for the target table and its columns to use the trigger feature of MySQL and MariaDB.
 * It makes changes to SQL only if `suppress_filters` is false.
+* This plugin sets 4294967295 to group_concat_max_len when it create an original table with JSON type column. You can change the number by using filter 'beyond_group_concat_max_len'.

beyond-wpdb/trunk/beyond-wpdb.php

-                      r2337801
+                      r2352101
 Plugin Name: Beyond Wpdb
 Plugin URI:
 Description: Create a new table and register the meta data in a single row of JSON columns.Registration is done by mysql's trigger function.The data in WordPress is not affected.Convert sql statement, when searching for meta data.
 Version: 1.1.0
+Description: Speed up your WordPress database by making use of JSON type columns in MySQL.
+Version: 2.0.0
 Author: PRESSMAN
 Author URI: https://www.pressman.ne.jp/
 …
 // Deifne Beyond_table name.
 foreach( array_keys( BEYOND_WPDB_PRIMARYS ) as $primary ) {
     define( beyond_wpdb_get_define_table_name( $primary ), $wpdb->prefix . $primary . 'meta_json' );
+    define( beyond_wpdb_get_define_table_name( $primary ), $wpdb->prefix . $primary . 'meta_beyond' );
+}
 // Require files.
+require_once( plugin_dir_path( __FILE__ ) . 'class/register-hook.php' );
+require_once( plugin_dir_path( __FILE__ ) . 'class/register.php' );
+require_once( plugin_dir_path( __FILE__ ) . 'class/information.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/sql.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/meta-query.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/wp-orderby.php' );
-require_once( plugin_dir_path( __FILE__ ) . 'class/column.php' );
 require_once( plugin_dir_path( __FILE__ ) . 'class/options.php' );
+// Plugin activation hook.
+register_activation_hook( __FILE__, ['Beyond_Wpdb_Register_Hook', 'activation'] );
+register_deactivation_hook( __FILE__, ['Beyond_Wpdb_Register_Hook', 'deactivation'] );
+register_uninstall_hook( __FILE__, ['Beyond_Wpdb_Register_Hook', 'uninstall'] );
+require_once( plugin_dir_path( __FILE__ ) . 'class/admin-ajax.php' );

beyond-wpdb/trunk/class/meta-query.php

-                      r2337801
+                      r2352101
      */
     public function virtual_column_exists( $key, $type ) {
+        $beyond_wpdb_column = new Beyond_Wpdb_Column();
+        $beyond_wpdb_column->set_columns();
+        $virtual_columns = $beyond_wpdb_column->get_columns();
+        return in_array( $key, $virtual_columns[$type] );
+        $beyond_wpdb_info = new Beyond_Wpdb_Information();
+        $beyond_wpdb_info->set_columns();
+        $virtual_columns = $beyond_wpdb_info->get_columns();
+        $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $type ) ) );
+        return in_array( $key, $virtual_columns[$table_name] );
+    }

beyond-wpdb/trunk/class/options.php

-                      r2337802
+                      r2352101
  */
 class Beyond_Wpdb_Settings_page {
     private $columns = array();
+    private $privilege_error = False;
     /**
 …
     public function __construct()
+    {
+        load_theme_textdomain( 'beyond-wpdb', plugin_dir_path( __FILE__ ) . '../lang' );
+        add_action( 'admin_notices', array( $this, 'notice__warnig_or_error' ) );
         add_action( 'admin_menu', array( $this, 'add_beyond_wpdb_settings_page' ) );
         add_action( 'admin_init', array( $this, 'page_init' ) );
+        $beyond_wpdb_column = new Beyond_Wpdb_Column();
+        $beyond_wpdb_column->set_columns();
+        $this->columns = $beyond_wpdb_column->get_columns();
+        // add option beyond_wpdb_virtual_column_name
+        add_action( 'add_option_beyond_wpdb_virtual_column_name', array( $this, 'do_after_add_virtual_column' ) ,10, 2 );
+        // add option beyond_wpdb_data_init_name
+        add_action( 'add_option_beyond_wpdb_data_init_name', array( $this, 'do_after_add_data_init' ) ,10, 2 );
+        // update option beyond_wpdb_virtual_column_name
+        add_action( 'update_option_beyond_wpdb_virtual_column_name', array( $this, 'do_after_update_virtual_column' ) ,10, 3 );
+        // update option beyond_wpdb_data_init_name
+        add_action( 'update_option_beyond_wpdb_data_init_name', array ( $this, 'do_after_update_data_init' ) ,10, 3 );
+    }
+    /**
+     * Display warnig or error
+     */
+    function notice__warnig_or_error() {
+        global $wpdb, $pagenow;
+        $current_db = $wpdb->get_var( "SELECT DATABASE()" );
+        $current_user = explode('@', $wpdb->get_var( "SELECT USER()" ));
+        $grants = $wpdb->get_results( "SHOW GRANTS FOR '{$current_user[0]}'@'{$current_user[1]}'", ARRAY_A );
+        $grant_for_mysql_db = False;
+        $current_privileges = array();
+        foreach ( $grants as $value ) {
+            $value = array_values( $value )[0];
+            if ( strpos( $value, "ON `mysql`." ) || strpos( $value, "root" ) ) {
+                $grant_for_mysql_db = True;
+            }
+        }
+        if ( $grant_for_mysql_db ) {
+            $result_privileges = $wpdb->get_results( "SELECT * FROM mysql.db WHERE Db = '{$current_db}' AND User = '{$current_user[0]}'", ARRAY_A );
+            foreach ( $result_privileges as $val ) {
+                $keys = array_keys( $val );
+                $values = array_values( $val );
+                // If $v is Y, $keys[$idx] is assumed to be authorized.
+                foreach ( $values as $idx => $v ) {
+                    if ( $v === 'Y' ) {
+                        array_push( $current_privileges, $keys[$idx] );
+                    }
+                }
+            }
+        }
+        $privileges_error = '';
+        $expected_privileges = array(
+            'Trigger_priv',
+            'Select_priv',
+            'Insert_priv',
+            'Update_priv',
+            'Delete_priv',
+            'Create_priv',
+            'Drop_priv'
+        );
+        if ( count( $current_privileges ) > 0 ) {
+            foreach ( $expected_privileges as $privilege ) {
+                if ( ! in_array( $privilege, $current_privileges ) ) {
+                    switch ( $privilege ) {
+                        case 'Trigger_priv':
+                            $privileges_error .= "<p>" . __("You do not have permission to create triggers.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Select_priv':
+                            $privileges_error .= "<p>" . __("You don't have SELECT permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Insert_priv':
+                            $privileges_error .= "<p>" . __("You don't have INSERT permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Update_priv':
+                            $privileges_error .= "<p>" . __("You don't have UPDATE permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Delete_priv':
+                            $privileges_error .= "<p>" . __("You don't have DELETE permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        case 'Create_priv':
+                            $privileges_error .= "<p>" . __("You don't have CREATE permission.", "beyond-wpdb") . "</p>";
+                            break;
+                        default :
+                            $privileges_error .= "<p>" . __("You don't have DROP permission.", "beyond-wpdb") . "</p>";
+                            break;
+                    }
+                    $this->privilege_error = True;
+                }
+            }
+        }
+        if ( isset( $_GET['page'] ) && ! $_GET['page'] && $pagenow === 'options-general.php' ) {
+            if ( ! $grant_for_mysql_db ) {
+                $grants_message = __("You do not have permission to the mysql database.<br>We access the db table in the mysql database to find out what permissions we have to use each feature.<br>There is a possibility that each function will not be available.", "beyond-wpdb" );
+                print "
+                <div class='notice notice-warning is-dismissible'>
+                    <p>
+                    {$grants_message}
+                    </p>
+                </div>
+                ";
+            }
+            if ( $privileges_error ) {
+                $priviles_message = __( "You do not have the following permissions.Please use each function after granting permission.", "beyond-wpdb" );
+                print "
+                <div class='notice notice-error is-dismissible'>
+                    <p>{$priviles_message}</p>
+                    {$privileges_error}
+                </div>";
+            }
+        }
+    }
 …
     public function create_beyond_wpdb_settings_page()
+    {
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        $features = __( "Speed up database loading by creating your own tables that aggregate meta information.<br>This is especially useful when you have a large number of records and for complex meta-query data calls.", "beyond-wpdb" );
 ?>
+        <style>
+            .d-block {
+                display: block;
+            }
+            .d-inline-block {
+                display: inline-block;
+            }
+            .d-none {
+                display: none;
+            }
+            .table_not_exists {
+                opacity:0.3;
+                pointer-envet:none;
+            }
+        </style>
         <div class='beyond-wpdb-settings-wrap'>
             <!-- title -->
+            <h1 style="margin-bottom: 30px;">Beyond WPDB Settings</h1>
+            <!-- form -->
+            <form action='options.php' method='post'>
+                <!-- Group Concat -->
+                <div>
+                    <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                    <?php do_settings_sections( 'croup_concat_section' ); ?>
+                </div>
+                <div>
+                    <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                    <?php do_settings_sections( 'data_init_section' ); ?>
+                </div>
+                <!-- Virtual Column Settings -->
+                <div>
+                    <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                    <?php do_settings_sections( 'virtual_columns_section' ); ?>
+                </div>
+            <div style="margin-bottom: 30px;">
+                <h1 style="margin-bottom: 30px;">Beyond WPDB Settings</h1>
+                <p>
+                    <?php echo $features; ?>
+                </p>
+                <h2><?php echo __( "Settings", "beyond-wpdb" ) ?></h2>
+            </div>
+            <!-- data init -->
+            <div class="data_init_section">
+                <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                <?php do_settings_sections( 'data_init_section' ); ?>
                 <!-- Submit Button -->
+                <?php submit_button(); ?>
+            </form>
+                <p class="submit">
+                    <button id="beyond-wpdb-init-btn" class="button button-primary" <?php echo $disabled; ?>>Update</button>
+                </p>
+            </>
+            <!-- Virtual Column Settings -->
+            <div>
+                <?php settings_fields( 'beyond_wpdb_group' ); ?>
+                <?php do_settings_sections( 'virtual_columns_section' ); ?>
+                <!-- Submit Button -->
+                <p class="submit">
+                    <button id="beyond-wpdb-virtual-columns-btn" class="button button-primary" <?php echo $disabled; ?>>Update</button>
+                </p>
+            </div>
+            <!-- notice -->
+            <div>
+                <h2><?php echo __( "NOTICE", "beyond-wpdb" ) ?></h2>
+                <p><?php echo __( "This plugin sets 4294967295 to group_concat_max_len when it createa an original table with JSON type column.<br>You can change the number by using filter 'beyond_group_concat_max_len'.", "beyond-wpdb" ) ?></p>
+            </div>
         </div>
 <?php
 …
     public function page_init()
+    {
+        // group concat
+        add_settings_section(
+            'setting_section_croup_concat',
+            '<h2>Group Concat</h2>',
+            array( $this, 'print_group_concat_section_info' ),
+            'croup_concat_section'
+        );
+        add_settings_field(
+            '',
+            "group_concat_max_len",
+            array( $this, 'print_croup_concat_field' ),
+            'croup_concat_section',
+            'setting_section_croup_concat'
+        );
+        register_setting(
+            'beyond_wpdb_group',
+            '',
+            'sanitize_text_field'
+        );
+        global $wpdb;
         // data init
         add_settings_section(
             'setting_section_data_init',
             '<h2>Data Init</h2>',
+            '<h2>' . __( "Creating and deleting custom metatables", "beyond-wpdb" ) . '</h2>',
             array( $this, 'print_data_init_section_info' ),
             'data_init_section'
 …
         add_settings_field(
             'data_init',
             "Data Init",
+            'data_init_postmeta_json',
+            __( "Table for postmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: postmeta_beyond</span>",
             array( $this, 'print_data_init_field' ),
             'data_init_section',
+            'setting_section_data_init'
+            'setting_section_data_init',
+            array(
+                'id' => "data_init_{$wpdb->prefix}postmeta_beyond",
+                'class' => "{$wpdb->prefix}postmeta_beyond"
+            )
+        );
+        add_settings_field(
+            'data_init_usermeta_json',
+            __( "Table for usermeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: usermeta_beyond</span>",
+            array( $this, 'print_data_init_field' ),
+            'data_init_section',
+            'setting_section_data_init',
+            array(
+                'id' => "data_init_{$wpdb->prefix}usermeta_beyond",
+                'class' => "{$wpdb->prefix}usermeta_beyond"
+            )
+        );
+        add_settings_field(
+            'data_init_commentmeta_json',
+            __( "Table for commentmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: commentmeta_beyond</span>",
+            array( $this, 'print_data_init_field' ),
+            'data_init_section',
+            'setting_section_data_init',
+            array(
+                'id' => "data_init_{$wpdb->prefix}commentmeta_beyond",
+                'class' => "{$wpdb->prefix}commentmeta_beyond"
+            )
         );
 …
             'beyond_wpdb_group',
             'beyond_wpdb_data_init_name',
             array( $this, 'sanitize_input_columns' )
+            array()
         );
 …
         add_settings_section(
             'virtual_column_section_id',
             '<h2>Virtual Column Settings</h2>',
+            '<h2>' . __( "Virtual Column Settings", "beyond-wpdb" ) . '</h2>',
             array( $this, 'print_virtual_column_section_info' ),
             'virtual_columns_section'
 …
         add_settings_field(
             'postmeta_json', // id
             "postmeta_json", // title
+            __( "Table for postmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: postmeta_beyond</span>", // title
             array( $this, 'print_postmeta_json_field' ), // callback
             'virtual_columns_section', // section page
+            'virtual_column_section_id' // section id
+            'virtual_column_section_id', // section id,
+            array(
+                'id' => "virtual_column_{$wpdb->prefix}postmeta_beyond",
+                'class' => "virtual_column_{$wpdb->prefix}postmeta_beyond"
+            )
         );
         add_settings_field(
             'usermeta_json',
             "usermeta_json",
+            __( "Table for usermeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: usermeta_beyond</span>",
             array( $this, 'print_usermeta_json_field' ),
             'virtual_columns_section', // section page
+            'virtual_column_section_id' // section id
+            'virtual_column_section_id', // section id
+            array(
+                'id' => "virtual_column_{$wpdb->prefix}usermeta_beyond",
+                'class' => "virtual_column_{$wpdb->prefix}usermeta_beyond"
+            )
         );
         add_settings_field(
             'commentmeta_json', // id
             "commentmeta_json", // title
+            __( "Table for commentmeta", "beyond-wpdb" ) . "<br><span style='font-size: 0.7rem; color: #808080;'>Table name: commentmeta_beyond</span>", // title
             array( $this, 'print_commentmeta_json_field' ), // callback
             'virtual_columns_section', // section id
+            'virtual_column_section_id'
+            'virtual_column_section_id',
+            array(
+                'id' => "virtual_column_{$wpdb->prefix}commentmeta_beyond",
+                'class' => "virtual_column_{$wpdb->prefix}commentmeta_beyond"
+            )
         );
 …
             'beyond_wpdb_group',
             'beyond_wpdb_virtual_column_name',
+            array( $this, 'sanitize_input_columns' )
+        );
+    }
+    /**
+     * @param $option
+     * @param $value
+     */
+    public function do_after_add_virtual_column( $option, $value ) {
+        $this->do_virtual_column_processing( $option );
+    }
+    /**
+     * @param $option
+     * @param $value
+     */
+    public function do_after_add_data_init( $option, $value ) {
+        $this->do_data_init_processing( $option );
+    }
+    /**
+     * @param $old
+     * @param $value
+     * @param $option
+     */
+    public function do_after_update_virtual_column( $old, $value, $option ) {
+        $this->do_virtual_column_processing( $option );
+    }
+    /**
+     * @param $old
+     * @param $value
+     * @param $option
+     */
+    public function do_after_update_data_init( $old, $value, $option ) {
+        $this->do_data_init_processing( $option );
+    }
+    /**
+     * @param $option
+     * create virtual column and delete virtual column and delete option
+     */
+    public function do_virtual_column_processing( $option ) {
+        $this->create_virtual_column_and_index( get_option( $option ) );
+        $this->delete_virtual_column( get_option( $option ) );
+        delete_option( $option );
+    }
+    /**
+     * @param $option
+     * data init
+     */
+    public function do_data_init_processing( $option ) {
+        $this->data_init( get_option($option) );
+        delete_option( $option );
+    }
+    /**
+     * @param $input
+     *
+     * @return array
+     */
+    public function sanitize_input_columns( $input )
+    {
+        $new_input = array();
+        if( isset( $input['postmeta_json'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['postmeta_json'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['postmeta_json'] = implode( PHP_EOL, $input_columns );
+        }
+        if( isset( $input['usermeta_json'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['usermeta_json'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['usermeta_json'] = implode( PHP_EOL, $input_columns );
+        }
+        if( isset( $input['commentmeta_json'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['commentmeta_json'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['commentmeta_json'] = implode( PHP_EOL, $input_columns );
+        }
+        if( isset( $input['data_init'] ) ) {
+            $input_columns = explode( PHP_EOL, $input['data_init'] );
+            $input_columns = $this->sanitize( $input_columns );
+            $new_input['data_init'] = implode( PHP_EOL, $input_columns );
+        }
+        return $new_input;
+    }
+    /**
+     * @param $option
+     * data init
+     */
+    public function data_init( $option )
+    {
+        if ( isset( $option['data_init'] ) && $option['data_init'] === '1' ) {
+            $beyond_wpdb_sql = new Beyond_Wpdb_Sql();
+            $beyond_wpdb_sql->data_init();
+        }
+    }
+    /**
+     * @param $input_columns
+     *
+     * @return string
+     */
+    public function sanitize( $input_columns )
+    {
+        foreach ( $input_columns as $key => $value ) {
+            $input_columns[$key] = sanitize_text_field( $value );
+        }
+        return $input_columns;
+    }
+    /**
+     * @param $options
+     * create virtual column
+     */
+    public function create_virtual_column_and_index( $options ) {
+        global $wpdb;
+        if ( is_array( $options ) ) {
+            foreach ( $options as $key => $option ) {
+                if ( 'postmeta_json' === $key ) {
+                    $type = 'post';
+                } elseif ( 'usermeta_json' === $key ) {
+                    $type = 'user';
+                } else {
+                    $type = 'comment';
+                }
+                $table_name = $this->get_json_table_name( $type );
+                $exist_columns = $this->columns[$type];
+                $option = explode( PHP_EOL, $option );
+                foreach ( $option as $value ) {
+                    $value = esc_sql( $value );
+                    // If $value already exists, continue
+                    if ( in_array( $value, $exist_columns ) ) {
+                        continue;
+                    }
+                    $json_key = '$.' . $value;
+                    // create virtual column
+                    $sql = "ALTER TABLE {$table_name} ADD {$value} VARCHAR(255) GENERATED ALWAYS AS ( JSON_UNQUOTE( JSON_EXTRACT( json, '$json_key' ) ) )";
+                    $wpdb->query( $sql );
+                    // create index
+                    $sql = "ALTER TABLE {$table_name} ADD INDEX ({$value})";
+                    $wpdb->query( $sql );
+                }
+            }
+        }
+    }
+    /**
+     * @param $options
+     * delete virtual columns
+     * @return mixed
+     */
+    public function delete_virtual_column( $options ) {
+        global $wpdb;
+        if ( is_array( $options ) ) {
+            foreach ( $options as $key => $option ) {
+                if ( 'postmeta_json' === $key ) {
+                    $type = 'post';
+                } elseif ( 'usermeta_json' === $key ) {
+                    $type = 'user';
+                } else {
+                    $type = 'comment';
+                }
+                $table_name = $this->get_json_table_name( $type );
+                $exist_columns = $this->columns[$type];
+                $option = explode( PHP_EOL, $option );
+                foreach ( $exist_columns as $column ) {
+                    if ( ! in_array( $column, $option ) ) {
+                        $sql = "ALTER TABLE {$table_name} DROP COLUMN {$column}";
+                        $wpdb->query( $sql );
+                    }
+                }
+            }
+        }
+    }
+    public function get_json_table_name( $type ) {
+        return esc_sql( constant( beyond_wpdb_get_define_table_name( $type ) ) );
+            array()
+        );
+    }
 …
     public function print_virtual_column_section_info()
+    {
         print '<p>
                  Enter a list metakeys for which you want to create a virtual column.Each metakey should be separated by return enter key.<br>
                  The virtual column is indexed and the search uses the virtual column, which makes it faster.
                </p>';
+        $info = __( "When you enter a key of meta information separated by a new line,<br>the specified meta key from the JSON type column is set as a virtual column and the index is pasted.<br>You can further speed up data calls by specifying the most commonly used meta keys for meta queries.", "beyond-wpdb" );
+        print "<p>
+                 {$info}
+               </p>";
+    }
 …
      * print postmeta json field
      */
+    public function print_postmeta_json_field()
+    {
+        $value = implode( PHP_EOL, $this->columns['post'] );
+        printf(
+            '<textarea rows="3" cols="40" id="postmeta_json" name="beyond_wpdb_virtual_column_name[postmeta_json]">%s</textarea>',
+            $value
+        );
+    public function print_postmeta_json_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <textarea class='create_virtualColumns_text-area d-none' rows='3' cols='40' id='{$id}_textarea' name='{$id}' {$disabled}></textarea>
+        <span class='create_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+        <span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
 …
      * print usermeta json field
      */
+    public function print_usermeta_json_field()
+    {
+        $value = implode( PHP_EOL, $this->columns['user'] );
+        printf(
+            '<textarea rows="3" cols="40" id="usermeta_json" name="beyond_wpdb_virtual_column_name[usermeta_json]">%s</textarea>',
+            $value
+        );
+    public function print_usermeta_json_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <textarea class='create_virtualColumns_text-area d-none' rows='3' cols='40' id='{$id}_textarea' name='{$id}' {$disabled}></textarea>
+        <span class='create_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+        <span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
 …
      * print commentmeta json field
      */
+    public function print_commentmeta_json_field()
+    {
+        $value = implode( PHP_EOL, $this->columns['comment'] );
+        printf(
+            '<textarea rows="3" cols="40" id="commentmeta_json" name="beyond_wpdb_virtual_column_name[commentmeta_json]">%s</textarea>',
+            $value
+        );
+    }
+    /**
+     * print group concat section info
+     */
+    public function print_group_concat_section_info()
+    {
+        print '<p>Use GROUP_CONCAT in data init.If the json string consisting of all metas is longer than this value, an error occurs.</p>';
+    }
+    /**
+     * print croup_concat field
+     */
+    public function print_croup_concat_field()
+    {
+        global $wpdb;
+        $group_concat_max_len = $wpdb->get_results( "show variables like 'group_concat_max_len'" );
+        printf(
+            '<p>%s</p>',
+            $group_concat_max_len[0]->Value
+        );
+    public function print_commentmeta_json_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <textarea class='create_virtualColumns_text-area d-none' rows='3' cols='40' id='{$id}_textarea' name='{$id}' {$disabled}></textarea>
+        <span class='create_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+        <span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
 …
     public function print_data_init_section_info()
+    {
+        print '<p>Collect all meta information and re-register it in the json table.</p>';
+    }
+    /**
+     *  print data_init field
+     */
+    public function print_data_init_field()
+    {
+        print '<input type="checkbox" id="data_init" name="beyond_wpdb_data_init_name[data_init]" value="1">';
+        $info = __( "Create and delete your own tables that aggregate the metadata. (Enabled to create, disabled to delete)<br>As long as it is enabled, all meta information will continue to be automatically registered, updated, and deleted in the JSON type columns of the relevant table.<br>When enabled, a new table will be created or initialized, which will take some time. When disabled, the table will be deleted.", "beyond-wpdb" );
+        print $info;
+    }
+    /**
+     * Check to see if each feature is available
+     * @return bool
+     */
+    public function checkDisabled() {
+        return ! $this->privilege_error;
+    }
+    /**
+     * @param $args
+     * print data_init field
+     */
+    public function print_data_init_field( $args )
+    {
+        $id = $args['id'];
+        $disabled = $this->checkDisabled() ? '' : 'disabled';
+        print "
+        <div class='data-init-input-radio d-none'><input type='radio' id='{$id}_active' name='name_{$id}' value='1' {$disabled}>" . "<label for='{$id}_active'>" . __( "activation", "beyond-wpdb" ) . "</label>" .
+        "<input type='radio' id='{$id}_deactive' name='name_{$id}' value='0' {$disabled} style='margin-left: 25px;'>" . "<label for='{$id}_deactive'>" . __( "deactivation", "beyond-wpdb" ) . "</label>" .
+        "<span class='activate_{$id} d-none' style='margin-left: 20px;'>Processing...</span>
+         <span class='success_{$id}' style='margin-left: 20px; display: none;'>Success.</span></div><span class='data-init-input-loading' style='margin-left: 20px;'>Loading...</span>
+        ";
+    }
+}

beyond-wpdb/trunk/class/sql.php

-                      r2330366
+                      r2352101
  */
 class Beyond_Wpdb_Sql {
     function __construct() {
         global $wpdb;
         foreach( array_keys( BEYOND_WPDB_PRIMARYS ) as $primary ) {
+            $this->triggers[$primary] = array();
             $insert_trigger = 'insert_' . $primary . '_trigger';
             $this->$insert_trigger = esc_sql( $wpdb->prefix . 'insert_' . $primary . '_trigger' );
+            array_push( $this->triggers[$primary], $this->$insert_trigger );
             $delete_trigger = 'delete_' . $primary . '_trigger';
             $this->$delete_trigger = esc_sql( $wpdb->prefix . 'delete_' . $primary . '_trigger' );
+            array_push( $this->triggers[$primary], $this->$delete_trigger );
             $insert_meta_trigger = 'insert_' . $primary . 'meta_trigger';
             $this->$insert_meta_trigger = esc_sql( $wpdb->prefix . 'insert_' . $primary . 'meta_trigger' );
+            array_push( $this->triggers[$primary], $this->$insert_meta_trigger );
             $update_meta_trigger = 'update_' . $primary . 'meta_trigger';
             $this->$update_meta_trigger = esc_sql( $wpdb->prefix . 'update_' . $primary . 'meta_trigger' );
+            array_push( $this->triggers[$primary], $this->$update_meta_trigger );
             $delete_meta_trigger = 'delete_' . $primary . 'meta_trigger';
             $this->$delete_meta_trigger = esc_sql( $wpdb->prefix . 'delete_' . $primary . 'meta_trigger' );
+            array_push( $this->triggers[$primary], $this->$delete_meta_trigger );
+        }
+    }
 …
      * Create table
+     *
      * @return void
      */
     function create_table() : void {
         foreach( BEYOND_WPDB_PRIMARYS as $primary => $values ) {
             $this->create_table_sql( $primary, $values['meta_table_key'] );
+        }
+     * @param string $type
+     * @return void
+     */
+    function create_table( $primary ) : void {
+        $values = BEYOND_WPDB_PRIMARYS[$primary];
+        $this->create_table_sql( $primary, $values['meta_table_key'] );
+    }
 …
      * Drop table
+     *
      * @return void
      */
     function drop_table() {
         foreach( array_keys( BEYOND_WPDB_PRIMARYS ) as $primary ) {
             $this->drop_table_sql( $primary );
+        }
+     * @param $primary
+     * @return void
+     * @throws Exception
+     */
+    function drop_table( $primary ) {
+        $this->drop_table_sql( $primary );
+    }
 …
      * @param string $primary
      * @return void
+     * @throws Exception
      */
     protected function drop_table_sql( $primary ) {
         global $wpdb;
+        $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
+        $sql = 'DROP TABLE ' . $table_name;
+        $wpdb->query( $sql );
+        $exist_tables = $this->get_exist_json_tables();
+        $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
+        if ( in_array( $table_name, $exist_tables ) ) {
+            $sql = 'DROP TABLE ' . $table_name;
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * Insert/Update/Delete postmeta trigger
+     *
      * @return void
      */
     function create_trigger() {
         foreach( BEYOND_WPDB_PRIMARYS as $primary => $values ) {
             $this->insert_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
             $this->delete_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
             $this->insert_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
             $this->update_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
             $this->delete_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+        }
+     * @param string $primary
+     * @return void
+     * @throws Exception
+     */
+    function create_trigger( $primary ) {
+        $values = BEYOND_WPDB_PRIMARYS[$primary];
+        $this->insert_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
+        $this->delete_primary_trigger( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_key'] );
+        $this->insert_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+        $this->update_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+        $this->delete_meta_trigger( $primary, $values['meta_table_name'], $values['meta_table_key'] );
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function insert_primary_trigger( $primary, $primaty_table_name, $primary_table_key, $meta_table_key ) {
 …
         $insert_trigger = 'insert_' . $primary . '_trigger';
+        $exist_triggers = $this->get_exist_triggers();
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
         $primaty_table_name = esc_sql( $primaty_table_name );
 …
         $primary_table_key = esc_sql( $primary_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$insert_trigger . ' AFTER
+        if ( ! in_array( $this->$insert_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$insert_trigger . ' AFTER
                 INSERT ON
                 ' . $primaty_table_name . '
 …
                 VALUES
                     (NEW.' . $primary_table_key . ', "{}" )';
+        $wpdb->query( $sql );
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function delete_primary_trigger( $primary, $primaty_table_name, $primary_table_key, $meta_table_key ) {
 …
         $delete_trigger = 'delete_' . $primary . '_trigger';
+        $exist_triggers = $this->get_exist_triggers();
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
         $primaty_table_name = esc_sql( $primaty_table_name );
 …
         $primary_table_key = esc_sql( $primary_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$delete_trigger . ' BEFORE
+        DELETE ON
+        ' . $primaty_table_name . '
+        FOR
+        EACH
+        ROW
+        DELETE FROM ' . $table_name . '
+        WHERE ' . $meta_table_key . ' = OLD.' . $primary_table_key;
+        $wpdb->query( $sql );
+        if ( ! in_array( $this->$delete_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$delete_trigger . ' BEFORE
+            DELETE ON
+            ' . $primaty_table_name . '
+            FOR
+            EACH
+            ROW
+            DELETE FROM ' . $table_name . '
+            WHERE ' . $meta_table_key . ' = OLD.' . $primary_table_key;
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function insert_meta_trigger( $primary, $meta_table_name, $meta_table_key ) {
         global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
         $insert_meta_trigger = 'insert_' . $primary . 'meta_trigger';
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
 …
         $meta_table_key = esc_sql( $meta_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$insert_meta_trigger . ' AFTER
+        if ( ! in_array( $this->$insert_meta_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$insert_meta_trigger . ' AFTER
                 INSERT ON
                 ' . $meta_table_name . '
 …
                 (`json`, CONCAT
                 ("$.",NEW.meta_key), NEW.meta_value) WHERE ' . $meta_table_key . ' = NEW.'.$meta_table_key;
+        $wpdb->query( $sql );
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function update_meta_trigger( $primary, $meta_table_name, $meta_table_key ) {
         global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
         $update_meta_trigger = 'update_' . $primary . 'meta_trigger';
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
 …
         $meta_table_key = esc_sql( $meta_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$update_meta_trigger . ' AFTER
+        if ( ! in_array( $this->$update_meta_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$update_meta_trigger . ' AFTER
                 UPDATE ON
                 ' . $meta_table_name . '
 …
                 (`json`, CONCAT
                 ("$.",NEW.meta_key), NEW.meta_value) WHERE ' . $meta_table_key . ' = NEW.' . $meta_table_key;
+        $wpdb->query( $sql );
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
 …
      * @param string $meta_table_key
      * @return void
+     * @throws Exception
      */
     protected function delete_meta_trigger( $primary, $meta_table_name, $meta_table_key ) {
         global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
         $delete_meta_trigger = 'delete_' . $primary . 'meta_trigger';
         $table_name = esc_sql( constant( beyond_wpdb_get_define_table_name( $primary ) ) );
 …
         $meta_table_key = esc_sql( $meta_table_key );
+        $sql = 'CREATE TRIGGER ' . $this->$delete_meta_trigger . ' AFTER
+        if ( ! in_array( $this->$delete_meta_trigger, $exist_triggers ) ) {
+            $sql = 'CREATE TRIGGER ' . $this->$delete_meta_trigger . ' AFTER
                 DELETE ON
                 ' . $meta_table_name . '
 …
                 `json` = JSON_REMOVE(`json`, CONCAT
                 ("$.",OLD.meta_key)) WHERE ' . $meta_table_key . ' = OLD.'. $meta_table_key;
+            $wpdb->query( $sql );
+            if ( $wpdb->last_error ) {
+                throw new Exception( $wpdb->last_error );
+            }
+        }
+    }
+    /**
+     * Drop triggers
+     *
+     * @param $primary
+     * @return void
+     * @throws Exception
+     */
+    function drop_triggers( $primary ) {
+        global $wpdb;
+        $exist_triggers = $this->get_exist_triggers();
+        foreach ( $this->triggers[$primary] as $value ) {
+            if ( in_array( $value, $exist_triggers ) ) {
+                $sql = 'DROP TRIGGER ' . esc_sql( $value );
+                $wpdb->query( $sql );
+            }
+        }
+        if ( $wpdb->last_error ) {
+            throw new Exception( $wpdb->last_error );
+        }
+    }
+    /**
+     * Update table from post, postmeta table.
+     *
+     * @param $primary
+     *
+     * @throws Exception
+     */
+    function data_init( $primary ) {
+        global $wpdb;
+        $values = BEYOND_WPDB_PRIMARYS[$primary];
+        $maxlen = 4294967295;
+        $maxlen = apply_filters( 'beyond_group_concat_max_len', $maxlen );
+        $sql = 'SET SESSION group_concat_max_len = ' . $maxlen;
         $wpdb->query( $sql );
+    }
+    /**
+     * Drop triggers
+     *
+     * @return void
+     */
+    function drop_triggers() {
+        global $wpdb;
+        foreach( get_object_vars( $this ) as $value ) {
+            $sql = 'DROP TRIGGER ' . esc_sql( $value );
+            $wpdb->query( $sql );
+        }
+    }
+    /**
+     * Update table from post, postmeta table.
+     *
+     * @return void
+     */
+    function data_init() {
+        foreach( BEYOND_WPDB_PRIMARYS as $primary => $values ) {
+            $this->data_init_sql( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_name'], $values['meta_table_key'] );
+            $this->delete_non_existent_data_from_json( $primary, $values['primary_table_name'], $values['primary_table_key'] );
+        }
+        $this->data_init_sql( $primary, $values['primary_table_name'], $values['primary_table_key'], $values['meta_table_name'], $values['meta_table_key'] );
+        $this->delete_non_existent_data_from_json( $primary, $values['primary_table_name'], $values['primary_table_key'] );
+    }
 …
      * data init sql
+     *
+     * @param string $primary
+     * @param string $primary_table_name
+     * @param string $primary_table_key
+     * @param string $meta_table_name
+     * @param string $meta_table_key
+     * @return void
+     * @param $primary
+     * @param $primary_table_name
+     * @param $primary_table_key
+     * @param $meta_table_name
+     * @param $meta_table_key
+     *
+     * @throws Exception
      */
     protected function data_init_sql( $primary, $primary_table_name, $primary_table_key, $meta_table_name, $meta_table_key ) {
 …
                 UPDATE json = VALUES(json)';
         $wpdb->query( $sql );
+        if ( $wpdb->last_error ) {
+            throw new Exception( $wpdb->last_error );
+        }
+    }
 …
      * @param $primary_table_key
      * @return void
+     * @throws Exception
      */
     protected function delete_non_existent_data_from_json( $primary, $primary_table_name, $primary_table_key ) {
 …
         $wpdb->query( $sql );
+        if ( $wpdb->last_error ) {
+            throw new Exception( $wpdb->last_error );
+        }
+    }
+    /**
+     * Get exist virtual columns
+     * @return array
+     */
+    protected function get_exist_triggers() {
+        $beyond_wpdb_info = new Beyond_Wpdb_Information();
+        $beyond_wpdb_info->set_triggers();
+        return $beyond_wpdb_info->get_triggers();
+    }
+    /**
+     * Get exist json tables
+     * @return array
+     */
+    protected function get_exist_json_tables(){
+        $beyond_wpdb_info = new Beyond_Wpdb_Information();
+        $beyond_wpdb_info->set_tables();
+        return $beyond_wpdb_info->get_tables();
+    }
+}

beyond-wpdb/trunk/readme.txt

-                      r2337807
+                      r2352101
 Contributors: pressmaninc,kazunao,hiroshisekiguchi,hommakoharu,pmhirotaka
 Tags: pressman,pressmaninc,json,meta_query,wpdb,database,fast,speed,speed up,sql,replace sql
 Stable tag: 1.1.0
+Stable tag: 2.0.0
 License: GPLv2 or later
 License URI: http://www.gnu.org/licenses/gpl-2.0.html
 …
 Requires MariaDB: 10.2
 Tested up to: 5.4
+※ Table names have been changed since v2.0.Please delete the meta_json table and reactivate it in the options screen.
 Are you having trouble with slow queries due to WordPress table structure?
 …
 == Actual Behavior ==
+* You can create and delete json tables and triggers and data for each json table on the configuration page.
+* Create virtual columns and indexes for the json table you created in the configuration page.
+* If you add a virtual column to the json table in the configuration page, the SQL is converted to face the virtual column.
 * Converts the SQL that WordPress auto-generates when using the default APIs of get_posts, Wp_Query, and etc. into a simple SQL that combines the posts/users/comment table with its own meta table.
 * The custom meta table automatically stores data using MySQL and MariaDB triggers.
 …
 == Custom meta tables generated by plugin ==
+* postmeta_json (for posts table)
+* usermeta_json (for users table)
+* commentmeta_json (for comment table)
+The actual table names are prefixed with $table_prefix.
+* postmeta_beyond (for posts table)
+* usermeta_beyond (for users table)
+* commentmeta_beyond (for comment table)
 == Columns in each table ==
+* postmeta_json
+The actual table names are prefixed with $table_prefix.
+* postmeta_beyond
     * post_id(int)
     * json(json)
 * usermeta_json
+* usermeta_beyond
     * post_id(int)
     * json(json)
 * commentmeta_json
+* commentmeta_beyond
     * post_id(int)
     * json(json)
 …
 ex1 ) Create 100 posts and 50 meta data for each post. Combine them 9 times.
 * postmeta_json
+* postmeta_beyond
     * Equal:0.04
     * IN:0.09
 …
     * BETWEEN:0.04
     * NOT BETWEEN:0.04
 * postmeta
     * Equal:4.72
 …
     * NOT BETWEEN:0.63
+ex2 ) 10000 posts, 1 meta(category) per post.If you add a virtual column and put an index in Category, or if you add a virtual column and put an index in Category
+* postmeta_json(without virtual column)
+ex2 ) 10,000 posts, 1 meta per post. meta_key is category.
+      Add a virtual column(category) to postmeta_beyond and put an index,
+      and compare it to the case without adding a virtual column.
+* postmeta_beyond(with virtual column)
+    * Equal:0.02
+    * IN:0.35
+    * BETWEEN:0.12
+    * NOT BETWEEN:0.09
+* postmeta_beyond(without virtual column)
     * Equal:0.15
     * IN:1.92
     * BETWEEN:0.37
     * NOT BETWEEN:0.29
-* postmeta_json(with virtual column)
-    * Equal:0.02
-    * IN:0.35
-    * BETWEEN:0.12
-    * NOT BETWEEN:0.09
 == Installation ==
 …
 * You'll need TRIGGER, SELECT, and UPDATE permissions for the target table and its columns to use the trigger feature of MySQL and MariaDB.
 * It makes changes to SQL only if `suppress_filters` is false.
+* This plugin sets 4294967295 to group_concat_max_len when it create an original table with JSON type column. You can change the number by using filter 'beyond_group_concat_max_len'.

Note: See TracChangeset for help on using the changeset viewer.

Trac UI Preferences

Download in other formats: