sqlite-connection.js•8 kB
import Database from "better-sqlite3";
import { promises as fs } from "fs";
import path from "path";
/**
* SQLite Connection and Schema Management
* Handles database initialization, schema creation, and query operations
*/
export class SQLiteConnection {
db = null;
dbPath;
branchesPath;
basePath;
constructor(basePath) {
this.basePath = basePath;
this.branchesPath = path.join(basePath, ".memory");
this.dbPath = path.join(this.branchesPath, "memory.db");
}
async initialize() {
// Ensure directories exist
await fs.mkdir(this.basePath, { recursive: true });
await fs.mkdir(this.branchesPath, { recursive: true });
// Create SQLite database
this.db = new Database(this.dbPath);
// Enable optimizations
this.db.pragma("foreign_keys = ON");
this.db.pragma("journal_mode = WAL");
this.db.pragma("synchronous = NORMAL");
this.db.pragma("cache_size = 10000");
// Create schema
this.createSchema();
// Ensure main branch exists
this.execQuery("INSERT OR IGNORE INTO memory_branches (id, name, purpose) VALUES (1, 'main', 'Main project memory - core entities, business logic, and system architecture')");
}
createSchema() {
const queries = [
// Memory branches
`CREATE TABLE IF NOT EXISTS memory_branches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
purpose TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)`,
// Entities with optimization fields
`CREATE TABLE IF NOT EXISTS entities (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
entity_type TEXT NOT NULL,
branch_id INTEGER DEFAULT 1,
status TEXT DEFAULT 'active',
status_reason TEXT,
original_content TEXT NOT NULL,
optimized_content TEXT,
token_count INTEGER DEFAULT 0,
compression_ratio REAL DEFAULT 1.0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
last_accessed DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (branch_id) REFERENCES memory_branches(id),
UNIQUE(name, branch_id)
)`,
// Observations (normalized)
`CREATE TABLE IF NOT EXISTS observations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entity_id INTEGER NOT NULL,
content TEXT NOT NULL,
optimized_content TEXT,
sequence_order INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
)`,
// Relations
`CREATE TABLE IF NOT EXISTS relations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
from_entity_id INTEGER NOT NULL,
to_entity_id INTEGER NOT NULL,
relation_type TEXT NOT NULL,
branch_id INTEGER DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (from_entity_id) REFERENCES entities(id) ON DELETE CASCADE,
FOREIGN KEY (to_entity_id) REFERENCES entities(id) ON DELETE CASCADE,
FOREIGN KEY (branch_id) REFERENCES memory_branches(id),
UNIQUE(from_entity_id, to_entity_id, relation_type)
)`,
// Keywords for fast search
`CREATE TABLE IF NOT EXISTS keywords (
id INTEGER PRIMARY KEY AUTOINCREMENT,
keyword TEXT NOT NULL,
entity_id INTEGER NOT NULL,
weight REAL DEFAULT 1.0,
context TEXT,
FOREIGN KEY (entity_id) REFERENCES entities(id) ON DELETE CASCADE
)`,
// Cross-references
`CREATE TABLE IF NOT EXISTS cross_references (
id INTEGER PRIMARY KEY AUTOINCREMENT,
from_entity_id INTEGER NOT NULL,
target_branch_id INTEGER NOT NULL,
target_entity_name TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (from_entity_id) REFERENCES entities(id) ON DELETE CASCADE,
FOREIGN KEY (target_branch_id) REFERENCES memory_branches(id)
)`,
// Full-text search
`CREATE VIRTUAL TABLE IF NOT EXISTS entities_fts USING fts5(
name, entity_type, optimized_content, content='entities', content_rowid='id'
)`,
// FTS triggers
`CREATE TRIGGER IF NOT EXISTS entities_fts_insert AFTER INSERT ON entities BEGIN
INSERT INTO entities_fts(rowid, name, entity_type, optimized_content)
VALUES (new.id, new.name, new.entity_type, new.optimized_content);
END`,
`CREATE TRIGGER IF NOT EXISTS entities_fts_delete AFTER DELETE ON entities BEGIN
DELETE FROM entities_fts WHERE rowid = old.id;
END`,
`CREATE TRIGGER IF NOT EXISTS entities_fts_update AFTER UPDATE ON entities BEGIN
DELETE FROM entities_fts WHERE rowid = old.id;
INSERT INTO entities_fts(rowid, name, entity_type, optimized_content)
VALUES (new.id, new.name, new.entity_type, new.optimized_content);
END`,
];
const transaction = this.db.transaction((queriesToRun) => {
for (const query of queriesToRun) {
this.db.prepare(query).run();
}
});
transaction(queries);
// Create indexes
const indexes = [
"CREATE INDEX IF NOT EXISTS idx_entities_name ON entities(name)",
"CREATE INDEX IF NOT EXISTS idx_entities_branch ON entities(branch_id)",
"CREATE INDEX IF NOT EXISTS idx_entities_status ON entities(status)",
"CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(entity_type)",
"CREATE INDEX IF NOT EXISTS idx_entities_accessed ON entities(last_accessed)",
"CREATE INDEX IF NOT EXISTS idx_keywords_keyword ON keywords(keyword)",
"CREATE INDEX IF NOT EXISTS idx_keywords_entity ON keywords(entity_id)",
"CREATE INDEX IF NOT EXISTS idx_relations_from ON relations(from_entity_id)",
"CREATE INDEX IF NOT EXISTS idx_relations_to ON relations(to_entity_id)",
"CREATE INDEX IF NOT EXISTS idx_relations_type ON relations(relation_type)",
];
const indexTransaction = this.db.transaction((indexesToRun) => {
for (const index of indexesToRun) {
this.db.prepare(index).run();
}
});
indexTransaction(indexes);
}
execQuery(query, params = []) {
if (!this.db) {
throw new Error("Database not initialized");
}
this.db.prepare(query).run(params);
}
runQuery(query, params = []) {
if (!this.db) {
throw new Error("Database not initialized");
}
return this.db.prepare(query).all(params);
}
getQuery(query, params = []) {
if (!this.db) {
throw new Error("Database not initialized");
}
return this.db.prepare(query).get(params);
}
close() {
if (this.db) {
this.db.close();
}
}
getBranchId(branchName) {
const name = branchName || "main";
const branch = this.getQuery("SELECT id FROM memory_branches WHERE name = ?", [name]);
if (branch) {
return branch.id;
}
// Create branch if it doesn't exist
this.execQuery("INSERT INTO memory_branches (name, purpose) VALUES (?, ?)", [name, `Auto-created branch: ${name}`]);
const newBranch = this.getQuery("SELECT id FROM memory_branches WHERE name = ?", [name]);
return newBranch.id;
}
getBranchName(branchId) {
const branch = this.getQuery("SELECT name FROM memory_branches WHERE id = ?", [branchId]);
if (!branch) {
throw new Error(`Branch with ID ${branchId} not found`);
}
return branch.name;
}
}