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

Re: File attachment API proposal v2

Hi guys,

Sorry to come crawling from under my rock so late in the game. Just got back from 3 14hr working days; that leaves little time to join this discussion.

On Wed, Jul 6, 2011 at 10:04 PM, Luke <..hidden..> wrote:
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.

I like it too. If I'm understanding correctly, each new type of attachment needs a new set of functions and table(partitions) to be defined. However, the changes which are required are relatively small in number and nearly copy/paste from other similar usages.
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!

Thanks to the both of you for your willingness to pursue the design and see the discussion through!