94 lines
3.2 KiB
Bash
94 lines
3.2 KiB
Bash
|
#!/bin/bash
|
||
|
|
|
||
|
|
# Calejo Control Adapter Database Setup Script
|
||
|
|
# This script sets up the PostgreSQL database for the Calejo Control Adapter
|
||
|
|
|
||
|
|
set -e
|
||
|
|
|
||
|
|
# Configuration
|
||
|
|
DB_NAME="calejo"
|
||
|
|
DB_USER="control_reader"
|
||
|
|
DB_PASSWORD="secure_password"
|
||
|
|
DB_HOST="localhost"
|
||
|
|
DB_PORT="5432"
|
||
|
|
|
||
|
|
# Colors for output
|
||
|
|
RED='\033[0;31m'
|
||
|
|
GREEN='\033[0;32m'
|
||
|
|
YELLOW='\033[1;33m'
|
||
|
|
NC='\033[0m' # No Color
|
||
|
|
|
||
|
|
echo -e "${GREEN}Calejo Control Adapter Database Setup${NC}"
|
||
|
|
echo "=========================================="
|
||
|
|
|
||
|
|
# Check if PostgreSQL is running
|
||
|
|
if ! pg_isready -h $DB_HOST -p $DB_PORT >/dev/null 2>&1; then
|
||
|
|
echo -e "${RED}Error: PostgreSQL is not running on $DB_HOST:$DB_PORT${NC}"
|
||
|
|
echo "Please start PostgreSQL and ensure it's accessible"
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Check if database exists
|
||
|
|
if psql -h $DB_HOST -p $DB_PORT -U postgres -lqt | cut -d \| -f 1 | grep -qw "$DB_NAME"; then
|
||
|
|
echo -e "${YELLOW}Database '$DB_NAME' already exists${NC}"
|
||
|
|
read -p "Do you want to recreate it? (y/N): " -n 1 -r
|
||
|
|
echo
|
||
|
|
if [[ $REPLY =~ ^[Yy]$ ]]; then
|
||
|
|
echo "Dropping existing database..."
|
||
|
|
psql -h $DB_HOST -p $DB_PORT -U postgres -c "DROP DATABASE IF EXISTS $DB_NAME;"
|
||
|
|
else
|
||
|
|
echo "Using existing database"
|
||
|
|
fi
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Create database if it doesn't exist
|
||
|
|
if ! psql -h $DB_HOST -p $DB_PORT -U postgres -lqt | cut -d \| -f 1 | grep -qw "$DB_NAME"; then
|
||
|
|
echo "Creating database '$DB_NAME'..."
|
||
|
|
psql -h $DB_HOST -p $DB_PORT -U postgres -c "CREATE DATABASE $DB_NAME;"
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Create schema
|
||
|
|
echo "Creating database schema..."
|
||
|
|
psql -h $DB_HOST -p $DB_PORT -U postgres -d $DB_NAME -f database/schema.sql
|
||
|
|
|
||
|
|
# Create database user (if it doesn't exist)
|
||
|
|
if ! psql -h $DB_HOST -p $DB_PORT -U postgres -t -c "\du" | grep -qw "$DB_USER"; then
|
||
|
|
echo "Creating database user '$DB_USER'..."
|
||
|
|
psql -h $DB_HOST -p $DB_PORT -U postgres -c "CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';"
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Grant permissions
|
||
|
|
echo "Granting permissions to user '$DB_USER'..."
|
||
|
|
psql -h $DB_HOST -p $DB_PORT -U postgres -d $DB_NAME -c "
|
||
|
|
GRANT CONNECT ON DATABASE $DB_NAME TO $DB_USER;
|
||
|
|
GRANT USAGE ON SCHEMA public TO $DB_USER;
|
||
|
|
GRANT SELECT ON pump_stations, pumps, pump_plans, pump_feedback, pump_safety_limits TO $DB_USER;
|
||
|
|
GRANT INSERT ON safety_limit_violations, failsafe_events, emergency_stop_events, audit_log TO $DB_USER;
|
||
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO $DB_USER;
|
||
|
|
"
|
||
|
|
|
||
|
|
# Insert test data (optional)
|
||
|
|
read -p "Do you want to insert test data? (y/N): " -n 1 -r
|
||
|
|
echo
|
||
|
|
if [[ $REPLY =~ ^[Yy]$ ]]; then
|
||
|
|
echo "Inserting test data..."
|
||
|
|
psql -h $DB_HOST -p $DB_PORT -U postgres -d $DB_NAME -f database/test_data.sql
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Test connection with the new user
|
||
|
|
echo "Testing connection with user '$DB_USER'..."
|
||
|
|
if psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT 'Connection successful' AS result;" >/dev/null 2>&1; then
|
||
|
|
echo -e "${GREEN}Database setup completed successfully!${NC}"
|
||
|
|
echo ""
|
||
|
|
echo "Database Information:"
|
||
|
|
echo " Name: $DB_NAME"
|
||
|
|
echo " User: $DB_USER"
|
||
|
|
echo " Host: $DB_HOST"
|
||
|
|
echo " Port: $DB_PORT"
|
||
|
|
echo ""
|
||
|
|
echo "Connection string:"
|
||
|
|
echo " postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME"
|
||
|
|
else
|
||
|
|
echo -e "${RED}Error: Failed to connect with user '$DB_USER'${NC}"
|
||
|
|
exit 1
|
||
|
|
fi
|