by dmfay on 4/4/21, 11:19 PM with 27 comments
by chunkyks on 4/5/21, 12:08 AM
The couple tricks it has that I really like are a) it shows the table itself [which isn't uncommon], but also b) has the ability to group up tables, which for me turns out to be a devastatingly killer feature that no other tool has.
When I'm working on a schema [especially one I've not seen before], one of the things that drives me insane is not being able to say "these six go together. Group them". Then you can simply look at arrows that enter or leave that grouping for your next exploration step. By the time I'm done, anything not-categorised stands out [it's simply in the space between groups]; any boxes that have an excess of arrows between them can mean my mental model is wrong [or the schema has some nonoptimalities].
There's an example here; it only has two groupings ["input" and "output"], but it immediately gets the point across: https://icculus.org/~chunky/stuff/sqlite3_example/sqlitesche...
by simonw on 4/5/21, 3:51 AM
https://observablehq.com/@simonw/datasette-table-diagram
It defaults to showing the tables from my GitHub example database but you can paste in the URL to another Datasette to see that one visualized instead - try https://latest.datasette.io/fixtures for example
by tracyhenry on 4/5/21, 3:26 AM
Hopefully the UI is self-explanatory - if not try clicking on visual objects to perform drill down, or search something in the top righthand corner.
The underlying dataset is a public DBMS called MONDIAL: https://www.dbis.informatik.uni-goettingen.de/Mondial/mondia...
by meesterdude on 4/5/21, 2:45 AM
by pm90 on 4/5/21, 12:22 AM
by _wldu on 4/5/21, 1:09 AM
Also, graphviz is really cool.
by JoelJacobson on 4/5/21, 1:20 PM
Too bad they were not invented until SQL-89, and could thus not be considered when the JOIN syntax we all know was designed and formalized by ANSI in SQL-86.
Imagine if we could simply give the name of a foreign key, as an argument to JOIN, letting the query planner look up the foreign key columns, to do the join on.
The default names for foreign keys are not very user-friendly, due to the SQL standard unfortunate decision to require the foreign key name to be unique per namespace, and not just per table, which would have made more sense.
In PostgreSQL, foreign keys just need to be unique per table, so there we could give them names such as "customer" instead of "rental_customer_id_fkey".
Imagine if we instead of:
SELECT
language.name,
COUNT(*)
FROM rental
JOIN customer
ON customer.customer_id = rental.customer_id
JOIN address
ON address.address_id = customer.address_id
JOIN city
ON city.city_id = address.city_id
JOIN country
ON country.country_id = city.country_id
JOIN inventory
ON inventory.inventory_id = rental.inventory_id
JOIN film
ON film.film_id = inventory.film_id
JOIN language
ON language.language_id = film.language_id
WHERE country.country = 'Sweden'
GROUP BY language.name
SELECT
language.name,
COUNT(*)
FROM rental
JOIN rental->rental_customer_id_fkey AS customer
JOIN customer->customer_address_id_fkey AS address
JOIN address->address_city_id_fkey AS city
JOIN city->city_country_id_fkey AS country
JOIN rental->rental_inventory_id_fkey AS inventory
JOIN inventory->inventory_film_id_fkey AS film
JOIN film->film_language_id_fkey AS language
WHERE country.country = 'Sweden'
GROUP BY language.name
ALTER TABLE rental RENAME CONSTRAINT rental_customer_id_fkey TO customer;
ALTER TABLE rental RENAME CONSTRAINT rental_inventory_id_fkey TO inventory;
ALTER TABLE customer RENAME CONSTRAINT customer_address_id_fkey TO address;
ALTER TABLE address RENAME CONSTRAINT address_city_id_fkey TO city;
ALTER TABLE city RENAME CONSTRAINT city_country_id_fkey TO country;
ALTER TABLE inventory RENAME CONSTRAINT inventory_film_id_fkey TO film;
ALTER TABLE film RENAME CONSTRAINT film_language_id_fkey TO language;
SELECT
language.name,
COUNT(*)
FROM rental
JOIN rental->customer
JOIN customer->address
JOIN address->city
JOIN city->country
JOIN rental->inventory
JOIN inventory->film
JOIN film->language
WHERE country.country = 'Sweden'
GROUP BY language.name
SELECT
language.name,
COUNT(*)
FROM rental
JOIN rental->customer->address->city->country
JOIN rental->inventory->film->language
WHERE country.country = 'Sweden'
GROUP BY language.name
Q: How would we name foreign keys if there are two going to the same table?
A: Imagine having a "users" table with two columns "child_user_id" and "parent_user_id", both referencing "users". The foreign keys on such columns could simply be named "child" and "parent".