Thread: Naming conventions for lots of stored procedures
Hi all; One of my applications currently has over 60 stored procedures and future versions will likely have several hundred. I am wondering what folks find to be helpful naming conventions for managing a large number of stored procedures. We tried using double underscores to separate module vs procedure names and that just became a mess. I have found a few possible separators that might possibly work but they are aesthetically revolting (_$ for example, like select test_$echo(1);). I can't imagine I am the first person to run up against this problem and would rather ask advice of more experienced folks then to wander from one maintenance headache into a possibly far worse one. So, what are approaches each of you have taken in the past? Best Wishes, Chris Travers
On 3/10/2010 8:16 PM, Chris Travers wrote: > Hi all; > > One of my applications currently has over 60 stored procedures and > future versions will likely have several hundred. I am wondering what > folks find to be helpful naming conventions for managing a large > number of stored procedures. We tried using double underscores to > separate module vs procedure names and that just became a mess. I > have found a few possible separators that might possibly work but they > are aesthetically revolting (_$ for example, like select > test_$echo(1);). > > I can't imagine I am the first person to run up against this problem > and would rather ask advice of more experienced folks then to wander > from one maintenance headache into a possibly far worse one. > > So, what are approaches each of you have taken in the past? > > Best Wishes, > Chris Traverl > look into schemas. this allow group table and procedure logically and can limit access based on schemas. what i did is group procedures, views, and tables into schemas to keep them logically grouped. in one project there is 300 tables, and 1200 procedures wip (work in process) sales AR AP GL public All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
On Wed, Mar 10, 2010 at 7:08 PM, Justin Graf <justin@magwerks.com> wrote: > look into schemas. > > this allow group table and procedure logically and can limit access > based on schemas. > > what i did is group procedures, views, and tables into schemas to keep > them logically grouped. > in one project there is 300 tables, and 1200 procedures > wip (work in process) > sales > AR > AP > GL > public 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
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. 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) 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) 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 . I wonder if any database out there allows for nesting schemas. 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) 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. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
> 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
On Thu, Mar 11, 2010 at 08:38:46AM -0800, Chris Travers wrote: > > On 3/10/2010 11:52 PM, Chris Travers wrote: > > 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) Why dont you create such a function if you need it? CREATE FUNCTION "AR.Customer.Editing.Delete"(integer) ... Regards, Gerhard
Attachment
2010/3/11 Gerhard Heift <ml-postgresql-20081012-3518@gheift.de>: > On Thu, Mar 11, 2010 at 08:38:46AM -0800, Chris Travers wrote: >> > On 3/10/2010 11:52 PM, Chris Travers wrote: >> > 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) > > Why dont you create such a function if you need it? > > CREATE FUNCTION "AR.Customer.Editing.Delete"(integer) ... it's not good idea. Case sensitive names are usually problem. Customer.Editing.Delete isn't best identifier too - "Editing" is useless. customer_delete is enough. Regards Pavel Stehule > > Regards, > Gerhard > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.10 (GNU/Linux) > > iEYEARECAAYFAkuZH/sACgkQa8fhU24j2fml2gCgkpZfQ53fxotGDBoG4BYgIUZG > 2vUAn19yVUFq6hzAHFN0hAONiydtqq3B > =ZLVm > -----END PGP SIGNATURE----- > >