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