Table Command

This TableCommand class provides methods to synchronize database tables by creating, updating, and modifying schema definitions in MySQL.

handle() - Execute Table Synchronization

public function handle($tableName) {
    $modelClass = "\\App\\Models\\".ucfirst($tableName).'Model';
    
    // Initialize the model
    $model = new $modelClass();
    $table = $model->_table();
    $schema = $model->_schema();
    
    // Check the table and synchronize its structure
    $this->syncTableSchema($table, $schema);

    echo "{$table} Table has been synchronized.\n";
}

• Dynamically creates a model class using the given $tableName.

• Calls _table() and _schema() methods from the model to retrieve table name and schema definition.

• Passes these values to syncTableSchema() for synchronization.

syncTableSchema() - Synchronize Table Schema

protected function syncTableSchema($table, $schema) {
    if (!$this->tableExists($table)) {
        $this->createTable($table, $schema);
    } else {
        $this->updateTable($table, $schema);
    }
}

• Checks if the table exists using tableExists().

• If the table does not exist, calls createTable().

• Otherwise, calls updateTable() to modify the schema.

tableExists() - Check If Table Exists

protected function tableExists($table) {
    $query = "SHOW TABLES LIKE '{$table}'";
    $stmt = $this->db->prepare($query);
    $stmt->execute();
    return $stmt->rowCount() > 0;
}

• Executes a SQL query to check if a table exists in the database.

• Returns true if the table exists, otherwise false.

createTable() - Create a New Table

protected function createTable($table, $schema) {
    $columns = [];
    foreach ($schema as $column => $attributes) {
        $columns[] = $this->buildColumnTable($column, $attributes);
    }
    $configdb = config('db');
    $charset = $configdb['db_charset'] ?? 'utf8mb4';
    $collate = $configdb['db_collate'] ?? 'utf8mb4_unicode_ci';

    $query = "CREATE TABLE {$table} (" . implode(', ', $columns) . ") ENGINE=InnoDB DEFAULT CHARSET={$charset} COLLATE={$collate};";
    $stmt = $this->db->prepare($query);
    $stmt->execute();

    echo "Đã tạo bảng {$table}\n";
}

• Loops through the schema and builds SQL column definitions.

• Constructs a CREATE TABLE SQL statement using utf8mb4 charset.

• Executes the query to create the table in MySQL.

updateTable() - Update Table Schema

protected function updateTable($table, $schema) {
    $currentColumns = $this->getCurrentColumns($table);
    foreach ($schema as $column => $attributes) {
        if (!array_key_exists($column, $currentColumns)) {
            $this->addColumn($table, $column, $attributes);
        } else if ($this->needsModification($column, $currentColumns[$column], $attributes)) {
            $this->modifyColumn($table, $column, $attributes);
        }
    }
}

• Retrieves the current column list of the table.

• If a column does not exist, calls addColumn().

• If a column needs modification, calls modifyColumn().

getCurrentColumns() - Get Current Table Columns

protected function getCurrentColumns($table) {
    $query = "SHOW COLUMNS FROM {$table}";
    $stmt = $this->db->prepare($query);
    $stmt->execute();
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

• Executes the SHOW COLUMNS SQL command to retrieve all columns in a table.

Example:

If you want to create a new Table and named as 'user',mMake sure you have the UserModel.php file in the application/Models directory with the following content:

<?php
namespace App\Models;

use System\Core\BaseModel;

class UserModel extends BaseModel {

    protected $table = 'users';

    /**
     * Định nghĩa schema của bảng
     */
    public function _schema() {
        return [
            'id' => [
                'type' => 'INT',
                'auto_increment' => true,
                'key' => 'primary'
            ],
            'name' => [
                'type' => 'VARCHAR(255)',
                'null' => false
            ],
            'email' => [
                'type' => 'VARCHAR(255)',
                'null' => false,
                'key' => 'unique'
            ],
            'created_at' => [
                'type' => 'TIMESTAMP',
                'default' => 'CURRENT_TIMESTAMP'
            ]
        ];
    }
}

Then run the following command:

php init table user

And the result:

CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table "users" has been created successfully.