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 17 package com.example.android.searchabledict; 18 19 import android.app.SearchManager; 20 import android.content.ContentValues; 21 import android.content.Context; 22 import android.content.res.Resources; 23 import android.database.Cursor; 24 import android.database.sqlite.SQLiteDatabase; 25 import android.database.sqlite.SQLiteOpenHelper; 26 import android.database.sqlite.SQLiteQueryBuilder; 27 import android.provider.BaseColumns; 28 import android.text.TextUtils; 29 import android.util.Log; 30 31 import java.io.BufferedReader; 32 import java.io.IOException; 33 import java.io.InputStream; 34 import java.io.InputStreamReader; 35 import 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 */ 41 public 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 */ DictionaryDatabase(Context context)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 */ buildColumnMap()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 */ getWord(String rowId, String[] columns)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 */ getWordMatches(String query, String[] columns)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 */ query(String selection, String[] selectionArgs, String[] columns)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 DictionaryOpenHelper(Context context)175 DictionaryOpenHelper(Context context) { 176 super(context, DATABASE_NAME, null, DATABASE_VERSION); 177 mHelperContext = context; 178 } 179 180 @Override onCreate(SQLiteDatabase db)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 */ loadDictionary()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 loadWords()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 */ addWord(String word, String definition)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 onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)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