Data Editing: How to insert/delete rows?

[eirikbakke]

When working with tables in an external relational database, such as PostgreSQL, it is possible to use Ultorg to edit data from any perspective (visual query), using the Data Editing Mode button in the toolbar:

With this mode enabled, you can edit primitive values in any database table by moving the cursor to the relevant cell and typing, like in a spreadsheet. Changes will appear in a "staged" state, and committed to the database in a single transaction only once you press the "Apply Edits" button:

You can also insert and delete rows, by using the key combinations Ctrl+Shift+Minus and Ctrl+Shift+Plus. (That's the plus/minus keys between 0 and Backspace, not on the numeric keypad.) The Ctrl+Shift+Minus action can also be used to revert any selected existing staged edits. Undo/Redo works here as well, until you press Apply Edits, after which committed changes cannot be undone.

TODO: Make the keyboard shortcuts work with +/- on the numeric keypad too.
TODO: Make these keyboard shortcuts documented in the user interface.

Pressing Ctrl+Shift+Minus will put the currently selected row in a pending delete state (red/strikeout):

Pressing Ctrl+Shift+Plus will show a "pending insert" row in green, where values for the new record can be entered:

When inserting new rows, it's usually best to make all of the table's available fields visible first, as done above, since you'd usually want to fill in values for them. In the example above, Ultorg has already populated the "Course ID" field, because the insert operation was done with the cursor located in one of the sections for the course with ID=3, in a perspective that has Sections joined on Courses on these two fields.

There are a few caveats for the row insertion feature (and fixing these are on the Ultorg TODO list):

• You will usually need to specify a primary key. In the screenshot above, Ultorg is showing a placeholder value "+1" that the user should change to a new primary key value. In the future, Ultorg will auto-generate primary keys at commit time, in columns for which this is enabled.
• In some perspectives, the inserted row may fail to show up in the expected place, in particular when the Custom Group feature has been used. In this case the status bar will show the message "Not all edits are currently visible", and it's usually best to invoke Undo to get out of this state. This problem will be fixed in a future Ultorg version.
• After staging some edits, it is still possible to make modifications to the perspective, e.g. to show additional fields. But if you apply filters, or make other changes to the query, the edits you had previously staged may no longer be visible (e.g. because they were in rows that are now filtered out). This will also cause the "Not all edits are currently visible" message to appear. It's best to keep the query in a state where all edits are visible before pressing Apply Edits, and future versions of Ultorg might enforce this.

[ldhieu]

Thanks for writing this out very clearly.

I just wanted to add that if anyone sees an error while adding rows saying type error "Expected Whole number, got Other", that's because you need to change the "+1", "+2", ... that ultorg places in ID rows to the corresponding IDs.

When editing multiple parent-child relationships, change the ID for the parent first, then add in the rows for the children otherwise the rows will get cleared out.

[eirikbakke]

@Idhieu Thanks for the comment! Both of those observations are exactly right. Each of these usability problems will need to be ironed out over future Ultorg versions.

TODO: Support auto-generated primary keys for inserted rows. Probably require an explicit option for this, unless the underlying database schema already defines an auto-increment for the column.
TODO: When a primary key cannot be auto-generated, improve the error message.
TODO: When the user edits a value that participates in a parent/child join key, automatically keep the two in sync. (To avoid the second problem mentioned by @Idhieu above.)

[eirikbakke]

There's been many improvements in the Data Editing functionality in the last few Ultorg releases. These fix the most important issues mentioned here:

• The user interface now shows the keyboard shortcuts for inserting/deleting keys, and helps the user turn on Data Editing mode.
  
• Problems accessing the insert/delete shortcuts on certain keyboard layouts were fixed.
• Inserting into tables with auto-incrementing keys or computed columns now works ( Inserts on tables with computed columns #35 ).
• All edits must now be visible before Apply Edits will work, and Ultorg will prevent further edits if the user is temporarily in this state (e.g. after hiding a column with edits). Certain situations that would cause the "Not all edits are currently visible" state are now avoided.

See https://www.ultorg.com/releasenotes/ for details. Existing Ultorg users can find the latest version at the old download link.