CalendarDatabaseHelper.java revision 58313767dd0e7e3823c35ca61b40eddb9dd229bb 
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            // This is needed for "converting" 72 to 200, 73 to 201 and 74 to 202
671            // TODO: This code needs to be deleted later on especially when Froyo starts hitting
672            // those numbers
673            if (oldVersion == 72) {
674                oldVersion = 200;
675            }
676            if (oldVersion == 73) {
677                oldVersion = 201;
678            }
679            if (oldVersion == 74) {
680                oldVersion = 202;
681            }
682            if (oldVersion == 200) {
683                upgradeToVersion201(db);
684                oldVersion += 1;
685            }
686            if (oldVersion == 201) {
687                upgradeToVersion202(db);
688                oldVersion += 1;
689            }
690            if (oldVersion == 202 && need203Update) {
691                upgradeToVersion203(db);
692                oldVersion += 1;
693            }
694            if (oldVersion != DATABASE_VERSION) {
695                Log.e(TAG, "Need to recreate Calendar schema because of "
696                        + "unknown Calendar database version: " + oldVersion);
697                dropTables(db);
698                bootstrapDB(db);
699                oldVersion = DATABASE_VERSION;
700            }
701        } catch (SQLiteException e) {
702            Log.e(TAG, "onUpgrade: SQLiteException, recreating db. " + e);
703            dropTables(db);
704            bootstrapDB(db);
705            return; // this was lossy
706        }
707    }
708
709    /**
710     * If the user_version of the database if between 59 and 66 (those versions has been deployed
711     * with no primary key for the CalendarMetaData table)
712     */
713    private void recreateMetaDataAndInstances(SQLiteDatabase db) {
714        // Recreate the CalendarMetaData table with correct primary key
715        db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + ";");
716        createCalendarMetaDataTable(db);
717
718        // Also clean the Instance table as this table may be corrupted
719        db.execSQL("DELETE FROM " + Tables.INSTANCES + ";");
720    }
721
722    private static boolean fixAllDayTime(Time time, String timezone, Long timeInMillis) {
723        time.set(timeInMillis);
724        if(time.hour != 0 || time.minute != 0 || time.second != 0) {
725            time.hour = 0;
726            time.minute = 0;
727            time.second = 0;
728            return true;
729        }
730        return false;
731    }
732
733    @VisibleForTesting
734    void upgradeToVersion203(SQLiteDatabase db) {
735        // Same as Gingerbread version 100
736        Cursor cursor = db.rawQuery("SELECT value from CalendarCache WHERE key=?",
737                new String[] {"timezoneDatabaseVersion"});
738
739        String oldTimezoneDbVersion = null;
740        if (cursor != null && cursor.moveToNext()) {
741            try {
742                oldTimezoneDbVersion = cursor.getString(0);
743            } finally {
744                cursor.close();
745            }
746        }
747        initCalendarCacheTable(db, oldTimezoneDbVersion);
748
749        // Same as Gingerbread version 101
750        updateCalendarCacheTableTo203(db);
751    }
752
753    @VisibleForTesting
754    void upgradeToVersion202(SQLiteDatabase db) {
755        // We will drop the "hidden" column from the calendar schema and add the "sync5" column
756        db.execSQL("ALTER TABLE " + Tables.CALENDARS +" RENAME TO " +
757                Tables.CALENDARS + "_Backup;");
758
759        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
760        createCalendarsTable(db);
761
762        // Populate the new Calendars table and put into the "sync5" column the value of the
763        // old "hidden" column
764        db.execSQL("INSERT INTO " + Tables.CALENDARS + " (" +
765                Calendar.Calendars._ID + ", " +
766                Calendar.Calendars._SYNC_ACCOUNT + ", " +
767                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
768                Calendar.Calendars._SYNC_ID + ", " +
769                Calendar.Calendars._SYNC_VERSION + ", " +
770                Calendar.Calendars._SYNC_TIME + ", " +
771                Calendar.Calendars._SYNC_DATA + ", " +
772                Calendar.Calendars._SYNC_DIRTY + ", " +
773                Calendar.Calendars._SYNC_MARK + ", " +
774                Calendar.Calendars.NAME + ", " +
775                Calendar.Calendars.DISPLAY_NAME + ", " +
776                Calendar.Calendars.COLOR + ", " +
777                Calendar.Calendars.ACCESS_LEVEL + ", " +
778                Calendar.Calendars.SELECTED + ", " +
779                Calendar.Calendars.SYNC_EVENTS + ", " +
780                Calendar.Calendars.LOCATION + ", " +
781                Calendar.Calendars.TIMEZONE + ", " +
782                Calendar.Calendars.OWNER_ACCOUNT + ", " +
783                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
784                Calendar.Calendars.DELETED + ", " +
785                Calendar.Calendars.SYNC1 + ", " +
786                Calendar.Calendars.SYNC2 + ", " +
787                Calendar.Calendars.SYNC3 + ", " +
788                Calendar.Calendars.SYNC4 + ", " +
789                Calendar.Calendars.SYNC5 + ") " +
790                "SELECT " +
791                Calendar.Calendars._ID + ", " +
792                Calendar.Calendars._SYNC_ACCOUNT + ", " +
793                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
794                Calendar.Calendars._SYNC_ID + ", " +
795                Calendar.Calendars._SYNC_VERSION + ", " +
796                Calendar.Calendars._SYNC_TIME + ", " +
797                Calendar.Calendars._SYNC_DATA + ", " +
798                Calendar.Calendars._SYNC_DIRTY + ", " +
799                Calendar.Calendars._SYNC_MARK + ", " +
800                Calendar.Calendars.NAME + ", " +
801                Calendar.Calendars.DISPLAY_NAME + ", " +
802                Calendar.Calendars.COLOR + ", " +
803                Calendar.Calendars.ACCESS_LEVEL + ", " +
804                Calendar.Calendars.SELECTED + ", " +
805                Calendar.Calendars.SYNC_EVENTS + ", " +
806                Calendar.Calendars.LOCATION + ", " +
807                Calendar.Calendars.TIMEZONE + ", " +
808                Calendar.Calendars.OWNER_ACCOUNT + ", " +
809                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
810                Calendar.Calendars.DELETED + ", " +
811                Calendar.Calendars.SYNC1 + ", " +
812                Calendar.Calendars.SYNC2 + ", " +
813                Calendar.Calendars.SYNC3 + ", " +
814                Calendar.Calendars.SYNC4 + " " +
815                "hidden" + " " +
816                "FROM " + Tables.CALENDARS + "_Backup" + ";"
817        );
818
819        // Drop the backup table
820        db.execSQL("DROP TABLE " + Tables.CALENDARS + "_Backup;");
821
822        // Recreate the Events Views as column "hidden" has been deleted
823        createEventsView(db);
824    }
825
826    @VisibleForTesting
827    void upgradeToVersion201(SQLiteDatabase db) {
828        db.execSQL("ALTER TABLE " + Tables.CALENDARS +
829                " ADD COLUMN " + Calendar.Calendars.SYNC4 + " TEXT;");
830    }
831
832    @VisibleForTesting
833    void upgradeToVersion200(SQLiteDatabase db) {
834        // we cannot use here a Calendar.Calendars,URL constant for "url" as we are trying to make
835        // it disappear so we are keeping the hardcoded name "url" in all the SQLs
836        db.execSQL("ALTER TABLE " + Tables.CALENDARS +" RENAME TO " +
837                Tables.CALENDARS + "_Backup;");
838
839        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
840        createCalendarsTable(db);
841
842        // Populate the new Calendars table except the SYNC2 / SYNC3 columns
843        db.execSQL("INSERT INTO " + Tables.CALENDARS + " (" +
844                Calendar.Calendars._ID + ", " +
845                Calendar.Calendars._SYNC_ACCOUNT + ", " +
846                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
847                Calendar.Calendars._SYNC_ID + ", " +
848                Calendar.Calendars._SYNC_VERSION + ", " +
849                Calendar.Calendars._SYNC_TIME + ", " +
850                Calendar.Calendars._SYNC_DATA + ", " +
851                Calendar.Calendars._SYNC_DIRTY + ", " +
852                Calendar.Calendars._SYNC_MARK + ", " +
853                Calendar.Calendars.NAME + ", " +
854                Calendar.Calendars.DISPLAY_NAME + ", " +
855                Calendar.Calendars.COLOR + ", " +
856                Calendar.Calendars.ACCESS_LEVEL + ", " +
857                Calendar.Calendars.SELECTED + ", " +
858                Calendar.Calendars.SYNC_EVENTS + ", " +
859                Calendar.Calendars.LOCATION + ", " +
860                Calendar.Calendars.TIMEZONE + ", " +
861                Calendar.Calendars.OWNER_ACCOUNT + ", " +
862                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
863                Calendar.Calendars.DELETED + ", " +
864                Calendar.Calendars.SYNC1 + ") " +
865                "SELECT " +
866                Calendar.Calendars._ID + ", " +
867                Calendar.Calendars._SYNC_ACCOUNT + ", " +
868                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ", " +
869                Calendar.Calendars._SYNC_ID + ", " +
870                Calendar.Calendars._SYNC_VERSION + ", " +
871                Calendar.Calendars._SYNC_TIME + ", " +
872                Calendar.Calendars._SYNC_DATA + ", " +
873                Calendar.Calendars._SYNC_DIRTY + ", " +
874                Calendar.Calendars._SYNC_MARK + ", " +
875                Calendar.Calendars.NAME + ", " +
876                Calendar.Calendars.DISPLAY_NAME + ", " +
877                Calendar.Calendars.COLOR + ", " +
878                Calendar.Calendars.ACCESS_LEVEL + ", " +
879                Calendar.Calendars.SELECTED + ", " +
880                Calendar.Calendars.SYNC_EVENTS + ", " +
881                Calendar.Calendars.LOCATION + ", " +
882                Calendar.Calendars.TIMEZONE + ", " +
883                Calendar.Calendars.OWNER_ACCOUNT + ", " +
884                Calendar.Calendars.ORGANIZER_CAN_RESPOND + ", " +
885                "0" + ", " +
886                "url" + " " +
887                "FROM " + Tables.CALENDARS + "_Backup" + ";"
888        );
889
890        // Populate SYNC2 and SYNC3 columns - SYNC1 represent the old "url" column
891        // We will need to iterate over all the "com.google" type of calendars
892        String selectSql = "SELECT " + Calendar.Calendars._ID + ", " + "url" +
893                " FROM " + Tables.CALENDARS + "_Backup" +
894                " WHERE " + Calendar.Calendars._SYNC_ACCOUNT_TYPE  + "='com.google'" +
895                " AND url IS NOT NULL;";
896
897        String updateSql = "UPDATE " + Tables.CALENDARS + " SET " +
898                Calendar.Calendars.SYNC2 + "=?, " + // edit Url
899                Calendar.Calendars.SYNC3 + "=? " + // self Url
900                "WHERE " + Calendar.Calendars._ID + "=?;";
901
902        Cursor cursor = db.rawQuery(selectSql, null /* selection args */);
903        if (cursor != null && cursor.getCount() > 0) {
904            try {
905                Object[] bindArgs = new Object[3];
906
907                while (cursor.moveToNext()) {
908                    Long id = cursor.getLong(0);
909                    String url = cursor.getString(1);
910                    String selfUrl = getSelfUrlFromEventsUrl(url);
911                    String editUrl = getEditUrlFromEventsUrl(url);
912
913                    bindArgs[0] = editUrl;
914                    bindArgs[1] = selfUrl;
915                    bindArgs[2] = id;
916
917                    db.execSQL(updateSql, bindArgs);
918                }
919            } finally {
920                cursor.close();
921            }
922        }
923
924        // Drop the backup table
925        db.execSQL("DROP TABLE " + Tables.CALENDARS + "_Backup;");
926
927        // Recreate the Events Views as column "deleted" is now ambiguous
928        // ("deleted" is now defined in both Calendars and Events tables)
929        createEventsView(db);
930    }
931
932    @VisibleForTesting
933    static void upgradeToVersion69(SQLiteDatabase db) {
934        // Clean up allDay events which could be in an invalid state from an earlier version
935        // Some allDay events had hour, min, sec not set to zero, which throws elsewhere. This
936        // will go through the allDay events and make sure they have proper values and are in the
937        // correct timezone. Verifies that dtstart and dtend are in UTC and at midnight, that
938        // eventTimezone is set to UTC, tries to make sure duration is in days, and that dtstart2
939        // and dtend2 are at midnight in their timezone.
940        final String sql = "SELECT " + Calendar.Events._ID + ", " +
941                Calendar.Events.DTSTART + ", " +
942                Calendar.Events.DTEND + ", " +
943                Calendar.Events.DURATION + ", " +
944                Calendar.Events.DTSTART2 + ", " +
945                Calendar.Events.DTEND2 + ", " +
946                Calendar.Events.EVENT_TIMEZONE + ", " +
947                Calendar.Events.EVENT_TIMEZONE2 + ", " +
948                Calendar.Events.RRULE + " " +
949                "FROM " + Tables.EVENTS + " " +
950                "WHERE " + Calendar.Events.ALL_DAY + "=?";
951        Cursor cursor = db.rawQuery(sql, new String[] {"1"});
952        if (cursor != null) {
953            try {
954                String timezone;
955                String timezone2;
956                String duration;
957                Long dtstart;
958                Long dtstart2;
959                Long dtend;
960                Long dtend2;
961                Time time = new Time();
962                Long id;
963                // some things need to be in utc so we call this frequently, cache to make faster
964                final String utc = Time.TIMEZONE_UTC;
965                while (cursor.moveToNext()) {
966                    String rrule = cursor.getString(8);
967                    id = cursor.getLong(0);
968                    dtstart = cursor.getLong(1);
969                    dtstart2 = null;
970                    timezone = cursor.getString(6);
971                    timezone2 = cursor.getString(7);
972                    duration = cursor.getString(3);
973
974                    if (TextUtils.isEmpty(rrule)) {
975                        // For non-recurring events dtstart and dtend should both have values
976                        // and duration should be null.
977                        dtend = cursor.getLong(2);
978                        dtend2 = null;
979                        // Since we made all three of these at the same time if timezone2 exists
980                        // so should dtstart2 and dtend2.
981                        if(!TextUtils.isEmpty(timezone2)) {
982                            dtstart2 = cursor.getLong(4);
983                            dtend2 = cursor.getLong(5);
984                        }
985
986                        boolean update = false;
987                        if (!TextUtils.equals(timezone, utc)) {
988                            update = true;
989                            timezone = utc;
990                        }
991
992                        time.clear(timezone);
993                        update |= fixAllDayTime(time, timezone, dtstart);
994                        dtstart = time.normalize(false);
995
996                        time.clear(timezone);
997                        update |= fixAllDayTime(time, timezone, dtend);
998                        dtend = time.normalize(false);
999
1000                        if (dtstart2 != null) {
1001                            time.clear(timezone2);
1002                            update |= fixAllDayTime(time, timezone2, dtstart2);
1003                            dtstart2 = time.normalize(false);
1004                        }
1005
1006                        if (dtend2 != null) {
1007                            time.clear(timezone2);
1008                            update |= fixAllDayTime(time, timezone2, dtend2);
1009                            dtend2 = time.normalize(false);
1010                        }
1011
1012                        if (!TextUtils.isEmpty(duration)) {
1013                            update = true;
1014                        }
1015
1016                        if (update) {
1017                            // enforce duration being null
1018                            db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1019                                    Calendar.Events.DTSTART + "=?, " +
1020                                    Calendar.Events.DTEND + "=?, " +
1021                                    Calendar.Events.DTSTART2 + "=?, " +
1022                                    Calendar.Events.DTEND2 + "=?, " +
1023                                    Calendar.Events.DURATION + "=?, " +
1024                                    Calendar.Events.EVENT_TIMEZONE + "=?, " +
1025                                    Calendar.Events.EVENT_TIMEZONE2 + "=? " +
1026                                    "WHERE " + Calendar.Events._ID + "=?",
1027                                    new Object[] {
1028                                            dtstart,
1029                                            dtend,
1030                                            dtstart2,
1031                                            dtend2,
1032                                            null,
1033                                            timezone,
1034                                            timezone2,
1035                                            id}
1036                            );
1037                        }
1038
1039                    } else {
1040                        // For recurring events only dtstart and duration should be used.
1041                        // We ignore dtend since it will be overwritten if the event changes to a
1042                        // non-recurring event and won't be used otherwise.
1043                        if(!TextUtils.isEmpty(timezone2)) {
1044                            dtstart2 = cursor.getLong(4);
1045                        }
1046
1047                        boolean update = false;
1048                        if (!TextUtils.equals(timezone, utc)) {
1049                            update = true;
1050                            timezone = utc;
1051                        }
1052
1053                        time.clear(timezone);
1054                        update |= fixAllDayTime(time, timezone, dtstart);
1055                        dtstart = time.normalize(false);
1056
1057                        if (dtstart2 != null) {
1058                            time.clear(timezone2);
1059                            update |= fixAllDayTime(time, timezone2, dtstart2);
1060                            dtstart2 = time.normalize(false);
1061                        }
1062
1063                        if (TextUtils.isEmpty(duration)) {
1064                            // If duration was missing assume a 1 day duration
1065                            duration = "P1D";
1066                            update = true;
1067                        } else {
1068                            int len = duration.length();
1069                            // TODO fix durations in other formats as well
1070                            if (duration.charAt(0) == 'P' &&
1071                                    duration.charAt(len - 1) == 'S') {
1072                                int seconds = Integer.parseInt(duration.substring(1, len - 1));
1073                                int days = (seconds + DAY_IN_SECONDS - 1) / DAY_IN_SECONDS;
1074                                duration = "P" + days + "D";
1075                                update = true;
1076                            }
1077                        }
1078
1079                        if (update) {
1080                            // If there were other problems also enforce dtend being null
1081                            db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1082                                    Calendar.Events.DTSTART + "=?, " +
1083                                    Calendar.Events.DTEND + "=?, " +
1084                                    Calendar.Events.DTSTART2 + "=?, " +
1085                                    Calendar.Events.DTEND2 + "=?, " +
1086                                    Calendar.Events.DURATION + "=?," +
1087                                    Calendar.Events.EVENT_TIMEZONE + "=?, " +
1088                                    Calendar.Events.EVENT_TIMEZONE2 + "=? " +
1089                                    "WHERE " + Calendar.Events._ID + "=?",
1090                                    new Object[] {
1091                                            dtstart,
1092                                            null,
1093                                            dtstart2,
1094                                            null,
1095                                            duration,
1096                                            timezone,
1097                                            timezone2,
1098                                            id}
1099                            );
1100                        }
1101                    }
1102                }
1103            } finally {
1104                cursor.close();
1105            }
1106        }
1107    }
1108
1109    private void upgradeToVersion66(SQLiteDatabase db) {
1110        // Add a column to indicate whether the event organizer can respond to his own events
1111        // The UI should not show attendee status for events in calendars with this column = 0
1112        db.execSQL("ALTER TABLE " + Tables.CALENDARS +
1113                " ADD COLUMN " + Calendar.Calendars.ORGANIZER_CAN_RESPOND +
1114                    " INTEGER NOT NULL DEFAULT 1;");
1115    }
1116
1117    private void upgradeToVersion65(SQLiteDatabase db) {
1118        // we need to recreate the Events view
1119        createEventsView(db);
1120    }
1121
1122    private void upgradeToVersion64(SQLiteDatabase db) {
1123        // Add a column that may be used by sync adapters
1124        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1125                " ADD COLUMN " + Calendar.Events.SYNC_ADAPTER_DATA + " TEXT;");
1126    }
1127
1128    private void upgradeToVersion63(SQLiteDatabase db) {
1129        // we need to recreate the Events view
1130        createEventsView(db);
1131    }
1132
1133    private void upgradeToVersion62(SQLiteDatabase db) {
1134        // New columns are to transition to having allDay events in the local timezone
1135        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1136                " ADD COLUMN " + Calendar.Events.DTSTART2 + " INTEGER;");
1137        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1138                " ADD COLUMN " + Calendar.Events.DTEND2 + " INTEGER;");
1139        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1140                " ADD COLUMN " + Calendar.Events.EVENT_TIMEZONE2 + " TEXT;");
1141
1142        String[] allDayBit = new String[] {"0"};
1143        // Copy over all the data that isn't an all day event.
1144        db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1145                Calendar.Events.DTSTART2 + "=" + Calendar.Events.DTSTART + "," +
1146                Calendar.Events.DTEND2 + "=" + Calendar.Events.DTEND + "," +
1147                Calendar.Events.EVENT_TIMEZONE2 + "=" + Calendar.Events.EVENT_TIMEZONE + " " +
1148                "WHERE " + Calendar.Events.ALL_DAY + "=?;",
1149                allDayBit /* selection args */);
1150
1151        // "cursor" iterates over all the calendars
1152        allDayBit[0] = "1";
1153        Cursor cursor = db.rawQuery("SELECT " + Tables.EVENTS + "." + Calendar.Events._ID + "," +
1154                Calendar.Events.DTSTART + "," +
1155                Calendar.Events.DTEND + "," +
1156                Calendar.Events.EVENT_TIMEZONE + "," +
1157                Calendar.Calendars.TIMEZONE + " " +
1158                "FROM " + Tables.EVENTS + " INNER JOIN " + Tables.CALENDARS + " " +
1159                "WHERE " + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID + "=" +
1160                    Tables.CALENDARS + "." + Calendar.Calendars._ID +
1161                " AND "
1162                    + Calendar.Events.ALL_DAY + "=?",
1163                allDayBit /* selection args */);
1164
1165        Time oldTime = new Time();
1166        Time newTime = new Time();
1167        // Update the allday events in the new columns
1168        if (cursor != null) {
1169            try {
1170                String[] newData = new String[4];
1171                cursor.moveToPosition(-1);
1172                while (cursor.moveToNext()) {
1173                    long id = cursor.getLong(0); // Order from query above
1174                    long dtstart = cursor.getLong(1);
1175                    long dtend = cursor.getLong(2);
1176                    String eTz = cursor.getString(3); // current event timezone
1177                    String tz = cursor.getString(4); // Calendar timezone
1178                    //If there's no timezone for some reason use UTC by default.
1179                    if(eTz == null) {
1180                        eTz = Time.TIMEZONE_UTC;
1181                    }
1182
1183                    // Convert start time for all day events into the timezone of their calendar
1184                    oldTime.clear(eTz);
1185                    oldTime.set(dtstart);
1186                    newTime.clear(tz);
1187                    newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
1188                    newTime.normalize(false);
1189                    dtstart = newTime.toMillis(false /*ignoreDst*/);
1190
1191                    // Convert end time for all day events into the timezone of their calendar
1192                    oldTime.clear(eTz);
1193                    oldTime.set(dtend);
1194                    newTime.clear(tz);
1195                    newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
1196                    newTime.normalize(false);
1197                    dtend = newTime.toMillis(false /*ignoreDst*/);
1198
1199                    newData[0] = String.valueOf(dtstart);
1200                    newData[1] = String.valueOf(dtend);
1201                    newData[2] = tz;
1202                    newData[3] = String.valueOf(id);
1203                    db.execSQL("UPDATE " + Tables.EVENTS + " SET " +
1204                            Calendar.Events.DTSTART2 + "=?, " +
1205                            Calendar.Events.DTEND2 + "=?, " +
1206                            Calendar.Events.EVENT_TIMEZONE2 + "=? " +
1207                            "WHERE " + Calendar.Events._ID + "=?",
1208                            newData);
1209                }
1210            } finally {
1211                cursor.close();
1212            }
1213        }
1214    }
1215
1216    private void upgradeToVersion61(SQLiteDatabase db) {
1217        db.execSQL("DROP TABLE IF EXISTS CalendarCache;");
1218
1219        // IF NOT EXISTS should be normal pattern for table creation
1220        db.execSQL("CREATE TABLE IF NOT EXISTS CalendarCache (" +
1221                "_id INTEGER PRIMARY KEY," +
1222                "key TEXT NOT NULL," +
1223                "value TEXT" +
1224                ");");
1225
1226        db.execSQL("INSERT INTO CalendarCache (key, value) VALUES (" +
1227                "'" + CalendarCache.KEY_TIMEZONE_DATABASE_VERSION + "',"  +
1228                "'" + CalendarCache.DEFAULT_TIMEZONE_DATABASE_VERSION + "'" +
1229                ");");
1230    }
1231
1232    private void upgradeToVersion60(SQLiteDatabase db) {
1233        // Switch to CalendarProvider2
1234        upgradeSyncState(db);
1235        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1236        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
1237                "BEGIN " +
1238                CALENDAR_CLEANUP_TRIGGER_SQL +
1239                "END");
1240        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1241                " ADD COLUMN " + Calendar.Events.DELETED + " INTEGER NOT NULL DEFAULT 0;");
1242        db.execSQL("DROP TRIGGER IF EXISTS events_insert");
1243        // Trigger to set event's sync_account
1244        db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON " + Tables.EVENTS + " " +
1245                "BEGIN " +
1246                AFTER_EVENT_INSERT_SQL +
1247                "END");
1248        db.execSQL("DROP TABLE IF EXISTS DeletedEvents;");
1249        db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
1250        // Trigger to remove data tied to an event when we delete that event.
1251        db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
1252                "BEGIN " +
1253                EVENTS_CLEANUP_TRIGGER_SQL +
1254                "END");
1255        db.execSQL("DROP TRIGGER IF EXISTS attendees_update");
1256        db.execSQL("DROP TRIGGER IF EXISTS attendees_insert");
1257        db.execSQL("DROP TRIGGER IF EXISTS attendees_delete");
1258        db.execSQL("DROP TRIGGER IF EXISTS reminders_update");
1259        db.execSQL("DROP TRIGGER IF EXISTS reminders_insert");
1260        db.execSQL("DROP TRIGGER IF EXISTS reminders_delete");
1261        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update");
1262        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert");
1263        db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete");
1264
1265        createEventsView(db);
1266    }
1267
1268    private void upgradeToVersion59(SQLiteDatabase db) {
1269        db.execSQL("DROP TABLE IF EXISTS BusyBits;");
1270        db.execSQL("CREATE TEMPORARY TABLE " + Tables.CALENDAR_META_DATA + "_Backup" + "(" +
1271                Calendar.CalendarMetaData._ID + "," +
1272                Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," +
1273                Calendar.CalendarMetaData.MIN_INSTANCE + "," +
1274                Calendar.CalendarMetaData.MAX_INSTANCE +
1275                ");");
1276        db.execSQL("INSERT INTO " + Tables.CALENDAR_META_DATA + "_Backup " +
1277                "SELECT " +
1278                Calendar.CalendarMetaData._ID + "," +
1279                Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," +
1280                Calendar.CalendarMetaData.MIN_INSTANCE + "," +
1281                Calendar.CalendarMetaData.MAX_INSTANCE +
1282                " FROM " + Tables.CALENDAR_META_DATA + ";");
1283        db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + ";");
1284        createCalendarMetaDataTable(db);
1285        db.execSQL("INSERT INTO " + Tables.CALENDAR_META_DATA + " " +
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 + "_Backup;");
1292        db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + "_Backup;");
1293    }
1294
1295    private void upgradeToVersion57(SQLiteDatabase db) {
1296        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1297                " ADD COLUMN " + Calendar.Events.GUESTS_CAN_MODIFY +
1298                " INTEGER NOT NULL DEFAULT 0;");
1299        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1300                " ADD COLUMN " + Calendar.Events.GUESTS_CAN_INVITE_OTHERS +
1301                " INTEGER NOT NULL DEFAULT 1;");
1302        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1303                " ADD COLUMN " + Calendar.Events.GUESTS_CAN_SEE_GUESTS +
1304                " INTEGER NOT NULL DEFAULT 1;");
1305        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1306                " ADD COLUMN " + Calendar.Events.ORGANIZER +
1307                " STRING;");
1308        db.execSQL("UPDATE " + Tables.EVENTS + " SET " + Calendar.Events.ORGANIZER + "=" +
1309                "(SELECT " + Calendar.Attendees.ATTENDEE_EMAIL +
1310                " FROM " + Tables.ATTENDEES + ""  +
1311                " WHERE " +
1312                Tables.ATTENDEES + "." + Calendar.Attendees.EVENT_ID + "=" +
1313                Tables.EVENTS + "." + Calendar.Events._ID +
1314                " AND " +
1315                Tables.ATTENDEES + "." + Calendar.Attendees.ATTENDEE_RELATIONSHIP + "=2);");
1316    }
1317
1318    private void upgradeToVersion56(SQLiteDatabase db) {
1319        db.execSQL("ALTER TABLE " + Tables.CALENDARS +
1320                " ADD COLUMN " + Calendar.Calendars.OWNER_ACCOUNT + " TEXT;");
1321        db.execSQL("ALTER TABLE " + Tables.EVENTS +
1322                " ADD COLUMN " + Calendar.Events.HAS_ATTENDEE_DATA + " INTEGER;");
1323
1324        // Clear _sync_dirty to avoid a client-to-server sync that could blow away
1325        // server attendees.
1326        // Clear _sync_version to pull down the server's event (with attendees)
1327        // Change the URLs from full-selfattendance to full
1328        db.execSQL("UPDATE " + Tables.EVENTS
1329                + " SET " + Calendar.Events._SYNC_DIRTY + "=0, "
1330                + Calendar.Events._SYNC_VERSION + "=NULL, "
1331                + Calendar.Events._SYNC_ID + "="
1332                + "REPLACE(" + Calendar.Events._SYNC_ID + ", " +
1333                    "'/private/full-selfattendance', '/private/full'),"
1334                + Calendar.Events.COMMENTS_URI + "="
1335                + "REPLACE(" + Calendar.Events.COMMENTS_URI + ", " +
1336                    "'/private/full-selfattendance', '/private/full');");
1337
1338        db.execSQL("UPDATE " + Tables.CALENDARS
1339                + " SET " + "url="
1340                + "REPLACE(" + "url, " + "'/private/full-selfattendance', '/private/full');");
1341
1342        // "cursor" iterates over all the calendars
1343        Cursor cursor = db.rawQuery("SELECT " + Calendar.Calendars._ID + ", " +
1344                Calendar.Calendars.URL + " FROM " + Tables.CALENDARS,
1345                null /* selection args */);
1346        // Add the owner column.
1347        if (cursor != null) {
1348            try {
1349                final String updateSql = "UPDATE " + Tables.CALENDARS +
1350                        " SET " + Calendar.Calendars.OWNER_ACCOUNT + "=?" +
1351                        " WHERE " + Calendar.Calendars._ID + "=?";
1352                while (cursor.moveToNext()) {
1353                    Long id = cursor.getLong(0);
1354                    String url = cursor.getString(1);
1355                    String owner = calendarEmailAddressFromFeedUrl(url);
1356                    db.execSQL(updateSql, new Object[] {owner, id});
1357                }
1358            } finally {
1359                cursor.close();
1360            }
1361        }
1362    }
1363
1364    private void upgradeResync(SQLiteDatabase db) {
1365        // Delete sync state, so all records will be re-synced.
1366        db.execSQL("DELETE FROM " + Tables.SYNC_STATE + ";");
1367
1368        // "cursor" iterates over all the calendars
1369        Cursor cursor = db.rawQuery("SELECT " + Calendar.Calendars._SYNC_ACCOUNT + "," +
1370                Calendar.Calendars._SYNC_ACCOUNT_TYPE + ",url FROM " + Tables.CALENDARS,
1371                null /* selection args */);
1372        if (cursor != null) {
1373            try {
1374                while (cursor.moveToNext()) {
1375                    String accountName = cursor.getString(0);
1376                    String accountType = cursor.getString(1);
1377                    final Account account = new Account(accountName, accountType);
1378                    String calendarUrl = cursor.getString(2);
1379                    scheduleSync(account, false /* two-way sync */, calendarUrl);
1380                }
1381            } finally {
1382                cursor.close();
1383            }
1384        }
1385    }
1386
1387    private void upgradeToVersion55(SQLiteDatabase db) {
1388        db.execSQL("ALTER TABLE " + Tables.CALENDARS + " ADD COLUMN " +
1389                Calendar.Calendars._SYNC_ACCOUNT_TYPE + " TEXT;");
1390        db.execSQL("ALTER TABLE " + Tables.EVENTS + " ADD COLUMN " +
1391                Calendar.Events._SYNC_ACCOUNT_TYPE + " TEXT;");
1392        db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;");
1393        db.execSQL("UPDATE " + Tables.CALENDARS
1394                + " SET " + Calendar.Calendars._SYNC_ACCOUNT_TYPE + "='com.google'"
1395                + " WHERE " + Calendar.Calendars._SYNC_ACCOUNT + " IS NOT NULL");
1396        db.execSQL("UPDATE " + Tables.EVENTS
1397                + " SET " + Calendar.Events._SYNC_ACCOUNT_TYPE + "='com.google'"
1398                + " WHERE " + Calendar.Events._SYNC_ACCOUNT + " IS NOT NULL");
1399        db.execSQL("UPDATE DeletedEvents"
1400                + " SET _sync_account_type='com.google'"
1401                + " WHERE _sync_account IS NOT NULL");
1402        Log.w(TAG, "re-creating eventSyncAccountAndIdIndex");
1403        db.execSQL("DROP INDEX eventSyncAccountAndIdIndex");
1404        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON " + Tables.EVENTS + " ("
1405                + Calendar.Events._SYNC_ACCOUNT_TYPE + ", "
1406                + Calendar.Events._SYNC_ACCOUNT + ", "
1407                + Calendar.Events._SYNC_ID + ");");
1408    }
1409
1410    private void upgradeToVersion54(SQLiteDatabase db) {
1411        Log.w(TAG, "adding eventSyncAccountAndIdIndex");
1412        db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events ("
1413                + Calendar.Events._SYNC_ACCOUNT + ", " + Calendar.Events._SYNC_ID + ");");
1414    }
1415
1416    private void upgradeToVersion53(SQLiteDatabase db) {
1417        Log.w(TAG, "Upgrading CalendarAlerts table");
1418        db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " +
1419                Calendar.CalendarAlerts.CREATION_TIME + " INTEGER DEFAULT 0;");
1420        db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " +
1421                Calendar.CalendarAlerts.RECEIVED_TIME + " INTEGER DEFAULT 0;");
1422        db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " +
1423                Calendar.CalendarAlerts.NOTIFY_TIME + " INTEGER DEFAULT 0;");
1424    }
1425
1426    private void upgradeToVersion52(SQLiteDatabase db) {
1427        // We added "originalAllDay" to the Events table to keep track of
1428        // the allDay status of the original recurring event for entries
1429        // that are exceptions to that recurring event.  We need this so
1430        // that we can format the date correctly for the "originalInstanceTime"
1431        // column when we make a change to the recurrence exception and
1432        // send it to the server.
1433        db.execSQL("ALTER TABLE " + Tables.EVENTS + " ADD COLUMN " +
1434                Calendar.Events.ORIGINAL_ALL_DAY + " INTEGER;");
1435
1436        // Iterate through the Events table and for each recurrence
1437        // exception, fill in the correct value for "originalAllDay",
1438        // if possible.  The only times where this might not be possible
1439        // are (1) the original recurring event no longer exists, or
1440        // (2) the original recurring event does not yet have a _sync_id
1441        // because it was created on the phone and hasn't been synced to the
1442        // server yet.  In both cases the originalAllDay field will be set
1443        // to null.  In the first case we don't care because the recurrence
1444        // exception will not be displayed and we won't be able to make
1445        // any changes to it (and even if we did, the server should ignore
1446        // them, right?).  In the second case, the calendar client already
1447        // disallows making changes to an instance of a recurring event
1448        // until the recurring event has been synced to the server so the
1449        // second case should never occur.
1450
1451        // "cursor" iterates over all the recurrences exceptions.
1452        Cursor cursor = db.rawQuery("SELECT " + Calendar.Events._ID + "," +
1453                Calendar.Events.ORIGINAL_EVENT +
1454                " FROM " + Tables.EVENTS +
1455                " WHERE " + Calendar.Events.ORIGINAL_EVENT + " IS NOT NULL",
1456                null /* selection args */);
1457        if (cursor != null) {
1458            try {
1459                while (cursor.moveToNext()) {
1460                    long id = cursor.getLong(0);
1461                    String originalEvent = cursor.getString(1);
1462
1463                    // Find the original recurring event (if it exists)
1464                    Cursor recur = db.rawQuery("SELECT " + Calendar.Events.ALL_DAY +
1465                            " FROM " + Tables.EVENTS +
1466                            " WHERE " + Calendar.Events._SYNC_ID + "=?",
1467                            new String[] {originalEvent});
1468                    if (recur == null) {
1469                        continue;
1470                    }
1471
1472                    try {
1473                        // Fill in the "originalAllDay" field of the
1474                        // recurrence exception with the "allDay" value
1475                        // from the recurring event.
1476                        if (recur.moveToNext()) {
1477                            int allDay = recur.getInt(0);
1478                            db.execSQL("UPDATE " + Tables.EVENTS +
1479                                    " SET " + Calendar.Events.ORIGINAL_ALL_DAY + "=" + allDay +
1480                                    " WHERE " + Calendar.Events._ID + "="+id);
1481                        }
1482                    } finally {
1483                        recur.close();
1484                    }
1485                }
1486            } finally {
1487                cursor.close();
1488            }
1489        }
1490    }
1491
1492    private void upgradeToVersion51(SQLiteDatabase db) {
1493        Log.w(TAG, "Upgrading DeletedEvents table");
1494
1495        // We don't have enough information to fill in the correct
1496        // value of the calendar_id for old rows in the DeletedEvents
1497        // table, but rows in that table are transient so it is unlikely
1498        // that there are any rows.  Plus, the calendar_id is used only
1499        // when deleting a calendar, which is a rare event.  All new rows
1500        // will have the correct calendar_id.
1501        db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;");
1502
1503        // Trigger to remove a calendar's events when we delete the calendar
1504        db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1505        db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " +
1506                "BEGIN " +
1507                "DELETE FROM " + Tables.EVENTS + " WHERE " + Calendar.Events.CALENDAR_ID + "=" +
1508                    "old." + Calendar.Events._ID + ";" +
1509                "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" +
1510                "END");
1511        db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted");
1512    }
1513
1514    private void dropTables(SQLiteDatabase db) {
1515        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDARS + ";");
1516        db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS + ";");
1517        db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS_RAW_TIMES + ";");
1518        db.execSQL("DROP TABLE IF EXISTS " + Tables.INSTANCES + ";");
1519        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_META_DATA + ";");
1520        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";");
1521        db.execSQL("DROP TABLE IF EXISTS " + Tables.ATTENDEES + ";");
1522        db.execSQL("DROP TABLE IF EXISTS " + Tables.REMINDERS + ";");
1523        db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_ALERTS + ";");
1524        db.execSQL("DROP TABLE IF EXISTS " + Tables.EXTENDED_PROPERTIES + ";");
1525    }
1526
1527    @Override
1528    public synchronized SQLiteDatabase getWritableDatabase() {
1529        SQLiteDatabase db = super.getWritableDatabase();
1530        return db;
1531    }
1532
1533    public SyncStateContentProviderHelper getSyncState() {
1534        return mSyncState;
1535    }
1536
1537    /**
1538     * Schedule a calendar sync for the account.
1539     * @param account the account for which to schedule a sync
1540     * @param uploadChangesOnly if set, specify that the sync should only send
1541     *   up local changes.  This is typically used for a local sync, a user override of
1542     *   too many deletions, or a sync after a calendar is unselected.
1543     * @param url the url feed for the calendar to sync (may be null, in which case a poll of
1544     *   all feeds is done.)
1545     */
1546    void scheduleSync(Account account, boolean uploadChangesOnly, String url) {
1547        Bundle extras = new Bundle();
1548        if (uploadChangesOnly) {
1549            extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly);
1550        }
1551        if (url != null) {
1552            extras.putString("feed", url);
1553            extras.putBoolean(ContentResolver.SYNC_EXTRAS_MANUAL, true);
1554        }
1555        ContentResolver.requestSync(account, Calendar.Calendars.CONTENT_URI.getAuthority(), extras);
1556    }
1557
1558    private static void createEventsView(SQLiteDatabase db) {
1559        db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";");
1560        String eventsSelect = "SELECT "
1561                + Tables.EVENTS + "." + Calendar.Events._ID + " AS " + Calendar.Events._ID + ","
1562                + Calendar.Events.HTML_URI + ","
1563                + Calendar.Events.TITLE + ","
1564                + Calendar.Events.DESCRIPTION + ","
1565                + Calendar.Events.EVENT_LOCATION + ","
1566                + Calendar.Events.STATUS + ","
1567                + Calendar.Events.SELF_ATTENDEE_STATUS + ","
1568                + Calendar.Events.COMMENTS_URI + ","
1569                + Calendar.Events.DTSTART + ","
1570                + Calendar.Events.DTEND + ","
1571                + Calendar.Events.DURATION + ","
1572                + Calendar.Events.EVENT_TIMEZONE + ","
1573                + Calendar.Events.ALL_DAY + ","
1574                + Calendar.Events.VISIBILITY + ","
1575                + Calendar.Calendars.TIMEZONE + ","
1576                + Calendar.Calendars.SELECTED + ","
1577                + Calendar.Calendars.ACCESS_LEVEL + ","
1578                + Calendar.Events.TRANSPARENCY + ","
1579                + Calendar.Calendars.COLOR + ","
1580                + Calendar.Events.HAS_ALARM + ","
1581                + Calendar.Events.HAS_EXTENDED_PROPERTIES + ","
1582                + Calendar.Events.RRULE + ","
1583                + Calendar.Events.RDATE + ","
1584                + Calendar.Events.EXRULE + ","
1585                + Calendar.Events.EXDATE + ","
1586                + Calendar.Events.ORIGINAL_EVENT + ","
1587                + Calendar.Events.ORIGINAL_INSTANCE_TIME + ","
1588                + Calendar.Events.ORIGINAL_ALL_DAY + ","
1589                + Calendar.Events.LAST_DATE + ","
1590                + Calendar.Events.HAS_ATTENDEE_DATA + ","
1591                + Calendar.Events.CALENDAR_ID + ","
1592                + Calendar.Events.GUESTS_CAN_INVITE_OTHERS + ","
1593                + Calendar.Events.GUESTS_CAN_MODIFY + ","
1594                + Calendar.Events.GUESTS_CAN_SEE_GUESTS + ","
1595                + Calendar.Events.ORGANIZER + ","
1596                + Tables.EVENTS + "." + Calendar.Events.DELETED
1597                + " AS " + Calendar.Events.DELETED + ","
1598                + Tables.EVENTS + "." + Calendar.Events._SYNC_ID
1599                + " AS " + Calendar.Events._SYNC_ID + ","
1600                + Tables.EVENTS + "." + Calendar.Events._SYNC_VERSION
1601                + " AS " + Calendar.Events._SYNC_VERSION + ","
1602                + Tables.EVENTS + "." + Calendar.Events._SYNC_DIRTY
1603                + " AS " + Calendar.Events._SYNC_DIRTY + ","
1604                + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT
1605                + " AS " + Calendar.Events._SYNC_ACCOUNT + ","
1606                + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT_TYPE
1607                + " AS " + Calendar.Events._SYNC_ACCOUNT_TYPE + ","
1608                + Tables.EVENTS + "." + Calendar.Events._SYNC_TIME
1609                + " AS " + Calendar.Events._SYNC_TIME + ","
1610                + Tables.EVENTS + "." + Calendar.Events._SYNC_DATA
1611                + " AS " + Calendar.Events._SYNC_DATA + ","
1612                + Tables.EVENTS + "." + Calendar.Events._SYNC_MARK
1613                + " AS " + Calendar.Events._SYNC_MARK + ","
1614                + Calendar.Calendars.SYNC1 + ","
1615                + Calendar.Calendars.OWNER_ACCOUNT + ","
1616                + Calendar.Calendars.SYNC_EVENTS
1617                + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS
1618                + " ON (" + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID
1619                + "=" + Tables.CALENDARS + "." + Calendar.Calendars._ID
1620                + ")";
1621
1622        db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect);
1623    }
1624
1625    /**
1626     * Extracts the calendar email from a calendar feed url.
1627     * @param feed the calendar feed url
1628     * @return the calendar email that is in the feed url or null if it can't
1629     * find the email address.
1630     * TODO: this is duplicated in CalendarSyncAdapter; move to a library
1631     */
1632    public static String calendarEmailAddressFromFeedUrl(String feed) {
1633        // Example feed url:
1634        // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees
1635        String[] pathComponents = feed.split("/");
1636        if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) {
1637            try {
1638                return URLDecoder.decode(pathComponents[5], "UTF-8");
1639            } catch (UnsupportedEncodingException e) {
1640                Log.e(TAG, "unable to url decode the email address in calendar " + feed);
1641                return null;
1642            }
1643        }
1644
1645        Log.e(TAG, "unable to find the email address in calendar " + feed);
1646        return null;
1647    }
1648
1649    /**
1650     * Get a "allcalendars" url from a "private/full" or "private/free-busy" url
1651     * @param url
1652     * @return the rewritten Url
1653     *
1654     * For example:
1655     *
1656     *      http://www.google.com/calendar/feeds/joe%40joe.com/private/full
1657     *      http://www.google.com/calendar/feeds/joe%40joe.com/private/free-busy
1658     *
1659     * will be rewriten into:
1660     *
1661     *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
1662     *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
1663     */
1664    @VisibleForTesting
1665    private static String getAllCalendarsUrlFromEventsUrl(String url) {
1666        if (url == null) {
1667            if (Log.isLoggable(TAG, Log.DEBUG)) {
1668                Log.d(TAG, "Cannot get AllCalendars url from a NULL url");
1669            }
1670            return null;
1671        }
1672        if (url.contains("/private/full")) {
1673            return url.replace("/private/full", "").
1674                    replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
1675        }
1676        if (url.contains("/private/free-busy")) {
1677            return url.replace("/private/free-busy", "").
1678                    replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
1679        }
1680        // Just log as we dont recognize the provided Url
1681        if (Log.isLoggable(TAG, Log.DEBUG)) {
1682            Log.d(TAG, "Cannot get AllCalendars url from the following url: " + url);
1683        }
1684        return null;
1685    }
1686
1687    /**
1688     * Get "selfUrl" from "events url"
1689     * @param url the Events url (either "private/full" or "private/free-busy"
1690     * @return the corresponding allcalendar url
1691     */
1692    private static String getSelfUrlFromEventsUrl(String url) {
1693        return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
1694    }
1695
1696    /**
1697     * Get "editUrl" 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 getEditUrlFromEventsUrl(String url) {
1702        return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
1703    }
1704
1705    /**
1706     * Rewrite the url from "http" to "https" scheme
1707     * @param url the url to rewrite
1708     * @return the rewritten URL
1709     */
1710    private static String rewriteUrlFromHttpToHttps(String url) {
1711        if (url == null) {
1712            if (Log.isLoggable(TAG, Log.DEBUG)) {
1713                Log.d(TAG, "Cannot rewrite a NULL url");
1714            }
1715            return null;
1716        }
1717        if (url.startsWith(SCHEMA_HTTPS)) {
1718            return url;
1719        }
1720        if (!url.startsWith(SCHEMA_HTTP)) {
1721            throw new IllegalArgumentException("invalid url parameter, unknown scheme: " + url);
1722        }
1723        return SCHEMA_HTTPS + url.substring(SCHEMA_HTTP.length());
1724    }
1725}
1726