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 2006/05/01 21:32:32 UTC
svn commit: r398665 - in /db/derby/code/branches/10.1/java:
engine/org/apache/derby/catalog/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/functionTests/t...
Author: mikem
Date: Mon May 1 12:32:28 2006
New Revision: 398665
URL: http://svn.apache.org/viewcvs?rev=398665&view=rev
Log:
DERBY-437
backporting fix from trunk to 10.1 line (svn 219207), targeted for 10.1.3
release. Original fix notes:
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.
Modified:
db/derby/code/branches/10.1/java/engine/org/apache/derby/catalog/SystemProcedures.java
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/catalog/SystemProcedures.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/catalog/SystemProcedures.java?rev=398665&r1=398664&r2=398665&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/catalog/SystemProcedures.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/catalog/SystemProcedures.java Mon May 1 12:32:28 2006
@@ -644,9 +644,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: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out?rev=398665&r1=398664&r2=398665&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out Mon May 1 12:32:28 2006
@@ -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: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql?rev=398665&r1=398664&r2=398665&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql Mon May 1 12:32:28 2006
@@ -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: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java?rev=398665&r1=398664&r2=398665&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/store/streamingColumn.java Mon May 1 12:32:28 2006
@@ -968,7 +968,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();