1 package com.cooliris.picasa;
3 import java.lang.reflect.AnnotatedElement;
4 import java.lang.reflect.Field;
5 import java.util.ArrayList;
7 import android.content.ContentValues;
8 import android.database.Cursor;
9 import android.database.sqlite.SQLiteDatabase;
10 import android.util.Log;
12 public final class EntrySchema {
13 public static final int TYPE_STRING = 0;
14 public static final int TYPE_BOOLEAN = 1;
15 public static final int TYPE_SHORT = 2;
16 public static final int TYPE_INT = 3;
17 public static final int TYPE_LONG = 4;
18 public static final int TYPE_FLOAT = 5;
19 public static final int TYPE_DOUBLE = 6;
20 public static final int TYPE_BLOB = 7;
21 public static final String SQLITE_TYPES[] = { "TEXT", "INTEGER", "INTEGER", "INTEGER",
22 "INTEGER", "REAL", "REAL", "NONE" };
24 private static final String TAG = "SchemaInfo";
25 private static final String FULL_TEXT_INDEX_SUFFIX = "_fulltext";
27 private final String mTableName;
28 private final ColumnInfo[] mColumnInfo;
29 private final String[] mProjection;
30 private final boolean mHasFullTextIndex;
32 public EntrySchema(Class<? extends Entry> clazz) {
33 // Get table and column metadata from reflection.
34 ColumnInfo[] columns = parseColumnInfo(clazz);
35 mTableName = parseTableName(clazz);
36 mColumnInfo = columns;
38 // Cache the list of projection columns and check for full-text columns.
39 String[] projection = {};
40 boolean hasFullTextIndex = false;
41 if (columns != null) {
42 projection = new String[columns.length];
43 for (int i = 0; i != columns.length; ++i) {
44 ColumnInfo column = columns[i];
45 projection[i] = column.name;
46 if (column.fullText) {
47 hasFullTextIndex = true;
51 mProjection = projection;
52 mHasFullTextIndex = hasFullTextIndex;
55 public String getTableName() {
59 public ColumnInfo[] getColumnInfo() {
63 public String[] getProjection() {
67 private void logExecSql(SQLiteDatabase db, String sql) {
72 public void cursorToObject(Cursor cursor, Entry object) {
74 ColumnInfo[] columns = mColumnInfo;
75 for (int i = 0, size = columns.length; i != size; ++i) {
76 ColumnInfo column = columns[i];
77 int columnIndex = column.projectionIndex;
78 Field field = column.field;
79 switch (column.type) {
81 field.set(object, cursor.getString(columnIndex));
84 field.setBoolean(object, cursor.getShort(columnIndex) == 1);
87 field.setShort(object, cursor.getShort(columnIndex));
90 field.setInt(object, cursor.getInt(columnIndex));
93 field.setLong(object, cursor.getLong(columnIndex));
96 field.setFloat(object, cursor.getFloat(columnIndex));
99 field.setDouble(object, cursor.getDouble(columnIndex));
102 field.set(object, cursor.getBlob(columnIndex));
106 } catch (IllegalArgumentException e) {
107 Log.e(TAG, "SchemaInfo.setFromCursor: object not of the right type");
108 } catch (IllegalAccessException e) {
109 Log.e(TAG, "SchemaInfo.setFromCursor: field not accessible");
113 public void objectToValues(Entry object, ContentValues values) {
115 ColumnInfo[] columns = mColumnInfo;
116 for (int i = 0, size = columns.length; i != size; ++i) {
117 ColumnInfo column = columns[i];
118 String columnName = column.name;
119 Field field = column.field;
120 switch (column.type) {
122 values.put(columnName, (String)field.get(object));
125 values.put(columnName, field.getBoolean(object));
128 values.put(columnName, field.getShort(object));
131 values.put(columnName, field.getInt(object));
134 values.put(columnName, field.getLong(object));
137 values.put(columnName, field.getFloat(object));
140 values.put(columnName, field.getDouble(object));
143 values.put(columnName, (byte[])field.get(object));
147 } catch (IllegalArgumentException e) {
148 Log.e(TAG, "SchemaInfo.setFromCursor: object not of the right type");
149 } catch (IllegalAccessException e) {
150 Log.e(TAG, "SchemaInfo.setFromCursor: field not accessible");
154 public Cursor queryAll(SQLiteDatabase db) {
155 return db.query(mTableName, mProjection, null, null, null, null, null);
158 public boolean queryWithId(SQLiteDatabase db, long id, Entry entry) {
159 Cursor cursor = db.query(mTableName, mProjection, "_id=?",
160 new String[] { Long.toString(id) }, null, null, null);
161 boolean success = false;
162 if (cursor.moveToFirst()) {
163 cursorToObject(cursor, entry);
170 public long insertOrReplace(SQLiteDatabase db, Entry entry) {
171 ContentValues values = new ContentValues();
172 objectToValues(entry, values);
174 Log.i(TAG, "removing id before insert");
175 values.remove("_id");
177 long id = db.replace(mTableName, "_id", values);
182 public boolean deleteWithId(SQLiteDatabase db, long id) {
183 return db.delete(mTableName, "_id=?", new String[] { Long.toString(id) }) == 1;
186 public void createTables(SQLiteDatabase db) {
187 // Wrapped class must have a @Table.Definition.
188 String tableName = mTableName;
189 if (tableName == null) {
193 // Add the CREATE TABLE statement for the main table.
194 StringBuilder sql = new StringBuilder("CREATE TABLE ");
195 sql.append(tableName);
196 sql.append(" (_id INTEGER PRIMARY KEY");
197 ColumnInfo[] columns = mColumnInfo;
198 int numColumns = columns.length;
199 for (int i = 0; i != numColumns; ++i) {
200 ColumnInfo column = columns[i];
201 if (!column.isId()) {
203 sql.append(column.name);
205 sql.append(SQLITE_TYPES[column.type]);
206 if (column.extraSql != null) {
208 sql.append(column.extraSql);
213 logExecSql(db, sql.toString());
216 // Create indexes for all indexed columns.
217 for (int i = 0; i != numColumns; ++i) {
218 // Create an index on the indexed columns.
219 ColumnInfo column = columns[i];
220 if (column.indexed) {
221 sql.append("CREATE INDEX ");
222 sql.append(tableName);
223 sql.append("_index_");
224 sql.append(column.name);
226 sql.append(tableName);
228 sql.append(column.name);
230 logExecSql(db, sql.toString());
235 if (mHasFullTextIndex) {
236 // Add an FTS virtual table if using full-text search.
237 String ftsTableName = tableName + FULL_TEXT_INDEX_SUFFIX;
238 sql.append("CREATE VIRTUAL TABLE ");
239 sql.append(ftsTableName);
240 sql.append(" USING FTS3 (_id INTEGER PRIMARY KEY");
241 for (int i = 0; i != numColumns; ++i) {
242 ColumnInfo column = columns[i];
243 if (column.fullText) {
244 // Add the column to the FTS table.
245 String columnName = column.name;
247 sql.append(columnName);
252 logExecSql(db, sql.toString());
255 // Build an insert statement that will automatically keep the FTS table in sync.
256 StringBuilder insertSql = new StringBuilder("INSERT OR REPLACE INTO ");
257 insertSql.append(ftsTableName);
258 insertSql.append(" (_id");
259 for (int i = 0; i != numColumns; ++i) {
260 ColumnInfo column = columns[i];
261 if (column.fullText) {
262 insertSql.append(',');
263 insertSql.append(column.name);
266 insertSql.append(") VALUES (new._id");
267 for (int i = 0; i != numColumns; ++i) {
268 ColumnInfo column = columns[i];
269 if (column.fullText) {
270 insertSql.append(",new.");
271 insertSql.append(column.name);
274 insertSql.append(");");
275 String insertSqlString = insertSql.toString();
277 // Add an insert trigger.
278 sql.append("CREATE TRIGGER ");
279 sql.append(tableName);
280 sql.append("_insert_trigger AFTER INSERT ON ");
281 sql.append(tableName);
282 sql.append(" FOR EACH ROW BEGIN ");
283 sql.append(insertSqlString);
285 logExecSql(db, sql.toString());
288 // Add an update trigger.
289 sql.append("CREATE TRIGGER ");
290 sql.append(tableName);
291 sql.append("_update_trigger AFTER UPDATE ON ");
292 sql.append(tableName);
293 sql.append(" FOR EACH ROW BEGIN ");
294 sql.append(insertSqlString);
296 logExecSql(db, sql.toString());
299 // Add a delete trigger.
300 sql.append("CREATE TRIGGER ");
301 sql.append(tableName);
302 sql.append("_delete_trigger AFTER DELETE ON ");
303 sql.append(tableName);
304 sql.append(" FOR EACH ROW BEGIN DELETE FROM ");
305 sql.append(ftsTableName);
306 sql.append(" WHERE _id = old._id; END;");
307 logExecSql(db, sql.toString());
312 public void dropTables(SQLiteDatabase db) {
313 String tableName = mTableName;
314 StringBuilder sql = new StringBuilder("DROP TABLE IF EXISTS ");
315 sql.append(tableName);
317 logExecSql(db, sql.toString());
320 if (mHasFullTextIndex) {
321 sql.append("DROP TABLE IF EXISTS ");
322 sql.append(tableName);
323 sql.append(FULL_TEXT_INDEX_SUFFIX);
325 logExecSql(db, sql.toString());
330 public void deleteAll(SQLiteDatabase db) {
331 StringBuilder sql = new StringBuilder("DELETE FROM ");
332 sql.append(mTableName);
334 logExecSql(db, sql.toString());
337 private String parseTableName(Class<? extends Object> clazz) {
338 // Check for a table annotation.
339 Entry.Table table = clazz.getAnnotation(Entry.Table.class);
344 // Return the table name.
345 return table.value();
348 private ColumnInfo[] parseColumnInfo(Class<? extends Object> clazz) {
349 // Gather metadata from each annotated field.
350 ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
351 Field[] fields = clazz.getFields();
352 for (int i = 0; i != fields.length; ++i) {
353 // Get column metadata from the annotation.
354 Field field = fields[i];
355 Entry.Column info = ((AnnotatedElement)field).getAnnotation(Entry.Column.class);
360 // Determine the field type.
362 Class<?> fieldType = field.getType();
363 if (fieldType == String.class) {
365 } else if (fieldType == boolean.class) {
367 } else if (fieldType == short.class) {
369 } else if (fieldType == int.class) {
371 } else if (fieldType == long.class) {
373 } else if (fieldType == float.class) {
375 } else if (fieldType == double.class) {
377 } else if (fieldType == byte[].class) {
380 throw new IllegalArgumentException("Unsupported field type for column: " +
381 fieldType.getName());
384 // Add the column to the array.
385 int index = columns.size();
386 columns.add(new ColumnInfo(info.value(), type, info.indexed(), info.fullText(), field,
391 ColumnInfo[] columnList = new ColumnInfo[columns.size()];
392 columns.toArray(columnList);
396 public static final class ColumnInfo {
397 public final String name;
398 public final int type;
399 public final boolean indexed;
400 public final boolean fullText;
401 public final String extraSql = "";
402 public final Field field;
403 public final int projectionIndex;
405 public ColumnInfo(String name, int type, boolean indexed, boolean fullText, Field field,
406 int projectionIndex) {
407 this.name = name.toLowerCase();
409 this.indexed = indexed;
410 this.fullText = fullText;
412 this.projectionIndex = projectionIndex;
415 public boolean isId() {
416 return name == "_id";