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