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