98 lines
6.8 KiB
Bash
Executable file
98 lines
6.8 KiB
Bash
Executable file
#!/bin/bash
|
||
# ============================================================================
|
||
# Fix Corrupted Character Data - Safe Version
|
||
# ============================================================================
|
||
# This script fixes mojibake by generating SQL dynamically to avoid encoding issues
|
||
# ============================================================================
|
||
|
||
DB_USER="root"
|
||
DB_PASS="secureRootPassword"
|
||
DB_NAME="cmc"
|
||
CONTAINER="cmc-db"
|
||
|
||
# Function to run SQL
|
||
run_sql() {
|
||
docker exec $CONTAINER mariadb -u $DB_USER -p$DB_PASS --default-character-set=utf8mb4 $DB_NAME -e "$1"
|
||
}
|
||
|
||
echo "============================================================"
|
||
echo "Fixing Character Corruption - Safe Version"
|
||
echo "============================================================"
|
||
echo ""
|
||
|
||
# Set charset
|
||
run_sql "SET NAMES utf8mb4;"
|
||
|
||
echo "Fixing CUSTOMERS table..."
|
||
# Fix customers - specific known records
|
||
run_sql "UPDATE customers SET name = 'SOLUZÉ CIVIL ENGINEERS Trading Under SOLUZE PTY LTD' WHERE id = 253;"
|
||
run_sql "UPDATE customers SET name = 'Dr. Prem''s Molecules Private Limited (DPMolecules)' WHERE id = 1006;"
|
||
run_sql "UPDATE customers SET name = 'DEE ENTERPRISES (QLD) PTY LTD trading as \"MEKLEK\"' WHERE id = 1387;"
|
||
run_sql "UPDATE customers SET name = 'Guidera O''Connor Pty Ltd' WHERE id = 1608;"
|
||
run_sql "UPDATE customers SET name = 'Ingredion ANZ Pty Ltd' WHERE id = 2174;"
|
||
run_sql "UPDATE customers SET name = 'Vale Nouvelle-Calédonie S.A.S' WHERE id = 2215;"
|
||
run_sql "UPDATE customers SET name = 'Evaluación y Control Ambiental S.A.S.' WHERE id = 2375;"
|
||
run_sql "UPDATE customers SET name = 'Zontahevy Comércio e Serviços Offshore Ltda' WHERE id = 3143;"
|
||
run_sql "UPDATE customers SET name = 'Société des Mines de Syama' WHERE id = 3529;"
|
||
run_sql "UPDATE customers SET name = 'P.J. Berriman & Co PTY LTD' WHERE id = 3633;"
|
||
run_sql "UPDATE customers SET name = 'Rambøll Danmark' WHERE id = 4325;"
|
||
run_sql "UPDATE customers SET name = 'SONNIVA ENERGY MAKİNA İNŞAAT İTH. İHR. LTD. ŞTİ.' WHERE id = 4350;"
|
||
run_sql "UPDATE customers SET name = 'F.C.C. Fluides Conseils Calédonie SARL' WHERE id = 4669;"
|
||
run_sql "UPDATE customers SET name = 'DGPack Prostějov' WHERE id = 4743;"
|
||
run_sql "UPDATE customers SET name = 'Mccready Welding Pty Ltd Trading under the entity Mccready''s Welding Services' WHERE id = 4764;"
|
||
run_sql "UPDATE customers SET name = 'Oxiquímica, S.A.P.I. de C.V.' WHERE id = 4893;"
|
||
|
||
echo "Fixing PRINCIPLES table..."
|
||
run_sql "UPDATE principles SET address = 'Heinz-Fangman-Straße 18' WHERE id = 2;"
|
||
run_sql "UPDATE principles SET address = 'Bezručova 2901\n756 61 Rožnov pod Radhoštěm', city = 'Rožnov pod Radhoštěm' WHERE id = 9;"
|
||
run_sql "UPDATE principles SET name = 'IEP Technologies GmbH - BRILEX Gesellschaft für Explosionsschutz mbH' WHERE id = 13;"
|
||
run_sql "UPDATE principles SET address = 'Liebigstraße 2' WHERE id = 14;"
|
||
run_sql "UPDATE principles SET name = 'Nosia S.r.l.- Señalización Industrial' WHERE id = 58;"
|
||
run_sql "UPDATE principles SET address = 'Alte Emser Straße 32' WHERE id = 65;"
|
||
|
||
echo "Fixing ADDRESSES table..."
|
||
run_sql "UPDATE addresses SET address = 'Lvl 3, Building B, 7–11 Talavera Road' WHERE id = 19;"
|
||
|
||
echo "Fixing PRODUCTS table (specific records)..."
|
||
run_sql "UPDATE products SET title = 'C95SN189C DSTGL40/C-Digital temperature probe for P8xx1 Web Sensor. Range -30 to +80°C. With CINCH connector, 1m Cable' WHERE id = 30;"
|
||
run_sql "UPDATE products SET title = 'Mid-West Instrument Model 240 – SC – 02 – O(TT)' WHERE id = 38;"
|
||
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® II FIBC Static Earthing System, GRP Enclosure with 5m 2 Core Spiral Cable and FIBC Clamp' WHERE id = 67;"
|
||
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® RTR Tester - ER2/CRT' WHERE id = 76;"
|
||
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® Installers Kit A for IIB areas only (Metal Enclosures Only)' WHERE id = 77;"
|
||
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® RTR™ Tri-Mode Static Grounding System, Metal Enclosure, X90-IP Heavy Duty Clamp with 10m 2 Core Spiral Cable' WHERE id = 85;"
|
||
run_sql "UPDATE products SET title = 'Newson Gale Earth-Rite® RTR System Spares - X90-IP Heavy Duty 2 Core Clamp with 10m 2 Core Spiral Cable and Male Quick Connect' WHERE id = 86;"
|
||
run_sql "UPDATE products SET title = 'Newson Gale Bond-Rite® EZ, VESX90-IP Heavy Duty Clamp & 3m 2 Core Spiral Cable' WHERE id = 149;"
|
||
|
||
echo ""
|
||
echo "Fixing HTML entities in products..."
|
||
# These are safe ASCII characters, so no encoding issues
|
||
run_sql "UPDATE products SET title = REPLACE(title, '–', '–') WHERE title LIKE '%–%';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, '–', '–') WHERE description LIKE '%–%';"
|
||
run_sql "UPDATE products SET title = REPLACE(title, '&', '&') WHERE title LIKE '%&%';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, '&', '&') WHERE description LIKE '%&%';"
|
||
run_sql "UPDATE products SET title = REPLACE(title, ' ', ' ') WHERE title LIKE '% %';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, ' ', ' ') WHERE description LIKE '% %';"
|
||
run_sql "UPDATE products SET title = REPLACE(title, '°', '°') WHERE title LIKE '%°%';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, '°', '°') WHERE description LIKE '%°%';"
|
||
run_sql "UPDATE products SET title = REPLACE(title, '½', '½') WHERE title LIKE '%½%';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, '½', '½') WHERE description LIKE '%½%';"
|
||
run_sql "UPDATE products SET title = REPLACE(title, '”', '\"') WHERE title LIKE '%”%';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, '”', '\"') WHERE description LIKE '%”%';"
|
||
run_sql "UPDATE products SET title = REPLACE(title, '“', '\"') WHERE title LIKE '%“%';"
|
||
run_sql "UPDATE products SET description = REPLACE(description, '“', '\"') WHERE description LIKE '%“%';"
|
||
|
||
echo ""
|
||
echo "============================================================"
|
||
echo "Fix complete! Verification:"
|
||
echo "============================================================"
|
||
|
||
run_sql "SELECT 'Remaining corrupted customers:' as status, COUNT(*) as count FROM customers WHERE name REGEXP '[^ -~]';"
|
||
run_sql "SELECT 'Remaining corrupted principles:' as status, COUNT(*) as count FROM principles WHERE name REGEXP '[^ -~]' OR address REGEXP '[^ -~]';"
|
||
run_sql "SELECT 'Remaining corrupted products:' as status, COUNT(*) as count FROM products WHERE title REGEXP '[^ -~]' OR description REGEXP '[^ -~]';"
|
||
|
||
echo ""
|
||
echo "Done! Check specific records:"
|
||
echo "SELECT id, name FROM customers WHERE id IN (253, 1006, 1387);"
|
||
echo "SELECT id, name, city FROM principles WHERE id IN (2, 9, 13);"
|
||
echo "SELECT id, title FROM products WHERE id IN (30, 38, 67, 85);"
|