/**
* postgres-mcp - Citext Extension Tools Unit Tests
*
* Tests for case-insensitive text type tools.
*/
import { describe, it, expect, vi, beforeEach } from "vitest";
import type { PostgresAdapter } from "../../PostgresAdapter.js";
import {
createMockPostgresAdapter,
createMockRequestContext,
} from "../../../../__tests__/mocks/index.js";
import { getCitextTools } from "../citext.js";
describe("Citext Tools", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let mockContext: ReturnType<typeof createMockRequestContext>;
let tools: ReturnType<typeof getCitextTools>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
mockContext = createMockRequestContext();
tools = getCitextTools(mockAdapter as unknown as PostgresAdapter);
});
const findTool = (name: string) => tools.find((t) => t.name === name);
describe("pg_citext_create_extension", () => {
it("should create citext extension", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_create_extension");
const result = (await tool!.handler({}, mockContext)) as {
success: boolean;
message: string;
};
expect(result.success).toBe(true);
expect(result.message).toContain("citext");
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("CREATE EXTENSION IF NOT EXISTS citext"),
);
});
});
describe("pg_citext_convert_column", () => {
it("should throw error if extension not installed", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ installed: false }],
});
const tool = findTool("pg_citext_convert_column");
await expect(
tool!.handler(
{
table: "users",
column: "email",
},
mockContext,
),
).rejects.toThrow("citext extension is not installed");
});
it("should throw error if column not found", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ installed: true }] })
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_convert_column");
await expect(
tool!.handler(
{
table: "users",
column: "nonexistent",
},
mockContext,
),
).rejects.toThrow("not found");
});
it("should report already citext column", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ installed: true }] })
.mockResolvedValueOnce({
rows: [{ data_type: "USER-DEFINED", udt_name: "citext" }],
});
const tool = findTool("pg_citext_convert_column");
const result = (await tool!.handler(
{
table: "users",
column: "email",
},
mockContext,
)) as { success: boolean; wasAlreadyCitext: boolean };
expect(result.success).toBe(true);
expect(result.wasAlreadyCitext).toBe(true);
});
it("should convert text column to citext", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ installed: true }] })
.mockResolvedValueOnce({
rows: [{ data_type: "text", udt_name: "text" }],
})
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_convert_column");
const result = (await tool!.handler(
{
table: "users",
column: "email",
schema: "public",
},
mockContext,
)) as { success: boolean; previousType: string };
expect(result.success).toBe(true);
expect(result.previousType).toBe("text");
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.stringContaining("ALTER TABLE"),
);
});
});
describe("pg_citext_list_columns", () => {
it("should list all citext columns", async () => {
// Mock count query first, then main query
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 2 }] })
.mockResolvedValueOnce({
rows: [
{
table_schema: "public",
table_name: "users",
column_name: "email",
},
{
table_schema: "public",
table_name: "users",
column_name: "username",
},
],
});
const tool = findTool("pg_citext_list_columns");
const result = (await tool!.handler({}, mockContext)) as {
columns: unknown[];
count: number;
totalCount: number;
truncated: boolean;
};
expect(result.count).toBe(2);
expect(result.columns).toHaveLength(2);
expect(result.totalCount).toBe(2);
expect(result.truncated).toBe(false);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("COUNT(*)"),
[],
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("udt_name = 'citext'"),
[],
);
});
it("should filter by schema when provided", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 0 }] })
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_list_columns");
await tool!.handler({ schema: "custom" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("table_schema = $1"),
["custom"],
);
});
it("should apply default limit and return truncation info", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 150 }] })
.mockResolvedValueOnce({
rows: Array(100)
.fill(null)
.map((_, i) => ({
table_schema: "public",
table_name: `table_${String(i)}`,
column_name: "col",
})),
});
const tool = findTool("pg_citext_list_columns");
const result = (await tool!.handler({}, mockContext)) as {
count: number;
totalCount: number;
truncated: boolean;
limit: number;
};
expect(result.count).toBe(100);
expect(result.totalCount).toBe(150);
expect(result.truncated).toBe(true);
expect(result.limit).toBe(100);
});
it("should return all results with limit: 0", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 3 }] })
.mockResolvedValueOnce({
rows: [
{ table_schema: "public", table_name: "a", column_name: "col" },
{ table_schema: "public", table_name: "b", column_name: "col" },
{ table_schema: "public", table_name: "c", column_name: "col" },
],
});
const tool = findTool("pg_citext_list_columns");
const result = (await tool!.handler({ limit: 0 }, mockContext)) as {
count: number;
totalCount: number;
truncated: boolean;
limit?: number;
};
expect(result.count).toBe(3);
expect(result.totalCount).toBe(3);
expect(result.truncated).toBe(false);
expect(result.limit).toBeUndefined();
// Verify no LIMIT clause in query
expect(mockAdapter.executeQuery).toHaveBeenLastCalledWith(
expect.not.stringContaining("LIMIT"),
[],
);
});
});
describe("pg_citext_analyze_candidates", () => {
// System schemas excluded by default when no schema/table filter
const systemSchemas = [
"cron",
"topology",
"partman",
"tiger",
"tiger_data",
];
it("should find email and username columns", async () => {
// Mock count query first, then main query
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 2 }] })
.mockResolvedValueOnce({
rows: [
{
table_schema: "public",
table_name: "users",
column_name: "email",
data_type: "text",
},
{
table_schema: "public",
table_name: "users",
column_name: "username",
data_type: "character varying",
},
],
});
const tool = findTool("pg_citext_analyze_candidates");
const result = (await tool!.handler({}, mockContext)) as {
count: number;
summary: { highConfidence: number };
excludedSchemas?: string[];
};
expect(result.count).toBe(2);
expect(result.summary.highConfidence).toBe(2);
// Should include system schema exclusion info
expect(result.excludedSchemas).toEqual(systemSchemas);
});
it("should use custom patterns when provided", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 1 }] })
.mockResolvedValueOnce({
rows: [{ column_name: "custom_field", data_type: "text" }],
});
const tool = findTool("pg_citext_analyze_candidates");
await tool!.handler({ patterns: ["custom"] }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("LOWER(column_name) LIKE"),
expect.arrayContaining(["%custom%"]),
);
});
it("should return recommendation when candidates found", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 1 }] })
.mockResolvedValueOnce({
rows: [{ column_name: "email", data_type: "text" }],
});
const tool = findTool("pg_citext_analyze_candidates");
const result = (await tool!.handler({}, mockContext)) as {
recommendation: string;
};
expect(result.recommendation).toContain("Consider converting");
});
it("should exclude system schemas by default", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 0 }] })
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_analyze_candidates");
const result = (await tool!.handler({}, mockContext)) as {
excludedSchemas: string[];
};
// Query should include system schema exclusion
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("table_schema NOT IN"),
expect.arrayContaining(systemSchemas),
);
expect(result.excludedSchemas).toEqual(systemSchemas);
});
it("should include all schemas when excludeSystemSchemas: false", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 0 }] })
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_analyze_candidates");
const result = (await tool!.handler(
{ excludeSystemSchemas: false },
mockContext,
)) as {
excludedSchemas?: string[];
};
// Query should NOT include extra system schema exclusion params
expect(mockAdapter.executeQuery).not.toHaveBeenCalledWith(
expect.stringContaining("table_schema NOT IN"),
expect.arrayContaining(["cron"]),
);
// Should not have excludedSchemas in response
expect(result.excludedSchemas).toBeUndefined();
});
it("should not exclude system schemas when table filter is specified", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ total: 0 }] })
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_citext_analyze_candidates");
const result = (await tool!.handler({ table: "users" }, mockContext)) as {
excludedSchemas?: string[];
};
// Should not have excludedSchemas when filtering by table
expect(result.excludedSchemas).toBeUndefined();
});
});
describe("pg_citext_compare", () => {
it("should compare values with citext extension", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ installed: true }] })
.mockResolvedValueOnce({
rows: [
{
citext_equal: true,
text_equal: false,
lower_equal: true,
},
],
});
const tool = findTool("pg_citext_compare");
const result = (await tool!.handler(
{
value1: "HELLO",
value2: "hello",
},
mockContext,
)) as {
citextEqual: boolean;
textEqual: boolean;
extensionInstalled: boolean;
};
expect(result.citextEqual).toBe(true);
expect(result.textEqual).toBe(false);
expect(result.extensionInstalled).toBe(true);
});
it("should compare values without citext extension", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ installed: false }] })
.mockResolvedValueOnce({
rows: [
{
text_equal: false,
lower_equal: true,
},
],
});
const tool = findTool("pg_citext_compare");
const result = (await tool!.handler(
{
value1: "HELLO",
value2: "hello",
},
mockContext,
)) as {
textEqual: boolean;
lowerEqual: boolean;
extensionInstalled: boolean;
hint?: string;
};
expect(result.textEqual).toBe(false);
expect(result.lowerEqual).toBe(true);
expect(result.extensionInstalled).toBe(false);
expect(result.hint).toBeDefined();
});
it("should throw validation error when value2 is missing", async () => {
const tool = findTool("pg_citext_compare");
await expect(
tool!.handler(
{
value1: "HELLO",
// value2 is missing
},
mockContext,
),
).rejects.toThrow();
});
});
describe("pg_citext_schema_advisor", () => {
it("should recommend columns for conversion", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ "?column?": 1 }] }) // table exists
.mockResolvedValueOnce({
rows: [
{ column_name: "email", data_type: "text", udt_name: "text" },
{ column_name: "username", data_type: "text", udt_name: "text" },
{ column_name: "bio", data_type: "text", udt_name: "text" },
],
});
const tool = findTool("pg_citext_schema_advisor");
const result = (await tool!.handler(
{
table: "users",
},
mockContext,
)) as {
recommendations: Array<{
column: string;
recommendation: string;
confidence: string;
}>;
summary: { recommendConvert: number; highConfidence: number };
};
expect(result.summary.recommendConvert).toBe(2); // email, username
expect(result.summary.highConfidence).toBe(2);
const emailRec = result.recommendations.find((r) => r.column === "email");
expect(emailRec?.recommendation).toBe("convert");
expect(emailRec?.confidence).toBe("high");
});
it("should detect already citext columns", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ "?column?": 1 }] }) // table exists
.mockResolvedValueOnce({
rows: [
{
column_name: "email",
data_type: "USER-DEFINED",
udt_name: "citext",
},
],
});
const tool = findTool("pg_citext_schema_advisor");
const result = (await tool!.handler(
{
table: "users",
},
mockContext,
)) as {
recommendations: Array<{ column: string; recommendation: string }>;
summary: { alreadyCitext: number };
};
expect(result.summary.alreadyCitext).toBe(1);
expect(result.recommendations[0].recommendation).toBe("already_citext");
});
it("should provide next steps when conversions recommended", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ "?column?": 1 }] }) // table exists
.mockResolvedValueOnce({
rows: [{ column_name: "email", data_type: "text", udt_name: "text" }],
});
const tool = findTool("pg_citext_schema_advisor");
const result = (await tool!.handler(
{
table: "users",
schema: "auth",
},
mockContext,
)) as { nextSteps: string[] };
expect(result.nextSteps.length).toBeGreaterThan(0);
expect(result.nextSteps.some((step) => step.includes("Review"))).toBe(
true,
);
});
it("should throw error for non-existent table", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] }); // table does not exist
const tool = findTool("pg_citext_schema_advisor");
await expect(
tool!.handler(
{
table: "nonexistent",
},
mockContext,
),
).rejects.toThrow("not found");
});
});
it("should export all 6 citext tools", () => {
const toolNames = tools.map((t) => t.name);
expect(toolNames).toContain("pg_citext_create_extension");
expect(toolNames).toContain("pg_citext_convert_column");
expect(toolNames).toContain("pg_citext_list_columns");
expect(toolNames).toContain("pg_citext_analyze_candidates");
expect(toolNames).toContain("pg_citext_compare");
expect(toolNames).toContain("pg_citext_schema_advisor");
expect(tools).toHaveLength(6);
});
});
