Jyong
4ee43b8afc
Import the committed KnowledgeFS snapshot dc4072ee302317145612087ce7440851dc329fd0 under knowledge-fs/ without its Git history, local IDE settings, or build artifacts.
66 lines
2.6 KiB
SQL
66 lines
2.6 KiB
SQL
-- Knowledge Platform schema migration
|
|
-- Migration id: 0007_knowledge_node_generations
|
|
-- Dialect: tidb
|
|
|
|
-- Knowledge nodes are immutable-build components. NULL remains the legacy publication scope;
|
|
-- non-NULL generations can be built and evaluated without mutating currently readable nodes.
|
|
-- TiDB requires v8.5+ with CHECK and foreign-key enforcement enabled, as verified by the runner.
|
|
ALTER TABLE `knowledge_nodes`
|
|
ADD COLUMN IF NOT EXISTS `publication_generation_id` CHAR(36);
|
|
ALTER TABLE `knowledge_nodes`
|
|
MODIFY COLUMN IF EXISTS `kind` VARCHAR(16) NOT NULL;
|
|
ALTER TABLE `knowledge_nodes`
|
|
ADD COLUMN IF NOT EXISTS `publication_generation_key` CHAR(36)
|
|
GENERATED ALWAYS AS (
|
|
COALESCE(`publication_generation_id`, '00000000-0000-0000-0000-000000000000')
|
|
) VIRTUAL;
|
|
|
|
-- TiDB does not permit CHECK constraints to reference columns used by a foreign key referential
|
|
-- action. Candidate pair/checkpoint invariants remain transactionally enforced by the compilation
|
|
-- repository; PostgreSQL additionally keeps the database CHECK constraints.
|
|
|
|
-- The zero UUID is reserved only for mapping legacy NULL into the logical unique index. Adding the
|
|
-- constraint before enabling generation writers fails closed if historical data violates it.
|
|
ALTER TABLE `knowledge_nodes`
|
|
ADD CONSTRAINT `knowledge_nodes_pub_gen_nonzero_ck`
|
|
CHECK (
|
|
`publication_generation_id` IS NULL
|
|
OR (
|
|
`publication_generation_id` REGEXP '^[0-9A-Fa-f]{8}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{12}$'
|
|
AND `publication_generation_id` <> '00000000-0000-0000-0000-000000000000'
|
|
)
|
|
);
|
|
|
|
-- Retained generations must not amplify the two high-frequency node walks.
|
|
DROP INDEX IF EXISTS `knowledge_nodes_space_asset_kind_idx` ON `knowledge_nodes`;
|
|
CREATE INDEX IF NOT EXISTS `knowledge_nodes_space_asset_kind_idx`
|
|
ON `knowledge_nodes` (
|
|
`knowledge_space_id`,
|
|
`publication_generation_id`,
|
|
`document_asset_id`,
|
|
`kind`,
|
|
`id`
|
|
);
|
|
|
|
DROP INDEX IF EXISTS `knowledge_nodes_artifact_offset_idx` ON `knowledge_nodes`;
|
|
CREATE INDEX IF NOT EXISTS `knowledge_nodes_artifact_offset_idx`
|
|
ON `knowledge_nodes` (
|
|
`knowledge_space_id`,
|
|
`parse_artifact_id`,
|
|
`publication_generation_id`,
|
|
`start_offset`,
|
|
`id`
|
|
);
|
|
|
|
-- Do not delete or arbitrarily merge historical duplicates. Unique-index creation intentionally
|
|
-- aborts the migration so an operator can reconcile evidence-preserving node identity explicitly.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS `knowledge_nodes_artifact_kind_offsets_uq`
|
|
ON `knowledge_nodes` (
|
|
`knowledge_space_id`,
|
|
`parse_artifact_id`,
|
|
`kind`,
|
|
`start_offset`,
|
|
`end_offset`,
|
|
`publication_generation_key`
|
|
);
|