001: /*
002: * $Id: TestDMLMisc.java,v 1.5 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.PreparedStatement;
044: import java.sql.SQLException;
045:
046: import junit.framework.Test;
047: import junit.framework.TestSuite;
048:
049: import org.apache.commons.collections.Bag;
050: import org.apache.commons.collections.HashBag;
051:
052: /**
053: * Database Modification Language tests.
054: *
055: * @version $Revision: 1.5 $ $Date: 2005/12/20 18:32:44 $
056: * @author Ritesh Adval
057: * @author Ahimanikya Satapathy
058: */
059: public class TestDMLMisc extends AbstractFunctionalTest {
060:
061: //------------------------------------------------------------ Conventional
062:
063: public TestDMLMisc(String testName) {
064: super (testName);
065: }
066:
067: public static Test suite() {
068: return new TestSuite(TestDMLMisc.class);
069: }
070:
071: //--------------------------------------------------------------- Lifecycle
072:
073: public void setUp() throws Exception {
074: super .setUp();
075: }
076:
077: public void tearDown() throws Exception {
078: super .tearDown();
079: }
080:
081: //------------------------------------------------------------------- Tests
082:
083: public void testInvalidInsert() throws Exception {
084: _stmt.execute("create table foo ( id int, val varchar(10))");
085: _stmt
086: .executeUpdate("insert into foo (val, id) values ( 'zero', 0 )");
087: _stmt.executeUpdate("insert into foo values ( 1, 'one' )");
088: _stmt
089: .executeUpdate("insert into foo (id, val) values ( 2, 'two' )");
090: try {
091: _stmt
092: .executeUpdate("insert into foo (id, val) values ( 3 )");
093: fail("Expected SQLException");
094: } catch (SQLException e) {
095: // expected
096: }
097: try {
098: _stmt
099: .executeUpdate("insert into foo (id) values ( 3, 'three' )");
100: fail("Expected SQLException");
101: } catch (SQLException e) {
102: // expected
103: }
104: }
105:
106: public void testSimpleView() throws Exception {
107: _stmt.execute("create table foo ( id int, val varchar(10))");
108: _stmt.executeUpdate("insert into foo values ( 1, null )");
109: _stmt.executeUpdate("insert into foo values ( 2, 'two' )");
110: _stmt.executeUpdate("insert into foo values ( 3, null )");
111: _stmt
112: .execute("create view bar as select * from foo where val is not null");
113: _rset = _stmt.executeQuery("select * from bar");
114: assertNotNull(_rset);
115: assertTrue(_rset.next());
116: assertEquals(2, _rset.getInt(1));
117: assertFalse(_rset.next());
118:
119: try {
120: _stmt
121: .execute("create view bar as select * from foo where val is not null");
122: fail("Expected Exception");
123: } catch (Exception e) {
124: // expected
125: }
126:
127: _stmt
128: .execute("create view if not exists bar as select * from foo where val is not null");
129:
130: _stmt.execute("drop view bar");
131:
132: try {
133: _stmt.execute("drop view bar");
134: fail("Expected Exception");
135: } catch (Exception e) {
136: // expected
137: }
138: _stmt.execute("drop view if exists bar");
139: _rset.close();
140: }
141:
142: public void testTruncateTable() throws Exception {
143: createTableFoo();
144: populateTableFoo();
145: assertResult(NUM_ROWS_IN_FOO, "select count(*) from foo");
146: _stmt.execute("truncate table foo");
147: assertResult(0, "select count(*) from foo");
148: populateTableFoo();
149: assertResult(NUM_ROWS_IN_FOO, "select count(*) from foo");
150:
151: // now truncate twice, to test truncating an empty table
152: _stmt.execute("truncate table foo");
153: assertResult(0, "select count(*) from foo");
154: _stmt.execute("truncate table foo");
155: assertResult(0, "select count(*) from foo");
156: }
157:
158: public void testDefragTable() throws Exception {
159: _stmt.execute("create table foo ( str varchar(10), val int )");
160: assertEquals(1, _stmt
161: .executeUpdate("insert into foo values ( 'one', 1 )"));
162: assertEquals(1, _stmt
163: .executeUpdate("insert into foo values ( null, null )"));
164: assertEquals(1, _stmt
165: .executeUpdate("insert into foo values ( 'three', 3 )"));
166: assertEquals(2, _stmt
167: .executeUpdate("delete from foo where str is not null"));
168: _stmt.execute("defrag table foo");
169: }
170:
171: public void testSqlExceptionWhenExistingRowsViolateNewConstraint()
172: throws Exception {
173: _stmt.execute("create table foo ( str varchar(10) )");
174: assertEquals(1, _stmt
175: .executeUpdate("insert into foo values ( 'one' )"));
176: assertEquals(1, _stmt
177: .executeUpdate("insert into foo values ( null )"));
178: assertEquals(1, _stmt
179: .executeUpdate("insert into foo values ( 'three' )"));
180: try {
181: _stmt
182: .execute("alter table foo add constraint foo_not_null not null ( str )");
183: fail("Expected SQLException");
184: } catch (SQLException e) {
185: // expected
186: }
187: }
188:
189: public void testInsertWithUnspecifiedColumns() throws Exception {
190: _stmt
191: .execute("create table foo ( a integer, b varchar(10), c varchar(10), d varchar(10) )");
192: assertEquals(
193: 1,
194: _stmt
195: .executeUpdate("insert into foo ( a, b, c, d ) values ( 1, '1b', '1c', '1d' )"));
196: assertResult(new Object[] { new Integer(1), "1b", "1c", "1d" },
197: "select a, b, c, d from foo where a = 1");
198: assertEquals(
199: 1,
200: _stmt
201: .executeUpdate("insert into foo ( a, b, c ) values ( 2, '2b', '2c' )"));
202: assertResult(new Object[] { new Integer(2), "2b", "2c", null },
203: "select a, b, c, d from foo where a = 2");
204: assertEquals(
205: 1,
206: _stmt
207: .executeUpdate("insert into foo values ( 3, '3b', '3c', '3d' )"));
208: assertResult(new Object[] { new Integer(3), "3b", "3c", "3d" },
209: "select a, b, c, d from foo where a = 3");
210: assertEquals(
211: 1,
212: _stmt
213: .executeUpdate("insert into foo values ( 4, '4b', '4c' )"));
214: assertResult(new Object[] { new Integer(4), "4b", "4c", null },
215: "select a, b, c, d from foo where a = 4");
216: assertEquals(1, _stmt
217: .executeUpdate("insert into foo values ( 5, '5b' )"));
218: assertResult(new Object[] { new Integer(5), "5b", null, null },
219: "select a, b, c, d from foo where a = 5");
220: assertEquals(1, _stmt
221: .executeUpdate("insert into foo values ( 6 )"));
222: assertResult(new Object[] { new Integer(6), null, null, null },
223: "select a, b, c, d from foo where a = 6");
224: }
225:
226: public void testInsertWithLiteralDefault() throws Exception {
227: _stmt
228: .execute("create table foo ( id integer, str varchar(10) default 'xyzzy' )");
229: assertEquals(
230: 1,
231: _stmt
232: .executeUpdate("insert into foo ( id, str ) values ( 1, 'one' )"));
233: assertEquals(1, _stmt
234: .executeUpdate("insert into foo values ( 2, 'two' )"));
235: assertEquals(
236: 1,
237: _stmt
238: .executeUpdate("insert into foo ( id, str ) values ( 3, null )"));
239: assertEquals(1, _stmt
240: .executeUpdate("insert into foo values ( 4, null )"));
241: assertEquals(1, _stmt
242: .executeUpdate("insert into foo ( id ) values ( 5 )"));
243: _rset = _stmt.executeQuery("select str from foo order by id");
244: assertTrue(_rset.next());
245: assertEquals("one", _rset.getString(1));
246: assertTrue(_rset.next());
247: assertEquals("two", _rset.getString(1));
248: assertTrue(_rset.next());
249: assertEquals("xyzzy", _rset.getString(1));
250: assertTrue(_rset.next());
251: assertEquals("xyzzy", _rset.getString(1));
252: assertTrue(_rset.next());
253: assertEquals("xyzzy", _rset.getString(1));
254: assertTrue(!_rset.next());
255: }
256:
257: public void testAutonumberColumn() throws Exception {
258: _stmt.execute("create sequence foo_id_seq");
259: _stmt
260: .execute("create table foo ( id integer default foo_id_seq.nextval, str varchar(10) )");
261: assertEquals(
262: 1,
263: _stmt
264: .executeUpdate("insert into foo ( str ) values ( 'a' )"));
265: assertEquals(
266: 1,
267: _stmt
268: .executeUpdate("insert into foo ( id, str ) values ( null, 'b' )"));
269: assertEquals(
270: 1,
271: _stmt
272: .executeUpdate("insert into foo ( str ) values ( 'c' )"));
273: assertEquals(
274: 1,
275: _stmt
276: .executeUpdate("insert into foo ( id, str ) values ( null, 'd' )"));
277: assertEquals(
278: 1,
279: _stmt
280: .executeUpdate("insert into foo ( id, str ) values ( -17, 'e' )"));
281: _rset = _stmt.executeQuery("select id from foo order by str");
282: for (int i = 0; i < 4; i++) {
283: assertTrue(_rset.next());
284: assertEquals(i, _rset.getInt(1));
285: }
286: assertTrue(_rset.next());
287: assertEquals(-17, _rset.getInt(1));
288: assertTrue(!_rset.next());
289: }
290:
291: public void testAutonumberColumn2() throws Exception {
292: _stmt
293: .execute("create table foo ( id integer generated always as identity, str varchar(10) )");
294: assertEquals(
295: 1,
296: _stmt
297: .executeUpdate("insert into foo ( str ) values ( 'a' )"));
298: assertEquals(
299: 1,
300: _stmt
301: .executeUpdate("insert into foo ( str ) values ( 'b' )"));
302: assertEquals(
303: 1,
304: _stmt
305: .executeUpdate("insert into foo ( str ) values ( 'c' )"));
306: assertEquals(
307: 1,
308: _stmt
309: .executeUpdate("insert into foo ( str ) values ( 'd' )"));
310: _rset = _stmt.executeQuery("select id from foo order by str");
311: for (int i = 0; i < 4; i++) {
312: assertTrue(_rset.next());
313: assertEquals(i, _rset.getInt(1));
314: }
315: assertTrue(!_rset.next());
316: }
317:
318: public void testAutonumberColumn3() throws Exception {
319: _stmt
320: .execute("create table foo ( id integer generated by default as identity, str varchar(10) )");
321: assertEquals(
322: 1,
323: _stmt
324: .executeUpdate("insert into foo ( str ) values ( 'a' )"));
325: assertEquals(
326: 1,
327: _stmt
328: .executeUpdate("insert into foo ( id, str ) values ( null, 'b' )"));
329: assertEquals(
330: 1,
331: _stmt
332: .executeUpdate("insert into foo ( str ) values ( 'c' )"));
333: assertEquals(
334: 1,
335: _stmt
336: .executeUpdate("insert into foo ( id, str ) values ( null, 'd' )"));
337: assertEquals(
338: 1,
339: _stmt
340: .executeUpdate("insert into foo ( id, str ) values ( -17, 'e' )"));
341: _rset = _stmt.executeQuery("select id from foo order by str");
342: for (int i = 0; i < 4; i++) {
343: assertTrue(_rset.next());
344: assertEquals(i, _rset.getInt(1));
345: }
346: assertTrue(_rset.next());
347: assertEquals(-17, _rset.getInt(1));
348: assertTrue(!_rset.next());
349: }
350:
351: public void testInsertWithFunctions() throws Exception {
352: _stmt
353: .execute("create table foo ( id integer, str varchar(10), ustr varchar(10), dt date )");
354: assertEquals(
355: 1,
356: _stmt
357: .executeUpdate("insert into foo values ( 1, 'xyzzy', upper(str), now() )"));
358: _rset = _stmt.executeQuery("select id, str, ustr, dt from foo");
359: assertTrue(_rset.next());
360: assertEquals(1, _rset.getInt(1));
361: assertEquals("xyzzy", _rset.getString(2));
362: assertEquals("XYZZY", _rset.getString(3));
363: assertNotNull(_rset.getDate(4));
364: assertTrue(!_rset.next());
365: }
366:
367: // NOTE: This test is likely to need to change once we support client/server mode
368: // but for now it is simple and easy to fail on parse errors immediately
369: public void testAddBatchFailsImmediatelyOnParseError()
370: throws Exception {
371: try {
372: _stmt.addBatch("xyzzy");
373: fail("Expected SQLException");
374: } catch (SQLException e) {
375: // expected
376: }
377: }
378:
379: public void testCreateTableWithAlwaysGeneratedIdenity()
380: throws Exception {
381: try {
382: _stmt
383: .execute("create table FOOSEQ( NUM int generated always as identity start with 1 increment by 1 maxvalue 1000 minvalue 1 cycle, STR varchar, NUMTWO bigint generated by default as identity )");
384: fail("Expetecd Exception: multiple Identity not allowed");
385: } catch (SQLException e) {
386: // expected
387: }
388:
389: _stmt
390: .execute("create table FOOSEQ( NUM int generated always as identity start with 1 increment by 1 maxvalue 1000 minvalue 1 cycle, STR varchar, NUMTWO bigint)");
391:
392: try {
393: _stmt
394: .execute("insert into FOOSEQ ( NUM, STR, NUMTWO ) values ( NULL , NULL, NULL)");
395: fail("Expetecd Exception: can't insert value to generated column");
396: } catch (SQLException e) {
397: // expected
398: }
399:
400: for (int i = 0; i < 10; i += 2) {
401: _stmt
402: .execute("insert into FOOSEQ ( STR, NUMTWO ) values ( '"
403: + i + "' ," + i + " )");
404: _stmt
405: .execute("insert into FOOSEQ ( STR, NUMTWO ) values ( NULL, NULL)");
406: }
407:
408: String sql = "select NUM, STR, NUMTWO from FOOSEQ";
409: _rset = _stmt.executeQuery(sql);
410: assertNotNull("Should have been able to create ResultSet",
411: _rset);
412:
413: // can't assume the order in which rows will be returned
414: // so populate a set and compare 'em
415: Bag expected = new HashBag();
416: Bag found = new HashBag();
417:
418: for (int i = 0; i < 10; i += 2) {
419: assertTrue("ResultSet should contain more rows", _rset
420: .next());
421: expected.add(new Integer(i + 1));
422: int num = _rset.getInt(1);
423: assertTrue(!_rset.wasNull());
424: found.add(new Integer(num));
425:
426: assertTrue("ResultSet should contain more rows", _rset
427: .next());
428:
429: expected.add(new Integer(i + 2));
430: num = _rset.getInt(1);
431: assertTrue(!_rset.wasNull());
432: found.add(new Integer(num));
433:
434: }
435: assertTrue("ResultSet shouldn't have any more rows", !_rset
436: .next());
437: _rset.close();
438: assertEquals(expected, found);
439:
440: }
441:
442: public void testCreateTableWithGeneratedByDefaultIdenity()
443: throws Exception {
444:
445: _stmt
446: .execute("create table FOOSEQ( NUM int, STR varchar, NUMTWO bigint generated by default as identity start with 1 increment by 1 maxvalue 1000 minvalue 1 cycle)");
447: for (int i = 0; i < 10; i += 2) {
448: _stmt
449: .execute("insert into FOOSEQ ( NUM, STR, NUMTWO ) values ( "
450: + i + ",'" + i + "' ," + i + " )");
451: _stmt
452: .execute("insert into FOOSEQ ( NUM, STR, NUMTWO ) values ( NULL , NULL, NULL)");
453: }
454:
455: String sql = "select NUM, STR, NUMTWO from FOOSEQ";
456: _rset = _stmt.executeQuery(sql);
457: assertNotNull("Should have been able to create ResultSet",
458: _rset);
459:
460: // can't assume the order in which rows will be returned
461: // so populate a set and compare 'em
462: Bag expected = new HashBag();
463: Bag found = new HashBag();
464:
465: int seq = 1;
466: for (int i = 0; i < 10; i += 2) {
467: assertTrue("ResultSet should contain more rows", _rset
468: .next());
469: expected.add(new Long(i));
470: long num2 = _rset.getLong(3);
471: assertTrue(!_rset.wasNull());
472: found.add(new Long(num2));
473:
474: assertTrue("ResultSet should contain more rows", _rset
475: .next());
476:
477: expected.add(new Long(seq++));
478: num2 = _rset.getLong(3);
479: assertTrue(!_rset.wasNull());
480: found.add(new Long(num2));
481: }
482: assertTrue("ResultSet shouldn't have any more rows", !_rset
483: .next());
484: _rset.close();
485: assertEquals(expected, found);
486:
487: }
488:
489: public void test_insert_when_via_pstmt() throws Exception {
490: _stmt.execute("create table emp ( id int, name varchar(10) )");
491: _stmt
492: .execute("create table emp_target ( id int, name varchar(10))");
493: _stmt
494: .execute("create btree index emp_target_idx on emp_target ( id )");
495: _stmt
496: .execute("create table emp_log ( executionId int, id int, name varchar(10))");
497:
498: _stmt.execute("insert into emp values ( 1, 'Amy' )");
499: _stmt.execute("insert into emp values ( 2, 'Mike' )");
500: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
501:
502: // INSERT
503: PreparedStatement stmt = _conn
504: .prepareStatement("insert first when s_column1 = 1 then"
505: + " into emp_target (emp_target.id, emp_target.name)"
506: + " values (s_column1, s_column2)"
507: + " else into emp_log (executionId, id, name)"
508: + " values (?, s_column1, s_column2)"
509: + " ( SELECT S1.id as s_column1, S1.name AS s_column2 "
510: + " FROM emp S1)");
511:
512: stmt.setInt(1, 1);
513:
514: stmt.executeUpdate();
515: stmt.close();
516:
517: // SELECT on emp_target
518: _rset = _stmt.executeQuery("select * from emp_target");
519:
520: assertNotNull("Should have been able to create ResultSet",
521: _rset);
522: assertTrue(_rset.next());
523: assertEquals(1, _rset.getInt(1));
524: assertEquals("Amy", _rset.getString(2));
525: assertTrue("ResultSet shouldn't have any more rows", !_rset
526: .next());
527: _rset.close();
528:
529: //SELECT on emp_log
530: _rset = _stmt.executeQuery("select * from emp_log");
531:
532: assertNotNull("Should have been able to create ResultSet",
533: _rset);
534: assertTrue(_rset.next());
535: assertEquals(1, _rset.getInt(1));
536: assertEquals(2, _rset.getInt(2));
537: assertEquals("Mike", _rset.getString(3));
538:
539: assertTrue(_rset.next());
540: assertEquals(1, _rset.getInt(1));
541: assertEquals(3, _rset.getInt(2));
542: assertEquals("Teresa", _rset.getString(3));
543:
544: assertTrue("ResultSet shouldn't have any more rows", !_rset
545: .next());
546: _rset.close();
547:
548: }
549:
550: /**
551: * Tests insert-select when "case-when" used is used more than once in the Select
552: * statement. To test Engine handles it without throwing duplicate "CASEWHEN"
553: * alias/column name.
554: *
555: * @throws Exception
556: */
557: public void test_insert_select_cases() throws Exception {
558: String createT1 = "create table emp_tgt1 (emp_id number(10), emp_name varchar(100))";
559: String createS1 = "create table emp_src1 (emp_id number(10), emp_name varchar(100))";
560: String createS2 = "create table emp_src2 (emp_id number(10), emp_name varchar(100))";
561: String testInsert = "insert into emp_tgt1 (emp_id, emp_name)"
562: + " select "
563: + " case when (s1.emp_id IS NULL) "
564: + " then 0 "
565: + " else s1.emp_id "
566: + " end, "
567: + " case when (s1.emp_id > 50000) "
568: + " then s1.emp_id "
569: + " else s1.emp_name "
570: + " end "
571: + " from "
572: + " emp_src1 s1 INNER JOIN emp_src2 s2 ON s1.emp_id = s2.emp_id ";
573:
574: _stmt.execute(createT1);
575: _stmt.execute(createS1);
576: _stmt.execute(createS2);
577:
578: try {
579: _stmt.executeUpdate(testInsert);
580: } catch (Throwable ex) {
581: fail("Insert-Select exception:" + ex.toString());
582: }
583: }
584:
585: /**
586: * Tests insert-select when "count" function used is used more than once in the Select
587: * statement. To test Engine handles it without throwing duplicate "CASEWHEN"
588: * alias/column name.
589: *
590: * @throws Exception
591: */
592: public void test_insert_select_counts() throws Exception {
593: String createT1 = "create table emp_tgt1 (emp_id number(10), emp_name varchar(100))";
594: String createS1 = "create table emp_src1 (emp_id number(10), emp_name varchar(100))";
595: String testInsert = "insert into emp_tgt1 (emp_id, emp_name)"
596: + " select " + " count(emp_id), count(emp_name)"
597: + " from " + " emp_src1 ";
598:
599: _stmt.execute(createT1);
600: _stmt.execute(createS1);
601:
602: try {
603: _stmt.executeUpdate(testInsert);
604: } catch (Throwable ex) {
605: fail("Insert-Select exception:" + ex.toString());
606: }
607: }
608:
609: /**
610: * Tests insert-select when "max" function used is used more than once in the Select
611: * statement. To test Engine handles it without throwing duplicate "CASEWHEN"
612: * alias/column name.
613: *
614: * @throws Exception
615: */
616: public void test_insert_select_maxs() throws Exception {
617: String createT1 = "create table emp_tgt1 (emp_id number(10), emp_name varchar(100))";
618: String createS1 = "create table emp_src1 (emp_id number(10), emp_name varchar(100))";
619: String testInsert = "insert into emp_tgt1 (emp_id, emp_name)"
620: + " select " + " max(emp_id), max(emp_name)"
621: + " from " + " emp_src1 ";
622:
623: _stmt.execute(createT1);
624: _stmt.execute(createS1);
625:
626: try {
627: _stmt.executeUpdate(testInsert);
628: } catch (Throwable ex) {
629: fail("Insert-Select exception:" + ex.toString());
630: }
631: }
632:
633: public void testInsertSelectThreeTableJoinWithIsNullWhereCondition1()
634: throws Exception {
635: _stmt.execute("create table emp ( id int, name varchar(10) )");
636: _stmt
637: .execute("create table salary ( id int, base_salary int, bonus int )");
638: _stmt
639: .execute("create table emp_target ( id int, name varchar(10) , totalSalary int)");
640: _stmt
641: .execute("create btree index emp_target_idx on emp_target ( id )");
642:
643: _stmt.execute("insert into emp values ( 1, 'Amy' )");
644: _stmt.execute("insert into emp values ( 2, 'Mike' )");
645: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
646: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
647: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
648:
649: //insert select
650: int count = _stmt
651: .executeUpdate("insert into emp_target(id, name, totalSalary) select s1.id, s1.name, (s2.base_salary + s2.bonus) from emp s1 inner join salary s2 on (s1.id = s2.id) left outer join emp_target s3 on (s2.id = s3.id) where s3.id is null");
652: assertEquals(count, 2);
653:
654: //again insert select
655: count = _stmt
656: .executeUpdate("insert into emp_target(id, name, totalSalary) select s1.id, s1.name, (s2.base_salary + s2.bonus) from emp s1 inner join salary s2 on (s1.id = s2.id) left outer join emp_target s3 on (s2.id = s3.id) where s3.id is null");
657: assertEquals(count, 0);
658:
659: }
660:
661: public void testInsertSelectThreeTableJoinWithIsNullWhereCondition2()
662: throws Exception {
663: _stmt.execute("create table emp ( id int, name varchar(10) )");
664: _stmt
665: .execute("create table salary ( id int, base_salary int, bonus int )");
666: _stmt
667: .execute("create table emp_target ( id int, name varchar(10) , totalSalary int)");
668: _stmt
669: .execute("create btree index emp_target_idx on emp_target ( id )");
670:
671: _stmt.execute("insert into emp values ( 1, 'Amy' )");
672: _stmt.execute("insert into emp values ( 2, 'Mike' )");
673: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
674: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
675: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
676:
677: //insert select
678: int count = _stmt
679: .executeUpdate("insert into emp_target(id, name, totalSalary) select s1.id, s1.name, (s2.base_salary + s2.bonus) from emp s1 left outer join salary s2 on (s1.id = s2.id) left outer join emp_target s3 on (s2.id = s3.id) where s3.id is null");
680: assertEquals(count, 3);
681:
682: //again insert select
683: count = _stmt
684: .executeUpdate("insert into emp_target(id, name, totalSalary) select s1.id, s1.name, (s2.base_salary + s2.bonus) from emp s1 inner join salary s2 on (s1.id = s2.id) left outer join emp_target s3 on (s2.id = s3.id) where s3.id is null");
685: assertEquals(count, 0);
686:
687: }
688:
689: public void testInsertSelectWithGroupBy() throws Exception {
690: _stmt.execute("create table emp ( id int, name varchar(10) )");
691: _stmt
692: .execute("create table salary ( id int, base_salary int, bonus int )");
693: _stmt
694: .execute("create table emp_target ( id int, name varchar(10) , totalSalary int)");
695:
696: _stmt.execute("insert into emp values ( 1, 'Amy' )");
697: _stmt.execute("insert into emp values ( 2, 'Mike' )");
698: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
699: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
700: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
701: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
702: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
703:
704: //insert select
705: int count = _stmt
706: .executeUpdate("insert first when (myid > 0) then "
707: + " into emp_target(id, name, totalSalary) "
708: + " values(myid, myname, mysalary) "
709: + " (select s1.id myid, s1.name myname, sum(s2.base_salary + s2.bonus) as mysalary "
710: + " from emp s1 inner join salary s2 on (s1.id = s2.id) "
711: + " left outer join emp_target s3 on (s2.id = s3.id) "
712: + " where s3.id is null group by s1.id, s1.name)");
713: assertEquals(count, 2);
714:
715: //again insert select
716: count = _stmt
717: .executeUpdate("insert first when (myid > 0) then "
718: + " into emp_target(id, name, totalSalary) "
719: + " values(myid, myname, mysalary) "
720: + " (select s1.id myid, s1.name myname, sum(s2.base_salary + s2.bonus) as mysalary "
721: + " from emp s1 inner join salary s2 on (s1.id = s2.id) "
722: + " left outer join emp_target s3 on (s2.id = s3.id) "
723: + " where s3.id is null group by s1.id, s1.name)");
724: assertEquals(count, 0);
725:
726: }
727:
728: public void testTruncateOnIndexedTable() throws Exception {
729: _stmt.execute("create table x ( id int)");
730: _stmt.execute("create btree index idx1 on x (id)");
731: _stmt.execute("insert into x values ( 1)");
732: _stmt.execute("insert into x values ( 2)");
733:
734: //select
735: _rset = _stmt.executeQuery("select id from x ");
736: assertNotNull(_rset);
737: assertTrue(_rset.next());
738: assertTrue(_rset.next());
739: assertTrue(!_rset.next());
740: _rset.close();
741:
742: //truncate
743: _stmt.execute("truncate table x");
744:
745: //select id again
746: _rset = _stmt.executeQuery("select id from x ");
747: assertNotNull(_rset);
748: assertTrue(!_rset.next());
749:
750: //select where x = 1 condition
751: _rset = _stmt.executeQuery("select id from x where id = 1");
752: assertNotNull(_rset);
753: assertTrue(!_rset.next());
754: _rset.close();
755:
756: //select where x = 2 condition
757: _rset = _stmt.executeQuery("select id from x where id = 2");
758: assertNotNull(_rset);
759: assertTrue(!_rset.next());
760: _rset.close();
761:
762: }
763:
764: public void testAlterTable() throws Exception {
765: _stmt.execute("create table x ( id int, name varchar(5))");
766: _stmt.execute("insert into x values ( 1, 'aaa')");
767: _stmt.execute("insert into x values ( 2, 'bbb')");
768:
769: _stmt.execute("alter table x alter column id rename to myid ");
770:
771: try {
772: _stmt.executeQuery("select id from x ");
773: fail("Excepted Exception : column not found");
774: } catch (Exception e) {
775: // expected
776: }
777:
778: _stmt.execute("alter table x alter column myid rename to id ");
779: _stmt.execute("alter table x drop column id");
780:
781: // ALTER ADD COLUMN can be handled.
782: _stmt
783: .execute("alter table x add column id int default 1 not null");
784: _stmt.execute("insert into x (name) values ('name')");
785: assertResult(1, "select id from x where name = 'name'");
786:
787: // ALTER TABLE ALTER COLUMN definition.
788: _stmt.execute("alter table x drop column id cascade");
789: _stmt.execute("alter table x add column id int default 1");
790: _stmt.execute("alter table x alter column id set default 5");
791: _stmt.execute("insert into x (name) values ('name')");
792: assertResult("name", "select name from x where id = 5");
793:
794: _stmt.execute("alter table x alter column id drop default");
795: _stmt.execute("insert into x (name) values ('name2')");
796: _rset = _stmt
797: .executeQuery("select id from x where name = 'name2'");
798: assertTrue(_rset.next());
799: assertNull(_rset.getObject(1));
800: _rset.close();
801:
802: // ALTER TABLE <tablename> RENAME TO <newname>
803: _stmt.execute("alter table x rename to y ");
804: assertResult("name", "select name from y where id = 5");
805:
806: _stmt.execute("alter table y drop column id");
807:
808: try {
809: _stmt.execute("alter table y drop column name");
810: fail("Excepted Exception : can't drop last column");
811: } catch (Exception e) {
812: // expected
813: }
814:
815: }
816:
817: public void testAlterTableOnIndexedTable() throws Exception {
818: _stmt.execute("create table x ( id int, name varchar(5))");
819: _stmt.execute("create index idx1 on x (id)");
820: _stmt.execute("create index idx2 on x (name)");
821: _stmt.execute("insert into x values ( 1, 'aaa')");
822: _stmt.execute("insert into x values ( 2, 'bbb')");
823:
824: _stmt
825: .execute("alter table x alter column id rename to myid cascade");
826: _stmt.execute("create index idx1 on x (myid)");
827:
828: try {
829: _stmt.executeQuery("select id from x ");
830: fail("Excepted Exception : column not found");
831: } catch (Exception e) {
832: // expected
833: }
834:
835: _stmt
836: .execute("alter table x alter column myid rename to id cascade");
837: _stmt.execute("create index idx1 on x (id)");
838:
839: // ALTER ADD COLUMN can be handled.
840: _stmt.execute("alter table x alter column id set default 3");
841: _stmt.execute("insert into x (name) values ('name')");
842: assertResult(1, "select id from x where name = 'aaa'");
843: assertResult(2, "select id from x where name = 'bbb'");
844: assertResult(3, "select id from x where name = 'name'");
845:
846: _stmt.execute("alter table x add constraint primary key (id)");
847:
848: // ALTER TABLE ALTER COLUMN definition.
849: _stmt.execute("alter table x alter column id set default 5");
850: _stmt.execute("insert into x (name) values ('name')");
851: assertResult(1, "select id from x where name = 'aaa'");
852: assertResult(2, "select id from x where name = 'bbb'");
853: assertResult("name", "select name from x where id = 3");
854: assertResult("name", "select name from x where id = 5");
855:
856: try {
857: _stmt.execute("create index idx2 on x (name)");
858: fail("Excepted Exception : index already exists");
859: } catch (Exception e) {
860: // expected
861: }
862:
863: _stmt.execute("alter table x drop primary key");
864: _stmt.execute("alter table x alter column id drop default");
865: _stmt.execute("insert into x (name) values ('name2')");
866: _rset = _stmt
867: .executeQuery("select id from x where name = 'name2'");
868: assertTrue(_rset.next());
869: assertNull(_rset.getObject(1));
870: _rset.close();
871:
872: // ALTER TABLE <tablename> RENAME TO <newname>
873: _stmt.execute("alter table x rename to y cascade");
874: assertResult("name", "select name from y where id = 5");
875:
876: _stmt.execute("alter table y drop column id cascade");
877:
878: try {
879: _stmt.execute("alter table y drop column name cascade");
880: fail("Excepted Exception : can't drop last column");
881: } catch (Exception e) {
882: // expected
883: }
884:
885: try {
886: _stmt
887: .execute("alter table y add column id int default 2 primary key");
888: fail("Expected Exception: Could not apply constraint");
889: } catch (Exception e) {
890: // expected
891: }
892:
893: }
894:
895: public void testAlterTableOnAutonumberColumn() throws Exception {
896: _stmt
897: .execute("create table foo ( id integer generated always as identity start with 1, str varchar(10) )");
898: assertEquals(
899: 1,
900: _stmt
901: .executeUpdate("insert into foo ( str ) values ( 'a' )"));
902: assertEquals(
903: 1,
904: _stmt
905: .executeUpdate("insert into foo ( str ) values ( 'b' )"));
906:
907: try {
908: _stmt
909: .execute("alter table foo alter column id rename to myid ");
910: fail("Expected Exception: Can't rename generated columns");
911: } catch (Exception e) {
912: // expected
913: }
914:
915: assertResult("a", "select str from foo where id = 1");
916:
917: try {
918: _stmt
919: .execute("alter table foo alter column id drop default");
920: fail("Expected Exception: Can't rename generated columns");
921: } catch (Exception e) {
922: // expected
923: }
924: assertEquals(
925: 1,
926: _stmt
927: .executeUpdate("insert into foo ( str ) values ( 'c' )"));
928: assertResult("c", "select str from foo where id = 3");
929: }
930:
931: public void testDefalutValuesClauseInInsertCommand()
932: throws Exception {
933: _stmt
934: .execute("create table a(id int generated always as identity, name varchar(3) default 'xxx')");
935:
936: _stmt.execute("insert into a default values");
937: _stmt.execute("insert into a default values");
938:
939: //select
940: _rset = _stmt.executeQuery("select id, name from a ");
941: assertNotNull(_rset);
942: assertTrue(_rset.next());
943: assertEquals(0, _rset.getInt(1));
944: assertEquals("xxx", _rset.getString(2));
945:
946: assertTrue(_rset.next());
947: assertEquals(1, _rset.getInt(1));
948: assertEquals("xxx", _rset.getString(2));
949: assertTrue(!_rset.next());
950: _rset.close();
951: }
952:
953: }
|