sqlite-search-operations.js•8.74 kB
/**
* SQLite Search Operations
* Handles search and query operations for entities and relations
*/
export class SQLiteSearchOperations {
connection;
entityOps;
relationOps;
constructor(connection, entityOps, relationOps) {
this.connection = connection;
this.entityOps = entityOps;
this.relationOps = relationOps;
}
async searchEntities(query, branchName, includeStatuses) {
const entities = await this.performSearch(query, branchName, includeStatuses);
// Get relations for the found entities
let relations = [];
if (entities.length > 0) {
const branchId = branchName
? await this.connection.getBranchId(branchName)
: undefined;
const entityNames = entities.map((e) => e.name);
relations = await this.relationOps.getRelationsForEntities(entityNames, branchId);
}
return { entities, relations };
}
async performSearch(query, branchName, includeStatuses) {
// Handle special case: "*" means search all branches
const branchId = branchName && branchName !== "*"
? await this.connection.getBranchId(branchName)
: null;
// Enhanced search with multiple strategies
const searchTerms = this.prepareSearchTerms(query);
const results = await this.executeEnhancedSearch(searchTerms, branchId, includeStatuses);
return this.entityOps.convertRowsToEntities(results);
}
prepareSearchTerms(query) {
// Split query into individual words and clean them
const terms = query
.toLowerCase()
.split(/[\s\-_,./]+/)
.filter((term) => term.length > 1)
.map((term) => term.trim());
// Remove duplicates and common stop words
const stopWords = new Set([
"the",
"and",
"or",
"but",
"in",
"on",
"at",
"to",
"for",
"of",
"with",
"by",
]);
return Array.from(new Set(terms)).filter((term) => !stopWords.has(term));
}
async executeEnhancedSearch(searchTerms, branchId, includeStatuses) {
if (searchTerms.length === 0)
return [];
// Combine multiple search strategies
const results = new Map();
// Strategy 1: Keyword-based search (highest priority)
const keywordResults = await this.searchByKeywords(searchTerms, branchId, includeStatuses);
keywordResults.forEach((result) => {
results.set(result.id, {
...result,
relevance_score: (result.relevance_score || 0) + 15,
});
});
// Strategy 2: FTS search (high priority)
const ftsResults = await this.searchByFTS(searchTerms, branchId, includeStatuses);
ftsResults.forEach((result) => {
const existing = results.get(result.id);
if (existing) {
existing.relevance_score += 10;
}
else {
results.set(result.id, { ...result, relevance_score: 10 });
}
});
// Strategy 3: Traditional LIKE search (fallback)
const likeResults = await this.searchByLike(searchTerms, branchId, includeStatuses);
likeResults.forEach((result) => {
const existing = results.get(result.id);
if (existing) {
existing.relevance_score += 5;
}
else {
results.set(result.id, { ...result, relevance_score: 5 });
}
});
// Convert to array and sort by relevance
return Array.from(results.values())
.filter((result) => result.relevance_score > 0)
.sort((a, b) => b.relevance_score - a.relevance_score)
.slice(0, 50);
}
async searchByKeywords(searchTerms, branchId, includeStatuses) {
const statuses = includeStatuses && includeStatuses.length > 0
? includeStatuses
: ["active"];
// Search for entities that have matching keywords
let params = [];
const keywordConditions = searchTerms
.map(() => "k.keyword LIKE ?")
.join(" OR ");
searchTerms.forEach((term) => params.push(`%${term}%`));
let whereClause = `WHERE (${keywordConditions})`;
if (branchId !== null) {
whereClause += " AND e.branch_id = ?";
params.push(branchId);
}
whereClause += ` AND e.status IN (${statuses.map(() => "?").join(",")})`;
params.push(...statuses);
const results = await this.connection.runQuery(`
SELECT DISTINCT e.*,
GROUP_CONCAT(o.content, '|') as observations,
COUNT(k.id) * MAX(k.weight) as relevance_score
FROM entities e
LEFT JOIN observations o ON e.id = o.entity_id
LEFT JOIN keywords k ON e.id = k.entity_id
${whereClause}
GROUP BY e.id
ORDER BY relevance_score DESC
`, params);
return results;
}
async searchByFTS(searchTerms, branchId, includeStatuses) {
const statuses = includeStatuses && includeStatuses.length > 0
? includeStatuses
: ["active"];
const query = searchTerms.join(" OR ");
let params = [query];
let whereClause = "";
if (branchId !== null) {
whereClause += " AND e.branch_id = ?";
params.push(branchId);
}
whereClause += ` AND e.status IN (${statuses.map(() => "?").join(",")})`;
params.push(...statuses);
try {
const results = await this.connection.runQuery(`
SELECT DISTINCT e.*,
GROUP_CONCAT(o.content, '|') as observations,
fts.rank as relevance_score
FROM entities_fts fts
JOIN entities e ON e.id = fts.rowid
LEFT JOIN observations o ON e.id = o.entity_id
WHERE entities_fts MATCH ?${whereClause}
GROUP BY e.id
ORDER BY fts.rank
`, params);
return results;
}
catch (error) {
// FTS might fail, fallback gracefully
console.warn("FTS search failed:", error);
return [];
}
}
async searchByLike(searchTerms, branchId, includeStatuses) {
// Build dynamic WHERE clause with scoring
let whereConditions = [];
let params = [];
let scoreCalculation = [];
// Exact name matches (highest priority)
searchTerms.forEach((term, index) => {
whereConditions.push(`(e.name LIKE ? OR e.entity_type LIKE ?)`);
params.push(`%${term}%`, `%${term}%`);
// Add scoring for relevance
scoreCalculation.push(`
CASE
WHEN e.name LIKE ? THEN 10
WHEN e.entity_type LIKE ? THEN 8
ELSE 0
END
`);
params.push(`%${term}%`, `%${term}%`);
});
// Content matches (lower priority but included)
const contentConditions = searchTerms
.map(() => `o.content LIKE ?`)
.join(" OR ");
if (contentConditions) {
whereConditions.push(`(${contentConditions})`);
searchTerms.forEach((term) => {
params.push(`%${term}%`);
scoreCalculation.push(`CASE WHEN o.content LIKE ? THEN 3 ELSE 0 END`);
params.push(`%${term}%`);
});
}
let whereClause = whereConditions.length > 0
? `WHERE (${whereConditions.join(" OR ")})`
: "WHERE 1=1";
if (branchId !== null) {
whereClause += " AND e.branch_id = ?";
params.push(branchId);
}
// Add status filtering
const statuses = includeStatuses && includeStatuses.length > 0
? includeStatuses
: ["active"];
whereClause += ` AND e.status IN (${statuses.map(() => "?").join(",")})`;
params.push(...statuses);
// Build the score calculation
const scoreExpression = scoreCalculation.length > 0
? `(${scoreCalculation.join(" + ")}) as relevance_score`
: "0 as relevance_score";
const results = await this.connection.runQuery(`
SELECT DISTINCT e.*,
GROUP_CONCAT(o.content, '|') as observations,
${scoreExpression}
FROM entities e
LEFT JOIN observations o ON e.id = o.entity_id
${whereClause}
GROUP BY e.id
HAVING relevance_score > 0
ORDER BY relevance_score DESC, e.last_accessed DESC
`, params);
return results;
}
}