[addons/sql] Using QSqlTableModel when browsing table data to make table editable. Part 1

artyom kirnevrequested to merge
arti/kate_sql_select_table_data_action:editable_tables into master

As a web developer SQL addon experience is incredibly important to me, so I wanted to make it closer to the one the SQL Plugin for InteliJ IDEs offers (so when we are browsing data we can edit/insert/delete any row like we are in Excel table)

And seeing QT having QSqlTableModel out of the box allowing us to do it out of the box (and even QSqlRelationalTableModel that can make user's work with SQL pivot tables 10 times more convenient than what InteliJ offers) I wanted to do it for a long time.

I reworked DataOutputWidget class so it could interchangeably use old DataOutputModel class, new DataOutputEditableModel class (that uses QSqlTableModel under the hood) and future DataOutputEditableRelationshipModel (I created a new abstract class DataOutputModelInterface for them)

After having some unpleasant time with C++ inheritance (I can't make my common interface extend another abstract class like QAbstractItemModel. And I just moved styling into a separate helper class to avoid code duplication for different tables) I had only a proof of concept done. but I already felt like I've made plenty of changes for reviewers to digest at once.

SQL plugin already feels better than it was before - you can edit and sort data you are browsing now! (Changes are not saved 'till user submit them with a new "save" button)

So I'm making a Request For Comments, so reviewers can look at the foundation, better understand or/and direct my next steps (I'm gonna be making this weekend) and share their thoughts and ideas

(if you don't have a local SQL database configured you can use "DB Browser for Sqlite" to create a couple of tables in an empty .db file or/and ask ChatGPT to generate a SQL query inserting dummy data into new tables)

A roadmap so it's easier to understand what my foundation is building towards:

For this PR

1. move "save" button to a new horizontal toolbar above the editable table

1.5 add a refresh button for both read-only and editable qt tables

2. add a "insert new row" button there to create a new row

3. add a "delete" button there that just removes a row

4. optimize style/config helper class usage - instead of reading config each time we are making a request and then caching the data for every row (behavior on master) - our helper class can read the config inside DataOutputWidget and just be passed to new tables on every new request (I'll double check that slotGlobalSettingsChanged event does update all future and active tables and I didn't break hot reloading)

5. Undo button to trigger revertRow/revertAll/cancel deletion/remove new rows

6. where field for filters (in the toolbar above the table just like in InteliJ SQL Plugin)

7. Figure out how does QCompletionModel works and add a simple column name completion for "where" filter field

8. Undo action on right-clicking a modified row

9. "Set null" action on any field

10. "Clone row" action on any field 11. "Paste" action on any field

For future PRs

Part 2 - Settings + State (way lower priority than anticipated since QT shows which rows are new/deleted out of the box)

  1. Add a new setting group titled for "SQL Plugin - editable tables" - make the the new functionality disable-able (and probably disabled by default as "experimental")

  2. Refactor settings (both regular and for hotkeys) to use less hardcoded strings and more references to constant static strings to establish a single source of truth and avoid/solve a lot of bugs caused by typos

  3. Add highlight for dirty fields (doesn't matter if it's changed/inserted/deleted)

  4. Make hotkeys for editable tables configurable

Part 3 - Wet Dream:

  1. Use of QSqlRelationalTableModel for Tables with foreign keys. (It's gonna be hidden under another checkbox in the setting due to performance concerns - I'm planning on doing extra-checks for every column in the current table to find foreign keys + whether the table fits QSqlRelationalTableModel restrictions + checks for related tables to guess title/name column before failbacking to id. IMHO, for pivot tables performance trade-off is gonna be totally worth Quality Of Life improvements)

PR 4 - Wet dream that I'll probably never gonna have a time for:

  1. Figure Out how to insert all columns for current table into CTags/LSP/other auto-completion for SQL files

Update: finished everything for this PR

Edited by artyom kirnev

Merge request reports