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