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