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  package org.dbunit.operation;
22  
23  import java.io.File;
24  import java.io.FileReader;
25  import java.io.Reader;
26  import java.sql.SQLException;
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.DataSetUtils;
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.NoSuchColumnException;
46  import org.dbunit.dataset.SortedTable;
47  import org.dbunit.dataset.datatype.DataType;
48  import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
49  import org.dbunit.dataset.xml.XmlDataSet;
50  import org.dbunit.testutil.TestUtils;
51  
52  /**
53   * @author Manuel Laflamme
54   * @version $Revision: 1162 $
55   * @since Feb 19, 2002
56   */
57  public class InsertOperationIT extends AbstractDatabaseIT
58  {
59      public InsertOperationIT(String s)
60      {
61          super(s);
62      }
63  
64      public void testMockExecute() throws Exception
65      {
66          String schemaName = "schema";
67          String tableName = "table";
68          String[] expected = {
69              "insert into schema.table (c1, c2, c3) values ('toto', 1234, 'false')",
70              "insert into schema.table (c1, c2, c3) values ('qwerty', 123.45, 'true')",
71          };
72  
73          // setup table
74          Column[] columns = new Column[]{
75              new Column("c1", DataType.VARCHAR),
76              new Column("c2", DataType.NUMERIC),
77              new Column("c3", DataType.BOOLEAN),
78          };
79          DefaultTable table = new DefaultTable(tableName, columns);
80          table.addRow(new Object[]{"toto", "1234", Boolean.FALSE});
81          table.addRow(new Object[]{"qwerty", new Double("123.45"), "true"});
82          IDataSet dataSet = new DefaultDataSet(table);
83  
84          // setup mock objects
85          MockBatchStatement statement = new MockBatchStatement();
86          statement.addExpectedBatchStrings(expected);
87          statement.setExpectedExecuteBatchCalls(1);
88          statement.setExpectedClearBatchCalls(1);
89          statement.setExpectedCloseCalls(1);
90  
91          MockStatementFactory factory = new MockStatementFactory();
92          factory.setExpectedCreatePreparedStatementCalls(1);
93          factory.setupStatement(statement);
94  
95          MockDatabaseConnection connection = new MockDatabaseConnection();
96          connection.setupDataSet(dataSet);
97          connection.setupSchema(schemaName);
98          connection.setupStatementFactory(factory);
99          connection.setExpectedCloseCalls(0);
100 
101         // execute operation
102         new InsertOperation().execute(connection, dataSet);
103 
104         statement.verify();
105         factory.verify();
106         connection.verify();
107     }
108 
109     public void testExecuteUnknownColumn() throws Exception
110     {
111         String tableName = "table";
112 
113         // setup table
114         Column[] columns = new Column[]{
115             new Column("column", DataType.VARCHAR),
116             new Column("unknown", DataType.VARCHAR),
117         };
118         DefaultTable table = new DefaultTable(tableName, columns);
119         table.addRow();
120         table.setValue(0, columns[0].getColumnName(), null);
121         table.setValue(0, columns[0].getColumnName(), "value");
122         IDataSet insertDataset = new DefaultDataSet(table);
123 
124         IDataSet databaseDataSet = new DefaultDataSet(
125                 new DefaultTable(tableName, new Column[]{
126                     new Column("column", DataType.VARCHAR),
127                 }));
128 
129         // setup mock objects
130         MockBatchStatement statement = new MockBatchStatement();
131         statement.setExpectedExecuteBatchCalls(0);
132         statement.setExpectedClearBatchCalls(0);
133         statement.setExpectedCloseCalls(0);
134 
135         MockStatementFactory factory = new MockStatementFactory();
136         factory.setExpectedCreatePreparedStatementCalls(0);
137         factory.setupStatement(statement);
138 
139         MockDatabaseConnection connection = new MockDatabaseConnection();
140         connection.setupDataSet(databaseDataSet);
141         connection.setupStatementFactory(factory);
142         connection.setExpectedCloseCalls(0);
143 
144         // execute operation
145         try
146         {
147             new InsertOperation().execute(connection, insertDataset);
148             fail("Should not be here!");
149         }
150         catch (NoSuchColumnException e)
151         {
152 
153         }
154 
155         statement.verify();
156         factory.verify();
157         connection.verify();
158     }
159 
160     public void testExecuteIgnoreNone() throws Exception
161     {
162         String schemaName = "schema";
163         String tableName = "table";
164         String[] expected = {
165             "insert into schema.table (c1, c2, c3) values ('toto', 1234, 'false')",
166             "insert into schema.table (c2, c3) values (123.45, 'true')",
167             "insert into schema.table (c1, c2, c3) values ('qwerty1', 1, 'true')",
168             "insert into schema.table (c1, c2, c3) values ('qwerty2', 2, 'false')",
169             "insert into schema.table (c3) values ('false')",
170         };
171 
172         // setup table
173         Column[] columns = new Column[]{
174             new Column("c1", DataType.VARCHAR),
175             new Column("c2", DataType.NUMERIC),
176             new Column("c3", DataType.BOOLEAN),
177         };
178         DefaultTable table = new DefaultTable(tableName, columns);
179         table.addRow(new Object[]{"toto", "1234", Boolean.FALSE});
180         table.addRow(new Object[]{ITable.NO_VALUE, new Double("123.45"), "true"});
181         table.addRow(new Object[]{"qwerty1", "1", Boolean.TRUE});
182         table.addRow(new Object[]{"qwerty2", "2", Boolean.FALSE});
183         table.addRow(new Object[]{ITable.NO_VALUE, ITable.NO_VALUE, Boolean.FALSE});
184         IDataSet dataSet = new DefaultDataSet(table);
185 
186         // setup mock objects
187         MockBatchStatement statement = new MockBatchStatement();
188         statement.addExpectedBatchStrings(expected);
189         statement.setExpectedExecuteBatchCalls(4);
190         statement.setExpectedClearBatchCalls(4);
191         statement.setExpectedCloseCalls(4);
192 
193         MockStatementFactory factory = new MockStatementFactory();
194         factory.setExpectedCreatePreparedStatementCalls(4);
195         factory.setupStatement(statement);
196 
197         MockDatabaseConnection connection = new MockDatabaseConnection();
198         connection.setupDataSet(dataSet);
199         connection.setupSchema(schemaName);
200         connection.setupStatementFactory(factory);
201         connection.setExpectedCloseCalls(0);
202 
203         // execute operation
204         new InsertOperation().execute(connection, dataSet);
205 
206         statement.verify();
207         factory.verify();
208         connection.verify();
209     }
210 
211 //    public void testExecuteNullAsNone() throws Exception
212 //    {
213 //        String schemaName = "schema";
214 //        String tableName = "table";
215 //        String[] expected = {
216 //            "insert into schema.table (c1, c2, c3) values ('toto', 1234, 'false')",
217 //            "insert into schema.table (c2, c3) values (123.45, 'true')",
218 //            "insert into schema.table (c1, c2, c3) values ('qwerty1', 1, 'true')",
219 //            "insert into schema.table (c1, c2, c3) values ('qwerty2', 2, 'false')",
220 //            "insert into schema.table (c3) values ('false')",
221 //        };
222 //
223 //        // setup table
224 //        List valueList = new ArrayList();
225 //        valueList.add(new Object[]{"toto", "1234", Boolean.FALSE});
226 //        valueList.add(new Object[]{null, new Double("123.45"), "true"});
227 //        valueList.add(new Object[]{"qwerty1", "1", Boolean.TRUE});
228 //        valueList.add(new Object[]{"qwerty2", "2", Boolean.FALSE});
229 //        valueList.add(new Object[]{null, null, Boolean.FALSE});
230 //        Column[] columns = new Column[]{
231 //            new Column("c1", DataType.VARCHAR),
232 //            new Column("c2", DataType.NUMERIC),
233 //            new Column("c3", DataType.BOOLEAN),
234 //        };
235 //        DefaultTable table = new DefaultTable(tableName, columns, valueList);
236 //        IDataSet dataSet = new DefaultDataSet(table);
237 //
238 //        // setup mock objects
239 //        MockBatchStatement statement = new MockBatchStatement();
240 //        statement.addExpectedBatchStrings(expected);
241 //        statement.setExpectedExecuteBatchCalls(4);
242 //        statement.setExpectedClearBatchCalls(4);
243 //        statement.setExpectedCloseCalls(4);
244 //
245 //        MockStatementFactory factory = new MockStatementFactory();
246 //        factory.setExpectedCreatePreparedStatementCalls(4);
247 //        factory.setupStatement(statement);
248 //
249 //        MockDatabaseConnection connection = new MockDatabaseConnection();
250 //        connection.setupDataSet(dataSet);
251 //        connection.setupSchema(schemaName);
252 //        connection.setupStatementFactory(factory);
253 //        connection.setExpectedCloseCalls(0);
254 //        DatabaseConfig config = connection.getConfig();
255 //        config.setFeature(DatabaseConfig.FEATURE_NULL_AS_NONE, true);
256 //
257 //        // execute operation
258 //        new InsertOperation().execute(connection, dataSet);
259 //
260 //        statement.verify();
261 //        factory.verify();
262 //        connection.verify();
263 //    }
264 
265     public void testExecuteWithEscapedNames() throws Exception
266     {
267         String schemaName = "schema";
268         String tableName = "table";
269         String[] expected = {
270             "insert into 'schema'.'table' ('c1', 'c2', 'c3') values ('toto', 1234, 'false')",
271             "insert into 'schema'.'table' ('c1', 'c2', 'c3') values ('qwerty', 123.45, 'true')",
272         };
273 
274         // setup table
275         Column[] columns = new Column[]{
276             new Column("c1", DataType.VARCHAR),
277             new Column("c2", DataType.NUMERIC),
278             new Column("c3", DataType.BOOLEAN),
279         };
280         DefaultTable table = new DefaultTable(tableName, columns);
281         table.addRow(new Object[]{"toto", "1234", Boolean.FALSE});
282         table.addRow(new Object[]{"qwerty", new Double("123.45"), "true"});
283         IDataSet dataSet = new DefaultDataSet(table);
284 
285         // setup mock objects
286         MockBatchStatement statement = new MockBatchStatement();
287         statement.addExpectedBatchStrings(expected);
288         statement.setExpectedExecuteBatchCalls(1);
289         statement.setExpectedClearBatchCalls(1);
290         statement.setExpectedCloseCalls(1);
291 
292         MockStatementFactory factory = new MockStatementFactory();
293         factory.setExpectedCreatePreparedStatementCalls(1);
294         factory.setupStatement(statement);
295 
296         MockDatabaseConnection connection = new MockDatabaseConnection();
297         connection.setupDataSet(dataSet);
298         connection.setupSchema(schemaName);
299         connection.setupStatementFactory(factory);
300         connection.setExpectedCloseCalls(0);
301 
302         // execute operation
303         connection.getConfig().setProperty(
304                 DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "'?'");
305         new InsertOperation().execute(connection, dataSet);
306 
307         statement.verify();
308         factory.verify();
309         connection.verify();
310     }
311 
312     public void testExecuteWithEmptyTable() throws Exception
313     {
314         Column[] columns = {new Column("c1", DataType.VARCHAR)};
315         ITable table = new DefaultTable(new DefaultTableMetaData(
316                 "name", columns, columns));
317         IDataSet dataSet = new DefaultDataSet(table);
318 
319         // setup mock objects
320         MockStatementFactory factory = new MockStatementFactory();
321         factory.setExpectedCreatePreparedStatementCalls(0);
322 
323         MockDatabaseConnection connection = new MockDatabaseConnection();
324         connection.setupDataSet(dataSet);
325         connection.setupStatementFactory(factory);
326         connection.setExpectedCloseCalls(0);
327 
328         // execute operation
329         new InsertOperation().execute(connection, dataSet);
330 
331         factory.verify();
332         connection.verify();
333     }
334 
335     public void testInsertClob() throws Exception
336     {
337         // execute this test only if the target database support CLOB
338         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
339         if (environment.support(TestFeature.CLOB))
340         {
341             String tableName = "CLOB_TABLE";
342 
343             Reader in = new FileReader(TestUtils.getFile("xml/clobInsertTest.xml"));
344             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
345 
346             assertEquals("count before", 0, _connection.getRowCount(tableName));
347 
348             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
349 
350             ITable tableAfter = _connection.createDataSet().getTable(tableName);
351             assertEquals("count after", 3, tableAfter.getRowCount());
352             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
353         }
354     }
355 
356     public void testInsertBlob() throws Exception
357     {
358         // execute this test only if the target database support BLOB
359         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
360         if (environment.support(TestFeature.BLOB))
361         {
362             String tableName = "BLOB_TABLE";
363 
364             Reader in = new FileReader(TestUtils.getFile("xml/blobInsertTest.xml"));
365             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
366 
367             assertEquals("count before", 0, _connection.getRowCount(tableName));
368 
369             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
370 
371             ITable tableAfter = _connection.createDataSet().getTable(tableName);
372             assertEquals("count after", 1, tableAfter.getRowCount());
373             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
374         }
375     }
376 
377     public void testInsertSdoGeometry() throws Exception
378     {
379         // execute this test only if the target database supports SDO_GEOMETRY
380         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
381         if (environment.support(TestFeature.SDO_GEOMETRY))
382         {
383             String tableName = "SDO_GEOMETRY_TABLE";
384 
385             Reader in = new FileReader(TestUtils.getFile("xml/sdoGeometryInsertTest.xml"));
386             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
387 
388             assertEquals("count before", 0, _connection.getRowCount(tableName));
389 
390             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
391 
392             ITable tableAfter = _connection.createDataSet().getTable(tableName);
393             assertEquals("count after", 1, tableAfter.getRowCount());
394             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
395         }
396     }
397 
398     public void testMissingColumns() throws Exception
399     {
400         Reader in = TestUtils.getFileReader("xml/missingColumnTest.xml");
401         IDataSet xmlDataSet = new XmlDataSet(in);
402 
403         ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
404         DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
405         ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
406 
407         // verify tables before
408         for (int i = 0; i < tablesBefore.length; i++)
409         {
410             ITable table = tablesBefore[i];
411             String tableName = table.getTableMetaData().getTableName();
412             if (tableName.startsWith("EMPTY"))
413             {
414                 assertEquals(tableName + " before", 0, table.getRowCount());
415             }
416         }
417 
418         // verify tables after
419         for (int i = 0; i < tablesAfter.length; i++)
420         {
421             ITable databaseTable = tablesAfter[i];
422             String tableName = databaseTable.getTableMetaData().getTableName();
423 
424             if (tableName.startsWith("EMPTY"))
425             {
426                 Column[] columns = databaseTable.getTableMetaData().getColumns();
427                 ITable xmlTable = xmlDataSet.getTable(tableName);
428 
429                 // verify row count
430                 assertEquals("row count", xmlTable.getRowCount(),
431                         databaseTable.getRowCount());
432 
433                 // for each table row
434                 for (int j = 0; j < databaseTable.getRowCount(); j++)
435                 {
436                     // verify first column values
437                     Object expected = xmlTable.getValue(j, columns[0].getColumnName());
438                     Object actual = databaseTable.getValue(j, columns[0].getColumnName());
439 
440                     assertEquals(tableName + "." + columns[0].getColumnName(),
441                             expected, actual);
442 
443                     // all remaining columns should be null except mssql server timestamp column which is of type binary.
444                     for (int k = 1; k < columns.length; k++)
445                     {
446                         String columnName = columns[k].getColumnName();
447                         assertEquals(tableName + "." + columnName,
448                                 null, databaseTable.getValue(j, columnName));
449                     }
450                 }
451             }
452         }
453 
454     }
455 
456     public void testExecute() throws Exception
457     {
458         Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
459         IDataSet dataSet = new XmlDataSet(in);
460 
461         testExecute(dataSet);
462     }
463 
464     public void testExecuteCaseInsensitive() throws Exception
465     {
466         Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
467         IDataSet dataSet = new XmlDataSet(in);
468 
469         testExecute(new LowerCaseDataSet(dataSet));
470     }
471 
472     public void testExecuteForwardOnly() throws Exception
473     {
474         Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
475         IDataSet dataSet = new XmlDataSet(in);
476 
477         testExecute(new ForwardOnlyDataSet(dataSet));
478     }
479 
480     private void testExecute(IDataSet dataSet) throws Exception, SQLException
481     {
482         ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
483         DatabaseOperation.INSERT.execute(_connection, dataSet);
484         ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
485 
486         assertEquals("table count", tablesBefore.length, tablesAfter.length);
487         for (int i = 0; i < tablesBefore.length; i++)
488         {
489             ITable table = tablesBefore[i];
490             String name = table.getTableMetaData().getTableName();
491 
492 
493             if (name.startsWith("EMPTY"))
494             {
495                 assertEquals(name + "before", 0, table.getRowCount());
496             }
497         }
498 
499         for (int i = 0; i < tablesAfter.length; i++)
500         {
501             ITable table = tablesAfter[i];
502             String name = table.getTableMetaData().getTableName();
503 
504             if (name.startsWith("EMPTY"))
505             {
506                 if (dataSet instanceof ForwardOnlyDataSet)
507                 {
508                     assertTrue(name, table.getRowCount() > 0);
509                 }
510                 else
511                 {
512                     SortedTable expectedTable = new SortedTable(
513                             dataSet.getTable(name), dataSet.getTable(name).getTableMetaData());
514                     SortedTable actualTable = new SortedTable(table);
515                     Assertion.assertEquals(expectedTable, actualTable);
516                 }
517             }
518         }
519     }
520 }
521 
522 
523 
524 
525 
526 
527 
528 
529 
530