Database Tools - Feature request and suggestions

As I understand it, database stuff is a recent addition to phpstorm. I my self have just started using it. I really love the idea of being able to do all of my work from one place and not have to keep loading up phpmyadmin and whatnot. Unfortunately the DB tools just arent totally there yet and thus are more useless than useful to me. Here is a list of my gripes / needs and if I'm off base on something and you can do something I think is missing let me know.

- Insert colum at position (add to context right click on table column when columns are not sorted alpha). What this feature should do is auto select the field name for the field above the clicked on field from a drop down of all columns in the add column dialog. This dropdown should be attached to the 'AFTER' label that would need to be added. This way even if you didnt choose an insert location when telling it to add a column name you can still select an after column in the box. The columns should always be ordered acording to the schema in this selection box and not alphabetically.

- Modify the add column / modify column dialogs to include an AUTO_INCREMENT check box (and allow the starting value to be entered).

- All in all the whole schema editing stuff could be improved. It seems to be lacking a LOT of functionality phpMyAdmin has in regard to modifying the schema.

- Need right click on table name context menu to "insert" which would give a dialog box similar to phpMyAdmin add row (paying specifica attention to the functions drop down for each row). Right now, it appears the only way to manually enter a row is to F4 show the table data then click the + sign which adds a blank row which can be edited inline.

- When editing the table data there needs to be a way to specify a mysql functions - Its nice to have a drop down to select the function instead of just having autocomplete, but even thats not available. If I edit a field and put PASSWORD('somedata') and hit enter, it literally saves that string to the database. It does not execute the desired query of field_name=PASSWORD('somedata'). What the hell?

Lastly, the difference tool for sql is totally fucked.

When i create a new feature or hotfix branch on my project I also duplicate the database which is named <ORIG_DB_NAME>_<BRANCH_NAME> (so mysite_store -> mysite_store_feature-saved-carts).

I do my work on the new branch and change the sql schema along the way. New tables, dropping columns and dropping tables work ok.The problem is, when I rename a column in the schema, the diff function generated when its time to merge will completely destroy all of the data in the column that was renamed and it will not honor the position of any newly added column.

How is this you ask?

Becuase the DIFF script generated to "merge" the table schemas does not use the alter table CHANGE command, it completely drops the column and then re-adds it. And not only does it do that, but it doesnt even put the column back where it was when I issued the modify command. If I run this generated script to update my production site, and I changed the username column to user_id or something, then every single account on the published site would loose their "log in name" and the column would be at the end of the table after "last_login" which is not inline with how I structure my tables. The diff script generated also does not insert "AFTER" clauses when appropriate, even if I specifically added an "AFTER" to the statement where I added the new column.

No bueno.


Because of all this I still have to use phpMyAdmin to manage my schemas, and insert manual data. I still use the IDE for reference and testing queries but thats really kinda  defeating the purpose of having all this at my finger tips.

I even have to use and maintain my own schema diffing scripts which is less then ideal since this functionality is technically built into the ide, albiet just not correctly in my opinion.



Anyone else agree / disagree?

Comments? Concerns?

5 comments
Comment actions Permalink

I also found what I think is a bug. Not sure where to report it.

I have a table, first column id, primary key, auto increment.

I add a new row in the table editor.

Editor will not let me leave blank and forces me to enter a number. This should be NOT FILLABLE since its an auto field. or at the very least you should be able to leave it blank so the DB auto fills the next auto increment value.

Or am I crazy?

0
Comment actions Permalink

Hi there,

Please submit your ideas/suggestions/bug reports as tickets to the Issue Tracker

But before submitting new one -- check for existing tickets first:


Also make sure you are using latest IDE version: PhpStorm EAP

0
Comment actions Permalink
Or am I crazy?

You are not -- this pissed me off as well -- such a basic functionality and done wrong.

That's why I still prefer MS SSMS when working with SQL Server -- much better (plus got used to it over last 8 years).

P.S.
Accordingly to http://youtrack.jetbrains.com/issue/IDEA-120803 this should be fixed in v8 (have not tested it myself there).

0
Comment actions Permalink

I guess I'll have to retract my bitching about the drop and add on rename since logically the system has no way of knowing that it was a rename and not a drop / add. This problem is glaring on all mysql diff scripts I've tried.

I've decided on keeping a changes.sql file to track my schema changes manually per branch. I just have to physically remember to paste my sql commands into it every time I make schema changes. I guess in mysql 7.1 there are event triggers where I can automate this for schema changes - Il just hold out until then and just do it manually for now.

Still gonna use phpMyAdmin for schema tasks because of the column ordering issues and not supporting "after" in the gui.

Still using the integrated mysql tools for code completion, reference and quick queries to check backend stuff.

I also have been reading that column order should not be important but I guess I'm just stuck in my old ways. I like to see the tables in a layout that makes sense to my brain, not just some random mishmosh of columns in no particular order. Even alpha sorting on columns hurt my brain because i want to see the address fields together address1, address2, city, state, zip, country, phone, then other fields etc. Item databases I like seeing ID, linkid, product_number, product_name, price, sale_price, etc.... right at the front of the table...

Am I weird or is this fairly comon? Just because I like to order my columns to my taste doesnt mean I dont specify columns on inserts...

0
Comment actions Permalink

Please file feature and bug requests in our DB-specific issue tracker: http://youtrack.jetbrains.com/issues/DBE .

DB-related IDE functionality is evolving all the time so please do not let your ideas and rants get lost here on the forum.

0

Please sign in to leave a comment.