001: package liquibase.database.sql;
002:
003: import liquibase.database.Database;
004: import liquibase.database.HsqlDatabase;
005: import liquibase.database.structure.DatabaseSnapshot;
006: import liquibase.database.structure.ForeignKey;
007: import liquibase.database.structure.Table;
008: import liquibase.database.template.JdbcTemplate;
009: import liquibase.exception.JDBCException;
010: import liquibase.test.DatabaseTestTemplate;
011: import liquibase.test.SqlStatementDatabaseTest;
012: import liquibase.test.TestContext;
013: import static org.junit.Assert.*;
014: import org.junit.Test;
015:
016: public class CreateTableStatementTest extends AbstractSqlStatementTest {
017:
018: private static final String TABLE_NAME = "createTableStatementTest"
019: .toUpperCase();
020: private static final String FK_TABLE_NAME = "fk_table"
021: .toUpperCase();
022:
023: protected void setupDatabase(Database database) throws Exception {
024: dropTableIfExists(null, FK_TABLE_NAME, database);
025: dropTableIfExists(null, TABLE_NAME, database);
026: dropTableIfExists(TestContext.ALT_SCHEMA, TABLE_NAME, database);
027: }
028:
029: protected SqlStatement generateTestStatement() {
030: return new CreateTableStatement(null, null);
031: }
032:
033: @Test
034: public void createTable_standard() throws Exception {
035: new DatabaseTestTemplate()
036: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
037: null,
038: new CreateTableStatement(null, TABLE_NAME)
039: .addPrimaryKeyColumn("id", "int")
040: .addColumn("name", "varchar(255)")
041: .addColumn("username", "varchar(255)",
042: "'NEWUSER'")) {
043:
044: protected void preExecuteAssert(
045: DatabaseSnapshot snapshot) {
046: assertNull(snapshot.getTable(TABLE_NAME));
047: }
048:
049: protected void postExecuteAssert(
050: DatabaseSnapshot snapshot) {
051: Table table = snapshot.getTable(TABLE_NAME);
052: assertEquals(TABLE_NAME.toUpperCase(), table
053: .getName().toUpperCase());
054: assertNotNull(table.getColumn("id"));
055: assertNotNull(table.getColumn("name"));
056: assertNotNull(table.getColumn("username"));
057:
058: assertTrue(table.getColumn("id").isPrimaryKey());
059:
060: assertNull(table.getColumn("name")
061: .getDefaultValue());
062: assertTrue(table.getColumn("username")
063: .getDefaultValue().toString().indexOf(
064: "NEWUSER") >= 0);
065:
066: assertFalse(table.getColumn("id")
067: .isAutoIncrement());
068: }
069:
070: });
071: }
072:
073: @Test
074: public void createTable_autoincrementPK() throws Exception {
075: new DatabaseTestTemplate()
076: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
077: null,
078: new CreateTableStatement(null, TABLE_NAME)
079: .addPrimaryKeyColumn("id", "int")
080: .addColumn("name", "varchar(255)")
081: .addColumn("username", "varchar(255)",
082: "'NEWUSER'")
083: .addColumnConstraint(
084: new AutoIncrementConstraint(
085: "id"))) {
086:
087: protected boolean expectedException(
088: Database database, JDBCException exception) {
089: return !database.supportsAutoIncrement();
090: }
091:
092: protected void preExecuteAssert(
093: DatabaseSnapshot snapshot) {
094: assertNull(snapshot.getTable(TABLE_NAME));
095: }
096:
097: protected void postExecuteAssert(
098: DatabaseSnapshot snapshot) {
099: Table table = snapshot.getTable(TABLE_NAME);
100: assertEquals(TABLE_NAME.toUpperCase(), table
101: .getName().toUpperCase());
102: assertNotNull(table.getColumn("id"));
103: assertTrue(table.getColumn("id").isPrimaryKey());
104: assertTrue(table.getColumn("id")
105: .isAutoIncrement());
106: }
107: });
108: }
109:
110: @Test
111: public void createTable_foreignKeyColumn() throws Exception {
112: final String foreignKeyName = "fk_test_parent";
113: new DatabaseTestTemplate()
114: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
115: null,
116: new CreateTableStatement(null, TABLE_NAME)
117: .addPrimaryKeyColumn("id", "int")
118: .addColumn("name", "varchar(255)")
119: .addColumn(
120: "parent_id",
121: "int",
122: new ForeignKeyConstraint(
123: foreignKeyName,
124: TABLE_NAME + "(id)"))) {
125:
126: protected void preExecuteAssert(
127: DatabaseSnapshot snapshot) {
128: assertNull(snapshot.getTable(TABLE_NAME));
129: }
130:
131: protected void postExecuteAssert(
132: DatabaseSnapshot snapshot) {
133: Table table = snapshot.getTable(TABLE_NAME);
134: assertEquals(TABLE_NAME.toUpperCase(), table
135: .getName().toUpperCase());
136: assertNotNull(table.getColumn("id"));
137:
138: ForeignKey foundForeignKey = snapshot
139: .getForeignKey(foreignKeyName);
140: assertNotNull(foundForeignKey);
141: assertEquals(TABLE_NAME, foundForeignKey
142: .getPrimaryKeyTable().getName()
143: .toUpperCase());
144: assertEquals("ID", foundForeignKey
145: .getPrimaryKeyColumns().toUpperCase());
146: assertEquals(TABLE_NAME, foundForeignKey
147: .getForeignKeyTable().getName()
148: .toUpperCase());
149: assertEquals("PARENT_ID", foundForeignKey
150: .getForeignKeyColumns().toUpperCase());
151:
152: }
153:
154: });
155: }
156:
157: @Test
158: public void createTable_deferrableForeignKeyColumn()
159: throws Exception {
160: final String foreignKeyName = "fk_test_parent";
161:
162: new DatabaseTestTemplate()
163: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
164: null,
165: new CreateTableStatement(null, TABLE_NAME)
166: .addPrimaryKeyColumn("id", "int")
167: .addColumn("name", "varchar(255)")
168: .addColumn(
169: "parent_id",
170: "int",
171: new ForeignKeyConstraint(
172: foreignKeyName,
173: TABLE_NAME + "(id)")
174: .setDeferrable(true)
175: .setInitiallyDeferred(
176: true))) {
177:
178: protected boolean expectedException(
179: Database database, JDBCException exception) {
180: return !database
181: .supportsInitiallyDeferrableColumns();
182: }
183:
184: protected void preExecuteAssert(
185: DatabaseSnapshot snapshot) {
186: assertNull(snapshot.getTable(TABLE_NAME));
187: }
188:
189: protected void postExecuteAssert(
190: DatabaseSnapshot snapshot) {
191: Table table = snapshot.getTable(TABLE_NAME);
192: assertEquals(TABLE_NAME.toUpperCase(), table
193: .getName().toUpperCase());
194: assertNotNull(table.getColumn("id"));
195:
196: ForeignKey foundForeignKey = snapshot
197: .getForeignKey(foreignKeyName);
198: assertNotNull(foundForeignKey);
199: assertEquals(TABLE_NAME, foundForeignKey
200: .getPrimaryKeyTable().getName()
201: .toUpperCase());
202: assertEquals("ID", foundForeignKey
203: .getPrimaryKeyColumns().toUpperCase());
204: assertEquals(TABLE_NAME, foundForeignKey
205: .getForeignKeyTable().getName()
206: .toUpperCase());
207: assertEquals("PARENT_ID", foundForeignKey
208: .getForeignKeyColumns().toUpperCase());
209: assertTrue(foundForeignKey.isDeferrable());
210: assertTrue(foundForeignKey
211: .isInitiallyDeferred());
212: }
213:
214: });
215: }
216:
217: @Test
218: public void createTable_deleteCascadeForeignKeyColumn()
219: throws Exception {
220: final String foreignKeyName = "fk_test_parent";
221:
222: new DatabaseTestTemplate()
223: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
224: null,
225: new CreateTableStatement(null, TABLE_NAME)
226: .addPrimaryKeyColumn("id", "int")
227: .addColumn("name", "varchar(255)")
228: .addColumn(
229: "parent_id",
230: "int",
231: new ForeignKeyConstraint(
232: foreignKeyName,
233: FK_TABLE_NAME + "(id)")
234: .setDeleteCascade(true))) {
235:
236: protected void setup(Database database)
237: throws JDBCException {
238: new JdbcTemplate(database)
239: .execute(new CreateTableStatement(null,
240: FK_TABLE_NAME)
241: .addPrimaryKeyColumn("id",
242: "int").addColumn(
243: "name", "varchar(255)"));
244: super .setup(database);
245: }
246:
247: protected void preExecuteAssert(
248: DatabaseSnapshot snapshot) {
249: assertNull(snapshot.getTable(TABLE_NAME));
250: }
251:
252: protected void postExecuteAssert(
253: DatabaseSnapshot snapshot) {
254: Table table = snapshot.getTable(TABLE_NAME);
255: assertEquals(TABLE_NAME.toUpperCase(), table
256: .getName().toUpperCase());
257: assertNotNull(table.getColumn("id"));
258:
259: ForeignKey foundForeignKey = snapshot
260: .getForeignKey(foreignKeyName);
261: assertNotNull(foundForeignKey);
262: assertEquals(FK_TABLE_NAME, foundForeignKey
263: .getPrimaryKeyTable().getName()
264: .toUpperCase());
265: assertEquals("ID", foundForeignKey
266: .getPrimaryKeyColumns().toUpperCase());
267: assertEquals(TABLE_NAME, foundForeignKey
268: .getForeignKeyTable().getName()
269: .toUpperCase());
270: assertEquals("PARENT_ID", foundForeignKey
271: .getForeignKeyColumns().toUpperCase());
272: //TODO: test when tested by diff assertTrue(foundForeignKey.isDeleteCascade());
273: }
274:
275: });
276: }
277:
278: @Test
279: public void createTable_uniqueColumn() throws Exception {
280: new DatabaseTestTemplate()
281: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
282: null,
283: new CreateTableStatement(null, TABLE_NAME)
284: .addPrimaryKeyColumn("id", "int")
285: .addColumn("name", "varchar(255)")
286: .addColumn(
287: "username",
288: "int",
289: new UniqueConstraint(
290: "UQ_TESTCT_ID"),
291: new NotNullConstraint())) {
292:
293: protected boolean expectedException(
294: Database database) {
295: return !(database instanceof HsqlDatabase);
296: }
297:
298: protected void preExecuteAssert(
299: DatabaseSnapshot snapshot) {
300: assertNull(snapshot.getTable(TABLE_NAME));
301: }
302:
303: protected void postExecuteAssert(
304: DatabaseSnapshot snapshot) {
305: Table table = snapshot.getTable(TABLE_NAME);
306: assertEquals(TABLE_NAME.toUpperCase(), table
307: .getName().toUpperCase());
308: assertNotNull(table.getColumn("id"));
309:
310: //todo: actually test for uniqueness when diff can check for it assertTrue(table.getColumn("username").isUnique());
311: }
312:
313: });
314: }
315:
316: @Test
317: public void addPrimaryKeyColumn_oneColumn() {
318: CreateTableStatement statement = new CreateTableStatement(null,
319: "tableName");
320: statement.addPrimaryKeyColumn("id", "int");
321:
322: assertEquals(1, statement.getPrimaryKeyConstraint()
323: .getColumns().size());
324: }
325:
326: @Test
327: public void addPrimaryKeyColumn_multiColumn() {
328: CreateTableStatement statement = new CreateTableStatement(null,
329: "tableName");
330: statement.addPrimaryKeyColumn("id1", "int");
331: statement.addPrimaryKeyColumn("id2", "int");
332:
333: assertEquals(2, statement.getPrimaryKeyConstraint()
334: .getColumns().size());
335: }
336:
337: @Test
338: public void addColumnConstraint_notNullConstraint() {
339: CreateTableStatement statement = new CreateTableStatement(null,
340: "tableName");
341: statement.addColumn("id", "int");
342:
343: assertFalse(statement.getNotNullColumns().contains("id"));
344:
345: statement.addColumnConstraint(new NotNullConstraint("id"));
346:
347: assertTrue(statement.getNotNullColumns().contains("id"));
348: }
349:
350: @Test
351: public void addColumnConstraint_ForeignKeyConstraint() {
352: CreateTableStatement statement = new CreateTableStatement(null,
353: "tableName");
354: statement.addColumn("id", "int");
355:
356: assertEquals(0, statement.getForeignKeyConstraints().size());
357:
358: statement.addColumnConstraint(new ForeignKeyConstraint(
359: "fk_test", "fkTable(id)").setColumn("id"));
360:
361: assertEquals(1, statement.getForeignKeyConstraints().size());
362: assertEquals("fk_test", statement.getForeignKeyConstraints()
363: .iterator().next().getForeignKeyName());
364: }
365:
366: @Test
367: public void addColumnConstraint_UniqueConstraint() {
368: CreateTableStatement statement = new CreateTableStatement(null,
369: "tableName");
370: statement.addColumn("id", "int");
371:
372: assertEquals(0, statement.getUniqueConstraints().size());
373:
374: statement.addColumnConstraint(new UniqueConstraint("uq_test")
375: .addColumns("id"));
376:
377: assertEquals(1, statement.getUniqueConstraints().size());
378: assertEquals("uq_test", statement.getUniqueConstraints()
379: .iterator().next().getConstraintName());
380: }
381:
382: @Test
383: public void createTable_tablespace() throws Exception {
384: new DatabaseTestTemplate()
385: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
386: null,
387: new CreateTableStatement(null, TABLE_NAME)
388: .addPrimaryKeyColumn("id", "int")
389: .addColumn("name", "varchar(255)")
390: .addColumn("username", "varchar(255)",
391: "'NEWUSER'").setTablespace(
392: "liquibase2")) {
393:
394: protected boolean expectedException(
395: Database database, JDBCException exception) {
396: return !database.supportsTablespaces();
397: }
398:
399: protected void preExecuteAssert(
400: DatabaseSnapshot snapshot) {
401: assertNull(snapshot.getTable(TABLE_NAME));
402: }
403:
404: protected void postExecuteAssert(
405: DatabaseSnapshot snapshot) {
406: Table table = snapshot.getTable(TABLE_NAME);
407: assertEquals(TABLE_NAME.toUpperCase(), table
408: .getName().toUpperCase());
409:
410: //todo: test that tablespace is correct when diff returns it
411: }
412: });
413: }
414:
415: @Test
416: public void createTable_altSchema() throws Exception {
417: new DatabaseTestTemplate()
418: .testOnAvailableDatabases(new SqlStatementDatabaseTest(
419: TestContext.ALT_SCHEMA,
420: new CreateTableStatement(
421: TestContext.ALT_SCHEMA, TABLE_NAME)
422: .addPrimaryKeyColumn("id", "int")
423: .addColumn("name", "varchar(255)")
424: .addColumn("username", "varchar(255)",
425: "'NEWUSER'")) {
426:
427: protected void preExecuteAssert(
428: DatabaseSnapshot snapshot) {
429: assertNull(snapshot.getTable(TABLE_NAME));
430: }
431:
432: protected void postExecuteAssert(
433: DatabaseSnapshot snapshot) {
434: Table table = snapshot.getTable(TABLE_NAME);
435: assertNotNull(table);
436: assertEquals(TABLE_NAME.toUpperCase(), table
437: .getName().toUpperCase());
438: }
439: });
440: }
441: }
|