Hi Chris,
Sorry for the late reaction. I'm only now ready to follow up to your
questions below.
> 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.
------------------------------------------------------------------------------
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
