[
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)
