Source code: org/hsqldb/test/TestSql.java
1 /* Copyright (c) 2001-2002, The HSQL Development Group
2 * All rights reserved.
3 *
4 * Redistribution and use in source and binary forms, with or without
5 * modification, are permitted provided that the following conditions are met:
6 *
7 * Redistributions of source code must retain the above copyright notice, this
8 * list of conditions and the following disclaimer.
9 *
10 * Redistributions in binary form must reproduce the above copyright notice,
11 * this list of conditions and the following disclaimer in the documentation
12 * and/or other materials provided with the distribution.
13 *
14 * Neither the name of the HSQL Development Group nor the names of its
15 * contributors may be used to endorse or promote products derived from this
16 * software without specific prior written permission.
17 *
18 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29 */
30
31
32 package org.hsqldb.test;
33
34 import java.sql.*;
35 import java.io.*;
36 import java.util.Properties;
37 import junit.framework.*;
38
39 /**
40 * Test sql statements via jdbc against in-memory database
41 * @author fredt@users
42 */
43 public class TestSql extends TestCase {
44
45 // protected String url = "jdbc:hsqldb:hsql://localhost";
46 String url = "jdbc:hsqldb:test3";
47 String user;
48 String password;
49 Statement stmnt;
50 Connection cConnection;
51 String getColumnName;
52
53 public TestSql(String name) {
54 super(name);
55 }
56
57 protected void setUp() {
58
59 user = "sa";
60 password = "";
61 stmnt = null;
62 cConnection = null;
63 getColumnName = "false";
64
65 Properties props = new Properties();
66
67 props.put("user", user);
68 props.put("password", password);
69 props.put("jdbc.strict_md", "false");
70 props.put("jdbc.get_column_name", getColumnName);
71
72 try {
73 Class.forName("org.hsqldb.jdbcDriver");
74
75 cConnection = DriverManager.getConnection(url, props);
76 stmnt = cConnection.createStatement();
77 } catch (Exception e) {
78 e.printStackTrace();
79 System.out.println("TestSql.setUp() error: " + e.getMessage());
80 }
81 }
82
83 public void testMetaData() {
84
85 String ddl1 =
86 "CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP' NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP' NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
87 String ddl2 =
88 "CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
89 String ddl3 =
90 "CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
91 String result1 = "1";
92 String result2 = "2";
93 String result3 = "3";
94 String result4 = "4";
95 String result5 = "5";
96
97 try {
98 stmnt.execute(ddl1);
99 stmnt.execute(ddl2);
100 stmnt.execute(ddl3);
101
102 DatabaseMetaData md = cConnection.getMetaData();
103
104 {
105 ResultSet rs;
106
107 rs = md.getPrimaryKeys(null, null, "USER");
108
109 ResultSetMetaData rsmd = rs.getMetaData();
110 String result0 = "";
111
112 for (; rs.next(); ) {
113 for (int i = 0; i < rsmd.getColumnCount(); i++) {
114 result0 += rs.getString(i + 1) + ":";
115 }
116
117 result0 += "\n";
118 }
119
120 rs.close();
121 System.out.println(result0);
122 }
123
124 {
125 ResultSet rs;
126
127 rs = md.getBestRowIdentifier(null, null, "USER", 0, true);
128
129 ResultSetMetaData rsmd = rs.getMetaData();
130 String result0 = "";
131
132 for (; rs.next(); ) {
133 for (int i = 0; i < rsmd.getColumnCount(); i++) {
134 result0 += rs.getString(i + 1) + ":";
135 }
136
137 result0 += "\n";
138 }
139
140 rs.close();
141 System.out.println(result0);
142 }
143
144 {
145 ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
146 ResultSetMetaData rsmd = rs.getMetaData();
147
148 result1 = "";
149
150 for (; rs.next(); ) {
151 for (int i = 0; i < rsmd.getColumnCount(); i++) {
152 result1 += rs.getString(i + 1) + ":";
153 }
154
155 result1 += "\n";
156 }
157
158 rs.close();
159 System.out.println(result1);
160 }
161
162 {
163 ResultSet rs = md.getCrossReference(null, null,
164 "ADDRESSBOOK_CATEGORY",
165 null, null,
166 "ADDRESSBOOK");
167 ResultSetMetaData rsmd = rs.getMetaData();
168
169 result2 = "";
170
171 for (; rs.next(); ) {
172 for (int i = 0; i < rsmd.getColumnCount(); i++) {
173 result2 += rs.getString(i + 1) + ":";
174 }
175
176 result2 += "\n";
177 }
178
179 rs.close();
180 System.out.println(result2);
181 }
182
183 {
184 ResultSet rs = md.getExportedKeys(null, null, "USER");
185 ResultSetMetaData rsmd = rs.getMetaData();
186
187 result3 = "";
188
189 for (; rs.next(); ) {
190 for (int i = 0; i < rsmd.getColumnCount(); i++) {
191 result3 += rs.getString(i + 1) + ":";
192 }
193
194 result3 += "\n";
195 }
196
197 rs.close();
198 System.out.println(result3);
199 }
200
201 {
202 ResultSet rs = md.getCrossReference(null, null, "USER", null,
203 null,
204 "ADDRESSBOOK_CATEGORY");
205 ResultSetMetaData rsmd = rs.getMetaData();
206
207 result4 = "";
208
209 for (; rs.next(); ) {
210 for (int i = 0; i < rsmd.getColumnCount(); i++) {
211 result4 += rs.getString(i + 1) + ":";
212 }
213
214 result4 += "\n";
215 }
216
217 rs.close();
218 System.out.println(result4);
219 }
220
221 {
222 stmnt.executeQuery("CREATE TABLE T (A CHAR, B CHAR);");
223 stmnt.executeQuery(
224 "INSERT INTO T VALUES ('get_column_name', '"
225 + getColumnName + "');");
226
227 ResultSet rs = stmnt.executeQuery(
228 "SELECT A, B, A \"aliasA\", B \"aliasB\" FROM T;");
229 ResultSetMetaData rsmd = rs.getMetaData();
230
231 result5 = "";
232
233 for (; rs.next(); ) {
234 for (int i = 0; i < rsmd.getColumnCount(); i++) {
235 result5 += rsmd.getColumnName(i + 1) + ":"
236 + rs.getString(i + 1) + ":";
237 }
238
239 result5 += "\n";
240 }
241
242 rs.close();
243
244 rs = stmnt.executeQuery(
245 "SELECT A, B, A \"aliasA\", B \"aliasB\" FROM T;");;
246 rsmd = rs.getMetaData();
247
248 for (; rs.next(); ) {
249 for (int i = 0; i < rsmd.getColumnCount(); i++) {
250 result5 += rsmd.getColumnLabel(i + 1) + ":"
251 + rs.getString(i + 1) + ":";
252 }
253
254 result5 += "\n";
255 }
256
257 // most of these will throw if strict_md is true
258 rsmd.isAutoIncrement(1);
259 rsmd.isCaseSensitive(1);
260 rsmd.isCurrency(1);
261 rsmd.isDefinitelyWritable(1);
262 rsmd.isNullable(1);
263 rsmd.isReadOnly(1);
264 rsmd.isSearchable(1);
265 rsmd.isSigned(1);
266 rsmd.isWritable(1);
267 rs.close();
268 System.out.println(result5);
269 }
270 } catch (SQLException e) {
271 fail(e.getMessage());
272 }
273
274 // assert equality of exported and imported with xref
275 assertEquals(result1, result2);
276 assertEquals(result3, result4);
277 }
278
279 /**
280 * Demonstration of a reported bug.<p>
281 * Because all values were turned into strings with toString before
282 * PreparedStatement.executeQuery() was called, special values such as
283 * NaN were not accepted. In 1.7.0 these values are inserted as nulls
284 * (fredt)<b>
285 *
286 * This test can be extended to cover various conversions through JDBC
287 *
288 */
289 public void testDoubleNaN() {
290
291 double value = 0;
292 boolean wasEqual = false;
293 String message = "DB operation completed";
294 String ddl1 =
295 "DROP TABLE t1 IF EXISTS;"
296 + "CREATE TABLE t1 ( d DECIMAL, f DOUBLE, l BIGINT, i INTEGER, s SMALLINT, t TINYINT, "
297 + "dt DATE DEFAULT 'CURRENT_DATE', ti TIME DEFAULT 'CURRENT_TIME', ts TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP' );";
298
299 try {
300 stmnt.execute(ddl1);
301
302 PreparedStatement ps = cConnection.prepareStatement(
303 "INSERT INTO t1 (d,f,l,i,s,t,dt,ti,ts) VALUES (?,?,?,?,?,?,?,?,?)");
304
305 ps.setString(1, "0.2");
306 ps.setDouble(2, 0.2);
307 ps.setLong(3, java.lang.Long.MAX_VALUE);
308 ps.setInt(4, Integer.MAX_VALUE);
309 ps.setInt(5, Short.MAX_VALUE);
310 ps.setInt(6, 0);
311 ps.setDate(7, new java.sql.Date(System.currentTimeMillis()));
312 ps.setTime(8, new java.sql.Time(System.currentTimeMillis()));
313 ps.setTimestamp(
314 9, new java.sql.Timestamp(System.currentTimeMillis()));
315 ps.execute();
316 ps.setInt(1, 0);
317 ps.setDouble(2, java.lang.Double.NaN);
318 ps.setLong(3, java.lang.Long.MIN_VALUE);
319 ps.setInt(4, Integer.MIN_VALUE);
320 ps.setInt(5, Short.MIN_VALUE);
321 ps.setInt(6, 0);
322
323 // allowed conversions
324 ps.setTimestamp(
325 7, new java.sql.Timestamp(System.currentTimeMillis() + 1));
326 ps.setTime(8, new java.sql.Time(System.currentTimeMillis() + 1));
327 ps.setDate(9, new java.sql.Date(System.currentTimeMillis() + 1));
328 ps.execute();
329 ps.setInt(1, 0);
330 ps.setDouble(2, java.lang.Double.POSITIVE_INFINITY);
331 ps.setInt(4, Integer.MIN_VALUE);
332 ps.setObject(5, new Short((short) 2), Types.SMALLINT);
333 ps.setObject(6, new Integer(2), Types.TINYINT);
334
335 // allowed conversions
336 ps.setObject(7, new java.sql.Date(System.currentTimeMillis()
337 + 2));
338 ps.setObject(8, new java.sql.Time(System.currentTimeMillis()
339 + 2));
340 ps.setObject(9, new java.sql.Timestamp(System.currentTimeMillis()
341 + 2));
342 ps.execute();
343
344 ResultSet rs =
345 stmnt.executeQuery("SELECT d, f, l, i, s*2, t FROM t1");
346 boolean result = rs.next();
347
348 value = rs.getDouble(2);
349
350 // int smallintValue = rs.getShort(3);
351 int integerValue = rs.getInt(4);
352
353 if (rs.next()) {
354 value = rs.getDouble(2);
355 wasEqual = Double.isNaN(value);
356 integerValue = rs.getInt(4);
357 }
358
359 rs = stmnt.executeQuery("SELECT MAX(i) FROM t1");
360
361 if (rs.next()) {
362 int max = rs.getInt(1);
363
364 System.out.println("Max value for i: " + max);
365 }
366
367 {
368
369 // test for the value MAX(column) in an empty table
370 rs = stmnt.executeQuery(
371 "CREATE TABLE cdType (ID INTEGER NOT NULL, name VARCHAR(50), PRIMARY KEY(ID))");
372 rs = stmnt.executeQuery("SELECT MAX(ID) FROM cdType");
373
374 if (rs.next()) {
375 int max = rs.getInt(1);
376
377 System.out.println("Max value for ID: " + max);
378 } else {
379 System.out.println("Max value for ID not returned");
380 }
381
382 stmnt.executeUpdate(
383 "INSERT INTO cdType VALUES (10,'Test String');");
384
385 try {
386 stmnt.executeUpdate(
387 "INSERT INTO cdType VALUES (10,'Test String');");
388 } catch (SQLException e1) {
389 stmnt.execute("ROLLBACK");
390 cConnection.rollback();
391 }
392 }
393 } catch (SQLException e) {
394 fail(e.getMessage());
395 }
396
397 // assert new behaviour
398 assertEquals(true, wasEqual);
399 }
400
401 protected void tearDown() {
402
403 try {
404 cConnection.close();
405 } catch (Exception e) {
406 e.printStackTrace();
407 System.out.println("TestSql.tearDown() error: " + e.getMessage());
408 }
409 }
410
411 public static void main(String argv[]) {
412
413 TestResult result = new TestResult();
414 TestCase testA = new TestSql("testMetaData");
415 TestCase testB = new TestSql("testDoubleNaN");
416
417 testA.run(result);
418 testB.run(result);
419 System.out.println("TestSql error count: " + result.failureCount());
420 }
421 }