Re: Proposal for file attachment API

[Chris Travers <..hidden..>]

On Sat, Jul 2, 2011 at 5:26 AM, Erik Huelsmann <..hidden..> wrote:

>  Great! Especially the SQL part. As we discussed on IRC before, it would be
> a very nice signal to users of our APIs if we could document which SQL
> functions we consider public and which we consider non-public, assuming our
> public API will come with some kind of stability guarantees.

Agreed.  I think that the pg_autodoc stuff will also be very helpful
in combination with the manual and the COMMENT ON statements.
>
>>
>> The basic table structures are proposed as follows:
>>
>> CREATE TABLE mime_type AS (
>> id serial not null unique,
>> mime_type text primary key
>> );
>>
>> CREATE TABLE file_class AS (
>> id serial not null unique,
>> class text primary key);
>> );
>>
>> insert into file_class values (1, 'transaction');
>> insert into file_class values (2, 'order');
>
> It's not clear to me what we need the different attachment classes for.
> Could you explain what you want to use them for? Isn't a file just a file?

The file_base table is essentially a partitioned table of file
attachments.  The partitioning here occurs primarily for referential
integrity enforcement reasons.  Another possibility would be:

CREATE TABLE file_attachment{
content bytea NOT NULL,
mime_type_id int not null references mime_type(id),
file_name text not null,
description text,
id serial primary key,
ref_key int not null,
);

CREATE TABLE file_to_transaction(
file_id  int references file_attachment(id),
transaction_id int references transactions(id)
);

CREATE TABLE file_to_order (
file_id int references file_attachment(id),
order_id int references oe(id)
);

In terms of theory, this second possibility comes out cleaner, but it
also has two things going against it, in that traditional database
theory doesn't typically assume the possibility of inherited tables in
this way.  These are:

1)  A unified API for file retrieval and attachment operations, and
2)  More complex data integrity rules can be enforced in this second option.

Where it loses is in the ability to essentially attach a file to both
an order and an invoice, so that's the tradeoff.

>
> Sounds simple enough, technically. I assume the 'how' part (i.e. when to
> upload, attach, etc.) will be part of the user discussion?
> Let  me note that as an admin, I'd like to be able to disable uploading of
> files on a user level; so we should place sufficient authorization on the
> tables.

Ok, would you want to have all file attachments enabled/disabled on a
per user level. or just per transaction type (financial, order entry,
etc)?

Best Wishes,
Chris Travers