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         StringBuilder unique = new StringBuilder();
304         for (ColumnInfo column : mColumnInfo) {
305             if (!column.isId()) {
306                 sql.append(',');
307                 sql.append(column.name);
308                 sql.append(' ');
309                 sql.append(SQLITE_TYPES[column.type]);
310                 if (!TextUtils.isEmpty(column.defaultValue)) {
311                     sql.append(" DEFAULT ");
312                     sql.append(column.defaultValue);
313                 }
314                 if (column.unique) {
315                     if (unique.length() == 0) {
316                         unique.append(column.name);
317                     } else {
318                         unique.append(',').append(column.name);
319                     }
320                 }
321             }
322         }
323         if (unique.length() > 0) {
324             sql.append(",UNIQUE(").append(unique).append(')');
325         }
326         sql.append(");");
327         logExecSql(db, sql.toString());
328         sql.setLength(0);
329 
330         // Create indexes for all indexed columns.
331         for (ColumnInfo column : mColumnInfo) {
332             // Create an index on the indexed columns.
333             if (column.indexed) {
334                 sql.append("CREATE INDEX ");
335                 sql.append(tableName);
336                 sql.append("_index_");
337                 sql.append(column.name);
338                 sql.append(" ON ");
339                 sql.append(tableName);
340                 sql.append(" (");
341                 sql.append(column.name);
342                 sql.append(");");
343                 logExecSql(db, sql.toString());
344                 sql.setLength(0);
345             }
346         }
347 
348         if (mHasFullTextIndex) {
349             // Add an FTS virtual table if using full-text search.
350             String ftsTableName = tableName + FULL_TEXT_INDEX_SUFFIX;
351             sql.append("CREATE VIRTUAL TABLE ");
352             sql.append(ftsTableName);
353             sql.append(" USING FTS3 (_id INTEGER PRIMARY KEY");
354             for (ColumnInfo column : mColumnInfo) {
355                 if (column.fullText) {
356                     // Add the column to the FTS table.
357                     String columnName = column.name;
358                     sql.append(',');
359                     sql.append(columnName);
360                     sql.append(" TEXT");
361                 }
362             }
363             sql.append(");");
364             logExecSql(db, sql.toString());
365             sql.setLength(0);
366 
367             // Build an insert statement that will automatically keep the FTS
368             // table in sync.
369             StringBuilder insertSql = new StringBuilder("INSERT OR REPLACE INTO ");
370             insertSql.append(ftsTableName);
371             insertSql.append(" (_id");
372             for (ColumnInfo column : mColumnInfo) {
373                 if (column.fullText) {
374                     insertSql.append(',');
375                     insertSql.append(column.name);
376                 }
377             }
378             insertSql.append(") VALUES (new._id");
379             for (ColumnInfo column : mColumnInfo) {
380                 if (column.fullText) {
381                     insertSql.append(",new.");
382                     insertSql.append(column.name);
383                 }
384             }
385             insertSql.append(");");
386             String insertSqlString = insertSql.toString();
387 
388             // Add an insert trigger.
389             sql.append("CREATE TRIGGER ");
390             sql.append(tableName);
391             sql.append("_insert_trigger AFTER INSERT 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 an update trigger.
400             sql.append("CREATE TRIGGER ");
401             sql.append(tableName);
402             sql.append("_update_trigger AFTER UPDATE ON ");
403             sql.append(tableName);
404             sql.append(" FOR EACH ROW BEGIN ");
405             sql.append(insertSqlString);
406             sql.append("END;");
407             logExecSql(db, sql.toString());
408             sql.setLength(0);
409 
410             // Add a delete trigger.
411             sql.append("CREATE TRIGGER ");
412             sql.append(tableName);
413             sql.append("_delete_trigger AFTER DELETE ON ");
414             sql.append(tableName);
415             sql.append(" FOR EACH ROW BEGIN DELETE FROM ");
416             sql.append(ftsTableName);
417             sql.append(" WHERE _id = old._id; END;");
418             logExecSql(db, sql.toString());
419             sql.setLength(0);
420         }
421     }
422 
dropTables(SQLiteDatabase db)423     public void dropTables(SQLiteDatabase db) {
424         String tableName = mTableName;
425         StringBuilder sql = new StringBuilder("DROP TABLE IF EXISTS ");
426         sql.append(tableName);
427         sql.append(';');
428         logExecSql(db, sql.toString());
429         sql.setLength(0);
430 
431         if (mHasFullTextIndex) {
432             sql.append("DROP TABLE IF EXISTS ");
433             sql.append(tableName);
434             sql.append(FULL_TEXT_INDEX_SUFFIX);
435             sql.append(';');
436             logExecSql(db, sql.toString());
437         }
438 
439     }
440 
deleteAll(SQLiteDatabase db)441     public void deleteAll(SQLiteDatabase db) {
442         StringBuilder sql = new StringBuilder("DELETE FROM ");
443         sql.append(mTableName);
444         sql.append(";");
445         logExecSql(db, sql.toString());
446     }
447 
parseTableName(Class<? extends Object> clazz)448     private String parseTableName(Class<? extends Object> clazz) {
449         // Check for a table annotation.
450         Entry.Table table = clazz.getAnnotation(Entry.Table.class);
451         if (table == null) {
452             return null;
453         }
454 
455         // Return the table name.
456         return table.value();
457     }
458 
parseColumnInfo(Class<? extends Object> clazz)459     private ColumnInfo[] parseColumnInfo(Class<? extends Object> clazz) {
460         ArrayList<ColumnInfo> columns = new ArrayList<ColumnInfo>();
461         while (clazz != null) {
462             parseColumnInfo(clazz, columns);
463             clazz = clazz.getSuperclass();
464         }
465 
466         // Return a list.
467         ColumnInfo[] columnList = new ColumnInfo[columns.size()];
468         columns.toArray(columnList);
469         return columnList;
470     }
471 
parseColumnInfo(Class<? extends Object> clazz, ArrayList<ColumnInfo> columns)472     private void parseColumnInfo(Class<? extends Object> clazz, ArrayList<ColumnInfo> columns) {
473         // Gather metadata from each annotated field.
474         Field[] fields = clazz.getDeclaredFields(); // including non-public fields
475         for (int i = 0; i != fields.length; ++i) {
476             // Get column metadata from the annotation.
477             Field field = fields[i];
478             Entry.Column info = ((AnnotatedElement) field).getAnnotation(Entry.Column.class);
479             if (info == null) continue;
480 
481             // Determine the field type.
482             int type;
483             Class<?> fieldType = field.getType();
484             if (fieldType == String.class) {
485                 type = TYPE_STRING;
486             } else if (fieldType == boolean.class) {
487                 type = TYPE_BOOLEAN;
488             } else if (fieldType == short.class) {
489                 type = TYPE_SHORT;
490             } else if (fieldType == int.class) {
491                 type = TYPE_INT;
492             } else if (fieldType == long.class) {
493                 type = TYPE_LONG;
494             } else if (fieldType == float.class) {
495                 type = TYPE_FLOAT;
496             } else if (fieldType == double.class) {
497                 type = TYPE_DOUBLE;
498             } else if (fieldType == byte[].class) {
499                 type = TYPE_BLOB;
500             } else {
501                 throw new IllegalArgumentException(
502                         "Unsupported field type for column: " + fieldType.getName());
503             }
504 
505             // Add the column to the array.
506             int index = columns.size();
507             columns.add(new ColumnInfo(info.value(), type, info.indexed(), info.unique(),
508                     info.fullText(), info.defaultValue(), field, index));
509         }
510     }
511 
512     public static final class ColumnInfo {
513         private static final String ID_KEY = "_id";
514 
515         public final String name;
516         public final int type;
517         public final boolean indexed;
518         public final boolean unique;
519         public final boolean fullText;
520         public final String defaultValue;
521         public final Field field;
522         public final int projectionIndex;
523 
ColumnInfo(String name, int type, boolean indexed, boolean unique, boolean fullText, String defaultValue, Field field, int projectionIndex)524         public ColumnInfo(String name, int type, boolean indexed, boolean unique,
525                 boolean fullText, String defaultValue, Field field, int projectionIndex) {
526             this.name = name.toLowerCase();
527             this.type = type;
528             this.indexed = indexed;
529             this.unique = unique;
530             this.fullText = fullText;
531             this.defaultValue = defaultValue;
532             this.field = field;
533             this.projectionIndex = projectionIndex;
534 
535             field.setAccessible(true); // in order to set non-public fields
536         }
537 
isId()538         public boolean isId() {
539             return ID_KEY.equals(name);
540         }
541     }
542 }
543