Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PSQL packages [CORE2312] #2736

Closed
firebird-automations opened this issue Feb 8, 2009 · 14 comments
Closed

PSQL packages [CORE2312] #2736

firebird-automations opened this issue Feb 8, 2009 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is related to QA641

Votes: 1

Description:
A package is a group of procedures and functions managed as one entity.

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\>
        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\>

Objectives:
- Make functional dependent code separated in logical modules like programming languages does.

  It's well know in programming world that having code grouped in some way \(for example in
  namespaces, units or classes\) is a good thing\. With standard procedures and functions in the
  database this is not possible\. It's possible to group them in different scripts files, but
  two problems remains:
  1\) The grouping is not represented in the database metadata\.
  2\) They all participate in a flat namespace and all routines are callable by everyone \(not
     talking about security permissions here\)\.

\- Facilitate dependency tracking between its internal routines and between others packaged and
  unpackaged routines\.

  Firebird packages are divided in two pieces: a header \(aka PACKAGE\) and a body \(aka
  PACKAGE BODY\)\. This division is very similar to a Delphi unit\. The header correspond to the
  interface part, and the body correspond to the implementation part\.

  The user needs first to create the header \(CREATE PACKAGE\) and after it the body \(CREATE
  PACKAGE BODY\)\.

  When a packaged routine uses a determined database object, it's registered on Firebird system
  tables that the package body depends on that object\. If you want to, for example, drop that
  object, you first need to remove who depends on it\. As who depends on it is a package body,
  you can just drop it even if some other database object depends on this package\. When the body
  is dropped, the header remains, allowing you to create its body again after change it based on
  the object removal\.

\- Facilitate permission management\.

  It's generally a good practice to create routines with a privileged database user and grant
  usage to them for users or roles\. As Firebird runs the routines with the caller privileges,
  it's also necessary to grant resources usage to each routine, when these resources would not
  be directly accessible to the callers, and grant usage of each routine 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 \(including private\) package body routines,
  but are stored for the package header\. Example usage:
    GRANT SELECT ON TABLE secret TO PACKAGE pk\_secret;
    GRANT EXECUTE ON PACKAGE pk\_secret TO ROLE role\_secret;

\- Introduce private scope to routines making them available only for internal usage in the
  defining package\.

  All programming languages have the notion of routine scope\. But without some form of grouping,
  this is not possible\. Firebird packages also works as Delphi units in this regard\. If a
  routine is not declared on 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\.

Syntax rules:
- A package body should implement all routines declared in the header and in the body start,
with the same signature.

Notes:
- DROP PACKAGE drops the package body before drop its header.
- UDFs (DECLARE EXTERNAL FUNCTION) are currently not supported inside packages.

Examples:
- To come.

Commits: 0145996 ed0e0da

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Packages => PSQL packages

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: eXandr (i.reg)

Is it possible to declare package variables/constants(global, per connection, per transaction) ?
If yes, then it is also required:
1. initialization section/procedure in package on connection.
2. initialization section/procedure in package on transaction.
3. initialization section/procedure in package on start server(for consts);

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Is SQL functions supported in packages ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Yes.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

It not works for me. Could you show example here ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

What's the error?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

CREATE OR ALTER PACKAGE TEST
AS
BEGIN
PROCEDURE P1(I INT) RETURNS (O INT);
END

COMMIT

RECREATE PACKAGE BODY TEST
AS
BEGIN
FUNCTION F1(I INT) RETURNS INT;

PROCEDURE P1(I INT) RETURNS (O INT)
AS
BEGIN
O = F1(I);
END;

FUNCTION F1(I INT) RETURNS INT
AS
BEGIN
RETURN I + 3;
END;
END

COMMIT

EXECUTE PROCEDURE TEST.P1(1)

The assert is failed at JrdStatement::verifyAccess(), at line

	fb\_assert\(routine\);

call stack below

> engine12.dll!Jrd::JrdStatement::verifyAccess(Jrd::thread_db * tdbb=0x000000000377e6c0) Line 446 + 0x72 bytes C++
engine12.dll!CMP_compile2(Jrd::thread_db * tdbb=0x000000000377e6c0, const unsigned char * blr=0x00000000049a0518, unsigned long blr_length=42, bool internal_flag=false, unsigned long dbginfo_length=0, const unsigned char * dbginfo=0x00000000049a0938) Line 203 C++
engine12.dll!JRD_compile(Jrd::thread_db * tdbb=0x000000000377e6c0, Jrd::Attachment * attachment=0x0000000005050040, Jrd::jrd_req * * req_handle=0x00000000049a0d40, unsigned long blr_length=42, const unsigned char * blr=0x00000000049a0518, Firebird::RefPtr<Firebird::AnyRef<Firebird::StringBaseFirebird::StringComparator\ > > * ref_str=0x000000000377df60, unsigned long dbginfo_length=0, const unsigned char * dbginfo=0x00000000049a0938, bool isInternalRequest=false) Line 7387 + 0x3e bytes C++
engine12.dll!prepareStatement(Jrd::thread_db * tdbb=0x000000000377e6c0, Jrd::dsql_dbb * database=0x0000000006c80040, Jrd::jrd_tra * transaction=0x0000000004b70440, unsigned long string_length=30, const char * string=0x00000000025b9610, unsigned short client_dialect=3, unsigned short parser_version=2, bool isInternalRequest=false) Line 2108 + 0x156 bytes C++
engine12.dll!prepareRequest(Jrd::thread_db * tdbb=0x000000000377e6c0, Jrd::dsql_dbb * database=0x0000000006c80040, Jrd::jrd_tra * transaction=0x0000000004b70440, unsigned long stringLength=30, const char * string=0x00000000025b9610, unsigned short clientDialect=3, unsigned short parserVersion=2, bool isInternalRequest=false) Line 1833 C++
engine12.dll!DSQL_prepare(Jrd::thread_db * tdbb=0x000000000377e6c0, Jrd::jrd_tra * transaction=0x0000000004b70440, Jrd::dsql_req * * req_handle=0x0000000006d1e4f8, unsigned long length=30, const char * string=0x00000000025b9610, unsigned short dialect=3, unsigned long item_length=25, const unsigned char * items=0x00000000050244d0, unsigned long buffer_length=65535, unsigned char * buffer=0x0000000004a60040, bool isInternalRequest=false) Line 620 + 0x52 bytes C++
engine12.dll!Jrd::JStatement::prepare(Firebird::IStatus * user_status=0x000000000377f3e0, Firebird::ITransaction * apiTra=0x0000000004961510, unsigned int stmtLength=30, const char * sqlStmt=0x00000000025b9610, unsigned int dialect=32, unsigned int flags=7) Line 4547 C++
fbclient.dll!Why::YStatement::prepare(Firebird::IStatus * status=0x000000000377f3e0, Firebird::ITransaction * transaction=0x0000000002096c28, unsigned int stmtLength=30, const char * sqlStmt=0x00000000025b9610, unsigned int dialect=32, unsigned int flags=7) Line 3673 + 0x88 bytes C++

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Your test works for me:

SQL> EXECUTE PROCEDURE TEST.P1(1)!

       O 

============
4

I tried to run without autocommit and as non-sysdba user. How to reproduce it?

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA641 [ QA641 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Covered by another test(s)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants