Data Models¶

-- Define schema
DEFINE TABLE department SCHEMAFULL;
DEFINE FIELD name     ON department TYPE string;
DEFINE FIELD budget   ON department TYPE number;
DEFINE FIELD location ON department TYPE string;

DEFINE TABLE employee SCHEMAFULL;
DEFINE FIELD name       ON employee TYPE string;
DEFINE FIELD email      ON employee TYPE string ASSERT string::is::email($value);
DEFINE FIELD department ON employee TYPE record<department>;
DEFINE FIELD salary     ON employee TYPE number;
DEFINE FIELD hired      ON employee TYPE datetime;

-- Insert departments
CREATE department:engineering CONTENT {
    name: "Engineering", budget: 500000, location: "Amsterdam"
};
CREATE department:research CONTENT {
    name: "Research", budget: 300000, location: "Utrecht"
};

-- Insert employees
CREATE employee:alice CONTENT {
    name: "Alice van den Berg",
    email: "alice@example.com",
    department: department:engineering,
    salary: 85000,
    hired: d'2023-03-15T09:00:00Z'
};
CREATE employee:bob CONTENT {
    name: "Bob de Vries",
    email: "bob@example.com",
    department: department:engineering,
    salary: 92000,
    hired: d'2022-01-10T09:00:00Z'
};
CREATE employee:carol CONTENT {
    name: "Carol Jansen",
    email: "carol@example.com",
    department: department:research,
    salary: 78000,
    hired: d'2024-06-01T09:00:00Z'
};

-- Basic query: employees earning above 80k with department resolved
SELECT name, salary, department.name AS dept
FROM employee
WHERE salary > 80000
ORDER BY salary DESC;
-- Results: Bob (92000, Engineering), Alice (85000, Engineering)

-- Aggregation: average salary per department
SELECT department.name AS dept, math::mean(salary) AS avg_salary, count() AS headcount
FROM employee
GROUP BY department
ORDER BY dept;
-- Engineering: avg=88500, headcount=2; Research: avg=78000, headcount=1

-- Subquery: employees in departments with budget > 400k
SELECT name, department.name AS dept
FROM employee
WHERE department IN (
    SELECT VALUE id FROM department WHERE budget > 400000
);
-- Alice, Bob

-- Update: give Engineering a raise
UPDATE employee SET salary += 5000 WHERE department = department:engineering;

DEFINE TABLE project SCHEMALESS;
DEFINE TABLE task SCHEMALESS;

-- Create a project with nested metadata and milestones array
CREATE project:indentiagraph CONTENT {
    name: "IndentiaGraph",
    status: "active",
    tags: ["database", "graph", "rdf", "rust"],
    metadata: {
        started: "2024-01-15",
        lead: "Alice",
        priority: "high",
        budget: 250000
    },
    milestones: [
        { name: "Alpha", date: "2024-06-01", completed: true },
        { name: "Beta",  date: "2024-12-01", completed: true },
        { name: "GA",    date: "2025-06-01", completed: false }
    ]
};

-- Create tasks with record links to the project
CREATE task:lpg_csr CONTENT {
    title: "Implement CSR adjacency structure",
    project: project:indentiagraph,
    assignee: "Bob",
    status: "done",
    labels: ["performance", "lpg"],
    story_points: 8
};
CREATE task:sparql_engine CONTENT {
    title: "SPARQL 1.2 property path evaluation",
    project: project:indentiagraph,
    assignee: "Alice",
    status: "in_progress",
    labels: ["rdf", "sparql"],
    depends_on: [task:lpg_csr],
    story_points: 13
};

-- Nested field access
SELECT name, metadata.lead, metadata.priority
FROM project WHERE status = "active";

-- Array contains filter
SELECT title, assignee FROM task WHERE labels CONTAINS "lpg";

-- Filter array elements inline (returns only completed milestones)
SELECT milestones[WHERE completed = true] AS done_milestones
FROM project:indentiagraph;

-- Auto-resolve record link: project.name is fetched automatically
SELECT title, project.name AS project_name, assignee
FROM task
WHERE assignee = "Alice";

-- UPSERT: create or overwrite
UPSERT project:fleetapi SET
    name = "Fleet API",
    status = "active",
    metadata.lead = "Carol";

-- MERGE: partial update (only specified fields change)
UPDATE task:sparql_engine MERGE { status: "done", completed_at: time::now() };

-- DELETE with condition
DELETE task WHERE status = "done" AND story_points < 5;

-- Define table with embedding field
DEFINE TABLE document SCHEMAFULL;
DEFINE FIELD title     ON document TYPE string;
DEFINE FIELD content   ON document TYPE string;
DEFINE FIELD source    ON document TYPE string;
DEFINE FIELD embedding ON document TYPE array<float>;

-- Define HNSW index (1536-dimensional, OpenAI text-embedding-3-small compatible)
DEFINE INDEX idx_embedding ON document FIELDS embedding
    HNSW DIMENSION 1536 DIST COSINE
    EFC 200 M 16;

-- Insert documents with pre-computed embeddings (embedding values abbreviated)
CREATE document:doc1 CONTENT {
    title:   "Introduction to Knowledge Graphs",
    content: "Knowledge graphs represent real-world entities and their relationships...",
    source:  "internal_wiki",
    embedding: [0.023, -0.041, 0.087, /* ... 1536 floats total ... */]
};
CREATE document:doc2 CONTENT {
    title:   "SPARQL 1.2 Working Draft",
    content: "SPARQL is the W3C query language for RDF data...",
    source:  "w3c_spec",
    embedding: [0.015, -0.033, 0.091, /* ... */]
};

-- Similarity search: find 5 most relevant documents for a query vector
LET $query_vec = [0.021, -0.039, 0.085, /* ... */];

SELECT id, title, source,
    vector::similarity::cosine(embedding, $query_vec) AS score
FROM document
WHERE embedding <|5,200|> $query_vec
ORDER BY score DESC;

-- Euclidean distance search
SELECT id, title,
    vector::distance::euclidean(embedding, $query_vec) AS distance
FROM document
WHERE embedding <|5|> $query_vec
ORDER BY distance ASC;

-- Hybrid search: combine BM25 keyword score with vector similarity
DEFINE INDEX idx_content ON document FIELDS content
    SEARCH ANALYZER english_analyzer BM25;

LET $text_score  = search::score(1);
LET $vec_score   = vector::similarity::cosine(embedding, $query_vec);

SELECT id, title,
    ($text_score * 0.4 + $vec_score * 0.6) AS combined_score
FROM document
WHERE content @1@ "knowledge graph"
  AND embedding <|20,200|> $query_vec
ORDER BY combined_score DESC
LIMIT 10;

-- RAG context retrieval pattern
LET $context_chunks = (
    SELECT title, content,
        vector::similarity::cosine(embedding, $query_vec) AS relevance
    FROM document
    WHERE embedding <|5,100|> $query_vec
    ORDER BY relevance DESC
);

-- Return context for LLM prompt construction
SELECT title, content FROM $context_chunks;

-- Define analyzers
DEFINE ANALYZER english_content
    TOKENIZERS blank, class
    FILTERS lowercase, ascii, snowball(english), edgengram(2, 15);

DEFINE ANALYZER exact_match
    TOKENIZERS blank
    FILTERS lowercase, ascii;

-- Define table and search indexes
DEFINE TABLE article SCHEMAFULL;
DEFINE FIELD title      ON article TYPE string;
DEFINE FIELD body       ON article TYPE string;
DEFINE FIELD author     ON article TYPE string;
DEFINE FIELD tags       ON article TYPE array<string>;
DEFINE FIELD published  ON article TYPE datetime;

DEFINE INDEX idx_title  ON article FIELDS title  SEARCH ANALYZER english_content  BM25(1.2, 0.75);
DEFINE INDEX idx_body   ON article FIELDS body   SEARCH ANALYZER english_content  BM25(1.2, 0.75);
DEFINE INDEX idx_tags   ON article FIELDS tags   SEARCH ANALYZER exact_match       BM25;

-- Insert articles
CREATE article:a1 CONTENT {
    title:     "Building Knowledge Graphs with SPARQL",
    body:      "SPARQL enables complex querying of RDF knowledge graphs...",
    author:    "Alice",
    tags:      ["sparql", "rdf", "knowledge-graph"],
    published: d'2025-03-01T00:00:00Z'
};
CREATE article:a2 CONTENT {
    title:     "Vector Search in Rust",
    body:      "HNSW (Hierarchical Navigable Small World) enables fast approximate nearest neighbor search...",
    author:    "Bob",
    tags:      ["vector", "rust", "hnsw"],
    published: d'2025-04-15T00:00:00Z'
};
CREATE article:a3 CONTENT {
    title:     "Multi-Model Databases Explained",
    body:      "Modern applications need relational, document, graph, vector, and full-text search in one system...",
    author:    "Carol",
    tags:      ["database", "multi-model"],
    published: d'2025-05-10T00:00:00Z'
};

-- Single-field full-text search (title only)
SELECT title, search::score(1) AS relevance
FROM article
WHERE title @1@ "knowledge graph"
ORDER BY relevance DESC;

-- Multi-field search with boosted title (title score × 2 + body score)
SELECT title, author,
    search::score(1) * 2.0 + search::score(2) AS relevance
FROM article
WHERE title @1@ "graph"
   OR body   @2@ "graph"
ORDER BY relevance DESC
LIMIT 10;

-- Fuzzy prefix matching (edgengram allows partial tokens)
SELECT title FROM article WHERE title @1@ "knowl";
-- Returns "Building Knowledge Graphs..." because of edgengram(2,15)

-- Combined: full-text + date filter + tag filter
SELECT title, author, published,
    search::score(1) + search::score(2) AS relevance
FROM article
WHERE (title @1@ "database" OR body @2@ "database")
  AND published > d'2025-01-01T00:00:00Z'
ORDER BY relevance DESC;

-- Elasticsearch-compatible API (port 9200)
-- POST http://localhost:9200/article/_search
-- {
--   "query": {
--     "multi_match": {
--       "query": "knowledge graph",
--       "fields": ["title^2", "body"],
--       "fuzziness": "AUTO"
--     }
--   },
--   "size": 10
-- }