001: /*
002: * SchemaDiffTest.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.diff;
013:
014: import java.sql.SQLException;
015: import java.sql.Statement;
016: import workbench.TestUtil;
017: import workbench.db.WbConnection;
018: import workbench.util.SqlUtil;
019:
020: /**
021: *
022: * @author support@sql-workbench.net
023: */
024: public class SchemaDiffTest extends junit.framework.TestCase {
025:
026: private WbConnection source;
027: private WbConnection target;
028:
029: public SchemaDiffTest(String testName) {
030: super (testName);
031: }
032:
033: public void tearDown() {
034: try {
035: source.disconnect();
036: } catch (Throwable th) {
037: }
038: try {
039: target.disconnect();
040: } catch (Throwable th) {
041: }
042: }
043:
044: public void testBaseDiff() {
045: try {
046: setupBaseDatabase();
047: SchemaDiff diff = new SchemaDiff(source, target);
048: diff.setIncludeForeignKeys(true);
049: diff.setIncludePrimaryKeys(true);
050: diff.setIncludeProcedures(false);
051: diff.setIncludeTableGrants(false);
052: diff.setIncludeTableConstraints(true);
053: diff.setIncludeSequences(true);
054: diff.setIncludeViews(true);
055: diff.compareAll();
056: String xml = diff.getMigrateTargetXml();
057: // Thread.yield();
058: // System.out.println("---------------");
059: // System.out.println(xml);
060: // System.out.println("---------------");
061:
062: String count = TestUtil.getXPathValue(xml,
063: "count(/schema-diff/compare-settings/table-info)");
064: assertEquals("Incorrect number of tables listed", "3",
065: count);
066:
067: count = TestUtil
068: .getXPathValue(xml,
069: "count(/schema-diff/compare-settings/view-info[@compareTo='V_PERSON'])");
070: assertEquals("Incorrect number of views listed", "1", count);
071:
072: // Check if email column
073: String col = TestUtil
074: .getXPathValue(
075: xml,
076: "/schema-diff/modify-table[@name='ADDRESS']/add-column/column-def[@name='EMAIL']/column-name");
077: assertNotNull("Table ADDRESS not changed", col);
078: assertEquals("Table ADDRESS not changed", "EMAIL", col);
079:
080: count = TestUtil
081: .getXPathValue(xml,
082: "count(/schema-diff/modify-table[@name='ADDRESS']/add-column)");
083: assertEquals(
084: "Incorrect number of columns to add to ADDRESS",
085: "1", count);
086:
087: count = TestUtil
088: .getXPathValue(
089: xml,
090: "count(/schema-diff/modify-table[@name='ADDRESS']/remove-column[@name='REMARK'])");
091: assertEquals("Remark column not removed", "1", count);
092:
093: String value = TestUtil
094: .getXPathValue(
095: xml,
096: "/schema-diff/modify-table[@name='ADDRESS']/modify-column[@name='STREET']/dbms-data-type");
097: assertEquals("Street column not changed", "VARCHAR(50)",
098: value);
099:
100: value = TestUtil
101: .getXPathValue(
102: xml,
103: "/schema-diff/modify-table[@name='PERSON']/modify-column[@name='FIRSTNAME']/dbms-data-type");
104: assertEquals("Firstname column not changed",
105: "VARCHAR(100)", value);
106:
107: value = TestUtil
108: .getXPathValue(
109: xml,
110: "/schema-diff/modify-table[@name='PERSON_ADDRESS']/modify-column[@name='ADDRESS_ID']/add-reference/table-name");
111: assertEquals("FK to address not added", "ADDRESS", value);
112:
113: value = TestUtil
114: .getXPathValue(
115: xml,
116: "/schema-diff/modify-table[@name='PERSON_ADDRESS']/add-index/index-def/index-expression");
117: assertEquals("Index for address_id not added",
118: "ADDRESS_ID A", value);
119:
120: value = TestUtil
121: .getXPathValue(xml,
122: "/schema-diff/create-view/view-def[@name='V_PERSON']/view-name");
123: assertEquals("View not created ", "V_PERSON", value);
124:
125: value = TestUtil.getXPathValue(xml,
126: "/schema-diff/drop-view/view-name[1]");
127: assertEquals("View not dropped ", "SOMETHING", value);
128:
129: value = TestUtil
130: .getXPathValue(xml,
131: "/schema-diff/update-sequence[1]/sequence-def/sequence-name");
132: assertEquals("Sequence not updated", "SEQ_TWO", value);
133:
134: value = TestUtil
135: .getXPathValue(xml,
136: "/schema-diff/create-sequence[1]/sequence-def/sequence-name");
137: assertEquals("Sequence not created", "SEQ_THREE", value);
138:
139: value = TestUtil.getXPathValue(xml,
140: "/schema-diff/drop-sequence/sequence-name[1]");
141: assertEquals("Sequence not dropped", "SEQ_TO_BE_DELETED",
142: value);
143: } catch (Exception e) {
144: e.printStackTrace();
145: fail(e.getMessage());
146: }
147:
148: }
149:
150: public void testGrantDiff() {
151: try {
152: setupGrantTestDb();
153: SchemaDiff diff = new SchemaDiff(source, target);
154: diff.setIncludeForeignKeys(false);
155: diff.setIncludeIndex(false);
156: diff.setIncludePrimaryKeys(false);
157: diff.setIncludeProcedures(false);
158: diff.setIncludeTableGrants(true);
159: diff.setIncludeTableConstraints(false);
160: diff.setIncludeViews(false);
161: diff.compareAll();
162: String xml = diff.getMigrateTargetXml();
163:
164: String value = TestUtil
165: .getXPathValue(xml,
166: "/schema-diff/modify-table[@name='PERSON']/add-grants/grant[1]/grantee");
167: assertEquals("Grantee not correct", "UNIT_TEST", value);
168:
169: value = TestUtil
170: .getXPathValue(xml,
171: "/schema-diff/modify-table[@name='PERSON']/add-grants/grant[1]/privilege");
172: assertEquals("Privilege not correct", "SELECT", value);
173:
174: value = TestUtil
175: .getXPathValue(xml,
176: "/schema-diff/modify-table[@name='PERSON']/revoke-grants/grant[1]/privilege");
177: assertEquals("DELETE not revoked", "DELETE", value);
178:
179: } catch (Exception e) {
180: e.printStackTrace();
181: fail(e.getMessage());
182: }
183: }
184:
185: /**
186: * Check if an index change is detected even though nothing else has changed
187: */
188: public void testIndexChangeOnly() {
189: try {
190: setupIndexDiffTestDb();
191: SchemaDiff diff = new SchemaDiff(source, target);
192: diff.setIncludeForeignKeys(true);
193: diff.setIncludeIndex(true);
194: diff.setIncludePrimaryKeys(true);
195: diff.setIncludeProcedures(false);
196: diff.setIncludeTableGrants(false);
197: diff.setIncludeTableConstraints(true);
198: diff.setIncludeViews(false);
199: diff.compareAll();
200: String xml = diff.getMigrateTargetXml();
201:
202: String count = TestUtil.getXPathValue(xml,
203: "count(/schema-diff/compare-settings/table-info)");
204: assertEquals("Incorrect number of tables listed", "3",
205: count);
206:
207: String value = TestUtil
208: .getXPathValue(
209: xml,
210: "/schema-diff/modify-table[@name='PERSON']/add-index/index-def/index-expression");
211: assertEquals("Index for address_id not added",
212: "LASTNAME A", value);
213:
214: diff.setIncludeIndex(false);
215: xml = diff.getMigrateTargetXml();
216:
217: count = TestUtil.getXPathValue(xml,
218: "count(/schema-diff/compare-settings/table-info)");
219: assertEquals("Incorrect number of tables listed", "3",
220: count);
221:
222: count = TestUtil.getXPathValue(xml,
223: "count(/schema-diff/modify-table/add-index)");
224: assertEquals("Add index present", "0", count);
225: } catch (Exception e) {
226: e.printStackTrace();
227: fail(e.getMessage());
228: }
229: }
230:
231: private void setupGrantTestDb() throws SQLException,
232: ClassNotFoundException {
233: TestUtil util = new TestUtil("schemaDiffTest");
234:
235: this .source = util.getConnection("source");
236: this .target = util.getConnection("target");
237: Statement stmt = null;
238:
239: try {
240: stmt = source.createStatement();
241: stmt
242: .executeUpdate("create table person (person_id integer primary key, firstname varchar(100), lastname varchar(100))");
243: stmt
244: .executeUpdate("CREATE USER unit_test PASSWORD 'secret'");
245: stmt.executeUpdate("GRANT SELECT ON PERSON to unit_test");
246:
247: stmt = target.createStatement();
248: stmt
249: .executeUpdate("create table person (person_id integer primary key, firstname varchar(100), lastname varchar(100))");
250: stmt
251: .executeUpdate("CREATE USER unit_test PASSWORD 'secret'");
252: stmt.executeUpdate("GRANT DELETE ON PERSON to unit_test");
253: } finally {
254: SqlUtil.closeStatement(stmt);
255: }
256: }
257:
258: private void setupIndexDiffTestDb() throws SQLException,
259: ClassNotFoundException {
260: TestUtil util = new TestUtil("schemaDiffTest");
261:
262: this .source = util.getConnection("source");
263: this .target = util.getConnection("target");
264: Statement stmt = null;
265:
266: try {
267: stmt = source.createStatement();
268: stmt
269: .executeUpdate("create table person (person_id integer primary key, firstname varchar(100), lastname varchar(100))");
270: stmt
271: .executeUpdate("create table address (address_id integer primary key, street varchar(50), city varchar(100), phone varchar(50), email varchar(50))");
272: stmt
273: .executeUpdate("create table person_address (person_id integer, address_id integer, primary key (person_id, address_id))");
274: stmt
275: .executeUpdate("alter table person_address add constraint fk_pa_person foreign key (person_id) references person(person_id)");
276: stmt
277: .executeUpdate("alter table person_address add constraint fk_pa_address foreign key (address_id) references address(address_id)");
278:
279: stmt
280: .executeUpdate("create index test_index on person (lastname)");
281:
282: stmt = target.createStatement();
283: stmt
284: .executeUpdate("create table person (person_id integer primary key, firstname varchar(100), lastname varchar(100))");
285: stmt
286: .executeUpdate("create table address (address_id integer primary key, street varchar(50), city varchar(100), phone varchar(50), email varchar(50))");
287: stmt
288: .executeUpdate("create table person_address (person_id integer, address_id integer, primary key (person_id, address_id))");
289: stmt
290: .executeUpdate("alter table person_address add constraint fk_pa_person foreign key (person_id) references person(person_id)");
291: stmt
292: .executeUpdate("alter table person_address add constraint fk_pa_address foreign key (address_id) references address(address_id)");
293:
294: } finally {
295: SqlUtil.closeStatement(stmt);
296: }
297: }
298:
299: private void setupBaseDatabase() throws SQLException,
300: ClassNotFoundException {
301: TestUtil util = new TestUtil("schemaDiffTest");
302:
303: this .source = util.getConnection("source");
304: this .target = util.getConnection("target");
305: Statement stmt = null;
306:
307: try {
308: stmt = source.createStatement();
309: stmt
310: .executeUpdate("create table person (person_id integer primary key, firstname varchar(100), lastname varchar(100))");
311: stmt
312: .executeUpdate("create table address (address_id integer primary key, street varchar(50), city varchar(100), phone varchar(50), email varchar(50))");
313: stmt
314: .executeUpdate("create table person_address (person_id integer, address_id integer, primary key (person_id, address_id))");
315: stmt
316: .executeUpdate("alter table person_address add constraint fk_pa_person foreign key (person_id) references person(person_id)");
317: stmt
318: .executeUpdate("alter table person_address add constraint fk_pa_address foreign key (address_id) references address(address_id)");
319:
320: stmt
321: .executeUpdate("CREATE VIEW v_person AS SELECT * FROM person");
322: stmt.executeUpdate("CREATE sequence seq_one");
323: stmt
324: .executeUpdate("CREATE sequence seq_two increment by 5");
325: stmt.executeUpdate("CREATE sequence seq_three");
326:
327: stmt = target.createStatement();
328: stmt
329: .executeUpdate("create table person (person_id integer primary key, firstname varchar(50), lastname varchar(100))");
330: stmt
331: .executeUpdate("create table address (address_id integer primary key, street varchar(10), city varchar(100), pone varchar(50), remark varchar(500))");
332: stmt
333: .executeUpdate("create table person_address (person_id integer, address_id integer, primary key (person_id, address_id))");
334: stmt
335: .executeUpdate("alter table person_address add constraint fk_pa_person foreign key (person_id) references person(person_id)");
336:
337: stmt
338: .executeUpdate("CREATE VIEW something AS SELECT * FROM address");
339:
340: stmt.executeUpdate("CREATE sequence seq_one");
341: stmt.executeUpdate("CREATE sequence seq_two");
342: stmt.executeUpdate("CREATE sequence seq_to_be_deleted");
343: } finally {
344: SqlUtil.closeStatement(stmt);
345: }
346:
347: }
348: }
|