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/09/22 21:17:48 UTC
svn commit: r449058 - in /db/derby/code/branches/10.1/java:
engine/org/apache/derby/impl/sql/catalog/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/functi...
Author: mikem
Date: Fri Sep 22 12:17:47 2006
New Revision: 449058
URL: http://svn.apache.org/viewvc?view=rev&rev=449058
Log:
DERBY-1854
contributed by suresht
merging change from trunk to 10.1 codeline.
DERBY-1854 (SYSCS_COMPRESS_TABLE corrupts table with a single column which is bo
th a primary key and a foreign key)
Problem was all the conglomerate descriptor entries in sys.sysconglomerates
were not getting updated with new conglomerate number generated for an index
during compress/bulk-insert, when an index is shared. Update code was assuming
conglomerate id is common when an index is shared, but that is not correct.
ConglomerateID's in sys.sysconglomerates are unique.
This patch modifies the update conglomerate descriptor code to update each
conglomerate descriptor separately using conglomerateID as the key, when
there are more than one conglomerate descriptor referring to the same
conglomerate(conglomerate number).
This patch also adds a test-case to test import into a table that has the same c
olumn as a primary key and foreign key, import was also causing corruption beca
use of the same problem. See DERBY-1641 , for details.
Modified:
db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.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/master/importExportThruIJ.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/tools/importExportThruIJ.sql
Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?view=diff&rev=449058&r1=449057&r2=449058
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Fri Sep 22 12:17:47 2006
@@ -5294,8 +5294,11 @@
/**
* Update the conglomerateNumber for an array of ConglomerateDescriptors.
- * In case of more than one ConglomerateDescriptor, they are for duplicate
- * indexes sharing one conglomerate.
+ * In case of more than one ConglomerateDescriptor, each descriptor
+ * should be updated separately, conglomerate id is not same for all
+ * the descriptors. Even when indexes are sharing the same
+ * conglomerate(conglomerate number), conglomerate ids are unique.
+ *
* This is useful, in 1.3, when doing a bulkInsert into an
* empty table where we insert into a new conglomerate.
* (This will go away in 1.4.)
@@ -5314,38 +5317,35 @@
throws StandardException
{
ExecIndexRow keyRow1 = null;
- ExecRow[] rows = new ExecRow[cds.length];
+ ExecRow row;
DataValueDescriptor conglomIDOrderable;
TabInfo ti = coreInfo[SYSCONGLOMERATES_CORE_NUM];
SYSCONGLOMERATESRowFactory rf = (SYSCONGLOMERATESRowFactory) ti.getCatalogRowFactory();
-
- /* Use conglomIDOrderable in both start
- * and stop position for index 1 scan.
- */
- conglomIDOrderable = getValueAsDVD(cds[0].getUUID());
-
- /* Set up the start/stop position for the scan */
- keyRow1 = (ExecIndexRow) exFactory.getIndexableRow(1);
- keyRow1.setColumn(1, conglomIDOrderable);
+ boolean[] bArray = {false, false, false};
for (int i = 0; i < cds.length; i++)
{
+ /* Use conglomIDOrderable in both start
+ * and stop position for index 1 scan.
+ */
+ conglomIDOrderable = getValueAsDVD(cds[i].getUUID());
+
+ /* Set up the start/stop position for the scan */
+ keyRow1 = (ExecIndexRow) exFactory.getIndexableRow(1);
+ keyRow1.setColumn(1, conglomIDOrderable);
+
cds[i].setConglomerateNumber(conglomerateNumber);
// build the row to be stuffed into SYSCONGLOMERATES.
- rows[i] = rf.makeRow(cds[i], null);
- }
+ row = rf.makeRow(cds[i], null);
- // update row in catalog (no indexes)
- boolean[] bArray = new boolean[3];
- for (int index = 0; index < 3; index++)
- {
- bArray[index] = false;
+ // update row in catalog (no indexes)
+ ti.updateRow(keyRow1, row,
+ SYSCONGLOMERATESRowFactory.SYSCONGLOMERATES_INDEX1_ID,
+ bArray,
+ (int[])null,
+ tc);
}
- ti.updateRow(keyRow1, rows,
- SYSCONGLOMERATESRowFactory.SYSCONGLOMERATES_INDEX1_ID,
- bArray,
- (int[])null,
- tc);
+
}
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out?view=diff&rev=449058&r1=449057&r2=449058
==============================================================================
--- 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 Fri Sep 22 12:17:47 2006
@@ -1140,5 +1140,50 @@
ij> drop schema inventory RESTRICT;
0 rows inserted/updated/deleted
ij> --end derby-437 related test cases.
+-- test case for derby-1854
+-- perform compress on a table that has same column
+-- as a primary key and a foreign key.
+create table users (
+ user_id int not null generated by default as identity,
+ user_login varchar(255) not null,
+ primary key (user_id));
+0 rows inserted/updated/deleted
+ij> create table admins (
+ user_id int not null,
+ primary key (user_id),
+ constraint admin_uid_fk foreign key (user_id) references users (user_id));
+0 rows inserted/updated/deleted
+ij> insert into users (user_login) values('test1');
+1 row inserted/updated/deleted
+ij> insert into admins values (values identity_val_local());
+1 row inserted/updated/deleted
+ij> call syscs_util.syscs_compress_table('APP', 'ADMINS', 0);
+0 rows inserted/updated/deleted
+ij> -- do consistency check on the tables.
+values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'USERS');
+1
+-----------
+1
+ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS');
+1
+-----------
+1
+ij> select * from admins;
+USER_ID
+-----------
+1
+ij> select * from users;
+USER_ID |USER_LOGIN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1 |test1
+ij> insert into users (user_login) values('test2');
+1 row inserted/updated/deleted
+ij> insert into admins values (values identity_val_local());
+1 row inserted/updated/deleted
+ij> drop table admins;
+0 rows inserted/updated/deleted
+ij> drop table users;
+0 rows inserted/updated/deleted
+ij> -- end derby-1854 test case.
;
ij>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/importExportThruIJ.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/importExportThruIJ.out?view=diff&rev=449058&r1=449057&r2=449058
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/importExportThruIJ.out (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/importExportThruIJ.out Fri Sep 22 12:17:47 2006
@@ -715,4 +715,48 @@
Mickey |Mouse |Disneyland |Magic Kingdom |Los Angeles
ij> drop table imp_temp;
0 rows inserted/updated/deleted
+ij> -- test case for derby-1854/derby-1641
+-- perform import into a table that has same column
+-- as a primary key and a foreign key (ADMINS table).
+create table users (
+ user_id int not null generated by default as identity,
+ user_login varchar(255) not null,
+ primary key (user_id));
+0 rows inserted/updated/deleted
+ij> create table admins (
+ user_id int not null,
+ primary key (user_id),
+ constraint admin_uid_fk foreign key (user_id) references users (user_id));
+0 rows inserted/updated/deleted
+ij> insert into users (user_login) values('test1');
+1 row inserted/updated/deleted
+ij> insert into users (user_login) values('test2');
+1 row inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select user_id from users' ,
+ 'extinout/users_id.dat', null , null , null ) ;
+0 rows inserted/updated/deleted
+ij> call syscs_util.syscs_import_table( null, 'ADMINS',
+ 'extinout/users_id.dat', null, null, null,1);
+0 rows inserted/updated/deleted
+ij> select * from admins;
+USER_ID
+-----------
+1
+2
+ij> select * from users;
+USER_ID |USER_LOGIN
+--------------------------------------------------------------------------------------------------------------------------------------------
+1 |test1
+2 |test2
+ij> -- do consistency check on the table.
+values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS');
+1
+-----------
+1
+ij> drop table admins;
+0 rows inserted/updated/deleted
+ij> drop table users;
+0 rows inserted/updated/deleted
+ij> -- end derby-1854/derby-1641 test case.
+;
ij>
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/compressTable.sql?view=diff&rev=449058&r1=449057&r2=449058
==============================================================================
--- 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 Fri Sep 22 12:17:47 2006
@@ -490,3 +490,31 @@
drop table inventory.orderTable;
drop schema inventory RESTRICT;
--end derby-437 related test cases.
+
+-- test case for derby-1854
+-- perform compress on a table that has same column
+-- as a primary key and a foreign key.
+
+create table users (
+ user_id int not null generated by default as identity,
+ user_login varchar(255) not null,
+ primary key (user_id));
+
+create table admins (
+ user_id int not null,
+ primary key (user_id),
+ constraint admin_uid_fk foreign key (user_id) references users (user_id));
+
+insert into users (user_login) values('test1');
+insert into admins values (values identity_val_local());
+call syscs_util.syscs_compress_table('APP', 'ADMINS', 0);
+-- do consistency check on the tables.
+values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'USERS');
+values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS');
+select * from admins;
+select * from users;
+insert into users (user_login) values('test2');
+insert into admins values (values identity_val_local());
+drop table admins;
+drop table users;
+-- end derby-1854 test case.
Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql?view=diff&rev=449058&r1=449057&r2=449058
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql (original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/tools/importExportThruIJ.sql Fri Sep 22 12:17:47 2006
@@ -258,6 +258,35 @@
select * from imp_temp ;
drop table imp_temp;
+-- test case for derby-1854/derby-1641
+-- perform import into a table that has same column
+-- as a primary key and a foreign key (ADMINS table).
+
+create table users (
+ user_id int not null generated by default as identity,
+ user_login varchar(255) not null,
+ primary key (user_id));
+
+create table admins (
+ user_id int not null,
+ primary key (user_id),
+ constraint admin_uid_fk foreign key (user_id) references users (user_id));
+
+insert into users (user_login) values('test1');
+insert into users (user_login) values('test2');
+
+call SYSCS_UTIL.SYSCS_EXPORT_QUERY('select user_id from users' ,
+ 'extinout/users_id.dat', null , null , null ) ;
+call syscs_util.syscs_import_table( null, 'ADMINS',
+ 'extinout/users_id.dat', null, null, null,1);
+select * from admins;
+select * from users;
+-- do consistency check on the table.
+values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'ADMINS');
+drop table admins;
+drop table users;
+-- end derby-1854/derby-1641 test case.
+