1/*
2 * Copyright (C) 2007 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *     http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17package tests.java.sql;
18
19import java.math.BigDecimal;
20import java.sql.Connection;
21import java.sql.DatabaseMetaData;
22import java.sql.ResultSet;
23import java.sql.SQLException;
24import java.sql.Statement;
25import java.util.HashSet;
26
27import tests.support.DatabaseCreator;
28import tests.support.Support_SQL;
29import junit.extensions.TestSetup;
30import junit.framework.Test;
31import junit.framework.TestCase;
32import junit.framework.TestSuite;
33
34public class UpdateFunctionalityTest extends TestCase {
35
36    private static final int numberOfRecords = 20;
37
38    private static Connection conn;
39
40    private static Statement statement;
41
42    public void setUp() throws Exception {
43        super.setUp();
44        Support_SQL.loadDriver();
45        try {
46            conn = Support_SQL.getConnection();
47            statement = conn.createStatement();
48            createTestTables();
49        } catch (SQLException e) {
50            fail("Unexpected SQLException " + e.toString());
51        }
52        DatabaseCreator.fillTestTable1(conn, numberOfRecords);
53        DatabaseCreator.fillTestTable2(conn, numberOfRecords);
54    }
55
56    public void tearDown() throws Exception {
57        deleteTestTables();
58        statement.close();
59        conn.close();
60
61        super.tearDown();
62    }
63
64    protected void createTestTables() {
65        try {
66            DatabaseMetaData meta = conn.getMetaData();
67            ResultSet userTab = meta.getTables(null, null, null, null);
68
69            while (userTab.next()) {
70                String tableName = userTab.getString("TABLE_NAME");
71                if (tableName.equals(DatabaseCreator.TEST_TABLE1)) {
72                    statement.execute(DatabaseCreator.DROP_TABLE1);
73                } else if (tableName
74                        .equals(DatabaseCreator.TEST_TABLE2)) {
75                    statement.execute(DatabaseCreator.DROP_TABLE2);
76                } else if (tableName
77                        .equals(DatabaseCreator.TEST_TABLE3)) {
78                    statement.execute(DatabaseCreator.DROP_TABLE3);
79                }
80            }
81            userTab.close();
82
83            statement.execute(DatabaseCreator.CREATE_TABLE3);
84            statement.execute(DatabaseCreator.CREATE_TABLE2);
85            statement.execute(DatabaseCreator.CREATE_TABLE1);
86
87        } catch (SQLException e) {
88            fail("Unexpected SQLException " + e.toString());
89        }
90    }
91
92    protected void deleteTestTables() {
93        try {
94            statement.execute(DatabaseCreator.DROP_TABLE1);
95            statement.execute(DatabaseCreator.DROP_TABLE2);
96            statement.execute(DatabaseCreator.DROP_TABLE3);
97        } catch (SQLException e) {
98            fail("Unexpected SQLException " + e.toString());
99        }
100    }
101
102    /**
103     * UpdateFunctionalityTest#testUpdate1(). Updates all values in one
104     *        column in the table
105     */
106    public void testUpdate1() {
107        String newValue = "newValue";
108        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
109                + " SET field1='" + newValue + "'";
110        try {
111            int num = statement.executeUpdate(updateQuery);
112            assertEquals("Not all records in the database were updated",
113                    numberOfRecords, num);
114            String selectQuery = "SELECT field1 FROM "
115                    + DatabaseCreator.TEST_TABLE1;
116            ResultSet result = statement.executeQuery(selectQuery);
117            while (result.next()) {
118                assertEquals("The field field1 was not updated", newValue,
119                        result.getString("field1"));
120            }
121            result.close();
122        } catch (SQLException e) {
123            fail("Unexpected exception" + e.getMessage());
124        }
125    }
126
127    /**
128     * UpdateFunctionalityTest#testUpdate2(). Updates values in one
129     *        column in the table using where condition in update command
130     */
131    public void testUpdate2() {
132        String newValue = "newValue";
133        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
134                + " SET field1='" + newValue + "' WHERE (id > 2) and (id < 10)";
135        try {
136            int num = statement.executeUpdate(updateQuery);
137            int expectedUpdated = 7;
138            assertEquals("Not all records in the database were updated",
139                    expectedUpdated, num);
140            String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1;
141            ResultSet result = statement.executeQuery(selectQuery);
142            while (result.next()) {
143                int id = result.getInt("id");
144                String field1 = result.getString("field1");
145                if ((id > 2) && (id < 10)) {
146                    assertEquals("The field field1 was not updated", newValue,
147                            field1);
148                } else {
149                    assertEquals("The field field1 was not updated",
150                            DatabaseCreator.defaultString + id, field1);
151                }
152            }
153            result.close();
154        } catch (SQLException e) {
155            fail("Unexpected exception" + e.getMessage());
156        }
157    }
158
159    /**
160     * UpdateFunctionalityTest#testUpdate3(). Updates values in a several
161     *        columns in the table
162     */
163    public void testUpdate3() {
164        int newValue1 = -1;
165        int newValue2 = -2;
166        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
167                + " SET field2=" + newValue1 + ", field3=" + newValue2;
168        try {
169            int num = statement.executeUpdate(updateQuery);
170            assertEquals("Not all records in the database were updated",
171                    numberOfRecords, num);
172            String selectQuery = "SELECT field2, field3 FROM "
173                    + DatabaseCreator.TEST_TABLE1;
174            ResultSet result = statement.executeQuery(selectQuery);
175            while (result.next()) {
176           // TODO getBigDecimal is not supported
177//                assertEquals("The field field2 was not updated", newValue1,
178//                        result.getBigDecimal("field2").intValue());
179//                assertEquals("The field field3 was not updated", newValue2,
180//                        result.getBigDecimal("field3").intValue());
181            }
182            result.close();
183        } catch (SQLException e) {
184            fail("Unexpected exception" + e.getMessage());
185        }
186    }
187
188    /**
189     * UpdateFunctionalityTest#testUpdate4(). Updates values in a several
190     *        columns in the table using where condition in update command
191     */
192    public void testUpdate4() {
193        int newValue1 = -1;
194        int newValue2 = -2;
195        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
196                + " SET field2=" + newValue1 + ", field3=" + newValue2
197                + " WHERE id > 10";
198        try {
199            int num = statement.executeUpdate(updateQuery);
200            int expectedUpdated = 9;
201            assertEquals("Not all records in the database were updated",
202                    expectedUpdated, num);
203            String selectQuery = "SELECT id, field2, field3 FROM "
204                    + DatabaseCreator.TEST_TABLE1;
205            ResultSet result = statement.executeQuery(selectQuery);
206            while (result.next()) {
207                int id = result.getInt("id");
208                // TODO getBigDecimal is not supported
209//                int value2 = result.getBigDecimal("field2").intValue();
210//                int value3 = result.getBigDecimal("field3").intValue();
211//                if (id > expectedUpdated + 1) {
212//                    assertEquals("The field field2 was not updated", newValue1,
213//                            value2);
214//                    assertEquals("The field field3 was not updated", newValue2,
215//                            value3);
216//                } else {
217//                    assertEquals("The field field2 was not updated", id, value2);
218//                    assertEquals("The field field3 was not updated", id, value3);
219//                }
220            }
221            result.close();
222        } catch (SQLException e) {
223            fail("Unexpected exception" + e.getMessage());
224        }
225    }
226
227    /**
228     * UpdateFunctionalityTest#testUpdate5(). Updates values in one
229     *        columns in the table using condition
230     */
231    public void testUpdate5() {
232        int factor = 3;
233        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
234                + " SET field2=field2 *" + factor;
235        try {
236            String selectQuery = "SELECT field2 FROM "
237                    + DatabaseCreator.TEST_TABLE1;
238            ResultSet result = statement.executeQuery(selectQuery);
239            HashSet<BigDecimal> values = new HashSet<BigDecimal>();
240            // TODO getBigDecimal is not supported
241//            while (result.next()) {
242//                values.add(BigDecimal.valueOf(result.getBigDecimal("field2")
243//                        .intValue()
244//                        * factor));
245//            }
246
247            int num = statement.executeUpdate(updateQuery);
248            assertEquals("Not all records in the database were updated",
249                    numberOfRecords, num);
250            result = statement.executeQuery(selectQuery);
251            // TODO getBigDecimal is not supported
252//            while (result.next()) {
253//                BigDecimal value = result.getBigDecimal("field2");
254//                assertTrue("Wrong value of field2 after update"
255//                        + value.intValue(), values.remove(value));
256//            }
257            assertTrue("Not all records were updated", values.isEmpty());
258
259            result.close();
260        } catch (SQLException e) {
261            fail("Unexpected exception" + e.getMessage());
262        }
263    }
264
265    /**
266     * UpdateFunctionalityTest#testUpdate6(). Sets value of field2 to
267     *        default
268     */
269    public void testUpdate6() {
270        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
271                + " SET field2='1'";
272        try {
273
274            int num = statement.executeUpdate(updateQuery);
275            assertEquals("Not all records in the database were updated",
276                    numberOfRecords, num);
277            String selectQuery = "SELECT field2 FROM "
278                    + DatabaseCreator.TEST_TABLE1;
279            ResultSet result = statement.executeQuery(selectQuery);
280            // TODO getBigDecimal is not supported
281//            while (result.next()) {
282//                assertEquals("value of field2 should be default ",
283//                        DatabaseCreator.defaultInt, result.getBigDecimal(
284//                                "field2").intValue());
285//            }
286            result.close();
287        } catch (SQLException e) {
288            fail("Unexpected exception" + e.getMessage());
289        }
290    }
291
292    /**
293     * UpdateFunctionalityTest#testUpdate7(). Updates records in the
294     *        table using subquery in update command
295     */
296    public void testUpdate7() {
297        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
298                + " SET field2='1' WHERE id < ( SELECT COUNT(*) FROM "
299                + DatabaseCreator.TEST_TABLE2 + " WHERE finteger > 15)";
300        try {
301            int num = statement.executeUpdate(updateQuery);
302            int expectedUpdated = 4;
303            assertEquals("Not all records in the database were updated",
304                    expectedUpdated, num);
305            String selectQuery = "SELECT id, field2 FROM "
306                    + DatabaseCreator.TEST_TABLE1;
307            ResultSet result = statement.executeQuery(selectQuery);
308            while (result.next()) {
309           // TODO getBigDecimal is not supported
310//                int value = result.getBigDecimal("field2").intValue();
311//                int id = result.getInt("id");
312//                if (id < expectedUpdated) {
313//                    assertEquals("value of field2 should be default ",
314//                            DatabaseCreator.defaultInt, value);
315//                } else {
316//                    assertEquals("wrong value of field2", id, value);
317//                }
318            }
319            result.close();
320        } catch (SQLException e) {
321            fail("Unexpected exception" + e.getMessage());
322        }
323    }
324
325    /**
326     * UpdateFunctionalityTest#testUpdate8(). Sets value of field2 to
327     *        NULL
328     */
329    public void testUpdate8() {
330        String updateQuery = "UPDATE " + DatabaseCreator.TEST_TABLE1
331                + " SET field2=NULL";
332        try {
333
334            int num = statement.executeUpdate(updateQuery);
335            assertEquals("Not all records in the database were updated",
336                    numberOfRecords, num);
337            String selectQuery = "SELECT field2 FROM "
338                    + DatabaseCreator.TEST_TABLE1;
339            ResultSet result = statement.executeQuery(selectQuery);
340            while (result.next()) {
341                assertNull("value of field2 should be NULL", result
342                        .getObject("field2"));
343            }
344            result.close();
345        } catch (SQLException e) {
346            fail("Unexpected exception" + e.getMessage());
347        }
348    }
349}
350