ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- test java.sql.Connection.DatabaseMetaData.getBestRowIdentifier() -- -- create a java procedure to do the metadata call create procedure getBestRowID(in schema_param Char(10), in tableName_param Char(10), in scope_param int, in nullable_param Char(5)) parameter style java reads sql data dynamic result sets 1 language java external name 'org.apache.derbyTesting.functionTests.util.metadataHelperProcs.getBestRowId'; 0 rows inserted/updated/deleted ij> prepare bestrow as 'call getBestRowID(?,?,?,?)'; ij> autocommit off; ij> -- each one of these have only one choice create table t1 (i int not null primary key, j int); 0 rows inserted/updated/deleted ij> create table t2 (i int not null unique, j int); 0 rows inserted/updated/deleted ij> -- adding not null unique to j - otherwise t2 & t3 would be same. create table t3 (i int not null unique, j int not null unique); 0 rows inserted/updated/deleted ij> create table t4 (i int, j int); 0 rows inserted/updated/deleted ij> create unique index t4i on t4(i); 0 rows inserted/updated/deleted ij> create table t5 (i int, j int); 0 rows inserted/updated/deleted ij> -- result: column i execute bestrow using 'values(''APP'',''T1'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- result: column i execute bestrow using 'values(''APP'',''T2'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- result: column i execute bestrow using 'values(''APP'',''T3'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- result: column i execute bestrow using 'values(''APP'',''T4'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- result: columns i and j execute bestrow using 'values(''APP'',''T5'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> rollback work; ij> -- PK preferred to unique create table t6 (i int not null unique, j int not null primary key); 0 rows inserted/updated/deleted ij> -- result: column j execute bestrow using 'values(''APP'',''T6'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- PK preferred to unique index create table t7 (i int not null, j int not null primary key); 0 rows inserted/updated/deleted ij> create unique index t7i_index on t7(i); 0 rows inserted/updated/deleted ij> -- result: column j execute bestrow using 'values(''APP'',''T7'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- unique con preferred to unique index create table t8 (i int not null, j int not null unique); 0 rows inserted/updated/deleted ij> create unique index t8i_index on t8(i); 0 rows inserted/updated/deleted ij> -- result: column j execute bestrow using 'values(''APP'',''T8'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- non-unique index just ignored create table t9 (i int, j int); 0 rows inserted/updated/deleted ij> create index t9i_index on t9(i); 0 rows inserted/updated/deleted ij> -- result: columns i,j execute bestrow using 'values(''APP'',''T9'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> rollback work; ij> -- fewer cols unique con still ignored over primary key create table t10 (i int unique not null , j int not null , primary key (i,j)); 0 rows inserted/updated/deleted ij> -- result: columns i,j execute bestrow using 'values(''APP'',''T10'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- fewer cols unique index still ignored over primary key create table t11 (i int not null, j int not null, primary key (i,j)); 0 rows inserted/updated/deleted ij> create unique index t11i_index on t11(i); 0 rows inserted/updated/deleted ij> -- result: columns i,j execute bestrow using 'values(''APP'',''T11'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- fewer cols unique index still ignored over unique con create table t12 (i int not null, j int not null, unique (i,j)); 0 rows inserted/updated/deleted ij> create unique index t12i_index on t12(i); 0 rows inserted/updated/deleted ij> -- result: columns i,j execute bestrow using 'values(''APP'',''T12'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> rollback work; ij> -- REMIND: we aren't handling nullOk flag correctly -- we just drop nullable cols, we should skip an answer -- that has nullable cols in it instead and look for another one. create table t13 (i int not null, j int not null, k int, unique (i,j)); 0 rows inserted/updated/deleted ij> -- result: columns i, j (WRONG) -- the correct answer is k: the non-null columns of the table execute bestrow using 'values(''APP'',''T13'',0,''false'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 2 |J |4 |INTEGER |4 |NULL |10 |1 ij> -- fewest cols unique con is the one picked of several create table t14 (i int not null unique, j int not null, k int, unique (i,j)); 0 rows inserted/updated/deleted ij> -- result: columns i execute bestrow using 'values(''APP'',''T14'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- fewest cols unique index is the one picked of several create table t15 (i int not null, j int not null, k int); 0 rows inserted/updated/deleted ij> create unique index t15ij on t15(i,j); 0 rows inserted/updated/deleted ij> create unique index t15i on t15(i); 0 rows inserted/updated/deleted ij> -- result: columns i execute bestrow using 'values(''APP'',''T15'',0,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- we don't do anything with SCOPE except detect bad values create table t16 (i int not null primary key, j int); 0 rows inserted/updated/deleted ij> -- result: columns i execute bestrow using 'values(''APP'',''T16'',1,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- result: columns i execute bestrow using 'values(''APP'',''T16'',2,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2 |I |4 |INTEGER |4 |NULL |10 |1 ij> -- result: no rows execute bestrow using 'values(''APP'',''T16'',-1,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> -- result: no rows execute bestrow using 'values(''APP'',''T16'',3,''true'')'; SCOPE |COLUMN_NAME |DATA_TYPE |TYPE_NAME |COLUMN_SIZE|BUFFER_LEN&|DECIM&|PSEUD& -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> rollback work; ij> drop procedure getBestRowID; 0 rows inserted/updated/deleted ij> commit; ij>