DatabaseGeneralTest.java revision 1a44d5dcabc18cd5ef111f732ccff91683a1a093
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 android.content.ContentValues; 20import android.content.Context; 21import android.database.ContentObserver; 22import android.database.Cursor; 23import android.database.DatabaseUtils; 24import android.database.CharArrayBuffer; 25import android.database.sqlite.SQLiteDatabase; 26import android.database.sqlite.SQLiteStatement; 27import android.os.Environment; 28import android.os.Handler; 29import android.os.Parcel; 30import android.test.AndroidTestCase; 31import android.test.PerformanceTestCase; 32import android.test.suitebuilder.annotation.MediumTest; 33import android.test.suitebuilder.annotation.SmallTest; 34import android.util.Log; 35 36import java.io.File; 37import java.io.UnsupportedEncodingException; 38import java.text.Collator; 39import java.util.Arrays; 40 41import junit.framework.Assert; 42import junit.framework.TestCase; 43 44import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_COLUMNNAME_INDEX; 45import static android.database.DatabaseUtils.InsertHelper.TABLE_INFO_PRAGMA_DEFAULT_INDEX; 46 47public class DatabaseGeneralTest extends AndroidTestCase implements PerformanceTestCase { 48 49 private static final String sString1 = "this is a test"; 50 private static final String sString2 = "and yet another test"; 51 private static final String sString3 = "this string is a little longer, but still a test"; 52 private static final String PHONE_NUMBER = "16175551212"; 53 54 private static final int CURRENT_DATABASE_VERSION = 42; 55 private SQLiteDatabase mDatabase; 56 private File mDatabaseFile; 57 58 @Override 59 protected void setUp() throws Exception { 60 super.setUp(); 61 File dbDir = getContext().getDir(this.getClass().getName(), Context.MODE_PRIVATE); 62 mDatabaseFile = new File(dbDir, "database_test.db"); 63 if (mDatabaseFile.exists()) { 64 mDatabaseFile.delete(); 65 } 66 mDatabase = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile.getPath(), null); 67 assertNotNull(mDatabase); 68 mDatabase.setVersion(CURRENT_DATABASE_VERSION); 69 } 70 71 @Override 72 protected void tearDown() throws Exception { 73 mDatabase.close(); 74 mDatabaseFile.delete(); 75 super.tearDown(); 76 } 77 78 public boolean isPerformanceOnly() { 79 return false; 80 } 81 82 // These test can only be run once. 83 public int startPerformance(Intermediates intermediates) { 84 return 1; 85 } 86 87 private void populateDefaultTable() { 88 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 89 90 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');"); 91 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');"); 92 mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');"); 93 } 94 95 @MediumTest 96 public void testVersion() throws Exception { 97 assertEquals(CURRENT_DATABASE_VERSION, mDatabase.getVersion()); 98 mDatabase.setVersion(11); 99 assertEquals(11, mDatabase.getVersion()); 100 } 101 102 @MediumTest 103 public void testUpdate() throws Exception { 104 populateDefaultTable(); 105 106 ContentValues values = new ContentValues(1); 107 values.put("data", "this is an updated test"); 108 assertEquals(1, mDatabase.update("test", values, "_id=1", null)); 109 Cursor c = mDatabase.query("test", null, "_id=1", null, null, null, null); 110 assertNotNull(c); 111 assertEquals(1, c.getCount()); 112 c.moveToFirst(); 113 String value = c.getString(c.getColumnIndexOrThrow("data")); 114 assertEquals("this is an updated test", value); 115 } 116 117 @MediumTest 118 public void testPhoneNumbersEqual() throws Exception { 119 mDatabase.execSQL("CREATE TABLE phones (num TEXT);"); 120 mDatabase.execSQL("INSERT INTO phones (num) VALUES ('911');"); 121 mDatabase.execSQL("INSERT INTO phones (num) VALUES ('5555');"); 122 mDatabase.execSQL("INSERT INTO phones (num) VALUES ('+" + PHONE_NUMBER + "');"); 123 124 String number; 125 Cursor c; 126 127 c = mDatabase.query("phones", null, 128 "PHONE_NUMBERS_EQUAL(num, '504-555-7683')", null, null, null, null); 129 assertTrue(c == null || c.getCount() == 0); 130 c.close(); 131 132 c = mDatabase.query("phones", null, 133 "PHONE_NUMBERS_EQUAL(num, '911')", null, null, null, null); 134 assertNotNull(c); 135 assertEquals(1, c.getCount()); 136 c.moveToFirst(); 137 number = c.getString(c.getColumnIndexOrThrow("num")); 138 assertEquals("911", number); 139 c.close(); 140 141 c = mDatabase.query("phones", null, 142 "PHONE_NUMBERS_EQUAL(num, '5555')", null, null, null, null); 143 assertNotNull(c); 144 assertEquals(1, c.getCount()); 145 c.moveToFirst(); 146 number = c.getString(c.getColumnIndexOrThrow("num")); 147 assertEquals("5555", number); 148 c.close(); 149 150 c = mDatabase.query("phones", null, 151 "PHONE_NUMBERS_EQUAL(num, '180055555555')", null, null, null, null); 152 assertTrue(c == null || c.getCount() == 0); 153 c.close(); 154 155 c = mDatabase.query("phones", null, 156 "PHONE_NUMBERS_EQUAL(num, '+" + PHONE_NUMBER + "')", null, null, null, null); 157 assertNotNull(c); 158 assertEquals(1, c.getCount()); 159 c.moveToFirst(); 160 number = c.getString(c.getColumnIndexOrThrow("num")); 161 assertEquals("+" + PHONE_NUMBER, number); 162 c.close(); 163 164 c = mDatabase.query("phones", null, 165 "PHONE_NUMBERS_EQUAL(num, '+1 (617).555-1212')", null, null, null, null); 166 assertNotNull(c); 167 assertEquals(1, c.getCount()); 168 c.moveToFirst(); 169 number = c.getString(c.getColumnIndexOrThrow("num")); 170 assertEquals("+" + PHONE_NUMBER, number); 171 c.close(); 172 173 c = mDatabase.query("phones", null, 174 "PHONE_NUMBERS_EQUAL(num, '" + PHONE_NUMBER + "')", null, null, null, null); 175 assertNotNull(c); 176 assertEquals(1, c.getCount()); 177 c.moveToFirst(); 178 number = c.getString(c.getColumnIndexOrThrow("num")); 179 assertEquals("+" + PHONE_NUMBER, number); 180 c.close(); 181 182 /* 183 c = mDatabase.query("phones", null, 184 "PHONE_NUMBERS_EQUAL(num, '5551212')", null, null, null, null); 185 assertNotNull(c); 186 assertEquals(1, c.getCount()); 187 c.moveToFirst(); 188 number = c.getString(c.getColumnIndexOrThrow("num")); 189 assertEquals("+" + PHONE_NUMBER, number); 190 c.close(); 191 */ 192 193 c = mDatabase.query("phones", null, 194 "PHONE_NUMBERS_EQUAL(num, '011" + PHONE_NUMBER + "')", null, null, null, null); 195 assertNotNull(c); 196 assertEquals(1, c.getCount()); 197 c.moveToFirst(); 198 number = c.getString(c.getColumnIndexOrThrow("num")); 199 assertEquals("+" + PHONE_NUMBER, number); 200 c.close(); 201 202 c = mDatabase.query("phones", null, 203 "PHONE_NUMBERS_EQUAL(num, '00" + PHONE_NUMBER + "')", null, null, null, null); 204 assertNotNull(c); 205 assertEquals(1, c.getCount()); 206 c.moveToFirst(); 207 number = c.getString(c.getColumnIndexOrThrow("num")); 208 assertEquals("+" + PHONE_NUMBER, number); 209 c.close(); 210 } 211 212 private void phoneNumberCompare(String phone1, String phone2, boolean equal, 213 boolean useStrictComparation) { 214 String[] temporalPhoneNumbers = new String[2]; 215 temporalPhoneNumbers[0] = phone1; 216 temporalPhoneNumbers[1] = phone2; 217 218 Cursor cursor = mDatabase.rawQuery( 219 String.format( 220 "SELECT CASE WHEN PHONE_NUMBERS_EQUAL(?, ?, %d) " + 221 "THEN 'equal' ELSE 'not equal' END", 222 (useStrictComparation ? 1 : 0)), 223 temporalPhoneNumbers); 224 try { 225 assertNotNull(cursor); 226 assertTrue(cursor.moveToFirst()); 227 if (equal) { 228 assertEquals(String.format("Unexpectedly, \"%s != %s\".", phone1, phone2), 229 "equal", cursor.getString(0)); 230 } else { 231 assertEquals(String.format("Unexpectedly, \"%s\" == \"%s\".", phone1, phone2), 232 "not equal", cursor.getString(0)); 233 } 234 } finally { 235 if (cursor != null) { 236 cursor.close(); 237 } 238 } 239 } 240 241 private void assertPhoneNumberEqual(String phone1, String phone2) throws Exception { 242 assertPhoneNumberEqual(phone1, phone2, true); 243 assertPhoneNumberEqual(phone1, phone2, false); 244 } 245 246 private void assertPhoneNumberEqual(String phone1, String phone2, boolean useStrict) 247 throws Exception { 248 phoneNumberCompare(phone1, phone2, true, useStrict); 249 } 250 251 private void assertPhoneNumberNotEqual(String phone1, String phone2) throws Exception { 252 assertPhoneNumberNotEqual(phone1, phone2, true); 253 assertPhoneNumberNotEqual(phone1, phone2, false); 254 } 255 256 private void assertPhoneNumberNotEqual(String phone1, String phone2, boolean useStrict) 257 throws Exception { 258 phoneNumberCompare(phone1, phone2, false, useStrict); 259 } 260 261 /** 262 * Tests international matching issues for the PHONE_NUMBERS_EQUAL function. 263 * 264 * @throws Exception 265 */ 266 @SmallTest 267 public void testPhoneNumbersEqualInternationl() throws Exception { 268 assertPhoneNumberEqual("1", "1"); 269 assertPhoneNumberEqual("123123", "123123"); 270 assertPhoneNumberNotEqual("123123", "923123"); 271 assertPhoneNumberNotEqual("123123", "123129"); 272 assertPhoneNumberNotEqual("123123", "1231234"); 273 assertPhoneNumberNotEqual("123123", "0123123", false); 274 assertPhoneNumberNotEqual("123123", "0123123", true); 275 assertPhoneNumberEqual("650-253-0000", "6502530000"); 276 assertPhoneNumberEqual("650-253-0000", "650 253 0000"); 277 assertPhoneNumberEqual("650 253 0000", "6502530000"); 278 assertPhoneNumberEqual("+1 650-253-0000", "6502530000"); 279 assertPhoneNumberEqual("001 650-253-0000", "6502530000"); 280 assertPhoneNumberEqual("0111 650-253-0000", "6502530000"); 281 282 // Russian trunk digit 283 assertPhoneNumberEqual("+79161234567", "89161234567"); 284 285 // French trunk digit 286 assertPhoneNumberEqual("+33123456789", "0123456789"); 287 288 // Trunk digit for city codes in the Netherlands 289 assertPhoneNumberEqual("+31771234567", "0771234567"); 290 291 // Test broken caller ID seen on call from Thailand to the US 292 assertPhoneNumberEqual("+66811234567", "166811234567"); 293 294 // Test the same in-country number with different country codes 295 assertPhoneNumberNotEqual("+33123456789", "+1123456789"); 296 297 // Test one number with country code and the other without 298 assertPhoneNumberEqual("5125551212", "+15125551212"); 299 300 // Test two NANP numbers that only differ in the area code 301 assertPhoneNumberNotEqual("5125551212", "6505551212"); 302 303 // Japanese phone numbers 304 assertPhoneNumberEqual("090-1234-5678", "+819012345678"); 305 assertPhoneNumberEqual("090(1234)5678", "+819012345678"); 306 assertPhoneNumberEqual("090-1234-5678", "+81-90-1234-5678"); 307 308 // Equador 309 assertPhoneNumberEqual("+593(800)123-1234", "8001231234"); 310 assertPhoneNumberEqual("+593-2-1234-123", "21234123"); 311 312 // Two continuous 0 at the beginning of the phone string should not be 313 // treated as trunk prefix in the strict comparation. 314 assertPhoneNumberEqual("008001231234", "8001231234", false); 315 assertPhoneNumberNotEqual("008001231234", "8001231234", true); 316 317 // Confirm that the bug found before does not re-appear in the strict compalation 318 assertPhoneNumberEqual("080-1234-5678", "+819012345678", false); 319 assertPhoneNumberNotEqual("080-1234-5678", "+819012345678", true); 320 } 321 322 @MediumTest 323 public void testCopyString() throws Exception { 324 mDatabase.execSQL("CREATE TABLE guess (numi INTEGER, numf FLOAT, str TEXT);"); 325 mDatabase.execSQL( 326 "INSERT INTO guess (numi,numf,str) VALUES (0,0.0,'ZoomZoomZoomZoom');"); 327 mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (2000000000,3.1415926535,'');"); 328 String chinese = "\u4eac\u4ec5 \u5c3d\u5f84\u60ca"; 329 String[] arr = new String[1]; 330 arr[0] = chinese; 331 mDatabase.execSQL("INSERT INTO guess (numi,numf,str) VALUES (-32768,-1.0,?)", arr); 332 333 Cursor c; 334 335 c = mDatabase.rawQuery("SELECT * FROM guess", null); 336 337 c.moveToFirst(); 338 339 CharArrayBuffer buf = new CharArrayBuffer(14); 340 341 String compareTo = c.getString(c.getColumnIndexOrThrow("numi")); 342 int numiIdx = c.getColumnIndexOrThrow("numi"); 343 int numfIdx = c.getColumnIndexOrThrow("numf"); 344 int strIdx = c.getColumnIndexOrThrow("str"); 345 346 c.copyStringToBuffer(numiIdx, buf); 347 assertEquals(1, buf.sizeCopied); 348 assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied)); 349 350 c.copyStringToBuffer(strIdx, buf); 351 assertEquals("ZoomZoomZoomZoom", new String(buf.data, 0, buf.sizeCopied)); 352 353 c.moveToNext(); 354 compareTo = c.getString(numfIdx); 355 356 c.copyStringToBuffer(numfIdx, buf); 357 assertEquals(compareTo, new String(buf.data, 0, buf.sizeCopied)); 358 c.copyStringToBuffer(strIdx, buf); 359 assertEquals(0, buf.sizeCopied); 360 361 c.moveToNext(); 362 c.copyStringToBuffer(numfIdx, buf); 363 assertEquals(-1.0, Double.valueOf( 364 new String(buf.data, 0, buf.sizeCopied)).doubleValue()); 365 366 c.copyStringToBuffer(strIdx, buf); 367 compareTo = c.getString(strIdx); 368 assertEquals(chinese, compareTo); 369 370 assertEquals(chinese, new String(buf.data, 0, buf.sizeCopied)); 371 c.close(); 372 } 373 374 @MediumTest 375 public void testSchemaChange1() throws Exception { 376 SQLiteDatabase db1 = mDatabase; 377 Cursor cursor; 378 379 db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);"); 380 381 cursor = db1.query("db1", null, null, null, null, null, null); 382 assertNotNull("Cursor is null", cursor); 383 384 db1.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);"); 385 386 assertEquals(0, cursor.getCount()); 387 cursor.deactivate(); 388 } 389 390 @MediumTest 391 public void testSchemaChange2() throws Exception { 392 SQLiteDatabase db1 = mDatabase; 393 SQLiteDatabase db2 = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null); 394 Cursor cursor; 395 396 db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);"); 397 398 cursor = db1.query("db1", null, null, null, null, null, null); 399 assertNotNull("Cursor is null", cursor); 400 assertEquals(0, cursor.getCount()); 401 cursor.deactivate(); 402 // this cause exception because we're still using sqlite_prepate16 and not 403 // sqlite_prepare16_v2. The v2 variant added the ability to check the 404 // schema version and handle the case when the schema has changed 405 // Marco Nelissen claim it was 2x slower to compile SQL statements so 406 // I reverted back to the v1 variant. 407 /* db2.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);"); 408 409 cursor = db1.query("db1", null, null, null, null, null, null); 410 assertNotNull("Cursor is null", cursor); 411 assertEquals(0, cursor.count()); 412 cursor.deactivate(); 413 */ 414 } 415 416 @MediumTest 417 public void testSchemaChange3() throws Exception { 418 SQLiteDatabase db1 = mDatabase; 419 SQLiteDatabase db2 = SQLiteDatabase.openOrCreateDatabase(mDatabaseFile, null); 420 Cursor cursor; 421 422 423 db1.execSQL("CREATE TABLE db1 (_id INTEGER PRIMARY KEY, data TEXT);"); 424 db1.execSQL("INSERT INTO db1 (data) VALUES ('test');"); 425 426 cursor = db1.query("db1", null, null, null, null, null, null); 427 // this cause exception because we're still using sqlite_prepate16 and not 428 // sqlite_prepare16_v2. The v2 variant added the ability to check the 429 // schema version and handle the case when the schema has changed 430 // Marco Nelissen claim it was 2x slower to compile SQL statements so 431 // I reverted back to the v1 variant. 432 /* db2.execSQL("CREATE TABLE db2 (_id INTEGER PRIMARY KEY, data TEXT);"); 433 434 assertNotNull("Cursor is null", cursor); 435 assertEquals(1, cursor.count()); 436 assertTrue(cursor.first()); 437 assertEquals("test", cursor.getString(cursor.getColumnIndexOrThrow("data"))); 438 cursor.deactivate(); 439 */ 440 } 441 442 private class ChangeObserver extends ContentObserver { 443 private int mCursorNotificationCount = 0; 444 private int mNotificationCount = 0; 445 446 public int getCursorNotificationCount() { 447 return mCursorNotificationCount; 448 } 449 450 public int getNotificationCount() { 451 return mNotificationCount; 452 } 453 454 public ChangeObserver(boolean cursor) { 455 super(new Handler()); 456 mCursor = cursor; 457 } 458 459 @Override 460 public boolean deliverSelfNotifications() { 461 return true; 462 } 463 464 @Override 465 public void onChange(boolean selfChange) { 466 if (mCursor) { 467 mCursorNotificationCount++; 468 } else { 469 mNotificationCount++; 470 } 471 } 472 473 boolean mCursor; 474 } 475 476 @MediumTest 477 public void testNotificationTest1() throws Exception { 478 /* 479 Cursor c = mContentResolver.query(Notes.CONTENT_URI, 480 new String[] {Notes._ID, Notes.NOTE}, 481 null, null); 482 c.registerContentObserver(new MyContentObserver(true)); 483 int count = c.count(); 484 485 MyContentObserver observer = new MyContentObserver(false); 486 mContentResolver.registerContentObserver(Notes.CONTENT_URI, true, observer); 487 488 Uri uri; 489 490 HashMap<String, String> values = new HashMap<String, String>(); 491 values.put(Notes.NOTE, "test note1"); 492 uri = mContentResolver.insert(Notes.CONTENT_URI, values); 493 assertEquals(1, mCursorNotificationCount); 494 assertEquals(1, mNotificationCount); 495 496 c.requery(); 497 assertEquals(count + 1, c.count()); 498 c.first(); 499 assertEquals("test note1", c.getString(c.getColumnIndex(Notes.NOTE))); 500 c.updateString(c.getColumnIndex(Notes.NOTE), "test note2"); 501 c.commitUpdates(); 502 503 assertEquals(2, mCursorNotificationCount); 504 assertEquals(2, mNotificationCount); 505 506 mContentResolver.delete(uri, null); 507 508 assertEquals(3, mCursorNotificationCount); 509 assertEquals(3, mNotificationCount); 510 511 mContentResolver.unregisterContentObserver(observer); 512 */ 513 } 514 515 @MediumTest 516 public void testSelectionArgs() throws Exception { 517 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);"); 518 ContentValues values = new ContentValues(1); 519 values.put("data", "don't forget to handled 's"); 520 mDatabase.insert("test", "data", values); 521 values.clear(); 522 values.put("data", "no apostrophes here"); 523 mDatabase.insert("test", "data", values); 524 Cursor c = mDatabase.query( 525 "test", null, "data GLOB ?", new String[]{"*'*"}, null, null, null); 526 assertEquals(1, c.getCount()); 527 assertTrue(c.moveToFirst()); 528 assertEquals("don't forget to handled 's", c.getString(1)); 529 c.deactivate(); 530 531 // make sure code should checking null string properly so that 532 // it won't crash 533 try { 534 mDatabase.query("test", new String[]{"_id"}, 535 "_id=?", new String[]{null}, null, null, null); 536 fail("expected exception not thrown"); 537 } catch (IllegalArgumentException e) { 538 // expected 539 } 540 } 541 542 @MediumTest 543 public void testTokenize() throws Exception { 544 Cursor c; 545 mDatabase.execSQL("CREATE TABLE tokens (" + 546 "token TEXT COLLATE unicode," + 547 "source INTEGER," + 548 "token_index INTEGER," + 549 "tag TEXT" + 550 ");"); 551 mDatabase.execSQL("CREATE TABLE tokens_no_index (" + 552 "token TEXT COLLATE unicode," + 553 "source INTEGER" + 554 ");"); 555 556 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 557 "SELECT _TOKENIZE(NULL, NULL, NULL, NULL)", null)); 558 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 559 "SELECT _TOKENIZE('tokens', NULL, NULL, NULL)", null)); 560 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 561 "SELECT _TOKENIZE('tokens', 10, NULL, NULL)", null)); 562 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 563 "SELECT _TOKENIZE('tokens', 10, 'some string', NULL)", null)); 564 565 Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase, 566 "SELECT _TOKENIZE('tokens', 11, 'some string ok', ' ', 1, 'foo')", null)); 567 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 568 "SELECT _TOKENIZE('tokens', 11, 'second field', ' ', 1, 'bar')", null)); 569 570 Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase, 571 "SELECT _TOKENIZE('tokens_no_index', 20, 'some string ok', ' ')", null)); 572 Assert.assertEquals(3, DatabaseUtils.longForQuery(mDatabase, 573 "SELECT _TOKENIZE('tokens_no_index', 21, 'foo bar baz', ' ', 0)", null)); 574 575 // test Chinese 576 String chinese = new String("\u4eac\u4ec5 \u5c3d\u5f84\u60ca"); 577 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 578 "SELECT _TOKENIZE('tokens', 12,'" + chinese + "', ' ', 1)", null)); 579 580 String icustr = new String("Fr\u00e9d\u00e9ric Hj\u00f8nnev\u00e5g"); 581 582 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 583 "SELECT _TOKENIZE('tokens', 13, '" + icustr + "', ' ', 1)", null)); 584 585 Assert.assertEquals(9, DatabaseUtils.longForQuery(mDatabase, 586 "SELECT count(*) from tokens;", null)); 587 588 String key = DatabaseUtils.getHexCollationKey("Frederic Hjonneva"); 589 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 590 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 591 Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase, 592 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 593 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 594 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 595 key = DatabaseUtils.getHexCollationKey("Hjonneva"); 596 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 597 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 598 Assert.assertEquals(13, DatabaseUtils.longForQuery(mDatabase, 599 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 600 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 601 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 602 603 key = DatabaseUtils.getHexCollationKey("some string ok"); 604 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 605 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 606 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 607 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 608 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 609 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 610 Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase, 611 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 612 key = DatabaseUtils.getHexCollationKey("string"); 613 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 614 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 615 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 616 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 617 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 618 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 619 Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase, 620 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 621 key = DatabaseUtils.getHexCollationKey("ok"); 622 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 623 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 624 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 625 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 626 Assert.assertEquals(2, DatabaseUtils.longForQuery(mDatabase, 627 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 628 Assert.assertEquals("foo", DatabaseUtils.stringForQuery(mDatabase, 629 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 630 631 key = DatabaseUtils.getHexCollationKey("second field"); 632 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 633 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 634 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 635 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 636 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 637 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 638 Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase, 639 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 640 key = DatabaseUtils.getHexCollationKey("field"); 641 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 642 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 643 Assert.assertEquals(11, DatabaseUtils.longForQuery(mDatabase, 644 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 645 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 646 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 647 Assert.assertEquals("bar", DatabaseUtils.stringForQuery(mDatabase, 648 "SELECT tag from tokens where token GLOB '" + key + "*'", null)); 649 650 key = DatabaseUtils.getHexCollationKey(chinese); 651 String[] a = new String[1]; 652 a[0] = key; 653 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 654 "SELECT count(*) from tokens where token= ?", a)); 655 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 656 "SELECT source from tokens where token= ?", a)); 657 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 658 "SELECT token_index from tokens where token= ?", a)); 659 a[0] += "*"; 660 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 661 "SELECT count(*) from tokens where token GLOB ?", a)); 662 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 663 "SELECT source from tokens where token GLOB ?", a)); 664 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 665 "SELECT token_index from tokens where token GLOB ?", a)); 666 667 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 668 "SELECT count(*) from tokens where token= '" + key + "'", null)); 669 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 670 "SELECT source from tokens where token= '" + key + "'", null)); 671 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 672 "SELECT token_index from tokens where token= '" + key + "'", null)); 673 674 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 675 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 676 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 677 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 678 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 679 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 680 681 key = DatabaseUtils.getHexCollationKey("\u4eac\u4ec5"); 682 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 683 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 684 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 685 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 686 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 687 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 688 689 key = DatabaseUtils.getHexCollationKey("\u5c3d\u5f84\u60ca"); 690 Log.d("DatabaseGeneralTest", "key = " + key); 691 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 692 "SELECT count(*) from tokens where token GLOB '" + key + "*'", null)); 693 Assert.assertEquals(12, DatabaseUtils.longForQuery(mDatabase, 694 "SELECT source from tokens where token GLOB '" + key + "*'", null)); 695 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 696 "SELECT token_index from tokens where token GLOB '" + key + "*'", null)); 697 698 Assert.assertEquals(0, DatabaseUtils.longForQuery(mDatabase, 699 "SELECT count(*) from tokens where token GLOB 'ab*'", null)); 700 701 key = DatabaseUtils.getHexCollationKey("some string ok"); 702 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 703 "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null)); 704 Assert.assertEquals(20, DatabaseUtils.longForQuery(mDatabase, 705 "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null)); 706 707 key = DatabaseUtils.getHexCollationKey("bar"); 708 Assert.assertEquals(1, DatabaseUtils.longForQuery(mDatabase, 709 "SELECT count(*) from tokens_no_index where token GLOB '" + key + "*'", null)); 710 Assert.assertEquals(21, DatabaseUtils.longForQuery(mDatabase, 711 "SELECT source from tokens_no_index where token GLOB '" + key + "*'", null)); 712 } 713 714 @MediumTest 715 public void testTransactions() throws Exception { 716 mDatabase.execSQL("CREATE TABLE test (num INTEGER);"); 717 mDatabase.execSQL("INSERT INTO test (num) VALUES (0)"); 718 719 // Make sure that things work outside an explicit transaction. 720 setNum(1); 721 checkNum(1); 722 723 // Test a single-level transaction. 724 setNum(0); 725 mDatabase.beginTransaction(); 726 setNum(1); 727 mDatabase.setTransactionSuccessful(); 728 mDatabase.endTransaction(); 729 checkNum(1); 730 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 731 732 // Test a rolled-back transaction. 733 setNum(0); 734 mDatabase.beginTransaction(); 735 setNum(1); 736 mDatabase.endTransaction(); 737 checkNum(0); 738 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 739 740 // We should get an error if we end a non-existent transaction. 741 assertThrowsIllegalState(new Runnable() { public void run() { 742 mDatabase.endTransaction(); 743 }}); 744 745 // We should get an error if a set a non-existent transaction as clean. 746 assertThrowsIllegalState(new Runnable() { public void run() { 747 mDatabase.setTransactionSuccessful(); 748 }}); 749 750 mDatabase.beginTransaction(); 751 mDatabase.setTransactionSuccessful(); 752 // We should get an error if we mark a transaction as clean twice. 753 assertThrowsIllegalState(new Runnable() { public void run() { 754 mDatabase.setTransactionSuccessful(); 755 }}); 756 // We should get an error if we begin a transaction after marking the parent as clean. 757 assertThrowsIllegalState(new Runnable() { public void run() { 758 mDatabase.beginTransaction(); 759 }}); 760 mDatabase.endTransaction(); 761 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 762 763 // Test a two-level transaction. 764 setNum(0); 765 mDatabase.beginTransaction(); 766 mDatabase.beginTransaction(); 767 setNum(1); 768 mDatabase.setTransactionSuccessful(); 769 mDatabase.endTransaction(); 770 mDatabase.setTransactionSuccessful(); 771 mDatabase.endTransaction(); 772 checkNum(1); 773 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 774 775 // Test rolling back an inner transaction. 776 setNum(0); 777 mDatabase.beginTransaction(); 778 mDatabase.beginTransaction(); 779 setNum(1); 780 mDatabase.endTransaction(); 781 mDatabase.setTransactionSuccessful(); 782 mDatabase.endTransaction(); 783 checkNum(0); 784 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 785 786 // Test rolling back an outer transaction. 787 setNum(0); 788 mDatabase.beginTransaction(); 789 mDatabase.beginTransaction(); 790 setNum(1); 791 mDatabase.setTransactionSuccessful(); 792 mDatabase.endTransaction(); 793 mDatabase.endTransaction(); 794 checkNum(0); 795 Assert.assertFalse(mDatabase.isDbLockedByCurrentThread()); 796 } 797 798 private void setNum(int num) { 799 mDatabase.execSQL("UPDATE test SET num = " + num); 800 } 801 802 private void checkNum(int num) { 803 Assert.assertEquals( 804 num, DatabaseUtils.longForQuery(mDatabase, "SELECT num FROM test", null)); 805 } 806 807 private void assertThrowsIllegalState(Runnable r) { 808 boolean ok = false; 809 try { 810 r.run(); 811 } catch (IllegalStateException e) { 812 ok = true; 813 } 814 Assert.assertTrue(ok); 815 } 816 817 // Disable these until we can explicitly mark them as stress tests 818 public void xxtestMem1() throws Exception { 819 populateDefaultTable(); 820 821 for (int i = 0; i < 50000; i++) { 822 Cursor cursor = mDatabase.query("test", null, null, null, null, null, null); 823 cursor.moveToFirst(); 824 cursor.close(); 825// Log.i("~~~~", "Finished round " + i); 826 } 827 } 828 829 // Disable these until we can explicitly mark them as stress tests 830 public void xxtestMem2() throws Exception { 831 populateDefaultTable(); 832 833 for (int i = 0; i < 50000; i++) { 834 Cursor cursor = mDatabase.query("test", null, null, null, null, null, null); 835 cursor.close(); 836// Log.i("~~~~", "Finished round " + i); 837 } 838 } 839 840 // Disable these until we can explicitly mark them as stress tests 841 public void xxtestMem3() throws Exception { 842 populateDefaultTable(); 843 844 for (int i = 0; i < 50000; i++) { 845 Cursor cursor = mDatabase.query("test", null, null, null, null, null, null); 846 cursor.deactivate(); 847// Log.i("~~~~", "Finished round " + i); 848 } 849 } 850 851 @MediumTest 852 public void testContentValues() throws Exception { 853 ContentValues values = new ContentValues(); 854 values.put("string", "value"); 855 assertEquals("value", values.getAsString("string")); 856 byte[] bytes = new byte[42]; 857 Arrays.fill(bytes, (byte) 0x28); 858 values.put("byteArray", bytes); 859 assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray"))); 860 861 // Write the ContentValues to a Parcel and then read them out 862 Parcel p = Parcel.obtain(); 863 values.writeToParcel(p, 0); 864 p.setDataPosition(0); 865 values = ContentValues.CREATOR.createFromParcel(p); 866 867 // Read the values out again and make sure they're the same 868 assertTrue(Arrays.equals(bytes, values.getAsByteArray("byteArray"))); 869 assertEquals("value", values.get("string")); 870 } 871 872 @MediumTest 873 public void testTableInfoPragma() throws Exception { 874 mDatabase.execSQL("CREATE TABLE pragma_test (" + 875 "i INTEGER DEFAULT 1234, " + 876 "j INTEGER, " + 877 "s TEXT DEFAULT 'hello', " + 878 "t TEXT, " + 879 "'select' TEXT DEFAULT \"hello\")"); 880 try { 881 Cursor cur = mDatabase.rawQuery("PRAGMA table_info(pragma_test)", null); 882 Assert.assertEquals(5, cur.getCount()); 883 884 Assert.assertTrue(cur.moveToNext()); 885 Assert.assertEquals("i", 886 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 887 Assert.assertEquals("1234", 888 cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 889 890 Assert.assertTrue(cur.moveToNext()); 891 Assert.assertEquals("j", 892 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 893 Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 894 895 Assert.assertTrue(cur.moveToNext()); 896 Assert.assertEquals("s", 897 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 898 Assert.assertEquals("'hello'", 899 cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 900 901 Assert.assertTrue(cur.moveToNext()); 902 Assert.assertEquals("t", 903 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 904 Assert.assertNull(cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 905 906 Assert.assertTrue(cur.moveToNext()); 907 Assert.assertEquals("select", 908 cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX)); 909 Assert.assertEquals("\"hello\"", 910 cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX)); 911 912 cur.close(); 913 } catch (Throwable t) { 914 throw new RuntimeException( 915 "If you see this test fail, it's likely that something about " + 916 "sqlite's PRAGMA table_info(...) command has changed.", t); 917 } 918 } 919 920 @MediumTest 921 public void testInsertHelper() throws Exception { 922 Cursor cur; 923 ContentValues cv; 924 long row; 925 926 mDatabase.execSQL("CREATE TABLE insert_test (" + 927 "_id INTEGER PRIMARY KEY, " + 928 "s TEXT NOT NULL UNIQUE, " + 929 "t TEXT NOT NULL DEFAULT 'hello world', " + 930 "i INTEGER, " + 931 "j INTEGER NOT NULL DEFAULT 1234, " + 932 "'select' TEXT)"); 933 934 DatabaseUtils.InsertHelper ih = 935 new DatabaseUtils.InsertHelper(mDatabase, "insert_test"); 936 937 cv = new ContentValues(); 938 cv.put("s", "one"); 939 row = ih.insert(cv); 940 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 941 Assert.assertTrue(cur.moveToFirst()); 942 Assert.assertEquals("one", cur.getString(1)); 943 Assert.assertEquals("hello world", cur.getString(2)); 944 Assert.assertNull(cur.getString(3)); 945 Assert.assertEquals(1234, cur.getLong(4)); 946 Assert.assertNull(cur.getString(5)); 947 948 cv = new ContentValues(); 949 cv.put("s", "two"); 950 cv.put("t", "goodbye world"); 951 row = ih.insert(cv); 952 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 953 Assert.assertTrue(cur.moveToFirst()); 954 Assert.assertEquals("two", cur.getString(1)); 955 Assert.assertEquals("goodbye world", cur.getString(2)); 956 Assert.assertNull(cur.getString(3)); 957 Assert.assertEquals(1234, cur.getLong(4)); 958 Assert.assertNull(cur.getString(5)); 959 960 cv = new ContentValues(); 961 cv.put("t", "goodbye world"); 962 row = ih.insert(cv); 963 Assert.assertEquals(-1, row); 964 965 cv = new ContentValues(); 966 cv.put("s", "three"); 967 cv.put("i", 2345); 968 cv.put("j", 3456); 969 cv.put("select", "tricky"); 970 row = ih.insert(cv); 971 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 972 Assert.assertTrue(cur.moveToFirst()); 973 Assert.assertEquals("three", cur.getString(1)); 974 Assert.assertEquals("hello world", cur.getString(2)); 975 Assert.assertEquals(2345, cur.getLong(3)); 976 Assert.assertEquals(3456, cur.getLong(4)); 977 Assert.assertEquals("tricky", cur.getString(5)); 978 979 cv = new ContentValues(); 980 cv.put("s", "three"); 981 cv.put("i", 6789); 982 row = ih.insert(cv); 983 Assert.assertEquals(-1, row); 984 row = ih.replace(cv); 985 cur = mDatabase.rawQuery("SELECT * FROM insert_test WHERE _id == " + row, null); 986 Assert.assertTrue(cur.moveToFirst()); 987 Assert.assertEquals("three", cur.getString(1)); 988 Assert.assertEquals("hello world", cur.getString(2)); 989 Assert.assertEquals(6789, cur.getLong(3)); 990 991 ih.close(); 992 } 993 994 @MediumTest 995 public void testDbCloseReleasingAllCachedSql() { 996 mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, text1 TEXT, text2 TEXT, " + 997 "num1 INTEGER, num2 INTEGER, image BLOB);"); 998 final String statement = "DELETE FROM test WHERE _id=?;"; 999 SQLiteStatement statementDoNotClose = mDatabase.compileStatement(statement); 1000 assertTrue(statementDoNotClose.getUniqueId() > 0); 1001 int nStatement = statementDoNotClose.getUniqueId(); 1002 assertTrue(statementDoNotClose.getUniqueId() == nStatement); 1003 /* do not close statementDoNotClose object. 1004 * That should leave it in SQLiteDatabase.mPrograms. 1005 * mDatabase.close() in tearDown() should release it. 1006 */ 1007 } 1008} 1009