lana-k / sqliteviz Goto Github PK
View Code? Open in Web Editor NEWInstant offline SQL-powered data visualisation in your browser
Home Page: https://sqliteviz.com
License: Apache License 2.0
Instant offline SQL-powered data visualisation in your browser
Home Page: https://sqliteviz.com
License: Apache License 2.0
I have a sample CSV file:
name, points
Gryffindor, 100
Hufflepuff, 90
Ravenclaw, 95
Slytherin, 80
When I try to import the file I receive the error "File is not a database". I've tried with and without headers. Quotes and no quotes.
As a educational user of Sqliteviz,
In order to use SQLite to generate mathematical function series and graphs them,
I want to be able to call functions like sin
, cos
etc.
SQLite has math functions, but:
The math functions shown below are part of the SQLite amalgamation source file but are only active if the amalgamation
is compiled using the-DSQLITE_ENABLE_MATH_FUNCTIONS
compile-time option.
Turns out there's another extension mechanism used in sql-js' Makefile
to enable the mathematical function (which apparently was used before 3.35.0 2021-03-12 introduced SQLITE_ENABLE_MATH_FUNCTIONS
):
EXTENSION_FUNCTIONS = extension-functions.c
EXTENSION_FUNCTIONS_URL = https://www.sqlite.org/contrib/download/extension-functions.c?get=25
The C file contains this description (and these could be useful in the auto-complete.):
This library will provide common mathematical and string functions in
SQL queries using the operating system libraries or provided
definitions. It includes the following functions:
Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference,
degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp,
log, log10, power, sign, sqrt, square, ceil, floor, pi.
String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim,
replace, reverse, proper, padl, padr, padc, strfilter.
Aggregate: stdev, variance, mode, median, lower_quartile,
upper_quartile.
The string functions ltrim, rtrim, trim, replace are included in
recent versions of SQLite and so by default do not build.
A series can be generated by a recursive CTE without creating a table.
WITH RECURSIVE series(x) AS (
SELECT 0
UNION ALL
SELECT x + 0.01
FROM series
WHERE x + 0.01 <= 2 * pi()
)
SELECT x, sin(x) y
FROM series
There's simpler way to do it, with generate_series
table-valued function, but:
The generate_series(START,END,STEP) table-valued function is a loadable extension included in the SQLite source tree [...]
It can be used like like:
SELECT value FROM generate_series(5,100,5)
Or
SELECT random() FROM generate_series LIMIT 20
As a user of Sqliteviz,
In order to visualise 3D surface,
I want to be able to select the pivot/matrix table that Plotly 3D surface plot expects.
There's SQLite pivot_vtab virtual table extension (see building and running example in this SO answer).
.load ./pivot_vtab
.header on
.mode column
CREATE TABLE point(x REAL, y REAL, z REAL);
INSERT INTO point VALUES
(5,3,3.2),
(5,6,4.3),
(5,9,5.4),
(10,3,4),
(10,6,3.8),
(10,9,3.6),
(15,3,4.8),
(15,6,4),
(15,9,3.5);
CREATE VIRTUAL TABLE temp.pivot USING pivot_vtab(
(SELECT y FROM point GROUP BY y),
(SELECT x, x FROM point GROUP BY x),
(SELECT z FROM point WHERE y = ?1 AND x = ?2)
);
-- A SQL hack to "attach" unique X values as a column
SELECT xt.x, p.*
FROM (
SELECT row_number() OVER () rownum, *
FROM temp.pivot
) p
JOIN (
SELECT row_number() OVER () rownum, x
FROM point
GROUP BY x
) xt USING(rownum);
It produces:
x rownum y 5.0 10.0 15.0
---------- ---------- ---------- ---------- ---------- ----------
5.0 1 3.0 3.2 4.0 4.8
10.0 2 6.0 4.3 3.8 4.0
15.0 3 9.0 5.4 3.6 3.5
According to this issue sql-js/sql.js#342, loadable extension can also be pre-built. And here's SQLite's Statically Linking A Run-Time Loadable Extension.
The problem: if a user changes saved queries in one browser tab the changes won't be visible in another tab until refresh.
As a user of Sqliteviz,
In order to manually create a database using DDLs and DMLs,
I want to be able to have empty database created when I don't load one.
New tab can be opened with CREATE TABLE test(a INT, b TEXT)
or something like that.
As a user of Sqliteviz,
In order to share my data and its visualisation(s) with others,
I want to be able to import/download external data and the inquiry by just following a URL to Sqliteviz, and to export/upload mine.
The URL may look something like (respective to the instance can also be localhost:8080
and so on):
https://lana-k.github.io/sqliteviz/?database=...&inquiries=...&id=id-of-the-viz-in-the-file
Where:
database
is a (CORS-enabled) URL to a SQLite databaseinquiries
is a (CORS-enabled) URL to a Sqliteviz JSON inquiry file (like inquiries.json
)id
is a visualisation is the inquiries
fileTo make the user interaction smooth there can be an integration with GitHub Gist to automatically upload the database and config. Conversely it can be useful to also be able to open/import a GitHub Gist. CORS seems to be there as I can see Access-Control-Allow-Origin: *
there.
As a similar solution for reference:
Hello!
I got some errors when importing a CSV, and those stopped me from importing the rest of it. Would it be possible to allow a partial import, skipping the rows that couldn't be parsed?
Thanks,
Ben
As a user of sqliteviz,
In order to speed up introp with other local software,
I want to be able to copy and paste into/from sqliteviz.
It's mostly about tiny data volumes and a small convenience of interaction with sqliteviz. For instance you copy something from a spreadsheet, instead of moving it to another sheet, saving as CSV and drag'n'dropping to sqliteviz. Or export PNG with a chart to share it in a chat app.
What on the surface:
run, save, create
In order to let users understand what SQLite/SQL features/extentions they have, show the following:
onRender(data, layout, frames)
event handler is needed for plotly. It's called every time a user edit the right part of plotly editor.
But it also called when plotly just resized. So the changes need to be checked in order to decide enable Save button or not.
Because CSV has only one table a user can't get data from a join of different CSV. It would be useful to have an opportunity to load CSV as a new table of the existing db.
As a user of Sqliteviz,
In order to demonstrate a prepared visualisation(s),
I want to be able to hide all other controls and keep only the chart(s) on the screen.
In general the separate "edit" and "presentation" seem to makes sense in the context of Sqliteviz.
In case of multiple open tabs, it may a good idea to have a keyboard key for navigation between them (e.g. arrow keys).
As a user of Sqliteviz,
In order to present/print a high-fidelity visualisation,
I want to be able to export the Plotly visualisation as SVG.
Here's an example how to customise download options. Keeping both options, PNG and SVG, makes sense in my opinion. Here's a description of how to add buttons on the ModeBar.
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:
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, sql-js/sql.js#440.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.