Re: Proposal for file attachment API

[Chris Travers <..hidden..>]

On Sat, Jul 2, 2011 at 1:16 PM, Luke <..hidden..> wrote:

>
> You would have to replicate the handling for other documents, right?  I.E.
> quotes, payments, etc.

transactions would be ar/ap transactions, sales/vendor invoices, GL
transactions.

order would be sales/purchase orders, RFQ's and quotations.

>
>> Where it loses is in the ability to essentially attach a file to both
>> an order and an invoice, so that's the tradeoff.
>
> We have not talked about what happens when you convert an order to an
> invoice.  Did you envision the reference to the attachment going with it
> in your first model?  Obviously that wouldn't happen here--the file would
> have to be uploaded twice.

Correct.  But I wonder if there is a better solution here too, which
is to provide a hyperlink to the original quotation.  Suppose you take
a quotation, convert it to a sales order, and then attach another file
to the quotation?  Now we have no facility to move that over to the
sales order except to close the order out and recreate it, which poses
some reporting problems.

Another problem is that the current schema assumes we want to enforce
a unique constraint on the name of the file and the attached object.
An attached_to table makes that more or less impossible without fairly
expensive triggers, performance-wise.  So I think we'd also have to
ask whether such a constraint is reasonable or desired.

>
> I would rather see it go more generic.
> If you had an attached_to table, containing an ID for the document, and an
> ID for the file, unique keyed to both, it would not matter at the database
> level, which file was associated to which document(s)--a file could be
> associated with a quote, an order, and an invoice, if necessary.

you'd have to have an attached_to table for every table you'd attach
to, correct?  Otherwise you have ambiguous foreign keys, which is
about the worst thing about the old SQL-Ledger db schema, because they
can never be enforced.

>
> You would have to manage security at some other level, I think, although
> I'm not sure you wouldn't already have to do that.

I don't think this is any different security-wise.
>
> In a filesystem context, I am talking about hard links.
>
> Probably though, as I think about it, this would require globally unique
> filenames, and a name comparison with new uploads, possibly followed by a
> content comparison if names match.
> I'm not sure globally unique filenames are such a bad idea anyway.

There's a fairly nasty case here that you can run into.  If globally
unique file names are required, then how do you know in advance what
sort of names are used?  Do we want to expect the users of the system
to all come up with naming conventions that avoid collisions?
>
>> Ok, would you want to have all file attachments enabled/disabled on a
>> per user level. or just per transaction type (financial, order entry,
>> etc)?
>
> If you have the latter, as the more complex case, you get the former by
> inclusion anyway.
>
> I can see a reason for separating financial/transactional, from
> orders/quotes/etc.
>
> It is probably more likely that orders will be posted from external
> systems, than that invoices will.

Well, in 1.3, I think it is relatively safe to say that AR/AP
tansactions (not sales/vendor invoices) may be posted from external
systems.  In fact that's a major reason to look at 1.3, if you need to
do this.  So I wouldn't make that assumption at all.

Best Wishes,
Chris Travers