# Archive Controller Documentation

**File**: `/controllers/archiveController.php`  
**Purpose**: Database archiving, backup, and restoration operations with legacy MySQL support  
**Last Updated**: December 20, 2024  
**Total Functions**: 8+  
**Lines of Code**: ~547

---

## 📋 Overview

The Archive Controller provides comprehensive database management capabilities including backup creation, restoration, and database engine conversion. It handles:
- Complete database backup with selective table inclusion
- Database structure and data export to SQL files
- Database restoration from archived copies
- Multi-database management and switching
- Database engine conversion (MyISAM/InnoDB)
- Table emptying operations
- Legacy MySQL connection management
- Transaction-safe operations with rollback support

### Primary Functions
- [x] Create complete database backups with selective data
- [x] Export database structure and data to SQL format
- [x] Restore databases from archived copies
- [x] Switch between multiple database instances
- [x] Convert database engine types
- [x] Empty database tables safely
- [x] Manage database registry
- [x] Handle UTF-8 character encoding
- [x] Process large SQL files with transaction safety

### Related Controllers
- [archive2Controller.php](archive2Controller.md) - Advanced archiving with modern features
- [backupController.php](backupController.md) - Database backup utilities
- [programsettingsController.php](programsettingsController.md) - System configuration

---

## 🗄️ Database Tables

### Core Management Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **newdbname** | Database registry | newdbnameId, dbname |
| **usergroup** | User group settings | usergroupid, startpage |

### System Tables (Always Stable)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **billproperty** | Bill configuration | billpropertyid |
| **billname** | Bill naming | billnameid |
| **billsettings** | Bill settings | billsettingsid |
| **capital** | Capital accounts | capitalid |
| **properties** | System properties | propertiesid |
| **relusergroupproperties** | User permissions | relusergrouppropertiesid |
| **user** | System users | userid |
| **usergroup** | User groups | usergroupid |
| **programsettings** | Program settings | programsettingsid |
| **store** | Store/warehouse | storeid |
| **save** | Cash registers | saveid |
| **sparepartstore** | Spare part storage | sparepartstoreid |

### Variable Tables (Optional Migration)
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **product** | Product master | productId, productCatId |
| **productcat** | Product categories | productCatId |
| **client** | Customer data | clientid |
| **supplier** | Supplier data | supplierid |
| **employee** | Employee records | employeeid |
| **sellbill** | Sales bills | sellbillid |
| **buybill** | Purchase bills | buybillid |
| **storedetail** | Inventory details | storedetailid |
| **storemovement** | Stock movements | storemovementid |

### Transaction Tables
| Table Name | Purpose | Key Columns |
|------------|---------|-------------|
| **accountmovement** | Account movements | accountmovementid |
| **clientdebtchange** | Customer debt changes | clientdebtchangeid |
| **supplierdebtchange** | Supplier debt changes | supplierdebtchangeid |
| **savedaily** | Daily cash transactions | savedailyid |
| **transfermoney** | Money transfers | transfermoneyid |

---

## 🔑 Key Functions

### 1. **createNewDB()** - Database Creation and Export
**Location**: Line 168  
**Purpose**: Create new database with selective table and data migration

**Function Signature**:
```php
function createNewDB()
```

**Process Flow**:
1. Parse selected migration options from form
2. Build stable and variable table arrays
3. Export stable tables with data
4. Export variable tables (structure only)
5. Create new database with timestamped name
6. Execute SQL commands with transaction safety
7. Register new database in system

**Migration Options Supported**:
```php
$choosedItemArr = $_POST['choosedItem'];
// Options include: product, unit, productUnit, productCat, employee, 
// client, supplier, bank, asset, sparepart, expense, etc.
```

**Table Classification Logic**:
```php
// Stable tables (migrated with data)
$stableTables = array("billproperty", "billname", "billsettings", ...);

// Variable tables (structure only)
$variableTables = array("accountmovement", "clientdebtchange", ...);
```

---

### 2. **export_stable_tables()** - Data Export with UTF-8 Support
**Location**: Line 416  
**Purpose**: Export complete table structure and data to SQL format

**Function Signature**:
```php
function export_stable_tables($host, $user, $pass, $name, $tables = '*')
```

**Process Flow**:
1. Connect to source database with UTF-8 encoding
2. Get table list (all or specified)
3. For each table: export CREATE TABLE statement
4. For each table: export all data as INSERT statements
5. Handle special characters and encoding
6. Return complete SQL dump

**UTF-8 Handling**:
```php
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");
```

**Data Export Process**:
```php
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
$return.= "\n\n" . $row2[1] . ";\n\n";

for ($i = 0; $i < $num_fields; $i++) {
    while ($row = mysql_fetch_row($result)) {
        $return.= ' INSERT INTO ' . $table . ' VALUES(';
        for ($j = 0; $j < $num_fields; $j++) {
            $row[$j] = addslashes($row[$j]);
            $row[$j] = ereg_replace("\n", "\\n", $row[$j]);
            if (isset($row[$j])) {
                $return.= '"' . $row[$j] . '"';
            } else {
                $return.= '""';
            }
            if ($j < ($num_fields - 1)) {
                $return.= ',';
            }
        }
        $return.= ");\n";
    }
}
```

---

### 3. **export_variable_tables()** - Structure-Only Export
**Location**: Line 471  
**Purpose**: Export table structures without data for clean slate operations

**Function Signature**:
```php
function export_variable_tables($host, $user, $pass, $name, $tables = '*')
```

**Process Flow**:
1. Connect to database with proper encoding
2. Get table structure using SHOW CREATE TABLE
3. Export CREATE TABLE statements only
4. Skip data insertion for clean tables

---

### 4. **run_sql_file()** - SQL File Execution
**Location**: Line 120  
**Purpose**: Execute SQL file with comment filtering and error handling

**Function Signature**:
```php
function run_sql_file($location)
```

**Process Flow**:
1. Load SQL file content
2. Filter out SQL comments
3. Split into individual commands
4. Execute each command with error tracking
5. Return success statistics

**Comment Filtering**:
```php
foreach ($lines as $line) {
    $line = trim($line);
    if ($line && !strpos($line, '--')) {
        $commands .= $line . "\n";
    }
}
```

**Execution Statistics**:
```php
return array(
    "success" => $success,
    "total" => $total
);
```

---

### 5. **loadDatabases()** - Database Registry Management
**Location**: Line 505  
**Purpose**: Load available databases for selection and switching

**Function Signature**:
```php
function loadDatabases()
```

**Returns**: Array of database records from newdbname table

---

### 6. **restoreDB()** - Database Session Switching
**Location**: Line 513  
**Purpose**: Switch active database session to selected archive

**Function Signature**:
```php
function restoreDB()
```

**Process Flow**:
1. Get selected database ID
2. Load database record
3. Update session variable
4. Redirect to user's start page

**Session Management**:
```php
if (!empty($newdbnameId)) {
    $dbData = $newDbNameDAO->load($newdbnameId);
    $_SESSION['dbname'] = $dbData->dbname;
}
```

---

### 7. **alterDB()** - Engine Conversion
**Location**: Line 535  
**Purpose**: Convert database tables between storage engines

**Function Signature**:
```php
function alterDB()
```

**Process Flow**:
1. Get engine type from form
2. Connect to target database
3. Iterate through critical tables
4. Execute ALTER TABLE ENGINE statements
5. Commit changes with transaction safety

**Supported Tables**:
```php
$tablesArray = array("buyandruternbill", "buyandruternbilldetail", "buybill", 
                    "buybilldetail", "product", "productcat", "productsetting", 
                    "productunit", "returnbuybill", "returnbuybilldetail", 
                    "returnsellbill", "returnsellbilldetail", "savedaily", 
                    "sellbill", "sellbillandrutern", "sellbilldetail", 
                    "storedetail", "storemovement", "storereport", 
                    "supplierdebtchange", "movementmanage");
```

**Engine Conversion**:
```php
if (isset($engineType) && $engineType == 1) {
    $sql = mysql_query("ALTER TABLE " . $tbl . "  ENGINE=MYISAM");
} else if (isset($engineType) && $engineType == 2) {
    $sql = mysql_query("ALTER TABLE " . $tbl . "  ENGINE=InnoDB");
}
```

---

### 8. **emptying()** - Table Data Clearing
**Location**: Line 115  
**Purpose**: Clear table data using external SQL file

**Function Signature**:
```php
function emptying()
```

**Process**: Executes "pro1.sql" file to clear database tables

---

## 🔄 Workflows

### Workflow 1: Database Archive Creation
```
┌─────────────────────────────────────────────────────────────┐
│              START: Select Archive Options                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Parse Migration Selections                             │
│     - Determine which modules to include                   │
│     - Build stable and variable table lists                │
│     - Handle table dependencies                            │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Export Stable Tables                                    │
│     FOR EACH stable table:                                  │
│       │                                                     │
│       ├─→ Export CREATE TABLE statement                    │
│       ├─→ Export all data as INSERT statements             │
│       └─→ Handle UTF-8 encoding properly                  │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Export Variable Tables                                  │
│     FOR EACH variable table:                                │
│       │                                                     │
│       ├─→ Export CREATE TABLE statement only               │
│       └─→ Skip data for clean start                        │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Create New Database                                     │
│     - Generate timestamped database name                   │
│     - Create empty database                                │
│     - Set UTF-8 character set                             │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  5. Execute SQL Import                                      │
│     - Import stable tables with data                       │
│     - Import variable table structures                     │
│     - Handle large files with line-by-line processing      │
│     - Use transaction safety with rollback                 │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  6. Register and Activate                                   │
│     - Register database in newdbname table                 │
│     - Update file registry                                 │
│     - Destroy session to force re-login                    │
└─────────────────────────────────────────────────────────────┘
```

---

### Workflow 2: Database Engine Conversion
```
┌─────────────────────────────────────────────────────────────┐
│               START: Select Engine Type                    │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  1. Validate Engine Selection                              │
│     - engineType = 1: MyISAM                              │
│     - engineType = 2: InnoDB                              │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  2. Connect to Target Database                             │
│     - Use session database or default                      │
│     - Start transaction for safety                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  3. Convert Critical Tables                                │
│     FOR EACH table in conversion list:                     │
│       │                                                     │
│       ├─→ Execute ALTER TABLE ENGINE command               │
│       ├─→ Commit individual changes                        │
│       └─→ Handle conversion errors                         │
└────────────────────┬────────────────────────────────────────┘
                     │
                     ▼
┌─────────────────────────────────────────────────────────────┐
│  4. Display Success Message                                │
│     - Show conversion completion                            │
│     - Provide navigation back to options                   │
└─────────────────────────────────────────────────────────────┘
```

---

## 🌐 URL Routes & Actions

| URL Parameter | Function Called | Description |
|---------------|----------------|-------------|
| `do=emptydata` | Show form | Display table emptying interface |
| `do=emptying` | `emptying()` | Execute table emptying via SQL file |
| `do=archive` | Show form | Display archive creation interface |
| `do=newDB` | `createNewDB()` | Create new archived database |
| `do=changDB` or `do=show` | `loadDatabases()` | Show database selection interface |
| `do=restoreDB` | `restoreDB()` | Switch to selected database |
| `do=dbType` | Show form | Display engine conversion options |
| `do=alterdb` | `alterDB()` | Convert database engine type |
| `do=sucess` | Show template | Display success message |
| `do=error` | Show template | Display error message |

### Required Parameters by Action

**Database Creation** (`do=newDB`):
- `dbName` - New database name
- `choosedItem[]` - Array of modules to include

**Database Restoration** (`do=restoreDB`):
- `newdbnameid` - Database ID to restore

**Engine Conversion** (`do=alterdb`):
- `type` - 1 (MyISAM) or 2 (InnoDB)

---

## 🔧 Legacy MySQL Support

### Connection Management
```php
$con = mysql_connect("localhost", "root", "123456");
mysql_select_db($database, $con);
$charset = mysql_client_encoding($con);
mysql_query("SET NAMES 'utf8'");
mysql_query('SET CHARACTER_SET utf8');
```

### Transaction Safety
```php
mysql_query("START TRANSACTION");
mysql_query("BEGIN");
try {
    // Operations
    mysql_query("COMMIT");
} catch (Exception $e) {
    mysql_query("ROLLBACK");
}
```

### Character Encoding
```php
// Ensure proper UTF-8 handling
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");

// Escape data for SQL insertion
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n", "\\n", $row[$j]);
```

---

## 📊 Performance Considerations

### Large Database Handling
- Line-by-line SQL processing to avoid memory issues
- Transaction-based imports for data integrity
- Proper connection management and cleanup
- Error tracking and recovery mechanisms

### Optimization Strategies
- UTF-8 encoding set at connection level
- Bulk INSERT operations for data migration
- Selective table migration to reduce size
- Engine-specific optimizations

---

## 🔒 Security Features

### Input Validation
- Database name sanitization
- SQL injection prevention through proper escaping
- Parameter validation for all operations

### Access Control
- Authentication required for all operations
- Session-based user validation
- Database switching restricted to registered databases

### Error Handling
- Graceful failure with rollback support
- Error logging and user feedback
- Transaction safety for critical operations

---

## 🐛 Common Issues & Troubleshooting

### 1. **Character Encoding Issues**
**Issue**: Arabic text becomes garbled in exports  
**Cause**: Improper UTF-8 handling

**Fix**:
```php
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");
```

### 2. **Memory Exhaustion**
**Issue**: Large database exports fail  
**Cause**: Loading entire SQL dump into memory

**Solution**: Use line-by-line processing for large files

### 3. **Transaction Failures**
**Issue**: Partial database creation  
**Cause**: SQL errors during import

**Debug**: Check individual SQL statements and error messages

### 4. **Engine Conversion Failures**
**Issue**: ALTER TABLE commands fail  
**Cause**: Foreign key constraints or table locks

**Solution**: Check for active connections and foreign key dependencies

---

## 📚 Related Documentation

- [CLAUDE.md](/Applications/AMPPS/www/erp19/CLAUDE.md) - PHP 8.2 migration guide  
- [archive2Controller.md](archive2Controller.md) - Advanced archiving features
- [Database Schema Documentation](#) - Table relationships and constraints

---

**Documented By**: AI Assistant  
**Review Status**: ✅ Complete  
**Next Review**: When major changes occur