|
1
5 package org.h2.test.synth;
6
7 import java.sql.Connection  ;
8 import java.sql.ResultSet ;
9 import java.sql.SQLException ;
10 import java.sql.Statement ;
11
12 import org.h2.test.TestBase;
13
14 public class TestSimpleIndex extends TestBase {
15
16 Connection conn;
17 Statement stat;
18 RandomGen random;
19
20 public void test() throws Exception {
21 deleteDb("simpleIndex");
22 conn = getConnection("simpleIndex");
23 random = new RandomGen(null);
24 stat = conn.createStatement();
25 for(int i=0; i<10000; i++) {
26 testIndex(i);
27 }
28 }
29
30 private void testIndex(int seed) throws Exception {
31 random.setSeed(seed);
32 String unique = random.nextBoolean() ? "UNIQUE " : "";
33 int len = random.getInt(2) + 1;
34 StringBuffer buff = new StringBuffer ();
35 for(int i=0; i<len; i++) {
36 if(i > 0) {
37 buff.append(", ");
38 }
39 buff.append((char)('A' + random.getInt(3)));
40 }
41 String cols = buff.toString();
42 execute("CREATE MEMORY TABLE TEST_M(A INT, B INT, C INT, DATA VARCHAR(255))");
43 execute("CREATE CACHED TABLE TEST_D(A INT, B INT, C INT, DATA VARCHAR(255))");
44 execute("CREATE MEMORY TABLE TEST_MI(A INT, B INT, C INT, DATA VARCHAR(255))");
45 execute("CREATE CACHED TABLE TEST_DI(A INT, B INT, C INT, DATA VARCHAR(255))");
46 execute("CREATE " + unique + "INDEX M ON TEST_MI("+cols+")");
47 execute("CREATE " + unique + "INDEX D ON TEST_DI("+cols+")");
48 for(int i=0; i<100; i++) {
49 println("i="+i);
50 testRows(i);
51 }
52 execute("DROP INDEX M");
53 execute("DROP INDEX D");
54 execute("DROP TABLE TEST_M");
55 execute("DROP TABLE TEST_D");
56 execute("DROP TABLE TEST_MI");
57 execute("DROP TABLE TEST_DI");
58 }
59
60 private void testRows(int id) throws Exception {
61 String a = randomValue(), b = randomValue(), c = randomValue();
62 String data = a + "/" + b + "/" + c;
63 String sql = "VALUES("+a+", "+b+", "+c+", '" + data + "')";
64 boolean em, ed;
65 try {
69 execute("INSERT INTO TEST_MI " + sql);
70 em = false;
71 } catch(SQLException e) {
72 em = true;
73 }
74 try {
75 execute("INSERT INTO TEST_DI " + sql);
76 ed = false;
77 } catch(SQLException e) {
78 ed = true;
79 }
80 if(em != ed) {
81 error("different result: ");
82 }
83 if(!em) {
84 execute("INSERT INTO TEST_M " + sql);
85 execute("INSERT INTO TEST_D " + sql);
86 }
87 StringBuffer buff = new StringBuffer ("WHERE 1=1");
88 int len = random.getLog(10);
89 for(int i=0; i<len; i++) {
90 buff.append(" AND ");
91 buff.append('A' + random.getInt(3));
92 switch(random.getInt(10)) {
93 case 0:
94 buff.append("<");
95 buff.append((random.getInt(100) - 50));
96 break;
97 case 1:
98 buff.append("<=");
99 buff.append((random.getInt(100) - 50));
100 break;
101 case 2:
102 buff.append(">");
103 buff.append((random.getInt(100) - 50));
104 break;
105 case 3:
106 buff.append(">=");
107 buff.append((random.getInt(100) - 50));
108 break;
109 case 4:
110 buff.append("<>");
111 buff.append((random.getInt(100) - 50));
112 break;
113 case 5:
114 buff.append(" IS NULL");
115 break;
116 case 6:
117 buff.append(" IS NOT NULL");
118 break;
119 default:
120 buff.append("=");
121 buff.append((random.getInt(100) - 50));
122 }
123 }
124 String where = buff.toString();
125 String r1 = getResult("SELECT DATA FROM TEST_M " + where + " ORDER BY DATA");
126 String r2 = getResult("SELECT DATA FROM TEST_D " + where + " ORDER BY DATA");
127 String r3 = getResult("SELECT DATA FROM TEST_MI " + where + " ORDER BY DATA");
128 String r4 = getResult("SELECT DATA FROM TEST_DI " + where + " ORDER BY DATA");
129 check(r1, r2);
130 check(r1, r3);
131 check(r1, r4);
132 }
133
134 private String getResult(String sql) throws Exception {
135 ResultSet rs = stat.executeQuery(sql);
136 StringBuffer buff = new StringBuffer ();
137 while(rs.next()) {
138 buff.append(rs.getString(1));
139 buff.append("; ");
140 }
141 rs.close();
142 return buff.toString();
143 }
144
145 private String randomValue() {
146 return random.getInt(10) == 0 ? "NULL" : ""+(random.getInt(100) - 50);
147 }
148
149 private void execute(String sql) throws Exception {
150 try {
151 println(sql + ";");
152 stat.execute(sql);
153 println("> update count: 1");
154 } catch(SQLException e) {
155 println("> exception");
156 throw e;
157 }
158 }
159
160 }
161 |
Popular Tags |
Java API By Example, From Geeks To Geeks.