001: /*
002: * $Id: TestDML.java,v 1.58 2005/12/20 18:32:44 ahimanikya Exp $
003: * =======================================================================
004: * Copyright (c) 2002-2005 Axion Development Team. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above
011: * copyright notice, this list of conditions and the following
012: * disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
020: * not be used to endorse or promote products derived from this
021: * software without specific prior written permission.
022: *
023: * 4. Products derived from this software may not be called "Axion", nor
024: * may "Tigris" or "Axion" appear in their names without specific prior
025: * written permission.
026: *
027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
038: * =======================================================================
039: */
040:
041: package org.axiondb.functional;
042:
043: import java.sql.BatchUpdateException;
044: import java.sql.PreparedStatement;
045: import java.sql.ResultSet;
046: import java.sql.SQLException;
047: import java.sql.Statement;
048:
049: import junit.framework.Test;
050: import junit.framework.TestSuite;
051:
052: import org.apache.commons.collections.Bag;
053: import org.apache.commons.collections.HashBag;
054:
055: /**
056: * Database Modification Language tests.
057: *
058: * @version $Revision: 1.58 $ $Date: 2005/12/20 18:32:44 $
059: * @author Chuck Burdick
060: * @author Rodney Waldhoff
061: */
062: public class TestDML extends AbstractFunctionalTest {
063:
064: //------------------------------------------------------------ Conventional
065:
066: public TestDML(String testName) {
067: super (testName);
068: }
069:
070: public static Test suite() {
071: return new TestSuite(TestDML.class);
072: }
073:
074: //--------------------------------------------------------------- Lifecycle
075:
076: public void setUp() throws Exception {
077: super .setUp();
078: }
079:
080: public void tearDown() throws Exception {
081: super .tearDown();
082: }
083:
084: //------------------------------------------------------------------- Tests
085:
086: public void testTruncateTable() throws Exception {
087: createTableFoo();
088: populateTableFoo();
089: assertResult(NUM_ROWS_IN_FOO, "select count(*) from foo");
090: _stmt.execute("truncate table foo");
091: assertResult(0, "select count(*) from foo");
092: populateTableFoo();
093: assertResult(NUM_ROWS_IN_FOO, "select count(*) from foo");
094:
095: // now truncate twice, to test truncating an empty table
096: _stmt.execute("truncate table foo");
097: assertResult(0, "select count(*) from foo");
098: _stmt.execute("truncate table foo");
099: assertResult(0, "select count(*) from foo");
100: }
101:
102: public void testInsertViaBatchStatementWithBadStatment()
103: throws Exception {
104: createTableFoo();
105: Statement stmt = _conn.createStatement();
106: stmt
107: .addBatch("insert into FOO (NUM, STR, NUMTWO ) values ( 1, 'xyzzy', null )");
108: stmt
109: .addBatch("insert into FOO (NUM, STR, NUMTWO ) values ( 'this is not a number', 'xyzzy', null )");
110: stmt
111: .addBatch("insert into FOO (NUM, STR, NUMTWO ) values ( 2, 'xyzzy', null )");
112: int[] results = null;
113: try {
114: stmt.executeBatch();
115: fail("Expected BatchUpdateException");
116: } catch (BatchUpdateException e) {
117: // expected
118: results = e.getUpdateCounts();
119: }
120: stmt.close();
121:
122: assertEquals(3, results.length);
123: assertEquals(1, results[0]);
124: assertEquals(Statement.EXECUTE_FAILED, results[1]);
125: assertEquals(1, results[2]);
126:
127: _rset = _stmt.executeQuery("select count(NUM) from FOO");
128: assertTrue(_rset.next());
129: assertEquals(2, _rset.getInt(1));
130: assertTrue(!_rset.next());
131: _rset.close();
132: }
133:
134: public void testInsertViaBatchStatement() throws Exception {
135: createTableFoo();
136:
137: Statement stmt = _conn.createStatement();
138:
139: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
140: stmt
141: .addBatch("insert into FOO (NUM, STR, NUMTWO ) values ( "
142: + i + ", 'xyzzy', null )");
143: }
144:
145: int[] results = stmt.executeBatch();
146: assertEquals(NUM_ROWS_IN_FOO, results.length);
147:
148: assertEquals(0, stmt.executeBatch().length);
149:
150: stmt.close();
151:
152: for (int i = 0; i < results.length; i++) {
153: assertEquals(1, results[i]);
154: }
155:
156: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
157: _rset = _stmt
158: .executeQuery("select NUM, STR from FOO where NUM = "
159: + i);
160: assertTrue(_rset.next());
161: assertEquals(i, _rset.getInt(1));
162: assertEquals("xyzzy", _rset.getString(2));
163: assertTrue(!_rset.next());
164: _rset.close();
165: }
166: }
167:
168: public void testInsertViaBatchPreparedStatement() throws Exception {
169: createTableFoo();
170:
171: PreparedStatement pstmt = _conn
172: .prepareStatement("insert into FOO (NUM, STR, NUMTWO ) values ( ?, 'xyzzy', null )");
173:
174: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
175: pstmt.setInt(1, i);
176: pstmt.addBatch();
177: }
178:
179: int[] results = pstmt.executeBatch();
180: assertEquals(NUM_ROWS_IN_FOO, results.length);
181:
182: assertEquals(0, pstmt.executeBatch().length);
183:
184: pstmt.close();
185:
186: for (int i = 0; i < results.length; i++) {
187: assertEquals(1, results[i]);
188: }
189:
190: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
191: _rset = _stmt
192: .executeQuery("select NUM, STR from FOO where NUM = "
193: + i);
194: assertTrue(_rset.next());
195: assertEquals(i, _rset.getInt(1));
196: assertEquals("xyzzy", _rset.getString(2));
197: assertTrue(!_rset.next());
198: _rset.close();
199: }
200: }
201:
202: public void testCreateIndexOnAlreadyPopulatedTable()
203: throws Exception {
204: createTableFoo(false);
205: populateTableFoo();
206: assertEquals(1, _stmt
207: .executeUpdate("delete from FOO where NUM = 1"));
208: assertEquals(
209: 1,
210: _stmt
211: .executeUpdate("update FOO set NUM = 1, STR = '1', NUMTWO = 0 where NUM = 2"));
212: assertEquals(
213: 1,
214: _stmt
215: .executeUpdate("insert into FOO (NUM, STR, NUMTWO ) values ( 2, '2', 1 )"));
216: createIndexOnFoo();
217:
218: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
219: _rset = _stmt
220: .executeQuery("select NUM, STR from FOO where NUM = "
221: + i);
222: assertTrue(_rset.next());
223: assertEquals(i, _rset.getInt(1));
224: assertEquals(String.valueOf(i), _rset.getString(2));
225: assertTrue(!_rset.next());
226: _rset.close();
227: }
228: }
229:
230: public void test_delete_from_foo_where_num_gteq_5()
231: throws Exception {
232: createTableFoo();
233: populateTableFoo();
234:
235: assertEquals(NUM_ROWS_IN_FOO - 5, _stmt
236: .executeUpdate("delete from FOO where NUM >= 5"));
237:
238: _rset = _stmt.executeQuery("select STR from FOO");
239: assertNotNull("Should have been able to create ResultSet",
240: _rset);
241: Bag expected = new HashBag();
242: Bag found = new HashBag();
243:
244: for (int i = 0; i < 5; i++) {
245: assertTrue("ResultSet should contain more rows", _rset
246: .next());
247: expected.add(String.valueOf(i));
248: String val = _rset.getString(1);
249: assertNotNull("Returned String should not be null", val);
250: assertTrue("Shouldn't have seen \"" + val + "\" yet",
251: !found.contains(val));
252: found.add(val);
253: }
254: assertTrue("ResultSet shouldn't have any more rows", !_rset
255: .next());
256: _rset.close();
257: assertEquals(expected, found);
258: }
259:
260: public void test_delete_via_pstmt() throws Exception {
261: createTableFoo();
262: populateTableFoo();
263:
264: PreparedStatement pstmt = _conn
265: .prepareStatement("delete from FOO where NUM >= ? and NUM < ?");
266: for (int i = 0; i < NUM_ROWS_IN_FOO; i += 2) {
267: pstmt.setInt(1, i);
268: pstmt.setInt(2, i + 2);
269: assertEquals(2, pstmt.executeUpdate());
270:
271: _rset = _stmt.executeQuery("select STR from FOO");
272: assertNotNull("Should have been able to create ResultSet",
273: _rset);
274: Bag expected = new HashBag();
275: Bag found = new HashBag();
276: for (int j = i + 2; j < NUM_ROWS_IN_FOO; j++) {
277: assertTrue("ResultSet should contain more rows", _rset
278: .next());
279: expected.add(String.valueOf(j));
280: String val = _rset.getString(1);
281: assertNotNull("Returned String should not be null", val);
282: assertTrue("ResultSet shouldn't think value was null",
283: !_rset.wasNull());
284: assertTrue("Shouldn't have seen \"" + val + "\" yet",
285: !found.contains(val));
286: found.add(val);
287: }
288: assertTrue("ResultSet shouldn't have any more rows", !_rset
289: .next());
290: _rset.close();
291: assertEquals(expected, found);
292:
293: pstmt.clearParameters();
294: }
295: }
296:
297: public void test_update_via_pstmt() throws Exception {
298: createTableFoo();
299: populateTableFoo();
300:
301: PreparedStatement pstmt = _conn
302: .prepareStatement("update FOO set STR = ? where NUM >= ? and NUM < ?");
303: for (int i = 0; i < NUM_ROWS_IN_FOO; i += 2) {
304: pstmt.setString(1, "X");
305: pstmt.setInt(2, i);
306: pstmt.setInt(3, i + 2);
307: assertEquals(2, pstmt.executeUpdate());
308:
309: _rset = _stmt
310: .executeQuery("select STR from FOO where STR = 'X'");
311: assertNotNull("Should have been able to create ResultSet",
312: _rset);
313: for (int j = 0; j < i + 2; j++) {
314: assertTrue("ResultSet should contain more rows", _rset
315: .next());
316: assertNotNull(_rset.getString(1));
317: assertEquals("X", _rset.getString(1));
318: }
319: assertTrue("ResultSet shouldn't have any more rows", !_rset
320: .next());
321: _rset.close();
322:
323: pstmt.clearParameters();
324: }
325: }
326:
327: public void test_update_key_via_pstmt() throws Exception {
328: createTableFoo();
329: populateTableFoo();
330:
331: PreparedStatement pstmt = _conn
332: .prepareStatement("update FOO set NUM = ? where NUM = ?");
333: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
334: pstmt.setInt(1, (10 * i));
335: pstmt.setInt(2, i);
336: assertEquals(1, pstmt.executeUpdate());
337:
338: _rset = _stmt.executeQuery("select NUM from FOO");
339: assertNotNull("Should have been able to create ResultSet",
340: _rset);
341: Bag expected = new HashBag();
342: Bag found = new HashBag();
343: for (int j = 0; j <= i; j++) {
344: assertTrue("ResultSet should contain more rows", _rset
345: .next());
346: expected.add(new Integer(j * 10));
347: found.add(new Integer(_rset.getInt(1)));
348: }
349: for (int j = i + 1; j < NUM_ROWS_IN_FOO; j++) {
350: assertTrue("ResultSet should contain more rows", _rset
351: .next());
352: expected.add(new Integer(j));
353: found.add(new Integer(_rset.getInt(1)));
354: }
355: assertTrue("ResultSet shouldn't have any more rows", !_rset
356: .next());
357: _rset.close();
358: assertEquals(expected, found);
359:
360: pstmt.clearParameters();
361: }
362: }
363:
364: public void testUpdateToColumn() throws Exception {
365: createTableFoo();
366: populateTableFoo();
367: assertEquals(NUM_ROWS_IN_FOO, _stmt
368: .executeUpdate("update FOO set NUMTWO = NUM"));
369: _rset = _stmt.executeQuery("select NUMTWO from FOO");
370: HashBag expected = new HashBag();
371: HashBag found = new HashBag();
372: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
373: assertTrue(_rset.next());
374: expected.add(new Integer(i));
375: found.add(new Integer(_rset.getInt(1)));
376: assertTrue(!_rset.wasNull());
377: }
378: assertTrue(!_rset.next());
379: assertEquals(expected, found);
380: }
381:
382: public void testUpdateToFunction() throws Exception {
383: createTableFoo();
384: populateTableFoo();
385: assertEquals(
386: NUM_ROWS_IN_FOO,
387: _stmt
388: .executeUpdate("update FOO set STR = CONCAT(STR,';',NUM)"));
389: _rset = _stmt.executeQuery("select STR from FOO");
390: HashBag expected = new HashBag();
391: HashBag found = new HashBag();
392: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
393: assertTrue(_rset.next());
394: expected.add(String.valueOf(i) + ";" + String.valueOf(i));
395: found.add(_rset.getString(1));
396: assertTrue(!_rset.wasNull());
397: }
398: assertTrue(!_rset.next());
399: assertEquals(expected, found);
400: }
401:
402: public void test_insert_null() throws Exception {
403: createTableFoo();
404:
405: for (int i = 0; i < 10; i++) {
406: _stmt
407: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( "
408: + i + ", NULL, NULL )");
409: }
410:
411: String sql = "select NUM, STR, NUMTWO from FOO";
412: _rset = _stmt.executeQuery(sql);
413: assertNotNull("Should have been able to create ResultSet",
414: _rset);
415:
416: // can't assume the order in which rows will be returned
417: // so populate a set and compare 'em
418: Bag expected = new HashBag();
419: Bag found = new HashBag();
420:
421: for (int i = 0; i < 10; i++) {
422: assertTrue("ResultSet should contain more rows", _rset
423: .next());
424: expected.add(new Integer(i));
425: int num = _rset.getInt(1);
426: assertTrue(!_rset.wasNull());
427: found.add(new Integer(num));
428: assertNull(_rset.getString(2));
429: assertTrue(_rset.wasNull());
430: assertEquals(0, _rset.getInt(3));
431: assertTrue(_rset.wasNull());
432: }
433: assertTrue("ResultSet shouldn't have any more rows", !_rset
434: .next());
435: _rset.close();
436: assertEquals(expected, found);
437: }
438:
439: public void test_insert_without_colnames() throws Exception {
440: createTableFoo();
441:
442: for (int i = 0; i < 3; i++) {
443: _stmt.execute("insert into FOO values ( " + i + ", '" + i
444: + "', " + (i / 2) + " )");
445: }
446:
447: String sql = "select STR, NUM, NUMTWO from FOO";
448: _rset = _stmt.executeQuery(sql);
449: assertNotNull("Should have been able to create ResultSet",
450: _rset);
451:
452: // can't assume the order in which rows will be returned
453: // so populate a bag and compare 'em
454:
455: Bag expectedStr = new HashBag();
456: Bag foundStr = new HashBag();
457: Bag expectedNum = new HashBag();
458: Bag foundNum = new HashBag();
459: Bag expectedNumtwo = new HashBag();
460: Bag foundNumtwo = new HashBag();
461:
462: for (int i = 0; i < 3; i++) {
463: expectedNum.add(new Integer(i));
464: expectedNumtwo.add(new Integer(i / 2));
465: expectedStr.add(String.valueOf(i));
466:
467: assertTrue("ResultSet should contain more rows", _rset
468: .next());
469: String strVal = _rset.getString(1);
470: assertNotNull("Returned String should not be null", strVal);
471: assertTrue("ResultSet shouldn't think value was null",
472: !_rset.wasNull());
473: assertTrue("Shouldn't have seen \"" + strVal + "\" yet",
474: !foundStr.contains(strVal));
475: foundStr.add(strVal);
476:
477: int intVal = _rset.getInt(2);
478: assertTrue("ResultSet shouldn't think value was null",
479: !_rset.wasNull());
480: assertTrue("Shouldn't have seen \"" + intVal + "\" yet",
481: !foundNum.contains(new Integer(intVal)));
482: foundNum.add(new Integer(intVal));
483:
484: int intVal2 = _rset.getInt(3);
485: assertTrue("ResultSet shouldn't think value was null",
486: !_rset.wasNull());
487: foundNumtwo.add(new Integer(intVal2));
488:
489: }
490: assertTrue("ResultSet shouldn't have any more rows", !_rset
491: .next());
492: _rset.close();
493: assertEquals(expectedStr, foundStr);
494: assertEquals(expectedNum, foundNum);
495: assertEquals(expectedNumtwo, foundNumtwo);
496: }
497:
498: public void test_insert_via_stmt() throws Exception {
499: createTableFoo();
500: // INSERT
501: for (int i = 0; i < 3; i++) {
502: assertEquals(
503: 1,
504: _stmt
505: .executeUpdate("insert into FOO ( NUM, STR, NUMTWO ) values ( "
506: + i
507: + ", '"
508: + i
509: + "', "
510: + (i / 2)
511: + " )"));
512: }
513:
514: // SELECT
515: _rset = _stmt.executeQuery("select STR from FOO");
516: assertNotNull("Should have been able to create ResultSet",
517: _rset);
518: Bag expected = new HashBag();
519: Bag found = new HashBag();
520: for (int i = 0; i < 3; i++) {
521: assertTrue("ResultSet should contain more rows", _rset
522: .next());
523: expected.add(String.valueOf(i));
524: String val = _rset.getString(1);
525: assertNotNull("Returned String should not be null", val);
526: assertTrue("ResultSet shouldn't think value was null",
527: !_rset.wasNull());
528: assertTrue("Shouldn't have seen \"" + val + "\" yet",
529: !found.contains(val));
530: found.add(val);
531: }
532: assertTrue("ResultSet shouldn't have any more rows", !_rset
533: .next());
534: _rset.close();
535: assertEquals(expected, found);
536: }
537:
538: public void test_insert_via_pstmt() throws Exception {
539: createTableFoo();
540: // INSERT
541: PreparedStatement stmt = _conn
542: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, ?, ? )");
543: for (int i = 0; i < 3; i++) {
544: stmt.setInt(1, i);
545: stmt.setString(2, String.valueOf(i));
546: stmt.setInt(3, i / 2);
547: assertEquals(1, stmt.executeUpdate());
548: }
549: stmt.close();
550:
551: // SELECT
552: _rset = _stmt.executeQuery("select STR from FOO");
553: assertNotNull("Should have been able to create ResultSet",
554: _rset);
555: Bag expected = new HashBag();
556: Bag found = new HashBag();
557: for (int i = 0; i < 3; i++) {
558: assertTrue("ResultSet should contain more rows", _rset
559: .next());
560: expected.add(String.valueOf(i));
561: String val = _rset.getString(1);
562: assertNotNull("Returned String should not be null", val);
563: assertTrue("ResultSet shouldn't think value was null",
564: !_rset.wasNull());
565: assertTrue("Shouldn't have seen \"" + val + "\" yet",
566: !found.contains(val));
567: found.add(val);
568: }
569: assertTrue("ResultSet shouldn't have any more rows", !_rset
570: .next());
571: _rset.close();
572: assertEquals(expected, found);
573: }
574:
575: public void test_insert_sequence_nextval() throws Exception {
576: createTableFoo();
577: createSequenceFooSeq();
578:
579: PreparedStatement stmt = _conn
580: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( foo_seq.nextval, null, ? )");
581: for (int i = 0; i < 10; i++) {
582: stmt.setInt(1, i);
583: assertEquals(1, stmt.executeUpdate());
584: }
585: stmt.close();
586:
587: // SELECT
588: _rset = _stmt.executeQuery("select NUM, NUMTWO from FOO");
589: assertNotNull("Should have been able to create ResultSet",
590: _rset);
591: Bag expected = new HashBag();
592: Bag found = new HashBag();
593: for (int i = 0; i < 10; i++) {
594: assertTrue("ResultSet should contain more rows", _rset
595: .next());
596: assertEquals(_rset.getInt(1), _rset.getInt(2));
597:
598: expected.add(String.valueOf(i));
599: String val = _rset.getString(1);
600: assertNotNull("Returned String should not be null", val);
601: assertTrue("ResultSet shouldn't think value was null",
602: !_rset.wasNull());
603: assertTrue("Shouldn't have seen \"" + val + "\" yet",
604: !found.contains(val));
605: found.add(val);
606: }
607: assertTrue("ResultSet shouldn't have any more rows", !_rset
608: .next());
609: _rset.close();
610: assertEquals(expected, found);
611: }
612:
613: public void test_update_to_sequence_nextval() throws Exception {
614: createTableFoo();
615: createSequenceFooSeq();
616: {
617: String sql = "insert into FOO ( NUM, STR, NUMTWO ) values ( ?, 'foo', 999 )";
618: PreparedStatement stmt = _conn.prepareStatement(sql);
619: stmt.setInt(1, 999);
620: assertEquals(1, stmt.executeUpdate());
621: stmt.close();
622: }
623: {
624: String sql = "update FOO set NUM = foo_seq.nextval where NUM = 999";
625: PreparedStatement stmt = _conn.prepareStatement(sql);
626: assertEquals(1, stmt.executeUpdate());
627: stmt.close();
628: }
629: {
630: String sql = "select NUM, STR, NUMTWO from FOO where STR = 'foo'";
631: PreparedStatement stmt = _conn.prepareStatement(sql);
632: ResultSet rset = stmt.executeQuery();
633: assertNotNull(rset);
634: assertTrue(rset.next());
635: assertTrue(999 != rset.getInt(1));
636: assertTrue(!rset.wasNull());
637: assertTrue(!rset.next());
638: rset.close();
639: stmt.close();
640: }
641: }
642:
643: public void test_update_multiple_rows_to_sequence_nextval()
644: throws Exception {
645: createTableFoo();
646: createSequenceFooSeq();
647: {
648: String sql = "insert into FOO ( NUM, STR, NUMTWO ) values ( ?, 'foo', 999 )";
649: PreparedStatement stmt = _conn.prepareStatement(sql);
650: stmt.setInt(1, 10);
651: assertEquals(1, stmt.executeUpdate());
652: stmt.setInt(1, 20);
653: assertEquals(1, stmt.executeUpdate());
654: stmt.close();
655: }
656: {
657: String sql = "update FOO set NUM = foo_seq.nextval where NUMTWO = 999";
658: PreparedStatement stmt = _conn.prepareStatement(sql);
659: assertEquals(2, stmt.executeUpdate());
660: stmt.close();
661: }
662: {
663: String sql = "select NUM, STR, NUMTWO from FOO where STR = 'foo'";
664: PreparedStatement stmt = _conn.prepareStatement(sql);
665: ResultSet rset = stmt.executeQuery();
666: assertNotNull(rset);
667: assertTrue(rset.next());
668: int valone = rset.getInt(1);
669: assertTrue(!rset.wasNull());
670: assertTrue(rset.next());
671: int valtwo = rset.getInt(1);
672: assertTrue(!rset.wasNull());
673: assertTrue(valone != valtwo);
674: assertTrue(!rset.next());
675: rset.close();
676: stmt.close();
677: }
678: }
679:
680: public void testFKConstraint1() throws Exception {
681: _stmt.execute("create table A (X number(9,0), Y number(9,0))");
682: _stmt.execute("create table B (X number(9,0), Y number(9,0))");
683:
684: _stmt
685: .execute("ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X,Y)");
686: _stmt
687: .execute("ALTER TABLE B ADD CONSTRAINT B2A FOREIGN KEY (X,Y) REFERENCES A");
688:
689: try {
690: _stmt.execute("insert into b values (1,1)");
691: fail("Expected SQLException parent row does not exist");
692: } catch (SQLException ignore) {
693: // keep going
694: }
695:
696: _stmt.execute("insert into a values (1,1)");
697: _stmt.execute("insert into a values (1,2)");
698:
699: _stmt.execute("insert into b values (1,1)");
700: _stmt.execute("insert into b values (1,2)");
701:
702: try {
703: _stmt.execute("delete from a where x = 1");
704: fail("Expected SQLException can't delete parent row if child row refering it");
705: } catch (SQLException ignore) {
706: // keep going
707: }
708:
709: try {
710: _stmt.execute("update b set x = 5 where b.y = 1");
711: fail("Expected SQLException can't delete parent row if child row refering it");
712: } catch (SQLException ignore) {
713: // keep going
714: }
715:
716: _stmt.execute("update b set x = null where b.y = 1");
717:
718: }
719:
720: public void testFKConstraint2() throws Exception {
721:
722: _stmt.execute("create table A (X number(9,0))");
723: _stmt.execute("create table B (X number(9,0))");
724: _stmt.execute("create table C (Y number(9,0))");
725:
726: _stmt
727: .execute("ALTER TABLE A ADD CONSTRAINT A_PK PRIMARY KEY (X)");
728:
729: // should automatically pick parent table column that matches the name for child column
730: _stmt
731: .execute("ALTER TABLE B ADD CONSTRAINT B2A FOREIGN KEY (X) REFERENCES A");
732:
733: // should automatically pick the PK of parent table column as FK
734: _stmt
735: .execute("ALTER TABLE C ADD CONSTRAINT C2A FOREIGN KEY (Y) REFERENCES A");
736:
737: try {
738: _stmt
739: .execute("ALTER TABLE C ADD CONSTRAINT C2A2 FOREIGN KEY (Y) REFERENCES A(D)");
740: fail("Expected SQLException Parent column does not exist");
741: } catch (SQLException ignore) {
742: // keep going
743: }
744: }
745:
746: }
|