Restore Day Backup Controller Documentation

File: /controllers/restoredaybackup.php

Purpose: Restores database from SQL backup files with execution tracking and error reporting

Last Updated: December 21, 2024

Total Functions: 1

Lines of Code: ~129

---

📋 Overview

The Restore Day Backup Controller is a specialized database management utility that handles the restoration of MySQL databases from SQL backup files. It provides comprehensive SQL execution tracking, error reporting, and transaction management for database recovery operations. The controller handles:

• SQL backup file upload and processing
• Multi-query SQL execution with transaction support
• Comprehensive error tracking and reporting
• Comment and whitespace cleaning from SQL files
• Connection management with UTF-8 charset support
• Detailed execution statistics (success/failure counts)
• Transaction control (BEGIN, COMMIT, ROLLBACK)

Primary Functions

✅ Upload and process SQL backup files
✅ Execute SQL commands with transaction support
✅ Track successful and failed query executions
✅ Clean SQL files (remove comments and empty lines)
✅ Provide detailed execution feedback
✅ Handle UTF-8 character set properly
✅ Support MySQL transaction commands

Related Controllers

• backupController.php - Database backup creation
• databaseController.php - General database management
• migrationController.php - Database schema migrations

---

🗄️ Database Tables

No Direct Table Operations

This controller operates at the SQL execution level and doesn't directly manipulate specific tables. Instead, it:

• Executes arbitrary SQL commands from backup files
• Can affect any database table depending on backup content
• Uses direct MySQL connection for maximum compatibility

Connection Requirements

Component	Purpose	Details
MySQL Connection	Direct database access	Uses ConnectionProperty class for credentials
UTF-8 Support	Character encoding	Sets proper charset for international data
Transaction Support	Data consistency	Handles BEGIN/COMMIT/ROLLBACK commands

---

🔑 Key Functions

1. Default Action - Upload Interface

Location: Line 13

Purpose: Display the backup file upload interface

Process Flow:

1. Display file upload form via getfile.html template

2. Allow users to select SQL backup files

3. Provide interface for restoration process

Template: restoredaybackupview/getfile.html

---

2. runsql() - Main Backup Restoration Function

Location: Line 41

Purpose: Execute SQL commands from uploaded backup file with comprehensive tracking

Function Signature:

function runsql()

Process Flow:

1. File Upload Validation:

- Check file upload errors

- Verify file was properly uploaded

- Validate file accessibility

2. File Processing:

- Read complete file contents

- Clean SQL content (remove comments and empty lines)

- Split into individual SQL commands

3. Database Connection:

- Establish MySQL connection using ConnectionProperty

- Set UTF-8 character encoding

- Initialize connection for SQL execution

4. SQL Execution Loop:

- Process each SQL command individually

- Handle transaction commands specially

- Track success/failure for each command

- Provide detailed output for monitoring

5. Results Reporting:

- Return success and error counts

- Display detailed execution log

- Show final statistics

File Validation Logic:

if ($_FILES['pfile']['error'] == UPLOAD_ERR_OK && 
    is_uploaded_file($_FILES['pfile']['tmp_name'])) {
    $handle = fopen($_FILES['pfile']['tmp_name'], 'rb');
    if ($handle) {
        $commands = file_get_contents($_FILES['pfile']['tmp_name']);
        fclose($handle);
    }
}

Connection Setup:

$con = mysqli_connect(
    ConnectionProperty::getHost(), 
    ConnectionProperty::getUser(), 
    ConnectionProperty::getPassword(), 
    ConnectionProperty::getDatabase()
);

// Set UTF-8 encoding
mysqli_query("SET NAMES 'utf8'");
mysqli_query('SET CHARACTER_SET utf8');

SQL Cleaning Process:

// Remove comments and empty lines
$lines = explode("\n", $commands);
$commands = '';
foreach ($lines as $line) {
    $line = trim($line);
    if ($line && !strpos($line, '--')) {  // Skip comments and empty lines
        $commands .= $line . "\n";
    }
}

Command Execution Logic:

$commands = explode(";", $commands);
foreach ($commands as $command) {
    $command = trim($command);
    if ($command) {
        if ($command == "BEGIN;") {
            $con->begin_transaction();
        } else if ($command == "COMMIT;") {
            $con->commit();
        } else if ($command == "ROLLBACK;") {
            $con->rollback();
        } else {
            $res = $con->query($command);
            if ($res === TRUE) {
                echo "SUCCESS: $command<br/>";
                $success++;
            } else {
                echo "ERROR: $command<br/>Error: " . $con->error . "<br/>";
                $errors++;
            }
        }
    }
}

Return Value:

return array($success, $errors);

---

🔄 Workflows

Workflow 1: Database Restoration Process

┌─────────────────────────────────────────────────────────────┐

START: Backup File Upload

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

1File Upload Validation

- Check $_FILES['pfile']['error'] == UPLOAD_ERR_OK

- Verify is_uploaded_file() returns true

- Ensure file handle can be opened

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

2Read and Process File Contents

- Read complete file via file_get_contents()

- Split content into lines

- Remove comments (lines starting with '--')

- Remove empty lines and whitespace

- Rebuild clean SQL content

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

3Establish Database Connection

- Connect using ConnectionProperty credentials

- Check connection success

- Set UTF-8 character encoding:

├─ SET NAMES 'utf8'

│ └─ SET CHARACTER_SET utf8 │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

4Split Commands and Execute

- Split SQL content by semicolons

- Initialize success/error counters

│

FOR EACH SQL command:

│

→ Trim whitespace

│

→ Check for transaction commands:

│ ├─ BEGIN: Start transaction

│ ├─ COMMIT: Commit transaction

│ │ └─ ROLLBACK: Rollback transaction │

│

→ Execute regular SQL command

│

→ Handle Results:

│ ├─ SUCCESS: Increment success counter

│

│ │ └─ ERROR: Increment error counter │

│ Show command and error message

│

│ └─→ Display real-time progress │

└────────────────────┬────────────────────────────────────────┘

▼

┌─────────────────────────────────────────────────────────────┐

5Display Final Results

- Show total successful commands executed

- Show total commands that failed

- Display "DONE" or "Error" status

- Return statistics array

└─────────────────────────────────────────────────────────────┘

---

🌐 URL Routes & Actions

URL Parameter	Function Called	Description
`do=` (empty)	Default action	Display file upload interface
`do=runsql`	`runsql()`	Execute SQL from uploaded backup file

### Required Parameters by Action

File Upload Interface (do= empty):

• No parameters required
• Displays upload form for SQL files

SQL Execution (do=runsql):

• $_FILES['pfile'] - Uploaded SQL backup file (required)
• File must be valid SQL backup with proper format

File Upload Requirements

• File Input Name: pfile
• Supported Formats: SQL files (.sql recommended)
• File Size: Limited by PHP upload_max_filesize setting
• Content: Valid SQL commands separated by semicolons

---

🧮 Processing Methods

File Content Cleaning

// Split into lines for processing
$lines = explode("\n", $commands);
$commands = '';

// Clean each line
foreach ($lines as $line) {
    $line = trim($line);
    if ($line && !strpos($line, '--')) {  // Skip comments and empty lines
        $commands .= $line . "\n";
    }
}

SQL Command Splitting

// Split by semicolon to get individual commands
$commands = explode(";", $commands);

// Process each command
foreach ($commands as $command) {
    $command = trim($command);
    if ($command) {  // Skip empty commands
        // Execute command
    }
}

Transaction Command Handling

if ($command == "BEGIN;") {
    $con->begin_transaction();
} else if ($command == "COMMIT;") {
    $con->commit();
} else if ($command == "ROLLBACK;") {
    $con->rollback();
} else {
    // Regular SQL command execution
    $res = $con->query($command);
}

Statistics Tracking

$success = 0;  // Count of successful commands
$errors = 0;   // Count of failed commands

// For each command execution
if ($res === TRUE) {
    $success++;
    echo "SUCCESS: " . mysqli_insert_id($con);
} else {
    $errors++;
    echo "ERROR: " . $con->error;
}

return array($success, $errors);

---

🔒 Security & Permissions

File Upload Security

Current Implementation:

• Basic upload validation using is_uploaded_file()
• File error checking via $_FILES['pfile']['error']
• Temporary file handling

Security Risks:

• No file type validation beyond upload check
• No file size limits enforced
• No content validation before execution
• Executes ANY SQL command from uploaded file

Recommended Improvements:

// Add file type validation
$allowedTypes = ['application/sql', 'text/plain'];
$fileType = $_FILES['pfile']['type'];
if (!in_array($fileType, $allowedTypes)) {
    throw new Exception("Invalid file type");
}

// Add file size validation
$maxSize = 10 * 1024 * 1024; // 10MB
if ($_FILES['pfile']['size'] > $maxSize) {
    throw new Exception("File too large");
}

// Add SQL content validation
$dangerousCommands = ['DROP DATABASE', 'DROP TABLE', 'TRUNCATE'];
foreach ($dangerousCommands as $dangerous) {
    if (stripos($commands, $dangerous) !== false) {
        throw new Exception("Dangerous command detected: $dangerous");
    }
}

Authentication & Authorization

Current State:

• No authentication check implemented
• No user permission validation
• Direct access to database restoration

Critical Security Issues:

• Anyone can access restoration functionality
• No logging of who performed restorations
• No approval workflow for sensitive operations

Required Improvements:

// Add authentication check
include_once("../public/authentication.php");

// Add permission check
if (!$_SESSION['usergroup']->canRestoreDatabase) {
    throw new Exception("Insufficient permissions");
}

// Add operation logging
$logEntry = "Database restoration by user " . $_SESSION['userid'] . 
           " at " . date('Y-m-d H:i:s');
error_log($logEntry, 3, "/path/to/restore.log");

---

📊 Performance Considerations

File Processing Performance

1. Large File Handling:

- Current implementation loads entire file into memory

- May fail with very large backup files

- No streaming or chunked processing

2. Memory Usage:

- File contents loaded completely into PHP memory

- SQL commands split and stored in arrays

- Can cause memory exhaustion with large backups

Optimization Strategies:

// Stream large files instead of loading completely
function processLargeFile($filename) {
    $handle = fopen($filename, 'r');
    $buffer = '';
    
    while (!feof($handle)) {
        $line = fgets($handle);
        $buffer .= $line;
        
        // Process complete commands as found
        if (substr(trim($line), -1) === ';') {
            executeCommand($buffer);
            $buffer = '';
        }
    }
    fclose($handle);
}

Database Performance

1. Transaction Management:

- Supports explicit transaction control

- Can batch operations for better performance

- May hold locks for extended periods

2. Connection Efficiency:

- Uses single connection for all commands

- No connection pooling or optimization

- Character set configured properly

Performance Improvements:

// Add transaction batching
$batchSize = 100;
$commandCount = 0;

$con->begin_transaction();
foreach ($commands as $command) {
    $con->query($command);
    $commandCount++;
    
    if ($commandCount % $batchSize === 0) {
        $con->commit();
        $con->begin_transaction();
    }
}
$con->commit(); // Final batch

---

🐛 Common Issues & Troubleshooting

1. File Upload Failures

Issue: Files not uploading or processing

Cause: PHP upload settings, file permissions, or file size limits

Debug Steps:

// Check upload settings
echo "upload_max_filesize: " . ini_get('upload_max_filesize') . "<br>";
echo "post_max_size: " . ini_get('post_max_size') . "<br>";
echo "max_file_uploads: " . ini_get('max_file_uploads') . "<br>";

// Check file upload errors
switch ($_FILES['pfile']['error']) {
    case UPLOAD_ERR_OK:
        echo "File uploaded successfully";
        break;
    case UPLOAD_ERR_INI_SIZE:
        echo "File exceeds upload_max_filesize";
        break;
    case UPLOAD_ERR_FORM_SIZE:
        echo "File exceeds MAX_FILE_SIZE";
        break;
    case UPLOAD_ERR_PARTIAL:
        echo "File was only partially uploaded";
        break;
    default:
        echo "Upload error code: " . $_FILES['pfile']['error'];
}

2. SQL Execution Errors

Issue: SQL commands failing during restoration

Cause: Syntax errors, missing dependencies, or permission issues

Common Error Types:

-- Missing database selection
ERROR 1046 (3D000): No database selected

-- Table already exists
ERROR 1050 (42S01): Table 'tablename' already exists

-- Foreign key constraint fails
ERROR 1452 (23000): Cannot add or update a child row: foreign key constraint fails

Troubleshooting:

// Add detailed error reporting
if ($res === FALSE) {
    $errorInfo = array(
        'command' => $command,
        'error_code' => $con->errno,
        'error_message' => $con->error,
        'state' => $con->sqlstate
    );
    error_log("SQL Error: " . json_encode($errorInfo));
}

3. Character Encoding Issues

Issue: Foreign characters not displaying correctly

Cause: Character set mismatches or encoding problems

Solutions:

// Ensure proper encoding throughout
$con->set_charset("utf8mb4");
mysqli_query($con, "SET NAMES 'utf8mb4'");
mysqli_query($con, "SET CHARACTER_SET utf8mb4");
mysqli_query($con, "SET CHARACTER_SET_CONNECTION=utf8mb4");
mysqli_query($con, "SET CHARACTER_SET_DATABASE=utf8mb4");
mysqli_query($con, "SET CHARACTER_SET_RESULTS=utf8mb4");

4. Memory Exhaustion

Issue: PHP runs out of memory processing large files

Cause: Loading large backup files entirely into memory

Solutions:

// Increase memory limit temporarily
ini_set('memory_limit', '512M');

// Or implement streaming approach
function streamExecuteSQL($filename) {
    $handle = fopen($filename, 'r');
    $command = '';
    
    while (($line = fgets($handle)) !== false) {
        $command .= $line;
        if (substr(trim($line), -1) === ';') {
            // Execute complete command
            executeCommand($command);
            $command = '';
        }
    }
    fclose($handle);
}

---

🧪 Testing Scenarios

Test Case 1: Basic SQL File Restoration

1. Create simple SQL file with CREATE TABLE and INSERT statements
2. Upload via interface
3. Verify all commands execute successfully
4. Check data was properly restored
5. Confirm success/error counts are accurate

Test Case 2: Transaction Handling

1. Create SQL file with explicit BEGIN/COMMIT statements
2. Include some valid and some invalid commands
3. Verify transaction commands are handled separately
4. Check that rollbacks work when errors occur

Test Case 3: Large File Processing

1. Create large SQL backup file (>10MB)
2. Upload and monitor memory usage
3. Verify all commands process correctly
4. Check performance timing
5. Ensure no memory exhaustion occurs

Test Case 4: Error Handling

1. Create SQL file with intentional syntax errors
2. Upload and process file
3. Verify errors are caught and reported
4. Check that execution continues after errors
5. Confirm final statistics are accurate

Test Case 5: Character Encoding

1. Create SQL file with international characters
2. Include various Unicode characters
3. Process file and verify proper encoding
4. Check that characters display correctly
5. Validate database storage is correct

---

📚 Related Documentation

• CLAUDE.md - PHP 8.2 migration guide
• backupController.php - Database backup creation
• databaseController.php - General database management
• Database Schema Documentation - Table relationships

---

Documented By: AI Assistant

Review Status: ✅ Complete

Next Review: When major changes occur

📚 ERP Docs

Restoredaybackup Documentation

Restore Day Backup Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

No Direct Table Operations

Connection Requirements

🔑 Key Functions

1. Default Action - Upload Interface

2. runsql() - Main Backup Restoration Function

🔄 Workflows

Workflow 1: Database Restoration Process

🌐 URL Routes & Actions

File Upload Requirements

🧮 Processing Methods

File Content Cleaning

SQL Command Splitting

Transaction Command Handling

Statistics Tracking

🔒 Security & Permissions

File Upload Security

Authentication & Authorization

📊 Performance Considerations

File Processing Performance

Database Performance

🐛 Common Issues & Troubleshooting

1. File Upload Failures

2. SQL Execution Errors

3. Character Encoding Issues

4. Memory Exhaustion

🧪 Testing Scenarios

Test Case 1: Basic SQL File Restoration

Test Case 2: Transaction Handling

Test Case 3: Large File Processing

Test Case 4: Error Handling

Test Case 5: Character Encoding

📚 Related Documentation