database-utils.jsā¢43.3 kB
import sqlite3 from 'sqlite3';
import path from 'path';
import { mkdirSync, existsSync, readFileSync } from 'fs';
// NEW: Connection pool and timeout improvements
let connectionPool = [];
const MAX_POOL_SIZE = 5;
const WARMUP_TIMEOUT = 30000; // 30 seconds
const RETRY_ATTEMPTS = 3;
const RETRY_DELAY_BASE = 1000; // 1 second base delay
// Global warmer instance - shared across all database instances
let globalWarmer = null;
let globalDB = null;
let globalHeartbeat = null; // Track active heartbeat
let lastHeartbeatTime = null; // Track when last heartbeat occurred
const HEARTBEAT_TIMEOUT = 60000; // 60 seconds - heartbeat must be within this time
// NEW: Connection pool management
class ConnectionPool {
constructor(dbPath, maxSize = MAX_POOL_SIZE) {
this.dbPath = dbPath;
this.maxSize = maxSize;
this.connections = [];
this.waitingQueue = [];
}
async getConnection() {
return new Promise((resolve, reject) => {
// Try to get existing connection
if (this.connections.length > 0) {
const connection = this.connections.pop();
resolve(connection);
return;
}
// Create new connection if under limit
if (this.connections.length < this.maxSize) {
try {
const db = new sqlite3.Database(this.dbPath);
resolve(db);
} catch (error) {
reject(error);
}
return;
}
// Add to waiting queue
this.waitingQueue.push({ resolve, reject });
});
}
releaseConnection(db) {
// Check if someone is waiting
if (this.waitingQueue.length > 0) {
const { resolve } = this.waitingQueue.shift();
resolve(db);
} else {
this.connections.push(db);
}
}
closeAll() {
[...this.connections, ...this.waitingQueue.map(w => w.connection)].forEach(db => {
if (db && typeof db.close === 'function') {
db.close();
}
});
this.connections = [];
this.waitingQueue = [];
}
}
// NEW: Retry utility with exponential backoff
async function retryOperation(operation, maxAttempts = RETRY_ATTEMPTS, baseDelay = RETRY_DELAY_BASE) {
let lastError;
for (let attempt = 1; attempt <= maxAttempts; attempt++) {
try {
return await operation();
} catch (error) {
lastError = error;
console.warn(`ā ļø Attempt ${attempt}/${maxAttempts} failed: ${error.message}`);
if (attempt < maxAttempts) {
const delay = baseDelay * Math.pow(2, attempt - 1); // Exponential backoff
console.log(`š Retrying in ${delay}ms...`);
await new Promise(resolve => setTimeout(resolve, delay));
}
}
}
throw lastError;
}
// Embedded database warmer functionality - IMPROVED
class EmbeddedDatabaseWarmer {
constructor() {
this.warmupQueries = [
'faith',
'love',
'prayer',
'hope',
'charity'
];
this.isWarm = false;
this.lastWarmup = null;
this.warmupInterval = 5 * 60 * 1000; // 5 minutes
this.warmupPromise = null;
}
async initialize(db) {
console.log('š„ Initializing embedded database warmer...');
// Prevent multiple concurrent warmups
if (this.warmupPromise) {
return this.warmupPromise;
}
this.warmupPromise = this.performWarmupWithTimeout(db);
try {
await this.warmupPromise;
// Set up periodic warmup to keep database hot
setInterval(() => {
this.maintenanceWarmup(db);
}, this.warmupInterval);
console.log('ā
Embedded database warmer initialized and ready');
} catch (error) {
this.warmupPromise = null; // Reset on failure
throw error;
}
return this.warmupPromise;
}
async performWarmupWithTimeout(db) {
return new Promise(async (resolve, reject) => {
const timeoutId = setTimeout(() => {
reject(new Error(`Database warmup timeout after ${WARMUP_TIMEOUT}ms`));
}, WARMUP_TIMEOUT);
try {
const result = await this.warmupDatabase(db);
clearTimeout(timeoutId);
resolve(result);
} catch (error) {
clearTimeout(timeoutId);
reject(error);
}
});
}
async warmupDatabase(db) {
console.log('š„ Warming up database (cold start)...');
const startTime = Date.now();
try {
// Warmup with common search queries
for (const query of this.warmupQueries) {
await this.performWarmupQuery(db, query);
}
// Additional warmup: Get stats to load book data
await this.getStats(db);
const warmupTime = Date.now() - startTime;
console.log(`ā
Database warmup completed in ${warmupTime}ms`);
this.isWarm = true;
this.lastWarmup = new Date();
return warmupTime;
} catch (error) {
console.error('ā Database warmup failed:', error.message);
throw error;
}
}
async performWarmupQuery(db, query) {
return new Promise((resolve, reject) => {
// Try FTS first, fallback to LIKE if FTS table doesn't exist
const ftsSearchStmt = db.prepare(`
SELECT
fts.para_id,
fts.book_id,
p.content_plain as content,
p.element_type,
b.code as pub_code,
b.title as pub_name,
b.author as book_author,
b.pub_year
FROM paragraphs_fts fts
JOIN paragraphs p ON fts.para_id = p.para_id
JOIN books b ON fts.book_id = b.book_id
WHERE paragraphs_fts MATCH ?
AND (b.author = 'Ellen Gould White' OR b.author = 'Ellen G. White')
AND p.element_type = 'p'
ORDER BY rank
LIMIT 5
`);
ftsSearchStmt.all([query], (err, results) => {
ftsSearchStmt.finalize();
if (err) {
// FTS table doesn't exist, try simple LIKE query instead
const likeStmt = db.prepare(`
SELECT
p.para_id,
p.book_id,
p.content_plain as content,
p.element_type,
b.code as pub_code,
b.title as pub_name,
b.author as book_author,
b.pub_year
FROM paragraphs p
JOIN books b ON p.book_id = b.book_id
WHERE p.content_plain LIKE ?
AND (b.author = 'Ellen Gould White' OR b.author = 'Ellen G. White')
AND p.element_type = 'p'
ORDER BY RANDOM()
LIMIT 5
`);
likeStmt.all([`%${query}%`], (likeErr, likeResults) => {
likeStmt.finalize();
// Don't fail warmup for individual queries
resolve();
});
return;
}
resolve();
});
});
}
async getStats(db) {
return new Promise((resolve, reject) => {
db.get('SELECT COUNT(*) as count FROM books', [], (err, row) => {
if (err) reject(err);
else resolve(row?.count || 0);
});
});
}
async maintenanceWarmup(db) {
if (!this.isWarm) {
console.log('š„ Performing maintenance warmup...');
return await this.warmupDatabase(db);
}
// Light maintenance - just one quick query
try {
const startTime = Date.now();
await this.performWarmupQuery(db, 'faith');
const duration = Date.now() - startTime;
console.log(`š„ Maintenance warmup: ${duration}ms`);
this.lastWarmup = new Date();
} catch (error) {
console.error('ā Maintenance warmup failed:', error.message);
this.isWarm = false;
}
}
getStatus() {
return {
isWarm: this.isWarm,
lastWarmup: this.lastWarmup,
warmupInterval: this.warmupInterval
};
}
}
// NEW: Persistent heartbeat storage
class PersistentHeartbeat {
constructor() {
this.heartbeatFile = path.join(process.cwd(), 'apps', 'local-server', 'data', 'heartbeat.json');
}
async saveHeartbeat(data) {
try {
const heartbeatData = {
...data,
timestamp: new Date().toISOString(),
pid: process.pid
};
const fs = await import('fs');
await fs.promises.writeFile(this.heartbeatFile, JSON.stringify(heartbeatData, null, 2));
return true;
} catch (error) {
console.error('ā Failed to save heartbeat:', error.message);
return false;
}
}
async loadHeartbeat() {
try {
const fs = await import('fs');
const data = await fs.promises.readFile(this.heartbeatFile, 'utf8');
const heartbeat = JSON.parse(data);
// Check if heartbeat is still valid (within timeout)
const lastTime = new Date(heartbeat.timestamp);
const now = new Date();
const age = now - lastTime;
if (age < HEARTBEAT_TIMEOUT) {
return {
...heartbeat,
isValid: true,
age: age
};
}
return { isValid: false };
} catch (error) {
return { isValid: false };
}
}
async clearHeartbeat() {
try {
const fs = await import('fs');
await fs.promises.unlink(this.heartbeatFile);
return true;
} catch (error) {
// File might not exist, that's OK
return true;
}
}
}
// Global instances
let persistentHeartbeat = null;
let connectionPoolInstance = null;
export class SimpleEGWDatabase {
constructor(dbPath = './data/egw-writings.db', requireWarming = true) {
console.error('š Initializing database connection...');
// Initialize persistent heartbeat
if (!persistentHeartbeat) {
persistentHeartbeat = new PersistentHeartbeat();
}
// Initialize connection pool
if (!connectionPoolInstance && dbPath) {
connectionPoolInstance = new ConnectionPool(dbPath);
}
// IMPROVED: Initialize warmer with timeout and retry
if (requireWarming && !globalWarmer) {
console.error('š„ Creating new database connection for warming...');
// Ensure database directory exists
const dbDir = path.dirname(dbPath);
if (!existsSync(dbDir)) {
mkdirSync(dbDir, { recursive: true });
}
// Create fresh connection for warming
const freshDB = new sqlite3.Database(dbPath);
// Initialize warmer with timeout and retry
globalWarmer = new EmbeddedDatabaseWarmer();
retryOperation(() => globalWarmer.initialize(freshDB))
.then(() => {
globalDB = freshDB;
console.error('ā
Database warmed and ready for shared use');
})
.catch(error => {
console.error('ā Failed to warm database after retries:', error.message);
// Don't throw error, allow system to work with cold database
globalDB = freshDB;
});
// Store warm connection for this instance
this.db = freshDB;
} else if (requireWarming && globalWarmer) {
// Use existing warm connection
this.db = globalDB;
console.error('ā
Using existing warm database connection');
} else {
// Create fresh connection without warming (for testing only)
const dbDir = path.dirname(dbPath);
if (!existsSync(dbDir)) {
mkdirSync(dbDir, { recursive: true });
}
this.db = new sqlite3.Database(dbPath);
console.error('ā
Database connection ready (no warming)');
}
}
// IMPROVED: Heartbeat validation with persistent storage and retry
async validateHeartbeat(toolName = 'unknown-tool') {
try {
// First check persistent heartbeat
const heartbeatData = await persistentHeartbeat.loadHeartbeat();
if (heartbeatData.isValid) {
console.log(`š Persistent heartbeat valid for "${toolName}" (${Math.round(heartbeatData.age / 1000)}s old)`);
lastHeartbeatTime = new Date(heartbeatData.timestamp);
return true;
}
// No valid persistent heartbeat, try auto-start
console.log(`š Tool "${toolName}" needs heartbeat - attempting auto-start...`);
return await retryOperation(async () => {
const { AutoHeartbeatStarter } = await import('./auto-heartbeat-starter.cjs');
const autoStartResult = await AutoHeartbeatStarter.validateWithAutoStart(toolName);
if (autoStartResult.success) {
// Save persistent heartbeat
await persistentHeartbeat.saveHeartbeat({
toolName,
autoStarted: autoStartResult.autoStarted,
pid: autoStartResult.pid
});
lastHeartbeatTime = new Date();
return true;
} else {
throw new Error(`Auto-start failed: ${autoStartResult.message}`);
}
});
} catch (error) {
console.error('ā Heartbeat validation failed:', error.message);
// Fallback: Save temporary heartbeat to allow tools to work
await persistentHeartbeat.saveHeartbeat({
toolName,
fallback: true,
error: error.message
});
lastHeartbeatTime = new Date();
return true; // Allow tools to work even with heartbeat issues
}
}
// HEARTBEAT REGISTRATION: Register active heartbeat
static registerHeartbeat(heartbeatInstance) {
globalHeartbeat = heartbeatInstance;
lastHeartbeatTime = new Date();
// Save to persistent storage
if (persistentHeartbeat) {
persistentHeartbeat.saveHeartbeat({
registered: true,
pid: process.pid
});
}
console.log('š Heartbeat registered - database tools now active');
}
// NEW: Update heartbeat time (for external heartbeat updates)
static async updateHeartbeatTime() {
lastHeartbeatTime = new Date();
if (persistentHeartbeat) {
await persistentHeartbeat.saveHeartbeat({
updated: true,
pid: process.pid
});
}
}
// NEW: Health check for system monitoring
static async performHealthCheck() {
const health = {
timestamp: new Date().toISOString(),
database: {
isWarm: globalWarmer ? globalWarmer.isWarm : false,
lastWarmup: globalWarmer ? globalWarmer.lastWarmup : null
},
heartbeat: {
isActive: !!globalHeartbeat,
lastHeartbeat: lastHeartbeatTime,
persistent: persistentHeartbeat ? await persistentHeartbeat.loadHeartbeat() : null
},
connectionPool: {
size: connectionPoolInstance ? connectionPoolInstance.connections.length : 0,
waiting: connectionPoolInstance ? connectionPoolInstance.waitingQueue.length : 0
}
};
console.error('š„ System health check:', JSON.stringify(health, null, 2));
return health;
}
// Search database with retry
async search(query, limit = 20, offset = 0, toolName = 'search-function') {
await this.validateHeartbeat(toolName);
return retryOperation(async () => {
return new Promise((resolve, reject) => {
const stmt = this.db.prepare(`
SELECT
p.para_id,
p.book_id,
b.code as pub_code,
b.title as pub_name,
b.author,
p.refcode_long,
p.refcode_short,
b.pub_year,
p.content_plain as snippet,
b.folder_color_group as "group"
FROM paragraphs p
JOIN books b ON p.book_id = b.book_id
WHERE p.content_plain LIKE ?
ORDER BY p.puborder
LIMIT ? OFFSET ?
`);
const searchTerm = `%${query}%`;
stmt.all([searchTerm, limit, offset], (err, rows) => {
if (err) {
console.error('ā Search error:', err.message);
reject(err);
return;
}
const results = rows.map((row, index) => ({
index: offset + index,
para_id: row.para_id,
book_id: row.book_id,
pub_code: row.pub_code,
pub_name: row.pub_name,
author: row.author,
refcode_long: row.refcode_long,
refcode_short: row.refcode_short,
pub_year: row.pub_year,
snippet: row.snippet,
weight: 1,
group: row.group
}));
console.error(`š Found ${results.length} results for query: "${query}"`);
resolve(results);
});
stmt.finalize();
});
});
}
// Get book information with retry
async getBook(bookId, toolName = 'getBook-function') {
await this.validateHeartbeat(toolName);
return retryOperation(async () => {
return new Promise((resolve, reject) => {
this.db.get('SELECT * FROM books WHERE book_id = ?', [bookId], (err, row) => {
if (err) {
console.error('ā Get book error:', err.message);
reject(err);
return;
}
console.error(`š Retrieved book: ${row?.title || 'Unknown'}`);
resolve(row);
});
});
});
}
// Get paragraphs from a book with retry
async getParagraphs(bookId, limit = 3, offset = 0, toolName = 'getParagraphs-function') {
await this.validateHeartbeat(toolName);
return retryOperation(async () => {
return new Promise((resolve, reject) => {
let query = 'SELECT * FROM paragraphs WHERE book_id = ? ORDER BY puborder';
const params = [bookId];
if (limit) {
query += ' LIMIT ?';
params.push(limit);
if (offset) {
query += ' OFFSET ?';
params.push(offset);
}
}
this.db.all(query, params, (err, rows) => {
if (err) {
console.error('ā Get paragraphs error:', err.message);
reject(err);
return;
}
console.error(`š Retrieved ${rows.length} paragraphs from book ${bookId}`);
resolve(rows);
});
});
});
}
// List books with retry
async getBooks(limit = 10, toolName = 'getBooks-function') {
await this.validateHeartbeat(toolName);
return retryOperation(async () => {
return new Promise((resolve, reject) => {
let query = 'SELECT * FROM books ORDER BY title';
const params = [];
if (limit) {
query += ' LIMIT ?';
params.push(limit);
}
this.db.all(query, params, (err, rows) => {
if (err) {
console.error('ā Get books error:', err.message);
reject(err);
return;
}
console.error(`š Retrieved ${rows.length} books`);
resolve(rows);
});
});
});
}
// Get database statistics with retry
async getStats(toolName = 'getStats-function') {
await this.validateHeartbeat(toolName);
// First try to get enhanced stats from parsing report
const reportStats = await this.getEnhancedStatsFromReport();
if (reportStats) {
return reportStats;
}
// Fallback to database-only stats
return retryOperation(async () => {
return new Promise((resolve, reject) => {
Promise.all([
// Get total books count
new Promise((resolveBooks, rejectBooks) => {
this.db.get('SELECT COUNT(*) as count FROM books', [], (err, row) => {
if (err) rejectBooks(err);
else resolveBooks(row.count);
});
}),
// Get total paragraphs count
new Promise((resolveParas, rejectParas) => {
this.db.get('SELECT COUNT(*) as count FROM paragraphs', [], (err, row) => {
if (err) rejectParas(err);
else resolveParas(row.count);
});
}),
// Get languages count
new Promise((resolveLangs, rejectLangs) => {
this.db.get('SELECT COUNT(*) as count FROM languages', [], (err, row) => {
if (err) rejectLangs(err);
else resolveLangs(row.count);
});
}),
// Get completed parsed books (with content)
new Promise((resolveDownloaded, rejectDownloaded) => {
this.db.get('SELECT COUNT(*) as count FROM books WHERE downloaded_at IS NOT NULL', [], (err, row) => {
if (err) rejectDownloaded(err);
else resolveDownloaded(row.count);
});
}),
// Get detailed completion stats by book
new Promise((resolveDetails, rejectDetails) => {
this.db.all(`
SELECT
b.book_id,
b.code,
b.title,
b.author,
b.downloaded_at,
COUNT(p.para_id) as paragraph_count
FROM books b
LEFT JOIN paragraphs p ON b.book_id = p.book_id
GROUP BY b.book_id, b.code, b.title, b.author, b.downloaded_at
ORDER BY b.downloaded_at DESC, paragraph_count DESC
`, [], (err, rows) => {
if (err) rejectDetails(err);
else resolveDetails(rows);
});
}),
// Get completion percentage by category
new Promise((resolveCategories, rejectCategories) => {
this.db.all(`
SELECT
category,
subcategory,
COUNT(*) as total_books,
SUM(CASE WHEN downloaded_at IS NOT NULL THEN 1 ELSE 0 END) as completed_books,
ROUND(
(SUM(CASE WHEN downloaded_at IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)),
2
) as completion_percentage
FROM books
GROUP BY category, subcategory
ORDER BY completion_percentage DESC, category, subcategory
`, [], (err, rows) => {
if (err) rejectCategories(err);
else resolveCategories(rows);
});
})
]).then(([books, paragraphs, languages, downloadedBooks, bookDetails, categoryStats]) => {
const stats = {
books,
paragraphs,
languages,
downloadedBooks,
completion_percentage: ((downloadedBooks / books) * 100).toFixed(1),
bookDetails,
categoryStats
};
console.error('š Enhanced Database stats:', stats);
resolve(stats);
}).catch(reject);
});
});
}
// Get enhanced statistics from parsing report file
async getEnhancedStatsFromReport() {
const reportPath = path.join(process.cwd(), 'apps', 'local-server', 'data', 'parse-report.txt');
try {
if (!existsSync(reportPath)) {
return null;
}
const reportContent = readFileSync(reportPath, 'utf8');
const lines = reportContent.split('\n');
const stats = {
fromReport: true,
reportGenerated: lines.find(line => line.includes('Generated:'))?.split(': ')[1] || 'Unknown',
totalBooks: 0,
completedParsedBooks: 0,
completionPercentage: '0.0',
totalParagraphs: 0,
languagesSupported: 0,
databaseSize: 'Unknown',
sessionStarted: 'Unknown',
sessionElapsed: 'Unknown',
processingRate: 'Unknown',
etaForCompletion: 'Unknown',
recentlyCompleted: [],
failedBooks: []
};
// Enhanced parsing with better labels
for (const line of lines) {
if (line.includes('Total Books in Database:')) {
stats.totalBooks = parseInt(line.split(': ')[1]) || 0;
} else if (line.includes('Completed Parsed Books:')) {
stats.completedParsedBooks = parseInt(line.split(': ')[1]) || 0;
} else if (line.includes('Completion Percentage:')) {
const match = line.match(/(\d+\.\d+)%/);
stats.completionPercentage = match ? match[1] : '0.0';
} else if (line.includes('Total Paragraphs:')) {
stats.totalParagraphs = parseInt(line.split(': ')[1]) || 0;
} else if (line.includes('Languages Supported:')) {
stats.languagesSupported = parseInt(line.split(': ')[1]) || 0;
} else if (line.includes('Database Size:')) {
stats.databaseSize = line.split(': ')[1] || 'Unknown';
} else if (line.includes('Session Started:')) {
stats.sessionStarted = line.split(': ')[1] || 'Unknown';
} else if (line.includes('Elapsed Time:')) {
stats.sessionElapsed = line.split(': ')[1] || 'Unknown';
} else if (line.includes('Processing Rate:')) {
stats.processingRate = line.split(': ')[1] || 'Unknown';
} else if (line.includes('ETA for completion:')) {
stats.etaForCompletion = line.split(': ')[1] || 'Unknown';
}
}
// Add clear parsed books summary for logging
console.error(`š PARSED BOOKS STATUS: ${stats.completedParsedBooks}/${stats.totalBooks} books parsed (${stats.completionPercentage}%)`);
console.error(`š DATABASE STATUS: ${stats.totalParagraphs} paragraphs across ${stats.languagesSupported} languages`);
console.error(`š¾ DATABASE SIZE: ${stats.databaseSize}`);
console.error('š Using enhanced stats from parsing report:', stats);
return stats;
} catch (error) {
console.warn('ā ļø Could not read parsing report, falling back to database stats:', error.message);
return null;
}
}
// Find EGW quotes containing a search term - OPTIMIZED using FTS
async findEGWQuotes(query, numQuotes = 3, toolName = 'findEGWQuotes-function') {
await this.validateHeartbeat(toolName);
return retryOperation(async () => {
const startTime = Date.now();
return new Promise((resolve, reject) => {
console.error(`š Searching for quotes containing *${query}*...`);
// OPTIMIZED: Use FTS for fast search, then join with books table
// FIXED: Select actual paragraph content, not FTS content (which may contain book titles)
// FILTER: Only return paragraphs (element_type = 'p'), not headings (h4)
const ftsSearchStmt = this.db.prepare(`
SELECT
fts.para_id,
fts.book_id,
p.content_plain as content,
p.content as content_html,
p.element_type,
p.refcode_short,
p.refcode_long,
b.code as pub_code,
b.title as pub_name,
b.author as book_author,
b.pub_year,
b.folder_color_group as "group"
FROM paragraphs_fts fts
JOIN paragraphs p ON fts.para_id = p.para_id
JOIN books b ON fts.book_id = b.book_id
WHERE paragraphs_fts MATCH ?
AND (b.author = 'Ellen Gould White' OR b.author = 'Ellen G. White')
AND p.element_type = 'p'
ORDER BY rank
LIMIT ?
`);
// Use FTS MATCH for optimized search
const searchTerm = query;
// OPTIMIZED: Increase LIMIT to get more variety, but still reasonable
ftsSearchStmt.all([searchTerm, Math.min(numQuotes * 20, 80)], (err, searchResults) => {
if (err) {
console.error(`ā FTS search error for "*${query}*":`, err.message);
// Fallback to slower LIKE search if FTS fails
console.error(`š Falling back to LIKE search...`);
this.fallbackSearch(query, numQuotes, startTime, resolve, reject);
return;
}
console.error(`ā
Found ${searchResults.length} FTS results for "*${query}*"`);
if (searchResults.length === 0) {
ftsSearchStmt.finalize();
const duration = Date.now() - startTime;
resolve({
success: false,
message: `No EGW content found containing "*${query}*"` ,
query_time_ms: duration
});
return;
}
// OPTIMIZED: Filter out non-EGW content based on HTML markers (if any)
// Use faster string methods and early returns to improve performance
const validResults = [];
const nonEGWCount = { count: 0 };
for (let i = 0; i < searchResults.length; i++) {
const result = searchResults[i];
// Quick check for non-EGW content using optimized string search
const htmlContent = result.content_html || '';
if (htmlContent.length > 20 && htmlContent.includes('class="non-egw-')) {
nonEGWCount.count++;
continue;
}
validResults.push(result);
}
if (nonEGWCount.count > 0) {
console.error(`š« Filtered out ${nonEGWCount.count} non-EGW entries (performance optimized)`);
}
console.error(`ā
Using ${validResults.length} results after filtering non-EGW content`);
// SIMPLIFIED: Take larger pool for variety but still process efficiently
const expandedResults = validResults.slice(0, Math.min(validResults.length, numQuotes * 8));
// OPTIMIZED: Fast deduplication with lower similarity threshold
const deduplicatedResults = this.fastDeduplicateQuotes(expandedResults);
// SIMPLIFIED: Random selection with minimal variety processing to avoid repetition
const finalQuotes = this.getRandomSample(deduplicatedResults, numQuotes);
// Format quotes for display
const formattedQuotes = finalQuotes.map(quote => {
const cleanContent = (quote.content || '').replace(/\s+/g, ' ').trim();
const author = quote.book_author && quote.book_author.includes('Ellen') ? 'Ellen G. White' : quote.book_author;
const refcodeShort = quote.refcode_short || quote.pub_code || 'EGW';
// Use full book title from pub_name instead of truncated refcode_long
const bookTitle = quote.pub_name || quote.refcode_long || quote.refcode_short || '';
const refcodeParts = (quote.refcode_long || quote.refcode_short || '').split(',');
let page_and_paragraph = '';
if (refcodeParts[1]) {
const pageMatch = refcodeParts[1].match(/p\.\s*(\d+\.\d+)/);
page_and_paragraph = pageMatch ? pageMatch[1] : '';
}
const uniqueRefcodeShort = refcodeShort.includes(page_and_paragraph) ? refcodeShort.replace(page_and_paragraph, '') : refcodeShort;
const yearString = bookTitle.includes(quote.pub_year) ? '' : `(${quote.pub_year})`;
const trimmedShort = uniqueRefcodeShort.trim();
const trimmedParagraph = page_and_paragraph.trim();
const refBlock = (trimmedShort && trimmedParagraph
? `${trimmedShort} ${trimmedParagraph}`
: trimmedShort || trimmedParagraph).replace(/\s+/g, ' ');
return `"${cleanContent}" -- ${author}, ${bookTitle}${yearString}. - {${refBlock}}`;
});
let resultText = `Found ${finalQuotes.length} EGW quote(s) containing "*${query}*":\n\n`;
resultText += `Source Books: Multiple EGW writings\n`;
resultText += `Author: Ellen G. White\n\n`;
resultText += '='.repeat(80) + '\n\n';
formattedQuotes.forEach((quote, idx) => {
resultText += `QUOTE ${idx + 1}\n\n`;
resultText += `${quote}\n\n`;
resultText += '-'.repeat(80) + '\n\n';
});
const duration = Date.now() - startTime;
console.error(`ā
Successfully found ${finalQuotes.length} EGW quotes for "*${query}*" in ${duration}ms (FTS optimized)`);
ftsSearchStmt.finalize();
resolve({
success: true,
query: query,
quotes: finalQuotes.map(quote => ({
content: (quote.content || '').replace(/\s+/g, ' ').trim(),
reference: quote.refcode_long || quote.refcode_short,
book_title: quote.pub_name,
author: quote.book_author,
pub_year: quote.pub_year,
para_id: quote.para_id
})),
total_found: finalQuotes.length,
source_book: 'Multiple EGW writings',
author: 'Ellen G. White',
pub_year: 'Various',
query_time_ms: duration,
formatted_output: resultText
});
});
});
});
}
// OPTIMIZED: Fast deduplication using string hashing instead of Levenshtein
fastDeduplicateQuotes(quotes) {
const deduplicated = [];
const seenHashes = new Set();
for (const quote of quotes) {
const cleanContent = (quote.content || '').replace(/\s+/g, ' ').trim().toLowerCase();
// Skip very short content (likely not meaningful quotes)
if (cleanContent.length < 20) {
continue;
}
// Create simple hash for exact duplicates only
const contentHash = cleanContent.split('').sort().join('');
// Check for exact duplicates only
if (seenHashes.has(contentHash)) {
continue;
}
deduplicated.push(quote);
seenHashes.add(contentHash);
// Stop early if we have enough quotes
if (deduplicated.length >= 25) {
break;
}
}
console.error(`š Fast deduplicated ${quotes.length} results to ${deduplicated.length} unique quotes`);
return deduplicated;
}
// Fallback search method using LIKE (slower)
fallbackSearch(query, numQuotes, startTime, resolve, reject) {
const fallbackStmt = this.db.prepare(`
SELECT
p.para_id,
p.book_id,
p.content_plain as content,
p.content as content_html,
p.element_type,
p.refcode_short,
p.refcode_long,
b.code as pub_code,
b.title as pub_name,
b.author,
b.pub_year,
b.folder_color_group as "group"
FROM paragraphs p
JOIN books b ON p.book_id = b.book_id
WHERE p.content_plain LIKE ? COLLATE NOCASE
AND (b.author = 'Ellen Gould White' OR b.author = 'Ellen G. White')
AND p.element_type = 'p'
ORDER BY RANDOM()
LIMIT 80
`);
const searchTerm = `%${query}%`;
fallbackStmt.all([searchTerm], (err, searchResults) => {
if (err) {
console.error(`ā Fallback search error for "*${query}*":`, err.message);
fallbackStmt.finalize();
resolve({
success: false,
message: `Search error: ${err.message}`,
query_time_ms: Date.now() - startTime
});
return;
}
console.error(`ā ļø Found ${searchResults.length} fallback results for "*${query}*"`);
// OPTIMIZED: Use faster filtering for fallback search too
const validResults = [];
const nonEGWCount = { count: 0 };
for (let i = 0; i < searchResults.length; i++) {
const result = searchResults[i];
// Quick check for non-EGW content using optimized string search
const htmlContent = result.content_html || '';
if (htmlContent.length > 20 && htmlContent.includes('class="non-egw-')) {
nonEGWCount.count++;
continue;
}
validResults.push(result);
}
if (nonEGWCount.count > 0) {
console.error(`š« Filtered out ${nonEGWCount.count} non-EGW entries (performance optimized)`);
}
// OPTIMIZED: Use fast deduplication for fallback too
const deduplicatedResults = this.fastDeduplicateQuotes(validResults);
// SIMPLIFIED: Random selection with minimal variety processing to avoid repetition
const finalQuotes = this.getRandomSample(deduplicatedResults, numQuotes);
const duration = Date.now() - startTime;
console.error(`ā ļø Used fallback search for "*${query}*" in ${duration}ms`);
fallbackStmt.finalize();
resolve({
success: finalQuotes.length > 0,
query: query,
quotes: finalQuotes.slice(0, numQuotes).map(quote => ({
content: (quote.content || '').replace(/\s+/g, ' ').trim(),
reference: quote.refcode_long || quote.refcode_short,
book_title: quote.pub_name,
author: quote.author,
pub_year: quote.pub_year,
para_id: quote.para_id
})),
total_found: finalQuotes.length,
query_time_ms: duration,
message: finalQuotes.length > 0 ? `Found ${finalQuotes.length} quotes using fallback search` : `No results found using fallback search`
});
});
}
// Helper method to get random sample
getRandomSample(array, size) {
if (array.length <= size) return array;
const shuffled = [...array];
for (let i = shuffled.length - 1; i > 0; i--) {
const j = Math.floor(Math.random() * (i + 1));
[shuffled[i], shuffled[j]] = [shuffled[j], shuffled[i]];
}
return shuffled.slice(0, size);
}
// NEW: Get warmer status
static getWarmerStatus() {
return globalWarmer ? globalWarmer.getStatus() : { isWarm: false };
}
// NEW: Wait for database to be warmed with timeout
static async waitForWarmup() {
if (!globalWarmer) {
throw new Error('ā No warmer initialized. Create a database with requireWarming=true first.');
}
// Wait for warming to complete with timeout
const startTime = Date.now();
while (!globalWarmer.isWarm && (Date.now() - startTime < WARMUP_TIMEOUT)) {
await new Promise(resolve => setTimeout(resolve, 100));
}
if (!globalWarmer.isWarm) {
throw new Error(`ā Database warmup timeout after ${WARMUP_TIMEOUT}ms`);
}
return globalDB;
}
// NEW: Get heartbeat status
static getHeartbeatStatus() {
return {
isActive: !!globalHeartbeat,
lastHeartbeat: lastHeartbeatTime,
timeout: HEARTBEAT_TIMEOUT
};
}
close() {
if (this.db && this.db !== globalDB) {
this.db.close();
console.error('š Database connection closed');
}
// Close connection pool
if (connectionPoolInstance) {
connectionPoolInstance.closeAll();
}
}
// NEW: Launch local EGW setup tool - IMPROVED with coldstart/heartbeat handling
async launchLocalSetup(toolName = 'launchLocalSetup') {
console.log('š Launching EGW Writings MCP Server Setup...');
try {
const { spawn } = await import('child_process');
console.log('\nš Starting MCP Server with optimized startup...');
// IMPROVED: Start server with environment variables to handle coldstart/heartbeat
const serverProcess = spawn('node', ['apps/local-server/src/server-universal.js'], {
stdio: 'inherit',
shell: true,
env: {
...process.env,
// PREVENT COLDSTART: Use existing warm connection if available
EGW_SKIP_WARMING: 'true',
// HANDLE HEARTBEAT: Use auto-heartbeat starter
EGW_AUTO_HEARTBEAT: 'true',
// OPTIMIZE: Use shared database connection
EGW_SHARED_DB: 'true'
}
});
serverProcess.on('error', (error) => {
console.error('ā Failed to start MCP server:', error.message);
});
serverProcess.on('close', (code) => {
if (code !== 0) {
console.error(`ā MCP server exited with code ${code}`);
}
});
// Wait longer for server to initialize properly (warming and heartbeat)
console.log('ā³ Waiting for server initialization (warming + heartbeat setup)...');
await new Promise(resolve => setTimeout(resolve, 8000)); // Increased wait time
console.log('\nš¬ Starting EGW Chat CLI...');
const chatProcess = spawn('node', ['egw-chat-cli/egw-chat-cli-http.js'], {
stdio: 'inherit',
shell: true
});
chatProcess.on('error', (error) => {
console.error('ā Failed to start chat CLI:', error.message);
});
chatProcess.on('close', (code) => {
if (code !== 0) {
console.error(`ā Chat CLI exited with code ${code}`);
}
});
console.log('\nā
EGW Writings MCP Server and Chat CLI started successfully!');
console.log('š MCP Server: http://localhost:3000/mcp');
console.log('š¬ Chat CLI: Interactive terminal ready');
console.log('\nš Usage Tips:');
console.log(' ā¢ Ask questions about Ellen G. White writings');
console.log(' ā¢ Try: "give me a quote on love"');
console.log(' ā¢ Try: "what did EGW say about faith?"');
console.log(' ā¢ Try: "list all books"');
console.log(' ā¢ Type "exit" to quit the chat CLI');
console.log('\nš„ Optimization: Server started with shared warm connection and auto-heartbeat');
console.log('š Security: Your DeepSeek API key will be prompted for each session');
console.log('\nš” If server takes time to warm up, please wait a moment before using tools');
console.log('š„ Health checks enabled - system will monitor and auto-recover from failures');
return {
success: true,
server_process: serverProcess.pid,
chat_process: chatProcess.pid,
optimization_enabled: true,
message: 'Setup launched with coldstart and heartbeat optimization'
};
} catch (error) {
console.error('ā Failed to launch local setup:', error.message);
throw error;
}
}
// NEW: PyForge IDE integration for AI agent automation
async pyforgeExecute(code, action = 'execute', packageName = null, toolName = 'pyforgeExecute-function') {
await this.validateHeartbeat(toolName);
return retryOperation(async () => {
try {
console.error(`š PyForge IDE executing action: ${action}`);
// Import PyForge integration
const { PyForgeIntegration } = await import('./pyforge-integration.js');
const pyforge = new PyForgeIntegration();
// Check if PyForge IDE is available
if (!pyforge.isAvailable()) {
throw new Error('ā PyForge IDE not found. Please ensure PyForge IDE is properly installed in packages/shared/src/database-utils');
}
switch (action) {
case 'execute':
return await pyforge.executePythonCode(code);
case 'install_package':
if (!packageName) {
throw new Error('ā Package name required for install_package action');
}
return await pyforge.installPackage(packageName);
case 'list_files':
return await pyforge.listFiles();
default:
throw new Error(`ā Unknown action: ${action}`);
}
} catch (error) {
console.error('ā PyForge IDE execution error:', error.message);
throw error;
}
});
}
}