Php script to Output data definition language from a MySQL Database

OutputDDL.md

Sure, here's a refactored version of the script that is easier to maintain for developers:

// Database configuration
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$dbname = 'your_database_name';

// Create a new PDO object
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Error connecting to database: " . $e->getMessage());
}

// Query the information_schema database for table names
$stmt = $conn->prepare("SELECT table_name FROM information_schema.tables WHERE table_schema = :dbname");
$stmt->execute(array(':dbname' => $dbname));
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

// Loop through each table and generate the SQL DDL
$sql_ddl = '';
foreach ($tables as $table) {
    $columns = getTableColumns($conn, $dbname, $table);
    $sql_ddl .= generateTableSql($table, $columns);
}

// Close the database connection
$conn = null;

// Output the SQL DDL
echo $sql_ddl;

/**
 * Returns an array of columns for the specified table.
 *
 * @param PDO $conn The PDO database connection.
 * @param string $dbname The database name.
 * @param string $table The table name.
 * @return array An array of columns for the specified table.
 */
function getTableColumns($conn, $dbname, $table)
{
    $stmt = $conn->prepare("SELECT column_name, column_type, is_nullable, column_default, column_key, extra, column_comment FROM information_schema.columns WHERE table_schema = :dbname AND table_name = :table");
    $stmt->execute(array(':dbname' => $dbname, ':table' => $table));
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/**
 * Generates the SQL DDL for the specified table and columns.
 *
 * @param string $table The table name.
 * @param array $columns An array of columns for the table.
 * @return string The SQL DDL for the specified table and columns.
 */
function generateTableSql($table, $columns)
{
    $sql_ddl = "CREATE TABLE $table (\n";
    foreach ($columns as $column) {
        $column_name = $column['column_name'];
        $column_type = $column['column_type'];
        $is_nullable = $column['is_nullable'];
        $column_default = $column['column_default'];
        $column_key = $column['column_key'];
        $extra = $column['extra'];
        $column_comment = $column['column_comment'];

        $sql_ddl .= "  $column_name $column_type";
        if ($is_nullable == 'NO') {
            $sql_ddl .= " NOT NULL";
        }
        if ($column_default !== null) {
            $sql_ddl .= " DEFAULT $column_default";
        }
        if ($column_key == 'PRI') {
            $sql_ddl .= " PRIMARY KEY";
        }
        if ($extra !== '') {
            $sql_ddl .= " $extra";
        }
        if ($column_comment !== '') {
            $sql_ddl .= " COMMENT '$column_comment'";
        }
        $sql_ddl .= ",\n";
    }
    // Remove the trailing comma from the last column definition
    $sql_ddl = rtrim($sql_ddl, ",\n");
    $sql_ddl .= "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n\n";
    return $sql_ddl;
}

In this refactored version, the code has been split into three functions: getTableColumns(), generateTableSql(), and the main foreach loop. This makes it easier to read and maintain the code, as each function has a specific responsibility and can be tested independently.

The getTableColumns() function retrieves the columns for a given table, while the generateTableSql() function generates the SQL DDL for a given table and its columns. These functions are called from the main foreach loop, which loops through each table in the database and generates the SQL DDL for each table.

Additionally, error handling has been added to the database connection code using a try/catch block, which makes it easier to handle database connection errors.