Skip to content

Allow Insert/Update/Delete for Views on Browse Data tab #141

Closed

Description

@asperax

This is a feature request to allow Insert/Update/Delete user interface capability for Views on Browse Data tab.

Views are normally read-only in SQLite, however, SQLite supports using triggers on Views, which handle the write operations attempted against the View. When this is the case, the absence of a user interface for editing the view means you have to write SQL code to do so.

To avoid confusion, you might consider interrogating existing triggers for an "...INSTEAD OF [INSERT/UPDATE/DELETE] on ..." trigger to determine what user interface options to expose. But if this is too complex, it might just be better to expose the options and return the result from the database engine, whether that's an error (because the view is read-only and doesn't have a trigger for the action) or otherwise. Of course this may conflict with your easy-to-use design philosophy (some users might dislike having the user interface indicate that edits are possible when they are not), in which case I guess the enhancement should be rejected.

Activity

  1. justinclift commented on Oct 31, 2014

    @justinclift
    Member

    Yeah, not sure about this. Martin/Rene?

    We might want to reject this for now, but consider it again at some future point?

  2. nowox commented on Apr 28, 2017

    @nowox

    It is been two Years, can we reopen this issue?

    Consider this database:

    BEGIN TRANSACTION;
    CREATE TABLE Foo (a, b, c);
    INSERT INTO `Foo` (a,b,c) VALUES (1,2,3);
    CREATE TRIGGER update_bar
    INSTEAD OF UPDATE OF a ON Bar
    BEGIN
      UPDATE Foo SET a = NEW.a
      WHERE b=NEW.b;
    END;
    CREATE VIEW Bar AS SELECT a, b FROM Foo ORDER BY b;
    COMMIT;
    

    With sqlite3 It is legit to write:

    UPDATE Bar SET a = 23 WHERE b = 2;
    

    However, it is not possible to do it so on a View on DB Browser for SQLite.

    It would be a huge plus for me.

  3. justinclift commented on Apr 28, 2017

    @justinclift
    Member

    It's pretty likely we'll support them at some point. But it's not likely for the upcoming release (3.10.0), and we still a fair amount we need to get done for that. 😕

  4. nowox commented on Apr 30, 2017

    @nowox

    What amount of work this feature would represent? What are the concerned files? If I wanna build DB Browser for SQLite on Windows is there a VS project?

  5. justinclift commented on Apr 30, 2017

    @justinclift
    Member

    This takes you through the complete developer environment setup process for Windows, along with getting DB4S built and running:

    Does that help? 😄

  6. nowox commented on Apr 30, 2017

    @nowox

    It's tough, but well documented ^^

  7. justinclift commented on Apr 30, 2017

    @justinclift
    Member

    If you're up for getting it done (takes a few hours, though that's mostly downloading and waiting for installs to finish), be aware that some of the paths in the setup guide no longer match the ones hard coded in the source. They're not far off, and it's easy to fix if you know to watch out for it. 😉

    The paths used in the source at the moment are: https://github.com/sqlitebrowser/sqlitebrowser/blob/master/CMakeLists.txt#L18-L30

    (that's for compiling on 64 bit windows)

    Notice the "5.7" in the Qt path? That'd likely need updating to "5.8". Similar for the OpenSSL and SQLite paths in that highlighted section too, just make sure they match the directory structure you create when you go through the guide. 😄

  8. nowox commented on Apr 30, 2017

    @nowox

    Great!

  9. MKleusberg commented on May 12, 2017

    @MKleusberg
    Member

    @nowox Working on this now. Turns out to be trickier than expected but I might be able to push a first version this evening.

  10. added a commit that references this issue on May 12, 2017
  11. MKleusberg commented on May 12, 2017

    @MKleusberg
    Member

    Progress:

    • Update
    • Insert
    • Delete

    Delete seems easy enough, insert however more tricky.

    By default, views are still locked from editing because most databases don't have triggers in place for editing views. This means you have to enable editing for a view by browsing it, right clicking on the column headers, and clicking "Unlock view editing". You'll then be asked for the name of a unique column in that view. That's because we somehow have to decide how to address rows in the view and there doesn't seem to be any good way to find out a suitable column automatically.

  12. nowox commented on May 14, 2017

    @nowox

    Wooh that's nice. I should give a try

  13. MKleusberg commented on Jun 7, 2018

    @MKleusberg
    Member

    It's been a long time but I have just added support for deleting from a view 😄

  14. 20 remaining items

  15. cristilupascu commented on Feb 22, 2019

    @cristilupascu
    Contributor

    @mgrojo I see. Then I'll start looking through the code and see if I can figure it out. At the moment, the insert button is always disabled for views.

  16. justinclift commented on Feb 23, 2019

    @justinclift
    Member

    Thanks @cristilupascu, that'd be awesome. 😄

  17. cristilupascu commented on Mar 3, 2019

    @cristilupascu
    Contributor

    Uff, sorry for all those references. I am pretty sure I added the Insert functionality without breaking anything for tables. Right now, for views, the AddRecordDialog is always used when inserting. The other method (inserting a row directly into the table) seems too complex to modify, so I won't be working on it.

  18. justinclift commented on Mar 4, 2019

    @justinclift
    Member

    @cristilupascu Sounds like it'll be a good start. 😄

  19. MKleusberg commented on Mar 6, 2019

    @MKleusberg
    Member

    I have just merged @cristilupascu's pull request 😄 So starting with tomorrow's nightly build there should be inserting into views too now. Somebody still around to test this? We could finally close this issue then 😉

    @cristilupascu Using the AddRecordDialog was also the way which I thought makes most sense. Inserting a row directly into the table probably won't ever work 100% correctly for views. Also I imagine some people might have an ON INSERT but no ON UPDATE trigger and the approach to insert a row directly requires both. So if no problems pop up in the tests, I think we can consider this finished 😄

  20. MKleusberg commented on Aug 10, 2019

    @MKleusberg
    Member

    Because I haven't heard of any problems with this, I'll finally close this issue 😄 I'd suggest considering this as done. So if there is any (remaining or new) problem with editing views, let's open a dedicated issue for that problem.

  21. bam80 commented on Jan 23, 2020

    @bam80

    Please check if it's still an issue here: #1653 (comment)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions