search

sqlectron / sqlectron-gui

A simple and lightweight SQL client desktop with cross database and platform support.
https://sqlectron.github.io/
MIT License
4.51k stars 527 forks source link

Support for database diagram #165

Closed BornaP closed 8 years ago

BornaP commented 8 years ago

Okay, so here's the idea which I'll try to implement (as it is one of features my mentor requests me to do, and I guess it would be really nice one to have!). I was thinking of making database diagramming component which would produce database visualization based on database tables, views and their references (primary and foreign keys! -> this will need some smaller extension of back-end API). The idea is to spit out simple HTML tables connected with related tables. Furthermore, user would be able to reopen diagram once it's already generated (so some simple JSON with coordinates would be created upon generating diagram). I was searching around for suitable library and found JointJS and JS-graph-it to be probably the best choice for this use case. Both mentioned libraries have things we need - simple API, connecting custom HTML elements (which would be our tables/views), draggable nodes and connections. Of course, if anyone has better library to propose, please do.

TL;DR: I'll try to build support for creating database diagrams and storing them so user can reopen already existing diagrams.

EDIT: Think I'll go with JointJS as it is hosted on npm, supports nesting children elements inside of a parent (i.e. each table is a parent element with sub elements for each attribute), offers a rich API and supports graph export to JSON and reading it from JSON, so persisting the graph object should be trivial.

BornaP commented 8 years ago

@maxcnunes @krolow @esnunes : All suggestions and opinions are welcome!

maxcnunes commented 8 years ago

This would be an awesome feature. Not sure which library is the best for ERD diagramas. Seems like JointJS has a better documentation/support. I have found this GoJS (erd example http://gojs.net/latest/samples/entityRelationship.html). But I am not sure it is free.

BornaP commented 8 years ago

GoJS is $1350 per developer :smile: But no worries, I think JointJS has everything necessary to customly build simple ERD diagram. Will give it a try!

BornaP commented 8 years ago

UPDATE: After including latest changes in sqlectron-core, this is what I got so far:

wip digram0 1

It's working on live data! (on picture it's some sample database I had locally)

Most time I 'wasted' debugging JointJS in modal window, but it works smooth now. Diagram is generated on clicking 'Show Database Diagram' option in database context menu. Since every diagram needs columns and links, I call fetchColumnsIfNeeded for every table included. Since it takes some time for more tables, here's what I was thinking in implementing next:

Also changes I've in mind:

maxcnunes commented 8 years ago

It is looking pretty cool. I agree with all your suggestions of implementation. They seem the right approach.

joeblew99 commented 8 years ago

@BornaP This is awesome. I was thinking about doing type of functionality last week.

I was wondering if you have thought about how the ERD auto layout works ? Not many people have heard of the concept of "Crows feet up" in ERDS, but i have found it to work wel with every DB schema i have worked on. Essentially it means that the things you are referencing are located on the page above the other tables.

So any lookup / enum style table like "Countries" would be above "User" tables, because Users has a FK to Countries.

The layout logic needs to counts the FK for each table and the interdependencies, and then it can work out whats above others. Horizontal layout i am not sure about.

The othr way to do this is totally disregard the auto layout feature, and let devs do it how they like, but to allow saving of the layout. If a new table is added, then it just slapped in the center in front of others. This is what SQL Server does or example ( many moons ago).

BornaP commented 8 years ago

Hey @joeblew99 ,thanks for the feedback. The 'other way' you have mentioned is actually currently implemented (the SQL Server way) in the master. New tables are added in front of the others, so devs can move them as they like and SAVE the layout (positions are saved). This is fully resolved with #192 .

I'm don't think first approach you mentioned would be good in our case as it might make use of 'paper' badly, because there might be lots of scroll vertically and lot of empty space horizontally. That's why I think its the best to let developers move tables and joints (arrow might be moved around the table and links can be pulled on any point you like). Just makes user move flexible and a lot of them are used to this because SQL Server studio does the similar thing :)

Currently I'm still working on this, next thing is marking PK and FKs on the table, enabling export to SVG and 'add related tables' feature which would enable user to add tables that current table has references to. This would provide easy way to check referential integrity constraints. I plan on finishing this withing the week, I'll keep you update on this issue.

joeblew99 commented 8 years ago

Sounds good . All of it.

What about saving the view, ? Json format is fine. You could save the view in the a tual dB.

On Sun, 12 Jun 2016, 09:56 Borna Perak, notifications@github.com wrote:

Hey @joeblew99 https://github.com/joeblew99 ,thanks for the feedback. The 'other way' you have mentioned is actually currently implemented (the SQL Server way) in the master. New tables are added in front of the others, so devs can move them as they like and SAVE the layout. This is fully resolved with #192 https://github.com/sqlectron/sqlectron-gui/pull/192 .

I'm don't think first approach you mentioned would be good in our case as it might make use of 'paper' badly, because there might be lots of scroll vertically and lot of empty space horizontally. That's why I think its the best to let developers move tables and joints (arrow might be moved around the table and links can be pulled on any point you like). Just makes user move flexible and a lot of them are used to this because SQL Server studio does the similar thing :)

Currently I'm still working on this, next thing is marking PR and FKs on the table, enabling export to SVG and 'add related tables' feature which would enable user to add tables that current table has references to. This would provide easy way to check referential integrity constraints. I plan on finishing this withing the week, I'll keep you update on this issue.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/sqlectron/sqlectron-gui/issues/165#issuecomment-225415698, or mute the thread https://github.com/notifications/unsubscribe/ALcacx5LUdud682fhJPpY9M-gJ-4FVFBks5qK7ulgaJpZM4Ikd_b .

BornaP commented 8 years ago

I'm not sure I quite understand you. We're already saving the whole diagram into a JSON as a file, so that user can load it from file. All tables, links (their 'view') and their positions are saved, actually whole SVG is saved and you can manipulate the diagram when you open it as you want.

I don't think there is a need to save a diagram into a actual database, as this application uses none. This is primary SQL editor. If someone wants to save JSON into DB he can do it manually after save :)

joeblew99 commented 8 years ago

ah did not see that - awesome stuff :)

joeblew99 commented 8 years ago

i wil try it out.

BornaP commented 8 years ago

Since the feature was implemented with #189 and further enhanced with #192 , #194 ,#195 and #198 , I'll close the issue. Further enhancements or possible bugs should be discussed in their own issues.