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!
Bye,
Erik.