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.
Great! Especially the SQL part. As we discussed on IRC before, it would be a very nice signal to users of our APIs if we could document which SQL functions we consider public and which we consider non-public, assuming our public API will come with some kind of stability guarantees.
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');
It's not clear to me what we need the different attachment classes for. Could you explain what you want to use them for? Isn't a file just a file?
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?
Sounds simple enough, technically. I assume the 'how' part (i.e. when to upload, attach, etc.) will be part of the user discussion?
Let me note that as an admin, I'd like to be able to disable uploading of files on a user level; so we should place sufficient authorization on the tables.
Bye,
Erik.