The Android Open Source Project | 0eec464 | 2012-04-01 00:00:00 -0700 | [diff] [blame] | 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.TestTargetClass; |
| 20 | import dalvik.annotation.TestTargets; |
| 21 | import dalvik.annotation.TestLevel; |
| 22 | import dalvik.annotation.TestTargetNew; |
| 23 | |
| 24 | import java.math.BigDecimal; |
| 25 | import java.sql.Connection; |
| 26 | import java.sql.ResultSet; |
| 27 | import java.sql.SQLException; |
| 28 | import java.sql.Statement; |
| 29 | |
| 30 | import tests.support.DatabaseCreator; |
| 31 | import tests.support.Support_SQL; |
| 32 | |
| 33 | import junit.extensions.TestSetup; |
| 34 | import junit.framework.Test; |
| 35 | import junit.framework.TestCase; |
| 36 | import junit.framework.TestSuite; |
| 37 | |
| 38 | import tests.support.ThreadPool; |
| 39 | |
| 40 | @TestTargetClass(Statement.class) |
| 41 | public class MultiThreadAccessTest extends TestCase { |
| 42 | |
| 43 | private static Connection conn; |
| 44 | |
| 45 | private static Statement statement; |
| 46 | |
| 47 | private static final int numThreads = 10; |
| 48 | |
| 49 | private static final int numOfRecords = 20; |
| 50 | |
| 51 | private ThreadPool threadPool; |
| 52 | |
| 53 | public void setUp() throws Exception { |
| 54 | super.setUp(); |
| 55 | Support_SQL.loadDriver(); |
| 56 | try { |
| 57 | conn = Support_SQL.getConnection(); |
| 58 | statement = conn.createStatement(); |
| 59 | createTestTables(); |
| 60 | } catch (SQLException e) { |
| 61 | fail("Unexpected SQLException " + e.toString()); |
| 62 | } |
| 63 | threadPool = new ThreadPool(numThreads); |
| 64 | } |
| 65 | |
| 66 | public void tearDown() throws Exception { |
| 67 | threadPool.join(); |
| 68 | deleteTestTables(); |
| 69 | conn.close(); |
| 70 | statement.close(); |
| 71 | super.tearDown(); |
| 72 | } |
| 73 | |
| 74 | public void createTestTables() { |
| 75 | try { |
| 76 | ResultSet userTab = conn.getMetaData().getTables(null, |
| 77 | null, null, null); |
| 78 | |
| 79 | while (userTab.next()) { |
| 80 | String tableName = userTab.getString("TABLE_NAME"); |
| 81 | if (tableName.equals(DatabaseCreator.TEST_TABLE1)) { |
| 82 | statement.execute(DatabaseCreator.DROP_TABLE1); |
| 83 | } else if (tableName |
| 84 | .equals(DatabaseCreator.TEST_TABLE2)) { |
| 85 | statement.execute(DatabaseCreator.DROP_TABLE2); |
| 86 | } else if (tableName |
| 87 | .equals(DatabaseCreator.TEST_TABLE4)) { |
| 88 | statement.execute(DatabaseCreator.DROP_TABLE4); |
| 89 | } else if (tableName |
| 90 | .equals(DatabaseCreator.TEST_TABLE3)) { |
| 91 | statement.execute(DatabaseCreator.DROP_TABLE3); |
| 92 | } |
| 93 | } |
| 94 | |
| 95 | userTab.close(); |
| 96 | statement.execute(DatabaseCreator.CREATE_TABLE3); |
| 97 | statement.execute(DatabaseCreator.CREATE_TABLE4); |
| 98 | statement.execute(DatabaseCreator.CREATE_TABLE1); |
| 99 | statement.execute(DatabaseCreator.CREATE_TABLE2); |
| 100 | |
| 101 | DatabaseCreator.fillTestTable1(conn, numOfRecords); |
| 102 | DatabaseCreator.fillTestTable2(conn, numOfRecords); |
| 103 | DatabaseCreator.fillTestTable4(conn, numOfRecords); |
| 104 | } catch (SQLException e) { |
| 105 | fail("Unexpected SQLException " + e.toString()); |
| 106 | } |
| 107 | } |
| 108 | |
| 109 | public void deleteTestTables() { |
| 110 | try { |
| 111 | statement.execute(DatabaseCreator.DROP_TABLE1); |
| 112 | statement.execute(DatabaseCreator.DROP_TABLE2); |
| 113 | statement.execute(DatabaseCreator.DROP_TABLE3); |
| 114 | statement.execute(DatabaseCreator.DROP_TABLE4); |
| 115 | } catch (SQLException e) { |
| 116 | fail("Unexpected SQLException " + e.toString()); |
| 117 | } |
| 118 | } |
| 119 | |
| 120 | /** |
| 121 | * A few threads execute select operation in the same time for one table in |
| 122 | * the database. Number of threads is defined by numThreads variable |
| 123 | * |
| 124 | * @throws SQLException |
| 125 | */ |
| 126 | @TestTargetNew( |
| 127 | level = TestLevel.PARTIAL, |
| 128 | notes = "Functionality test: A few threads execute select operation in the same time for one table in the database.", |
| 129 | method = "executeQuery", |
| 130 | args = {java.lang.String.class} |
| 131 | ) |
| 132 | public void test_MultipleAccessToOneTable() throws SQLException { |
| 133 | for (int i = 0; i < numThreads; i++) { |
| 134 | threadPool.runTask(createTask1(i)); |
| 135 | } |
| 136 | } |
| 137 | |
| 138 | /** |
| 139 | * A few threads execute select operation in the same time for different |
| 140 | * tables in the database. Number of threads is defined by numThreads |
| 141 | * variable |
| 142 | * |
| 143 | * @throws SQLException |
| 144 | */ |
| 145 | @TestTargetNew( |
| 146 | level = TestLevel.PARTIAL, |
| 147 | notes = "Functionality test: A few threads execute select operation in the same time for different tables in the database", |
| 148 | method = "executeQuery", |
| 149 | args = {java.lang.String.class} |
| 150 | ) |
| 151 | public void test_MultipleAccessToSeveralTables() throws SQLException { |
| 152 | threadPool.runTask(createTask1(1)); |
| 153 | threadPool.runTask(createTask2(2)); |
| 154 | threadPool.runTask(createTask3(3)); |
| 155 | } |
| 156 | |
| 157 | /** |
| 158 | * A few threads execute update, insert and delete operations in the same |
| 159 | * time for one table in the database. Number of threads is defined by |
| 160 | * numThreads variable |
| 161 | * |
| 162 | * @throws SQLException |
| 163 | */ |
| 164 | @TestTargetNew( |
| 165 | level = TestLevel.PARTIAL, |
| 166 | notes = "Functionality test: A few threads execute update, insert and delete operations in the same time for one table in the database", |
| 167 | method = "executeQuery", |
| 168 | args = {java.lang.String.class} |
| 169 | ) |
| 170 | public void test_MultipleOperationsInSeveralTables() throws SQLException { |
| 171 | int id1 = numOfRecords - 1; |
| 172 | threadPool.runTask(createTask4(id1)); |
| 173 | |
| 174 | int id2 = numOfRecords + 1; |
| 175 | threadPool.runTask(createTask5(id2)); |
| 176 | |
| 177 | int oldID = 5; |
| 178 | int newID = 100; |
| 179 | threadPool.runTask(createTask6(oldID, newID)); |
| 180 | |
| 181 | threadPool.join(); |
| 182 | |
| 183 | Statement statement = conn.createStatement(); |
| 184 | String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1 |
| 185 | + " WHERE id="; |
| 186 | |
| 187 | ResultSet result = statement.executeQuery(selectQuery + id1); |
| 188 | assertFalse("The record was not deleted", result.next()); |
| 189 | |
| 190 | result = statement.executeQuery(selectQuery + id2); |
| 191 | assertTrue("The record was not inserted", result.next()); |
| 192 | |
| 193 | assertEquals("Wrong value of field1", DatabaseCreator.defaultString |
| 194 | + id2, result.getString("field1")); |
| 195 | // TODO getBigDecimal is not supported |
| 196 | assertEquals("Wrong value of field2", Integer.valueOf(id2).intValue(), result |
| 197 | .getInt("field2")); |
| 198 | assertEquals("Wrong value of field3", Integer.valueOf(id2).intValue(), result |
| 199 | .getInt("field3")); |
| 200 | result.close(); |
| 201 | |
| 202 | result = statement.executeQuery(selectQuery + oldID); |
| 203 | assertFalse("The record was not deleted", result.next()); |
| 204 | result.close(); |
| 205 | |
| 206 | result = statement.executeQuery(selectQuery + newID); |
| 207 | assertTrue("The record was not updated", result.next()); |
| 208 | |
| 209 | assertEquals("Wrong value of field1", DatabaseCreator.defaultString |
| 210 | + newID, result.getString("field1")); |
| 211 | // TODO getBigDecimal is not supported |
| 212 | assertEquals("Wrong value of field2", Integer.valueOf(newID).intValue(), result |
| 213 | .getInt("field2")); |
| 214 | assertEquals("Wrong value of field3", Integer.valueOf(newID).intValue(), result |
| 215 | .getInt("field3")); |
| 216 | result.close(); |
| 217 | } |
| 218 | |
| 219 | /** |
| 220 | * A few threads execute update operation in the same time for one tables in |
| 221 | * the database. Number of threads is defined by numThreads variable |
| 222 | * |
| 223 | * @throws SQLException |
| 224 | */ |
| 225 | @TestTargetNew( |
| 226 | level = TestLevel.PARTIAL, |
| 227 | notes = "Functional test: A few threads execute update operation in the same time for one tables in the database", |
| 228 | method = "executeQuery", |
| 229 | args = {java.lang.String.class} |
| 230 | ) |
| 231 | public void test_MultipleUpdatesInOneTables() throws SQLException { |
| 232 | int id = 1; |
| 233 | String field = "field3"; |
| 234 | |
| 235 | String selectQuery = "SELECT * FROM " + DatabaseCreator.TEST_TABLE1 |
| 236 | + " WHERE id=" + id; |
| 237 | Statement statement = conn.createStatement(); |
| 238 | |
| 239 | ResultSet result = statement.executeQuery(selectQuery); |
| 240 | assertTrue("There is no records with id = " + id, result.next()); |
| 241 | // TODO getBigDecimal is not supported |
| 242 | // assertEquals("Wrong value of field " + field, BigDecimal.valueOf(id), |
| 243 | // result.getBigDecimal(field)); |
| 244 | result.close(); |
| 245 | |
| 246 | for (int i = 0; i < numThreads; i++) { |
| 247 | threadPool.runTask(createTask7(id, field)); |
| 248 | } |
| 249 | |
| 250 | threadPool.join(); |
| 251 | |
| 252 | double expectedVal = id + numThreads; |
| 253 | result = statement.executeQuery(selectQuery); |
| 254 | assertTrue("There is no records with id = " + id, result.next()); |
| 255 | // TODO getBigDecimal is not supported -> |
| 256 | // assertEquals("Wrong value of field " + field, expectedVal, result |
| 257 | // .getBigDecimal(field).doubleValue()); |
| 258 | result.close(); |
| 259 | } |
| 260 | |
| 261 | /** |
| 262 | * This method creates a Runnable that executes select operation for the |
| 263 | * first table |
| 264 | * |
| 265 | * @param taskID |
| 266 | * @return |
| 267 | */ |
| 268 | private static Runnable createTask1(final int taskID) { |
| 269 | return new Runnable() { |
| 270 | public void run() { |
| 271 | try { |
| 272 | Statement statement = conn.createStatement(); |
| 273 | ResultSet result = statement.executeQuery("SELECT * FROM " |
| 274 | + DatabaseCreator.TEST_TABLE1); |
| 275 | |
| 276 | while (result.next()) { |
| 277 | assertEquals("Wrong value of id ", |
| 278 | DatabaseCreator.defaultString |
| 279 | + result.getInt("id"), result |
| 280 | .getString("field1")); |
| 281 | assertEquals("Wrong value of field2 ", result.getInt("id"), result |
| 282 | .getInt("field2")); |
| 283 | assertEquals("Wrong value of field3 ",result.getInt("id"), result |
| 284 | .getInt("field3")); |
| 285 | } |
| 286 | result.close(); |
| 287 | } catch (Exception e) { |
| 288 | System.err.println("Task 1 "+e.getMessage()); |
| 289 | } |
| 290 | } |
| 291 | }; |
| 292 | } |
| 293 | |
| 294 | /** |
| 295 | * This method creates a Runnable that execute select operation for the |
| 296 | * second table |
| 297 | * |
| 298 | * @param taskID |
| 299 | */ |
| 300 | private static Runnable createTask2(final int taskID) { |
| 301 | return new Runnable() { |
| 302 | public void run() { |
| 303 | try { |
| 304 | Statement statement = conn.createStatement(); |
| 305 | ResultSet result = statement.executeQuery("SELECT * FROM " |
| 306 | + DatabaseCreator.TEST_TABLE2); |
| 307 | |
| 308 | while (result.next()) { |
| 309 | while (result.next()) { |
| 310 | int id = result.getInt("finteger"); |
| 311 | assertEquals("Wrong value of ftext", |
| 312 | DatabaseCreator.defaultString + id, result |
| 313 | .getString("ftext")); |
| 314 | assertEquals("Wrong value of fcharacter", |
| 315 | DatabaseCreator.defaultCharacter + id, |
| 316 | result.getString("fcharacter")); |
| 317 | assertEquals("Wrong value of fdecimal", |
| 318 | DatabaseCreator.defaultDouble + id, result |
| 319 | .getDouble("fdecimal")); |
| 320 | assertEquals("Wrong value of fnumeric", |
| 321 | DatabaseCreator.defaultDouble + id, result |
| 322 | .getDouble("fnumeric")); |
| 323 | assertEquals("Wrong value of fsmallint", result |
| 324 | .getInt("finteger"), result |
| 325 | .getShort("fsmallint")); |
| 326 | assertEquals("Wrong value of ffloat", |
| 327 | (float) DatabaseCreator.defaultDouble + id, |
| 328 | result.getFloat("ffloat")); |
| 329 | assertEquals("Wrong value of freal", |
| 330 | (float) DatabaseCreator.defaultDouble + id, |
| 331 | result.getFloat("freal")); |
| 332 | assertEquals("Wrong value of fdouble", |
| 333 | DatabaseCreator.defaultDouble + id, result |
| 334 | .getDouble("fdouble")); |
| 335 | } |
| 336 | } |
| 337 | result.close(); |
| 338 | } catch (Exception e) { |
| 339 | System.err.println("Task2 "+e.getMessage()); |
| 340 | } |
| 341 | } |
| 342 | }; |
| 343 | } |
| 344 | |
| 345 | /** |
| 346 | * This method creates a Runnable that execute select operation for the |
| 347 | * third table |
| 348 | * |
| 349 | * @param taskID |
| 350 | */ |
| 351 | private static Runnable createTask3(final int taskID) { |
| 352 | return new Runnable() { |
| 353 | public void run() { |
| 354 | try { |
| 355 | Statement statement = conn.createStatement(); |
| 356 | ResultSet result = statement.executeQuery("SELECT * FROM " |
| 357 | + DatabaseCreator.TEST_TABLE4); |
| 358 | |
| 359 | while (result.next()) { |
| 360 | assertEquals("Wrong value of field1", |
| 361 | DatabaseCreator.defaultString |
| 362 | + result.getInt("fk"), result |
| 363 | .getString("field1")); |
| 364 | } |
| 365 | result.close(); |
| 366 | } catch (Exception e) { |
| 367 | System.err.println("Task 3 "+e.getMessage()); |
| 368 | } |
| 369 | } |
| 370 | }; |
| 371 | } |
| 372 | |
| 373 | /** |
| 374 | * This method creates a Runnable that executes delete operation for the |
| 375 | * first table |
| 376 | * |
| 377 | * @param taskID |
| 378 | */ |
| 379 | private static Runnable createTask4(final int id) { |
| 380 | return new Runnable() { |
| 381 | public void run() { |
| 382 | try { |
| 383 | Statement statement = conn.createStatement(); |
| 384 | statement.execute("DELETE FROM " |
| 385 | + DatabaseCreator.TEST_TABLE1 + " WHERE id=" + id); |
| 386 | } catch (Exception e) { |
| 387 | System.err.println("Task 4 "+e.getMessage()); |
| 388 | } |
| 389 | } |
| 390 | }; |
| 391 | } |
| 392 | |
| 393 | /** |
| 394 | * This method creates a Runnable that executes insert operation for the |
| 395 | * first table |
| 396 | * |
| 397 | * @param taskID |
| 398 | */ |
| 399 | private static Runnable createTask5(final int id) { |
| 400 | return new Runnable() { |
| 401 | public void run() { |
| 402 | try { |
| 403 | Statement statement = conn.createStatement(); |
| 404 | String value = DatabaseCreator.defaultString + id; |
| 405 | |
| 406 | String insertQuery = "INSERT INTO " |
| 407 | + DatabaseCreator.TEST_TABLE1 |
| 408 | + " (id, field1, field2, field3) VALUES(" + id |
| 409 | + ", '" + value + "', " + id + ", " + id + ")"; |
| 410 | statement.execute(insertQuery); |
| 411 | } catch (Exception e) { |
| 412 | System.err.println("Task 5 "+e.getMessage()); |
| 413 | } |
| 414 | } |
| 415 | }; |
| 416 | } |
| 417 | |
| 418 | /** |
| 419 | * This method creates a Runnable that executes update operation for the one |
| 420 | * record of the first table |
| 421 | * |
| 422 | * @param taskID |
| 423 | */ |
| 424 | private static Runnable createTask6(final int oldID, final int newID) { |
| 425 | return new Runnable() { |
| 426 | public void run() { |
| 427 | try { |
| 428 | Statement statement = conn.createStatement(); |
| 429 | String value = DatabaseCreator.defaultString + newID; |
| 430 | String updateQuery = "UPDATE " |
| 431 | + DatabaseCreator.TEST_TABLE1 + " SET id=" + newID |
| 432 | + ", field1='" + value + "', field2=" + newID |
| 433 | + ", field3=" + newID + " WHERE id=" + oldID; |
| 434 | statement.execute(updateQuery); |
| 435 | } catch (Exception e) { |
| 436 | System.err.println("Task 6 "+e.getMessage()); |
| 437 | } |
| 438 | } |
| 439 | }; |
| 440 | } |
| 441 | |
| 442 | /** |
| 443 | * This method creates a Runnable that executes update operation for the one |
| 444 | * field of one record with identifier id in the first table |
| 445 | * |
| 446 | * @param taskID |
| 447 | */ |
| 448 | private static Runnable createTask7(final int id, final String field) { |
| 449 | return new Runnable() { |
| 450 | public void run() { |
| 451 | try { |
| 452 | Statement statement = conn.createStatement(); |
| 453 | String updateQuery = "UPDATE " |
| 454 | + DatabaseCreator.TEST_TABLE1 + " SET " + field |
| 455 | + "= " + field + "+ 1 WHERE id=" + id; |
| 456 | statement.execute(updateQuery); |
| 457 | } catch (Exception e) { |
| 458 | System.err.println("Task 7 "+e.getMessage()); |
| 459 | } |
| 460 | } |
| 461 | }; |
| 462 | } |
| 463 | } |