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 public static final int TYPE_STRING = 0; 33 public static final int TYPE_BOOLEAN = 1; 34 public static final int TYPE_SHORT = 2; 35 public static final int TYPE_INT = 3; 36 public static final int TYPE_LONG = 4; 37 public static final int TYPE_FLOAT = 5; 38 public static final int TYPE_DOUBLE = 6; 39 public 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 EntrySchema(Class<? extends Entry> clazz)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 getTableName()73 public String getTableName() { 74 return mTableName; 75 } 76 getColumnInfo()77 public ColumnInfo[] getColumnInfo() { 78 return mColumnInfo; 79 } 80 getProjection()81 public String[] getProjection() { 82 return mProjection; 83 } 84 getColumnIndex(String columnName)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 getColumn(String columnName)94 public ColumnInfo getColumn(String columnName) { 95 int index = getColumnIndex(columnName); 96 return (index < 0) ? null : mColumnInfo[index]; 97 } 98 logExecSql(SQLiteDatabase db, String sql)99 private void logExecSql(SQLiteDatabase db, String sql) { 100 db.execSQL(sql); 101 } 102 cursorToObject(Cursor cursor, T object)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 setIfNotNull(Field field, Object object, Object value)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 */ valuesToObject(ContentValues values, T object)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 objectToValues(Entry object, ContentValues values)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 toDebugString(Entry entry)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 toDebugString(Entry entry, String... columnNames)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 queryAll(SQLiteDatabase db)263 public Cursor queryAll(SQLiteDatabase db) { 264 return db.query(mTableName, mProjection, null, null, null, null, null); 265 } 266 queryWithId(SQLiteDatabase db, long id, Entry entry)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 insertOrReplace(SQLiteDatabase db, Entry entry)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 deleteWithId(SQLiteDatabase db, long id)290 public boolean deleteWithId(SQLiteDatabase db, long id) { 291 return db.delete(mTableName, "_id=?", new String[] { Long.toString(id) }) == 1; 292 } 293 createTables(SQLiteDatabase db)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 StringBuilder unique = new StringBuilder(); 304 for (ColumnInfo column : mColumnInfo) { 305 if (!column.isId()) { 306 sql.append(','); 307 sql.append(column.name); 308 sql.append(' '); 309 sql.append(SQLITE_TYPES[column.type]); 310 if (!TextUtils.isEmpty(column.defaultValue)) { 311 sql.append(" DEFAULT "); 312 sql.append(column.defaultValue); 313 } 314 if (column.unique) { 315 if (unique.length() == 0) { 316 unique.append(column.name); 317 } else { 318 unique.append(',').append(column.name); 319 } 320 } 321 } 322 } 323 if (unique.length() > 0) { 324 sql.append(",UNIQUE(").append(unique).append(')'); 325 } 326 sql.append(");"); 327 logExecSql(db, sql.toString()); 328 sql.setLength(0); 329 330 // Create indexes for all indexed columns. 331 for (ColumnInfo column : mColumnInfo) { 332 // Create an index on the indexed columns. 333 if (column.indexed) { 334 sql.append("CREATE INDEX "); 335 sql.append(tableName); 336 sql.append("_index_"); 337 sql.append(column.name); 338 sql.append(" ON "); 339 sql.append(tableName); 340 sql.append(" ("); 341 sql.append(column.name); 342 sql.append(");"); 343 logExecSql(db, sql.toString()); 344 sql.setLength(0); 345 } 346 } 347 348 if (mHasFullTextIndex) { 349 // Add an FTS virtual table if using full-text search. 350 String ftsTableName = tableName + FULL_TEXT_INDEX_SUFFIX; 351 sql.append("CREATE VIRTUAL TABLE "); 352 sql.append(ftsTableName); 353 sql.append(" USING FTS3 (_id INTEGER PRIMARY KEY"); 354 for (ColumnInfo column : mColumnInfo) { 355 if (column.fullText) { 356 // Add the column to the FTS table. 357 String columnName = column.name; 358 sql.append(','); 359 sql.append(columnName); 360 sql.append(" TEXT"); 361 } 362 } 363 sql.append(");"); 364 logExecSql(db, sql.toString()); 365 sql.setLength(0); 366 367 // Build an insert statement that will automatically keep the FTS 368 // table in sync. 369 StringBuilder insertSql = new StringBuilder("INSERT OR REPLACE INTO "); 370 insertSql.append(ftsTableName); 371 insertSql.append(" (_id"); 372 for (ColumnInfo column : mColumnInfo) { 373 if (column.fullText) { 374 insertSql.append(','); 375 insertSql.append(column.name); 376 } 377 } 378 insertSql.append(") VALUES (new._id"); 379 for (ColumnInfo column : mColumnInfo) { 380 if (column.fullText) { 381 insertSql.append(",new."); 382 insertSql.append(column.name); 383 } 384 } 385 insertSql.append(");"); 386 String insertSqlString = insertSql.toString(); 387 388 // Add an insert trigger. 389 sql.append("CREATE TRIGGER "); 390 sql.append(tableName); 391 sql.append("_insert_trigger AFTER INSERT 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 an update trigger. 400 sql.append("CREATE TRIGGER "); 401 sql.append(tableName); 402 sql.append("_update_trigger AFTER UPDATE ON "); 403 sql.append(tableName); 404 sql.append(" FOR EACH ROW BEGIN "); 405 sql.append(insertSqlString); 406 sql.append("END;"); 407 logExecSql(db, sql.toString()); 408 sql.setLength(0); 409 410 // Add a delete trigger. 411 sql.append("CREATE TRIGGER "); 412 sql.append(tableName); 413 sql.append("_delete_trigger AFTER DELETE ON "); 414 sql.append(tableName); 415 sql.append(" FOR EACH ROW BEGIN DELETE FROM "); 416 sql.append(ftsTableName); 417 sql.append(" WHERE _id = old._id; END;"); 418 logExecSql(db, sql.toString()); 419 sql.setLength(0); 420 } 421 } 422 dropTables(SQLiteDatabase db)423 public void dropTables(SQLiteDatabase db) { 424 String tableName = mTableName; 425 StringBuilder sql = new StringBuilder("DROP TABLE IF EXISTS "); 426 sql.append(tableName); 427 sql.append(';'); 428 logExecSql(db, sql.toString()); 429 sql.setLength(0); 430 431 if (mHasFullTextIndex) { 432 sql.append("DROP TABLE IF EXISTS "); 433 sql.append(tableName); 434 sql.append(FULL_TEXT_INDEX_SUFFIX); 435 sql.append(';'); 436 logExecSql(db, sql.toString()); 437 } 438 439 } 440 deleteAll(SQLiteDatabase db)441 public void deleteAll(SQLiteDatabase db) { 442 StringBuilder sql = new StringBuilder("DELETE FROM "); 443 sql.append(mTableName); 444 sql.append(";"); 445 logExecSql(db, sql.toString()); 446 } 447 parseTableName(Class<? extends Object> clazz)448 private String parseTableName(Class<? extends Object> clazz) { 449 // Check for a table annotation. 450 Entry.Table table = clazz.getAnnotation(Entry.Table.class); 451 if (table == null) { 452 return null; 453 } 454 455 // Return the table name. 456 return table.value(); 457 } 458 parseColumnInfo(Class<? extends Object> clazz)459 private ColumnInfo[] parseColumnInfo(Class<? extends Object> clazz) { 460 ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>(); 461 while (clazz != null) { 462 parseColumnInfo(clazz, columns); 463 clazz = clazz.getSuperclass(); 464 } 465 466 // Return a list. 467 ColumnInfo[] columnList = new ColumnInfo[columns.size()]; 468 columns.toArray(columnList); 469 return columnList; 470 } 471 parseColumnInfo(Class<? extends Object> clazz, ArrayList<ColumnInfo> columns)472 private void parseColumnInfo(Class<? extends Object> clazz, ArrayList<ColumnInfo> columns) { 473 // Gather metadata from each annotated field. 474 Field[] fields = clazz.getDeclaredFields(); // including non-public fields 475 for (int i = 0; i != fields.length; ++i) { 476 // Get column metadata from the annotation. 477 Field field = fields[i]; 478 Entry.Column info = ((AnnotatedElement) field).getAnnotation(Entry.Column.class); 479 if (info == null) continue; 480 481 // Determine the field type. 482 int type; 483 Class<?> fieldType = field.getType(); 484 if (fieldType == String.class) { 485 type = TYPE_STRING; 486 } else if (fieldType == boolean.class) { 487 type = TYPE_BOOLEAN; 488 } else if (fieldType == short.class) { 489 type = TYPE_SHORT; 490 } else if (fieldType == int.class) { 491 type = TYPE_INT; 492 } else if (fieldType == long.class) { 493 type = TYPE_LONG; 494 } else if (fieldType == float.class) { 495 type = TYPE_FLOAT; 496 } else if (fieldType == double.class) { 497 type = TYPE_DOUBLE; 498 } else if (fieldType == byte[].class) { 499 type = TYPE_BLOB; 500 } else { 501 throw new IllegalArgumentException( 502 "Unsupported field type for column: " + fieldType.getName()); 503 } 504 505 // Add the column to the array. 506 int index = columns.size(); 507 columns.add(new ColumnInfo(info.value(), type, info.indexed(), info.unique(), 508 info.fullText(), info.defaultValue(), field, index)); 509 } 510 } 511 512 public static final class ColumnInfo { 513 private static final String ID_KEY = "_id"; 514 515 public final String name; 516 public final int type; 517 public final boolean indexed; 518 public final boolean unique; 519 public final boolean fullText; 520 public final String defaultValue; 521 public final Field field; 522 public final int projectionIndex; 523 ColumnInfo(String name, int type, boolean indexed, boolean unique, boolean fullText, String defaultValue, Field field, int projectionIndex)524 public ColumnInfo(String name, int type, boolean indexed, boolean unique, 525 boolean fullText, String defaultValue, Field field, int projectionIndex) { 526 this.name = name.toLowerCase(); 527 this.type = type; 528 this.indexed = indexed; 529 this.unique = unique; 530 this.fullText = fullText; 531 this.defaultValue = defaultValue; 532 this.field = field; 533 this.projectionIndex = projectionIndex; 534 535 field.setAccessible(true); // in order to set non-public fields 536 } 537 isId()538 public boolean isId() { 539 return ID_KEY.equals(name); 540 } 541 } 542 } 543