Re: [GENERAL] Naming conventions for lots of stored procedures
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 our proprietary 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 information proprietary 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 that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
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 signature.asc Description: Digital signature
Re: [GENERAL] Naming conventions for lots of stored procedures
2010/3/11 Gerhard Heift ml-postgresql-20081012-3...@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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
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 our proprietary 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 information proprietary 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 that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
On Wed, Mar 10, 2010 at 7:08 PM, Justin Graf jus...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
