init_metadata_db.sql

-- SQL script to initialize the metadata database for MySQL MCP

CREATE DATABASE IF NOT EXISTS metadata_db;
USE metadata_db;

-- Table to store database connection information
CREATE TABLE IF NOT EXISTS database_connections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    host VARCHAR(255) NOT NULL,
    port INT NOT NULL DEFAULT 3306,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    database_name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table to store table metadata
CREATE TABLE IF NOT EXISTS table_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    database_id INT NOT NULL,
    table_name VARCHAR(255) NOT NULL,
    table_schema JSON,
    row_count INT DEFAULT 0,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (database_id) REFERENCES database_connections(id) ON DELETE CASCADE,
    UNIQUE KEY unique_table_per_db (database_id, table_name)
);

-- Table to store column metadata
CREATE TABLE IF NOT EXISTS column_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_id INT NOT NULL,
    column_name VARCHAR(255) NOT NULL,
    data_type VARCHAR(255) NOT NULL,
    is_nullable BOOLEAN NOT NULL DEFAULT TRUE,
    column_key VARCHAR(10),
    column_comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (table_id) REFERENCES table_metadata(id) ON DELETE CASCADE,
    UNIQUE KEY unique_column_per_table (table_id, column_name)
);

-- Table to store query history
CREATE TABLE IF NOT EXISTS query_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    database_id INT NOT NULL,
    query_text TEXT NOT NULL,
    natural_language_query TEXT,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    execution_time_ms INT NOT NULL,
    result_count INT NOT NULL DEFAULT 0,
    FOREIGN KEY (database_id) REFERENCES database_connections(id) ON DELETE CASCADE
);

-- Insert sample database connection
INSERT INTO database_connections (name, host, port, username, password, database_name, description) 
VALUES 
    ('Sample DB 1', 'localhost', 3306, 'user1', 'pass1', 'sample_db1', 'Sample database for testing'),
    ('Sample DB 2', 'localhost', 3306, 'user2', 'pass2', 'sample_db2', 'Another sample database');