Can we please get the process started to have the most basic part of the Base user interface straighted up so there is a clear separation between data and design? Tables need to list just tables, and only tables. Queries need to list just things that are queries. Since what is currently called a View, looks like to me like a Query, edits like a Query, and runs like a Query, I think the behavior of a View, e.g. column formatting, should be merged into a Query, and views depreciated. This is also how MS Access is done, so this fix would further make Base consistent with Access to remove one more stumbling block and help attract people like me who are trying to move from Access to Base.
Perhaps http://stackoverflow.com/questions/1278521/why-do-you-create-a-view-in-a-database gives you some ideas, why views are necessary. I recommend closing this issue as "Wontfix".
(In reply to Regina Henschel from comment #1) > Perhaps > http://stackoverflow.com/questions/1278521/why-do-you-create-a-view-in-a- > database gives you some ideas, why views are necessary. > > I recommend closing this issue as "Wontfix". I know there will be resistance to this, because once something is in place people want to defend it, even when there is a good reason for the change. But each, and every one of these suggested uses, can be done by a query: 1. Views can hide complexity If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table. *** Queries can hide complexity just the same. I have used sub-queries for 20 years to do just this. 2. Views can be used as a security mechanism A view can select certain columns and/or rows from a table, and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see. *** In LibreOffice is this used? No. And there are good reasons for it, first and foremost is that it's not a very good security practice in the first place. Security should be comprehensive, and well thought out, where the entire server is protected. It would be easy to go around a view I think. 3. Views can simplify supporting legacy code If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure. *** As long as queries can reference other queries, queries can do this just as well. And because only one layer is involved it keeps the user interface simpler, with less places to look when remodeling code. But here is the single most important reason to use queries in favor of views: There is no good reason to use a view, when a query can do everything a view can do. And having just one flexible layer in-between tables and forms/reports simplifies things, without any loss of functionality. I have many times, over decades of use, and hundreds of data sets, used queries in MS Access to do each of the 3 things listed in this referred to post.
My only comment here would be that our ODB files interface with a large number of db engines, many of which treat Views as virtual table objects. See, for example : https://msdn.microsoft.com/en-us/library/ms190174.aspx where even Microsoft considers a view to be a virtual table, and: https://blogs.office.com/2012/08/08/access-2013-and-sql-server/ where using Access with SQL Server turns all Queries into server-side Views. In Access 2007, per : https://support.office.com/en-us/article/View-and-manage-objects-by-using-the-Navigation-Pane-274dfc5a-281b-472b-94e2-ef931c5cc590#bm4 views would appear to be displayed under the category "Tables and Related Views". MySQL Views are considered to be virtual tables but the MySQLWorkbench UI displays Views in a separate category under "Views". My point being that if there is no general over-arching consistency in how things are done in other existing graphical DBMS products, it begs the question of why we should decide to implement what I would assume to be a fairly invasive code change - currently our db objects are referenced mainly as XML in the ODB file with a particular internal directory structure for some objects - and the corresponding UI code to display them. All of this notwithstanding the developer resources to do so. If you have such resources, and can draw up a spec for implementation, then it could at least be proposed for discussion - otherwise IMHO it is just wishful thinking.
Personally, I would field this out for discussion to the UX people, and keep Lionel on copy (he being the current "guardian/expert" of the db code within the LO project.
Queries are designed in the Base-file and will be sent to the database every time it should be executed. You could create queries to change data in a table. Views are designed in the database. They will be sent to Base like tables, which couldn't be edited. Views are faster than queries and you could filter and sort views better. Most of reports in Report-Builder will run better with a view than a query, because it handles views like a table. The content in the folder for Tables is the content of the database. The content of the folder for Queries is the content of the Base-file. I would prefer "Wontfix" for this one.
I have a new simpler suggestion to resolve and clarify this, given what is currently in place. Make a new section for Views, and organize things like this: Tables, Queries, Views, Forms, Reports, .. This will keep the concept of LO Views unchanged, as it is now. But it will make it clear that Views are not tables. It will also make it clear that Views are not queries. And it suggest that Views are more capable than LO queries, which they are. And it might suggest, as is the case, that views are based on queries, (like they appear to be, as far as I can see.) Although LibreOffice Base, looks very similar to MS Access on the surface, in fact this is misleading because the very concept of a Query in Base is quite different from a Query in Access. To Access converts this is problematic. Here is an Acccess / LO Base comparison of terms & features: editable? formattable (e.g. centered, and renamed foo)? =============== ========= ============================================= LO Base Query: no no --------------- --------- --------------------------------------------- Access Query: yes* yes --------------- --------- --------------------------------------------- LO Base View: yes yes --------------- --------- --------------------------------------------- * Some queries are not editable, e.g. when the data they return no longer directly connects to the underlying tables. It's too bad that LO and Access came to use the word Query so differently. I can see why LO did this, to try to get back to a simpler view of a query based on a SQL query, where Access expanded a Query to also make it transparently editable. I like what Access did much better than what LO did. And it's too late to give a LO query a new name to unify this. So the best I think we can do is clarify what a view is and how it contrasts with a LO query. (I am new to Base and struggling to understand it, and so I hope my facts as stated above are correct.)
Correction, it appears neither Queries nor Views in LO are editable. editable? formattable (e.g. centered, and renamed foo)? =============== ========= ============================================= LO Base Query: no no --------------- --------- --------------------------------------------- Access Query: yes* yes --------------- --------- --------------------------------------------- LO Base View: No** yes --------------- --------- --------------------------------------------- **Could in the future be made editable?
"View" is not a LibreOffice concept, but a concept of the underlying database engine. LibreOffice shows as view the views created in the database, just like it shows as tables the tables created in the database. Hence the data shown by a view (as opposed to the view definition itself) can only be edited from LibreOffice if the that is possible in the underlying database. That is, if "v_foo" is the name of a view, statements such as: UPDATE v_foo SET column=value WHERE condition; DELETE FROM v_foo WHERE condition; INSERT INTO v_foo (column1, column2, column3) VALUES (value1, value2, value3); must be allowed by the database engine. Plus LibreOffice needs to detect a primary key, so as to be able to construct the "condition" part. That might be a blocker.
Thanks for the points about virtual tables and underlying views. It appears to me that in Access a Query might actually be an underlying View. I imagine that you can just close this now. I'm frustrated because although LO Base at first looks a lot like Access, so much important functionality is missing. I hope to someday get to where I could help make LO Base better. I'm here because Windows 10 is such a mess now. Thanks to all of you.
Here is another similar idea: Make a new separate section for Views, and instead insert it between Table and Queries, like this: Tables, Views, Queries, Forms, Reports, .. Next, because the only feature I can see that a Query has over a View, is that it seems to remember the physical layout (xy positions) of the underlying table's relationships, can this one feature then be merged into a View? (Am I missing something, or is this the only difference between the two?) In other words, can the physical xy position of the table's relationships be somehow saved in Views, because as it is, it forgets this information and makes a mess out of my connected tables when I reopen a view. And then later depreciate Queries, because a View can do everything a Query can and more, so we get: Tables, Views, Forms, Reports, .. This breaks from Access, but might straighten things up a bit, and remain more compatible with other things.
(In reply to Regina Henschel from comment #1) > I recommend closing this issue as "Wontfix". (In reply to robert from comment #5) > I would prefer "Wontfix" for this one. (In reply to Lionel Elie Mamane from comment #8) > "View" is not a LibreOffice concept, but a concept of the underlying > database engine. While database handling in general is the opposite of an user friendly workflow I don't think we can revolutionize it in LibreOffice. Views are just different from queries, https://en.wikipedia.org/wiki/View_(SQL). Closing as WF.
In light of a better understanding of this, I'd like to revise my prior suggestion as follows: ---- Make a new section for Views, and organize things like this: - Tables, Queries, Views, Forms, Reports, .. + Tables, Views, Queries, Forms, Reports, .. ---- I'm hearing 'WF' from a number of people, but I'm not seeing anyone yet saying that Views should really quietly remain in Tables. I am hearing that Views are important and useful, and they are. So to those of you who are suggesting WF: Perhaps we need to mark this as: 'DWF' (Don't Want to Fix), or 'THTFRN' (Too Hard To Fix Right Now), or better yet, 'FS' (Fix Someday). I admit that when I submitted this suggestion 1.5 years ago, I was coming from a MS Access world that I'd been using Access for more than 10 years and was fluent in it, and where I didn't know what Views were. In Access queries were, and are, much more capable than in base. I found it extremely frustrating, and I still do, at how limited base is in so many ways. But I hasten to add that I have now moved all of my stuff to LO base over these past 2 years, much to the dismay of my partner who has watched my frustration and countless months spent during this conversion process. This is not because I'm enamored with LO base, for I am not, ...rather it's because I want to be on GNU/Linux, and away from Windows. I think it's so easy for those who already know how something works to assume that others new to it will just get it. But I've seen many people on the LO ask site ask questions related to moving from Access to base and struggling with this transition, so I'm not alone in this adventure. How are people to move to Linux who have databases on Windows??? Do they have to spend years to get this done? I hope not. Thus the focus of most all of my bug reports and feature suggestions. More recently, I have been using a few Views, and find them quite helpful at times! But I still think if on the main screen under "Database" it says "Tables", then that's what should be there, Tables, and only tables! And while one could change the title to "Tables and Views" to correct it, and though that would be the simplest way to bring the honesty back to this misleading situation, I don't recommend this. I still don't see what's so wrong with wanting a Views button between Tables and Queries (Tables, Views, Queries, Forms, Reports). For one it would cause the new users to say, "Hey, what up with Views? Let's go look that up..." So please if you can mark this as FS (Fix Someday) or something, but not WF, unless you can really justify that it should not be fixed because it would not be a good idea. ......Because someday I still hope to get where I can fix some of these things. (I'm still on that nasty Linux learning curve on the way up.) Thanks again.