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
(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.