Re: Naming conventions for lots of stored procedures - Mailing list pgsql-general

> On 3/10/2010 11:52 PM, Chris Travers wrote:
>>
>> There are two major limitations here of schemas:
>>
>> 1)  They can't be nested leading again to possible namespace ambiguity.
>> 2)  there are a number of requests to try to get the application to
>> install into an arbitrary, nonpublic schema.
>>
>> If schemas could be nested this would solve both of these problems.
>>
>> However, if the above is anywhere near a complete list of schemas for
>> 1200 procedures, you must also have some strong naming conventions to
>> prevent collisions.  I would be interested in what they are.
>>
>> Best wishes,
>> Chris Travers
>>
>
> This is an app i took over and there was no strong name convention  plus
> an godly  amount of overloaded procedures.

In the current framework we can't handle overloaded functions.  The
program is written in Perl (with no strong typing).  The next version
will probably allow a limited amount of overloading.
>
> the procedures use very very long names  example
> createardebitmemo(int, text, text date, numeric, text, int, int mint
> date, int int, numeric )
> createarcreditmemo(integer, text, text, date, numeric, text, integer,
> integer, integer, date, integer, integer, numeric, integer, integer)

We'd probably add underscores...  Maybe putting the package last would
be better than putting it first.
>
> this means Create Accounts Receiver Debit Memo
>
> deleteaccount(integer)
> deleteaccountingperiod(integer)
> deleteaccountingyearperiod(integer)
> deletecustomer(integer)
>
> after the moving the functions into schemas this is how one would/could
> call them.
> gl.deleteaccount(integer)
> gl.deleteaccountingperiod(integer)
> gl.deleteaccountingyearperiod(integer)
> ar.deletecustomer(integer)
> ar.createardebitmemo(int, text, text date, numeric, text, int, int mint
> date, int int, numeric )
> ar.createardreditmemo(integer, text, text, date, numeric, text, integer,
> integer, integer, date, integer, integer, numeric, integer, integer)

Sure.  This can be handled by our stored procedure mapping API.

>
> Now one problem is if 2 functions have the same name, same number and
> type of inputs then Postgresql will throw ambiguous error,  if the
> search path includes the 2 schemas where  the functions are stored .

We use fully qualified function names in our calls.  Currently the
schema is admin-definable.  If it were to be set per module, that
would be possible too.
>
> I wonder if any database out there allows for nesting schemas.

Oracle allows nested packages which provides some similar functionality.

The manual recommends using schemas instead of packages when porting
from Oracle.  So at least some RDBMS's provide some sort of nested
logical grouping to functions.

> Which
> i'm at a loss why nesting would help solve any problem what so ever.  I
> imagine the search path on some connections would be all inclusive so
> ambiguous names is not solved.   Also would not be a big fan typing
> something like
>
> AR.Customer.Editing.Delete(ID)

Well, the way we would use something like this would be (Perl pseudocode here):

our const $nspname = 'invoice.ar'

sub save_invoice {
    my ($self) = @_;
    $self->exec_mapped_proc({ procname => 'save'});
}

sub approve_invoice {
  my ($self) = @_;
  $self->exec_mapped_proc({ procname => 'approve'});
}

exec_mapped_proc then resolves the procname to its fully qualified
name (invoice.ar.save, invoice.ar.approve), discovers named arguments,
maps them in, and calls it.

>
> what has been gained???
>
> think if the search path was all inclusive
>  AR.Contact.Editing.Delete
> WIP.WorkOrder.Delete
>
> and this was called
> Select Delete(5784);
>
> Postgresql will through ambiguous error which delete, the one in
> AR.Customer, AR.Contact or WIP.Workorder  schema.
>
The way I look at it, boring stuff can be automated.  We intend to
provide reference implementations for how this mapping works anyway so
that addons can be written perhaps in other languages.

Best Wishes,
Chris Travers