ITL-Huge/application/model/DatabaseModel.php

<?php

/**
 * Class DatabaseModel
 * 
 * Model for database operations using PDO
 */
class DatabaseModel
{
    /**
     * Get all databases on the server
     * @return array
     */
    public static function getAllDatabases()
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        
        $sql = "SHOW DATABASES";
        $query = $database->prepare($sql);
        $query->execute();
        
        $databases = $query->fetchAll(PDO::FETCH_COLUMN);
        
        $system_dbs = ['information_schema', 'performance_schema', 'mysql', 'sys'];
        return array_diff($databases, $system_dbs);
    }

    /**
     * Get all tables in a specific database
     * @param string $database_name
     * @return array
     */
    public static function getTablesInDatabase($database_name)
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        
        $sql = "SHOW TABLES FROM `" . $database_name . "`";
        $query = $database->prepare($sql);
        $query->execute();
        
        return $query->fetchAll(PDO::FETCH_COLUMN);
    }

    /**
     * Get detailed information about tables in a database
     * @param string $database_name
     * @return array
     */
    public static function getTableDetails($database_name)
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        $tables = self::getTablesInDatabase($database_name);
        $table_details = array();
        
        foreach ($tables as $table) {
            $sql = "SHOW TABLE STATUS FROM `" . $database_name . "` LIKE :table_name";
            $query = $database->prepare($sql);
            $query->execute(array(':table_name' => $table));
            
            $details = $query->fetch(PDO::FETCH_ASSOC);
            if ($details) {
                $table_details[$table] = array(
                    'engine' => $details['Engine'],
                    'rows' => $details['Rows'],
                    'data_size' => self::formatBytes($details['Data_length']),
                    'index_size' => self::formatBytes($details['Index_length']),
                    'total_size' => self::formatBytes($details['Data_length'] + $details['Index_length']),
                    'collation' => $details['Collation'],
                    'comment' => $details['Comment']
                );
            }
        }
        
        return $table_details;
    }

    /**
     * Get complete database structure (tables and columns)
     * @param string $database_name
     * @return array
     */
    public static function getDatabaseStructure($database_name)
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        $structure = array();
        $tables = self::getTablesInDatabase($database_name);
        
        foreach ($tables as $table) {
            $sql = "DESCRIBE `" . $database_name . "`.`" . $table . "`";
            $query = $database->prepare($sql);
            $query->execute();
            
            $columns = $query->fetchAll(PDO::FETCH_ASSOC);
            $structure[$table] = $columns;
        }
        
        return $structure;
    }

    /**
     * Create a new database
     * @param string $database_name
     * @return bool
     */
    public static function createDatabase($database_name)
    {
        if (!$database_name || !preg_match('/^[a-zA-Z0-9_]+$/', $database_name)) {
            return false;
        }

        $database = DatabaseFactory::getFactory()->getConnection();
        
        try {
            $sql = "CREATE DATABASE `" . $database_name . "` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
            $query = $database->prepare($sql);
            return $query->execute();
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Delete a database
     * @param string $database_name
     * @return bool
     */
    public static function deleteDatabase($database_name)
    {
        if (!$database_name) {
            return false;
        }

        $database = DatabaseFactory::getFactory()->getConnection();
        
        try {
            $sql = "DROP DATABASE `" . $database_name . "`";
            $query = $database->prepare($sql);
            return $query->execute();
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Get table columns with details
     * @param string $database_name
     * @param string $table_name
     * @return array
     */
    public static function getTableColumns($database_name, $table_name)
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        
        $sql = "SHOW COLUMNS FROM `" . $database_name . "`.`" . $table_name . "`";
        $query = $database->prepare($sql);
        $query->execute();
        
        return $query->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Export database as SQL dump
     * @param string $database_name
     * @return string
     */
    public static function exportDatabase($database_name)
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        $output = "-- Database Export: " . $database_name . "\n";
        $output .= "-- Generated: " . date('Y-m-d H:i:s') . "\n\n";
        $output .= "SET FOREIGN_KEY_CHECKS=0;\n";
        $output .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n\n";

        $tables = self::getTablesInDatabase($database_name);

        foreach ($tables as $table) {
            $sql = "SHOW CREATE TABLE `" . $database_name . "`.`" . $table . "`";
            $query = $database->prepare($sql);
            $query->execute();
            $row = $query->fetch(PDO::FETCH_NUM);

            $output .= "DROP TABLE IF EXISTS `" . $table . "`;\n";
            $output .= $row[1] . ";\n\n";

            $sql = "SELECT * FROM `" . $database_name . "`.`" . $table . "`";
            $query = $database->prepare($sql);
            $query->execute();
            $rows = $query->fetchAll(PDO::FETCH_ASSOC);

            if (!empty($rows)) {
                foreach ($rows as $dataRow) {
                    $columns = array_keys($dataRow);
                    $values = array_map(function($val) use ($database) {
                        if ($val === null) {
                            return 'NULL';
                        }
                        return $database->quote($val);
                    }, array_values($dataRow));

                    $output .= "INSERT INTO `" . $table . "` (`" . implode("`, `", $columns) . "`) VALUES (" . implode(", ", $values) . ");\n";
                }
                $output .= "\n";
            }
        }

        $output .= "SET FOREIGN_KEY_CHECKS=1;\n";
        return $output;
    }

    /**
     * Export single table as SQL dump
     * @param string $database_name
     * @param string $table_name
     * @return string
     */
    public static function exportTable($database_name, $table_name)
    {
        $database = DatabaseFactory::getFactory()->getConnection();
        $output = "-- Table Export: " . $table_name . " from " . $database_name . "\n";
        $output .= "-- Generated: " . date('Y-m-d H:i:s') . "\n\n";
        $output .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

        $sql = "SHOW CREATE TABLE `" . $database_name . "`.`" . $table_name . "`";
        $query = $database->prepare($sql);
        $query->execute();
        $row = $query->fetch(PDO::FETCH_NUM);

        $output .= "DROP TABLE IF EXISTS `" . $table_name . "`;\n";
        $output .= $row[1] . ";\n\n";

        $sql = "SELECT * FROM `" . $database_name . "`.`" . $table_name . "`";
        $query = $database->prepare($sql);
        $query->execute();
        $rows = $query->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($rows)) {
            foreach ($rows as $dataRow) {
                $columns = array_keys($dataRow);
                $values = array_map(function($val) use ($database) {
                    if ($val === null) {
                        return 'NULL';
                    }
                    return $database->quote($val);
                }, array_values($dataRow));

                $output .= "INSERT INTO `" . $table_name . "` (`" . implode("`, `", $columns) . "`) VALUES (" . implode(", ", $values) . ");\n";
            }
        }

        $output .= "\nSET FOREIGN_KEY_CHECKS=1;\n";
        return $output;
    }

    /**
     * Format bytes to human readable format
     * @param int $bytes
     * @return string
     */
    private static function formatBytes($bytes)
    {
        if ($bytes >= 1073741824) {
            return number_format($bytes / 1073741824, 2) . ' GB';
        } elseif ($bytes >= 1048576) {
            return number_format($bytes / 1048576, 2) . ' MB';
        } elseif ($bytes >= 1024) {
            return number_format($bytes / 1024, 2) . ' KB';
        } elseif ($bytes > 1) {
            return $bytes . ' bytes';
        } elseif ($bytes == 1) {
            return '1 byte';
        } else {
            return '0 bytes';
        }
    }
}