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 ------------------------------------------------------------------------------ All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2d-c2 _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
