import { z, ZodError } from "zod";
/** Extract human-readable messages from a ZodError instead of raw JSON array */
function formatZodError(error: ZodError): string {
return error.issues.map((i) => i.message).join("; ");
}
import type { MySQLAdapter } from "../../MySQLAdapter.js";
import type {
ToolDefinition,
RequestContext,
} from "../../../../types/index.js";
import {
validateQualifiedIdentifier,
escapeQualifiedTable,
} from "../../../../utils/validators.js";
const ListViewsSchema = z.object({
schema: z
.string()
.optional()
.describe("Schema name (defaults to current database)"),
});
const CreateViewSchemaBase = z.object({
name: z.string().describe("View name"),
definition: z.string().describe("SELECT statement defining the view"),
orReplace: z.boolean().default(false).describe("Use CREATE OR REPLACE"),
algorithm: z.string().default("UNDEFINED").describe("View algorithm"),
checkOption: z.string().default("NONE").describe("WITH CHECK OPTION"),
});
const CreateViewSchema = z.object({
name: z.string().describe("View name"),
definition: z.string().describe("SELECT statement defining the view"),
orReplace: z.boolean().default(false).describe("Use CREATE OR REPLACE"),
algorithm: z
.enum(["UNDEFINED", "MERGE", "TEMPTABLE"])
.default("UNDEFINED")
.describe("View algorithm"),
checkOption: z
.enum(["NONE", "CASCADED", "LOCAL"])
.default("NONE")
.describe("WITH CHECK OPTION"),
});
/**
* List all views
*/
export function createListViewsTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: "mysql_list_views",
title: "MySQL List Views",
description:
"List all views with their definitions, security type, and check option.",
group: "schema",
inputSchema: ListViewsSchema,
requiredScopes: ["read"],
annotations: {
readOnlyHint: true,
idempotentHint: true,
},
handler: async (params: unknown, _context: RequestContext) => {
let parsed;
try {
parsed = ListViewsSchema.parse(params);
} catch (error: unknown) {
if (error instanceof ZodError) {
return { success: false, error: formatZodError(error) };
}
throw error;
}
const { schema } = parsed;
// P154: Schema existence check when explicitly provided
if (schema) {
const schemaCheck = await adapter.executeQuery(
"SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?",
[schema],
);
if (!schemaCheck.rows || schemaCheck.rows.length === 0) {
return { exists: false, schema };
}
}
const query = `
SELECT
TABLE_NAME as name,
VIEW_DEFINITION as definition,
DEFINER as definer,
SECURITY_TYPE as securityType,
CHECK_OPTION as checkOption,
IS_UPDATABLE as isUpdatable
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = COALESCE(?, DATABASE())
ORDER BY TABLE_NAME
`;
const result = await adapter.executeQuery(query, [schema ?? null]);
return {
views: result.rows,
count: result.rows?.length ?? 0,
};
},
};
}
/**
* Create a view
*/
export function createCreateViewTool(adapter: MySQLAdapter): ToolDefinition {
return {
name: "mysql_create_view",
title: "MySQL Create View",
description:
"Create or replace a view with specified algorithm and check option.",
group: "schema",
inputSchema: CreateViewSchemaBase,
requiredScopes: ["write"],
annotations: {
readOnlyHint: false,
},
handler: async (params: unknown, _context: RequestContext) => {
let parsed;
try {
parsed = CreateViewSchema.parse(params);
} catch (error: unknown) {
if (error instanceof ZodError) {
return { success: false, error: formatZodError(error) };
}
throw error;
}
const { name, definition, orReplace, algorithm, checkOption } = parsed;
try {
validateQualifiedIdentifier(name, "view");
} catch (err: unknown) {
const message = err instanceof Error ? err.message : String(err);
return { success: false, error: message };
}
const fullViewName = escapeQualifiedTable(name);
const createClause = orReplace ? "CREATE OR REPLACE" : "CREATE";
let sql = `${createClause} ALGORITHM=${algorithm} VIEW ${fullViewName} AS ${definition}`;
if (checkOption !== "NONE") {
sql += ` WITH ${checkOption} CHECK OPTION`;
}
try {
await adapter.executeQuery(sql);
adapter.clearSchemaCache();
return { success: true, viewName: name };
} catch (err: unknown) {
const message = err instanceof Error ? err.message : String(err);
if (message.toLowerCase().includes("already exists")) {
return {
success: false,
error: `View '${name}' already exists`,
};
}
return {
success: false,
error: message
.replace(/^Query failed:\s*/i, "")
.replace(/^Execute failed:\s*/i, ""),
};
}
},
};
}
