Google Git
Sign in
apache / derby / dbed020d3949dd7707a6eca23741aa5ba155ece1 / . / java / testing / org / apache / derbyTesting / functionTests / tests / lang / CollationTest2.java
blob: ed8f9876c0e5fe28f5e802ac4eca3dac09eae636 [file] [log] [blame]
/**
* Derby - Class org.apache.derbyTesting.functionTests.tests.lang.CollationTest2
*
* 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.
*/
package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Collator;
import java.text.RuleBasedCollator;
import java.util.Locale;
import java.util.Properties;
import junit.framework.Assert;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.Decorator;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
import org.apache.derbyTesting.junit.TestConfiguration;
/**
Junit test targeted at testing language based Collation.
Test the following with data that shows different ordering between default
collation and different language based collation:
T0: (DONE) Heap based compare using predicate pushing
T1: (DONE) Index based compare start/stop predicates on index
T2: (TODO) Index based compare using predicate pushing
T3: (DONE) order by on heap using in memory sorter
T4: (TODO) order by on heap using disk based sorter
T5: (TODO) system catalogs should not be collated
T6: (DONE) test like
T7: (TODO) test create conglomerate triggered by DiskHashtable code
T8: (TODO) test create conglomerate triggered by DataDictionaryImpl
T9: (TODO) test create conglomerate triggered by java/engine/org/apache/derby/impl/sql/conn/GenericLanguageConnectionContext.java
T10: (DONE) alter table compress with indexes
T11: (DONE) alter table drop column with indexes
T12: (DONE) alter table add column with index
T13: (DONE) bulk insert into empty table, with and without indexes
T14: (DONE) bulk insert replace, with and without indexes
T15: (TODO) java/engine/org/apache/derby/impl/sql/execute/MaterializedResultSet.java
T16: (TODO) /java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java
T17: (TODO) /java/engine/org/apache/derby/impl/store/access/PropertyConglomerate.java
T18: (TODO) upgrade tests - may be changes to upgrade suite rather than here.
T19: (TODO) recovery testing - may be old function harness changes as no one has
suggested how to do this in junit harness.
T20: (TODO) For both a newly created 10.3 database and an upgraded 10.3
database, make sure that metadata continues to show the scale for
character datatypes as 0 (rather than the collation type value).
That is, test that the scale of the character datatypes is always
0 and it didn't get impacted negatively by the overloading of scale
field as collation type in TypeDescriptor.
T21: (TODO) Testing with views
T22: (TODO) Testing with global temporary tables
T23: (TODO) Alter table testing. Two specific cases 1)add a new character column and 2)increase the length of an existing character
T24: (DONE) DERBY-2669 If no territory attribute is specified at create
database time, then create collated db based on default territory
of Database.
<p>
NOTE: The prefix "ci_test" is used for tests that require a case insensitive
collation order.
**/
public class CollationTest2 extends BaseJDBCTestCase
{
/**************************************************************************
* Fields of the class
**************************************************************************
*/
/**
* Set to get output if something in the test is failing and you want
* more information about what was going on.
**/
private static final boolean verbose_debug = false;
private static final int TEST_DEFAULT = 0;
private static final int TEST_ENGLISH = 1;
private static final int TEST_POLISH = 2;
private static final int TEST_NORWAY = 3;
private static final String[] NAMES =
{
// Just Smith, Zebra, Acorn with alternate A,S and Z
"Smith",
"Zebra",
"\u0104corn",
"\u017Bebra",
"Acorn",
"\u015Amith",
"aacorn"
};
private static final int[] DEFAULT_NAME_ORDER =
{
4, // Acorn
0, // Smith
1, // Zebra
6, // aacorn
2, // \u0104corn
5, // \u015Amith
3 // \u017Bebra
};
private static final int[] ENGLISH_NAME_ORDER =
{
6, // aacorn
4, // Acorn
2, // \u0104corn
0, // Smith
5, // \u015Amith
1, // Zebra
3 // \u017Bebra
};
private static final int[] POLISH_NAME_ORDER =
{
6, // aacorn
4, // Acorn
2, // \u0104corn
0, // Smith
5, // \u015Amith
1, // Zebra
3 // \u017Bebra
};
private static final int[] NORWAY_NAME_ORDER =
{
4, // Acorn
2, // \u0104corn
0, // Smith
5, // \u015Amith
1, // Zebra
3, // \u017Bebra
6 // aacorn
};
private static final int[][] EXPECTED_NAME_ORDER =
{
DEFAULT_NAME_ORDER,
ENGLISH_NAME_ORDER,
POLISH_NAME_ORDER,
NORWAY_NAME_ORDER
};
/**
* set up LIKE test cases, configured for all languages by
* the TEST_* constants.
* <p>
* Insert all data to tested against into LIKE_NAMES. A customer table
* will be filled with this data.
* <p>
* Insert test cases for like string into the LIKE_TEST_CASES, results
* are expected only to return a single row.
* <p>
* Insert actual string expected back for each language, for each test
* case in the {LANG}_LIKE_RESULT array. Insert null if no match is
* expected.
* <p>
* Current test tries all 4 datatypes, CHAR will blank pad making the
* results different than the other datatypes if data is shorter than
* type, thus a different set of LIKE clauses needs to be entered in the
* LIKE_CHAR_TEST_CASES which should match the same results in a CHAR
* field as does the corresponding LIKE_TEST_CASES test.
*
**/
private static final String[] LIKE_NAMES =
{
"Waagan", // 0
"Smith", // 1
"Zebra", // 2
"xcorn", // 3
"aBebra", // 4
"Acorn", // 5
"Amith", // 6
"aacorn", // 7
"xxxaa", // 8
"aaxxx", // 9
"yyyaa y", // 10
};
private static final String[] LIKE_TEST_CASES =
{
"Waagan",
"W_gan",
"aaxxx",
"_xxx",
"xxxaa",
"xxx_",
"xxx_%",
"yyy_%"
};
private static final String[] LIKE_CHAR_TEST_CASES =
{
"Waagan ",
"W_gan ",
"aaxxx%",
"_xxx%",
"xxx%",
"xxx_ %",
"xxx%",
"yyy_%"
};
private static final int[] DEFAULT_LIKE_RESULT =
{
0,
-1,
9,
-1,
8,
-1,
8,
10
};
private static final int[] ENGLISH_LIKE_RESULT =
{
0,
-1,
9,
-1,
8,
-1,
8,
10
};
private static final int[] POLISH_LIKE_RESULT =
{
0,
-1,
9,
-1,
8,
-1,
8,
10
};
private static final int[] NORWAY_LIKE_RESULT =
{
0,
-1,
9,
-1,
8,
-1,
8,
10
};
private static final int[][] EXPECTED_LIKE_RESULTS =
{
DEFAULT_LIKE_RESULT,
ENGLISH_LIKE_RESULT,
POLISH_LIKE_RESULT,
NORWAY_LIKE_RESULT
};
/**************************************************************************
* Constructors for This class:
**************************************************************************
*/
public CollationTest2(String name)
{
super(name);
}
/**************************************************************************
* Private/Protected setup/utility methods of This class:
**************************************************************************
*/
protected void initializeConnection(Connection conn) throws SQLException
{
conn.setAutoCommit(false);
}
/**
* RESOLVE - unfinished LIKE test with dataset of all unicode characters
**/
private static final void printRuleBasedCollator()
{
// get en_US Collator rules
RuleBasedCollator en_USCollator =
(RuleBasedCollator)Collator.getInstance(Locale.US);
String en_rules = en_USCollator.getRules();
System.out.println("ENGLISH RULES: " + en_rules);
System.out.println("ENGLISH RULES: " + formatString(en_rules, true));
System.out.println("ENGLISH RULES: " + formatString(en_rules, false));
}
/**
* RESOLVE - unfinished LIKE test with dataset of all unicode characters
**/
private static final String formatString(
String str,
boolean all)
{
// format it as \u0000(x)\u0001(x)...
String ret_val = "";
for (int i = 0; i < str.length(); i++)
{
char ch = str.charAt(i);
if (!all && (ch <= 128))
{
ret_val += Character.toString(ch);
}
else
{
ret_val +=
"\\u" + Integer.toString(ch, 16) +
"(" + Character.toString(ch) + ")";
}
}
return(ret_val);
}
/**
* RESOLVE - unfinished LIKE test with dataset of all unicode characters
**/
private final void formatLikeResults(
Connection conn,
String query)
throws SQLException
{
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(query);
String txt_str = null;
String ucode_str = null;
String mixed_str = null;
int count = 0;
while (rs.next())
{
count++;
if (count == 1)
{
txt_str = "{";
ucode_str = "{";
mixed_str = "{";
}
else
{
txt_str += ", ";
ucode_str += ", ";
mixed_str += ", ";
}
String ret_val = rs.getString(1);
txt_str += "\"" + ret_val + "\"";
// string using \u0104 format for chars
ucode_str += formatString(ret_val, true);
mixed_str += "{\"" + formatString(ret_val, false) + "\"}";
}
if (count != 0)
{
txt_str += "}";
ucode_str += "}";
mixed_str += "}";
}
System.out.println(
"Query: " + query +
"\nnumber rows :" + count +
"\nString Result:" + txt_str +
"\nUcode Result:" + ucode_str +
"\nmixed Result:" + mixed_str);
rs.close();
s.close();
}
/**
* RESOLVE - unfinished LIKE test with dataset of all unicode characters
**/
private final void printLikeResults(Connection conn)
throws SQLException
{
/*
RESOLVE-COMMENTED OUT
for (int i = 0; i < LIKE_ALLVALS_TEST.length; i++)
{
if (verbose_debug)
{
System.out.println(
"Running like allvals test[" + i + "] = " +
LIKE_ALLVALS_TEST[i]);
}
formatLikeResults(
conn,
"SELECT STR_VARCHAR FROM ALLVALS WHERE STR_VARCHAR LIKE " +
"'" + LIKE_ALLVALS_TEST[i] + "'");
formatLikeResults(
conn,
"SELECT STR_LONGVARCHAR FROM ALLVALS WHERE STR_LONGVARCHAR LIKE " +
"'" + LIKE_ALLVALS_TEST[i] + "'");
formatLikeResults(
conn,
"SELECT STR_CLOB FROM ALLVALS WHERE STR_CLOB LIKE " +
"'" + LIKE_ALLVALS_TEST[i] + "'");
formatLikeResults(
conn,
"SELECT STR_CHAR FROM ALLVALS WHERE STR_CHAR LIKE " +
"'" + LIKE_ALLVALS_TEST[i] + "'");
}
*/
}
private void checkLangBasedQuery(
String query,
String[][] expectedResult,
boolean ordered)
throws SQLException
{
Statement s = createStatement();
ResultSet rs = s.executeQuery(query);
if (verbose_debug)
{
System.out.println("executed query: " + query);
}
if (expectedResult == null) //expecting empty resultset from the query
{
if (verbose_debug)
System.out.println(
"executed query expecting no results: " + query);
JDBC.assertEmpty(rs);
}
else
{
if (ordered)
{
if (verbose_debug)
System.out.println(
"executed query expecting ordered results: " + query);
JDBC.assertFullResultSet(rs, expectedResult);
}
else
{
if (verbose_debug)
System.out.println(
"executed query expecting unordered results: " + query);
JDBC.assertUnorderedResultSet(rs, expectedResult);
}
}
}
private void checkParamQuery(
String query,
String[] param,
int paramNumber,
String[][] expectedResult,
boolean ordered)
throws SQLException
{
PreparedStatement ps = prepareStatement(query);
for (int i=0; i < paramNumber;i++)
{
ps.setString(i+1, param[i]);
}
ResultSet rs = ps.executeQuery();
if (expectedResult == null) //expecting empty resultset from the query
{
JDBC.assertEmpty(rs);
}
else
{
if (ordered)
JDBC.assertFullResultSet(rs,expectedResult);
else
JDBC.assertUnorderedResultSet(rs, expectedResult);
}
// re-execute it to test path through the cache
for (int i=0; i < paramNumber;i++)
{
ps.setString(i+1, param[i]);
}
rs = ps.executeQuery();
if (expectedResult == null) //expecting empty resultset from the query
{
JDBC.assertEmpty(rs);
}
else
{
if (ordered)
JDBC.assertFullResultSet(rs,expectedResult);
else
JDBC.assertUnorderedResultSet(rs, expectedResult);
}
commit();
}
/**
* Perform export using SYSCS_UTIL.SYSCS_EXPORT_TABLE procedure.
*/
protected void doExportTable(
String schemaName,
String tableName,
String fileName,
String colDel ,
String charDel,
String codeset)
throws SQLException
{
//DERBY-2925: need to delete existing files first.
SupportFilesSetup.deleteFile(fileName);
CallableStatement ps =
prepareCall(
"call SYSCS_UTIL.SYSCS_EXPORT_TABLE (? , ? , ? , ?, ? , ?)");
ps.setString(1, schemaName);
ps.setString(2, tableName);
ps.setString(3, fileName);
ps.setString(4, colDel);
ps.setString(5, charDel);
ps.setString(6, codeset);
ps.executeUpdate();
ps.close();
}
/**
* Perform import using SYSCS_UTIL.SYSCS_IMPORT_TABLE procedure.
*/
protected void doImportTable(
String schemaName,
String tableName,
String fileName,
String colDel,
String charDel,
String codeset,
int replace)
throws SQLException
{
CallableStatement ps =
prepareCall(
"call SYSCS_UTIL.SYSCS_IMPORT_TABLE (?, ?, ?, ?, ?, ?, ?)");
ps.setString(1, schemaName);
ps.setString(2, tableName);
ps.setString(3, fileName);
ps.setString(4, colDel);
ps.setString(5, charDel);
ps.setString(6, codeset);
ps.setInt( 7, replace);
ps.executeUpdate();
ps.close();
}
/**
* Produce an expect row set given the order and asc/desc info.
* <p>
* Given the expected order of rows, the offset of first and last row
* to return, and whether rows will be ascending or descending produce
* a 2d expected row set. Each row in the row set represents a row
* with 2 columns (ID, NAME) from the CUSTOMER table used throughout
* this test.
*
* @param expected_order Expected order of rows in this language.
* @param start_offset expect rows starting at
* expected_order[start_offset] up to and including
* expected_order[stop_offset].
* @param stop_offset expect rows starting at
* expected_order[start_offset] up to and including
* expected_order[stop_offset].
* @param ascending_order true if rows are in order, else rows are in
* reverse order.
**/
private String[][] full_row_set(
int[] expected_order,
int start_offset,
int stop_offset,
boolean ascending_order)
{
String[][] ret_order = null;
int num_vals = stop_offset - start_offset + 1;
if (num_vals > 0)
{
ret_order = new String[num_vals][2];
if (ascending_order)
{
int dest = 0;
for (int src = start_offset; src <= stop_offset; src++)
{
ret_order[dest][0] = String.valueOf(expected_order[src]);
ret_order[dest][1] = NAMES[expected_order[src]];
dest++;
}
}
else
{
// rows are expected in reverse order from what is passsed in,
// so swap them to create the output expected result array.
int dest = 0;
for (int src = stop_offset; src >= start_offset; src--)
{
ret_order[dest][0] = String.valueOf(expected_order[src]);
ret_order[dest][1] = NAMES[expected_order[src]];
dest++;
}
}
}
return(ret_order);
}
/**
* Produce an expect row set given list and offset of row in list.
* <p>
* Given the list of rows and offset of the expected row in the list
* produce a 2d expected row set. If expected_row is -1 then no row
* set is returned. Each row in the row set represents a row
* with 2 columns (ID, NAME) from the CUSTOMER table used throughout
* this test.
*
* @param expected_row -1 if no expected row, else
* ret_list[expected_row] is single value expected.
* @param ret_list list of strings in data set.
**/
private String[][] full_row_single_value(
int expected_row,
String[] ret_list)
{
String[][] ret_order = null;
if (expected_row != -1)
{
// if not -1 then exactly one row expected.
ret_order = new String[1][2];
ret_order[0][0] = String.valueOf(expected_row);
ret_order[0][1] = ret_list[expected_row];
}
return(ret_order);
}
private boolean isDatabaseBasicCollation()
throws SQLException
{
return "UCS_BASIC".equals(getDatabaseProperty("derby.database.collation"));
}
/**************************************************************************
* Set up and clean up routines.
**************************************************************************
*/
private void setUpTable() throws SQLException
{
Statement s = createStatement();
s.execute(
"CREATE TABLE CUSTOMER(" +
"D1 CHAR(200), D2 CHAR(200), D3 CHAR(200), D4 INT, " +
"ID INT, NAME VARCHAR(40), NAME2 VARCHAR(40))");
PreparedStatement ps =
prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?,?,?)");
for (int i = 0; i < NAMES.length; i++)
{
ps.setString(1, NAMES[i]);
ps.setString(2, NAMES[i]);
ps.setString(3, NAMES[i]);
ps.setInt( 4, i);
ps.setInt( 5, i);
ps.setString(6, NAMES[i]);
ps.setString(7, NAMES[i]);
ps.executeUpdate();
}
s.execute(
"CREATE TABLE EMPTY_TABLE (NAME VARCHAR(40))");
s.execute(
"CREATE INDEX EMPTY_TABLE_IDX ON EMPTY_TABLE(NAME)");
commit();
}
private void addSomeMoreCustomers( int counter ) throws SQLException
{ addSomeMoreCustomers( counter, true, true ); }
private void addSomeMoreCustomers( int counter, boolean useD1, boolean useD2 ) throws SQLException
{
PreparedStatement ps;
if( useD1 && useD2 )
ps = prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?,?,?)");
else if( useD2 )
ps = prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?,?)");
else
ps = prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?)");
int colNo = 1;
for (int i = 0; i < NAMES.length; i++)
{
if( useD1 )
ps.setString(colNo++, "Another " + counter + NAMES[i]);
if( useD2 )
ps.setString(colNo++, "Another " + counter + NAMES[i]);
ps.setString(colNo++, "Another " + counter + NAMES[i]);
ps.setInt( colNo++, NAMES.length + counter + i);
ps.setInt( colNo++, NAMES.length + counter + i);
ps.setString(colNo++, "Another " + counter + NAMES[i]);
ps.setString(colNo++, "Another " + counter + NAMES[i]);
ps.executeUpdate();
colNo = 1;
}
}
private void dropExtraCustomers( int counter ) throws SQLException
{
PreparedStatement ps = prepareStatement("DELETE FROM CUSTOMER WHERE ID >= ?");
ps.setInt( 1, counter );
ps.executeUpdate();
}
private void setUpLikeTable() throws SQLException
{
Statement s = createStatement();
s.execute(
"CREATE TABLE CUSTOMER (" +
"NAME_CHAR CHAR(10), " +
"NAME_VARCHAR VARCHAR(40)," +
"NAME_LONGVARCHAR LONG VARCHAR," +
"NAME_CLOB CLOB," +
"ID INT)");
PreparedStatement ps =
prepareStatement("INSERT INTO CUSTOMER VALUES(?,?,?,?,?)");
for (int i = 0; i < LIKE_NAMES.length; i++)
{
ps.setString(1, LIKE_NAMES[i]);
ps.setString(2, LIKE_NAMES[i]);
ps.setString(3, LIKE_NAMES[i]);
ps.setString(4, LIKE_NAMES[i]);
ps.setInt( 5, i);
ps.executeUpdate();
}
ps.close();
s.close();
commit();
}
/**
* RESOLVE - unfinished LIKE test with dataset of all unicode characters
**/
private void setUpALLVALS(Connection conn)
throws SQLException
{
Statement s = conn.createStatement();
s.execute(
"CREATE TABLE ALLVALS (" +
"STR_CHAR CHAR(3), " +
"STR_VARCHAR VARCHAR(40)," +
"STR_LONGVARCHAR LONG VARCHAR," +
"STR_CLOB CLOB," +
"ID INT)");
PreparedStatement ps =
conn.prepareStatement("INSERT INTO ALLVALS VALUES(?,?,?,?,?)");
char[] single_char = new char[1];
char[] leading_b = new char[2];
char[] trailing_b = new char[2];
char[] middle_b = new char[3];
leading_b[0] = 'b';
trailing_b[trailing_b.length - 1] = 'b';
middle_b[1] = 'b';
int max_char = (int) Character.MAX_VALUE;
long before_load_ms = System.currentTimeMillis();
for (int i = Character.MIN_VALUE; i <= max_char; i++)
{
// insert a row with string value of a single unicode char
single_char[0] = (char) i;
String str_val = String.valueOf(single_char);
ps.setString(1, str_val);
ps.setString(2, str_val);
ps.setString(3, str_val);
ps.setString(4, str_val);
ps.setInt( 5, i);
ps.executeUpdate();
// insert a row with 'b' followed by unicode value followed by 'b'
leading_b[1] = (char) i;
str_val = String.valueOf(leading_b);
ps.setString(1, str_val);
ps.setString(2, str_val);
ps.setString(3, str_val);
ps.setString(4, str_val);
ps.setInt( 5, i);
ps.executeUpdate();
// insert a row with unicode value followed by 'b'
trailing_b[0] = (char) i;
str_val = String.valueOf(trailing_b);
ps.setString(1, str_val);
ps.setString(2, str_val);
ps.setString(3, str_val);
ps.setString(4, str_val);
ps.setInt( 5, i);
ps.executeUpdate();
// insert a row with unicode value followed by 'b'
// followed by unicode value
middle_b[0] = (char) i;
middle_b[2] = (char) i;
str_val = String.valueOf(middle_b);
ps.setString(1, str_val);
ps.setString(2, str_val);
ps.setString(3, str_val);
ps.setString(4, str_val);
ps.setInt( 5, i);
ps.executeUpdate();
}
long after_load_ms = System.currentTimeMillis();
conn.commit();
long after_commit_ms = System.currentTimeMillis();
if (verbose_debug)
{
System.out.println("Loaded and committed ALLVALS table:");
System.out.println(
"load time = " + (after_load_ms - before_load_ms) +
"commit time = " + (after_commit_ms - after_load_ms));
}
ps.close();
s.close();
}
/**************************************************************************
* run*() tests, called from the actual test*() tests.
**************************************************************************
*/
/**
* Test simple call to DatabaseMetaData.getColumns()
* <p>
* This test is the same form of the getColumns() call that
* the IMPORT and EXPORT system procedures depend on.
* Currently on ibm and sun 1.4.2 jvm's this test fails.
**/
private void runDERBY_2703(int db_index)
throws SQLException
{
setUpTable();
ResultSet rs =
getConnection().getMetaData().getColumns(null, "APP", "CUSTOMER", "%");
int rowCount = JDBC.assertDrainResults(rs);
Assert.assertTrue("catch bug where no rows are returned.", rowCount > 0);
dropTable();
}
/**
* Tests that DERBY-5367 is fixed, a bug where updating the index in a
* database with a case insensitive collation resulted in data corruption.
* <p>
* The bug tested is where a deleted row with an incorrect key value in
* the index is undeleted as an optimized insert. In this case it was
* caused by the a case insensitive collation order, but other collation
* rules could cause this to happen as well.
*/
public void ci_testDerby5367()
throws SQLException {
assertFalse(isDatabaseBasicCollation());
setAutoCommit(true);
String TABLE = "DERBY_5367";
Statement stmt = createStatement();
stmt.executeUpdate("create table " + TABLE + "(" +
"VAL varchar(10) not null unique)");
// Run first time when the congloms were newly created.
runDerby5367TestCode(TABLE);
// Shut down the database, reboot. This will trigger the code to
// read the congloms from disk.
TestConfiguration.getCurrent().shutdownDatabase();
getConnection();
// Run second time, read congloms from disk.
runDerby5367TestCode(TABLE);
dropTable(TABLE);
}
/** Runs the core code for the DERBY-5367 test. */
private void runDerby5367TestCode(String table)
throws SQLException {
PreparedStatement sel = prepareStatement("select val from " + table +
" where val = 'Test'");
PreparedStatement ins = prepareStatement("insert into " + table +
" values ?");
ins.setString(1, "Test");
ins.executeUpdate();
JDBC.assertFullResultSet(sel.executeQuery(), new String[][] {{"Test"}});
Statement stmt = createStatement();
stmt.executeUpdate("delete from " + table + " where val = 'Test'");
ins.setString(1, "test");
ins.executeUpdate();
JDBC.assertFullResultSet(sel.executeQuery(), new String[][] {{"test"}});
stmt.executeUpdate("delete from " + table);
}
/**************************************************************************
* Private/Protected tests of This class:
**************************************************************************
*/
/**
* Check simple boolean compare of string constant to column value.
* <p>
* Check &lt;, &lt;=, =, &gt;=, &gt; of constant to column, ie. of the form
* select * from table where col boolean constant
*
*
* @throws SQLException
**/
private void checkSimpleCompare(
int[] expected_order)
throws SQLException
{
// loop through all the rows using each as the descriminator, this
// gives us low, high and middle special cases. Expect the number
// of rows for this test case to be low.
for (int i = 0; i < expected_order.length; i++)
{
// '<' test
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME < '" +
NAMES[expected_order[i]] + "' ORDER BY NAME",
full_row_set(
expected_order,
0,
i - 1,
true),
true);
// '<=' test
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME <= '" +
NAMES[expected_order[i]] + "' ORDER BY NAME",
full_row_set(
expected_order,
0,
i,
true),
true);
// '=' test
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME = '" +
NAMES[expected_order[i]] + "' ORDER BY NAME",
full_row_set(
expected_order,
i,
i,
true),
true);
// '>=' test
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME >= '" +
NAMES[expected_order[i]] + "' ORDER BY NAME",
full_row_set(
expected_order,
i,
expected_order.length - 1,
true),
true);
// '>' test
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME > '" +
NAMES[expected_order[i]] + "' ORDER BY NAME",
full_row_set(
expected_order,
i + 1,
expected_order.length - 1,
true),
true);
// now check prepared query
// '<' test
checkParamQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME < ? ORDER BY NAME",
new String[] {NAMES[expected_order[i]]},
1,
full_row_set(
expected_order,
0,
i - 1,
true),
true);
// '<=' test
checkParamQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME <= ? ORDER BY NAME",
new String[] {NAMES[expected_order[i]]},
1,
full_row_set(
expected_order,
0,
i,
true),
true);
// '=' test
checkParamQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME = ? ORDER BY NAME",
new String[] {NAMES[expected_order[i]]},
1,
full_row_set(
expected_order,
i,
i,
true),
true);
// '>=' test
checkParamQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME >= ? ORDER BY NAME",
new String[] {NAMES[expected_order[i]]},
1,
full_row_set(
expected_order,
i,
expected_order.length - 1,
true),
true);
// '>' test
checkParamQuery(
"SELECT ID, NAME FROM CUSTOMER where NAME > ? ORDER BY NAME",
new String[] {NAMES[expected_order[i]]},
1,
full_row_set(
expected_order,
i + 1,
expected_order.length - 1,
true),
true);
}
}
/**
* Check simple boolean compare of string constant to column value.
* <p>
* Check &lt;, &glt;=, =, &gt;=, &gt; of constant to column, ie. of the form
* select * from table where col boolean constant
*
*
* @throws SQLException
**/
private void checkTwoPersistentCompare(
int[] expected_order)
throws SQLException
{
Statement s = createStatement();
commit();
s.execute(
"ALTER TABLE CUSTOMER ADD COLUMN TWO_CHECK_CHAR CHAR(40)");
s.execute(
"ALTER TABLE CUSTOMER ADD COLUMN TWO_CHECK_VARCHAR VARCHAR(400)");
// Set CHAR field to be third item im expected order array
PreparedStatement ps =
prepareStatement("UPDATE CUSTOMER SET TWO_CHECK_CHAR = ?");
ps.setString(1, NAMES[expected_order[3]]);
ps.executeUpdate();
// Set VARCHAR field to be third item im expected order array
ps =
prepareStatement("UPDATE CUSTOMER SET TWO_CHECK_VARCHAR = ?");
ps.setString(1, NAMES[expected_order[3]]);
ps.executeUpdate();
// check persistent compared to persistent - VARCHAR TO CHAR,
// should return rows bigger than 3rd in expected order.
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER WHERE NAME > TWO_CHECK_CHAR ORDER BY NAME",
full_row_set(
expected_order,
4,
expected_order.length - 1,
true),
true);
// check persistent compared to persistent - CHAR TO VARCHAR,
// should return rows bigger than 3rd in expected order.
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER WHERE TWO_CHECK_CHAR < NAME ORDER BY NAME",
full_row_set(
expected_order,
4,
expected_order.length - 1,
true),
true);
// check persistent compared to persistent - VARCHAR TO VARCHAR,
// should return rows bigger than 3rd in expected order.
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER WHERE NAME > TWO_CHECK_VARCHAR ORDER BY NAME",
full_row_set(
expected_order,
4,
expected_order.length - 1,
true),
true);
// check persistent compared to persistent - CHAR TO CHAR,
// should return rows bigger than 3rd in expected order.
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER WHERE D3 > TWO_CHECK_CHAR ORDER BY NAME",
full_row_set(
expected_order,
4,
expected_order.length - 1,
true),
true);
// put back data the way it was on entry to test.
rollback();
}
private void dropTable() throws SQLException
{
dropTable("CUSTOMER");
dropTable("EMPTY_TABLE");
}
private void runQueries(
int db_index,
String create_idx_qry,
String idx_name)
throws SQLException
{
Statement s = createStatement();
if (create_idx_qry != null)
{
s.execute(create_idx_qry);
commit();
}
// Simple check of getting all rows back in order
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER ORDER BY NAME",
full_row_set(
EXPECTED_NAME_ORDER[db_index],
0,
EXPECTED_NAME_ORDER[db_index].length - 1,
true),
true);
// Simple check of getting all rows back in order
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER ORDER BY NAME, ID",
full_row_set(
EXPECTED_NAME_ORDER[db_index],
0,
EXPECTED_NAME_ORDER[db_index].length - 1,
true),
true);
// Simple check of getting all rows back in opposite order
checkLangBasedQuery(
"SELECT ID, NAME FROM CUSTOMER ORDER BY NAME DESC",
full_row_set(
EXPECTED_NAME_ORDER[db_index],
0,
EXPECTED_NAME_ORDER[db_index].length - 1,
false),
true);
// Check <, <=, =, >=, > operators on constant vs. column
checkSimpleCompare(EXPECTED_NAME_ORDER[db_index]);
// Check compare of 2 persistent values, using join
checkTwoPersistentCompare(EXPECTED_NAME_ORDER[db_index]);
if (create_idx_qry != null)
s.execute("DROP INDEX " + idx_name);
commit();
}
/**
* Test various like expressions against all string datatypes.
*
* T6: (DONE) test like
* @throws SQLException
**/
private void runLikeTests(
int db_index)
throws SQLException
{
setUpLikeTable();
for (int i = 0; i < LIKE_TEST_CASES.length; i++)
{
if (verbose_debug)
{
System.out.println(
"Running like test[" + i + "] = " + LIKE_TEST_CASES[i]);
}
// varchar column - constant pattern
checkLangBasedQuery(
"SELECT ID, NAME_VARCHAR FROM CUSTOMER " +
"WHERE NAME_VARCHAR LIKE '" + LIKE_TEST_CASES[i] + "'",
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// varchar column - parameter pattern
checkParamQuery(
"SELECT ID, NAME_VARCHAR FROM CUSTOMER " +
"WHERE NAME_VARCHAR LIKE ?",
new String[] {LIKE_TEST_CASES[i]},
1,
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// long varchar column - constant
checkLangBasedQuery(
"SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER " +
"WHERE NAME_LONGVARCHAR LIKE '" + LIKE_TEST_CASES[i] + "'",
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// long varchar column - parameter
checkParamQuery(
"SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER " +
"WHERE NAME_LONGVARCHAR LIKE ?",
new String[] {LIKE_TEST_CASES[i]},
1,
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// clob column - constant
checkLangBasedQuery(
"SELECT ID, NAME_CLOB FROM CUSTOMER WHERE NAME_CLOB LIKE " +
"'" + LIKE_TEST_CASES[i] + "'",
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// clob column - parameter
checkParamQuery(
"SELECT ID, NAME_CLOB FROM CUSTOMER WHERE NAME_CLOB LIKE ?",
new String[] {LIKE_TEST_CASES[i]},
1,
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// char column, char includes blank padding so alter all these
// tests cases to match for blanks at end also.
checkLangBasedQuery(
"SELECT ID, NAME_CHAR FROM CUSTOMER WHERE NAME_CHAR LIKE " +
"'" + LIKE_CHAR_TEST_CASES[i] + "%'",
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
// char column, char includes blank padding so alter all these
// tests cases to match for blanks at end also.
checkParamQuery(
"SELECT ID, NAME_CHAR FROM CUSTOMER WHERE NAME_CHAR LIKE ?",
new String[] {LIKE_CHAR_TEST_CASES[i] + "%"},
1,
full_row_single_value(
EXPECTED_LIKE_RESULTS[db_index][i],
LIKE_NAMES),
true);
}
// test error thrown from LIKE on mismatched collation
String zero_row_syscat_query1 =
"SELECT * from SYS.SYSCOLUMNS where COLUMNNAME like 'nonmatchiing'";
String zero_row_syscat_query2 =
"SELECT * from SYS.SYSCOLUMNS where 'nonmatchiing' like COLUMNNAME";
String zero_row_syscat_query_param1 =
"SELECT * from SYS.SYSCOLUMNS where COLUMNNAME like ?";
String zero_row_syscat_query_param2 =
"SELECT * from SYS.SYSCOLUMNS where ? like COLUMNNAME";
String zero_row_syscat_query_param3 =
"SELECT count(*) from SYS.SYSCOLUMNS where ? like ?";
if (!isDatabaseBasicCollation())
{
// collation of 'fred' picked up from current schema which is
// territory based collation, but system column will have basic
// collation.
assertCompileError("42ZA2", zero_row_syscat_query1);
assertCompileError("42ZA2", zero_row_syscat_query2);
//The following 2 queries will work because ? in the query will
//take it's collation from the context, which in this case would
//mean from COLUMNNAME column in SYS.SYSCOLUMNS
//
checkParamQuery(
zero_row_syscat_query_param1,
new String[] {"nonmatchiing"}, 1, null, true);
checkParamQuery(
zero_row_syscat_query_param2,
new String[] {"nonmatchiing"}, 1, null, true);
checkParamQuery(
zero_row_syscat_query_param3,
new String[] {"nonmatching", "matching"}, 2,
new String[][] {{"0"}}, true);
}
else
{
checkLangBasedQuery(zero_row_syscat_query1, null, true);
checkLangBasedQuery(zero_row_syscat_query2, null, true);
checkParamQuery(
zero_row_syscat_query_param1,
new String[] {"nonmatchiing"}, 1, null, true);
checkParamQuery(
zero_row_syscat_query_param2,
new String[] {"nonmatchiing"}, 1, null, true);
checkParamQuery(
zero_row_syscat_query_param3,
new String[] {"nonmatching", "123"}, 2,
new String[][] {{"0"}}, true);
}
dropTable();
}
/**
* test paths through alter table compress
*
* Tests:
* T10: alter table compress with indexes
**/
private void runAlterTableCompress(
int db_index)
throws SQLException
{
Statement s = createStatement();
setUpTable();
s.execute("CREATE INDEX IDX1 ON CUSTOMER (NAME)");
s.execute("CREATE INDEX IDX2 ON CUSTOMER (NAME, ID)");
s.execute("CREATE INDEX IDX3 ON CUSTOMER (ID, NAME)");
s.execute("CREATE INDEX IDX4 ON CUSTOMER (ID)");
s.execute("CREATE INDEX IDX5 ON CUSTOMER (ID, NAME, D1, D2, D3)");
commit();
// execute alter table compress which will build all new indexes and
// base conglomerates, verify collation info correctly gets into new
// entities.
CallableStatement call_stmt = prepareCall(
" call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'CUSTOMER', 1)");
assertUpdateCount(call_stmt, 0);
commit();
runQueries(db_index, null, null);
s.execute("DROP INDEX IDX1 ");
s.execute("DROP INDEX IDX2 ");
s.execute("DROP INDEX IDX3 ");
s.execute("DROP INDEX IDX4 ");
s.execute("DROP INDEX IDX5 ");
// let's test abort get's back to right collation also.
rollback();
runQueries(db_index, null, null);
addSomeMoreCustomers( 100 );
dropTable();
commit();
}
/**
* Drop column test.
* <p>
* Drop column will drop and recreate base table and associated indexes,
* need to test to make sure correct colation ids get passed to new
* containers.
*
* Tests:
* T11: alter table drop column with indexes
**/
private void runAlterTableDropColumn(
int db_index)
throws SQLException
{
Statement s = createStatement();
setUpTable();
s.execute("ALTER TABLE CUSTOMER DROP COLUMN D1");
runQueries(db_index, null, null);
addSomeMoreCustomers( 100, false, true );
dropExtraCustomers( 100 );
s.execute("CREATE INDEX IDX1 ON CUSTOMER (NAME)");
s.execute("ALTER TABLE CUSTOMER DROP COLUMN D2");
runQueries(db_index, null, null);
addSomeMoreCustomers( 100, false, false );
rollback();
dropTable();
commit();
}
/**
* Add column test.
* <p>
* Add column adds a new template column which requires a collation
* info related store update. Test that added column had right
* collation setting.
*
* Tests:
* T12: alter table add column with index
**/
private void runAlterTableAddColumn(
int db_index)
throws SQLException
{
Statement s = createStatement();
setUpTable();
s.execute("ALTER TABLE CUSTOMER DROP COLUMN NAME");
s.execute("ALTER TABLE CUSTOMER ADD COLUMN NAME CHAR(40)");
s.execute("UPDATE CUSTOMER SET NAME = D1");
runQueries(db_index, null, null);
s.execute("CREATE INDEX IDX1 ON CUSTOMER (NAME)");
runQueries(db_index, null, null);
addSomeMoreCustomers( 100 );
dropTable();
commit();
}
/**
* Bulk insert test.
* <p>
* Tests code path through create conglomerate code executed as part of
* a bulk table insert. In empty table and replace case the bulk table
* code will create new conglomerates for the base table and index table
* and this tests the code that the correct collation is associated with
* the new tables/indexes.
*
* Tests:
* T13: (DONE) bulk insert into empty table, with and without indexes
* T14: (DONE) bulk insert replace, with and without indexes
**/
private void runBulkInsert(
int db_index)
throws SQLException
{
Statement s = createStatement();
setUpTable();
//DERBY-4435
String emptyFileName =
(SupportFilesSetup.getReadWrite("empty_file.dat")).getPath();
s.execute("DELETE FROM EMPTY_TABLE");
//there is no data in EMPTY_TABLE so empty_file.dat will be empty
//after export
doExportTable("APP", "EMPTY_TABLE", emptyFileName, null, null, "UTF-16");
commit();
// bulk insert with replace to empty table/one index from an empty file
// import empty_file.dat into EMPTY_TABLE
doImportTable(
"APP", "EMPTY_TABLE", emptyFileName, "|", "`", null, 1);
commit();
// export CUSTOMER date to names.dat
String fileName =
(SupportFilesSetup.getReadWrite("names.dat")).getPath();
doExportTable("APP", "CUSTOMER", fileName, null, null, "UTF-16");
commit();
// bulk insert to empty table, no indexes without replace
// (last arg 0 = no replace).
s.execute("DELETE FROM CUSTOMER");
commit();
// checkGetColumn(conn);
doImportTable(
"APP", "CUSTOMER", fileName, null, null, "UTF-16", 0);
runQueries(db_index, null, null);
// bulk insert to empty table, with indexes without replace
// (last arg 0 = no replace).
s.execute("DELETE FROM CUSTOMER");
s.execute("CREATE INDEX IDX1 ON CUSTOMER (NAME)");
s.execute("CREATE INDEX IDX2 ON CUSTOMER (NAME, ID)");
s.execute("CREATE INDEX IDX3 ON CUSTOMER (ID, NAME)");
s.execute("CREATE INDEX IDX4 ON CUSTOMER (ID)");
s.execute("CREATE INDEX IDX5 ON CUSTOMER (ID, NAME, D1, D2, D3)");
doImportTable(
"APP", "CUSTOMER", fileName, null, null, "UTF-16", 0);
runQueries(db_index, null, null);
s.execute("DROP INDEX IDX1 ");
s.execute("DROP INDEX IDX2 ");
s.execute("DROP INDEX IDX3 ");
s.execute("DROP INDEX IDX4 ");
s.execute("DROP INDEX IDX5 ");
// bulk insert to non-empty table, no indexes with replace, call
// import first to double the rows in the table.
// (last arg to Import 1 = replace).
doImportTable(
"APP", "CUSTOMER", fileName, null, null, "UTF-16", 0);
doImportTable(
"APP", "CUSTOMER", fileName, null, null, "UTF-16", 1);
runQueries(db_index, null, null);
// bulk insert to non-empty table, indexes with replace, call
// import first to double the rows in the table.
// (last arg to Import 1 = replace).
s.execute("CREATE INDEX IDX1 ON CUSTOMER (NAME)");
s.execute("CREATE INDEX IDX2 ON CUSTOMER (NAME, ID)");
s.execute("CREATE INDEX IDX3 ON CUSTOMER (ID, NAME)");
s.execute("CREATE INDEX IDX4 ON CUSTOMER (ID)");
s.execute("CREATE INDEX IDX5 ON CUSTOMER (ID, NAME, D1, D2, D3)");
doImportTable(
"APP", "CUSTOMER", fileName, null, null, "UTF-16", 0);
doImportTable(
"APP", "CUSTOMER", fileName, null, null, "UTF-16", 1);
runQueries(db_index, null, null);
s.execute("DROP INDEX IDX1 ");
s.execute("DROP INDEX IDX2 ");
s.execute("DROP INDEX IDX3 ");
s.execute("DROP INDEX IDX4 ");
s.execute("DROP INDEX IDX5 ");
dropTable();
commit();
}
private static final String[] derby2670_pattern =
{
"%",
"a%",
"b%",
"c%",
"%a%",
"%b%",
"%c%",
"%a",
"%b",
"%c"
};
private static final String[][][] derby2670_pattern_result =
{
// pattern = %
{ {"a"},
{"A"},
{" a"},
{"-a"},
{"\u00ADa"},
{"b"},
{"B"},
{" b"},
{"-b"},
{"\u00ADb"},
{"C"},
{"ekstra\u00ADarbeid"},
{"ekstrabetaling"},
{"ekstraarbeid"},
{"Wanvik"},
{"W\u00E5gan"},
{"Waagan"},
{"W\u00E5han"}
},
// pattern = a%
{ {"a"} },
// pattern = b%
{ {"b"} },
// pattern = c%
null,
// pattern = %a%
{ {"a"},
{" a"},
{"-a"},
{"\u00ADa"},
{"ekstra\u00ADarbeid"},
{"ekstrabetaling"},
{"ekstraarbeid"},
{"Wanvik"},
{"W\u00E5gan"},
{"Waagan"},
{"W\u00E5han"}
},
// pattern = %b%
{ {"b"},
{" b"},
{"-b"},
{"\u00ADb"},
{"ekstra\u00ADarbeid"},
{"ekstrabetaling"},
{"ekstraarbeid"}
},
// pattern = %c%
null,
// pattern = %a
{ {"a"},
{" a"},
{"-a"},
{"\u00ADa"}
},
// pattern = %b
{ {"b"},
{" b"},
{"-b"},
{"\u00ADb"}
},
// pattern = %c
null
};
/**
* Test case for DERBY-2670 - problem with like in no like processing.
* <p>
* Before fix, the table/query below would return results like B and
* C, obviously wrong for like %a%. The code was incorrectly caching
* collation key info in a DataValueDescriptor across the reuse of the
* holder object from one row to the next.
* <p>
* Added more patterns to also test DERBY-2710 and DERBY-2706, both
* to do with bad like optimization which can not be applied to collation
* based like.
**/
private void runDerby2670()
throws SQLException
{
Statement s = createStatement();
String[] rows =
{ "Waagan", "W\u00E5han", "Wanvik", "W\u00E5gan", "ekstrabetaling",
"ekstraarbeid", "ekstra\u00ADarbeid", "\u00ADa", "a", "\u00ADb",
"b", "-a", "-b", " a", " b", "A", "B", "C"
};
s.executeUpdate("create table t (x varchar(20))");
PreparedStatement ps = prepareStatement("insert into t values ?");
for (int i = 0; i < rows.length; i++) {
ps.setString(1, rows[i]);
ps.executeUpdate();
}
ps.close();
Assert.assertEquals(
"source and result arrays do not match for derby2670",
derby2670_pattern_result.length, derby2670_pattern.length);
String like_qry = "select * from t where x like ";
PreparedStatement ps_like =
prepareStatement("select * from t where x like ?");
PreparedStatement ps_like_orderby =
prepareStatement("select * from t where x like ? order by x");
for (int i = 0; i < derby2670_pattern.length; i++)
{
// Try just unordered like with constant pattern
String qry = like_qry + "'" + derby2670_pattern[i] + "'";
checkLangBasedQuery(
qry, derby2670_pattern_result[i], false);
// add an order by
qry += " order by x";
checkLangBasedQuery(
qry, derby2670_pattern_result[i], false);
// try parameter for pattern
ps_like.setString(1, derby2670_pattern[i]);
ResultSet rs = ps_like.executeQuery();
if (derby2670_pattern_result[i] == null)
JDBC.assertEmpty(rs);
else
JDBC.assertUnorderedResultSet(rs, derby2670_pattern_result[i]);
rs.close();
rs = null;
// try parameter for pattern
ps_like_orderby.setString(1, derby2670_pattern[i]);
rs = ps_like_orderby.executeQuery();
if (derby2670_pattern_result[i] == null)
JDBC.assertEmpty(rs);
else
JDBC.assertFullResultSet(rs, derby2670_pattern_result[i]);
rs.close();
rs = null;
}
// add an index and try it again.
s.executeUpdate("create index t_idx on t (x)");
like_qry = "select * from t where x like ";
for (int i = 0; i < derby2670_pattern.length; i++)
{
// Try just unordered like with constant pattern
String qry = like_qry + "'" + derby2670_pattern[i] + "'";
checkLangBasedQuery(
qry, derby2670_pattern_result[i], false);
// add an order by
qry += " order by x";
checkLangBasedQuery(
qry, derby2670_pattern_result[i], false);
// try parameter for pattern
ps_like.setString(1, derby2670_pattern[i]);
ResultSet rs = ps_like.executeQuery();
if (derby2670_pattern_result[i] == null)
JDBC.assertEmpty(rs);
else
JDBC.assertUnorderedResultSet(rs, derby2670_pattern_result[i]);
rs.close();
rs = null;
// try parameter for pattern
ps_like_orderby.setString(1, derby2670_pattern[i]);
rs = ps_like_orderby.executeQuery();
if (derby2670_pattern_result[i] == null)
JDBC.assertEmpty(rs);
else
JDBC.assertFullResultSet(rs, derby2670_pattern_result[i]);
rs.close();
rs = null;
}
s.executeUpdate("drop table t");
commit();
// cleanup
ps_like_orderby.close();
ps_like.close();
s.close();
}
/**
* Tests that truncating a table with indexes leaves us with a valid set
* of conglomerates.
*/
private void runDerby5530TruncateIndex()
throws SQLException {
setAutoCommit(false);
Statement s = createStatement();
s.executeUpdate("create table d5530i (val varchar(10))");
s.executeUpdate("create index idx on d5530i(val)");
s.executeUpdate("truncate table d5530i");
s.executeUpdate("insert into d5530i values 'one', 'two'");
ResultSet rs = s.executeQuery("select * from d5530i");
JDBC.assertUnorderedResultSet(rs, new String[][] {{"one"}, {"two"}});
rollback();
}
/**
* Tests that truncating a table without indexes leaves us with a valid
* conglomerate.
*/
private void runDerby5530TruncateNoIndex()
throws SQLException {
setAutoCommit(false);
Statement s = createStatement();
s.executeUpdate("create table d5530 (val varchar(10))");
s.executeUpdate("truncate table d5530");
s.executeUpdate("insert into d5530 values 'one', 'two'");
ResultSet rs = s.executeQuery("select * from d5530");
JDBC.assertUnorderedResultSet(rs, new String[][] {{"one"}, {"two"}});
rollback();
}
/**
* Shared code to run all test cases against a single collation.
* <p>
* Pass in the index of which TEST_DATABASE database to test. So
* for instance to run the default, pass in 0.
* <p>
*
* @param db_index index of which test to run.
*
* @exception SQLException
**/
private void runTestIter(
int db_index)
throws SQLException
{
setUpTable();
// run tests against base table no index, exercise heap path
// Tests the following:
// T0: Heap based compare using predicate pushing
// T3: order by on heap using in memory sorter
runQueries(db_index, null, null);
// run tests against base table with non unique index
// Tests the following:
// T1: (DONE) Index based compare start/stop predicates on index
runQueries(
db_index,
"CREATE INDEX NAME_IDX ON CUSTOMER (NAME)", "NAME_IDX");
// run tests against base table with only unique index
runQueries(
db_index,
"CREATE UNIQUE INDEX IDX ON CUSTOMER (NAME)", "IDX");
// run tests against base table with non unique descending index
runQueries(
db_index,
"CREATE INDEX NAME_IDX ON CUSTOMER (NAME DESC)", "NAME_IDX");
// run tests against base table with unique descending index
runQueries(
db_index,
"CREATE UNIQUE INDEX IDX ON CUSTOMER (NAME DESC)", "IDX");
// run tests against base table with unique composite key
runQueries(
db_index,
"CREATE UNIQUE INDEX IDX ON CUSTOMER (NAME, ID)", "IDX");
dropTable();
// the following tests mess with column values and ddl, so they
// are going to drop and recreate the small test data table.
runDERBY_2703(db_index);
runAlterTableAddColumn(db_index);
runAlterTableCompress(db_index);
// because of jvm issue described in DERBY-3055, do not
// test this with J2ME/JSR169.
if (JDBC.vmSupportsJDBC3())
runBulkInsert(db_index);
runLikeTests(db_index);
runDerby5530TruncateNoIndex();
runDerby5530TruncateIndex();
dropTable();
runAlterTableDropColumn(db_index);
commit();
}
/**************************************************************************
* Public Methods of This class:
**************************************************************************
*/
public void testDefaultCollation() throws SQLException
{
assertTrue(isDatabaseBasicCollation());
runTestIter(TEST_DEFAULT);
}
public void testEnglishCollation() throws SQLException
{
assertFalse(isDatabaseBasicCollation());
runTestIter(TEST_ENGLISH);
}
public void testPolishCollation() throws SQLException
{
assertFalse(isDatabaseBasicCollation());
runTestIter(TEST_POLISH);
}
public void testNorwayCollation() throws SQLException
{
assertFalse(isDatabaseBasicCollation());
runDerby2670();
runTestIter(TEST_NORWAY);
}
/**
* Test creating a TERRITORY_BASED collated database by only setting
* the collation attribute. The Territory will be picked up from the
* default territory of the JVM.
*
* Tests:
* T24: DERBY-2669 If no territory attribute is specified at create
* database time, then create collated db based on default
* territory of Database.
**/
public void testDefaultJVMTerritoryCollation() throws SQLException
{
Locale locale = Locale.getDefault();
if (locale.getLanguage().equals("en"))
{
testEnglishCollation();
}
else if (locale.getLanguage().equals("no"))
{
testNorwayCollation();
}
else if (locale.getLanguage().equals("po"))
{
testPolishCollation();
}
}
public static Test suite()
{
// only test in embedded mode, all tests are server side actions.
BaseTestSuite suite = new BaseTestSuite("CollationTest2");
suite.addTest(new CollationTest2("testDefaultCollation"));
suite.addTest(collatedTest("en", "testEnglishCollation"));
suite.addTest(caseInsensitiveCollationSuite());
// Only add tests for other locales if they are in fact supported
// by the jvm.
Locale[] availableLocales = Collator.getAvailableLocales();
boolean norwegian = false;
boolean polish = false;
for (int i=0; i<availableLocales.length ; i++) {
if("no".equals(availableLocales[i].getLanguage())) {
norwegian = true;
}
if("pl".equals(availableLocales[i].getLanguage())) {
polish = true;
}
}
if(norwegian) {
suite.addTest(collatedTest("no_NO", "testNorwayCollation"));
}
if(polish) {
suite.addTest(collatedTest("pl", "testPolishCollation"));
}
suite.addTest(collatedTest(null, "testDefaultJVMTerritoryCollation"));
// add support to use external files for import/export calls.
Test test = new SupportFilesSetup(suite);
// turn on log statement text for sequence of statements in derby.log.
if (verbose_debug)
{
Properties props = new Properties();
props.setProperty("derby.language.logStatementText", "true");
test = new SystemPropertyTestSetup(test, props);
}
return test;
}
private static Test collatedTest(String locale, String fixture)
{
return Decorator.territoryCollatedDatabase(
new CollationTest2(fixture), locale);
}
/**
* Returns a suite of tests running with a collation strength resulting
* in case insensitivity.
*
* @return A suite of tests.
*/
private static Test caseInsensitiveCollationSuite() {
BaseTestSuite suite =
new BaseTestSuite("Case insensitive specific tests");
suite.addTest(new CollationTest2("ci_testDerby5367"));
return Decorator.territoryCollatedCaseInsensitiveDatabase(
suite, "en_US");
}
}