|
1
5 package org.h2.test.db;
6
7 import java.sql.*;
8 import java.util.Random  ;
9
10 import org.h2.test.TestBase;
11
12 public class TestIndex extends TestBase {
13
14 Connection conn;
15 Statement stat;
16 Random random = new Random ();
17
18 private void reconnect() throws Exception {
19 if(conn != null) {
20 conn.close();
21 conn = null;
22 }
23 conn = getConnection("index");
24 stat = conn.createStatement();
25 }
26
27 public void test() throws Exception {
28 if(config.networked && config.big) {
29 return;
30 }
31
32 random.setSeed(100);
33
34 deleteDb("index");
35 testWideIndex(147);
36 testWideIndex(313);
37 testWideIndex(979);
38 testWideIndex(1200);
39 testWideIndex(2400);
40 if(config.big && config.logMode == 2) {
41 for(int i=0; i<2000; i++) {
42 if((i%100)==0) {
43 System.out.println("width: " + i);
44 }
45 testWideIndex(i);
46 }
47 }
48
49 testLike();
50 reconnect();
51 testConstraint();
52 testLargeIndex();
53 testMultiColumnIndex();
54 testHashIndex(true, false);
57 testHashIndex(false, false);
58 testHashIndex(true, true);
61 testHashIndex(false, true);
62 testMultiColumnHashIndex();
64
65 conn.close();
66 }
67
68 String getRandomString(int len) {
69 StringBuffer buff = new StringBuffer ();
70 for(int i = 0; i<len; i++) {
71 buff.append((char)('a' + random.nextInt(26)));
72 }
73 return buff.toString();
74 }
75
76 void testWideIndex(int length) throws Exception {
77 reconnect();
78 stat.execute("CREATE TABLE TEST(ID INT, NAME VARCHAR)");
79 stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
80 for(int i=0; i<100; i++) {
81 stat.execute("INSERT INTO TEST VALUES("+i+", SPACE("+length+") || "+i+" )");
82 }
83 ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY NAME");
84 while(rs.next()) {
85 int id = rs.getInt("ID");
86 String name = rs.getString("NAME");
87 check(""+id, name.trim());
88 }
89 if(!config.memory) {
90 reconnect();
91 rs = stat.executeQuery("SELECT * FROM TEST ORDER BY NAME");
92 while(rs.next()) {
93 int id = rs.getInt("ID");
94 String name = rs.getString("NAME");
95 check(""+id, name.trim());
96 }
97 }
98 stat.execute("DROP TABLE TEST");
99 }
100
101 void testLike() throws Exception {
102 reconnect();
103 stat.execute("CREATE TABLE ABC(ID INT, NAME VARCHAR)");
104 stat.execute("INSERT INTO ABC VALUES(1, 'Hello')");
105 PreparedStatement prep = conn.prepareStatement("SELECT * FROM ABC WHERE NAME LIKE CAST(? AS VARCHAR)");
106 prep.setString(1, "Hi%");
107 prep.execute();
108 stat.execute("DROP TABLE ABC");
109 }
110
111 void testConstraint() throws Exception {
112 if(config.memory) {
113 return;
114 }
115 stat.execute("CREATE TABLE PARENT(ID INT PRIMARY KEY)");
116 stat.execute("CREATE TABLE CHILD(ID INT PRIMARY KEY, PID INT, FOREIGN KEY(PID) REFERENCES PARENT(ID))");
117 reconnect();
118 stat.execute("DROP TABLE PARENT");
119 stat.execute("DROP TABLE CHILD");
120 }
121
122 void testLargeIndex() throws Exception {
123 random.setSeed(10);
124 for(int i=1; i<100; i += getSize(1000, 3)) {
125 stat.execute("DROP TABLE IF EXISTS TEST");
126 stat.execute("CREATE TABLE TEST(NAME VARCHAR("+i+"))");
127 stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
128 PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)");
129 for(int j=0; j<getSize(2, 5); j++) {
130 prep.setString(1, getRandomString(i));
131 prep.execute();
132 }
133 if(!config.memory) {
134 conn.close();
135 conn = getConnection("index");
136 stat = conn.createStatement();
137 }
138 ResultSet rs = stat.executeQuery("SELECT COUNT(*) FROM TEST WHERE NAME > 'mdd'");
139 rs.next();
140 int count = rs.getInt(1);
141 trace(i+" count="+count);
142 }
143
144 stat.execute("DROP TABLE IF EXISTS TEST");
145 }
146
147 void testHashIndex(boolean primaryKey, boolean hash) throws Exception {
148 if(config.memory) {
149 return;
150 }
151
152 reconnect();
153
154 stat.execute("DROP TABLE IF EXISTS TEST");
155 if(primaryKey) {
156 stat.execute("CREATE TABLE TEST(A INT PRIMARY KEY "+(hash?"HASH":"")+", B INT)");
157 } else {
158 stat.execute("CREATE TABLE TEST(A INT, B INT)");
159 stat.execute("CREATE UNIQUE "+(hash?"HASH":"")+" INDEX ON TEST(A)");
160 }
161 PreparedStatement prep;
162 prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
163 int len = getSize(5, 1000);
164 for(int a=0; a<len; a++) {
165 prep.setInt(1, a);
166 prep.setInt(2, a);
167 prep.execute();
168 check(1, getValue(stat, "SELECT COUNT(*) FROM TEST WHERE A=" + a));
169 check(0, getValue(stat, "SELECT COUNT(*) FROM TEST WHERE A=-1-" + a));
170 }
171
172 reconnect();
173
174 prep = conn.prepareStatement("DELETE FROM TEST WHERE A=?");
175 for(int a=0; a<len; a++) {
176 if(getValue(stat, "SELECT COUNT(*) FROM TEST WHERE A=" + a) != 1) {
177 check(1, getValue(stat, "SELECT COUNT(*) FROM TEST WHERE A=" + a));
178 }
179 prep.setInt(1, a);
180 check(1, prep.executeUpdate());
181 }
182 check(0, getValue(stat, "SELECT COUNT(*) FROM TEST"));
183 }
184
185 void testMultiColumnIndex() throws Exception {
186 stat.execute("DROP TABLE IF EXISTS TEST");
187 stat.execute("CREATE TABLE TEST(A INT, B INT)");
188 PreparedStatement prep;
189 prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
190 int len = getSize(3, 260);
191 for(int a=0; a<len; a++) {
192 prep.setInt(1, a);
193 prep.setInt(2, a);
194 prep.execute();
195 }
196 stat.execute("INSERT INTO TEST SELECT A, B FROM TEST");
197 stat.execute("CREATE INDEX ON TEST(A, B)");
198 prep = conn.prepareStatement("DELETE FROM TEST WHERE A=?");
199 for(int a=0; a<len; a++) {
200 log(stat, "SELECT * FROM TEST");
201 check(2, getValue(stat, "SELECT COUNT(*) FROM TEST WHERE A=" + (len-a-1)));
202 check((len-a)*2, getValue(stat, "SELECT COUNT(*) FROM TEST"));
203 prep.setInt(1, (len-a-1));
204 prep.execute();
205 }
206 check(0, getValue(stat, "SELECT COUNT(*) FROM TEST"));
207 }
208
209 void testMultiColumnHashIndex() throws Exception {
210 if(config.memory) {
211 return;
212 }
213
214 stat.execute("DROP TABLE IF EXISTS TEST");
215 stat.execute("CREATE TABLE TEST(A INT, B INT, DATA VARCHAR(255))");
216 stat.execute("CREATE UNIQUE HASH INDEX IDXAB ON TEST(A, B)");
217 PreparedStatement prep;
218 prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
219 int len = getSize(2, 14);
221 for(int a=0; a<len; a++) {
222 for(int b=0; b<len; b+=2) {
223 prep.setInt(1, a);
224 prep.setInt(2, b);
225 prep.setString(3, "i("+a+","+b+")");
226 prep.execute();
227 }
228 }
229
230 reconnect();
231
232 prep = conn.prepareStatement("UPDATE TEST SET DATA=DATA||? WHERE A=? AND B=?");
233 for(int a=0; a<len; a++) {
234 for(int b=0; b<len; b+=2) {
235 prep.setString(1, "u("+a+","+b+")");
236 prep.setInt(2, a);
237 prep.setInt(3, b);
238 prep.execute();
239 }
240 }
241
242 reconnect();
243
244 ResultSet rs = stat.executeQuery("SELECT * FROM TEST WHERE DATA <> 'i('||a||','||b||')u('||a||','||b||')'");
245 checkFalse(rs.next());
246 check(len*(len/2), getValue(stat, "SELECT COUNT(*) FROM TEST"));
247 stat.execute("DROP TABLE TEST");
248 }
249
250 int getValue(Statement stat, String sql) throws Exception {
251 ResultSet rs = stat.executeQuery(sql);
252 rs.next();
253 return rs.getInt(1);
254 }
255
256 void log(Statement stat, String sql) throws Exception {
257 trace(sql);
258 ResultSet rs = stat.executeQuery(sql);
259 int cols = rs.getMetaData().getColumnCount();
260 while(rs.next()) {
261 for(int i=0; i<cols; i++) {
262 trace("["+i+"]="+rs.getString(i+1));
263 }
264 }
265 trace("---done---");
266 }
267
268 }
269 |
Popular Tags |
Java API By Example, From Geeks To Geeks.