You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by mi...@apache.org on 2005/07/15 17:25:36 UTC
svn commit: r219207 - in /incubator/derby/code/trunk/java:
engine/org/apache/derby/catalog/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/functionTests/te...
Author: mikem
Date: Fri Jul 15 08:25:33 2005
New Revision: 219207
URL: http://svn.apache.org/viewcvs?rev=219207&view=rev
Log:
Fix for DERBY-437.
Currently for compress procedure to work incase of case-sensitive table name,
schema names, they needs to be passed in quotes like
( call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP' , '"Order"' ,1).
This behaviour is not consitent with other procedures and the docs.
This patch makes compress procedure parameters for table names,
schema name should be passed in the case-sensitive form
if they are quoted identfiers and in upper case if they are not quoted SQL
identifiers, similar to other system procedures. Compress procedure
generates ALTER COMPRESS statement.with quoted table name and schema name.
Committed on behalf of: Suresh Thalamati
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java?rev=219207&r1=219206&r2=219207&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java Fri Jul 15 08:25:33 2005
@@ -637,9 +637,10 @@
int sequential)
throws SQLException
{
+
String query =
- "alter table " + schema + "." + tablename + " compress" +
- (sequential != 0 ? " sequential" : "");
+ "alter table " + "\"" + schema + "\"" + "." + "\"" + tablename + "\"" +
+ " compress" + (sequential != 0 ? " sequential" : "");
Connection conn = getDefaultConn();
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out?rev=219207&r1=219206&r2=219207&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out Fri Jul 15 08:25:33 2005
@@ -1082,4 +1082,63 @@
0 rows inserted/updated/deleted
ij> drop table newconglom;
0 rows inserted/updated/deleted
+ij> --test case for bug (DERBY-437)
+--test compress table with reserved words as table Name/schema Name
+create schema "Group";
+0 rows inserted/updated/deleted
+ij> create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
+0 rows inserted/updated/deleted
+ij> insert into "Group"."Order" values(1, 2, 'memory') ;
+1 row inserted/updated/deleted
+ij> insert into "Group"."Order" values(3, 4, 'disk') ;
+1 row inserted/updated/deleted
+ij> insert into "Group"."Order" values(5, 6, 'mouse') ;
+1 row inserted/updated/deleted
+ij> --following compress call should fail because schema name is not matching the way it is defined using delimited quotes.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('GROUP', 'Order' , 0) ;
+ERROR 38000: The exception 'SQL Exception: Schema 'GROUP' does not exist' was thrown while evaluating an expression.
+ERROR 42Y07: Schema 'GROUP' does not exist
+ij> --following compress call should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'ORDER' , 0) ;
+ERROR 38000: The exception 'SQL Exception: 'ALTER TABLE' cannot be performed on 'Group.ORDER' because it does not exist.' was thrown while evaluating an expression.
+ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'Group.ORDER' because it does not exist.
+ij> --following compress should pass.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 0) ;
+0 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 1) ;
+0 rows inserted/updated/deleted
+ij> drop table "Group"."Order";
+0 rows inserted/updated/deleted
+ij> drop schema "Group" RESTRICT;
+0 rows inserted/updated/deleted
+ij> ---test undelimited names( All unquoted SQL identfiers should be passed in upper case).
+create schema inventory;
+0 rows inserted/updated/deleted
+ij> create table inventory.orderTable(id int, amount int, itemName char(20)) ;
+0 rows inserted/updated/deleted
+ij> insert into inventory.orderTable values(101, 5, 'pizza') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(102, 6, 'coke') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(103, 7, 'break sticks') ;
+1 row inserted/updated/deleted
+ij> insert into inventory.orderTable values(104, 8, 'buffolo wings') ;
+1 row inserted/updated/deleted
+ij> --following compress should fail because schema name is not in upper case.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('inventory', 'ORDERTABLE' , 0) ;
+ERROR 38000: The exception 'SQL Exception: Schema 'inventory' does not exist' was thrown while evaluating an expression.
+ERROR 42Y07: Schema 'inventory' does not exist
+ij> --following compress should fail because table name is not in upper case.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ordertable', 0) ;
+ERROR 38000: The exception 'SQL Exception: 'ALTER TABLE' cannot be performed on 'INVENTORY.ordertable' because it does not exist.' was thrown while evaluating an expression.
+ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'INVENTORY.ordertable' because it does not exist.
+ij> --following compress should pass.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ORDERTABLE' , 1) ;
+0 rows inserted/updated/deleted
+ij> drop table inventory.orderTable;
+0 rows inserted/updated/deleted
+ij> drop schema inventory RESTRICT;
+0 rows inserted/updated/deleted
+ij> --end derby-437 related test cases.
+;
ij>
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql?rev=219207&r1=219206&r2=219207&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql Fri Jul 15 08:25:33 2005
@@ -452,3 +452,41 @@
drop table indexes;
drop table oldconglom;
drop table newconglom;
+
+--test case for bug (DERBY-437)
+--test compress table with reserved words as table Name/schema Name
+create schema "Group";
+create table "Group"."Order"("select" int, "delete" int, itemName char(20)) ;
+insert into "Group"."Order" values(1, 2, 'memory') ;
+insert into "Group"."Order" values(3, 4, 'disk') ;
+insert into "Group"."Order" values(5, 6, 'mouse') ;
+
+--following compress call should fail because schema name is not matching the way it is defined using delimited quotes.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('GROUP', 'Order' , 0) ;
+--following compress call should fail because table name is not matching the way it is defined in the quotes.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'ORDER' , 0) ;
+
+--following compress should pass.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 0) ;
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('Group', 'Order' , 1) ;
+drop table "Group"."Order";
+drop schema "Group" RESTRICT;
+---test undelimited names( All unquoted SQL identfiers should be passed in upper case).
+create schema inventory;
+create table inventory.orderTable(id int, amount int, itemName char(20)) ;
+insert into inventory.orderTable values(101, 5, 'pizza') ;
+insert into inventory.orderTable values(102, 6, 'coke') ;
+insert into inventory.orderTable values(103, 7, 'break sticks') ;
+insert into inventory.orderTable values(104, 8, 'buffolo wings') ;
+
+--following compress should fail because schema name is not in upper case.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('inventory', 'ORDERTABLE' , 0) ;
+--following compress should fail because table name is not in upper case.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ordertable', 0) ;
+
+--following compress should pass.
+call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('INVENTORY', 'ORDERTABLE' , 1) ;
+
+drop table inventory.orderTable;
+drop schema inventory RESTRICT;
+--end derby-437 related test cases.
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java?rev=219207&r1=219206&r2=219207&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java Fri Jul 15 08:25:33 2005
@@ -953,7 +953,7 @@
CallableStatement cs = conn.prepareCall(
"CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
cs.setString(1, "APP");
- cs.setString(2, "testLongVarChar");
+ cs.setString(2, "TESTLONGVARCHAR");
cs.setInt(3, 0);
cs.execute();