[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: File attachment API proposal v2

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.