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