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
17package com.android.gallery3d.common;
18
19import android.content.ContentValues;
20import android.database.Cursor;
21import android.database.sqlite.SQLiteDatabase;
22import android.text.TextUtils;
23
24import java.lang.reflect.AnnotatedElement;
25import java.lang.reflect.Field;
26import java.util.ArrayList;
27
28public 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        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
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
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
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
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
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
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
538        public boolean isId() {
539            return ID_KEY.equals(name);
540        }
541    }
542}
543