lana-k/sqliteviz

Instant offline SQL-powered data visualisation in your browser

sqliteviz's Issues

[RFE] Clipboard interactions

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:

1. Past CSV (table structure you copy from Libre Office Calc, Google Sheets, etc) for import (probably in a text area because JS isn't allowed to examine the clipboard on it's own)
2. Copy result set as a CSV (table structure that the spreadsheet editors recognise; saving as a file also makes sense by the way)
3. Copy PNG from Plotly

Pre-defined query file should be empty

Virtual tables are not displayed

No db in schema sidebar

• Add drag and drop
• Add warning

Add tooltips

• predefined badge
• query icons (rename, copy, ...)

Add "Save as" button

add ctrl+enter for running queries

Github actions for release build

Allow (partial) import of CSV with errors

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

Save dialog remembers the name for another tab

[RFE] Presentation mode

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).

Add table filter in schema sidebar

Tiny SVGs are not embedded

CSV import error: File is not a database

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.

License file is missing in the build

Add hotkeys

run, save, create

[RFE] Graph visualisation

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, sql-js/sql.js#440.

'>>>' should be deleted?

sqliteviz/src/views/Main/Editor/Tabs/Tab/Chart/index.vue

Line 95 in cc483f4

>>> .editor_controls .sidebar__item:before {

Desktop PWA

Concurrent query execution for different tabs

Update schema view after script running

[RFE] Support vector graphic chart download

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.

Export db

Wrong trigger of autocomplete

Fix plotly settings panel overflow

Search table raises toUpperCase of undefined

SQL editor issues

• Fat cursor fitness
• SQL editor should use monospace font

Create illustrations and logo

App diagnostic dialog

In order to let users understand what SQLite/SQL features/extentions they have, show the following:

• sqliteviz version
• sqlite version
• PRAGMA compile_options

Cancelling queries

Add schema tokens in codemirror autocomplit

Rename connection to loading

[RFE] Presentation of an remote database and sharing

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 database
• inquiries is a (CORS-enabled) URL to a Sqliteviz JSON inquiry file (like inquiries.json)
• id is a visualisation is the inquiries file

To 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:

• d3.js' bl.ocks.org (https://bl.ocks.org/mbostock/3231298, https://gist.github.com/mbostock/3231298) -- GitHub Gists are used to store code, data and metadata
• DBHub (sample) -- a la GitHub for (SQLite) databases,
• Commercial services: observablehq.com, datawrapper.de

Pivot table visualisation

https://react-pivottable.js.org/

Add csv to existing db

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.

Show result of the last query in a script, not the first one

Make a table/chart area collapsable

[RFE] Skiping database load should create an empty database

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.

Warn about unsaved tab(s) when closing the browser tab

CSV support

Add window.onstorage handler

The problem: if a user changes saved queries in one browser tab the changes won't be visible in another tab until refresh.

Add + button in tabs

First load loading indicator

Changes of chart title are not detected

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.

Support all CSV media types

Empty state for my queries

[RFE] Custom sql.js build with newer SQLite and a few extensions

Use cases

Math functions

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

Generating matrix for Plotly surface plot

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       

WASM build of SQLite

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.

SQL query progress and better error output

• use Logs component for errors
• show execution indicator for long execution (after 1 second)

Upload validation

• Database upload
• JSON import

Choose licence