109 lines
3.0 KiB
Bash
Executable File
109 lines
3.0 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
# Phase 1 Database Cleanup - Remove Empty Tables Only
|
|
# This script safely removes tables with 0 rows and no API usage
|
|
|
|
echo "=== Tilbudgivern Database Cleanup - Phase 1 ==="
|
|
echo "Removing empty tables with no API usage..."
|
|
echo
|
|
|
|
# Database connection details
|
|
DB_HOST="localhost"
|
|
DB_USER="tilbudgivern_service"
|
|
DB_PASS="REDACTED_PASSWORD"
|
|
DB_NAME="tilbudgivern"
|
|
|
|
# Function to check if table exists and is empty
|
|
check_table() {
|
|
local table_name=$1
|
|
echo "Checking table: $table_name"
|
|
|
|
# Check if table exists
|
|
exists=$(mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "SHOW TABLES LIKE '$table_name';" 2>/dev/null | grep -c "$table_name")
|
|
|
|
if [ "$exists" -eq 0 ]; then
|
|
echo " ❌ Table $table_name does not exist"
|
|
return 1
|
|
fi
|
|
|
|
# Check row count
|
|
row_count=$(mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "SELECT COUNT(*) FROM $table_name;" 2>/dev/null | tail -n 1)
|
|
|
|
if [ "$row_count" -eq 0 ]; then
|
|
echo " ✅ Table $table_name is empty ($row_count rows)"
|
|
return 0
|
|
else
|
|
echo " ⚠️ Table $table_name has $row_count rows - SKIPPING"
|
|
return 1
|
|
fi
|
|
}
|
|
|
|
# Function to drop table safely
|
|
drop_table() {
|
|
local table_name=$1
|
|
echo "Dropping table: $table_name"
|
|
|
|
result=$(mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "DROP TABLE IF EXISTS $table_name;" 2>&1)
|
|
|
|
if [ $? -eq 0 ]; then
|
|
echo " ✅ Successfully dropped $table_name"
|
|
else
|
|
echo " ❌ Failed to drop $table_name: $result"
|
|
fi
|
|
}
|
|
|
|
# Create backup timestamp
|
|
backup_timestamp=$(date +"%Y%m%d_%H%M%S")
|
|
echo "Creating backup before cleanup..."
|
|
mysqldump -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" > "tilbudgivern_backup_before_cleanup_$backup_timestamp.sql"
|
|
|
|
if [ $? -eq 0 ]; then
|
|
echo "✅ Backup created: tilbudgivern_backup_before_cleanup_$backup_timestamp.sql"
|
|
echo
|
|
else
|
|
echo "❌ Backup failed! Aborting cleanup."
|
|
exit 1
|
|
fi
|
|
|
|
# Phase 1: Remove only empty tables (safest cleanup)
|
|
echo "=== Phase 1: Removing Empty Tables ==="
|
|
echo
|
|
|
|
tables_to_remove=(
|
|
"bygma_materials_cache"
|
|
"quote_items"
|
|
"quote_feedback"
|
|
"quote_pricing_history"
|
|
"recent_material_prices"
|
|
)
|
|
|
|
removed_count=0
|
|
skipped_count=0
|
|
|
|
for table in "${tables_to_remove[@]}"; do
|
|
if check_table "$table"; then
|
|
drop_table "$table"
|
|
((removed_count++))
|
|
else
|
|
((skipped_count++))
|
|
fi
|
|
echo
|
|
done
|
|
|
|
echo "=== Cleanup Summary ==="
|
|
echo "Tables removed: $removed_count"
|
|
echo "Tables skipped: $skipped_count"
|
|
echo "Backup file: tilbudgivern_backup_before_cleanup_$backup_timestamp.sql"
|
|
echo
|
|
|
|
# Verify remaining table count
|
|
echo "=== Final Table Count ==="
|
|
final_count=$(mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "SHOW TABLES;" 2>/dev/null | wc -l)
|
|
((final_count--)) # Subtract header row
|
|
|
|
echo "Remaining tables: $final_count"
|
|
echo
|
|
|
|
echo "✅ Phase 1 cleanup completed successfully!"
|
|
echo "Next steps: Review results and consider Phase 2 (legacy tables) if needed."
|