1 /*
2  * Copyright (C) 2013 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.deskclock.provider;
18 
19 import android.content.ContentValues;
20 import android.content.Context;
21 import android.database.Cursor;
22 import android.database.SQLException;
23 import android.database.sqlite.SQLiteDatabase;
24 import android.database.sqlite.SQLiteOpenHelper;
25 import android.net.Uri;
26 import android.text.TextUtils;
27 
28 import com.android.deskclock.LogUtils;
29 import com.android.deskclock.data.Weekdays;
30 
31 import java.util.Calendar;
32 
33 /**
34  * Helper class for opening the database from multiple providers.  Also provides
35  * some common functionality.
36  */
37 class ClockDatabaseHelper extends SQLiteOpenHelper {
38     /**
39      * Original Clock Database.
40      **/
41     private static final int VERSION_5 = 5;
42 
43     /**
44      * Added alarm_instances table
45      * Added selected_cities table
46      * Added DELETE_AFTER_USE column to alarms table
47      */
48     private static final int VERSION_6 = 6;
49 
50     /**
51      * Added alarm settings to instance table.
52      */
53     private static final int VERSION_7 = 7;
54 
55     /**
56      * Removed selected_cities table.
57      */
58     private static final int VERSION_8 = 8;
59 
60     // This creates a default alarm at 8:30 for every Mon,Tue,Wed,Thu,Fri
61     private static final String DEFAULT_ALARM_1 = "(8, 30, 31, 0, 1, '', NULL, 0);";
62 
63     // This creates a default alarm at 9:30 for every Sat,Sun
64     private static final String DEFAULT_ALARM_2 = "(9, 00, 96, 0, 1, '', NULL, 0);";
65 
66     // Database and table names
67     static final String DATABASE_NAME = "alarms.db";
68     static final String OLD_ALARMS_TABLE_NAME = "alarms";
69     static final String ALARMS_TABLE_NAME = "alarm_templates";
70     static final String INSTANCES_TABLE_NAME = "alarm_instances";
71     private static final String SELECTED_CITIES_TABLE_NAME = "selected_cities";
72 
createAlarmsTable(SQLiteDatabase db)73     private static void createAlarmsTable(SQLiteDatabase db) {
74         db.execSQL("CREATE TABLE " + ALARMS_TABLE_NAME + " (" +
75                 ClockContract.AlarmsColumns._ID + " INTEGER PRIMARY KEY," +
76                 ClockContract.AlarmsColumns.HOUR + " INTEGER NOT NULL, " +
77                 ClockContract.AlarmsColumns.MINUTES + " INTEGER NOT NULL, " +
78                 ClockContract.AlarmsColumns.DAYS_OF_WEEK + " INTEGER NOT NULL, " +
79                 ClockContract.AlarmsColumns.ENABLED + " INTEGER NOT NULL, " +
80                 ClockContract.AlarmsColumns.VIBRATE + " INTEGER NOT NULL, " +
81                 ClockContract.AlarmsColumns.LABEL + " TEXT NOT NULL, " +
82                 ClockContract.AlarmsColumns.RINGTONE + " TEXT, " +
83                 ClockContract.AlarmsColumns.DELETE_AFTER_USE + " INTEGER NOT NULL DEFAULT 0);");
84         LogUtils.i("Alarms Table created");
85     }
86 
createInstanceTable(SQLiteDatabase db)87     private static void createInstanceTable(SQLiteDatabase db) {
88         db.execSQL("CREATE TABLE " + INSTANCES_TABLE_NAME + " (" +
89                 ClockContract.InstancesColumns._ID + " INTEGER PRIMARY KEY," +
90                 ClockContract.InstancesColumns.YEAR + " INTEGER NOT NULL, " +
91                 ClockContract.InstancesColumns.MONTH + " INTEGER NOT NULL, " +
92                 ClockContract.InstancesColumns.DAY + " INTEGER NOT NULL, " +
93                 ClockContract.InstancesColumns.HOUR + " INTEGER NOT NULL, " +
94                 ClockContract.InstancesColumns.MINUTES + " INTEGER NOT NULL, " +
95                 ClockContract.InstancesColumns.VIBRATE + " INTEGER NOT NULL, " +
96                 ClockContract.InstancesColumns.LABEL + " TEXT NOT NULL, " +
97                 ClockContract.InstancesColumns.RINGTONE + " TEXT, " +
98                 ClockContract.InstancesColumns.ALARM_STATE + " INTEGER NOT NULL, " +
99                 ClockContract.InstancesColumns.ALARM_ID + " INTEGER REFERENCES " +
100                     ALARMS_TABLE_NAME + "(" + ClockContract.AlarmsColumns._ID + ") " +
101                     "ON UPDATE CASCADE ON DELETE CASCADE" +
102                 ");");
103         LogUtils.i("Instance table created");
104     }
105 
ClockDatabaseHelper(Context context)106     public ClockDatabaseHelper(Context context) {
107         super(context, DATABASE_NAME, null, VERSION_8);
108     }
109 
110     @Override
onCreate(SQLiteDatabase db)111     public void onCreate(SQLiteDatabase db) {
112         createAlarmsTable(db);
113         createInstanceTable(db);
114 
115         // insert default alarms
116         LogUtils.i("Inserting default alarms");
117         String cs = ", "; //comma and space
118         String insertMe = "INSERT INTO " + ALARMS_TABLE_NAME + " (" +
119                 ClockContract.AlarmsColumns.HOUR + cs +
120                 ClockContract.AlarmsColumns.MINUTES + cs +
121                 ClockContract.AlarmsColumns.DAYS_OF_WEEK + cs +
122                 ClockContract.AlarmsColumns.ENABLED + cs +
123                 ClockContract.AlarmsColumns.VIBRATE + cs +
124                 ClockContract.AlarmsColumns.LABEL + cs +
125                 ClockContract.AlarmsColumns.RINGTONE + cs +
126                 ClockContract.AlarmsColumns.DELETE_AFTER_USE + ") VALUES ";
127         db.execSQL(insertMe + DEFAULT_ALARM_1);
128         db.execSQL(insertMe + DEFAULT_ALARM_2);
129     }
130 
131     @Override
onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion)132     public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) {
133         LogUtils.v("Upgrading alarms database from version %d to %d", oldVersion, currentVersion);
134 
135         if (oldVersion <= VERSION_7) {
136             // This was not used in VERSION_7 or prior, so we can just drop it.
137             db.execSQL("DROP TABLE IF EXISTS " + SELECTED_CITIES_TABLE_NAME + ";");
138         }
139 
140         if (oldVersion <= VERSION_6) {
141             // This was not used in VERSION_6 or prior, so we can just drop it.
142             db.execSQL("DROP TABLE IF EXISTS " + INSTANCES_TABLE_NAME + ";");
143 
144             // Create new alarms table and copy over the data
145             createAlarmsTable(db);
146             createInstanceTable(db);
147 
148             LogUtils.i("Copying old alarms to new table");
149             final String[] OLD_TABLE_COLUMNS = {
150                     "_id",
151                     "hour",
152                     "minutes",
153                     "daysofweek",
154                     "enabled",
155                     "vibrate",
156                     "message",
157                     "alert",
158             };
159             try (Cursor cursor = db.query(OLD_ALARMS_TABLE_NAME, OLD_TABLE_COLUMNS,
160                     null, null, null, null, null)) {
161                 final Calendar currentTime = Calendar.getInstance();
162                 while (cursor != null && cursor.moveToNext()) {
163                     final Alarm alarm = new Alarm();
164                     alarm.id = cursor.getLong(0);
165                     alarm.hour = cursor.getInt(1);
166                     alarm.minutes = cursor.getInt(2);
167                     alarm.daysOfWeek = Weekdays.fromBits(cursor.getInt(3));
168                     alarm.enabled = cursor.getInt(4) == 1;
169                     alarm.vibrate = cursor.getInt(5) == 1;
170                     alarm.label = cursor.getString(6);
171 
172                     final String alertString = cursor.getString(7);
173                     if ("silent".equals(alertString)) {
174                         alarm.alert = Alarm.NO_RINGTONE_URI;
175                     } else {
176                         alarm.alert =
177                                 TextUtils.isEmpty(alertString) ? null : Uri.parse(alertString);
178                     }
179 
180                     // Save new version of alarm and create alarm instance for it
181                     db.insert(ALARMS_TABLE_NAME, null, Alarm.createContentValues(alarm));
182                     if (alarm.enabled) {
183                         AlarmInstance newInstance = alarm.createInstanceAfter(currentTime);
184                         db.insert(INSTANCES_TABLE_NAME, null,
185                                 AlarmInstance.createContentValues(newInstance));
186                     }
187                 }
188             }
189 
190             LogUtils.i("Dropping old alarm table");
191             db.execSQL("DROP TABLE IF EXISTS " + OLD_ALARMS_TABLE_NAME + ";");
192         }
193     }
194 
fixAlarmInsert(ContentValues values)195     long fixAlarmInsert(ContentValues values) {
196         // Why are we doing this? Is this not a programming bug if we try to
197         // insert an already used id?
198         final SQLiteDatabase db = getWritableDatabase();
199         db.beginTransaction();
200         long rowId = -1;
201         try {
202             // Check if we are trying to re-use an existing id.
203             final Object value = values.get(ClockContract.AlarmsColumns._ID);
204             if (value != null) {
205                 long id = (Long) value;
206                 if (id > -1) {
207                     final String[] columns = {ClockContract.AlarmsColumns._ID};
208                     final String selection = ClockContract.AlarmsColumns._ID + " = ?";
209                     final String[] selectionArgs = {String.valueOf(id)};
210                     try (Cursor cursor = db.query(ALARMS_TABLE_NAME, columns, selection,
211                             selectionArgs, null, null, null)) {
212                         if (cursor.moveToFirst()) {
213                             // Record exists. Remove the id so sqlite can generate a new one.
214                             values.putNull(ClockContract.AlarmsColumns._ID);
215                         }
216                     }
217                 }
218             }
219 
220             rowId = db.insert(ALARMS_TABLE_NAME, ClockContract.AlarmsColumns.RINGTONE, values);
221             db.setTransactionSuccessful();
222         } finally {
223             db.endTransaction();
224         }
225         if (rowId < 0) {
226             throw new SQLException("Failed to insert row");
227         }
228         LogUtils.v("Added alarm rowId = " + rowId);
229 
230         return rowId;
231     }
232 }
233