/**
* JSON Tools Tests
*
* Tests for SQLite JSON1 tools.
*/
import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { SqliteAdapter } from '../../../src/adapters/sqlite/SqliteAdapter.js';
describe('JSON Tools', () => {
let adapter: SqliteAdapter;
let tools: Map<string, (params: unknown) => Promise<unknown>>;
beforeEach(async () => {
adapter = new SqliteAdapter();
await adapter.connect({
type: 'sqlite',
connectionString: ':memory:'
});
tools = new Map();
const toolDefs = adapter.getToolDefinitions();
const context = { scopes: ['read', 'write', 'admin'] };
for (const tool of toolDefs) {
tools.set(tool.name, (params) => tool.handler(params, context as never));
}
// Create test table with JSON data
await adapter.executeWriteQuery(`
CREATE TABLE profiles (
id INTEGER PRIMARY KEY,
data TEXT
)
`);
await adapter.executeWriteQuery(`
INSERT INTO profiles (data) VALUES
('{"name": "Alice", "age": 30, "tags": ["developer", "gamer"]}'),
('{"name": "Bob", "age": 25, "tags": ["designer"]}')
`);
});
afterEach(async () => {
await adapter.disconnect();
});
describe('sqlite_json_valid', () => {
it('should validate correct JSON', async () => {
const result = await tools.get('sqlite_json_valid')?.({
json: '{"name": "test"}'
}) as { valid: boolean };
expect(result.valid).toBe(true);
});
it('should detect invalid JSON', async () => {
const result = await tools.get('sqlite_json_valid')?.({
json: '{invalid json}'
}) as { valid: boolean };
expect(result.valid).toBe(false);
});
});
describe('sqlite_json_extract', () => {
it('should extract JSON values', async () => {
const result = await tools.get('sqlite_json_extract')?.({
table: 'profiles',
column: 'data',
path: '$.name'
}) as { values: unknown[] };
expect(result.values).toContain('Alice');
expect(result.values).toContain('Bob');
});
it('should extract nested JSON values', async () => {
const result = await tools.get('sqlite_json_extract')?.({
table: 'profiles',
column: 'data',
path: '$.age'
}) as { values: unknown[] };
expect(result.values).toContain(30);
expect(result.values).toContain(25);
});
});
describe('sqlite_json_set', () => {
it('should set JSON values', async () => {
await tools.get('sqlite_json_set')?.({
table: 'profiles',
column: 'data',
path: '$.country',
value: 'USA',
whereClause: 'id = 1'
});
const result = await adapter.executeReadQuery(
"SELECT json_extract(data, '$.country') as country FROM profiles WHERE id = 1"
);
expect(result.rows?.[0]?.['country']).toBe('USA');
});
it('should update existing JSON values', async () => {
await tools.get('sqlite_json_set')?.({
table: 'profiles',
column: 'data',
path: '$.age',
value: 31,
whereClause: 'id = 1'
});
const result = await adapter.executeReadQuery(
"SELECT json_extract(data, '$.age') as age FROM profiles WHERE id = 1"
);
expect(result.rows?.[0]?.['age']).toBe(31);
});
});
describe('sqlite_json_array_length', () => {
it('should get array length', async () => {
const result = await tools.get('sqlite_json_array_length')?.({
table: 'profiles',
column: 'data',
path: '$.tags'
}) as { lengths: number[] };
// Alice has 2 tags, Bob has 1
expect(result.lengths).toContain(2);
expect(result.lengths).toContain(1);
});
});
describe('sqlite_json_insert', () => {
it('should insert JSON row', async () => {
const result = await tools.get('sqlite_json_insert')?.({
table: 'profiles',
column: 'data',
data: { name: 'Charlie', age: 35 }
}) as { success: boolean; rowsAffected: number };
expect(result.success).toBe(true);
expect(result.rowsAffected).toBe(1);
const count = await adapter.executeReadQuery('SELECT COUNT(*) as count FROM profiles');
expect(count.rows?.[0]?.['count']).toBe(3);
});
it('should insert JSON with additional columns', async () => {
// Create table with extra column
await adapter.executeWriteQuery('CREATE TABLE test_json (id INTEGER PRIMARY KEY, json_data TEXT, label TEXT)');
await tools.get('sqlite_json_insert')?.({
table: 'test_json',
column: 'json_data',
data: { foo: 'bar' },
additionalColumns: { label: 'test' }
});
const result = await adapter.executeReadQuery('SELECT * FROM test_json');
expect(result.rows?.[0]?.['label']).toBe('test');
});
});
describe('sqlite_json_validate_path', () => {
it('should validate correct path', async () => {
const result = await tools.get('sqlite_json_validate_path')?.({
path: '$.name'
}) as { valid: boolean };
expect(result.valid).toBe(true);
});
it('should reject invalid path', async () => {
const result = await tools.get('sqlite_json_validate_path')?.({
path: 'name' // Missing $
}) as { valid: boolean; issues: string[] };
expect(result.valid).toBe(false);
expect(result.issues).toBeDefined();
});
});
});
