/**
* 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: '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' }] })
.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 () => {
mockAdapter.executeQuery.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 };
expect(result.count).toBe(2);
expect(result.columns).toHaveLength(2);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("udt_name = 'citext'"),
[]
);
});
it('should filter by schema when provided', async () => {
mockAdapter.executeQuery.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']
);
});
});
describe('pg_citext_analyze_candidates', () => {
it('should find email and username columns', async () => {
mockAdapter.executeQuery.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 };
};
expect(result.count).toBe(2);
expect(result.summary.highConfidence).toBe(2);
});
it('should use custom patterns when provided', async () => {
mockAdapter.executeQuery.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: [{ 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');
});
});
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);
});
});
