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