Add "--init-sql".

Signed-off-by: Munyoki Kilyungi <me@bonfacemunyoki.com>

1 files changed, 89 insertions, 0 deletions

diff --git a/genenetwork-local-container.sh b/genenetwork-local-container.sh
index 8e599e4..2bbe529 100755
--- a/genenetwork-local-container.sh
+++ b/genenetwork-local-container.sh
@@ -261,3 +261,92 @@ if [ "$1" = "--init-container" ]; then
     log "INFO" "Email: test@development.user"
     log "INFO" "Password: testpasswd"
 fi
+
+if [ "$1" = "--init-sql" ]; then
+    # Configuration
+    URL="https://files.genenetwork.org/database/db_webqtl_s-2025-02-18.sql.xz"
+    DOWNLOAD_DIR="/tmp"
+    FILE_NAME=$(basename "$URL")
+    EXTRACTED_FILE="${FILE_NAME%.xz}"
+    DB_USER="webqtlout"
+    DB_PASSWORD="webqtlout"
+    DB_HOST="localhost"
+    DB_NAME="db_webqtl_local"
+    MYSQL_ROOT_USER=""  # Change to your MySQL admin user if different
+    MYSQL_ROOT_PASSWORD=""   # Set this or leave empty to prompt
+
+    # Check for required tools
+    for cmd in wget xz mysql; do
+	if ! command -v "$cmd" &> /dev/null; then
+            log ERROR "Required command '$cmd' not found"
+            exit 1
+	fi
+    done
+
+    # Download the file
+    log INFO "Downloading $URL to $DOWNLOAD_DIR/$FILE_NAME"
+    if ! wget -O "$DOWNLOAD_DIR/$FILE_NAME" "$URL"; then
+	log ERROR "Failed to download $URL"
+	exit 1
+    fi
+
+    # Extract the .xz file
+    log INFO "Extracting $DOWNLOAD_DIR/$FILE_NAME"
+    if ! xz -d "$DOWNLOAD_DIR/$FILE_NAME"; then
+	log ERROR "Failed to extract $DOWNLOAD_DIR/$FILE_NAME"
+	rm -f "$DOWNLOAD_DIR/$FILE_NAME"
+	exit 1
+    fi
+
+    # Prepare MySQL user and password credentials
+    if [ -z "$MYSQL_ROOT_USER" ]; then
+	log INFO "MySQL root user not set, prompting for input"
+	read -s -p "Enter MySQL user: " MYSQL_ROOT_USER
+    fi
+    if [ -z "$MYSQL_ROOT_PASSWORD" ]; then
+	log INFO "MySQL root password not set, prompting for input"
+	read -s -p "Enter MySQL root password: " MYSQL_ROOT_PASSWORD
+    fi
+
+    # Check if DB user exists, create if not
+    log INFO "Checking if MySQL user $DB_USER exists"
+    USER_EXISTS=$(mysql -h "$DB_HOST" -u "$MYSQL_ROOT_USER" -p"$MYSQL_ROOT_PASSWORD" -e "SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = '$DB_USER' AND host = 'localhost') AS user_exists;" 2>/dev/null | grep -o '[0-1]$')
+    if [ "$USER_EXISTS" = "0" ]; then
+	log INFO "Creating MySQL user $DB_USER"
+	if ! mysql -h "$DB_HOST" -u "$MYSQL_ROOT_USER" -p"$MYSQL_ROOT_PASSWORD" -e "CREATE USER '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASSWORD'; GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'localhost'; FLUSH PRIVILEGES;" 2>/dev/null; then
+            log ERROR "Failed to create MySQL user $DB_USER"
+            rm -f "$DOWNLOAD_DIR/$EXTRACTED_FILE"
+            exit 1
+	fi
+    else
+	log INFO "User $DB_USER already exists, ensuring privileges"
+	if ! mysql -h "$DB_HOST" -u "$MYSQL_ROOT_USER" -p"$MYSQL_ROOT_PASSWORD" -e "GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'localhost'; FLUSH PRIVILEGES;" 2>/dev/null; then
+            log ERROR "Failed to update privileges for $DB_USER"
+            rm -f "$DOWNLOAD_DIR/$EXTRACTED_FILE"
+            exit 1
+	fi
+    fi
+
+    # Create database if it doesn't exist
+    log INFO "Ensuring database $DB_NAME exists"
+    if ! mysql -h "$DB_HOST" -u "$MYSQL_ROOT_USER" -p"$MYSQL_ROOT_PASSWORD" -e "CREATE DATABASE IF NOT EXISTS $DB_NAME;" 2>/dev/null; then
+	log ERROR "Failed to create or verify database $DB_NAME"
+	rm -f "$DOWNLOAD_DIR/$EXTRACTED_FILE"
+	exit 1
+    fi
+
+    # Install the SQL dump into the database
+    log INFO "Importing $DOWNLOAD_DIR/$EXTRACTED_FILE into $DB_NAME"
+    if ! mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$DOWNLOAD_DIR/$EXTRACTED_FILE"; then
+	log ERROR "Failed to import $DOWNLOAD_DIR/$EXTRACTED_FILE into $DB_NAME"
+	rm -f "$DOWNLOAD_DIR/$EXTRACTED_FILE"
+	exit 1
+    fi
+
+    # Clean up
+    log INFO "Removing $DOWNLOAD_DIR/$EXTRACTED_FILE"
+    rm -f "$DOWNLOAD_DIR/$EXTRACTED_FILE"
+
+    log INFO "Database import completed successfully"
+    exit 0
+fi