Re: File attachment API proposal v2

[Luke <..hidden..>]

On Wed, 6 Jul 2011, Chris Travers wrote:

> This is the second proposal for file attachment API/technical implementation.
>
> The current approach I am taking is to use table inheritance to
> provide a consistent set of tables with different foreign keys.
> Constraints would first-and-foremost be enforced in the database and
> secondarily handled in the application.  An API would be provided in
> SQL and Perl.
>
> This proposal uses PostgreSQL features heavily including the fact that
> by default a referenced field cannot be updated or deleted until all
> references are gone, and the use of table inheritance.
 > This proposal assumes that the following are required:
> 1)  Tracking who uploaded what when, and where a document entered the system.
> 2)  Linking some documents to multiple objects.
> 3)  Linking to external resources.

I'm Not clear on that.  File_base.contents can not be NULL: are you using 
it as a specification for external resources in this context?

> This proposal assumes that the following are not required:
> 1)  Enforcing uniqueness of file content.
>
> The following other assumptions are made:
> 1)  That software which adds files to the system have user accounts too.
>
> The basic table structures are proposed as follows:
[.]
> COMMENT ON COLUMN file_class.ref_key IS

Shouldn't that be file_base.ref_key?  Assuming that was a typo, but just 
trying to understand the  design here.

> $$This column inheriting tables is used to reference the database row for the
> attachment.  Inheriting tables MUST set the foreign key here appropriately.
>
> This can also be used to create classifications of other documents, such as by
> source of automatic import (where the file is not yet attached) or
> even standard,
> long-lived documents.$$;

So, for unattached files, we'd have something like:

CREATE TABLE file_unattached (
check (file_class = 0),	// or 10 or whatever
) inherits (file_base);

When files are later attached to something, the record would have to be 
moved from this to the file_(transaction|order) table?

[.]
> Any feedback?

Yes.  I like it.

It covers all the use cases I can think of.
Not *exactly* in the way I would prefer to see them covered, but in a way 
that will work, and allow the security parameters that are necessary.

Also, it looks easy to work with from a development standpoint.

Thanks for your willingness to hash this out.

Luke