Build Systems

 1-- Format output to display columns vertically
 2.mode line
 3
 4-- Load Learn posts
 5CREATE TABLE posts AS 
 6SELECT * FROM read_json_auto('https://quantile.co/learn/data.json');
 7
 8-- Install and load full-text search extension
 9INSTALL fts;
10LOAD fts;
11
12-- Create FTS index on searchable fields
13-- Syntax: table_name, id_column, column1, column2, ...
14PRAGMA create_fts_index('posts', 'url', 'title', 'subtitle', 'content', overwrite = 1);
15
16-- Example queries:
17
18-- 1. Full-text search for 'bazel'
19SELECT title, url, date, tags, fts_main_posts.match_bm25(url, 'bazel') as score
20FROM posts 
21WHERE score IS NOT NULL
22ORDER BY score DESC
23LIMIT 10;
24
25-- 2. Filter by specific tag (case-insensitive)
26SELECT title, url, date, tags 
27FROM posts 
28WHERE list_has(list_transform(tags, x -> lower(x)), lower('Observability'))
29ORDER BY date DESC;
30
31-- 3. Posts from last 30 days
32SELECT title, url, date, tags 
33FROM posts 
34WHERE CAST(date AS TIMESTAMP) >= CURRENT_DATE - INTERVAL 30 DAY
35ORDER BY date DESC;