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 android.database; 18 19 import android.annotation.NonNull; 20 import android.annotation.Nullable; 21 import android.compat.annotation.UnsupportedAppUsage; 22 import android.content.ContentValues; 23 import android.content.Context; 24 import android.content.OperationApplicationException; 25 import android.database.sqlite.Flags; 26 import android.database.sqlite.SQLiteAbortException; 27 import android.database.sqlite.SQLiteConstraintException; 28 import android.database.sqlite.SQLiteDatabase; 29 import android.database.sqlite.SQLiteDatabaseCorruptException; 30 import android.database.sqlite.SQLiteDiskIOException; 31 import android.database.sqlite.SQLiteException; 32 import android.database.sqlite.SQLiteFullException; 33 import android.database.sqlite.SQLiteProgram; 34 import android.database.sqlite.SQLiteStatement; 35 import android.os.Build; 36 import android.os.OperationCanceledException; 37 import android.os.Parcel; 38 import android.os.ParcelFileDescriptor; 39 import android.text.TextUtils; 40 import android.util.Log; 41 42 import com.android.internal.util.ArrayUtils; 43 44 import java.io.FileNotFoundException; 45 import java.io.PrintStream; 46 import java.text.Collator; 47 import java.util.Arrays; 48 import java.util.HashMap; 49 import java.util.Locale; 50 import java.util.Map; 51 52 /** 53 * Static utility methods for dealing with databases and {@link Cursor}s. 54 */ 55 public class DatabaseUtils { 56 private static final String TAG = "DatabaseUtils"; 57 58 private static final boolean DEBUG = false; 59 60 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 61 public static final int STATEMENT_SELECT = 1; 62 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 63 public static final int STATEMENT_UPDATE = 2; 64 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 65 public static final int STATEMENT_ATTACH = 3; 66 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 67 public static final int STATEMENT_BEGIN = 4; 68 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 69 public static final int STATEMENT_COMMIT = 5; 70 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 71 public static final int STATEMENT_ABORT = 6; 72 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 73 public static final int STATEMENT_PRAGMA = 7; 74 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 75 public static final int STATEMENT_DDL = 8; 76 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 77 public static final int STATEMENT_UNPREPARED = 9; 78 /** One of the values returned by {@link #getSqlStatementType(String)}. */ 79 public static final int STATEMENT_OTHER = 99; 80 81 // The following statement types are "extended" and are for internal use only. These types 82 // are not public and are never returned by {@link #getSqlStatementType(String)}. 83 84 /** An internal statement type @hide **/ 85 public static final int STATEMENT_WITH = 100; 86 /** An internal statement type @hide **/ 87 public static final int STATEMENT_CREATE = 101; 88 /** An internal statement type denoting a comment. @hide **/ 89 public static final int STATEMENT_COMMENT = 102; 90 91 /** 92 * Special function for writing an exception result at the header of 93 * a parcel, to be used when returning an exception from a transaction. 94 * exception will be re-thrown by the function in another process 95 * @param reply Parcel to write to 96 * @param e The Exception to be written. 97 * @see Parcel#writeNoException 98 * @see Parcel#writeException 99 */ writeExceptionToParcel(Parcel reply, Exception e)100 public static final void writeExceptionToParcel(Parcel reply, Exception e) { 101 int code = 0; 102 boolean logException = true; 103 if (e instanceof FileNotFoundException) { 104 code = 1; 105 logException = false; 106 } else if (e instanceof IllegalArgumentException) { 107 code = 2; 108 } else if (e instanceof UnsupportedOperationException) { 109 code = 3; 110 } else if (e instanceof SQLiteAbortException) { 111 code = 4; 112 } else if (e instanceof SQLiteConstraintException) { 113 code = 5; 114 } else if (e instanceof SQLiteDatabaseCorruptException) { 115 code = 6; 116 } else if (e instanceof SQLiteFullException) { 117 code = 7; 118 } else if (e instanceof SQLiteDiskIOException) { 119 code = 8; 120 } else if (e instanceof SQLiteException) { 121 code = 9; 122 } else if (e instanceof OperationApplicationException) { 123 code = 10; 124 } else if (e instanceof OperationCanceledException) { 125 code = 11; 126 logException = false; 127 } else { 128 reply.writeException(e); 129 Log.e(TAG, "Writing exception to parcel", e); 130 return; 131 } 132 reply.writeInt(code); 133 reply.writeString(e.getMessage()); 134 135 if (logException) { 136 Log.e(TAG, "Writing exception to parcel", e); 137 } 138 } 139 140 /** 141 * Special function for reading an exception result from the header of 142 * a parcel, to be used after receiving the result of a transaction. This 143 * will throw the exception for you if it had been written to the Parcel, 144 * otherwise return and let you read the normal result data from the Parcel. 145 * @param reply Parcel to read from 146 * @see Parcel#writeNoException 147 * @see Parcel#readException 148 */ readExceptionFromParcel(Parcel reply)149 public static final void readExceptionFromParcel(Parcel reply) { 150 int code = reply.readExceptionCode(); 151 if (code == 0) return; 152 String msg = reply.readString(); 153 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 154 } 155 readExceptionWithFileNotFoundExceptionFromParcel( Parcel reply)156 public static void readExceptionWithFileNotFoundExceptionFromParcel( 157 Parcel reply) throws FileNotFoundException { 158 int code = reply.readExceptionCode(); 159 if (code == 0) return; 160 String msg = reply.readString(); 161 if (code == 1) { 162 throw new FileNotFoundException(msg); 163 } else { 164 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 165 } 166 } 167 readExceptionWithOperationApplicationExceptionFromParcel( Parcel reply)168 public static void readExceptionWithOperationApplicationExceptionFromParcel( 169 Parcel reply) throws OperationApplicationException { 170 int code = reply.readExceptionCode(); 171 if (code == 0) return; 172 String msg = reply.readString(); 173 if (code == 10) { 174 throw new OperationApplicationException(msg); 175 } else { 176 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 177 } 178 } 179 readExceptionFromParcel(Parcel reply, String msg, int code)180 private static final void readExceptionFromParcel(Parcel reply, String msg, int code) { 181 switch (code) { 182 case 2: 183 throw new IllegalArgumentException(msg); 184 case 3: 185 throw new UnsupportedOperationException(msg); 186 case 4: 187 throw new SQLiteAbortException(msg); 188 case 5: 189 throw new SQLiteConstraintException(msg); 190 case 6: 191 throw new SQLiteDatabaseCorruptException(msg); 192 case 7: 193 throw new SQLiteFullException(msg); 194 case 8: 195 throw new SQLiteDiskIOException(msg); 196 case 9: 197 throw new SQLiteException(msg); 198 case 11: 199 throw new OperationCanceledException(msg); 200 default: 201 reply.readException(code, msg); 202 } 203 } 204 205 /** {@hide} */ executeInsert(@onNull SQLiteDatabase db, @NonNull String sql, @Nullable Object[] bindArgs)206 public static long executeInsert(@NonNull SQLiteDatabase db, @NonNull String sql, 207 @Nullable Object[] bindArgs) throws SQLException { 208 try (SQLiteStatement st = db.compileStatement(sql)) { 209 bindArgs(st, bindArgs); 210 return st.executeInsert(); 211 } 212 } 213 214 /** {@hide} */ executeUpdateDelete(@onNull SQLiteDatabase db, @NonNull String sql, @Nullable Object[] bindArgs)215 public static int executeUpdateDelete(@NonNull SQLiteDatabase db, @NonNull String sql, 216 @Nullable Object[] bindArgs) throws SQLException { 217 try (SQLiteStatement st = db.compileStatement(sql)) { 218 bindArgs(st, bindArgs); 219 return st.executeUpdateDelete(); 220 } 221 } 222 223 /** {@hide} */ bindArgs(@onNull SQLiteStatement st, @Nullable Object[] bindArgs)224 private static void bindArgs(@NonNull SQLiteStatement st, @Nullable Object[] bindArgs) { 225 if (bindArgs == null) return; 226 227 for (int i = 0; i < bindArgs.length; i++) { 228 final Object bindArg = bindArgs[i]; 229 switch (getTypeOfObject(bindArg)) { 230 case Cursor.FIELD_TYPE_NULL: 231 st.bindNull(i + 1); 232 break; 233 case Cursor.FIELD_TYPE_INTEGER: 234 st.bindLong(i + 1, ((Number) bindArg).longValue()); 235 break; 236 case Cursor.FIELD_TYPE_FLOAT: 237 st.bindDouble(i + 1, ((Number) bindArg).doubleValue()); 238 break; 239 case Cursor.FIELD_TYPE_BLOB: 240 st.bindBlob(i + 1, (byte[]) bindArg); 241 break; 242 case Cursor.FIELD_TYPE_STRING: 243 default: 244 if (bindArg instanceof Boolean) { 245 // Provide compatibility with legacy 246 // applications which may pass Boolean values in 247 // bind args. 248 st.bindLong(i + 1, ((Boolean) bindArg).booleanValue() ? 1 : 0); 249 } else { 250 st.bindString(i + 1, bindArg.toString()); 251 } 252 break; 253 } 254 } 255 } 256 257 /** 258 * Binds the given Object to the given SQLiteProgram using the proper 259 * typing. For example, bind numbers as longs/doubles, and everything else 260 * as a string by call toString() on it. 261 * 262 * @param prog the program to bind the object to 263 * @param index the 1-based index to bind at 264 * @param value the value to bind 265 */ bindObjectToProgram(SQLiteProgram prog, int index, Object value)266 public static void bindObjectToProgram(SQLiteProgram prog, int index, 267 Object value) { 268 if (value == null) { 269 prog.bindNull(index); 270 } else if (value instanceof Double || value instanceof Float) { 271 prog.bindDouble(index, ((Number)value).doubleValue()); 272 } else if (value instanceof Number) { 273 prog.bindLong(index, ((Number)value).longValue()); 274 } else if (value instanceof Boolean) { 275 Boolean bool = (Boolean)value; 276 if (bool) { 277 prog.bindLong(index, 1); 278 } else { 279 prog.bindLong(index, 0); 280 } 281 } else if (value instanceof byte[]){ 282 prog.bindBlob(index, (byte[]) value); 283 } else { 284 prog.bindString(index, value.toString()); 285 } 286 } 287 288 /** 289 * Bind the given selection with the given selection arguments. 290 * <p> 291 * Internally assumes that '?' is only ever used for arguments, and doesn't 292 * appear as a literal or escaped value. 293 * <p> 294 * This method is typically useful for trusted code that needs to cook up a 295 * fully-bound selection. 296 * 297 * @hide 298 */ bindSelection(@ullable String selection, @Nullable Object... selectionArgs)299 public static @Nullable String bindSelection(@Nullable String selection, 300 @Nullable Object... selectionArgs) { 301 if (selection == null) return null; 302 // If no arguments provided, so we can't bind anything 303 if (ArrayUtils.isEmpty(selectionArgs)) return selection; 304 // If no bindings requested, so we can shortcut 305 if (selection.indexOf('?') == -1) return selection; 306 307 // Track the chars immediately before and after each bind request, to 308 // decide if it needs additional whitespace added 309 char before = ' '; 310 char after = ' '; 311 312 int argIndex = 0; 313 final int len = selection.length(); 314 final StringBuilder res = new StringBuilder(len); 315 for (int i = 0; i < len; ) { 316 char c = selection.charAt(i++); 317 if (c == '?') { 318 // Assume this bind request is guarded until we find a specific 319 // trailing character below 320 after = ' '; 321 322 // Sniff forward to see if the selection is requesting a 323 // specific argument index 324 int start = i; 325 for (; i < len; i++) { 326 c = selection.charAt(i); 327 if (c < '0' || c > '9') { 328 after = c; 329 break; 330 } 331 } 332 if (start != i) { 333 argIndex = Integer.parseInt(selection.substring(start, i)) - 1; 334 } 335 336 // Manually bind the argument into the selection, adding 337 // whitespace when needed for clarity 338 final Object arg = selectionArgs[argIndex++]; 339 if (before != ' ' && before != '=') res.append(' '); 340 switch (DatabaseUtils.getTypeOfObject(arg)) { 341 case Cursor.FIELD_TYPE_NULL: 342 res.append("NULL"); 343 break; 344 case Cursor.FIELD_TYPE_INTEGER: 345 res.append(((Number) arg).longValue()); 346 break; 347 case Cursor.FIELD_TYPE_FLOAT: 348 res.append(((Number) arg).doubleValue()); 349 break; 350 case Cursor.FIELD_TYPE_BLOB: 351 throw new IllegalArgumentException("Blobs not supported"); 352 case Cursor.FIELD_TYPE_STRING: 353 default: 354 if (arg instanceof Boolean) { 355 // Provide compatibility with legacy applications which may pass 356 // Boolean values in bind args. 357 res.append(((Boolean) arg).booleanValue() ? 1 : 0); 358 } else { 359 res.append('\''); 360 res.append(arg.toString()); 361 res.append('\''); 362 } 363 break; 364 } 365 if (after != ' ') res.append(' '); 366 } else { 367 res.append(c); 368 before = c; 369 } 370 } 371 return res.toString(); 372 } 373 374 /** 375 * Make a deep copy of the given argument list, ensuring that the returned 376 * value is completely isolated from any changes to the original arguments. 377 * 378 * @hide 379 */ deepCopyOf(@ullable Object[] args)380 public static @Nullable Object[] deepCopyOf(@Nullable Object[] args) { 381 if (args == null) return null; 382 383 final Object[] res = new Object[args.length]; 384 for (int i = 0; i < args.length; i++) { 385 final Object arg = args[i]; 386 387 if ((arg == null) || (arg instanceof Number) || (arg instanceof String)) { 388 // When the argument is immutable, we can copy by reference 389 res[i] = arg; 390 } else if (arg instanceof byte[]) { 391 // Need to deep copy blobs 392 final byte[] castArg = (byte[]) arg; 393 res[i] = Arrays.copyOf(castArg, castArg.length); 394 } else { 395 // Convert everything else to string, making it immutable 396 res[i] = String.valueOf(arg); 397 } 398 } 399 return res; 400 } 401 402 /** 403 * Returns data type of the given object's value. 404 *<p> 405 * Returned values are 406 * <ul> 407 * <li>{@link Cursor#FIELD_TYPE_NULL}</li> 408 * <li>{@link Cursor#FIELD_TYPE_INTEGER}</li> 409 * <li>{@link Cursor#FIELD_TYPE_FLOAT}</li> 410 * <li>{@link Cursor#FIELD_TYPE_STRING}</li> 411 * <li>{@link Cursor#FIELD_TYPE_BLOB}</li> 412 *</ul> 413 *</p> 414 * 415 * @param obj the object whose value type is to be returned 416 * @return object value type 417 * @hide 418 */ 419 @UnsupportedAppUsage(maxTargetSdk = Build.VERSION_CODES.P, trackingBug = 115609023) getTypeOfObject(Object obj)420 public static int getTypeOfObject(Object obj) { 421 if (obj == null) { 422 return Cursor.FIELD_TYPE_NULL; 423 } else if (obj instanceof byte[]) { 424 return Cursor.FIELD_TYPE_BLOB; 425 } else if (obj instanceof Float || obj instanceof Double) { 426 return Cursor.FIELD_TYPE_FLOAT; 427 } else if (obj instanceof Long || obj instanceof Integer 428 || obj instanceof Short || obj instanceof Byte) { 429 return Cursor.FIELD_TYPE_INTEGER; 430 } else { 431 return Cursor.FIELD_TYPE_STRING; 432 } 433 } 434 435 /** 436 * Fills the specified cursor window by iterating over the contents of the cursor. 437 * The window is filled until the cursor is exhausted or the window runs out 438 * of space. 439 * 440 * The original position of the cursor is left unchanged by this operation. 441 * 442 * @param cursor The cursor that contains the data to put in the window. 443 * @param position The start position for filling the window. 444 * @param window The window to fill. 445 * @hide 446 */ cursorFillWindow(final Cursor cursor, int position, final CursorWindow window)447 public static void cursorFillWindow(final Cursor cursor, 448 int position, final CursorWindow window) { 449 if (position < 0 || position >= cursor.getCount()) { 450 return; 451 } 452 final int oldPos = cursor.getPosition(); 453 final int numColumns = cursor.getColumnCount(); 454 window.clear(); 455 window.setStartPosition(position); 456 window.setNumColumns(numColumns); 457 if (cursor.moveToPosition(position)) { 458 rowloop: do { 459 if (!window.allocRow()) { 460 break; 461 } 462 for (int i = 0; i < numColumns; i++) { 463 final int type = cursor.getType(i); 464 final boolean success; 465 switch (type) { 466 case Cursor.FIELD_TYPE_NULL: 467 success = window.putNull(position, i); 468 break; 469 470 case Cursor.FIELD_TYPE_INTEGER: 471 success = window.putLong(cursor.getLong(i), position, i); 472 break; 473 474 case Cursor.FIELD_TYPE_FLOAT: 475 success = window.putDouble(cursor.getDouble(i), position, i); 476 break; 477 478 case Cursor.FIELD_TYPE_BLOB: { 479 final byte[] value = cursor.getBlob(i); 480 success = value != null ? window.putBlob(value, position, i) 481 : window.putNull(position, i); 482 break; 483 } 484 485 default: // assume value is convertible to String 486 case Cursor.FIELD_TYPE_STRING: { 487 final String value = cursor.getString(i); 488 success = value != null ? window.putString(value, position, i) 489 : window.putNull(position, i); 490 break; 491 } 492 } 493 if (!success) { 494 window.freeLastRow(); 495 break rowloop; 496 } 497 } 498 position += 1; 499 } while (cursor.moveToNext()); 500 } 501 cursor.moveToPosition(oldPos); 502 } 503 504 /** 505 * Appends an SQL string to the given StringBuilder, including the opening 506 * and closing single quotes. Any single quotes internal to sqlString will 507 * be escaped. 508 * 509 * This method is deprecated because we want to encourage everyone 510 * to use the "?" binding form. However, when implementing a 511 * ContentProvider, one may want to add WHERE clauses that were 512 * not provided by the caller. Since "?" is a positional form, 513 * using it in this case could break the caller because the 514 * indexes would be shifted to accomodate the ContentProvider's 515 * internal bindings. In that case, it may be necessary to 516 * construct a WHERE clause manually. This method is useful for 517 * those cases. 518 * 519 * @param sb the StringBuilder that the SQL string will be appended to 520 * @param sqlString the raw string to be appended, which may contain single 521 * quotes 522 */ appendEscapedSQLString(StringBuilder sb, String sqlString)523 public static void appendEscapedSQLString(StringBuilder sb, String sqlString) { 524 sb.append('\''); 525 int length = sqlString.length(); 526 for (int i = 0; i < length; i++) { 527 char c = sqlString.charAt(i); 528 if (Character.isHighSurrogate(c)) { 529 if (i == length - 1) { 530 continue; 531 } 532 if (Character.isLowSurrogate(sqlString.charAt(i + 1))) { 533 // add them both 534 sb.append(c); 535 sb.append(sqlString.charAt(i + 1)); 536 continue; 537 } else { 538 // this is a lone surrogate, skip it 539 continue; 540 } 541 } 542 if (Character.isLowSurrogate(c)) { 543 continue; 544 } 545 if (c == '\'') { 546 sb.append('\''); 547 } 548 sb.append(c); 549 } 550 sb.append('\''); 551 } 552 553 /** 554 * SQL-escape a string. 555 */ sqlEscapeString(String value)556 public static String sqlEscapeString(String value) { 557 StringBuilder escaper = new StringBuilder(); 558 559 DatabaseUtils.appendEscapedSQLString(escaper, value); 560 561 return escaper.toString(); 562 } 563 564 /** 565 * Appends an Object to an SQL string with the proper escaping, etc. 566 */ appendValueToSql(StringBuilder sql, Object value)567 public static final void appendValueToSql(StringBuilder sql, Object value) { 568 if (value == null) { 569 sql.append("NULL"); 570 } else if (value instanceof Boolean) { 571 Boolean bool = (Boolean)value; 572 if (bool) { 573 sql.append('1'); 574 } else { 575 sql.append('0'); 576 } 577 } else { 578 appendEscapedSQLString(sql, value.toString()); 579 } 580 } 581 582 /** 583 * Concatenates two SQL WHERE clauses, handling empty or null values. 584 */ concatenateWhere(String a, String b)585 public static String concatenateWhere(String a, String b) { 586 if (TextUtils.isEmpty(a)) { 587 return b; 588 } 589 if (TextUtils.isEmpty(b)) { 590 return a; 591 } 592 593 return "(" + a + ") AND (" + b + ")"; 594 } 595 596 /** 597 * return the collation key 598 * @param name 599 * @return the collation key 600 */ getCollationKey(String name)601 public static String getCollationKey(String name) { 602 byte [] arr = getCollationKeyInBytes(name); 603 try { 604 return new String(arr, 0, getKeyLen(arr), "ISO8859_1"); 605 } catch (Exception ex) { 606 return ""; 607 } 608 } 609 610 /** 611 * return the collation key in hex format 612 * @param name 613 * @return the collation key in hex format 614 */ getHexCollationKey(String name)615 public static String getHexCollationKey(String name) { 616 byte[] arr = getCollationKeyInBytes(name); 617 char[] keys = encodeHex(arr); 618 return new String(keys, 0, getKeyLen(arr) * 2); 619 } 620 621 622 /** 623 * Used building output as Hex 624 */ 625 private static final char[] DIGITS = { 626 '0', '1', '2', '3', '4', '5', '6', '7', 627 '8', '9', 'a', 'b', 'c', 'd', 'e', 'f' 628 }; 629 encodeHex(byte[] input)630 private static char[] encodeHex(byte[] input) { 631 int l = input.length; 632 char[] out = new char[l << 1]; 633 634 // two characters form the hex value. 635 for (int i = 0, j = 0; i < l; i++) { 636 out[j++] = DIGITS[(0xF0 & input[i]) >>> 4 ]; 637 out[j++] = DIGITS[ 0x0F & input[i] ]; 638 } 639 640 return out; 641 } 642 getKeyLen(byte[] arr)643 private static int getKeyLen(byte[] arr) { 644 if (arr[arr.length - 1] != 0) { 645 return arr.length; 646 } else { 647 // remove zero "termination" 648 return arr.length-1; 649 } 650 } 651 getCollationKeyInBytes(String name)652 private static byte[] getCollationKeyInBytes(String name) { 653 if (mColl == null) { 654 mColl = Collator.getInstance(); 655 mColl.setStrength(Collator.PRIMARY); 656 } 657 return mColl.getCollationKey(name).toByteArray(); 658 } 659 660 private static Collator mColl = null; 661 /** 662 * Prints the contents of a Cursor to System.out. The position is restored 663 * after printing. 664 * 665 * @param cursor the cursor to print 666 */ dumpCursor(Cursor cursor)667 public static void dumpCursor(Cursor cursor) { 668 dumpCursor(cursor, System.out); 669 } 670 671 /** 672 * Prints the contents of a Cursor to a PrintSteam. The position is restored 673 * after printing. 674 * 675 * @param cursor the cursor to print 676 * @param stream the stream to print to 677 */ dumpCursor(Cursor cursor, PrintStream stream)678 public static void dumpCursor(Cursor cursor, PrintStream stream) { 679 stream.println(">>>>> Dumping cursor " + cursor); 680 if (cursor != null) { 681 int startPos = cursor.getPosition(); 682 683 cursor.moveToPosition(-1); 684 while (cursor.moveToNext()) { 685 dumpCurrentRow(cursor, stream); 686 } 687 cursor.moveToPosition(startPos); 688 } 689 stream.println("<<<<<"); 690 } 691 692 /** 693 * Prints the contents of a Cursor to a StringBuilder. The position 694 * is restored after printing. 695 * 696 * @param cursor the cursor to print 697 * @param sb the StringBuilder to print to 698 */ dumpCursor(Cursor cursor, StringBuilder sb)699 public static void dumpCursor(Cursor cursor, StringBuilder sb) { 700 sb.append(">>>>> Dumping cursor ").append(cursor).append('\n'); 701 if (cursor != null) { 702 int startPos = cursor.getPosition(); 703 704 cursor.moveToPosition(-1); 705 while (cursor.moveToNext()) { 706 dumpCurrentRow(cursor, sb); 707 } 708 cursor.moveToPosition(startPos); 709 } 710 sb.append("<<<<<\n"); 711 } 712 713 /** 714 * Prints the contents of a Cursor to a String. The position is restored 715 * after printing. 716 * 717 * @param cursor the cursor to print 718 * @return a String that contains the dumped cursor 719 */ dumpCursorToString(Cursor cursor)720 public static String dumpCursorToString(Cursor cursor) { 721 StringBuilder sb = new StringBuilder(); 722 dumpCursor(cursor, sb); 723 return sb.toString(); 724 } 725 726 /** 727 * Prints the contents of a Cursor's current row to System.out. 728 * 729 * @param cursor the cursor to print from 730 */ dumpCurrentRow(Cursor cursor)731 public static void dumpCurrentRow(Cursor cursor) { 732 dumpCurrentRow(cursor, System.out); 733 } 734 735 /** 736 * Prints the contents of a Cursor's current row to a PrintSteam. 737 * 738 * @param cursor the cursor to print 739 * @param stream the stream to print to 740 */ dumpCurrentRow(Cursor cursor, PrintStream stream)741 public static void dumpCurrentRow(Cursor cursor, PrintStream stream) { 742 String[] cols = cursor.getColumnNames(); 743 stream.println("" + cursor.getPosition() + " {"); 744 int length = cols.length; 745 for (int i = 0; i< length; i++) { 746 String value; 747 try { 748 value = cursor.getString(i); 749 } catch (SQLiteException e) { 750 // assume that if the getString threw this exception then the column is not 751 // representable by a string, e.g. it is a BLOB. 752 value = "<unprintable>"; 753 } 754 stream.println(" " + cols[i] + '=' + value); 755 } 756 stream.println("}"); 757 } 758 759 /** 760 * Prints the contents of a Cursor's current row to a StringBuilder. 761 * 762 * @param cursor the cursor to print 763 * @param sb the StringBuilder to print to 764 */ dumpCurrentRow(Cursor cursor, StringBuilder sb)765 public static void dumpCurrentRow(Cursor cursor, StringBuilder sb) { 766 String[] cols = cursor.getColumnNames(); 767 sb.append(cursor.getPosition()).append(" {\n"); 768 int length = cols.length; 769 for (int i = 0; i < length; i++) { 770 String value; 771 try { 772 value = cursor.getString(i); 773 } catch (SQLiteException e) { 774 // assume that if the getString threw this exception then the column is not 775 // representable by a string, e.g. it is a BLOB. 776 value = "<unprintable>"; 777 } 778 sb.append(" ").append(cols[i]).append('=').append(value).append('\n'); 779 } 780 sb.append("}\n"); 781 } 782 783 /** 784 * Dump the contents of a Cursor's current row to a String. 785 * 786 * @param cursor the cursor to print 787 * @return a String that contains the dumped cursor row 788 */ dumpCurrentRowToString(Cursor cursor)789 public static String dumpCurrentRowToString(Cursor cursor) { 790 StringBuilder sb = new StringBuilder(); 791 dumpCurrentRow(cursor, sb); 792 return sb.toString(); 793 } 794 795 /** 796 * Reads a String out of a field in a Cursor and writes it to a Map. 797 * 798 * @param cursor The cursor to read from 799 * @param field The TEXT field to read 800 * @param values The {@link ContentValues} to put the value into, with the field as the key 801 */ cursorStringToContentValues(Cursor cursor, String field, ContentValues values)802 public static void cursorStringToContentValues(Cursor cursor, String field, 803 ContentValues values) { 804 cursorStringToContentValues(cursor, field, values, field); 805 } 806 807 /** 808 * Reads a String out of a field in a Cursor and writes it to an InsertHelper. 809 * 810 * @param cursor The cursor to read from 811 * @param field The TEXT field to read 812 * @param inserter The InsertHelper to bind into 813 * @param index the index of the bind entry in the InsertHelper 814 */ cursorStringToInsertHelper(Cursor cursor, String field, InsertHelper inserter, int index)815 public static void cursorStringToInsertHelper(Cursor cursor, String field, 816 InsertHelper inserter, int index) { 817 inserter.bind(index, cursor.getString(cursor.getColumnIndexOrThrow(field))); 818 } 819 820 /** 821 * Reads a String out of a field in a Cursor and writes it to a Map. 822 * 823 * @param cursor The cursor to read from 824 * @param field The TEXT field to read 825 * @param values The {@link ContentValues} to put the value into, with the field as the key 826 * @param key The key to store the value with in the map 827 */ cursorStringToContentValues(Cursor cursor, String field, ContentValues values, String key)828 public static void cursorStringToContentValues(Cursor cursor, String field, 829 ContentValues values, String key) { 830 values.put(key, cursor.getString(cursor.getColumnIndexOrThrow(field))); 831 } 832 833 /** 834 * Reads an Integer out of a field in a Cursor and writes it to a Map. 835 * 836 * @param cursor The cursor to read from 837 * @param field The INTEGER field to read 838 * @param values The {@link ContentValues} to put the value into, with the field as the key 839 */ cursorIntToContentValues(Cursor cursor, String field, ContentValues values)840 public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values) { 841 cursorIntToContentValues(cursor, field, values, field); 842 } 843 844 /** 845 * Reads a Integer out of a field in a Cursor and writes it to a Map. 846 * 847 * @param cursor The cursor to read from 848 * @param field The INTEGER field to read 849 * @param values The {@link ContentValues} to put the value into, with the field as the key 850 * @param key The key to store the value with in the map 851 */ cursorIntToContentValues(Cursor cursor, String field, ContentValues values, String key)852 public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values, 853 String key) { 854 int colIndex = cursor.getColumnIndex(field); 855 if (!cursor.isNull(colIndex)) { 856 values.put(key, cursor.getInt(colIndex)); 857 } else { 858 values.put(key, (Integer) null); 859 } 860 } 861 862 /** 863 * Reads a Long out of a field in a Cursor and writes it to a Map. 864 * 865 * @param cursor The cursor to read from 866 * @param field The INTEGER field to read 867 * @param values The {@link ContentValues} to put the value into, with the field as the key 868 */ cursorLongToContentValues(Cursor cursor, String field, ContentValues values)869 public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values) 870 { 871 cursorLongToContentValues(cursor, field, values, field); 872 } 873 874 /** 875 * Reads a Long out of a field in a Cursor and writes it to a Map. 876 * 877 * @param cursor The cursor to read from 878 * @param field The INTEGER field to read 879 * @param values The {@link ContentValues} to put the value into 880 * @param key The key to store the value with in the map 881 */ cursorLongToContentValues(Cursor cursor, String field, ContentValues values, String key)882 public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values, 883 String key) { 884 int colIndex = cursor.getColumnIndex(field); 885 if (!cursor.isNull(colIndex)) { 886 Long value = Long.valueOf(cursor.getLong(colIndex)); 887 values.put(key, value); 888 } else { 889 values.put(key, (Long) null); 890 } 891 } 892 893 /** 894 * Reads a Double out of a field in a Cursor and writes it to a Map. 895 * 896 * @param cursor The cursor to read from 897 * @param field The REAL field to read 898 * @param values The {@link ContentValues} to put the value into 899 */ cursorDoubleToCursorValues(Cursor cursor, String field, ContentValues values)900 public static void cursorDoubleToCursorValues(Cursor cursor, String field, ContentValues values) 901 { 902 cursorDoubleToContentValues(cursor, field, values, field); 903 } 904 905 /** 906 * Reads a Double out of a field in a Cursor and writes it to a Map. 907 * 908 * @param cursor The cursor to read from 909 * @param field The REAL field to read 910 * @param values The {@link ContentValues} to put the value into 911 * @param key The key to store the value with in the map 912 */ cursorDoubleToContentValues(Cursor cursor, String field, ContentValues values, String key)913 public static void cursorDoubleToContentValues(Cursor cursor, String field, 914 ContentValues values, String key) { 915 int colIndex = cursor.getColumnIndex(field); 916 if (!cursor.isNull(colIndex)) { 917 values.put(key, cursor.getDouble(colIndex)); 918 } else { 919 values.put(key, (Double) null); 920 } 921 } 922 923 /** 924 * Read the entire contents of a cursor row and store them in a ContentValues. 925 * 926 * @param cursor the cursor to read from. 927 * @param values the {@link ContentValues} to put the row into. 928 */ cursorRowToContentValues(Cursor cursor, ContentValues values)929 public static void cursorRowToContentValues(Cursor cursor, ContentValues values) { 930 String[] columns = cursor.getColumnNames(); 931 int length = columns.length; 932 for (int i = 0; i < length; i++) { 933 if (cursor.getType(i) == Cursor.FIELD_TYPE_BLOB) { 934 values.put(columns[i], cursor.getBlob(i)); 935 } else { 936 values.put(columns[i], cursor.getString(i)); 937 } 938 } 939 } 940 941 /** 942 * Picks a start position for {@link Cursor#fillWindow} such that the 943 * window will contain the requested row and a useful range of rows 944 * around it. 945 * 946 * When the data set is too large to fit in a cursor window, seeking the 947 * cursor can become a very expensive operation since we have to run the 948 * query again when we move outside the bounds of the current window. 949 * 950 * We try to choose a start position for the cursor window such that 951 * 1/3 of the window's capacity is used to hold rows before the requested 952 * position and 2/3 of the window's capacity is used to hold rows after the 953 * requested position. 954 * 955 * @param cursorPosition The row index of the row we want to get. 956 * @param cursorWindowCapacity The estimated number of rows that can fit in 957 * a cursor window, or 0 if unknown. 958 * @return The recommended start position, always less than or equal to 959 * the requested row. 960 * @hide 961 */ 962 @UnsupportedAppUsage cursorPickFillWindowStartPosition( int cursorPosition, int cursorWindowCapacity)963 public static int cursorPickFillWindowStartPosition( 964 int cursorPosition, int cursorWindowCapacity) { 965 return Math.max(cursorPosition - cursorWindowCapacity / 3, 0); 966 } 967 968 /** 969 * Query the table for the number of rows in the table. 970 * @param db the database the table is in 971 * @param table the name of the table to query 972 * @return the number of rows in the table 973 */ queryNumEntries(SQLiteDatabase db, String table)974 public static long queryNumEntries(SQLiteDatabase db, String table) { 975 return queryNumEntries(db, table, null, null); 976 } 977 978 /** 979 * Query the table for the number of rows in the table. 980 * @param db the database the table is in 981 * @param table the name of the table to query 982 * @param selection A filter declaring which rows to return, 983 * formatted as an SQL WHERE clause (excluding the WHERE itself). 984 * Passing null will count all rows for the given table 985 * @return the number of rows in the table filtered by the selection 986 */ queryNumEntries(SQLiteDatabase db, String table, String selection)987 public static long queryNumEntries(SQLiteDatabase db, String table, String selection) { 988 return queryNumEntries(db, table, selection, null); 989 } 990 991 /** 992 * Query the table for the number of rows in the table. 993 * @param db the database the table is in 994 * @param table the name of the table to query 995 * @param selection A filter declaring which rows to return, 996 * formatted as an SQL WHERE clause (excluding the WHERE itself). 997 * Passing null will count all rows for the given table 998 * @param selectionArgs You may include ?s in selection, 999 * which will be replaced by the values from selectionArgs, 1000 * in order that they appear in the selection. 1001 * The values will be bound as Strings. 1002 * @return the number of rows in the table filtered by the selection 1003 */ queryNumEntries(SQLiteDatabase db, String table, String selection, String[] selectionArgs)1004 public static long queryNumEntries(SQLiteDatabase db, String table, String selection, 1005 String[] selectionArgs) { 1006 String s = (!TextUtils.isEmpty(selection)) ? " where " + selection : ""; 1007 return longForQuery(db, "select count(*) from " + table + s, 1008 selectionArgs); 1009 } 1010 1011 /** 1012 * Query the table to check whether a table is empty or not 1013 * @param db the database the table is in 1014 * @param table the name of the table to query 1015 * @return True if the table is empty 1016 * @hide 1017 */ queryIsEmpty(SQLiteDatabase db, String table)1018 public static boolean queryIsEmpty(SQLiteDatabase db, String table) { 1019 long isEmpty = longForQuery(db, "select exists(select 1 from " + table + ")", null); 1020 return isEmpty == 0; 1021 } 1022 1023 /** 1024 * Utility method to run the query on the db and return the value in the 1025 * first column of the first row. 1026 */ longForQuery(SQLiteDatabase db, String query, String[] selectionArgs)1027 public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs) { 1028 SQLiteStatement prog = db.compileStatement(query); 1029 try { 1030 return longForQuery(prog, selectionArgs); 1031 } finally { 1032 prog.close(); 1033 } 1034 } 1035 1036 /** 1037 * Utility method to run the pre-compiled query and return the value in the 1038 * first column of the first row. 1039 */ longForQuery(SQLiteStatement prog, String[] selectionArgs)1040 public static long longForQuery(SQLiteStatement prog, String[] selectionArgs) { 1041 prog.bindAllArgsAsStrings(selectionArgs); 1042 return prog.simpleQueryForLong(); 1043 } 1044 1045 /** 1046 * Utility method to run the query on the db and return the value in the 1047 * first column of the first row. 1048 */ stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs)1049 public static String stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs) { 1050 SQLiteStatement prog = db.compileStatement(query); 1051 try { 1052 return stringForQuery(prog, selectionArgs); 1053 } finally { 1054 prog.close(); 1055 } 1056 } 1057 1058 /** 1059 * Utility method to run the pre-compiled query and return the value in the 1060 * first column of the first row. 1061 */ stringForQuery(SQLiteStatement prog, String[] selectionArgs)1062 public static String stringForQuery(SQLiteStatement prog, String[] selectionArgs) { 1063 prog.bindAllArgsAsStrings(selectionArgs); 1064 return prog.simpleQueryForString(); 1065 } 1066 1067 /** 1068 * Utility method to run the query on the db and return the blob value in the 1069 * first column of the first row. 1070 * 1071 * @return A read-only file descriptor for a copy of the blob value. 1072 */ blobFileDescriptorForQuery(SQLiteDatabase db, String query, String[] selectionArgs)1073 public static ParcelFileDescriptor blobFileDescriptorForQuery(SQLiteDatabase db, 1074 String query, String[] selectionArgs) { 1075 SQLiteStatement prog = db.compileStatement(query); 1076 try { 1077 return blobFileDescriptorForQuery(prog, selectionArgs); 1078 } finally { 1079 prog.close(); 1080 } 1081 } 1082 1083 /** 1084 * Utility method to run the pre-compiled query and return the blob value in the 1085 * first column of the first row. 1086 * 1087 * @return A read-only file descriptor for a copy of the blob value. 1088 */ blobFileDescriptorForQuery(SQLiteStatement prog, String[] selectionArgs)1089 public static ParcelFileDescriptor blobFileDescriptorForQuery(SQLiteStatement prog, 1090 String[] selectionArgs) { 1091 prog.bindAllArgsAsStrings(selectionArgs); 1092 return prog.simpleQueryForBlobFileDescriptor(); 1093 } 1094 1095 /** 1096 * Reads a String out of a column in a Cursor and writes it to a ContentValues. 1097 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 1098 * 1099 * @param cursor The cursor to read from 1100 * @param column The column to read 1101 * @param values The {@link ContentValues} to put the value into 1102 */ cursorStringToContentValuesIfPresent(Cursor cursor, ContentValues values, String column)1103 public static void cursorStringToContentValuesIfPresent(Cursor cursor, ContentValues values, 1104 String column) { 1105 final int index = cursor.getColumnIndex(column); 1106 if (index != -1 && !cursor.isNull(index)) { 1107 values.put(column, cursor.getString(index)); 1108 } 1109 } 1110 1111 /** 1112 * Reads a Long out of a column in a Cursor and writes it to a ContentValues. 1113 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 1114 * 1115 * @param cursor The cursor to read from 1116 * @param column The column to read 1117 * @param values The {@link ContentValues} to put the value into 1118 */ cursorLongToContentValuesIfPresent(Cursor cursor, ContentValues values, String column)1119 public static void cursorLongToContentValuesIfPresent(Cursor cursor, ContentValues values, 1120 String column) { 1121 final int index = cursor.getColumnIndex(column); 1122 if (index != -1 && !cursor.isNull(index)) { 1123 values.put(column, cursor.getLong(index)); 1124 } 1125 } 1126 1127 /** 1128 * Reads a Short out of a column in a Cursor and writes it to a ContentValues. 1129 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 1130 * 1131 * @param cursor The cursor to read from 1132 * @param column The column to read 1133 * @param values The {@link ContentValues} to put the value into 1134 */ cursorShortToContentValuesIfPresent(Cursor cursor, ContentValues values, String column)1135 public static void cursorShortToContentValuesIfPresent(Cursor cursor, ContentValues values, 1136 String column) { 1137 final int index = cursor.getColumnIndex(column); 1138 if (index != -1 && !cursor.isNull(index)) { 1139 values.put(column, cursor.getShort(index)); 1140 } 1141 } 1142 1143 /** 1144 * Reads a Integer out of a column in a Cursor and writes it to a ContentValues. 1145 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 1146 * 1147 * @param cursor The cursor to read from 1148 * @param column The column to read 1149 * @param values The {@link ContentValues} to put the value into 1150 */ cursorIntToContentValuesIfPresent(Cursor cursor, ContentValues values, String column)1151 public static void cursorIntToContentValuesIfPresent(Cursor cursor, ContentValues values, 1152 String column) { 1153 final int index = cursor.getColumnIndex(column); 1154 if (index != -1 && !cursor.isNull(index)) { 1155 values.put(column, cursor.getInt(index)); 1156 } 1157 } 1158 1159 /** 1160 * Reads a Float out of a column in a Cursor and writes it to a ContentValues. 1161 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 1162 * 1163 * @param cursor The cursor to read from 1164 * @param column The column to read 1165 * @param values The {@link ContentValues} to put the value into 1166 */ cursorFloatToContentValuesIfPresent(Cursor cursor, ContentValues values, String column)1167 public static void cursorFloatToContentValuesIfPresent(Cursor cursor, ContentValues values, 1168 String column) { 1169 final int index = cursor.getColumnIndex(column); 1170 if (index != -1 && !cursor.isNull(index)) { 1171 values.put(column, cursor.getFloat(index)); 1172 } 1173 } 1174 1175 /** 1176 * Reads a Double out of a column in a Cursor and writes it to a ContentValues. 1177 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 1178 * 1179 * @param cursor The cursor to read from 1180 * @param column The column to read 1181 * @param values The {@link ContentValues} to put the value into 1182 */ cursorDoubleToContentValuesIfPresent(Cursor cursor, ContentValues values, String column)1183 public static void cursorDoubleToContentValuesIfPresent(Cursor cursor, ContentValues values, 1184 String column) { 1185 final int index = cursor.getColumnIndex(column); 1186 if (index != -1 && !cursor.isNull(index)) { 1187 values.put(column, cursor.getDouble(index)); 1188 } 1189 } 1190 1191 /** 1192 * This class allows users to do multiple inserts into a table using 1193 * the same statement. 1194 * <p> 1195 * This class is not thread-safe. 1196 * </p> 1197 * 1198 * @deprecated Use {@link SQLiteStatement} instead. 1199 */ 1200 @Deprecated 1201 public static class InsertHelper { 1202 private final SQLiteDatabase mDb; 1203 private final String mTableName; 1204 private HashMap<String, Integer> mColumns; 1205 private String mInsertSQL = null; 1206 private SQLiteStatement mInsertStatement = null; 1207 private SQLiteStatement mReplaceStatement = null; 1208 private SQLiteStatement mPreparedStatement = null; 1209 1210 /** 1211 * {@hide} 1212 * 1213 * These are the columns returned by sqlite's "PRAGMA 1214 * table_info(...)" command that we depend on. 1215 */ 1216 public static final int TABLE_INFO_PRAGMA_COLUMNNAME_INDEX = 1; 1217 1218 /** 1219 * This field was accidentally exposed in earlier versions of the platform 1220 * so we can hide it but we can't remove it. 1221 * 1222 * @hide 1223 */ 1224 public static final int TABLE_INFO_PRAGMA_DEFAULT_INDEX = 4; 1225 1226 /** 1227 * @param db the SQLiteDatabase to insert into 1228 * @param tableName the name of the table to insert into 1229 */ InsertHelper(SQLiteDatabase db, String tableName)1230 public InsertHelper(SQLiteDatabase db, String tableName) { 1231 mDb = db; 1232 mTableName = tableName; 1233 } 1234 buildSQL()1235 private void buildSQL() throws SQLException { 1236 StringBuilder sb = new StringBuilder(128); 1237 sb.append("INSERT INTO "); 1238 sb.append(mTableName); 1239 sb.append(" ("); 1240 1241 StringBuilder sbv = new StringBuilder(128); 1242 sbv.append("VALUES ("); 1243 1244 int i = 1; 1245 Cursor cur = null; 1246 try { 1247 cur = mDb.rawQuery("PRAGMA table_info(" + mTableName + ")", null); 1248 mColumns = new HashMap<String, Integer>(cur.getCount()); 1249 while (cur.moveToNext()) { 1250 String columnName = cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX); 1251 String defaultValue = cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX); 1252 1253 mColumns.put(columnName, i); 1254 sb.append("'"); 1255 sb.append(columnName); 1256 sb.append("'"); 1257 1258 if (defaultValue == null) { 1259 sbv.append("?"); 1260 } else { 1261 sbv.append("COALESCE(?, "); 1262 sbv.append(defaultValue); 1263 sbv.append(")"); 1264 } 1265 1266 sb.append(i == cur.getCount() ? ") " : ", "); 1267 sbv.append(i == cur.getCount() ? ");" : ", "); 1268 ++i; 1269 } 1270 } finally { 1271 if (cur != null) cur.close(); 1272 } 1273 1274 sb.append(sbv); 1275 1276 mInsertSQL = sb.toString(); 1277 if (DEBUG) Log.v(TAG, "insert statement is " + mInsertSQL); 1278 } 1279 getStatement(boolean allowReplace)1280 private SQLiteStatement getStatement(boolean allowReplace) throws SQLException { 1281 if (allowReplace) { 1282 if (mReplaceStatement == null) { 1283 if (mInsertSQL == null) buildSQL(); 1284 // chop "INSERT" off the front and prepend "INSERT OR REPLACE" instead. 1285 String replaceSQL = "INSERT OR REPLACE" + mInsertSQL.substring(6); 1286 mReplaceStatement = mDb.compileStatement(replaceSQL); 1287 } 1288 return mReplaceStatement; 1289 } else { 1290 if (mInsertStatement == null) { 1291 if (mInsertSQL == null) buildSQL(); 1292 mInsertStatement = mDb.compileStatement(mInsertSQL); 1293 } 1294 return mInsertStatement; 1295 } 1296 } 1297 1298 /** 1299 * Performs an insert, adding a new row with the given values. 1300 * 1301 * @param values the set of values with which to populate the 1302 * new row 1303 * @param allowReplace if true, the statement does "INSERT OR 1304 * REPLACE" instead of "INSERT", silently deleting any 1305 * previously existing rows that would cause a conflict 1306 * 1307 * @return the row ID of the newly inserted row, or -1 if an 1308 * error occurred 1309 */ insertInternal(ContentValues values, boolean allowReplace)1310 private long insertInternal(ContentValues values, boolean allowReplace) { 1311 // Start a transaction even though we don't really need one. 1312 // This is to help maintain compatibility with applications that 1313 // access InsertHelper from multiple threads even though they never should have. 1314 // The original code used to lock the InsertHelper itself which was prone 1315 // to deadlocks. Starting a transaction achieves the same mutual exclusion 1316 // effect as grabbing a lock but without the potential for deadlocks. 1317 mDb.beginTransactionNonExclusive(); 1318 try { 1319 SQLiteStatement stmt = getStatement(allowReplace); 1320 stmt.clearBindings(); 1321 if (DEBUG) Log.v(TAG, "--- inserting in table " + mTableName); 1322 for (Map.Entry<String, Object> e: values.valueSet()) { 1323 final String key = e.getKey(); 1324 int i = getColumnIndex(key); 1325 DatabaseUtils.bindObjectToProgram(stmt, i, e.getValue()); 1326 if (DEBUG) { 1327 Log.v(TAG, "binding " + e.getValue() + " to column " + 1328 i + " (" + key + ")"); 1329 } 1330 } 1331 long result = stmt.executeInsert(); 1332 mDb.setTransactionSuccessful(); 1333 return result; 1334 } catch (SQLException e) { 1335 Log.e(TAG, "Error inserting " + values + " into table " + mTableName, e); 1336 return -1; 1337 } finally { 1338 mDb.endTransaction(); 1339 } 1340 } 1341 1342 /** 1343 * Returns the index of the specified column. This is index is suitagble for use 1344 * in calls to bind(). 1345 * @param key the column name 1346 * @return the index of the column 1347 */ getColumnIndex(String key)1348 public int getColumnIndex(String key) { 1349 getStatement(false); 1350 final Integer index = mColumns.get(key); 1351 if (index == null) { 1352 throw new IllegalArgumentException("column '" + key + "' is invalid"); 1353 } 1354 return index; 1355 } 1356 1357 /** 1358 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1359 * without a matching execute() must have already have been called. 1360 * @param index the index of the slot to which to bind 1361 * @param value the value to bind 1362 */ bind(int index, double value)1363 public void bind(int index, double value) { 1364 mPreparedStatement.bindDouble(index, value); 1365 } 1366 1367 /** 1368 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1369 * without a matching execute() must have already have been called. 1370 * @param index the index of the slot to which to bind 1371 * @param value the value to bind 1372 */ bind(int index, float value)1373 public void bind(int index, float value) { 1374 mPreparedStatement.bindDouble(index, value); 1375 } 1376 1377 /** 1378 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1379 * without a matching execute() must have already have been called. 1380 * @param index the index of the slot to which to bind 1381 * @param value the value to bind 1382 */ bind(int index, long value)1383 public void bind(int index, long value) { 1384 mPreparedStatement.bindLong(index, value); 1385 } 1386 1387 /** 1388 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1389 * without a matching execute() must have already have been called. 1390 * @param index the index of the slot to which to bind 1391 * @param value the value to bind 1392 */ bind(int index, int value)1393 public void bind(int index, int value) { 1394 mPreparedStatement.bindLong(index, value); 1395 } 1396 1397 /** 1398 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1399 * without a matching execute() must have already have been called. 1400 * @param index the index of the slot to which to bind 1401 * @param value the value to bind 1402 */ bind(int index, boolean value)1403 public void bind(int index, boolean value) { 1404 mPreparedStatement.bindLong(index, value ? 1 : 0); 1405 } 1406 1407 /** 1408 * Bind null to an index. A prepareForInsert() or prepareForReplace() 1409 * without a matching execute() must have already have been called. 1410 * @param index the index of the slot to which to bind 1411 */ bindNull(int index)1412 public void bindNull(int index) { 1413 mPreparedStatement.bindNull(index); 1414 } 1415 1416 /** 1417 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1418 * without a matching execute() must have already have been called. 1419 * @param index the index of the slot to which to bind 1420 * @param value the value to bind 1421 */ bind(int index, byte[] value)1422 public void bind(int index, byte[] value) { 1423 if (value == null) { 1424 mPreparedStatement.bindNull(index); 1425 } else { 1426 mPreparedStatement.bindBlob(index, value); 1427 } 1428 } 1429 1430 /** 1431 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1432 * without a matching execute() must have already have been called. 1433 * @param index the index of the slot to which to bind 1434 * @param value the value to bind 1435 */ bind(int index, String value)1436 public void bind(int index, String value) { 1437 if (value == null) { 1438 mPreparedStatement.bindNull(index); 1439 } else { 1440 mPreparedStatement.bindString(index, value); 1441 } 1442 } 1443 1444 /** 1445 * Performs an insert, adding a new row with the given values. 1446 * If the table contains conflicting rows, an error is 1447 * returned. 1448 * 1449 * @param values the set of values with which to populate the 1450 * new row 1451 * 1452 * @return the row ID of the newly inserted row, or -1 if an 1453 * error occurred 1454 */ insert(ContentValues values)1455 public long insert(ContentValues values) { 1456 return insertInternal(values, false); 1457 } 1458 1459 /** 1460 * Execute the previously prepared insert or replace using the bound values 1461 * since the last call to prepareForInsert or prepareForReplace. 1462 * 1463 * <p>Note that calling bind() and then execute() is not thread-safe. The only thread-safe 1464 * way to use this class is to call insert() or replace(). 1465 * 1466 * @return the row ID of the newly inserted row, or -1 if an 1467 * error occurred 1468 */ execute()1469 public long execute() { 1470 if (mPreparedStatement == null) { 1471 throw new IllegalStateException("you must prepare this inserter before calling " 1472 + "execute"); 1473 } 1474 try { 1475 if (DEBUG) Log.v(TAG, "--- doing insert or replace in table " + mTableName); 1476 return mPreparedStatement.executeInsert(); 1477 } catch (SQLException e) { 1478 Log.e(TAG, "Error executing InsertHelper with table " + mTableName, e); 1479 return -1; 1480 } finally { 1481 // you can only call this once per prepare 1482 mPreparedStatement = null; 1483 } 1484 } 1485 1486 /** 1487 * Prepare the InsertHelper for an insert. The pattern for this is: 1488 * <ul> 1489 * <li>prepareForInsert() 1490 * <li>bind(index, value); 1491 * <li>bind(index, value); 1492 * <li>... 1493 * <li>bind(index, value); 1494 * <li>execute(); 1495 * </ul> 1496 */ prepareForInsert()1497 public void prepareForInsert() { 1498 mPreparedStatement = getStatement(false); 1499 mPreparedStatement.clearBindings(); 1500 } 1501 1502 /** 1503 * Prepare the InsertHelper for a replace. The pattern for this is: 1504 * <ul> 1505 * <li>prepareForReplace() 1506 * <li>bind(index, value); 1507 * <li>bind(index, value); 1508 * <li>... 1509 * <li>bind(index, value); 1510 * <li>execute(); 1511 * </ul> 1512 */ prepareForReplace()1513 public void prepareForReplace() { 1514 mPreparedStatement = getStatement(true); 1515 mPreparedStatement.clearBindings(); 1516 } 1517 1518 /** 1519 * Performs an insert, adding a new row with the given values. 1520 * If the table contains conflicting rows, they are deleted 1521 * and replaced with the new row. 1522 * 1523 * @param values the set of values with which to populate the 1524 * new row 1525 * 1526 * @return the row ID of the newly inserted row, or -1 if an 1527 * error occurred 1528 */ replace(ContentValues values)1529 public long replace(ContentValues values) { 1530 return insertInternal(values, true); 1531 } 1532 1533 /** 1534 * Close this object and release any resources associated with 1535 * it. The behavior of calling <code>insert()</code> after 1536 * calling this method is undefined. 1537 */ close()1538 public void close() { 1539 if (mInsertStatement != null) { 1540 mInsertStatement.close(); 1541 mInsertStatement = null; 1542 } 1543 if (mReplaceStatement != null) { 1544 mReplaceStatement.close(); 1545 mReplaceStatement = null; 1546 } 1547 mInsertSQL = null; 1548 mColumns = null; 1549 } 1550 } 1551 1552 /** 1553 * Creates a db and populates it with the sql statements in sqlStatements. 1554 * 1555 * @param context the context to use to create the db 1556 * @param dbName the name of the db to create 1557 * @param dbVersion the version to set on the db 1558 * @param sqlStatements the statements to use to populate the db. This should be a single string 1559 * of the form returned by sqlite3's <tt>.dump</tt> command (statements separated by 1560 * semicolons) 1561 */ createDbFromSqlStatements( Context context, String dbName, int dbVersion, String sqlStatements)1562 static public void createDbFromSqlStatements( 1563 Context context, String dbName, int dbVersion, String sqlStatements) { 1564 SQLiteDatabase db = context.openOrCreateDatabase(dbName, 0, null); 1565 // TODO: this is not quite safe since it assumes that all semicolons at the end of a line 1566 // terminate statements. It is possible that a text field contains ;\n. We will have to fix 1567 // this if that turns out to be a problem. 1568 String[] statements = TextUtils.split(sqlStatements, ";\n"); 1569 for (String statement : statements) { 1570 if (TextUtils.isEmpty(statement)) continue; 1571 db.execSQL(statement); 1572 } 1573 db.setVersion(dbVersion); 1574 db.close(); 1575 } 1576 1577 /** 1578 * Return the index of the first character past comments and whitespace. -1 is returned if 1579 * a comment is malformed. 1580 */ getSqlStatementPrefixOffset(String s)1581 private static int getSqlStatementPrefixOffset(String s) { 1582 final int limit = s.length() - 2; 1583 if (limit < 0) return -1; 1584 int i = 0; 1585 while (i < limit) { 1586 final char c = s.charAt(i); 1587 if (c <= ' ') { 1588 // This behavior conforms to String.trim(), which is used by the legacy Android 1589 // SQL prefix logic. This test is not unicode-aware. Notice that it accepts the 1590 // null character as whitespace even though the null character will terminate the 1591 // SQL string in native code. 1592 i++; 1593 } else if (c == '-') { 1594 if (s.charAt(i+1) != '-') return i; 1595 i = s.indexOf('\n', i+2); 1596 if (i < 0) return -1; 1597 i++; 1598 } else if (c == '/') { 1599 if (s.charAt(i+1) != '*') return i; 1600 i++; 1601 do { 1602 i = s.indexOf('*', i+1); 1603 if (i < 0) return -1; 1604 i++; 1605 } while (s.charAt(i) != '/'); 1606 i++; 1607 } else { 1608 return i; 1609 } 1610 } 1611 return -1; 1612 } 1613 1614 /** 1615 * Scan past leading comments without using the Java regex routines. 1616 */ getSqlStatementPrefixExtendedNoRegex(String sql)1617 private static String getSqlStatementPrefixExtendedNoRegex(String sql) { 1618 int n = getSqlStatementPrefixOffset(sql); 1619 if (n < 0) { 1620 // Bad comment syntax. 1621 return null; 1622 } 1623 final int end = sql.length(); 1624 if (n > end) { 1625 // Bad scanning. This indicates a programming error. 1626 return null; 1627 } 1628 final int eos = Math.min(n+3, end); 1629 return sql.substring(n, eos).toUpperCase(Locale.ROOT); 1630 } 1631 1632 /** 1633 * Return the extended statement type for the SQL statement. This is not a public API and it 1634 * can return values that are not publicly visible. 1635 * @hide 1636 */ categorizeStatement(@onNull String prefix, @NonNull String sql)1637 private static int categorizeStatement(@NonNull String prefix, @NonNull String sql) { 1638 if (prefix == null) return STATEMENT_OTHER; 1639 1640 switch (prefix) { 1641 case "SEL": return STATEMENT_SELECT; 1642 case "INS": 1643 case "UPD": 1644 case "REP": 1645 case "DEL": return STATEMENT_UPDATE; 1646 case "ATT": return STATEMENT_ATTACH; 1647 case "COM": 1648 case "END": return STATEMENT_COMMIT; 1649 case "ROL": 1650 if (sql.toUpperCase(Locale.ROOT).contains(" TO ")) { 1651 // Rollback to savepoint. 1652 return STATEMENT_OTHER; 1653 } 1654 return STATEMENT_ABORT; 1655 case "BEG": return STATEMENT_BEGIN; 1656 case "PRA": return STATEMENT_PRAGMA; 1657 case "CRE": return STATEMENT_CREATE; 1658 case "DRO": 1659 case "ALT": return STATEMENT_DDL; 1660 case "ANA": 1661 case "DET": return STATEMENT_UNPREPARED; 1662 case "WIT": return STATEMENT_WITH; 1663 default: 1664 if (prefix.startsWith("--") || prefix.startsWith("/*")) { 1665 return STATEMENT_COMMENT; 1666 } 1667 return STATEMENT_OTHER; 1668 } 1669 } 1670 1671 /** 1672 * Return the extended statement type for the SQL statement. This is not a public API and it 1673 * can return values that are not publicly visible. 1674 * @hide 1675 */ getSqlStatementTypeExtended(@onNull String sql)1676 public static int getSqlStatementTypeExtended(@NonNull String sql) { 1677 return categorizeStatement(getSqlStatementPrefixExtendedNoRegex(sql), sql); 1678 } 1679 1680 /** 1681 * Convert an extended statement type to a public SQL statement type value. 1682 * @hide 1683 */ getSqlStatementType(int extended)1684 public static int getSqlStatementType(int extended) { 1685 switch (extended) { 1686 case STATEMENT_CREATE: return STATEMENT_DDL; 1687 case STATEMENT_WITH: return STATEMENT_OTHER; 1688 case STATEMENT_COMMENT: return STATEMENT_OTHER; 1689 } 1690 return extended; 1691 } 1692 1693 /** 1694 * Returns one of the following which represent the type of the given SQL statement. 1695 * <ol> 1696 * <li>{@link #STATEMENT_SELECT}</li> 1697 * <li>{@link #STATEMENT_UPDATE}</li> 1698 * <li>{@link #STATEMENT_ATTACH}</li> 1699 * <li>{@link #STATEMENT_BEGIN}</li> 1700 * <li>{@link #STATEMENT_COMMIT}</li> 1701 * <li>{@link #STATEMENT_ABORT}</li> 1702 * <li>{@link #STATEMENT_PRAGMA}</li> 1703 * <li>{@link #STATEMENT_DDL}</li> 1704 * <li>{@link #STATEMENT_UNPREPARED}</li> 1705 * <li>{@link #STATEMENT_OTHER}</li> 1706 * </ol> 1707 * @param sql the SQL statement whose type is returned by this method 1708 * @return one of the values listed above 1709 */ getSqlStatementType(String sql)1710 public static int getSqlStatementType(String sql) { 1711 return getSqlStatementType(getSqlStatementTypeExtended(sql)); 1712 } 1713 1714 /** 1715 * Appends one set of selection args to another. This is useful when adding a selection 1716 * argument to a user provided set. 1717 */ appendSelectionArgs(String[] originalValues, String[] newValues)1718 public static String[] appendSelectionArgs(String[] originalValues, String[] newValues) { 1719 if (originalValues == null || originalValues.length == 0) { 1720 return newValues; 1721 } 1722 String[] result = new String[originalValues.length + newValues.length ]; 1723 System.arraycopy(originalValues, 0, result, 0, originalValues.length); 1724 System.arraycopy(newValues, 0, result, originalValues.length, newValues.length); 1725 return result; 1726 } 1727 1728 /** 1729 * Returns column index of "_id" column, or -1 if not found. 1730 * @hide 1731 */ findRowIdColumnIndex(String[] columnNames)1732 public static int findRowIdColumnIndex(String[] columnNames) { 1733 int length = columnNames.length; 1734 for (int i = 0; i < length; i++) { 1735 if (columnNames[i].equals("_id")) { 1736 return i; 1737 } 1738 } 1739 return -1; 1740 } 1741 1742 /** 1743 * Escape the given argument for use in a {@code LIKE} statement. 1744 * @hide 1745 */ escapeForLike(@onNull String arg)1746 public static String escapeForLike(@NonNull String arg) { 1747 // Shamelessly borrowed from com.android.providers.media.util.DatabaseUtils 1748 final StringBuilder sb = new StringBuilder(); 1749 for (int i = 0; i < arg.length(); i++) { 1750 final char c = arg.charAt(i); 1751 switch (c) { 1752 case '%': sb.append('\\'); 1753 break; 1754 case '_': sb.append('\\'); 1755 break; 1756 } 1757 sb.append(c); 1758 } 1759 return sb.toString(); 1760 } 1761 } 1762