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.database;
23  
24  import org.dbunit.DatabaseEnvironment;
25  import org.dbunit.dataset.AbstractDataSetTest;
26  import org.dbunit.dataset.IDataSet;
27  import org.dbunit.dataset.ITable;
28  import org.dbunit.dataset.NoSuchColumnException;
29  import org.dbunit.operation.DatabaseOperation;
30  
31  /**
32   * @author Manuel Laflamme
33   * @version $Revision: 1137 $
34   * @since Feb 18, 2002
35   */
36  public class QueryDataSetIT extends AbstractDataSetTest
37  {
38      private IDatabaseConnection _connection;
39  
40      public QueryDataSetIT(String s)
41      {
42          super(s);
43      }
44  
45      ////////////////////////////////////////////////////////////////////////////
46      // TestCase class
47  
48      protected void setUp() throws Exception
49      {
50          super.setUp();
51  
52          DatabaseEnvironment env = DatabaseEnvironment.getInstance();
53          _connection = env.getConnection();
54  
55          DatabaseOperation.CLEAN_INSERT.execute(_connection, env.getInitDataSet());
56      }
57  
58      protected void tearDown() throws Exception
59      {
60          super.tearDown();
61  
62          _connection = null;
63      }
64  
65      ////////////////////////////////////////////////////////////////////////////
66      // AbstractDataSetTest class
67  
68      protected IDataSet createDataSet() throws Exception
69      {
70          String[] names = getExpectedNames();
71  
72          QueryDataSet dataSet = new QueryDataSet(_connection);
73          for (int i = 0; i < names.length; i++)
74          {
75              String name = names[i];
76              String query = "select * from " + name;
77              dataSet.addTable(name, query);
78  /*
79              if (i % 2 == 0)
80              {
81                  String query = "select * from " + name;
82                  dataSet.addTable(name, query);
83              }
84              else
85              {
86                  dataSet.addTable(name);
87              }
88  */
89          }
90          return dataSet;
91      }
92  
93      protected IDataSet createDuplicateDataSet() throws Exception
94      {
95          QueryDataSet dataSet = new QueryDataSet(_connection);
96          String[] names = getExpectedDuplicateNames();
97  
98          // first table expect 1 row
99          String queryOneRow = "select * from only_pk_table";
100         dataSet.addTable(names[0], queryOneRow);
101 
102         // second table expect 0 row
103         String queryNoRow = "select * from empty_table";
104         dataSet.addTable(names[1], queryNoRow);
105 
106         // third table expect 2 row
107         String queryTwoRow = "select * from pk_table where PK0=0 or PK0=1";
108         dataSet.addTable(names[2], queryTwoRow);
109 
110         return dataSet;
111     }
112 
113     protected IDataSet createMultipleCaseDuplicateDataSet() throws Exception {
114         QueryDataSet dataSet = new QueryDataSet(_connection);
115         String[] names = getExpectedDuplicateNames();
116 
117         // first table expect 1 row
118         String queryOneRow = "select * from only_pk_table";
119         dataSet.addTable(names[0], queryOneRow);
120 
121         // second table expect 0 row
122         String queryNoRow = "select * from empty_table";
123         dataSet.addTable(names[1], queryNoRow);
124 
125         // third table expect 2 row
126         String queryTwoRow = "select * from pk_table where PK0=0 or PK0=1";
127         dataSet.addTable(names[2].toLowerCase(), queryTwoRow); // lowercase table name which should fail as well
128 
129         return dataSet;
130     }
131 
132 
133     ////////////////////////////////////////////////////////////////////////////
134     // Test methods
135 
136     public void testGetSelectPartialData() throws Exception
137     {
138 
139         QueryDataSet ptds = new QueryDataSet(_connection);
140         ptds.addTable("PK_TABLE", "SELECT PK0, PK1 FROM PK_TABLE where PK0 = 0");
141 
142         ITable table = ptds.getTable("PK_TABLE");
143         assertEquals("", "0", table.getValue(0, "PK0").toString());
144         assertEquals("", "1", new String(table.getRowCount() + ""));
145 
146     }
147 
148     public void testGetAllColumnsWithStar() throws Exception
149     {
150 
151         QueryDataSet ptds = new QueryDataSet(_connection);
152         ptds.addTable("PK_TABLE", "SELECT * FROM PK_TABLE where PK0 = 0");
153 
154         ITable table = ptds.getTable("PK_TABLE");
155         assertEquals("", "0", table.getValue(0, "PK0").toString());
156         assertEquals("", "1", new String(table.getRowCount() + ""));
157 
158     }
159 
160     public void testGetAllRowsSingleColumn() throws Exception
161     {
162 
163         QueryDataSet ptds = new QueryDataSet(_connection);
164         ptds.addTable("PK_TABLE", "SELECT PK0 FROM PK_TABLE");
165 
166         ITable table = ptds.getTable("PK_TABLE");
167         assertEquals("", "0", table.getValue(0, "PK0").toString());
168         assertEquals("", "3", new String(table.getRowCount() + ""));
169     }
170 
171 
172     public void testOnlySpecifiedColumnsReturned() throws Exception
173     {
174 
175         QueryDataSet ptds = new QueryDataSet(_connection);
176         ptds.addTable("PK_TABLE", "SELECT PK0 FROM PK_TABLE");
177 
178         ITable table = ptds.getTable("PK_TABLE");
179         assertEquals("", "0", table.getValue(0, "PK0").toString());
180 
181         try
182         {
183             table.getValue(0, "PK1").toString();
184             fail("Should not have reached here, we should have thrown a NoSuchColumnException");
185         }
186         catch (NoSuchColumnException nsce)
187         {
188             String errorMsg = "org.dbunit.dataset.NoSuchColumnException: PK_TABLE.PK1";
189             assertTrue("Find text:" + errorMsg, nsce.toString().indexOf(errorMsg) >= 0);
190         }
191     }
192 
193     public void testGetSelectPartialData2() throws Exception
194     {
195 
196         QueryDataSet ptds = new QueryDataSet(_connection);
197         ptds.addTable("SECOND_TABLE",
198                 "SELECT * FROM SECOND_TABLE where COLUMN0='row 0 col 0'");
199 
200         ITable table = ptds.getTable("SECOND_TABLE");
201         assertEquals("", "row 0 col 0", table.getValue(0, "COLUMN0").toString());
202         assertEquals("", "row 0 col 3", table.getValue(0, "COLUMN3").toString());
203         assertEquals("", "1", new String(table.getRowCount() + ""));
204 
205     }
206 
207     public void testCombinedWhere() throws Exception
208     {
209 
210         QueryDataSet ptds = new QueryDataSet(_connection);
211         ptds.addTable("SECOND_TABLE",
212                 "SELECT COLUMN0, COLUMN3 FROM SECOND_TABLE where COLUMN0='row 0 col 0' and COLUMN2='row 0 col 2'");
213 
214         ITable table = ptds.getTable("SECOND_TABLE");
215         assertEquals("", "row 0 col 0", table.getValue(0, "COLUMN0").toString());
216         assertEquals("", "row 0 col 3", table.getValue(0, "COLUMN3").toString());
217         assertEquals("", "1", new String(table.getRowCount() + ""));
218 
219     }
220 
221     public void testMultipleTables() throws Exception
222     {
223         ITable table = null;
224 
225         QueryDataSet ptds = new QueryDataSet(_connection);
226         ptds.addTable("SECOND_TABLE",
227                 "SELECT * from SECOND_TABLE where COLUMN0='row 0 col 0' and COLUMN2='row 0 col 2'");
228         ptds.addTable("PK_TABLE",
229                 "SELECT * FROM PK_TABLE where PK0 = 0");
230 
231         table = ptds.getTable("SECOND_TABLE");
232         assertEquals("", "row 0 col 0", table.getValue(0, "COLUMN0").toString());
233         assertEquals("", "row 0 col 3", table.getValue(0, "COLUMN3").toString());
234         assertEquals("", "1", new String(table.getRowCount() + ""));
235 
236         table = ptds.getTable("PK_TABLE");
237         assertEquals("", "0", table.getValue(0, "PK0").toString());
238         assertEquals("", "1", new String(table.getRowCount() + ""));
239 
240     }
241 
242     public void testMultipleTablesWithMissingWhere() throws Exception
243     {
244         QueryDataSet ptds = new QueryDataSet(_connection);
245         ptds.addTable("SECOND_TABLE",
246                 "SELECT * from SECOND_TABLE where COLUMN0='row 0 col 0' and COLUMN2='row 0 col 2'");
247         ptds.addTable("PK_TABLE", null);
248     }
249 }
250 
251 
252 
253 
254 
255 
256 
257 
258 
259 
260