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

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

Related Controllers

• archive2Controller.php - Advanced archiving with modern features
• backupController.php - Database backup utilities
• programsettingsController.php - 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:

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:

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

Table Classification Logic:

// 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:

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:

mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'");

Data Export Process:

$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:

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:

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:

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

Execution Statistics:

return array(
    "success" => $success,
    "total" => $total
);

---

5. loadDatabases() - Database Registry Management

Location: Line 505

Purpose: Load available databases for selection and switching

Function Signature:

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:

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:

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:

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:

$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:

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:

function emptying()

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

---

🔄 Workflows

Workflow 1: Database Archive Creation

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

START: Select Archive Options

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

▼

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

1Parse Migration Selections

- Determine which modules to include

- Build stable and variable table lists

- Handle table dependencies

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

▼

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

2Export Stable Tables

FOR EACH stable table:

│

→ Export CREATE TABLE statement

→ Export all data as INSERT statements

│ └─→ Handle UTF-8 encoding properly │

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

▼

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

3Export Variable Tables

FOR EACH variable table:

│

→ Export CREATE TABLE statement only

│ └─→ Skip data for clean start │

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

▼

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

4Create New Database

- Generate timestamped database name

- Create empty database

- Set UTF-8 character set

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

▼

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

5Execute SQL Import

- Import stable tables with data

- Import variable table structures

- Handle large files with line-by-line processing

- Use transaction safety with rollback

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

▼

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

6Register 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

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

▼

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

1Validate Engine Selection

- engineType = 1: MyISAM

- engineType = 2: InnoDB

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

▼

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

2Connect to Target Database

- Use session database or default

- Start transaction for safety

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

▼

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

3Convert Critical Tables

FOR EACH table in conversion list:

│

→ Execute ALTER TABLE ENGINE command

→ Commit individual changes

│ └─→ Handle conversion errors │

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

▼

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

4Display 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

$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

mysql_query("START TRANSACTION");
mysql_query("BEGIN");
try {
    // Operations
    mysql_query("COMMIT");
} catch (Exception $e) {
    mysql_query("ROLLBACK");
}

Character Encoding

// 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:

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 - PHP 8.2 migration guide
• 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

📚 ERP Docs

Archive Documentation

Archive Controller Documentation

📋 Overview

Primary Functions

Related Controllers

🗄️ Database Tables

Core Management Tables

🔑 Key Functions

1. createNewDB() - Database Creation and Export

2. export_stable_tables() - Data Export with UTF-8 Support

3. export_variable_tables() - Structure-Only Export

4. run_sql_file() - SQL File Execution

5. loadDatabases() - Database Registry Management

6. restoreDB() - Database Session Switching

7. alterDB() - Engine Conversion

8. emptying() - Table Data Clearing

🔄 Workflows

Workflow 1: Database Archive Creation

Workflow 2: Database Engine Conversion

🌐 URL Routes & Actions

🔧 Legacy MySQL Support

Connection Management

Transaction Safety

Character Encoding

📊 Performance Considerations

Large Database Handling

Optimization Strategies

🔒 Security Features

Input Validation

Access Control

Error Handling

🐛 Common Issues & Troubleshooting

1. Character Encoding Issues

2. Memory Exhaustion

3. Transaction Failures

4. Engine Conversion Failures

📚 Related Documentation