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 17 package tests.java.sql; 18 19 import dalvik.annotation.KnownFailure; 20 21 import java.sql.Connection; 22 import java.sql.DatabaseMetaData; 23 import java.sql.PreparedStatement; 24 import java.sql.ResultSet; 25 import java.sql.SQLException; 26 import java.sql.Statement; 27 28 import tests.support.DatabaseCreator; 29 import tests.support.Support_SQL; 30 31 import junit.extensions.TestSetup; 32 import junit.framework.Test; 33 import junit.framework.TestCase; 34 import junit.framework.TestSuite; 35 36 public class UpdateFunctionalityTest2 extends TestCase { 37 38 private static Connection conn = null; 39 40 private static Statement statement = null; 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.fillParentTable(conn); 53 DatabaseCreator.fillSimpleTable3(conn); 54 DatabaseCreator.fillSimpleTable1(conn); 55 } 56 57 public void tearDown() throws Exception { 58 deleteTestTables(); 59 statement.close(); 60 conn.close(); 61 super.tearDown(); 62 } 63 64 private 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.PARENT_TABLE)) { 72 statement 73 .execute(DatabaseCreator.DROP_TABLE_PARENT); 74 } else if (tableName 75 .equals(DatabaseCreator.FKCASCADE_TABLE)) { 76 statement 77 .execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 78 } else if (tableName 79 .equals(DatabaseCreator.FKSTRICT_TABLE)) { 80 statement 81 .execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 82 } else if (tableName 83 .equals(DatabaseCreator.SIMPLE_TABLE1)) { 84 statement 85 .execute(DatabaseCreator.DROP_TABLE_SIMPLE1); 86 } else if (tableName 87 .equals(DatabaseCreator.SIMPLE_TABLE3)) { 88 statement 89 .execute(DatabaseCreator.DROP_TABLE_SIMPLE3); 90 } else if (tableName 91 .equals(DatabaseCreator.TEST_TABLE5)) { 92 statement.execute(DatabaseCreator.DROP_TABLE5); 93 } 94 } 95 userTab.close(); 96 statement.execute(DatabaseCreator.CREATE_TABLE_PARENT); 97 statement.execute(DatabaseCreator.CREATE_TABLE_FKSTRICT); 98 statement.execute(DatabaseCreator.CREATE_TABLE_FKCASCADE); 99 statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE3); 100 statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE1); 101 statement.execute(DatabaseCreator.CREATE_TABLE5); 102 } catch (SQLException e) { 103 fail("Unexpected SQLException " + e.toString()); 104 } 105 } 106 107 private void deleteTestTables() { 108 try { 109 statement.execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 110 statement.execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 111 statement.execute(DatabaseCreator.DROP_TABLE_PARENT); 112 statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE3); 113 statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE1); 114 statement.execute(DatabaseCreator.DROP_TABLE5); 115 } catch (SQLException e) { 116 fail("Unexpected SQLException " + e.toString()); 117 } 118 } 119 120 /** 121 * UpdateFunctionalityTest2#testUpdate1(). Updates row with no 122 * referencing ones and RESTRICT action 123 */ 124 public void testUpdate1() throws SQLException { 125 DatabaseCreator.fillFKStrictTable(conn); 126 statement.execute("UPDATE " + DatabaseCreator.PARENT_TABLE 127 + " SET id = 4 WHERE id = 3"); 128 } 129 130 /** 131 * UpdateFunctionalityTest2#testUpdate2(). Attempts to update row 132 * with referencing ones and RESTRICT action - expecting SQLException 133 * 134 * TODO not supported 135 */ 136 @KnownFailure("not supported") 137 public void testUpdate2() throws SQLException { 138 DatabaseCreator.fillFKStrictTable(conn); 139 try { 140 statement.executeUpdate("UPDATE " + DatabaseCreator.PARENT_TABLE 141 + " SET id = 5 WHERE id = 1;"); 142 fail("expecting SQLException"); 143 } catch (SQLException ex) { 144 // expected 145 146 } 147 } 148 149 /** 150 * UpdateFunctionalityTest2#testUpdate3(). Deletes all referencing 151 * rows and then updates referenced one 152 */ 153 public void testUpdate3() throws SQLException { 154 DatabaseCreator.fillFKStrictTable(conn); 155 statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE 156 + " WHERE name_id = 1;"); 157 statement.execute("UPDATE " + DatabaseCreator.PARENT_TABLE 158 + " SET id = 5 WHERE id = 1;"); 159 } 160 161 /** 162 * UpdateFunctionalityTest2#testUpdate4(). Attempts to set incorrect 163 * foreign key value - expecting SQLException 164 * 165 * TODO foreign key functionality is not supported 166 */ 167 @KnownFailure("not supported") 168 public void testUpdate4() throws SQLException { 169 DatabaseCreator.fillFKStrictTable(conn); 170 try { 171 statement.executeUpdate("UPDATE " + DatabaseCreator.FKSTRICT_TABLE 172 + " SET name_id = 6 WHERE name_id = 2"); 173 fail("expecting SQLException"); 174 } catch (SQLException ex) { 175 // expected 176 } 177 } 178 179 /** 180 * UpdateFunctionalityTest2#testUpdate5(). Updates row with 181 * referencing ones and CASCADE action - expecting that all 182 * referencing rows will also be updated 183 */ 184 public void testUpdate5() throws SQLException { 185 DatabaseCreator.fillFKCascadeTable(conn); 186 statement.executeUpdate("UPDATE " + DatabaseCreator.PARENT_TABLE 187 + " SET id = 5 WHERE id = 1;"); 188 189 ResultSet r = statement.executeQuery("SELECT COUNT(*) " + "FROM " 190 + DatabaseCreator.FKCASCADE_TABLE + " WHERE name_id = 1;"); 191 r.next(); 192 assertEquals("Should be 2 rows", 2, r.getInt(1)); 193 r = statement.executeQuery("SELECT COUNT(*) " + "FROM " 194 + DatabaseCreator.FKCASCADE_TABLE + " WHERE name_id = 5;"); 195 r.next(); 196 assertEquals("Should be 0 rows", 0, r.getInt(1)); 197 r.close(); 198 } 199 200 /** 201 * UpdateFunctionalityTest2#testUpdate6(). Attempts to set incorrect 202 * foreign key value to row with CASCADE action - expecting 203 * SQLException 204 * 205 * TODO Foreign key functionality is not supported 206 */ 207 @KnownFailure("not supported") 208 public void testUpdate6() throws SQLException { 209 DatabaseCreator.fillFKCascadeTable(conn); 210 try { 211 statement.executeUpdate("UPDATE " + DatabaseCreator.FKCASCADE_TABLE 212 + " SET name_id = 6 WHERE name_id = 2"); 213 fail("expecting SQLException"); 214 } catch (SQLException ex) { 215 // expected 216 } 217 } 218 219 /** 220 * UpdateFunctionalityTest2#testUpdate7(). Updates table using 221 * subquery in WHERE clause 222 * 223 * TODO Foreign key functionality is not supported 224 */ 225 @KnownFailure("not supported") 226 public void testUpdate7() throws SQLException { 227 228 DatabaseCreator.fillFKStrictTable(conn); 229 statement.executeUpdate("UPDATE " + DatabaseCreator.FKSTRICT_TABLE 230 + " SET value = 'updated' WHERE name_id = ANY (SELECT id FROM " 231 + DatabaseCreator.PARENT_TABLE + " WHERE id > 1)"); 232 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 233 + DatabaseCreator.FKSTRICT_TABLE + " WHERE value = 'updated';"); 234 r.next(); 235 assertEquals("Should be 1 row", 1, r.getInt(1)); 236 r.close(); 237 } 238 239 /** 240 * UpdateFunctionalityTest2#testUpdate8(). Updates table using scalar 241 * subquery as new field value 242 */ 243 public void testUpdate8() throws SQLException { 244 statement.executeUpdate("UPDATE " + DatabaseCreator.SIMPLE_TABLE3 245 + " SET speed = (SELECT MAX(speed) FROM " 246 + DatabaseCreator.SIMPLE_TABLE1 247 + ") WHERE id = (SELECT id FROM " 248 + DatabaseCreator.SIMPLE_TABLE1 249 + " WHERE speed = (SELECT MAX(speed) FROM " 250 + DatabaseCreator.SIMPLE_TABLE1 + "))"); 251 ResultSet r = statement.executeQuery("SELECT id FROM " 252 + DatabaseCreator.SIMPLE_TABLE3 253 + " WHERE speed = (SELECT MAX(speed) FROM " 254 + DatabaseCreator.SIMPLE_TABLE1 + ");"); 255 r.next(); 256 assertEquals("Incorrect id updated", 1, r.getInt(1)); 257 r.close(); 258 } 259 260 /** 261 * UpdateFunctionalityTest2#testUpdate9(). Updates table using 262 * PreparedStatement 263 */ 264 public void testUpdate9() throws SQLException { 265 DatabaseCreator.fillTestTable5(conn); 266 PreparedStatement stat = conn.prepareStatement("UPDATE " 267 + DatabaseCreator.TEST_TABLE5 268 + " SET testValue = ? WHERE testID = ?"); 269 stat.setString(1, "1"); 270 stat.setInt(2, 1); 271 stat.execute(); 272 stat.setString(1, "2"); 273 stat.setInt(2, 2); 274 stat.execute(); 275 ResultSet r = statement.executeQuery("SELECT testId, testValue FROM " 276 + DatabaseCreator.TEST_TABLE5 277 + " WHERE testID < 3 ORDER BY testID"); 278 while (r.next()) { 279 assertEquals("Incorrect value was returned", new Integer(r 280 .getInt(1)).toString(), r.getString(2)); 281 } 282 r.close(); 283 stat.close(); 284 } 285 } 286
