File attachment API proposal v2

[Chris Travers <..hidden..>]

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.

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:

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

CREATE TABLE file_class (
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 (
content bytea NOT NULL,
mime_type_id int not null references mime_type(id),
file_name text not null,
description text,
uploaded_by int default not null get_my_entity_id() references entity(id),
uploaded_at timestamp not null default now();
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, file_class)
);

COMMENT ON TABLE file_base IS
$$Abstract table, holds no records.  Inheriting table store actual file
attachment data.$$;

COMMENT ON COLUMN file_class.ref_key IS
$$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.$$;

-- rules would be added to rewrite inserts

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

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

CREATE TABLE file_secondary_attachment (
file_id int not null,
source_class int references file_class(id),
ref_key int not null,
dest_class int references file_class(id),
attached_by int not null references entity(id),
attached_at timestamp not null default now()
);

-- rules would be added to rewrite inserts

COMMENT ON  TABLE file_secondary_attachment IS
$$This is another abstract table, inheriting tables provide
secondary links between the file and other database objects.

Due to the nature of database inheritance and unique constraints
in PostgreSQL, this must be partitioned in a star format.$$;

CREATE TABLE file_tx_to_order (
foreign key file_id references file_transaction(id),
foreign key ref_key references oe(id),
check source_class = 1,
check dest_class = 2
) INHERITS (file_secondary_attachment);

CREATE TABLE file_order_to_order (
foreign key file_id references file_order(id),
foreign key ref_key references oe(id),
check source_class = 2,
check dest_class = 2
) INHERITS (file_secondary_attachment);


CREATE TABLE file_order_to_tx (
foreign key file_id references file_order(id),
foreign key ref_key references transactions(id),
check source_class = 2,
check dest_class = 1
) INHERITS (file_secondary_attachment);

SQL API's would include:

create or replace function file__attach,
(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__link
(in_file_id int, in_source_class int, in_ref_key int, in_dest_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.....

CREATE OR REPLACE view file_tx_links
-- view of links FROM transactions

CREATE OR REPLACE view file_order_links
-- view of links FROM orders

CREATE OR REPLACE VIEW file_links
AS
select * from  file_tx_links
UNION
select * from file_order_links;
-- view of all links

We can't use inheritance on views, so to get secondary linking
gracefully we'd probably
have to add our own catalog and a function to rebuild the file_links view based
on appropriate views that would be plugged in.  However, this would
allow linking to be
purely a UI change.

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