[FB-Tracker] Created: (CORE-5583) Through rowsets

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[FB-Tracker] Created: (CORE-5583) Through rowsets

JIRA tracker@firebirdsql.org
Through rowsets
---------------

                 Key: CORE-5583
                 URL: http://tracker.firebirdsql.org/browse/CORE-5583
             Project: Firebird Core
          Issue Type: New Feature
          Components: API / Client Library, Engine
            Reporter: Denis


Through rowsets

Requirements:
* Through rowset must have 64-bit integer identifier
  (further "ROWSET_ID") representing it;
* Manipulations with a through rowset are allowed within
  the BEGIN...END block only;
* Through rowset can be used as parameter in invokes
  and returns of stored procedures and/or execute blocks
  by own ROWSET_ID only;
* The lifetime of through rowset depends on method
  and location of the declaration;
* Through rowset (own ROWSET_ID) can be checked for NULL
  or rowset can be cleared by assignment ROWSET_ID to NULL;
* Through rowset can be assigned to another through rowset
  with same domain by operator "=" and in this case,
  another rowset will get a copy of data and new ROWSET_ID;

Futures:
* Reducing the overhead of transfer rowsets in/from SP or EB
  as opposing of transfer by SUSPEND and recursive invokes
  of stored procedure;
* Using non-overlapping rowsets with same name as opposing
  of temporary tables;

================================================
Defenition sintax of domain that can be used
to transaction-wide through rowset declaration:
================================================
CREATE DOMAIN <through_rowset_domain_name>
  AS ROWSET (<temporary_table-like_fields_&_constraints_definition>);

================================================
Instance declaration sintax of through rowset in
stored procedure, trigger or execute block, that
can be used for transaction-wide through rowsets:
================================================
DECLARE VARIABLE <through_rowset_name> <through_rowset_domain_name>;

Example:
--------
CREATE DOMAIN SomeRowsetDomain
  AS ROWSET
  (
    id    INTEGER NOT NULL PRIMARY KEY,
    name  VARCHAR(200) NOT NULL UNIQUE
  );

SET TERM ^;
CREATE OR ALTER PROCEDURE procCalc
  (
    someRowset SomeRowsetDomain
  )
AS
BEGIN
  -- change the through rowset "someRowset"
END^
SET TERM ;^

SET TERM ^;
CREATE OR ALTER PROCEDURE procResult
  (
    someRowset SomeRowsetDomain
  )
  RETURNS
  (
    id    INTEGER,
    name  VARCHAR(200)
  )
AS
BEGIN
  EXECUTE PROCEDURE procCalc (:someRowset);
  FOR
      SELECT id, name
        FROM :someRowset
        ORDER BY id
        INTO: id, name
    DO
      SUSPEND;
END^
SET TERM ^;

-- through rowset initialization
EXECUTE BLOCK
  (
    id    INTEGER NOT NULL,
    name  VARCHAR(200) NOT NULL
  )
  RETURNS
  (
    -- return ROWSET_ID to client
    someRowset SomeRowsetDomain
  )
AS
BEGIN
  INSERT INTO :someRowset (id, name) VALUES (:id, :name);
END

-- Multiple execution of client's query in same transaction
-- to fill the through rowset by ROWSET_ID.
-- Client can use this to manually fill the through rowset
-- from a UI grid
EXECUTE BLOCK
  (
    someRowset SomeRowsetDomain NOT NULL,
    id    INTEGER NOT NULL,
    name  VARCHAR(200) NOT NULL
  )
AS
BEGIN
  INSERT INTO :someRowset (id, name) VALUES (:id, :name);
END

-- Obtaining the result
SELECT * FROM procResult(:someRowset)
or
EXECUTE BLOCK
  (
    someRowset SomeRowsetDomain
  )
  RETURNS
  (
    id    INTEGER,
    name  VARCHAR(200)
  )
AS
BEGIN
  EXECUTE PROCEDURE procCalc (:someRowset);
  FOR
      SELECT id, name
        FROM :someRowset
        ORDER BY id
        INTO: id, name
    DO
      SUSPEND;
END

================================================
Instance declaration sintax of through rowset in
stored procedure, trigger or execute block, that
can be used for Trigger/EB/SP-wide through rowsets.
In this case, through rowset can not be used as
input/output parameter and it's lifetime limited
by a runtime of SP/EB/Trigger (similar to cursor)
================================================
DECLARE VARIABLE <through_rowset_name>
  AS ROWSET (<temporary_table-like_fields_&_constraints_definition>);


--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Loading...