1 /* 2 * Copyright (C) 2009 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 android.database.sqlite.cts; 18 19 20 import android.content.Context; 21 import android.database.Cursor; 22 import android.database.sqlite.SQLiteCursor; 23 import android.database.sqlite.SQLiteCursorDriver; 24 import android.database.sqlite.SQLiteDatabase; 25 import android.database.sqlite.SQLiteQuery; 26 import android.database.sqlite.SQLiteQueryBuilder; 27 import android.os.CancellationSignal; 28 import android.os.OperationCanceledException; 29 import android.test.AndroidTestCase; 30 31 import java.util.HashMap; 32 import java.util.Map; 33 import java.util.concurrent.Semaphore; 34 35 public class SQLiteQueryBuilderTest extends AndroidTestCase { 36 private SQLiteDatabase mDatabase; 37 private final String TEST_TABLE_NAME = "test"; 38 private final String EMPLOYEE_TABLE_NAME = "employee"; 39 private static final String DATABASE_FILE = "database_test.db"; 40 41 @Override 42 protected void setUp() throws Exception { 43 super.setUp(); 44 45 getContext().deleteDatabase(DATABASE_FILE); 46 mDatabase = getContext().openOrCreateDatabase(DATABASE_FILE, Context.MODE_PRIVATE, null); 47 assertNotNull(mDatabase); 48 } 49 50 @Override 51 protected void tearDown() throws Exception { 52 mDatabase.close(); 53 getContext().deleteDatabase(DATABASE_FILE); 54 super.tearDown(); 55 } 56 57 public void testConstructor() { 58 new SQLiteQueryBuilder(); 59 } 60 61 public void testSetDistinct() { 62 String expected; 63 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 64 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 65 sqliteQueryBuilder.setDistinct(false); 66 sqliteQueryBuilder.appendWhere("age=20"); 67 String sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 68 null, null, null, null, null, null); 69 assertEquals(TEST_TABLE_NAME, sqliteQueryBuilder.getTables()); 70 expected = "SELECT age, address FROM " + TEST_TABLE_NAME + " WHERE (age=20)"; 71 assertEquals(expected, sql); 72 73 sqliteQueryBuilder = new SQLiteQueryBuilder(); 74 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 75 sqliteQueryBuilder.setDistinct(true); 76 sqliteQueryBuilder.appendWhere("age>32"); 77 sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 78 null, null, null, null, null, null); 79 assertEquals(EMPLOYEE_TABLE_NAME, sqliteQueryBuilder.getTables()); 80 expected = "SELECT DISTINCT age, address FROM " + EMPLOYEE_TABLE_NAME + " WHERE (age>32)"; 81 assertEquals(expected, sql); 82 83 sqliteQueryBuilder = new SQLiteQueryBuilder(); 84 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 85 sqliteQueryBuilder.setDistinct(true); 86 sqliteQueryBuilder.appendWhereEscapeString("age>32"); 87 sql = sqliteQueryBuilder.buildQuery(new String[] { "age", "address" }, 88 null, null, null, null, null, null); 89 assertEquals(EMPLOYEE_TABLE_NAME, sqliteQueryBuilder.getTables()); 90 expected = "SELECT DISTINCT age, address FROM " + EMPLOYEE_TABLE_NAME 91 + " WHERE ('age>32')"; 92 assertEquals(expected, sql); 93 } 94 95 public void testSetProjectionMap() { 96 String expected; 97 Map<String, String> projectMap = new HashMap<String, String>(); 98 projectMap.put("EmployeeName", "name"); 99 projectMap.put("EmployeeAge", "age"); 100 projectMap.put("EmployeeAddress", "address"); 101 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 102 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 103 sqliteQueryBuilder.setDistinct(false); 104 sqliteQueryBuilder.setProjectionMap(projectMap); 105 String sql = sqliteQueryBuilder.buildQuery(new String[] { "EmployeeName", "EmployeeAge" }, 106 null, null, null, null, null, null); 107 expected = "SELECT name, age FROM " + TEST_TABLE_NAME; 108 assertEquals(expected, sql); 109 110 sql = sqliteQueryBuilder.buildQuery(null, // projectionIn is null 111 null, null, null, null, null, null); 112 assertTrue(sql.matches("SELECT (age|name|address), (age|name|address), (age|name|address) " 113 + "FROM " + TEST_TABLE_NAME)); 114 assertTrue(sql.contains("age")); 115 assertTrue(sql.contains("name")); 116 assertTrue(sql.contains("address")); 117 118 sqliteQueryBuilder.setProjectionMap(null); 119 sql = sqliteQueryBuilder.buildQuery(new String[] { "name", "address" }, 120 null, null, null, null, null, null); 121 assertTrue(sql.matches("SELECT (name|address), (name|address) " 122 + "FROM " + TEST_TABLE_NAME)); 123 assertTrue(sql.contains("name")); 124 assertTrue(sql.contains("address")); 125 } 126 127 public void testSetCursorFactory() { 128 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, " + 129 "name TEXT, age INTEGER, address TEXT);"); 130 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('Mike', '20', 'LA');"); 131 mDatabase.execSQL("INSERT INTO test (name, age, address) VALUES ('jack', '40', 'LA');"); 132 133 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 134 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 135 Cursor cursor = sqliteQueryBuilder.query(mDatabase, new String[] { "name", "age" }, 136 null, null, null, null, null); 137 assertNotNull(cursor); 138 assertTrue(cursor instanceof SQLiteCursor); 139 140 SQLiteDatabase.CursorFactory factory = new SQLiteDatabase.CursorFactory() { 141 public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery, 142 String editTable, SQLiteQuery query) { 143 return new MockCursor(db, masterQuery, editTable, query); 144 } 145 }; 146 147 sqliteQueryBuilder.setCursorFactory(factory); 148 cursor = sqliteQueryBuilder.query(mDatabase, new String[] { "name", "age" }, 149 null, null, null, null, null); 150 assertNotNull(cursor); 151 assertTrue(cursor instanceof MockCursor); 152 } 153 154 private static class MockCursor extends SQLiteCursor { 155 public MockCursor(SQLiteDatabase db, SQLiteCursorDriver driver, 156 String editTable, SQLiteQuery query) { 157 super(db, driver, editTable, query); 158 } 159 } 160 161 public void testBuildQueryString() { 162 String expected; 163 final String[] DEFAULT_TEST_PROJECTION = new String [] { "name", "age", "sum(salary)" }; 164 final String DEFAULT_TEST_WHERE = "age > 25"; 165 final String DEFAULT_HAVING = "sum(salary) > 3000"; 166 167 String sql = SQLiteQueryBuilder.buildQueryString(false, "Employee", 168 DEFAULT_TEST_PROJECTION, 169 DEFAULT_TEST_WHERE, "name", DEFAULT_HAVING, "name", "100"); 170 171 expected = "SELECT name, age, sum(salary) FROM Employee WHERE " + DEFAULT_TEST_WHERE + 172 " GROUP BY name " + 173 "HAVING " + DEFAULT_HAVING + " " + 174 "ORDER BY name " + 175 "LIMIT 100"; 176 assertEquals(expected, sql); 177 } 178 179 public void testBuildQuery() { 180 final String[] DEFAULT_TEST_PROJECTION = new String[] { "name", "sum(salary)" }; 181 final String DEFAULT_TEST_WHERE = "age > 25"; 182 final String DEFAULT_HAVING = "sum(salary) > 2000"; 183 184 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 185 sqliteQueryBuilder.setTables(TEST_TABLE_NAME); 186 sqliteQueryBuilder.setDistinct(false); 187 String sql = sqliteQueryBuilder.buildQuery(DEFAULT_TEST_PROJECTION, 188 DEFAULT_TEST_WHERE, null, "name", DEFAULT_HAVING, "name", "2"); 189 String expected = "SELECT name, sum(salary) FROM " + TEST_TABLE_NAME 190 + " WHERE (" + DEFAULT_TEST_WHERE + ") " + 191 "GROUP BY name HAVING " + DEFAULT_HAVING + " ORDER BY name LIMIT 2"; 192 assertEquals(expected, sql); 193 } 194 195 public void testAppendColumns() { 196 StringBuilder sb = new StringBuilder(); 197 String[] columns = new String[] { "name", "age" }; 198 199 assertEquals("", sb.toString()); 200 SQLiteQueryBuilder.appendColumns(sb, columns); 201 assertEquals("name, age ", sb.toString()); 202 } 203 204 public void testQuery() { 205 createEmployeeTable(); 206 207 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 208 sqliteQueryBuilder.setTables("Employee"); 209 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 210 new String[] { "name", "sum(salary)" }, null, null, 211 "name", "sum(salary)>1000", "name"); 212 assertNotNull(cursor); 213 assertEquals(3, cursor.getCount()); 214 215 final int COLUMN_NAME_INDEX = 0; 216 final int COLUMN_SALARY_INDEX = 1; 217 cursor.moveToFirst(); 218 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 219 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 220 cursor.moveToNext(); 221 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 222 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 223 cursor.moveToNext(); 224 assertEquals("jack", cursor.getString(COLUMN_NAME_INDEX)); 225 assertEquals(3500, cursor.getInt(COLUMN_SALARY_INDEX)); 226 227 sqliteQueryBuilder = new SQLiteQueryBuilder(); 228 sqliteQueryBuilder.setTables(EMPLOYEE_TABLE_NAME); 229 cursor = sqliteQueryBuilder.query(mDatabase, 230 new String[] { "name", "sum(salary)" }, null, null, 231 "name", "sum(salary)>1000", "name", "2" // limit is 2 232 ); 233 assertNotNull(cursor); 234 assertEquals(2, cursor.getCount()); 235 cursor.moveToFirst(); 236 assertEquals("Jim", cursor.getString(COLUMN_NAME_INDEX)); 237 assertEquals(4500, cursor.getInt(COLUMN_SALARY_INDEX)); 238 cursor.moveToNext(); 239 assertEquals("Mike", cursor.getString(COLUMN_NAME_INDEX)); 240 assertEquals(4000, cursor.getInt(COLUMN_SALARY_INDEX)); 241 } 242 243 public void testUnionQuery() { 244 String expected; 245 String[] innerProjection = new String[] {"name", "age", "location"}; 246 SQLiteQueryBuilder employeeQueryBuilder = new SQLiteQueryBuilder(); 247 SQLiteQueryBuilder peopleQueryBuilder = new SQLiteQueryBuilder(); 248 249 employeeQueryBuilder.setTables("employee"); 250 peopleQueryBuilder.setTables("people"); 251 252 String employeeSubQuery = employeeQueryBuilder.buildUnionSubQuery( 253 "_id", innerProjection, 254 null, 2, "employee", 255 "age=25", 256 null, null, null); 257 String peopleSubQuery = peopleQueryBuilder.buildUnionSubQuery( 258 "_id", innerProjection, 259 null, 2, "people", 260 "location=LA", 261 null, null, null); 262 expected = "SELECT name, age, location FROM employee WHERE (age=25)"; 263 assertEquals(expected, employeeSubQuery); 264 expected = "SELECT name, age, location FROM people WHERE (location=LA)"; 265 assertEquals(expected, peopleSubQuery); 266 267 SQLiteQueryBuilder unionQueryBuilder = new SQLiteQueryBuilder(); 268 269 unionQueryBuilder.setDistinct(true); 270 271 String unionQuery = unionQueryBuilder.buildUnionQuery( 272 new String[] { employeeSubQuery, peopleSubQuery }, null, null); 273 expected = "SELECT name, age, location FROM employee WHERE (age=25) " + 274 "UNION SELECT name, age, location FROM people WHERE (location=LA)"; 275 assertEquals(expected, unionQuery); 276 } 277 278 public void testCancelableQuery_WhenNotCanceled_ReturnsResultSet() { 279 createEmployeeTable(); 280 281 CancellationSignal cancellationSignal = new CancellationSignal(); 282 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 283 sqliteQueryBuilder.setTables("Employee"); 284 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 285 new String[] { "name", "sum(salary)" }, null, null, 286 "name", "sum(salary)>1000", "name", null, cancellationSignal); 287 288 assertEquals(3, cursor.getCount()); 289 } 290 291 public void testCancelableQuery_WhenCanceledBeforeQuery_ThrowsImmediately() { 292 createEmployeeTable(); 293 294 CancellationSignal cancellationSignal = new CancellationSignal(); 295 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 296 sqliteQueryBuilder.setTables("Employee"); 297 298 cancellationSignal.cancel(); 299 try { 300 sqliteQueryBuilder.query(mDatabase, 301 new String[] { "name", "sum(salary)" }, null, null, 302 "name", "sum(salary)>1000", "name", null, cancellationSignal); 303 fail("Expected OperationCanceledException"); 304 } catch (OperationCanceledException ex) { 305 // expected 306 } 307 } 308 309 public void testCancelableQuery_WhenCanceledAfterQuery_ThrowsWhenExecuted() { 310 createEmployeeTable(); 311 312 CancellationSignal cancellationSignal = new CancellationSignal(); 313 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 314 sqliteQueryBuilder.setTables("Employee"); 315 316 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 317 new String[] { "name", "sum(salary)" }, null, null, 318 "name", "sum(salary)>1000", "name", null, cancellationSignal); 319 320 cancellationSignal.cancel(); 321 try { 322 cursor.getCount(); // force execution 323 fail("Expected OperationCanceledException"); 324 } catch (OperationCanceledException ex) { 325 // expected 326 } 327 } 328 329 public void testCancelableQuery_WhenCanceledDueToContention_StopsWaitingAndThrows() { 330 createEmployeeTable(); 331 332 for (int i = 0; i < 5; i++) { 333 final CancellationSignal cancellationSignal = new CancellationSignal(); 334 final Semaphore barrier1 = new Semaphore(0); 335 final Semaphore barrier2 = new Semaphore(0); 336 Thread contentionThread = new Thread() { 337 @Override 338 public void run() { 339 mDatabase.beginTransaction(); // acquire the only available connection 340 barrier1.release(); // release query to start running 341 try { 342 barrier2.acquire(); // wait for test to end 343 } catch (InterruptedException e) { 344 } 345 mDatabase.endTransaction(); // release the connection 346 } 347 }; 348 Thread cancellationThread = new Thread() { 349 @Override 350 public void run() { 351 try { 352 Thread.sleep(300); 353 } catch (InterruptedException ex) { 354 } 355 cancellationSignal.cancel(); 356 } 357 }; 358 try { 359 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 360 sqliteQueryBuilder.setTables("Employee"); 361 362 contentionThread.start(); 363 cancellationThread.start(); 364 365 try { 366 barrier1.acquire(); // wait for contention thread to start transaction 367 } catch (InterruptedException e) { 368 } 369 370 final long startTime = System.nanoTime(); 371 try { 372 Cursor cursor = sqliteQueryBuilder.query(mDatabase, 373 new String[] { "name", "sum(salary)" }, null, null, 374 "name", "sum(salary)>1000", "name", null, cancellationSignal); 375 cursor.getCount(); // force execution 376 fail("Expected OperationCanceledException"); 377 } catch (OperationCanceledException ex) { 378 // expected 379 } 380 381 // We want to confirm that the query really was blocked trying to acquire a 382 // connection for a certain amount of time before it was freed by cancel. 383 final long waitTime = System.nanoTime() - startTime; 384 if (waitTime > 150 * 1000000L) { 385 return; // success! 386 } 387 } finally { 388 barrier1.release(); 389 barrier2.release(); 390 try { 391 contentionThread.join(); 392 cancellationThread.join(); 393 } catch (InterruptedException e) { 394 } 395 } 396 } 397 398 // Occasionally we might miss the timing deadline due to factors in the 399 // environment, but if after several trials we still couldn't demonstrate 400 // that the query was blocked, then the test must be broken. 401 fail("Could not prove that the query actually blocked before cancel() was called."); 402 } 403 404 public void testCancelableQuery_WhenCanceledDuringLongRunningQuery_CancelsQueryAndThrows() { 405 // Populate a table with a bunch of integers. 406 mDatabase.execSQL("CREATE TABLE x (v INTEGER);"); 407 for (int i = 0; i < 100; i++) { 408 mDatabase.execSQL("INSERT INTO x VALUES (?)", new Object[] { i }); 409 } 410 411 for (int i = 0; i < 5; i++) { 412 final CancellationSignal cancellationSignal = new CancellationSignal(); 413 Thread cancellationThread = new Thread() { 414 @Override 415 public void run() { 416 try { 417 Thread.sleep(300); 418 } catch (InterruptedException ex) { 419 } 420 cancellationSignal.cancel(); 421 } 422 }; 423 try { 424 // Build an unsatisfiable 5-way cross-product query over 100 values but 425 // produces no output. This should force SQLite to loop for a long time 426 // as it tests 10^10 combinations. 427 SQLiteQueryBuilder sqliteQueryBuilder = new SQLiteQueryBuilder(); 428 sqliteQueryBuilder.setTables("x AS a, x AS b, x AS c, x AS d, x AS e"); 429 430 cancellationThread.start(); 431 432 final long startTime = System.nanoTime(); 433 try { 434 Cursor cursor = sqliteQueryBuilder.query(mDatabase, null, 435 "a.v + b.v + c.v + d.v + e.v > 1000000", 436 null, null, null, null, null, cancellationSignal); 437 cursor.getCount(); // force execution 438 fail("Expected OperationCanceledException"); 439 } catch (OperationCanceledException ex) { 440 // expected 441 } 442 443 // We want to confirm that the query really was running and then got 444 // canceled midway. 445 final long waitTime = System.nanoTime() - startTime; 446 if (waitTime > 150 * 1000000L && waitTime < 600 * 1000000L) { 447 return; // success! 448 } 449 } finally { 450 try { 451 cancellationThread.join(); 452 } catch (InterruptedException e) { 453 } 454 } 455 } 456 457 // Occasionally we might miss the timing deadline due to factors in the 458 // environment, but if after several trials we still couldn't demonstrate 459 // that the query was canceled, then the test must be broken. 460 fail("Could not prove that the query actually canceled midway during execution."); 461 } 462 463 private void createEmployeeTable() { 464 mDatabase.execSQL("CREATE TABLE employee (_id INTEGER PRIMARY KEY, " + 465 "name TEXT, month INTEGER, salary INTEGER);"); 466 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 467 "VALUES ('Mike', '1', '1000');"); 468 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 469 "VALUES ('Mike', '2', '3000');"); 470 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 471 "VALUES ('jack', '1', '2000');"); 472 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 473 "VALUES ('jack', '3', '1500');"); 474 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 475 "VALUES ('Jim', '1', '1000');"); 476 mDatabase.execSQL("INSERT INTO employee (name, month, salary) " + 477 "VALUES ('Jim', '3', '3500');"); 478 } 479 } 480
