db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6370) dblook doesn't schema-qualify identifiers in trigger actions
Date Tue, 29 Apr 2014 13:57:17 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Knut Anders Hatlen updated DERBY-6370:
--------------------------------------

    Attachment: d6370-1a.diff

Rewriting the trigger text to have qualified names was trickier than the similar rewrite of
CHECK constraints in DERBY-6362. What's different with triggers, is that the statements are
stored twice in slightly different forms. For example, given the trigger

{code:sql}
  CREATE TRIGGER TR AFTER INSERT ON T1
    REFERENCING NEW TABLE AS NEW
    INSERT INTO T2 SELECT Y FROM NEW, T3 WHERE NEW.X = T3.X
{code}

the trigger action text will be stored in SYS.SYSTRIGGERS as

{code:sql}
INSERT INTO T2 SELECT Y FROM NEW, T3 WHERE NEW.X = T3.X
{code>

and in SYS.SYSSTATEMENTS as

{code:sql}
INSERT INTO T2 SELECT Y FROM new org.apache.derby.catalog.TriggerNewTransitionRows() NEW ,
T3 WHERE NEW.X = T3.X
{code}

dblook will need the first one of these to be rewritten and stored with fully qualified table
names. However, in order to find the fully qualified names, we need to bind the trigger action
text, and only the text stored in SYS.SYSSTATEMENTS is bindable. (The first one is not bindable
because of the reference to NEW, which is not a proper table.)

So in order to rewrite the identifiers, we'll have to (a) transform the action text from the
first form to the second form, (b) bind it, (c) qualify the identifiers in the transformed
action text, and (d) bring the qualified identifiers over to the original text stored in SYS.SYSTRIGGERS.

(a) and (b) are already done by the current code. (c) can be done using the mechanisms developed
in DERBY-6362. (d) is what requires a new approach.

To help solve (d), I chose to enhance the current code for (a) so that it also produced a
translation table that could be used to map positions in the transformed SQL text back to
positions in the original text. This way, when qualify names in the transformed text in (c),
we also know where to apply the same changes to the original text.

The attached patch, d6370-1a.diff, uses this approach. All regression tests (except upgrade
tests - DERBY-6547) ran cleanly with the patch.

List of changes made by the patch:

*impl/sql/catalog/DataDictionaryImpl.java*

Change getTriggerActionString() so that it records the positions where it has made changes
to the original trigger action text when replacing transition variables with VTI calls. This
extra information is used by CreateTableNode to map positions of identifiers back to the original
trigger action text when qualifying them.

*impl/sql/compile/CreateTriggerNode.java*

Change the code that replaces transition tables with VTIs in a similar manner to DataDictionaryImpl.getTriggerActionString(),
so that it records where in the text changes have been made.

Qualify names in actionText, originalActionText, whenText and originalWhenText by searching
for TableName nodes and replacing them with their fully qualified equivalents.

*impl/sql/compile/sqlgrammar.jj*

Make sure begin and end offsets of more query tree nodes are recorded by the parser. These
offsets are needed when rewriting SQL fragments later. (This change may allow us to remove
the actionOffset and whenOffset fields in CreateTriggerNode, and the corresponding parameters
in DataDictionary.getTriggerActionString(). Will look into that later and post a followup
patch if the code can be simplified.)

*iapi/sql/dictionary/DataDictionary.java*
*iapi/sql/dictionary/TriggerDescriptor.java*
*impl/sql/execute/AlterTableConstantAction.java*
*impl/storeless/EmptyDictionary.java*

Minor signature changes needed because of the change in DataDictionaryImpl.

*impl/sql/compile/OffsetOrderVisitor.java*
*impl/sql/compile/QueryTreeNode.java*

Make OffsetOrderVisitor non-public, and instead expose the functionality through a public
helper method in QueryTreeNode. This simplifies the calling code.

*impl/sql/compile/StaticMethodCallNode.java*
*impl/sql/compile/AggregateNode.java*

Preserve the name by which a user-defined aggregate is called. Without this fix, the name
of UDAs used in the trigger action are not found by the visitors, and the names will stay
unqualified after the rewrite.

*impl/sql/compile/FromBaseTable.java*

Make sure TableName nodes in FROM lists are always bound. Without this fix, the TableName
node will stay unbound if no column in the table is references (for example {{SELECT 1 FROM
T}}), and it will stay unqualified after the rewrite.

*impl/sql/compile/NextSequenceNode.java*

Make sure the name of a sequence generator is bound, so that its name can be qualified in
the trigger action.

*functionTests/master/DerbyNetClient/dblook_test_net.out*
*functionTests/master/DerbyNetClient/dblook_test_net_territory.out*
*functionTests/master/dblook_test.out*
*functionTests/master/dblook_test_territory.out*
*functionTests/master/derived.out*
*functionTests/tests/lang/TriggerGeneralTest.java*

Update test canons to reflect that identifiers used in triggers are normalized in the system
tables.

*functionTests/tests/lang/TriggerTest.java*

Add test case that verifies that all kinds of identifiers are qualified before they are stored,
when used in a trigger action.

> dblook doesn't schema-qualify identifiers in trigger actions
> ------------------------------------------------------------
>
>                 Key: DERBY-6370
>                 URL: https://issues.apache.org/jira/browse/DERBY-6370
>             Project: Derby
>          Issue Type: Bug
>          Components: Tools
>    Affects Versions: 10.10.1.1
>            Reporter: Knut Anders Hatlen
>            Assignee: Knut Anders Hatlen
>         Attachments: d6370-1a.diff
>
>
> dblook doesn't qualify identifiers in the trigger action if they weren't explicitly qualified
in the original CREATE TRIGGER statement.
> Example:
> ij version 10.10
> ij> connect 'jdbc:derby:db;create=true';  
> ij> create table s1.t1(x int);
> 0 rows inserted/updated/deleted
> ij> create table s2.t2(x int);
> 0 rows inserted/updated/deleted
> ij> set schema s1;
> 0 rows inserted/updated/deleted
> ij> create trigger tr1 after insert on t1 insert into s2.t2 select * from t1;
> 0 rows inserted/updated/deleted
> ij> set schema s2;
> 0 rows inserted/updated/deleted
> ij> create trigger tr2 after insert on s1.t1 insert into t2 select * from s1.t1;
> 0 rows inserted/updated/deleted
> ij> exit;
> Then run dblook on the created database:
> -- Timestamp: 2013-10-04 12:46:14.974
> -- Source database is: db
> -- Connection URL is: jdbc:derby:db
> -- appendLogs: false
> -- ----------------------------------------------
> -- DDL Statements for schemas
> -- ----------------------------------------------
> CREATE SCHEMA "S1";
> CREATE SCHEMA "S2";
> -- ----------------------------------------------
> -- DDL Statements for tables
> -- ----------------------------------------------
> CREATE TABLE "S1"."T1" ("X" INTEGER);
> CREATE TABLE "S2"."T2" ("X" INTEGER);
> -- ----------------------------------------------
> -- DDL Statements for triggers
> -- ----------------------------------------------
> CREATE TRIGGER "S1"."TR1" AFTER INSERT ON "S1"."T1" FOR EACH STATEMENT insert into s2.t2
select * from t1;
> CREATE TRIGGER "S2"."TR2" AFTER INSERT ON "S1"."T1" FOR EACH STATEMENT insert into t2
select * from s1.t1;
> dblook should either qualify all identifiers in the trigger actions, or contain SET SCHEMA
statements before each of the CREATE TRIGGER statements to ensure the correct implicit schema
is chosen.



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message