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 
buildInsertSql(String tableName, ColumnInfo[] columns)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 
buildUpdateSql(String tableName, ColumnInfo[] columns)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 
buildDeleteSql(String tableName)186     private static String buildDeleteSql(String tableName) {
187         return "DELETE FROM " + tableName + " WHERE " + BaseColumns._ID + "=?";
188     }
DvrDatabaseHelper(Context context)189     public DvrDatabaseHelper(Context context) {
190         super(context.getApplicationContext(), DB_NAME, null, DATABASE_VERSION);
191     }
192 
193     @Override
onConfigure(SQLiteDatabase db)194     public void onConfigure(SQLiteDatabase db) {
195         db.setForeignKeyConstraintsEnabled(true);
196     }
197 
198     @Override
onCreate(SQLiteDatabase db)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
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)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      */
query(String tableName, String[] projections)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      */
insertSchedules(ScheduledRecording... scheduledRecordings)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      */
updateSchedules(ScheduledRecording... scheduledRecordings)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      */
deleteSchedules(ScheduledRecording... scheduledRecordings)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      */
insertSeriesRecordings(SeriesRecording... seriesRecordings)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      */
updateSeriesRecordings(SeriesRecording... seriesRecordings)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      */
deleteSeriesRecordings(SeriesRecording... seriesRecordings)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 
bindColumns(SQLiteStatement statement, ColumnInfo[] columns, ContentValues values)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 
ColumnInfo(String name, int type)381         ColumnInfo(String name, int type) {
382             this.name = name;
383             this.type = type;
384         }
385     }
386 }
387