| /* |
| * Copyright (C) 2015 The Android Open Source Project |
| * |
| * Licensed under the Apache License, Version 2.0 (the "License"); |
| * you may not use this file except in compliance with the License. |
| * You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| |
| package com.android.tv.dvr.provider; |
| |
| import android.content.ContentValues; |
| import android.content.Context; |
| import android.database.Cursor; |
| import android.database.sqlite.SQLiteDatabase; |
| import android.database.sqlite.SQLiteOpenHelper; |
| import android.database.sqlite.SQLiteQueryBuilder; |
| import android.database.sqlite.SQLiteStatement; |
| import android.provider.BaseColumns; |
| import android.text.TextUtils; |
| import android.util.Log; |
| import com.android.tv.dvr.data.ScheduledRecording; |
| import com.android.tv.dvr.data.SeriesRecording; |
| import com.android.tv.dvr.provider.DvrContract.Schedules; |
| import com.android.tv.dvr.provider.DvrContract.SeriesRecordings; |
| |
| /** A data class for one recorded contents. */ |
| public class DvrDatabaseHelper extends SQLiteOpenHelper { |
| private static final String TAG = "DvrDatabaseHelper"; |
| private static final boolean DEBUG = false; |
| |
| private static final int DATABASE_VERSION = 17; |
| private static final String DB_NAME = "dvr.db"; |
| |
| private static final String SQL_CREATE_SCHEDULES = |
| "CREATE TABLE " |
| + Schedules.TABLE_NAME |
| + "(" |
| + Schedules._ID |
| + " INTEGER PRIMARY KEY AUTOINCREMENT," |
| + Schedules.COLUMN_PRIORITY |
| + " INTEGER DEFAULT " |
| + ScheduledRecording.DEFAULT_PRIORITY |
| + "," |
| + Schedules.COLUMN_TYPE |
| + " TEXT NOT NULL," |
| + Schedules.COLUMN_INPUT_ID |
| + " TEXT NOT NULL," |
| + Schedules.COLUMN_CHANNEL_ID |
| + " INTEGER NOT NULL," |
| + Schedules.COLUMN_PROGRAM_ID |
| + " INTEGER," |
| + Schedules.COLUMN_PROGRAM_TITLE |
| + " TEXT," |
| + Schedules.COLUMN_START_TIME_UTC_MILLIS |
| + " INTEGER NOT NULL," |
| + Schedules.COLUMN_END_TIME_UTC_MILLIS |
| + " INTEGER NOT NULL," |
| + Schedules.COLUMN_SEASON_NUMBER |
| + " TEXT," |
| + Schedules.COLUMN_EPISODE_NUMBER |
| + " TEXT," |
| + Schedules.COLUMN_EPISODE_TITLE |
| + " TEXT," |
| + Schedules.COLUMN_PROGRAM_DESCRIPTION |
| + " TEXT," |
| + Schedules.COLUMN_PROGRAM_LONG_DESCRIPTION |
| + " TEXT," |
| + Schedules.COLUMN_PROGRAM_POST_ART_URI |
| + " TEXT," |
| + Schedules.COLUMN_PROGRAM_THUMBNAIL_URI |
| + " TEXT," |
| + Schedules.COLUMN_STATE |
| + " TEXT NOT NULL," |
| + Schedules.COLUMN_SERIES_RECORDING_ID |
| + " INTEGER," |
| + "FOREIGN KEY(" |
| + Schedules.COLUMN_SERIES_RECORDING_ID |
| + ") " |
| + "REFERENCES " |
| + SeriesRecordings.TABLE_NAME |
| + "(" |
| + SeriesRecordings._ID |
| + ") " |
| + "ON UPDATE CASCADE ON DELETE SET NULL);"; |
| |
| private static final String SQL_DROP_SCHEDULES = "DROP TABLE IF EXISTS " + Schedules.TABLE_NAME; |
| |
| private static final String SQL_CREATE_SERIES_RECORDINGS = |
| "CREATE TABLE " |
| + SeriesRecordings.TABLE_NAME |
| + "(" |
| + SeriesRecordings._ID |
| + " INTEGER PRIMARY KEY AUTOINCREMENT," |
| + SeriesRecordings.COLUMN_PRIORITY |
| + " INTEGER DEFAULT " |
| + SeriesRecording.DEFAULT_PRIORITY |
| + "," |
| + SeriesRecordings.COLUMN_TITLE |
| + " TEXT NOT NULL," |
| + SeriesRecordings.COLUMN_SHORT_DESCRIPTION |
| + " TEXT," |
| + SeriesRecordings.COLUMN_LONG_DESCRIPTION |
| + " TEXT," |
| + SeriesRecordings.COLUMN_INPUT_ID |
| + " TEXT NOT NULL," |
| + SeriesRecordings.COLUMN_CHANNEL_ID |
| + " INTEGER NOT NULL," |
| + SeriesRecordings.COLUMN_SERIES_ID |
| + " TEXT NOT NULL," |
| + SeriesRecordings.COLUMN_START_FROM_SEASON |
| + " INTEGER DEFAULT " |
| + SeriesRecordings.THE_BEGINNING |
| + "," |
| + SeriesRecordings.COLUMN_START_FROM_EPISODE |
| + " INTEGER DEFAULT " |
| + SeriesRecordings.THE_BEGINNING |
| + "," |
| + SeriesRecordings.COLUMN_CHANNEL_OPTION |
| + " TEXT DEFAULT " |
| + SeriesRecordings.OPTION_CHANNEL_ONE |
| + "," |
| + SeriesRecordings.COLUMN_CANONICAL_GENRE |
| + " TEXT," |
| + SeriesRecordings.COLUMN_POSTER_URI |
| + " TEXT," |
| + SeriesRecordings.COLUMN_PHOTO_URI |
| + " TEXT," |
| + SeriesRecordings.COLUMN_STATE |
| + " TEXT)"; |
| |
| private static final String SQL_DROP_SERIES_RECORDINGS = |
| "DROP TABLE IF EXISTS " + SeriesRecordings.TABLE_NAME; |
| |
| private static final int SQL_DATA_TYPE_LONG = 0; |
| private static final int SQL_DATA_TYPE_INT = 1; |
| private static final int SQL_DATA_TYPE_STRING = 2; |
| |
| private static final ColumnInfo[] COLUMNS_SCHEDULES = |
| new ColumnInfo[] { |
| new ColumnInfo(Schedules._ID, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(Schedules.COLUMN_PRIORITY, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(Schedules.COLUMN_TYPE, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_INPUT_ID, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_CHANNEL_ID, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(Schedules.COLUMN_PROGRAM_ID, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(Schedules.COLUMN_PROGRAM_TITLE, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_START_TIME_UTC_MILLIS, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(Schedules.COLUMN_END_TIME_UTC_MILLIS, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(Schedules.COLUMN_SEASON_NUMBER, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_EPISODE_NUMBER, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_EPISODE_TITLE, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_PROGRAM_DESCRIPTION, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_PROGRAM_LONG_DESCRIPTION, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_PROGRAM_POST_ART_URI, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_PROGRAM_THUMBNAIL_URI, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_STATE, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(Schedules.COLUMN_SERIES_RECORDING_ID, SQL_DATA_TYPE_LONG) |
| }; |
| |
| private static final String SQL_INSERT_SCHEDULES = |
| buildInsertSql(Schedules.TABLE_NAME, COLUMNS_SCHEDULES); |
| private static final String SQL_UPDATE_SCHEDULES = |
| buildUpdateSql(Schedules.TABLE_NAME, COLUMNS_SCHEDULES); |
| private static final String SQL_DELETE_SCHEDULES = buildDeleteSql(Schedules.TABLE_NAME); |
| |
| private static final ColumnInfo[] COLUMNS_SERIES_RECORDINGS = |
| new ColumnInfo[] { |
| new ColumnInfo(SeriesRecordings._ID, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(SeriesRecordings.COLUMN_PRIORITY, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(SeriesRecordings.COLUMN_INPUT_ID, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_CHANNEL_ID, SQL_DATA_TYPE_LONG), |
| new ColumnInfo(SeriesRecordings.COLUMN_SERIES_ID, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_TITLE, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_SHORT_DESCRIPTION, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_LONG_DESCRIPTION, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_START_FROM_SEASON, SQL_DATA_TYPE_INT), |
| new ColumnInfo(SeriesRecordings.COLUMN_START_FROM_EPISODE, SQL_DATA_TYPE_INT), |
| new ColumnInfo(SeriesRecordings.COLUMN_CHANNEL_OPTION, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_CANONICAL_GENRE, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_POSTER_URI, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_PHOTO_URI, SQL_DATA_TYPE_STRING), |
| new ColumnInfo(SeriesRecordings.COLUMN_STATE, SQL_DATA_TYPE_STRING) |
| }; |
| |
| private static final String SQL_INSERT_SERIES_RECORDINGS = |
| buildInsertSql(SeriesRecordings.TABLE_NAME, COLUMNS_SERIES_RECORDINGS); |
| private static final String SQL_UPDATE_SERIES_RECORDINGS = |
| buildUpdateSql(SeriesRecordings.TABLE_NAME, COLUMNS_SERIES_RECORDINGS); |
| private static final String SQL_DELETE_SERIES_RECORDINGS = |
| buildDeleteSql(SeriesRecordings.TABLE_NAME); |
| |
| private static String buildInsertSql(String tableName, ColumnInfo[] columns) { |
| StringBuilder sb = new StringBuilder(); |
| sb.append("INSERT INTO ").append(tableName).append(" ("); |
| boolean appendComma = false; |
| for (ColumnInfo columnInfo : columns) { |
| if (appendComma) { |
| sb.append(","); |
| } |
| appendComma = true; |
| sb.append(columnInfo.name); |
| } |
| sb.append(") VALUES (?"); |
| for (int i = 1; i < columns.length; ++i) { |
| sb.append(",?"); |
| } |
| sb.append(")"); |
| return sb.toString(); |
| } |
| |
| private static String buildUpdateSql(String tableName, ColumnInfo[] columns) { |
| StringBuilder sb = new StringBuilder(); |
| sb.append("UPDATE ").append(tableName).append(" SET "); |
| boolean appendComma = false; |
| for (ColumnInfo columnInfo : columns) { |
| if (appendComma) { |
| sb.append(","); |
| } |
| appendComma = true; |
| sb.append(columnInfo.name).append("=?"); |
| } |
| sb.append(" WHERE ").append(BaseColumns._ID).append("=?"); |
| return sb.toString(); |
| } |
| |
| private static String buildDeleteSql(String tableName) { |
| return "DELETE FROM " + tableName + " WHERE " + BaseColumns._ID + "=?"; |
| } |
| |
| public DvrDatabaseHelper(Context context) { |
| super(context.getApplicationContext(), DB_NAME, null, DATABASE_VERSION); |
| } |
| |
| @Override |
| public void onConfigure(SQLiteDatabase db) { |
| db.setForeignKeyConstraintsEnabled(true); |
| } |
| |
| @Override |
| public void onCreate(SQLiteDatabase db) { |
| if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_CREATE_SCHEDULES); |
| db.execSQL(SQL_CREATE_SCHEDULES); |
| if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_CREATE_SERIES_RECORDINGS); |
| db.execSQL(SQL_CREATE_SERIES_RECORDINGS); |
| } |
| |
| @Override |
| public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { |
| if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_DROP_SCHEDULES); |
| db.execSQL(SQL_DROP_SCHEDULES); |
| if (DEBUG) Log.d(TAG, "Executing SQL: " + SQL_DROP_SERIES_RECORDINGS); |
| db.execSQL(SQL_DROP_SERIES_RECORDINGS); |
| onCreate(db); |
| } |
| |
| /** Handles the query request and returns a {@link Cursor}. */ |
| public Cursor query(String tableName, String[] projections) { |
| SQLiteDatabase db = getReadableDatabase(); |
| SQLiteQueryBuilder builder = new SQLiteQueryBuilder(); |
| builder.setTables(tableName); |
| return builder.query(db, projections, null, null, null, null, null); |
| } |
| |
| /** Inserts schedules. */ |
| public void insertSchedules(ScheduledRecording... scheduledRecordings) { |
| SQLiteDatabase db = getWritableDatabase(); |
| SQLiteStatement statement = db.compileStatement(SQL_INSERT_SCHEDULES); |
| db.beginTransaction(); |
| try { |
| for (ScheduledRecording r : scheduledRecordings) { |
| statement.clearBindings(); |
| ContentValues values = ScheduledRecording.toContentValues(r); |
| bindColumns(statement, COLUMNS_SCHEDULES, values); |
| statement.execute(); |
| } |
| db.setTransactionSuccessful(); |
| } finally { |
| db.endTransaction(); |
| } |
| } |
| |
| /** Update schedules. */ |
| public void updateSchedules(ScheduledRecording... scheduledRecordings) { |
| SQLiteDatabase db = getWritableDatabase(); |
| SQLiteStatement statement = db.compileStatement(SQL_UPDATE_SCHEDULES); |
| db.beginTransaction(); |
| try { |
| for (ScheduledRecording r : scheduledRecordings) { |
| statement.clearBindings(); |
| ContentValues values = ScheduledRecording.toContentValues(r); |
| bindColumns(statement, COLUMNS_SCHEDULES, values); |
| statement.bindLong(COLUMNS_SCHEDULES.length + 1, r.getId()); |
| statement.execute(); |
| } |
| db.setTransactionSuccessful(); |
| } finally { |
| db.endTransaction(); |
| } |
| } |
| |
| /** Delete schedules. */ |
| public void deleteSchedules(ScheduledRecording... scheduledRecordings) { |
| SQLiteDatabase db = getWritableDatabase(); |
| SQLiteStatement statement = db.compileStatement(SQL_DELETE_SCHEDULES); |
| db.beginTransaction(); |
| try { |
| for (ScheduledRecording r : scheduledRecordings) { |
| statement.clearBindings(); |
| statement.bindLong(1, r.getId()); |
| statement.execute(); |
| } |
| db.setTransactionSuccessful(); |
| } finally { |
| db.endTransaction(); |
| } |
| } |
| |
| /** Inserts series recordings. */ |
| public void insertSeriesRecordings(SeriesRecording... seriesRecordings) { |
| SQLiteDatabase db = getWritableDatabase(); |
| SQLiteStatement statement = db.compileStatement(SQL_INSERT_SERIES_RECORDINGS); |
| db.beginTransaction(); |
| try { |
| for (SeriesRecording r : seriesRecordings) { |
| statement.clearBindings(); |
| ContentValues values = SeriesRecording.toContentValues(r); |
| bindColumns(statement, COLUMNS_SERIES_RECORDINGS, values); |
| statement.execute(); |
| } |
| db.setTransactionSuccessful(); |
| } finally { |
| db.endTransaction(); |
| } |
| } |
| |
| /** Update series recordings. */ |
| public void updateSeriesRecordings(SeriesRecording... seriesRecordings) { |
| SQLiteDatabase db = getWritableDatabase(); |
| SQLiteStatement statement = db.compileStatement(SQL_UPDATE_SERIES_RECORDINGS); |
| db.beginTransaction(); |
| try { |
| for (SeriesRecording r : seriesRecordings) { |
| statement.clearBindings(); |
| ContentValues values = SeriesRecording.toContentValues(r); |
| bindColumns(statement, COLUMNS_SERIES_RECORDINGS, values); |
| statement.bindLong(COLUMNS_SERIES_RECORDINGS.length + 1, r.getId()); |
| statement.execute(); |
| } |
| db.setTransactionSuccessful(); |
| } finally { |
| db.endTransaction(); |
| } |
| } |
| |
| /** Delete series recordings. */ |
| public void deleteSeriesRecordings(SeriesRecording... seriesRecordings) { |
| SQLiteDatabase db = getWritableDatabase(); |
| SQLiteStatement statement = db.compileStatement(SQL_DELETE_SERIES_RECORDINGS); |
| db.beginTransaction(); |
| try { |
| for (SeriesRecording r : seriesRecordings) { |
| statement.clearBindings(); |
| statement.bindLong(1, r.getId()); |
| statement.execute(); |
| } |
| db.setTransactionSuccessful(); |
| } finally { |
| db.endTransaction(); |
| } |
| } |
| |
| private void bindColumns( |
| SQLiteStatement statement, ColumnInfo[] columns, ContentValues values) { |
| for (int i = 0; i < columns.length; ++i) { |
| ColumnInfo columnInfo = columns[i]; |
| Object value = values.get(columnInfo.name); |
| switch (columnInfo.type) { |
| case SQL_DATA_TYPE_LONG: |
| if (value == null) { |
| statement.bindNull(i + 1); |
| } else { |
| statement.bindLong(i + 1, (Long) value); |
| } |
| break; |
| case SQL_DATA_TYPE_INT: |
| if (value == null) { |
| statement.bindNull(i + 1); |
| } else { |
| statement.bindLong(i + 1, (Integer) value); |
| } |
| break; |
| case SQL_DATA_TYPE_STRING: |
| { |
| if (TextUtils.isEmpty((String) value)) { |
| statement.bindNull(i + 1); |
| } else { |
| statement.bindString(i + 1, (String) value); |
| } |
| break; |
| } |
| } |
| } |
| } |
| |
| private static class ColumnInfo { |
| final String name; |
| final int type; |
| |
| ColumnInfo(String name, int type) { |
| this.name = name; |
| this.type = type; |
| } |
| } |
| } |