<?php /** @noinspection SqlNoDataSourceInspection */

class ImfsQueries {

  /** get cell data for byte counts
   *
   * @param number $bytes
   * @param null $unit
   * @param string $prefix
   *
   * @return string
   */
  public static function byteCell( $bytes, $unit = null, $prefix = '' ) {
    if ( $bytes === 0.0 ) {
      return $prefix !== '' ? '' : '0';
    }
    if ( $unit === null ) {
      $unit = self::getByteUnit( $bytes );
    }

    return $prefix . number_format_i18n( $bytes / $unit[0], $unit[2] ) . $unit[1];
  }

  public static function getByteUnit( $bytes ) {
    if ( $bytes >= 1024 * 1024 * 1024 * 1024 ) {
      $unit = [ 1024 * 1024 * 1024 * 1024, 'TiB', 0 ];
    } else if ( $bytes >= 1024 * 1024 * 1024 * 1024 * 0.5 ) {
      $unit = [ 1024 * 1024 * 1024 * 1024, 'TiB', 1 ];
    } else if ( $bytes >= 1024 * 1024 * 1024 ) {
      $unit = [ 1024 * 1024 * 1024, 'GiB', 0 ];
    } else if ( $bytes >= 1024 * 1024 * 1024 * 0.5 ) {
      $unit = [ 1024 * 1024 * 1024, 'GiB', 1 ];
    } else if ( $bytes >= 1024 * 1024 ) {
      $unit = [ 1024 * 1024, 'MiB', 0 ];
    } else if ( $bytes >= 1024 * 1024 * 0.5 ) {
      $unit = [ 1024 * 1024, 'MiB', 1 ];
    } else if ( $bytes >= 1024 ) {
      $unit = [ 1024, 'KiB', 0 ];
    } else if ( $bytes >= 1024 * 0.1 ) {
      $unit = [ 1024, 'KiB', 1 ];
    } else {
      $unit = [ 1, 'B', 0 ];
    }

    return $unit;
  }


  public static function date ( $time ) {
    $date_format = get_option( 'date_format' ) . ' ' . get_option( 'time_format' );
    return get_date_from_gmt( date( 'Y-m-d H:i:s', $time ), $date_format );

  }
  public static function percent ( $num, $denom = null, $points = 1 ) {
    if ( $denom ) {
      $num = $num / $denom;
    }
    return number_format ( 100.0 * $num, $points );
  }
  /** get cell data for microsecond times
   *
   * @param number $time in microseconds (multiply seconds by a million)
   * @param null $unit
   * @param string $prefix
   *
   * @return array
   */
  public static function timeCell( $time, $unit = null, $prefix = '' ) {
    if ( $time === 0.0 ) {
      $displayTime = $prefix !== '' ? '' : '0';

      return [ $displayTime, '0' ];
    }
    $renderTime = $time * 0.000001;
    if ( $unit === null ) {
      $unit = self::getTimeUnit( $renderTime );
    }
    $displayTime = $prefix . number_format_i18n( $renderTime / $unit[0], $unit[2] ) . $unit[1];

    return [ $displayTime, - $renderTime ];
  }

  public static function getTimeUnit( $timeSeconds ) {
    if ( $timeSeconds >= 86400 ) {
      $unit = [ 86400, 'd', 1 ];
    } else if ( $timeSeconds >= 3600 * 0.9 ) {
      $unit = [ 3600, 'h', 1 ];
    } else if ( $timeSeconds >= 60 * 0.9 ) {
      $unit = [ 60, 'm', 1 ];
    } else if ( $timeSeconds >= 0.9 ) {
      $unit = [ 1, 's', 2 ];
    } else if ( $timeSeconds >= 0.1 ) {
      $unit = [ 0.001, 'ms', 0 ];
    } else if ( $timeSeconds >= 0.01 ) {
      $unit = [ 0.001, 'ms', 1 ];
    } else if ( $timeSeconds >= 0.001 ) {
      $unit = [ 0.001, 'ms', 2 ];
    } else {
      $unit = [ 0.000001, 'μs', 0 ];
    }

    return $unit;
  }


  public static function stripPrefix( $name ) {
    global $wpdb;
    $pattern = '/^' . $wpdb->prefix . '/';

    return preg_replace( $pattern, '', $name );
  }

  /** Get version information from the database server
   * @return object
   */
  public static function getMySQLVersion() {
    global $wpdb;
    global $wp_db_version;
    $semver  = " 
	 SELECT VERSION() version,
	        1 canreindex,
	        0 unconstrained,
            CAST(SUBSTRING_INDEX(VERSION(), '.', 1) AS UNSIGNED) major,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '.', 2), '.', -1) AS UNSIGNED) minor,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(VERSION(), '-', '.'), '.', 3), '.', -1) AS UNSIGNED) build,
            '' fork, '' distro";
    $results = $wpdb->get_results( ImfsQueries::tagQuery( $semver ) );
    $results = $results[0];

    $results->db_host = ImfsQueries::redactHost( DB_HOST );
    $ver              = explode( '-', $results->version, 3 );
    $version_string   = $ver[0];
    if ( count( $ver ) >= 2 ) {
      $results->fork = $ver[1];
    }
    if ( count( $ver ) >= 3 ) {
      $results->distro = $ver[2];
    }

    /* check db version ... TODO with new db versions, test again */
    if ( $wp_db_version < index_wp_mysql_for_speed_first_compatible_db_version ||
         ( index_wp_mysql_for_speed_last_compatible_db_version &&
           $wp_db_version > index_wp_mysql_for_speed_last_compatible_db_version ) ) {
      /* fail if we don't have an expected version */
      $results->canreindex = 0;

      return ImfsQueries::makeNumeric( $results );
    }

    $isMaria = ! ! stripos( $results->version, "mariadb" );
    /* work out whether we have Antelope or Barracuda InnoDB format */
    /* mysql 8+ */
    if ( ! $isMaria && $results->major >= 8 ) {
      $results->unconstrained = 1;

      return ImfsQueries::makeNumeric( $results );
    }
    /* work out whether we have Antelope or Barracuda InnoDB format */
    /* mariadb 10.3 + */
    if ( $isMaria && version_compare( $version_string, '10.3.0', 'ge' ) ) {
      $results->unconstrained = 1;

      return ImfsQueries::makeNumeric( $results );
    }
    /* mariadb 10.2 or before */
    if ( $isMaria && version_compare( $version_string, '10.0.0', 'ge' ) ) {

      $results->unconstrained = ImfsQueries::hasLargePrefix();

      return ImfsQueries::makeNumeric( $results );
    }

    /* waaay too old */
    if ( $results->major < 5 ) {
      $results->canreindex = 0;

      return ImfsQueries::makeNumeric( $results );
    }
    /* before 5.5 */
    if ( $results->major == 5 && $results->minor < 5 ) {
      $results->canreindex = 0;

      return ImfsQueries::makeNumeric( $results );
    }
    /* older 5.5 */
    if ( $results->major === 5 && $results->minor === 5 && $results->build < 62 ) {
      $results->canreindex = 0;

      return ImfsQueries::makeNumeric( $results );
    }
    /* older 5.6 */
    if ( $results->major === 5 && $results->minor === 6 && $results->build < 4 ) {
      $results->canreindex = 0;

      return ImfsQueries::makeNumeric( $results );
    }
    $results->unconstrained = ImfsQueries::hasLargePrefix();

    return ImfsQueries::makeNumeric( $results );
  }

  public static function tagQuery( $q ) {
    return $q . '/*' . index_wp_mysql_for_speed_querytag . mt_rand( 0, 999999999 ) . '*/';
  }

  public static function redactHost( $host ) {
    if ( trim( $host ) === '' ) {
      return $host;
    }
    if ( trim( $host ) === '127.0.0.1' ) {
      return $host;
    }
    if ( trim( $host ) === 'localhost' ) {
      return $host;
    }
    if ( trim( $host ) === '::1' ) {
      return $host;
    }

    return "Redacted, not localhost";
  }

  /** Convert values in objects to numbers wherever possible.
   *
   * @param object $ob
   *
   * @return object the input suitable for serializing into JSON
   */
  public static function makeNumeric( $ob ) {
    $result = [];
    foreach ( $ob as $key => $val ) {
      if ( is_numeric( $val ) ) {
        $val = $val + 0;
      }
      $result[ $key ] = $val;
    }

    return (object) $result;
  }

  /**
   * @return int 1 if the MySQL instance supports 3072-byte index columns
   */
  public static function hasLargePrefix() {
    global $wpdb;
    /* innodb_large_prefix variable is missing in MySQL 8+ */
    $prefix = $wpdb->get_results( ImfsQueries::tagQuery( "SHOW VARIABLES LIKE 'innodb_large_prefix'" ), OBJECT_K );
    if ( $prefix && is_array( $prefix ) && array_key_exists( 'innodb_large_prefix', $prefix ) ) {
      $prefix = $prefix['innodb_large_prefix'];

      return ( $prefix->Value === 'ON' || $prefix->Value === '1' ) ? 1 : 0;
    }

    return 0;
  }

  /**
   * Query the total size usage of the database.
   *
   * @return string
   */
  public static function getCurrentDbTableSizesQuery() {
    return "SELECT SUM(IF(ENGINE='InnoDB' AND TABLE_SCHEMA = DATABASE(), DATA_LENGTH,0)) innodb_data_len, 
                   SUM(IF(ENGINE='InnoDB' AND TABLE_SCHEMA = DATABASE(), INDEX_LENGTH,0)) innodb_key_len, 
                   SUM(IF(ENGINE='InnoDB' AND TABLE_SCHEMA = DATABASE(), 1,0)) innodb_table_count, 
                   SUM(IF(ENGINE<>'InnoDB' AND TABLE_SCHEMA = DATABASE(), DATA_LENGTH,0)) myisam_data_len, 
                   SUM(IF(ENGINE<>'INNODB' AND TABLE_SCHEMA = DATABASE(), INDEX_LENGTH,0)) myisam_key_len,
                   SUM(IF(ENGINE<>'InnoDB' AND TABLE_SCHEMA = DATABASE(), 1,0)) myisam_table_count, 
                   SUM(IF(ENGINE='InnoDB', DATA_LENGTH,0)) innodb_data_total, 
                   SUM(IF(ENGINE='InnoDB', INDEX_LENGTH,0)) innodb_key_total, 
                   SUM(IF(ENGINE='InnoDB', 1,0)) innodb_table_total, 
                   SUM(IF(ENGINE<>'InnoDB', DATA_LENGTH,0)) myisam_data_total, 
                   SUM(IF(ENGINE<>'InnoDB', INDEX_LENGTH,0)) myisam_key_total,
                   SUM(IF(ENGINE<>'InnoDB', 1,0)) myisam_table_total,
                   COUNT(DISTINCT TABLE_SCHEMA) database_count
              FROM information_schema.TABLES
            WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')";
  }
  /** @noinspection PhpUnused */
  public static function getTableStatsQuery() {
    global $wpdb;
    $p = $wpdb->prefix;

    return
      "SELECT REPLACE(t.TABLE_NAME, '$p', '') AS 'table',
               '$p' AS 'prefix',
                MAX(t.TABLE_ROWS) AS 'count',
                MAX(p.CARDINALITY) AS distinct_id,
                MIN(k.CARDINALITY) AS distinct_key,
                MAX(autoload.autoload_count) AS autoload_count,
                MAX(t.ENGINE) AS engine,
                MAX(t.ROW_FORMAT) AS row_format,
                MAX(t.TABLE_COLLATION) AS collation,
                MAX(t.DATA_LENGTH) AS data_length,
                MAX(t.INDEX_LENGTH) AS index_length
             FROM information_schema.TABLES t
             LEFT JOIN information_schema.STATISTICS p 
				       ON t.TABLE_SCHEMA = p.TABLE_SCHEMA
						AND t.TABLE_NAME = p.TABLE_NAME
						AND LOWER(p.COLUMN_NAME) LIKE '%id'
						AND p.CARDINALITY < t.TABLE_ROWS
             LEFT JOIN information_schema.STATISTICS k 
				       ON t.TABLE_SCHEMA = k.TABLE_SCHEMA
						AND t.TABLE_NAME = k.TABLE_NAME
						AND LOWER(k.COLUMN_NAME) LIKE '%_key'
             LEFT JOIN (
                  SELECT '{$p}options' TABLE_NAME,
                         DATABASE() TABLE_SCHEMA,    
                         SUM(autoload = 'yes') autoload_count
                    FROM {$p}options
                  ) autoload 
				       ON t.TABLE_SCHEMA = autoload.TABLE_SCHEMA
						AND t.TABLE_NAME = autoload.TABLE_NAME
             WHERE t.TABLE_SCHEMA = DATABASE() 
               AND t.TABLE_NAME IN 
               ('{$p}postmeta','{$p}termmeta','{$p}usermeta' ,'{$p}posts','{$p}comments', '{$p}options', '{$p}users', '{$p}commentmeta', 
                 '{$p}woocommerce_order_itemmeta','{$p}wc_orders_meta','{$p}automatewoo_log_meta')
             GROUP BY REPLACE(t.TABLE_NAME, 'wp_', '')";
  }

  /** get a query to list the tables, with approx row count, engine, row_format, and collation
   * @return string
   */
  public static function getTableCountsQuery() {
    global $wpdb;
    $p = $wpdb->prefix;

    return
      "SELECT REPLACE(t.TABLE_NAME, '$p', '') AS 'table',
               '$p' AS 'prefix',
                t.TABLE_ROWS AS 'count',
                t.ENGINE AS engine,
                t.ROW_FORMAT AS row_format,
                t.TABLE_COLLATION AS collation
             FROM information_schema.TABLES t
             WHERE t.TABLE_SCHEMA = DATABASE() 
               AND t.TABLE_NAME IN  ('{$p}postmeta','{$p}termmeta','{$p}usermeta' ,'{$p}posts','{$p}comments', '{$p}options', '{$p}users', '{$p}commentmeta', 
                 '{$p}woocommerce_order_itemmeta','{$p}wc_orders_meta','{$p}automatewoo_log_meta')";
  }

  public static function getTableFormatsQuery() {
    return "
            SELECT t.TABLE_NAME,
                   t.ENGINE,
                   t.ROW_FORMAT,
                   t.TABLE_ROWS row_count
                 FROM information_schema.TABLES t
                 WHERE t.TABLE_SCHEMA = DATABASE()
                   AND t.TABLE_TYPE = 'BASE TABLE'
                   AND t.ENGINE IS NOT NULL";
  }

  /** @noinspection PhpUnused */
  public static function getFullTableFormatsQuery() {
    return "
            SELECT c.TABLE_NAME,
                   t.ENGINE,
                   t.ROW_FORMAT,
                   COUNT(*) column_count,
                   t.TABLE_ROWS row_count,
                   SUM(IF(c.DATA_TYPE = 'varchar', c.CHARACTER_OCTET_LENGTH, 0)) varchar_total_octets,
                   MAX(IF(c.DATA_TYPE = 'varchar', c.CHARACTER_OCTET_LENGTH, 0)) varchar_max_octets,
                   SUM(IF(c.DATA_TYPE = 'varchar', c.CHARACTER_MAXIMUM_LENGTH, 0)) varchar_total_length,
                   MAX(IF(c.DATA_TYPE = 'varchar', c.CHARACTER_MAXIMUM_LENGTH, 0)) varchar_max_length,
                   SUM(c.DATA_TYPE= 'varchar') varchar_columns,
                   SUM(IF(c.DATA_TYPE = 'char', c.CHARACTER_OCTET_LENGTH, 0)) char_total_octets,
                   MAX(IF(c.DATA_TYPE = 'char', c.CHARACTER_OCTET_LENGTH, 0)) char_max_octets,
                   SUM(IF(c.DATA_TYPE = 'char', c.CHARACTER_MAXIMUM_LENGTH, 0)) char_total_length,
                   MAX(IF(c.DATA_TYPE = 'char', c.CHARACTER_MAXIMUM_LENGTH, 0)) char_max_length,
                   SUM(c.DATA_TYPE= 'char') char_columns,
                   SUM(IF(c.DATA_TYPE = 'longtext', c.CHARACTER_OCTET_LENGTH, 0)) longtext_total_octets,
                   MAX(IF(c.DATA_TYPE = 'longtext', c.CHARACTER_OCTET_LENGTH, 0)) longtext_max_octets,
                   SUM(IF(c.DATA_TYPE = 'longtext', c.CHARACTER_MAXIMUM_LENGTH, 0)) longtext_total_length,
                   MAX(IF(c.DATA_TYPE = 'longtext', c.CHARACTER_MAXIMUM_LENGTH, 0)) longtext_max_length,
                   SUM(c.DATA_TYPE= 'longtext') longtext_columns,
                   SUM(IF(c.DATA_TYPE = 'text', c.CHARACTER_OCTET_LENGTH, 0)) text_sum_octets,
                   MAX(IF(c.DATA_TYPE = 'text', c.CHARACTER_OCTET_LENGTH, 0)) text_max_octets,
                   SUM(IF(c.DATA_TYPE = 'text', c.CHARACTER_MAXIMUM_LENGTH, 0)) text_total_length,
                   MAX(IF(c.DATA_TYPE = 'text', c.CHARACTER_MAXIMUM_LENGTH, 0)) text_max_length,
                   SUM(c.DATA_TYPE= 'text') text_columns,		        
                   SUM(
                       CASE WHEN c.DATA_TYPE IN ('varchar', 'char') THEN c.CHARACTER_OCTET_LENGTH
                            WHEN c.DATA_TYPE = 'int' THEN 4
                            WHEN c.DATA_TYPE = 'bigint' THEN 8
                            WHEN c.DATA_TYPE = 'float' THEN 4
                            WHEN c.DATA_TYPE = 'double' THEN 8
                            WHEN c.DATA_TYPE = 'date' THEN 3
                            WHEN c.DATA_TYPE = 'time' THEN 3
                            WHEN c.DATA_TYPE = 'timestamp' THEN 4
                            WHEN c.DATA_TYPE = 'datetime' THEN 5
                            ELSE 0 END                
                       ) rowlength
                 FROM information_schema.COLUMNS c
                 JOIN information_schema.TABLES t
                       ON c.TABLE_NAME = t.TABLE_NAME
                      AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
                      AND c.TABLE_CATALOG = t.TABLE_CATALOG
                 WHERE c.TABLE_SCHEMA = DATABASE()
                   AND t.TABLE_TYPE = 'BASE TABLE'
                   AND t.ENGINE IS NOT NULL
                GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA, c.TABLE_CATALOG
            ";
  }

  /** Get the indexes for a table, with name, add, drop columns.
   * @return string
   * @see getFullTableIndexesQuery for a slower but more complete index-description query.
   */
  public static function getTableIndexesQuery() {
    return
      "SELECT key_name, `add`, `drop`
         FROM (
          SELECT             
            IF(tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY', tc.CONSTRAINT_TYPE, CONCAT (s.INDEX_NAME)) key_name,   
            IF(tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY', 1, 0) is_primary,
            CASE WHEN tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY' THEN 1 
                WHEN tc.CONSTRAINT_TYPE LIKE 'UNIQUE' THEN 1
                ELSE 0 END is_unique,
            CONCAT ( 'ADD ',
                CASE WHEN tc.CONSTRAINT_TYPE = 'UNIQUE' THEN CONCAT ('UNIQUE KEY ', s.INDEX_NAME)
                     WHEN tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY' THEN tc.CONSTRAINT_TYPE
                                         ELSE CONCAT ('KEY', ' ', s.INDEX_NAME) END,
                ' (',
                GROUP_CONCAT(
                  IF(s.SUB_PART IS NULL, s.COLUMN_NAME, CONCAT(s.COLUMN_NAME,'(',s.SUB_PART,')'))
                  ORDER BY s.SEQ_IN_INDEX 
                  SEPARATOR ', '),
                ')'
                ) `add`,
            CONCAT ( 'DROP ',
                IF(tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY', tc.CONSTRAINT_TYPE, CONCAT ('KEY', ' ', s.INDEX_NAME))
                ) `drop`
          FROM information_schema.STATISTICS s
          LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
                  ON s.TABLE_NAME = tc.TABLE_NAME
                 AND s.TABLE_SCHEMA = tc.TABLE_SCHEMA
                 AND s.TABLE_CATALOG = tc.CONSTRAINT_CATALOG 
                 AND s.INDEX_NAME = tc.CONSTRAINT_NAME
         WHERE s.TABLE_SCHEMA = DATABASE()
           AND s.TABLE_NAME = %s
           /* #37 don't do anything with FULLTEXT indexes */
           AND s.INDEX_TYPE <> 'FULLTEXT'
         GROUP BY s.INDEX_NAME
        ) q
        ORDER BY is_primary DESC, is_unique DESC, key_name";
  }

  /** Get the index descriptions for a table.
   * @return string
   * @see getTableIndexesQuery for a faster index-description query.
   */
  public static function getFullTableIndexesQuery() {
    return
      "        SELECT *,
               IF(is_autoincrement = 1, columns, NULL) autoincrement_column
         FROM (
          SELECT             
           IF(tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY', tc.CONSTRAINT_TYPE, CONCAT (s.INDEX_NAME)) key_name,   
           s.TABLE_NAME,  
               IF(tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY', 1, 0) is_primary,
               CASE WHEN tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY' THEN 1 
                    WHEN tc.CONSTRAINT_TYPE LIKE 'UNIQUE' THEN 1
                    ELSE 0 END is_unique,
               IF(MAX(c.EXTRA) = 'auto_increment', 1, 0) 'contains_autoincrement',
               IF(MAX(c.EXTRA) = 'auto_increment' AND COUNT(*) = 1, 1, 0) 'is_autoincrement',
               GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX SEPARATOR ', ') 'columns',
               CONCAT ( 'ADD ',
                CASE WHEN tc.CONSTRAINT_TYPE = 'UNIQUE' THEN CONCAT ('UNIQUE KEY ', s.INDEX_NAME)
                     WHEN tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY' THEN tc.CONSTRAINT_TYPE
                                         ELSE CONCAT ('KEY', ' ', s.INDEX_NAME) END,
                ' (',
                GROUP_CONCAT(
                  IF(s.SUB_PART IS NULL, s.COLUMN_NAME, CONCAT(s.COLUMN_NAME,'(',s.SUB_PART,')'))
                  ORDER BY s.SEQ_IN_INDEX 
                  SEPARATOR ', '),
                ')'
                ) `add`,
               CONCAT ( 'DROP ',
                IF(tc.CONSTRAINT_TYPE LIKE 'PRIMARY KEY', tc.CONSTRAINT_TYPE, CONCAT ('KEY', ' ', s.INDEX_NAME))
                ) `drop`,
               CONCAT ('ALTER TABLE `', s.TABLE_SCHEMA, '`.`', s.TABLE_NAME, '` ') `alter`,	
               MAX(t.ENGINE) 'engine',
               MAX(t.ROW_FORMAT) 'row_format'
          FROM information_schema.STATISTICS s
          LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
                  ON s.TABLE_NAME = tc.TABLE_NAME
                 AND s.TABLE_SCHEMA = tc.TABLE_SCHEMA
                 AND s.TABLE_CATALOG = tc.CONSTRAINT_CATALOG 
                 AND s.INDEX_NAME = tc.CONSTRAINT_NAME
          LEFT JOIN information_schema.COLUMNS c
                  ON s.TABLE_NAME = c.TABLE_NAME
                 AND s.TABLE_SCHEMA = c.TABLE_SCHEMA
                 AND s.TABLE_CATALOG = c.TABLE_CATALOG 
                 AND s.COLUMN_NAME = c.COLUMN_NAME
      LEFT JOIN information_schema.TABLES t
                  ON s.TABLE_NAME = t.TABLE_NAME
                 AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
                 AND s.TABLE_CATALOG = t.TABLE_CATALOG
         WHERE s.TABLE_SCHEMA = DATABASE()
           AND s.TABLE_NAME = %s
         GROUP BY TABLE_NAME, INDEX_NAME
        ) q
        ORDER BY TABLE_NAME, is_primary DESC, is_unique DESC, key_name";
  }

  /** Get a pseudo-random text string from letters and numbers.
   *  This function does not generate cryptographically secure values, and should not be used for cryptographic purposes.
   *
   * @param integer $length desired string length
   *
   * @return string random string suitable for upload id and similar purposes
   */
  public static function getRandomString( $length ) {
    /* some characters removed from this set to reduce confusion reading aloud */
    $characters       = '23456789abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRTUVWXYZ';
    $charactersLength = strlen( $characters );
    $randomString     = '';
    for ( $i = 0; $i < $length; $i ++ ) {
      $randomString .= $characters[ mt_rand( 0, $charactersLength - 1 ) ];
    }

    return $randomString;
  }

  /** Transform a result set array of objects, like from SHOW VARIABLES, to an object
   *
   * @param array $rows Array of {Variable_name, Value} objects
   *
   * @return object
   */
  public static function toObject( $rows ) {
    $variables = [];
    foreach ( $rows as $row ) {
      $variables[ $row->Variable_name ] = is_numeric( $row->Value ) ? $row->Value + 0 : $row->Value;
    }

    return (object) $variables;
  }

  /** Make an ordinary 2-column result set object look like a result set array
   *
   * @param object $rows result set to transform
   * @param string $nameCaption the name of the first column, default Item
   * @param string $valueCaption the name of the second column, default Value
   *
   * @return array
   */
  public static function toResultSet( $rows, $nameCaption = 'Item', $valueCaption = 'Value' ) {
    $res = [];
    foreach ( $rows as $name => $value ) {
      $rsrow = [ $nameCaption => $name, $valueCaption => $value ];
      $res[] = $rsrow;
    }

    return $res;
  }

  /** Retrieve WordPress configuration
   *
   * @param ImfsDb $db database instance
   *
   * @return array describing the current WordPress configuration
   */
  public static function getWpDescription( $db ) {
    global $wp_db_version;
    global $wp_version;
    global $_SERVER;
    $dropins    = get_dropins();
    $dropinList = [];
    foreach ( $dropins as $dropin ) {
      $dropinList[] = $dropin ['Name'];
    }
    $extensions = get_loaded_extensions();
    natcasesort( $extensions );
    /** @noinspection PhpUnnecessaryLocalVariableInspection */
    $wordpress = [
      'webserverversion' => $_SERVER['SERVER_SOFTWARE'],
      'wp_version'       => $wp_version,
      'wp_db_version'    => $wp_db_version,
      'phpversion'       => phpversion(),
      'mysqlversion'     => $db->semver->version,
      'pluginversion'    => index_wp_mysql_for_speed_VERSION_NUM,
      'is_multisite'     => is_multisite(),
      'is_main_site'     => is_main_site(),
      'current_blog_id'  => get_current_blog_id(),
      'active_plugins'   => implode( '|', ImfsQueries::getActivePlugins() ),
      'active_dropins'   => implode( '|', $dropinList ),
      'extensions'       => implode( '|', $extensions ),
    ];

    return $wordpress;
  }

  /** Get a list of active plugins.
   * @return array plugin names
   */
  public static function getActivePlugins() {
    $plugins = get_plugins();
    $result  = [];
    foreach ( $plugins as $path => $desc ) {
      if ( is_plugin_active( $path ) ) {
        $result[] = $desc['Name'];
      }
    }

    return $result;
  }

}
