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 package tests.java.sql; 17 18 import dalvik.annotation.KnownFailure; 19 20 import java.sql.Connection; 21 import java.sql.DatabaseMetaData; 22 import java.sql.PreparedStatement; 23 import java.sql.ResultSet; 24 import java.sql.SQLException; 25 import java.sql.Statement; 26 27 import tests.support.DatabaseCreator; 28 import tests.support.Support_SQL; 29 30 import junit.extensions.TestSetup; 31 import junit.framework.Test; 32 import junit.framework.TestCase; 33 import junit.framework.TestSuite; 34 35 public class DeleteFunctionalityTest extends TestCase { 36 37 private static Connection conn = null; 38 39 private static Statement statement = null; 40 41 public void setUp() throws Exception { 42 super.setUp(); 43 Support_SQL.loadDriver(); 44 conn = Support_SQL.getConnection(); 45 statement = conn.createStatement(); 46 createTestTables(); 47 DatabaseCreator.fillParentTable(conn); 48 } 49 50 public void tearDown() throws Exception { 51 deleteTestTables(); 52 statement.close(); 53 conn.close(); 54 super.tearDown(); 55 } 56 57 58 public void createTestTables() { 59 try { 60 DatabaseMetaData meta = conn.getMetaData(); 61 ResultSet userTab = meta.getTables(null, null, null, null); 62 63 while (userTab.next()) { 64 String tableName = userTab.getString("TABLE_NAME"); 65 if (tableName.equals(DatabaseCreator.PARENT_TABLE)) { 66 statement 67 .execute(DatabaseCreator.DROP_TABLE_PARENT); 68 } else if (tableName 69 .equals(DatabaseCreator.FKCASCADE_TABLE)) { 70 statement 71 .execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 72 } else if (tableName 73 .equals(DatabaseCreator.FKSTRICT_TABLE)) { 74 statement 75 .execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 76 } else if (tableName 77 .equals(DatabaseCreator.TEST_TABLE5)) { 78 statement.execute(DatabaseCreator.DROP_TABLE5); 79 } 80 } 81 userTab.close(); 82 statement.execute(DatabaseCreator.CREATE_TABLE_PARENT); 83 statement.execute(DatabaseCreator.CREATE_TABLE_FKSTRICT); 84 statement.execute(DatabaseCreator.CREATE_TABLE_FKCASCADE); 85 statement.execute(DatabaseCreator.CREATE_TABLE5); 86 } catch (SQLException e) { 87 fail("Unexpected SQLException " + e.toString()); 88 } 89 } 90 91 public void deleteTestTables() { 92 try { 93 statement.execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 94 statement.execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 95 statement.execute(DatabaseCreator.DROP_TABLE_PARENT); 96 statement.execute(DatabaseCreator.DROP_TABLE5); 97 } catch (SQLException e) { 98 fail("Unexpected SQLException " + e.toString()); 99 } 100 } 101 102 /** 103 * DeleteFunctionalityTest#testDelete1(). Deletes row with no 104 * referencing ones and RESTRICT action 105 */ 106 public void testDelete1() throws SQLException { 107 DatabaseCreator.fillFKStrictTable(conn); 108 statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE 109 + " WHERE id = 3;"); 110 } 111 112 /** 113 * DeleteFunctionalityTest#testDelete2(). Attempts to delete row with 114 * referencing ones and RESTRICT action - expecting SQLException 115 * TODO foreign key functionality is not supported 116 */ 117 /* public void testDelete2() throws SQLException { 118 DatabaseCreator.fillFKStrictTable(conn); 119 try { 120 statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE 121 + " WHERE id = 1;"); 122 fail("expecting SQLException"); 123 } catch (SQLException ex) { 124 // expected 125 } 126 } 127 */ 128 /** 129 * DeleteFunctionalityTest#testDelete3(). Deletes all referencing 130 * rows and then deletes referenced one 131 */ 132 public void testDelete3() throws SQLException { 133 statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE 134 + " WHERE name_id = 1;"); 135 statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE 136 + " WHERE id = 1;"); 137 } 138 139 /** 140 * DeleteFunctionalityTest#testDelete4(). Deletes row with no 141 * referencing ones and CASCADE action 142 */ 143 public void testDelete4() throws SQLException { 144 DatabaseCreator.fillFKCascadeTable(conn); 145 statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE 146 + " WHERE id = 3;"); 147 } 148 149 /** 150 * DeleteFunctionalityTest#testDelete5(). Attempts to delete row with 151 * referencing ones and CASCADE action - expecting all referencing 152 * rows will also be deleted 153 */ 154 public void testDelete5() throws SQLException { 155 statement.execute("DELETE FROM " + DatabaseCreator.PARENT_TABLE 156 + " WHERE id = 1;"); 157 158 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 159 + DatabaseCreator.FKCASCADE_TABLE + " WHERE name_id = 1;"); 160 r.next(); 161 assertEquals("Should be no rows", 0, r.getInt(1)); 162 r.close(); 163 } 164 165 /** 166 * DeleteFunctionalityTest#testDelete6(). 167 * TODO Foreign key functionality is not supported 168 */ 169 @KnownFailure("not supported") 170 public void testDelete6() throws SQLException { 171 DatabaseCreator.fillFKStrictTable(conn); 172 statement.execute("DELETE FROM " + DatabaseCreator.FKSTRICT_TABLE 173 + " WHERE name_id = ANY (SELECT id FROM " 174 + DatabaseCreator.PARENT_TABLE + " WHERE id > 1)"); 175 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 176 + DatabaseCreator.FKSTRICT_TABLE + " WHERE name_id = 1;"); 177 r.next(); 178 assertEquals("Should be 2 rows", 2, r.getInt(1)); 179 r.close(); 180 } 181 182 /** 183 * DeleteFunctionalityTest#testDelete7(). Deletes rows using 184 * PreparedStatement 185 */ 186 public void testDelete7() throws SQLException { 187 DatabaseCreator.fillTestTable5(conn); 188 PreparedStatement stat = conn.prepareStatement("DELETE FROM " 189 + DatabaseCreator.TEST_TABLE5 + " WHERE testID = ?"); 190 stat.setInt(1, 1); 191 stat.execute(); 192 stat.setInt(1, 2); 193 stat.execute(); 194 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 195 + DatabaseCreator.TEST_TABLE5 + " WHERE testID < 3 "); 196 r.next(); 197 assertEquals(0, r.getInt(1)); 198 r.close(); 199 stat.close(); 200 } 201 } 202
