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 java.sql.Connection; 19 import java.sql.DatabaseMetaData; 20 import java.sql.PreparedStatement; 21 import java.sql.ResultSet; 22 import java.sql.SQLException; 23 import java.sql.Statement; 24 25 import tests.support.Support_SQL; 26 import tests.support.DatabaseCreator; 27 28 import junit.extensions.TestSetup; 29 import junit.framework.Test; 30 import junit.framework.TestCase; 31 import junit.framework.TestSuite; 32 33 public class InsertFunctionalityTest extends TestCase { 34 35 private static Connection conn = null; 36 37 private static Statement statement = null; 38 39 public void setUp() throws Exception { 40 super.setUp(); 41 Support_SQL.loadDriver(); 42 conn = Support_SQL.getConnection(); 43 statement = conn.createStatement(); 44 createTestTables(); 45 46 } 47 48 public void tearDown() throws Exception { 49 deleteTestTables(); 50 statement.close(); 51 conn.close(); 52 super.tearDown(); 53 } 54 55 public void createTestTables() { 56 try { 57 DatabaseMetaData meta = conn.getMetaData(); 58 ResultSet userTab = meta.getTables(null, null, null, null); 59 60 while (userTab.next()) { 61 String tableName = userTab.getString("TABLE_NAME"); 62 if (tableName.equals(DatabaseCreator.PARENT_TABLE)) { 63 statement 64 .execute(DatabaseCreator.DROP_TABLE_PARENT); 65 } else if (tableName 66 .equals(DatabaseCreator.FKCASCADE_TABLE)) { 67 statement 68 .execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 69 } else if (tableName 70 .equals(DatabaseCreator.FKSTRICT_TABLE)) { 71 statement 72 .execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 73 } else if (tableName 74 .equals(DatabaseCreator.SIMPLE_TABLE1)) { 75 statement 76 .execute(DatabaseCreator.DROP_TABLE_SIMPLE1); 77 } else if (tableName 78 .equals(DatabaseCreator.SIMPLE_TABLE2)) { 79 statement 80 .execute(DatabaseCreator.DROP_TABLE_SIMPLE2); 81 } else if (tableName 82 .equals(DatabaseCreator.TEST_TABLE5)) { 83 statement.execute(DatabaseCreator.DROP_TABLE5); 84 } 85 } 86 userTab.close(); 87 statement.execute(DatabaseCreator.CREATE_TABLE_PARENT); 88 statement.execute(DatabaseCreator.CREATE_TABLE_FKSTRICT); 89 statement.execute(DatabaseCreator.CREATE_TABLE_FKCASCADE); 90 statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE2); 91 statement.execute(DatabaseCreator.CREATE_TABLE_SIMPLE1); 92 statement.execute(DatabaseCreator.CREATE_TABLE5); 93 } catch (SQLException e) { 94 fail("Unexpected SQLException " + e.toString()); 95 } 96 } 97 98 public void deleteTestTables() { 99 try { 100 statement.execute(DatabaseCreator.DROP_TABLE_FKCASCADE); 101 statement.execute(DatabaseCreator.DROP_TABLE_FKSTRICT); 102 statement.execute(DatabaseCreator.DROP_TABLE_PARENT); 103 statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE2); 104 statement.execute(DatabaseCreator.DROP_TABLE_SIMPLE1); 105 statement.execute(DatabaseCreator.DROP_TABLE5); 106 } catch (SQLException e) { 107 fail("Unexpected SQLException " + e.toString()); 108 } 109 } 110 111 /** 112 * InsertFunctionalityTest#testInsert1(). Attempts to insert row into 113 * table with integrity checking 114 */ 115 public void testInsert1() throws SQLException { 116 DatabaseCreator.fillParentTable(conn); 117 DatabaseCreator.fillFKStrictTable(conn); 118 DatabaseCreator.fillFKCascadeTable(conn); 119 statement.execute("INSERT INTO " + DatabaseCreator.FKSTRICT_TABLE 120 + " VALUES(4, 1, 'testInsert')"); 121 statement.execute("INSERT INTO " + DatabaseCreator.FKCASCADE_TABLE 122 + " VALUES(4, 1, 'testInsert')"); 123 } 124 125 /** 126 * InsertFunctionalityTest#testInsert2(). Attempts to insert row into 127 * table with integrity checking when row has incorrect foreign key 128 * value - expecting SQLException 129 */ 130 public void testInsert2() throws SQLException { 131 DatabaseCreator.fillParentTable(conn); 132 DatabaseCreator.fillFKStrictTable(conn); 133 DatabaseCreator.fillFKCascadeTable(conn); 134 try { 135 statement.execute("INSERT INTO " + DatabaseCreator.FKSTRICT_TABLE 136 + " VALUES(4, 4, 'testInsert')"); 137 // TODO Foreign key functionality isn't supported 138 // fail("expecting SQLException"); 139 } catch (SQLException ex) { 140 // expected 141 } 142 try { 143 statement.execute("INSERT INTO " + DatabaseCreator.FKCASCADE_TABLE 144 + " VALUES(4, 4, 'testInsert')"); 145 // TODO Foreign key functionality isn't supported 146 // fail("expecting SQLException"); 147 } catch (SQLException ex) { 148 // expected 149 } 150 } 151 152 /** 153 * InsertFunctionalityTest#testInsert3(). Tests INSERT ... SELECT 154 * functionality 155 */ 156 public void testInsert3() throws SQLException { 157 DatabaseCreator.fillParentTable(conn); 158 DatabaseCreator.fillFKStrictTable(conn); 159 statement.execute("INSERT INTO " + DatabaseCreator.TEST_TABLE5 160 + " SELECT id AS testId, value AS testValue " + "FROM " 161 + DatabaseCreator.FKSTRICT_TABLE + " WHERE name_id = 1"); 162 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 163 + DatabaseCreator.TEST_TABLE5); 164 r.next(); 165 assertEquals("Should be 2 rows", 2, r.getInt(1)); 166 r.close(); 167 } 168 169 /** 170 * InsertFunctionalityTest#testInsert4(). Tests INSERT ... SELECT 171 * with expressions in SELECT query 172 */ 173 public void testInsert4() throws SQLException { 174 DatabaseCreator.fillSimpleTable1(conn); 175 statement.execute("INSERT INTO " + DatabaseCreator.SIMPLE_TABLE2 176 + " SELECT id, speed*10 AS speed, size-1 AS size FROM " 177 + DatabaseCreator.SIMPLE_TABLE1); 178 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 179 + DatabaseCreator.SIMPLE_TABLE2 + " AS a JOIN " 180 + DatabaseCreator.SIMPLE_TABLE1 181 + " AS b ON a.speed = 10*b.speed AND a.size = b.size-1"); 182 r.next(); 183 assertEquals("Should be 2 rows", 2, r.getInt(1)); 184 r.close(); 185 } 186 187 /** 188 * InsertFunctionalityTest#testInsert5(). Inserts multiple rows using 189 * UNION ALL 190 */ 191 public void testInsert5() throws SQLException { 192 statement.execute("INSERT INTO " + DatabaseCreator.TEST_TABLE5 193 + " SELECT 1 as testId, 2 as testValue " 194 + "UNION SELECT 2 as testId, 3 as testValue " 195 + "UNION SELECT 3 as testId, 4 as testValue"); 196 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 197 + DatabaseCreator.TEST_TABLE5); 198 r.next(); 199 assertEquals("Should be 3 rows", 3, r.getInt(1)); 200 r.close(); 201 } 202 203 /** 204 * InsertFunctionalityTest#testInsert6(). Tests INSERT with 205 * PreparedStatement 206 */ 207 public void testInsertPrepared() throws SQLException { 208 PreparedStatement stat = conn.prepareStatement("INSERT INTO " 209 + DatabaseCreator.TEST_TABLE5 + " VALUES(?, ?)"); 210 stat.setInt(1, 1); 211 stat.setString(2, "1"); 212 stat.execute(); 213 stat.setInt(1, 2); 214 stat.setString(2, "3"); 215 stat.execute(); 216 ResultSet r = statement.executeQuery("SELECT COUNT(*) FROM " 217 + DatabaseCreator.TEST_TABLE5 218 + " WHERE (testId = 1 AND testValue = '1') " 219 + "OR (testId = 2 AND testValue = '3')"); 220 r.next(); 221 assertEquals("Incorrect number of records", 2, r.getInt(1)); 222 r.close(); 223 stat.close(); 224 } 225 } 226
