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 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 public 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 } 530
