Firebird Documentation IndexFirebird 3.0.6 Release NotesProcedural SQL (PSQL) → Packages
Firebird Home Firebird Home Prev: PSQL Sub-routinesFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: DDL triggers

Packages

A. dos Santos Fernandes

Signatures
Packaging Syntax
Simple Packaging Example

Acknowledgement

This feature was sponsored with donations gathered at the fifth Brazilian Firebird Developers' Day, 2008

A package is a group of procedures and functions managed as one entity. The notion of packaging the code components of a database operation addresses several objectives:

Modularisation

The idea is to separate blocks of interdependent code into logical modules, as programming languages do.

In programming it is well recognised that grouping code in various ways, in namespaces, units or classes, for example, is a good thing. With standard procedures and functions in the database this is not possible. Although they can be grouped in different script files, two problems remain:

  1. The grouping is not represented in the database metadata.

  2. Scripted routines all participate in a flat namespace and are callable by everyone (we are not referring to security permissions here).

To facilitate dependency tracking

We want a mechanism to facilitate dependency tracking between a collection of related internal routines, as well as between this collection and other routines, both packaged and unpackaged.

Firebird packages come in two parts: a header (keyword PACKAGE) and a body (keyword PACKAGE BODY). This division is very similar to a Delphi unit, the header corresponding to the interface part and the body corresponding to the implementation part.

The header is created first (CREATE PACKAGE) and the body (CREATE PACKAGE BODY) follows.

Whenever a packaged routine determines that it uses a certain database object, a dependency on that object is registered in Firebird system tables. Thereafter, to drop, or maybe alter that object, you first need to remove what depends on it. As it is a package body that depends on it, that package body can just be dropped, even if some other database object depends on this package. When the body is dropped, the header remains, allowing you to recreate its body once the changes related to the removed object are done.

To facilitate permission management

It is good practice in general to create routines to require privileged use and to use roles or users to enable the privileged use. As Firebird runs routines with the caller privileges, it is necessary also to grant resource usage to each routine when these resources would not be directly accessible to the caller. Usage of each routine needs to be granted to users and/or roles.

Packaged routines do not have individual privileges. The privileges act on the package. Privileges granted to packages are valid for all package body routines, including private ones, but are stored for the package header.

For example:

  GRANT SELECT ON TABLE secret TO PACKAGE pk_secret;
  GRANT EXECUTE ON PACKAGE pk_secret TO ROLE role_secret;
            
To enable private scope

This objective was to introduce private scope to routines; that is, to make them available only for internal usage within the defining package.

All programming languages have the notion of routine scope, which is not possible without some form of grouping. Firebird packages also work like Delphi units in this regard. If a routine is not declared in the package header (interface) and is implemented in the body (implementation), it becomes a private routine. A private routine can only be called from inside its package.

Signatures

For each routine that is assigned to a package, elements of a digital signature (the set of [routine name, parameters and return type]) are stored in the system tables.

The signature of a procedure or routine can be queried, as follows:

SELECT...
-- sample query to come
      

Packaging Syntax

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

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

    <function_decl> ::=
        FUNCTION <name> [( <parameters> )] RETURNS <type>

    <procedure_decl> ::=
        PROCEDURE <name> [( <parameters> ) [RETURNS ( <parameters> )]]

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

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

    <function_impl> ::=
        FUNCTION <name> [( <parameters> )] RETURNS <type>
        AS
        BEGIN
           ...
        END
        |
        FUNCTION <name>  [( <parameters> )] RETURNS <type>
            EXTERNAL NAME '<name>' ENGINE <engine>

    <procedure_impl> ::=
        PROCEDURE <name> [( <parameters> ) [RETURNS ( <parameters> )]]
        AS
        BEGIN
           ...
        END
        |
        PROCEDURE <name> [( <parameters> ) [RETURNS ( <parameters> )]]
            EXTERNAL NAME '<name>' ENGINE <engine>

    <drop_package_header> ::=
        DROP PACKAGE <name>

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

Syntax rules

  • All routines declared in the header and at the start of the body should be implemented in the body with the same signature, i.e., you cannot declare the routine in different ways in the header and in the body.

  • Default values for procedure parameters cannot be redefined in <package_item> and <package_body_item>. They can be in <package_body_item> only for private procedures that are not declared.

Notes

  • DROP PACKAGE drops the package body before dropping its header.

  • The source of package bodies is retained after ALTER/RECREATE PACKAGE. The column RDB$PACKAGES.RDB$VALID_BODY_FLAG indicates the state of the package body. See Tracker item CORE-4487.

  • UDF declarations (DECLARE EXTERNAL FUNCTION) are currently not supported inside packages.

  • Syntax is available for a description (COMMENT ON) for package procedures and functions and their parameters. See Tracker item CORE-4484.

Simple Packaging Example

SET TERM ^;
-- package header, declarations only
CREATE OR ALTER PACKAGE TEST
AS
BEGIN
  PROCEDURE P1(I INT) RETURNS (O INT); -- public procedure
END

-- package body, implementation
RECREATE PACKAGE BODY TEST
AS
BEGIN
  FUNCTION F1(I INT) RETURNS INT; -- private function
  PROCEDURE P1(I INT) RETURNS (O INT)
  AS
  BEGIN
  END
  FUNCTION F1(I INT) RETURNS INT
  AS
  BEGIN
    RETURN 0;
  END
END ^
      

Note

More examples can be found in the Firebird installation, in ../examples/package/.

Prev: PSQL Sub-routinesFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: DDL triggers
Firebird Documentation IndexFirebird 3.0.6 Release NotesProcedural SQL (PSQL) → Packages