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 dalvik.annotation.KnownFailure; 20import dalvik.annotation.TestTargetClass; 21import dalvik.annotation.TestTargets; 22import dalvik.annotation.TestLevel; 23import dalvik.annotation.TestTargetNew; 24 25import java.sql.Connection; 26import java.sql.DatabaseMetaData; 27import java.sql.PreparedStatement; 28import java.sql.ResultSet; 29import java.sql.SQLException; 30import java.sql.Statement; 31 32import tests.support.DatabaseCreator; 33import tests.support.Support_SQL; 34 35import junit.extensions.TestSetup; 36import junit.framework.Test; 37import junit.framework.TestCase; 38import junit.framework.TestSuite; 39 40@TestTargetClass(Statement.class) 41public class UpdateFunctionalityTest2 extends TestCase { 42 43 private static Connection conn = null; 44 45 private static Statement statement = null; 46 47 public void setUp() throws Exception { 48 super.setUp(); 49 Support_SQL.loadDriver(); 50 try { 51 conn = Support_SQL.getConnection(); 52 statement = conn.createStatement(); 53 createTestTables(); 54 } catch (SQLException e) { 55 fail("Unexpected SQLException " + e.toString()); 56 } 57 DatabaseCreator.fillParentTable(conn); 58 DatabaseCreator.fillSimpleTable3(conn); 59 DatabaseCreator.fillSimpleTable1(conn); 60 } 61 62 public void tearDown() throws Exception { 63 deleteTestTables(); 64 statement.close(); 65 conn.close(); 66 super.tearDown(); 67 } 68 69 private void createTestTables() { 70 try { 71 DatabaseMetaData meta = conn.getMetaData(); 72 ResultSet userTab = meta.getTables(null, null, null, null); 73 74 while (userTab.next()) { 75 String tableName = userTab.getString("TABLE_NAME"); 76 if (tableName.equals(DatabaseCreator.PARENT_TABLE)) { 77 statement 78 .execute(DatabaseCreator.DROP_TABLE_PARENT); 79 } else if (tableName 80 .equals(DatabaseCreator.FKCASCADE_TABLE)) { 81 statement 82 .execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 83 } else if (tableName 84 .equals(DatabaseCreator.FKSTRICT_TABLE)) { 85 statement 86 .execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 87 } else if (tableName 88 .equals(DatabaseCreator.SIMPLE_TABLE1)) { 89 statement 90 .execute(DatabaseCreator.DROP_TABLE_SIMPLE1); 91 } else if (tableName 92 .equals(DatabaseCreator.SIMPLE_TABLE3)) { 93 statement 94 .execute(DatabaseCreator.DROP_TABLE_SIMPLE3); 95 } else if (tableName 96 .equals(DatabaseCreator.TEST_TABLE5)) { 97 statement.execute(DatabaseCreator.DROP_TABLE5); 98 } 99 } 100 userTab.close(); 101 statement.execute(DatabaseCreator.CREATE_TABLE_PARENT); 102 statement.execute(DatabaseCreator.CREATE_TABLE_FKSTRICT); 103 statement.execute(DatabaseCreator.CREATE_TABLE_FKCASCADE); 104 statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE3); 105 statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE1); 106 statement.execute(DatabaseCreator.CREATE_TABLE5); 107 } catch (SQLException e) { 108 fail("Unexpected SQLException " + e.toString()); 109 } 110 } 111 112 private void deleteTestTables() { 113 try { 114 statement.execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 115 statement.execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 116 statement.execute(DatabaseCreator.DROP_TABLE_PARENT); 117 statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE3); 118 statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE1); 119 statement.execute(DatabaseCreator.DROP_TABLE5); 120 } catch (SQLException e) { 121 fail("Unexpected SQLException " + e.toString()); 122 } 123 } 124 125 /** 126 * @tests UpdateFunctionalityTest2#testUpdate1(). Updates row with no 127 * referencing ones and RESTRICT action 128 */ 129 @TestTargetNew( 130 level = TestLevel.PARTIAL_COMPLETE, 131 notes = "Functionality test: Updates row with no referencing ones and RESTRICT action", 132 method = "execute", 133 args = {java.lang.String.class} 134 ) 135 public void testUpdate1() throws SQLException { 136 DatabaseCreator.fillFKStrictTable(conn); 137 statement.execute("UPDATE " + DatabaseCreator.PARENT_TABLE 138 + " SET id = 4 WHERE id = 3"); 139 } 140 141 /** 142 * @tests UpdateFunctionalityTest2#testUpdate2(). Attempts to update row 143 * with referencing ones and RESTRICT action - expecting SQLException 144 * 145 * TODO not supported 146 */ 147 @TestTargetNew( 148 level = TestLevel.PARTIAL_COMPLETE, 149 notes = "Attempts to update row with referencing ones and RESTRICT action - expecting SQLException", 150 method = "execute", 151 args = {java.lang.String.class} 152 ) 153 @KnownFailure("not supported") 154 public void testUpdate2() throws SQLException { 155 DatabaseCreator.fillFKStrictTable(conn); 156 try { 157 statement.executeUpdate("UPDATE " + DatabaseCreator.PARENT_TABLE 158 + " SET id = 5 WHERE id = 1;"); 159 fail("expecting SQLException"); 160 } catch (SQLException ex) { 161 // expected 162 163 } 164 } 165 166 /** 167 * @tests UpdateFunctionalityTest2#testUpdate3(). Deletes all referencing 168 * rows and then updates referenced one 169 */ 170 @TestTargetNew( 171 level = TestLevel.PARTIAL_COMPLETE, 172 notes = "Functionality test: Deletes all referencing rows and then updates referenced one", 173 method = "execute", 174 args = {java.lang.String.class} 175 ) 176 public void testUpdate3() throws SQLException { 177 DatabaseCreator.fillFKStrictTable(conn); 178 statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE 179 + " WHERE name_id = 1;"); 180 statement.execute("UPDATE " + DatabaseCreator.PARENT_TABLE 181 + " SET id = 5 WHERE id = 1;"); 182 } 183 184 /** 185 * @tests UpdateFunctionalityTest2#testUpdate4(). Attempts to set incorrect 186 * foreign key value - expecting SQLException 187 * 188 * TODO foreign key functionality is not supported 189 */ 190 @TestTargetNew( 191 level = TestLevel.PARTIAL_COMPLETE, 192 notes = "Attempts to set incorrect foreign key value - expecting SQLException", 193 method = "executeUpdate", 194 args = {java.lang.String.class} 195 ) 196 @KnownFailure("not supported") 197 public void testUpdate4() throws SQLException { 198 DatabaseCreator.fillFKStrictTable(conn); 199 try { 200 statement.executeUpdate("UPDATE " + DatabaseCreator.FKSTRICT_TABLE 201 + " SET name_id = 6 WHERE name_id = 2"); 202 fail("expecting SQLException"); 203 } catch (SQLException ex) { 204 // expected 205 } 206 } 207 208 /** 209 * @tests UpdateFunctionalityTest2#testUpdate5(). Updates row with 210 * referencing ones and CASCADE action - expecting that all 211 * referencing rows will also be updated 212 */ 213 @TestTargets({ 214 @TestTargetNew( 215 level = TestLevel.PARTIAL_COMPLETE, 216 notes = "Functionality test: Updates row with referencing ones and CASCADE action - expecting that all referencing rows will also be updated", 217 method = "executeUpdate", 218 args = {java.lang.String.class} 219 ), 220 @TestTargetNew( 221 level = TestLevel.PARTIAL_COMPLETE, 222 notes = "Functionality test: Updates row with referencing ones and CASCADE action - expecting that all referencing rows will also be updated", 223 method = "executeQuery", 224 args = {java.lang.String.class} 225 ) 226 }) 227 public void testUpdate5() throws SQLException { 228 DatabaseCreator.fillFKCascadeTable(conn); 229 statement.executeUpdate("UPDATE " + DatabaseCreator.PARENT_TABLE 230 + " SET id = 5 WHERE id = 1;"); 231 232 ResultSet r = statement.executeQuery("SELECT COUNT(*) " + "FROM " 233 + DatabaseCreator.FKCASCADE_TABLE + " WHERE name_id = 1;"); 234 r.next(); 235 assertEquals("Should be 2 rows", 2, r.getInt(1)); 236 r = statement.executeQuery("SELECT COUNT(*) " + "FROM " 237 + DatabaseCreator.FKCASCADE_TABLE + " WHERE name_id = 5;"); 238 r.next(); 239 assertEquals("Should be 0 rows", 0, r.getInt(1)); 240 r.close(); 241 } 242 243 /** 244 * @tests UpdateFunctionalityTest2#testUpdate6(). Attempts to set incorrect 245 * foreign key value to row with CASCADE action - expecting 246 * SQLException 247 * 248 * TODO Foreign key functionality is not supported 249 */ 250 @TestTargetNew( 251 level = TestLevel.PARTIAL_COMPLETE, 252 notes = "Functionality test: Attempts to set incorrect\n" + 253 "foreign key value to row with CASCADE action - expecting SQLException: not supported", 254 method = "executeUpdate", 255 args = {java.lang.String.class} 256 ) 257 @KnownFailure("not supported") 258 public void testUpdate6() throws SQLException { 259 DatabaseCreator.fillFKCascadeTable(conn); 260 try { 261 statement.executeUpdate("UPDATE " + DatabaseCreator.FKCASCADE_TABLE 262 + " SET name_id = 6 WHERE name_id = 2"); 263 fail("expecting SQLException"); 264 } catch (SQLException ex) { 265 // expected 266 } 267 } 268 269 /** 270 * @tests UpdateFunctionalityTest2#testUpdate7(). Updates table using 271 * subquery in WHERE clause 272 * 273 * TODO Foreign key functionality is not supported 274 */ 275 @TestTargets({ 276 @TestTargetNew( 277 level = TestLevel.PARTIAL_COMPLETE, 278 notes = "Functionality test: Updates table using subquery in WHERE clause. Not supported: FK", 279 method = "executeQuery", 280 args = {java.lang.String.class} 281 ), 282 @TestTargetNew( 283 level = TestLevel.PARTIAL_COMPLETE, 284 notes = "Functionality test: Updates table using subquery in WHERE clause. Not supported: FK.", 285 method = "executeUpdate", 286 args = {java.lang.String.class} 287 ) 288 }) 289 @KnownFailure("not supported") 290 public void testUpdate7() throws SQLException { 291 292 DatabaseCreator.fillFKStrictTable(conn); 293 statement.executeUpdate("UPDATE " + DatabaseCreator.FKSTRICT_TABLE 294 + " SET value = 'updated' WHERE name_id = ANY (SELECT id FROM " 295 + DatabaseCreator.PARENT_TABLE + " WHERE id > 1)"); 296 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 297 + DatabaseCreator.FKSTRICT_TABLE + " WHERE value = 'updated';"); 298 r.next(); 299 assertEquals("Should be 1 row", 1, r.getInt(1)); 300 r.close(); 301 } 302 303 /** 304 * @tests UpdateFunctionalityTest2#testUpdate8(). Updates table using scalar 305 * subquery as new field value 306 */ 307 @TestTargets({ 308 @TestTargetNew( 309 level = TestLevel.PARTIAL_COMPLETE, 310 notes = "Functionality test: Updates table using scalar subquery as new field value", 311 method = "executeQuery", 312 args = {java.lang.String.class} 313 ), 314 @TestTargetNew( 315 level = TestLevel.PARTIAL_COMPLETE, 316 notes = "Functionality test: Updates table using scalar subquery as new field value", 317 method = "executeUpdate", 318 args = {java.lang.String.class} 319 ) 320 }) 321 public void testUpdate8() throws SQLException { 322 statement.executeUpdate("UPDATE " + DatabaseCreator.SIMPLE_TABLE3 323 + " SET speed = (SELECT MAX(speed) FROM " 324 + DatabaseCreator.SIMPLE_TABLE1 325 + ") WHERE id = (SELECT id FROM " 326 + DatabaseCreator.SIMPLE_TABLE1 327 + " WHERE speed = (SELECT MAX(speed) FROM " 328 + DatabaseCreator.SIMPLE_TABLE1 + "))"); 329 ResultSet r = statement.executeQuery("SELECT id FROM " 330 + DatabaseCreator.SIMPLE_TABLE3 331 + " WHERE speed = (SELECT MAX(speed) FROM " 332 + DatabaseCreator.SIMPLE_TABLE1 + ");"); 333 r.next(); 334 assertEquals("Incorrect id updated", 1, r.getInt(1)); 335 r.close(); 336 } 337 338 /** 339 * @tests UpdateFunctionalityTest2#testUpdate9(). Updates table using 340 * PreparedStatement 341 */ 342 @TestTargetNew( 343 level = TestLevel.PARTIAL_COMPLETE, 344 notes = "Functionality test: Updates table using PreparedStatement", 345 method = "executeQuery", 346 args = {java.lang.String.class} 347 ) 348 public void testUpdate9() throws SQLException { 349 DatabaseCreator.fillTestTable5(conn); 350 PreparedStatement stat = conn.prepareStatement("UPDATE " 351 + DatabaseCreator.TEST_TABLE5 352 + " SET testValue = ? WHERE testID = ?"); 353 stat.setString(1, "1"); 354 stat.setInt(2, 1); 355 stat.execute(); 356 stat.setString(1, "2"); 357 stat.setInt(2, 2); 358 stat.execute(); 359 ResultSet r = statement.executeQuery("SELECT testId, testValue FROM " 360 + DatabaseCreator.TEST_TABLE5 361 + " WHERE testID < 3 ORDER BY testID"); 362 while (r.next()) { 363 assertEquals("Incorrect value was returned", new Integer(r 364 .getInt(1)).toString(), r.getString(2)); 365 } 366 r.close(); 367 stat.close(); 368 } 369} 370