Bug 67649 - Postgresql SDBC updatable views
Summary: Postgresql SDBC updatable views
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2013-08-02 07:19 UTC by Ali Joumma
Modified: 2019-04-12 09:48 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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?
Comment 5 Julien Nabet 2016-10-14 18:46:01 UTC
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.