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;

I created this by sending GPT-4 the following prompt:

-- 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 data into the 'nodes' table in SQLite
INSERT INTO nodes (data) VALUES ('Bob');
INSERT INTO nodes (data) VALUES ('Hank');
INSERT INTO nodes (data) VALUES ('Jeff');
INSERT INTO nodes (data) VALUES ('Sally');
INSERT INTO nodes (data) VALUES ('Sue');
INSERT INTO nodes (data) VALUES ('Sam');

-- Insert data into the 'edges' table in SQLite
INSERT INTO edges (previous_node, next_node) VALUES (1, 2);
INSERT INTO edges (previous_node, next_node) VALUES (1, 3);
INSERT INTO edges (previous_node, next_node) VALUES (2, 4);
INSERT INTO edges (previous_node, next_node) VALUES (3, 4);
INSERT INTO edges (previous_node, next_node) VALUES (4, 5);

Rewrite that SQL to use a succinct SQLite recursive query to populate the nodes and edges tables
with random data - one million row in each table

Then following up with:

I got an error about a unique constraint violated on the edges table, rewrite that SQL so that any
existing rows are ignored rather than causing that error

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