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