140 lines
4.8 KiB
Plaintext
140 lines
4.8 KiB
Plaintext
-- SurrealDB Schema for Logseq Blocks Support
|
|
-- This schema extends the KOGRAL system to support block-level storage and querying
|
|
|
|
-- Define namespace and database
|
|
-- USE NS kogral;
|
|
-- USE DB default;
|
|
|
|
-- ============================================================================
|
|
-- BLOCK TABLE
|
|
-- ============================================================================
|
|
|
|
DEFINE TABLE block SCHEMAFULL;
|
|
|
|
-- Fields
|
|
DEFINE FIELD node_id ON block TYPE record(node) ASSERT $value != NONE;
|
|
DEFINE FIELD block_id ON block TYPE string ASSERT $value != NONE;
|
|
DEFINE FIELD content ON block TYPE string;
|
|
DEFINE FIELD parent_id ON block TYPE option<string>;
|
|
DEFINE FIELD created ON block TYPE datetime;
|
|
DEFINE FIELD modified ON block TYPE datetime;
|
|
|
|
-- Properties (flexible object for block metadata)
|
|
DEFINE FIELD properties ON block TYPE object;
|
|
DEFINE FIELD properties.tags ON block TYPE array;
|
|
DEFINE FIELD properties.status ON block TYPE option<string>;
|
|
DEFINE FIELD properties.custom ON block TYPE option<object>;
|
|
DEFINE FIELD properties.block_refs ON block TYPE array;
|
|
DEFINE FIELD properties.page_refs ON block TYPE array;
|
|
|
|
-- ============================================================================
|
|
-- INDEXES
|
|
-- ============================================================================
|
|
|
|
-- Index on node_id for fast lookup of all blocks in a node
|
|
DEFINE INDEX block_node_idx ON block COLUMNS node_id;
|
|
|
|
-- Index on block_id for direct block lookup
|
|
DEFINE INDEX block_id_idx ON block COLUMNS block_id UNIQUE;
|
|
|
|
-- Index on tags for tag-based queries (find all #card blocks)
|
|
DEFINE INDEX block_tags_idx ON block COLUMNS properties.tags;
|
|
|
|
-- Index on status for TODO/DONE queries
|
|
DEFINE INDEX block_status_idx ON block COLUMNS properties.status;
|
|
|
|
-- Index on parent_id for hierarchical queries
|
|
DEFINE INDEX block_parent_idx ON block COLUMNS parent_id;
|
|
|
|
-- Full-text search index on content
|
|
DEFINE INDEX block_content_search ON block COLUMNS content SEARCH ANALYZER simple BM25;
|
|
|
|
-- ============================================================================
|
|
-- QUERIES (Examples)
|
|
-- ============================================================================
|
|
|
|
-- Find all blocks with a specific tag
|
|
-- SELECT * FROM block WHERE $tag IN properties.tags;
|
|
|
|
-- Find all TODO blocks
|
|
-- SELECT * FROM block WHERE properties.status = "TODO";
|
|
|
|
-- Find all blocks in a specific node
|
|
-- SELECT * FROM block WHERE node_id = $node_record_id;
|
|
|
|
-- Find blocks with custom property
|
|
-- SELECT * FROM block WHERE properties.custom[$key] = $value;
|
|
|
|
-- Full-text search in block content
|
|
-- SELECT * FROM block WHERE content @@ $search_term;
|
|
|
|
-- Get block hierarchy (parent and all children)
|
|
-- SELECT *,
|
|
-- (SELECT * FROM block WHERE parent_id = $parent.block_id) AS children
|
|
-- FROM block WHERE block_id = $parent_block_id;
|
|
|
|
-- ============================================================================
|
|
-- RELATIONSHIPS
|
|
-- ============================================================================
|
|
|
|
-- Block belongs to a Node
|
|
DEFINE FIELD node_id ON block TYPE record(node);
|
|
|
|
-- Example: Get all blocks for a node with their content
|
|
-- SELECT * FROM block WHERE node_id = $node_id ORDER BY created ASC;
|
|
|
|
-- Example: Count blocks by tag across all nodes
|
|
-- SELECT
|
|
-- array::flatten(properties.tags) AS tag,
|
|
-- count() AS count
|
|
-- FROM block
|
|
-- GROUP BY tag
|
|
-- ORDER BY count DESC;
|
|
|
|
-- Example: Get all flashcards (#card) across the knowledge base
|
|
-- SELECT
|
|
-- node_id.title AS node_title,
|
|
-- content,
|
|
-- properties
|
|
-- FROM block
|
|
-- WHERE "card" IN properties.tags;
|
|
|
|
-- ============================================================================
|
|
-- MIGRATION QUERIES
|
|
-- ============================================================================
|
|
|
|
-- Populate blocks table from existing nodes (run once)
|
|
-- This would be executed by the sync mechanism or migration script
|
|
|
|
-- Example migration pseudocode:
|
|
-- FOR node IN (SELECT * FROM node) {
|
|
-- LET parsed_blocks = parse_markdown(node.content);
|
|
-- FOR block IN parsed_blocks {
|
|
-- CREATE block CONTENT {
|
|
-- node_id: node.id,
|
|
-- block_id: block.id,
|
|
-- content: block.content,
|
|
-- parent_id: block.parent_id,
|
|
-- created: block.created,
|
|
-- modified: block.modified,
|
|
-- properties: {
|
|
-- tags: block.properties.tags,
|
|
-- status: block.properties.status,
|
|
-- custom: block.properties.custom,
|
|
-- block_refs: block.properties.block_refs,
|
|
-- page_refs: block.properties.page_refs
|
|
-- }
|
|
-- };
|
|
-- }
|
|
-- }
|
|
|
|
-- ============================================================================
|
|
-- CLEANUP QUERIES
|
|
-- ============================================================================
|
|
|
|
-- Delete all blocks for a specific node
|
|
-- DELETE block WHERE node_id = $node_record_id;
|
|
|
|
-- Delete orphaned blocks (node no longer exists)
|
|
-- DELETE block WHERE node_id NOT IN (SELECT id FROM node);
|