CalendarDatabaseHelper.java revision c2d2953fa4ac4bf9066f40d97858e69e519269f1 
1/*
2 * Copyright (C) 2009 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *      http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License
15 */
16
17package com.android.providers.calendar;
18
19import com.google.common.annotations.VisibleForTesting;
20
21import com.android.internal.content.SyncStateContentProviderHelper;
22
23import android.accounts.Account;
24import android.content.ContentResolver;
25import android.content.ContentValues;
26import android.content.Context;
27import android.content.res.Resources;
28import android.database.Cursor;
29import android.database.DatabaseUtils;
30import android.database.sqlite.SQLiteDatabase;
31import android.database.sqlite.SQLiteException;
32import android.database.sqlite.SQLiteOpenHelper;
33import android.os.Bundle;
34import android.provider.Calendar;
35import android.provider.ContactsContract;
36import android.provider.SyncStateContract;
37import android.text.TextUtils;
38import android.text.format.Time;
39import android.util.Log;
40
41import java.io.UnsupportedEncodingException;
42import java.net.URLDecoder;
43import java.util.TimeZone;
44
45/**
46 * Database helper for calendar. Designed as a singleton to make sure that all
47 * {@link android.content.ContentProvider} users get the same reference.
48 */
49/* package */ class CalendarDatabaseHelper extends SQLiteOpenHelper {
50
51    private static final String TAG = "CalendarDatabaseHelper";
52
53    private static final boolean LOGD = false;
54
55    private static final String DATABASE_NAME = "calendar.db";
56
57    private static final int DAY_IN_SECONDS = 24 * 60 * 60;
58
59    // Note: if you update the version number, you must also update the code
60    // in upgradeDatabase() to modify the database (gracefully, if possible).
61    static final int DATABASE_VERSION = 203;
62
63    private static final int PRE_FROYO_SYNC_STATE_VERSION = 3;
64
65    public interface Tables {
66        public static final String CALENDARS = "Calendars";
67        public static final String EVENTS = "Events";
68        public static final String EVENTS_RAW_TIMES = "EventsRawTimes";
69        public static final String INSTANCES = "Instances";
70        public static final String ATTENDEES = "Attendees";
71        public static final String REMINDERS = "Reminders";
72        public static final String CALENDAR_ALERTS = "CalendarAlerts";
73        public static final String EXTENDED_PROPERTIES = "ExtendedProperties";
74        public static final String CALENDAR_META_DATA = "CalendarMetaData";
75        public static final String CALENDAR_CACHE = "CalendarCache";
76        public static final String SYNC_STATE = "_sync_state";
77        public static final String SYNC_STATE_META = "_sync_state_metadata";
78    }
79
80    public interface Views {
81      public static final String EVENTS = "view_events";
82    }
83
84    // Copied from SyncStateContentProviderHelper.  Don't really want to make them public there.
85    private static final String SYNC_STATE_META_VERSION_COLUMN = "version";
86
87    private static final String AFTER_EVENT_INSERT_SQL =
88            "UPDATE " + Tables.EVENTS +
89            " SET " + Calendar.Events._SYNC_ACCOUNT + "=" +
90            " (SELECT " + Calendar.Calendars._SYNC_ACCOUNT + " FROM " + Tables.CALENDARS +
91            "   WHERE " + Tables.CALENDARS + "." + Calendar.Calendars._ID +
92                        "=new." + Calendar.Events.CALENDAR_ID + ")," +
93            Calendar.SyncColumns._SYNC_ACCOUNT_TYPE + "=" +
94            " (SELECT " + Calendar.Calendars._SYNC_ACCOUNT_TYPE + " FROM " + Tables.CALENDARS +
95            "   WHERE " + Tables.CALENDARS + "." + Calendar.Calendars._ID +
96                        "=new." + Calendar.Events.CALENDAR_ID + ") " +
97            "WHERE " + Tables.EVENTS + "." + Calendar.Events._ID +
98                    "=new." + Calendar.Events._ID + ";";
99
100    // This needs to be done when all the tables are already created
101    private static final String EVENTS_CLEANUP_TRIGGER_SQL =
102            "DELETE FROM " + Tables.INSTANCES +
103                " WHERE "+ Calendar.Instances.EVENT_ID + "=" +
104                    "old." + Calendar.Events._ID + ";" +
105            "DELETE FROM " + Tables.EVENTS_RAW_TIMES +
106                " WHERE " + Calendar.EventsRawTimes.EVENT_ID + "=" +
107                    "old." + Calendar.Events._ID + ";" +
108            "DELETE FROM " + Tables.ATTENDEES +
109                " WHERE " + Calendar.Attendees.EVENT_ID + "=" +
110                    "old." + Calendar.Events._ID + ";" +
111            "DELETE FROM " + Tables.REMINDERS +
112                " WHERE " + Calendar.Reminders.EVENT_ID + "=" +
113                    "old." + Calendar.Events._ID + ";" +
114            "DELETE FROM " + Tables.CALENDAR_ALERTS +
115                " WHERE " + Calendar.CalendarAlerts.EVENT_ID + "=" +
116                    "old." + Calendar.Events._ID + ";" +
117            "DELETE FROM " + Tables.EXTENDED_PROPERTIES +
118                " WHERE " + Calendar.ExtendedProperties.EVENT_ID + "=" +
119                    "old." + Calendar.Events._ID + ";";
120
121    private static final String CALENDAR_CLEANUP_TRIGGER_SQL = "DELETE FROM " + Tables.EVENTS +
122            " WHERE " + Calendar.Events.CALENDAR_ID + "=" +
123                "old." + Calendar.Events._ID + ";";
124
125    private static final String SCHEMA_HTTPS = "https://";
126    private static final String SCHEMA_HTTP = "http://";
127
128    private final Context mContext;
129    private final SyncStateContentProviderHelper mSyncState;
130
131    private static CalendarDatabaseHelper sSingleton = null;
132
133    private DatabaseUtils.InsertHelper mCalendarsInserter;
134    private DatabaseUtils.InsertHelper mEventsInserter;
135    private DatabaseUtils.InsertHelper mEventsRawTimesInserter;
136    private DatabaseUtils.InsertHelper mInstancesInserter;
137    private DatabaseUtils.InsertHelper mAttendeesInserter;
138    private DatabaseUtils.InsertHelper mRemindersInserter;
139    private DatabaseUtils.InsertHelper mCalendarAlertsInserter;
140    private DatabaseUtils.InsertHelper mExtendedPropertiesInserter;
141
142    public long calendarsInsert(ContentValues values) {
143        return mCalendarsInserter.insert(values);
144    }
145
146    public long eventsInsert(ContentValues values) {
147        return mEventsInserter.insert(values);
148    }
149
150    public long eventsRawTimesInsert(ContentValues values) {
151        return mEventsRawTimesInserter.insert(values);
152    }
153
154    public long eventsRawTimesReplace(ContentValues values) {
155        return mEventsRawTimesInserter.replace(values);
156    }
157
158    public long instancesInsert(ContentValues values) {
159        return mInstancesInserter.insert(values);
160    }
161
162    public long instancesReplace(ContentValues values) {
163        return mInstancesInserter.replace(values);
164    }
165
166    public long attendeesInsert(ContentValues values) {
167        return mAttendeesInserter.insert(values);
168    }
169
170    public long remindersInsert(ContentValues values) {
171        return mRemindersInserter.insert(values);
172    }
173
174    public long calendarAlertsInsert(ContentValues values) {
175        return mCalendarAlertsInserter.insert(values);
176    }
177
178    public long extendedPropertiesInsert(ContentValues values) {
179        return mExtendedPropertiesInserter.insert(values);
180    }
181
182    public static synchronized CalendarDatabaseHelper getInstance(Context context) {
183        if (sSingleton == null) {
184            sSingleton = new CalendarDatabaseHelper(context);
185        }
186        return sSingleton;
187    }
188
189    /**
190     * Private constructor, callers except unit tests should obtain an instance through
191     * {@link #getInstance(android.content.Context)} instead.
192     */
193    /* package */ CalendarDatabaseHelper(Context context) {
194        super(context, DATABASE_NAME, null, DATABASE_VERSION);
195        if (LOGD) Log.d(TAG, "Creating OpenHelper");
196        Resources resources = context.getResources();
197
198        mContext = context;
199        mSyncState = new SyncStateContentProviderHelper();
200    }
201
202    @Override
203    public void onOpen(SQLiteDatabase db) {
204        mSyncState.onDatabaseOpened(db);
205
206        mCalendarsInserter = new DatabaseUtils.InsertHelper(db, Tables.CALENDARS);
207        mEventsInserter = new DatabaseUtils.InsertHelper(db, Tables.EVENTS);
208        mEventsRawTimesInserter = new DatabaseUtils.InsertHelper(db, Tables.EVENTS_RAW_TIMES);
209        mInstancesInserter = new DatabaseUtils.InsertHelper(db, Tables.INSTANCES);
210        mAttendeesInserter = new DatabaseUtils.InsertHelper(db, Tables.ATTENDEES);
211        mRemindersInserter = new DatabaseUtils.InsertHelper(db, Tables.REMINDERS);
212        mCalendarAlertsInserter = new DatabaseUtils.InsertHelper(db, Tables.CALENDAR_ALERTS);
213        mExtendedPropertiesInserter =
214                new DatabaseUtils.InsertHelper(db, Tables.EXTENDED_PROPERTIES);
215    }
216
217    /*
218     * Upgrade sync state table if necessary.  Note that the data bundle
219     * in the table is not upgraded.
220     *
221     * The sync state used to be stored with version 3, but now uses the
222     * same sync state code as contacts, which is version 1.  This code
223     * upgrades from 3 to 1 if necessary.  (Yes, the numbers are unfortunately
224     * backwards.)
225     *
226     * This code is only called when upgrading from an old calendar version,
227     * so there is no problem if sync state version 3 gets used again in the
228     * future.
229     */
230    private void upgradeSyncState(SQLiteDatabase db) {
231        long version = DatabaseUtils.longForQuery(db,
232                 "SELECT " + SYNC_STATE_META_VERSION_COLUMN
233                 + " FROM " + Tables.SYNC_STATE_META,
234                 null);
235        if (version == PRE_FROYO_SYNC_STATE_VERSION) {
236            Log.i(TAG, "Upgrading calendar sync state table");
237            db.execSQL("CREATE TEMPORARY TABLE state_backup(_sync_account TEXT, "
238                    + "_sync_account_type TEXT, data TEXT);");
239            db.execSQL("INSERT INTO state_backup SELECT _sync_account, _sync_account_type, data"
240                    + " FROM "
241                    + Tables.SYNC_STATE
242                    + " WHERE _sync_account is not NULL and _sync_account_type is not NULL;");
243            db.execSQL("DROP TABLE " + Tables.SYNC_STATE + ";");
244            mSyncState.onDatabaseOpened(db);
245            db.execSQL("INSERT INTO " + Tables.SYNC_STATE + "("
246                    + SyncStateContract.Columns.ACCOUNT_NAME + ","
247                    + SyncStateContract.Columns.ACCOUNT_TYPE + ","
248                    + SyncStateContract.Columns.DATA
249                    + ") SELECT _sync_account, _sync_account_type, data from state_backup;");
250            db.execSQL("DROP TABLE state_backup;");
251        } else {
252            // Wrong version to upgrade.
253            // Don't need to do anything more here because mSyncState.onDatabaseOpened() will blow
254            // away and recreate  the database (which will result in a resync).
255            Log.w(TAG, "upgradeSyncState: current version is " + version + ", skipping upgrade.");
256        }
257    }
258
259    @Override
260    public void onCreate(SQLiteDatabase db) {
261        bootstrapDB(db);
262    }
263
264    private void bootstrapDB(SQLiteDatabase db) {
265        Log.i(TAG, "Bootstrapping database");
266
267        mSyncState.createDatabase(db);
268
269        createCalendarsTable(db);
270
271        // TODO: do we need both dtend and duration?
272        db.execSQL("CREATE TABLE " + Tables.EVENTS + " (" +
273                Calendar.Events._ID + " INTEGER PRIMARY KEY," +
274                Calendar.Events._SYNC_ACCOUNT + " TEXT," +
275                Calendar.Events._SYNC_ACCOUNT_TYPE + " TEXT," +
276                Calendar.Events._SYNC_ID + " TEXT," +
277                Calendar.Events._SYNC_VERSION + " TEXT," +
278                // sync time in UTC
279                Calendar.Events._SYNC_TIME + " TEXT," +
280                Calendar.Events._SYNC_DATA + " INTEGER," +
281                Calendar.Events._SYNC_DIRTY + " INTEGER," +
282                // sync mark to filter out new rows
283                Calendar.Events._SYNC_MARK + " INTEGER," +
284                Calendar.Events.CALENDAR_ID + " INTEGER NOT NULL," +
285                Calendar.Events.HTML_URI + " TEXT," +
286                Calendar.Events.TITLE + " TEXT," +
287                Calendar.Events.EVENT_LOCATION + " TEXT," +
288                Calendar.Events.DESCRIPTION + " TEXT," +
289                Calendar.Events.STATUS + " INTEGER," +
290                Calendar.Events.SELF_ATTENDEE_STATUS + " INTEGER NOT NULL DEFAULT 0," +
291                Calendar.Events.COMMENTS_URI + " TEXT," +
292                // dtstart in millis since epoch
293                Calendar.Events.DTSTART + " INTEGER," +
294                // dtend in millis since epoch
295                Calendar.Events.DTEND + " INTEGER," +
296                // timezone for event
297                Calendar.Events.EVENT_TIMEZONE + " TEXT," +
298                Calendar.Events.DURATION + " TEXT," +
299                Calendar.Events.ALL_DAY + " INTEGER NOT NULL DEFAULT 0," +
300                Calendar.Events.VISIBILITY + " INTEGER NOT NULL DEFAULT 0," +
301                Calendar.Events.TRANSPARENCY + " INTEGER NOT NULL DEFAULT 0," +
302                Calendar.Events.HAS_ALARM + " INTEGER NOT NULL DEFAULT 0," +
303                Calendar.Events.HAS_EXTENDED_PROPERTIES + " INTEGER NOT NULL DEFAULT 0," +
304                Calendar.Events.RRULE + " TEXT," +
305                Calendar.Events.RDATE + " TEXT," +
306                Calendar.Events.EXRULE + " TEXT," +
307                Calendar.Events.EXDATE + " TEXT," +
308                // originalEvent is the _sync_id of recurring event
309                Calendar.Events.ORIGINAL_EVENT + " TEXT," +
310                // originalInstanceTime is in millis since epoch
311                Calendar.Events.ORIGINAL_INSTANCE_TIME + " INTEGER," +
312                Calendar.Events.ORIGINAL_ALL_DAY + " INTEGER," +
313                // lastDate is in millis since epoch
314                Calendar.Events.LAST_DATE + " INTEGER," +
315                Calendar.Events.HAS_ATTENDEE_DATA + " INTEGER NOT NULL DEFAULT 0," +
316                Calendar.Events.GUESTS_CAN_MODIFY + " INTEGER NOT NULL DEFAULT 0," +
317                Calendar.Events.GUESTS_CAN_INVITE_OTHERS + " INTEGER NOT NULL DEFAULT 1," +
318                Calendar.Events.GUESTS_CAN_SEE_GUESTS + " INTEGER NOT NULL DEFAULT 1," +
319                Calendar.Events.ORGANIZER + " STRING," +
320                Calendar.Events.DELETED + " INTEGER NOT NULL DEFAULT 0," +
321                // dstart2 is in millis since epoch, allDay events are in local timezone
322                Calendar.Events.DTSTART2 + " INTEGER," +
323                // dtend2 is in millis since epoch, allDay events are in local timezone
324                Calendar.Events.DTEND2 + " INTEGER," +
325                // timezone for event with allDay events are in local timezone
326                Calendar.Events.EVENT_TIMEZONE2 + " TEXT," +
327                // syncAdapterData is available for use by sync adapters
328                Calendar.Events.SYNC_ADAPTER_DATA + " TEXT" +
329                ");");
330
331        // Trigger to set event's sync_account
332        db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON " + Tables.EVENTS + " " +
333                "BEGIN " +
334                AFTER_EVENT_INSERT_SQL +
335                "END");
336
337        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON " + Tables.EVENTS + " (" +
338                Calendar.Events._SYNC_ACCOUNT_TYPE + ", " +
339                Calendar.Events._SYNC_ACCOUNT + ", " +
340                Calendar.Events._SYNC_ID +
341                ");");
342
343        db.execSQL("CREATE INDEX eventsCalendarIdIndex ON " + Tables.EVENTS + " (" +
344                Calendar.Events.CALENDAR_ID +
345                ");");
346
347        db.execSQL("CREATE TABLE " + Tables.EVENTS_RAW_TIMES + " (" +
348                Calendar.EventsRawTimes._ID + " INTEGER PRIMARY KEY," +
349                Calendar.EventsRawTimes.EVENT_ID + " INTEGER NOT NULL," +
350                Calendar.EventsRawTimes.DTSTART_2445 + " TEXT," +
351                Calendar.EventsRawTimes.DTEND_2445 + " TEXT," +
352                Calendar.EventsRawTimes.ORIGINAL_INSTANCE_TIME_2445 + " TEXT," +
353                Calendar.EventsRawTimes.LAST_DATE_2445 + " TEXT," +
354                "UNIQUE (" + Calendar.EventsRawTimes.EVENT_ID + ")" +
355                ");");
356
357        db.execSQL("CREATE TABLE " + Tables.INSTANCES + " (" +
358                Calendar.Instances._ID + " INTEGER PRIMARY KEY," +
359                Calendar.Instances.EVENT_ID + " INTEGER," +
360                Calendar.Instances.BEGIN + " INTEGER," +         // UTC millis
361                Calendar.Instances.END + " INTEGER," +           // UTC millis
362                Calendar.Instances.START_DAY + " INTEGER," +      // Julian start day
363                Calendar.Instances.END_DAY + " INTEGER," +        // Julian end day
364                Calendar.Instances.START_MINUTE + " INTEGER," +   // minutes from midnight
365                Calendar.Instances.END_MINUTE + " INTEGER," +     // minutes from midnight
366                "UNIQUE (" +
367                    Calendar.Instances.EVENT_ID + ", " +
368                    Calendar.Instances.BEGIN + ", " +
369                    Calendar.Instances.END + ")" +
370                ");");
371
372        db.execSQL("CREATE INDEX instancesStartDayIndex ON " + Tables.INSTANCES + " (" +
373                Calendar.Instances.START_DAY +
374                ");");
375
376        createCalendarMetaDataTable(db);
377
378        createCalendarCacheTable(db, null);
379
380        db.execSQL("CREATE TABLE " + Tables.ATTENDEES + " (" +
381                Calendar.Attendees._ID + " INTEGER PRIMARY KEY," +
382                Calendar.Attendees.EVENT_ID + " INTEGER," +
383                Calendar.Attendees.ATTENDEE_NAME + " TEXT," +
384                Calendar.Attendees.ATTENDEE_EMAIL + " TEXT," +
385                Calendar.Attendees.ATTENDEE_STATUS + " INTEGER," +
386                Calendar.Attendees.ATTENDEE_RELATIONSHIP + " INTEGER," +
387                Calendar.Attendees.ATTENDEE_TYPE + " INTEGER" +
388                ");");
389
390        db.execSQL("CREATE INDEX attendeesEventIdIndex ON " + Tables.ATTENDEES + " (" +
391                Calendar.Attendees.EVENT_ID +
392                ");");
393
394        db.execSQL("CREATE TABLE " + Tables.REMINDERS + " (" +
395                Calendar.Reminders._ID + " INTEGER PRIMARY KEY," +
396                Calendar.Reminders.EVENT_ID + " INTEGER," +
397                Calendar.Reminders.MINUTES + " INTEGER," +
398                Calendar.Reminders.METHOD + " INTEGER NOT NULL" +
399                " DEFAULT " + Calendar.Reminders.METHOD_DEFAULT +
400                ");");
401
402        db.execSQL("CREATE INDEX remindersEventIdIndex ON " + Tables.REMINDERS + " (" +
403                Calendar.Reminders.EVENT_ID +
404                ");");
405
406         // This table stores the Calendar notifications that have gone off.
407        db.execSQL("CREATE TABLE " + Tables.CALENDAR_ALERTS + " (" +
408                Calendar.CalendarAlerts._ID + " INTEGER PRIMARY KEY," +
409                Calendar.CalendarAlerts.EVENT_ID + " INTEGER," +
410                Calendar.CalendarAlerts.BEGIN + " INTEGER NOT NULL," +          // UTC millis
411                Calendar.CalendarAlerts.END + " INTEGER NOT NULL," +            // UTC millis
412                Calendar.CalendarAlerts.ALARM_TIME + " INTEGER NOT NULL," +     // UTC millis
413                Calendar.CalendarAlerts.CREATION_TIME + " INTEGER NOT NULL," +  // UTC millis
414                Calendar.CalendarAlerts.RECEIVED_TIME + " INTEGER NOT NULL," +  // UTC millis
415                Calendar.CalendarAlerts.NOTIFY_TIME + " INTEGER NOT NULL," +    // UTC millis
416                Calendar.CalendarAlerts.STATE + " INTEGER NOT NULL," +
417                Calendar.CalendarAlerts.MINUTES + " INTEGER," +
418                "UNIQUE (" +
419                    Calendar.CalendarAlerts.ALARM_TIME + ", " +
420                    Calendar.CalendarAlerts.BEGIN + ", " +
421                    Calendar.CalendarAlerts.EVENT_ID + ")" +
422                ");");
423
424        db.execSQL("CREATE INDEX calendarAlertsEventIdIndex ON " + Tables.CALENDAR_ALERTS + " (" +
425                Calendar.CalendarAlerts.EVENT_ID +
426                ");");
427
428        db.execSQL("CREATE TABLE " + Tables.EXTENDED_PROPERTIES + " (" +
429                Calendar.ExtendedProperties._ID + " INTEGER PRIMARY KEY," +
430                Calendar.ExtendedProperties.EVENT_ID + " INTEGER," +
431                Calendar.ExtendedProperties.NAME + " TEXT," +
432                Calendar.ExtendedProperties.VALUE + " TEXT" +
433                ");");
434
435        db.execSQL("CREATE INDEX extendedPropertiesEventIdIndex ON " + Tables.EXTENDED_PROPERTIES
436                + " (" +
437                Calendar.ExtendedProperties.EVENT_ID +
438                ");");
439
440        createEventsView(db);
441
442        // Trigger to remove data tied to an event when we delete that event.
443        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
444                "BEGIN " +
445                EVENTS_CLEANUP_TRIGGER_SQL +
446                "END");
447
448        ContentResolver.requestSync(null /* all accounts */,
449                ContactsContract.AUTHORITY, new Bundle());
450    }
451
452    private void createCalendarsTable(SQLiteDatabase db) {
453        db.execSQL("CREATE TABLE " + Tables.CALENDARS + " (" +
454                Calendar.Calendars._ID + " INTEGER PRIMARY KEY," +
455                Calendar.Calendars._SYNC_ACCOUNT + " TEXT," +
456                Calendar.Calendars._SYNC_ACCOUNT_TYPE + " TEXT," +
457                Calendar.Calendars._SYNC_ID + " TEXT," +
458                Calendar.Calendars._SYNC_VERSION + " TEXT," +
459                Calendar.Calendars._SYNC_TIME + " TEXT," +  // UTC
460                Calendar.Calendars._SYNC_DATA + " INTEGER," +
461                Calendar.Calendars._SYNC_DIRTY + " INTEGER," +
462                Calendar.Calendars._SYNC_MARK + " INTEGER," + // Used to filter out new rows
463                Calendar.Calendars.NAME + " TEXT," +
464                Calendar.Calendars.DISPLAY_NAME + " TEXT," +
465                Calendar.Calendars.COLOR + " INTEGER," +
466                Calendar.Calendars.ACCESS_LEVEL + " INTEGER," +
467                Calendar.Calendars.SELECTED + " INTEGER NOT NULL DEFAULT 1," +
468                Calendar.Calendars.SYNC_EVENTS + " INTEGER NOT NULL DEFAULT 0," +
469                Calendar.Calendars.LOCATION + " TEXT," +
470                Calendar.Calendars.TIMEZONE + " TEXT," +
471                Calendar.Calendars.OWNER_ACCOUNT + " TEXT, " +
472                Calendar.Calendars.ORGANIZER_CAN_RESPOND + " INTEGER NOT NULL DEFAULT 1," +
473                Calendar.Calendars.DELETED + " INTEGER NOT NULL DEFAULT 0," +
474                Calendar.Calendars.SYNC1 + " TEXT," +
475                Calendar.Calendars.SYNC2 + " TEXT," +
476                Calendar.Calendars.SYNC3 + " TEXT," +
477                Calendar.Calendars.SYNC4 + " TEXT," +
478                Calendar.Calendars.SYNC5 + " TEXT" +
479                ");");
480
481        // Trigger to remove a calendar's events when we delete the calendar
482        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
483                "BEGIN " +
484                CALENDAR_CLEANUP_TRIGGER_SQL +
485                "END");
486    }
487
488    private void createCalendarMetaDataTable(SQLiteDatabase db) {
489        db.execSQL("CREATE TABLE " + Tables.CALENDAR_META_DATA + " (" +
490                Calendar.CalendarMetaData._ID + " INTEGER PRIMARY KEY," +
491                Calendar.CalendarMetaData.LOCAL_TIMEZONE + " TEXT," +
492                Calendar.CalendarMetaData.MIN_INSTANCE + " INTEGER," +      // UTC millis
493                Calendar.CalendarMetaData.MAX_INSTANCE + " INTEGER" +       // UTC millis
494                ");");
495    }
496
497    private void createCalendarCacheTable(SQLiteDatabase db, String oldTimezoneDbVersion) {
498        // This is a hack because versioning skipped version number 61 of schema
499        // TODO after version 70 this can be removed
500        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";");
501
502        // IF NOT EXISTS should be normal pattern for table creation
503        db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.CALENDAR_CACHE + " (" +
504                CalendarCache.COLUMN_NAME_ID + " INTEGER PRIMARY KEY," +
505                CalendarCache.COLUMN_NAME_KEY + " TEXT NOT NULL," +
506                CalendarCache.COLUMN_NAME_VALUE + " TEXT" +
507                ");");
508
509        initCalendarCacheTable(db, oldTimezoneDbVersion);
510        updateCalendarCacheTableTo203(db);
511    }
512
513    private void initCalendarCacheTable(SQLiteDatabase db, String oldTimezoneDbVersion) {
514        String timezoneDbVersion = (oldTimezoneDbVersion != null) ?
515                oldTimezoneDbVersion : CalendarCache.DEFAULT_TIMEZONE_DATABASE_VERSION;
516
517        // Set the default timezone database version
518        db.execSQL("INSERT OR REPLACE INTO CalendarCache (_id, key, value) VALUES (" +
519                CalendarCache.KEY_TIMEZONE_DATABASE_VERSION.hashCode() + "," +
520                "'" + CalendarCache.KEY_TIMEZONE_DATABASE_VERSION + "',"  +
521                "'" + timezoneDbVersion + "'" +
522                ");");
523    }
524
525    private void updateCalendarCacheTableTo203(SQLiteDatabase db) {
526        // Define the default timezone type for Instances timezone management
527        db.execSQL("INSERT INTO CalendarCache (_id, key, value) VALUES (" +
528                CalendarCache.KEY_TIMEZONE_TYPE.hashCode() + "," +
529                "'" + CalendarCache.KEY_TIMEZONE_TYPE + "',"  +
530                "'" + CalendarCache.TIMEZONE_TYPE_AUTO + "'" +
531                ");");
532
533        String defaultTimezone = TimeZone.getDefault().getID();
534
535        // Define the default timezone for Instances
536        db.execSQL("INSERT INTO CalendarCache (_id, key, value) VALUES (" +
537                CalendarCache.KEY_TIMEZONE_INSTANCES.hashCode() + "," +
538                "'" + CalendarCache.KEY_TIMEZONE_INSTANCES + "',"  +
539                "'" + defaultTimezone + "'" +
540                ");");
541
542        // Define the default previous timezone for Instances
543        db.execSQL("INSERT INTO CalendarCache (_id, key, value) VALUES (" +
544                CalendarCache.KEY_TIMEZONE_INSTANCES_PREVIOUS.hashCode() + "," +
545                "'" + CalendarCache.KEY_TIMEZONE_INSTANCES_PREVIOUS + "',"  +
546                "'" + defaultTimezone + "'" +
547                ");");
548    }
549
550    @Override
551    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
552        Log.i(TAG, "Upgrading DB from version " + oldVersion
553                + " to " + newVersion);
554        if (oldVersion < 49) {
555            dropTables(db);
556            mSyncState.createDatabase(db);
557            return; // this was lossy
558        }
559
560        // From schema versions 59 to version 66, the CalendarMetaData table definition had lost
561        // the primary key leading to having the CalendarMetaData with multiple rows instead of
562        // only one. The Instance table was then corrupted (during Instance expansion we are using
563        // the localTimezone, minInstance and maxInstance from CalendarMetaData table.
564        // This boolean helps us tracking the need to recreate the CalendarMetaData table and
565        // clear the Instance table (and thus force an Instance expansion).
566        boolean recreateMetaDataAndInstances = (oldVersion >= 59 && oldVersion <= 66);
567
568        try {
569            if (oldVersion < 51) {
570                upgradeToVersion51(db); // From 50 or 51
571                oldVersion = 51;
572            }
573            if (oldVersion == 51) {
574                upgradeToVersion52(db);
575                oldVersion += 1;
576            }
577            if (oldVersion == 52) {
578                upgradeToVersion53(db);
579                oldVersion += 1;
580            }
581            if (oldVersion == 53) {
582                upgradeToVersion54(db);
583                oldVersion += 1;
584            }
585            if (oldVersion == 54) {
586                upgradeToVersion55(db);
587                oldVersion += 1;
588            }
589            if (oldVersion == 55 || oldVersion == 56) {
590                // Both require resync, so just schedule it once
591                upgradeResync(db);
592            }
593            if (oldVersion == 55) {
594                upgradeToVersion56(db);
595                oldVersion += 1;
596            }
597            if (oldVersion == 56) {
598                upgradeToVersion57(db);
599                oldVersion += 1;
600            }
601            if (oldVersion == 57) {
602                // Changes are undone upgrading to 60, so don't do anything.
603                oldVersion += 1;
604            }
605            if (oldVersion == 58) {
606                upgradeToVersion59(db);
607                oldVersion += 1;
608            }
609            if (oldVersion == 59) {
610                upgradeToVersion60(db);
611                oldVersion += 1;
612            }
613            if (oldVersion == 60) {
614                upgradeToVersion61(db);
615                oldVersion += 1;
616            }
617            if (oldVersion == 61) {
618                upgradeToVersion62(db);
619                oldVersion += 1;
620            }
621            if (oldVersion == 62) {
622                upgradeToVersion63(db);
623                oldVersion += 1;
624            }
625            if (oldVersion == 63) {
626                upgradeToVersion64(db);
627                oldVersion += 1;
628            }
629            if (oldVersion == 64) {
630                upgradeToVersion65(db);
631                oldVersion += 1;
632            }
633            if (oldVersion == 65) {
634                upgradeToVersion66(db);
635                oldVersion += 1;
636            }
637            if (oldVersion == 66) {
638                // Changes are done thru recreateMetaDataAndInstances() method
639                oldVersion += 1;
640            }
641            if (recreateMetaDataAndInstances) {
642                recreateMetaDataAndInstances(db);
643            }
644            if (oldVersion == 67 || oldVersion == 68) {
645                upgradeToVersion69(db);
646                oldVersion = 69;
647            }
648            // 69. 70 are for Froyo/old Gingerbread only and 100s are for Gingerbread only
649            // 70 and 71 have been for Honeycomb but no more used
650            // 72 and 73 and 74 were for Honeycomb only but are considered as obsolete for enabling
651            // room for Froyo version numbers
652            if(oldVersion == 69) {
653                upgradeToVersion200(db);
654                oldVersion = 200;
655            }
656            if (oldVersion == 70) {
657                upgradeToVersion200(db);
658                oldVersion = 200;
659            }
660            if (oldVersion == 100) {
661                upgradeToVersion200(db);
662                oldVersion = 200;
663            }
664            boolean need203Update = true;
665            if (oldVersion == 101) {
666                oldVersion = 200;
667                // Gingerbread version 101 is similar to Honeycomb version 203
668                need203Update = false;
669            }
670            if (oldVersion == 200) {
671                upgradeToVersion201(db);
672                oldVersion += 1;
673            }
674            if (oldVersion == 201) {
675                upgradeToVersion202(db);
676                oldVersion += 1;
677            }
678            if (oldVersion == 202 && need203Update) {
679                upgradeToVersion203(db);
680                oldVersion += 1;
681            }
682            if (oldVersion != DATABASE_VERSION) {
683                Log.e(TAG, "Need to recreate Calendar schema because of "
684                        + "unknown Calendar database version: " + oldVersion);
685                dropTables(db);
686                bootstrapDB(db);
687                oldVersion = DATABASE_VERSION;
688            }
689        } catch (SQLiteException e) {
690            Log.e(TAG, "onUpgrade: SQLiteException, recreating db. " + e);
691            dropTables(db);
692            bootstrapDB(db);
693            return; // this was lossy
694        }
695    }
696
697    /**
698     * If the user_version of the database if between 59 and 66 (those versions has been deployed
699     * with no primary key for the CalendarMetaData table)
700     */
701    private void recreateMetaDataAndInstances(SQLiteDatabase db) {
702        // Recreate the CalendarMetaData table with correct primary key
703        db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + ";");
704        createCalendarMetaDataTable(db);
705
706        // Also clean the Instance table as this table may be corrupted
707        db.execSQL("DELETE FROM " + Tables.INSTANCES + ";");
708    }
709
710    private static boolean fixAllDayTime(Time time, String timezone, Long timeInMillis) {
711        time.set(timeInMillis);
712        if(time.hour != 0 || time.minute != 0 || time.second != 0) {
713            time.hour = 0;
714            time.minute = 0;
715            time.second = 0;
716            return true;
717        }
718        return false;
719    }
720
721    @VisibleForTesting
722    void upgradeToVersion203(SQLiteDatabase db) {
723        // Same as Gingerbread version 100
724        Cursor cursor = db.rawQuery("SELECT value from CalendarCache WHERE key=?",
725                new String[] {"timezoneDatabaseVersion"});
726
727        String oldTimezoneDbVersion = null;
728        if (cursor != null && cursor.moveToNext()) {
729            try {
730                oldTimezoneDbVersion = cursor.getString(0);
731            } finally {
732                cursor.close();
733            }
734            // Also clean the CalendarCache table
735            db.execSQL("DELETE FROM CalendarCache;");
736        }
737        initCalendarCacheTable(db, oldTimezoneDbVersion);
738
739        // Same as Gingerbread version 101
740        updateCalendarCacheTableTo203(db);
741    }
742
743    @VisibleForTesting
744    void upgradeToVersion202(SQLiteDatabase db) {
745        // We will drop the "hidden" column from the calendar schema and add the "sync5" column
746        db.execSQL("ALTER TABLE " + Tables.CALENDARS +" RENAME TO " +
747                Tables.CALENDARS + "_Backup;");
748
749        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
750        createCalendarsTable(db);
751
752        // Populate the new Calendars table and put into the "sync5" column the value of the
753        // old "hidden" column
754        db.execSQL("INSERT INTO " + Tables.CALENDARS + " (" +
755                Calendar.Calendars._ID + ", " +
756                Calendar.Calendars._SYNC_ACCOUNT + ", " +
757                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
758                Calendar.Calendars._SYNC_ID + ", " +
759                Calendar.Calendars._SYNC_VERSION + ", " +
760                Calendar.Calendars._SYNC_TIME + ", " +
761                Calendar.Calendars._SYNC_DATA + ", " +
762                Calendar.Calendars._SYNC_DIRTY + ", " +
763                Calendar.Calendars._SYNC_MARK + ", " +
764                Calendar.Calendars.NAME + ", " +
765                Calendar.Calendars.DISPLAY_NAME + ", " +
766                Calendar.Calendars.COLOR + ", " +
767                Calendar.Calendars.ACCESS_LEVEL + ", " +
768                Calendar.Calendars.SELECTED + ", " +
769                Calendar.Calendars.SYNC_EVENTS + ", " +
770                Calendar.Calendars.LOCATION + ", " +
771                Calendar.Calendars.TIMEZONE + ", " +
772                Calendar.Calendars.OWNER_ACCOUNT + ", " +
773                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
774                Calendar.Calendars.DELETED + ", " +
775                Calendar.Calendars.SYNC1 + ", " +
776                Calendar.Calendars.SYNC2 + ", " +
777                Calendar.Calendars.SYNC3 + ", " +
778                Calendar.Calendars.SYNC4 + ", " +
779                Calendar.Calendars.SYNC5 + ") " +
780                "SELECT " +
781                Calendar.Calendars._ID + ", " +
782                Calendar.Calendars._SYNC_ACCOUNT + ", " +
783                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
784                Calendar.Calendars._SYNC_ID + ", " +
785                Calendar.Calendars._SYNC_VERSION + ", " +
786                Calendar.Calendars._SYNC_TIME + ", " +
787                Calendar.Calendars._SYNC_DATA + ", " +
788                Calendar.Calendars._SYNC_DIRTY + ", " +
789                Calendar.Calendars._SYNC_MARK + ", " +
790                Calendar.Calendars.NAME + ", " +
791                Calendar.Calendars.DISPLAY_NAME + ", " +
792                Calendar.Calendars.COLOR + ", " +
793                Calendar.Calendars.ACCESS_LEVEL + ", " +
794                Calendar.Calendars.SELECTED + ", " +
795                Calendar.Calendars.SYNC_EVENTS + ", " +
796                Calendar.Calendars.LOCATION + ", " +
797                Calendar.Calendars.TIMEZONE + ", " +
798                Calendar.Calendars.OWNER_ACCOUNT + ", " +
799                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
800                Calendar.Calendars.DELETED + ", " +
801                Calendar.Calendars.SYNC1 + ", " +
802                Calendar.Calendars.SYNC2 + ", " +
803                Calendar.Calendars.SYNC3 + ", " +
804                Calendar.Calendars.SYNC4 + " " +
805                "hidden" + " " +
806                "FROM " + Tables.CALENDARS + "_Backup" + ";"
807        );
808
809        // Drop the backup table
810        db.execSQL("DROP TABLE " + Tables.CALENDARS + "_Backup;");
811
812        // Recreate the Events Views as column "hidden" has been deleted
813        createEventsView(db);
814    }
815
816    @VisibleForTesting
817    void upgradeToVersion201(SQLiteDatabase db) {
818        db.execSQL("ALTER TABLE " + Tables.CALENDARS +
819                " ADD COLUMN " + Calendar.Calendars.SYNC4 + " TEXT;");
820    }
821
822    @VisibleForTesting
823    void upgradeToVersion200(SQLiteDatabase db) {
824        // we cannot use here a Calendar.Calendars,URL constant for "url" as we are trying to make
825        // it disappear so we are keeping the hardcoded name "url" in all the SQLs
826        db.execSQL("ALTER TABLE " + Tables.CALENDARS +" RENAME TO " +
827                Tables.CALENDARS + "_Backup;");
828
829        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
830        createCalendarsTable(db);
831
832        // Populate the new Calendars table except the SYNC2 / SYNC3 columns
833        db.execSQL("INSERT INTO " + Tables.CALENDARS + " (" +
834                Calendar.Calendars._ID + ", " +
835                Calendar.Calendars._SYNC_ACCOUNT + ", " +
836                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
837                Calendar.Calendars._SYNC_ID + ", " +
838                Calendar.Calendars._SYNC_VERSION + ", " +
839                Calendar.Calendars._SYNC_TIME + ", " +
840                Calendar.Calendars._SYNC_DATA + ", " +
841                Calendar.Calendars._SYNC_DIRTY + ", " +
842                Calendar.Calendars._SYNC_MARK + ", " +
843                Calendar.Calendars.NAME + ", " +
844                Calendar.Calendars.DISPLAY_NAME + ", " +
845                Calendar.Calendars.COLOR + ", " +
846                Calendar.Calendars.ACCESS_LEVEL + ", " +
847                Calendar.Calendars.SELECTED + ", " +
848                Calendar.Calendars.SYNC_EVENTS + ", " +
849                Calendar.Calendars.LOCATION + ", " +
850                Calendar.Calendars.TIMEZONE + ", " +
851                Calendar.Calendars.OWNER_ACCOUNT + ", " +
852                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
853                Calendar.Calendars.DELETED + ", " +
854                Calendar.Calendars.SYNC1 + ") " +
855                "SELECT " +
856                Calendar.Calendars._ID + ", " +
857                Calendar.Calendars._SYNC_ACCOUNT + ", " +
858                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
859                Calendar.Calendars._SYNC_ID + ", " +
860                Calendar.Calendars._SYNC_VERSION + ", " +
861                Calendar.Calendars._SYNC_TIME + ", " +
862                Calendar.Calendars._SYNC_DATA + ", " +
863                Calendar.Calendars._SYNC_DIRTY + ", " +
864                Calendar.Calendars._SYNC_MARK + ", " +
865                Calendar.Calendars.NAME + ", " +
866                Calendar.Calendars.DISPLAY_NAME + ", " +
867                Calendar.Calendars.COLOR + ", " +
868                Calendar.Calendars.ACCESS_LEVEL + ", " +
869                Calendar.Calendars.SELECTED + ", " +
870                Calendar.Calendars.SYNC_EVENTS + ", " +
871                Calendar.Calendars.LOCATION + ", " +
872                Calendar.Calendars.TIMEZONE + ", " +
873                Calendar.Calendars.OWNER_ACCOUNT + ", " +
874                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
875                "0" + ", " +
876                "url" + " " +
877                "FROM " + Tables.CALENDARS + "_Backup" + ";"
878        );
879
880        // Populate SYNC2 and SYNC3 columns - SYNC1 represent the old "url" column
881        // We will need to iterate over all the "com.google" type of calendars
882        String selectSql = "SELECT " + Calendar.Calendars._ID + ", " + "url" +
883                " FROM " + Tables.CALENDARS + "_Backup" +
884                " WHERE " + Calendar.Calendars._SYNC_ACCOUNT_TYPE  + "='com.google'" +
885                " AND url IS NOT NULL;";
886
887        String updateSql = "UPDATE " + Tables.CALENDARS + " SET " +
888                Calendar.Calendars.SYNC2 + "=?, " + // edit Url
889                Calendar.Calendars.SYNC3 + "=? " + // self Url
890                "WHERE " + Calendar.Calendars._ID + "=?;";
891
892        Cursor cursor = db.rawQuery(selectSql, null /* selection args */);
893        if (cursor != null && cursor.getCount() > 0) {
894            try {
895                Object[] bindArgs = new Object[3];
896
897                while (cursor.moveToNext()) {
898                    Long id = cursor.getLong(0);
899                    String url = cursor.getString(1);
900                    String selfUrl = getSelfUrlFromEventsUrl(url);
901                    String editUrl = getEditUrlFromEventsUrl(url);
902
903                    bindArgs[0] = editUrl;
904                    bindArgs[1] = selfUrl;
905                    bindArgs[2] = id;
906
907                    db.execSQL(updateSql, bindArgs);
908                }
909            } finally {
910                cursor.close();
911            }
912        }
913
914        // Drop the backup table
915        db.execSQL("DROP TABLE " + Tables.CALENDARS + "_Backup;");
916
917        // Recreate the Events Views as column "deleted" is now ambiguous
918        // ("deleted" is now defined in both Calendars and Events tables)
919        createEventsView(db);
920    }
921
922    @VisibleForTesting
923    static void upgradeToVersion69(SQLiteDatabase db) {
924        // Clean up allDay events which could be in an invalid state from an earlier version
925        // Some allDay events had hour, min, sec not set to zero, which throws elsewhere. This
926        // will go through the allDay events and make sure they have proper values and are in the
927        // correct timezone. Verifies that dtstart and dtend are in UTC and at midnight, that
928        // eventTimezone is set to UTC, tries to make sure duration is in days, and that dtstart2
929        // and dtend2 are at midnight in their timezone.
930        final String sql = "SELECT " + Calendar.Events._ID + ", " +
931                Calendar.Events.DTSTART + ", " +
932                Calendar.Events.DTEND + ", " +
933                Calendar.Events.DURATION + ", " +
934                Calendar.Events.DTSTART2 + ", " +
935                Calendar.Events.DTEND2 + ", " +
936                Calendar.Events.EVENT_TIMEZONE + ", " +
937                Calendar.Events.EVENT_TIMEZONE2 + ", " +
938                Calendar.Events.RRULE + " " +
939                "FROM " + Tables.EVENTS + " " +
940                "WHERE " + Calendar.Events.ALL_DAY + "=?";
941        Cursor cursor = db.rawQuery(sql, new String[] {"1"});
942        if (cursor != null) {
943            try {
944                String timezone;
945                String timezone2;
946                String duration;
947                Long dtstart;
948                Long dtstart2;
949                Long dtend;
950                Long dtend2;
951                Time time = new Time();
952                Long id;
953                // some things need to be in utc so we call this frequently, cache to make faster
954                final String utc = Time.TIMEZONE_UTC;
955                while (cursor.moveToNext()) {
956                    String rrule = cursor.getString(8);
957                    id = cursor.getLong(0);
958                    dtstart = cursor.getLong(1);
959                    dtstart2 = null;
960                    timezone = cursor.getString(6);
961                    timezone2 = cursor.getString(7);
962                    duration = cursor.getString(3);
963
964                    if (TextUtils.isEmpty(rrule)) {
965                        // For non-recurring events dtstart and dtend should both have values
966                        // and duration should be null.
967                        dtend = cursor.getLong(2);
968                        dtend2 = null;
969                        // Since we made all three of these at the same time if timezone2 exists
970                        // so should dtstart2 and dtend2.
971                        if(!TextUtils.isEmpty(timezone2)) {
972                            dtstart2 = cursor.getLong(4);
973                            dtend2 = cursor.getLong(5);
974                        }
975
976                        boolean update = false;
977                        if (!TextUtils.equals(timezone, utc)) {
978                            update = true;
979                            timezone = utc;
980                        }
981
982                        time.clear(timezone);
983                        update |= fixAllDayTime(time, timezone, dtstart);
984                        dtstart = time.normalize(false);
985
986                        time.clear(timezone);
987                        update |= fixAllDayTime(time, timezone, dtend);
988                        dtend = time.normalize(false);
989
990                        if (dtstart2 != null) {
991                            time.clear(timezone2);
992                            update |= fixAllDayTime(time, timezone2, dtstart2);
993                            dtstart2 = time.normalize(false);
994                        }
995
996                        if (dtend2 != null) {
997                            time.clear(timezone2);
998                            update |= fixAllDayTime(time, timezone2, dtend2);
999                            dtend2 = time.normalize(false);
1000                        }
1001
1002                        if (!TextUtils.isEmpty(duration)) {
1003                            update = true;
1004                        }
1005
1006                        if (update) {
1007                            // enforce duration being null
1008                            db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1009                                    Calendar.Events.DTSTART + "=?, " +
1010                                    Calendar.Events.DTEND + "=?, " +
1011                                    Calendar.Events.DTSTART2 + "=?, " +
1012                                    Calendar.Events.DTEND2 + "=?, " +
1013                                    Calendar.Events.DURATION + "=?, " +
1014                                    Calendar.Events.EVENT_TIMEZONE + "=?, " +
1015                                    Calendar.Events.EVENT_TIMEZONE2 + "=? " +
1016                                    "WHERE " + Calendar.Events._ID + "=?",
1017                                    new Object[] {
1018                                            dtstart,
1019                                            dtend,
1020                                            dtstart2,
1021                                            dtend2,
1022                                            null,
1023                                            timezone,
1024                                            timezone2,
1025                                            id}
1026                            );
1027                        }
1028
1029                    } else {
1030                        // For recurring events only dtstart and duration should be used.
1031                        // We ignore dtend since it will be overwritten if the event changes to a
1032                        // non-recurring event and won't be used otherwise.
1033                        if(!TextUtils.isEmpty(timezone2)) {
1034                            dtstart2 = cursor.getLong(4);
1035                        }
1036
1037                        boolean update = false;
1038                        if (!TextUtils.equals(timezone, utc)) {
1039                            update = true;
1040                            timezone = utc;
1041                        }
1042
1043                        time.clear(timezone);
1044                        update |= fixAllDayTime(time, timezone, dtstart);
1045                        dtstart = time.normalize(false);
1046
1047                        if (dtstart2 != null) {
1048                            time.clear(timezone2);
1049                            update |= fixAllDayTime(time, timezone2, dtstart2);
1050                            dtstart2 = time.normalize(false);
1051                        }
1052
1053                        if (TextUtils.isEmpty(duration)) {
1054                            // If duration was missing assume a 1 day duration
1055                            duration = "P1D";
1056                            update = true;
1057                        } else {
1058                            int len = duration.length();
1059                            // TODO fix durations in other formats as well
1060                            if (duration.charAt(0) == 'P' &&
1061                                    duration.charAt(len - 1) == 'S') {
1062                                int seconds = Integer.parseInt(duration.substring(1, len - 1));
1063                                int days = (seconds + DAY_IN_SECONDS - 1) / DAY_IN_SECONDS;
1064                                duration = "P" + days + "D";
1065                                update = true;
1066                            }
1067                        }
1068
1069                        if (update) {
1070                            // If there were other problems also enforce dtend being null
1071                            db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1072                                    Calendar.Events.DTSTART + "=?, " +
1073                                    Calendar.Events.DTEND + "=?, " +
1074                                    Calendar.Events.DTSTART2 + "=?, " +
1075                                    Calendar.Events.DTEND2 + "=?, " +
1076                                    Calendar.Events.DURATION + "=?," +
1077                                    Calendar.Events.EVENT_TIMEZONE + "=?, " +
1078                                    Calendar.Events.EVENT_TIMEZONE2 + "=? " +
1079                                    "WHERE " + Calendar.Events._ID + "=?",
1080                                    new Object[] {
1081                                            dtstart,
1082                                            null,
1083                                            dtstart2,
1084                                            null,
1085                                            duration,
1086                                            timezone,
1087                                            timezone2,
1088                                            id}
1089                            );
1090                        }
1091                    }
1092                }
1093            } finally {
1094                cursor.close();
1095            }
1096        }
1097    }
1098
1099    private void upgradeToVersion66(SQLiteDatabase db) {
1100        // Add a column to indicate whether the event organizer can respond to his own events
1101        // The UI should not show attendee status for events in calendars with this column = 0
1102        db.execSQL("ALTER TABLE " + Tables.CALENDARS +
1103                " ADD COLUMN " + Calendar.Calendars.ORGANIZER_CAN_RESPOND +
1104                    " INTEGER NOT NULL DEFAULT 1;");
1105    }
1106
1107    private void upgradeToVersion65(SQLiteDatabase db) {
1108        // we need to recreate the Events view
1109        createEventsView(db);
1110    }
1111
1112    private void upgradeToVersion64(SQLiteDatabase db) {
1113        // Add a column that may be used by sync adapters
1114        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1115                " ADD COLUMN " + Calendar.Events.SYNC_ADAPTER_DATA + " TEXT;");
1116    }
1117
1118    private void upgradeToVersion63(SQLiteDatabase db) {
1119        // we need to recreate the Events view
1120        createEventsView(db);
1121    }
1122
1123    private void upgradeToVersion62(SQLiteDatabase db) {
1124        // New columns are to transition to having allDay events in the local timezone
1125        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1126                " ADD COLUMN " + Calendar.Events.DTSTART2 + " INTEGER;");
1127        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1128                " ADD COLUMN " + Calendar.Events.DTEND2 + " INTEGER;");
1129        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1130                " ADD COLUMN " + Calendar.Events.EVENT_TIMEZONE2 + " TEXT;");
1131
1132        String[] allDayBit = new String[] {"0"};
1133        // Copy over all the data that isn't an all day event.
1134        db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1135                Calendar.Events.DTSTART2 + "=" + Calendar.Events.DTSTART + "," +
1136                Calendar.Events.DTEND2 + "=" + Calendar.Events.DTEND + "," +
1137                Calendar.Events.EVENT_TIMEZONE2 + "=" + Calendar.Events.EVENT_TIMEZONE + " " +
1138                "WHERE " + Calendar.Events.ALL_DAY + "=?;",
1139                allDayBit /* selection args */);
1140
1141        // "cursor" iterates over all the calendars
1142        allDayBit[0] = "1";
1143        Cursor cursor = db.rawQuery("SELECT " + Tables.EVENTS + "." + Calendar.Events._ID + "," +
1144                Calendar.Events.DTSTART + "," +
1145                Calendar.Events.DTEND + "," +
1146                Calendar.Events.EVENT_TIMEZONE + "," +
1147                Calendar.Calendars.TIMEZONE + " " +
1148                "FROM " + Tables.EVENTS + " INNER JOIN " + Tables.CALENDARS + " " +
1149                "WHERE " + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID + "=" +
1150                    Tables.CALENDARS + "." + Calendar.Calendars._ID +
1151                " AND "
1152                    + Calendar.Events.ALL_DAY + "=?",
1153                allDayBit /* selection args */);
1154
1155        Time oldTime = new Time();
1156        Time newTime = new Time();
1157        // Update the allday events in the new columns
1158        if (cursor != null) {
1159            try {
1160                String[] newData = new String[4];
1161                cursor.moveToPosition(-1);
1162                while (cursor.moveToNext()) {
1163                    long id = cursor.getLong(0); // Order from query above
1164                    long dtstart = cursor.getLong(1);
1165                    long dtend = cursor.getLong(2);
1166                    String eTz = cursor.getString(3); // current event timezone
1167                    String tz = cursor.getString(4); // Calendar timezone
1168                    //If there's no timezone for some reason use UTC by default.
1169                    if(eTz == null) {
1170                        eTz = Time.TIMEZONE_UTC;
1171                    }
1172
1173                    // Convert start time for all day events into the timezone of their calendar
1174                    oldTime.clear(eTz);
1175                    oldTime.set(dtstart);
1176                    newTime.clear(tz);
1177                    newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
1178                    newTime.normalize(false);
1179                    dtstart = newTime.toMillis(false /*ignoreDst*/);
1180
1181                    // Convert end time for all day events into the timezone of their calendar
1182                    oldTime.clear(eTz);
1183                    oldTime.set(dtend);
1184                    newTime.clear(tz);
1185                    newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
1186                    newTime.normalize(false);
1187                    dtend = newTime.toMillis(false /*ignoreDst*/);
1188
1189                    newData[0] = String.valueOf(dtstart);
1190                    newData[1] = String.valueOf(dtend);
1191                    newData[2] = tz;
1192                    newData[3] = String.valueOf(id);
1193                    db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1194                            Calendar.Events.DTSTART2 + "=?, " +
1195                            Calendar.Events.DTEND2 + "=?, " +
1196                            Calendar.Events.EVENT_TIMEZONE2 + "=? " +
1197                            "WHERE " + Calendar.Events._ID + "=?",
1198                            newData);
1199                }
1200            } finally {
1201                cursor.close();
1202            }
1203        }
1204    }
1205
1206    private void upgradeToVersion61(SQLiteDatabase db) {
1207        db.execSQL("DROP TABLE IF EXISTS CalendarCache;");
1208
1209        // IF NOT EXISTS should be normal pattern for table creation
1210        db.execSQL("CREATE TABLE IF NOT EXISTS CalendarCache (" +
1211                "_id INTEGER PRIMARY KEY," +
1212                "key TEXT NOT NULL," +
1213                "value TEXT" +
1214                ");");
1215
1216        db.execSQL("INSERT INTO CalendarCache (key, value) VALUES (" +
1217                "'" + CalendarCache.KEY_TIMEZONE_DATABASE_VERSION + "',"  +
1218                "'" + CalendarCache.DEFAULT_TIMEZONE_DATABASE_VERSION + "'" +
1219                ");");
1220    }
1221
1222    private void upgradeToVersion60(SQLiteDatabase db) {
1223        // Switch to CalendarProvider2
1224        upgradeSyncState(db);
1225        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1226        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
1227                "BEGIN " +
1228                CALENDAR_CLEANUP_TRIGGER_SQL +
1229                "END");
1230        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1231                " ADD COLUMN " + Calendar.Events.DELETED + " INTEGER NOT NULL DEFAULT 0;");
1232        db.execSQL("DROP TRIGGER IF EXISTS events_insert");
1233        // Trigger to set event's sync_account
1234        db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON " + Tables.EVENTS + " " +
1235                "BEGIN " +
1236                AFTER_EVENT_INSERT_SQL +
1237                "END");
1238        db.execSQL("DROP TABLE IF EXISTS DeletedEvents;");
1239        db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
1240        // Trigger to remove data tied to an event when we delete that event.
1241        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
1242                "BEGIN " +
1243                EVENTS_CLEANUP_TRIGGER_SQL +
1244                "END");
1245        db.execSQL("DROP TRIGGER IF EXISTS attendees_update");
1246        db.execSQL("DROP TRIGGER IF EXISTS attendees_insert");
1247        db.execSQL("DROP TRIGGER IF EXISTS attendees_delete");
1248        db.execSQL("DROP TRIGGER IF EXISTS reminders_update");
1249        db.execSQL("DROP TRIGGER IF EXISTS reminders_insert");
1250        db.execSQL("DROP TRIGGER IF EXISTS reminders_delete");
1251        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update");
1252        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert");
1253        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete");
1254
1255        createEventsView(db);
1256    }
1257
1258    private void upgradeToVersion59(SQLiteDatabase db) {
1259        db.execSQL("DROP TABLE IF EXISTS BusyBits;");
1260        db.execSQL("CREATE TEMPORARY TABLE " + Tables.CALENDAR_META_DATA + "_Backup" + "(" +
1261                Calendar.CalendarMetaData._ID + "," +
1262                Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," +
1263                Calendar.CalendarMetaData.MIN_INSTANCE + "," +
1264                Calendar.CalendarMetaData.MAX_INSTANCE +
1265                ");");
1266        db.execSQL("INSERT INTO " + Tables.CALENDAR_META_DATA + "_Backup " +
1267                "SELECT " +
1268                Calendar.CalendarMetaData._ID + "," +
1269                Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," +
1270                Calendar.CalendarMetaData.MIN_INSTANCE + "," +
1271                Calendar.CalendarMetaData.MAX_INSTANCE +
1272                " FROM " + Tables.CALENDAR_META_DATA + ";");
1273        db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + ";");
1274        createCalendarMetaDataTable(db);
1275        db.execSQL("INSERT INTO " + Tables.CALENDAR_META_DATA + " " +
1276                "SELECT " +
1277                Calendar.CalendarMetaData._ID + "," +
1278                Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," +
1279                Calendar.CalendarMetaData.MIN_INSTANCE + "," +
1280                Calendar.CalendarMetaData.MAX_INSTANCE +
1281                " FROM " + Tables.CALENDAR_META_DATA + "_Backup;");
1282        db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + "_Backup;");
1283    }
1284
1285    private void upgradeToVersion57(SQLiteDatabase db) {
1286        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1287                " ADD COLUMN " + Calendar.Events.GUESTS_CAN_MODIFY +
1288                " INTEGER NOT NULL DEFAULT 0;");
1289        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1290                " ADD COLUMN " + Calendar.Events.GUESTS_CAN_INVITE_OTHERS +
1291                " INTEGER NOT NULL DEFAULT 1;");
1292        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1293                " ADD COLUMN " + Calendar.Events.GUESTS_CAN_SEE_GUESTS +
1294                " INTEGER NOT NULL DEFAULT 1;");
1295        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1296                " ADD COLUMN " + Calendar.Events.ORGANIZER +
1297                " STRING;");
1298        db.execSQL("UPDATE " + Tables.EVENTS + " SET " + Calendar.Events.ORGANIZER + "=" +
1299                "(SELECT " + Calendar.Attendees.ATTENDEE_EMAIL +
1300                " FROM " + Tables.ATTENDEES + ""  +
1301                " WHERE " +
1302                Tables.ATTENDEES + "." + Calendar.Attendees.EVENT_ID + "=" +
1303                Tables.EVENTS + "." + Calendar.Events._ID +
1304                " AND " +
1305                Tables.ATTENDEES + "." + Calendar.Attendees.ATTENDEE_RELATIONSHIP + "=2);");
1306    }
1307
1308    private void upgradeToVersion56(SQLiteDatabase db) {
1309        db.execSQL("ALTER TABLE " + Tables.CALENDARS +
1310                " ADD COLUMN " + Calendar.Calendars.OWNER_ACCOUNT + " TEXT;");
1311        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1312                " ADD COLUMN " + Calendar.Events.HAS_ATTENDEE_DATA + " INTEGER;");
1313
1314        // Clear _sync_dirty to avoid a client-to-server sync that could blow away
1315        // server attendees.
1316        // Clear _sync_version to pull down the server's event (with attendees)
1317        // Change the URLs from full-selfattendance to full
1318        db.execSQL("UPDATE " + Tables.EVENTS
1319                + " SET " + Calendar.Events._SYNC_DIRTY + "=0, "
1320                + Calendar.Events._SYNC_VERSION + "=NULL, "
1321                + Calendar.Events._SYNC_ID + "="
1322                + "REPLACE(" + Calendar.Events._SYNC_ID + ", " +
1323                    "'/private/full-selfattendance', '/private/full'),"
1324                + Calendar.Events.COMMENTS_URI + "="
1325                + "REPLACE(" + Calendar.Events.COMMENTS_URI + ", " +
1326                    "'/private/full-selfattendance', '/private/full');");
1327
1328        db.execSQL("UPDATE " + Tables.CALENDARS
1329                + " SET " + "url="
1330                + "REPLACE(" + "url, " + "'/private/full-selfattendance', '/private/full');");
1331
1332        // "cursor" iterates over all the calendars
1333        Cursor cursor = db.rawQuery("SELECT " + Calendar.Calendars._ID + ", " +
1334                Calendar.Calendars.URL + " FROM " + Tables.CALENDARS,
1335                null /* selection args */);
1336        // Add the owner column.
1337        if (cursor != null) {
1338            try {
1339                final String updateSql = "UPDATE " + Tables.CALENDARS +
1340                        " SET " + Calendar.Calendars.OWNER_ACCOUNT + "=?" +
1341                        " WHERE " + Calendar.Calendars._ID + "=?";
1342                while (cursor.moveToNext()) {
1343                    Long id = cursor.getLong(0);
1344                    String url = cursor.getString(1);
1345                    String owner = calendarEmailAddressFromFeedUrl(url);
1346                    db.execSQL(updateSql, new Object[] {owner, id});
1347                }
1348            } finally {
1349                cursor.close();
1350            }
1351        }
1352    }
1353
1354    private void upgradeResync(SQLiteDatabase db) {
1355        // Delete sync state, so all records will be re-synced.
1356        db.execSQL("DELETE FROM " + Tables.SYNC_STATE + ";");
1357
1358        // "cursor" iterates over all the calendars
1359        Cursor cursor = db.rawQuery("SELECT " + Calendar.Calendars._SYNC_ACCOUNT + "," +
1360                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ",url FROM " + Tables.CALENDARS,
1361                null /* selection args */);
1362        if (cursor != null) {
1363            try {
1364                while (cursor.moveToNext()) {
1365                    String accountName = cursor.getString(0);
1366                    String accountType = cursor.getString(1);
1367                    final Account account = new Account(accountName, accountType);
1368                    String calendarUrl = cursor.getString(2);
1369                    scheduleSync(account, false /* two-way sync */, calendarUrl);
1370                }
1371            } finally {
1372                cursor.close();
1373            }
1374        }
1375    }
1376
1377    private void upgradeToVersion55(SQLiteDatabase db) {
1378        db.execSQL("ALTER TABLE " + Tables.CALENDARS + " ADD COLUMN " +
1379                Calendar.Calendars._SYNC_ACCOUNT_TYPE + " TEXT;");
1380        db.execSQL("ALTER TABLE " + Tables.EVENTS + " ADD COLUMN " +
1381                Calendar.Events._SYNC_ACCOUNT_TYPE + " TEXT;");
1382        db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;");
1383        db.execSQL("UPDATE " + Tables.CALENDARS
1384                + " SET " + Calendar.Calendars._SYNC_ACCOUNT_TYPE + "='com.google'"
1385                + " WHERE " + Calendar.Calendars._SYNC_ACCOUNT + " IS NOT NULL");
1386        db.execSQL("UPDATE " + Tables.EVENTS
1387                + " SET " + Calendar.Events._SYNC_ACCOUNT_TYPE + "='com.google'"
1388                + " WHERE " + Calendar.Events._SYNC_ACCOUNT + " IS NOT NULL");
1389        db.execSQL("UPDATE DeletedEvents"
1390                + " SET _sync_account_type='com.google'"
1391                + " WHERE _sync_account IS NOT NULL");
1392        Log.w(TAG, "re-creating eventSyncAccountAndIdIndex");
1393        db.execSQL("DROP INDEX eventSyncAccountAndIdIndex");
1394        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON " + Tables.EVENTS + " ("
1395                + Calendar.Events._SYNC_ACCOUNT_TYPE + ", "
1396                + Calendar.Events._SYNC_ACCOUNT + ", "
1397                + Calendar.Events._SYNC_ID + ");");
1398    }
1399
1400    private void upgradeToVersion54(SQLiteDatabase db) {
1401        Log.w(TAG, "adding eventSyncAccountAndIdIndex");
1402        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events ("
1403                + Calendar.Events._SYNC_ACCOUNT + ", " + Calendar.Events._SYNC_ID + ");");
1404    }
1405
1406    private void upgradeToVersion53(SQLiteDatabase db) {
1407        Log.w(TAG, "Upgrading CalendarAlerts table");
1408        db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " +
1409                Calendar.CalendarAlerts.CREATION_TIME + " INTEGER DEFAULT 0;");
1410        db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " +
1411                Calendar.CalendarAlerts.RECEIVED_TIME + " INTEGER DEFAULT 0;");
1412        db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " +
1413                Calendar.CalendarAlerts.NOTIFY_TIME + " INTEGER DEFAULT 0;");
1414    }
1415
1416    private void upgradeToVersion52(SQLiteDatabase db) {
1417        // We added "originalAllDay" to the Events table to keep track of
1418        // the allDay status of the original recurring event for entries
1419        // that are exceptions to that recurring event.  We need this so
1420        // that we can format the date correctly for the "originalInstanceTime"
1421        // column when we make a change to the recurrence exception and
1422        // send it to the server.
1423        db.execSQL("ALTER TABLE " + Tables.EVENTS + " ADD COLUMN " +
1424                Calendar.Events.ORIGINAL_ALL_DAY + " INTEGER;");
1425
1426        // Iterate through the Events table and for each recurrence
1427        // exception, fill in the correct value for "originalAllDay",
1428        // if possible.  The only times where this might not be possible
1429        // are (1) the original recurring event no longer exists, or
1430        // (2) the original recurring event does not yet have a _sync_id
1431        // because it was created on the phone and hasn't been synced to the
1432        // server yet.  In both cases the originalAllDay field will be set
1433        // to null.  In the first case we don't care because the recurrence
1434        // exception will not be displayed and we won't be able to make
1435        // any changes to it (and even if we did, the server should ignore
1436        // them, right?).  In the second case, the calendar client already
1437        // disallows making changes to an instance of a recurring event
1438        // until the recurring event has been synced to the server so the
1439        // second case should never occur.
1440
1441        // "cursor" iterates over all the recurrences exceptions.
1442        Cursor cursor = db.rawQuery("SELECT " + Calendar.Events._ID + "," +
1443                Calendar.Events.ORIGINAL_EVENT +
1444                " FROM " + Tables.EVENTS +
1445                " WHERE " + Calendar.Events.ORIGINAL_EVENT + " IS NOT NULL",
1446                null /* selection args */);
1447        if (cursor != null) {
1448            try {
1449                while (cursor.moveToNext()) {
1450                    long id = cursor.getLong(0);
1451                    String originalEvent = cursor.getString(1);
1452
1453                    // Find the original recurring event (if it exists)
1454                    Cursor recur = db.rawQuery("SELECT " + Calendar.Events.ALL_DAY +
1455                            " FROM " + Tables.EVENTS +
1456                            " WHERE " + Calendar.Events._SYNC_ID + "=?",
1457                            new String[] {originalEvent});
1458                    if (recur == null) {
1459                        continue;
1460                    }
1461
1462                    try {
1463                        // Fill in the "originalAllDay" field of the
1464                        // recurrence exception with the "allDay" value
1465                        // from the recurring event.
1466                        if (recur.moveToNext()) {
1467                            int allDay = recur.getInt(0);
1468                            db.execSQL("UPDATE " + Tables.EVENTS +
1469                                    " SET " + Calendar.Events.ORIGINAL_ALL_DAY + "=" + allDay +
1470                                    " WHERE " + Calendar.Events._ID + "="+id);
1471                        }
1472                    } finally {
1473                        recur.close();
1474                    }
1475                }
1476            } finally {
1477                cursor.close();
1478            }
1479        }
1480    }
1481
1482    private void upgradeToVersion51(SQLiteDatabase db) {
1483        Log.w(TAG, "Upgrading DeletedEvents table");
1484
1485        // We don't have enough information to fill in the correct
1486        // value of the calendar_id for old rows in the DeletedEvents
1487        // table, but rows in that table are transient so it is unlikely
1488        // that there are any rows.  Plus, the calendar_id is used only
1489        // when deleting a calendar, which is a rare event.  All new rows
1490        // will have the correct calendar_id.
1491        db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;");
1492
1493        // Trigger to remove a calendar's events when we delete the calendar
1494        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1495        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
1496                "BEGIN " +
1497                "DELETE FROM " + Tables.EVENTS + " WHERE " + Calendar.Events.CALENDAR_ID + "=" +
1498                    "old." + Calendar.Events._ID + ";" +
1499                "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" +
1500                "END");
1501        db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted");
1502    }
1503
1504    private void dropTables(SQLiteDatabase db) {
1505        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDARS + ";");
1506        db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS + ";");
1507        db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS_RAW_TIMES + ";");
1508        db.execSQL("DROP TABLE IF EXISTS " + Tables.INSTANCES + ";");
1509        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_META_DATA + ";");
1510        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";");
1511        db.execSQL("DROP TABLE IF EXISTS " + Tables.ATTENDEES + ";");
1512        db.execSQL("DROP TABLE IF EXISTS " + Tables.REMINDERS + ";");
1513        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_ALERTS + ";");
1514        db.execSQL("DROP TABLE IF EXISTS " + Tables.EXTENDED_PROPERTIES + ";");
1515    }
1516
1517    @Override
1518    public synchronized SQLiteDatabase getWritableDatabase() {
1519        SQLiteDatabase db = super.getWritableDatabase();
1520        return db;
1521    }
1522
1523    public SyncStateContentProviderHelper getSyncState() {
1524        return mSyncState;
1525    }
1526
1527    /**
1528     * Schedule a calendar sync for the account.
1529     * @param account the account for which to schedule a sync
1530     * @param uploadChangesOnly if set, specify that the sync should only send
1531     *   up local changes.  This is typically used for a local sync, a user override of
1532     *   too many deletions, or a sync after a calendar is unselected.
1533     * @param url the url feed for the calendar to sync (may be null, in which case a poll of
1534     *   all feeds is done.)
1535     */
1536    void scheduleSync(Account account, boolean uploadChangesOnly, String url) {
1537        Bundle extras = new Bundle();
1538        if (uploadChangesOnly) {
1539            extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly);
1540        }
1541        if (url != null) {
1542            extras.putString("feed", url);
1543            extras.putBoolean(ContentResolver.SYNC_EXTRAS_MANUAL, true);
1544        }
1545        ContentResolver.requestSync(account, Calendar.Calendars.CONTENT_URI.getAuthority(), extras);
1546    }
1547
1548    private static void createEventsView(SQLiteDatabase db) {
1549        db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";");
1550        String eventsSelect = "SELECT "
1551                + Tables.EVENTS + "." + Calendar.Events._ID + " AS " + Calendar.Events._ID + ","
1552                + Calendar.Events.HTML_URI + ","
1553                + Calendar.Events.TITLE + ","
1554                + Calendar.Events.DESCRIPTION + ","
1555                + Calendar.Events.EVENT_LOCATION + ","
1556                + Calendar.Events.STATUS + ","
1557                + Calendar.Events.SELF_ATTENDEE_STATUS + ","
1558                + Calendar.Events.COMMENTS_URI + ","
1559                + Calendar.Events.DTSTART + ","
1560                + Calendar.Events.DTEND + ","
1561                + Calendar.Events.DURATION + ","
1562                + Calendar.Events.EVENT_TIMEZONE + ","
1563                + Calendar.Events.ALL_DAY + ","
1564                + Calendar.Events.VISIBILITY + ","
1565                + Calendar.Calendars.TIMEZONE + ","
1566                + Calendar.Calendars.SELECTED + ","
1567                + Calendar.Calendars.ACCESS_LEVEL + ","
1568                + Calendar.Events.TRANSPARENCY + ","
1569                + Calendar.Calendars.COLOR + ","
1570                + Calendar.Events.HAS_ALARM + ","
1571                + Calendar.Events.HAS_EXTENDED_PROPERTIES + ","
1572                + Calendar.Events.RRULE + ","
1573                + Calendar.Events.RDATE + ","
1574                + Calendar.Events.EXRULE + ","
1575                + Calendar.Events.EXDATE + ","
1576                + Calendar.Events.ORIGINAL_EVENT + ","
1577                + Calendar.Events.ORIGINAL_INSTANCE_TIME + ","
1578                + Calendar.Events.ORIGINAL_ALL_DAY + ","
1579                + Calendar.Events.LAST_DATE + ","
1580                + Calendar.Events.HAS_ATTENDEE_DATA + ","
1581                + Calendar.Events.CALENDAR_ID + ","
1582                + Calendar.Events.GUESTS_CAN_INVITE_OTHERS + ","
1583                + Calendar.Events.GUESTS_CAN_MODIFY + ","
1584                + Calendar.Events.GUESTS_CAN_SEE_GUESTS + ","
1585                + Calendar.Events.ORGANIZER + ","
1586                + Tables.EVENTS + "." + Calendar.Events.DELETED
1587                + " AS " + Calendar.Events.DELETED + ","
1588                + Tables.EVENTS + "." + Calendar.Events._SYNC_ID
1589                + " AS " + Calendar.Events._SYNC_ID + ","
1590                + Tables.EVENTS + "." + Calendar.Events._SYNC_VERSION
1591                + " AS " + Calendar.Events._SYNC_VERSION + ","
1592                + Tables.EVENTS + "." + Calendar.Events._SYNC_DIRTY
1593                + " AS " + Calendar.Events._SYNC_DIRTY + ","
1594                + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT
1595                + " AS " + Calendar.Events._SYNC_ACCOUNT + ","
1596                + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT_TYPE
1597                + " AS " + Calendar.Events._SYNC_ACCOUNT_TYPE + ","
1598                + Tables.EVENTS + "." + Calendar.Events._SYNC_TIME
1599                + " AS " + Calendar.Events._SYNC_TIME + ","
1600                + Tables.EVENTS + "." + Calendar.Events._SYNC_DATA
1601                + " AS " + Calendar.Events._SYNC_DATA + ","
1602                + Tables.EVENTS + "." + Calendar.Events._SYNC_MARK
1603                + " AS " + Calendar.Events._SYNC_MARK + ","
1604                + Calendar.Calendars.SYNC1 + ","
1605                + Calendar.Calendars.OWNER_ACCOUNT + ","
1606                + Calendar.Calendars.SYNC_EVENTS
1607                + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS
1608                + " ON (" + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID
1609                + "=" + Tables.CALENDARS + "." + Calendar.Calendars._ID
1610                + ")";
1611
1612        db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect);
1613    }
1614
1615    /**
1616     * Extracts the calendar email from a calendar feed url.
1617     * @param feed the calendar feed url
1618     * @return the calendar email that is in the feed url or null if it can't
1619     * find the email address.
1620     * TODO: this is duplicated in CalendarSyncAdapter; move to a library
1621     */
1622    public static String calendarEmailAddressFromFeedUrl(String feed) {
1623        // Example feed url:
1624        // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees
1625        String[] pathComponents = feed.split("/");
1626        if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) {
1627            try {
1628                return URLDecoder.decode(pathComponents[5], "UTF-8");
1629            } catch (UnsupportedEncodingException e) {
1630                Log.e(TAG, "unable to url decode the email address in calendar " + feed);
1631                return null;
1632            }
1633        }
1634
1635        Log.e(TAG, "unable to find the email address in calendar " + feed);
1636        return null;
1637    }
1638
1639    /**
1640     * Get a "allcalendars" url from a "private/full" or "private/free-busy" url
1641     * @param url
1642     * @return the rewritten Url
1643     *
1644     * For example:
1645     *
1646     *      http://www.google.com/calendar/feeds/joe%40joe.com/private/full
1647     *      http://www.google.com/calendar/feeds/joe%40joe.com/private/free-busy
1648     *
1649     * will be rewriten into:
1650     *
1651     *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
1652     *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
1653     */
1654    @VisibleForTesting
1655    private static String getAllCalendarsUrlFromEventsUrl(String url) {
1656        if (url == null) {
1657            if (Log.isLoggable(TAG, Log.DEBUG)) {
1658                Log.d(TAG, "Cannot get AllCalendars url from a NULL url");
1659            }
1660            return null;
1661        }
1662        if (url.contains("/private/full")) {
1663            return url.replace("/private/full", "").
1664                    replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
1665        }
1666        if (url.contains("/private/free-busy")) {
1667            return url.replace("/private/free-busy", "").
1668                    replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
1669        }
1670        // Just log as we dont recognize the provided Url
1671        if (Log.isLoggable(TAG, Log.DEBUG)) {
1672            Log.d(TAG, "Cannot get AllCalendars url from the following url: " + url);
1673        }
1674        return null;
1675    }
1676
1677    /**
1678     * Get "selfUrl" from "events url"
1679     * @param url the Events url (either "private/full" or "private/free-busy"
1680     * @return the corresponding allcalendar url
1681     */
1682    private static String getSelfUrlFromEventsUrl(String url) {
1683        return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
1684    }
1685
1686    /**
1687     * Get "editUrl" from "events url"
1688     * @param url the Events url (either "private/full" or "private/free-busy"
1689     * @return the corresponding allcalendar url
1690     */
1691    private static String getEditUrlFromEventsUrl(String url) {
1692        return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
1693    }
1694
1695    /**
1696     * Rewrite the url from "http" to "https" scheme
1697     * @param url the url to rewrite
1698     * @return the rewritten URL
1699     */
1700    private static String rewriteUrlFromHttpToHttps(String url) {
1701        if (url == null) {
1702            if (Log.isLoggable(TAG, Log.DEBUG)) {
1703                Log.d(TAG, "Cannot rewrite a NULL url");
1704            }
1705            return null;
1706        }
1707        if (url.startsWith(SCHEMA_HTTPS)) {
1708            return url;
1709        }
1710        if (!url.startsWith(SCHEMA_HTTP)) {
1711            throw new IllegalArgumentException("invalid url parameter, unknown scheme: " + url);
1712        }
1713        return SCHEMA_HTTPS + url.substring(SCHEMA_HTTP.length());
1714    }
1715}
1716