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 
setUp()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 
tearDown()50     public void tearDown() throws Exception {
51         deleteTestTables();
52         statement.close();
53         conn.close();
54         super.tearDown();
55     }
56 
57 
createTestTables()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 
deleteTestTables()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      */
testDelete1()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      */
testDelete3()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      */
testDelete4()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      */
testDelete5()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")
testDelete6()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      */
testDelete7()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