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.android.tv.dvr.provider; 18 19 import android.content.ContentValues; 20 import android.content.Context; 21 import android.database.Cursor; 22 import android.database.sqlite.SQLiteDatabase; 23 import android.database.sqlite.SQLiteOpenHelper; 24 import android.database.sqlite.SQLiteQueryBuilder; 25 import android.database.sqlite.SQLiteStatement; 26 import android.provider.BaseColumns; 27 import android.text.TextUtils; 28 import android.util.Log; 29 import com.android.tv.dvr.data.ScheduledRecording; 30 import com.android.tv.dvr.data.SeriesRecording; 31 import com.android.tv.dvr.provider.DvrContract.Schedules; 32 import com.android.tv.dvr.provider.DvrContract.SeriesRecordings; 33 34 /** A data class for one recorded contents. */ 35 public class DvrDatabaseHelper extends SQLiteOpenHelper { 36 private static final String TAG = "DvrDatabaseHelper"; 37 private static final boolean DEBUG = false; 38 39 private static final int DATABASE_VERSION = 18; 40 private static final String DB_NAME = "dvr.db"; 41 42 private static final String SQL_CREATE_SCHEDULES = 43 "CREATE TABLE " 44 + Schedules.TABLE_NAME 45 + "(" 46 + Schedules._ID 47 + " INTEGER PRIMARY KEY AUTOINCREMENT," 48 + Schedules.COLUMN_PRIORITY 49 + " INTEGER DEFAULT " 50 + ScheduledRecording.DEFAULT_PRIORITY 51 + "," 52 + Schedules.COLUMN_TYPE 53 + " TEXT NOT NULL," 54 + Schedules.COLUMN_INPUT_ID 55 + " TEXT NOT NULL," 56 + Schedules.COLUMN_CHANNEL_ID 57 + " INTEGER NOT NULL," 58 + Schedules.COLUMN_PROGRAM_ID 59 + " INTEGER," 60 + Schedules.COLUMN_PROGRAM_TITLE 61 + " TEXT," 62 + Schedules.COLUMN_START_TIME_UTC_MILLIS 63 + " INTEGER NOT NULL," 64 + Schedules.COLUMN_END_TIME_UTC_MILLIS 65 + " INTEGER NOT NULL," 66 + Schedules.COLUMN_SEASON_NUMBER 67 + " TEXT," 68 + Schedules.COLUMN_EPISODE_NUMBER 69 + " TEXT," 70 + Schedules.COLUMN_EPISODE_TITLE 71 + " TEXT," 72 + Schedules.COLUMN_PROGRAM_DESCRIPTION 73 + " TEXT," 74 + Schedules.COLUMN_PROGRAM_LONG_DESCRIPTION 75 + " TEXT," 76 + Schedules.COLUMN_PROGRAM_POST_ART_URI 77 + " TEXT," 78 + Schedules.COLUMN_PROGRAM_THUMBNAIL_URI 79 + " TEXT," 80 + Schedules.COLUMN_STATE 81 + " TEXT NOT NULL," 82 + Schedules.COLUMN_SERIES_RECORDING_ID 83 + " INTEGER," 84 + "FOREIGN KEY(" 85 + Schedules.COLUMN_SERIES_RECORDING_ID 86 + ") " 87 + "REFERENCES " 88 + SeriesRecordings.TABLE_NAME 89 + "(" 90 + SeriesRecordings._ID 91 + ") " 92 + "ON UPDATE CASCADE ON DELETE SET NULL);"; 93 94 private static final String SQL_DROP_SCHEDULES = "DROP TABLE IF EXISTS " + Schedules.TABLE_NAME; 95 96 private static final String SQL_CREATE_SERIES_RECORDINGS = 97 "CREATE TABLE " 98 + SeriesRecordings.TABLE_NAME 99 + "(" 100 + SeriesRecordings._ID 101 + " INTEGER PRIMARY KEY AUTOINCREMENT," 102 + SeriesRecordings.COLUMN_PRIORITY 103 + " INTEGER DEFAULT " 104 + SeriesRecording.DEFAULT_PRIORITY 105 + "," 106 + SeriesRecordings.COLUMN_TITLE 107 + " TEXT NOT NULL," 108 + SeriesRecordings.COLUMN_SHORT_DESCRIPTION 109 + " TEXT," 110 + SeriesRecordings.COLUMN_LONG_DESCRIPTION 111 + " TEXT," 112 + SeriesRecordings.COLUMN_INPUT_ID 113 + " TEXT NOT NULL," 114 + SeriesRecordings.COLUMN_CHANNEL_ID 115 + " INTEGER NOT NULL," 116 + SeriesRecordings.COLUMN_SERIES_ID 117 + " TEXT NOT NULL," 118 + SeriesRecordings.COLUMN_START_FROM_SEASON 119 + " INTEGER DEFAULT " 120 + SeriesRecordings.THE_BEGINNING 121 + "," 122 + SeriesRecordings.COLUMN_START_FROM_EPISODE 123 + " INTEGER DEFAULT " 124 + SeriesRecordings.THE_BEGINNING 125 + "," 126 + SeriesRecordings.COLUMN_CHANNEL_OPTION 127 + " TEXT DEFAULT " 128 + SeriesRecordings.OPTION_CHANNEL_ONE 129 + "," 130 + SeriesRecordings.COLUMN_CANONICAL_GENRE 131 + " TEXT," 132 + SeriesRecordings.COLUMN_POSTER_URI 133 + " TEXT," 134 + SeriesRecordings.COLUMN_PHOTO_URI 135 + " TEXT," 136 + SeriesRecordings.COLUMN_STATE 137 + " TEXT)"; 138 139 private static final String SQL_DROP_SERIES_RECORDINGS = 140 "DROP TABLE IF EXISTS " + SeriesRecordings.TABLE_NAME; 141 142 private static final int SQL_DATA_TYPE_LONG = 0; 143 private static final int SQL_DATA_TYPE_INT = 1; 144 private static final int SQL_DATA_TYPE_STRING = 2; 145 146 private static final ColumnInfo[] COLUMNS_SCHEDULES = 147 new ColumnInfo[] { 148 new ColumnInfo(Schedules._ID, SQL_DATA_TYPE_LONG), 149 new ColumnInfo(Schedules.COLUMN_PRIORITY, SQL_DATA_TYPE_LONG), 150 new ColumnInfo(Schedules.COLUMN_TYPE, SQL_DATA_TYPE_STRING), 151 new ColumnInfo(Schedules.COLUMN_INPUT_ID, SQL_DATA_TYPE_STRING), 152 new ColumnInfo(Schedules.COLUMN_CHANNEL_ID, SQL_DATA_TYPE_LONG), 153 new ColumnInfo(Schedules.COLUMN_PROGRAM_ID, SQL_DATA_TYPE_LONG), 154 new ColumnInfo(Schedules.COLUMN_PROGRAM_TITLE, SQL_DATA_TYPE_STRING), 155 new ColumnInfo(Schedules.COLUMN_START_TIME_UTC_MILLIS, SQL_DATA_TYPE_LONG), 156 new ColumnInfo(Schedules.COLUMN_END_TIME_UTC_MILLIS, SQL_DATA_TYPE_LONG), 157 new ColumnInfo(Schedules.COLUMN_SEASON_NUMBER, SQL_DATA_TYPE_STRING), 158 new ColumnInfo(Schedules.COLUMN_EPISODE_NUMBER, SQL_DATA_TYPE_STRING), 159 new ColumnInfo(Schedules.COLUMN_EPISODE_TITLE, SQL_DATA_TYPE_STRING), 160 new ColumnInfo(Schedules.COLUMN_PROGRAM_DESCRIPTION, SQL_DATA_TYPE_STRING), 161 new ColumnInfo(Schedules.COLUMN_PROGRAM_LONG_DESCRIPTION, SQL_DATA_TYPE_STRING), 162 new ColumnInfo(Schedules.COLUMN_PROGRAM_POST_ART_URI, SQL_DATA_TYPE_STRING), 163 new ColumnInfo(Schedules.COLUMN_PROGRAM_THUMBNAIL_URI, SQL_DATA_TYPE_STRING), 164 new ColumnInfo(Schedules.COLUMN_STATE, SQL_DATA_TYPE_STRING), 165 new ColumnInfo(Schedules.COLUMN_FAILED_REASON, SQL_DATA_TYPE_STRING), 166 new ColumnInfo(Schedules.COLUMN_SERIES_RECORDING_ID, SQL_DATA_TYPE_LONG) 167 }; 168 169 private static final String SQL_INSERT_SCHEDULES = 170 buildInsertSql(Schedules.TABLE_NAME, COLUMNS_SCHEDULES); 171 private static final String SQL_UPDATE_SCHEDULES = 172 buildUpdateSql(Schedules.TABLE_NAME, COLUMNS_SCHEDULES); 173 private static final String SQL_DELETE_SCHEDULES = buildDeleteSql(Schedules.TABLE_NAME); 174 175 private static final ColumnInfo[] COLUMNS_SERIES_RECORDINGS = 176 new ColumnInfo[] { 177 new ColumnInfo(SeriesRecordings._ID, SQL_DATA_TYPE_LONG), 178 new ColumnInfo(SeriesRecordings.COLUMN_PRIORITY, SQL_DATA_TYPE_LONG), 179 new ColumnInfo(SeriesRecordings.COLUMN_INPUT_ID, SQL_DATA_TYPE_STRING), 180 new ColumnInfo(SeriesRecordings.COLUMN_CHANNEL_ID, SQL_DATA_TYPE_LONG), 181 new ColumnInfo(SeriesRecordings.COLUMN_SERIES_ID, SQL_DATA_TYPE_STRING), 182 new ColumnInfo(SeriesRecordings.COLUMN_TITLE, SQL_DATA_TYPE_STRING), 183 new ColumnInfo(SeriesRecordings.COLUMN_SHORT_DESCRIPTION, SQL_DATA_TYPE_STRING), 184 new ColumnInfo(SeriesRecordings.COLUMN_LONG_DESCRIPTION, SQL_DATA_TYPE_STRING), 185 new ColumnInfo(SeriesRecordings.COLUMN_START_FROM_SEASON, SQL_DATA_TYPE_INT), 186 new ColumnInfo(SeriesRecordings.COLUMN_START_FROM_EPISODE, SQL_DATA_TYPE_INT), 187 new ColumnInfo(SeriesRecordings.COLUMN_CHANNEL_OPTION, SQL_DATA_TYPE_STRING), 188 new ColumnInfo(SeriesRecordings.COLUMN_CANONICAL_GENRE, SQL_DATA_TYPE_STRING), 189 new ColumnInfo(SeriesRecordings.COLUMN_POSTER_URI, SQL_DATA_TYPE_STRING), 190 new ColumnInfo(SeriesRecordings.COLUMN_PHOTO_URI, SQL_DATA_TYPE_STRING), 191 new ColumnInfo(SeriesRecordings.COLUMN_STATE, SQL_DATA_TYPE_STRING) 192 }; 193 194 private static final String SQL_INSERT_SERIES_RECORDINGS = 195 buildInsertSql(SeriesRecordings.TABLE_NAME, COLUMNS_SERIES_RECORDINGS); 196 private static final String SQL_UPDATE_SERIES_RECORDINGS = 197 buildUpdateSql(SeriesRecordings.TABLE_NAME, COLUMNS_SERIES_RECORDINGS); 198 private static final String SQL_DELETE_SERIES_RECORDINGS = 199 buildDeleteSql(SeriesRecordings.TABLE_NAME); 200 buildInsertSql(String tableName, ColumnInfo[] columns)201 private static String buildInsertSql(String tableName, ColumnInfo[] columns) { 202 StringBuilder sb = new StringBuilder(); 203 sb.append("INSERT INTO ").append(tableName).append(" ("); 204 boolean appendComma = false; 205 for (ColumnInfo columnInfo : columns) { 206 if (appendComma) { 207 sb.append(","); 208 } 209 appendComma = true; 210 sb.append(columnInfo.name); 211 } 212 sb.append(") VALUES (?"); 213 for (int i = 1; i < columns.length; ++i) { 214 sb.append(",?"); 215 } 216 sb.append(")"); 217 return sb.toString(); 218 } 219 buildUpdateSql(String tableName, ColumnInfo[] columns)220 private static String buildUpdateSql(String tableName, ColumnInfo[] columns) { 221 StringBuilder sb = new StringBuilder(); 222 sb.append("UPDATE ").append(tableName).append(" SET "); 223 boolean appendComma = false; 224 for (ColumnInfo columnInfo : columns) { 225 if (appendComma) { 226 sb.append(","); 227 } 228 appendComma = true; 229 sb.append(columnInfo.name).append("=?"); 230 } 231 sb.append(" WHERE ").append(BaseColumns._ID).append("=?"); 232 return sb.toString(); 233 } 234 buildDeleteSql(String tableName)235 private static String buildDeleteSql(String tableName) { 236 return "DELETE FROM " + tableName + " WHERE " + BaseColumns._ID + "=?"; 237 } 238 DvrDatabaseHelper(Context context)239 public DvrDatabaseHelper(Context context) { 240 super(context.getApplicationContext(), DB_NAME, null, DATABASE_VERSION); 241 } 242 243 @Override onConfigure(SQLiteDatabase db)244 public void onConfigure(SQLiteDatabase db) { 245 db.setForeignKeyConstraintsEnabled(true); 246 } 247 248 @Override onCreate(SQLiteDatabase db)249 public void onCreate(SQLiteDatabase db) { 250 if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_CREATE_SCHEDULES); 251 db.execSQL(SQL_CREATE_SCHEDULES); 252 if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_CREATE_SERIES_RECORDINGS); 253 db.execSQL(SQL_CREATE_SERIES_RECORDINGS); 254 } 255 256 @Override onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)257 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 258 if (oldVersion < 17) { 259 if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_DROP_SCHEDULES); 260 db.execSQL(SQL_DROP_SCHEDULES); 261 if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_DROP_SERIES_RECORDINGS); 262 db.execSQL(SQL_DROP_SERIES_RECORDINGS); 263 onCreate(db); 264 } 265 if (oldVersion < 18) { 266 db.execSQL("ALTER TABLE " + Schedules.TABLE_NAME + " ADD COLUMN " 267 + Schedules.COLUMN_FAILED_REASON + " TEXT DEFAULT null;"); 268 } 269 } 270 271 /** Handles the query request and returns a {@link Cursor}. */ query(String tableName, String[] projections)272 public Cursor query(String tableName, String[] projections) { 273 SQLiteDatabase db = getReadableDatabase(); 274 SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); 275 builder.setTables(tableName); 276 return builder.query(db, projections, null, null, null, null, null); 277 } 278 279 /** Inserts schedules. */ insertSchedules(ScheduledRecording... scheduledRecordings)280 public void insertSchedules(ScheduledRecording... scheduledRecordings) { 281 SQLiteDatabase db = getWritableDatabase(); 282 SQLiteStatement statement = db.compileStatement(SQL_INSERT_SCHEDULES); 283 db.beginTransaction(); 284 try { 285 for (ScheduledRecording r : scheduledRecordings) { 286 statement.clearBindings(); 287 ContentValues values = ScheduledRecording.toContentValues(r); 288 bindColumns(statement, COLUMNS_SCHEDULES, values); 289 statement.execute(); 290 } 291 db.setTransactionSuccessful(); 292 } finally { 293 db.endTransaction(); 294 } 295 } 296 297 /** Update schedules. */ updateSchedules(ScheduledRecording... scheduledRecordings)298 public void updateSchedules(ScheduledRecording... scheduledRecordings) { 299 SQLiteDatabase db = getWritableDatabase(); 300 SQLiteStatement statement = db.compileStatement(SQL_UPDATE_SCHEDULES); 301 db.beginTransaction(); 302 try { 303 for (ScheduledRecording r : scheduledRecordings) { 304 statement.clearBindings(); 305 ContentValues values = ScheduledRecording.toContentValues(r); 306 bindColumns(statement, COLUMNS_SCHEDULES, values); 307 statement.bindLong(COLUMNS_SCHEDULES.length + 1, r.getId()); 308 statement.execute(); 309 } 310 db.setTransactionSuccessful(); 311 } finally { 312 db.endTransaction(); 313 } 314 } 315 316 /** Delete schedules. */ deleteSchedules(ScheduledRecording... scheduledRecordings)317 public void deleteSchedules(ScheduledRecording... scheduledRecordings) { 318 SQLiteDatabase db = getWritableDatabase(); 319 SQLiteStatement statement = db.compileStatement(SQL_DELETE_SCHEDULES); 320 db.beginTransaction(); 321 try { 322 for (ScheduledRecording r : scheduledRecordings) { 323 statement.clearBindings(); 324 statement.bindLong(1, r.getId()); 325 statement.execute(); 326 } 327 db.setTransactionSuccessful(); 328 } finally { 329 db.endTransaction(); 330 } 331 } 332 333 /** Inserts series recordings. */ insertSeriesRecordings(SeriesRecording... seriesRecordings)334 public void insertSeriesRecordings(SeriesRecording... seriesRecordings) { 335 SQLiteDatabase db = getWritableDatabase(); 336 SQLiteStatement statement = db.compileStatement(SQL_INSERT_SERIES_RECORDINGS); 337 db.beginTransaction(); 338 try { 339 for (SeriesRecording r : seriesRecordings) { 340 statement.clearBindings(); 341 ContentValues values = SeriesRecording.toContentValues(r); 342 bindColumns(statement, COLUMNS_SERIES_RECORDINGS, values); 343 statement.execute(); 344 } 345 db.setTransactionSuccessful(); 346 } finally { 347 db.endTransaction(); 348 } 349 } 350 351 /** Update series recordings. */ updateSeriesRecordings(SeriesRecording... seriesRecordings)352 public void updateSeriesRecordings(SeriesRecording... seriesRecordings) { 353 SQLiteDatabase db = getWritableDatabase(); 354 SQLiteStatement statement = db.compileStatement(SQL_UPDATE_SERIES_RECORDINGS); 355 db.beginTransaction(); 356 try { 357 for (SeriesRecording r : seriesRecordings) { 358 statement.clearBindings(); 359 ContentValues values = SeriesRecording.toContentValues(r); 360 bindColumns(statement, COLUMNS_SERIES_RECORDINGS, values); 361 statement.bindLong(COLUMNS_SERIES_RECORDINGS.length + 1, r.getId()); 362 statement.execute(); 363 } 364 db.setTransactionSuccessful(); 365 } finally { 366 db.endTransaction(); 367 } 368 } 369 370 /** Delete series recordings. */ deleteSeriesRecordings(SeriesRecording... seriesRecordings)371 public void deleteSeriesRecordings(SeriesRecording... seriesRecordings) { 372 SQLiteDatabase db = getWritableDatabase(); 373 SQLiteStatement statement = db.compileStatement(SQL_DELETE_SERIES_RECORDINGS); 374 db.beginTransaction(); 375 try { 376 for (SeriesRecording r : seriesRecordings) { 377 statement.clearBindings(); 378 statement.bindLong(1, r.getId()); 379 statement.execute(); 380 } 381 db.setTransactionSuccessful(); 382 } finally { 383 db.endTransaction(); 384 } 385 } 386 bindColumns( SQLiteStatement statement, ColumnInfo[] columns, ContentValues values)387 private void bindColumns( 388 SQLiteStatement statement, ColumnInfo[] columns, ContentValues values) { 389 for (int i = 0; i < columns.length; ++i) { 390 ColumnInfo columnInfo = columns[i]; 391 Object value = values.get(columnInfo.name); 392 switch (columnInfo.type) { 393 case SQL_DATA_TYPE_LONG: 394 if (value == null) { 395 statement.bindNull(i + 1); 396 } else { 397 statement.bindLong(i + 1, (Long) value); 398 } 399 break; 400 case SQL_DATA_TYPE_INT: 401 if (value == null) { 402 statement.bindNull(i + 1); 403 } else { 404 statement.bindLong(i + 1, (Integer) value); 405 } 406 break; 407 case SQL_DATA_TYPE_STRING: 408 { 409 if (TextUtils.isEmpty((String) value)) { 410 statement.bindNull(i + 1); 411 } else { 412 statement.bindString(i + 1, (String) value); 413 } 414 break; 415 } 416 } 417 } 418 } 419 420 private static class ColumnInfo { 421 final String name; 422 final int type; 423 ColumnInfo(String name, int type)424 ColumnInfo(String name, int type) { 425 this.name = name; 426 this.type = type; 427 } 428 } 429 } 430