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