511 lines
17 KiB
Bash
Executable File
511 lines
17 KiB
Bash
Executable File
#!/bin/bash
|
|
# Version: 1.6.0
|
|
# Author: Tomi Eckert
|
|
# ==============================================================================
|
|
# HANA Database Manager Menu (hanamgr.sh)
|
|
#
|
|
# An interactive command-line menu for managing SAP HANA schemas.
|
|
# Provides functionality to Export, Import, Import-Rename, Drop schemas,
|
|
# and Rename the B1 company name within a database.
|
|
# ==============================================================================
|
|
|
|
# --- Configuration ---
|
|
# Assuming hdbsql is in the environment PATH
|
|
HDBSQL_CMD="hdbsql"
|
|
|
|
# --- Helper Functions ---
|
|
|
|
# Function to print a separator
|
|
print_separator() {
|
|
echo "--------------------------------------------------------"
|
|
}
|
|
|
|
# Function to check if hdbsql is available
|
|
check_hdbsql() {
|
|
if ! command -v "$HDBSQL_CMD" &> /dev/null; then
|
|
echo -e "\033[31m[ERROR] '$HDBSQL_CMD' command not found. Ensure it is in your PATH.\033[0m"
|
|
exit 1
|
|
fi
|
|
}
|
|
|
|
# Function to execute a SQL query and capture the exit code
|
|
execute_sql() {
|
|
local key="$1"
|
|
local query="$2"
|
|
local output
|
|
|
|
output=$("$HDBSQL_CMD" -U "$key" "$query" 2>&1)
|
|
local exit_code=$?
|
|
|
|
if [ $exit_code -eq 0 ]; then
|
|
return 0
|
|
else
|
|
echo "$output"
|
|
return $exit_code
|
|
fi
|
|
}
|
|
|
|
# Function to interactively select a schema from the database
|
|
# Sets the global variable SELECTED_SCHEMA
|
|
select_schema() {
|
|
local user_key="$1"
|
|
SELECTED_SCHEMA=""
|
|
|
|
echo "[🔎] Fetching available schemas..."
|
|
local query="SELECT SCHEMA_NAME FROM SCHEMAS WHERE SCHEMA_OWNER = 'SYSTEM' AND SCHEMA_NAME NOT IN ('_SYS_SECURITY', 'IFSERV', 'B1if', 'SYSTEM', 'RSP');"
|
|
|
|
local raw_output
|
|
raw_output=$("$HDBSQL_CMD" -U "$user_key" "$query" 2>/dev/null)
|
|
|
|
if [ $? -ne 0 ]; then
|
|
echo -e "\033[31m[ERROR] Failed to fetch schemas. Check your HDBUSERSTORE key.\033[0m"
|
|
# Fallback to manual entry
|
|
read -p "Enter Schema Name manually: " SELECTED_SCHEMA
|
|
return 1
|
|
fi
|
|
|
|
local schemas=()
|
|
while IFS= read -r line; do
|
|
if [[ -z "$line" || "$line" == SCHEMA_NAME* || "$line" == *"rows selected"* || "$line" == *"row selected"* || "$line" == *"overall time"* || "$line" == -* ]]; then
|
|
continue
|
|
fi
|
|
local clean_name
|
|
clean_name=$(echo "$line" | tr -d '"' | xargs)
|
|
if [[ -n "$clean_name" ]]; then
|
|
schemas+=("$clean_name")
|
|
fi
|
|
done <<< "$raw_output"
|
|
|
|
if [ ${#schemas[@]} -eq 0 ]; then
|
|
echo -e "\033[33m[WARN] No eligible schemas found in the database.\033[0m"
|
|
# Fallback to manual
|
|
read -p "Enter Schema Name manually: " SELECTED_SCHEMA
|
|
return 0
|
|
fi
|
|
|
|
echo -e "\n\033[1mAvailable Schemas:\033[0m"
|
|
for i in "${!schemas[@]}"; do
|
|
echo "$((i+1))) ${schemas[$i]}"
|
|
done
|
|
echo "0) Enter manually"
|
|
echo
|
|
|
|
local choice
|
|
while true; do
|
|
read -p "Select a schema (0-${#schemas[@]}): " choice
|
|
if [[ "$choice" == "0" ]]; then
|
|
read -p "Enter Schema Name manually: " SELECTED_SCHEMA
|
|
break
|
|
elif [[ "$choice" =~ ^[0-9]+$ ]] && [ "$choice" -ge 1 ] && [ "$choice" -le "${#schemas[@]}" ]; then
|
|
SELECTED_SCHEMA="${schemas[$((choice-1))]}"
|
|
break
|
|
else
|
|
echo -e "\033[31m[ERROR] Invalid selection. Please try again.\033[0m"
|
|
fi
|
|
done
|
|
}
|
|
|
|
# --- Operation Functions ---
|
|
|
|
do_export() {
|
|
echo -e "\n\033[1m=== Export Schema ===\033[0m"
|
|
select_schema "$GLOBAL_USER_KEY"
|
|
local schema_name="$SELECTED_SCHEMA"
|
|
read -p "Enter Target Directory or File Path (.tar.gz): " target_path
|
|
|
|
local max_threads=$(nproc --all 2>/dev/null || echo 1)
|
|
local half_threads=$((max_threads / 2))
|
|
[[ $half_threads -lt 1 ]] && half_threads=1
|
|
|
|
read -p "Number of threads (default: $half_threads, max: $max_threads): " threads
|
|
threads=${threads:-$half_threads}
|
|
|
|
read -p "Compress output as tar.gz? (y/N): " compress
|
|
|
|
if [[ -z "$GLOBAL_USER_KEY" || -z "$schema_name" || -z "$target_path" ]]; then
|
|
echo -e "\033[31m[ERROR] Key, Schema, and Path are required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
echo -e "\n- Starting export of schema '${schema_name}'..."
|
|
local export_dir="$target_path"
|
|
local archive_file=""
|
|
|
|
if [[ "$compress" =~ ^[Yy]$ ]]; then
|
|
# If the target path ends with .tar.gz, treat it as the final filename
|
|
if [[ "$target_path" == *.tar.gz ]]; then
|
|
archive_file="$target_path"
|
|
local target_dir=$(dirname "$target_path")
|
|
mkdir -p "$target_dir"
|
|
export_dir=$(mktemp -d "${target_dir}/export_${schema_name}_XXXXXXXX")
|
|
else
|
|
archive_file="${target_path}/${schema_name}_export_$(date +%Y%m%d_%H%M%S).tar.gz"
|
|
mkdir -p "$target_path"
|
|
export_dir=$(mktemp -d "${target_path}/export_${schema_name}_XXXXXXXX")
|
|
fi
|
|
echo "- Using temporary export directory: ${export_dir}"
|
|
else
|
|
mkdir -p "$export_dir"
|
|
fi
|
|
|
|
local query="EXPORT \"${schema_name}\".\"*\" AS BINARY INTO '${export_dir}' WITH REPLACE THREADS ${threads} NO DEPENDENCIES;"
|
|
|
|
if execute_sql "$GLOBAL_USER_KEY" "$query"; then
|
|
echo -e "\033[32m[DONE] Successfully exported schema '${schema_name}'.\033[0m"
|
|
|
|
if [[ "$compress" =~ ^[Yy]$ ]]; then
|
|
echo "- Compressing export to ${archive_file}..."
|
|
|
|
if command -v pigz &> /dev/null; then
|
|
tar -I "pigz -p $threads" -cf "$archive_file" -C "$(dirname "$export_dir")" "$(basename "$export_dir")"
|
|
else
|
|
tar -czf "$archive_file" -C "$(dirname "$export_dir")" "$(basename "$export_dir")"
|
|
fi
|
|
|
|
if [ $? -eq 0 ]; then
|
|
echo -e "\033[32m[DONE] Compression successful.\033[0m"
|
|
rm -rf "$export_dir"
|
|
else
|
|
echo -e "\033[31m[ERROR] Compression failed.\033[0m"
|
|
fi
|
|
fi
|
|
else
|
|
echo -e "\033[31m[ERROR] Export failed.\033[0m"
|
|
if [[ "$compress" =~ ^[Yy]$ ]]; then rm -rf "$export_dir"; fi
|
|
fi
|
|
}
|
|
|
|
do_import() {
|
|
local rename_mode="$1"
|
|
|
|
if [[ "$rename_mode" == "true" ]]; then
|
|
echo -e "\n\033[1m=== Import & Rename Schema ===\033[0m"
|
|
else
|
|
echo -e "\n\033[1m=== Import Schema ===\033[0m"
|
|
fi
|
|
|
|
read -p "Enter Source Schema Name (as it was exported): " schema_name
|
|
|
|
local new_schema_name=""
|
|
if [[ "$rename_mode" == "true" ]]; then
|
|
read -p "Enter NEW Target Schema Name: " new_schema_name
|
|
if [[ -z "$new_schema_name" ]]; then
|
|
echo -e "\033[31m[ERROR] New Schema Name is required for renaming.\033[0m"
|
|
return
|
|
fi
|
|
fi
|
|
|
|
read -p "Enter Source Path (Directory or .tar.gz): " source_path
|
|
|
|
local max_threads=$(nproc --all 2>/dev/null || echo 1)
|
|
local half_threads=$((max_threads / 2))
|
|
[[ $half_threads -lt 1 ]] && half_threads=1
|
|
|
|
read -p "Number of threads (default: $half_threads, max: $max_threads): " threads
|
|
threads=${threads:-$half_threads}
|
|
|
|
read -p "Replace existing objects? (y/N): " replace_opt
|
|
|
|
if [[ -z "$GLOBAL_USER_KEY" || -z "$schema_name" || -z "$source_path" ]]; then
|
|
echo -e "\033[31m[ERROR] Key, Schema, and Path are required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
echo -e "\n- Starting schema import..."
|
|
local import_dir="$source_path"
|
|
local cleanup_temp=false
|
|
|
|
# Handle compressed archives
|
|
if [[ -f "$source_path" && "$source_path" == *.tar.gz ]]; then
|
|
import_dir=$(mktemp -d "/tmp/import_${schema_name}_XXXXXXXX")
|
|
cleanup_temp=true
|
|
echo "- Decompressing archive to ${import_dir}..."
|
|
|
|
if command -v pigz &> /dev/null; then
|
|
tar -I "pigz -p $threads" -xf "$source_path" -C "$import_dir" --strip-components=1
|
|
else
|
|
tar -xzf "$source_path" -C "$import_dir" --strip-components=1
|
|
fi
|
|
|
|
if [ $? -ne 0 ]; then
|
|
echo -e "\033[31m[ERROR] Failed to decompress archive.\033[0m"
|
|
rm -rf "$import_dir"
|
|
return
|
|
fi
|
|
elif [[ ! -d "$import_dir" ]]; then
|
|
echo -e "\033[31m[ERROR] Path is neither a directory nor a valid .tar.gz archive.\033[0m"
|
|
return
|
|
fi
|
|
|
|
local import_options="IGNORE EXISTING"
|
|
if [[ "$replace_opt" =~ ^[Yy]$ ]]; then
|
|
import_options="REPLACE"
|
|
fi
|
|
|
|
if [[ "$rename_mode" == "true" ]]; then
|
|
import_options="${import_options} RENAME SCHEMA \"${schema_name}\" TO \"${new_schema_name}\""
|
|
fi
|
|
|
|
local query="IMPORT \"${schema_name}\".\"*\" AS BINARY FROM '${import_dir}' WITH ${import_options} THREADS ${threads};"
|
|
|
|
if execute_sql "$GLOBAL_USER_KEY" "$query"; then
|
|
echo -e "\033[32m[DONE] Successfully imported schema.\033[0m"
|
|
else
|
|
echo -e "\033[31m[ERROR] Import failed.\033[0m"
|
|
fi
|
|
|
|
if [[ "$cleanup_temp" == "true" ]]; then
|
|
echo "- Cleaning up temporary files..."
|
|
rm -rf "$import_dir"
|
|
fi
|
|
}
|
|
|
|
do_drop() {
|
|
echo -e "\n\033[1m=== Drop Schema ===\033[0m"
|
|
select_schema "$GLOBAL_USER_KEY"
|
|
local schema_name="$SELECTED_SCHEMA"
|
|
|
|
if [[ -z "$GLOBAL_USER_KEY" || -z "$schema_name" ]]; then
|
|
echo -e "\033[31m[ERROR] Key and Schema Name are required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
echo -e "\033[31m[WARN] You are about to completely drop the schema '${schema_name}'.\033[0m"
|
|
read -p "Are you absolutely sure? Type 'YES' to confirm: " confirm
|
|
|
|
if [[ "$confirm" == "YES" ]]; then
|
|
echo "- Dropping schema '${schema_name}'..."
|
|
local query="DROP SCHEMA \"${schema_name}\" CASCADE"
|
|
if execute_sql "$GLOBAL_USER_KEY" "$query"; then
|
|
echo -e "\033[32m[DONE] Schema successfully dropped.\033[0m"
|
|
else
|
|
echo -e "\033[31m[ERROR] Failed to drop schema.\033[0m"
|
|
fi
|
|
else
|
|
echo "- Operation cancelled."
|
|
fi
|
|
}
|
|
|
|
do_copy() {
|
|
echo -e "\n\033[1m=== Copy Schema ===\033[0m"
|
|
select_schema "$GLOBAL_USER_KEY"
|
|
local source_schema="$SELECTED_SCHEMA"
|
|
|
|
if [[ -z "$source_schema" ]]; then
|
|
echo -e "\033[31m[ERROR] Source Schema is required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
read -p "Enter Target Schema Name: " target_schema
|
|
read -p "Enter Temporary Export Directory Path: " export_path
|
|
|
|
local max_threads=$(nproc --all 2>/dev/null || echo 1)
|
|
local half_threads=$((max_threads / 2))
|
|
[[ $half_threads -lt 1 ]] && half_threads=1
|
|
|
|
read -p "Number of threads (default: $half_threads, max: $max_threads): " threads
|
|
threads=${threads:-$half_threads}
|
|
|
|
read -p "Replace existing objects in target schema? (y/N): " replace_opt
|
|
|
|
if [[ -z "$GLOBAL_USER_KEY" || -z "$target_schema" || -z "$export_path" ]]; then
|
|
echo -e "\033[31m[ERROR] Key, Target Schema, and Path are required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
# 1. Export
|
|
echo -e "\n-Step 1/2: Exporting source schema '${source_schema}' to '${export_path}'..."
|
|
mkdir -p "$export_path"
|
|
local temp_export_dir=$(mktemp -d "${export_path}/copy_export_${source_schema}_XXXXXXXX")
|
|
echo "-Using temporary export directory: ${temp_export_dir}"
|
|
|
|
local export_query="EXPORT \"${source_schema}\".\"*\" AS BINARY INTO '${temp_export_dir}' WITH REPLACE THREADS ${threads} NO DEPENDENCIES;"
|
|
|
|
if ! execute_sql "$GLOBAL_USER_KEY" "$export_query"; then
|
|
echo -e "\033[31m[ERROR] Export phase failed. Aborting copy.\033[0m"
|
|
rm -rf "$temp_export_dir"
|
|
return
|
|
fi
|
|
echo -e "\033[32m[DONE] Export phase completed successfully.\033[0m"
|
|
|
|
# 2. Import-Rename
|
|
echo -e "\n-Step 2/2: Importing and renaming to '${target_schema}'..."
|
|
local import_options="IGNORE EXISTING"
|
|
if [[ "$replace_opt" =~ ^[Yy]$ ]]; then
|
|
import_options="REPLACE"
|
|
fi
|
|
import_options="${import_options} RENAME SCHEMA \"${source_schema}\" TO \"${target_schema}\""
|
|
|
|
local import_query="IMPORT \"${source_schema}\".\"*\" AS BINARY FROM '${temp_export_dir}' WITH ${import_options} THREADS ${threads};"
|
|
|
|
if execute_sql "$GLOBAL_USER_KEY" "$import_query"; then
|
|
echo -e "\033[32m[DONE] Successfully copied schema '${source_schema}' to '${target_schema}'.\033[0m"
|
|
else
|
|
echo -e "\033[31m[ERROR] Import phase failed.\033[0m"
|
|
fi
|
|
|
|
# Cleanup
|
|
echo "- Cleaning up temporary files..."
|
|
rm -rf "$temp_export_dir"
|
|
}
|
|
|
|
do_backup() {
|
|
echo -e "\n\033[1m=== Backup Tenant ===\033[0m"
|
|
read -p "Enter Target Directory Path (parent directory): " target_path
|
|
|
|
local max_threads=$(nproc --all 2>/dev/null || echo 1)
|
|
local half_threads=$((max_threads / 2))
|
|
[[ $half_threads -lt 1 ]] && half_threads=1
|
|
|
|
read -p "Number of threads for compression (default: $half_threads, max: $max_threads): " threads
|
|
threads=${threads:-$half_threads}
|
|
|
|
read -p "Compress output as tar.gz? (y/N): " compress
|
|
|
|
if [[ -z "$GLOBAL_USER_KEY" || -z "$target_path" ]]; then
|
|
echo -e "\033[31m[ERROR] Key and Path are required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
# Get tenant name
|
|
local tenant_query="SELECT DATABASE_NAME FROM SYS.M_DATABASES;"
|
|
local tenant_name
|
|
tenant_name=$("$HDBSQL_CMD" -U "$GLOBAL_USER_KEY" "$tenant_query" 2>/dev/null | tail -n +2 | head -n 1 | tr -d '[:space:]' | tr -d '"')
|
|
|
|
if [[ -z "$tenant_name" ]]; then
|
|
echo -e "\033[31m[ERROR] Could not retrieve HANA tenant name.\033[0m"
|
|
return
|
|
fi
|
|
|
|
local timestamp=$(date +%Y%m%d_%H%M%S)
|
|
local backup_target_dir="$target_path"
|
|
local archive_file=""
|
|
|
|
if [[ "$compress" =~ ^[Yy]$ ]]; then
|
|
backup_target_dir=$(mktemp -d "${target_path}/${tenant_name}_backup_${timestamp}_XXXXXXXX")
|
|
archive_file="${target_path}/${tenant_name}_backup_${timestamp}.tar.gz"
|
|
echo "- Using temporary backup directory: ${backup_target_dir}"
|
|
fi
|
|
|
|
mkdir -p "$backup_target_dir"
|
|
local backup_path_prefix="${backup_target_dir}/backup_${tenant_name}_${timestamp}"
|
|
|
|
echo -e "\n- Starting backup of tenant '${tenant_name}'..."
|
|
local query="BACKUP DATA USING FILE ('${backup_path_prefix}')"
|
|
|
|
if execute_sql "$GLOBAL_USER_KEY" "$query"; then
|
|
echo -e "\033[32m[DONE] Successfully initiated tenant backup.\033[0m"
|
|
|
|
if [[ "$compress" =~ ^[Yy]$ ]]; then
|
|
echo "- Compressing backup to ${archive_file}..."
|
|
|
|
if command -v pigz &> /dev/null; then
|
|
tar -I "pigz -p $threads" -cf "$archive_file" -C "$backup_target_dir" .
|
|
else
|
|
tar -czf "$archive_file" -C "$backup_target_dir" .
|
|
fi
|
|
|
|
if [ $? -eq 0 ]; then
|
|
echo -e "\033[32m[DONE] Compression successful.\033[0m"
|
|
rm -rf "$backup_target_dir"
|
|
else
|
|
echo -e "\033[31m[ERROR] Compression failed.\033[0m"
|
|
fi
|
|
fi
|
|
else
|
|
echo -e "\033[31m[ERROR] Backup failed.\033[0m"
|
|
if [[ "$compress" =~ ^[Yy]$ ]]; then rm -rf "$backup_target_dir"; fi
|
|
fi
|
|
}
|
|
|
|
do_rename_db() {
|
|
echo -e "\n\033[1m=== Rename Database (Company Name) ===\033[0m"
|
|
select_schema "$GLOBAL_USER_KEY"
|
|
local schema_name="$SELECTED_SCHEMA"
|
|
read -p "Enter NEW Company Name: " new_compny_name
|
|
|
|
if [[ -z "$GLOBAL_USER_KEY" || -z "$schema_name" || -z "$new_compny_name" ]]; then
|
|
echo -e "\033[31m[ERROR] Key, Schema Name, and New Company Name are required.\033[0m"
|
|
return
|
|
fi
|
|
|
|
echo -e "\n- Updating company name for '${schema_name}' to '${new_compny_name}'..."
|
|
|
|
# Update CINF
|
|
echo " -> Updating CINF table..."
|
|
local q_cinf="UPDATE \"${schema_name}\".CINF SET \"CompnyName\" = '${new_compny_name}';"
|
|
execute_sql "$GLOBAL_USER_KEY" "$q_cinf" > /dev/null
|
|
|
|
# Update OADM
|
|
echo " -> Updating OADM table..."
|
|
local q_oadm="UPDATE \"${schema_name}\".OADM SET \"CompnyName\" = '${new_compny_name}', \"PrintHeadr\" = '${new_compny_name}';"
|
|
execute_sql "$GLOBAL_USER_KEY" "$q_oadm" > /dev/null
|
|
|
|
echo -e "\033[32m[DONE] Database renamed successfully.\033[0m"
|
|
}
|
|
|
|
# --- Main Menu Loop ---
|
|
|
|
check_hdbsql
|
|
|
|
echo
|
|
echo "========================================================"
|
|
echo " HANA Database Manager Init "
|
|
echo "========================================================"
|
|
read -p "Enter HDBUSERSTORE Key: " GLOBAL_USER_KEY
|
|
if [[ -z "$GLOBAL_USER_KEY" ]]; then
|
|
echo -e "\033[31m[ERROR] Key is required.\033[0m"
|
|
exit 1
|
|
fi
|
|
|
|
while true; do
|
|
echo
|
|
echo "========================================================"
|
|
echo " HANA Database Manager Menu "
|
|
echo "========================================================"
|
|
echo "1) Export Schema"
|
|
echo "2) Import Schema"
|
|
echo "3) Import-Rename Schema"
|
|
echo "4) Copy Schema"
|
|
echo "5) Drop Schema"
|
|
echo "6) Rename Database (Update Company Name)"
|
|
echo "7) Backup Tenant"
|
|
echo "8) Quit"
|
|
echo
|
|
read -p "Please select an option (1-8): " choice
|
|
|
|
case $choice in
|
|
1)
|
|
do_export
|
|
;;
|
|
2)
|
|
do_import "false"
|
|
;;
|
|
3)
|
|
do_import "true"
|
|
;;
|
|
4)
|
|
do_copy
|
|
;;
|
|
5)
|
|
do_drop
|
|
;;
|
|
6)
|
|
do_rename_db
|
|
;;
|
|
7)
|
|
do_backup
|
|
;;
|
|
8)
|
|
echo "Exiting."
|
|
exit 0
|
|
;;
|
|
*)
|
|
echo -e "\033[31m[WARN] Invalid option. Please try again.\033[0m"
|
|
;;
|
|
esac
|
|
|
|
echo
|
|
read -n 1 -s -r -p "Press any key to continue..."
|
|
done
|