SQLite Graph

graph.sql

/*
 * A directed graph in SQLite
 * Designed to work on any version, binding, and basic featureset
*/

/*
 * nodes/edges table creation with constraints
 * No use of FOREIGN KEY
 * Indexes, etc...
 */

CREATE TABLE IF NOT EXISTS "nodes" (
-- Each edge is given a numerical id
	"id" INTEGER PRIMARY KEY AUTOINCREMENT,
-- Each node is given a label
	"label" TEXT NOT NULL UNIQUE,
-- Count of how many times this node has occurred
  "ct" INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS "edges" (
-- Each edge is given a numerical id
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
-- source node label of the edge
  "src" NOT NULL,
-- destination node label of the edge
  "dst" NOT NULL,
-- Each edge can be given a label
  "label" TEXT,
-- Count of how many times this edge has occurred
  "ct" INTEGER NOT NULL DEFAULT 1,
-- Ensure that each ordered pair edge is unqiue
  UNIQUE("src", "dst")
);
CREATE INDEX IF NOT EXISTS edges_src_idx ON edges(src);
CREATE INDEX IF NOT EXISTS edges_dst_idx ON edges(dst);
CREATE INDEX IF NOT EXISTS edges_label_idx ON edges(label);

/*
 * The following is meant for easy programatic data-binding.
 * Copy and paste from DATABINDING_START to DATABINDING_END
 * and bind src_label, dst_label, edge_label
 */

-- DATABINDING_START
CREATE TEMP TABLE rel (
    src_label  TEXT,
    dst_label  TEXT,
    edge_label TEXT
);
-- Bind these as (?, ?, ?)
INSERT INTO rel VALUES ('fromHere', 'toThere', 'Test');
-- Ensure nodes exist
INSERT OR IGNORE INTO nodes(label)
SELECT src_label FROM rel;
INSERT
	OR IGNORE
INTO
	nodes(label)
SELECT
	dst_label
FROM
	rel;
-- Increment node counters
UPDATE nodes
SET ct = ct + 1
WHERE label IN (
    SELECT src_label FROM rel
    UNION ALL
    SELECT dst_label FROM rel
);
-- Ensure edge exists
INSERT OR IGNORE INTO edges (src, dst, label)
SELECT
    (SELECT id FROM nodes WHERE label = rel.src_label),
    (SELECT id FROM nodes WHERE label = rel.dst_label),
    rel.edge_label
FROM rel;
-- Increment edge counter
UPDATE edges
SET ct = ct + 1
WHERE src = (SELECT id FROM nodes WHERE label = (SELECT src_label FROM rel))
  AND dst = (SELECT id FROM nodes WHERE label = (SELECT dst_label FROM rel));
DROP TABLE rel;
-- DATABINDING_END

/*
 * Peek at the data and have some fun
 */
SELECT * from nodes n;
SELECT * FROM edges e;