Proposal for file attachment API

[Chris Travers <..hidden..>]

Hi all;

So when the next beta ships I will start on adding file attachments to
AR. AP, GL, and order entry items.  This current approach is to
discuss API proposals rather than the usability side.  I will submit a
usability proposal to the -users list once this side is complete.

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.

The basic table structures are proposed as follows:

CREATE TABLE mime_type AS (
id serial not null unique,
mime_type text primary key
);

CREATE TABLE file_class AS (
id serial not null unique,
class text primary key);
);

insert into file_class values (1, 'transaction');
insert into file_class values (2, 'order');

CREATE TABLE file_base AS (
content bytea NOT NULL,
mime_type_id int not null references mime_type(id),
file_name text not null,
description text,
id serial not null unique,
ref_key int not null,
file_class int not null references file_class(id),
primary key (ref_key, file_name)
);

CREATE TABLE file_transaction AS (
check (file_class = 1),
foreign key ref_key REFERENCES transactions(id)
) inherits (file_base);

CREATE TABLE file_order AS (
check (file_class=2),
foreign key ref_key references oe(id)
);

SQL API's would include:

create or replace function file__attach_to_tx
(in_content bytea, in_mime_type_id int, in_file_name text,
in_description text, in_id int, ref_key int, file_class int)
RETURNS file_base.....

create or replace function file__attach_to_order
(in_content bytea, in_mime_type_id int, in_file_name text,
in_description text, in_id int, ref_key int, file_class int)

create or replace function file__list_by(in_ref_key int, in_file_class int)
RETURNS SETOF file_base ......

create or replace function file__get(in_id int, in_file_class int)
RETURNS file_base.....

A Perl class would export these functions to any other Perl programs
while providing nicer handling of database checks.  The old code
sections would have to call these stored procedures directly.  Future
work via an add-on can subclass this module and thus add
functionality.  The idea here is to try to make it as easy as possible
for third party applications to access this functionality with
centrally enforced security and data integrity.

The class would be defined as follows, using Class::Struct:

struct LedgerSMB::DBObject::file_attachement => {
     content => '$',
     mime_type_id =>  '$',
     file_name   =>  '$',
     description =>  '$',
     id =>  '$',
     ref_key =>  '$',
     file_class =>  '$',
}

Any feedback?

Best Wishes,
Chris Travers