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  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$
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 testExecuteWithBlanksDisabledAndEmptyString() throws Exception {
110         String schemaName = "schema";
111         String tableName = "table";
112 
113         Column[] columns = new Column[]{
114             new Column("c3", DataType.VARCHAR),
115             new Column("c4", DataType.NUMERIC),
116         };
117         DefaultTable table = new DefaultTable(tableName, columns);
118         table.addRow(new Object[]{"", "1"});
119         IDataSet dataSet = new DefaultDataSet(table);
120 
121         // setup mock objects
122         MockBatchStatement statement = new MockBatchStatement();
123         statement.setExpectedExecuteBatchCalls(0);
124         statement.setExpectedClearBatchCalls(0);
125         statement.setExpectedCloseCalls(1);
126 
127         MockStatementFactory factory = new MockStatementFactory();
128         factory.setExpectedCreatePreparedStatementCalls(1);
129         factory.setupStatement(statement);
130 
131         MockDatabaseConnection connection = new MockDatabaseConnection();
132         connection.setupDataSet(dataSet);
133         connection.setupSchema(schemaName);
134         connection.setupStatementFactory(factory);
135         connection.setExpectedCloseCalls(0);
136 
137         // execute operation
138         connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, false);
139         try {
140             new InsertOperation().execute(connection, dataSet);
141             fail("Update should not succedd");
142         } catch(IllegalArgumentException e) {
143             // ignore
144         } finally {
145 	        statement.verify();
146 	        factory.verify();
147 	        connection.verify();
148         }
149     }
150 
151     public void testExecuteWithBlanksDisabledAndNonEmptyStrings() throws Exception {
152         String schemaName = "schema";
153         String tableName = "table";
154         String[] expected = {
155                 String.format("insert into %s.%s (c3, c4) values ('not-empty', 1)", schemaName, tableName),
156                 String.format("insert into %s.%s (c3, c4) values (NULL, 2)", schemaName, tableName)
157         };
158 
159         Column[] columns = new Column[]{
160             new Column("c3", DataType.VARCHAR),
161             new Column("c4", DataType.NUMERIC),
162         };
163         DefaultTable table = new DefaultTable(tableName, columns);
164         table.addRow(new Object[]{"not-empty", "1"});
165         table.addRow(new Object[]{null, "2"});
166         IDataSet dataSet = new DefaultDataSet(table);
167 
168         // setup mock objects
169         MockBatchStatement statement = new MockBatchStatement();
170         statement.addExpectedBatchStrings(expected);
171         statement.setExpectedExecuteBatchCalls(1);
172         statement.setExpectedClearBatchCalls(1);
173         statement.setExpectedCloseCalls(1);
174 
175         MockStatementFactory factory = new MockStatementFactory();
176         factory.setExpectedCreatePreparedStatementCalls(1);
177         factory.setupStatement(statement);
178 
179         MockDatabaseConnection connection = new MockDatabaseConnection();
180         connection.setupDataSet(dataSet);
181         connection.setupSchema(schemaName);
182         connection.setupStatementFactory(factory);
183         connection.setExpectedCloseCalls(0);
184 
185         // execute operation
186         connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, false);
187         new InsertOperation().execute(connection, dataSet);
188 
189         statement.verify();
190         factory.verify();
191         connection.verify();
192     }
193 
194     public void testExecuteWithBlanksAllowed() throws Exception {
195         String schemaName = "schema";
196         String tableName = "table";
197         String[] expected = {
198             String.format("insert into %s.%s (c3, c4) values ('not-empty', 1)", schemaName, tableName),
199             String.format("insert into %s.%s (c3, c4) values (NULL, 2)", schemaName, tableName),
200             String.format("insert into %s.%s (c3, c4) values ('', 3)", schemaName, tableName),
201         };
202 
203         Column[] columns = new Column[]{
204             new Column("c3", DataType.VARCHAR),
205             new Column("c4", DataType.NUMERIC),
206         };
207         DefaultTable table = new DefaultTable(tableName, columns);
208         table.addRow(new Object[]{"not-empty", "1"});
209         table.addRow(new Object[]{null, "2"});
210         table.addRow(new Object[]{"", "3"});
211         IDataSet dataSet = new DefaultDataSet(table);
212 
213         // setup mock objects
214         MockBatchStatement statement = new MockBatchStatement();
215         statement.addExpectedBatchStrings(expected);
216         statement.setExpectedExecuteBatchCalls(1);
217         statement.setExpectedClearBatchCalls(1);
218         statement.setExpectedCloseCalls(1);
219 
220         MockStatementFactory factory = new MockStatementFactory();
221         factory.setExpectedCreatePreparedStatementCalls(1);
222         factory.setupStatement(statement);
223 
224         MockDatabaseConnection connection = new MockDatabaseConnection();
225         connection.setupDataSet(dataSet);
226         connection.setupSchema(schemaName);
227         connection.setupStatementFactory(factory);
228         connection.setExpectedCloseCalls(0);
229 
230         // execute operation
231         connection.getConfig().setFeature(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);
232         new InsertOperation().execute(connection, dataSet);
233 
234         statement.verify();
235         factory.verify();
236         connection.verify();
237     }
238 
239     public void testExecuteUnknownColumn() throws Exception
240     {
241         String tableName = "table";
242 
243         // setup table
244         Column[] columns = new Column[]{
245             new Column("column", DataType.VARCHAR),
246             new Column("unknown", DataType.VARCHAR),
247         };
248         DefaultTable table = new DefaultTable(tableName, columns);
249         table.addRow();
250         table.setValue(0, columns[0].getColumnName(), null);
251         table.setValue(0, columns[0].getColumnName(), "value");
252         IDataSet insertDataset = new DefaultDataSet(table);
253 
254         IDataSet databaseDataSet = new DefaultDataSet(
255                 new DefaultTable(tableName, new Column[]{
256                     new Column("column", DataType.VARCHAR),
257                 }));
258 
259         // setup mock objects
260         MockBatchStatement statement = new MockBatchStatement();
261         statement.setExpectedExecuteBatchCalls(0);
262         statement.setExpectedClearBatchCalls(0);
263         statement.setExpectedCloseCalls(0);
264 
265         MockStatementFactory factory = new MockStatementFactory();
266         factory.setExpectedCreatePreparedStatementCalls(0);
267         factory.setupStatement(statement);
268 
269         MockDatabaseConnection connection = new MockDatabaseConnection();
270         connection.setupDataSet(databaseDataSet);
271         connection.setupStatementFactory(factory);
272         connection.setExpectedCloseCalls(0);
273 
274         // execute operation
275         try
276         {
277             new InsertOperation().execute(connection, insertDataset);
278             fail("Should not be here!");
279         }
280         catch (NoSuchColumnException e)
281         {
282 
283         }
284 
285         statement.verify();
286         factory.verify();
287         connection.verify();
288     }
289 
290     public void testExecuteIgnoreNone() throws Exception
291     {
292         String schemaName = "schema";
293         String tableName = "table";
294         String[] expected = {
295             "insert into schema.table (c1, c2, c3) values ('toto', 1234, 'false')",
296             "insert into schema.table (c2, c3) values (123.45, 'true')",
297             "insert into schema.table (c1, c2, c3) values ('qwerty1', 1, 'true')",
298             "insert into schema.table (c1, c2, c3) values ('qwerty2', 2, 'false')",
299             "insert into schema.table (c3) values ('false')",
300         };
301 
302         // setup table
303         Column[] columns = new Column[]{
304             new Column("c1", DataType.VARCHAR),
305             new Column("c2", DataType.NUMERIC),
306             new Column("c3", DataType.BOOLEAN),
307         };
308         DefaultTable table = new DefaultTable(tableName, columns);
309         table.addRow(new Object[]{"toto", "1234", Boolean.FALSE});
310         table.addRow(new Object[]{ITable.NO_VALUE, new Double("123.45"), "true"});
311         table.addRow(new Object[]{"qwerty1", "1", Boolean.TRUE});
312         table.addRow(new Object[]{"qwerty2", "2", Boolean.FALSE});
313         table.addRow(new Object[]{ITable.NO_VALUE, ITable.NO_VALUE, Boolean.FALSE});
314         IDataSet dataSet = new DefaultDataSet(table);
315 
316         // setup mock objects
317         MockBatchStatement statement = new MockBatchStatement();
318         statement.addExpectedBatchStrings(expected);
319         statement.setExpectedExecuteBatchCalls(4);
320         statement.setExpectedClearBatchCalls(4);
321         statement.setExpectedCloseCalls(4);
322 
323         MockStatementFactory factory = new MockStatementFactory();
324         factory.setExpectedCreatePreparedStatementCalls(4);
325         factory.setupStatement(statement);
326 
327         MockDatabaseConnection connection = new MockDatabaseConnection();
328         connection.setupDataSet(dataSet);
329         connection.setupSchema(schemaName);
330         connection.setupStatementFactory(factory);
331         connection.setExpectedCloseCalls(0);
332 
333         // execute operation
334         new InsertOperation().execute(connection, dataSet);
335 
336         statement.verify();
337         factory.verify();
338         connection.verify();
339     }
340 
341 //    public void testExecuteNullAsNone() throws Exception
342 //    {
343 //        String schemaName = "schema";
344 //        String tableName = "table";
345 //        String[] expected = {
346 //            "insert into schema.table (c1, c2, c3) values ('toto', 1234, 'false')",
347 //            "insert into schema.table (c2, c3) values (123.45, 'true')",
348 //            "insert into schema.table (c1, c2, c3) values ('qwerty1', 1, 'true')",
349 //            "insert into schema.table (c1, c2, c3) values ('qwerty2', 2, 'false')",
350 //            "insert into schema.table (c3) values ('false')",
351 //        };
352 //
353 //        // setup table
354 //        List valueList = new ArrayList();
355 //        valueList.add(new Object[]{"toto", "1234", Boolean.FALSE});
356 //        valueList.add(new Object[]{null, new Double("123.45"), "true"});
357 //        valueList.add(new Object[]{"qwerty1", "1", Boolean.TRUE});
358 //        valueList.add(new Object[]{"qwerty2", "2", Boolean.FALSE});
359 //        valueList.add(new Object[]{null, null, Boolean.FALSE});
360 //        Column[] columns = new Column[]{
361 //            new Column("c1", DataType.VARCHAR),
362 //            new Column("c2", DataType.NUMERIC),
363 //            new Column("c3", DataType.BOOLEAN),
364 //        };
365 //        DefaultTable table = new DefaultTable(tableName, columns, valueList);
366 //        IDataSet dataSet = new DefaultDataSet(table);
367 //
368 //        // setup mock objects
369 //        MockBatchStatement statement = new MockBatchStatement();
370 //        statement.addExpectedBatchStrings(expected);
371 //        statement.setExpectedExecuteBatchCalls(4);
372 //        statement.setExpectedClearBatchCalls(4);
373 //        statement.setExpectedCloseCalls(4);
374 //
375 //        MockStatementFactory factory = new MockStatementFactory();
376 //        factory.setExpectedCreatePreparedStatementCalls(4);
377 //        factory.setupStatement(statement);
378 //
379 //        MockDatabaseConnection connection = new MockDatabaseConnection();
380 //        connection.setupDataSet(dataSet);
381 //        connection.setupSchema(schemaName);
382 //        connection.setupStatementFactory(factory);
383 //        connection.setExpectedCloseCalls(0);
384 //        DatabaseConfig config = connection.getConfig();
385 //        config.setFeature(DatabaseConfig.FEATURE_NULL_AS_NONE, true);
386 //
387 //        // execute operation
388 //        new InsertOperation().execute(connection, dataSet);
389 //
390 //        statement.verify();
391 //        factory.verify();
392 //        connection.verify();
393 //    }
394 
395     public void testExecuteWithEscapedNames() throws Exception
396     {
397         String schemaName = "schema";
398         String tableName = "table";
399         String[] expected = {
400             "insert into 'schema'.'table' ('c1', 'c2', 'c3') values ('toto', 1234, 'false')",
401             "insert into 'schema'.'table' ('c1', 'c2', 'c3') values ('qwerty', 123.45, 'true')",
402         };
403 
404         // setup table
405         Column[] columns = new Column[]{
406             new Column("c1", DataType.VARCHAR),
407             new Column("c2", DataType.NUMERIC),
408             new Column("c3", DataType.BOOLEAN),
409         };
410         DefaultTable table = new DefaultTable(tableName, columns);
411         table.addRow(new Object[]{"toto", "1234", Boolean.FALSE});
412         table.addRow(new Object[]{"qwerty", new Double("123.45"), "true"});
413         IDataSet dataSet = new DefaultDataSet(table);
414 
415         // setup mock objects
416         MockBatchStatement statement = new MockBatchStatement();
417         statement.addExpectedBatchStrings(expected);
418         statement.setExpectedExecuteBatchCalls(1);
419         statement.setExpectedClearBatchCalls(1);
420         statement.setExpectedCloseCalls(1);
421 
422         MockStatementFactory factory = new MockStatementFactory();
423         factory.setExpectedCreatePreparedStatementCalls(1);
424         factory.setupStatement(statement);
425 
426         MockDatabaseConnection connection = new MockDatabaseConnection();
427         connection.setupDataSet(dataSet);
428         connection.setupSchema(schemaName);
429         connection.setupStatementFactory(factory);
430         connection.setExpectedCloseCalls(0);
431 
432         // execute operation
433         connection.getConfig().setProperty(
434                 DatabaseConfig.PROPERTY_ESCAPE_PATTERN, "'?'");
435         new InsertOperation().execute(connection, dataSet);
436 
437         statement.verify();
438         factory.verify();
439         connection.verify();
440     }
441 
442     public void testExecuteWithEmptyTable() throws Exception
443     {
444         Column[] columns = {new Column("c1", DataType.VARCHAR)};
445         ITable table = new DefaultTable(new DefaultTableMetaData(
446                 "name", columns, columns));
447         IDataSet dataSet = new DefaultDataSet(table);
448 
449         // setup mock objects
450         MockStatementFactory factory = new MockStatementFactory();
451         factory.setExpectedCreatePreparedStatementCalls(0);
452 
453         MockDatabaseConnection connection = new MockDatabaseConnection();
454         connection.setupDataSet(dataSet);
455         connection.setupStatementFactory(factory);
456         connection.setExpectedCloseCalls(0);
457 
458         // execute operation
459         new InsertOperation().execute(connection, dataSet);
460 
461         factory.verify();
462         connection.verify();
463     }
464 
465     public void testInsertClob() throws Exception
466     {
467         // execute this test only if the target database support CLOB
468         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
469         if (environment.support(TestFeature.CLOB))
470         {
471             String tableName = "CLOB_TABLE";
472 
473             Reader in = new FileReader(TestUtils.getFile("xml/clobInsertTest.xml"));
474             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
475 
476             assertEquals("count before", 0, _connection.getRowCount(tableName));
477 
478             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
479 
480             ITable tableAfter = _connection.createDataSet().getTable(tableName);
481             assertEquals("count after", 3, tableAfter.getRowCount());
482             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
483         }
484     }
485 
486     public void testInsertBlob() throws Exception
487     {
488         // execute this test only if the target database support BLOB
489         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
490         if (environment.support(TestFeature.BLOB))
491         {
492             String tableName = "BLOB_TABLE";
493 
494             Reader in = new FileReader(TestUtils.getFile("xml/blobInsertTest.xml"));
495             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
496 
497             assertEquals("count before", 0, _connection.getRowCount(tableName));
498 
499             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
500 
501             ITable tableAfter = _connection.createDataSet().getTable(tableName);
502             assertEquals("count after", 3, tableAfter.getRowCount());
503             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
504         }
505     }
506 
507     public void testInsertSdoGeometry() throws Exception
508     {
509         // execute this test only if the target database supports SDO_GEOMETRY
510         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
511         if (environment.support(TestFeature.SDO_GEOMETRY))
512         {
513             String tableName = "SDO_GEOMETRY_TABLE";
514 
515             Reader in = new FileReader(TestUtils.getFile("xml/sdoGeometryInsertTest.xml"));
516             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
517 
518             assertEquals("count before", 0, _connection.getRowCount(tableName));
519 
520             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
521 
522             ITable tableAfter = _connection.createDataSet().getTable(tableName);
523             assertEquals("count after", 1, tableAfter.getRowCount());
524             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
525         }
526     }
527 
528     public void testInsertXmlType() throws Exception
529     {
530         // execute this test only if the target database support CLOB
531         DatabaseEnvironment environment = DatabaseEnvironment.getInstance();
532         if (environment.support(TestFeature.XML_TYPE))
533         {
534             String tableName = "XML_TYPE_TABLE";
535 
536             Reader in = new FileReader(TestUtils.getFile("xml/xmlTypeInsertTest.xml"));
537             IDataSet xmlDataSet = new FlatXmlDataSetBuilder().build(in);
538 
539             assertEquals("count before", 0, _connection.getRowCount(tableName));
540 
541             DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
542 
543             ITable tableAfter = _connection.createDataSet().getTable(tableName);
544             assertEquals("count after", 3, tableAfter.getRowCount());
545             Assertion.assertEquals(xmlDataSet.getTable(tableName), tableAfter);
546         }
547     }
548 
549     public void testMissingColumns() throws Exception
550     {
551         Reader in = TestUtils.getFileReader("xml/missingColumnTest.xml");
552         IDataSet xmlDataSet = new XmlDataSet(in);
553 
554         ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
555         DatabaseOperation.INSERT.execute(_connection, xmlDataSet);
556         ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
557 
558         // verify tables before
559         for (int i = 0; i < tablesBefore.length; i++)
560         {
561             ITable table = tablesBefore[i];
562             String tableName = table.getTableMetaData().getTableName();
563             if (tableName.startsWith("EMPTY"))
564             {
565                 assertEquals(tableName + " before", 0, table.getRowCount());
566             }
567         }
568 
569         // verify tables after
570         for (int i = 0; i < tablesAfter.length; i++)
571         {
572             ITable databaseTable = tablesAfter[i];
573             String tableName = databaseTable.getTableMetaData().getTableName();
574 
575             if (tableName.startsWith("EMPTY"))
576             {
577                 Column[] columns = databaseTable.getTableMetaData().getColumns();
578                 ITable xmlTable = xmlDataSet.getTable(tableName);
579 
580                 // verify row count
581                 assertEquals("row count", xmlTable.getRowCount(),
582                         databaseTable.getRowCount());
583 
584                 // for each table row
585                 for (int j = 0; j < databaseTable.getRowCount(); j++)
586                 {
587                     // verify first column values
588                     Object expected = xmlTable.getValue(j, columns[0].getColumnName());
589                     Object actual = databaseTable.getValue(j, columns[0].getColumnName());
590 
591                     assertEquals(tableName + "." + columns[0].getColumnName(),
592                             expected, actual);
593 
594                     // all remaining columns should be null except mssql server timestamp column which is of type binary.
595                     for (int k = 1; k < columns.length; k++)
596                     {
597                         String columnName = columns[k].getColumnName();
598                         assertEquals(tableName + "." + columnName,
599                                 null, databaseTable.getValue(j, columnName));
600                     }
601                 }
602             }
603         }
604 
605     }
606 
607     public void testDefaultValues() throws Exception
608     {
609         String schemaName = "schema";
610         String tableName = "table";
611         String[] expected = {
612             "insert into schema.table (c1, c3, c4) values (NULL, NULL, NULL)"
613         };
614   
615         // setup table
616         Column[] columns = new Column[]{
617             new Column("c1", DataType.NUMERIC, Column.NO_NULLS), // Disallow null, no default
618             new Column("c2", DataType.NUMERIC, DataType.NUMERIC.toString(), Column.NO_NULLS, "2"), // Disallow null, default
619             new Column("c3", DataType.NUMERIC, Column.NULLABLE), // Allow null, no default
620             new Column("c4", DataType.NUMERIC, DataType.NUMERIC.toString(), Column.NULLABLE, "4"), // Allow null, default
621         };
622         DefaultTable table = new DefaultTable(tableName, columns);
623         table.addRow(new Object[]{null, null, null, null});
624         IDataSet dataSet = new DefaultDataSet(table);
625   
626         // setup mock objects
627         MockBatchStatement statement = new MockBatchStatement();
628         statement.addExpectedBatchStrings(expected);
629         statement.setExpectedExecuteBatchCalls(1);
630         statement.setExpectedClearBatchCalls(1);
631         statement.setExpectedCloseCalls(1);
632   
633         MockStatementFactory factory = new MockStatementFactory();
634         factory.setExpectedCreatePreparedStatementCalls(1);
635         factory.setupStatement(statement);
636   
637         MockDatabaseConnection connection = new MockDatabaseConnection();
638         connection.setupDataSet(dataSet);
639         connection.setupSchema(schemaName);
640         connection.setupStatementFactory(factory);
641         connection.setExpectedCloseCalls(0);
642   
643         // execute operation
644         new InsertOperation().execute(connection, dataSet);
645   
646         statement.verify();
647         factory.verify();
648         connection.verify();
649     }
650 
651     public void testExecute() throws Exception
652     {
653         Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
654         IDataSet dataSet = new XmlDataSet(in);
655 
656         testExecute(dataSet);
657     }
658 
659     public void testExecuteCaseInsensitive() throws Exception
660     {
661         Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
662         IDataSet dataSet = new XmlDataSet(in);
663 
664         testExecute(new LowerCaseDataSet(dataSet));
665     }
666 
667     public void testExecuteForwardOnly() throws Exception
668     {
669         Reader in = TestUtils.getFileReader("xml/insertOperationTest.xml");
670         IDataSet dataSet = new XmlDataSet(in);
671 
672         testExecute(new ForwardOnlyDataSet(dataSet));
673     }
674 
675     private void testExecute(IDataSet dataSet) throws Exception, SQLException
676     {
677         ITable[] tablesBefore = DataSetUtils.getTables(_connection.createDataSet());
678         DatabaseOperation.INSERT.execute(_connection, dataSet);
679         ITable[] tablesAfter = DataSetUtils.getTables(_connection.createDataSet());
680 
681         assertEquals("table count", tablesBefore.length, tablesAfter.length);
682         for (int i = 0; i < tablesBefore.length; i++)
683         {
684             ITable table = tablesBefore[i];
685             String name = table.getTableMetaData().getTableName();
686 
687 
688             if (name.startsWith("EMPTY"))
689             {
690                 assertEquals(name + "before", 0, table.getRowCount());
691             }
692         }
693 
694         for (int i = 0; i < tablesAfter.length; i++)
695         {
696             ITable table = tablesAfter[i];
697             String name = table.getTableMetaData().getTableName();
698 
699             if (name.startsWith("EMPTY"))
700             {
701                 if (dataSet instanceof ForwardOnlyDataSet)
702                 {
703                     assertTrue(name, table.getRowCount() > 0);
704                 }
705                 else
706                 {
707                     SortedTable expectedTable = new SortedTable(
708                             dataSet.getTable(name), dataSet.getTable(name).getTableMetaData());
709                     SortedTable actualTable = new SortedTable(table);
710                     Assertion.assertEquals(expectedTable, actualTable);
711                 }
712             }
713         }
714     }
715 }
716 
717 
718 
719 
720 
721 
722 
723 
724 
725