big-graph.sql

-- Create the 'nodes' table in SQLite
CREATE TABLE nodes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  data TEXT
);

-- Create the 'edges' table in SQLite
CREATE TABLE edges (
  previous_node INTEGER REFERENCES nodes(id),
  next_node INTEGER REFERENCES nodes(id),
  PRIMARY KEY (previous_node, next_node)
);

-- Insert random data into the 'nodes' table in SQLite
WITH RECURSIVE
  random_data(cnt, rnd) AS (
    SELECT 1, (abs(random()) % 1000000) || '_name'
    UNION ALL
    SELECT cnt+1, (abs(random()) % 1000000) || '_name'
    FROM random_data
    WHERE cnt < 1000000
  )
INSERT INTO nodes(data)
  SELECT rnd FROM random_data;

-- Insert random data into the 'edges' table in SQLite
WITH RECURSIVE
  random_edges(cnt, previous_node, next_node) AS (
    SELECT 1, abs(random()) % 1000000 + 1, abs(random()) % 1000000 + 1
    UNION ALL
    SELECT cnt+1, abs(random()) % 1000000 + 1, abs(random()) % 1000000 + 1
    FROM random_edges
    WHERE cnt < 1000000
  )
INSERT OR IGNORE INTO edges(previous_node, next_node)
  SELECT previous_node, next_node FROM random_edges;

Example query: https://lite.datasette.io/?sql=https://gist.github.com/simonw/c16ce01244760e186a3a0aa3fee0405d#/data?sql=WITH+RECURSIVE+friend_of_friend+AS+%28%0A++SELECT+edges.next_node%0A++FROM+edges%0A++WHERE+edges.previous_node+%3D+14%0A++UNION%0A++SELECT+edges.next_node%0A++FROM+edges%0A++JOIN+friend_of_friend+ON+edges.previous_node+%3D+friend_of_friend.next_node%0A%29%0ASELECT+nodes.data%0AFROM+nodes%0AJOIN+friend_of_friend+ON+nodes.id+%3D+friend_of_friend.next_node%3B

Related Hacker News thread: https://news.ycombinator.com/item?id=35386948