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