SQLite Database Visualisation Project#

1. Technical Documentation#

(for maintainers & contributors)

1.1 Overview#

This project lets ordinary Python sqlite3 code run inside a Pyodide worker and persist across page reloads and different tabs using the browser’s IndexedDB.
A Vue + Pinia front-end then renders each database as an interactive graph with D3-force on an HTML <canvas>.

1.2 Architecture#

Layer	Main Method / File / Class	Responsibility
Pyodide Worker	`helpers.patch_sqlite3()`	Replaces `sqlite3.connect()` with `CustomConnection` that loads/saves binary blobs in IndexedDB.
Bridge	`pyodideStore.ts`	Sends messages (`sql`, `sql_load`, …) between the worker and the UI.
Persistence	`SQLiteService.ts`	Simple wrapper around IndexedDB: `saveDatabase`, `loadDatabase`, `exportDatabase`.
State & Tabs	`canvasDatabaseStore.ts`	One tab + one `DatabaseD3Handler` per DB; survives Vue unmount/mount.
Rendering	`DatabaseD3Handler.ts`	Pure‑canvas graph: drag, zoom, Foreign Key highlight, mini / big / full modes of nodes that represents tables

1.3 Runtime Flow#

Python calls sqlite3.connect("myDB.db").
CustomConnection loads the last snapshot (once) and memorises the DB name.
commit() triggers persist_to_indexeddb() → backs up the in‑memory DB to a temp file and sends binary via runner.callback("sql", …).
Web Worker receives the message → pyodideStore.handleMain("sql")
- Saves blob to IndexedDB.
- Adds/activates a DB tab.
- Invokes displayDatabaseInCanvas() to introspect tables & foreign keys.
Canvas layer calls DatabaseD3Handler.drawTables() to (re)draw.

1.4 Persistence Guarantees#

Every commit() emits a fresh snapshot → crash‑safe.
First connection on page load restores that snapshot → tab‑safe.
Separate databases stay isolated by key name.

2. User Guide#

(for teachers and students)

2.1 What it does#

Run Python/SQLite and the database1 library in the textbook Einfach Informatik: Programmieren 7-9 entirely in your browser.
Auto‑save: each conn.commit() stores the DB in browser storage.
Visualise: tables appear as draggable cards, foreign keys as coloured cables.
Multi‑Database support: every file gets its own tab.

2.2 Quick Start#

import sqlite3
conn = sqlite3.connect("myshop.db")
cur = conn.cursor()

# create a sample table
cur.execute("""
    CREATE TABLE IF NOT EXISTS products (
        product_id INTEGER PRIMARY KEY,
        name       TEXT    NOT NULL,
        price      REAL    NOT NULL
    )
""")

# insert two rows 
cur.executemany(
    "INSERT INTO products (name, price) VALUES (?, ?)",
    [
        ("Notebook", 2.50),
        ("Pencil",   0.80),
    ]
)

conn.commit()  

Run the code. A Database canvas pops up with a MYSHOP.DB tab.

2.3 Table Card Sizes#

Size	How to open	Main purpose
Normal	Default after running Python code	View column list (icons + PK) at a glance.
Big	Click ↗ on a Normal card	Scroll through sample rows without leaving the diagram.
Full‑screen	Click ↗ again while in Big	Work with one table in detail; scrolling is easier and the code editor stays side‑by‑side.

2.2 Normal Card (Level 1) – Icons & Buttons#


Default	After clicking ⓘ

Legend of icons: 123 = numeric column, ABC = text column, 🔑 = primary key, ⓘ = toggle for column types, ↗ / ↙ = expand / shrink.*

2.3 Big Card (Level 2)#

Preview data rows directly in the diagram. Mouse‑wheel scrolls vertically; Shift + wheel scrolls horizontally. Use ↗ to go Full‑screen or ↙ to return to Normal.*

2.4 Full‑screen Card (Level 3)#

Feature	Benefit
Canvas fills right pane	Plenty of room for long tables while your Python code (left) remains visible.
Independent scrolling	Scroll vertically to browse rows; hold `Shift` to scroll horizontally through many columns.
Floating controls	↙ returns to Big size

2.5 Foreign‑Key Highlighting (Green & Blue)#


1· Click on `users`: outbound links turn green	2· Click on `products`: inbound links turn blue

Colour	Meaning	Example SQL that triggers the colour
Green	Outgoing FK – the clicked table references another table (link originates here).	`FOREIGN KEY (user_id) REFERENCES users(user_id)` `FOREIGN KEY (product_id) REFERENCES products(product_id)` When you click orders, these two columns / cables turn green.
Blue	Incoming FK – another table references the clicked table (link terminates here).	Reference from `orders.product_id → products.product_id` lights up blue when you click products.

2.6 Typical Workflow#

Design schema using Normal cards.
Check the data in Big mode.
Inspect deeply in Full‑screen mode (Level 3) while editing Python side‑by‑side.
Press ↙ as needed to step back.

3 · Working with `database1`#

Unlike the SQLite workflow, database1 lets you use the features described in the Einfach Informatik textbook.

from database1 import *

3.1 API cheatsheet#

Methods on `Database`#

Method	In Einfach Informatik?	What it does
`execute(sql)`	—	Run a raw SQL string against this mini-DB.
`getTables()`	—	Return a list of all table names.
`getVariables()`	—	Return a list of all variable names.
`removeTable(name)`	—	Drop the table called `name`.

Methods on `Table`#

Method	In Einfach Informatik?	What it does
`append(*values)`	Yes	Insert one row (values are positional).
`len()`	Yes	Return the number of rows.
`remove(value)`	Yes	Delete the first row that matches `value`.
`tableInfo()`	—	Return name, column count, column names & row count.
`addColumn(name)`	—	Add a new column.
`removeColumn(name)`	—	Delete a column.
`renameColumn(old, new)`	—	Rename a column.

Everything else—dragging and expanding behaves just like it does for SQLite tables.

3.2 How `database1` objects appear on the canvas#

Object type	Colour	Size levels	Notes
Normal table (`Table`)	light grey	Normal · Big · Full-screen	Same icons and buttons as SQLite tables.
Variable node (`DB.x = 0`)	light green	Normal · Big	Rendered as a single-row table with one column `value`. No Full‑screen level because the node shows only one value.


Normal table — Normal view	Normal table — Big view

Variable node — Normal view	Variable node — Big view

Runtime behaviour

Creating Database("meine_datenbank") immediately opens a MEINE_DATENBANK tab.
Adding a Table or assigning a variable (DB.x = 42) triggers a live canvas update.

SQLite Database Visualisation Project

Contents