[plug] OpenOffice.org data connection - Database for the rest of us

[Craig Ringer]

> For those of you that missed out on the July PLUG Seminar tonight: 
> OpenOffice.org data connection - Database for the rest of us presented 
> by Mike Gilks, see:
> http://www.westnet.com.au/mike.gilks/plug/index.html

BTW, you don't need the macro in OO.o 1.1beta2, I just tested.

I haven't figured out how to get a writeable subquery though, at least 
without using updateable views (these qualify as "the hard way" to do 
simple things).

The subquery linkage seems to requrire an SQL select statement, so 
you're not looking straight at the table but rather at a row or set of 
data from the table. Ideally, you'd be able to simply link two fields so 
that when the master field - a foreign or primary key - is updated it 
causes the subquery to be updated to display the appropriate referenced 
record. Think the master query having a customer_id field that, when 
updated, causes the subform to skip to the record in the "customer" 
table that has customer_id=[value_in_master_field] . Sorry, sentance 
from hell. This would make it easy to have a subform in which the data 
could be easily changed.

The only way I can think of doing it right now is by using a macro (if 
possible) to change which record the subform is currently displaying 
when the field in the master form is updated. A UI would be really nice 
for this, say an pulldown on a subform's properties that lets you pick 
which field in the master form it should track (so that the primary key 
of the displayed record in the subform was always equal to the value of 
this tracked value).

Thinking about it some more, I'm afraid that it might be better 
implemented in the database with updateable views - but I don't think 
MySQL supports those, just PostgreSQL and probably Firebird and/or 
SAP-DB. Updateable views are a right pain anyway, but they're more 
flexible than using foreign key (or the primary key of you're viewing 
the same table in the subquery) relationships to synchronise views in 
OO.o forms would be.

As far as I understand it, an updateable view is a database view with 
some additional information telling the database how to translate an 
UPDATE query run on the view to alter the real tables the view draws its 
data from. I seem to remember the PostgreSQL manual has some good info 
on views and updateable views. Views are essentially just a stored query 
in the database, so apps can access a view like any normal (though 
generally read only) table.

Urrggh. 2:00am is not the time for this... sorry if I've rambled.

Craig Ringer