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