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 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 dropTables(SQLiteDatabase db)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 deleteAll(SQLiteDatabase db)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 parseTableName(Class<? extends Object> clazz)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 parseColumnInfo(Class<? extends Object> clazz)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 parseColumnInfo(Class<? extends Object> clazz, ArrayList<ColumnInfo> columns)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 ColumnInfo(String name, int type, boolean indexed, boolean fullText, String defaultValue, Field field, int projectionIndex)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 isId()525 public boolean isId() { 526 return ID_KEY.equals(name); 527 } 528 } 529 } 530