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