You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Allow Insert/Update/Delete for Views on Browse Data tab #141
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.
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'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. 😕
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?
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. 😉
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. 😄
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.
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.
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 😄
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.
Activity
justinclift commentedon Oct 31, 2014
Yeah, not sure about this. Martin/Rene?
We might want to reject this for now, but consider it again at some future point?
nowox commentedon Apr 28, 2017
It is been two Years, can we reopen this issue?
Consider this database:
With
sqlite3It is legit to write: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.
justinclift commentedon Apr 28, 2017
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. 😕
nowox commentedon Apr 30, 2017
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?
justinclift commentedon Apr 30, 2017
This takes you through the complete developer environment setup process for Windows, along with getting DB4S built and running:
Does that help? 😄
nowox commentedon Apr 30, 2017
It's tough, but well documented ^^
justinclift commentedon Apr 30, 2017
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. 😄
nowox commentedon Apr 30, 2017
Great!
MKleusberg commentedon May 12, 2017
@nowox Working on this now. Turns out to be trickier than expected but I might be able to push a first version this evening.
Allow updating views
MKleusberg commentedon May 12, 2017
Progress:
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.
nowox commentedon May 14, 2017
Wooh that's nice. I should give a try
Add support for deleting rows in views which are unlocked for editing
MKleusberg commentedon Jun 7, 2018
It's been a long time but I have just added support for deleting from a view 😄
20 remaining items
cristilupascu commentedon Feb 22, 2019
@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.
justinclift commentedon Feb 23, 2019
Thanks @cristilupascu, that'd be awesome. 😄
Add Insert for Views using AddRecordDialog (sqlitebrowser#141)
Merge pull request #1 from cristilupascu/view_insert
Add Insert for Views using AddRecordDialog (sqlitebrowser#141)
Add Insert for Views using AddRecordDialog (sqlitebrowser#141)
cristilupascu commentedon Mar 3, 2019
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.
justinclift commentedon Mar 4, 2019
@cristilupascu Sounds like it'll be a good start. 😄
Add Insert for Views using AddRecordDialog (#141)
MKleusberg commentedon Mar 6, 2019
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 INSERTbut noON UPDATEtrigger 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 😄MKleusberg commentedon Aug 10, 2019
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.
bam80 commentedon Jan 23, 2020
Please check if it's still an issue here: #1653 (comment)