Start a new topic

problem while: duplicating rows, updating arrays

[This topic is migrated from our old forums. The original author name has been removed]

Hello, I'm a new user of DBVis (ver 9.2.7) with postgres 9.3.4. It looks great but I have some questions: Question 1 (undo deletes while copy/paste in grid mode) ======================================================== Imagine the following scenario, while editing data in grid mode: 1. select row 2. ctrl+c 3. ctrl+i 4. paste on new row 5. click on a cell (say that I didn't double-click on it to really focus on it) and I accidentally click delete button. The whole line is deleted, and I can't find any way to undo it. If it's just one line, it's not that much of a deal, but imagine doing copy/paste with a lot of lines and you happen to do this mistake somewhere in the middle. It gets frustrating. Is there any way to undo the last delete? Question 2 (update sql with arrays seems broken) ================================================= Updating arrays (while in grid mode) on postgres seems broken: This is the generated sql: UPDATE "public"."fb_reports_dets" SET "vals" = '['10']' WHERE "id" = 25 part of my DDL: Column | Type vals | text[] | Correct sql should be: UPDATE "public"."fb_reports_dets" SET vals = '{10}' WHERE "id" = 25; (OR UPDATE "public"."fb_reports_dets" SET vals = '{10,11,12}' WHERE "id" = 25; UPDATE "public"."fb_reports_dets" SET vals = '{"10","11","12"}' WHERE "id" = 25; ) Am I doing something wrong or is it a bug? Question 3 (duplicate rows has problems with serial primary key) ================================================================= It seems like I can't make "duplicate rows" to work: I select one row, click "duplicate rows" button, the new line appears fine, I obviously delete the id and (null) appears, click save and I get an error "duplicate key..." I can see that the generated SQL includes the id in the sql, which is wrong of course, thus the error msg. The auto-generated sql that I see is like: insert into table(PK, col1, col2, ...) values(...), although it should have been: insert into table(col1, col2, ...) values(...) I've found a work-around though: 1. select row 2. ctrl+c 3. ctrl+i 4. paste on new row 5. double-click on id cell, click delete, "(null)" appears in grey 6. save works fine like this (I can also see that the auto-generated sql doesn't include the PK, that's why it's working) This is ok for a single line, but if I copy/paste many lines I have to do this to every line! Someone may say, why I don't just copy/paste the "(null)" from 1st line to the others? This doesn't seem to work because although the "(null)" of the 1st line is gray, as soon as I copy/paste it to the other IDs, the become yellow "(null)", so when I click "save" I get the same error. In order for this to work, I need to copy/paste and press F2 on each yellow "(null)" to make it gray. Only then save works. I've also tried setting the IDs "null" from the button with small green arrow (with "Set to NULL") option, but it still gives the same problem. I don't know if I make myself clear, but I think that this is a bug. Question 4 (SQL history of commands in grid mode) ================================================= Say that I do some updates while in grid mode. Is there any way to see the SQL history of these commands? Tools -> SQL history, shows only the statements that I'm doing in "SQL Commander" tabs. (I know I can see the generated SQL while I'm doing the update in grid mode, but sometimes you want to see what you have done a few moments ago.) Thank you for your time ilias

[This reply is migrated from our old forums. The original author name has been removed]

Re: problem while: duplicating rows, updating arrays
Hello again, >The easiest fix is that you configure the key bindings in Tools->Tool Properties and in the General / Key Bindings Category. The related key bindings are available in the Data Editor. Yeah, but then you loose the (single-button) delete functionality on the cell. Instead you must do F2+del. Anyway I can live with that. Thank you for your time. Ilias
[This reply is migrated from our old forums.]

Re: problem while: duplicating rows, updating arrays
Ilias, > {quote:title=ilias wrote:}{quote} > A small note on Question 3, is that "duplicate row(s)" button works indeed correctly, as long as you don't do what I did (which was to delete the IDs, but only because it displayed the same IDs with the original rows!). > So, I think that your UI needs to be kind of improoved, in order to give to the user the right impression of what it's going to happen as soon as he/she presses the save button. I mean, ideally, it shouldn't show the same IDs, as it could cause confusion. > > I hope I make myself clear. The general philosophy is that copy/paste and duplicate rows will retain all values as-is. The visual difference is that the cell indicator may switch from being a yellow border to a filled yellow background depending on if the column is reported as being an auto increment type or not by the JDBC driver. One reason for keeping the values is that not all JDBC drivers properly indicate if columns are auto increment. It may potentially be the other way around whereas columns are being reported as auto increment while they in fact are not. The latter would result in a real headache. > > {quote:title=Roger Bjarevall wrote:}{quote} > > It is not possible to undo (revert) an undo operation. > About question 1, I don't want to undo an undo operation. > What I'm trying to say is that I'm duplicating rows, and by mistake I click the delete button. > The whole line goes away. (If you press CTRL+Z -presumably to undo- it also deletes one more line! try it! I just noticed it.) It would be nice to be able to undo this mistake. > Let me put it better: It would be better if the "delete" button wouln't delete the whole row, but rather the field that it's currently focused. The delete button should delete only what it is selected. If the user wanted to delete the whole line, let him/her select the whole line and then delete it. I think this is a cleaner approach. The easiest fix is that you configure the key bindings in Tools->Tool Properties and in the General / Key Bindings Category. The related key bindings are available in the Data Editor. Before editing the key bindings you need to create a custom key map from one of the existing ones. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: problem while: duplicating rows, updating arrays
Hello Roger, First of all, thank you for your prompt and to-the-point reply! You have answered to my last 3 questions. A small note on Question 3, is that "duplicate row(s)" button works indeed correctly, as long as you don't do what I did (which was to delete the IDs, but only because it displayed the same IDs with the original rows!). So, I think that your UI needs to be kind of improoved, in order to give to the user the right impression of what it's going to happen as soon as he/she presses the save button. I mean, ideally, it shouldn't show the same IDs, as it could cause confusion. I hope I make myself clear. > {quote:title=Roger Bjarevall wrote:}{quote} > It is not possible to undo (revert) an undo operation. About question 1, I don't want to undo an undo operation. What I'm trying to say is that I'm duplicating rows, and by mistake I click the delete button. The whole line goes away. (If you press CTRL+Z -presumably to undo- it also deletes one more line! try it! I just noticed it.) It would be nice to be able to undo this mistake. Let me put it better: It would be better if the "delete" button wouln't delete the whole row, but rather the field that it's currently focused. The delete button should delete only what it is selected. If the user wanted to delete the whole line, let him/her select the whole line and then delete it. I think this is a cleaner approach. Anyway, let me thank you once again for your support. Ilias
[This reply is migrated from our old forums.]

Re: problem while: duplicating rows, updating arrays
Ilias, Please find my response below. > {quote:title=ilias wrote:}{quote} > Question 1 (undo deletes while copy/paste in grid mode) > ======================================================== > Imagine the following scenario, while editing data in grid mode: > 1. select row > 2. ctrl+c > 3. ctrl+i > 4. paste on new row > 5. click on a cell (say that I didn't double-click on it to really focus on it) and I accidentally click delete button. > > The whole line is deleted, and I can't find any way to undo it. > > If it's just one line, it's not that much of a deal, but imagine doing copy/paste with a lot of lines and you happen to do this mistake somewhere in the middle. > It gets frustrating. > > Is there any way to undo the last delete? The data in the editor local meaning that all edits are made in DbVisualizer only. These are propagated to the database only when manually saving the edits. The current undo support undos only on the command level such as: - undoing an insert/duplicated row will remove it from the grid, thus not being inserted during save - undoing a cell that has been edited reverts the value to its original value - undoing a row that has been marked for delete reverts it to an unedited state It is not possible to undo (revert) an undo operation. > Question 2 (update sql with arrays seems broken) > ================================================= > Updating arrays (while in grid mode) on postgres seems broken: > This is the generated sql: > UPDATE "public"."fb_reports_dets" SET "vals" = '['10']' WHERE "id" = 25 > > part of my DDL: > Column | Type > vals | text[] | > > Correct sql should be: > UPDATE "public"."fb_reports_dets" SET vals = '{10}' WHERE "id" = 25; > (OR > UPDATE "public"."fb_reports_dets" SET vals = '{10,11,12}' WHERE "id" = 25; > UPDATE "public"."fb_reports_dets" SET vals = '{"10","11","12"}' WHERE "id" = 25; > ) > > Am I doing something wrong or is it a bug? This is an issue that will be addressed in the next 9.2.8 version. > Question 3 (duplicate rows has problems with serial primary key) > ================================================================= > It seems like I can't make "duplicate rows" to work: > > I select one row, click "duplicate rows" button, the new line appears fine, I obviously delete the id and (null) appears, click save and I get an error "duplicate key..." > I can see that the generated SQL includes the id in the sql, which is wrong of course, thus the error msg. > The auto-generated sql that I see is like: insert into table(PK, col1, col2, ...) values(...), although it should have been: insert into table(col1, col2, ...) values(...) > > I've found a work-around though: > 1. select row > 2. ctrl+c > 3. ctrl+i > 4. paste on new row > 5. double-click on id cell, click delete, "(null)" appears in grey > 6. save works fine like this (I can also see that the auto-generated sql doesn't include the PK, that's why it's working) > > This is ok for a single line, but if I copy/paste many lines I have to do this to every line! > Someone may say, why I don't just copy/paste the "(null)" from 1st line to the others? This doesn't seem to work because although the "(null)" of the 1st line is gray, as soon as I copy/paste it to the other IDs, the become yellow "(null)", so when I click "save" I get the same error. In order for this to work, I need to copy/paste and press F2 on each yellow "(null)" to make it gray. Only then save works. > I've also tried setting the IDs "null" from the button with small green arrow (with "Set to NULL") option, but it still gives the same problem. > > I don't know if I make myself clear, but I think that this is a bug. When you paste cells into a row all cells in the target rows are marked with a yellow background. This indicates that the columns will be included in the SQL statement once saving the edits. For auto increment, serial and sequence fields this is not very good as you've discovered. To resolve this continue reading below. - If you are copying complete rows a better option is to use the Duplicate feature. It checks whether the target fields is an auto increment field and if so it will not mark it as yellow and include it in the final insert request with the database. - If duplicating is not an option you may select the cell (or cells) containing the auto increment type and select Edit Table Data->Undo Edit(s) in Selected Cell(s) in the right-click menu. (An option to the latter is to click the top-right Undo button in the table data editor toolbar). > Question 4 (SQL history of commands in grid mode) > ================================================= > Say that I do some updates while in grid mode. > Is there any way to see the SQL history of these commands? > Tools -> SQL history, shows only the statements that I'm doing in "SQL Commander" tabs. > (I know I can see the generated SQL while I'm doing the update in grid mode, but sometimes you want to see what you have done a few moments ago.) We do have an open ticket to offer a general history of SQL statements and not only those executed in the SQL Commander. I will add your vote for it. If you have any further questions, please let me know. Regards Roger