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