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