001: /*
002: * Copyright 2001-2007 Geert Bevin <gbevin[remove] at uwyn dot com>
003: * Distributed under the terms of either:
004: * - the common development and distribution license (CDDL), v1.0; or
005: * - the GNU Lesser General Public License, v2.1 or later
006: * $Id: TestCreateTableOracle.java 3696 2007-03-16 13:20:26Z gbevin $
007: */
008: package com.uwyn.rife.database.queries;
009:
010: import com.uwyn.rife.database.BeanImpl;
011: import com.uwyn.rife.database.BeanImplConstrained;
012: import com.uwyn.rife.database.exceptions.ColumnsRequiredException;
013: import com.uwyn.rife.database.exceptions.TableNameRequiredException;
014: import com.uwyn.rife.database.exceptions.UnsupportedSqlFeatureException;
015: import java.math.BigDecimal;
016: import java.sql.Blob;
017:
018: public class TestCreateTableOracle extends TestCreateTable {
019: public TestCreateTableOracle(String name) {
020: super (name);
021: }
022:
023: public void testInstantiationOracle() {
024: CreateTable query = new CreateTable(mOracle);
025: assertNotNull(query);
026: try {
027: query.getSql();
028: fail();
029: } catch (TableNameRequiredException e) {
030: assertEquals(e.getQueryName(), "CreateTable");
031: }
032: }
033:
034: public void testIncompleteQueryOracle() {
035: CreateTable query = new CreateTable(mOracle);
036: try {
037: query.getSql();
038: fail();
039: } catch (TableNameRequiredException e) {
040: assertEquals(e.getQueryName(), "CreateTable");
041: }
042: query.table("tablename");
043: try {
044: query.getSql();
045: fail();
046: } catch (ColumnsRequiredException e) {
047: assertEquals(e.getQueryName(), "CreateTable");
048: }
049: query.table("tablename").column("string", String.class);
050: assertNotNull(query.getSql());
051: }
052:
053: public void testClearOracle() {
054: CreateTable query = new CreateTable(mOracle);
055: query.table("tablename").column("string", String.class);
056: assertNotNull(query.getSql());
057: query.clear();
058: try {
059: query.getSql();
060: fail();
061: } catch (TableNameRequiredException e) {
062: assertEquals(e.getQueryName(), "CreateTable");
063: }
064: }
065:
066: public void testColumnOracle() {
067: CreateTable query = new CreateTable(mOracle);
068: query.table("tablename1").column("string", String.class)
069: .column("stringbuffer", StringBuffer.class).column(
070: "characterobject", Character.class).column(
071: "booleanobject", Boolean.class).column(
072: "byteobject", Byte.class).column(
073: "doubleobject", Double.class).column(
074: "floatobject", Float.class).column(
075: "integerobject", Integer.class).column(
076: "longobject", Long.class).column("shortobject",
077: Short.class).column("bigdecimal",
078: BigDecimal.class).column("charcolumn",
079: char.class).column("booleancolumn",
080: boolean.class).column("bytecolumn", byte.class)
081: .column("doublecolumn", double.class).column(
082: "floatcolumn", float.class).column("intcolumn",
083: int.class).column("longcolumn", long.class)
084: .column("shortcolumn", short.class).column(
085: "blobcolumn", Blob.class);
086: assertEquals(
087: query.getSql(),
088: "CREATE TABLE tablename1 (string VARCHAR2(4000), stringbuffer VARCHAR2(4000), characterobject CHAR, booleanobject NUMBER(1), byteobject NUMBER(3), doubleobject FLOAT, floatobject FLOAT, integerobject NUMBER(10), longobject NUMBER(19), shortobject NUMBER(5), bigdecimal NUMERIC, charcolumn CHAR, booleancolumn NUMBER(1), bytecolumn NUMBER(3), doublecolumn FLOAT, floatcolumn FLOAT, intcolumn NUMBER(10), longcolumn NUMBER(19), shortcolumn NUMBER(5), blobcolumn BLOB)");
089: // this is invalid to execute with Oracle
090: // VARCHAR2 and CHAR need size specification
091: }
092:
093: public void testColumnPrecisionOracle() {
094: CreateTable query = new CreateTable(mOracle);
095: query.table("tablename1").column("string", String.class, 255)
096: .column("stringbuffer", StringBuffer.class, 100)
097: .column("characterobject", Character.class, 20).column(
098: "booleanobject", Boolean.class, 7).column(
099: "byteobject", Byte.class, 9).column(
100: "doubleobject", Double.class, 30, 2).column(
101: "floatobject", Float.class, 20, 2).column(
102: "integerobject", Integer.class, 10).column(
103: "longobject", Long.class, 8).column(
104: "shortobject", Short.class, 8).column(
105: "bigdecimal", BigDecimal.class, 19, 9).column(
106: "charcolumn", char.class, 10).column(
107: "booleancolumn", boolean.class, 4).column(
108: "bytecolumn", byte.class, 8).column(
109: "doublecolumn", double.class, 12, 3).column(
110: "floatcolumn", float.class, 13, 2).column(
111: "intcolumn", int.class, 10).column(
112: "longcolumn", long.class, 12).column(
113: "shortcolumn", short.class, 9).column(
114: "blobcolumn", Blob.class, 20);
115: assertEquals(
116: query.getSql(),
117: "CREATE TABLE tablename1 (string VARCHAR2(255), stringbuffer VARCHAR2(100), characterobject CHAR(20), booleanobject NUMBER(1), byteobject NUMBER(3), doubleobject FLOAT, floatobject FLOAT, integerobject NUMBER(10), longobject NUMBER(19), shortobject NUMBER(5), bigdecimal NUMERIC(19,9), charcolumn CHAR(10), booleancolumn NUMBER(1), bytecolumn NUMBER(3), doublecolumn FLOAT, floatcolumn FLOAT, intcolumn NUMBER(10), longcolumn NUMBER(19), shortcolumn NUMBER(5), blobcolumn BLOB)");
118: execute(query);
119: }
120:
121: public void testColumnsBeanOracle() {
122: CreateTable query = new CreateTable(mOracle);
123: query.table("tablename").columns(BeanImpl.class);
124: assertEquals(
125: query.getSql(),
126: "CREATE TABLE tablename (propertyBigDecimal NUMERIC, propertyBoolean NUMBER(1), propertyBooleanObject NUMBER(1), propertyByte NUMBER(3), propertyByteObject NUMBER(3), propertyCalendar DATE, propertyChar CHAR, propertyCharacterObject CHAR, propertyDate DATE, propertyDouble FLOAT, propertyDoubleObject FLOAT, propertyEnum VARCHAR(255), propertyFloat FLOAT, propertyFloatObject FLOAT, propertyInt NUMBER(10), propertyIntegerObject NUMBER(10), propertyLong NUMBER(19), propertyLongObject NUMBER(19), propertyShort NUMBER(5), propertyShortObject NUMBER(5), propertySqlDate DATE, propertyString VARCHAR2(4000), propertyStringbuffer VARCHAR2(4000), propertyTime DATE, propertyTimestamp DATE, CHECK (propertyEnum IS NULL OR propertyEnum IN ('VALUE_ONE','VALUE_TWO','VALUE_THREE')))");
127: // this is invalid to execute with Oracle
128: // VARCHAR2 and CHAR need size specification
129: }
130:
131: public void testColumnsBeanIncludedOracle() {
132: CreateTable query = new CreateTable(mOracle);
133: query.table("tablename").columnsIncluded(
134: BeanImpl.class,
135: new String[] { "propertyBigDecimal", "propertyByte",
136: "propertyFloat", "propertyStringbuffer",
137: "propertyTime" });
138: assertEquals(
139: query.getSql(),
140: "CREATE TABLE tablename (propertyBigDecimal NUMERIC, propertyByte NUMBER(3), propertyFloat FLOAT, propertyStringbuffer VARCHAR2(4000), propertyTime DATE)");
141: // this is invalid to execute with Oracle
142: // VARCHAR2 and CHAR need size specification
143: }
144:
145: public void testColumnsBeanExcludedOracle() {
146: CreateTable query = new CreateTable(mOracle);
147: query.table("tablename").columnsExcluded(
148: BeanImpl.class,
149: new String[] { "propertyBigDecimal", "propertyByte",
150: "propertyFloat", "propertyStringbuffer",
151: "propertyTime" });
152: assertEquals(
153: query.getSql(),
154: "CREATE TABLE tablename (propertyBoolean NUMBER(1), propertyBooleanObject NUMBER(1), propertyByteObject NUMBER(3), propertyCalendar DATE, propertyChar CHAR, propertyCharacterObject CHAR, propertyDate DATE, propertyDouble FLOAT, propertyDoubleObject FLOAT, propertyEnum VARCHAR(255), propertyFloatObject FLOAT, propertyInt NUMBER(10), propertyIntegerObject NUMBER(10), propertyLong NUMBER(19), propertyLongObject NUMBER(19), propertyShort NUMBER(5), propertyShortObject NUMBER(5), propertySqlDate DATE, propertyString VARCHAR2(4000), propertyTimestamp DATE, CHECK (propertyEnum IS NULL OR propertyEnum IN ('VALUE_ONE','VALUE_TWO','VALUE_THREE')))");
155: // this is invalid to execute with Oracle
156: // VARCHAR2 and CHAR need size specification
157: }
158:
159: public void testColumnsBeanFilteredOracle() {
160: CreateTable query = new CreateTable(mOracle);
161: query
162: .table("tablename")
163: .columnsFiltered(
164: BeanImpl.class,
165: new String[] { "propertyBigDecimal",
166: "propertyByte", "propertyFloat",
167: "propertyStringbuffer", "propertyTime" },
168: new String[] { "propertyByte",
169: "propertyStringbuffer" });
170: assertEquals(
171: query.getSql(),
172: "CREATE TABLE tablename (propertyBigDecimal NUMERIC, propertyFloat FLOAT, propertyTime DATE)");
173: execute(query);
174: }
175:
176: public void testColumnsBeanPrecisionOracle() {
177: CreateTable query = new CreateTable(mOracle);
178: query.table("tablename").columns(BeanImpl.class).precision(
179: "propertyBigDecimal", 19, 9).precision(
180: "propertyBoolean", 4).precision(
181: "propertyBooleanObject", 7)
182: .precision("propertyByte", 8).precision(
183: "propertyByteObject", 9).precision(
184: "propertyCalendar", 20).precision(
185: "propertyChar", 10).precision(
186: "propertyCharacterObject", 12).precision(
187: "propertyDate", 7).precision("propertyDouble",
188: 12, 3).precision("propertyDoubleObject", 14, 4)
189: .precision("propertyFloat", 13, 2).precision(
190: "propertyFloatObject", 12, 1).precision(
191: "propertyInt", 10).precision(
192: "propertyIntegerObject", 8).precision(
193: "propertyLong", 12).precision(
194: "propertyLongObject", 11).precision(
195: "propertyShort", 9).precision(
196: "propertyShortObject", 6).precision(
197: "propertySqlDate", 8).precision(
198: "propertyString", 255).precision(
199: "propertyStringbuffer", 100).precision(
200: "propertyTime", 9).precision(
201: "propertyTimestamp", 30, 2).precision(
202: "propertyEnum", 14);
203: assertEquals(
204: query.getSql(),
205: "CREATE TABLE tablename (propertyBigDecimal NUMERIC(19,9), propertyBoolean NUMBER(1), propertyBooleanObject NUMBER(1), propertyByte NUMBER(3), propertyByteObject NUMBER(3), propertyCalendar DATE, propertyChar CHAR(10), propertyCharacterObject CHAR(12), propertyDate DATE, propertyDouble FLOAT, propertyDoubleObject FLOAT, propertyEnum VARCHAR(255), propertyFloat FLOAT, propertyFloatObject FLOAT, propertyInt NUMBER(10), propertyIntegerObject NUMBER(10), propertyLong NUMBER(19), propertyLongObject NUMBER(19), propertyShort NUMBER(5), propertyShortObject NUMBER(5), propertySqlDate DATE, propertyString VARCHAR2(255), propertyStringbuffer VARCHAR2(100), propertyTime DATE, propertyTimestamp DATE, CHECK (propertyEnum IS NULL OR propertyEnum IN ('VALUE_ONE','VALUE_TWO','VALUE_THREE')))");
206: execute(query);
207: }
208:
209: public void testColumnsBeanConstrainedOracle() {
210: CreateTable query = new CreateTable(mOracle);
211: query.table("tablename").columns(BeanImplConstrained.class);
212: assertEquals(
213: query.getSql(),
214: "CREATE TABLE tablename (propertyBigDecimal NUMERIC(17,6), propertyBoolean NUMBER(1), propertyBooleanObject NUMBER(1), propertyByte NUMBER(3), propertyByteObject NUMBER(3) NOT NULL, propertyCalendar DATE, propertyChar CHAR, propertyCharacterObject CHAR, propertyDate DATE, propertyDouble FLOAT, propertyDoubleObject FLOAT, propertyFloat FLOAT, propertyFloatObject FLOAT, propertyInt NUMBER(10) DEFAULT 23, propertyIntegerObject NUMBER(10), propertyLongObject NUMBER(19), propertyShort NUMBER(5), propertySqlDate DATE, propertyString VARCHAR2(30) DEFAULT 'one' NOT NULL, propertyStringbuffer VARCHAR2(20) NOT NULL, propertyTime DATE, propertyTimestamp DATE, PRIMARY KEY (propertyString), UNIQUE (propertyStringbuffer, propertyByteObject), UNIQUE (propertyStringbuffer), CHECK (propertyByteObject != -1), CHECK (propertyInt != 0), CHECK (propertyLongObject IS NULL OR propertyLongObject IN (89,1221,66875,878)), CHECK (propertyString IS NULL OR propertyString IN ('one','tw''''o','someotherstring')), CHECK (propertyStringbuffer != ''), CHECK (propertyStringbuffer != 'some''blurp'))");
215: execute(query);
216: }
217:
218: public void testNullableOracle() {
219: CreateTable query = new CreateTable(mOracle);
220: query.table("tablename").column("intColumn1", int.class,
221: CreateTable.NULL).column("stringColumn", String.class,
222: 12, CreateTable.NOTNULL)
223: .column("intColumn2", int.class).column("intColumn3",
224: int.class).column("floatColumn", float.class,
225: 13, 6, CreateTable.NOTNULL).nullable(
226: "intColumn2", CreateTable.NULL).nullable(
227: "intColumn3", CreateTable.NOTNULL).nullable(
228: "floatColumn", null);
229: assertEquals(
230: query.getSql(),
231: "CREATE TABLE tablename (intColumn1 NUMBER(10) NULL, stringColumn VARCHAR2(12) NOT NULL, intColumn2 NUMBER(10) NULL, intColumn3 NUMBER(10) NOT NULL, floatColumn FLOAT)");
232: execute(query);
233: }
234:
235: public void testDefaultOracle() {
236: CreateTable query = new CreateTable(mOracle);
237: query.table("tablename1").column("string", String.class, 255)
238: .column("stringbuffer", StringBuffer.class, 100)
239: .column("characterobject", Character.class, 22).column(
240: "booleanobject", Boolean.class, 7).column(
241: "byteobject", Byte.class, 9).column(
242: "doubleobject", Double.class, 30, 2).column(
243: "floatobject", Float.class, 20, 2).column(
244: "integerobject", Integer.class, 10).column(
245: "longobject", Long.class, 8).column(
246: "shortobject", Short.class, 8).column(
247: "bigdecimal", BigDecimal.class, 19, 9).column(
248: "charcolumn", char.class, 10).column(
249: "booleancolumn", boolean.class, 4).column(
250: "bytecolumn", byte.class, 8).column(
251: "doublecolumn", double.class, 12, 3).column(
252: "floatcolumn", float.class, 13, 2).column(
253: "intcolumn", int.class, 10).column(
254: "longcolumn", long.class, 12).column(
255: "shortcolumn", short.class, 9).defaultValue(
256: "string", "stringDefault").defaultValue(
257: "stringbuffer", "stringbufferDefault")
258: .defaultValue("characterobject",
259: "characterobjectDefault").defaultValue(
260: "booleanobject", new Boolean(true))
261: .defaultValue("byteobject", new Byte((byte) 34))
262: .defaultValue("doubleobject", new Double(234.87d))
263: .defaultValue("floatobject", new Float(834.43f))
264: .defaultValue("integerobject", new Integer(463))
265: .defaultValue("longobject", new Long(34876L))
266: .defaultValue("shortobject", new Short((short) 98))
267: .defaultValue("bigdecimal", new BigDecimal("347.14"))
268: .defaultValue("charcolumn", "OSJFDZ").defaultValue(
269: "booleancolumn", false).defaultValue(
270: "bytecolumn", (byte) 27).defaultValue(
271: "doublecolumn", 934.5d).defaultValue(
272: "floatcolumn", 35.87f).defaultValue(
273: "intcolumn", 983734).defaultValue("longcolumn",
274: 2343345L).defaultValue("shortcolumn", 12);
275: assertEquals(
276: query.getSql(),
277: "CREATE TABLE tablename1 (string VARCHAR2(255) DEFAULT 'stringDefault', stringbuffer VARCHAR2(100) DEFAULT 'stringbufferDefault', characterobject CHAR(22) DEFAULT 'characterobjectDefault', booleanobject NUMBER(1) DEFAULT 1, byteobject NUMBER(3) DEFAULT 34, doubleobject FLOAT DEFAULT 234.87, floatobject FLOAT DEFAULT 834.43, integerobject NUMBER(10) DEFAULT 463, longobject NUMBER(19) DEFAULT 34876, shortobject NUMBER(5) DEFAULT 98, bigdecimal NUMERIC(19,9) DEFAULT 347.14, charcolumn CHAR(10) DEFAULT 'OSJFDZ', booleancolumn NUMBER(1) DEFAULT 0, bytecolumn NUMBER(3) DEFAULT 27, doublecolumn FLOAT DEFAULT 934.5, floatcolumn FLOAT DEFAULT 35.87, intcolumn NUMBER(10) DEFAULT 983734, longcolumn NUMBER(19) DEFAULT 2343345, shortcolumn NUMBER(5) DEFAULT 12)");
278: execute(query);
279: }
280:
281: public void testDefaultFunctionOracle() {
282: CreateTable query = new CreateTable(mOracle);
283: query.table("tablename1").column("intcolumn", int.class)
284: .defaultFunction("intcolumn", "6+1");
285: assertEquals(query.getSql(),
286: "CREATE TABLE tablename1 (intcolumn NUMBER(10) DEFAULT 6+1)");
287: execute(query);
288: }
289:
290: public void testCustomAttributeOracle() {
291: CreateTable query = new CreateTable(mOracle);
292: query.table("tablename1").column("intColumn", Integer.class)
293: .customAttribute("intColumn", "CHECK (intColumn > 0)");
294: assertEquals(query.getSql(),
295: "CREATE TABLE tablename1 (intColumn NUMBER(10) CHECK (intColumn > 0))");
296: execute(query);
297: }
298:
299: public void testTemporaryOracle() {
300: CreateTable query = new CreateTable(mOracle);
301: query.table("tablename").temporary(true).column("boolColumn",
302: boolean.class);
303: assertEquals(query.getSql(),
304: "CREATE GLOBAL TEMPORARY TABLE tablename (boolColumn NUMBER(1))");
305: execute(query);
306: }
307:
308: public void testPrimaryKeySimpleOracle() {
309: CreateTable query = new CreateTable(mOracle);
310: query.table("tablename").column("intColumn", int.class)
311: .primaryKey("intColumn");
312: assertEquals(
313: query.getSql(),
314: "CREATE TABLE tablename (intColumn NUMBER(10) NOT NULL, PRIMARY KEY (intColumn))");
315: execute(query);
316: }
317:
318: public void testPrimaryKeyMultipleOracle() {
319: CreateTable query = new CreateTable(mOracle);
320: query.table("tablename").column("intColumn", int.class).column(
321: "stringColumn", String.class, 50).primaryKey(
322: new String[] { "intColumn", "stringColumn" });
323: assertEquals(
324: query.getSql(),
325: "CREATE TABLE tablename (intColumn NUMBER(10) NOT NULL, stringColumn VARCHAR2(50) NOT NULL, PRIMARY KEY (intColumn, stringColumn))");
326: execute(query);
327: }
328:
329: public void testPrimaryKeyNamedOracle() {
330: CreateTable query = new CreateTable(mOracle);
331: query.table("tablename").column("intColumn", int.class)
332: .primaryKey("constraint_name", "intColumn");
333: assertEquals(
334: query.getSql(),
335: "CREATE TABLE tablename (intColumn NUMBER(10) NOT NULL, CONSTRAINT constraint_name PRIMARY KEY (intColumn))");
336: execute(query);
337: }
338:
339: public void testPrimaryKeyMultipleNamedOracle() {
340: CreateTable query = new CreateTable(mOracle);
341: query.table("tablename").column("intColumn", int.class).column(
342: "stringColumn", String.class, 50).primaryKey(
343: "constraint_name",
344: new String[] { "intColumn", "stringColumn" });
345: assertEquals(
346: query.getSql(),
347: "CREATE TABLE tablename (intColumn NUMBER(10) NOT NULL, stringColumn VARCHAR2(50) NOT NULL, CONSTRAINT constraint_name PRIMARY KEY (intColumn, stringColumn))");
348: execute(query);
349: }
350:
351: public void testUniqueSimpleOracle() {
352: CreateTable query = new CreateTable(mOracle);
353: query.table("tablename").column("intColumn", int.class).unique(
354: "intColumn");
355: assertEquals(query.getSql(),
356: "CREATE TABLE tablename (intColumn NUMBER(10), UNIQUE (intColumn))");
357: execute(query);
358: }
359:
360: public void testUniqueMultipleOracle() {
361: CreateTable query = new CreateTable(mOracle);
362: query.table("tablename").column("intColumn", int.class).column(
363: "stringColumn", String.class, 50).unique(
364: new String[] { "intColumn", "stringColumn" });
365: assertEquals(
366: query.getSql(),
367: "CREATE TABLE tablename (intColumn NUMBER(10), stringColumn VARCHAR2(50), UNIQUE (intColumn, stringColumn))");
368: execute(query);
369: }
370:
371: public void testUniqueNamedOracle() {
372: CreateTable query = new CreateTable(mOracle);
373: query.table("tablename").column("intColumn", int.class).unique(
374: "constraint_name", "intColumn");
375: assertEquals(
376: query.getSql(),
377: "CREATE TABLE tablename (intColumn NUMBER(10), CONSTRAINT constraint_name UNIQUE (intColumn))");
378: execute(query);
379: }
380:
381: public void testUniqueMultipleNamedOracle() {
382: CreateTable query = new CreateTable(mOracle);
383: query.table("tablename").column("intColumn", int.class).column(
384: "stringColumn", String.class, 50).unique(
385: "constraint_name",
386: new String[] { "intColumn", "stringColumn" });
387: assertEquals(
388: query.getSql(),
389: "CREATE TABLE tablename (intColumn NUMBER(10), stringColumn VARCHAR2(50), CONSTRAINT constraint_name UNIQUE (intColumn, stringColumn))");
390: execute(query);
391: }
392:
393: public void testForeignKeySimpleOracle() {
394: CreateTable query = new CreateTable(mOracle);
395: query.table("tablename").column("intColumn", int.class)
396: .foreignKey("foreigntable", "intColumn",
397: "foreignIntColumn");
398: assertEquals(
399: query.getSql(),
400: "CREATE TABLE tablename (intColumn NUMBER(10), FOREIGN KEY (intColumn) REFERENCES foreigntable (foreignIntColumn))");
401: execute(query);
402: }
403:
404: public void testForeignKeyMultipleOracle() {
405: CreateTable query = new CreateTable(mOracle);
406: query.table("tablename").column("intColumn", int.class).column(
407: "stringColumn", String.class, 50).foreignKey(
408: "foreigntable",
409: new String[] { "intColumn", "foreignIntColumn",
410: "stringColumn", "foreignStringColumn" });
411: assertEquals(
412: query.getSql(),
413: "CREATE TABLE tablename (intColumn NUMBER(10), stringColumn VARCHAR2(50), FOREIGN KEY (intColumn, stringColumn) REFERENCES foreigntable (foreignIntColumn, foreignStringColumn))");
414: execute(query);
415: }
416:
417: public void testForeignKeySimpleNamedOracle() {
418: CreateTable query = new CreateTable(mOracle);
419: query.table("tablename").column("intColumn", int.class)
420: .foreignKey("constraint_name", "foreigntable",
421: "intColumn", "foreignIntColumn");
422: assertEquals(
423: query.getSql(),
424: "CREATE TABLE tablename (intColumn NUMBER(10), CONSTRAINT constraint_name FOREIGN KEY (intColumn) REFERENCES foreigntable (foreignIntColumn))");
425: execute(query);
426: }
427:
428: public void testForeignKeyMultipleNamedOracle() {
429: CreateTable query = new CreateTable(mOracle);
430: query.table("tablename").column("intColumn", int.class).column(
431: "stringColumn", String.class, 50).foreignKey(
432: "constraint_name",
433: "foreigntable",
434: new String[] { "intColumn", "foreignIntColumn",
435: "stringColumn", "foreignStringColumn" });
436: assertEquals(
437: query.getSql(),
438: "CREATE TABLE tablename (intColumn NUMBER(10), stringColumn VARCHAR2(50), CONSTRAINT constraint_name FOREIGN KEY (intColumn, stringColumn) REFERENCES foreigntable (foreignIntColumn, foreignStringColumn))");
439: execute(query);
440: }
441:
442: public void testForeignKeyViolationsSingleOracle() {
443: CreateTable query = new CreateTable(mOracle);
444: query.table("tablename").column("intColumn", int.class)
445: .foreignKey("foreigntable", "intColumn",
446: "foreignIntColumn", CreateTable.CASCADE, null);
447: try {
448: query.getSql();
449: fail();
450: } catch (UnsupportedSqlFeatureException e) {
451: assertTrue(true);
452: }
453: query.clear();
454:
455: query.table("tablename").column("intColumn", int.class)
456: .foreignKey("foreigntable", "intColumn",
457: "foreignIntColumn", CreateTable.NOACTION, null);
458: try {
459: query.getSql();
460: fail();
461: } catch (UnsupportedSqlFeatureException e) {
462: assertTrue(true);
463: }
464: query.clear();
465:
466: query.table("tablename").column("intColumn", int.class)
467: .foreignKey("foreigntable", "intColumn",
468: "foreignIntColumn", CreateTable.RESTRICT, null);
469: try {
470: query.getSql();
471: fail();
472: } catch (UnsupportedSqlFeatureException e) {
473: assertTrue(true);
474: }
475: query.clear();
476:
477: query.table("tablename").column("intColumn", int.class)
478: .foreignKey("foreigntable", "intColumn",
479: "foreignIntColumn", CreateTable.SETDEFAULT,
480: null);
481: try {
482: query.getSql();
483: fail();
484: } catch (UnsupportedSqlFeatureException e) {
485: assertTrue(true);
486: }
487: query.clear();
488:
489: query.table("tablename").column("intColumn", int.class)
490: .foreignKey("foreigntable", "intColumn",
491: "foreignIntColumn", CreateTable.SETNULL, null);
492: try {
493: query.getSql();
494: fail();
495: } catch (UnsupportedSqlFeatureException e) {
496: assertTrue(true);
497: }
498: query.clear();
499:
500: query.table("tablename").column("intColumn", int.class)
501: .foreignKey("foreigntable", "intColumn",
502: "foreignIntColumn", null, CreateTable.CASCADE);
503: assertEquals(
504: query.getSql(),
505: "CREATE TABLE tablename (intColumn NUMBER(10), FOREIGN KEY (intColumn) REFERENCES foreigntable (foreignIntColumn) ON DELETE CASCADE)");
506: execute(query);
507: query.clear();
508:
509: query.table("tablename").column("intColumn", int.class)
510: .foreignKey("foreigntable", "intColumn",
511: "foreignIntColumn", null, CreateTable.NOACTION);
512: try {
513: query.getSql();
514: fail();
515: } catch (UnsupportedSqlFeatureException e) {
516: assertTrue(true);
517: }
518: query.clear();
519:
520: query.table("tablename").column("intColumn", int.class)
521: .foreignKey("foreigntable", "intColumn",
522: "foreignIntColumn", null, CreateTable.RESTRICT);
523: try {
524: query.getSql();
525: fail();
526: } catch (UnsupportedSqlFeatureException e) {
527: assertTrue(true);
528: }
529: query.clear();
530:
531: query.table("tablename").column("intColumn", int.class)
532: .foreignKey("foreigntable", "intColumn",
533: "foreignIntColumn", null,
534: CreateTable.SETDEFAULT);
535: try {
536: query.getSql();
537: fail();
538: } catch (UnsupportedSqlFeatureException e) {
539: assertTrue(true);
540: }
541: query.clear();
542:
543: query.table("tablename").column("intColumn", int.class)
544: .foreignKey("foreigntable", "intColumn",
545: "foreignIntColumn", null, CreateTable.SETNULL);
546: assertEquals(
547: query.getSql(),
548: "CREATE TABLE tablename (intColumn NUMBER(10), FOREIGN KEY (intColumn) REFERENCES foreigntable (foreignIntColumn) ON DELETE SET NULL)");
549: execute(query);
550: query.clear();
551: }
552:
553: public void testForeignKeyViolationsOracle() {
554: CreateTable query = new CreateTable(mOracle);
555: query.table("tablename").column("intColumn", int.class)
556: .foreignKey("foreigntable", "intColumn",
557: "foreignIntColumn", CreateTable.CASCADE,
558: CreateTable.NOACTION);
559: try {
560: query.getSql();
561: fail();
562: } catch (UnsupportedSqlFeatureException e) {
563: assertTrue(true);
564: }
565: }
566:
567: public void testForeignKeyMultipleViolationsOracle() {
568: CreateTable query = new CreateTable(mOracle);
569: query.table("tablename").column("intColumn", int.class).column(
570: "stringColumn", String.class, 50).foreignKey(
571: "foreigntable",
572: new String[] { "intColumn", "foreignIntColumn",
573: "stringColumn", "foreignStringColumn" },
574: CreateTable.RESTRICT, CreateTable.CASCADE);
575: try {
576: query.getSql();
577: fail();
578: } catch (UnsupportedSqlFeatureException e) {
579: assertTrue(true);
580: }
581: }
582:
583: public void testCheckSimpleOracle() {
584: CreateTable query = new CreateTable(mOracle);
585: query.table("tablename").column("intColumn", int.class).check(
586: "intColumn > 0");
587: assertEquals(query.getSql(),
588: "CREATE TABLE tablename (intColumn NUMBER(10), CHECK (intColumn > 0))");
589: execute(query);
590: }
591:
592: public void testCheckNamedOracle() {
593: CreateTable query = new CreateTable(mOracle);
594: query.table("tablename").column("intColumn", int.class).check(
595: "NAME_CK", "intColumn > 0");
596: assertEquals(
597: query.getSql(),
598: "CREATE TABLE tablename (intColumn NUMBER(10), CONSTRAINT NAME_CK CHECK (intColumn > 0))");
599: execute(query);
600: }
601:
602: public void testCloneOracle() {
603: CreateTable query = new CreateTable(mOracle);
604: query
605: .table("tablename")
606: .columns(BeanImpl.class)
607: .precision("propertyBigDecimal", 19, 9)
608: .precision("propertyBoolean", 4)
609: .precision("propertyByte", 8)
610: .precision("propertyCalendar", 20)
611: .precision("propertyChar", 10)
612: .precision("propertyDate", 7)
613: .precision("propertyDouble", 12, 3)
614: .precision("propertyFloat", 13, 2)
615: .precision("propertyInt", 10)
616: .precision("propertyLong", 12)
617: .precision("propertyShort", 9)
618: .precision("propertySqlDate", 8)
619: .precision("propertyString", 255)
620: .precision("propertyStringbuffer", 100)
621: .precision("propertyTime", 9)
622: .precision("propertyTimestamp", 30, 2)
623: .nullable("propertyString", CreateTable.NULL)
624: .nullable("propertyInt", CreateTable.NOTNULL)
625: .defaultValue("propertyStringbuffer", "stringDefault")
626: .defaultFunction("propertyLong", "6+1")
627: .customAttribute("propertyInt",
628: "CHECK (propertyInt > 0)")
629: .primaryKey(
630: "constraint_name1",
631: new String[] { "propertyInt", "propertyString" })
632: .unique(
633: "constraint_name2",
634: new String[] { "propertyLong", "propertyString" })
635: .foreignKey(
636: "foreigntable",
637: new String[] { "propertyInt",
638: "foreignIntColumn", "propertyString",
639: "foreignStringColumn" }, null,
640: CreateTable.CASCADE).check("NAME_CK",
641: "propertyInt > 0");
642: CreateTable query_clone = query.clone();
643: assertEquals(query.getSql(), query_clone.getSql());
644: assertTrue(query != query_clone);
645: execute(query_clone);
646: }
647: }
|