Bug 67649

Summary: Postgresql SDBC updatable views
Product: LibreOffice Reporter: Ali Joumma <ali.joumma>
Component: LibreofficeAssignee: Ali Joumma <ali.joumma>
Status: ASSIGNED --- QA Contact:
Severity: enhancement    
Priority: medium CC: ali.joumma, lionel
Version: unspecified   
Hardware: All   
OS: All   
Whiteboard:
i915 platform: i915 features:

Description Ali Joumma 2013-08-02 07:19:49 UTC
I don't know if it's the right place, sorry if it's not,

Postgresql starting from 9.1 supports "instead of" triggers which -among other things- used for updatable views
Postgresql starting from 9.3 makes all simple views automatically updatable

I have tested this with ODBC and Access and it worked, the only modification I needed was to add "oid" field to the view (I guess it services as a primary key, not sure how good of an idea it is)

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, I'm building a huge and complicated application, the only thing missing is row-level-security and if I have that I can get away without building a custom web application and only using Libre office

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

thanks a lot for you hard work guys
Comment 1 Lionel Elie Mamane 2013-08-02 07:35:24 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.
Comment 2 Lionel Elie Mamane 2013-08-02 14:01:35 UTC
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.
Comment 3 Ali Joumma 2013-08-02 14:39:57 UTC
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
Comment 4 Lionel Elie Mamane 2014-04-28 16:33:02 UTC
Ali? Any progress? Do you still intend to work on that?

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.