语义搜索SQLite服务器

# One-time setup: Import existing tickets
execute_sql("""
  CREATE TABLE tickets(
    id INTEGER PRIMARY KEY,
    question TEXT,
    answer TEXT,
    embedding BLOB CHECK(vec_length(embedding) == 1024)
  )
""")

execute_sql("""
  INSERT INTO tickets(question, answer, embedding)
  SELECT question, answer, vec_f32(generate_embedding(question))
  FROM imported_tickets
""")

# For each new ticket, find similar solved tickets
execute_sql("""
  SELECT question, answer, 
         vec_distance_cosine(embedding, vec_f32(:new_ticket)) as similarity
  FROM tickets
  WHERE similarity < 0.3
  ORDER BY similarity LIMIT 3
""", bindings={"new_ticket": {"_embedding_text": user_question}})

CREATE TABLE documents(
  id INTEGER PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding BLOB CHECK(
    typeof(embedding) == 'blob'
    AND vec_length(embedding) == 1024  -- Qwen embeddings are 1024-dimensional
  )
);

-- Insert with automatic embedding
INSERT INTO documents(title, content, embedding)
VALUES (
  'User Guide',
  'How to use the application...',
  vec_f32(generate_embedding('How to use the application...'))
);

-- Bulk update missing embeddings
UPDATE documents
SET embedding = vec_f32(generate_embedding(content))
WHERE embedding IS NULL;

execute_sql(
    "INSERT INTO docs(text, embedding) VALUES (:text, vec_f32(:emb))",
    bindings={
        "text": "Document content here",
        "emb": {"_embedding_col": "text"}  # Embeds the :text value
    }
)

execute_sql(
    "INSERT INTO metadata(category, embedding) VALUES (:cat, vec_f32(:emb))",
    bindings={
        "cat": "technical",
        "emb": {"_embedding_text": "Technical documentation category"}
    }
)

-- Find similar documents
SELECT 
  title, 
  content,
  vec_distance_cosine(embedding, vec_f32(:query)) as similarity
FROM documents
WHERE vec_distance_cosine(embedding, vec_f32(:query)) < 0.5
ORDER BY similarity
LIMIT 10;

-- Automatically includes authenticated username
SELECT * FROM user_documents 
WHERE owner = :authenticated_user;

-- Audit trail with automatic user tracking
INSERT INTO actions (user, action, timestamp)
VALUES (:authenticated_user, :action, datetime('now'));

PRAGMA foreign_keys = ON;      -- Enable referential integrity
PRAGMA journal_mode = WAL;     -- Write-Ahead Logging for concurrency
PRAGMA synchronous = NORMAL;   -- Balance safety and speed
PRAGMA cache_size = -2000;     -- 2MB cache
PRAGMA page_size;              -- Query page size
PRAGMA encoding;               -- Database encoding

-- Find potential duplicates
WITH candidates AS (
  SELECT 
    d1.id as id1, 
    d2.id as id2,
    d1.title as title1,
    d2.title as title2,
    vec_distance_cosine(d1.embedding, d2.embedding) as similarity
  FROM documents d1
  JOIN documents d2 ON d1.id < d2.id
  WHERE similarity < 0.1  -- Very similar
)
SELECT * FROM candidates
ORDER BY similarity;

-- Search across title and content with different weights
WITH query_emb AS (
  SELECT vec_f32(generate_embedding(:query)) as emb
)
SELECT 
  title,
  content,
  (
    0.7 * vec_distance_cosine(title_embedding, query_emb.emb) +
    0.3 * vec_distance_cosine(content_embedding, query_emb.emb)
  ) as weighted_similarity
FROM documents, query_emb
WHERE weighted_similarity < 0.5
ORDER BY weighted_similarity
LIMIT 20;

-- Find documents similar to documents similar to a query
WITH first_pass AS (
  SELECT id, embedding
  FROM documents
  WHERE vec_distance_cosine(embedding, vec_f32(:query)) < 0.3
  LIMIT 10
)
SELECT DISTINCT d.id, d.title
FROM documents d
JOIN first_pass fp 
  ON vec_distance_cosine(d.embedding, fp.embedding) < 0.4
WHERE d.id NOT IN (SELECT id FROM first_pass)
ORDER BY vec_distance_cosine(d.embedding, vec_f32(:query))
LIMIT 20;

-- Prioritize recent + relevant
SELECT 
  title,
  created_at,
  vec_distance_cosine(embedding, vec_f32(:query)) as relevance,
  (julianday('now') - julianday(created_at)) as age_days,
  (
    0.7 * vec_distance_cosine(embedding, vec_f32(:query)) +
    0.3 * (1.0 / (1.0 + (julianday('now') - julianday(created_at)) / 365.0))
  ) as score
FROM documents
WHERE relevance < 0.5
ORDER BY score
LIMIT 10;

{
  "input": {
    "sql": "SELECT * FROM users WHERE age > :min_age",
    "database": "myapp.db",
    "bindings": {"min_age": 18},
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

{
  "input": {
    "sql": "SELECT * FROM documents WHERE owner = :authenticated_user",
    "database": "@user_data/docs.db",
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

{
  "input": {
    "sql": "SELECT title, vec_distance_cosine(embedding, vec_f32(:query)) as sim FROM docs WHERE sim < 0.5 ORDER BY sim LIMIT 5",
    "database": "knowledge.db",
    "bindings": {
      "query": {"_embedding_text": "How do I reset my password?"}
    },
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

{
  "input": {
    "sql": "UPDATE articles SET embedding = vec_f32(generate_embedding(content)) WHERE embedding IS NULL",
    "database": "@user_data/articles.db",
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

{
  "operation_was_successful": true,
  "error_message_if_operation_failed": null,
  "rows_modified_by_operation": 5,
  "column_names_in_result_set": ["id", "title", "similarity"],
  "data_rows_from_result_set": [
    {"id": 1, "title": "Getting Started", "similarity": 0.12},
    {"id": 5, "title": "Quick Start Guide", "similarity": 0.18},
    ...
  ]
}

# Access by column name
row['title']
row['created_at']

# Or by index
row[0]
row[1]

# Iterate over columns
for key in row.keys():
    print(f"{key}: {row[key]}")

-- Create index on vector column for faster similarity search
CREATE INDEX idx_doc_embedding ON documents(embedding);

-- Partial index for active documents only
CREATE INDEX idx_active_embedding 
ON documents(embedding) 
WHERE status = 'active';

-- Use CTE to avoid recalculating query embedding
WITH query_vec AS (
  SELECT vec_f32(generate_embedding(:query)) as emb
)
SELECT title, vec_distance_cosine(embedding, query_vec.emb) as sim
FROM documents, query_vec
WHERE sim < 0.5
ORDER BY sim;

-- Batch insert with single embedding generation per unique text
INSERT INTO documents(title, content, embedding)
SELECT 
  title,
  content,
  vec_f32(generate_embedding(content))
FROM staging_documents;

-- Enable Write-Ahead Logging
PRAGMA journal_mode = WAL;

-- Allows concurrent reads during writes
-- Significantly improves multi-user performance

-- Encode and decode
SELECT from_i64bes(to_i64bes(-12345));  -- Returns: -12345
SELECT from_f64bes(to_f64bes(3.14159)); -- Returns: 3.14159
SELECT from_i64bes(to_t64bes());        -- Returns: current epoch microseconds

-- Create table with sortable timestamp
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  created_at BLOB NOT NULL,  -- Stores to_t64bes()
  event_type TEXT,
  data TEXT
);

-- Insert with automatic timestamp
INSERT INTO events (created_at, event_type, data)
VALUES (to_t64bes(), 'user_login', '{"user_id": 123}');

-- Query in chronological order (BLOB comparison = time order!)
SELECT 
  id,
  from_i64bes(created_at) as epoch_us,
  datetime(from_i64bes(created_at) / 1000000, 'unixepoch') as human_time,
  event_type
FROM events
ORDER BY created_at;  -- Sorts chronologically!

-- Range query (last hour)
SELECT * FROM events
WHERE created_at > to_i64bes((strftime('%s', 'now') - 3600) * 1000000);

-- Store sensor readings compactly
CREATE TABLE sensor_data (
  sensor_id INTEGER,
  timestamp BLOB,      -- 8 bytes (to_t64bes)
  temperature BLOB,    -- 4 bytes (to_f32bes) 
  humidity BLOB,       -- 2 bytes (to_u16bes, 0-65535 = 0.00-655.35%)
  PRIMARY KEY (sensor_id, timestamp)
);

-- Insert reading
INSERT INTO sensor_data VALUES (
  1,
  to_t64bes(),
  to_f32bes(23.5),
  to_u16bes(6520)  -- 65.20%
);

-- Query with decoded values
SELECT 
  sensor_id,
  from_i64bes(timestamp) as ts,
  from_f32bes(temperature) as temp_c,
  from_u16bes(humidity) / 100.0 as humidity_pct
FROM sensor_data
WHERE sensor_id = 1
ORDER BY timestamp DESC
LIMIT 100;

-- Demonstrate correct sorting of signed integers
CREATE TABLE signed_test (val BLOB);
INSERT INTO signed_test VALUES 
  (to_i64bes(-1000)),
  (to_i64bes(-1)),
  (to_i64bes(0)),
  (to_i64bes(1)),
  (to_i64bes(1000));

-- ORDER BY BLOB gives correct numerical order!
SELECT from_i64bes(val) as decoded FROM signed_test ORDER BY val;
-- Returns: -1000, -1, 0, 1, 1000 (correct!)

-- Demonstrate correct sorting of floats (including negatives)
CREATE TABLE float_test (val BLOB);
INSERT INTO float_test VALUES 
  (to_f64bes(-1000.5)),
  (to_f64bes(-0.001)),
  (to_f64bes(0.0)),
  (to_f64bes(0.001)),
  (to_f64bes(1000.5));

-- ORDER BY BLOB gives correct numerical order!
SELECT from_f64bes(val) as decoded FROM float_test ORDER BY val;
-- Returns: -1000.5, -0.001, 0.0, 0.001, 1000.5 (correct!)

-- Combine keyword matching with semantic similarity
SELECT 
  d.title,
  d.content,
  vec_distance_cosine(d.embedding, vec_f32(:query)) as semantic_sim,
  (d.title LIKE '%' || :keyword || '%' OR d.content LIKE '%' || :keyword || '%') as keyword_match
FROM documents d
WHERE semantic_sim < 0.5 OR keyword_match = 1
ORDER BY 
  keyword_match DESC,  -- Exact matches first
  semantic_sim ASC     -- Then by semantic similarity
LIMIT 20;

-- Search within category with semantic ranking
SELECT 
  title,
  category,
  vec_distance_cosine(embedding, vec_f32(:query)) as relevance
FROM documents
WHERE 
  category = :category
  AND relevance < 0.5
ORDER BY relevance
LIMIT 10;

-- Find clusters of similar documents
WITH similarities AS (
  SELECT 
    d1.id as id1,
    d2.id as id2,
    vec_distance_cosine(d1.embedding, d2.embedding) as distance
  FROM documents d1
  CROSS JOIN documents d2
  WHERE d1.id < d2.id
    AND distance < 0.2
)
SELECT id1, id2, distance
FROM similarities
ORDER BY distance;

Cause: Missing vec_f32() wrapper in SQL
Fix: Always use vec_f32() for vector parameters

Cause: Binding name doesn't match SQL parameter
Fix: Ensure :param_name matches bindings key

Cause: Model loading issue (first use) or memory pressure
Fix: Usually auto-resolves on retry. Check available RAM.

Cause: Concurrent write attempts
Fix: Use WAL mode or implement retry logic

https://www.apache.org/licenses/LICENSE-2.0