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", "INTEGER", "REAL", "REAL", "NONE" };
23 private static final String TAG = "SchemaInfo";
24 private static final String FULL_TEXT_INDEX_SUFFIX = "_fulltext";
26 private final String mTableName;
27 private final ColumnInfo[] mColumnInfo;
28 private final String[] mProjection;
29 private final boolean mHasFullTextIndex;
31 public EntrySchema(Class<? extends Entry> clazz) {
32 // Get table and column metadata from reflection.
33 ColumnInfo[] columns = parseColumnInfo(clazz);
34 mTableName = parseTableName(clazz);
35 mColumnInfo = columns;
37 // Cache the list of projection columns and check for full-text columns.
38 String[] projection = {};
39 boolean hasFullTextIndex = false;
40 if (columns != null) {
41 projection = new String[columns.length];
42 for (int i = 0; i != columns.length; ++i) {
43 ColumnInfo column = columns[i];
44 projection[i] = column.name;
45 if (column.fullText) {
46 hasFullTextIndex = true;
50 mProjection = projection;
51 mHasFullTextIndex = hasFullTextIndex;
54 public String getTableName() {
58 public ColumnInfo[] getColumnInfo() {
62 public String[] getProjection() {
66 private void logExecSql(SQLiteDatabase db, String sql) {
71 public void cursorToObject(Cursor cursor, Entry object) {
73 ColumnInfo[] columns = mColumnInfo;
74 for (int i = 0, size = columns.length; i != size; ++i) {
75 ColumnInfo column = columns[i];
76 int columnIndex = column.projectionIndex;
77 Field field = column.field;
78 switch (column.type) {
80 field.set(object, cursor.getString(columnIndex));
83 field.setBoolean(object, cursor.getShort(columnIndex) == 1);
86 field.setShort(object, cursor.getShort(columnIndex));
89 field.setInt(object, cursor.getInt(columnIndex));
92 field.setLong(object, cursor.getLong(columnIndex));
95 field.setFloat(object, cursor.getFloat(columnIndex));
98 field.setDouble(object, cursor.getDouble(columnIndex));
101 field.set(object, cursor.getBlob(columnIndex));
105 } catch (IllegalArgumentException e) {
106 Log.e(TAG, "SchemaInfo.setFromCursor: object not of the right type");
107 } catch (IllegalAccessException e) {
108 Log.e(TAG, "SchemaInfo.setFromCursor: field not accessible");
112 public void objectToValues(Entry object, ContentValues values) {
114 ColumnInfo[] columns = mColumnInfo;
115 for (int i = 0, size = columns.length; i != size; ++i) {
116 ColumnInfo column = columns[i];
117 String columnName = column.name;
118 Field field = column.field;
119 switch (column.type) {
121 values.put(columnName, (String) field.get(object));
124 values.put(columnName, field.getBoolean(object));
127 values.put(columnName, field.getShort(object));
130 values.put(columnName, field.getInt(object));
133 values.put(columnName, field.getLong(object));
136 values.put(columnName, field.getFloat(object));
139 values.put(columnName, field.getDouble(object));
142 values.put(columnName, (byte[]) field.get(object));
146 } catch (IllegalArgumentException e) {
147 Log.e(TAG, "SchemaInfo.setFromCursor: object not of the right type");
148 } catch (IllegalAccessException e) {
149 Log.e(TAG, "SchemaInfo.setFromCursor: field not accessible");
153 public Cursor queryAll(SQLiteDatabase db) {
154 return db.query(mTableName, mProjection, null, null, null, null, null);
157 public boolean queryWithId(SQLiteDatabase db, long id, Entry entry) {
158 Cursor cursor = db.query(mTableName, mProjection, "_id=?", new String[] { Long.toString(id) }, null, null, null);
159 boolean success = false;
160 if (cursor.moveToFirst()) {
161 cursorToObject(cursor, entry);
168 public long insertOrReplace(SQLiteDatabase db, Entry entry) {
169 ContentValues values = new ContentValues();
170 objectToValues(entry, values);
172 Log.i(TAG, "removing id before insert");
173 values.remove("_id");
175 long id = db.replace(mTableName, "_id", values);
180 public boolean deleteWithId(SQLiteDatabase db, long id) {
181 return db.delete(mTableName, "_id=?", new String[] { Long.toString(id) }) == 1;
184 public void createTables(SQLiteDatabase db) {
185 // Wrapped class must have a @Table.Definition.
186 String tableName = mTableName;
187 if (tableName == null) {
191 // Add the CREATE TABLE statement for the main table.
192 StringBuilder sql = new StringBuilder("CREATE TABLE ");
193 sql.append(tableName);
194 sql.append(" (_id INTEGER PRIMARY KEY");
195 ColumnInfo[] columns = mColumnInfo;
196 int numColumns = columns.length;
197 for (int i = 0; i != numColumns; ++i) {
198 ColumnInfo column = columns[i];
199 if (!column.isId()) {
201 sql.append(column.name);
203 sql.append(SQLITE_TYPES[column.type]);
204 if (column.extraSql != null) {
206 sql.append(column.extraSql);
211 logExecSql(db, sql.toString());
214 // Create indexes for all indexed columns.
215 for (int i = 0; i != numColumns; ++i) {
216 // Create an index on the indexed columns.
217 ColumnInfo column = columns[i];
218 if (column.indexed) {
219 sql.append("CREATE INDEX ");
220 sql.append(tableName);
221 sql.append("_index_");
222 sql.append(column.name);
224 sql.append(tableName);
226 sql.append(column.name);
228 logExecSql(db, sql.toString());
233 if (mHasFullTextIndex) {
234 // Add an FTS virtual table if using full-text search.
235 String ftsTableName = tableName + FULL_TEXT_INDEX_SUFFIX;
236 sql.append("CREATE VIRTUAL TABLE ");
237 sql.append(ftsTableName);
238 sql.append(" USING FTS3 (_id INTEGER PRIMARY KEY");
239 for (int i = 0; i != numColumns; ++i) {
240 ColumnInfo column = columns[i];
241 if (column.fullText) {
242 // Add the column to the FTS table.
243 String columnName = column.name;
245 sql.append(columnName);
250 logExecSql(db, sql.toString());
253 // Build an insert statement that will automatically keep the FTS
255 StringBuilder insertSql = new StringBuilder("INSERT OR REPLACE INTO ");
256 insertSql.append(ftsTableName);
257 insertSql.append(" (_id");
258 for (int i = 0; i != numColumns; ++i) {
259 ColumnInfo column = columns[i];
260 if (column.fullText) {
261 insertSql.append(',');
262 insertSql.append(column.name);
265 insertSql.append(") VALUES (new._id");
266 for (int i = 0; i != numColumns; ++i) {
267 ColumnInfo column = columns[i];
268 if (column.fullText) {
269 insertSql.append(",new.");
270 insertSql.append(column.name);
273 insertSql.append(");");
274 String insertSqlString = insertSql.toString();
276 // Add an insert trigger.
277 sql.append("CREATE TRIGGER ");
278 sql.append(tableName);
279 sql.append("_insert_trigger AFTER INSERT ON ");
280 sql.append(tableName);
281 sql.append(" FOR EACH ROW BEGIN ");
282 sql.append(insertSqlString);
284 logExecSql(db, sql.toString());
287 // Add an update trigger.
288 sql.append("CREATE TRIGGER ");
289 sql.append(tableName);
290 sql.append("_update_trigger AFTER UPDATE ON ");
291 sql.append(tableName);
292 sql.append(" FOR EACH ROW BEGIN ");
293 sql.append(insertSqlString);
295 logExecSql(db, sql.toString());
298 // Add a delete trigger.
299 sql.append("CREATE TRIGGER ");
300 sql.append(tableName);
301 sql.append("_delete_trigger AFTER DELETE ON ");
302 sql.append(tableName);
303 sql.append(" FOR EACH ROW BEGIN DELETE FROM ");
304 sql.append(ftsTableName);
305 sql.append(" WHERE _id = old._id; END;");
306 logExecSql(db, sql.toString());
311 public void dropTables(SQLiteDatabase db) {
312 String tableName = mTableName;
313 StringBuilder sql = new StringBuilder("DROP TABLE IF EXISTS ");
314 sql.append(tableName);
316 logExecSql(db, sql.toString());
319 if (mHasFullTextIndex) {
320 sql.append("DROP TABLE IF EXISTS ");
321 sql.append(tableName);
322 sql.append(FULL_TEXT_INDEX_SUFFIX);
324 logExecSql(db, sql.toString());
329 public void deleteAll(SQLiteDatabase db) {
330 StringBuilder sql = new StringBuilder("DELETE FROM ");
331 sql.append(mTableName);
333 logExecSql(db, sql.toString());
336 private String parseTableName(Class<? extends Object> clazz) {
337 // Check for a table annotation.
338 Entry.Table table = clazz.getAnnotation(Entry.Table.class);
343 // Return the table name.
344 return table.value();
347 private ColumnInfo[] parseColumnInfo(Class<? extends Object> clazz) {
348 // Gather metadata from each annotated field.
349 ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
350 Field[] fields = clazz.getFields();
351 for (int i = 0; i != fields.length; ++i) {
352 // Get column metadata from the annotation.
353 Field field = fields[i];
354 Entry.Column info = ((AnnotatedElement) field).getAnnotation(Entry.Column.class);
359 // Determine the field type.
361 Class<?> fieldType = field.getType();
362 if (fieldType == String.class) {
364 } else if (fieldType == boolean.class) {
366 } else if (fieldType == short.class) {
368 } else if (fieldType == int.class) {
370 } else if (fieldType == long.class) {
372 } else if (fieldType == float.class) {
374 } else if (fieldType == double.class) {
376 } else if (fieldType == byte[].class) {
379 throw new IllegalArgumentException("Unsupported field type for column: " + fieldType.getName());
382 // Add the column to the array.
383 int index = columns.size();
384 columns.add(new ColumnInfo(info.value(), type, info.indexed(), info.fullText(), field, index));
388 ColumnInfo[] columnList = new ColumnInfo[columns.size()];
389 columns.toArray(columnList);
393 public static final class ColumnInfo {
394 public final String name;
395 public final int type;
396 public final boolean indexed;
397 public final boolean fullText;
398 public final String extraSql = "";
399 public final Field field;
400 public final int projectionIndex;
402 public ColumnInfo(String name, int type, boolean indexed, boolean fullText, Field field, int projectionIndex) {
403 this.name = name.toLowerCase();
405 this.indexed = indexed;
406 this.fullText = fullText;
408 this.projectionIndex = projectionIndex;
411 public boolean isId() {
412 return name == "_id";