init_metadata_db.sql•2.55 kB
-- 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');