Files
pages/internal/store/search_index.go
Anya Tokugawa c1175d389f Implement Knowledge City platform, organizer and DAV integration
Capture accumulated application, UI, CLI, regression tests and design documentation changes, including task project mapping and default CalDAV list naming.

Keep deployment data, backups, local credentials and build artifacts out of Git. Move the Compose enrollment token to an ignored local environment file.
2026-09-27 14:44:43 +09:00

212 lines
6.5 KiB
Go

package store
import "database/sql"
// EnsureSearchIndexSchema installs an FTS5 index over immutable Revisions.
// Paths stay outside the index because links can be renamed independently; the
// search query joins matching latest Revisions to the current domain Paths.
func (s *Store) EnsureSearchIndexSchema() error {
var revisionsTable string
if err := s.DB.QueryRow(`
SELECT name FROM sqlite_master
WHERE type = 'table' AND name = 'revisions'
`).Scan(&revisionsTable); err != nil {
if err == sql.ErrNoRows {
return nil
}
return err
}
if _, err := s.DB.Exec(`
CREATE VIRTUAL TABLE IF NOT EXISTS revision_search USING fts5(
revision_uuid UNINDEXED,
object_uuid UNINDEXED,
filename,
body,
tokenize = 'unicode61'
)
`); err != nil {
return err
}
if _, err := s.DB.Exec(`
CREATE TABLE IF NOT EXISTS revision_search_state (
revision_uuid TEXT PRIMARY KEY
)
`); err != nil {
return err
}
if _, err := s.DB.Exec(`
CREATE TABLE IF NOT EXISTS latest_revision_index (
object_uuid TEXT PRIMARY KEY,
revision_uuid TEXT NOT NULL,
content_type TEXT NOT NULL,
filename TEXT NOT NULL,
document_hash TEXT NOT NULL,
created_at_ms INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS search_schema_state (
name TEXT PRIMARY KEY
);
CREATE INDEX IF NOT EXISTS idx_latest_revision_search_uuid ON latest_revision_index(revision_uuid)
`); err != nil {
return err
}
var latestBackfilled int
if err := s.DB.QueryRow(`
SELECT EXISTS(
SELECT 1 FROM search_schema_state
WHERE name = 'latest_revision_index_v1'
)
`).Scan(&latestBackfilled); err != nil {
return err
}
if latestBackfilled == 0 {
if _, err := s.DB.Exec(`
INSERT OR REPLACE INTO latest_revision_index (
object_uuid, revision_uuid, content_type, filename,
document_hash, created_at_ms
)
SELECT
object_uuid, uuid, COALESCE(content_type, ''),
COALESCE(filename, ''), COALESCE(document_hash, ''),
COALESCE(created_at_ms, created_at * 1000, 0)
FROM (
SELECT
uuid, object_uuid, content_type, filename,
document_hash, created_at_ms, created_at,
ROW_NUMBER() OVER (
PARTITION BY object_uuid
ORDER BY COALESCE(created_at_ms, created_at * 1000) DESC,
rowid DESC
) AS rn
FROM revisions
)
WHERE rn = 1;
INSERT OR IGNORE INTO search_schema_state (name)
VALUES ('latest_revision_index_v1');
`); err != nil {
return err
}
}
// Populate the cheap membership table once for installations that already
// created revision_search before revision_search_state was introduced.
var stateExists int
if err := s.DB.QueryRow(`
SELECT EXISTS(SELECT 1 FROM revision_search_state LIMIT 1)
`).Scan(&stateExists); err != nil {
return err
}
if stateExists == 0 {
if _, err := s.DB.Exec(`
INSERT OR IGNORE INTO revision_search_state (revision_uuid)
SELECT revision_uuid FROM revision_search
`); err != nil {
return err
}
}
// Trigger definitions evolve with the projection schema; recreate them so
// existing installations receive the current maintenance logic.
if _, err := s.DB.Exec(`
DROP TRIGGER IF EXISTS revision_search_insert;
DROP TRIGGER IF EXISTS revision_search_delete;
`); err != nil {
return err
}
if _, err := s.DB.Exec(`
CREATE TRIGGER IF NOT EXISTS revision_search_insert
AFTER INSERT ON revisions BEGIN
INSERT INTO revision_search (
revision_uuid, object_uuid, filename, body
) VALUES (
new.uuid,
new.object_uuid,
COALESCE(new.filename, ''),
CASE
WHEN lower(COALESCE(new.content_type, '')) LIKE 'text/%'
OR lower(COALESCE(new.content_type, '')) LIKE 'application/json%'
OR lower(COALESCE(new.content_type, '')) LIKE 'application/xml%'
OR lower(COALESCE(new.content_type, '')) LIKE 'application/javascript%'
OR lower(COALESCE(new.content_type, '')) LIKE 'application/x-yaml%'
OR lower(COALESCE(new.content_type, '')) LIKE 'application/yaml%'
THEN COALESCE(new.body, '')
ELSE ''
END
);
INSERT OR IGNORE INTO revision_search_state (revision_uuid)
VALUES (new.uuid);
INSERT INTO latest_revision_index (
object_uuid, revision_uuid, content_type, filename,
document_hash, created_at_ms
) VALUES (
new.object_uuid, new.uuid, COALESCE(new.content_type, ''),
COALESCE(new.filename, ''), COALESCE(new.document_hash, ''),
COALESCE(new.created_at_ms, new.created_at * 1000, 0)
)
ON CONFLICT(object_uuid) DO UPDATE SET
revision_uuid = excluded.revision_uuid,
content_type = excluded.content_type,
filename = excluded.filename,
document_hash = excluded.document_hash,
created_at_ms = excluded.created_at_ms
WHERE excluded.created_at_ms >= latest_revision_index.created_at_ms;
END
`); err != nil {
return err
}
if _, err := s.DB.Exec(`
CREATE TRIGGER IF NOT EXISTS revision_search_delete
AFTER DELETE ON revisions BEGIN
DELETE FROM revision_search WHERE revision_uuid = old.uuid;
DELETE FROM revision_search_state WHERE revision_uuid = old.uuid;
DELETE FROM latest_revision_index
WHERE object_uuid = old.object_uuid
AND revision_uuid = old.uuid;
INSERT OR REPLACE INTO latest_revision_index (
object_uuid, revision_uuid, content_type, filename,
document_hash, created_at_ms
)
SELECT
object_uuid, uuid, COALESCE(content_type, ''),
COALESCE(filename, ''), COALESCE(document_hash, ''),
COALESCE(created_at_ms, created_at * 1000, 0)
FROM revisions
WHERE object_uuid = old.object_uuid
ORDER BY COALESCE(created_at_ms, created_at * 1000) DESC, rowid DESC
LIMIT 1;
END
`); err != nil {
return err
}
_, err := s.DB.Exec(`
INSERT INTO revision_search (
revision_uuid, object_uuid, filename, body
)
SELECT
r.uuid,
r.object_uuid,
COALESCE(r.filename, ''),
CASE
WHEN lower(COALESCE(r.content_type, '')) LIKE 'text/%'
OR lower(COALESCE(r.content_type, '')) LIKE 'application/json%'
OR lower(COALESCE(r.content_type, '')) LIKE 'application/xml%'
OR lower(COALESCE(r.content_type, '')) LIKE 'application/javascript%'
OR lower(COALESCE(r.content_type, '')) LIKE 'application/x-yaml%'
OR lower(COALESCE(r.content_type, '')) LIKE 'application/yaml%'
THEN COALESCE(r.body, '')
ELSE ''
END
FROM revisions r
WHERE NOT EXISTS (
SELECT 1 FROM revision_search_state state
WHERE state.revision_uuid = r.uuid
)
`)
if err != nil {
return err
}
_, err = s.DB.Exec(`
INSERT OR IGNORE INTO revision_search_state (revision_uuid)
SELECT uuid FROM revisions
`)
return err
}