# 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
- [x] Upload and process SQL backup files
- [x] Execute SQL commands with transaction support
- [x] Track successful and failed query executions
- [x] Clean SQL files (remove comments and empty lines)
- [x] Provide detailed execution feedback
- [x] Handle UTF-8 character set properly
- [x] 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**:
```php
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**:
```php
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**:
```php
$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**:
```php
// 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**:
```php
$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**:
```php
return array($success, $errors);
```

---

## 🔄 Workflows

### Workflow 1: Database Restoration Process
```
┌─────────────────────────────────────────────────────────────┐
│                START: Backup File Upload                   │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. File Upload Validation                                  │
│     - Check $_FILES['pfile']['error'] == UPLOAD_ERR_OK     │
│     - Verify is_uploaded_file() returns true               │
│     - Ensure file handle can be opened                     │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Read 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                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Establish Database Connection                           │
│     - Connect using ConnectionProperty credentials         │
│     - Check connection success                              │
│     - Set UTF-8 character encoding:                        │
│       ├─ SET NAMES 'utf8'                                  │
│       └─ SET CHARACTER_SET utf8                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Split 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            │
│       │   │           Show command and result              │
│       │   └─ ERROR:   Increment error counter              │
│       │               Show command and error message       │
│       │                                                     │
│       └─→ Display real-time progress                       │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Display 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
```php
// 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
```php
// 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
```php
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
```php
$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**:
```php
// 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**:
```php
// 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**:
```php
// 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**:
```php
// 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**:
```php
// 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**:
```sql
-- 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**:
```php
// 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**:
```php
// 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**:
```php
// 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](/Applications/AMPPS/www/erp19/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