001: /*
002: * Copyright 2006-2007, Unitils.org
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016: package org.unitils.dbmaintainer.clean.impl;
017:
018: import org.apache.commons.logging.Log;
019: import org.apache.commons.logging.LogFactory;
020: import org.hsqldb.Trigger;
021: import org.junit.After;
022: import static org.junit.Assert.*;
023: import org.junit.Before;
024: import org.junit.Test;
025: import org.unitils.UnitilsJUnit4;
026: import org.unitils.core.ConfigurationLoader;
027: import org.unitils.core.dbsupport.DbSupport;
028: import static org.unitils.core.dbsupport.DbSupportFactory.getDefaultDbSupport;
029: import org.unitils.core.dbsupport.SQLHandler;
030: import static org.unitils.core.util.SQLTestUtils.*;
031: import static org.unitils.database.SQLUnitils.executeUpdate;
032: import org.unitils.database.annotations.TestDataSource;
033: import org.unitils.dbmaintainer.clean.DBClearer;
034: import static org.unitils.dbmaintainer.clean.impl.DefaultDBClearer.PROPKEY_VERSION_TABLE_NAME;
035:
036: import javax.sql.DataSource;
037: import java.util.Properties;
038:
039: /**
040: * Test class for the {@link DBClearer}.
041: *
042: * @author Filip Neven
043: * @author Tim Ducheyne
044: * @author Scott Prater
045: */
046: public class DefaultDBClearerTest extends UnitilsJUnit4 {
047:
048: /* The logger instance for this class */
049: private static Log logger = LogFactory
050: .getLog(DefaultDBClearerTest.class);
051:
052: /* DataSource for the test database, is injected */
053: @TestDataSource
054: private DataSource dataSource = null;
055:
056: /* Tested object */
057: private DefaultDBClearer defaultDbClearer;
058:
059: /* The DbSupport object */
060: private DbSupport dbSupport;
061:
062: /* The name of the version tabel */
063: private String versionTableName;
064:
065: /**
066: * Configures the tested object. Creates a test table, index, view and sequence
067: */
068: @Before
069: public void setUp() throws Exception {
070: Properties configuration = new ConfigurationLoader()
071: .loadConfiguration();
072: SQLHandler sqlHandler = new SQLHandler(dataSource);
073: dbSupport = getDefaultDbSupport(configuration, sqlHandler);
074: // create clearer instance
075: defaultDbClearer = new DefaultDBClearer();
076: defaultDbClearer.init(configuration, sqlHandler);
077: versionTableName = configuration
078: .getProperty(PROPKEY_VERSION_TABLE_NAME);
079:
080: cleanupTestDatabase();
081: createTestDatabase();
082: }
083:
084: /**
085: * Removes all test tables.
086: */
087: @After
088: public void tearDown() throws Exception {
089: cleanupTestDatabase();
090: }
091:
092: /**
093: * Checks if the tables are correctly dropped.
094: */
095: @Test
096: public void testClearDatabase_tables() throws Exception {
097: assertEquals(2, dbSupport.getTableNames().size());
098: defaultDbClearer.clearSchemas();
099: assertTrue(dbSupport.getTableNames().isEmpty());
100: }
101:
102: /**
103: * Checks if the db version table is preserved.
104: */
105: @Test
106: public void testClearDatabase_dbVersionTables() throws Exception {
107: executeUpdate("create table " + versionTableName
108: + "(testcolumn varchar(10))", dataSource);
109: assertEquals(3, dbSupport.getTableNames().size());
110: defaultDbClearer.clearSchemas();
111: assertEquals(1, dbSupport.getTableNames().size()); // version table
112: }
113:
114: /**
115: * Checks if the views are correctly dropped
116: */
117: @Test
118: public void testClearDatabase_views() throws Exception {
119: assertEquals(2, dbSupport.getViewNames().size());
120: defaultDbClearer.clearSchemas();
121: assertTrue(dbSupport.getViewNames().isEmpty());
122: }
123:
124: /**
125: * Checks if the synonyms are correctly dropped
126: */
127: @Test
128: public void testClearDatabase_synonyms() throws Exception {
129: if (!dbSupport.supportsSynonyms()) {
130: logger
131: .warn("Current dialect does not support synonyms. Skipping test.");
132: return;
133: }
134: assertEquals(2, dbSupport.getSynonymNames().size());
135: defaultDbClearer.clearSchemas();
136: assertTrue(dbSupport.getSynonymNames().isEmpty());
137: }
138:
139: /**
140: * Tests if the triggers are correctly dropped
141: */
142: @Test
143: public void testClearDatabase_sequences() throws Exception {
144: if (!dbSupport.supportsSequences()) {
145: logger
146: .warn("Current dialect does not support sequences. Skipping test.");
147: return;
148: }
149: assertEquals(2, dbSupport.getSequenceNames().size());
150: defaultDbClearer.clearSchemas();
151: assertTrue(dbSupport.getSequenceNames().isEmpty());
152: }
153:
154: /**
155: * Creates all test database structures (view, tables...)
156: */
157: private void createTestDatabase() throws Exception {
158: String dialect = dbSupport.getDatabaseDialect();
159: if ("hsqldb".equals(dialect)) {
160: createTestDatabaseHsqlDb();
161: } else if ("mysql".equals(dialect)) {
162: createTestDatabaseMySql();
163: } else if ("oracle".equals(dialect)) {
164: createTestDatabaseOracle();
165: } else if ("postgresql".equals(dialect)) {
166: createTestDatabasePostgreSql();
167: } else if ("db2".equals(dialect)) {
168: createTestDatabaseDb2();
169: } else if ("derby".equals(dialect)) {
170: createTestDatabaseDerby();
171: } else {
172: fail("This test is not implemented for current dialect: "
173: + dialect);
174: }
175: }
176:
177: /**
178: * Drops all created test database structures (views, tables...)
179: */
180: private void cleanupTestDatabase() throws Exception {
181: String dialect = dbSupport.getDatabaseDialect();
182: if ("hsqldb".equals(dialect)) {
183: cleanupTestDatabaseHsqlDb();
184: } else if ("mysql".equals(dialect)) {
185: cleanupTestDatabaseMySql();
186: } else if ("oracle".equals(dialect)) {
187: cleanupTestDatabaseOracle();
188: } else if ("postgresql".equals(dialect)) {
189: cleanupTestDatabasePostgreSql();
190: } else if ("db2".equals(dialect)) {
191: cleanupTestDatabaseDb2();
192: } else if ("derby".equals(dialect)) {
193: cleanupTestDatabaseDerby();
194: }
195: }
196:
197: //
198: // Database setup for HsqlDb
199: //
200:
201: /**
202: * Creates all test database structures (view, tables...)
203: */
204: private void createTestDatabaseHsqlDb() throws Exception {
205: // create tables
206: executeUpdate(
207: "create table test_table (col1 int not null identity, col2 varchar(12) not null)",
208: dataSource);
209: executeUpdate(
210: "create table \"Test_CASE_Table\" (col1 int, foreign key (col1) references test_table(col1))",
211: dataSource);
212: // create views
213: executeUpdate(
214: "create view test_view as select col1 from test_table",
215: dataSource);
216: executeUpdate(
217: "create view \"Test_CASE_View\" as select col1 from \"Test_CASE_Table\"",
218: dataSource);
219: // create sequences
220: executeUpdate("create sequence test_sequence", dataSource);
221: executeUpdate("create sequence \"Test_CASE_Sequence\"",
222: dataSource);
223: // create triggers
224: // todo move to code clearer test
225: executeUpdate(
226: "create trigger test_trigger before insert on \"Test_CASE_Table\" call \"org.unitils.core.dbsupport.HsqldbDbSupportTest.TestTrigger\"",
227: dataSource);
228: executeUpdate(
229: "create trigger \"Test_CASE_Trigger\" before insert on \"Test_CASE_Table\" call \"org.unitils.core.dbsupport.HsqldbDbSupportTest.TestTrigger\"",
230: dataSource);
231: }
232:
233: /**
234: * Drops all created test database structures (views, tables...)
235: */
236: private void cleanupTestDatabaseHsqlDb() throws Exception {
237: dropTestTables(dbSupport, "test_table", "\"Test_CASE_Table\"",
238: versionTableName);
239: dropTestViews(dbSupport, "test_view", "\"Test_CASE_View\"");
240: dropTestSequences(dbSupport, "test_sequence",
241: "\"Test_CASE_Sequence\"");
242: dropTestTriggers(dbSupport, "test_trigger",
243: "\"Test_CASE_Trigger\"");
244: }
245:
246: /**
247: * Test trigger for hypersonic.
248: *
249: * @author Filip Neven
250: * @author Tim Ducheyne
251: */
252: public static class TestTrigger implements Trigger {
253:
254: public void fire(int i, String string, String string1,
255: Object[] objects, Object[] objects1) {
256: }
257: }
258:
259: //
260: // Database setup for MySql
261: //
262:
263: /**
264: * Creates all test database structures (view, tables...) <p/> NO FOREIGN KEY USED: drop cascade does not work in
265: * MySQL
266: */
267: private void createTestDatabaseMySql() throws Exception {
268: // create tables
269: executeUpdate(
270: "create table test_table (col1 int not null primary key AUTO_INCREMENT, col2 varchar(12) not null)",
271: dataSource);
272: executeUpdate("create table `Test_CASE_Table` (col1 int)",
273: dataSource);
274: // create views
275: executeUpdate(
276: "create view test_view as select col1 from test_table",
277: dataSource);
278: executeUpdate(
279: "create view `Test_CASE_View` as select col1 from `Test_CASE_Table`",
280: dataSource);
281: // create triggers
282: executeUpdate(
283: "create trigger test_trigger before insert on `Test_CASE_Table` FOR EACH ROW begin end",
284: dataSource);
285: executeUpdate(
286: "create trigger `Test_CASE_Trigger` after insert on `Test_CASE_Table` FOR EACH ROW begin end",
287: dataSource);
288: }
289:
290: /**
291: * Drops all created test database structures (views, tables...)
292: */
293: private void cleanupTestDatabaseMySql() throws Exception {
294: dropTestTables(dbSupport, "test_table", "`Test_CASE_Table`",
295: versionTableName);
296: dropTestViews(dbSupport, "test_view", "`Test_CASE_View`");
297: dropTestTriggers(dbSupport, "test_trigger",
298: "`Test_CASE_Trigger`");
299: }
300:
301: //
302: // Database setup for Oracle
303: //
304:
305: /**
306: * Creates all test database structures (view, tables...)
307: */
308: private void createTestDatabaseOracle() throws Exception {
309: // create tables
310: executeUpdate(
311: "create table test_table (col1 varchar(10) not null primary key, col2 varchar(12) not null)",
312: dataSource);
313: executeUpdate(
314: "create table \"Test_CASE_Table\" (col1 varchar(10), foreign key (col1) references test_table(col1))",
315: dataSource);
316: // create views
317: executeUpdate(
318: "create view test_view as select col1 from test_table",
319: dataSource);
320: executeUpdate(
321: "create view \"Test_CASE_View\" as select col1 from \"Test_CASE_Table\"",
322: dataSource);
323: // create synonyms
324: executeUpdate("create synonym test_synonym for test_table",
325: dataSource);
326: executeUpdate(
327: "create synonym \"Test_CASE_Synonym\" for \"Test_CASE_Table\"",
328: dataSource);
329: // create sequences
330: executeUpdate("create sequence test_sequence", dataSource);
331: executeUpdate("create sequence \"Test_CASE_Sequence\"",
332: dataSource);
333: // create triggers
334: executeUpdate(
335: "create or replace trigger test_trigger before insert on \"Test_CASE_Table\" begin dbms_output.put_line('test'); end test_trigger",
336: dataSource);
337: executeUpdate(
338: "create or replace trigger \"Test_CASE_Trigger\" before insert on \"Test_CASE_Table\" begin dbms_output.put_line('test'); end \"Test_CASE_Trigger\"",
339: dataSource);
340: // create types
341: executeUpdate("create type test_type AS (col1 int)", dataSource);
342: executeUpdate("create type \"Test_CASE_Type\" AS (col1 int)",
343: dataSource);
344: }
345:
346: /**
347: * Drops all created test database structures (views, tables...)
348: */
349: private void cleanupTestDatabaseOracle() throws Exception {
350: dropTestTables(dbSupport, "test_table", "\"Test_CASE_Table\"",
351: versionTableName);
352: dropTestViews(dbSupport, "test_view", "\"Test_CASE_View\"");
353: dropTestSynonyms(dbSupport, "test_synonym",
354: "\"Test_CASE_Synonym\"");
355: dropTestSequences(dbSupport, "test_sequence",
356: "\"Test_CASE_Sequence\"");
357: dropTestTriggers(dbSupport, "test_trigger",
358: "\"Test_CASE_Trigger\"");
359: dropTestTypes(dbSupport, "test_type", "\"Test_CASE_Type\"");
360: }
361:
362: //
363: // Database setup for PostgreSql
364: //
365:
366: /**
367: * Creates all test database structures (view, tables...)
368: */
369: private void createTestDatabasePostgreSql() throws Exception {
370: // create tables
371: executeUpdate(
372: "create table test_table (col1 varchar(10) not null primary key, col2 varchar(12) not null)",
373: dataSource);
374: executeUpdate(
375: "create table \"Test_CASE_Table\" (col1 varchar(10), foreign key (col1) references test_table(col1))",
376: dataSource);
377: // create views
378: executeUpdate(
379: "create view test_view as select col1 from test_table",
380: dataSource);
381: executeUpdate(
382: "create view \"Test_CASE_View\" as select col1 from \"Test_CASE_Table\"",
383: dataSource);
384: // create sequences
385: executeUpdate("create sequence test_sequence", dataSource);
386: executeUpdate("create sequence \"Test_CASE_Sequence\"",
387: dataSource);
388: // create triggers
389: try {
390: executeUpdate("create language plpgsql", dataSource);
391: } catch (Exception e) {
392: // ignore language already exists
393: }
394: executeUpdate(
395: "create or replace function test() returns trigger as $$ declare begin end; $$ language plpgsql",
396: dataSource);
397: executeUpdate(
398: "create trigger test_trigger before insert on \"Test_CASE_Table\" FOR EACH ROW EXECUTE PROCEDURE test()",
399: dataSource);
400: executeUpdate(
401: "create trigger \"Test_CASE_Trigger\" before insert on \"Test_CASE_Table\" FOR EACH ROW EXECUTE PROCEDURE test()",
402: dataSource);
403: // create types
404: executeUpdate("create type test_type AS (col1 int)", dataSource);
405: executeUpdate("create type \"Test_CASE_Type\" AS (col1 int)",
406: dataSource);
407: }
408:
409: /**
410: * Drops all created test database structures (views, tables...)
411: */
412: private void cleanupTestDatabasePostgreSql() throws Exception {
413: dropTestTables(dbSupport, "test_table", "\"Test_CASE_Table\"",
414: versionTableName);
415: dropTestViews(dbSupport, "test_view", "\"Test_CASE_View\"");
416: dropTestSequences(dbSupport, "test_sequence",
417: "\"Test_CASE_Sequence\"");
418: dropTestTriggers(dbSupport, "test_trigger",
419: "\"Test_CASE_Trigger\"");
420: dropTestTypes(dbSupport, "test_type", "\"Test_CASE_Type\"");
421: }
422:
423: //
424: // Database setup for Db2
425: //
426:
427: /**
428: * Creates all test database structures (view, tables...)
429: */
430: private void createTestDatabaseDb2() throws Exception {
431: // create tables
432: executeUpdate(
433: "create table test_table (col1 int not null primary key generated by default as identity, col2 varchar(12) not null)",
434: dataSource);
435: executeUpdate(
436: "create table \"Test_CASE_Table\" (col1 int, foreign key (col1) references test_table(col1))",
437: dataSource);
438: // create views
439: executeUpdate(
440: "create view test_view as select col1 from test_table",
441: dataSource);
442: executeUpdate(
443: "create view \"Test_CASE_View\" as select col1 from \"Test_CASE_Table\"",
444: dataSource);
445: // create sequences
446: executeUpdate("create sequence test_sequence", dataSource);
447: executeUpdate("create sequence \"Test_CASE_Sequence\"",
448: dataSource);
449: // create triggers
450: executeUpdate(
451: "create trigger test_trigger before insert on \"Test_CASE_Table\" FOR EACH ROW when (1 < 0) SIGNAL SQLSTATE '0'",
452: dataSource);
453: executeUpdate(
454: "create trigger \"Test_CASE_Trigger\" before insert on \"Test_CASE_Table\" FOR EACH ROW when (1 < 0) SIGNAL SQLSTATE '0'",
455: dataSource);
456: // create types
457: executeUpdate(
458: "create type test_type AS (col1 int) MODE DB2SQL",
459: dataSource);
460: executeUpdate(
461: "create type \"Test_CASE_Type\" AS (col1 int) MODE DB2SQL",
462: dataSource);
463: }
464:
465: /**
466: * Drops all created test database structures (views, tables...)
467: */
468: private void cleanupTestDatabaseDb2() throws Exception {
469: dropTestTables(dbSupport, "test_table", "\"Test_CASE_Table\"",
470: versionTableName);
471: dropTestViews(dbSupport, "test_view", "\"Test_CASE_View\"");
472: dropTestSynonyms(dbSupport, "test_synonym",
473: "\"Test_CASE_Synonym\"");
474: dropTestSequences(dbSupport, "test_sequence",
475: "\"Test_CASE_Sequence\"");
476: dropTestTriggers(dbSupport, "test_trigger",
477: "\"Test_CASE_Trigger\"");
478: dropTestTypes(dbSupport, "test_type", "\"Test_CASE_Type\"");
479: }
480:
481: //
482: // Database setup for Derby
483: //
484:
485: /**
486: * Creates all test database structures (view, tables...)
487: */
488: private void createTestDatabaseDerby() throws Exception {
489: // create tables
490: executeUpdate(
491: "create table \"TEST_TABLE\" (col1 int not null primary key generated by default as identity, col2 varchar(12) not null)",
492: dataSource);
493: executeUpdate(
494: "create table \"Test_CASE_Table\" (col1 int, foreign key (col1) references test_table(col1))",
495: dataSource);
496: // create views
497: executeUpdate(
498: "create view test_view as select col1 from test_table",
499: dataSource);
500: executeUpdate(
501: "create view \"Test_CASE_View\" as select col1 from \"Test_CASE_Table\"",
502: dataSource);
503: // create synonyms
504: executeUpdate("create synonym test_synonym for test_table",
505: dataSource);
506: executeUpdate(
507: "create synonym \"Test_CASE_Synonym\" for \"Test_CASE_Table\"",
508: dataSource);
509: // create triggers
510: executeUpdate(
511: "call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('testKey', 'test')",
512: dataSource);
513: executeUpdate(
514: "create trigger test_trigger no cascade before insert on \"Test_CASE_Table\" FOR EACH ROW MODE DB2SQL VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('testKey')",
515: dataSource);
516: executeUpdate(
517: "create trigger \"Test_CASE_Trigger\" no cascade before insert on \"Test_CASE_Table\" FOR EACH ROW MODE DB2SQL VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('testKey')",
518: dataSource);
519: }
520:
521: /**
522: * Drops all created test database structures (views, tables...) First drop the views, since Derby doesn't support
523: * "drop table ... cascade" (yet, as of Derby 10.3)
524: */
525: private void cleanupTestDatabaseDerby() throws Exception {
526: dropTestSynonyms(dbSupport, "test_synonym",
527: "\"Test_CASE_Synonym\"");
528: dropTestViews(dbSupport, "test_view", "\"Test_CASE_View\"");
529: dropTestTriggers(dbSupport, "test_trigger",
530: "\"Test_CASE_Trigger\"");
531: dropTestTables(dbSupport, "\"Test_CASE_Table\"", "TEST_TABLE",
532: versionTableName);
533: }
534: }
|