Owen Lin | a2fba68 | 2011-08-17 22:07:43 +0800 | [diff] [blame] | 1 | /* |
| 2 | * Copyright (C) 2009 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 com.android.gallery3d.common; |
| 18 | |
| 19 | import android.content.ContentValues; |
| 20 | import android.database.Cursor; |
| 21 | import android.database.sqlite.SQLiteDatabase; |
| 22 | import android.text.TextUtils; |
| 23 | |
| 24 | import java.lang.reflect.AnnotatedElement; |
| 25 | import java.lang.reflect.Field; |
| 26 | import java.util.ArrayList; |
| 27 | |
| 28 | public final class EntrySchema { |
| 29 | @SuppressWarnings("unused") |
| 30 | private static final String TAG = "EntrySchema"; |
| 31 | |
| 32 | private static final int TYPE_STRING = 0; |
| 33 | private static final int TYPE_BOOLEAN = 1; |
| 34 | private static final int TYPE_SHORT = 2; |
| 35 | private static final int TYPE_INT = 3; |
| 36 | private static final int TYPE_LONG = 4; |
| 37 | private static final int TYPE_FLOAT = 5; |
| 38 | private static final int TYPE_DOUBLE = 6; |
| 39 | private static final int TYPE_BLOB = 7; |
| 40 | private static final String SQLITE_TYPES[] = { |
| 41 | "TEXT", "INTEGER", "INTEGER", "INTEGER", "INTEGER", "REAL", "REAL", "NONE" }; |
| 42 | |
| 43 | private static final String FULL_TEXT_INDEX_SUFFIX = "_fulltext"; |
| 44 | |
| 45 | private final String mTableName; |
| 46 | private final ColumnInfo[] mColumnInfo; |
| 47 | private final String[] mProjection; |
| 48 | private final boolean mHasFullTextIndex; |
| 49 | |
| 50 | public EntrySchema(Class<? extends Entry> clazz) { |
| 51 | // Get table and column metadata from reflection. |
| 52 | ColumnInfo[] columns = parseColumnInfo(clazz); |
| 53 | mTableName = parseTableName(clazz); |
| 54 | mColumnInfo = columns; |
| 55 | |
| 56 | // Cache the list of projection columns and check for full-text columns. |
| 57 | String[] projection = {}; |
| 58 | boolean hasFullTextIndex = false; |
| 59 | if (columns != null) { |
| 60 | projection = new String[columns.length]; |
| 61 | for (int i = 0; i != columns.length; ++i) { |
| 62 | ColumnInfo column = columns[i]; |
| 63 | projection[i] = column.name; |
| 64 | if (column.fullText) { |
| 65 | hasFullTextIndex = true; |
| 66 | } |
| 67 | } |
| 68 | } |
| 69 | mProjection = projection; |
| 70 | mHasFullTextIndex = hasFullTextIndex; |
| 71 | } |
| 72 | |
| 73 | public String getTableName() { |
| 74 | return mTableName; |
| 75 | } |
| 76 | |
| 77 | public ColumnInfo[] getColumnInfo() { |
| 78 | return mColumnInfo; |
| 79 | } |
| 80 | |
| 81 | public String[] getProjection() { |
| 82 | return mProjection; |
| 83 | } |
| 84 | |
| 85 | public int getColumnIndex(String columnName) { |
| 86 | for (ColumnInfo column : mColumnInfo) { |
| 87 | if (column.name.equals(columnName)) { |
| 88 | return column.projectionIndex; |
| 89 | } |
| 90 | } |
| 91 | return -1; |
| 92 | } |
| 93 | |
| 94 | private ColumnInfo getColumn(String columnName) { |
| 95 | int index = getColumnIndex(columnName); |
| 96 | return (index < 0) ? null : mColumnInfo[index]; |
| 97 | } |
| 98 | |
| 99 | private void logExecSql(SQLiteDatabase db, String sql) { |
| 100 | db.execSQL(sql); |
| 101 | } |
| 102 | |
| 103 | public <T extends Entry> T cursorToObject(Cursor cursor, T object) { |
| 104 | try { |
| 105 | for (ColumnInfo column : mColumnInfo) { |
| 106 | int columnIndex = column.projectionIndex; |
| 107 | Field field = column.field; |
| 108 | switch (column.type) { |
| 109 | case TYPE_STRING: |
| 110 | field.set(object, cursor.isNull(columnIndex) |
| 111 | ? null |
| 112 | : cursor.getString(columnIndex)); |
| 113 | break; |
| 114 | case TYPE_BOOLEAN: |
| 115 | field.setBoolean(object, cursor.getShort(columnIndex) == 1); |
| 116 | break; |
| 117 | case TYPE_SHORT: |
| 118 | field.setShort(object, cursor.getShort(columnIndex)); |
| 119 | break; |
| 120 | case TYPE_INT: |
| 121 | field.setInt(object, cursor.getInt(columnIndex)); |
| 122 | break; |
| 123 | case TYPE_LONG: |
| 124 | field.setLong(object, cursor.getLong(columnIndex)); |
| 125 | break; |
| 126 | case TYPE_FLOAT: |
| 127 | field.setFloat(object, cursor.getFloat(columnIndex)); |
| 128 | break; |
| 129 | case TYPE_DOUBLE: |
| 130 | field.setDouble(object, cursor.getDouble(columnIndex)); |
| 131 | break; |
| 132 | case TYPE_BLOB: |
| 133 | field.set(object, cursor.isNull(columnIndex) |
| 134 | ? null |
| 135 | : cursor.getBlob(columnIndex)); |
| 136 | break; |
| 137 | } |
| 138 | } |
| 139 | return object; |
| 140 | } catch (IllegalAccessException e) { |
| 141 | throw new RuntimeException(e); |
| 142 | } |
| 143 | } |
| 144 | |
| 145 | private void setIfNotNull(Field field, Object object, Object value) |
| 146 | throws IllegalAccessException { |
| 147 | if (value != null) field.set(object, value); |
| 148 | } |
| 149 | |
| 150 | /** |
| 151 | * Converts the ContentValues to the object. The ContentValues may not |
| 152 | * contain values for all the fields in the object. |
| 153 | */ |
| 154 | public <T extends Entry> T valuesToObject(ContentValues values, T object) { |
| 155 | try { |
| 156 | for (ColumnInfo column : mColumnInfo) { |
| 157 | String columnName = column.name; |
| 158 | Field field = column.field; |
| 159 | switch (column.type) { |
| 160 | case TYPE_STRING: |
| 161 | setIfNotNull(field, object, values.getAsString(columnName)); |
| 162 | break; |
| 163 | case TYPE_BOOLEAN: |
| 164 | setIfNotNull(field, object, values.getAsBoolean(columnName)); |
| 165 | break; |
| 166 | case TYPE_SHORT: |
| 167 | setIfNotNull(field, object, values.getAsShort(columnName)); |
| 168 | break; |
| 169 | case TYPE_INT: |
| 170 | setIfNotNull(field, object, values.getAsInteger(columnName)); |
| 171 | break; |
| 172 | case TYPE_LONG: |
| 173 | setIfNotNull(field, object, values.getAsLong(columnName)); |
| 174 | break; |
| 175 | case TYPE_FLOAT: |
| 176 | setIfNotNull(field, object, values.getAsFloat(columnName)); |
| 177 | break; |
| 178 | case TYPE_DOUBLE: |
| 179 | setIfNotNull(field, object, values.getAsDouble(columnName)); |
| 180 | break; |
| 181 | case TYPE_BLOB: |
| 182 | setIfNotNull(field, object, values.getAsByteArray(columnName)); |
| 183 | break; |
| 184 | } |
| 185 | } |
| 186 | return object; |
| 187 | } catch (IllegalAccessException e) { |
| 188 | throw new RuntimeException(e); |
| 189 | } |
| 190 | } |
| 191 | |
| 192 | public void objectToValues(Entry object, ContentValues values) { |
| 193 | try { |
| 194 | for (ColumnInfo column : mColumnInfo) { |
| 195 | String columnName = column.name; |
| 196 | Field field = column.field; |
| 197 | switch (column.type) { |
| 198 | case TYPE_STRING: |
| 199 | values.put(columnName, (String) field.get(object)); |
| 200 | break; |
| 201 | case TYPE_BOOLEAN: |
| 202 | values.put(columnName, field.getBoolean(object)); |
| 203 | break; |
| 204 | case TYPE_SHORT: |
| 205 | values.put(columnName, field.getShort(object)); |
| 206 | break; |
| 207 | case TYPE_INT: |
| 208 | values.put(columnName, field.getInt(object)); |
| 209 | break; |
| 210 | case TYPE_LONG: |
| 211 | values.put(columnName, field.getLong(object)); |
| 212 | break; |
| 213 | case TYPE_FLOAT: |
| 214 | values.put(columnName, field.getFloat(object)); |
| 215 | break; |
| 216 | case TYPE_DOUBLE: |
| 217 | values.put(columnName, field.getDouble(object)); |
| 218 | break; |
| 219 | case TYPE_BLOB: |
| 220 | values.put(columnName, (byte[]) field.get(object)); |
| 221 | break; |
| 222 | } |
| 223 | } |
| 224 | } catch (IllegalAccessException e) { |
| 225 | throw new RuntimeException(e); |
| 226 | } |
| 227 | } |
| 228 | |
| 229 | public String toDebugString(Entry entry) { |
| 230 | try { |
| 231 | StringBuilder sb = new StringBuilder(); |
| 232 | sb.append("ID=").append(entry.id); |
| 233 | for (ColumnInfo column : mColumnInfo) { |
| 234 | String columnName = column.name; |
| 235 | Field field = column.field; |
| 236 | Object value = field.get(entry); |
| 237 | sb.append(" ").append(columnName).append("=") |
| 238 | .append((value == null) ? "null" : value.toString()); |
| 239 | } |
| 240 | return sb.toString(); |
| 241 | } catch (IllegalAccessException e) { |
| 242 | throw new RuntimeException(e); |
| 243 | } |
| 244 | } |
| 245 | |
| 246 | public String toDebugString(Entry entry, String... columnNames) { |
| 247 | try { |
| 248 | StringBuilder sb = new StringBuilder(); |
| 249 | sb.append("ID=").append(entry.id); |
| 250 | for (String columnName : columnNames) { |
| 251 | ColumnInfo column = getColumn(columnName); |
| 252 | Field field = column.field; |
| 253 | Object value = field.get(entry); |
| 254 | sb.append(" ").append(columnName).append("=") |
| 255 | .append((value == null) ? "null" : value.toString()); |
| 256 | } |
| 257 | return sb.toString(); |
| 258 | } catch (IllegalAccessException e) { |
| 259 | throw new RuntimeException(e); |
| 260 | } |
| 261 | } |
| 262 | |
| 263 | public Cursor queryAll(SQLiteDatabase db) { |
| 264 | return db.query(mTableName, mProjection, null, null, null, null, null); |
| 265 | } |
| 266 | |
| 267 | public boolean queryWithId(SQLiteDatabase db, long id, Entry entry) { |
| 268 | Cursor cursor = db.query(mTableName, mProjection, "_id=?", |
| 269 | new String[] {Long.toString(id)}, null, null, null); |
| 270 | boolean success = false; |
| 271 | if (cursor.moveToFirst()) { |
| 272 | cursorToObject(cursor, entry); |
| 273 | success = true; |
| 274 | } |
| 275 | cursor.close(); |
| 276 | return success; |
| 277 | } |
| 278 | |
| 279 | public long insertOrReplace(SQLiteDatabase db, Entry entry) { |
| 280 | ContentValues values = new ContentValues(); |
| 281 | objectToValues(entry, values); |
| 282 | if (entry.id == 0) { |
| 283 | values.remove("_id"); |
| 284 | } |
| 285 | long id = db.replace(mTableName, "_id", values); |
| 286 | entry.id = id; |
| 287 | return id; |
| 288 | } |
| 289 | |
| 290 | public boolean deleteWithId(SQLiteDatabase db, long id) { |
| 291 | return db.delete(mTableName, "_id=?", new String[] { Long.toString(id) }) == 1; |
| 292 | } |
| 293 | |
| 294 | public void createTables(SQLiteDatabase db) { |
| 295 | // Wrapped class must have a @Table.Definition. |
| 296 | String tableName = mTableName; |
| 297 | Utils.assertTrue(tableName != null); |
| 298 | |
| 299 | // Add the CREATE TABLE statement for the main table. |
| 300 | StringBuilder sql = new StringBuilder("CREATE TABLE "); |
| 301 | sql.append(tableName); |
| 302 | sql.append(" (_id INTEGER PRIMARY KEY AUTOINCREMENT"); |
| 303 | for (ColumnInfo column : mColumnInfo) { |
| 304 | if (!column.isId()) { |
| 305 | sql.append(','); |
| 306 | sql.append(column.name); |
| 307 | sql.append(' '); |
| 308 | sql.append(SQLITE_TYPES[column.type]); |
| 309 | if (!TextUtils.isEmpty(column.defaultValue)) { |
| 310 | sql.append(" DEFAULT "); |
| 311 | sql.append(column.defaultValue); |
| 312 | } |
| 313 | } |
| 314 | } |
| 315 | sql.append(");"); |
| 316 | logExecSql(db, sql.toString()); |
| 317 | sql.setLength(0); |
| 318 | |
| 319 | // Create indexes for all indexed columns. |
| 320 | for (ColumnInfo column : mColumnInfo) { |
| 321 | // Create an index on the indexed columns. |
| 322 | if (column.indexed) { |
| 323 | sql.append("CREATE INDEX "); |
| 324 | sql.append(tableName); |
| 325 | sql.append("_index_"); |
| 326 | sql.append(column.name); |
| 327 | sql.append(" ON "); |
| 328 | sql.append(tableName); |
| 329 | sql.append(" ("); |
| 330 | sql.append(column.name); |
| 331 | sql.append(");"); |
| 332 | logExecSql(db, sql.toString()); |
| 333 | sql.setLength(0); |
| 334 | } |
| 335 | } |
| 336 | |
| 337 | if (mHasFullTextIndex) { |
| 338 | // Add an FTS virtual table if using full-text search. |
| 339 | String ftsTableName = tableName + FULL_TEXT_INDEX_SUFFIX; |
| 340 | sql.append("CREATE VIRTUAL TABLE "); |
| 341 | sql.append(ftsTableName); |
| 342 | sql.append(" USING FTS3 (_id INTEGER PRIMARY KEY"); |
| 343 | for (ColumnInfo column : mColumnInfo) { |
| 344 | if (column.fullText) { |
| 345 | // Add the column to the FTS table. |
| 346 | String columnName = column.name; |
| 347 | sql.append(','); |
| 348 | sql.append(columnName); |
| 349 | sql.append(" TEXT"); |
| 350 | } |
| 351 | } |
| 352 | sql.append(");"); |
| 353 | logExecSql(db, sql.toString()); |
| 354 | sql.setLength(0); |
| 355 | |
| 356 | // Build an insert statement that will automatically keep the FTS |
| 357 | // table in sync. |
| 358 | StringBuilder insertSql = new StringBuilder("INSERT OR REPLACE INTO "); |
| 359 | insertSql.append(ftsTableName); |
| 360 | insertSql.append(" (_id"); |
| 361 | for (ColumnInfo column : mColumnInfo) { |
| 362 | if (column.fullText) { |
| 363 | insertSql.append(','); |
| 364 | insertSql.append(column.name); |
| 365 | } |
| 366 | } |
| 367 | insertSql.append(") VALUES (new._id"); |
| 368 | for (ColumnInfo column : mColumnInfo) { |
| 369 | if (column.fullText) { |
| 370 | insertSql.append(",new."); |
| 371 | insertSql.append(column.name); |
| 372 | } |
| 373 | } |
| 374 | insertSql.append(");"); |
| 375 | String insertSqlString = insertSql.toString(); |
| 376 | |
| 377 | // Add an insert trigger. |
| 378 | sql.append("CREATE TRIGGER "); |
| 379 | sql.append(tableName); |
| 380 | sql.append("_insert_trigger AFTER INSERT ON "); |
| 381 | sql.append(tableName); |
| 382 | sql.append(" FOR EACH ROW BEGIN "); |
| 383 | sql.append(insertSqlString); |
| 384 | sql.append("END;"); |
| 385 | logExecSql(db, sql.toString()); |
| 386 | sql.setLength(0); |
| 387 | |
| 388 | // Add an update trigger. |
| 389 | sql.append("CREATE TRIGGER "); |
| 390 | sql.append(tableName); |
| 391 | sql.append("_update_trigger AFTER UPDATE ON "); |
| 392 | sql.append(tableName); |
| 393 | sql.append(" FOR EACH ROW BEGIN "); |
| 394 | sql.append(insertSqlString); |
| 395 | sql.append("END;"); |
| 396 | logExecSql(db, sql.toString()); |
| 397 | sql.setLength(0); |
| 398 | |
| 399 | // Add a delete trigger. |
| 400 | sql.append("CREATE TRIGGER "); |
| 401 | sql.append(tableName); |
| 402 | sql.append("_delete_trigger AFTER DELETE ON "); |
| 403 | sql.append(tableName); |
| 404 | sql.append(" FOR EACH ROW BEGIN DELETE FROM "); |
| 405 | sql.append(ftsTableName); |
| 406 | sql.append(" WHERE _id = old._id; END;"); |
| 407 | logExecSql(db, sql.toString()); |
| 408 | sql.setLength(0); |
| 409 | } |
| 410 | } |
| 411 | |
| 412 | public void dropTables(SQLiteDatabase db) { |
| 413 | String tableName = mTableName; |
| 414 | StringBuilder sql = new StringBuilder("DROP TABLE IF EXISTS "); |
| 415 | sql.append(tableName); |
| 416 | sql.append(';'); |
| 417 | logExecSql(db, sql.toString()); |
| 418 | sql.setLength(0); |
| 419 | |
| 420 | if (mHasFullTextIndex) { |
| 421 | sql.append("DROP TABLE IF EXISTS "); |
| 422 | sql.append(tableName); |
| 423 | sql.append(FULL_TEXT_INDEX_SUFFIX); |
| 424 | sql.append(';'); |
| 425 | logExecSql(db, sql.toString()); |
| 426 | } |
| 427 | |
| 428 | } |
| 429 | |
| 430 | public void deleteAll(SQLiteDatabase db) { |
| 431 | StringBuilder sql = new StringBuilder("DELETE FROM "); |
| 432 | sql.append(mTableName); |
| 433 | sql.append(";"); |
| 434 | logExecSql(db, sql.toString()); |
| 435 | } |
| 436 | |
| 437 | private String parseTableName(Class<? extends Object> clazz) { |
| 438 | // Check for a table annotation. |
| 439 | Entry.Table table = clazz.getAnnotation(Entry.Table.class); |
| 440 | if (table == null) { |
| 441 | return null; |
| 442 | } |
| 443 | |
| 444 | // Return the table name. |
| 445 | return table.value(); |
| 446 | } |
| 447 | |
| 448 | private ColumnInfo[] parseColumnInfo(Class<? extends Object> clazz) { |
| 449 | ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>(); |
| 450 | while (clazz != null) { |
| 451 | parseColumnInfo(clazz, columns); |
| 452 | clazz = clazz.getSuperclass(); |
| 453 | } |
| 454 | |
| 455 | // Return a list. |
| 456 | ColumnInfo[] columnList = new ColumnInfo[columns.size()]; |
| 457 | columns.toArray(columnList); |
| 458 | return columnList; |
| 459 | } |
| 460 | |
| 461 | private void parseColumnInfo(Class<? extends Object> clazz, ArrayList<ColumnInfo> columns) { |
| 462 | // Gather metadata from each annotated field. |
| 463 | Field[] fields = clazz.getDeclaredFields(); // including non-public fields |
| 464 | for (int i = 0; i != fields.length; ++i) { |
| 465 | // Get column metadata from the annotation. |
| 466 | Field field = fields[i]; |
| 467 | Entry.Column info = ((AnnotatedElement) field).getAnnotation(Entry.Column.class); |
| 468 | if (info == null) continue; |
| 469 | |
| 470 | // Determine the field type. |
| 471 | int type; |
| 472 | Class<?> fieldType = field.getType(); |
| 473 | if (fieldType == String.class) { |
| 474 | type = TYPE_STRING; |
| 475 | } else if (fieldType == boolean.class) { |
| 476 | type = TYPE_BOOLEAN; |
| 477 | } else if (fieldType == short.class) { |
| 478 | type = TYPE_SHORT; |
| 479 | } else if (fieldType == int.class) { |
| 480 | type = TYPE_INT; |
| 481 | } else if (fieldType == long.class) { |
| 482 | type = TYPE_LONG; |
| 483 | } else if (fieldType == float.class) { |
| 484 | type = TYPE_FLOAT; |
| 485 | } else if (fieldType == double.class) { |
| 486 | type = TYPE_DOUBLE; |
| 487 | } else if (fieldType == byte[].class) { |
| 488 | type = TYPE_BLOB; |
| 489 | } else { |
| 490 | throw new IllegalArgumentException( |
| 491 | "Unsupported field type for column: " + fieldType.getName()); |
| 492 | } |
| 493 | |
| 494 | // Add the column to the array. |
| 495 | int index = columns.size(); |
| 496 | columns.add(new ColumnInfo(info.value(), type, info.indexed(), |
| 497 | info.fullText(), info.defaultValue(), field, index)); |
| 498 | } |
| 499 | } |
| 500 | |
| 501 | public static final class ColumnInfo { |
| 502 | private static final String ID_KEY = "_id"; |
| 503 | |
| 504 | public final String name; |
| 505 | public final int type; |
| 506 | public final boolean indexed; |
| 507 | public final boolean fullText; |
| 508 | public final String defaultValue; |
| 509 | public final Field field; |
| 510 | public final int projectionIndex; |
| 511 | |
| 512 | public ColumnInfo(String name, int type, boolean indexed, |
| 513 | boolean fullText, String defaultValue, Field field, int projectionIndex) { |
| 514 | this.name = name.toLowerCase(); |
| 515 | this.type = type; |
| 516 | this.indexed = indexed; |
| 517 | this.fullText = fullText; |
| 518 | this.defaultValue = defaultValue; |
| 519 | this.field = field; |
| 520 | this.projectionIndex = projectionIndex; |
| 521 | |
| 522 | field.setAccessible(true); // in order to set non-public fields |
| 523 | } |
| 524 | |
| 525 | public boolean isId() { |
| 526 | return ID_KEY.equals(name); |
| 527 | } |
| 528 | } |
| 529 | } |