1 /* 2 * Copyright (C) 2015 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.tvleanback.data; 18 19 import android.app.SearchManager; 20 import android.content.ContentValues; 21 import android.content.Context; 22 import android.database.Cursor; 23 import android.database.sqlite.SQLiteDatabase; 24 import android.database.sqlite.SQLiteOpenHelper; 25 import android.database.sqlite.SQLiteQueryBuilder; 26 import android.media.Rating; 27 import android.provider.BaseColumns; 28 import android.util.Log; 29 30 import com.example.android.tvleanback.R; 31 import com.example.android.tvleanback.model.Movie; 32 33 import org.json.JSONException; 34 35 import java.io.IOException; 36 import java.util.HashMap; 37 import java.util.List; 38 import java.util.Map; 39 40 /** 41 * Contains logic to return specific words from the video database, and 42 * load the video database table when it needs to be created. 43 */ 44 public class VideoDatabase { 45 //The columns we'll include in the video database table 46 public static final String KEY_NAME = SearchManager.SUGGEST_COLUMN_TEXT_1; 47 public static final String KEY_DESCRIPTION = SearchManager.SUGGEST_COLUMN_TEXT_2; 48 public static final String KEY_ICON = SearchManager.SUGGEST_COLUMN_RESULT_CARD_IMAGE; 49 public static final String KEY_DATA_TYPE = SearchManager.SUGGEST_COLUMN_CONTENT_TYPE; 50 public static final String KEY_IS_LIVE = SearchManager.SUGGEST_COLUMN_IS_LIVE; 51 public static final String KEY_VIDEO_WIDTH = SearchManager.SUGGEST_COLUMN_VIDEO_WIDTH; 52 public static final String KEY_VIDEO_HEIGHT = SearchManager.SUGGEST_COLUMN_VIDEO_HEIGHT; 53 public static final String KEY_AUDIO_CHANNEL_CONFIG = 54 SearchManager.SUGGEST_COLUMN_AUDIO_CHANNEL_CONFIG; 55 public static final String KEY_PURCHASE_PRICE = SearchManager.SUGGEST_COLUMN_PURCHASE_PRICE; 56 public static final String KEY_RENTAL_PRICE = SearchManager.SUGGEST_COLUMN_RENTAL_PRICE; 57 public static final String KEY_RATING_STYLE = SearchManager.SUGGEST_COLUMN_RATING_STYLE; 58 public static final String KEY_RATING_SCORE = SearchManager.SUGGEST_COLUMN_RATING_SCORE; 59 public static final String KEY_PRODUCTION_YEAR = SearchManager.SUGGEST_COLUMN_PRODUCTION_YEAR; 60 public static final String KEY_COLUMN_DURATION = SearchManager.SUGGEST_COLUMN_DURATION; 61 public static final String KEY_ACTION = SearchManager.SUGGEST_COLUMN_INTENT_ACTION; 62 private static final String TAG = "VideoDatabase"; 63 private static final String DATABASE_NAME = "video_database_leanback"; 64 private static final String FTS_VIRTUAL_TABLE = "Leanback_table"; 65 private static final int DATABASE_VERSION = 2; 66 private static final HashMap<String, String> COLUMN_MAP = buildColumnMap(); 67 private static int CARD_WIDTH = 313; 68 private static int CARD_HEIGHT = 176; 69 private final VideoDatabaseOpenHelper mDatabaseOpenHelper; 70 71 /** 72 * Constructor 73 * 74 * @param context The Context within which to work, used to create the DB 75 */ VideoDatabase(Context context)76 public VideoDatabase(Context context) { 77 mDatabaseOpenHelper = new VideoDatabaseOpenHelper(context); 78 } 79 80 /** 81 * Builds a map for all columns that may be requested, which will be given to the 82 * SQLiteQueryBuilder. This is a good way to define aliases for column names, but must include 83 * all columns, even if the value is the key. This allows the ContentProvider to request 84 * columns w/o the need to know real column names and create the alias itself. 85 */ buildColumnMap()86 private static HashMap<String, String> buildColumnMap() { 87 HashMap<String, String> map = new HashMap<String, String>(); 88 map.put(KEY_NAME, KEY_NAME); 89 map.put(KEY_DESCRIPTION, KEY_DESCRIPTION); 90 map.put(KEY_ICON, KEY_ICON); 91 map.put(KEY_DATA_TYPE, KEY_DATA_TYPE); 92 map.put(KEY_IS_LIVE, KEY_IS_LIVE); 93 map.put(KEY_VIDEO_WIDTH, KEY_VIDEO_WIDTH); 94 map.put(KEY_VIDEO_HEIGHT, KEY_VIDEO_HEIGHT); 95 map.put(KEY_AUDIO_CHANNEL_CONFIG, KEY_AUDIO_CHANNEL_CONFIG); 96 map.put(KEY_PURCHASE_PRICE, KEY_PURCHASE_PRICE); 97 map.put(KEY_RENTAL_PRICE, KEY_RENTAL_PRICE); 98 map.put(KEY_RATING_STYLE, KEY_RATING_STYLE); 99 map.put(KEY_RATING_SCORE, KEY_RATING_SCORE); 100 map.put(KEY_PRODUCTION_YEAR, KEY_PRODUCTION_YEAR); 101 map.put(KEY_COLUMN_DURATION, KEY_COLUMN_DURATION); 102 map.put(KEY_ACTION, KEY_ACTION); 103 map.put(BaseColumns._ID, "rowid AS " + 104 BaseColumns._ID); 105 map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " + 106 SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID); 107 map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " + 108 SearchManager.SUGGEST_COLUMN_SHORTCUT_ID); 109 return map; 110 } 111 112 /** 113 * Returns a Cursor positioned at the word specified by rowId 114 * 115 * @param rowId id of word to retrieve 116 * @param columns The columns to include, if null then all are included 117 * @return Cursor positioned to matching word, or null if not found. 118 */ getWord(String rowId, String[] columns)119 public Cursor getWord(String rowId, String[] columns) { 120 /* This builds a query that looks like: 121 * SELECT <columns> FROM <table> WHERE rowid = <rowId> 122 */ 123 String selection = "rowid = ?"; 124 String[] selectionArgs = new String[]{rowId}; 125 126 return query(selection, selectionArgs, columns); 127 } 128 129 /** 130 * Returns a Cursor over all words that match the first letter of the given query 131 * 132 * @param query The string to search for 133 * @param columns The columns to include, if null then all are included 134 * @return Cursor over all words that match, or null if none found. 135 */ getWordMatch(String query, String[] columns)136 public Cursor getWordMatch(String query, String[] columns) { 137 /* This builds a query that looks like: 138 * SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*' 139 * which is an FTS3 search for the query text (plus a wildcard) inside the word column. 140 * 141 * - "rowid" is the unique id for all rows but we need this value for the "_id" column in 142 * order for the Adapters to work, so the columns need to make "_id" an alias for "rowid" 143 * - "rowid" also needs to be used by the SUGGEST_COLUMN_INTENT_DATA alias in order 144 * for suggestions to carry the proper intent data.SearchManager 145 * These aliases are defined in the VideoProvider when queries are made. 146 * - This can be revised to also search the definition text with FTS3 by changing 147 * the selection clause to use FTS_VIRTUAL_TABLE instead of KEY_WORD (to search across 148 * the entire table, but sorting the relevance could be difficult. 149 */ 150 String selection = KEY_NAME + " MATCH ?"; 151 String[] selectionArgs = new String[]{query + "*"}; 152 153 return query(selection, selectionArgs, columns); 154 } 155 156 /** 157 * Performs a database query. 158 * 159 * @param selection The selection clause 160 * @param selectionArgs Selection arguments for "?" components in the selection 161 * @param columns The columns to return 162 * @return A Cursor over all rows matching the query 163 */ query(String selection, String[] selectionArgs, String[] columns)164 private Cursor query(String selection, String[] selectionArgs, String[] columns) { 165 /* The SQLiteBuilder provides a map for all possible columns requested to 166 * actual columns in the database, creating a simple column alias mechanism 167 * by which the ContentProvider does not need to know the real column names 168 */ 169 SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 170 builder.setTables(FTS_VIRTUAL_TABLE); 171 builder.setProjectionMap(COLUMN_MAP); 172 173 Cursor cursor = new PaginatedCursor(builder.query(mDatabaseOpenHelper.getReadableDatabase(), 174 columns, selection, selectionArgs, null, null, null)); 175 176 if (cursor == null) { 177 return null; 178 } else if (!cursor.moveToFirst()) { 179 cursor.close(); 180 return null; 181 } 182 return cursor; 183 } 184 185 /** 186 * This creates/opens the database. 187 */ 188 private static class VideoDatabaseOpenHelper extends SQLiteOpenHelper { 189 190 private final Context mHelperContext; 191 private SQLiteDatabase mDatabase; 192 VideoDatabaseOpenHelper(Context context)193 VideoDatabaseOpenHelper(Context context) { 194 super(context, DATABASE_NAME, null, DATABASE_VERSION); 195 mHelperContext = context; 196 } 197 198 /* Note that FTS3 does not support column constraints and thus, you cannot 199 * declare a primary key. However, "rowid" is automatically used as a unique 200 * identifier, so when making requests, we will use "_id" as an alias for "rowid" 201 */ 202 private static final String FTS_TABLE_CREATE = 203 "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE + 204 " USING fts3 (" + 205 KEY_NAME + ", " + 206 KEY_DESCRIPTION + "," + 207 KEY_ICON + "," + 208 KEY_DATA_TYPE + "," + 209 KEY_IS_LIVE + "," + 210 KEY_VIDEO_WIDTH + "," + 211 KEY_VIDEO_HEIGHT + "," + 212 KEY_AUDIO_CHANNEL_CONFIG + "," + 213 KEY_PURCHASE_PRICE + "," + 214 KEY_RENTAL_PRICE + "," + 215 KEY_RATING_STYLE + "," + 216 KEY_RATING_SCORE + "," + 217 KEY_PRODUCTION_YEAR + "," + 218 KEY_COLUMN_DURATION + "," + 219 KEY_ACTION + ");"; 220 221 @Override onCreate(SQLiteDatabase db)222 public void onCreate(SQLiteDatabase db) { 223 mDatabase = db; 224 mDatabase.execSQL(FTS_TABLE_CREATE); 225 loadDatabase(); 226 } 227 228 /** 229 * Starts a thread to load the database table with words 230 */ loadDatabase()231 private void loadDatabase() { 232 new Thread(new Runnable() { 233 public void run() { 234 try { 235 loadMovies(); 236 } catch (IOException e) { 237 throw new RuntimeException(e); 238 } 239 } 240 }).start(); 241 } 242 loadMovies()243 private void loadMovies() throws IOException { 244 Log.d(TAG, "Loading movies..."); 245 246 HashMap<String, List<Movie>> movies = null; 247 try { 248 VideoProvider.setContext(mHelperContext); 249 movies = VideoProvider.buildMedia(mHelperContext, 250 mHelperContext.getResources().getString(R.string.catalog_url)); 251 } catch (JSONException e) { 252 Log.e(TAG, "JSon Exception when loading movie", e); 253 } 254 255 for (Map.Entry<String, List<Movie>> entry : movies.entrySet()) { 256 List<Movie> list = entry.getValue(); 257 for (Movie movie : list) { 258 long id = addMovie(movie); 259 if (id < 0) { 260 Log.e(TAG, "unable to add movie: " + movie.toString()); 261 } 262 } 263 } 264 // add dummy movies to illustrate action deep link in search detail 265 // Android TV Search requires that the media’s title, MIME type, production year, 266 // and duration all match exactly to those found from Google’s servers. 267 addMovieForDeepLink(mHelperContext.getString(R.string.noah_title), 268 mHelperContext.getString(R.string.noah_description), 269 R.drawable.noah, 270 8280000, 271 "2014"); 272 addMovieForDeepLink(mHelperContext.getString(R.string.dragon2_title), 273 mHelperContext.getString(R.string.dragon2_description), 274 R.drawable.dragon2, 275 6300000, 276 "2014"); 277 addMovieForDeepLink(mHelperContext.getString(R.string.maleficent_title), 278 mHelperContext.getString(R.string.maleficent_description), 279 R.drawable.maleficent, 280 5820000, 281 "2014"); 282 } 283 284 /** 285 * Add a movie to the database. 286 * 287 * @return rowId or -1 if failed 288 */ addMovie(Movie movie)289 public long addMovie(Movie movie) { 290 ContentValues initialValues = new ContentValues(); 291 initialValues.put(KEY_NAME, movie.getTitle()); 292 initialValues.put(KEY_DESCRIPTION, movie.getDescription()); 293 initialValues.put(KEY_ICON, movie.getCardImageUrl()); 294 initialValues.put(KEY_DATA_TYPE, "video/mp4"); 295 initialValues.put(KEY_IS_LIVE, false); 296 initialValues.put(KEY_VIDEO_WIDTH, CARD_WIDTH); 297 initialValues.put(KEY_VIDEO_HEIGHT, CARD_HEIGHT); 298 initialValues.put(KEY_AUDIO_CHANNEL_CONFIG, "2.0"); 299 initialValues.put(KEY_PURCHASE_PRICE, mHelperContext.getString(R.string.buy_2)); 300 initialValues.put(KEY_RENTAL_PRICE, mHelperContext.getString(R.string.rent_2)); 301 initialValues.put(KEY_RATING_STYLE, Rating.RATING_5_STARS); 302 initialValues.put(KEY_RATING_SCORE, 3.5f); 303 initialValues.put(KEY_PRODUCTION_YEAR, 2014); 304 initialValues.put(KEY_COLUMN_DURATION, 0); 305 initialValues.put(KEY_ACTION, mHelperContext.getString(R.string.global_search)); 306 return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues); 307 } 308 309 /** 310 * Add an entry to the database for dummy deep link. 311 * 312 * @return rowId or -1 if failed 313 */ addMovieForDeepLink(String title, String description, int icon, long duration, String production_year)314 public long addMovieForDeepLink(String title, String description, int icon, long duration, String production_year) { 315 ContentValues initialValues = new ContentValues(); 316 initialValues.put(KEY_NAME, title); 317 initialValues.put(KEY_DESCRIPTION, description); 318 initialValues.put(KEY_ICON, icon); 319 initialValues.put(KEY_DATA_TYPE, "video/mp4"); 320 initialValues.put(KEY_IS_LIVE, false); 321 initialValues.put(KEY_VIDEO_WIDTH, 1280); 322 initialValues.put(KEY_VIDEO_HEIGHT, 720); 323 initialValues.put(KEY_AUDIO_CHANNEL_CONFIG, "2.0"); 324 initialValues.put(KEY_PURCHASE_PRICE, "Free"); 325 initialValues.put(KEY_RENTAL_PRICE, "Free"); 326 initialValues.put(KEY_RATING_STYLE, Rating.RATING_5_STARS); 327 initialValues.put(KEY_RATING_SCORE, 3.5f); 328 initialValues.put(KEY_PRODUCTION_YEAR, production_year); 329 initialValues.put(KEY_COLUMN_DURATION, duration); 330 return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues); 331 } 332 333 @Override onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)334 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 335 Log.w(TAG, "Upgrading database from version " + oldVersion + " to " 336 + newVersion + ", which will destroy all old data"); 337 db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE); 338 onCreate(db); 339 } 340 341 342 } 343 344 } 345