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
17package com.android.tv.dvr.provider;
18
19import android.content.ContentValues;
20import android.content.Context;
21import android.database.Cursor;
22import android.database.sqlite.SQLiteDatabase;
23import android.database.sqlite.SQLiteOpenHelper;
24import android.database.sqlite.SQLiteQueryBuilder;
25import android.database.sqlite.SQLiteStatement;
26import android.provider.BaseColumns;
27import android.text.TextUtils;
28import android.util.Log;
29
30import com.android.tv.dvr.data.ScheduledRecording;
31import com.android.tv.dvr.data.SeriesRecording;
32import com.android.tv.dvr.provider.DvrContract.Schedules;
33import com.android.tv.dvr.provider.DvrContract.SeriesRecordings;
34
35/**
36 * A data class for one recorded contents.
37 */
38public 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