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