CalendarDatabaseHelper.java revision 02f97c538fc46a08d857d2c807c76fd0eec12493 
1/*
2 * Copyright (C) 2009 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.providers.calendar;
18
19import com.android.common.content.SyncStateContentProviderHelper;
20import com.google.common.annotations.VisibleForTesting;
21
22import android.accounts.Account;
23import android.content.ContentResolver;
24import android.content.ContentValues;
25import android.content.Context;
26import android.database.Cursor;
27import android.database.DatabaseUtils;
28import android.database.sqlite.SQLiteDatabase;
29import android.database.sqlite.SQLiteException;
30import android.database.sqlite.SQLiteOpenHelper;
31import android.os.Bundle;
32import android.provider.Calendar;
33import android.provider.Calendar.Attendees;
34import android.provider.Calendar.Events;
35import android.provider.Calendar.Reminders;
36import android.provider.ContactsContract;
37import android.provider.SyncStateContract;
38import android.text.TextUtils;
39import android.text.format.Time;
40import android.util.Log;
41
42import java.io.UnsupportedEncodingException;
43import java.net.URLDecoder;
44import java.util.TimeZone;
45
46/**
47 * Database helper for calendar. Designed as a singleton to make sure that all
48 * {@link android.content.ContentProvider} users get the same reference.
49 */
50/* package */ class CalendarDatabaseHelper extends SQLiteOpenHelper {
51
52    private static final String TAG = "CalendarDatabaseHelper";
53
54    private static final boolean LOGD = false;
55
56    private static final String DATABASE_NAME = "calendar.db";
57
58    private static final int DAY_IN_SECONDS = 24 * 60 * 60;
59
60    // Note: if you update the version number, you must also update the code
61    // in upgradeDatabase() to modify the database (gracefully, if possible).
62    // Versions under 100 cover through Froyo, 1xx version are for Gingerbread,
63    // 2xx for Honeycomb, and 3xx for ICS. For future versions bump this to the
64    // next hundred at each major release.
65    static final int DATABASE_VERSION = 305;
66
67    private static final int PRE_FROYO_SYNC_STATE_VERSION = 3;
68
69    // columns used to duplicate an event row
70    private static final String LAST_SYNCED_EVENT_COLUMNS =
71            Events._SYNC_ID + "," +
72            Events.CALENDAR_ID + "," +
73            Events.TITLE + "," +
74            Events.EVENT_LOCATION + "," +
75            Events.DESCRIPTION + "," +
76            Events.EVENT_COLOR + "," +
77            Events.STATUS + "," +
78            Events.SELF_ATTENDEE_STATUS + "," +
79            Events.DTSTART + "," +
80            Events.DTEND + "," +
81            Events.EVENT_TIMEZONE + "," +
82            Events.EVENT_END_TIMEZONE + "," +
83            Events.DURATION + "," +
84            Events.ALL_DAY + "," +
85            Events.ACCESS_LEVEL + "," +
86            Events.AVAILABILITY + "," +
87            Events.HAS_ALARM + "," +
88            Events.HAS_EXTENDED_PROPERTIES + "," +
89            Events.RRULE + "," +
90            Events.RDATE + "," +
91            Events.EXRULE + "," +
92            Events.EXDATE + "," +
93            Events.ORIGINAL_SYNC_ID + "," +
94            Events.ORIGINAL_ID + "," +
95            Events.ORIGINAL_INSTANCE_TIME + "," +
96            Events.ORIGINAL_ALL_DAY + "," +
97            Events.LAST_DATE + "," +
98            Events.HAS_ATTENDEE_DATA + "," +
99            Events.GUESTS_CAN_MODIFY + "," +
100            Events.GUESTS_CAN_INVITE_OTHERS + "," +
101            Events.GUESTS_CAN_SEE_GUESTS + "," +
102            Events.ORGANIZER;
103
104    // columns used to duplicate a reminder row
105    private static final String LAST_SYNCED_REMINDER_COLUMNS =
106            Calendar.Reminders.MINUTES + "," +
107            Calendar.Reminders.METHOD;
108
109    // columns used to duplicate an attendee row
110    private static final String LAST_SYNCED_ATTENDEE_COLUMNS =
111            Calendar.Attendees.ATTENDEE_NAME + "," +
112            Calendar.Attendees.ATTENDEE_EMAIL + "," +
113            Calendar.Attendees.ATTENDEE_STATUS + "," +
114            Calendar.Attendees.ATTENDEE_RELATIONSHIP + "," +
115            Calendar.Attendees.ATTENDEE_TYPE;
116
117    // columns used to duplicate an extended property row
118    private static final String LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS =
119            Calendar.ExtendedProperties.NAME + "," +
120            Calendar.ExtendedProperties.VALUE;
121
122    public interface Tables {
123        public static final String CALENDARS = "Calendars";
124        public static final String EVENTS = "Events";
125        public static final String EVENTS_RAW_TIMES = "EventsRawTimes";
126        public static final String INSTANCES = "Instances";
127        public static final String ATTENDEES = "Attendees";
128        public static final String REMINDERS = "Reminders";
129        public static final String CALENDAR_ALERTS = "CalendarAlerts";
130        public static final String EXTENDED_PROPERTIES = "ExtendedProperties";
131        public static final String CALENDAR_META_DATA = "CalendarMetaData";
132        public static final String CALENDAR_CACHE = "CalendarCache";
133        public static final String SYNC_STATE = "_sync_state";
134        public static final String SYNC_STATE_META = "_sync_state_metadata";
135    }
136
137    public interface Views {
138        public static final String EVENTS = "view_events";
139    }
140
141    // Copied from SyncStateContentProviderHelper.  Don't really want to make them public there.
142    private static final String SYNC_STATE_META_VERSION_COLUMN = "version";
143
144    // This needs to be done when all the tables are already created
145    private static final String EVENTS_CLEANUP_TRIGGER_SQL =
146            "DELETE FROM " + Tables.INSTANCES +
147                " WHERE "+ Calendar.Instances.EVENT_ID + "=" +
148                    "old." + Calendar.Events._ID + ";" +
149            "DELETE FROM " + Tables.EVENTS_RAW_TIMES +
150                " WHERE " + Calendar.EventsRawTimes.EVENT_ID + "=" +
151                    "old." + Calendar.Events._ID + ";" +
152            "DELETE FROM " + Tables.ATTENDEES +
153                " WHERE " + Calendar.Attendees.EVENT_ID + "=" +
154                    "old." + Calendar.Events._ID + ";" +
155            "DELETE FROM " + Tables.REMINDERS +
156                " WHERE " + Calendar.Reminders.EVENT_ID + "=" +
157                    "old." + Calendar.Events._ID + ";" +
158            "DELETE FROM " + Tables.CALENDAR_ALERTS +
159                " WHERE " + Calendar.CalendarAlerts.EVENT_ID + "=" +
160                    "old." + Calendar.Events._ID + ";" +
161            "DELETE FROM " + Tables.EXTENDED_PROPERTIES +
162                " WHERE " + Calendar.ExtendedProperties.EVENT_ID + "=" +
163                    "old." + Calendar.Events._ID + ";";
164
165    // This ensures any exceptions based on an event get their original_sync_id
166    // column set when an the _sync_id is set.
167    private static final String EVENTS_ORIGINAL_SYNC_TRIGGER_SQL =
168            "UPDATE " + Tables.EVENTS +
169                " SET " + Events.ORIGINAL_SYNC_ID + "=new." + Events._SYNC_ID +
170                " WHERE " + Events.ORIGINAL_ID + "=old." + Events._ID + ";";
171
172    private static final String SYNC_ID_UPDATE_TRIGGER_NAME = "original_sync_update";
173    private static final String CREATE_SYNC_ID_UPDATE_TRIGGER =
174            "CREATE TRIGGER " + SYNC_ID_UPDATE_TRIGGER_NAME + " UPDATE OF " + Events._SYNC_ID +
175            " ON " + Tables.EVENTS +
176            " BEGIN " +
177                EVENTS_ORIGINAL_SYNC_TRIGGER_SQL +
178            " END";
179
180    private static final String CALENDAR_CLEANUP_TRIGGER_SQL = "DELETE FROM " + Tables.EVENTS +
181            " WHERE " + Calendar.Events.CALENDAR_ID + "=" +
182                "old." + Calendar.Events._ID + ";";
183
184    private static final String SCHEMA_HTTPS = "https://";
185    private static final String SCHEMA_HTTP = "http://";
186
187    private final SyncStateContentProviderHelper mSyncState;
188
189    private static CalendarDatabaseHelper sSingleton = null;
190
191    private DatabaseUtils.InsertHelper mCalendarsInserter;
192    private DatabaseUtils.InsertHelper mEventsInserter;
193    private DatabaseUtils.InsertHelper mEventsRawTimesInserter;
194    private DatabaseUtils.InsertHelper mInstancesInserter;
195    private DatabaseUtils.InsertHelper mAttendeesInserter;
196    private DatabaseUtils.InsertHelper mRemindersInserter;
197    private DatabaseUtils.InsertHelper mCalendarAlertsInserter;
198    private DatabaseUtils.InsertHelper mExtendedPropertiesInserter;
199
200    public long calendarsInsert(ContentValues values) {
201        return mCalendarsInserter.insert(values);
202    }
203
204    public long eventsInsert(ContentValues values) {
205        return mEventsInserter.insert(values);
206    }
207
208    public long eventsRawTimesInsert(ContentValues values) {
209        return mEventsRawTimesInserter.insert(values);
210    }
211
212    public long eventsRawTimesReplace(ContentValues values) {
213        return mEventsRawTimesInserter.replace(values);
214    }
215
216    public long instancesInsert(ContentValues values) {
217        return mInstancesInserter.insert(values);
218    }
219
220    public long instancesReplace(ContentValues values) {
221        return mInstancesInserter.replace(values);
222    }
223
224    public long attendeesInsert(ContentValues values) {
225        return mAttendeesInserter.insert(values);
226    }
227
228    public long remindersInsert(ContentValues values) {
229        return mRemindersInserter.insert(values);
230    }
231
232    public long calendarAlertsInsert(ContentValues values) {
233        return mCalendarAlertsInserter.insert(values);
234    }
235
236    public long extendedPropertiesInsert(ContentValues values) {
237        return mExtendedPropertiesInserter.insert(values);
238    }
239
240    public static synchronized CalendarDatabaseHelper getInstance(Context context) {
241        if (sSingleton == null) {
242            sSingleton = new CalendarDatabaseHelper(context);
243        }
244        return sSingleton;
245    }
246
247    /**
248     * Private constructor, callers except unit tests should obtain an instance through
249     * {@link #getInstance(android.content.Context)} instead.
250     */
251    /* package */ CalendarDatabaseHelper(Context context) {
252        super(context, DATABASE_NAME, null, DATABASE_VERSION);
253        if (LOGD) Log.d(TAG, "Creating OpenHelper");
254
255        mSyncState = new SyncStateContentProviderHelper();
256    }
257
258    @Override
259    public void onOpen(SQLiteDatabase db) {
260        mSyncState.onDatabaseOpened(db);
261
262        mCalendarsInserter = new DatabaseUtils.InsertHelper(db, Tables.CALENDARS);
263        mEventsInserter = new DatabaseUtils.InsertHelper(db, Tables.EVENTS);
264        mEventsRawTimesInserter = new DatabaseUtils.InsertHelper(db, Tables.EVENTS_RAW_TIMES);
265        mInstancesInserter = new DatabaseUtils.InsertHelper(db, Tables.INSTANCES);
266        mAttendeesInserter = new DatabaseUtils.InsertHelper(db, Tables.ATTENDEES);
267        mRemindersInserter = new DatabaseUtils.InsertHelper(db, Tables.REMINDERS);
268        mCalendarAlertsInserter = new DatabaseUtils.InsertHelper(db, Tables.CALENDAR_ALERTS);
269        mExtendedPropertiesInserter =
270                new DatabaseUtils.InsertHelper(db, Tables.EXTENDED_PROPERTIES);
271    }
272
273    /*
274     * Upgrade sync state table if necessary.  Note that the data bundle
275     * in the table is not upgraded.
276     *
277     * The sync state used to be stored with version 3, but now uses the
278     * same sync state code as contacts, which is version 1.  This code
279     * upgrades from 3 to 1 if necessary.  (Yes, the numbers are unfortunately
280     * backwards.)
281     *
282     * This code is only called when upgrading from an old calendar version,
283     * so there is no problem if sync state version 3 gets used again in the
284     * future.
285     */
286    private void upgradeSyncState(SQLiteDatabase db) {
287        long version = DatabaseUtils.longForQuery(db,
288                 "SELECT " + SYNC_STATE_META_VERSION_COLUMN
289                 + " FROM " + Tables.SYNC_STATE_META,
290                 null);
291        if (version == PRE_FROYO_SYNC_STATE_VERSION) {
292            Log.i(TAG, "Upgrading calendar sync state table");
293            db.execSQL("CREATE TEMPORARY TABLE state_backup(_sync_account TEXT, "
294                    + "_sync_account_type TEXT, data TEXT);");
295            db.execSQL("INSERT INTO state_backup SELECT _sync_account, _sync_account_type, data"
296                    + " FROM "
297                    + Tables.SYNC_STATE
298                    + " WHERE _sync_account is not NULL and _sync_account_type is not NULL;");
299            db.execSQL("DROP TABLE " + Tables.SYNC_STATE + ";");
300            mSyncState.onDatabaseOpened(db);
301            db.execSQL("INSERT INTO " + Tables.SYNC_STATE + "("
302                    + SyncStateContract.Columns.ACCOUNT_NAME + ","
303                    + SyncStateContract.Columns.ACCOUNT_TYPE + ","
304                    + SyncStateContract.Columns.DATA
305                    + ") SELECT _sync_account, _sync_account_type, data from state_backup;");
306            db.execSQL("DROP TABLE state_backup;");
307        } else {
308            // Wrong version to upgrade.
309            // Don't need to do anything more here because mSyncState.onDatabaseOpened() will blow
310            // away and recreate  the database (which will result in a resync).
311            Log.w(TAG, "upgradeSyncState: current version is " + version + ", skipping upgrade.");
312        }
313    }
314
315    @Override
316    public void onCreate(SQLiteDatabase db) {
317        bootstrapDB(db);
318    }
319
320    private void bootstrapDB(SQLiteDatabase db) {
321        Log.i(TAG, "Bootstrapping database");
322
323        mSyncState.createDatabase(db);
324
325        createCalendarsTable(db);
326
327        createEventsTable(db);
328
329        db.execSQL("CREATE TABLE " + Tables.EVENTS_RAW_TIMES + " (" +
330                Calendar.EventsRawTimes._ID + " INTEGER PRIMARY KEY," +
331                Calendar.EventsRawTimes.EVENT_ID + " INTEGER NOT NULL," +
332                Calendar.EventsRawTimes.DTSTART_2445 + " TEXT," +
333                Calendar.EventsRawTimes.DTEND_2445 + " TEXT," +
334                Calendar.EventsRawTimes.ORIGINAL_INSTANCE_TIME_2445 + " TEXT," +
335                Calendar.EventsRawTimes.LAST_DATE_2445 + " TEXT," +
336                "UNIQUE (" + Calendar.EventsRawTimes.EVENT_ID + ")" +
337                ");");
338
339        db.execSQL("CREATE TABLE " + Tables.INSTANCES + " (" +
340                Calendar.Instances._ID + " INTEGER PRIMARY KEY," +
341                Calendar.Instances.EVENT_ID + " INTEGER," +
342                Calendar.Instances.BEGIN + " INTEGER," +         // UTC millis
343                Calendar.Instances.END + " INTEGER," +           // UTC millis
344                Calendar.Instances.START_DAY + " INTEGER," +      // Julian start day
345                Calendar.Instances.END_DAY + " INTEGER," +        // Julian end day
346                Calendar.Instances.START_MINUTE + " INTEGER," +   // minutes from midnight
347                Calendar.Instances.END_MINUTE + " INTEGER," +     // minutes from midnight
348                "UNIQUE (" +
349                    Calendar.Instances.EVENT_ID + ", " +
350                    Calendar.Instances.BEGIN + ", " +
351                    Calendar.Instances.END + ")" +
352                ");");
353
354        db.execSQL("CREATE INDEX instancesStartDayIndex ON " + Tables.INSTANCES + " (" +
355                Calendar.Instances.START_DAY +
356                ");");
357
358        createCalendarMetaDataTable(db);
359
360        createCalendarCacheTable(db, null);
361
362        db.execSQL("CREATE TABLE " + Tables.ATTENDEES + " (" +
363                Calendar.Attendees._ID + " INTEGER PRIMARY KEY," +
364                Calendar.Attendees.EVENT_ID + " INTEGER," +
365                Calendar.Attendees.ATTENDEE_NAME + " TEXT," +
366                Calendar.Attendees.ATTENDEE_EMAIL + " TEXT," +
367                Calendar.Attendees.ATTENDEE_STATUS + " INTEGER," +
368                Calendar.Attendees.ATTENDEE_RELATIONSHIP + " INTEGER," +
369                Calendar.Attendees.ATTENDEE_TYPE + " INTEGER" +
370                ");");
371
372        db.execSQL("CREATE INDEX attendeesEventIdIndex ON " + Tables.ATTENDEES + " (" +
373                Calendar.Attendees.EVENT_ID +
374                ");");
375
376        db.execSQL("CREATE TABLE " + Tables.REMINDERS + " (" +
377                Calendar.Reminders._ID + " INTEGER PRIMARY KEY," +
378                Calendar.Reminders.EVENT_ID + " INTEGER," +
379                Calendar.Reminders.MINUTES + " INTEGER," +
380                Calendar.Reminders.METHOD + " INTEGER NOT NULL" +
381                " DEFAULT " + Calendar.Reminders.METHOD_DEFAULT +
382                ");");
383
384        db.execSQL("CREATE INDEX remindersEventIdIndex ON " + Tables.REMINDERS + " (" +
385                Calendar.Reminders.EVENT_ID +
386                ");");
387
388         // This table stores the Calendar notifications that have gone off.
389        db.execSQL("CREATE TABLE " + Tables.CALENDAR_ALERTS + " (" +
390                Calendar.CalendarAlerts._ID + " INTEGER PRIMARY KEY," +
391                Calendar.CalendarAlerts.EVENT_ID + " INTEGER," +
392                Calendar.CalendarAlerts.BEGIN + " INTEGER NOT NULL," +          // UTC millis
393                Calendar.CalendarAlerts.END + " INTEGER NOT NULL," +            // UTC millis
394                Calendar.CalendarAlerts.ALARM_TIME + " INTEGER NOT NULL," +     // UTC millis
395                // UTC millis
396                Calendar.CalendarAlerts.CREATION_TIME + " INTEGER NOT NULL DEFAULT 0," +
397                // UTC millis
398                Calendar.CalendarAlerts.RECEIVED_TIME + " INTEGER NOT NULL DEFAULT 0," +
399                // UTC millis
400                Calendar.CalendarAlerts.NOTIFY_TIME + " INTEGER NOT NULL DEFAULT 0," +
401                Calendar.CalendarAlerts.STATE + " INTEGER NOT NULL," +
402                Calendar.CalendarAlerts.MINUTES + " INTEGER," +
403                "UNIQUE (" +
404                    Calendar.CalendarAlerts.ALARM_TIME + ", " +
405                    Calendar.CalendarAlerts.BEGIN + ", " +
406                    Calendar.CalendarAlerts.EVENT_ID + ")" +
407                ");");
408
409        db.execSQL("CREATE INDEX calendarAlertsEventIdIndex ON " + Tables.CALENDAR_ALERTS + " (" +
410                Calendar.CalendarAlerts.EVENT_ID +
411                ");");
412
413        db.execSQL("CREATE TABLE " + Tables.EXTENDED_PROPERTIES + " (" +
414                Calendar.ExtendedProperties._ID + " INTEGER PRIMARY KEY," +
415                Calendar.ExtendedProperties.EVENT_ID + " INTEGER," +
416                Calendar.ExtendedProperties.NAME + " TEXT," +
417                Calendar.ExtendedProperties.VALUE + " TEXT" +
418                ");");
419
420        db.execSQL("CREATE INDEX extendedPropertiesEventIdIndex ON " + Tables.EXTENDED_PROPERTIES
421                + " (" +
422                Calendar.ExtendedProperties.EVENT_ID +
423                ");");
424
425        createEventsView(db);
426
427        // Trigger to remove data tied to an event when we delete that event.
428        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
429                "BEGIN " +
430                EVENTS_CLEANUP_TRIGGER_SQL +
431                "END");
432
433        // Trigger to update exceptions when an original event updates its
434        // _sync_id
435        db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
436
437        ContentResolver.requestSync(null /* all accounts */,
438                ContactsContract.AUTHORITY, new Bundle());
439    }
440
441    private void createEventsTable(SQLiteDatabase db) {
442        // IMPORTANT: when adding new columns, be sure to update ALLOWED_IN_EXCEPTION and
443        // DONT_CLONE_INTO_EXCEPTION in CalendarProvider2.
444        //
445        // TODO: do we need both dtend and duration?
446        // **When updating this be sure to also update LAST_SYNCED_EVENT_COLUMNS
447        db.execSQL("CREATE TABLE " + Tables.EVENTS + " (" +
448                Calendar.Events._ID + " INTEGER PRIMARY KEY," +
449                Calendar.Events._SYNC_ID + " TEXT," +
450                Calendar.Events.DIRTY + " INTEGER," +
451                Calendar.Events.LAST_SYNCED + " INTEGER DEFAULT 0," +
452                Calendar.Events.CALENDAR_ID + " INTEGER NOT NULL," +
453                Calendar.Events.TITLE + " TEXT," +
454                Calendar.Events.EVENT_LOCATION + " TEXT," +
455                Calendar.Events.DESCRIPTION + " TEXT," +
456                Calendar.Events.EVENT_COLOR + " INTEGER," +
457                Calendar.Events.STATUS + " INTEGER," +
458                Calendar.Events.SELF_ATTENDEE_STATUS + " INTEGER NOT NULL DEFAULT 0," +
459                // dtstart in millis since epoch
460                Calendar.Events.DTSTART + " INTEGER," +
461                // dtend in millis since epoch
462                Calendar.Events.DTEND + " INTEGER," +
463                // timezone for event
464                Calendar.Events.EVENT_TIMEZONE + " TEXT," +
465                Calendar.Events.DURATION + " TEXT," +
466                Calendar.Events.ALL_DAY + " INTEGER NOT NULL DEFAULT 0," +
467                Calendar.Events.ACCESS_LEVEL + " INTEGER NOT NULL DEFAULT 0," +
468                Calendar.Events.AVAILABILITY + " INTEGER NOT NULL DEFAULT 0," +
469                Calendar.Events.HAS_ALARM + " INTEGER NOT NULL DEFAULT 0," +
470                Calendar.Events.HAS_EXTENDED_PROPERTIES + " INTEGER NOT NULL DEFAULT 0," +
471                Calendar.Events.RRULE + " TEXT," +
472                Calendar.Events.RDATE + " TEXT," +
473                Calendar.Events.EXRULE + " TEXT," +
474                Calendar.Events.EXDATE + " TEXT," +
475                Calendar.Events.ORIGINAL_ID + " INTEGER," +
476                // ORIGINAL_SYNC_ID is the _sync_id of recurring event
477                Calendar.Events.ORIGINAL_SYNC_ID + " TEXT," +
478                // originalInstanceTime is in millis since epoch
479                Calendar.Events.ORIGINAL_INSTANCE_TIME + " INTEGER," +
480                Calendar.Events.ORIGINAL_ALL_DAY + " INTEGER," +
481                // lastDate is in millis since epoch
482                Calendar.Events.LAST_DATE + " INTEGER," +
483                Calendar.Events.HAS_ATTENDEE_DATA + " INTEGER NOT NULL DEFAULT 0," +
484                Calendar.Events.GUESTS_CAN_MODIFY + " INTEGER NOT NULL DEFAULT 0," +
485                Calendar.Events.GUESTS_CAN_INVITE_OTHERS + " INTEGER NOT NULL DEFAULT 1," +
486                Calendar.Events.GUESTS_CAN_SEE_GUESTS + " INTEGER NOT NULL DEFAULT 1," +
487                Calendar.Events.ORGANIZER + " STRING," +
488                Calendar.Events.DELETED + " INTEGER NOT NULL DEFAULT 0," +
489                // timezone for event with allDay events are in local timezone
490                Calendar.Events.EVENT_END_TIMEZONE + " TEXT," +
491                // SYNC_DATAX columns are available for use by sync adapters
492                Calendar.Events.SYNC_DATA1 + " TEXT," +
493                Calendar.Events.SYNC_DATA2 + " TEXT," +
494                Calendar.Events.SYNC_DATA3 + " TEXT," +
495                Calendar.Events.SYNC_DATA4 + " TEXT," +
496                Calendar.Events.SYNC_DATA5 + " TEXT," +
497                Calendar.Events.SYNC_DATA6 + " TEXT," +
498                Calendar.Events.SYNC_DATA7 + " TEXT," +
499                Calendar.Events.SYNC_DATA8 + " TEXT," +
500                Calendar.Events.SYNC_DATA9 + " TEXT," +
501                Calendar.Events.SYNC_DATA10 + " TEXT" + ");");
502
503        // **When updating this be sure to also update LAST_SYNCED_EVENT_COLUMNS
504
505        db.execSQL("CREATE INDEX eventsCalendarIdIndex ON " + Tables.EVENTS + " ("
506                + Calendar.Events.CALENDAR_ID + ");");
507    }
508
509    // TODO Remove this method after merging all ICS upgrades
510    private void createEventsTable300(SQLiteDatabase db) {
511        db.execSQL("CREATE TABLE Events (" +
512                "_id INTEGER PRIMARY KEY," +
513                "_sync_id TEXT," +
514                "_sync_version TEXT," +
515                // sync time in UTC
516                "_sync_time TEXT,"  +
517                "_sync_local_id INTEGER," +
518                "dirty INTEGER," +
519                // sync mark to filter out new rows
520                "_sync_mark INTEGER," +
521                "calendar_id INTEGER NOT NULL," +
522                "htmlUri TEXT," +
523                "title TEXT," +
524                "eventLocation TEXT," +
525                "description TEXT," +
526                "eventStatus INTEGER," +
527                "selfAttendeeStatus INTEGER NOT NULL DEFAULT 0," +
528                "commentsUri TEXT," +
529                // dtstart in millis since epoch
530                "dtstart INTEGER," +
531                // dtend in millis since epoch
532                "dtend INTEGER," +
533                // timezone for event
534                "eventTimezone TEXT," +
535                "duration TEXT," +
536                "allDay INTEGER NOT NULL DEFAULT 0," +
537                "accessLevel INTEGER NOT NULL DEFAULT 0," +
538                "availability INTEGER NOT NULL DEFAULT 0," +
539                "hasAlarm INTEGER NOT NULL DEFAULT 0," +
540                "hasExtendedProperties INTEGER NOT NULL DEFAULT 0," +
541                "rrule TEXT," +
542                "rdate TEXT," +
543                "exrule TEXT," +
544                "exdate TEXT," +
545                // originalEvent is the _sync_id of recurring event
546                "original_sync_id TEXT," +
547                // originalInstanceTime is in millis since epoch
548                "originalInstanceTime INTEGER," +
549                "originalAllDay INTEGER," +
550                // lastDate is in millis since epoch
551                "lastDate INTEGER," +
552                "hasAttendeeData INTEGER NOT NULL DEFAULT 0," +
553                "guestsCanModify INTEGER NOT NULL DEFAULT 0," +
554                "guestsCanInviteOthers INTEGER NOT NULL DEFAULT 1," +
555                "guestsCanSeeGuests INTEGER NOT NULL DEFAULT 1," +
556                "organizer STRING," +
557                "deleted INTEGER NOT NULL DEFAULT 0," +
558                // timezone for event with allDay events are in local timezone
559                "eventEndTimezone TEXT," +
560                // syncAdapterData is available for use by sync adapters
561                "sync_data1 TEXT);");
562
563        db.execSQL("CREATE INDEX eventsCalendarIdIndex ON Events (calendar_id);");
564    }
565
566    private void createCalendarsTable303(SQLiteDatabase db) {
567        db.execSQL("CREATE TABLE " + Tables.CALENDARS + " (" +
568                "_id INTEGER PRIMARY KEY," +
569                "account_name TEXT," +
570                "account_type TEXT," +
571                "_sync_id TEXT," +
572                "_sync_version TEXT," +
573                "_sync_time TEXT," +  // UTC
574                "dirty INTEGER," +
575                "name TEXT," +
576                "displayName TEXT," +
577                "calendar_color INTEGER," +
578                "access_level INTEGER," +
579                "visible INTEGER NOT NULL DEFAULT 1," +
580                "sync_events INTEGER NOT NULL DEFAULT 0," +
581                "calendar_location TEXT," +
582                "calendar_timezone TEXT," +
583                "ownerAccount TEXT, " +
584                "canOrganizerRespond INTEGER NOT NULL DEFAULT 1," +
585                "canModifyTimeZone INTEGER DEFAULT 1," +
586                "maxReminders INTEGER DEFAULT 5," +
587                "allowedReminders TEXT DEFAULT '0,1,2,3'," +
588                "deleted INTEGER NOT NULL DEFAULT 0," +
589                "cal_sync1 TEXT," +
590                "cal_sync2 TEXT," +
591                "cal_sync3 TEXT," +
592                "cal_sync4 TEXT," +
593                "cal_sync5 TEXT," +
594                "cal_sync6 TEXT" +
595                ");");
596
597        // Trigger to remove a calendar's events when we delete the calendar
598        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
599                "BEGIN " +
600                CALENDAR_CLEANUP_TRIGGER_SQL +
601                "END");
602    }
603
604    private void createCalendarsTable(SQLiteDatabase db) {
605        db.execSQL("CREATE TABLE " + Tables.CALENDARS + " (" +
606                Calendar.Calendars._ID + " INTEGER PRIMARY KEY," +
607                Calendar.Calendars.ACCOUNT_NAME + " TEXT," +
608                Calendar.Calendars.ACCOUNT_TYPE + " TEXT," +
609                Calendar.Calendars._SYNC_ID + " TEXT," +
610                Calendar.Calendars.DIRTY + " INTEGER," +
611                Calendar.Calendars.NAME + " TEXT," +
612                Calendar.Calendars.CALENDAR_DISPLAY_NAME + " TEXT," +
613                Calendar.Calendars.CALENDAR_COLOR + " INTEGER," +
614                Calendar.Calendars.CALENDAR_ACCESS_LEVEL + " INTEGER," +
615                Calendar.Calendars.VISIBLE + " INTEGER NOT NULL DEFAULT 1," +
616                Calendar.Calendars.SYNC_EVENTS + " INTEGER NOT NULL DEFAULT 0," +
617                Calendar.Calendars.CALENDAR_LOCATION + " TEXT," +
618                Calendar.Calendars.CALENDAR_TIME_ZONE + " TEXT," +
619                Calendar.Calendars.OWNER_ACCOUNT + " TEXT, " +
620                Calendar.Calendars.CAN_ORGANIZER_RESPOND + " INTEGER NOT NULL DEFAULT 1," +
621                Calendar.Calendars.CAN_MODIFY_TIME_ZONE + " INTEGER DEFAULT 1," +
622                Calendar.Calendars.CAN_PARTIALLY_UPDATE + " INTEGER DEFAULT 0," +
623                Calendar.Calendars.MAX_REMINDERS + " INTEGER DEFAULT 5," +
624                Calendar.Calendars.ALLOWED_REMINDERS + " TEXT DEFAULT '0,1,2'," +
625                Calendar.Calendars.DELETED + " INTEGER NOT NULL DEFAULT 0," +
626                Calendar.Calendars.CAL_SYNC1 + " TEXT," +
627                Calendar.Calendars.CAL_SYNC2 + " TEXT," +
628                Calendar.Calendars.CAL_SYNC3 + " TEXT," +
629                Calendar.Calendars.CAL_SYNC4 + " TEXT," +
630                Calendar.Calendars.CAL_SYNC5 + " TEXT," +
631                Calendar.Calendars.CAL_SYNC6 + " TEXT," +
632                Calendar.Calendars.CAL_SYNC7 + " TEXT," +
633                Calendar.Calendars.CAL_SYNC8 + " TEXT," +
634                Calendar.Calendars.CAL_SYNC9 + " TEXT," +
635                Calendar.Calendars.CAL_SYNC10 + " TEXT" +
636                ");");
637
638        // Trigger to remove a calendar's events when we delete the calendar
639        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
640                "BEGIN " +
641                CALENDAR_CLEANUP_TRIGGER_SQL +
642                "END");
643    }
644
645    private void createCalendarsTable300(SQLiteDatabase db) {
646        db.execSQL("CREATE TABLE " + Tables.CALENDARS + " (" +
647                "_id INTEGER PRIMARY KEY," +
648                "account_name TEXT," +
649                "account_type TEXT," +
650                "_sync_id TEXT," +
651                "_sync_version TEXT," +
652                "_sync_time TEXT," +  // UTC
653                "dirty INTEGER," +
654                "name TEXT," +
655                "displayName TEXT," +
656                "calendar_color INTEGER," +
657                "access_level INTEGER," +
658                "visible INTEGER NOT NULL DEFAULT 1," +
659                "sync_events INTEGER NOT NULL DEFAULT 0," +
660                "calendar_location TEXT," +
661                "calendar_timezone TEXT," +
662                "ownerAccount TEXT, " +
663                "canOrganizerRespond INTEGER NOT NULL DEFAULT 1," +
664                "canModifyTimeZone INTEGER DEFAULT 1," +
665                "maxReminders INTEGER DEFAULT 5," +
666                "allowedReminders TEXT DEFAULT '0,1,2'," +
667                "deleted INTEGER NOT NULL DEFAULT 0," +
668                "sync1 TEXT," +
669                "sync2 TEXT," +
670                "sync3 TEXT," +
671                "sync4 TEXT," +
672                "sync5 TEXT," +
673                "sync6 TEXT" +
674                ");");
675
676        // Trigger to remove a calendar's events when we delete the calendar
677        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
678                "BEGIN " +
679                CALENDAR_CLEANUP_TRIGGER_SQL +
680                "END");
681    }
682
683    private void createCalendarsTable205(SQLiteDatabase db) {
684        db.execSQL("CREATE TABLE Calendars (" +
685                "_id INTEGER PRIMARY KEY," +
686                "_sync_account TEXT," +
687                "_sync_account_type TEXT," +
688                "_sync_id TEXT," +
689                "_sync_version TEXT," +
690                "_sync_time TEXT," +  // UTC
691                "_sync_dirty INTEGER," +
692                "name TEXT," +
693                "displayName TEXT," +
694                "color INTEGER," +
695                "access_level INTEGER," +
696                "visible INTEGER NOT NULL DEFAULT 1," +
697                "sync_events INTEGER NOT NULL DEFAULT 0," +
698                "location TEXT," +
699                "timezone TEXT," +
700                "ownerAccount TEXT, " +
701                "canOrganizerRespond INTEGER NOT NULL DEFAULT 1," +
702                "canModifyTimeZone INTEGER DEFAULT 1, " +
703                "maxReminders INTEGER DEFAULT 5," +
704                "deleted INTEGER NOT NULL DEFAULT 0," +
705                "sync1 TEXT," +
706                "sync2 TEXT," +
707                "sync3 TEXT," +
708                "sync4 TEXT," +
709                "sync5 TEXT," +
710                "sync6 TEXT" +
711                ");");
712
713        createCalendarsCleanup200(db);
714    }
715
716    private void createCalendarsTable202(SQLiteDatabase db) {
717        db.execSQL("CREATE TABLE Calendars (" +
718                "_id INTEGER PRIMARY KEY," +
719                "_sync_account TEXT," +
720                "_sync_account_type TEXT," +
721                "_sync_id TEXT," +
722                "_sync_version TEXT," +
723                "_sync_time TEXT," +  // UTC
724                "_sync_local_id INTEGER," +
725                "_sync_dirty INTEGER," +
726                "_sync_mark INTEGER," + // Used to filter out new rows
727                "name TEXT," +
728                "displayName TEXT," +
729                "color INTEGER," +
730                "access_level INTEGER," +
731                "selected INTEGER NOT NULL DEFAULT 1," +
732                "sync_events INTEGER NOT NULL DEFAULT 0," +
733                "location TEXT," +
734                "timezone TEXT," +
735                "ownerAccount TEXT, " +
736                "organizerCanRespond INTEGER NOT NULL DEFAULT 1," +
737                "deleted INTEGER NOT NULL DEFAULT 0," +
738                "sync1 TEXT," +
739                "sync2 TEXT," +
740                "sync3 TEXT," +
741                "sync4 TEXT," +
742                "sync5 TEXT" +
743                ");");
744
745        createCalendarsCleanup200(db);
746    }
747
748    private void createCalendarsTable200(SQLiteDatabase db) {
749        db.execSQL("CREATE TABLE Calendars (" +
750                "_id INTEGER PRIMARY KEY," +
751                "_sync_account TEXT," +
752                "_sync_account_type TEXT," +
753                "_sync_id TEXT," +
754                "_sync_version TEXT," +
755                "_sync_time TEXT," +  // UTC
756                "_sync_local_id INTEGER," +
757                "_sync_dirty INTEGER," +
758                "_sync_mark INTEGER," + // Used to filter out new rows
759                "name TEXT," +
760                "displayName TEXT," +
761                "hidden INTEGER NOT NULL DEFAULT 0," +
762                "color INTEGER," +
763                "access_level INTEGER," +
764                "selected INTEGER NOT NULL DEFAULT 1," +
765                "sync_events INTEGER NOT NULL DEFAULT 0," +
766                "location TEXT," +
767                "timezone TEXT," +
768                "ownerAccount TEXT, " +
769                "organizerCanRespond INTEGER NOT NULL DEFAULT 1," +
770                "deleted INTEGER NOT NULL DEFAULT 0," +
771                "sync1 TEXT," +
772                "sync2 TEXT," +
773                "sync3 TEXT" +
774                ");");
775
776        createCalendarsCleanup200(db);
777    }
778
779    /** Trigger to remove a calendar's events when we delete the calendar */
780    private void createCalendarsCleanup200(SQLiteDatabase db) {
781        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " +
782                "BEGIN " +
783                "DELETE FROM Events WHERE calendar_id=old._id;" +
784                "END");
785    }
786
787    private void createCalendarMetaDataTable(SQLiteDatabase db) {
788        db.execSQL("CREATE TABLE " + Tables.CALENDAR_META_DATA + " (" +
789                Calendar.CalendarMetaData._ID + " INTEGER PRIMARY KEY," +
790                Calendar.CalendarMetaData.LOCAL_TIMEZONE + " TEXT," +
791                Calendar.CalendarMetaData.MIN_INSTANCE + " INTEGER," +      // UTC millis
792                Calendar.CalendarMetaData.MAX_INSTANCE + " INTEGER" +       // UTC millis
793                ");");
794    }
795
796    private void createCalendarMetaDataTable59(SQLiteDatabase db) {
797        db.execSQL("CREATE TABLE CalendarMetaData (" +
798                "_id INTEGER PRIMARY KEY," +
799                "localTimezone TEXT," +
800                "minInstance INTEGER," +      // UTC millis
801                "maxInstance INTEGER" +       // UTC millis
802                ");");
803    }
804
805    private void createCalendarCacheTable(SQLiteDatabase db, String oldTimezoneDbVersion) {
806        // This is a hack because versioning skipped version number 61 of schema
807        // TODO after version 70 this can be removed
808        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";");
809
810        // IF NOT EXISTS should be normal pattern for table creation
811        db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.CALENDAR_CACHE + " (" +
812                CalendarCache.COLUMN_NAME_ID + " INTEGER PRIMARY KEY," +
813                CalendarCache.COLUMN_NAME_KEY + " TEXT NOT NULL," +
814                CalendarCache.COLUMN_NAME_VALUE + " TEXT" +
815                ");");
816
817        initCalendarCacheTable(db, oldTimezoneDbVersion);
818        updateCalendarCacheTable(db);
819    }
820
821    private void initCalendarCacheTable(SQLiteDatabase db, String oldTimezoneDbVersion) {
822        String timezoneDbVersion = (oldTimezoneDbVersion != null) ?
823                oldTimezoneDbVersion : CalendarCache.DEFAULT_TIMEZONE_DATABASE_VERSION;
824
825        // Set the default timezone database version
826        db.execSQL("INSERT OR REPLACE INTO " + Tables.CALENDAR_CACHE +
827                " (" + CalendarCache.COLUMN_NAME_ID + ", " +
828                CalendarCache.COLUMN_NAME_KEY + ", " +
829                CalendarCache.COLUMN_NAME_VALUE + ") VALUES (" +
830                CalendarCache.KEY_TIMEZONE_DATABASE_VERSION.hashCode() + "," +
831                "'" + CalendarCache.KEY_TIMEZONE_DATABASE_VERSION + "',"  +
832                "'" + timezoneDbVersion + "'" +
833                ");");
834    }
835
836    private void updateCalendarCacheTable(SQLiteDatabase db) {
837        // Define the default timezone type for Instances timezone management
838        db.execSQL("INSERT INTO " + Tables.CALENDAR_CACHE +
839                " (" + CalendarCache.COLUMN_NAME_ID + ", " +
840                CalendarCache.COLUMN_NAME_KEY + ", " +
841                CalendarCache.COLUMN_NAME_VALUE + ") VALUES (" +
842                CalendarCache.KEY_TIMEZONE_TYPE.hashCode() + "," +
843                "'" + CalendarCache.KEY_TIMEZONE_TYPE + "',"  +
844                "'" + CalendarCache.TIMEZONE_TYPE_AUTO + "'" +
845                ");");
846
847        String defaultTimezone = TimeZone.getDefault().getID();
848
849        // Define the default timezone for Instances
850        db.execSQL("INSERT INTO " + Tables.CALENDAR_CACHE +
851                " (" + CalendarCache.COLUMN_NAME_ID + ", " +
852                CalendarCache.COLUMN_NAME_KEY + ", " +
853                CalendarCache.COLUMN_NAME_VALUE + ") VALUES (" +
854                CalendarCache.KEY_TIMEZONE_INSTANCES.hashCode() + "," +
855                "'" + CalendarCache.KEY_TIMEZONE_INSTANCES + "',"  +
856                "'" + defaultTimezone + "'" +
857                ");");
858
859        // Define the default previous timezone for Instances
860        db.execSQL("INSERT INTO " + Tables.CALENDAR_CACHE +
861                " (" + CalendarCache.COLUMN_NAME_ID + ", " +
862                CalendarCache.COLUMN_NAME_KEY + ", " +
863                CalendarCache.COLUMN_NAME_VALUE + ") VALUES (" +
864                CalendarCache.KEY_TIMEZONE_INSTANCES_PREVIOUS.hashCode() + "," +
865                "'" + CalendarCache.KEY_TIMEZONE_INSTANCES_PREVIOUS + "',"  +
866                "'" + defaultTimezone + "'" +
867                ");");
868    }
869
870    private void initCalendarCacheTable203(SQLiteDatabase db, String oldTimezoneDbVersion) {
871        String timezoneDbVersion = (oldTimezoneDbVersion != null) ?
872                oldTimezoneDbVersion : "2009s";
873
874        // Set the default timezone database version
875        db.execSQL("INSERT OR REPLACE INTO CalendarCache" +
876                " (_id, " +
877                "key, " +
878                "value) VALUES (" +
879                "timezoneDatabaseVersion".hashCode() + "," +
880                "'timezoneDatabaseVersion',"  +
881                "'" + timezoneDbVersion + "'" +
882                ");");
883    }
884
885    private void updateCalendarCacheTableTo203(SQLiteDatabase db) {
886        // Define the default timezone type for Instances timezone management
887        db.execSQL("INSERT INTO CalendarCache" +
888                " (_id, key, value) VALUES (" +
889                "timezoneType".hashCode() + "," +
890                "'timezoneType',"  +
891                "'auto'" +
892                ");");
893
894        String defaultTimezone = TimeZone.getDefault().getID();
895
896        // Define the default timezone for Instances
897        db.execSQL("INSERT INTO CalendarCache" +
898                " (_id, key, value) VALUES (" +
899                "timezoneInstances".hashCode() + "," +
900                "'timezoneInstances',"  +
901                "'" + defaultTimezone + "'" +
902                ");");
903
904        // Define the default previous timezone for Instances
905        db.execSQL("INSERT INTO CalendarCache" +
906                " (_id, key, value) VALUES (" +
907                "timezoneInstancesPrevious".hashCode() + "," +
908                "'timezoneInstancesPrevious',"  +
909                "'" + defaultTimezone + "'" +
910                ");");
911    }
912
913    @Override
914    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
915        Log.i(TAG, "Upgrading DB from version " + oldVersion
916                + " to " + newVersion);
917        if (oldVersion < 49) {
918            dropTables(db);
919            mSyncState.createDatabase(db);
920            return; // this was lossy
921        }
922
923        // From schema versions 59 to version 66, the CalendarMetaData table definition had lost
924        // the primary key leading to having the CalendarMetaData with multiple rows instead of
925        // only one. The Instance table was then corrupted (during Instance expansion we are using
926        // the localTimezone, minInstance and maxInstance from CalendarMetaData table.
927        // This boolean helps us tracking the need to recreate the CalendarMetaData table and
928        // clear the Instance table (and thus force an Instance expansion).
929        boolean recreateMetaDataAndInstances = (oldVersion >= 59 && oldVersion <= 66);
930        boolean createEventsView = false;
931
932        try {
933            if (oldVersion < 51) {
934                upgradeToVersion51(db); // From 50 or 51
935                oldVersion = 51;
936            }
937            if (oldVersion == 51) {
938                upgradeToVersion52(db);
939                oldVersion += 1;
940            }
941            if (oldVersion == 52) {
942                upgradeToVersion53(db);
943                oldVersion += 1;
944            }
945            if (oldVersion == 53) {
946                upgradeToVersion54(db);
947                oldVersion += 1;
948            }
949            if (oldVersion == 54) {
950                upgradeToVersion55(db);
951                oldVersion += 1;
952            }
953            if (oldVersion == 55 || oldVersion == 56) {
954                // Both require resync, so just schedule it once
955                upgradeResync(db);
956            }
957            if (oldVersion == 55) {
958                upgradeToVersion56(db);
959                oldVersion += 1;
960            }
961            if (oldVersion == 56) {
962                upgradeToVersion57(db);
963                oldVersion += 1;
964            }
965            if (oldVersion == 57) {
966                // Changes are undone upgrading to 60, so don't do anything.
967                oldVersion += 1;
968            }
969            if (oldVersion == 58) {
970                upgradeToVersion59(db);
971                oldVersion += 1;
972            }
973            if (oldVersion == 59) {
974                upgradeToVersion60(db);
975                createEventsView = true;
976                oldVersion += 1;
977            }
978            if (oldVersion == 60) {
979                upgradeToVersion61(db);
980                oldVersion += 1;
981            }
982            if (oldVersion == 61) {
983                upgradeToVersion62(db);
984                oldVersion += 1;
985            }
986            if (oldVersion == 62) {
987                createEventsView = true;
988                oldVersion += 1;
989            }
990            if (oldVersion == 63) {
991                upgradeToVersion64(db);
992                oldVersion += 1;
993            }
994            if (oldVersion == 64) {
995                createEventsView = true;
996                oldVersion += 1;
997            }
998            if (oldVersion == 65) {
999                upgradeToVersion66(db);
1000                oldVersion += 1;
1001            }
1002            if (oldVersion == 66) {
1003                // Changes are done thru recreateMetaDataAndInstances() method
1004                oldVersion += 1;
1005            }
1006            if (recreateMetaDataAndInstances) {
1007                recreateMetaDataAndInstances67(db);
1008            }
1009            if (oldVersion == 67 || oldVersion == 68) {
1010                upgradeToVersion69(db);
1011                oldVersion = 69;
1012            }
1013            // 69. 70 are for Froyo/old Gingerbread only and 100s are for Gingerbread only
1014            // 70 and 71 have been for Honeycomb but no more used
1015            // 72 and 73 and 74 were for Honeycomb only but are considered as obsolete for enabling
1016            // room for Froyo version numbers
1017            if(oldVersion == 69) {
1018                upgradeToVersion200(db);
1019                createEventsView = true;
1020                oldVersion = 200;
1021            }
1022            if (oldVersion == 70) {
1023                upgradeToVersion200(db);
1024                oldVersion = 200;
1025            }
1026            if (oldVersion == 100) {
1027                upgradeToVersion200(db);
1028                oldVersion = 200;
1029            }
1030            boolean need203Update = true;
1031            if (oldVersion == 101) {
1032                oldVersion = 200;
1033                // Gingerbread version 101 is similar to Honeycomb version 203
1034                need203Update = false;
1035            }
1036            if (oldVersion == 200) {
1037                upgradeToVersion201(db);
1038                oldVersion += 1;
1039            }
1040            if (oldVersion == 201) {
1041                upgradeToVersion202(db);
1042                createEventsView = true;
1043                oldVersion += 1;
1044            }
1045            if (oldVersion == 202 && need203Update) {
1046                upgradeToVersion203(db);
1047                oldVersion += 1;
1048            }
1049            if (oldVersion == 203) {
1050                createEventsView = true;
1051                oldVersion += 1;
1052            }
1053            if (oldVersion == 204) {
1054                // This is an ICS update, all following use 300+ versions.
1055                upgradeToVersion205(db);
1056                createEventsView = true;
1057                oldVersion += 1;
1058            }
1059            if (oldVersion == 205) {
1060                // Move ICS updates to 300 range
1061                upgradeToVersion300(db);
1062                createEventsView = true;
1063                oldVersion = 300;
1064            }
1065            if (oldVersion == 300) {
1066                upgradeToVersion301(db);
1067                createEventsView = true;
1068                oldVersion++;
1069            }
1070            if (oldVersion == 301) {
1071                upgradeToVersion302(db);
1072                oldVersion++;
1073            }
1074            if (oldVersion == 302) {
1075                upgradeToVersion303(db);
1076                oldVersion++;
1077                createEventsView = true;
1078            }
1079            if (oldVersion == 303) {
1080                upgradeToVersion304(db);
1081                oldVersion++;
1082                createEventsView = true;
1083            }
1084            if (oldVersion == 304) {
1085                upgradeToVersion305(db);
1086                oldVersion++;
1087                createEventsView = true;
1088            }
1089            if (createEventsView) {
1090                createEventsView(db);
1091            }
1092            if (oldVersion != DATABASE_VERSION) {
1093                Log.e(TAG, "Need to recreate Calendar schema because of "
1094                        + "unknown Calendar database version: " + oldVersion);
1095                dropTables(db);
1096                bootstrapDB(db);
1097                oldVersion = DATABASE_VERSION;
1098            }
1099        } catch (SQLiteException e) {
1100            Log.e(TAG, "onUpgrade: SQLiteException, recreating db. " + e);
1101            dropTables(db);
1102            bootstrapDB(db);
1103            return; // this was lossy
1104        }
1105
1106        /**
1107         * db versions < 100 correspond to Froyo and earlier. Gingerbread bumped
1108         * the db versioning to 100. Honeycomb bumped it to 200. ICS will begin
1109         * in 300. At each major release we should jump to the next
1110         * centiversion.
1111         */
1112    }
1113
1114    /**
1115     * If the user_version of the database if between 59 and 66 (those versions has been deployed
1116     * with no primary key for the CalendarMetaData table)
1117     */
1118    private void recreateMetaDataAndInstances67(SQLiteDatabase db) {
1119        // Recreate the CalendarMetaData table with correct primary key
1120        db.execSQL("DROP TABLE CalendarMetaData;");
1121        createCalendarMetaDataTable59(db);
1122
1123        // Also clean the Instance table as this table may be corrupted
1124        db.execSQL("DELETE FROM Instances;");
1125    }
1126
1127    private static boolean fixAllDayTime(Time time, String timezone, Long timeInMillis) {
1128        time.set(timeInMillis);
1129        if(time.hour != 0 || time.minute != 0 || time.second != 0) {
1130            time.hour = 0;
1131            time.minute = 0;
1132            time.second = 0;
1133            return true;
1134        }
1135        return false;
1136    }
1137
1138    @VisibleForTesting
1139    void upgradeToVersion305(SQLiteDatabase db) {
1140        /*
1141         * Changes from version 304 to 305:
1142         * -Add CAL_SYNC columns up to 10
1143         * -Rename Calendars.access_level to calendar_access_level
1144         * -Rename calendars _sync_version to cal_sync7
1145         * -Rename calendars _sync_time to cal_sync8
1146         * -Rename displayName to calendar_displayName
1147         * -Rename _sync_local_id to sync_data2
1148         * -Rename htmlUri to sync_data3
1149         * -Rename events _sync_version to sync_data4
1150         * -Rename events _sync_time to sync_data5
1151         * -Rename commentsUri to sync_data6
1152         * -Migrate Events _sync_mark to sync_data8
1153         * -Change sync_data2 from INTEGER to TEXT
1154         * -Change sync_data8 from INTEGER to TEXT
1155         * -Add SYNC_DATA columns up to 10
1156         * -Add EVENT_COLOR to Events table
1157         */
1158
1159        // rename old table, create new table with updated layout
1160        db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1161        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1162        createCalendarsTable(db);
1163
1164        // copy fields from old to new
1165        db.execSQL("INSERT INTO Calendars (" +
1166                "_id, " +
1167                "account_name, " +
1168                "account_type, " +
1169                "_sync_id, " +
1170                "cal_sync7, " +             // rename from _sync_version
1171                "cal_sync8, " +             // rename from _sync_time
1172                "dirty, " +
1173                "name, " +
1174                "calendar_displayName, " +  // rename from displayName
1175                "calendar_color, " +
1176                "calendar_access_level, " + // rename from access_level
1177                "visible, " +
1178                "sync_events, " +
1179                "calendar_location, " +
1180                "calendar_timezone, " +
1181                "ownerAccount, " +
1182                "canOrganizerRespond, " +
1183                "canModifyTimeZone, " +
1184                "maxReminders, " +
1185                "allowedReminders, " +
1186                "deleted, " +
1187                "canPartiallyUpdate," +
1188                "cal_sync1, " +
1189                "cal_sync2, " +
1190                "cal_sync3, " +
1191                "cal_sync4, " +
1192                "cal_sync5, " +
1193                "cal_sync6) " +
1194                "SELECT " +
1195                "_id, " +
1196                "account_name, " +
1197                "account_type, " +
1198                "_sync_id, " +
1199                "_sync_version, " +
1200                "_sync_time, " +
1201                "dirty, " +
1202                "name, " +
1203                "displayName, " +
1204                "calendar_color, " +
1205                "access_level, " +
1206                "visible, " +
1207                "sync_events, " +
1208                "calendar_location, " +
1209                "calendar_timezone, " +
1210                "ownerAccount, " +
1211                "canOrganizerRespond, " +
1212                "canModifyTimeZone, " +
1213                "maxReminders, " +
1214                "allowedReminders, " +
1215                "deleted, " +
1216                "canPartiallyUpdate," +
1217                "cal_sync1, " +
1218                "cal_sync2, " +
1219                "cal_sync3, " +
1220                "cal_sync4, " +
1221                "cal_sync5, " +
1222                "cal_sync6 " +
1223                "FROM Calendars_Backup;");
1224
1225        // drop the old table
1226        db.execSQL("DROP TABLE Calendars_Backup;");
1227
1228        db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;");
1229        db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
1230        db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex");
1231        createEventsTable(db);
1232
1233        // copy fields from old to new
1234        db.execSQL("INSERT INTO Events (" +
1235                "_id, " +
1236                "_sync_id, " +
1237                "sync_data4, " +        // renamed from _sync_version
1238                "sync_data5, " +        // renamed from _sync_time
1239                "sync_data2, " +        // renamed from _sync_local_id
1240                "dirty, " +
1241                "sync_data8, " +        // renamed from _sync_mark
1242                "calendar_id, " +
1243                "sync_data3, " +        // renamed from htmlUri
1244                "title, " +
1245                "eventLocation, " +
1246                "description, " +
1247                "eventStatus, " +
1248                "selfAttendeeStatus, " +
1249                "sync_data6, " +        // renamed from commentsUri
1250                "dtstart, " +
1251                "dtend, " +
1252                "eventTimezone, " +
1253                "eventEndTimezone, " +
1254                "duration, " +
1255                "allDay, " +
1256                "accessLevel, " +
1257                "availability, " +
1258                "hasAlarm, " +
1259                "hasExtendedProperties, " +
1260                "rrule, " +
1261                "rdate, " +
1262                "exrule, " +
1263                "exdate, " +
1264                "original_id," +
1265                "original_sync_id, " +
1266                "originalInstanceTime, " +
1267                "originalAllDay, " +
1268                "lastDate, " +
1269                "hasAttendeeData, " +
1270                "guestsCanModify, " +
1271                "guestsCanInviteOthers, " +
1272                "guestsCanSeeGuests, " +
1273                "organizer, " +
1274                "deleted, " +
1275                "sync_data7," +
1276                "lastSynced," +
1277                "sync_data1) " +
1278
1279                "SELECT " +
1280                "_id, " +
1281                "_sync_id, " +
1282                "_sync_version, " +
1283                "_sync_time, " +
1284                "_sync_local_id, " +
1285                "dirty, " +
1286                "_sync_mark, " +
1287                "calendar_id, " +
1288                "htmlUri, " +
1289                "title, " +
1290                "eventLocation, " +
1291                "description, " +
1292                "eventStatus, " +
1293                "selfAttendeeStatus, " +
1294                "commentsUri, " +
1295                "dtstart, " +
1296                "dtend, " +
1297                "eventTimezone, " +
1298                "eventEndTimezone, " +
1299                "duration, " +
1300                "allDay, " +
1301                "accessLevel, " +
1302                "availability, " +
1303                "hasAlarm, " +
1304                "hasExtendedProperties, " +
1305                "rrule, " +
1306                "rdate, " +
1307                "exrule, " +
1308                "exdate, " +
1309                "original_id," +
1310                "original_sync_id, " +
1311                "originalInstanceTime, " +
1312                "originalAllDay, " +
1313                "lastDate, " +
1314                "hasAttendeeData, " +
1315                "guestsCanModify, " +
1316                "guestsCanInviteOthers, " +
1317                "guestsCanSeeGuests, " +
1318                "organizer, " +
1319                "deleted, " +
1320                "sync_data7," +
1321                "lastSynced," +
1322                "sync_data1 " +
1323
1324                "FROM Events_Backup;"
1325        );
1326
1327        db.execSQL("DROP TABLE Events_Backup;");
1328
1329        // Trigger to remove data tied to an event when we delete that event.
1330        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
1331                "BEGIN " +
1332                EVENTS_CLEANUP_TRIGGER_SQL +
1333                "END");
1334
1335        // Trigger to update exceptions when an original event updates its
1336        // _sync_id
1337        db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
1338    }
1339
1340    @VisibleForTesting
1341    void upgradeToVersion304(SQLiteDatabase db) {
1342        /*
1343         * Changes from version 303 to 304:
1344         * - add canPartiallyUpdate to Calendars table
1345         * - add sync_data7 to Calendars to Events table
1346         * - add lastSynced to Calendars to Events table
1347         */
1348        db.execSQL("ALTER TABLE Calendars ADD COLUMN canPartiallyUpdate INTEGER DEFAULT 0;");
1349        db.execSQL("ALTER TABLE Events ADD COLUMN sync_data7 TEXT;");
1350        db.execSQL("ALTER TABLE Events ADD COLUMN lastSynced INTEGER DEFAULT 0;");
1351    }
1352
1353    @VisibleForTesting
1354    void upgradeToVersion303(SQLiteDatabase db) {
1355        /*
1356         * Changes from version 302 to 303:
1357         * - change SYNCx columns to CAL_SYNCx
1358         */
1359
1360        // rename old table, create new table with updated layout
1361        db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1362        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1363        createCalendarsTable303(db);
1364
1365        // copy fields from old to new
1366        db.execSQL("INSERT INTO Calendars (" +
1367                "_id, " +
1368                "account_name, " +
1369                "account_type, " +
1370                "_sync_id, " +
1371                "_sync_version, " +
1372                "_sync_time, " +
1373                "dirty, " +
1374                "name, " +
1375                "displayName, " +
1376                "calendar_color, " +
1377                "access_level, " +
1378                "visible, " +
1379                "sync_events, " +
1380                "calendar_location, " +
1381                "calendar_timezone, " +
1382                "ownerAccount, " +
1383                "canOrganizerRespond, " +
1384                "canModifyTimeZone, " +
1385                "maxReminders, " +
1386                "allowedReminders, " +
1387                "deleted, " +
1388                "cal_sync1, " +     // rename from sync1
1389                "cal_sync2, " +     // rename from sync2
1390                "cal_sync3, " +     // rename from sync3
1391                "cal_sync4, " +     // rename from sync4
1392                "cal_sync5, " +     // rename from sync5
1393                "cal_sync6) " +     // rename from sync6
1394                "SELECT " +
1395                "_id, " +
1396                "account_name, " +
1397                "account_type, " +
1398                "_sync_id, " +
1399                "_sync_version, " +
1400                "_sync_time, " +
1401                "dirty, " +
1402                "name, " +
1403                "displayName, " +
1404                "calendar_color, " +
1405                "access_level, " +
1406                "visible, " +
1407                "sync_events, " +
1408                "calendar_location, " +
1409                "calendar_timezone, " +
1410                "ownerAccount, " +
1411                "canOrganizerRespond, " +
1412                "canModifyTimeZone, " +
1413                "maxReminders, " +
1414                "allowedReminders," +
1415                "deleted, " +
1416                "sync1, " +
1417                "sync2, " +
1418                "sync3, " +
1419                "sync4," +
1420                "sync5," +
1421                "sync6 " +
1422                "FROM Calendars_Backup;"
1423        );
1424
1425        // drop the old table
1426        db.execSQL("DROP TABLE Calendars_Backup;");
1427    }
1428
1429    @VisibleForTesting
1430    void upgradeToVersion302(SQLiteDatabase db) {
1431        /*
1432         * Changes from version 301 to 302
1433         * - Move Exchange eventEndTimezone values to SYNC_DATA1
1434         */
1435        db.execSQL("UPDATE Events SET sync_data1=eventEndTimezone WHERE calendar_id IN "
1436                + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');");
1437
1438        db.execSQL("UPDATE Events SET eventEndTimezone=NULL WHERE calendar_id IN "
1439                + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');");
1440    }
1441
1442    @VisibleForTesting
1443    void upgradeToVersion301(SQLiteDatabase db) {
1444        /*
1445         * Changes from version 300 to 301
1446         * - Added original_id column to Events table
1447         * - Added triggers to keep original_id and original_sync_id in sync
1448         */
1449
1450        db.execSQL("DROP TRIGGER IF EXISTS " + SYNC_ID_UPDATE_TRIGGER_NAME + ";");
1451
1452        db.execSQL("ALTER TABLE Events ADD COLUMN original_id INTEGER;");
1453
1454        // Fill in the original_id for all events that have an original_sync_id
1455        db.execSQL("UPDATE Events set original_id=" +
1456                "(SELECT Events2._id FROM Events AS Events2 " +
1457                        "WHERE Events2._sync_id=Events.original_sync_id) " +
1458                "WHERE Events.original_sync_id NOT NULL");
1459        // Trigger to update exceptions when an original event updates its
1460        // _sync_id
1461        db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
1462    }
1463
1464    @VisibleForTesting
1465    void upgradeToVersion300(SQLiteDatabase db) {
1466
1467        /*
1468         * Changes from version 205 to 300:
1469         * - rename _sync_account to account_name in Calendars table
1470         * - remove _sync_account from Events table
1471         * - rename _sync_account_type to account_type in Calendars table
1472         * - remove _sync_account_type from Events table
1473         * - rename _sync_dirty to dirty in Calendars/Events table
1474         * - rename color to calendar_color in Calendars table
1475         * - rename location to calendar_location in Calendars table
1476         * - rename timezone to calendar_timezone in Calendars table
1477         * - add allowedReminders in Calendars table
1478         * - rename visibility to accessLevel in Events table
1479         * - rename transparency to availability in Events table
1480         * - rename originalEvent to original_sync_id in Events table
1481         * - remove dtstart2 and dtend2 from Events table
1482         * - rename syncAdapterData to sync_data1 in Events table
1483         */
1484
1485        // rename old table, create new table with updated layout
1486        db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1487        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup;");
1488        createCalendarsTable300(db);
1489
1490        // copy fields from old to new
1491        db.execSQL("INSERT INTO Calendars (" +
1492                "_id, " +
1493                "account_name, " +          // rename from _sync_account
1494                "account_type, " +          // rename from _sync_account_type
1495                "_sync_id, " +
1496                "_sync_version, " +
1497                "_sync_time, " +
1498                "dirty, " +                 // rename from _sync_dirty
1499                "name, " +
1500                "displayName, " +
1501                "calendar_color, " +        // rename from color
1502                "access_level, " +
1503                "visible, " +
1504                "sync_events, " +
1505                "calendar_location, " +     // rename from location
1506                "calendar_timezone, " +     // rename from timezone
1507                "ownerAccount, " +
1508                "canOrganizerRespond, " +
1509                "canModifyTimeZone, " +
1510                "maxReminders, " +
1511                "allowedReminders," +
1512                "deleted, " +
1513                "sync1, " +
1514                "sync2, " +
1515                "sync3, " +
1516                "sync4," +
1517                "sync5," +
1518                "sync6) " +
1519
1520                "SELECT " +
1521                "_id, " +
1522                "_sync_account, " +
1523                "_sync_account_type, " +
1524                "_sync_id, " +
1525                "_sync_version, " +
1526                "_sync_time, " +
1527                "_sync_dirty, " +
1528                "name, " +
1529                "displayName, " +
1530                "color, " +
1531                "access_level, " +
1532                "visible, " +
1533                "sync_events, " +
1534                "location, " +
1535                "timezone, " +
1536                "ownerAccount, " +
1537                "canOrganizerRespond, " +
1538                "canModifyTimeZone, " +
1539                "maxReminders, " +
1540                "'0,1,2,3'," +
1541                "deleted, " +
1542                "sync1, " +
1543                "sync2, " +
1544                "sync3, " +
1545                "sync4, " +
1546                "sync5, " +
1547                "sync6 " +
1548                "FROM Calendars_Backup;"
1549        );
1550
1551        // drop the old table
1552        db.execSQL("DROP TABLE Calendars_Backup;");
1553
1554        db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;");
1555        db.execSQL("DROP TRIGGER IF EXISTS events_insert");
1556        db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
1557        db.execSQL("DROP INDEX IF EXISTS eventSyncAccountAndIdIndex");
1558        db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex");
1559        createEventsTable300(db);
1560
1561        // copy fields from old to new
1562        db.execSQL("INSERT INTO Events (" +
1563                "_id, " +
1564                "_sync_id, " +
1565                "_sync_version, " +
1566                "_sync_time, " +
1567                "_sync_local_id, " +
1568                "dirty, " +                 // renamed from _sync_dirty
1569                "_sync_mark, " +
1570                "calendar_id, " +
1571                "htmlUri, " +
1572                "title, " +
1573                "eventLocation, " +
1574                "description, " +
1575                "eventStatus, " +
1576                "selfAttendeeStatus, " +
1577                "commentsUri, " +
1578                "dtstart, " +
1579                "dtend, " +
1580                "eventTimezone, " +
1581                "eventEndTimezone, " +      // renamed from eventTimezone2
1582                "duration, " +
1583                "allDay, " +
1584                "accessLevel, " +           // renamed from visibility
1585                "availability, " +          // renamed from transparency
1586                "hasAlarm, " +
1587                "hasExtendedProperties, " +
1588                "rrule, " +
1589                "rdate, " +
1590                "exrule, " +
1591                "exdate, " +
1592                "original_sync_id, " +      // renamed from originalEvent
1593                "originalInstanceTime, " +
1594                "originalAllDay, " +
1595                "lastDate, " +
1596                "hasAttendeeData, " +
1597                "guestsCanModify, " +
1598                "guestsCanInviteOthers, " +
1599                "guestsCanSeeGuests, " +
1600                "organizer, " +
1601                "deleted, " +
1602                "sync_data1) " +             // renamed from syncAdapterData
1603
1604                "SELECT " +
1605                "_id, " +
1606                "_sync_id, " +
1607                "_sync_version, " +
1608                "_sync_time, " +
1609                "_sync_local_id, " +
1610                "_sync_dirty, " +
1611                "_sync_mark, " +
1612                "calendar_id, " +
1613                "htmlUri, " +
1614                "title, " +
1615                "eventLocation, " +
1616                "description, " +
1617                "eventStatus, " +
1618                "selfAttendeeStatus, " +
1619                "commentsUri, " +
1620                "dtstart, " +
1621                "dtend, " +
1622                "eventTimezone, " +
1623                "eventTimezone2, " +
1624                "duration, " +
1625                "allDay, " +
1626                "visibility, " +
1627                "transparency, " +
1628                "hasAlarm, " +
1629                "hasExtendedProperties, " +
1630                "rrule, " +
1631                "rdate, " +
1632                "exrule, " +
1633                "exdate, " +
1634                "originalEvent, " +
1635                "originalInstanceTime, " +
1636                "originalAllDay, " +
1637                "lastDate, " +
1638                "hasAttendeeData, " +
1639                "guestsCanModify, " +
1640                "guestsCanInviteOthers, " +
1641                "guestsCanSeeGuests, " +
1642                "organizer, " +
1643                "deleted, " +
1644                "syncAdapterData " +
1645
1646                "FROM Events_Backup;"
1647        );
1648
1649        db.execSQL("DROP TABLE Events_Backup;");
1650
1651        // Trigger to remove data tied to an event when we delete that event.
1652        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
1653                "BEGIN " +
1654                EVENTS_CLEANUP_TRIGGER_SQL +
1655                "END");
1656
1657    }
1658
1659    @VisibleForTesting
1660    void upgradeToVersion205(SQLiteDatabase db) {
1661        /*
1662         * Changes from version 204 to 205:
1663         * - rename+reorder "_sync_mark" to "sync6" (and change type from INTEGER to TEXT)
1664         * - rename "selected" to "visible"
1665         * - rename "organizerCanRespond" to "canOrganizerRespond"
1666         * - add "canModifyTimeZone"
1667         * - add "maxReminders"
1668         * - remove "_sync_local_id" (a/k/a _SYNC_DATA)
1669         */
1670
1671        // rename old table, create new table with updated layout
1672        db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1673        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1674        createCalendarsTable205(db);
1675
1676        // copy fields from old to new
1677        db.execSQL("INSERT INTO Calendars (" +
1678                "_id, " +
1679                "_sync_account, " +
1680                "_sync_account_type, " +
1681                "_sync_id, " +
1682                "_sync_version, " +
1683                "_sync_time, " +
1684                "_sync_dirty, " +
1685                "name, " +
1686                "displayName, " +
1687                "color, " +
1688                "access_level, " +
1689                "visible, " +                   // rename from "selected"
1690                "sync_events, " +
1691                "location, " +
1692                "timezone, " +
1693                "ownerAccount, " +
1694                "canOrganizerRespond, " +       // rename from "organizerCanRespond"
1695                "canModifyTimeZone, " +
1696                "maxReminders, " +
1697                "deleted, " +
1698                "sync1, " +
1699                "sync2, " +
1700                "sync3, " +
1701                "sync4," +
1702                "sync5," +
1703                "sync6) " +                     // rename/reorder from _sync_mark
1704                "SELECT " +
1705                "_id, " +
1706                "_sync_account, " +
1707                "_sync_account_type, " +
1708                "_sync_id, " +
1709                "_sync_version, " +
1710                "_sync_time, " +
1711                "_sync_dirty, " +
1712                "name, " +
1713                "displayName, " +
1714                "color, " +
1715                "access_level, " +
1716                "selected, " +
1717                "sync_events, " +
1718                "location, " +
1719                "timezone, " +
1720                "ownerAccount, " +
1721                "organizerCanRespond, " +
1722                "1, " +
1723                "5, " +
1724                "deleted, " +
1725                "sync1, " +
1726                "sync2, " +
1727                "sync3, " +
1728                "sync4, " +
1729                "sync5, " +
1730                "_sync_mark " +
1731                "FROM Calendars_Backup;"
1732        );
1733
1734        // set these fields appropriately for Exchange events
1735        db.execSQL("UPDATE Calendars SET canModifyTimeZone=0, maxReminders=1 " +
1736                "WHERE _sync_account_type='com.android.exchange'");
1737
1738        // drop the old table
1739        db.execSQL("DROP TABLE Calendars_Backup;");
1740    }
1741
1742    @VisibleForTesting
1743    void upgradeToVersion203(SQLiteDatabase db) {
1744        // Same as Gingerbread version 100
1745        Cursor cursor = db.rawQuery("SELECT value FROM CalendarCache WHERE key=?",
1746                new String[] {"timezoneDatabaseVersion"});
1747
1748        String oldTimezoneDbVersion = null;
1749        if (cursor != null && cursor.moveToNext()) {
1750            try {
1751                oldTimezoneDbVersion = cursor.getString(0);
1752            } finally {
1753                cursor.close();
1754            }
1755            // Also clean the CalendarCache table
1756            db.execSQL("DELETE FROM CalendarCache;");
1757        }
1758        initCalendarCacheTable203(db, oldTimezoneDbVersion);
1759
1760        // Same as Gingerbread version 101
1761        updateCalendarCacheTableTo203(db);
1762    }
1763
1764    @VisibleForTesting
1765    void upgradeToVersion202(SQLiteDatabase db) {
1766        // We will drop the "hidden" column from the calendar schema and add the "sync5" column
1767        db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1768
1769        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1770        createCalendarsTable202(db);
1771
1772        // Populate the new Calendars table and put into the "sync5" column the value of the
1773        // old "hidden" column
1774        db.execSQL("INSERT INTO Calendars (" +
1775                "_id, " +
1776                "_sync_account, " +
1777                "_sync_account_type, " +
1778                "_sync_id, " +
1779                "_sync_version, " +
1780                "_sync_time, " +
1781                "_sync_local_id, " +
1782                "_sync_dirty, " +
1783                "_sync_mark, " +
1784                "name, " +
1785                "displayName, " +
1786                "color, " +
1787                "access_level, " +
1788                "selected, " +
1789                "sync_events, " +
1790                "location, " +
1791                "timezone, " +
1792                "ownerAccount, " +
1793                "organizerCanRespond, " +
1794                "deleted, " +
1795                "sync1, " +
1796                "sync2, " +
1797                "sync3, " +
1798                "sync4," +
1799                "sync5) " +
1800                "SELECT " +
1801                "_id, " +
1802                "_sync_account, " +
1803                "_sync_account_type, " +
1804                "_sync_id, " +
1805                "_sync_version, " +
1806                "_sync_time, " +
1807                "_sync_local_id, " +
1808                "_sync_dirty, " +
1809                "_sync_mark, " +
1810                "name, " +
1811                "displayName, " +
1812                "color, " +
1813                "access_level, " +
1814                "selected, " +
1815                "sync_events, " +
1816                "location, " +
1817                "timezone, " +
1818                "ownerAccount, " +
1819                "organizerCanRespond, " +
1820                "deleted, " +
1821                "sync1, " +
1822                "sync2, " +
1823                "sync3, " +
1824                "sync4, " +
1825                "hidden " +
1826                "FROM Calendars_Backup;"
1827        );
1828
1829        // Drop the backup table
1830        db.execSQL("DROP TABLE Calendars_Backup;");
1831    }
1832
1833    @VisibleForTesting
1834    void upgradeToVersion201(SQLiteDatabase db) {
1835        db.execSQL("ALTER TABLE Calendars ADD COLUMN sync4 TEXT;");
1836    }
1837
1838    @VisibleForTesting
1839    void upgradeToVersion200(SQLiteDatabase db) {
1840        // we cannot use here a Calendar.Calendars,URL constant for "url" as we are trying to make
1841        // it disappear so we are keeping the hardcoded name "url" in all the SQLs
1842        db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1843
1844        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1845        createCalendarsTable200(db);
1846
1847        // Populate the new Calendars table except the SYNC2 / SYNC3 columns
1848        db.execSQL("INSERT INTO Calendars (" +
1849                "_id, " +
1850                "_sync_account, " +
1851                "_sync_account_type, " +
1852                "_sync_id, " +
1853                "_sync_version, " +
1854                "_sync_time, " +
1855                "_sync_local_id, " +
1856                "_sync_dirty, " +
1857                "_sync_mark, " +
1858                "name, " +
1859                "displayName, " +
1860                "color, " +
1861                "access_level, " +
1862                "selected, " +
1863                "sync_events, " +
1864                "location, " +
1865                "timezone, " +
1866                "ownerAccount, " +
1867                "organizerCanRespond, " +
1868                "deleted, " +
1869                "sync1) " +
1870                "SELECT " +
1871                "_id, " +
1872                "_sync_account, " +
1873                "_sync_account_type, " +
1874                "_sync_id, " +
1875                "_sync_version, " +
1876                "_sync_time, " +
1877                "_sync_local_id, " +
1878                "_sync_dirty, " +
1879                "_sync_mark, " +
1880                "name, " +
1881                "displayName, " +
1882                "color, " +
1883                "access_level, " +
1884                "selected, " +
1885                "sync_events, " +
1886                "location, " +
1887                "timezone, " +
1888                "ownerAccount, " +
1889                "organizerCanRespond, " +
1890                "0, " +
1891                "url " +
1892                "FROM Calendars_Backup;"
1893        );
1894
1895        // Populate SYNC2 and SYNC3 columns - SYNC1 represent the old "url" column
1896        // We will need to iterate over all the "com.google" type of calendars
1897        String selectSql = "SELECT _id, url" +
1898                " FROM Calendars_Backup" +
1899                " WHERE _sync_account_type='com.google'" +
1900                " AND url IS NOT NULL;";
1901
1902        String updateSql = "UPDATE Calendars SET " +
1903                "sync2=?, " + // edit Url
1904                "sync3=? " + // self Url
1905                "WHERE _id=?;";
1906
1907        Cursor cursor = db.rawQuery(selectSql, null /* selection args */);
1908        if (cursor != null && cursor.getCount() > 0) {
1909            try {
1910                Object[] bindArgs = new Object[3];
1911
1912                while (cursor.moveToNext()) {
1913                    Long id = cursor.getLong(0);
1914                    String url = cursor.getString(1);
1915                    String selfUrl = getSelfUrlFromEventsUrl(url);
1916                    String editUrl = getEditUrlFromEventsUrl(url);
1917
1918                    bindArgs[0] = editUrl;
1919                    bindArgs[1] = selfUrl;
1920                    bindArgs[2] = id;
1921
1922                    db.execSQL(updateSql, bindArgs);
1923                }
1924            } finally {
1925                cursor.close();
1926            }
1927        }
1928
1929        // Drop the backup table
1930        db.execSQL("DROP TABLE Calendars_Backup;");
1931    }
1932
1933    @VisibleForTesting
1934    static void upgradeToVersion69(SQLiteDatabase db) {
1935        // Clean up allDay events which could be in an invalid state from an earlier version
1936        // Some allDay events had hour, min, sec not set to zero, which throws elsewhere. This
1937        // will go through the allDay events and make sure they have proper values and are in the
1938        // correct timezone. Verifies that dtstart and dtend are in UTC and at midnight, that
1939        // eventTimezone is set to UTC, tries to make sure duration is in days, and that dtstart2
1940        // and dtend2 are at midnight in their timezone.
1941        final String sql = "SELECT _id, " +
1942                "dtstart, " +
1943                "dtend, " +
1944                "duration, " +
1945                "dtstart2, " +
1946                "dtend2, " +
1947                "eventTimezone, " +
1948                "eventTimezone2, " +
1949                "rrule " +
1950                "FROM Events " +
1951                "WHERE allDay=?";
1952        Cursor cursor = db.rawQuery(sql, new String[] {"1"});
1953        if (cursor != null) {
1954            try {
1955                String timezone;
1956                String timezone2;
1957                String duration;
1958                Long dtstart;
1959                Long dtstart2;
1960                Long dtend;
1961                Long dtend2;
1962                Time time = new Time();
1963                Long id;
1964                // some things need to be in utc so we call this frequently, cache to make faster
1965                final String utc = Time.TIMEZONE_UTC;
1966                while (cursor.moveToNext()) {
1967                    String rrule = cursor.getString(8);
1968                    id = cursor.getLong(0);
1969                    dtstart = cursor.getLong(1);
1970                    dtstart2 = null;
1971                    timezone = cursor.getString(6);
1972                    timezone2 = cursor.getString(7);
1973                    duration = cursor.getString(3);
1974
1975                    if (TextUtils.isEmpty(rrule)) {
1976                        // For non-recurring events dtstart and dtend should both have values
1977                        // and duration should be null.
1978                        dtend = cursor.getLong(2);
1979                        dtend2 = null;
1980                        // Since we made all three of these at the same time if timezone2 exists
1981                        // so should dtstart2 and dtend2.
1982                        if(!TextUtils.isEmpty(timezone2)) {
1983                            dtstart2 = cursor.getLong(4);
1984                            dtend2 = cursor.getLong(5);
1985                        }
1986
1987                        boolean update = false;
1988                        if (!TextUtils.equals(timezone, utc)) {
1989                            update = true;
1990                            timezone = utc;
1991                        }
1992
1993                        time.clear(timezone);
1994                        update |= fixAllDayTime(time, timezone, dtstart);
1995                        dtstart = time.normalize(false);
1996
1997                        time.clear(timezone);
1998                        update |= fixAllDayTime(time, timezone, dtend);
1999                        dtend = time.normalize(false);
2000
2001                        if (dtstart2 != null) {
2002                            time.clear(timezone2);
2003                            update |= fixAllDayTime(time, timezone2, dtstart2);
2004                            dtstart2 = time.normalize(false);
2005                        }
2006
2007                        if (dtend2 != null) {
2008                            time.clear(timezone2);
2009                            update |= fixAllDayTime(time, timezone2, dtend2);
2010                            dtend2 = time.normalize(false);
2011                        }
2012
2013                        if (!TextUtils.isEmpty(duration)) {
2014                            update = true;
2015                        }
2016
2017                        if (update) {
2018                            // enforce duration being null
2019                            db.execSQL("UPDATE Events SET " +
2020                                    "dtstart=?, " +
2021                                    "dtend=?, " +
2022                                    "dtstart2=?, " +
2023                                    "dtend2=?, " +
2024                                    "duration=?, " +
2025                                    "eventTimezone=?, " +
2026                                    "eventTimezone2=? " +
2027                                    "WHERE _id=?",
2028                                    new Object[] {
2029                                            dtstart,
2030                                            dtend,
2031                                            dtstart2,
2032                                            dtend2,
2033                                            null,
2034                                            timezone,
2035                                            timezone2,
2036                                            id}
2037                            );
2038                        }
2039
2040                    } else {
2041                        // For recurring events only dtstart and duration should be used.
2042                        // We ignore dtend since it will be overwritten if the event changes to a
2043                        // non-recurring event and won't be used otherwise.
2044                        if(!TextUtils.isEmpty(timezone2)) {
2045                            dtstart2 = cursor.getLong(4);
2046                        }
2047
2048                        boolean update = false;
2049                        if (!TextUtils.equals(timezone, utc)) {
2050                            update = true;
2051                            timezone = utc;
2052                        }
2053
2054                        time.clear(timezone);
2055                        update |= fixAllDayTime(time, timezone, dtstart);
2056                        dtstart = time.normalize(false);
2057
2058                        if (dtstart2 != null) {
2059                            time.clear(timezone2);
2060                            update |= fixAllDayTime(time, timezone2, dtstart2);
2061                            dtstart2 = time.normalize(false);
2062                        }
2063
2064                        if (TextUtils.isEmpty(duration)) {
2065                            // If duration was missing assume a 1 day duration
2066                            duration = "P1D";
2067                            update = true;
2068                        } else {
2069                            int len = duration.length();
2070                            // TODO fix durations in other formats as well
2071                            if (duration.charAt(0) == 'P' &&
2072                                    duration.charAt(len - 1) == 'S') {
2073                                int seconds = Integer.parseInt(duration.substring(1, len - 1));
2074                                int days = (seconds + DAY_IN_SECONDS - 1) / DAY_IN_SECONDS;
2075                                duration = "P" + days + "D";
2076                                update = true;
2077                            }
2078                        }
2079
2080                        if (update) {
2081                            // If there were other problems also enforce dtend being null
2082                            db.execSQL("UPDATE Events SET " +
2083                                    "dtstart=?, " +
2084                                    "dtend=?, " +
2085                                    "dtstart2=?, " +
2086                                    "dtend2=?, " +
2087                                    "duration=?," +
2088                                    "eventTimezone=?, " +
2089                                    "eventTimezone2=? " +
2090                                    "WHERE _id=?",
2091                                    new Object[] {
2092                                            dtstart,
2093                                            null,
2094                                            dtstart2,
2095                                            null,
2096                                            duration,
2097                                            timezone,
2098                                            timezone2,
2099                                            id}
2100                            );
2101                        }
2102                    }
2103                }
2104            } finally {
2105                cursor.close();
2106            }
2107        }
2108    }
2109
2110    private void upgradeToVersion66(SQLiteDatabase db) {
2111        // Add a column to indicate whether the event organizer can respond to his own events
2112        // The UI should not show attendee status for events in calendars with this column = 0
2113        db.execSQL("ALTER TABLE Calendars" +
2114                " ADD COLUMN organizerCanRespond INTEGER NOT NULL DEFAULT 1;");
2115    }
2116
2117    private void upgradeToVersion64(SQLiteDatabase db) {
2118        // Add a column that may be used by sync adapters
2119        db.execSQL("ALTER TABLE Events" +
2120                " ADD COLUMN syncAdapterData TEXT;");
2121    }
2122
2123    private void upgradeToVersion62(SQLiteDatabase db) {
2124        // New columns are to transition to having allDay events in the local timezone
2125        db.execSQL("ALTER TABLE Events" +
2126                " ADD COLUMN dtstart2 INTEGER;");
2127        db.execSQL("ALTER TABLE Events" +
2128                " ADD COLUMN dtend2 INTEGER;");
2129        db.execSQL("ALTER TABLE Events" +
2130                " ADD COLUMN eventTimezone2 TEXT;");
2131
2132        String[] allDayBit = new String[] {"0"};
2133        // Copy over all the data that isn't an all day event.
2134        db.execSQL("UPDATE Events SET " +
2135                "dtstart2=dtstart," +
2136                "dtend2=dtend," +
2137                "eventTimezone2=eventTimezone " +
2138                "WHERE allDay=?;",
2139                allDayBit /* selection args */);
2140
2141        // "cursor" iterates over all the calendars
2142        allDayBit[0] = "1";
2143        Cursor cursor = db.rawQuery("SELECT Events._id," +
2144                "dtstart," +
2145                "dtend," +
2146                "eventTimezone," +
2147                "timezone " +
2148                "FROM Events INNER JOIN Calendars " +
2149                "WHERE Events.calendar_id=Calendars._id" +
2150                " AND allDay=?",
2151                allDayBit /* selection args */);
2152
2153        Time oldTime = new Time();
2154        Time newTime = new Time();
2155        // Update the allday events in the new columns
2156        if (cursor != null) {
2157            try {
2158                String[] newData = new String[4];
2159                cursor.moveToPosition(-1);
2160                while (cursor.moveToNext()) {
2161                    long id = cursor.getLong(0); // Order from query above
2162                    long dtstart = cursor.getLong(1);
2163                    long dtend = cursor.getLong(2);
2164                    String eTz = cursor.getString(3); // current event timezone
2165                    String tz = cursor.getString(4); // Calendar timezone
2166                    //If there's no timezone for some reason use UTC by default.
2167                    if(eTz == null) {
2168                        eTz = Time.TIMEZONE_UTC;
2169                    }
2170
2171                    // Convert start time for all day events into the timezone of their calendar
2172                    oldTime.clear(eTz);
2173                    oldTime.set(dtstart);
2174                    newTime.clear(tz);
2175                    newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
2176                    newTime.normalize(false);
2177                    dtstart = newTime.toMillis(false /*ignoreDst*/);
2178
2179                    // Convert end time for all day events into the timezone of their calendar
2180                    oldTime.clear(eTz);
2181                    oldTime.set(dtend);
2182                    newTime.clear(tz);
2183                    newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
2184                    newTime.normalize(false);
2185                    dtend = newTime.toMillis(false /*ignoreDst*/);
2186
2187                    newData[0] = String.valueOf(dtstart);
2188                    newData[1] = String.valueOf(dtend);
2189                    newData[2] = tz;
2190                    newData[3] = String.valueOf(id);
2191                    db.execSQL("UPDATE Events SET " +
2192                            "dtstart2=?, " +
2193                            "dtend2=?, " +
2194                            "eventTimezone2=? " +
2195                            "WHERE _id=?",
2196                            newData);
2197                }
2198            } finally {
2199                cursor.close();
2200            }
2201        }
2202    }
2203
2204    private void upgradeToVersion61(SQLiteDatabase db) {
2205        db.execSQL("DROP TABLE IF EXISTS CalendarCache;");
2206
2207        // IF NOT EXISTS should be normal pattern for table creation
2208        db.execSQL("CREATE TABLE IF NOT EXISTS CalendarCache (" +
2209                "_id INTEGER PRIMARY KEY," +
2210                "key TEXT NOT NULL," +
2211                "value TEXT" +
2212                ");");
2213
2214        db.execSQL("INSERT INTO CalendarCache (" +
2215                "key, " +
2216                "value) VALUES (" +
2217                "'timezoneDatabaseVersion',"  +
2218                "'2009s'" +
2219                ");");
2220    }
2221
2222    private void upgradeToVersion60(SQLiteDatabase db) {
2223        // Switch to CalendarProvider2
2224        upgradeSyncState(db);
2225        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
2226        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " +
2227                "BEGIN " +
2228                ("DELETE FROM Events" +
2229                        " WHERE calendar_id=old._id;") +
2230                "END");
2231        db.execSQL("ALTER TABLE Events" +
2232                " ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;");
2233        db.execSQL("DROP TRIGGER IF EXISTS events_insert");
2234        // Trigger to set event's sync_account
2235        db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON Events " +
2236                "BEGIN " +
2237                "UPDATE Events" +
2238                " SET _sync_account=" +
2239                " (SELECT _sync_account FROM Calendars" +
2240                " WHERE Calendars._id=new.calendar_id)," +
2241                "_sync_account_type=" +
2242                " (SELECT _sync_account_type FROM Calendars" +
2243                " WHERE Calendars._id=new.calendar_id) " +
2244                "WHERE Events._id=new._id;" +
2245                "END");
2246        db.execSQL("DROP TABLE IF EXISTS DeletedEvents;");
2247        db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
2248        // Trigger to remove data tied to an event when we delete that event.
2249        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON Events " +
2250                "BEGIN " +
2251                ("DELETE FROM Instances" +
2252                    " WHERE event_id=old._id;" +
2253                "DELETE FROM EventsRawTimes" +
2254                    " WHERE event_id=old._id;" +
2255                "DELETE FROM Attendees" +
2256                    " WHERE event_id=old._id;" +
2257                "DELETE FROM Reminders" +
2258                    " WHERE event_id=old._id;" +
2259                "DELETE FROM CalendarAlerts" +
2260                    " WHERE event_id=old._id;" +
2261                "DELETE FROM ExtendedProperties" +
2262                    " WHERE event_id=old._id;") +
2263                "END");
2264        db.execSQL("DROP TRIGGER IF EXISTS attendees_update");
2265        db.execSQL("DROP TRIGGER IF EXISTS attendees_insert");
2266        db.execSQL("DROP TRIGGER IF EXISTS attendees_delete");
2267        db.execSQL("DROP TRIGGER IF EXISTS reminders_update");
2268        db.execSQL("DROP TRIGGER IF EXISTS reminders_insert");
2269        db.execSQL("DROP TRIGGER IF EXISTS reminders_delete");
2270        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update");
2271        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert");
2272        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete");
2273    }
2274
2275    private void upgradeToVersion59(SQLiteDatabase db) {
2276        db.execSQL("DROP TABLE IF EXISTS BusyBits;");
2277        db.execSQL("CREATE TEMPORARY TABLE CalendarMetaData_Backup(" +
2278                "_id," +
2279                "localTimezone," +
2280                "minInstance," +
2281                "maxInstance" +
2282                ");");
2283        db.execSQL("INSERT INTO CalendarMetaData_Backup " +
2284                "SELECT " +
2285                "_id," +
2286                "localTimezone," +
2287                "minInstance," +
2288                "maxInstance" +
2289                " FROM CalendarMetaData;");
2290        db.execSQL("DROP TABLE CalendarMetaData;");
2291        createCalendarMetaDataTable59(db);
2292        db.execSQL("INSERT INTO CalendarMetaData " +
2293                "SELECT " +
2294                "_id," +
2295                "localTimezone," +
2296                "minInstance," +
2297                "maxInstance" +
2298                " FROM CalendarMetaData_Backup;");
2299        db.execSQL("DROP TABLE CalendarMetaData_Backup;");
2300    }
2301
2302    private void upgradeToVersion57(SQLiteDatabase db) {
2303        db.execSQL("ALTER TABLE Events" +
2304                " ADD COLUMN guestsCanModify" +
2305                " INTEGER NOT NULL DEFAULT 0;");
2306        db.execSQL("ALTER TABLE Events" +
2307                " ADD COLUMN guestsCanInviteOthers" +
2308                " INTEGER NOT NULL DEFAULT 1;");
2309        db.execSQL("ALTER TABLE Events" +
2310                " ADD COLUMN guestsCanSeeGuests" +
2311                " INTEGER NOT NULL DEFAULT 1;");
2312        db.execSQL("ALTER TABLE Events" +
2313                " ADD COLUMN organizer" +
2314                " STRING;");
2315        db.execSQL("UPDATE Events SET organizer=" +
2316                "(SELECT attendeeEmail" +
2317                " FROM Attendees"  +
2318                " WHERE " +
2319                "Attendees.event_id=" +
2320                "Events._id" +
2321                " AND " +
2322                "Attendees.attendeeRelationship=2);");
2323    }
2324
2325    private void upgradeToVersion56(SQLiteDatabase db) {
2326        db.execSQL("ALTER TABLE Calendars" +
2327                " ADD COLUMN ownerAccount TEXT;");
2328        db.execSQL("ALTER TABLE Events" +
2329                " ADD COLUMN hasAttendeeData INTEGER NOT NULL DEFAULT 0;");
2330
2331        // Clear _sync_dirty to avoid a client-to-server sync that could blow away
2332        // server attendees.
2333        // Clear _sync_version to pull down the server's event (with attendees)
2334        // Change the URLs from full-selfattendance to full
2335        db.execSQL("UPDATE Events"
2336                + " SET _sync_dirty=0, "
2337                + "_sync_version=NULL, "
2338                + "_sync_id="
2339                + "REPLACE(_sync_id, " +
2340                    "'/private/full-selfattendance', '/private/full'),"
2341                + "commentsUri="
2342                + "REPLACE(commentsUri, " +
2343                    "'/private/full-selfattendance', '/private/full');");
2344
2345        db.execSQL("UPDATE Calendars"
2346                + " SET url="
2347                + "REPLACE(url, '/private/full-selfattendance', '/private/full');");
2348
2349        // "cursor" iterates over all the calendars
2350        Cursor cursor = db.rawQuery("SELECT _id, " +
2351                "url FROM Calendars",
2352                null /* selection args */);
2353        // Add the owner column.
2354        if (cursor != null) {
2355            try {
2356                final String updateSql = "UPDATE Calendars" +
2357                        " SET ownerAccount=?" +
2358                        " WHERE _id=?";
2359                while (cursor.moveToNext()) {
2360                    Long id = cursor.getLong(0);
2361                    String url = cursor.getString(1);
2362                    String owner = calendarEmailAddressFromFeedUrl(url);
2363                    db.execSQL(updateSql, new Object[] {owner, id});
2364                }
2365            } finally {
2366                cursor.close();
2367            }
2368        }
2369    }
2370
2371    private void upgradeResync(SQLiteDatabase db) {
2372        // Delete sync state, so all records will be re-synced.
2373        db.execSQL("DELETE FROM _sync_state;");
2374
2375        // "cursor" iterates over all the calendars
2376        Cursor cursor = db.rawQuery("SELECT _sync_account," +
2377                "_sync_account_type,url FROM Calendars",
2378                null /* selection args */);
2379        if (cursor != null) {
2380            try {
2381                while (cursor.moveToNext()) {
2382                    String accountName = cursor.getString(0);
2383                    String accountType = cursor.getString(1);
2384                    final Account account = new Account(accountName, accountType);
2385                    String calendarUrl = cursor.getString(2);
2386                    scheduleSync(account, false /* two-way sync */, calendarUrl);
2387                }
2388            } finally {
2389                cursor.close();
2390            }
2391        }
2392    }
2393
2394    private void upgradeToVersion55(SQLiteDatabase db) {
2395        db.execSQL("ALTER TABLE Calendars ADD COLUMN " +
2396                "_sync_account_type TEXT;");
2397        db.execSQL("ALTER TABLE Events ADD COLUMN " +
2398                "_sync_account_type TEXT;");
2399        db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;");
2400        db.execSQL("UPDATE Calendars"
2401                + " SET _sync_account_type='com.google'"
2402                + " WHERE _sync_account IS NOT NULL");
2403        db.execSQL("UPDATE Events"
2404                + " SET _sync_account_type='com.google'"
2405                + " WHERE _sync_account IS NOT NULL");
2406        db.execSQL("UPDATE DeletedEvents"
2407                + " SET _sync_account_type='com.google'"
2408                + " WHERE _sync_account IS NOT NULL");
2409        Log.w(TAG, "re-creating eventSyncAccountAndIdIndex");
2410        db.execSQL("DROP INDEX eventSyncAccountAndIdIndex");
2411        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events ("
2412                + "_sync_account_type, "
2413                + "_sync_account, "
2414                + "_sync_id);");
2415    }
2416
2417    private void upgradeToVersion54(SQLiteDatabase db) {
2418        Log.w(TAG, "adding eventSyncAccountAndIdIndex");
2419        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events ("
2420                + "_sync_account, _sync_id);");
2421    }
2422
2423    private void upgradeToVersion53(SQLiteDatabase db) {
2424        Log.w(TAG, "Upgrading CalendarAlerts table");
2425        db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " +
2426                "creationTime INTEGER NOT NULL DEFAULT 0;");
2427        db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " +
2428                "receivedTime INTEGER NOT NULL DEFAULT 0;");
2429        db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " +
2430                "notifyTime INTEGER NOT NULL DEFAULT 0;");
2431    }
2432
2433    private void upgradeToVersion52(SQLiteDatabase db) {
2434        // We added "originalAllDay" to the Events table to keep track of
2435        // the allDay status of the original recurring event for entries
2436        // that are exceptions to that recurring event.  We need this so
2437        // that we can format the date correctly for the "originalInstanceTime"
2438        // column when we make a change to the recurrence exception and
2439        // send it to the server.
2440        db.execSQL("ALTER TABLE Events ADD COLUMN " +
2441                "originalAllDay INTEGER;");
2442
2443        // Iterate through the Events table and for each recurrence
2444        // exception, fill in the correct value for "originalAllDay",
2445        // if possible.  The only times where this might not be possible
2446        // are (1) the original recurring event no longer exists, or
2447        // (2) the original recurring event does not yet have a _sync_id
2448        // because it was created on the phone and hasn't been synced to the
2449        // server yet.  In both cases the originalAllDay field will be set
2450        // to null.  In the first case we don't care because the recurrence
2451        // exception will not be displayed and we won't be able to make
2452        // any changes to it (and even if we did, the server should ignore
2453        // them, right?).  In the second case, the calendar client already
2454        // disallows making changes to an instance of a recurring event
2455        // until the recurring event has been synced to the server so the
2456        // second case should never occur.
2457
2458        // "cursor" iterates over all the recurrences exceptions.
2459        Cursor cursor = db.rawQuery("SELECT _id," +
2460                "originalEvent" +
2461                " FROM Events" +
2462                " WHERE originalEvent IS NOT NULL",
2463                null /* selection args */);
2464        if (cursor != null) {
2465            try {
2466                while (cursor.moveToNext()) {
2467                    long id = cursor.getLong(0);
2468                    String originalEvent = cursor.getString(1);
2469
2470                    // Find the original recurring event (if it exists)
2471                    Cursor recur = db.rawQuery("SELECT allDay" +
2472                            " FROM Events" +
2473                            " WHERE _sync_id=?",
2474                            new String[] {originalEvent});
2475                    if (recur == null) {
2476                        continue;
2477                    }
2478
2479                    try {
2480                        // Fill in the "originalAllDay" field of the
2481                        // recurrence exception with the "allDay" value
2482                        // from the recurring event.
2483                        if (recur.moveToNext()) {
2484                            int allDay = recur.getInt(0);
2485                            db.execSQL("UPDATE Events" +
2486                                    " SET originalAllDay=" + allDay +
2487                                    " WHERE _id="+id);
2488                        }
2489                    } finally {
2490                        recur.close();
2491                    }
2492                }
2493            } finally {
2494                cursor.close();
2495            }
2496        }
2497    }
2498
2499    private void upgradeToVersion51(SQLiteDatabase db) {
2500        Log.w(TAG, "Upgrading DeletedEvents table");
2501
2502        // We don't have enough information to fill in the correct
2503        // value of the calendar_id for old rows in the DeletedEvents
2504        // table, but rows in that table are transient so it is unlikely
2505        // that there are any rows.  Plus, the calendar_id is used only
2506        // when deleting a calendar, which is a rare event.  All new rows
2507        // will have the correct calendar_id.
2508        db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;");
2509
2510        // Trigger to remove a calendar's events when we delete the calendar
2511        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
2512        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " +
2513                "BEGIN " +
2514                "DELETE FROM Events WHERE calendar_id=" +
2515                    "old._id;" +
2516                "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" +
2517                "END");
2518        db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted");
2519    }
2520
2521    private void dropTables(SQLiteDatabase db) {
2522        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDARS + ";");
2523        db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS + ";");
2524        db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS_RAW_TIMES + ";");
2525        db.execSQL("DROP TABLE IF EXISTS " + Tables.INSTANCES + ";");
2526        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_META_DATA + ";");
2527        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";");
2528        db.execSQL("DROP TABLE IF EXISTS " + Tables.ATTENDEES + ";");
2529        db.execSQL("DROP TABLE IF EXISTS " + Tables.REMINDERS + ";");
2530        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_ALERTS + ";");
2531        db.execSQL("DROP TABLE IF EXISTS " + Tables.EXTENDED_PROPERTIES + ";");
2532    }
2533
2534    @Override
2535    public synchronized SQLiteDatabase getWritableDatabase() {
2536        SQLiteDatabase db = super.getWritableDatabase();
2537        return db;
2538    }
2539
2540    public SyncStateContentProviderHelper getSyncState() {
2541        return mSyncState;
2542    }
2543
2544    /**
2545     * Schedule a calendar sync for the account.
2546     * @param account the account for which to schedule a sync
2547     * @param uploadChangesOnly if set, specify that the sync should only send
2548     *   up local changes.  This is typically used for a local sync, a user override of
2549     *   too many deletions, or a sync after a calendar is unselected.
2550     * @param url the url feed for the calendar to sync (may be null, in which case a poll of
2551     *   all feeds is done.)
2552     */
2553    void scheduleSync(Account account, boolean uploadChangesOnly, String url) {
2554        Bundle extras = new Bundle();
2555        if (uploadChangesOnly) {
2556            extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly);
2557        }
2558        if (url != null) {
2559            extras.putString("feed", url);
2560            extras.putBoolean(ContentResolver.SYNC_EXTRAS_MANUAL, true);
2561        }
2562        ContentResolver.requestSync(account, Calendar.Calendars.CONTENT_URI.getAuthority(), extras);
2563    }
2564
2565    private static void createEventsView(SQLiteDatabase db) {
2566        db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";");
2567        String eventsSelect = "SELECT "
2568                + Tables.EVENTS + "." + Calendar.Events._ID + " AS " + Calendar.Events._ID + ","
2569                + Calendar.Events.TITLE + ","
2570                + Calendar.Events.DESCRIPTION + ","
2571                + Calendar.Events.EVENT_LOCATION + ","
2572                + Calendar.Events.EVENT_COLOR + ","
2573                + Calendar.Events.STATUS + ","
2574                + Calendar.Events.SELF_ATTENDEE_STATUS + ","
2575                + Calendar.Events.DTSTART + ","
2576                + Calendar.Events.DTEND + ","
2577                + Calendar.Events.DURATION + ","
2578                + Calendar.Events.EVENT_TIMEZONE + ","
2579                + Calendar.Events.EVENT_END_TIMEZONE + ","
2580                + Calendar.Events.ALL_DAY + ","
2581                + Calendar.Events.ACCESS_LEVEL + ","
2582                + Calendar.Events.AVAILABILITY + ","
2583                + Calendar.Events.HAS_ALARM + ","
2584                + Calendar.Events.HAS_EXTENDED_PROPERTIES + ","
2585                + Calendar.Events.RRULE + ","
2586                + Calendar.Events.RDATE + ","
2587                + Calendar.Events.EXRULE + ","
2588                + Calendar.Events.EXDATE + ","
2589                + Calendar.Events.ORIGINAL_SYNC_ID + ","
2590                + Calendar.Events.ORIGINAL_ID + ","
2591                + Calendar.Events.ORIGINAL_INSTANCE_TIME + ","
2592                + Calendar.Events.ORIGINAL_ALL_DAY + ","
2593                + Calendar.Events.LAST_DATE + ","
2594                + Calendar.Events.HAS_ATTENDEE_DATA + ","
2595                + Calendar.Events.CALENDAR_ID + ","
2596                + Calendar.Events.GUESTS_CAN_INVITE_OTHERS + ","
2597                + Calendar.Events.GUESTS_CAN_MODIFY + ","
2598                + Calendar.Events.GUESTS_CAN_SEE_GUESTS + ","
2599                + Calendar.Events.ORGANIZER + ","
2600                + Calendar.Events.SYNC_DATA1 + ","
2601                + Calendar.Events.SYNC_DATA2 + ","
2602                + Calendar.Events.SYNC_DATA3 + ","
2603                + Calendar.Events.SYNC_DATA4 + ","
2604                + Calendar.Events.SYNC_DATA5 + ","
2605                + Calendar.Events.SYNC_DATA6 + ","
2606                + Calendar.Events.SYNC_DATA7 + ","
2607                + Calendar.Events.SYNC_DATA8 + ","
2608                + Calendar.Events.SYNC_DATA9 + ","
2609                + Calendar.Events.SYNC_DATA10 + ","
2610                + Tables.EVENTS + "." + Calendar.Events.DELETED
2611                + " AS " + Calendar.Events.DELETED + ","
2612                + Tables.EVENTS + "." + Calendar.Events._SYNC_ID
2613                + " AS " + Calendar.Events._SYNC_ID + ","
2614                + Tables.EVENTS + "." + Calendar.Events.DIRTY
2615                + " AS " + Calendar.Events.DIRTY + ","
2616                + Calendar.Events.LAST_SYNCED + ","
2617                + Tables.CALENDARS + "." + Calendar.Calendars.ACCOUNT_NAME
2618                + " AS " + Calendar.Events.ACCOUNT_NAME + ","
2619                + Tables.CALENDARS + "." + Calendar.Calendars.ACCOUNT_TYPE
2620                + " AS " + Calendar.Events.ACCOUNT_TYPE + ","
2621                + Calendar.Calendars.CALENDAR_TIME_ZONE + ","
2622                + Calendar.Calendars.CALENDAR_DISPLAY_NAME + ","
2623                + Calendar.Calendars.CALENDAR_LOCATION + ","
2624                + Calendar.Calendars.VISIBLE + ","
2625                + Calendar.Calendars.CALENDAR_COLOR + ","
2626                + Calendar.Calendars.CALENDAR_ACCESS_LEVEL + ","
2627                + Calendar.Calendars.MAX_REMINDERS + ","
2628                + Calendar.Calendars.ALLOWED_REMINDERS + ","
2629                + Calendar.Calendars.CAN_ORGANIZER_RESPOND + ","
2630                + Calendar.Calendars.CAN_MODIFY_TIME_ZONE + ","
2631                + Calendar.Calendars.CAN_PARTIALLY_UPDATE + ","
2632                + Calendar.Calendars.CAL_SYNC1 + ","
2633                + Calendar.Calendars.CAL_SYNC2 + ","
2634                + Calendar.Calendars.CAL_SYNC3 + ","
2635                + Calendar.Calendars.CAL_SYNC4 + ","
2636                + Calendar.Calendars.CAL_SYNC5 + ","
2637                + Calendar.Calendars.CAL_SYNC6 + ","
2638                + Calendar.Calendars.CAL_SYNC7 + ","
2639                + Calendar.Calendars.CAL_SYNC8 + ","
2640                + Calendar.Calendars.CAL_SYNC9 + ","
2641                + Calendar.Calendars.CAL_SYNC10 + ","
2642                + Calendar.Calendars.OWNER_ACCOUNT + ","
2643                + Calendar.Calendars.SYNC_EVENTS
2644                + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS
2645                + " ON (" + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID
2646                + "=" + Tables.CALENDARS + "." + Calendar.Calendars._ID
2647                + ")";
2648
2649        db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect);
2650    }
2651
2652    /**
2653     * Extracts the calendar email from a calendar feed url.
2654     * @param feed the calendar feed url
2655     * @return the calendar email that is in the feed url or null if it can't
2656     * find the email address.
2657     * TODO: this is duplicated in CalendarSyncAdapter; move to a library
2658     */
2659    public static String calendarEmailAddressFromFeedUrl(String feed) {
2660        // Example feed url:
2661        // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees
2662        String[] pathComponents = feed.split("/");
2663        if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) {
2664            try {
2665                return URLDecoder.decode(pathComponents[5], "UTF-8");
2666            } catch (UnsupportedEncodingException e) {
2667                Log.e(TAG, "unable to url decode the email address in calendar " + feed);
2668                return null;
2669            }
2670        }
2671
2672        Log.e(TAG, "unable to find the email address in calendar " + feed);
2673        return null;
2674    }
2675
2676    /**
2677     * Get a "allcalendars" url from a "private/full" or "private/free-busy" url
2678     * @param url
2679     * @return the rewritten Url
2680     *
2681     * For example:
2682     *
2683     *      http://www.google.com/calendar/feeds/joe%40joe.com/private/full
2684     *      http://www.google.com/calendar/feeds/joe%40joe.com/private/free-busy
2685     *
2686     * will be rewriten into:
2687     *
2688     *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
2689     *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
2690     */
2691    @VisibleForTesting
2692    private static String getAllCalendarsUrlFromEventsUrl(String url) {
2693        if (url == null) {
2694            if (Log.isLoggable(TAG, Log.DEBUG)) {
2695                Log.d(TAG, "Cannot get AllCalendars url from a NULL url");
2696            }
2697            return null;
2698        }
2699        if (url.contains("/private/full")) {
2700            return url.replace("/private/full", "").
2701                    replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
2702        }
2703        if (url.contains("/private/free-busy")) {
2704            return url.replace("/private/free-busy", "").
2705                    replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
2706        }
2707        // Just log as we dont recognize the provided Url
2708        if (Log.isLoggable(TAG, Log.DEBUG)) {
2709            Log.d(TAG, "Cannot get AllCalendars url from the following url: " + url);
2710        }
2711        return null;
2712    }
2713
2714    /**
2715     * Get "selfUrl" from "events url"
2716     * @param url the Events url (either "private/full" or "private/free-busy"
2717     * @return the corresponding allcalendar url
2718     */
2719    private static String getSelfUrlFromEventsUrl(String url) {
2720        return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
2721    }
2722
2723    /**
2724     * Get "editUrl" from "events url"
2725     * @param url the Events url (either "private/full" or "private/free-busy"
2726     * @return the corresponding allcalendar url
2727     */
2728    private static String getEditUrlFromEventsUrl(String url) {
2729        return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
2730    }
2731
2732    /**
2733     * Rewrite the url from "http" to "https" scheme
2734     * @param url the url to rewrite
2735     * @return the rewritten URL
2736     */
2737    private static String rewriteUrlFromHttpToHttps(String url) {
2738        if (url == null) {
2739            if (Log.isLoggable(TAG, Log.DEBUG)) {
2740                Log.d(TAG, "Cannot rewrite a NULL url");
2741            }
2742            return null;
2743        }
2744        if (url.startsWith(SCHEMA_HTTPS)) {
2745            return url;
2746        }
2747        if (!url.startsWith(SCHEMA_HTTP)) {
2748            throw new IllegalArgumentException("invalid url parameter, unknown scheme: " + url);
2749        }
2750        return SCHEMA_HTTPS + url.substring(SCHEMA_HTTP.length());
2751    }
2752
2753    protected void duplicateEvent(final long id) {
2754        final SQLiteDatabase db = getWritableDatabase();
2755        final long canPartiallyUpdate = DatabaseUtils.longForQuery(
2756                db,
2757                "SELECT " + Calendar.Calendars.CAN_PARTIALLY_UPDATE + " FROM " + Views.EVENTS
2758                        + " WHERE " + Events._ID + " = ?",
2759                new String[]{String.valueOf(id)});
2760        if (canPartiallyUpdate == 0) {
2761            return;
2762        }
2763
2764        db.execSQL("INSERT INTO " + CalendarDatabaseHelper.Tables.EVENTS
2765                + "  (" + LAST_SYNCED_EVENT_COLUMNS + ","
2766                +         Events.DIRTY + "," + Events.LAST_SYNCED + ")"
2767                + " SELECT " + LAST_SYNCED_EVENT_COLUMNS + ", 0, 1"
2768                + " FROM " + Tables.EVENTS
2769                + " WHERE "  + Events._ID + " = ? AND " + Events.DIRTY + " = ?",
2770                new Object[]{
2771                        id,
2772                        0, // Events.DIRTY
2773                });
2774        final long newId = DatabaseUtils.longForQuery(
2775                db, "SELECT CASE changes() WHEN 0 THEN -1 ELSE last_insert_rowid() END", null);
2776        if (newId < 0) {
2777            return;
2778        }
2779
2780        if (Log.isLoggable(TAG, Log.VERBOSE)) {
2781            Log.v(TAG, "Duplicating event " + id + " into new event " + newId);
2782        }
2783
2784        copyEventRelatedTables(db, newId, id);
2785    }
2786
2787    static void copyEventRelatedTables(SQLiteDatabase db, long newId, long id) {
2788        db.execSQL("INSERT INTO " + Tables.REMINDERS
2789                + " ( "  + Calendar.Reminders.EVENT_ID + ", " + LAST_SYNCED_REMINDER_COLUMNS + ") "
2790                + "SELECT ?," + LAST_SYNCED_REMINDER_COLUMNS
2791                + " FROM " + Tables.REMINDERS
2792                + " WHERE " + Calendar.Reminders.EVENT_ID + " = ?",
2793                new Object[] {newId, id});
2794        db.execSQL("INSERT INTO "
2795                + Tables.ATTENDEES
2796                + " (" + Calendar.Attendees.EVENT_ID + "," + LAST_SYNCED_ATTENDEE_COLUMNS + ") "
2797                + "SELECT ?," + LAST_SYNCED_ATTENDEE_COLUMNS + " FROM " + Tables.ATTENDEES
2798                + " WHERE " + Calendar.Attendees.EVENT_ID + " = ?",
2799                new Object[] {newId, id});
2800        db.execSQL("INSERT INTO " + Tables.EXTENDED_PROPERTIES
2801                + " (" + Calendar.ExtendedProperties.EVENT_ID + ","
2802                + LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS + ") "
2803                + "SELECT ?, " + LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS
2804                + " FROM " + Tables.EXTENDED_PROPERTIES
2805                + " WHERE " + Calendar.ExtendedProperties.EVENT_ID + " = ?",
2806                new Object[]{newId, id});
2807    }
2808
2809    protected void removeDuplicateEvent(final long id) {
2810        final SQLiteDatabase db = getWritableDatabase();
2811        final Cursor cursor = db.rawQuery("SELECT " + Events._ID + " FROM " + Tables.EVENTS
2812                + " WHERE " + Events._SYNC_ID
2813                + " = (SELECT " + Events._SYNC_ID
2814                + " FROM " + Tables.EVENTS
2815                + " WHERE " + Events._ID + " = ?) "
2816                + "AND " + Events.LAST_SYNCED + " = ?",
2817                new String[]{
2818                        String.valueOf(id),
2819                        "1", // Events.LAST_SYNCED
2820                });
2821        try {
2822            // there should only be at most one but this can't hurt
2823            if (cursor.moveToNext()) {
2824                final long dupId = cursor.getLong(0);
2825
2826                if (Log.isLoggable(TAG, Log.VERBOSE)) {
2827                    Log.v(TAG, "Removing duplicate event " + dupId + " of original event " + id);
2828                }
2829                // triggers will clean up related tables.
2830                db.execSQL("DELETE FROM Events WHERE " + Events._ID + " = ?", new Object[]{dupId});
2831            }
2832        } finally {
2833          cursor.close();
2834        }
2835    }
2836}
2837