| Summary: | Postgresql SDBC updatable views | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | Ali Joumma <ali.joumma> |
| Component: | LibreOffice | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | NEW --- | ||
| Severity: | enhancement | CC: | ali.joumma, lionel |
| Priority: | medium | ||
| Version: | unspecified | ||
| Hardware: | All | ||
| OS: | All | ||
| See Also: | https://bugs.documentfoundation.org/show_bug.cgi?id=137442 | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
| Bug Depends on: | |||
| Bug Blocks: | 104790 | ||
|
Description
Ali Joumma
2013-08-02 07:19:49 UTC
(In reply to comment #0) > while if I used SDBC and Base, all views are read-only, > it would be great if we can have updatable views in Libreoffice, > my use case is row-level-security, (...) Views don't offer row-level security if the users can define their own server-side (SQL) functions, because the optimiser can decide to call them on rows outside of the view (if the view condition is "more expensive"). Ah, but I now discover that newer PostgreSQL have a security_barrierflag for that... Oh, nice. > at least can somebody give me guidance on which file in the source code I > need to look at so I can try to implementing that my self Since you intend to work on it yourself, I assign this enhancement to you. I'll prepare you a few code pointers, but probably not today. First, you need to find out why these views read-only. Are they read-only also when using LibreOffice and ODBC, or LibreOffice and JDBC? Maybe LibreOffice does not find a primary key. Since you had to add the "oid" column to make it work with other systems, possibly LibreOffice does not see the "oid" column as a primary key? The code that marks tables without a primary key as read-only is the constructor dbaccess::ORowSetCache::ORowSetCache in file "dbaccess/source/core/api/RowSetCache.cxx"; see the places where it manipulates m_nPrivileges. The gist of the "find primary columns" code is dbtools::getPrimaryKeyColumns_throw in file "connectivity/source/commontools/dbtools.cxx". It basically uses the driver's getKeys() and then looks for a key that the driver has flagged as PRIMARY. In the case of PostgreSQL-SDBC, the object returned by xKeys->getByIndex(i) should be a pq_sdbc_driver::Key, from file connectivity/source/drivers/postgresql/pq_xkey.cxx. The properties supported by that are defined in pq_statics.cxx (look for the line statics.refl.key.pProps = ... ) However, the properties of a Key are filled in in Keys::refresh (file pq_xkeys.cxx). This takes it by reading from pg_constraint. I guess the driver will have to "pierce" the view veil and: 1) Check that the view is "simple" view. 2) Execute the query filling in the underlying *table* name, not the *view* name. 3) Translate column names (in case they are renamed in the view) Unless PostgreSQL has another pg_FOO table (or something in INFORMATION_SCHEMA) that will give the information more easily. I've already tried JDBC, it's not read-only when I add oid, but still it doesn't work because it's too buggy, I can add rows (using instead of rules\triggers), but I can't update rows, it crashes I will start working on the rest, thanks a lot for your pointers Ali? Any progress? Do you still intend to work on that? No feedback since years, let's put it to NEW and unassign. Ali: if you want to get back to it, don't hesitate to assign yourself again. I've tested that if one hacks PostgreSQL to add a primary key on the view (by direct inserts in pg_constraint) and the default values (by direct inserts in pg_attrdef) on the columns of the view, LibreOffice considers the view as an updatable table and it works correctly. |