c9aadfcb30
- Add FTS5 pre-creation in fetch-templates.ts before data import - Create prebuild-fts5.ts script to ensure FTS5 tables exist - Improve logging in template-repository.ts for better debugging - Add npm script 'prebuild:fts5' for manual FTS5 setup This ensures template search works consistently in Docker mode where runtime FTS5 table creation might fail due to permissions. 🤖 Generated with [Claude Code](https://claude.ai/code) Co-Authored-By: Claude <noreply@anthropic.com>
111 lines
3.3 KiB
TypeScript
111 lines
3.3 KiB
TypeScript
#!/usr/bin/env npx tsx
|
||
/**
|
||
* Pre-build FTS5 indexes for the database
|
||
* This ensures FTS5 tables are created before the database is deployed to Docker
|
||
*/
|
||
import { createDatabaseAdapter } from '../src/database/database-adapter';
|
||
import { logger } from '../src/utils/logger';
|
||
import * as fs from 'fs';
|
||
|
||
async function prebuildFTS5() {
|
||
console.log('🔍 Pre-building FTS5 indexes...\n');
|
||
|
||
const dbPath = './data/nodes.db';
|
||
|
||
if (!fs.existsSync(dbPath)) {
|
||
console.error('❌ Database not found at', dbPath);
|
||
console.error(' Please run npm run rebuild first');
|
||
process.exit(1);
|
||
}
|
||
|
||
const db = await createDatabaseAdapter(dbPath);
|
||
|
||
// Check FTS5 support
|
||
const hasFTS5 = db.checkFTS5Support();
|
||
|
||
if (!hasFTS5) {
|
||
console.log('ℹ️ FTS5 not supported in this SQLite build');
|
||
console.log(' Skipping FTS5 pre-build');
|
||
db.close();
|
||
return;
|
||
}
|
||
|
||
console.log('✅ FTS5 is supported');
|
||
|
||
try {
|
||
// Create FTS5 virtual table for templates
|
||
console.log('\n📋 Creating FTS5 table for templates...');
|
||
db.exec(`
|
||
CREATE VIRTUAL TABLE IF NOT EXISTS templates_fts USING fts5(
|
||
name, description, content=templates
|
||
);
|
||
`);
|
||
|
||
// Create triggers to keep FTS5 in sync
|
||
console.log('🔗 Creating synchronization triggers...');
|
||
|
||
db.exec(`
|
||
CREATE TRIGGER IF NOT EXISTS templates_ai AFTER INSERT ON templates BEGIN
|
||
INSERT INTO templates_fts(rowid, name, description)
|
||
VALUES (new.id, new.name, new.description);
|
||
END;
|
||
`);
|
||
|
||
db.exec(`
|
||
CREATE TRIGGER IF NOT EXISTS templates_au AFTER UPDATE ON templates BEGIN
|
||
UPDATE templates_fts SET name = new.name, description = new.description
|
||
WHERE rowid = new.id;
|
||
END;
|
||
`);
|
||
|
||
db.exec(`
|
||
CREATE TRIGGER IF NOT EXISTS templates_ad AFTER DELETE ON templates BEGIN
|
||
DELETE FROM templates_fts WHERE rowid = old.id;
|
||
END;
|
||
`);
|
||
|
||
// Rebuild FTS5 index from existing data
|
||
console.log('🔄 Rebuilding FTS5 index from existing templates...');
|
||
|
||
// Clear existing FTS data
|
||
db.exec('DELETE FROM templates_fts');
|
||
|
||
// Repopulate from templates table
|
||
db.exec(`
|
||
INSERT INTO templates_fts(rowid, name, description)
|
||
SELECT id, name, description FROM templates
|
||
`);
|
||
|
||
// Get counts
|
||
const templateCount = db.prepare('SELECT COUNT(*) as count FROM templates').get() as { count: number };
|
||
const ftsCount = db.prepare('SELECT COUNT(*) as count FROM templates_fts').get() as { count: number };
|
||
|
||
console.log(`\n✅ FTS5 pre-build complete!`);
|
||
console.log(` Templates: ${templateCount.count}`);
|
||
console.log(` FTS5 entries: ${ftsCount.count}`);
|
||
|
||
// Test FTS5 search
|
||
console.log('\n🧪 Testing FTS5 search...');
|
||
const testResults = db.prepare(`
|
||
SELECT COUNT(*) as count FROM templates t
|
||
JOIN templates_fts ON t.id = templates_fts.rowid
|
||
WHERE templates_fts MATCH 'webhook'
|
||
`).get() as { count: number };
|
||
|
||
console.log(` Found ${testResults.count} templates matching "webhook"`);
|
||
|
||
} catch (error) {
|
||
console.error('❌ Error pre-building FTS5:', error);
|
||
process.exit(1);
|
||
}
|
||
|
||
db.close();
|
||
console.log('\n✅ Database is ready for Docker deployment!');
|
||
}
|
||
|
||
// Run if called directly
|
||
if (require.main === module) {
|
||
prebuildFTS5().catch(console.error);
|
||
}
|
||
|
||
export { prebuildFTS5 }; |