search

lukemurray / data-atom

Query and manage data sources from Atom
MIT License
152 stars 36 forks source link

More database information and visualization #33

Open bmcorum opened 8 years ago

bmcorum commented 8 years ago

@lukemurray, @stuartquin I am going to work on some more visual components i.e. sequences, views, and column constraints. I currently have it pulling primary and foreign key constraints and showing them in the details window by way of a key font rather than column font.

Ideas,

DBs

Functions?

Sequences

Tables

Views?

Thoughts, questions, concerns?

lukemurray commented 8 years ago

Hey @bmcorum sounds fantastic. Here are my thoughts

DBs - agreed, although where are Stored Procedures? Should we list Triggers as well? I think trigger could come later if we want, but SPs should be now with Functions

Functions - you could expand this to then show the parameters it requires, their type etc. And the return type

SPs - Same a functions

Sequences - agreed.

Tables - agreed

Views - Similar to tables, list all the views under those list the columns it returns.

Thoughts?

bmcorum commented 8 years ago

@lukemurray sounds like a good plan. I do not currently have a DB with all these components to test against so I may need some help. I will try to get as much of it done and tested as I can and submit a pull request, but it may leave out some components if I cannot test against them.

lukemurray commented 8 years ago

That's ok. I should be able to lend a hand testing. Add what you can. I can always add more later when I get some more time. 

What actions, if any, would you want to perform from these details?

On Thu, Nov 5, 2015 at 12:05 AM, Brice Corum notifications@github.com wrote:

@lukemurray sounds like a good plan. I do not currently have a DB with all these components to test against so I may need some help. I will try to get as much of it done and tested as I can and submit a pull request, but it may leave out some components if I cannot test against them.

Reply to this email directly or view it on GitHub: https://github.com/lukemurray/data-atom/issues/33#issuecomment-153714737

bmcorum commented 8 years ago

I would at least like to add some actions to columns to view details such as data type, nullable, if foreign key then what it references. Maybe on tables details could display all details for all columns in the results view. I guess my ultimate goal would be to try to replace as much of the need for a database GUI program as possible. It is nice to be able to work in a single program and not have to jump around to different screens or jump all around the desktop.

lukemurray commented 8 years ago

Great, that's my goal too. I use sql management studio a lot at work and miss it at home so I started building this. 

I think we are aligned with what we want out of this so that's great.

On Thu, Nov 5, 2015 at 12:37 AM, Brice Corum notifications@github.com wrote:

I would at least like to add some actions to columns to view details such as data type, nullable, if foreign key then what it references. Maybe on tables details could display all details for all columns in the results view. I guess my ultimate goal would be to try to replace as much of the need for a database GUI program as possible. It is nice to be able to work in a single program and not have to jump around to different screens or jump all around the desktop.

Reply to this email directly or view it on GitHub: https://github.com/lukemurray/data-atom/issues/33#issuecomment-153721459

bmcorum commented 8 years ago

@lukemurray I have added context menu to detail view panel with the following options, but still need to code the actions.

On Database

On Tables (folder)

On Table

Questions, Comments, Concerns?

lukemurray commented 8 years ago

Sounds great! I might have some time to help if you want to push a PR earlier.

bmcorum commented 8 years ago

@lukemurray I am working on the CREATE/INSERT/UPDATE/DELETE scripts and am having an "internal battle". Upon selecting one of these actions I was going to create the basic outline like you would get from the GUI tool, but where to display said outline is bothering me!!!! I figured if one (me) is using a query box in the results view then I would put it there. If not, then should I create a new tab and place it there or paste it into the current editor (tab) that has focus? (I am shying away from current tab as I would hate to just inject text into the middle of file)

lukemurray commented 8 years ago

I would create it in a new editor tab and set that editor tab up with the same connection ready to roll.

With the the quick query box that does raise a good question. In MS SQL Management Studio I think you get a few options, clipboard, file, etc. Could we do similar? Inline or New File ? One would insert it in the current active editor (inline) which could be the query box or not, the other would open a new editor tab.

Thoughts?

bmcorum commented 8 years ago

Right now I have it set so that if you use the query box it will put the query statement otherwise "TODO".

I guess the real question is how it is going to be used. I will be using it mainly to run queries because I need to examine the data, but someone else may want to use it to help create the query statements for faster programming (less typing).

lukemurray commented 8 years ago

Great question, I think it'll be used similar to what you said.

do you have it all pushed to you fork? I'd love to play with it, helps me decide on usability items :)

lukemurray commented 8 years ago

Also we now have support for MySQL (with a few issues) so I'd love this to work with mySQL, MS SQL and Postgres. I'd be very happy to help test when you have something ready.

Cheers

bmcorum commented 8 years ago

All my changes are still local. I have a lot of it unfinished and was trying to get a slightly more complete solution before submitting a pull request. I will try to clean up as much as I can next week and possibly submit a pull request.

On Nov 21, 2015, at 6:32 AM, Luke Murray notifications@github.com wrote:

Also we now have support for MySQL (with a few issues) so I'd love this to work with mySQL, MS SQL and Postgres. I'd be very happy to help test when you have something ready.

Cheers

— Reply to this email directly or view it on GitHub.

lukemurray commented 8 years ago

No stress, take you time.

bmcorum commented 8 years ago

@lukemurray i have made some more progress but am far from finished. I have most of the framing complete and have most of it working for PSSQL DBs. I need to finish the Functions and Sequences. Need to complete the tables list break down, and hunt down a click event issue. I hesitate to create a pull request for fear we could potentially "step on each other toes", but I would be willing to submit what I have if you will work exclusively on making the additions work for MSSQL whilst I continue on PSSQL. This should hopefully reduce merge conflicts.

lukemurray commented 8 years ago

Happy to help out, I would have time this weekend to focus on that.

bmcorum commented 8 years ago

@lukemurray I have found some time again (though I don't know how much) to work on this enhancement. I was working on the options for viewing top 100 and last 100 rows and ran into a rather interesting outcome. Top 100 rows no big deal, however last 100 rows will return an interesting result in some instances (at least for PSQL only one I have tried). When pull last X rows you get the actual last X rows inserted by the database itself and not necessarily the user. When a record is updated there are instances where the database cannot update the record where it exist in the table therefore it removes the row and recreates it and thus it is now at the end of the table. No big deal really except if a user ask for the last 100 rows of a 1000 record table with a primary key starting at 1 and gets results back with a row containing say the record with primary key 53. Though this is not really an issue though I can see potential users submitting issues expecting last 100 rows returning records with primary keys 901-1000. Sorry for the long explanation and setup but here is the question....

Should be pull the "TRUE" last 100 rows

or

Should we pull the last 100 primary keys created (assuming the table has a primary key)

lukemurray commented 8 years ago

Awesome! If there isn't a pk in the table then you can't really do the same thing. I'd say just go with the easy way, use the same code for all types of table (pk or not).  Rather get the functionality used and then we can enhance it. 

_____________________________

From: Brice Corum notifications@github.com Sent: Thursday, January 21, 2016 1:42 AM Subject: Re: [data-atom] More database information and visualization (#33) To: lukemurray/data-atom data-atom@noreply.github.com Cc: Luke Murray lukemurray@gmail.com

@lukemurray I have found some time again (though I don't know how much) to work on this enhancement. I was working on the options for viewing top 100 and last 100 rows and ran into a rather interesting outcome. Top 100 rows no big deal, however last 100 rows will return an interesting result in some instances (at least for PSQL only one I have tried). When pull last X rows you get the actual last X rows inserted by the database itself and not necessarily the user. When a record is updated there are instances where the database cannot update the record where it exist in the table therefore it removes the row and recreates it and thus it is now at the end of the table. No big deal really except if a user ask for the last 100 rows of a 1000 record table with a primary key starting at 1 and gets results back with a row containing say the record with primary key 53. Though this is not really an issue though I can see pote ntial users submitting issues expecting last 100 rows returning records with primary keys 901-1000. Sorry for the long explanation and setup but here is the question....

Should be pull the "TRUE" last 100 rows

or

Should we pull the last 100 primary keys created (assuming the table has a primary key)

— Reply to this email directly or view it on GitHub.

bmcorum commented 8 years ago

@lukemurray I was able to query the db first and if a pk exists it will get last N rows sorting by pk otherwise gives you the last N rows by row number. I have a few more thing to finish for PSQL connections and then need to work on MSSQL. I have no experience with MySQL so I am not sure what your plan is for MySQL support.