get_row($wpdb->prepare( "SELECT CHARACTER_SET_NAME cs, COLLATION_NAME co FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", $table, $column ), ARRAY_A); self::$columnCollations[$key] = ($row && $row['co']) ? ['charset' => $row['cs'], 'collation' => $row['co']] : null; } return self::$columnCollations[$key]; } /** * SQL expression to use for a SKU column inside plugin <-> WooCommerce joins. * * Normally returns the column untouched, so MySQL can use its index. Only when * actionwear_products.sku and wc_product_meta_lookup.sku have different collations * — which makes the join fail with "Illegal mix of collations" (#1267) and silently * returns 0 rows — the column is converted to the WooCommerce charset/collation. * Migration 1.2.4 aligns the columns, so this is just a safety net for installations * where the ALTER could not run. */ public static function skuJoinExpr($column) { $wc = self::getColumnCollation($GLOBALS['wpdb']->prefix . 'wc_product_meta_lookup', 'sku'); $plugin = self::getColumnCollation($GLOBALS['wpdb']->prefix . 'actionwear_products', 'sku'); if (!$wc || !$plugin || $wc['collation'] === $plugin['collation']) { return $column; } return "CONVERT({$column} USING {$wc['charset']}) COLLATE {$wc['collation']}"; } /** * Forget memoized collations (after an ALTER that changes them). */ public static function resetCollationCache() { self::$columnCollations = null; } const TABLES = [ "actionwear_log", "actionwear_categories_associations", "actionwear_categories_product", "actionwear_products", "actionwear_brands", "actionwear_categories", "actionwear_recharges_details", "actionwear_recharges", "actionwear_images", "actionwear_orders", "actionwear_addresses", "actionwear_jobs", "actionwear_sync_state" ]; public static function truncateAll($exclude = []) { global $wpdb; $wpdb->query("SET FOREIGN_KEY_CHECKS = 0;"); foreach (self::TABLES as $table) { if (!in_array($table, $exclude)) $wpdb->query("TRUNCATE $wpdb->prefix$table"); } $wpdb->query("SET FOREIGN_KEY_CHECKS = 1;"); } public static function addPrefix($table) { global $wpdb; return $wpdb->prefix . $table; } public static function install() { global $wpdb; $current_db_version = get_option("_ACTIONWEAR_DB_VERSION"); foreach (self::TABLES as $table) { $$table = self::addPrefix($table); } $wp_terms = $wpdb->prefix . "terms"; $charset_collate = $wpdb->get_charset_collate(); $sql = <<prefix}actionwear_products ADD UNIQUE(`sku`)", "ALTER TABLE {$wpdb->prefix}actionwear_products ADD INDEX `idx_wc_status` (`wc_to_create`, `wc_created`)", "ALTER TABLE {$wpdb->prefix}actionwear_products ADD INDEX `idx_products_brand` (`brand_name`)", "ALTER TABLE {$wpdb->prefix}actionwear_brands ADD UNIQUE(`id_actionwear`)", "ALTER TABLE {$wpdb->prefix}actionwear_categories ADD INDEX(`id_actionwear`)", "ALTER TABLE {$wpdb->prefix}actionwear_categories ADD UNIQUE(`id_actionwear`)", "ALTER TABLE {$wpdb->prefix}actionwear_categories_product ADD INDEX `idx_id_product` (`id_product`)", "ALTER TABLE {$wpdb->prefix}actionwear_categories_product ADD INDEX `idx_id_category` (`id_category`)", "ALTER TABLE {$wpdb->prefix}actionwear_images ADD INDEX `idx_sku` (`sku`)", "ALTER TABLE {$wpdb->prefix}actionwear_images ADD UNIQUE INDEX `idx_images_sku` (`sku`)", ]; // force add of unique keys foreach ($queries as $query) { $wpdb->query($query); } } } }