Menu

#211 Cannot insert table data with self-reference in 2.4.0

closed-fixed
Bug (232)
5
2014-08-26
2008-12-01
No

I tried to implement a database schema of employees. An employee can have a supervisor. The supervisor also an employee so there is a reference from a row in the employees table to another row in employees table.

How to reproduce:
* Unpack the files in attached zip file to a directory.
* Mysql test: Change Mysql username and password. Create the database "test"
* H2 test: It may be necessary to manually download the H2 1.0.79-jar file and deploy in your local maven repository
* Run "mvn test"

Actual:
Both the Mysql and H2 test fails (but a bit differently)

In the Mysql case no data is inserted what so ever. An exception is raised:
org.dbunit.dataset.NoSuchColumnException: EMPLOYEES.SSN - (Non-uppercase input column: ssn) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.
at org.dbunit.dataset.AbstractTableMetaData.getColumnIndex(AbstractTableMetaData.java:128)
at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:89)
at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:140)
at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
at se.lesc.dbunit_ref_insert_problem.InsertMysqlTest.testInsertRef(InsertMysqlTest.java:61)

In the H2 case only 2 rows are inserted

Expected:
It should be possible to populate the database even if there is a self-reference in the table.

Source code snippets:
String createEmployeesTableSql =
"CREATE TABLE employees (" +
" ssn varchar(255) NOT NULL, " +
" firstName varchar(255) default NULL, " +
" initial varchar(255) default NULL, " +
" lastName varchar(255) default NULL, " +
" salary bigint(20) default NULL, " +
" supervisor varchar(255) default NULL, " +
" PRIMARY KEY (ssn), " +
" FOREIGN KEY (supervisor) REFERENCES employees (ssn)" +
")";

<dataset>
<employees ssn="730403" firstname="Niklas" lastname="Berglind" />
<employees ssn="720202" firstname="Bjorn" lastname="Julander" />
<employees ssn="710101" firstname="Petter" lastname="Edblom" />

<employees ssn="790630" firstname="Lennart" lastname="Schedin" supervisor="730403" />
<employees ssn="760503" firstname="Johan" lastname="Fransson" supervisor="790630" />
</dataset>

DatabaseConnection iDatabaseConnection = new DatabaseConnection(connection);
IDataSet dataSet = new FlatXmlDataSet(getClass().getResource("/employees.xml"), false, true);
DatabaseOperation.CLEAN_INSERT.execute(iDatabaseConnection, dataSet);

Reproducible in: Dbunit 2.4.0 (Windows XP)
Not reproducible in: Dbunit 2.3.0 (Windows XP)

Discussion

  • Lennart Schedin

    Lennart Schedin - 2008-12-01

    dbunit-ref-insert-problem.zip

     
  • Lennart Schedin

    Lennart Schedin - 2008-12-01

    I downloaded the source of dbunit to investigate this problem a bit more and (at least part of) the problem is even easier to reproduce. Just add the line

    assertEquals(3, table.getRowCount());
    last in
    org.dbunit.dataset.xml.FlatXmlDataSetTest.testMissingColumnAndDisableDtdMetadataAndSensing()

    When using dbunit 2.3.0 the table.getRowCount() returned 10!(?). There must have been some change between 2.3.0 and 2.4.0 with respect to parsing XML files and creating dataset.

     
  • matthias g

    matthias g - 2008-12-01

    Hi there,

    first of all thanks a lot for this detailed bugreport. The problem was introduced with 2.4.0 because of a bugreport with respect to multiple tables in a dataset having column sensing enabled (the rows have not been cleaned). I located two new problems now and added the suggested lines to the unit test to ensure that the row count & row content is okay in the future.

    I committed my bugfixes to svn rev. 901/trunk for the 2.4.1 bugfix release. I would be very grateful if you could test this using the latest jar from parabuild: http://parabuild.viewtier.com:8080/parabuild/index.htm?view=detailed&buildid=30 (click on "Results" and then on the jar file)

    Thanks again and regards,
    mat

     
  • Lennart Schedin

    Lennart Schedin - 2008-12-02

    Only 2 out of 5 rows are inserted

     
  • Lennart Schedin

    Lennart Schedin - 2008-12-02

    I tried the 2.4.1-SNAPSHOT and it solved the strange Mysql-exception. But I still have the problem that only 2 out or 5 rows from the XML file are inserted (see attached picture only_2_of_5_are_inserted.jpg).

     
  • matthias g

    matthias g - 2008-12-02

    Hm, I saw that parabuild seems to have a problem. It did not recognize my last commit - the last build is rev. 900 instead of 901 (which includes the fix).
    Since I cannot attach the snapshot here (jar is too big) I want to ask you to build the current snapshot on your own. Therefore you need to checkout the current trunk ("svn checkout https://dbunit.svn.sourceforge.net/svnroot/dbunit/trunk dbunit") and then invoke a "mvn clean package" on the commandline. You can find the jar file then in the "target" directory.

    Regards,
    mat

     
  • Lennart Schedin

    Lennart Schedin - 2008-12-03

    The latest jar from (http://parabuild.viewtier.com:8080/parabuild/build/result/30/3163/dbunit-2.4.1-SNAPSHOT.jar) worked! My test cases now passes. I made a bit more complex XML file test case and it also passed.

    Thanks for the quick response!

     
  • matthias g

    matthias g - 2008-12-03
    • assigned_to: slecallonnec --> gommma
    • status: open --> closed-fixed
     
  • matthias g

    matthias g - 2008-12-03

    Set to closed since it is resolved for 2.4.1.
    Thanks again for the detailed bug report. I wish all bug reports were as detailed as yours!

    Regards,
    mat

     

Log in to post a comment.