[RFE] Graph visualisation

[saaj]

As a user of Sqliteviz, In order to study structure and/or dynamics of complex networks (e.g. biological or infrastructure), I want to be able to visualise a graph (a set of vertices and edges).

Candidate JavaScript graph libraries:

1. sigma.js
2. cytoscape.js

For both the data model looks roughly like (sans styling, layout, etc):

{
  "nodes": [
    {"id": "n0", "label": "A node"},
    {"id": "n1", "label": "Another node"},
    {"id": "n2", "label": "And a last one"}
  ],
  "edges": [
    {"id": "e0", "source": "n0", "target": "n1"},
    {"id": "e1", "source": "n1", "target": "n2"},
    {"id": "e2", "source": "n2", "target": "n0"}
  ]
}

This can be mapped to SQLite resultset structure like this.

CREATE TABLE "node" (
	"node_id"	INTEGER NOT NULL,
	"label"	    TEXT,
	PRIMARY KEY("node_id" AUTOINCREMENT)
);
CREATE TABLE "edge" (
	"edge_id"	INTEGER NOT NULL,
	"source_id"	INTEGER NOT NULL,
	"target_id"	INTEGER NOT NULL,
	PRIMARY KEY("edge_id" AUTOINCREMENT),
	FOREIGN KEY("source_id") REFERENCES "node"("node_id"),
	FOREIGN KEY("target_id") REFERENCES "node"("node_id")
);

INSERT INTO "node" VALUES (1,'A node');
INSERT INTO "node" VALUES (2,'Another node');
INSERT INTO "node" VALUES (3,'And a last one');
INSERT INTO "edge" VALUES (1,1,2);
INSERT INTO "edge" VALUES (2,2,3);
INSERT INTO "edge" VALUES (3,3,1);                  

SELECT 'e' "type", edge_id, source_id, target_id, json_object('foo', 1) "properties"
FROM edge
UNION
SELECT 'n' "type", node_id, NULL, NULL, json_object('label', label) "properties"
FROM node

json_object required JSON1 extension, which should be included by default in recent official builds of sql.js, https://github.com/sql-js/sql.js/pull/440.

[saaj]

Here's a Hacker News discussion on dpapathanasiou/simple-graph (which goes all over the place and can be a good overview) which has similar approach to storing graphs in SQLite (document-oriented upfront, its schema.sql). Another library's schema mentioned there.

[twoxfh]

@lana-k is graphing outside the scope of this project? I do not see plotly supporting this, but maybe I'm wrong.

[lana-k]

@twoxfh there are no graphs in plotly indeed, but that doesn't mean that this issue is outside the scope of sqliteviz. Sqliteviz is supposed to support different kinds of visualisation in future including graphs.

[twoxfh]

@lana-k sounds good, I would like to chime in on graphviz which is my preference. Also may want to consider filtering at the table level which pushes updates to the various visualizationa

[saaj]

The workflow w.r.t. filtering should stay as is for any new visualisation components. 1) Drop database/CSV, 2) write SQL to produce desired result set, 3) explore the result set with the visualisation of choice. The latter should stay GUI way (as the users aren't expected to know more languages but SQL) so I don't see how dot language fits here.

[twoxfh]

Not sure how that will be accomplished with the other libraries.