All,

Treatment of stored procedures and functions individually is problematic
in any DBMS I know. The issues, generally are:
1) A flat namespace is not good as we've learned with every modern
programming language. It's not good in the database either. And schemas
is not a good feature to separate them.
2) Interdependencies between them becomes a nightmare. Some ones do
invalidations. We simple don't allow changes that break dependencies.
3) In the future, I suppose we may have some system procedures and more
functions. Using the prefix RDB$ and put it all together on the flat
namespace will certainly be not good.
4) With external engines, there will be tools generating group of
procedures and functions declarations automatically. For example, one
can read a Java class file and just generate a SQL script to publish all
the functions to the database. Tools can't manage group of individual
procedures and functions satisfactorily when the programmer changes the
sources.

So far, I propose the creation of PACKAGEs. My spec. is based on Oracle
packages, a feature widely used by its users. Initial implementation
will have only procedures and functions. Some others features like
package cursors and variables may be added later. Packages are also
similar to Delphi Units (that have interface and implementation parts).

With packages, the database developer can create two different scripts
for each logical module. The header and the body scripts. He can then
run all header scripts and all body scripts and have all the logic created.

If he wants to delete all the logic, he can just drop all package bodies
and then drop all headers. Dependencies problems are solved as dropping
the body drops all registered dependencies.

It makes things much more simple and elegant. There is no reason to not
have them. :-)

Syntax:

<package_header> ::=
{ CREATE [OR ALTER] | ALTER } PACKAGE <name>
AS
BEGIN
[ <package_item> ... ]
END

<package_item> ::=
<function_decl> ; |
<procedure_decl> ;

<function_decl> ::=
FUNCTION <name> ...(everything of DECLARE EXTERNAL FUNCTION - and
new CREATE FUNCTION with EXTERNAL NAME ... ENGINE)

<procedure_decl> ::=
PROCEDURE <name> ...(everything before AS of CREATE PROCEDURE)

<package_body> ::=
{ CREATE [OR ALTER] | ALTER } PACKAGE BODY <name>
AS
BEGIN
[ <package_item> ... ]
[ <package_body_item> ... ]
END

<package_body_item> ::=
<function_impl> ; |
<procedure_impl> ;

<function_impl> ::=
<function_decl> -- will support PSQL functions when we have them

<procedure_impl> ::=
PROCEDURE <name> ... AS BEGIN ... END

<drop_package_header> ::=
DROP PACKAGE <name>

<drop_package_body> ::=
DROP PACKAGE BODY <name>

Dumb example:

SET TERM !;

CREATE PACKAGE UTIL
AS
BEGIN
FUNCTION SUBSTRLEN CSTRING(255) NULL, SMALLINT, SMALLINT
RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'IB_UDF_substrlen' MODULE_NAME 'ib_udf';

PROCEDURE TEST RETURNS (N INTEGER);
END!

CREATE PACKAGE BODY UTIL
AS
BEGIN
PROCEDURE TEST RETURNS (X VARCHAR(255))
AS
BEGIN
X = SUBSTRLEN('TEST', 2, 3); -- same as TEST.SUBSTRLEN
END;
END!

EXECUTE PROCEDURE UTIL.TEST!

Semantics:

Items that are declared in the PACKAGE header are public and may be
called outside the package. The others are private to the package body.

Private items may be declared and implemented in the body or just
implemented. Declaration in the body is necessary when an item depends
on another one being implemented after it or there are circular
dependencies.

Security should act on the entire package and not in individual items,
i.e. we should have GRANT EXECUTE ON PACKAGE <name> ...

Implementation:

New system table RDB$PACKAGES with columns:
RDB$PACKAGE_NAME
RDB$PACKAGE_HEADER_SOURCE
RDB$PACKAGE_BODY_SOURCE
RDB$OWNER
RDB$SYSTEM_FLAG

Column RDB$SCOPE (to represent public and non-public) added to tables
RDB$FUNCTIONS and RDB$PROCEDURES.

Column RDB$PACKAGE_NAME added to tables RDB$FUNCTIONS,
RDB$FUNCTION_ARGUMENTS, RDB$PROCEDURES and RDB$PROCEDURE_PARAMETERS.

Current PROCEDURE and FUNCTION commands and usages will only look for
records with NULL RDB$PACKAGE_NAME on the above tables.

Package DDL commands will change the tables under savepoint control.

CREATE PACKAGE creates all procedures with NULL BLR. ALTER PACKAGE and
DROP PACKAGE BODY removes all private items and set BLR of all public
procedures to NULL. These procedures doesn't run but others could still
reference them at compilation time.

DROP PACKAGE drops the header and the body.

CREATE/ALTER PACKAGE BODY should correctly implement all package
procedures declared or will fail completely.

Sources of individual procedures will not be stored. The package header
and body sources will be stored in RDB$PACKAGES.

Comments?


Adriano