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