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