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