104 lines
3.9 KiB
Bash
104 lines
3.9 KiB
Bash
|
#!/bin/bash
|
|||
|
|
# Test Database Connection and Queries
|
|||
|
|
|
|||
|
|
echo -e "\n========================================"
|
|||
|
|
echo -e " DATABASE CONNECTION TEST"
|
|||
|
|
echo -e "========================================\n"
|
|||
|
|
|
|||
|
|
# Check if .env exists
|
|||
|
|
if [ ! -f "orchestrator/.env" ]; then
|
|||
|
|
echo -e "\033[0;31m❌ orchestrator/.env not found\033[0m"
|
|||
|
|
exit 1
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
# Get DATABASE_URL
|
|||
|
|
DATABASE_URL=$(grep "^DATABASE_URL=" orchestrator/.env | cut -d '=' -f2- | tr -d '"' | tr -d "'")
|
|||
|
|
|
|||
|
|
if [ -z "$DATABASE_URL" ]; then
|
|||
|
|
echo -e "\033[0;31m❌ DATABASE_URL not set\033[0m"
|
|||
|
|
exit 1
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
# Extract connection details
|
|||
|
|
if [[ $DATABASE_URL =~ postgresql://([^:]+):([^@]+)@([^:]+):([^/]+)/(.+) ]]; then
|
|||
|
|
DB_USER="${BASH_REMATCH[1]}"
|
|||
|
|
DB_PASS="${BASH_REMATCH[2]}"
|
|||
|
|
DB_HOST="${BASH_REMATCH[3]}"
|
|||
|
|
DB_PORT="${BASH_REMATCH[4]}"
|
|||
|
|
DB_NAME="${BASH_REMATCH[5]}"
|
|||
|
|
else
|
|||
|
|
echo -e "\033[0;31m❌ Could not parse DATABASE_URL\033[0m"
|
|||
|
|
exit 1
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
# Check if psql is available
|
|||
|
|
if ! command -v psql &> /dev/null; then
|
|||
|
|
echo -e "\033[0;33m⚠️ psql not found. Install PostgreSQL client to run tests.\033[0m"
|
|||
|
|
echo -e " Ubuntu: sudo apt install postgresql-client"
|
|||
|
|
exit 1
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
echo -e "Testing connection to: $DB_HOST:$DB_PORT/$DB_NAME\n"
|
|||
|
|
|
|||
|
|
# Test 1: Basic connection
|
|||
|
|
echo -e "1. Testing basic connection..."
|
|||
|
|
if PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT version();" > /dev/null 2>&1; then
|
|||
|
|
echo -e " \033[0;32m✅ Connection successful\033[0m"
|
|||
|
|
else
|
|||
|
|
echo -e " \033[0;31m❌ Connection failed\033[0m"
|
|||
|
|
exit 1
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
# Test 2: Check tables exist
|
|||
|
|
echo -e "\n2. Checking database tables..."
|
|||
|
|
TABLES=$(PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" 2>/dev/null | tr -d ' ')
|
|||
|
|
|
|||
|
|
if [ -n "$TABLES" ] && [ "$TABLES" -gt 0 ]; then
|
|||
|
|
echo -e " \033[0;32m✅ Found $TABLES table(s)\033[0m"
|
|||
|
|
|
|||
|
|
# List tables
|
|||
|
|
echo -e "\n Tables:"
|
|||
|
|
PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;" 2>/dev/null | grep -v "^$" | grep -v "table_name" | grep -v "---" | sed 's/^/ - /'
|
|||
|
|
else
|
|||
|
|
echo -e " \033[0;33m⚠️ No tables found. Run migrations first.\033[0m"
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
# Test 3: Test queries on each table
|
|||
|
|
if [ -n "$TABLES" ] && [ "$TABLES" -gt 0 ]; then
|
|||
|
|
echo -e "\n3. Testing queries on tables..."
|
|||
|
|
|
|||
|
|
TABLES_LIST=$(PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';" 2>/dev/null | tr -d ' ')
|
|||
|
|
|
|||
|
|
for table in $TABLES_LIST; do
|
|||
|
|
if [ -n "$table" ]; then
|
|||
|
|
COUNT=$(PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM $table;" 2>/dev/null | tr -d ' ')
|
|||
|
|
if [ $? -eq 0 ]; then
|
|||
|
|
echo -e " \033[0;32m✅ $table: $COUNT row(s)\033[0m"
|
|||
|
|
else
|
|||
|
|
echo -e " \033[0;33m⚠️ $table: Query failed\033[0m"
|
|||
|
|
fi
|
|||
|
|
fi
|
|||
|
|
done
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
# Test 4: Test health endpoint (if orchestrator is running)
|
|||
|
|
echo -e "\n4. Testing orchestrator health endpoint..."
|
|||
|
|
if curl -s http://localhost:8080/health > /dev/null 2>&1; then
|
|||
|
|
HEALTH=$(curl -s http://localhost:8080/health)
|
|||
|
|
if echo "$HEALTH" | grep -q "healthy\|status"; then
|
|||
|
|
echo -e " \033[0;32m✅ Health endpoint responding\033[0m"
|
|||
|
|
if command -v jq &> /dev/null; then
|
|||
|
|
DB_STATUS=$(echo "$HEALTH" | jq -r '.checks.database // "unknown"' 2>/dev/null)
|
|||
|
|
echo -e " Database status: $DB_STATUS"
|
|||
|
|
fi
|
|||
|
|
else
|
|||
|
|
echo -e " \033[0;33m⚠️ Health endpoint returned unexpected response\033[0m"
|
|||
|
|
fi
|
|||
|
|
else
|
|||
|
|
echo -e " \033[0;33m⚠️ Orchestrator not running or not accessible\033[0m"
|
|||
|
|
fi
|
|||
|
|
|
|||
|
|
echo -e "\n\033[0;32m✅ Database tests completed\033[0m"
|
|||
|
|
echo ""
|
|||
|
|
|