create_table.php

<?php
/**
 * TironData — DBMS: Data Builder for Massive Samples
 *
 * @package   TironData
 * @author    Harits Nala B. <developer.haritsnb@gmail.com>
 * @license   MIT License <https://opensource.org/licenses/MIT>
 * @link      https://github.com/haritsnb/tirondata
 */

session_start();
header('Content-Type: application/json; charset=utf-8');

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['success' => false, 'message' => 'Metode tidak diizinkan']);
    exit;
}

if (!isset($_SESSION['db_connection'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Sesi koneksi tidak ditemukan']);
    exit;
}

 $input = json_decode(file_get_contents('php://input'), true);
if (!$input) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Payload tidak valid']);
    exit;
}

 $tableName = trim($input['tableName'] ?? '');
 $engine    = trim($input['engine'] ?? 'InnoDB');
 $charset   = trim($input['charset'] ?? 'utf8mb4');
 $comment   = trim($input['comment'] ?? '');
 $columns   = $input['columns'] ?? '';

if ($tableName === '') {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Nama tabel wajib diisi']);
    exit;
}
if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName)) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Nama tabel hanya boleh berisi huruf, angka, dan underscore']);
    exit;
}

 $allowedEngines = ['InnoDB', 'MyISAM', 'MEMORY', 'CSV', 'ARCHIVE', 'BLACKHOLE'];
if (!in_array($engine, $allowedEngines)) $engine = 'InnoDB';
 $allowedCharsets = ['utf8mb4', 'utf8', 'latin1', 'ascii', 'utf16', 'utf32'];
if (!in_array($charset, $allowedCharsets)) $charset = 'utf8mb4';

if (empty($columns) || !is_array($columns)) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Tabel harus memiliki minimal satu kolom']);
    exit;
}

function isNumericType($t) { return in_array($t, ['INT','TINYINT','SMALLINT','MEDIUMINT','BIGINT','FLOAT','DOUBLE','DECIMAL']); }
function isIntegerType($t) { return in_array($t, ['INT','TINYINT','SMALLINT','MEDIUMINT','BIGINT']); }

 $columnDefs     = [];
 $primaryKeys    = [];
 $uniqueKeys     = [];
 $indexes        = [];
 $usedNames      = [];

foreach ($columns as $i => $col) {
    $colName       = trim($col['name'] ?? '');
    $colType       = strtoupper(trim($col['type'] ?? ''));
    $colLength     = trim($col['length'] ?? '');
    $defaultType   = strtoupper(trim($col['defaultType'] ?? 'NONE'));
    $defaultValue  = $col['defaultValue'] ?? '';
    $colComment    = trim($col['comment'] ?? '');
    $colIndex      = strtoupper(trim($col['index'] ?? 'NONE'));
    $isNull        = !empty($col['isNull']);
    $isUnsigned    = !empty($col['isUnsigned']);
    $isAutoInc     = !empty($col['isAutoIncrement']);

    if ($colName === '') {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Nama kolom ke-' . ($i + 1) . ' wajib diisi']);
        exit;
    }
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $colName)) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Nama kolom "' . $colName . '" tidak valid']);
        exit;
    }
    if (in_array(strtolower($colName), $usedNames)) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Nama kolom "' . $colName . '" duplikat']);
        exit;
    }
    $usedNames[] = strtolower($colName);

    if ($colType === '') {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Tipe kolom "' . $colName . '" wajib dipilih']);
        exit;
    }

    if ($isAutoInc && !isIntegerType($colType)) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'AUTO_INCREMENT hanya untuk tipe integer (kolom "' . $colName . '")']);
        exit;
    }

    /* ---- Bangun definisi SQL kolom ---- */
    $safeName = str_replace('`', '``', $colName);
    $def = '`' . $safeName . '` ' . $colType;

    if ($isUnsigned && isNumericType($colType)) $def .= ' UNSIGNED';
    if ($colLength !== '') $def .= '(' . $colLength . ')';
    if ($isNull) { $def .= ' NULL'; } else { $def .= ' NOT NULL'; }

    if ($isNull && ($defaultType === 'NONE' || $defaultType === 'NULL')) {
        $def .= ' DEFAULT NULL';
    } elseif ($defaultType === 'CURRENT_TIMESTAMP') {
        $def .= ' DEFAULT CURRENT_TIMESTAMP';
    } elseif ($defaultType === 'USER_DEFINED' && $defaultValue !== '') {
        $def .= is_numeric($defaultValue)
            ? ' DEFAULT ' . $defaultValue
            : " DEFAULT '" . str_replace("'", "''", $defaultValue) . "'";
    }

    if ($isAutoInc) $def .= ' AUTO_INCREMENT';
    if ($colComment !== '') $def .= " COMMENT '" . str_replace("'", "''", $colComment) . "'";

    $columnDefs[] = $def;

    /* ---- Kumpulkan index berdasarkan pilihan user ---- */
    if ($colIndex === 'PRIMARY') {
        $primaryKeys[] = '`' . $safeName . '`';
    } elseif ($colIndex === 'UNIQUE') {
        $uniqueKeys[] = '`' . $safeName . '`';
    } elseif ($colIndex === 'INDEX') {
        $indexes[] = '`' . $safeName . '`';
    }
}

/* ---- Bangun bagian index ---- */
 $indexDefs = '';
if (!empty($primaryKeys)) {
    $indexDefs .= ', PRIMARY KEY (' . implode(', ', $primaryKeys) . ')';
}
foreach ($uniqueKeys as $uk) {
    /* trim() menghapus backtick kiri DAN kanan, sehingga nama index bersih */
    $cleanUk = trim($uk, '`');
    $indexDefs .= ', UNIQUE KEY `uk_' . $cleanUk . '` (' . $uk . ')';
}
foreach ($indexes as $idx) {
    /* trim() menghapus backtick kiri DAN kanan, sehingga nama index bersih */
    $cleanIdx = trim($idx, '`');
    $indexDefs .= ', KEY `idx_' . $cleanIdx . '` (' . $idx . ')';
}

/* ---- Bangun SQL lengkap ---- */
 $safeTable = str_replace('`', '``', $tableName);
 $sql = "CREATE TABLE `{$safeTable}` (\n  "
     . implode(",\n  ", $columnDefs)
     . $indexDefs
     . "\n) ENGINE={$engine} DEFAULT CHARSET={$charset}";

if ($comment !== '') {
    $sql .= " COMMENT='" . str_replace("'", "''", $comment) . "'";
}

/* ---- Eksekusi ---- */
 $c = $_SESSION['db_connection'];
try {
    $dsn = "mysql:host={$c['host']};port={$c['port']};dbname={$c['database']};charset=utf8mb4";
    $pdo = new PDO($dsn, $c['username'], $c['password'], [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $pdo->exec($sql);

    echo json_encode([
        'success' => true,
        'message' => 'Tabel "' . $tableName . '" berhasil dibuat dengan ' . count($columnDefs) . ' kolom',
    ]);

} catch (PDOException $e) {
    $msg = $e->getMessage();
    if (strpos($msg, 'already exists') !== false) {
        $msg = 'Tabel "' . $tableName . '" sudah ada';
    } elseif (strpos($msg, 'BLOB/TEXT column') !== false) {
        $msg = 'Kolom TEXT/BLOB tidak boleh memiliki default value';
    } elseif (strpos($msg, 'Used default value') !== false) {
        $msg = 'Nilai default tidak sesuai dengan tipe kolom';
    }
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Gagal membuat tabel: ' . $msg]);
}