sqlite-entity-operations.js•16.3 kB
import { logger } from "../logger.js";
/**
* SQLite Entity Operations
* Handles CRUD operations for entities and observations
*/
export class SQLiteEntityOperations {
connection;
constructor(connection) {
this.connection = connection;
}
async createEntities(entities, branchName) {
const branchId = await this.connection.getBranchId(branchName);
const created = [];
for (const entity of entities) {
try {
const createdEntity = await this.createSingleEntity(entity, branchId);
created.push(createdEntity);
}
catch (error) {
logger.error(`Failed to create entity "${entity.name}":`, error);
// Continue with other entities
}
}
return created;
}
async updateEntity(entity, branchName) {
const branchId = await this.connection.getBranchId(branchName);
// Find the existing entity
const existingEntity = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [entity.name, branchId]);
if (!existingEntity) {
throw new Error(`Entity "${entity.name}" not found in branch ${branchName || "main"}`);
}
// Update the entity
await this.connection.execQuery(`UPDATE entities
SET entity_type = ?, status = ?, status_reason = ?, updated_at = ?
WHERE id = ?`, [
entity.entityType,
entity.status || "active",
entity.statusReason || null,
new Date().toISOString(),
existingEntity.id,
]);
// Update observations - delete old ones and insert new ones
await this.connection.execQuery("DELETE FROM observations WHERE entity_id = ?", [existingEntity.id]);
if (entity.observations && entity.observations.length > 0) {
for (let i = 0; i < entity.observations.length; i++) {
await this.connection.execQuery(`INSERT INTO observations (entity_id, content, optimized_content, sequence_order)
VALUES (?, ?, ?, ?)`, [existingEntity.id, entity.observations[i], entity.observations[i], i]);
}
}
// Update cross-references - delete old ones and create new ones
await this.connection.execQuery("DELETE FROM cross_references WHERE from_entity_id = ?", [existingEntity.id]);
const crossRefs = entity.crossRefs || [];
if (crossRefs.length > 0) {
const branchName = await this.connection.getBranchName(branchId);
for (const crossRef of crossRefs) {
try {
await this.createCrossReference(entity.name, crossRef.memoryBranch, crossRef.entityNames, branchName);
}
catch (error) {
logger.error(`Failed to update cross-reference for entity "${entity.name}":`, error);
}
}
}
// Retrieve cross-references for the updated entity
const entityCrossRefs = await this.getCrossReferences(entity.name, branchId);
return {
...entity,
lastUpdated: new Date().toISOString(),
crossRefs: entityCrossRefs,
};
}
async deleteEntities(entityNames, branchName) {
if (!entityNames || entityNames.length === 0) {
return;
}
const branchId = await this.connection.getBranchId(branchName);
for (const entityName of entityNames) {
try {
// Get entity ID first
const entity = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [entityName, branchId]);
if (!entity) {
logger.warn(`Entity "${entityName}" not found in branch ${branchName || "main"}`);
continue;
}
// Delete observations first (foreign key constraints)
await this.connection.execQuery("DELETE FROM observations WHERE entity_id = ?", [entity.id]);
// Delete relations involving this entity
await this.connection.execQuery("DELETE FROM relations WHERE from_entity_id = ? OR to_entity_id = ?", [entity.id, entity.id]);
// Delete keywords
await this.connection.execQuery("DELETE FROM keywords WHERE entity_id = ?", [entity.id]);
// Delete cross references
await this.connection.execQuery("DELETE FROM cross_references WHERE from_entity_id = ?", [entity.id]);
// Finally delete the entity (FTS trigger will handle FTS cleanup)
await this.connection.execQuery("DELETE FROM entities WHERE id = ?", [
entity.id,
]);
logger.info(`Deleted entity "${entityName}" and all related data`);
}
catch (error) {
logger.error(`Failed to delete entity "${entityName}":`, error);
throw error;
}
}
}
async findEntityByName(name, branchName) {
const branchId = branchName
? await this.connection.getBranchId(branchName)
: null;
let whereClause = "WHERE e.name = ?";
let params = [name];
if (branchId) {
whereClause += " AND e.branch_id = ?";
params.push(branchId.toString());
}
const results = await this.connection.runQuery(`
SELECT DISTINCT e.*, GROUP_CONCAT(o.content, '|') as observations
FROM entities e
LEFT JOIN observations o ON e.id = o.entity_id
${whereClause}
GROUP BY e.id
`, params);
if (results.length === 0) {
return null;
}
const entities = await this.convertRowsToEntities(results);
return entities[0] || null;
}
async createSingleEntity(entity, branchId) {
// Validate and sanitize entity data
const validName = (entity.name || "").toString().trim() || "Unnamed Entity";
const validEntityType = (entity.entityType || "").toString().trim() || "Unknown";
const validObservations = (entity.observations || []).filter((obs) => obs && obs.toString().trim());
// Check if entity already exists
const existing = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [validName, branchId]);
if (existing) {
throw new Error(`Entity "${validName}" already exists`);
}
const originalContent = JSON.stringify({
name: validName,
entityType: validEntityType,
observations: validObservations,
});
// Insert entity
await this.connection.execQuery(`
INSERT INTO entities (name, entity_type, branch_id, status, status_reason, original_content, optimized_content, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
`, [
validName,
validEntityType,
branchId,
entity.status || "active",
entity.statusReason || null,
originalContent,
originalContent, // Will be optimized later
new Date().toISOString(),
]);
const entityRow = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [validName, branchId]);
// Insert observations
if (validObservations.length > 0) {
for (let i = 0; i < validObservations.length; i++) {
await this.connection.execQuery(`
INSERT INTO observations (entity_id, content, optimized_content, sequence_order)
VALUES (?, ?, ?, ?)
`, [entityRow.id, validObservations[i], validObservations[i], i]);
}
}
// Store keywords if provided
if (entity._keywordData?.keywords) {
const keywords = entity._keywordData.keywords;
for (let i = 0; i < keywords.length; i++) {
const keyword = keywords[i];
if (keyword && keyword.trim()) {
await this.connection.execQuery(`INSERT INTO keywords (keyword, entity_id, weight, context)
VALUES (?, ?, ?, ?)`, [keyword.trim().toLowerCase(), entityRow.id, 1.0, "entity_content"]);
}
}
logger.info(`Stored ${keywords.length} keywords for entity "${validName}"`);
}
// Process cross-references if provided
const crossRefs = entity.crossRefs || [];
if (crossRefs.length > 0) {
const branchName = await this.connection.getBranchName(branchId);
for (const crossRef of crossRefs) {
try {
await this.createCrossReference(validName, crossRef.memoryBranch, crossRef.entityNames, branchName);
}
catch (error) {
logger.error(`Failed to create cross-reference for entity "${validName}":`, error);
}
}
}
// Retrieve cross-references for the created entity
const entityCrossRefs = await this.getCrossReferences(validName, branchId);
return {
name: validName,
entityType: validEntityType,
observations: validObservations,
status: entity.status || "active",
statusReason: entity.statusReason,
lastUpdated: new Date().toISOString(),
crossRefs: entityCrossRefs,
};
}
async addObservations(observations, branchName) {
const branchId = await this.connection.getBranchId(branchName);
const results = [];
for (const obs of observations) {
try {
// Find the entity
const entity = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [obs.entityName, branchId]);
if (!entity) {
logger.warn(`Entity "${obs.entityName}" not found in branch ${branchName || "main"}`);
continue;
}
// Get current max sequence order
const maxSeq = await this.connection.getQuery("SELECT COALESCE(MAX(sequence_order), -1) as max_seq FROM observations WHERE entity_id = ?", [entity.id]);
const startSeq = (maxSeq?.max_seq || -1) + 1;
const addedObservations = [];
// Add each new observation
for (let i = 0; i < obs.contents.length; i++) {
const content = obs.contents[i];
if (content && content.trim()) {
await this.connection.execQuery(`INSERT INTO observations (entity_id, content, optimized_content, sequence_order)
VALUES (?, ?, ?, ?)`, [entity.id, content, content, startSeq + i]);
addedObservations.push(content);
}
}
// Update entity timestamp
await this.connection.execQuery("UPDATE entities SET updated_at = ? WHERE id = ?", [new Date().toISOString(), entity.id]);
results.push({
entityName: obs.entityName,
addedObservations,
});
logger.info(`Added ${addedObservations.length} observations to "${obs.entityName}"`);
}
catch (error) {
logger.error(`Failed to add observations to "${obs.entityName}":`, error);
}
}
return results;
}
async deleteObservations(deletions, branchName) {
const branchId = await this.connection.getBranchId(branchName);
for (const deletion of deletions) {
try {
// Find the entity
const entity = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [deletion.entityName, branchId]);
if (!entity) {
logger.warn(`Entity "${deletion.entityName}" not found in branch ${branchName || "main"}`);
continue;
}
// Delete specific observations
for (const obsContent of deletion.observations) {
await this.connection.execQuery("DELETE FROM observations WHERE entity_id = ? AND content = ?", [entity.id, obsContent]);
}
// Update entity timestamp
await this.connection.execQuery("UPDATE entities SET updated_at = ? WHERE id = ?", [new Date().toISOString(), entity.id]);
logger.info(`Deleted ${deletion.observations.length} observations from "${deletion.entityName}"`);
}
catch (error) {
logger.error(`Failed to delete observations from "${deletion.entityName}":`, error);
}
}
}
async getCrossReferences(entityName, branchId) {
const crossRefs = await this.connection.runQuery(`
SELECT cr.target_branch_id, cr.target_entity_name, mb.name as branch_name
FROM cross_references cr
JOIN memory_branches mb ON cr.target_branch_id = mb.id
JOIN entities e ON cr.from_entity_id = e.id
WHERE e.name = ? AND e.branch_id = ?
ORDER BY cr.target_branch_id
`, [entityName, branchId]);
// Group by target branch
const groupedRefs = {};
const branchNames = {};
for (const ref of crossRefs) {
const branchId = ref.target_branch_id.toString();
if (!groupedRefs[branchId]) {
groupedRefs[branchId] = [];
branchNames[branchId] = ref.branch_name;
}
groupedRefs[branchId].push(ref.target_entity_name);
}
// Convert to expected format
return Object.keys(groupedRefs).map((branchId) => ({
memoryBranch: branchNames[branchId],
entityNames: groupedRefs[branchId],
}));
}
async createCrossReference(entityName, targetBranch, targetEntityNames, sourceBranch) {
const sourceBranchId = await this.connection.getBranchId(sourceBranch);
const targetBranchId = await this.connection.getBranchId(targetBranch);
// Find the source entity
const sourceEntity = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [entityName, sourceBranchId]);
if (!sourceEntity) {
throw new Error(`Entity "${entityName}" not found in branch ${sourceBranch || "main"}`);
}
// Create cross-references for each target entity
for (const targetEntityName of targetEntityNames) {
try {
// Verify target entity exists
const targetEntity = await this.connection.getQuery("SELECT id FROM entities WHERE name = ? AND branch_id = ?", [targetEntityName, targetBranchId]);
if (!targetEntity) {
logger.warn(`Target entity "${targetEntityName}" not found in branch "${targetBranch}"`);
continue;
}
// Insert cross-reference (ignore duplicates)
await this.connection.execQuery(`INSERT OR IGNORE INTO cross_references (from_entity_id, target_branch_id, target_entity_name)
VALUES (?, ?, ?)`, [sourceEntity.id, targetBranchId, targetEntityName]);
logger.info(`Created cross-reference: "${entityName}" -> "${targetEntityName}" (${targetBranch})`);
}
catch (error) {
logger.error(`Failed to create cross-reference to "${targetEntityName}":`, error);
}
}
}
async convertRowsToEntities(rows) {
const entities = [];
for (const row of rows) {
const crossRefs = await this.getCrossReferences(row.name, row.branch_id);
entities.push({
name: row.name,
entityType: row.entity_type,
observations: row.observations ? row.observations.split("|") : [],
status: row.status,
statusReason: row.status_reason,
lastUpdated: row.updated_at,
crossRefs: crossRefs,
});
}
return entities;
}
}