Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 254311068D for ; Wed, 23 Oct 2013 08:35:43 +0000 (UTC) Received: (qmail 13632 invoked by uid 500); 23 Oct 2013 08:35:42 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 13601 invoked by uid 500); 23 Oct 2013 08:35:42 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 13588 invoked by uid 99); 23 Oct 2013 08:35:41 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 Oct 2013 08:35:41 +0000 Date: Wed, 23 Oct 2013 08:35:41 +0000 (UTC) From: "Knut Anders Hatlen (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DERBY-6370) dblook doesn't schema-qualify identifiers in trigger actions MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-6370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13802713#comment-13802713 ] Knut Anders Hatlen commented on DERBY-6370: ------------------------------------------- For VIEWs, dblook has solved the problem by generating SET SCHEMA statements. Normalizing the trigger actions and qualifying unqualified names is more elegant, but adding SET SCHEMA statements is easier to implement. > 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 > > 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.1#6144)