1/* 2 * Copyright (C) 2006 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 17package android.database; 18 19import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_COLUMNNAME_INDEX; 20import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_DEFAULT_INDEX; 21 22import android.content.ContentValues; 23import android.content.Context; 24import android.database.sqlite.SQLiteDatabase; 25import android.database.sqlite.SQLiteDebug; 26import android.database.sqlite.SQLiteException; 27import android.os.Parcel; 28import android.support.test.InstrumentationRegistry; 29import android.support.test.uiautomator.UiDevice; 30import android.test.AndroidTestCase; 31import android.test.PerformanceTestCase; 32import android.test.suitebuilder.annotation.LargeTest; 33import android.test.suitebuilder.annotation.MediumTest; 34import android.test.suitebuilder.annotation.SmallTest; 35import android.util.Log; 36import android.util.Pair; 37 38import junit.framework.Assert; 39 40import java.io.File; 41import java.util.ArrayList; 42import java.util.Arrays; 43import java.util.List; 44import java.util.Locale; 45 46/** 47 * Usage: bit FrameworksCoreTests:android.database.DatabaseGeneralTest 48 */ 49public class DatabaseGeneralTest extends AndroidTestCase implements PerformanceTestCase { 50 private static final String TAG = "DatabaseGeneralTest"; 51 52 private static final String sString1 = "this is a test"; 53 private static final String sString2 = "and yet another test"; 54 private static final String sString3 = "this string is a little longer, but still a test"; 55 private static final String PHONE_NUMBER = "16175551212"; 56 57 private static final int CURRENT_DATABASE_VERSION = 42; 58 private SQLiteDatabase mDatabase; 59 private File mDatabaseFile; 60 61 @Override 62 protected void setUp() throws Exception { 63 super.setUp(); 64 File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); 65 mDatabaseFile = new File(dbDir, "database_test.db"); 66 if (mDatabaseFile.exists()) { 67 mDatabaseFile.delete(); 68 } 69 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); 70 assertNotNull(mDatabase); 71 mDatabase.setVersion(CURRENT_DATABASE_VERSION); 72 } 73 74 @Override 75 protected void tearDown() throws Exception { 76 mDatabase.close(); 77 SQLiteDatabase.deleteDatabase(mDatabaseFile); 78 super.tearDown(); 79 } 80 81 public boolean isPerformanceOnly() { 82 return false; 83 } 84 85 // These test can only be run once. 86 public int startPerformance(Intermediates intermediates) { 87 return 1; 88 } 89 90 private void populateDefaultTable() { 91 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 92 93 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');"); 94 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');"); 95 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');"); 96 } 97 98 @MediumTest 99 public void testVersion() throws Exception { 100 assertEquals(CURRENT_DATABASE_VERSION, mDatabase.getVersion()); 101 mDatabase.setVersion(11); 102 assertEquals(11, mDatabase.getVersion()); 103 } 104 105 @MediumTest 106 public void testUpdate() throws Exception { 107 populateDefaultTable(); 108 109 ContentValues values = new ContentValues(1); 110 values.put("data", "this is an updated test"); 111 assertEquals(1, mDatabase.update("test", values, "_id=1", null)); 112 Cursor c = mDatabase.query("test", null, "_id=1", null, null, null, null); 113 assertNotNull(c); 114 assertEquals(1, c.getCount()); 115 c.moveToFirst(); 116 String value = c.getString(c.getColumnIndexOrThrow("data")); 117 assertEquals("this is an updated test", value); 118 } 119 120 @MediumTest 121 public void testPhoneNumbersEqual() throws Exception { 122 mDatabase.execSQL("CREATE TABLE phones (num TEXT);"); 123 mDatabase.execSQL("INSERT INTO phones (num) VALUES ('911');"); 124 mDatabase.execSQL("INSERT INTO phones (num) VALUES ('5555');"); 125 mDatabase.execSQL("INSERT INTO phones (num) VALUES ('+" + PHONE_NUMBER + "');"); 126 127 String number; 128 Cursor c; 129 130 c = mDatabase.query("phones", null, 131 "PHONE_NUMBERS_EQUAL(num, '504-555-7683')", null, null, null, null); 132 assertTrue(c == null || c.getCount() == 0); 133 c.close(); 134 135 c = mDatabase.query("phones", null, 136 "PHONE_NUMBERS_EQUAL(num, '911')", null, null, null, null); 137 assertNotNull(c); 138 assertEquals(1, c.getCount()); 139 c.moveToFirst(); 140 number = c.getString(c.getColumnIndexOrThrow("num")); 141 assertEquals("911", number); 142 c.close(); 143 144 c = mDatabase.query("phones", null, 145 "PHONE_NUMBERS_EQUAL(num, '5555')", null, null, null, null); 146 assertNotNull(c); 147 assertEquals(1, c.getCount()); 148 c.moveToFirst(); 149 number = c.getString(c.getColumnIndexOrThrow("num")); 150 assertEquals("5555", number); 151 c.close(); 152 153 c = mDatabase.query("phones", null, 154 "PHONE_NUMBERS_EQUAL(num, '180055555555')", null, null, null, null); 155 assertTrue(c == null || c.getCount() == 0); 156 c.close(); 157 158 c = mDatabase.query("phones", null, 159 "PHONE_NUMBERS_EQUAL(num, '+" + PHONE_NUMBER + "')", null, null, null, null); 160 assertNotNull(c); 161 assertEquals(1, c.getCount()); 162 c.moveToFirst(); 163 number = c.getString(c.getColumnIndexOrThrow("num")); 164 assertEquals("+" + PHONE_NUMBER, number); 165 c.close(); 166 167 c = mDatabase.query("phones", null, 168 "PHONE_NUMBERS_EQUAL(num, '+1 (617).555-1212')", null, null, null, null); 169 assertNotNull(c); 170 assertEquals(1, c.getCount()); 171 c.moveToFirst(); 172 number = c.getString(c.getColumnIndexOrThrow("num")); 173 assertEquals("+" + PHONE_NUMBER, number); 174 c.close(); 175 176 c = mDatabase.query("phones", null, 177 "PHONE_NUMBERS_EQUAL(num, '" + PHONE_NUMBER + "')", null, null, null, null); 178 assertNotNull(c); 179 assertEquals(1, c.getCount()); 180 c.moveToFirst(); 181 number = c.getString(c.getColumnIndexOrThrow("num")); 182 assertEquals("+" + PHONE_NUMBER, number); 183 c.close(); 184 185 /* 186 c = mDatabase.query("phones", null, 187 "PHONE_NUMBERS_EQUAL(num, '5551212')", null, null, null, null); 188 assertNotNull(c); 189 assertEquals(1, c.getCount()); 190 c.moveToFirst(); 191 number = c.getString(c.getColumnIndexOrThrow("num")); 192 assertEquals("+" + PHONE_NUMBER, number); 193 c.close(); 194 */ 195 196 c = mDatabase.query("phones", null, 197 "PHONE_NUMBERS_EQUAL(num, '011" + PHONE_NUMBER + "')", null, null, null, null); 198 assertNotNull(c); 199 assertEquals(1, c.getCount()); 200 c.moveToFirst(); 201 number = c.getString(c.getColumnIndexOrThrow("num")); 202 assertEquals("+" + PHONE_NUMBER, number); 203 c.close(); 204 205 c = mDatabase.query("phones", null, 206 "PHONE_NUMBERS_EQUAL(num, '00" + PHONE_NUMBER + "')", null, null, null, null); 207 assertNotNull(c); 208 assertEquals(1, c.getCount()); 209 c.moveToFirst(); 210 number = c.getString(c.getColumnIndexOrThrow("num")); 211 assertEquals("+" + PHONE_NUMBER, number); 212 c.close(); 213 } 214 215 private void phoneNumberCompare(String phone1, String phone2, boolean equal, 216 boolean useStrictComparation) { 217 String[] temporalPhoneNumbers = new String[2]; 218 temporalPhoneNumbers[0] = phone1; 219 temporalPhoneNumbers[1] = phone2; 220 221 Cursor cursor = mDatabase.rawQuery( 222 String.format( 223 "SELECT CASE WHEN PHONE_NUMBERS_EQUAL(?, ?, %d) " + 224 "THEN 'equal' ELSE 'not equal' END", 225 (useStrictComparation ? 1 : 0)), 226 temporalPhoneNumbers); 227 try { 228 assertNotNull(cursor); 229 assertTrue(cursor.moveToFirst()); 230 if (equal) { 231 assertEquals(String.format("Unexpectedly, \"%s != %s\".", phone1, phone2), 232 "equal", cursor.getString(0)); 233 } else { 234 assertEquals(String.format("Unexpectedly, \"%s\" == \"%s\".", phone1, phone2), 235 "not equal", cursor.getString(0)); 236 } 237 } finally { 238 if (cursor != null) { 239 cursor.close(); 240 } 241 } 242 } 243 244 private void assertPhoneNumberEqual(String phone1, String phone2) throws Exception { 245 assertPhoneNumberEqual(phone1, phone2, true); 246 assertPhoneNumberEqual(phone1, phone2, false); 247 } 248 249 private void assertPhoneNumberEqual(String phone1, String phone2, boolean useStrict) 250 throws Exception { 251 phoneNumberCompare(phone1, phone2, true, useStrict); 252 } 253 254 private void assertPhoneNumberNotEqual(String phone1, String phone2) throws Exception { 255 assertPhoneNumberNotEqual(phone1, phone2, true); 256 assertPhoneNumberNotEqual(phone1, phone2, false); 257 } 258 259 private void assertPhoneNumberNotEqual(String phone1, String phone2, boolean useStrict) 260 throws Exception { 261 phoneNumberCompare(phone1, phone2, false, useStrict); 262 } 263 264 /** 265 * Tests international matching issues for the PHONE_NUMBERS_EQUAL function. 266 * 267 * @throws Exception 268 */ 269 @SmallTest 270 public void testPhoneNumbersEqualInternational() throws Exception { 271 assertPhoneNumberEqual("1", "1"); 272 assertPhoneNumberEqual("123123", "123123"); 273 assertPhoneNumberNotEqual("123123", "923123"); 274 assertPhoneNumberNotEqual("123123", "123129"); 275 assertPhoneNumberNotEqual("123123", "1231234"); 276 assertPhoneNumberNotEqual("123123", "0123123", false); 277 assertPhoneNumberNotEqual("123123", "0123123", true); 278 assertPhoneNumberEqual("650-253-0000", "6502530000"); 279 assertPhoneNumberEqual("650-253-0000", "650 253 0000"); 280 assertPhoneNumberEqual("650 253 0000", "6502530000"); 281 assertPhoneNumberEqual("+1 650-253-0000", "6502530000"); 282 assertPhoneNumberEqual("001 650-253-0000", "6502530000"); 283 assertPhoneNumberEqual("0111 650-253-0000", "6502530000"); 284 285 // Russian trunk digit 286 assertPhoneNumberEqual("+79161234567", "89161234567"); 287 288 // French trunk digit 289 assertPhoneNumberEqual("+33123456789", "0123456789"); 290 291 // Hungarian two digit trunk (currently only works for loose comparison) 292 assertPhoneNumberEqual("+36 1 234 5678", "06 1234-5678", false); 293 294 // Mexican two digit trunk (currently only works for loose comparison) 295 assertPhoneNumberEqual("+52 55 1234 5678", "01 55 1234 5678", false); 296 297 // Mongolian two digit trunk (currently only works for loose comparison) 298 assertPhoneNumberEqual("+976 1 123 4567", "01 1 23 4567", false); 299 assertPhoneNumberEqual("+976 2 234 5678", "02 2 34 5678", false); 300 301 // Trunk digit for city codes in the Netherlands 302 assertPhoneNumberEqual("+31771234567", "0771234567"); 303 304 // Test broken caller ID seen on call from Thailand to the US 305 assertPhoneNumberEqual("+66811234567", "166811234567"); 306 307 // Test the same in-country number with different country codes 308 assertPhoneNumberNotEqual("+33123456789", "+1123456789"); 309 310 // Test one number with country code and the other without 311 assertPhoneNumberEqual("5125551212", "+15125551212"); 312 313 // Test two NANP numbers that only differ in the area code 314 assertPhoneNumberNotEqual("5125551212", "6505551212"); 315 316 // Japanese phone numbers 317 assertPhoneNumberEqual("090-1234-5678", "+819012345678"); 318 assertPhoneNumberEqual("090(1234)5678", "+819012345678"); 319 assertPhoneNumberEqual("090-1234-5678", "+81-90-1234-5678"); 320 321 // Equador 322 assertPhoneNumberEqual("+593(800)123-1234", "8001231234"); 323 assertPhoneNumberEqual("+593-2-1234-123", "21234123"); 324 325 // Two continuous 0 at the beginning of the phone string should not be 326 // treated as trunk prefix in the strict comparation. 327 assertPhoneNumberEqual("008001231234", "8001231234", false); 328 assertPhoneNumberNotEqual("008001231234", "8001231234", true); 329 330 // Confirm that the bug found before does not re-appear 331 assertPhoneNumberNotEqual("080-1234-5678", "+819012345678"); 332 333 // Wrong prefix for Japan (currently only works for loose comparison) 334 assertPhoneNumberNotEqual("290-1234-5678", "+819012345678", false); 335 assertPhoneNumberNotEqual("+819012345678", "290-1234-5678", false); 336 337 // Wrong prefix for USA 338 assertPhoneNumberNotEqual("550-450-3605", "+14504503605"); 339 assertPhoneNumberNotEqual("550-450-3605", "+15404503605"); 340 assertPhoneNumberNotEqual("550-450-3605", "+15514503605"); 341 assertPhoneNumberNotEqual("5504503605", "+14504503605"); 342 assertPhoneNumberNotEqual("+14504503605", "550-450-3605"); 343 assertPhoneNumberNotEqual("+15404503605", "550-450-3605"); 344 assertPhoneNumberNotEqual("+15514503605", "550-450-3605"); 345 assertPhoneNumberNotEqual("+14504503605", "5504503605"); 346 } 347 348 @MediumTest 349 public void testCopyString() throws Exception { 350 mDatabase.execSQL("CREATE TABLE guess (numi INTEGER, numf FLOAT, str TEXT);"); 351 mDatabase.execSQL( 352 "INSERT INTO guess (numi,numf,str) VALUES (0,0.0,'ZoomZoomZoomZoom');"); 353 mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (2000000000,3.1415926535,'');"); 354 String chinese = "\u4eac\u4ec5 \u5c3d\u5f84\u60ca"; 355 String[] arr = new String[1]; 356 arr[0] = chinese; 357 mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (-32768,-1.0,?)", arr); 358 359 Cursor c; 360 361 c = mDatabase.rawQuery("SELECT * FROM guess", null); 362 363 c.moveToFirst(); 364 365 CharArrayBuffer buf = new CharArrayBuffer(14); 366 367 String compareTo = c.getString(c.getColumnIndexOrThrow("numi")); 368 int numiIdx = c.getColumnIndexOrThrow("numi"); 369 int numfIdx = c.getColumnIndexOrThrow("numf"); 370 int strIdx = c.getColumnIndexOrThrow("str"); 371 372 c.copyStringToBuffer(numiIdx, buf); 373 assertEquals(1, buf.sizeCopied); 374 assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied)); 375 376 c.copyStringToBuffer(strIdx, buf); 377 assertEquals("ZoomZoomZoomZoom", new String(buf.data, 0, buf.sizeCopied)); 378 379 c.moveToNext(); 380 compareTo = c.getString(numfIdx); 381 382 c.copyStringToBuffer(numfIdx, buf); 383 assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied)); 384 c.copyStringToBuffer(strIdx, buf); 385 assertEquals(0, buf.sizeCopied); 386 387 c.moveToNext(); 388 c.copyStringToBuffer(numfIdx, buf); 389 assertEquals(-1.0, Double.valueOf( 390 new String(buf.data, 0, buf.sizeCopied)).doubleValue()); 391 392 c.copyStringToBuffer(strIdx, buf); 393 compareTo = c.getString(strIdx); 394 assertEquals(chinese, compareTo); 395 396 assertEquals(chinese, new String(buf.data, 0, buf.sizeCopied)); 397 c.close(); 398 } 399 400 @MediumTest 401 public void testSchemaChange1() throws Exception { 402 SQLiteDatabase db1 = mDatabase; 403 Cursor cursor; 404 405 db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);"); 406 407 cursor = db1.query("db1", null, null, null, null, null, null); 408 assertNotNull("Cursor is null", cursor); 409 410 db1.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);"); 411 412 assertEquals(0, cursor.getCount()); 413 cursor.deactivate(); 414 } 415 416 @MediumTest 417 public void testSchemaChange2() throws Exception { 418 mDatabase.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);"); 419 Cursor cursor = mDatabase.query("db1", null, null, null, null, null, null); 420 assertNotNull(cursor); 421 assertEquals(0, cursor.getCount()); 422 cursor.close(); 423 } 424 425 @MediumTest 426 public void testSchemaChange3() throws Exception { 427 mDatabase.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);"); 428 mDatabase.execSQL("INSERT INTO db1 (data) VALUES ('test');"); 429 mDatabase.execSQL("ALTER TABLE db1 ADD COLUMN blah int;"); 430 Cursor c = null; 431 try { 432 c = mDatabase.rawQuery("select blah from db1", null); 433 } catch (SQLiteException e) { 434 fail("unexpected exception: " + e.getMessage()); 435 } finally { 436 if (c != null) { 437 c.close(); 438 } 439 } 440 } 441 442 @MediumTest 443 public void testSelectionArgs() throws Exception { 444 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 445 ContentValues values = new ContentValues(1); 446 values.put("data", "don't forget to handled 's"); 447 mDatabase.insert("test", "data", values); 448 values.clear(); 449 values.put("data", "no apostrophes here"); 450 mDatabase.insert("test", "data", values); 451 Cursor c = mDatabase.query( 452 "test", null, "data GLOB ?", new String[]{"*'*"}, null, null, null); 453 assertEquals(1, c.getCount()); 454 assertTrue(c.moveToFirst()); 455 assertEquals("don't forget to handled 's", c.getString(1)); 456 c.deactivate(); 457 458 // make sure code should checking null string properly so that 459 // it won't crash 460 try { 461 mDatabase.query("test", new String[]{"_id"}, 462 "_id=?", new String[]{null}, null, null, null); 463 fail("expected exception not thrown"); 464 } catch (IllegalArgumentException e) { 465 // expected 466 } 467 } 468 469 @MediumTest 470 public void testTokenize() throws Exception { 471 Cursor c; 472 mDatabase.execSQL("CREATE TABLE tokens (" + 473 "token TEXT COLLATE unicode," + 474 "source INTEGER," + 475 "token_index INTEGER," + 476 "tag TEXT" + 477 ");"); 478 mDatabase.execSQL("CREATE TABLE tokens_no_index (" + 479 "token TEXT COLLATE unicode," + 480 "source INTEGER" + 481 ");"); 482 483 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 484 "SELECT _TOKENIZE(NULL, NULL, NULL, NULL)", null)); 485 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 486 "SELECT _TOKENIZE('tokens', NULL, NULL, NULL)", null)); 487 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 488 "SELECT _TOKENIZE('tokens', 10, NULL, NULL)", null)); 489 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 490 "SELECT _TOKENIZE('tokens', 10, 'some string', NULL)", null)); 491 492 Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase, 493 "SELECT _TOKENIZE('tokens', 11, 'some string ok', ' ', 1, 'foo')", null)); 494 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 495 "SELECT _TOKENIZE('tokens', 11, 'second field', ' ', 1, 'bar')", null)); 496 497 Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase, 498 "SELECT _TOKENIZE('tokens_no_index', 20, 'some string ok', ' ')", null)); 499 Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase, 500 "SELECT _TOKENIZE('tokens_no_index', 21, 'foo bar baz', ' ', 0)", null)); 501 502 // test Chinese 503 String chinese = new String("\u4eac\u4ec5 \u5c3d\u5f84\u60ca"); 504 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 505 "SELECT _TOKENIZE('tokens', 12,'" + chinese + "', ' ', 1)", null)); 506 507 String icustr = new String("Fr\u00e9d\u00e9ric Hj\u00f8nnev\u00e5g"); 508 509 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 510 "SELECT _TOKENIZE('tokens', 13, '" + icustr + "', ' ', 1)", null)); 511 512 Assert.assertEquals(9, DatabaseUtils.longForQuery(mDatabase, 513 "SELECT count(*) from tokens;", null)); 514 515 String key = DatabaseUtils.getHexCollationKey("Frederic Hjonneva"); 516 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 517 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 518 Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase, 519 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 520 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 521 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 522 key = DatabaseUtils.getHexCollationKey("Hjonneva"); 523 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 524 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 525 Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase, 526 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 527 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 528 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 529 530 key = DatabaseUtils.getHexCollationKey("some string ok"); 531 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 532 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 533 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 534 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 535 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 536 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 537 Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase, 538 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 539 key = DatabaseUtils.getHexCollationKey("string"); 540 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 541 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 542 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 543 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 544 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 545 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 546 Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase, 547 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 548 key = DatabaseUtils.getHexCollationKey("ok"); 549 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 550 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 551 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 552 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 553 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 554 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 555 Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase, 556 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 557 558 key = DatabaseUtils.getHexCollationKey("second field"); 559 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 560 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 561 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 562 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 563 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 564 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 565 Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase, 566 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 567 key = DatabaseUtils.getHexCollationKey("field"); 568 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 569 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 570 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 571 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 572 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 573 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 574 Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase, 575 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 576 577 key = DatabaseUtils.getHexCollationKey(chinese); 578 String[] a = new String[1]; 579 a[0] = key; 580 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 581 "SELECT count(*) from tokens where token= ?", a)); 582 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 583 "SELECT source from tokens where token= ?", a)); 584 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 585 "SELECT token_index from tokens where token= ?", a)); 586 a[0] += "*"; 587 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 588 "SELECT count(*) from tokens where token GLOB ?", a)); 589 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 590 "SELECT source from tokens where token GLOB ?", a)); 591 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 592 "SELECT token_index from tokens where token GLOB ?", a)); 593 594 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 595 "SELECT count(*) from tokens where token= '" + key + "'", null)); 596 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 597 "SELECT source from tokens where token= '" + key + "'", null)); 598 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 599 "SELECT token_index from tokens where token= '" + key + "'", null)); 600 601 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 602 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 603 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 604 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 605 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 606 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 607 608 key = DatabaseUtils.getHexCollationKey("\u4eac\u4ec5"); 609 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 610 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 611 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 612 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 613 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 614 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 615 616 key = DatabaseUtils.getHexCollationKey("\u5c3d\u5f84\u60ca"); 617 Log.d("DatabaseGeneralTest", "key = " + key); 618 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 619 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 620 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 621 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 622 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 623 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 624 625 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 626 "SELECT count(*) from tokens where token GLOB 'ab*'", null)); 627 628 key = DatabaseUtils.getHexCollationKey("some string ok"); 629 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 630 "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null)); 631 Assert.assertEquals(20, DatabaseUtils.longForQuery(mDatabase, 632 "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null)); 633 634 key = DatabaseUtils.getHexCollationKey("bar"); 635 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 636 "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null)); 637 Assert.assertEquals(21, DatabaseUtils.longForQuery(mDatabase, 638 "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null)); 639 } 640 641 @MediumTest 642 public void testTransactions() throws Exception { 643 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 644 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 645 646 // Make sure that things work outside an explicit transaction. 647 setNum(1); 648 checkNum(1); 649 650 // Test a single-level transaction. 651 setNum(0); 652 mDatabase.beginTransaction(); 653 setNum(1); 654 mDatabase.setTransactionSuccessful(); 655 mDatabase.endTransaction(); 656 checkNum(1); 657 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 658 659 // Test a rolled-back transaction. 660 setNum(0); 661 mDatabase.beginTransaction(); 662 setNum(1); 663 mDatabase.endTransaction(); 664 checkNum(0); 665 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 666 667 // We should get an error if we end a non-existent transaction. 668 assertThrowsIllegalState(new Runnable() { public void run() { 669 mDatabase.endTransaction(); 670 }}); 671 672 // We should get an error if a set a non-existent transaction as clean. 673 assertThrowsIllegalState(new Runnable() { public void run() { 674 mDatabase.setTransactionSuccessful(); 675 }}); 676 677 mDatabase.beginTransaction(); 678 mDatabase.setTransactionSuccessful(); 679 // We should get an error if we mark a transaction as clean twice. 680 assertThrowsIllegalState(new Runnable() { public void run() { 681 mDatabase.setTransactionSuccessful(); 682 }}); 683 // We should get an error if we begin a transaction after marking the parent as clean. 684 assertThrowsIllegalState(new Runnable() { public void run() { 685 mDatabase.beginTransaction(); 686 }}); 687 mDatabase.endTransaction(); 688 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 689 690 // Test a two-level transaction. 691 setNum(0); 692 mDatabase.beginTransaction(); 693 mDatabase.beginTransaction(); 694 setNum(1); 695 mDatabase.setTransactionSuccessful(); 696 mDatabase.endTransaction(); 697 mDatabase.setTransactionSuccessful(); 698 mDatabase.endTransaction(); 699 checkNum(1); 700 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 701 702 // Test rolling back an inner transaction. 703 setNum(0); 704 mDatabase.beginTransaction(); 705 mDatabase.beginTransaction(); 706 setNum(1); 707 mDatabase.endTransaction(); 708 mDatabase.setTransactionSuccessful(); 709 mDatabase.endTransaction(); 710 checkNum(0); 711 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 712 713 // Test rolling back an outer transaction. 714 setNum(0); 715 mDatabase.beginTransaction(); 716 mDatabase.beginTransaction(); 717 setNum(1); 718 mDatabase.setTransactionSuccessful(); 719 mDatabase.endTransaction(); 720 mDatabase.endTransaction(); 721 checkNum(0); 722 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 723 } 724 725 private void setNum(int num) { 726 mDatabase.execSQL("UPDATE test SET num = " + num); 727 } 728 729 private void checkNum(int num) { 730 Assert.assertEquals( 731 num, DatabaseUtils.longForQuery(mDatabase, "SELECT num FROM test", null)); 732 } 733 734 private void assertThrowsIllegalState(Runnable r) { 735 boolean ok = false; 736 try { 737 r.run(); 738 } catch (IllegalStateException e) { 739 ok = true; 740 } 741 Assert.assertTrue(ok); 742 } 743 744 // Disable these until we can explicitly mark them as stress tests 745 public void xxtestMem1() throws Exception { 746 populateDefaultTable(); 747 748 for (int i = 0; i < 50000; i++) { 749 Cursor cursor = mDatabase.query("test", null, null, null, null, null, null); 750 cursor.moveToFirst(); 751 cursor.close(); 752// Log.i("~~~~", "Finished round " + i); 753 } 754 } 755 756 // Disable these until we can explicitly mark them as stress tests 757 public void xxtestMem2() throws Exception { 758 populateDefaultTable(); 759 760 for (int i = 0; i < 50000; i++) { 761 Cursor cursor = mDatabase.query("test", null, null, null, null, null, null); 762 cursor.close(); 763// Log.i("~~~~", "Finished round " + i); 764 } 765 } 766 767 // Disable these until we can explicitly mark them as stress tests 768 public void xxtestMem3() throws Exception { 769 populateDefaultTable(); 770 771 for (int i = 0; i < 50000; i++) { 772 Cursor cursor = mDatabase.query("test", null, null, null, null, null, null); 773 cursor.deactivate(); 774// Log.i("~~~~", "Finished round " + i); 775 } 776 } 777 778 @MediumTest 779 public void testContentValues() throws Exception { 780 ContentValues values = new ContentValues(); 781 values.put("string", "value"); 782 assertEquals("value", values.getAsString("string")); 783 byte[] bytes = new byte[42]; 784 Arrays.fill(bytes, (byte) 0x28); 785 values.put("byteArray", bytes); 786 assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray"))); 787 788 // Write the ContentValues to a Parcel and then read them out 789 Parcel p = Parcel.obtain(); 790 values.writeToParcel(p, 0); 791 p.setDataPosition(0); 792 values = ContentValues.CREATOR.createFromParcel(p); 793 794 // Read the values out again and make sure they're the same 795 assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray"))); 796 assertEquals("value", values.get("string")); 797 } 798 799 @MediumTest 800 public void testTableInfoPragma() throws Exception { 801 mDatabase.execSQL("CREATE TABLE pragma_test (" + 802 "i INTEGER DEFAULT 1234, " + 803 "j INTEGER, " + 804 "s TEXT DEFAULT 'hello', " + 805 "t TEXT, " + 806 "'select' TEXT DEFAULT \"hello\")"); 807 try { 808 Cursor cur = mDatabase.rawQuery("PRAGMA table_info(pragma_test)", null); 809 Assert.assertEquals(5, cur.getCount()); 810 811 Assert.assertTrue(cur.moveToNext()); 812 Assert.assertEquals("i", 813 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 814 Assert.assertEquals("1234", 815 cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 816 817 Assert.assertTrue(cur.moveToNext()); 818 Assert.assertEquals("j", 819 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 820 Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 821 822 Assert.assertTrue(cur.moveToNext()); 823 Assert.assertEquals("s", 824 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 825 Assert.assertEquals("'hello'", 826 cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 827 828 Assert.assertTrue(cur.moveToNext()); 829 Assert.assertEquals("t", 830 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 831 Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 832 833 Assert.assertTrue(cur.moveToNext()); 834 Assert.assertEquals("select", 835 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 836 Assert.assertEquals("\"hello\"", 837 cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 838 839 cur.close(); 840 } catch (Throwable t) { 841 throw new RuntimeException( 842 "If you see this test fail, it's likely that something about " + 843 "sqlite's PRAGMA table_info(...) command has changed.", t); 844 } 845 } 846 847 @MediumTest 848 public void testInsertHelper() throws Exception { 849 Cursor cur; 850 ContentValues cv; 851 long row; 852 853 mDatabase.execSQL("CREATE TABLE insert_test (" + 854 "_id INTEGER PRIMARY KEY, " + 855 "s TEXT NOT NULL UNIQUE, " + 856 "t TEXT NOT NULL DEFAULT 'hello world', " + 857 "i INTEGER, " + 858 "j INTEGER NOT NULL DEFAULT 1234, " + 859 "'select' TEXT)"); 860 861 DatabaseUtils.InsertHelper ih = 862 new DatabaseUtils.InsertHelper(mDatabase, "insert_test"); 863 864 cv = new ContentValues(); 865 cv.put("s", "one"); 866 row = ih.insert(cv); 867 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 868 Assert.assertTrue(cur.moveToFirst()); 869 Assert.assertEquals("one", cur.getString(1)); 870 Assert.assertEquals("hello world", cur.getString(2)); 871 Assert.assertNull(cur.getString(3)); 872 Assert.assertEquals(1234, cur.getLong(4)); 873 Assert.assertNull(cur.getString(5)); 874 cur.close(); 875 876 cv = new ContentValues(); 877 cv.put("s", "two"); 878 cv.put("t", "goodbye world"); 879 row = ih.insert(cv); 880 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 881 Assert.assertTrue(cur.moveToFirst()); 882 Assert.assertEquals("two", cur.getString(1)); 883 Assert.assertEquals("goodbye world", cur.getString(2)); 884 Assert.assertNull(cur.getString(3)); 885 Assert.assertEquals(1234, cur.getLong(4)); 886 Assert.assertNull(cur.getString(5)); 887 cur.close(); 888 889 cv = new ContentValues(); 890 cv.put("t", "goodbye world"); 891 row = ih.insert(cv); 892 Assert.assertEquals(-1, row); 893 894 cv = new ContentValues(); 895 cv.put("s", "three"); 896 cv.put("i", 2345); 897 cv.put("j", 3456); 898 cv.put("select", "tricky"); 899 row = ih.insert(cv); 900 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 901 Assert.assertTrue(cur.moveToFirst()); 902 Assert.assertEquals("three", cur.getString(1)); 903 Assert.assertEquals("hello world", cur.getString(2)); 904 Assert.assertEquals(2345, cur.getLong(3)); 905 Assert.assertEquals(3456, cur.getLong(4)); 906 Assert.assertEquals("tricky", cur.getString(5)); 907 cur.close(); 908 909 cv = new ContentValues(); 910 cv.put("s", "three"); 911 cv.put("i", 6789); 912 row = ih.insert(cv); 913 Assert.assertEquals(-1, row); 914 row = ih.replace(cv); 915 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 916 Assert.assertTrue(cur.moveToFirst()); 917 Assert.assertEquals("three", cur.getString(1)); 918 Assert.assertEquals("hello world", cur.getString(2)); 919 Assert.assertEquals(6789, cur.getLong(3)); 920 cur.close(); 921 922 ih.close(); 923 } 924 925 @MediumTest 926 public void testSemicolonsInStatements() throws Exception { 927 mDatabase.execSQL("CREATE TABLE pragma_test (" + 928 "i INTEGER DEFAULT 1234, " + 929 "j INTEGER, " + 930 "s TEXT DEFAULT 'hello', " + 931 "t TEXT, " + 932 "'select' TEXT DEFAULT \"hello\")"); 933 try { 934 // ending the sql statement with semicolons shouldn't be a problem. 935 Cursor cur = mDatabase.rawQuery("PRAGMA database_list;", null); 936 cur.close(); 937 // two semicolons in the statement shouldn't be a problem. 938 cur = mDatabase.rawQuery("PRAGMA database_list;;", null); 939 cur.close(); 940 } catch (Throwable t) { 941 fail("unexpected, of course"); 942 } 943 } 944 945 @MediumTest 946 public void testUnionsWithBindArgs() { 947 /* make sure unions with bindargs work http://b/issue?id=1061291 */ 948 mDatabase.execSQL("CREATE TABLE A (i int);"); 949 mDatabase.execSQL("create table B (k int);"); 950 mDatabase.execSQL("create table C (n int);"); 951 mDatabase.execSQL("insert into A values(1);"); 952 mDatabase.execSQL("insert into A values(2);"); 953 mDatabase.execSQL("insert into A values(3);"); 954 mDatabase.execSQL("insert into B values(201);"); 955 mDatabase.execSQL("insert into B values(202);"); 956 mDatabase.execSQL("insert into B values(203);"); 957 mDatabase.execSQL("insert into C values(901);"); 958 mDatabase.execSQL("insert into C values(902);"); 959 String s = "select i from A where i > 2 " + 960 "UNION select k from B where k > 201 " + 961 "UNION select n from C where n !=900;"; 962 Cursor c = mDatabase.rawQuery(s, null); 963 int n = c.getCount(); 964 c.close(); 965 String s1 = "select i from A where i > ? " + 966 "UNION select k from B where k > ? " + 967 "UNION select n from C where n != ?;"; 968 Cursor c1 = mDatabase.rawQuery(s1, new String[]{"2", "201", "900"}); 969 assertEquals(n, c1.getCount()); 970 c1.close(); 971 } 972 973 /** 974 * This test is available only when the platform has a locale with the language "ja". 975 * It finishes without failure when it is not available. 976 */ 977 @MediumTest 978 public void testCollateLocalizedForJapanese() throws Exception { 979 final String testName = "DatabaseGeneralTest#testCollateLocalizedForJapanese()"; 980 final Locale[] localeArray = Locale.getAvailableLocales(); 981 final String japanese = Locale.JAPANESE.getLanguage(); 982 final String english = Locale.ENGLISH.getLanguage(); 983 Locale japaneseLocale = null; 984 Locale englishLocale = null; 985 for (Locale locale : localeArray) { 986 if (locale != null) { 987 final String language = locale.getLanguage(); 988 if (language == null) { 989 continue; 990 } else if (language.equals(japanese)) { 991 japaneseLocale = locale; 992 } else if (language.equals(english)) { 993 englishLocale = locale; 994 } 995 } 996 997 if (japaneseLocale != null && englishLocale != null) { 998 break; 999 } 1000 } 1001 1002 if (japaneseLocale == null || englishLocale == null) { 1003 Log.d(TAG, testName + "n is silently skipped since " + 1004 (englishLocale == null ? 1005 (japaneseLocale == null ? 1006 "Both English and Japanese locales do not exist." : 1007 "English locale does not exist.") : 1008 (japaneseLocale == null ? 1009 "Japanese locale does not exist." : 1010 "...why?"))); 1011 return; 1012 } 1013 1014 Locale originalLocale = Locale.getDefault(); 1015 try { 1016 1017 final String dbName = "collate_localized_test"; 1018 mDatabase.execSQL("CREATE TABLE " + dbName + " (" + 1019 "_id INTEGER PRIMARY KEY, " + 1020 "s TEXT COLLATE LOCALIZED) "); 1021 DatabaseUtils.InsertHelper ih = 1022 new DatabaseUtils.InsertHelper(mDatabase, dbName); 1023 ContentValues cv = new ContentValues(); 1024 1025 cv = new ContentValues(); // 1026 cv.put("s", "\uFF75\uFF77\uFF85\uFF9C"); // O-ki-na-wa in half-width Katakana 1027 ih.insert(cv); 1028 1029 cv = new ContentValues(); // 1030 cv.put("s", "\u306B\u307B\u3093"); // Ni-ho-n in Hiragana 1031 ih.insert(cv); 1032 1033 cv = new ContentValues(); // 1034 cv.put("s", "\u30A2\u30E1\u30EA\u30AB"); // A-me-ri-ca in hull-width Katakana 1035 ih.insert(cv); 1036 1037 // Assume setLocale() does REINDEX and an English locale does not consider 1038 // Japanese-specific LOCALIZED order. 1039 Locale.setDefault(englishLocale); 1040 Locale.setDefault(japaneseLocale); 1041 1042 Cursor cur = mDatabase.rawQuery( 1043 "SELECT * FROM " + dbName + " ORDER BY s", null); 1044 assertTrue(cur.moveToFirst()); 1045 assertEquals("\u30A2\u30E1\u30EA\u30AB", cur.getString(1)); 1046 assertTrue(cur.moveToNext()); 1047 assertEquals("\uFF75\uFF77\uFF85\uFF9C", cur.getString(1)); 1048 assertTrue(cur.moveToNext()); 1049 assertEquals("\u306B\u307B\u3093", cur.getString(1)); 1050 } finally { 1051 if (originalLocale != null) { 1052 try { 1053 Locale.setDefault(originalLocale); 1054 } catch (Exception e) { 1055 } 1056 } 1057 } 1058 } 1059 1060 @SmallTest 1061 public void testSetMaxCahesize() { 1062 mDatabase.execSQL("CREATE TABLE test (i int, j int);"); 1063 mDatabase.execSQL("insert into test values(1,1);"); 1064 // set cache size 1065 int N = SQLiteDatabase.MAX_SQL_CACHE_SIZE; 1066 mDatabase.setMaxSqlCacheSize(N); 1067 1068 // try reduce cachesize 1069 try { 1070 mDatabase.setMaxSqlCacheSize(1); 1071 } catch (IllegalStateException e) { 1072 assertTrue(e.getMessage().contains("cannot set cacheSize to a value less than")); 1073 } 1074 } 1075 1076 @SmallTest 1077 public void testOpenDatabaseLookasideConfig() { 1078 // First check that lookaside is active 1079 verifyLookasideStats(false); 1080 // Reopen test db with lookaside disabled 1081 mDatabase.close(); 1082 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1083 .setLookasideConfig(0, 0).build(); 1084 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1085 verifyLookasideStats(true); 1086 } 1087 1088 @SmallTest 1089 public void testOpenParamsSetLookasideConfigValidation() { 1090 try { 1091 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1092 .setLookasideConfig(-1, 0).build(); 1093 fail("Negative slot size should be rejected"); 1094 } catch (IllegalArgumentException expected) { 1095 } 1096 try { 1097 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1098 .setLookasideConfig(0, -10).build(); 1099 fail("Negative slot count should be rejected"); 1100 } catch (IllegalArgumentException expected) { 1101 } 1102 } 1103 1104 void verifyLookasideStats(boolean expectDisabled) { 1105 boolean dbStatFound = false; 1106 SQLiteDebug.PagerStats info = SQLiteDebug.getDatabaseInfo(); 1107 for (SQLiteDebug.DbStats dbStat : info.dbStats) { 1108 if (dbStat.dbName.endsWith(mDatabaseFile.getName())) { 1109 dbStatFound = true; 1110 Log.i(TAG, "Lookaside for " + dbStat.dbName + " " + dbStat.lookaside); 1111 if (expectDisabled) { 1112 assertTrue("lookaside slots count should be zero", dbStat.lookaside == 0); 1113 } else { 1114 assertTrue("lookaside slots count should be greater than zero", 1115 dbStat.lookaside > 0); 1116 } 1117 } 1118 } 1119 assertTrue("No dbstat found for " + mDatabaseFile.getName(), dbStatFound); 1120 } 1121 1122 @LargeTest 1123 public void testDefaultDatabaseErrorHandler() { 1124 DefaultDatabaseErrorHandler errorHandler = new DefaultDatabaseErrorHandler(); 1125 1126 // close the database. and call corruption handler. 1127 // it should delete the database file. 1128 File dbfile = new File(mDatabase.getPath()); 1129 mDatabase.close(); 1130 assertFalse(mDatabase.isOpen()); 1131 assertTrue(dbfile.exists()); 1132 try { 1133 errorHandler.onCorruption(mDatabase); 1134 assertFalse(dbfile.exists()); 1135 } catch (Exception e) { 1136 fail("unexpected"); 1137 } 1138 1139 // create an in-memory database. and corruption handler shouldn't try to delete it 1140 SQLiteDatabase memoryDb = SQLiteDatabase.openOrCreateDatabase(":memory:", null); 1141 assertNotNull(memoryDb); 1142 memoryDb.close(); 1143 assertFalse(memoryDb.isOpen()); 1144 try { 1145 errorHandler.onCorruption(memoryDb); 1146 } catch (Exception e) { 1147 fail("unexpected"); 1148 } 1149 1150 // create a database, keep it open, call corruption handler. database file should be deleted 1151 SQLiteDatabase dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null); 1152 assertTrue(dbfile.exists()); 1153 assertNotNull(dbObj); 1154 assertTrue(dbObj.isOpen()); 1155 try { 1156 errorHandler.onCorruption(dbObj); 1157 assertFalse(dbfile.exists()); 1158 } catch (Exception e) { 1159 fail("unexpected"); 1160 } 1161 1162 // create a database, attach 2 more databases to it 1163 // attached database # 1: ":memory:" 1164 // attached database # 2: mDatabase.getPath() + "1"; 1165 // call corruption handler. database files including the one for attached database # 2 1166 // should be deleted 1167 String attachedDb1File = mDatabase.getPath() + "1"; 1168 dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null); 1169 dbObj.execSQL("ATTACH DATABASE ':memory:' as memoryDb"); 1170 dbObj.execSQL("ATTACH DATABASE '" + attachedDb1File + "' as attachedDb1"); 1171 assertTrue(dbfile.exists()); 1172 assertTrue(new File(attachedDb1File).exists()); 1173 assertNotNull(dbObj); 1174 assertTrue(dbObj.isOpen()); 1175 List<Pair<String, String>> attachedDbs = dbObj.getAttachedDbs(); 1176 try { 1177 errorHandler.onCorruption(dbObj); 1178 assertFalse(dbfile.exists()); 1179 assertFalse(new File(attachedDb1File).exists()); 1180 } catch (Exception e) { 1181 fail("unexpected"); 1182 } 1183 1184 // same as above, except this is a bit of stress testing. attach 5 database files 1185 // and make sure they are all removed. 1186 int N = 5; 1187 ArrayList<String> attachedDbFiles = new ArrayList<String>(N); 1188 for (int i = 0; i < N; i++) { 1189 attachedDbFiles.add(mDatabase.getPath() + i); 1190 } 1191 dbObj = SQLiteDatabase.openOrCreateDatabase(mDatabase.getPath(), null); 1192 dbObj.execSQL("ATTACH DATABASE ':memory:' as memoryDb"); 1193 for (int i = 0; i < N; i++) { 1194 dbObj.execSQL("ATTACH DATABASE '" + attachedDbFiles.get(i) + "' as attachedDb" + i); 1195 } 1196 assertTrue(dbfile.exists()); 1197 for (int i = 0; i < N; i++) { 1198 assertTrue(new File(attachedDbFiles.get(i)).exists()); 1199 } 1200 assertNotNull(dbObj); 1201 assertTrue(dbObj.isOpen()); 1202 attachedDbs = dbObj.getAttachedDbs(); 1203 try { 1204 errorHandler.onCorruption(dbObj); 1205 assertFalse(dbfile.exists()); 1206 for (int i = 0; i < N; i++) { 1207 assertFalse(new File(attachedDbFiles.get(i)).exists()); 1208 } 1209 } catch (Exception e) { 1210 fail("unexpected"); 1211 } 1212 } 1213 1214 @MediumTest 1215 public void testCloseIdleConnection() throws Exception { 1216 mDatabase.close(); 1217 SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder() 1218 .setIdleConnectionTimeout(1000).build(); 1219 mDatabase = SQLiteDatabase.openDatabase(mDatabaseFile, params); 1220 // Wait a bit and check that connection is still open 1221 Thread.sleep(100); 1222 String output = executeShellCommand("dumpsys dbinfo " + getContext().getPackageName()); 1223 assertTrue("Connection #0 should be open. Output: " + output, 1224 output.contains("Connection #0:")); 1225 1226 // Now cause idle timeout and check that connection is closed 1227 Thread.sleep(1000); 1228 output = executeShellCommand("dumpsys dbinfo " + getContext().getPackageName()); 1229 assertFalse("Connection #0 should be closed. Output: " + output, 1230 output.contains("Connection #0:")); 1231 } 1232 1233 @SmallTest 1234 public void testSetIdleConnectionTimeoutValidation() throws Exception { 1235 try { 1236 new SQLiteDatabase.OpenParams.Builder().setIdleConnectionTimeout(-1).build(); 1237 fail("Negative timeout should be rejected"); 1238 } catch (IllegalArgumentException expected) { 1239 } 1240 } 1241 1242 private String executeShellCommand(String cmd) throws Exception { 1243 return UiDevice.getInstance( 1244 InstrumentationRegistry.getInstrumentation()).executeShellCommand(cmd); 1245 } 1246 1247 @SmallTest 1248 public void testSavepointRollbacks() { 1249 try (SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(":memory:", null)) { 1250 db.execSQL("drop table if exists data"); 1251 db.execSQL("create table if not exists data (id INTEGER PRIMARY KEY, val TEXT)"); 1252 db.execSQL("begin deferred transaction"); 1253 db.execSQL("insert into data (val) values('row 1')"); 1254 db.execSQL("savepoint foo"); 1255 db.execSQL("insert into data (val) values('row 2')"); 1256 db.execSQL("rollback to foo"); 1257 db.execSQL("commit transaction"); 1258 long rowCount = DatabaseUtils.longForQuery(db, "select count(*) from data", null); 1259 assertEquals(1, rowCount); 1260 } 1261 } 1262} 1263