Firebird 3.0 packages

<< Role | IBExpert | Index/Indizes >>

Die deutschsprachige Dokumentation wird seit dem 26. Juli 2016 nicht mehr gepflegt. Aktuelle und vollständige Dokumentation finden Sie auf der englischsprachigen Webseite: IBExpert Documentation


tutorial available

Firebird 3.0 Packages:

Firebird 3.0 Packages

Packages sind ein neuer Datenbankobjekttyp, der ähnlich zu Oracle die logische Gruppierung von Prozeduren und Funktionen ermöglicht.

Ein einfacher Packages Editor wurde in IBExpert Version 2014.01.01 implementiert. Derzeit funktioniert er nur im Bearbeitungsmodus (der Package sollte bereits in der Datenbank vorhanden sind). Die Script-Seite wurde in IBExpert Version 2014.06.17, und der Package Routinen-Explorer in IBExpert Version 2016.03.15.

implementiert.

Die Versionshistorie-, Vergleich- und To-do-Seiten wurden in IBExpert Version 2016.06.19 eingeführt.

Folgendes ist ein Auszug (englischsprachig) aus dem The Firebird 3.0 Release Notes (29 November 2014 - Document v.0300-16 - for Firebird 3.0 Beta 1) Kapitel, Procedural SQL (PSQL):

Packages

A. dos Santos Fernandes

Note: 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 to 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, viz., 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/]

Quelle: Firebird 3.0 Release Notes von Helen Borrie (Collator/Editor): 29. November 2014 - Dokument v.0300-16 - für Firebird 3.0 Beta 1

zurück zum Seitenanfang

Beispiel mit Soundex

Hier ist ein Beispiel eines Packages, basierend auf dem Soundex-Funktion, die wir auch in der IBExpert Dokumentation Kapitel, Firebird 3.0 Stored Funktionen: Beispiel mit Soundex, gezeigt haben.

Sie können sich ein Paket als eine Art versteckte Implementation vorstellen. Zum Beispiel hier haben wir eine Prozedur namens psoundex und die putils Package: 

 ALTER PACKAGE PUTILS
 AS 
 BEGIN
     PROCEDURE PSOUNDEX(
     WORD varchar(1000),
     LNG char(3),
     SLEN bigint = 4)
     RETURNS (SOUNDEX VARCHAR(1000)); -- public procedure
 END

Jetzt werden wir die gleiche Funktion Namen von einem Package implementieren: 

 create or alter function SOUNDEX (
     WORD varchar(1000),
     LNG char(3),
     SLEN bigint = 4)
 returns varchar(1000) 
 AS
 declare variable res varchar(1000);
 begin
   execute procedure putils.psoundex(:word,:lng,:slen) returning_values res;
   return res;
 end

Wenn wir eine länderspezifische Version des Soundex-Funktion hinzufügen, können wir die gleiche Funktionalität implementieren, und hier lediglich auf das Paket referenzieren. Die Implementierung ist in diesem Paket.

Und wir können eine weitere Prozedur pii integrieren, durch Zufügen im Package-Header: 

 procedure pii
 returns (res numeric(18,16))

und dann im Package-Body: 

 procedure pii
 returns (res numeric(18,16))
 as
 begin
   res=3.141592;
   suspend;
 end

Wenn wir dann folgende Abfrage ausführen: 

 select * from putils.pii

erhalten wir das Ergebnis: 

 3.141592

Sie sehen also, Sie können mehrere Prozeduren und/oder Funktionen für globale Implementation in einem Paket kombinieren.

Firebird 3.0 Packages bieten Ihnen Möglichkeiten, Ihren Code modular zu gestalten, bieten Sicherheitsfunktionen und ermöglichen Rechteverwaltung und die Verfolgung von Abhängigkeiten.

zurück zum Seitenanfang

Neues Systemtabelle RDB$PACKAGES

Für die Speicherung der Package-Metadaten ist in Firebird 3.0 eine neue Systemtabelle RDB$PACKAGES hinzugekommen bzw. wurde RDB$PROCEDURES unter anderem mit einem neuen Feld RDB$PACKAGE_NAME erweitert.

zurück zum Seitenanfang
<< Role | IBExpert | Index/Indizes >>