Home | History | Annotate | Download | only in provider 
      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 
     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 
     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 
    105     public ClockDatabaseHelper(Context context) {
    106         super(context, DATABASE_NAME, null, VERSION_8);
    107     }
    108 
    109     @Override
    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
    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 
    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