refresh_database.ps1

# Enhanced PowerShell Script for MySQL/MariaDB Database Dump and Restore
#
# This script automates dumping and restoring databases using mysqldump and mysql, with support for configuration 
# detection and fallbacks. It checks for database configuration in 'config.php' or 'wp-config.php' files, or uses 
# the current directory name if no database name is provided. Users can customize behavior through command-line 
# arguments for various parameters.
#
# Usage Examples:
#
# 1. Dumping a Database:
# Command to dump a database named 'mydatabase':
# .\script.ps1 --dump --DatabaseName "mydatabase"
#
# 2. Restoring a Database:
# Command to restore a database from a dump file located at 'doc\database.sql':
# .\script.ps1 --restore --DatabaseName "mydatabase"
#
# 3. Using a Custom Configuration File:
# Command to dump a database using settings from a custom configuration file:
# .\script.ps1 --dump --ConfigFilePath "C:\path\to\myconfig.php"
#
# 4. Specifying Custom MySQL Executables:
# Command to dump a database with a specific mysqldump executable path:
# .\script.ps1 --dump --MysqldumpExecutable "C:\custom\path\mysqldump.exe"
#
# 5. Auto-Detecting Database Name from Current Directory:
# If the database name is not provided, the script uses the name of the current 
# directory. This is handy for quick backups in project folders.
# .\script.ps1 --dump
#
#
# !Remember to adjust executable paths and other parameters according to your 
# MySQL/MariaDB installation and your database's specific requirements.

# Here are the defaults. Change them as per your environment.
param (
    [string]$Mode = "--help", # Operation mode: --dump, --restore, or --help.
    [string]$DatabaseName, # Database name, automatically determined if not specified.
    [string]$ConfigFilePath = ".\config.php", # Path to custom PHP config file.
    [string]$WPConfigFilePath = ".\wp-config.php", # Path to WordPress config file.
    [string]$DumpFileLocation = "doc\database.sql", # Location for the database dump file.
    [string]$MysqlExecutable = 'C:\Program Files\MySQL\MySQL Server 8.1\bin\mysql.exe', # Path to the mysql executable.
    [string]$MysqldumpExecutable = 'C:\Program Files\MySQL\MySQL Server 8.1\bin\mysqldump.exe', # Path to the mysqldump executable.
    [hashtable]$DefaultDbConfig = @{ # Default database configuration.
        "username" = "root";
        "password" = "qwerty";
        "database" = "";
        "hostname" = "127.0.0.1";
    }
)

function Get-DatabaseConfig {
    param (
        [string]$ConfigFilePath,
        [string]$WPConfigFilePath,
        [hashtable]$DefaultDbConfig
    )

    $config = $DefaultDbConfig.Clone()

    $pathsToCheck = @($ConfigFilePath, $WPConfigFilePath)

    foreach ($path in $pathsToCheck) {
        if (Test-Path $path) {
            $configContent = Get-Content $path

            foreach ($line in $configContent) {
                # Custom config constants
                if ($line -match "define\(['`"]DATABASE_USERNAME['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["username"] = $matches[1] }
                elseif ($line -match "const\s+DATABASE_USERNAME\s*=\s*['`"](.+?)['`"];") { $config["username"] = $matches[1] }

                if ($line -match "define\(['`"]DATABASE_PASSWORD['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["password"] = $matches[1] }
                elseif ($line -match "const\s+DATABASE_PASSWORD\s*=\s*['`"](.+?)['`"];") { $config["password"] = $matches[1] }

                if ($line -match "define\(['`"]DATABASE_DATABASE['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["database"] = $matches[1] }
                elseif ($line -match "const\s+DATABASE_DATABASE\s*=\s*['`"](.+?)['`"];") { $config["database"] = $matches[1] }

                if ($line -match "define\(['`"]DATABASE_HOSTNAME['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["hostname"] = $matches[1] }
                elseif ($line -match "const\s+DATABASE_HOSTNAME\s*=\s*['`"](.+?)['`"];") { $config["hostname"] = $matches[1] }

                # WordPress config constants
                if ($line -match "define\(['`"]DB_USER['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["username"] = $matches[1] }
                if ($line -match "define\(['`"]DB_PASSWORD['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["password"] = $matches[1] }
                if ($line -match "define\(['`"]DB_NAME['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["database"] = $matches[1] }
                if ($line -match "define\(['`"]DB_HOST['`"],\s*['`"](.+?)['`"]\s*\);?") { $config["hostname"] = $matches[1] }
            }
        }
    }

    if (-not $config["database"]) {
        $config["database"] = Split-Path -Leaf (Get-Location)
        $config["database"] = $config["database"] -replace '[^\w\d]', ''
        if ($Mode -ne "--help") {
            Write-Host "No database name found in config files. Using current directory name for database: $($config["database"])"
        }
    }

    # Add debugging statements to confirm the values being read
    if ($Mode -ne "--help") {
        Write-Host "Parsed Config - Username: $($config["username"])"
        Write-Host "Parsed Config - Password: $($config["password"])"
        Write-Host "Parsed Config - Database: $($config["database"])"
        Write-Host "Parsed Config - Hostname: $($config["hostname"])"
    }

    return $config
}

$dbConfig = Get-DatabaseConfig -ConfigFilePath $ConfigFilePath -WPConfigFilePath $WPConfigFilePath -DefaultDbConfig $DefaultDbConfig

if (-not $DatabaseName -and $dbConfig["database"]) {
    $DatabaseName = $dbConfig["database"]
}
elseif (-not $DatabaseName) {
    $DatabaseName = (Get-Location).Path.Split('\\')[-1]
    Write-Host "No database name provided. Using current directory name: $DatabaseName"
}

function Show-Help {
    Write-Host "This script is used to dump and restore MySQL/MariaDB databases using the mysqldump and mysql command line utilities."
    Write-Host "Usage:"
    Write-Host "  --dump [DatabaseName]    : Dumps the specified database into a SQL file. If DatabaseName is not specified, tries to use the database name from the configuration file or the current directory name."
    Write-Host "  --restore [DatabaseName] : Restores the specified database from a SQL file. If DatabaseName is not specified, tries to use the database name from the configuration file or the current directory name."
    Write-Host "  --help                   : Displays this help message."
}

$MysqlUser = $dbConfig["username"]
$MysqlPassword = $dbConfig["password"]
$MysqlHost = $dbConfig["hostname"]

function Invoke-DumpFileProcessing {
    Write-Host "Processing dump file for more consistent output across MySQL/MariaDB"

    # Replacing utf8mb3 with utf8
    Write-Host "Replacing utf8mb3 with utf8"
    (Get-Content $DumpFileLocation) -replace 'utf8mb3', 'utf8' | Set-Content $DumpFileLocation

    # Removing COLLATE=utf8_general_ci
    Write-Host "Removing COLLATE=utf8_general_ci"
    (Get-Content $DumpFileLocation) -replace 'COLLATE=utf8_general_ci', '' | Set-Content $DumpFileLocation

    # Removing COLLATE=utf8mb4_0900_ai_ci
    Write-Host "Removing COLLATE=utf8mb4_0900_ai_ci"
    (Get-Content $DumpFileLocation) -replace 'COLLATE=utf8mb4_0900_ai_ci', '' | Set-Content $DumpFileLocation

    # Replacing NOT NULL DEFAULT 0.00 with NOT NULL DEFAULT '0.00'
    Write-Host "Replacing NOT NULL DEFAULT 0.00 with NOT NULL DEFAULT '0.00'"
    (Get-Content $DumpFileLocation) -replace "NOT NULL DEFAULT 0.00", "NOT NULL DEFAULT '0.00'" | Set-Content $DumpFileLocation

    # Replacing CURRENT_TIMESTAMP with current_timestamp()
    Write-Host "Replacing CURRENT_TIMESTAMP with current_timestamp()"
    (Get-Content $DumpFileLocation) -replace 'CURRENT_TIMESTAMP', 'current_timestamp()' | Set-Content $DumpFileLocation

    Write-Host "Dump file processed."
}

switch ($Mode) {
    "--dump" {
        if (Test-Path $DumpFileLocation) {
            $confirmation = Read-Host "The dump file already exists. Do you want to overwrite it? (Y/N)"
            if ($confirmation -ne 'Y' -and $confirmation -ne 'y') {
                Write-Host "Dump operation canceled by the user."
                return
            }
        }

        Write-Host "Dumping database $DatabaseName..."
        $env:MYSQL_PWD = $MysqlPassword
        & $MysqldumpExecutable -u $MysqlUser -h $MysqlHost $DatabaseName > $DumpFileLocation

        # Call the function to process the dump file after creation
        Invoke-DumpFileProcessing

        Write-Host "Dump completed successfully."
        Remove-Item Env:MYSQL_PWD
    }
    "--restore" {
        Write-Host "Starting the restore process..."
        Write-Host "Using database name from config: $DatabaseName"
        Write-Host "Database Name: $DatabaseName"
        Write-Host "MySQL Executable: $MysqlExecutable"
        Write-Host "MySQL User: $MysqlUser"
        Write-Host "MySQL Host: $MysqlHost"
        Write-Host "Dump File Location: $DumpFileLocation"

        $env:MYSQL_PWD = $MysqlPassword
        Write-Host "MYSQL_PWD environment variable set for authentication."

        # Drop the database if it exists and create it
        $dropAndCreateDbCommand = "& `"$MysqlExecutable`" -u $MysqlUser -h $MysqlHost -e `"DROP DATABASE IF EXISTS $DatabaseName; CREATE DATABASE $DatabaseName;`""
        Write-Host "Executing command to drop (if exists) and create database: $dropAndCreateDbCommand"
        try {
            Invoke-Expression $dropAndCreateDbCommand
            if ($LASTEXITCODE -ne 0) {
                Write-Host "Error encountered during database drop/create operation. Exit code: $LASTEXITCODE"
                exit $LASTEXITCODE
            }
            else {
                Write-Host "Database $DatabaseName dropped (if existed) and created successfully."
            }
        }
        catch {
            Write-Host "Exception encountered during database drop/create operation: $_"
            exit 1
        }

        # Restore the database from the dump file
        $restoreCommand = "& `"$MysqlExecutable`" -u $MysqlUser -h $MysqlHost $DatabaseName -e `"source $DumpFileLocation`""
        Write-Host "Constructed command for restore: $restoreCommand"

        try {
            Invoke-Expression $restoreCommand
            if ($LASTEXITCODE -ne 0) {
                Write-Host "Error encountered during restore operation. Exit code: $LASTEXITCODE"
                exit $LASTEXITCODE
            }
            else {
                Write-Host "Restore operation executed successfully."
            }
        }
        catch {
            Write-Host "Exception encountered during restore operation: $_"
            exit 1
        }

        Remove-Item Env:MYSQL_PWD -ErrorAction SilentlyContinue
        Write-Host "MYSQL_PWD environment variable removed."
        Write-Host "Restore process completed."
    }


    default {
        Show-Help
    }
}