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