DatabaseUtils.java revision 8b0dd7da360d70920a37802eb455ba41500d3b45
1/* 2 * Copyright (C) 2006 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17package android.database; 18 19import org.apache.commons.codec.binary.Hex; 20 21import android.content.ContentValues; 22import android.content.Context; 23import android.content.OperationApplicationException; 24import android.database.sqlite.SQLiteAbortException; 25import android.database.sqlite.SQLiteConstraintException; 26import android.database.sqlite.SQLiteDatabase; 27import android.database.sqlite.SQLiteDatabaseCorruptException; 28import android.database.sqlite.SQLiteDiskIOException; 29import android.database.sqlite.SQLiteException; 30import android.database.sqlite.SQLiteFullException; 31import android.database.sqlite.SQLiteProgram; 32import android.database.sqlite.SQLiteStatement; 33import android.os.Parcel; 34import android.text.TextUtils; 35import android.util.Config; 36import android.util.Log; 37 38import java.io.FileNotFoundException; 39import java.io.PrintStream; 40import java.text.Collator; 41import java.util.HashMap; 42import java.util.Map; 43 44/** 45 * Static utility methods for dealing with databases and {@link Cursor}s. 46 */ 47public class DatabaseUtils { 48 private static final String TAG = "DatabaseUtils"; 49 50 private static final boolean DEBUG = false; 51 private static final boolean LOCAL_LOGV = DEBUG ? Config.LOGD : Config.LOGV; 52 53 private static final String[] countProjection = new String[]{"count(*)"}; 54 55 /** 56 * Special function for writing an exception result at the header of 57 * a parcel, to be used when returning an exception from a transaction. 58 * exception will be re-thrown by the function in another process 59 * @param reply Parcel to write to 60 * @param e The Exception to be written. 61 * @see Parcel#writeNoException 62 * @see Parcel#writeException 63 */ 64 public static final void writeExceptionToParcel(Parcel reply, Exception e) { 65 int code = 0; 66 boolean logException = true; 67 if (e instanceof FileNotFoundException) { 68 code = 1; 69 logException = false; 70 } else if (e instanceof IllegalArgumentException) { 71 code = 2; 72 } else if (e instanceof UnsupportedOperationException) { 73 code = 3; 74 } else if (e instanceof SQLiteAbortException) { 75 code = 4; 76 } else if (e instanceof SQLiteConstraintException) { 77 code = 5; 78 } else if (e instanceof SQLiteDatabaseCorruptException) { 79 code = 6; 80 } else if (e instanceof SQLiteFullException) { 81 code = 7; 82 } else if (e instanceof SQLiteDiskIOException) { 83 code = 8; 84 } else if (e instanceof SQLiteException) { 85 code = 9; 86 } else if (e instanceof OperationApplicationException) { 87 code = 10; 88 } else { 89 reply.writeException(e); 90 Log.e(TAG, "Writing exception to parcel", e); 91 return; 92 } 93 reply.writeInt(code); 94 reply.writeString(e.getMessage()); 95 96 if (logException) { 97 Log.e(TAG, "Writing exception to parcel", e); 98 } 99 } 100 101 /** 102 * Special function for reading an exception result from the header of 103 * a parcel, to be used after receiving the result of a transaction. This 104 * will throw the exception for you if it had been written to the Parcel, 105 * otherwise return and let you read the normal result data from the Parcel. 106 * @param reply Parcel to read from 107 * @see Parcel#writeNoException 108 * @see Parcel#readException 109 */ 110 public static final void readExceptionFromParcel(Parcel reply) { 111 int code = reply.readInt(); 112 if (code == 0) return; 113 String msg = reply.readString(); 114 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 115 } 116 117 public static void readExceptionWithFileNotFoundExceptionFromParcel( 118 Parcel reply) throws FileNotFoundException { 119 int code = reply.readInt(); 120 if (code == 0) return; 121 String msg = reply.readString(); 122 if (code == 1) { 123 throw new FileNotFoundException(msg); 124 } else { 125 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 126 } 127 } 128 129 public static void readExceptionWithOperationApplicationExceptionFromParcel( 130 Parcel reply) throws OperationApplicationException { 131 int code = reply.readInt(); 132 if (code == 0) return; 133 String msg = reply.readString(); 134 if (code == 10) { 135 throw new OperationApplicationException(msg); 136 } else { 137 DatabaseUtils.readExceptionFromParcel(reply, msg, code); 138 } 139 } 140 141 private static final void readExceptionFromParcel(Parcel reply, String msg, int code) { 142 switch (code) { 143 case 2: 144 throw new IllegalArgumentException(msg); 145 case 3: 146 throw new UnsupportedOperationException(msg); 147 case 4: 148 throw new SQLiteAbortException(msg); 149 case 5: 150 throw new SQLiteConstraintException(msg); 151 case 6: 152 throw new SQLiteDatabaseCorruptException(msg); 153 case 7: 154 throw new SQLiteFullException(msg); 155 case 8: 156 throw new SQLiteDiskIOException(msg); 157 case 9: 158 throw new SQLiteException(msg); 159 default: 160 reply.readException(code, msg); 161 } 162 } 163 164 /** 165 * Binds the given Object to the given SQLiteProgram using the proper 166 * typing. For example, bind numbers as longs/doubles, and everything else 167 * as a string by call toString() on it. 168 * 169 * @param prog the program to bind the object to 170 * @param index the 1-based index to bind at 171 * @param value the value to bind 172 */ 173 public static void bindObjectToProgram(SQLiteProgram prog, int index, 174 Object value) { 175 if (value == null) { 176 prog.bindNull(index); 177 } else if (value instanceof Double || value instanceof Float) { 178 prog.bindDouble(index, ((Number)value).doubleValue()); 179 } else if (value instanceof Number) { 180 prog.bindLong(index, ((Number)value).longValue()); 181 } else if (value instanceof Boolean) { 182 Boolean bool = (Boolean)value; 183 if (bool) { 184 prog.bindLong(index, 1); 185 } else { 186 prog.bindLong(index, 0); 187 } 188 } else if (value instanceof byte[]){ 189 prog.bindBlob(index, (byte[]) value); 190 } else { 191 prog.bindString(index, value.toString()); 192 } 193 } 194 195 /** 196 * Returns data type of the given object's value. 197 *<p> 198 * Returned values are 199 * <ul> 200 * <li>{@link Cursor#FIELD_TYPE_NULL}</li> 201 * <li>{@link Cursor#FIELD_TYPE_INTEGER}</li> 202 * <li>{@link Cursor#FIELD_TYPE_FLOAT}</li> 203 * <li>{@link Cursor#FIELD_TYPE_STRING}</li> 204 * <li>{@link Cursor#FIELD_TYPE_BLOB}</li> 205 *</ul> 206 *</p> 207 * 208 * @param obj the object whose value type is to be returned 209 * @return object value type 210 * @hide 211 */ 212 public static int getTypeOfObject(Object obj) { 213 if (obj == null) { 214 return Cursor.FIELD_TYPE_NULL; 215 } else if (obj instanceof byte[]) { 216 return Cursor.FIELD_TYPE_BLOB; 217 } else if (obj instanceof Float || obj instanceof Double) { 218 return Cursor.FIELD_TYPE_FLOAT; 219 } else if (obj instanceof Long || obj instanceof Integer) { 220 return Cursor.FIELD_TYPE_INTEGER; 221 } else { 222 return Cursor.FIELD_TYPE_STRING; 223 } 224 } 225 226 /** 227 * Appends an SQL string to the given StringBuilder, including the opening 228 * and closing single quotes. Any single quotes internal to sqlString will 229 * be escaped. 230 * 231 * This method is deprecated because we want to encourage everyone 232 * to use the "?" binding form. However, when implementing a 233 * ContentProvider, one may want to add WHERE clauses that were 234 * not provided by the caller. Since "?" is a positional form, 235 * using it in this case could break the caller because the 236 * indexes would be shifted to accomodate the ContentProvider's 237 * internal bindings. In that case, it may be necessary to 238 * construct a WHERE clause manually. This method is useful for 239 * those cases. 240 * 241 * @param sb the StringBuilder that the SQL string will be appended to 242 * @param sqlString the raw string to be appended, which may contain single 243 * quotes 244 */ 245 public static void appendEscapedSQLString(StringBuilder sb, String sqlString) { 246 sb.append('\''); 247 if (sqlString.indexOf('\'') != -1) { 248 int length = sqlString.length(); 249 for (int i = 0; i < length; i++) { 250 char c = sqlString.charAt(i); 251 if (c == '\'') { 252 sb.append('\''); 253 } 254 sb.append(c); 255 } 256 } else 257 sb.append(sqlString); 258 sb.append('\''); 259 } 260 261 /** 262 * SQL-escape a string. 263 */ 264 public static String sqlEscapeString(String value) { 265 StringBuilder escaper = new StringBuilder(); 266 267 DatabaseUtils.appendEscapedSQLString(escaper, value); 268 269 return escaper.toString(); 270 } 271 272 /** 273 * Appends an Object to an SQL string with the proper escaping, etc. 274 */ 275 public static final void appendValueToSql(StringBuilder sql, Object value) { 276 if (value == null) { 277 sql.append("NULL"); 278 } else if (value instanceof Boolean) { 279 Boolean bool = (Boolean)value; 280 if (bool) { 281 sql.append('1'); 282 } else { 283 sql.append('0'); 284 } 285 } else { 286 appendEscapedSQLString(sql, value.toString()); 287 } 288 } 289 290 /** 291 * Concatenates two SQL WHERE clauses, handling empty or null values. 292 * @hide 293 */ 294 public static String concatenateWhere(String a, String b) { 295 if (TextUtils.isEmpty(a)) { 296 return b; 297 } 298 if (TextUtils.isEmpty(b)) { 299 return a; 300 } 301 302 return "(" + a + ") AND (" + b + ")"; 303 } 304 305 /** 306 * return the collation key 307 * @param name 308 * @return the collation key 309 */ 310 public static String getCollationKey(String name) { 311 byte [] arr = getCollationKeyInBytes(name); 312 try { 313 return new String(arr, 0, getKeyLen(arr), "ISO8859_1"); 314 } catch (Exception ex) { 315 return ""; 316 } 317 } 318 319 /** 320 * return the collation key in hex format 321 * @param name 322 * @return the collation key in hex format 323 */ 324 public static String getHexCollationKey(String name) { 325 byte [] arr = getCollationKeyInBytes(name); 326 char[] keys = Hex.encodeHex(arr); 327 return new String(keys, 0, getKeyLen(arr) * 2); 328 } 329 330 private static int getKeyLen(byte[] arr) { 331 if (arr[arr.length - 1] != 0) { 332 return arr.length; 333 } else { 334 // remove zero "termination" 335 return arr.length-1; 336 } 337 } 338 339 private static byte[] getCollationKeyInBytes(String name) { 340 if (mColl == null) { 341 mColl = Collator.getInstance(); 342 mColl.setStrength(Collator.PRIMARY); 343 } 344 return mColl.getCollationKey(name).toByteArray(); 345 } 346 347 private static Collator mColl = null; 348 /** 349 * Prints the contents of a Cursor to System.out. The position is restored 350 * after printing. 351 * 352 * @param cursor the cursor to print 353 */ 354 public static void dumpCursor(Cursor cursor) { 355 dumpCursor(cursor, System.out); 356 } 357 358 /** 359 * Prints the contents of a Cursor to a PrintSteam. The position is restored 360 * after printing. 361 * 362 * @param cursor the cursor to print 363 * @param stream the stream to print to 364 */ 365 public static void dumpCursor(Cursor cursor, PrintStream stream) { 366 stream.println(">>>>> Dumping cursor " + cursor); 367 if (cursor != null) { 368 int startPos = cursor.getPosition(); 369 370 cursor.moveToPosition(-1); 371 while (cursor.moveToNext()) { 372 dumpCurrentRow(cursor, stream); 373 } 374 cursor.moveToPosition(startPos); 375 } 376 stream.println("<<<<<"); 377 } 378 379 /** 380 * Prints the contents of a Cursor to a StringBuilder. The position 381 * is restored after printing. 382 * 383 * @param cursor the cursor to print 384 * @param sb the StringBuilder to print to 385 */ 386 public static void dumpCursor(Cursor cursor, StringBuilder sb) { 387 sb.append(">>>>> Dumping cursor " + cursor + "\n"); 388 if (cursor != null) { 389 int startPos = cursor.getPosition(); 390 391 cursor.moveToPosition(-1); 392 while (cursor.moveToNext()) { 393 dumpCurrentRow(cursor, sb); 394 } 395 cursor.moveToPosition(startPos); 396 } 397 sb.append("<<<<<\n"); 398 } 399 400 /** 401 * Prints the contents of a Cursor to a String. The position is restored 402 * after printing. 403 * 404 * @param cursor the cursor to print 405 * @return a String that contains the dumped cursor 406 */ 407 public static String dumpCursorToString(Cursor cursor) { 408 StringBuilder sb = new StringBuilder(); 409 dumpCursor(cursor, sb); 410 return sb.toString(); 411 } 412 413 /** 414 * Prints the contents of a Cursor's current row to System.out. 415 * 416 * @param cursor the cursor to print from 417 */ 418 public static void dumpCurrentRow(Cursor cursor) { 419 dumpCurrentRow(cursor, System.out); 420 } 421 422 /** 423 * Prints the contents of a Cursor's current row to a PrintSteam. 424 * 425 * @param cursor the cursor to print 426 * @param stream the stream to print to 427 */ 428 public static void dumpCurrentRow(Cursor cursor, PrintStream stream) { 429 String[] cols = cursor.getColumnNames(); 430 stream.println("" + cursor.getPosition() + " {"); 431 int length = cols.length; 432 for (int i = 0; i< length; i++) { 433 String value; 434 try { 435 value = cursor.getString(i); 436 } catch (SQLiteException e) { 437 // assume that if the getString threw this exception then the column is not 438 // representable by a string, e.g. it is a BLOB. 439 value = "<unprintable>"; 440 } 441 stream.println(" " + cols[i] + '=' + value); 442 } 443 stream.println("}"); 444 } 445 446 /** 447 * Prints the contents of a Cursor's current row to a StringBuilder. 448 * 449 * @param cursor the cursor to print 450 * @param sb the StringBuilder to print to 451 */ 452 public static void dumpCurrentRow(Cursor cursor, StringBuilder sb) { 453 String[] cols = cursor.getColumnNames(); 454 sb.append("" + cursor.getPosition() + " {\n"); 455 int length = cols.length; 456 for (int i = 0; i < length; i++) { 457 String value; 458 try { 459 value = cursor.getString(i); 460 } catch (SQLiteException e) { 461 // assume that if the getString threw this exception then the column is not 462 // representable by a string, e.g. it is a BLOB. 463 value = "<unprintable>"; 464 } 465 sb.append(" " + cols[i] + '=' + value + "\n"); 466 } 467 sb.append("}\n"); 468 } 469 470 /** 471 * Dump the contents of a Cursor's current row to a String. 472 * 473 * @param cursor the cursor to print 474 * @return a String that contains the dumped cursor row 475 */ 476 public static String dumpCurrentRowToString(Cursor cursor) { 477 StringBuilder sb = new StringBuilder(); 478 dumpCurrentRow(cursor, sb); 479 return sb.toString(); 480 } 481 482 /** 483 * Reads a String out of a field in a Cursor and writes it to a Map. 484 * 485 * @param cursor The cursor to read from 486 * @param field The TEXT field to read 487 * @param values The {@link ContentValues} to put the value into, with the field as the key 488 */ 489 public static void cursorStringToContentValues(Cursor cursor, String field, 490 ContentValues values) { 491 cursorStringToContentValues(cursor, field, values, field); 492 } 493 494 /** 495 * Reads a String out of a field in a Cursor and writes it to an InsertHelper. 496 * 497 * @param cursor The cursor to read from 498 * @param field The TEXT field to read 499 * @param inserter The InsertHelper to bind into 500 * @param index the index of the bind entry in the InsertHelper 501 */ 502 public static void cursorStringToInsertHelper(Cursor cursor, String field, 503 InsertHelper inserter, int index) { 504 inserter.bind(index, cursor.getString(cursor.getColumnIndexOrThrow(field))); 505 } 506 507 /** 508 * Reads a String out of a field in a Cursor and writes it to a Map. 509 * 510 * @param cursor The cursor to read from 511 * @param field The TEXT field to read 512 * @param values The {@link ContentValues} to put the value into, with the field as the key 513 * @param key The key to store the value with in the map 514 */ 515 public static void cursorStringToContentValues(Cursor cursor, String field, 516 ContentValues values, String key) { 517 values.put(key, cursor.getString(cursor.getColumnIndexOrThrow(field))); 518 } 519 520 /** 521 * Reads an Integer out of a field in a Cursor and writes it to a Map. 522 * 523 * @param cursor The cursor to read from 524 * @param field The INTEGER field to read 525 * @param values The {@link ContentValues} to put the value into, with the field as the key 526 */ 527 public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values) { 528 cursorIntToContentValues(cursor, field, values, field); 529 } 530 531 /** 532 * Reads a Integer out of a field in a Cursor and writes it to a Map. 533 * 534 * @param cursor The cursor to read from 535 * @param field The INTEGER field to read 536 * @param values The {@link ContentValues} to put the value into, with the field as the key 537 * @param key The key to store the value with in the map 538 */ 539 public static void cursorIntToContentValues(Cursor cursor, String field, ContentValues values, 540 String key) { 541 int colIndex = cursor.getColumnIndex(field); 542 if (!cursor.isNull(colIndex)) { 543 values.put(key, cursor.getInt(colIndex)); 544 } else { 545 values.put(key, (Integer) null); 546 } 547 } 548 549 /** 550 * Reads a Long out of a field in a Cursor and writes it to a Map. 551 * 552 * @param cursor The cursor to read from 553 * @param field The INTEGER field to read 554 * @param values The {@link ContentValues} to put the value into, with the field as the key 555 */ 556 public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values) 557 { 558 cursorLongToContentValues(cursor, field, values, field); 559 } 560 561 /** 562 * Reads a Long out of a field in a Cursor and writes it to a Map. 563 * 564 * @param cursor The cursor to read from 565 * @param field The INTEGER field to read 566 * @param values The {@link ContentValues} to put the value into 567 * @param key The key to store the value with in the map 568 */ 569 public static void cursorLongToContentValues(Cursor cursor, String field, ContentValues values, 570 String key) { 571 int colIndex = cursor.getColumnIndex(field); 572 if (!cursor.isNull(colIndex)) { 573 Long value = Long.valueOf(cursor.getLong(colIndex)); 574 values.put(key, value); 575 } else { 576 values.put(key, (Long) null); 577 } 578 } 579 580 /** 581 * Reads a Double out of a field in a Cursor and writes it to a Map. 582 * 583 * @param cursor The cursor to read from 584 * @param field The REAL field to read 585 * @param values The {@link ContentValues} to put the value into 586 */ 587 public static void cursorDoubleToCursorValues(Cursor cursor, String field, ContentValues values) 588 { 589 cursorDoubleToContentValues(cursor, field, values, field); 590 } 591 592 /** 593 * Reads a Double out of a field in a Cursor and writes it to a Map. 594 * 595 * @param cursor The cursor to read from 596 * @param field The REAL field to read 597 * @param values The {@link ContentValues} to put the value into 598 * @param key The key to store the value with in the map 599 */ 600 public static void cursorDoubleToContentValues(Cursor cursor, String field, 601 ContentValues values, String key) { 602 int colIndex = cursor.getColumnIndex(field); 603 if (!cursor.isNull(colIndex)) { 604 values.put(key, cursor.getDouble(colIndex)); 605 } else { 606 values.put(key, (Double) null); 607 } 608 } 609 610 /** 611 * Read the entire contents of a cursor row and store them in a ContentValues. 612 * 613 * @param cursor the cursor to read from. 614 * @param values the {@link ContentValues} to put the row into. 615 */ 616 public static void cursorRowToContentValues(Cursor cursor, ContentValues values) { 617 AbstractWindowedCursor awc = 618 (cursor instanceof AbstractWindowedCursor) ? (AbstractWindowedCursor) cursor : null; 619 620 String[] columns = cursor.getColumnNames(); 621 int length = columns.length; 622 for (int i = 0; i < length; i++) { 623 if (awc != null && awc.isBlob(i)) { 624 values.put(columns[i], cursor.getBlob(i)); 625 } else { 626 values.put(columns[i], cursor.getString(i)); 627 } 628 } 629 } 630 631 /** 632 * Query the table for the number of rows in the table. 633 * @param db the database the table is in 634 * @param table the name of the table to query 635 * @return the number of rows in the table 636 */ 637 public static long queryNumEntries(SQLiteDatabase db, String table) { 638 Cursor cursor = db.query(table, countProjection, 639 null, null, null, null, null); 640 try { 641 cursor.moveToFirst(); 642 return cursor.getLong(0); 643 } finally { 644 cursor.close(); 645 } 646 } 647 648 /** 649 * Utility method to run the query on the db and return the value in the 650 * first column of the first row. 651 */ 652 public static long longForQuery(SQLiteDatabase db, String query, String[] selectionArgs) { 653 SQLiteStatement prog = db.compileStatement(query); 654 try { 655 return longForQuery(prog, selectionArgs); 656 } finally { 657 prog.close(); 658 } 659 } 660 661 /** 662 * Utility method to run the pre-compiled query and return the value in the 663 * first column of the first row. 664 */ 665 public static long longForQuery(SQLiteStatement prog, String[] selectionArgs) { 666 if (selectionArgs != null) { 667 int size = selectionArgs.length; 668 for (int i = 0; i < size; i++) { 669 bindObjectToProgram(prog, i + 1, selectionArgs[i]); 670 } 671 } 672 long value = prog.simpleQueryForLong(); 673 return value; 674 } 675 676 /** 677 * Utility method to run the query on the db and return the value in the 678 * first column of the first row. 679 */ 680 public static String stringForQuery(SQLiteDatabase db, String query, String[] selectionArgs) { 681 SQLiteStatement prog = db.compileStatement(query); 682 try { 683 return stringForQuery(prog, selectionArgs); 684 } finally { 685 prog.close(); 686 } 687 } 688 689 /** 690 * Utility method to run the pre-compiled query and return the value in the 691 * first column of the first row. 692 */ 693 public static String stringForQuery(SQLiteStatement prog, String[] selectionArgs) { 694 if (selectionArgs != null) { 695 int size = selectionArgs.length; 696 for (int i = 0; i < size; i++) { 697 bindObjectToProgram(prog, i + 1, selectionArgs[i]); 698 } 699 } 700 String value = prog.simpleQueryForString(); 701 return value; 702 } 703 704 /** 705 * Reads a String out of a column in a Cursor and writes it to a ContentValues. 706 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 707 * 708 * @param cursor The cursor to read from 709 * @param column The column to read 710 * @param values The {@link ContentValues} to put the value into 711 */ 712 public static void cursorStringToContentValuesIfPresent(Cursor cursor, ContentValues values, 713 String column) { 714 final int index = cursor.getColumnIndexOrThrow(column); 715 if (!cursor.isNull(index)) { 716 values.put(column, cursor.getString(index)); 717 } 718 } 719 720 /** 721 * Reads a Long out of a column in a Cursor and writes it to a ContentValues. 722 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 723 * 724 * @param cursor The cursor to read from 725 * @param column The column to read 726 * @param values The {@link ContentValues} to put the value into 727 */ 728 public static void cursorLongToContentValuesIfPresent(Cursor cursor, ContentValues values, 729 String column) { 730 final int index = cursor.getColumnIndexOrThrow(column); 731 if (!cursor.isNull(index)) { 732 values.put(column, cursor.getLong(index)); 733 } 734 } 735 736 /** 737 * Reads a Short out of a column in a Cursor and writes it to a ContentValues. 738 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 739 * 740 * @param cursor The cursor to read from 741 * @param column The column to read 742 * @param values The {@link ContentValues} to put the value into 743 */ 744 public static void cursorShortToContentValuesIfPresent(Cursor cursor, ContentValues values, 745 String column) { 746 final int index = cursor.getColumnIndexOrThrow(column); 747 if (!cursor.isNull(index)) { 748 values.put(column, cursor.getShort(index)); 749 } 750 } 751 752 /** 753 * Reads a Integer out of a column in a Cursor and writes it to a ContentValues. 754 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 755 * 756 * @param cursor The cursor to read from 757 * @param column The column to read 758 * @param values The {@link ContentValues} to put the value into 759 */ 760 public static void cursorIntToContentValuesIfPresent(Cursor cursor, ContentValues values, 761 String column) { 762 final int index = cursor.getColumnIndexOrThrow(column); 763 if (!cursor.isNull(index)) { 764 values.put(column, cursor.getInt(index)); 765 } 766 } 767 768 /** 769 * Reads a Float out of a column in a Cursor and writes it to a ContentValues. 770 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 771 * 772 * @param cursor The cursor to read from 773 * @param column The column to read 774 * @param values The {@link ContentValues} to put the value into 775 */ 776 public static void cursorFloatToContentValuesIfPresent(Cursor cursor, ContentValues values, 777 String column) { 778 final int index = cursor.getColumnIndexOrThrow(column); 779 if (!cursor.isNull(index)) { 780 values.put(column, cursor.getFloat(index)); 781 } 782 } 783 784 /** 785 * Reads a Double out of a column in a Cursor and writes it to a ContentValues. 786 * Adds nothing to the ContentValues if the column isn't present or if its value is null. 787 * 788 * @param cursor The cursor to read from 789 * @param column The column to read 790 * @param values The {@link ContentValues} to put the value into 791 */ 792 public static void cursorDoubleToContentValuesIfPresent(Cursor cursor, ContentValues values, 793 String column) { 794 final int index = cursor.getColumnIndexOrThrow(column); 795 if (!cursor.isNull(index)) { 796 values.put(column, cursor.getDouble(index)); 797 } 798 } 799 800 /** 801 * This class allows users to do multiple inserts into a table but 802 * compile the SQL insert statement only once, which may increase 803 * performance. 804 */ 805 public static class InsertHelper { 806 private final SQLiteDatabase mDb; 807 private final String mTableName; 808 private HashMap<String, Integer> mColumns; 809 private String mInsertSQL = null; 810 private SQLiteStatement mInsertStatement = null; 811 private SQLiteStatement mReplaceStatement = null; 812 private SQLiteStatement mPreparedStatement = null; 813 814 /** 815 * {@hide} 816 * 817 * These are the columns returned by sqlite's "PRAGMA 818 * table_info(...)" command that we depend on. 819 */ 820 public static final int TABLE_INFO_PRAGMA_COLUMNNAME_INDEX = 1; 821 public static final int TABLE_INFO_PRAGMA_DEFAULT_INDEX = 4; 822 823 /** 824 * @param db the SQLiteDatabase to insert into 825 * @param tableName the name of the table to insert into 826 */ 827 public InsertHelper(SQLiteDatabase db, String tableName) { 828 mDb = db; 829 mTableName = tableName; 830 } 831 832 private void buildSQL() throws SQLException { 833 StringBuilder sb = new StringBuilder(128); 834 sb.append("INSERT INTO "); 835 sb.append(mTableName); 836 sb.append(" ("); 837 838 StringBuilder sbv = new StringBuilder(128); 839 sbv.append("VALUES ("); 840 841 int i = 1; 842 Cursor cur = null; 843 try { 844 cur = mDb.rawQuery("PRAGMA table_info(" + mTableName + ")", null); 845 mColumns = new HashMap<String, Integer>(cur.getCount()); 846 while (cur.moveToNext()) { 847 String columnName = cur.getString(TABLE_INFO_PRAGMA_COLUMNNAME_INDEX); 848 String defaultValue = cur.getString(TABLE_INFO_PRAGMA_DEFAULT_INDEX); 849 850 mColumns.put(columnName, i); 851 sb.append("'"); 852 sb.append(columnName); 853 sb.append("'"); 854 855 if (defaultValue == null) { 856 sbv.append("?"); 857 } else { 858 sbv.append("COALESCE(?, "); 859 sbv.append(defaultValue); 860 sbv.append(")"); 861 } 862 863 sb.append(i == cur.getCount() ? ") " : ", "); 864 sbv.append(i == cur.getCount() ? ");" : ", "); 865 ++i; 866 } 867 } finally { 868 if (cur != null) cur.close(); 869 } 870 871 sb.append(sbv); 872 873 mInsertSQL = sb.toString(); 874 if (LOCAL_LOGV) Log.v(TAG, "insert statement is " + mInsertSQL); 875 } 876 877 private SQLiteStatement getStatement(boolean allowReplace) throws SQLException { 878 if (allowReplace) { 879 if (mReplaceStatement == null) { 880 if (mInsertSQL == null) buildSQL(); 881 // chop "INSERT" off the front and prepend "INSERT OR REPLACE" instead. 882 String replaceSQL = "INSERT OR REPLACE" + mInsertSQL.substring(6); 883 mReplaceStatement = mDb.compileStatement(replaceSQL); 884 } 885 return mReplaceStatement; 886 } else { 887 if (mInsertStatement == null) { 888 if (mInsertSQL == null) buildSQL(); 889 mInsertStatement = mDb.compileStatement(mInsertSQL); 890 } 891 return mInsertStatement; 892 } 893 } 894 895 /** 896 * Performs an insert, adding a new row with the given values. 897 * 898 * @param values the set of values with which to populate the 899 * new row 900 * @param allowReplace if true, the statement does "INSERT OR 901 * REPLACE" instead of "INSERT", silently deleting any 902 * previously existing rows that would cause a conflict 903 * 904 * @return the row ID of the newly inserted row, or -1 if an 905 * error occurred 906 */ 907 private synchronized long insertInternal(ContentValues values, boolean allowReplace) { 908 try { 909 SQLiteStatement stmt = getStatement(allowReplace); 910 stmt.clearBindings(); 911 if (LOCAL_LOGV) Log.v(TAG, "--- inserting in table " + mTableName); 912 for (Map.Entry<String, Object> e: values.valueSet()) { 913 final String key = e.getKey(); 914 int i = getColumnIndex(key); 915 DatabaseUtils.bindObjectToProgram(stmt, i, e.getValue()); 916 if (LOCAL_LOGV) { 917 Log.v(TAG, "binding " + e.getValue() + " to column " + 918 i + " (" + key + ")"); 919 } 920 } 921 return stmt.executeInsert(); 922 } catch (SQLException e) { 923 Log.e(TAG, "Error inserting " + values + " into table " + mTableName, e); 924 return -1; 925 } 926 } 927 928 /** 929 * Returns the index of the specified column. This is index is suitagble for use 930 * in calls to bind(). 931 * @param key the column name 932 * @return the index of the column 933 */ 934 public int getColumnIndex(String key) { 935 getStatement(false); 936 final Integer index = mColumns.get(key); 937 if (index == null) { 938 throw new IllegalArgumentException("column '" + key + "' is invalid"); 939 } 940 return index; 941 } 942 943 /** 944 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 945 * without a matching execute() must have already have been called. 946 * @param index the index of the slot to which to bind 947 * @param value the value to bind 948 */ 949 public void bind(int index, double value) { 950 mPreparedStatement.bindDouble(index, value); 951 } 952 953 /** 954 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 955 * without a matching execute() must have already have been called. 956 * @param index the index of the slot to which to bind 957 * @param value the value to bind 958 */ 959 public void bind(int index, float value) { 960 mPreparedStatement.bindDouble(index, value); 961 } 962 963 /** 964 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 965 * without a matching execute() must have already have been called. 966 * @param index the index of the slot to which to bind 967 * @param value the value to bind 968 */ 969 public void bind(int index, long value) { 970 mPreparedStatement.bindLong(index, value); 971 } 972 973 /** 974 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 975 * without a matching execute() must have already have been called. 976 * @param index the index of the slot to which to bind 977 * @param value the value to bind 978 */ 979 public void bind(int index, int value) { 980 mPreparedStatement.bindLong(index, value); 981 } 982 983 /** 984 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 985 * without a matching execute() must have already have been called. 986 * @param index the index of the slot to which to bind 987 * @param value the value to bind 988 */ 989 public void bind(int index, boolean value) { 990 mPreparedStatement.bindLong(index, value ? 1 : 0); 991 } 992 993 /** 994 * Bind null to an index. A prepareForInsert() or prepareForReplace() 995 * without a matching execute() must have already have been called. 996 * @param index the index of the slot to which to bind 997 */ 998 public void bindNull(int index) { 999 mPreparedStatement.bindNull(index); 1000 } 1001 1002 /** 1003 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1004 * without a matching execute() must have already have been called. 1005 * @param index the index of the slot to which to bind 1006 * @param value the value to bind 1007 */ 1008 public void bind(int index, byte[] value) { 1009 if (value == null) { 1010 mPreparedStatement.bindNull(index); 1011 } else { 1012 mPreparedStatement.bindBlob(index, value); 1013 } 1014 } 1015 1016 /** 1017 * Bind the value to an index. A prepareForInsert() or prepareForReplace() 1018 * without a matching execute() must have already have been called. 1019 * @param index the index of the slot to which to bind 1020 * @param value the value to bind 1021 */ 1022 public void bind(int index, String value) { 1023 if (value == null) { 1024 mPreparedStatement.bindNull(index); 1025 } else { 1026 mPreparedStatement.bindString(index, value); 1027 } 1028 } 1029 1030 /** 1031 * Performs an insert, adding a new row with the given values. 1032 * If the table contains conflicting rows, an error is 1033 * returned. 1034 * 1035 * @param values the set of values with which to populate the 1036 * new row 1037 * 1038 * @return the row ID of the newly inserted row, or -1 if an 1039 * error occurred 1040 */ 1041 public long insert(ContentValues values) { 1042 return insertInternal(values, false); 1043 } 1044 1045 /** 1046 * Execute the previously prepared insert or replace using the bound values 1047 * since the last call to prepareForInsert or prepareForReplace. 1048 * 1049 * <p>Note that calling bind() and then execute() is not thread-safe. The only thread-safe 1050 * way to use this class is to call insert() or replace(). 1051 * 1052 * @return the row ID of the newly inserted row, or -1 if an 1053 * error occurred 1054 */ 1055 public long execute() { 1056 if (mPreparedStatement == null) { 1057 throw new IllegalStateException("you must prepare this inserter before calling " 1058 + "execute"); 1059 } 1060 try { 1061 if (LOCAL_LOGV) Log.v(TAG, "--- doing insert or replace in table " + mTableName); 1062 return mPreparedStatement.executeInsert(); 1063 } catch (SQLException e) { 1064 Log.e(TAG, "Error executing InsertHelper with table " + mTableName, e); 1065 return -1; 1066 } finally { 1067 // you can only call this once per prepare 1068 mPreparedStatement = null; 1069 } 1070 } 1071 1072 /** 1073 * Prepare the InsertHelper for an insert. The pattern for this is: 1074 * <ul> 1075 * <li>prepareForInsert() 1076 * <li>bind(index, value); 1077 * <li>bind(index, value); 1078 * <li>... 1079 * <li>bind(index, value); 1080 * <li>execute(); 1081 * </ul> 1082 */ 1083 public void prepareForInsert() { 1084 mPreparedStatement = getStatement(false); 1085 mPreparedStatement.clearBindings(); 1086 } 1087 1088 /** 1089 * Prepare the InsertHelper for a replace. The pattern for this is: 1090 * <ul> 1091 * <li>prepareForReplace() 1092 * <li>bind(index, value); 1093 * <li>bind(index, value); 1094 * <li>... 1095 * <li>bind(index, value); 1096 * <li>execute(); 1097 * </ul> 1098 */ 1099 public void prepareForReplace() { 1100 mPreparedStatement = getStatement(true); 1101 mPreparedStatement.clearBindings(); 1102 } 1103 1104 /** 1105 * Performs an insert, adding a new row with the given values. 1106 * If the table contains conflicting rows, they are deleted 1107 * and replaced with the new row. 1108 * 1109 * @param values the set of values with which to populate the 1110 * new row 1111 * 1112 * @return the row ID of the newly inserted row, or -1 if an 1113 * error occurred 1114 */ 1115 public long replace(ContentValues values) { 1116 return insertInternal(values, true); 1117 } 1118 1119 /** 1120 * Close this object and release any resources associated with 1121 * it. The behavior of calling <code>insert()</code> after 1122 * calling this method is undefined. 1123 */ 1124 public void close() { 1125 if (mInsertStatement != null) { 1126 mInsertStatement.close(); 1127 mInsertStatement = null; 1128 } 1129 if (mReplaceStatement != null) { 1130 mReplaceStatement.close(); 1131 mReplaceStatement = null; 1132 } 1133 mInsertSQL = null; 1134 mColumns = null; 1135 } 1136 } 1137 1138 /** 1139 * Creates a db and populates it with the sql statements in sqlStatements. 1140 * 1141 * @param context the context to use to create the db 1142 * @param dbName the name of the db to create 1143 * @param dbVersion the version to set on the db 1144 * @param sqlStatements the statements to use to populate the db. This should be a single string 1145 * of the form returned by sqlite3's <tt>.dump</tt> command (statements separated by 1146 * semicolons) 1147 */ 1148 static public void createDbFromSqlStatements( 1149 Context context, String dbName, int dbVersion, String sqlStatements) { 1150 SQLiteDatabase db = context.openOrCreateDatabase(dbName, 0, null); 1151 // TODO: this is not quite safe since it assumes that all semicolons at the end of a line 1152 // terminate statements. It is possible that a text field contains ;\n. We will have to fix 1153 // this if that turns out to be a problem. 1154 String[] statements = TextUtils.split(sqlStatements, ";\n"); 1155 for (String statement : statements) { 1156 if (TextUtils.isEmpty(statement)) continue; 1157 db.execSQL(statement); 1158 } 1159 db.setVersion(dbVersion); 1160 db.close(); 1161 } 1162} 1163