View Javadoc
1   /*
2    *
3    * The DbUnit Database Testing Framework
4    * Copyright (C)2002-2004, DbUnit.org
5    *
6    * This library is free software; you can redistribute it and/or
7    * modify it under the terms of the GNU Lesser General Public
8    * License as published by the Free Software Foundation; either
9    * version 2.1 of the License, or (at your option) any later version.
10   *
11   * This library is distributed in the hope that it will be useful,
12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
14   * Lesser General Public License for more details.
15   *
16   * You should have received a copy of the GNU Lesser General Public
17   * License along with this library; if not, write to the Free Software
18   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
19   *
20   */
21  
22  package org.dbunit.operation;
23  
24  import java.io.File;
25  import java.io.FileReader;
26  import java.io.Reader;
27  
28  import org.dbunit.AbstractDatabaseIT;
29  import org.dbunit.Assertion;
30  import org.dbunit.DatabaseEnvironment;
31  import org.dbunit.TestFeature;
32  import org.dbunit.database.DatabaseConfig;
33  import org.dbunit.database.MockDatabaseConnection;
34  import org.dbunit.database.statement.MockBatchStatement;
35  import org.dbunit.database.statement.MockStatementFactory;
36  import org.dbunit.dataset.Column;
37  import org.dbunit.dataset.CompositeDataSet;
38  import org.dbunit.dataset.DefaultDataSet;
39  import org.dbunit.dataset.DefaultTable;
40  import org.dbunit.dataset.DefaultTableMetaData;
41  import org.dbunit.dataset.ForwardOnlyDataSet;
42  import org.dbunit.dataset.IDataSet;
43  import org.dbunit.dataset.ITable;
44  import org.dbunit.dataset.LowerCaseDataSet;
45  import org.dbunit.dataset.NoPrimaryKeyException;
46  import org.dbunit.dataset.datatype.DataType;
47  import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
48  import org.dbunit.dataset.xml.XmlDataSet;
49  import org.dbunit.testutil.TestUtils;
50  
51  /**
52   * @author Manuel Laflamme
53   * @version $Revision$
54   * @since Feb 19, 2002
55   */
56  public class UpdateOperationIT extends AbstractDatabaseIT
57  {
58      public UpdateOperationIT(String s)
59      {
60          super(s);
61      }
62  
63      ////////////////////////////////////////////////////////////////////////////
64      //
65  
66      protected IDataSet getDataSet() throws Exception
67      {
68          IDataSet dataSet = super.getDataSet();
69  
70          DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
71          if (environment.support(TestFeature.BLOB))
72          {
73              dataSet = new CompositeDataSet(
74                      new FlatXmlDataSetBuilder().build(TestUtils.getFile("xml/blobInsertTest.xml")),
75                      dataSet);
76          }
77  
78          if (environment.support(TestFeature.CLOB))
79          {
80              dataSet = new CompositeDataSet(
81                      new FlatXmlDataSetBuilder().build(TestUtils.getFile("xml/clobInsertTest.xml")),
82                      dataSet);
83          }
84  
85          if (environment.support(TestFeature.SDO_GEOMETRY))
86          {
87              dataSet = new CompositeDataSet(
88                      new FlatXmlDataSetBuilder().build(TestUtils.getFile("xml/sdoGeometryInsertTest.xml")),
89                      dataSet
90              );
91          }
92  
93          if (environment.support(TestFeature.XML_TYPE))
94          {
95              dataSet = new CompositeDataSet(
96                      new FlatXmlDataSetBuilder().build(TestUtils.getFile("xml/xmlTypeInsertTest.xml")),
97                      dataSet);
98          }
99  
100         return dataSet;
101     }
102 
103     ////////////////////////////////////////////////////////////////////////////
104     //
105 
106     public void testMockExecute() throws Exception
107     {
108         String schemaName = "schema";
109         String tableName = "table";
110         String[] expected = {
111             "update schema.table set c2 = 1234, c3 = 'false' where c4 = 0 and c1 = 'toto'",
112             "update schema.table set c2 = 123.45, c3 = NULL where c4 = 0 and c1 = 'qwerty'",
113         };
114 
115         Column[] columns = new Column[]{
116             new Column("c1", DataType.VARCHAR),
117             new Column("c2", DataType.NUMERIC),
118             new Column("c3", DataType.VARCHAR),
119             new Column("c4", DataType.NUMERIC),
120         };
121         String[] primaryKeys = {"c4", "c1"};
122         DefaultTable table = new DefaultTable(new DefaultTableMetaData(
123                 tableName, columns, primaryKeys));
124         table.addRow(new Object[]{"toto", "1234", "false", "0"});
125         table.addRow(new Object[]{"qwerty", new Double("123.45"), null, "0"});
126         IDataSet dataSet = new DefaultDataSet(table);
127 
128         // setup mock objects
129         MockBatchStatement statement = new MockBatchStatement();
130         statement.addExpectedBatchStrings(expected);
131         statement.setExpectedExecuteBatchCalls(1);
132         statement.setExpectedClearBatchCalls(1);
133         statement.setExpectedCloseCalls(1);
134 
135         MockStatementFactory factory = new MockStatementFactory();
136         factory.setExpectedCreatePreparedStatementCalls(1);
137         factory.setupStatement(statement);
138 
139         MockDatabaseConnection connection = new MockDatabaseConnection();
140         connection.setupDataSet(dataSet);
141         connection.setupSchema(schemaName);
142         connection.setupStatementFactory(factory);
143         connection.setExpectedCloseCalls(0);
144 
145         // execute operation
146         new UpdateOperation().execute(connection, dataSet);
147 
148         statement.verify();
149         factory.verify();
150         connection.verify();
151     }
152 
153     public void testExecuteWithBlanksDisabledAndEmptyString() throws Exception {
154         String schemaName = "schema";
155         String tableName = "table";
156 
157         Column[] columns = new Column[]{
158             new Column("c3", DataType.VARCHAR),
159             new Column("c4", DataType.NUMERIC),
160         };
161         String[] primaryKeys = {"c4"};
162         DefaultTable table = new DefaultTable(new DefaultTableMetaData(
163                 tableName, columns, primaryKeys));
164         table.addRow(new Object[]{"", "1"});
165         IDataSet dataSet = new DefaultDataSet(table);
166 
167         // setup mock objects
168         MockBatchStatement statement = new MockBatchStatement();
169         statement.setExpectedExecuteBatchCalls(0);
170         statement.setExpectedClearBatchCalls(0);
171         statement.setExpectedCloseCalls(1);
172 
173         MockStatementFactory factory = new MockStatementFactory();
174         factory.setExpectedCreatePreparedStatementCalls(1);
175         factory.setupStatement(statement);
176 
177         MockDatabaseConnection connection = new MockDatabaseConnection();
178         connection.setupDataSet(dataSet);
179         connection.setupSchema(schemaName);
180         connection.setupStatementFactory(factory);
181         connection.setExpectedCloseCalls(0);
182 
183         // execute operation
184         connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, false);
185         try {
186             new UpdateOperation().execute(connection, dataSet);
187             fail("Update should not succedd");
188         } catch(IllegalArgumentException e) {
189             // ignore
190         } finally {
191 	        statement.verify();
192 	        factory.verify();
193 	        connection.verify();
194         }
195     }
196 
197     public void testExecuteWithBlanksDisabledAndNonEmptyStrings() throws Exception {
198         String schemaName = "schema";
199         String tableName = "table";
200         String[] expected = {
201                 String.format("update %s.%s set c3 = 'not-empty' where c4 = 1", schemaName, tableName),
202                 String.format("update %s.%s set c3 = NULL where c4 = 2", schemaName, tableName)
203         };
204 
205         Column[] columns = new Column[]{
206             new Column("c3", DataType.VARCHAR),
207             new Column("c4", DataType.NUMERIC),
208         };
209         String[] primaryKeys = {"c4"};
210         DefaultTable table = new DefaultTable(new DefaultTableMetaData(
211                 tableName, columns, primaryKeys));
212         table.addRow(new Object[]{"not-empty", "1"});
213         table.addRow(new Object[]{null, "2"});
214         IDataSet dataSet = new DefaultDataSet(table);
215 
216         // setup mock objects
217         MockBatchStatement statement = new MockBatchStatement();
218         statement.addExpectedBatchStrings(expected);
219         statement.setExpectedExecuteBatchCalls(1);
220         statement.setExpectedClearBatchCalls(1);
221         statement.setExpectedCloseCalls(1);
222 
223         MockStatementFactory factory = new MockStatementFactory();
224         factory.setExpectedCreatePreparedStatementCalls(1);
225         factory.setupStatement(statement);
226 
227         MockDatabaseConnection connection = new MockDatabaseConnection();
228         connection.setupDataSet(dataSet);
229         connection.setupSchema(schemaName);
230         connection.setupStatementFactory(factory);
231         connection.setExpectedCloseCalls(0);
232 
233         // execute operation
234         connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, false);
235         new UpdateOperation().execute(connection, dataSet);
236 
237         statement.verify();
238         factory.verify();
239         connection.verify();
240     }
241 
242     public void testExecuteWithBlanksAllowed() throws Exception {
243         String schemaName = "schema";
244         String tableName = "table";
245         String[] expected = {
246             String.format("update %s.%s set c3 = 'not-empty' where c4 = 1", schemaName, tableName),
247             String.format("update %s.%s set c3 = NULL where c4 = 2", schemaName, tableName),
248             String.format("update %s.%s set c3 = '' where c4 = 3", schemaName, tableName),
249         };
250 
251         Column[] columns = new Column[]{
252             new Column("c3", DataType.VARCHAR),
253             new Column("c4", DataType.NUMERIC),
254         };
255         String[] primaryKeys = {"c4"};
256         DefaultTable table = new DefaultTable(new DefaultTableMetaData(
257                 tableName, columns, primaryKeys));
258         table.addRow(new Object[]{"not-empty", "1"});
259         table.addRow(new Object[]{null, "2"});
260         table.addRow(new Object[]{"", "3"});
261         IDataSet dataSet = new DefaultDataSet(table);
262 
263         // setup mock objects
264         MockBatchStatement statement = new MockBatchStatement();
265         statement.addExpectedBatchStrings(expected);
266         statement.setExpectedExecuteBatchCalls(1);
267         statement.setExpectedClearBatchCalls(1);
268         statement.setExpectedCloseCalls(1);
269 
270         MockStatementFactory factory = new MockStatementFactory();
271         factory.setExpectedCreatePreparedStatementCalls(1);
272         factory.setupStatement(statement);
273 
274         MockDatabaseConnection connection = new MockDatabaseConnection();
275         connection.setupDataSet(dataSet);
276         connection.setupSchema(schemaName);
277         connection.setupStatementFactory(factory);
278         connection.setExpectedCloseCalls(0);
279 
280         // execute operation
281         connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);
282         new UpdateOperation().execute(connection, dataSet);
283 
284         statement.verify();
285         factory.verify();
286         connection.verify();
287     }
288 
289     public void testExecuteWithEscapedName() throws Exception
290     {
291         String schemaName = "schema";
292         String tableName = "table";
293         String[] expected = {
294             "update [schema].[table] set [c2] = 1234, [c3] = 'false' where [c4] = 0 and [c1] = 'toto'",
295             "update [schema].[table] set [c2] = 123.45, [c3] = NULL where [c4] = 0 and [c1] = 'qwerty'",
296         };
297 
298         Column[] columns = new Column[]{
299             new Column("c1", DataType.VARCHAR),
300             new Column("c2", DataType.NUMERIC),
301             new Column("c3", DataType.VARCHAR),
302             new Column("c4", DataType.NUMERIC),
303         };
304         String[] primaryKeys = {"c4", "c1"};
305         DefaultTable table = new DefaultTable(new DefaultTableMetaData(
306                 tableName, columns, primaryKeys));
307         table.addRow(new Object[]{"toto", "1234", "false", "0"});
308         table.addRow(new Object[]{"qwerty", new Double("123.45"), null, "0"});
309         IDataSet dataSet = new DefaultDataSet(table);
310 
311         // setup mock objects
312         MockBatchStatement statement = new MockBatchStatement();
313         statement.addExpectedBatchStrings(expected);
314         statement.setExpectedExecuteBatchCalls(1);
315         statement.setExpectedClearBatchCalls(1);
316         statement.setExpectedCloseCalls(1);
317 
318         MockStatementFactory factory = new MockStatementFactory();
319         factory.setExpectedCreatePreparedStatementCalls(1);
320         factory.setupStatement(statement);
321 
322         MockDatabaseConnection connection = new MockDatabaseConnection();
323         connection.setupDataSet(dataSet);
324         connection.setupSchema(schemaName);
325         connection.setupStatementFactory(factory);
326         connection.setExpectedCloseCalls(0);
327 
328         // execute operation
329         connection.getConfig().setProperty(
330                 DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "[?]");
331         new UpdateOperation().execute(connection, dataSet);
332 
333         statement.verify();
334         factory.verify();
335         connection.verify();
336     }
337 
338     public void testExecuteWithEmptyTable() throws Exception
339     {
340         Column[] columns = {new Column("c1", DataType.VARCHAR)};
341         ITable table = new DefaultTable(new DefaultTableMetaData(
342                 "name", columns, columns));
343         IDataSet dataSet = new DefaultDataSet(table);
344 
345         // setup mock objects
346         MockStatementFactory factory = new MockStatementFactory();
347         factory.setExpectedCreatePreparedStatementCalls(0);
348 
349         MockDatabaseConnection connection = new MockDatabaseConnection();
350         connection.setupDataSet(dataSet);
351         connection.setupStatementFactory(factory);
352         connection.setExpectedCloseCalls(0);
353 
354         // execute operation
355         new UpdateOperation().execute(connection, dataSet);
356 
357         factory.verify();
358         connection.verify();
359     }
360 
361     public void testUpdateClob() throws Exception
362     {
363         // execute this test only if the target database support CLOB
364         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
365         if (environment.support(TestFeature.CLOB))
366         {
367             String tableName = "CLOB_TABLE";
368 
369             {
370                 IDataSet beforeDataSet = new FlatXmlDataSetBuilder().build(
371                         TestUtils.getFile("xml/clobInsertTest.xml"));
372 
373                 ITable tableBefore = _connection.createDataSet().getTable(tableName);
374                 assertEquals("count before", 3, _connection.getRowCount(tableName));
375                 Assertion.assertEquals(beforeDataSet.getTable(tableName), tableBefore);
376             }
377 
378             IDataSet afterDataSet = new FlatXmlDataSetBuilder().build(
379                     TestUtils.getFile("xml/clobUpdateTest.xml"));
380             DatabaseOperation.REFRESH.execute(_connection, afterDataSet);
381 
382             {
383                 ITable tableAfter = _connection.createDataSet().getTable(tableName);
384                 assertEquals("count after", 4, tableAfter.getRowCount());
385                 Assertion.assertEquals(afterDataSet.getTable(tableName), tableAfter);
386             }
387         }
388     }
389 
390     public void testUpdateBlob() throws Exception
391     {
392         // execute this test only if the target database support BLOB
393         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
394         if (environment.support(TestFeature.BLOB))
395         {
396             String tableName = "BLOB_TABLE";
397 
398             {
399                 IDataSet beforeDataSet = new FlatXmlDataSetBuilder().build(
400                         TestUtils.getFile("xml/blobInsertTest.xml"));
401 
402                 ITable tableBefore = _connection.createDataSet().getTable(tableName);
403                 assertEquals("count before", 3, _connection.getRowCount(tableName));
404                 Assertion.assertEquals(beforeDataSet.getTable(tableName), tableBefore);
405 
406 //                System.out.println("****** BEFORE *******");
407 //                FlatXmlDataSet.write(_connection.createDataSet(), System.out);
408             }
409 
410             IDataSet afterDataSet = new FlatXmlDataSetBuilder().build(
411                     TestUtils.getFile("xml/blobUpdateTest.xml"));
412             DatabaseOperation.REFRESH.execute(_connection, afterDataSet);
413 
414             {
415                 ITable tableAfter = _connection.createDataSet().getTable(tableName);
416                 assertEquals("count after", 4, tableAfter.getRowCount());
417                 Assertion.assertEquals(afterDataSet.getTable(tableName), tableAfter);
418 
419 //                System.out.println("****** AFTER *******");
420 //                FlatXmlDataSet.write(_connection.createDataSet(), System.out);
421             }
422         }
423     }
424 
425     public void testUpdateSdoGeometry() throws Exception
426     {
427         // execute this test only if the target database supports SDO_GEOMETRY
428         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
429         if (environment.support(TestFeature.SDO_GEOMETRY))
430         {
431             String tableName = "SDO_GEOMETRY_TABLE";
432 
433             {
434                 IDataSet beforeDataSet = new FlatXmlDataSetBuilder().build(
435                         TestUtils.getFile("xml/sdoGeometryInsertTest.xml"));
436 
437                 ITable tableBefore = _connection.createDataSet().getTable(tableName);
438                 assertEquals("count before", 1, _connection.getRowCount(tableName));
439                 Assertion.assertEquals(beforeDataSet.getTable(tableName), tableBefore);
440             }
441 
442             IDataSet afterDataSet = new FlatXmlDataSetBuilder().build(
443                     TestUtils.getFile("xml/sdoGeometryUpdateTest.xml"));
444             DatabaseOperation.REFRESH.execute(_connection, afterDataSet);
445 
446             {
447                 ITable tableAfter = _connection.createDataSet().getTable(tableName);
448                 assertEquals("count after", 8, tableAfter.getRowCount());
449                 Assertion.assertEquals(afterDataSet.getTable(tableName), tableAfter);
450             }
451         }
452     }
453 
454     public void testUpdateXmlType() throws Exception
455     {
456         // execute this test only if the target database support XML_TYPE
457         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
458         if (environment.support(TestFeature.XML_TYPE))
459         {
460             String tableName = "XML_TYPE_TABLE";
461 
462             {
463                 IDataSet beforeDataSet = new FlatXmlDataSetBuilder().build(
464                         TestUtils.getFile("xml/xmlTypeInsertTest.xml"));
465 
466                 ITable tableBefore = _connection.createDataSet().getTable(tableName);
467                 assertEquals("count before", 3, _connection.getRowCount(tableName));
468                 Assertion.assertEquals(beforeDataSet.getTable(tableName), tableBefore);
469             }
470 
471             IDataSet afterDataSet = new FlatXmlDataSetBuilder().build(
472                     TestUtils.getFile("xml/xmlTypeUpdateTest.xml"));
473             DatabaseOperation.REFRESH.execute(_connection, afterDataSet);
474 
475             {
476                 ITable tableAfter = _connection.createDataSet().getTable(tableName);
477                 assertEquals("count after", 4, tableAfter.getRowCount());
478                 Assertion.assertEquals(afterDataSet.getTable(tableName), tableAfter);
479             }
480         }
481     }
482 
483     public void testExecute() throws Exception
484     {
485         Reader in = new FileReader(
486                 TestUtils.getFile("xml/updateOperationTest.xml"));
487         IDataSet dataSet = new XmlDataSet(in);
488 
489         testExecute(dataSet);
490 
491     }
492 
493     public void testExecuteCaseInsensitive() throws Exception
494     {
495         Reader in = new FileReader(
496                 TestUtils.getFile("xml/updateOperationTest.xml"));
497         IDataSet dataSet = new XmlDataSet(in);
498 
499         testExecute(new LowerCaseDataSet(dataSet));
500     }
501 
502     public void testExecuteForwardOnly() throws Exception
503     {
504         Reader in = new FileReader(
505                 TestUtils.getFile("xml/updateOperationTest.xml"));
506         IDataSet dataSet = new XmlDataSet(in);
507 
508         testExecute(new ForwardOnlyDataSet(dataSet));
509     }
510 
511     public void testExecuteAndNoPrimaryKeys() throws Exception
512     {
513         String tableName = "TEST_TABLE";
514 
515         Reader reader = TestUtils.getFileReader("xml/updateOperationNoPKTest.xml");
516         IDataSet dataSet = new FlatXmlDataSetBuilder().build(reader);
517 
518         // verify table before
519         assertEquals("row count before", 6, _connection.getRowCount(tableName));
520 
521         try
522         {
523             DatabaseOperation.REFRESH.execute(_connection, dataSet);
524             fail("Should not be here!");
525         }
526         catch (NoPrimaryKeyException e)
527         {
528 
529         }
530 
531         // verify table after
532         assertEquals("row count before", 6, _connection.getRowCount(tableName));
533     }
534 
535     private void testExecute(IDataSet dataSet) throws Exception
536     {
537         String tableName = "PK_TABLE";
538         String[] columnNames = {"PK0", "PK1", "PK2", "NORMAL0", "NORMAL1"};
539         int modifiedRow = 1;
540 
541         // verify table before
542         ITable tableBefore = createOrderedTable(tableName, columnNames[0]);
543         assertEquals("row count before", 3, tableBefore.getRowCount());
544 
545         DatabaseOperation.UPDATE.execute(_connection, dataSet);
546 
547         ITable tableAfter = createOrderedTable(tableName, columnNames[0]);
548         assertEquals("row count after", 3, tableAfter.getRowCount());
549         for (int i = 0; i < tableAfter.getRowCount(); i++)
550         {
551             // verify modified row
552             if (i == modifiedRow)
553             {
554                 assertEquals("PK0", "1",
555                         tableAfter.getValue(i, "PK0").toString());
556                 assertEquals("PK1", "1",
557                         tableAfter.getValue(i, "PK1").toString());
558                 assertEquals("PK2", "1",
559                         tableAfter.getValue(i, "PK2").toString());
560                 assertEquals("NORMAL0", "toto",
561                         tableAfter.getValue(i, "NORMAL0").toString());
562                 assertEquals("NORMAL1", "qwerty",
563                         tableAfter.getValue(i, "NORMAL1").toString());
564             }
565             // all other row must be equals than before update
566             else
567             {
568                 for (int j = 0; j < columnNames.length; j++)
569                 {
570                     String name = columnNames[j];
571                     Object valueAfter = tableAfter.getValue(i, name);
572                     Object valueBefore = tableBefore.getValue(i, name);
573                     assertEquals("c=" + name + ",r=" + j, valueBefore, valueAfter);
574                 }
575             }
576         }
577     }
578 
579 
580 }
581 
582 
583 
584 
585 
586 
587 
588