1/*
2 * Copyright (C) 2010 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.example.android.searchabledict;
18
19import android.app.SearchManager;
20import android.content.ContentValues;
21import android.content.Context;
22import android.content.res.Resources;
23import android.database.Cursor;
24import android.database.sqlite.SQLiteDatabase;
25import android.database.sqlite.SQLiteOpenHelper;
26import android.database.sqlite.SQLiteQueryBuilder;
27import android.provider.BaseColumns;
28import android.text.TextUtils;
29import android.util.Log;
30
31import java.io.BufferedReader;
32import java.io.IOException;
33import java.io.InputStream;
34import java.io.InputStreamReader;
35import java.util.HashMap;
36
37/**
38 * Contains logic to return specific words from the dictionary, and
39 * load the dictionary table when it needs to be created.
40 */
41public class DictionaryDatabase {
42    private static final String TAG = "DictionaryDatabase";
43
44    //The columns we'll include in the dictionary table
45    public static final String KEY_WORD = SearchManager.SUGGEST_COLUMN_TEXT_1;
46    public static final String KEY_DEFINITION = SearchManager.SUGGEST_COLUMN_TEXT_2;
47
48    private static final String DATABASE_NAME = "dictionary";
49    private static final String FTS_VIRTUAL_TABLE = "FTSdictionary";
50    private static final int DATABASE_VERSION = 2;
51
52    private final DictionaryOpenHelper mDatabaseOpenHelper;
53    private static final HashMap<String,String> mColumnMap = buildColumnMap();
54
55    /**
56     * Constructor
57     * @param context The Context within which to work, used to create the DB
58     */
59    public DictionaryDatabase(Context context) {
60        mDatabaseOpenHelper = new DictionaryOpenHelper(context);
61    }
62
63    /**
64     * Builds a map for all columns that may be requested, which will be given to the
65     * SQLiteQueryBuilder. This is a good way to define aliases for column names, but must include
66     * all columns, even if the value is the key. This allows the ContentProvider to request
67     * columns w/o the need to know real column names and create the alias itself.
68     */
69    private static HashMap<String,String> buildColumnMap() {
70        HashMap<String,String> map = new HashMap<String,String>();
71        map.put(KEY_WORD, KEY_WORD);
72        map.put(KEY_DEFINITION, KEY_DEFINITION);
73        map.put(BaseColumns._ID, "rowid AS " +
74                BaseColumns._ID);
75        map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " +
76                SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
77        map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " +
78                SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);
79        return map;
80    }
81
82    /**
83     * Returns a Cursor positioned at the word specified by rowId
84     *
85     * @param rowId id of word to retrieve
86     * @param columns The columns to include, if null then all are included
87     * @return Cursor positioned to matching word, or null if not found.
88     */
89    public Cursor getWord(String rowId, String[] columns) {
90        String selection = "rowid = ?";
91        String[] selectionArgs = new String[] {rowId};
92
93        return query(selection, selectionArgs, columns);
94
95        /* This builds a query that looks like:
96         *     SELECT <columns> FROM <table> WHERE rowid = <rowId>
97         */
98    }
99
100    /**
101     * Returns a Cursor over all words that match the given query
102     *
103     * @param query The string to search for
104     * @param columns The columns to include, if null then all are included
105     * @return Cursor over all words that match, or null if none found.
106     */
107    public Cursor getWordMatches(String query, String[] columns) {
108        String selection = KEY_WORD + " MATCH ?";
109        String[] selectionArgs = new String[] {query+"*"};
110
111        return query(selection, selectionArgs, columns);
112
113        /* This builds a query that looks like:
114         *     SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*'
115         * which is an FTS3 search for the query text (plus a wildcard) inside the word column.
116         *
117         * - "rowid" is the unique id for all rows but we need this value for the "_id" column in
118         *    order for the Adapters to work, so the columns need to make "_id" an alias for "rowid"
119         * - "rowid" also needs to be used by the SUGGEST_COLUMN_INTENT_DATA alias in order
120         *   for suggestions to carry the proper intent data.
121         *   These aliases are defined in the DictionaryProvider when queries are made.
122         * - This can be revised to also search the definition text with FTS3 by changing
123         *   the selection clause to use FTS_VIRTUAL_TABLE instead of KEY_WORD (to search across
124         *   the entire table, but sorting the relevance could be difficult.
125         */
126    }
127
128    /**
129     * Performs a database query.
130     * @param selection The selection clause
131     * @param selectionArgs Selection arguments for "?" components in the selection
132     * @param columns The columns to return
133     * @return A Cursor over all rows matching the query
134     */
135    private Cursor query(String selection, String[] selectionArgs, String[] columns) {
136        /* The SQLiteBuilder provides a map for all possible columns requested to
137         * actual columns in the database, creating a simple column alias mechanism
138         * by which the ContentProvider does not need to know the real column names
139         */
140        SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
141        builder.setTables(FTS_VIRTUAL_TABLE);
142        builder.setProjectionMap(mColumnMap);
143
144        Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
145                columns, selection, selectionArgs, null, null, null);
146
147        if (cursor == null) {
148            return null;
149        } else if (!cursor.moveToFirst()) {
150            cursor.close();
151            return null;
152        }
153        return cursor;
154    }
155
156
157    /**
158     * This creates/opens the database.
159     */
160    private static class DictionaryOpenHelper extends SQLiteOpenHelper {
161
162        private final Context mHelperContext;
163        private SQLiteDatabase mDatabase;
164
165        /* Note that FTS3 does not support column constraints and thus, you cannot
166         * declare a primary key. However, "rowid" is automatically used as a unique
167         * identifier, so when making requests, we will use "_id" as an alias for "rowid"
168         */
169        private static final String FTS_TABLE_CREATE =
170                    "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
171                    " USING fts3 (" +
172                    KEY_WORD + ", " +
173                    KEY_DEFINITION + ");";
174
175        DictionaryOpenHelper(Context context) {
176            super(context, DATABASE_NAME, null, DATABASE_VERSION);
177            mHelperContext = context;
178        }
179
180        @Override
181        public void onCreate(SQLiteDatabase db) {
182            mDatabase = db;
183            mDatabase.execSQL(FTS_TABLE_CREATE);
184            loadDictionary();
185        }
186
187        /**
188         * Starts a thread to load the database table with words
189         */
190        private void loadDictionary() {
191            new Thread(new Runnable() {
192                public void run() {
193                    try {
194                        loadWords();
195                    } catch (IOException e) {
196                        throw new RuntimeException(e);
197                    }
198                }
199            }).start();
200        }
201
202        private void loadWords() throws IOException {
203            Log.d(TAG, "Loading words...");
204            final Resources resources = mHelperContext.getResources();
205            InputStream inputStream = resources.openRawResource(R.raw.definitions);
206            BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
207
208            try {
209                String line;
210                while ((line = reader.readLine()) != null) {
211                    String[] strings = TextUtils.split(line, "-");
212                    if (strings.length < 2) continue;
213                    long id = addWord(strings[0].trim(), strings[1].trim());
214                    if (id < 0) {
215                        Log.e(TAG, "unable to add word: " + strings[0].trim());
216                    }
217                }
218            } finally {
219                reader.close();
220            }
221            Log.d(TAG, "DONE loading words.");
222        }
223
224        /**
225         * Add a word to the dictionary.
226         * @return rowId or -1 if failed
227         */
228        public long addWord(String word, String definition) {
229            ContentValues initialValues = new ContentValues();
230            initialValues.put(KEY_WORD, word);
231            initialValues.put(KEY_DEFINITION, definition);
232
233            return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues);
234        }
235
236        @Override
237        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
238            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
239                    + newVersion + ", which will destroy all old data");
240            db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);
241            onCreate(db);
242        }
243    }
244
245}
246