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 = 601;
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             if (oldVersion < 601) {
1416                 // There are no table changes in 601, but recreating the events view is required
1417                 createEventsView = true;
1418                 oldVersion = 601;
1419             }
1420 
1421             if (createEventsView) {
1422                 createEventsView(db);
1423             }
1424             if (oldVersion != DATABASE_VERSION) {
1425                 Log.e(TAG, "Need to recreate Calendar schema because of "
1426                         + "unknown Calendar database version: " + oldVersion);
1427                 dropTables(db);
1428                 bootstrapDB(db);
1429                 oldVersion = DATABASE_VERSION;
1430             } else {
1431                 removeOrphans(db);
1432             }
1433         } catch (SQLiteException e) {
1434             if (mInTestMode) {
1435                 // We do want to crash if we are in test mode.
1436                 throw e;
1437             }
1438             Log.e(TAG, "onUpgrade: SQLiteException, recreating db. ", e);
1439             Log.e(TAG, "(oldVersion was " + oldVersion + ")");
1440             dropTables(db);
1441             bootstrapDB(db);
1442             return; // this was lossy
1443         }
1444 
1445         long endWhen = System.nanoTime();
1446         Log.d(TAG, "Calendar upgrade took " + ((endWhen - startWhen) / 1000000) + "ms");
1447 
1448         /**
1449          * db versions < 100 correspond to Froyo and earlier. Gingerbread bumped
1450          * the db versioning to 100. Honeycomb bumped it to 200. ICS will begin
1451          * in 300. At each major release we should jump to the next
1452          * centiversion.
1453          */
1454     }
1455 
1456     @Override
onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)1457     public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
1458         Log.i(TAG, "Can't downgrade DB from version " + oldVersion + " to " + newVersion);
1459         dropTables(db);
1460         bootstrapDB(db);
1461         return;
1462     }
1463 
1464     /**
1465      * If the user_version of the database if between 59 and 66 (those versions has been deployed
1466      * with no primary key for the CalendarMetaData table)
1467      */
recreateMetaDataAndInstances67(SQLiteDatabase db)1468     private void recreateMetaDataAndInstances67(SQLiteDatabase db) {
1469         // Recreate the CalendarMetaData table with correct primary key
1470         db.execSQL("DROP TABLE CalendarMetaData;");
1471         createCalendarMetaDataTable59(db);
1472 
1473         // Also clean the Instance table as this table may be corrupted
1474         db.execSQL("DELETE FROM Instances;");
1475     }
1476 
fixAllDayTime(Time time, String timezone, Long timeInMillis)1477     private static boolean fixAllDayTime(Time time, String timezone, Long timeInMillis) {
1478         time.set(timeInMillis);
1479         if(time.hour != 0 || time.minute != 0 || time.second != 0) {
1480             time.hour = 0;
1481             time.minute = 0;
1482             time.second = 0;
1483             return true;
1484         }
1485         return false;
1486     }
1487 
1488     /**********************************************************/
1489     /* DO NOT USE CONSTANTS FOR UPGRADES, USE STRING LITERALS */
1490     /**********************************************************/
1491 
1492     /**********************************************************/
1493     /* 6xx db version is for K release
1494     /**********************************************************/
1495 
upgradeToVersion600(SQLiteDatabase db)1496     private void upgradeToVersion600(SQLiteDatabase db) {
1497         /*
1498          * Changes from version 5xx to 600:
1499          * - add mutator columns to Events & calendars
1500          */
1501         db.execSQL("ALTER TABLE Events ADD COLUMN mutators TEXT;");
1502         db.execSQL("ALTER TABLE Calendars ADD COLUMN mutators TEXT;");
1503     }
1504 
1505     /**********************************************************/
1506     /* 5xx db version is for JB MR1 release
1507     /**********************************************************/
1508 
upgradeToVersion501(SQLiteDatabase db)1509     private void upgradeToVersion501(SQLiteDatabase db) {
1510         /*
1511          * Changes from version 403 to 501:
1512          * - add isOrganizer column to Events table
1513          * - add isPrimary column to Calendars table
1514          */
1515         db.execSQL("ALTER TABLE Events ADD COLUMN isOrganizer INTEGER;");
1516         db.execSQL("ALTER TABLE Calendars ADD COLUMN isPrimary INTEGER;");
1517     }
1518 
upgradeToVersion502(SQLiteDatabase db)1519     private void upgradeToVersion502(SQLiteDatabase db) {
1520         /*
1521          * Changes from version 501 to 502:
1522          * - add UID for events added from the RFC 2445 iCalendar format.
1523          */
1524         db.execSQL("ALTER TABLE Events ADD COLUMN uid2445 TEXT;");
1525     }
1526 
1527     /**********************************************************/
1528     /* 4xx db version is for J release
1529     /**********************************************************/
1530 
upgradeToVersion403(SQLiteDatabase db)1531     private void upgradeToVersion403(SQLiteDatabase db) {
1532         /*
1533          * Changes from version 402 to 403:
1534          * - add custom app package name and uri Events table
1535          */
1536         db.execSQL("ALTER TABLE Events ADD COLUMN customAppPackage TEXT;");
1537         db.execSQL("ALTER TABLE Events ADD COLUMN customAppUri TEXT;");
1538     }
1539 
upgradeToVersion402(SQLiteDatabase db)1540     private void upgradeToVersion402(SQLiteDatabase db) {
1541         /*
1542          * Changes from version 401 to 402:
1543          * - add identity and namespace to Attendees table
1544          */
1545         db.execSQL("ALTER TABLE Attendees ADD COLUMN attendeeIdentity TEXT;");
1546         db.execSQL("ALTER TABLE Attendees ADD COLUMN attendeeIdNamespace TEXT;");
1547     }
1548 
1549     /*
1550      * Changes from version 309 to 401:
1551      * Fix repeating events' exceptions with the wrong original_id
1552      */
upgradeToVersion401(SQLiteDatabase db)1553     private void upgradeToVersion401(SQLiteDatabase db) {
1554         db.execSQL("UPDATE events SET original_id=(SELECT _id FROM events inner_events WHERE " +
1555                 "inner_events._sync_id=events.original_sync_id AND " +
1556                 "inner_events.calendar_id=events.calendar_id) WHERE NOT original_id IS NULL AND " +
1557                 "(SELECT calendar_id FROM events ex_events WHERE " +
1558                 "ex_events._id=events.original_id) <> calendar_id ");
1559     }
1560 
upgradeToVersion400(SQLiteDatabase db)1561     private void upgradeToVersion400(SQLiteDatabase db) {
1562         db.execSQL("DROP TRIGGER IF EXISTS calendar_color_update");
1563         // CREATE_CALENDAR_COLOR_UPDATE_TRIGGER was inlined
1564         db.execSQL("CREATE TRIGGER "
1565                 + "calendar_color_update" + " UPDATE OF " + Calendars.CALENDAR_COLOR_KEY
1566                 + " ON " + Tables.CALENDARS + " WHEN new." + Calendars.CALENDAR_COLOR_KEY
1567                 + " NOT NULL BEGIN " + "UPDATE " + Tables.CALENDARS
1568                 + " SET calendar_color=(SELECT " + Colors.COLOR + " FROM " + Tables.COLORS
1569                 + " WHERE " + Colors.ACCOUNT_NAME + "=" + "new." + Calendars.ACCOUNT_NAME + " AND "
1570                 + Colors.ACCOUNT_TYPE + "=" + "new." + Calendars.ACCOUNT_TYPE + " AND "
1571                 + Colors.COLOR_KEY + "=" + "new." + Calendars.CALENDAR_COLOR_KEY + " AND "
1572                 + Colors.COLOR_TYPE + "=" + Colors.TYPE_CALENDAR + ") "
1573                 + " WHERE " + Calendars._ID + "=" + "old." + Calendars._ID
1574                 + ";" + " END");
1575         db.execSQL("DROP TRIGGER IF EXISTS event_color_update");
1576         // CREATE_EVENT_COLOR_UPDATE_TRIGGER was inlined
1577         db.execSQL("CREATE TRIGGER "
1578                 + "event_color_update" + " UPDATE OF " + Events.EVENT_COLOR_KEY + " ON "
1579                 + Tables.EVENTS + " WHEN new." + Events.EVENT_COLOR_KEY + " NOT NULL BEGIN "
1580                 + "UPDATE " + Tables.EVENTS
1581                 + " SET eventColor=(SELECT " + Colors.COLOR + " FROM " + Tables.COLORS + " WHERE "
1582                 + Colors.ACCOUNT_NAME + "=" + "(SELECT " + Calendars.ACCOUNT_NAME + " FROM "
1583                 + Tables.CALENDARS + " WHERE " + Calendars._ID + "=new." + Events.CALENDAR_ID
1584                 + ") AND " + Colors.ACCOUNT_TYPE + "=" + "(SELECT " + Calendars.ACCOUNT_TYPE
1585                 + " FROM " + Tables.CALENDARS + " WHERE " + Calendars._ID + "=new."
1586                 + Events.CALENDAR_ID + ") AND " + Colors.COLOR_KEY + "=" + "new."
1587                 + Events.EVENT_COLOR_KEY + " AND " + Colors.COLOR_TYPE + "="
1588                 + Colors.TYPE_EVENT + ") "
1589                 + " WHERE " + Events._ID + "=" + "old." + Events._ID + ";" + " END");
1590     }
1591 
upgradeToVersion308(SQLiteDatabase db)1592     private void upgradeToVersion308(SQLiteDatabase db) {
1593         /*
1594          * Changes from version 307 to 308:
1595          * - add Colors table to db
1596          * - add eventColor_index to Events table
1597          * - add calendar_color_index to Calendars table
1598          * - add allowedAttendeeTypes to Calendars table
1599          * - add allowedAvailability to Calendars table
1600          */
1601         createColorsTable(db);
1602 
1603         db.execSQL("ALTER TABLE Calendars ADD COLUMN allowedAvailability TEXT DEFAULT '0,1';");
1604         db.execSQL("ALTER TABLE Calendars ADD COLUMN allowedAttendeeTypes TEXT DEFAULT '0,1,2';");
1605         db.execSQL("ALTER TABLE Calendars ADD COLUMN calendar_color_index TEXT;");
1606         db.execSQL("ALTER TABLE Events ADD COLUMN eventColor_index TEXT;");
1607 
1608         // Default Exchange calendars to be supporting the 'tentative'
1609         // availability as well
1610         db.execSQL("UPDATE Calendars SET allowedAvailability='0,1,2' WHERE _id IN "
1611                 + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');");
1612 
1613         // Triggers to update the color stored in an event or a calendar when
1614         // the color_index is changed.
1615         createColorsTriggers(db);
1616     }
1617 
upgradeToVersion307(SQLiteDatabase db)1618     private void upgradeToVersion307(SQLiteDatabase db) {
1619         /*
1620          * Changes from version 306 to 307:
1621          * - Changed _id field to AUTOINCREMENT
1622          */
1623         db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;");
1624         db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
1625         db.execSQL("DROP TRIGGER IF EXISTS original_sync_update");
1626         db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex");
1627         createEventsTable307(db);
1628 
1629         String FIELD_LIST =
1630             "_id, " +
1631             "_sync_id, " +
1632             "dirty, " +
1633             "lastSynced," +
1634             "calendar_id, " +
1635             "title, " +
1636             "eventLocation, " +
1637             "description, " +
1638             "eventColor, " +
1639             "eventStatus, " +
1640             "selfAttendeeStatus, " +
1641             "dtstart, " +
1642             "dtend, " +
1643             "eventTimezone, " +
1644             "duration, " +
1645             "allDay, " +
1646             "accessLevel, " +
1647             "availability, " +
1648             "hasAlarm, " +
1649             "hasExtendedProperties, " +
1650             "rrule, " +
1651             "rdate, " +
1652             "exrule, " +
1653             "exdate, " +
1654             "original_id," +
1655             "original_sync_id, " +
1656             "originalInstanceTime, " +
1657             "originalAllDay, " +
1658             "lastDate, " +
1659             "hasAttendeeData, " +
1660             "guestsCanModify, " +
1661             "guestsCanInviteOthers, " +
1662             "guestsCanSeeGuests, " +
1663             "organizer, " +
1664             "deleted, " +
1665             "eventEndTimezone, " +
1666             "sync_data1," +
1667             "sync_data2," +
1668             "sync_data3," +
1669             "sync_data4," +
1670             "sync_data5," +
1671             "sync_data6," +
1672             "sync_data7," +
1673             "sync_data8," +
1674             "sync_data9," +
1675             "sync_data10 ";
1676 
1677         // copy fields from old to new
1678         db.execSQL("INSERT INTO Events (" + FIELD_LIST + ") SELECT " + FIELD_LIST +
1679                 "FROM Events_Backup;");
1680 
1681         db.execSQL("DROP TABLE Events_Backup;");
1682 
1683         // Trigger to remove data tied to an event when we delete that event.
1684         db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
1685                 "BEGIN " + EVENTS_CLEANUP_TRIGGER_SQL + "END");
1686 
1687         // Trigger to update exceptions when an original event updates its
1688         // _sync_id
1689         db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
1690     }
1691 
upgradeToVersion306(SQLiteDatabase db)1692     private void upgradeToVersion306(SQLiteDatabase db) {
1693         /*
1694         * The following changes are for google.com accounts only.
1695         *
1696         * Change event id's from ".../private/full/... to .../events/...
1697         * Set Calendars.canPartiallyUpdate to 1 to support partial updates
1698         * Nuke sync state so we re-sync with a fresh etag and edit url
1699         *
1700         * We need to drop the original_sync_update trigger because it fires whenever the
1701         * sync_id field is touched, and dramatically slows this operation.
1702         */
1703         db.execSQL("DROP TRIGGER IF EXISTS original_sync_update");
1704         db.execSQL("UPDATE Events SET "
1705                 + "_sync_id = REPLACE(_sync_id, '/private/full/', '/events/'), "
1706                 + "original_sync_id = REPLACE(original_sync_id, '/private/full/', '/events/') "
1707                 + "WHERE _id IN (SELECT Events._id FROM Events "
1708                 +    "JOIN Calendars ON Events.calendar_id = Calendars._id "
1709                 +    "WHERE account_type = 'com.google')"
1710         );
1711         db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
1712 
1713         db.execSQL("UPDATE Calendars SET canPartiallyUpdate = 1 WHERE account_type = 'com.google'");
1714 
1715         db.execSQL("DELETE FROM _sync_state WHERE account_type = 'com.google'");
1716     }
1717 
upgradeToVersion305(SQLiteDatabase db)1718     private void upgradeToVersion305(SQLiteDatabase db) {
1719         /*
1720          * Changes from version 304 to 305:
1721          * -Add CAL_SYNC columns up to 10
1722          * -Rename Calendars.access_level to calendar_access_level
1723          * -Rename calendars _sync_version to cal_sync7
1724          * -Rename calendars _sync_time to cal_sync8
1725          * -Rename displayName to calendar_displayName
1726          * -Rename _sync_local_id to sync_data2
1727          * -Rename htmlUri to sync_data3
1728          * -Rename events _sync_version to sync_data4
1729          * -Rename events _sync_time to sync_data5
1730          * -Rename commentsUri to sync_data6
1731          * -Migrate Events _sync_mark to sync_data8
1732          * -Change sync_data2 from INTEGER to TEXT
1733          * -Change sync_data8 from INTEGER to TEXT
1734          * -Add SYNC_DATA columns up to 10
1735          * -Add EVENT_COLOR to Events table
1736          */
1737 
1738         // rename old table, create new table with updated layout
1739         db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1740         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1741         createCalendarsTable305(db);
1742 
1743         // copy fields from old to new
1744         db.execSQL("INSERT INTO Calendars (" +
1745                 "_id, " +
1746                 "account_name, " +
1747                 "account_type, " +
1748                 "_sync_id, " +
1749                 "cal_sync7, " +             // rename from _sync_version
1750                 "cal_sync8, " +             // rename from _sync_time
1751                 "dirty, " +
1752                 "name, " +
1753                 "calendar_displayName, " +  // rename from displayName
1754                 "calendar_color, " +
1755                 "calendar_access_level, " + // rename from access_level
1756                 "visible, " +
1757                 "sync_events, " +
1758                 "calendar_location, " +
1759                 "calendar_timezone, " +
1760                 "ownerAccount, " +
1761                 "canOrganizerRespond, " +
1762                 "canModifyTimeZone, " +
1763                 "maxReminders, " +
1764                 "allowedReminders, " +
1765                 "deleted, " +
1766                 "canPartiallyUpdate," +
1767                 "cal_sync1, " +
1768                 "cal_sync2, " +
1769                 "cal_sync3, " +
1770                 "cal_sync4, " +
1771                 "cal_sync5, " +
1772                 "cal_sync6) " +
1773                 "SELECT " +
1774                 "_id, " +
1775                 "account_name, " +
1776                 "account_type, " +
1777                 "_sync_id, " +
1778                 "_sync_version, " +
1779                 "_sync_time, " +
1780                 "dirty, " +
1781                 "name, " +
1782                 "displayName, " +
1783                 "calendar_color, " +
1784                 "access_level, " +
1785                 "visible, " +
1786                 "sync_events, " +
1787                 "calendar_location, " +
1788                 "calendar_timezone, " +
1789                 "ownerAccount, " +
1790                 "canOrganizerRespond, " +
1791                 "canModifyTimeZone, " +
1792                 "maxReminders, " +
1793                 "allowedReminders, " +
1794                 "deleted, " +
1795                 "canPartiallyUpdate," +
1796                 "cal_sync1, " +
1797                 "cal_sync2, " +
1798                 "cal_sync3, " +
1799                 "cal_sync4, " +
1800                 "cal_sync5, " +
1801                 "cal_sync6 " +
1802                 "FROM Calendars_Backup;");
1803 
1804         // drop the old table
1805         db.execSQL("DROP TABLE Calendars_Backup;");
1806 
1807         db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;");
1808         db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
1809         db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex");
1810         // 305 and 307 can share the same createEventsTable implementation, because the
1811         // addition of "autoincrement" to _ID doesn't affect the upgrade path.  (Note that
1812         // much older databases may also already have autoincrement set because the change
1813         // was back-ported.)
1814         createEventsTable307(db);
1815 
1816         // copy fields from old to new
1817         db.execSQL("INSERT INTO Events (" +
1818                 "_id, " +
1819                 "_sync_id, " +
1820                 "sync_data4, " +        // renamed from _sync_version
1821                 "sync_data5, " +        // renamed from _sync_time
1822                 "sync_data2, " +        // renamed from _sync_local_id
1823                 "dirty, " +
1824                 "sync_data8, " +        // renamed from _sync_mark
1825                 "calendar_id, " +
1826                 "sync_data3, " +        // renamed from htmlUri
1827                 "title, " +
1828                 "eventLocation, " +
1829                 "description, " +
1830                 "eventStatus, " +
1831                 "selfAttendeeStatus, " +
1832                 "sync_data6, " +        // renamed from commentsUri
1833                 "dtstart, " +
1834                 "dtend, " +
1835                 "eventTimezone, " +
1836                 "eventEndTimezone, " +
1837                 "duration, " +
1838                 "allDay, " +
1839                 "accessLevel, " +
1840                 "availability, " +
1841                 "hasAlarm, " +
1842                 "hasExtendedProperties, " +
1843                 "rrule, " +
1844                 "rdate, " +
1845                 "exrule, " +
1846                 "exdate, " +
1847                 "original_id," +
1848                 "original_sync_id, " +
1849                 "originalInstanceTime, " +
1850                 "originalAllDay, " +
1851                 "lastDate, " +
1852                 "hasAttendeeData, " +
1853                 "guestsCanModify, " +
1854                 "guestsCanInviteOthers, " +
1855                 "guestsCanSeeGuests, " +
1856                 "organizer, " +
1857                 "deleted, " +
1858                 "sync_data7," +
1859                 "lastSynced," +
1860                 "sync_data1) " +
1861 
1862                 "SELECT " +
1863                 "_id, " +
1864                 "_sync_id, " +
1865                 "_sync_version, " +
1866                 "_sync_time, " +
1867                 "_sync_local_id, " +
1868                 "dirty, " +
1869                 "_sync_mark, " +
1870                 "calendar_id, " +
1871                 "htmlUri, " +
1872                 "title, " +
1873                 "eventLocation, " +
1874                 "description, " +
1875                 "eventStatus, " +
1876                 "selfAttendeeStatus, " +
1877                 "commentsUri, " +
1878                 "dtstart, " +
1879                 "dtend, " +
1880                 "eventTimezone, " +
1881                 "eventEndTimezone, " +
1882                 "duration, " +
1883                 "allDay, " +
1884                 "accessLevel, " +
1885                 "availability, " +
1886                 "hasAlarm, " +
1887                 "hasExtendedProperties, " +
1888                 "rrule, " +
1889                 "rdate, " +
1890                 "exrule, " +
1891                 "exdate, " +
1892                 "original_id," +
1893                 "original_sync_id, " +
1894                 "originalInstanceTime, " +
1895                 "originalAllDay, " +
1896                 "lastDate, " +
1897                 "hasAttendeeData, " +
1898                 "guestsCanModify, " +
1899                 "guestsCanInviteOthers, " +
1900                 "guestsCanSeeGuests, " +
1901                 "organizer, " +
1902                 "deleted, " +
1903                 "sync_data7," +
1904                 "lastSynced," +
1905                 "sync_data1 " +
1906 
1907                 "FROM Events_Backup;"
1908         );
1909 
1910         db.execSQL("DROP TABLE Events_Backup;");
1911 
1912         // Trigger to remove data tied to an event when we delete that event.
1913         db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
1914                 "BEGIN " +
1915                 EVENTS_CLEANUP_TRIGGER_SQL +
1916                 "END");
1917 
1918         // Trigger to update exceptions when an original event updates its
1919         // _sync_id
1920         db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
1921     }
1922 
upgradeToVersion304(SQLiteDatabase db)1923     private void upgradeToVersion304(SQLiteDatabase db) {
1924         /*
1925          * Changes from version 303 to 304:
1926          * - add canPartiallyUpdate to Calendars table
1927          * - add sync_data7 to Calendars to Events table
1928          * - add lastSynced to Calendars to Events table
1929          */
1930         db.execSQL("ALTER TABLE Calendars ADD COLUMN canPartiallyUpdate INTEGER DEFAULT 0;");
1931         db.execSQL("ALTER TABLE Events ADD COLUMN sync_data7 TEXT;");
1932         db.execSQL("ALTER TABLE Events ADD COLUMN lastSynced INTEGER DEFAULT 0;");
1933     }
1934 
upgradeToVersion303(SQLiteDatabase db)1935     private void upgradeToVersion303(SQLiteDatabase db) {
1936         /*
1937          * Changes from version 302 to 303:
1938          * - change SYNCx columns to CAL_SYNCx
1939          */
1940 
1941         // rename old table, create new table with updated layout
1942         db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
1943         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
1944         createCalendarsTable303(db);
1945 
1946         // copy fields from old to new
1947         db.execSQL("INSERT INTO Calendars (" +
1948                 "_id, " +
1949                 "account_name, " +
1950                 "account_type, " +
1951                 "_sync_id, " +
1952                 "_sync_version, " +
1953                 "_sync_time, " +
1954                 "dirty, " +
1955                 "name, " +
1956                 "displayName, " +
1957                 "calendar_color, " +
1958                 "access_level, " +
1959                 "visible, " +
1960                 "sync_events, " +
1961                 "calendar_location, " +
1962                 "calendar_timezone, " +
1963                 "ownerAccount, " +
1964                 "canOrganizerRespond, " +
1965                 "canModifyTimeZone, " +
1966                 "maxReminders, " +
1967                 "allowedReminders, " +
1968                 "deleted, " +
1969                 "cal_sync1, " +     // rename from sync1
1970                 "cal_sync2, " +     // rename from sync2
1971                 "cal_sync3, " +     // rename from sync3
1972                 "cal_sync4, " +     // rename from sync4
1973                 "cal_sync5, " +     // rename from sync5
1974                 "cal_sync6) " +     // rename from sync6
1975                 "SELECT " +
1976                 "_id, " +
1977                 "account_name, " +
1978                 "account_type, " +
1979                 "_sync_id, " +
1980                 "_sync_version, " +
1981                 "_sync_time, " +
1982                 "dirty, " +
1983                 "name, " +
1984                 "displayName, " +
1985                 "calendar_color, " +
1986                 "access_level, " +
1987                 "visible, " +
1988                 "sync_events, " +
1989                 "calendar_location, " +
1990                 "calendar_timezone, " +
1991                 "ownerAccount, " +
1992                 "canOrganizerRespond, " +
1993                 "canModifyTimeZone, " +
1994                 "maxReminders, " +
1995                 "allowedReminders," +
1996                 "deleted, " +
1997                 "sync1, " +
1998                 "sync2, " +
1999                 "sync3, " +
2000                 "sync4," +
2001                 "sync5," +
2002                 "sync6 " +
2003                 "FROM Calendars_Backup;"
2004         );
2005 
2006         // drop the old table
2007         db.execSQL("DROP TABLE Calendars_Backup;");
2008     }
2009 
upgradeToVersion302(SQLiteDatabase db)2010     private void upgradeToVersion302(SQLiteDatabase db) {
2011         /*
2012          * Changes from version 301 to 302
2013          * - Move Exchange eventEndTimezone values to SYNC_DATA1
2014          */
2015         db.execSQL("UPDATE Events SET sync_data1=eventEndTimezone WHERE calendar_id IN "
2016                 + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');");
2017 
2018         db.execSQL("UPDATE Events SET eventEndTimezone=NULL WHERE calendar_id IN "
2019                 + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');");
2020     }
2021 
upgradeToVersion301(SQLiteDatabase db)2022     private void upgradeToVersion301(SQLiteDatabase db) {
2023         /*
2024          * Changes from version 300 to 301
2025          * - Added original_id column to Events table
2026          * - Added triggers to keep original_id and original_sync_id in sync
2027          */
2028 
2029         db.execSQL("DROP TRIGGER IF EXISTS " + SYNC_ID_UPDATE_TRIGGER_NAME + ";");
2030 
2031         db.execSQL("ALTER TABLE Events ADD COLUMN original_id INTEGER;");
2032 
2033         // Fill in the original_id for all events that have an original_sync_id
2034         db.execSQL("UPDATE Events set original_id=" +
2035                 "(SELECT Events2._id FROM Events AS Events2 " +
2036                         "WHERE Events2._sync_id=Events.original_sync_id) " +
2037                 "WHERE Events.original_sync_id NOT NULL");
2038         // Trigger to update exceptions when an original event updates its
2039         // _sync_id
2040         db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER);
2041     }
2042 
upgradeToVersion300(SQLiteDatabase db)2043     private void upgradeToVersion300(SQLiteDatabase db) {
2044 
2045         /*
2046          * Changes from version 205 to 300:
2047          * - rename _sync_account to account_name in Calendars table
2048          * - remove _sync_account from Events table
2049          * - rename _sync_account_type to account_type in Calendars table
2050          * - remove _sync_account_type from Events table
2051          * - rename _sync_dirty to dirty in Calendars/Events table
2052          * - rename color to calendar_color in Calendars table
2053          * - rename location to calendar_location in Calendars table
2054          * - rename timezone to calendar_timezone in Calendars table
2055          * - add allowedReminders in Calendars table
2056          * - rename visibility to accessLevel in Events table
2057          * - rename transparency to availability in Events table
2058          * - rename originalEvent to original_sync_id in Events table
2059          * - remove dtstart2 and dtend2 from Events table
2060          * - rename syncAdapterData to sync_data1 in Events table
2061          */
2062 
2063         // rename old table, create new table with updated layout
2064         db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
2065         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup;");
2066         createCalendarsTable300(db);
2067 
2068         // copy fields from old to new
2069         db.execSQL("INSERT INTO Calendars (" +
2070                 "_id, " +
2071                 "account_name, " +          // rename from _sync_account
2072                 "account_type, " +          // rename from _sync_account_type
2073                 "_sync_id, " +
2074                 "_sync_version, " +
2075                 "_sync_time, " +
2076                 "dirty, " +                 // rename from _sync_dirty
2077                 "name, " +
2078                 "displayName, " +
2079                 "calendar_color, " +        // rename from color
2080                 "access_level, " +
2081                 "visible, " +
2082                 "sync_events, " +
2083                 "calendar_location, " +     // rename from location
2084                 "calendar_timezone, " +     // rename from timezone
2085                 "ownerAccount, " +
2086                 "canOrganizerRespond, " +
2087                 "canModifyTimeZone, " +
2088                 "maxReminders, " +
2089                 "allowedReminders," +
2090                 "deleted, " +
2091                 "sync1, " +
2092                 "sync2, " +
2093                 "sync3, " +
2094                 "sync4," +
2095                 "sync5," +
2096                 "sync6) " +
2097 
2098                 "SELECT " +
2099                 "_id, " +
2100                 "_sync_account, " +
2101                 "_sync_account_type, " +
2102                 "_sync_id, " +
2103                 "_sync_version, " +
2104                 "_sync_time, " +
2105                 "_sync_dirty, " +
2106                 "name, " +
2107                 "displayName, " +
2108                 "color, " +
2109                 "access_level, " +
2110                 "visible, " +
2111                 "sync_events, " +
2112                 "location, " +
2113                 "timezone, " +
2114                 "ownerAccount, " +
2115                 "canOrganizerRespond, " +
2116                 "canModifyTimeZone, " +
2117                 "maxReminders, " +
2118                 "'0,1,2,3'," +
2119                 "deleted, " +
2120                 "sync1, " +
2121                 "sync2, " +
2122                 "sync3, " +
2123                 "sync4, " +
2124                 "sync5, " +
2125                 "sync6 " +
2126                 "FROM Calendars_Backup;"
2127         );
2128 
2129         /* expand the set of allowed reminders for Google calendars to include email */
2130         db.execSQL("UPDATE Calendars SET allowedReminders = '0,1,2' " +
2131                 "WHERE account_type = 'com.google'");
2132 
2133         // drop the old table
2134         db.execSQL("DROP TABLE Calendars_Backup;");
2135 
2136         db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;");
2137         db.execSQL("DROP TRIGGER IF EXISTS events_insert");
2138         db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
2139         db.execSQL("DROP INDEX IF EXISTS eventSyncAccountAndIdIndex");
2140         db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex");
2141         createEventsTable300(db);
2142 
2143         // copy fields from old to new
2144         db.execSQL("INSERT INTO Events (" +
2145                 "_id, " +
2146                 "_sync_id, " +
2147                 "_sync_version, " +
2148                 "_sync_time, " +
2149                 "_sync_local_id, " +
2150                 "dirty, " +                 // renamed from _sync_dirty
2151                 "_sync_mark, " +
2152                 "calendar_id, " +
2153                 "htmlUri, " +
2154                 "title, " +
2155                 "eventLocation, " +
2156                 "description, " +
2157                 "eventStatus, " +
2158                 "selfAttendeeStatus, " +
2159                 "commentsUri, " +
2160                 "dtstart, " +
2161                 "dtend, " +
2162                 "eventTimezone, " +
2163                 "eventEndTimezone, " +      // renamed from eventTimezone2
2164                 "duration, " +
2165                 "allDay, " +
2166                 "accessLevel, " +           // renamed from visibility
2167                 "availability, " +          // renamed from transparency
2168                 "hasAlarm, " +
2169                 "hasExtendedProperties, " +
2170                 "rrule, " +
2171                 "rdate, " +
2172                 "exrule, " +
2173                 "exdate, " +
2174                 "original_sync_id, " +      // renamed from originalEvent
2175                 "originalInstanceTime, " +
2176                 "originalAllDay, " +
2177                 "lastDate, " +
2178                 "hasAttendeeData, " +
2179                 "guestsCanModify, " +
2180                 "guestsCanInviteOthers, " +
2181                 "guestsCanSeeGuests, " +
2182                 "organizer, " +
2183                 "deleted, " +
2184                 "sync_data1) " +             // renamed from syncAdapterData
2185 
2186                 "SELECT " +
2187                 "_id, " +
2188                 "_sync_id, " +
2189                 "_sync_version, " +
2190                 "_sync_time, " +
2191                 "_sync_local_id, " +
2192                 "_sync_dirty, " +
2193                 "_sync_mark, " +
2194                 "calendar_id, " +
2195                 "htmlUri, " +
2196                 "title, " +
2197                 "eventLocation, " +
2198                 "description, " +
2199                 "eventStatus, " +
2200                 "selfAttendeeStatus, " +
2201                 "commentsUri, " +
2202                 "dtstart, " +
2203                 "dtend, " +
2204                 "eventTimezone, " +
2205                 "eventTimezone2, " +
2206                 "duration, " +
2207                 "allDay, " +
2208                 "visibility, " +
2209                 "transparency, " +
2210                 "hasAlarm, " +
2211                 "hasExtendedProperties, " +
2212                 "rrule, " +
2213                 "rdate, " +
2214                 "exrule, " +
2215                 "exdate, " +
2216                 "originalEvent, " +
2217                 "originalInstanceTime, " +
2218                 "originalAllDay, " +
2219                 "lastDate, " +
2220                 "hasAttendeeData, " +
2221                 "guestsCanModify, " +
2222                 "guestsCanInviteOthers, " +
2223                 "guestsCanSeeGuests, " +
2224                 "organizer, " +
2225                 "deleted, " +
2226                 "syncAdapterData " +
2227 
2228                 "FROM Events_Backup;"
2229         );
2230 
2231         db.execSQL("DROP TABLE Events_Backup;");
2232 
2233         // Trigger to remove data tied to an event when we delete that event.
2234         db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " +
2235                 "BEGIN " +
2236                 EVENTS_CLEANUP_TRIGGER_SQL +
2237                 "END");
2238 
2239     }
2240 
upgradeToVersion205(SQLiteDatabase db)2241     private void upgradeToVersion205(SQLiteDatabase db) {
2242         /*
2243          * Changes from version 204 to 205:
2244          * - rename+reorder "_sync_mark" to "sync6" (and change type from INTEGER to TEXT)
2245          * - rename "selected" to "visible"
2246          * - rename "organizerCanRespond" to "canOrganizerRespond"
2247          * - add "canModifyTimeZone"
2248          * - add "maxReminders"
2249          * - remove "_sync_local_id" (a/k/a _SYNC_DATA)
2250          */
2251 
2252         // rename old table, create new table with updated layout
2253         db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
2254         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
2255         createCalendarsTable205(db);
2256 
2257         // copy fields from old to new
2258         db.execSQL("INSERT INTO Calendars (" +
2259                 "_id, " +
2260                 "_sync_account, " +
2261                 "_sync_account_type, " +
2262                 "_sync_id, " +
2263                 "_sync_version, " +
2264                 "_sync_time, " +
2265                 "_sync_dirty, " +
2266                 "name, " +
2267                 "displayName, " +
2268                 "color, " +
2269                 "access_level, " +
2270                 "visible, " +                   // rename from "selected"
2271                 "sync_events, " +
2272                 "location, " +
2273                 "timezone, " +
2274                 "ownerAccount, " +
2275                 "canOrganizerRespond, " +       // rename from "organizerCanRespond"
2276                 "canModifyTimeZone, " +
2277                 "maxReminders, " +
2278                 "deleted, " +
2279                 "sync1, " +
2280                 "sync2, " +
2281                 "sync3, " +
2282                 "sync4," +
2283                 "sync5," +
2284                 "sync6) " +                     // rename/reorder from _sync_mark
2285                 "SELECT " +
2286                 "_id, " +
2287                 "_sync_account, " +
2288                 "_sync_account_type, " +
2289                 "_sync_id, " +
2290                 "_sync_version, " +
2291                 "_sync_time, " +
2292                 "_sync_dirty, " +
2293                 "name, " +
2294                 "displayName, " +
2295                 "color, " +
2296                 "access_level, " +
2297                 "selected, " +
2298                 "sync_events, " +
2299                 "location, " +
2300                 "timezone, " +
2301                 "ownerAccount, " +
2302                 "organizerCanRespond, " +
2303                 "1, " +
2304                 "5, " +
2305                 "deleted, " +
2306                 "sync1, " +
2307                 "sync2, " +
2308                 "sync3, " +
2309                 "sync4, " +
2310                 "sync5, " +
2311                 "_sync_mark " +
2312                 "FROM Calendars_Backup;"
2313         );
2314 
2315         // set these fields appropriately for Exchange events
2316         db.execSQL("UPDATE Calendars SET canModifyTimeZone=0, maxReminders=1 " +
2317                 "WHERE _sync_account_type='com.android.exchange'");
2318 
2319         // drop the old table
2320         db.execSQL("DROP TABLE Calendars_Backup;");
2321     }
2322 
upgradeToVersion203(SQLiteDatabase db)2323     private void upgradeToVersion203(SQLiteDatabase db) {
2324         // Same as Gingerbread version 100
2325         Cursor cursor = db.rawQuery("SELECT value FROM CalendarCache WHERE key=?",
2326                 new String[] {"timezoneDatabaseVersion"});
2327 
2328         String oldTimezoneDbVersion = null;
2329         if (cursor != null) {
2330             try {
2331                 if (cursor.moveToNext()) {
2332                     oldTimezoneDbVersion = cursor.getString(0);
2333                     cursor.close();
2334                     cursor = null;
2335                     // Also clean the CalendarCache table
2336                     db.execSQL("DELETE FROM CalendarCache;");
2337                 }
2338             } finally {
2339                 if (cursor != null) {
2340                     cursor.close();
2341                 }
2342             }
2343         }
2344         initCalendarCacheTable203(db, oldTimezoneDbVersion);
2345 
2346         // Same as Gingerbread version 101
2347         updateCalendarCacheTableTo203(db);
2348     }
2349 
upgradeToVersion202(SQLiteDatabase db)2350     private void upgradeToVersion202(SQLiteDatabase db) {
2351         // We will drop the "hidden" column from the calendar schema and add the "sync5" column
2352         db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
2353 
2354         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
2355         createCalendarsTable202(db);
2356 
2357         // Populate the new Calendars table and put into the "sync5" column the value of the
2358         // old "hidden" column
2359         db.execSQL("INSERT INTO Calendars (" +
2360                 "_id, " +
2361                 "_sync_account, " +
2362                 "_sync_account_type, " +
2363                 "_sync_id, " +
2364                 "_sync_version, " +
2365                 "_sync_time, " +
2366                 "_sync_local_id, " +
2367                 "_sync_dirty, " +
2368                 "_sync_mark, " +
2369                 "name, " +
2370                 "displayName, " +
2371                 "color, " +
2372                 "access_level, " +
2373                 "selected, " +
2374                 "sync_events, " +
2375                 "location, " +
2376                 "timezone, " +
2377                 "ownerAccount, " +
2378                 "organizerCanRespond, " +
2379                 "deleted, " +
2380                 "sync1, " +
2381                 "sync2, " +
2382                 "sync3, " +
2383                 "sync4," +
2384                 "sync5) " +
2385                 "SELECT " +
2386                 "_id, " +
2387                 "_sync_account, " +
2388                 "_sync_account_type, " +
2389                 "_sync_id, " +
2390                 "_sync_version, " +
2391                 "_sync_time, " +
2392                 "_sync_local_id, " +
2393                 "_sync_dirty, " +
2394                 "_sync_mark, " +
2395                 "name, " +
2396                 "displayName, " +
2397                 "color, " +
2398                 "access_level, " +
2399                 "selected, " +
2400                 "sync_events, " +
2401                 "location, " +
2402                 "timezone, " +
2403                 "ownerAccount, " +
2404                 "organizerCanRespond, " +
2405                 "deleted, " +
2406                 "sync1, " +
2407                 "sync2, " +
2408                 "sync3, " +
2409                 "sync4, " +
2410                 "hidden " +
2411                 "FROM Calendars_Backup;"
2412         );
2413 
2414         // Drop the backup table
2415         db.execSQL("DROP TABLE Calendars_Backup;");
2416     }
2417 
upgradeToVersion201(SQLiteDatabase db)2418     private void upgradeToVersion201(SQLiteDatabase db) {
2419         db.execSQL("ALTER TABLE Calendars ADD COLUMN sync4 TEXT;");
2420     }
2421 
upgradeToVersion200(SQLiteDatabase db)2422     private void upgradeToVersion200(SQLiteDatabase db) {
2423         // we cannot use here a Calendar.Calendars,URL constant for "url" as we are trying to make
2424         // it disappear so we are keeping the hardcoded name "url" in all the SQLs
2425         db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;");
2426 
2427         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
2428         createCalendarsTable200(db);
2429 
2430         // Populate the new Calendars table except the SYNC2 / SYNC3 columns
2431         db.execSQL("INSERT INTO Calendars (" +
2432                 "_id, " +
2433                 "_sync_account, " +
2434                 "_sync_account_type, " +
2435                 "_sync_id, " +
2436                 "_sync_version, " +
2437                 "_sync_time, " +
2438                 "_sync_local_id, " +
2439                 "_sync_dirty, " +
2440                 "_sync_mark, " +
2441                 "name, " +
2442                 "displayName, " +
2443                 "color, " +
2444                 "access_level, " +
2445                 "selected, " +
2446                 "sync_events, " +
2447                 "location, " +
2448                 "timezone, " +
2449                 "ownerAccount, " +
2450                 "organizerCanRespond, " +
2451                 "deleted, " +
2452                 "sync1) " +
2453                 "SELECT " +
2454                 "_id, " +
2455                 "_sync_account, " +
2456                 "_sync_account_type, " +
2457                 "_sync_id, " +
2458                 "_sync_version, " +
2459                 "_sync_time, " +
2460                 "_sync_local_id, " +
2461                 "_sync_dirty, " +
2462                 "_sync_mark, " +
2463                 "name, " +
2464                 "displayName, " +
2465                 "color, " +
2466                 "access_level, " +
2467                 "selected, " +
2468                 "sync_events, " +
2469                 "location, " +
2470                 "timezone, " +
2471                 "ownerAccount, " +
2472                 "organizerCanRespond, " +
2473                 "0, " +
2474                 "url " +
2475                 "FROM Calendars_Backup;"
2476         );
2477 
2478         // Populate SYNC2 and SYNC3 columns - SYNC1 represent the old "url" column
2479         // We will need to iterate over all the "com.google" type of calendars
2480         String selectSql = "SELECT _id, url" +
2481                 " FROM Calendars_Backup" +
2482                 " WHERE _sync_account_type='com.google'" +
2483                 " AND url IS NOT NULL;";
2484 
2485         String updateSql = "UPDATE Calendars SET " +
2486                 "sync2=?, " + // edit Url
2487                 "sync3=? " + // self Url
2488                 "WHERE _id=?;";
2489 
2490         Cursor cursor = db.rawQuery(selectSql, null /* selection args */);
2491         if (cursor != null) {
2492             try {
2493                 if (cursor.getCount() > 0) {
2494                     Object[] bindArgs = new Object[3];
2495                     while (cursor.moveToNext()) {
2496                         Long id = cursor.getLong(0);
2497                         String url = cursor.getString(1);
2498                         String selfUrl = getSelfUrlFromEventsUrl(url);
2499                         String editUrl = getEditUrlFromEventsUrl(url);
2500 
2501                         bindArgs[0] = editUrl;
2502                         bindArgs[1] = selfUrl;
2503                         bindArgs[2] = id;
2504 
2505                         db.execSQL(updateSql, bindArgs);
2506                     }
2507                 }
2508             } finally {
2509                 cursor.close();
2510             }
2511         }
2512 
2513         // Drop the backup table
2514         db.execSQL("DROP TABLE Calendars_Backup;");
2515     }
2516 
2517     @VisibleForTesting
upgradeToVersion69(SQLiteDatabase db)2518     public static void upgradeToVersion69(SQLiteDatabase db) {
2519         // Clean up allDay events which could be in an invalid state from an earlier version
2520         // Some allDay events had hour, min, sec not set to zero, which throws elsewhere. This
2521         // will go through the allDay events and make sure they have proper values and are in the
2522         // correct timezone. Verifies that dtstart and dtend are in UTC and at midnight, that
2523         // eventTimezone is set to UTC, tries to make sure duration is in days, and that dtstart2
2524         // and dtend2 are at midnight in their timezone.
2525         final String sql = "SELECT _id, " +
2526                 "dtstart, " +
2527                 "dtend, " +
2528                 "duration, " +
2529                 "dtstart2, " +
2530                 "dtend2, " +
2531                 "eventTimezone, " +
2532                 "eventTimezone2, " +
2533                 "rrule " +
2534                 "FROM Events " +
2535                 "WHERE allDay=?";
2536         Cursor cursor = db.rawQuery(sql, new String[] {"1"});
2537         if (cursor != null) {
2538             try {
2539                 String timezone;
2540                 String timezone2;
2541                 String duration;
2542                 Long dtstart;
2543                 Long dtstart2;
2544                 Long dtend;
2545                 Long dtend2;
2546                 Time time = new Time();
2547                 Long id;
2548                 // some things need to be in utc so we call this frequently, cache to make faster
2549                 final String utc = Time.TIMEZONE_UTC;
2550                 while (cursor.moveToNext()) {
2551                     String rrule = cursor.getString(8);
2552                     id = cursor.getLong(0);
2553                     dtstart = cursor.getLong(1);
2554                     dtstart2 = null;
2555                     timezone = cursor.getString(6);
2556                     timezone2 = cursor.getString(7);
2557                     duration = cursor.getString(3);
2558 
2559                     if (TextUtils.isEmpty(rrule)) {
2560                         // For non-recurring events dtstart and dtend should both have values
2561                         // and duration should be null.
2562                         dtend = cursor.getLong(2);
2563                         dtend2 = null;
2564                         // Since we made all three of these at the same time if timezone2 exists
2565                         // so should dtstart2 and dtend2.
2566                         if(!TextUtils.isEmpty(timezone2)) {
2567                             dtstart2 = cursor.getLong(4);
2568                             dtend2 = cursor.getLong(5);
2569                         }
2570 
2571                         boolean update = false;
2572                         if (!TextUtils.equals(timezone, utc)) {
2573                             update = true;
2574                             timezone = utc;
2575                         }
2576 
2577                         time.clear(timezone);
2578                         update |= fixAllDayTime(time, timezone, dtstart);
2579                         dtstart = time.normalize(false);
2580 
2581                         time.clear(timezone);
2582                         update |= fixAllDayTime(time, timezone, dtend);
2583                         dtend = time.normalize(false);
2584 
2585                         if (dtstart2 != null) {
2586                             time.clear(timezone2);
2587                             update |= fixAllDayTime(time, timezone2, dtstart2);
2588                             dtstart2 = time.normalize(false);
2589                         }
2590 
2591                         if (dtend2 != null) {
2592                             time.clear(timezone2);
2593                             update |= fixAllDayTime(time, timezone2, dtend2);
2594                             dtend2 = time.normalize(false);
2595                         }
2596 
2597                         if (!TextUtils.isEmpty(duration)) {
2598                             update = true;
2599                         }
2600 
2601                         if (update) {
2602                             // enforce duration being null
2603                             db.execSQL("UPDATE Events SET " +
2604                                     "dtstart=?, " +
2605                                     "dtend=?, " +
2606                                     "dtstart2=?, " +
2607                                     "dtend2=?, " +
2608                                     "duration=?, " +
2609                                     "eventTimezone=?, " +
2610                                     "eventTimezone2=? " +
2611                                     "WHERE _id=?",
2612                                     new Object[] {
2613                                             dtstart,
2614                                             dtend,
2615                                             dtstart2,
2616                                             dtend2,
2617                                             null,
2618                                             timezone,
2619                                             timezone2,
2620                                             id}
2621                             );
2622                         }
2623 
2624                     } else {
2625                         // For recurring events only dtstart and duration should be used.
2626                         // We ignore dtend since it will be overwritten if the event changes to a
2627                         // non-recurring event and won't be used otherwise.
2628                         if(!TextUtils.isEmpty(timezone2)) {
2629                             dtstart2 = cursor.getLong(4);
2630                         }
2631 
2632                         boolean update = false;
2633                         if (!TextUtils.equals(timezone, utc)) {
2634                             update = true;
2635                             timezone = utc;
2636                         }
2637 
2638                         time.clear(timezone);
2639                         update |= fixAllDayTime(time, timezone, dtstart);
2640                         dtstart = time.normalize(false);
2641 
2642                         if (dtstart2 != null) {
2643                             time.clear(timezone2);
2644                             update |= fixAllDayTime(time, timezone2, dtstart2);
2645                             dtstart2 = time.normalize(false);
2646                         }
2647 
2648                         if (TextUtils.isEmpty(duration)) {
2649                             // If duration was missing assume a 1 day duration
2650                             duration = "P1D";
2651                             update = true;
2652                         } else {
2653                             int len = duration.length();
2654                             // TODO fix durations in other formats as well
2655                             if (duration.charAt(0) == 'P' &&
2656                                     duration.charAt(len - 1) == 'S') {
2657                                 int seconds = Integer.parseInt(duration.substring(1, len - 1));
2658                                 int days = (seconds + DAY_IN_SECONDS - 1) / DAY_IN_SECONDS;
2659                                 duration = "P" + days + "D";
2660                                 update = true;
2661                             }
2662                         }
2663 
2664                         if (update) {
2665                             // If there were other problems also enforce dtend being null
2666                             db.execSQL("UPDATE Events SET " +
2667                                     "dtstart=?, " +
2668                                     "dtend=?, " +
2669                                     "dtstart2=?, " +
2670                                     "dtend2=?, " +
2671                                     "duration=?," +
2672                                     "eventTimezone=?, " +
2673                                     "eventTimezone2=? " +
2674                                     "WHERE _id=?",
2675                                     new Object[] {
2676                                             dtstart,
2677                                             null,
2678                                             dtstart2,
2679                                             null,
2680                                             duration,
2681                                             timezone,
2682                                             timezone2,
2683                                             id}
2684                             );
2685                         }
2686                     }
2687                 }
2688             } finally {
2689                 cursor.close();
2690             }
2691         }
2692     }
2693 
upgradeToVersion66(SQLiteDatabase db)2694     private void upgradeToVersion66(SQLiteDatabase db) {
2695         // Add a column to indicate whether the event organizer can respond to his own events
2696         // The UI should not show attendee status for events in calendars with this column = 0
2697         db.execSQL("ALTER TABLE Calendars" +
2698                 " ADD COLUMN organizerCanRespond INTEGER NOT NULL DEFAULT 1;");
2699     }
2700 
upgradeToVersion64(SQLiteDatabase db)2701     private void upgradeToVersion64(SQLiteDatabase db) {
2702         // Add a column that may be used by sync adapters
2703         db.execSQL("ALTER TABLE Events" +
2704                 " ADD COLUMN syncAdapterData TEXT;");
2705     }
2706 
upgradeToVersion62(SQLiteDatabase db)2707     private void upgradeToVersion62(SQLiteDatabase db) {
2708         // New columns are to transition to having allDay events in the local timezone
2709         db.execSQL("ALTER TABLE Events" +
2710                 " ADD COLUMN dtstart2 INTEGER;");
2711         db.execSQL("ALTER TABLE Events" +
2712                 " ADD COLUMN dtend2 INTEGER;");
2713         db.execSQL("ALTER TABLE Events" +
2714                 " ADD COLUMN eventTimezone2 TEXT;");
2715 
2716         String[] allDayBit = new String[] {"0"};
2717         // Copy over all the data that isn't an all day event.
2718         db.execSQL("UPDATE Events SET " +
2719                 "dtstart2=dtstart," +
2720                 "dtend2=dtend," +
2721                 "eventTimezone2=eventTimezone " +
2722                 "WHERE allDay=?;",
2723                 allDayBit /* selection args */);
2724 
2725         // "cursor" iterates over all the calendars
2726         allDayBit[0] = "1";
2727         Cursor cursor = db.rawQuery("SELECT Events._id," +
2728                 "dtstart," +
2729                 "dtend," +
2730                 "eventTimezone," +
2731                 "timezone " +
2732                 "FROM Events INNER JOIN Calendars " +
2733                 "WHERE Events.calendar_id=Calendars._id" +
2734                 " AND allDay=?",
2735                 allDayBit /* selection args */);
2736 
2737         Time oldTime = new Time();
2738         Time newTime = new Time();
2739         // Update the allday events in the new columns
2740         if (cursor != null) {
2741             try {
2742                 String[] newData = new String[4];
2743                 cursor.moveToPosition(-1);
2744                 while (cursor.moveToNext()) {
2745                     long id = cursor.getLong(0); // Order from query above
2746                     long dtstart = cursor.getLong(1);
2747                     long dtend = cursor.getLong(2);
2748                     String eTz = cursor.getString(3); // current event timezone
2749                     String tz = cursor.getString(4); // Calendar timezone
2750                     //If there's no timezone for some reason use UTC by default.
2751                     if(eTz == null) {
2752                         eTz = Time.TIMEZONE_UTC;
2753                     }
2754 
2755                     // Convert start time for all day events into the timezone of their calendar
2756                     oldTime.clear(eTz);
2757                     oldTime.set(dtstart);
2758                     newTime.clear(tz);
2759                     newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
2760                     newTime.normalize(false);
2761                     dtstart = newTime.toMillis(false /*ignoreDst*/);
2762 
2763                     // Convert end time for all day events into the timezone of their calendar
2764                     oldTime.clear(eTz);
2765                     oldTime.set(dtend);
2766                     newTime.clear(tz);
2767                     newTime.set(oldTime.monthDay, oldTime.month, oldTime.year);
2768                     newTime.normalize(false);
2769                     dtend = newTime.toMillis(false /*ignoreDst*/);
2770 
2771                     newData[0] = String.valueOf(dtstart);
2772                     newData[1] = String.valueOf(dtend);
2773                     newData[2] = tz;
2774                     newData[3] = String.valueOf(id);
2775                     db.execSQL("UPDATE Events SET " +
2776                             "dtstart2=?, " +
2777                             "dtend2=?, " +
2778                             "eventTimezone2=? " +
2779                             "WHERE _id=?",
2780                             newData);
2781                 }
2782             } finally {
2783                 cursor.close();
2784             }
2785         }
2786     }
2787 
upgradeToVersion61(SQLiteDatabase db)2788     private void upgradeToVersion61(SQLiteDatabase db) {
2789         db.execSQL("DROP TABLE IF EXISTS CalendarCache;");
2790 
2791         // IF NOT EXISTS should be normal pattern for table creation
2792         db.execSQL("CREATE TABLE IF NOT EXISTS CalendarCache (" +
2793                 "_id INTEGER PRIMARY KEY," +
2794                 "key TEXT NOT NULL," +
2795                 "value TEXT" +
2796                 ");");
2797 
2798         db.execSQL("INSERT INTO CalendarCache (" +
2799                 "key, " +
2800                 "value) VALUES (" +
2801                 "'timezoneDatabaseVersion',"  +
2802                 "'2009s'" +
2803                 ");");
2804     }
2805 
upgradeToVersion60(SQLiteDatabase db)2806     private void upgradeToVersion60(SQLiteDatabase db) {
2807         // Switch to CalendarProvider2
2808         upgradeSyncState(db);
2809         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
2810         db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " +
2811                 "BEGIN " +
2812                 ("DELETE FROM Events" +
2813                         " WHERE calendar_id=old._id;") +
2814                 "END");
2815         db.execSQL("ALTER TABLE Events" +
2816                 " ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;");
2817         db.execSQL("DROP TRIGGER IF EXISTS events_insert");
2818         // Trigger to set event's sync_account
2819         db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON Events " +
2820                 "BEGIN " +
2821                 "UPDATE Events" +
2822                 " SET _sync_account=" +
2823                 " (SELECT _sync_account FROM Calendars" +
2824                 " WHERE Calendars._id=new.calendar_id)," +
2825                 "_sync_account_type=" +
2826                 " (SELECT _sync_account_type FROM Calendars" +
2827                 " WHERE Calendars._id=new.calendar_id) " +
2828                 "WHERE Events._id=new._id;" +
2829                 "END");
2830         db.execSQL("DROP TABLE IF EXISTS DeletedEvents;");
2831         db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete");
2832         // Trigger to remove data tied to an event when we delete that event.
2833         db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON Events " +
2834                 "BEGIN " +
2835                 ("DELETE FROM Instances" +
2836                     " WHERE event_id=old._id;" +
2837                 "DELETE FROM EventsRawTimes" +
2838                     " WHERE event_id=old._id;" +
2839                 "DELETE FROM Attendees" +
2840                     " WHERE event_id=old._id;" +
2841                 "DELETE FROM Reminders" +
2842                     " WHERE event_id=old._id;" +
2843                 "DELETE FROM CalendarAlerts" +
2844                     " WHERE event_id=old._id;" +
2845                 "DELETE FROM ExtendedProperties" +
2846                     " WHERE event_id=old._id;") +
2847                 "END");
2848         db.execSQL("DROP TRIGGER IF EXISTS attendees_update");
2849         db.execSQL("DROP TRIGGER IF EXISTS attendees_insert");
2850         db.execSQL("DROP TRIGGER IF EXISTS attendees_delete");
2851         db.execSQL("DROP TRIGGER IF EXISTS reminders_update");
2852         db.execSQL("DROP TRIGGER IF EXISTS reminders_insert");
2853         db.execSQL("DROP TRIGGER IF EXISTS reminders_delete");
2854         db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update");
2855         db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert");
2856         db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete");
2857     }
2858 
upgradeToVersion59(SQLiteDatabase db)2859     private void upgradeToVersion59(SQLiteDatabase db) {
2860         db.execSQL("DROP TABLE IF EXISTS BusyBits;");
2861         db.execSQL("CREATE TEMPORARY TABLE CalendarMetaData_Backup(" +
2862                 "_id," +
2863                 "localTimezone," +
2864                 "minInstance," +
2865                 "maxInstance" +
2866                 ");");
2867         db.execSQL("INSERT INTO CalendarMetaData_Backup " +
2868                 "SELECT " +
2869                 "_id," +
2870                 "localTimezone," +
2871                 "minInstance," +
2872                 "maxInstance" +
2873                 " FROM CalendarMetaData;");
2874         db.execSQL("DROP TABLE CalendarMetaData;");
2875         createCalendarMetaDataTable59(db);
2876         db.execSQL("INSERT INTO CalendarMetaData " +
2877                 "SELECT " +
2878                 "_id," +
2879                 "localTimezone," +
2880                 "minInstance," +
2881                 "maxInstance" +
2882                 " FROM CalendarMetaData_Backup;");
2883         db.execSQL("DROP TABLE CalendarMetaData_Backup;");
2884     }
2885 
upgradeToVersion57(SQLiteDatabase db)2886     private void upgradeToVersion57(SQLiteDatabase db) {
2887         db.execSQL("ALTER TABLE Events" +
2888                 " ADD COLUMN guestsCanModify" +
2889                 " INTEGER NOT NULL DEFAULT 0;");
2890         db.execSQL("ALTER TABLE Events" +
2891                 " ADD COLUMN guestsCanInviteOthers" +
2892                 " INTEGER NOT NULL DEFAULT 1;");
2893         db.execSQL("ALTER TABLE Events" +
2894                 " ADD COLUMN guestsCanSeeGuests" +
2895                 " INTEGER NOT NULL DEFAULT 1;");
2896         db.execSQL("ALTER TABLE Events" +
2897                 " ADD COLUMN organizer" +
2898                 " STRING;");
2899         db.execSQL("UPDATE Events SET organizer=" +
2900                 "(SELECT attendeeEmail" +
2901                 " FROM Attendees"  +
2902                 " WHERE " +
2903                 "Attendees.event_id=" +
2904                 "Events._id" +
2905                 " AND " +
2906                 "Attendees.attendeeRelationship=2);");
2907     }
2908 
upgradeToVersion56(SQLiteDatabase db)2909     private void upgradeToVersion56(SQLiteDatabase db) {
2910         db.execSQL("ALTER TABLE Calendars" +
2911                 " ADD COLUMN ownerAccount TEXT;");
2912         db.execSQL("ALTER TABLE Events" +
2913                 " ADD COLUMN hasAttendeeData INTEGER NOT NULL DEFAULT 0;");
2914 
2915         // Clear _sync_dirty to avoid a client-to-server sync that could blow away
2916         // server attendees.
2917         // Clear _sync_version to pull down the server's event (with attendees)
2918         // Change the URLs from full-selfattendance to full
2919         db.execSQL("UPDATE Events"
2920                 + " SET _sync_dirty=0, "
2921                 + "_sync_version=NULL, "
2922                 + "_sync_id="
2923                 + "REPLACE(_sync_id, " +
2924                     "'/private/full-selfattendance', '/private/full'),"
2925                 + "commentsUri="
2926                 + "REPLACE(commentsUri, " +
2927                     "'/private/full-selfattendance', '/private/full');");
2928 
2929         db.execSQL("UPDATE Calendars"
2930                 + " SET url="
2931                 + "REPLACE(url, '/private/full-selfattendance', '/private/full');");
2932 
2933         // "cursor" iterates over all the calendars
2934         Cursor cursor = db.rawQuery("SELECT _id, " +
2935                 "url FROM Calendars",
2936                 null /* selection args */);
2937         // Add the owner column.
2938         if (cursor != null) {
2939             try {
2940                 final String updateSql = "UPDATE Calendars" +
2941                         " SET ownerAccount=?" +
2942                         " WHERE _id=?";
2943                 while (cursor.moveToNext()) {
2944                     Long id = cursor.getLong(0);
2945                     String url = cursor.getString(1);
2946                     String owner = calendarEmailAddressFromFeedUrl(url);
2947                     db.execSQL(updateSql, new Object[] {owner, id});
2948                 }
2949             } finally {
2950                 cursor.close();
2951             }
2952         }
2953     }
2954 
upgradeResync(SQLiteDatabase db)2955     private void upgradeResync(SQLiteDatabase db) {
2956         // Delete sync state, so all records will be re-synced.
2957         db.execSQL("DELETE FROM _sync_state;");
2958 
2959         // "cursor" iterates over all the calendars
2960         Cursor cursor = db.rawQuery("SELECT _sync_account," +
2961                 "_sync_account_type,url FROM Calendars",
2962                 null /* selection args */);
2963         if (cursor != null) {
2964             try {
2965                 while (cursor.moveToNext()) {
2966                     String accountName = cursor.getString(0);
2967                     String accountType = cursor.getString(1);
2968                     final Account account = new Account(accountName, accountType);
2969                     String calendarUrl = cursor.getString(2);
2970                     scheduleSync(account, false /* two-way sync */, calendarUrl);
2971                 }
2972             } finally {
2973                 cursor.close();
2974             }
2975         }
2976     }
2977 
upgradeToVersion55(SQLiteDatabase db)2978     private void upgradeToVersion55(SQLiteDatabase db) {
2979         db.execSQL("ALTER TABLE Calendars ADD COLUMN " +
2980                 "_sync_account_type TEXT;");
2981         db.execSQL("ALTER TABLE Events ADD COLUMN " +
2982                 "_sync_account_type TEXT;");
2983         db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;");
2984         db.execSQL("UPDATE Calendars"
2985                 + " SET _sync_account_type='com.google'"
2986                 + " WHERE _sync_account IS NOT NULL");
2987         db.execSQL("UPDATE Events"
2988                 + " SET _sync_account_type='com.google'"
2989                 + " WHERE _sync_account IS NOT NULL");
2990         db.execSQL("UPDATE DeletedEvents"
2991                 + " SET _sync_account_type='com.google'"
2992                 + " WHERE _sync_account IS NOT NULL");
2993         Log.w(TAG, "re-creating eventSyncAccountAndIdIndex");
2994         db.execSQL("DROP INDEX eventSyncAccountAndIdIndex");
2995         db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events ("
2996                 + "_sync_account_type, "
2997                 + "_sync_account, "
2998                 + "_sync_id);");
2999     }
3000 
upgradeToVersion54(SQLiteDatabase db)3001     private void upgradeToVersion54(SQLiteDatabase db) {
3002         Log.w(TAG, "adding eventSyncAccountAndIdIndex");
3003         db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events ("
3004                 + "_sync_account, _sync_id);");
3005     }
3006 
upgradeToVersion53(SQLiteDatabase db)3007     private void upgradeToVersion53(SQLiteDatabase db) {
3008         Log.w(TAG, "Upgrading CalendarAlerts table");
3009         db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " +
3010                 "creationTime INTEGER NOT NULL DEFAULT 0;");
3011         db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " +
3012                 "receivedTime INTEGER NOT NULL DEFAULT 0;");
3013         db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " +
3014                 "notifyTime INTEGER NOT NULL DEFAULT 0;");
3015     }
3016 
upgradeToVersion52(SQLiteDatabase db)3017     private void upgradeToVersion52(SQLiteDatabase db) {
3018         // We added "originalAllDay" to the Events table to keep track of
3019         // the allDay status of the original recurring event for entries
3020         // that are exceptions to that recurring event.  We need this so
3021         // that we can format the date correctly for the "originalInstanceTime"
3022         // column when we make a change to the recurrence exception and
3023         // send it to the server.
3024         db.execSQL("ALTER TABLE Events ADD COLUMN " +
3025                 "originalAllDay INTEGER;");
3026 
3027         // Iterate through the Events table and for each recurrence
3028         // exception, fill in the correct value for "originalAllDay",
3029         // if possible.  The only times where this might not be possible
3030         // are (1) the original recurring event no longer exists, or
3031         // (2) the original recurring event does not yet have a _sync_id
3032         // because it was created on the phone and hasn't been synced to the
3033         // server yet.  In both cases the originalAllDay field will be set
3034         // to null.  In the first case we don't care because the recurrence
3035         // exception will not be displayed and we won't be able to make
3036         // any changes to it (and even if we did, the server should ignore
3037         // them, right?).  In the second case, the calendar client already
3038         // disallows making changes to an instance of a recurring event
3039         // until the recurring event has been synced to the server so the
3040         // second case should never occur.
3041 
3042         // "cursor" iterates over all the recurrences exceptions.
3043         Cursor cursor = db.rawQuery("SELECT _id," +
3044                 "originalEvent" +
3045                 " FROM Events" +
3046                 " WHERE originalEvent IS NOT NULL",
3047                 null /* selection args */);
3048         if (cursor != null) {
3049             try {
3050                 while (cursor.moveToNext()) {
3051                     long id = cursor.getLong(0);
3052                     String originalEvent = cursor.getString(1);
3053 
3054                     // Find the original recurring event (if it exists)
3055                     Cursor recur = db.rawQuery("SELECT allDay" +
3056                             " FROM Events" +
3057                             " WHERE _sync_id=?",
3058                             new String[] {originalEvent});
3059                     if (recur == null) {
3060                         continue;
3061                     }
3062 
3063                     try {
3064                         // Fill in the "originalAllDay" field of the
3065                         // recurrence exception with the "allDay" value
3066                         // from the recurring event.
3067                         if (recur.moveToNext()) {
3068                             int allDay = recur.getInt(0);
3069                             db.execSQL("UPDATE Events" +
3070                                     " SET originalAllDay=" + allDay +
3071                                     " WHERE _id="+id);
3072                         }
3073                     } finally {
3074                         recur.close();
3075                     }
3076                 }
3077             } finally {
3078                 cursor.close();
3079             }
3080         }
3081     }
3082 
upgradeToVersion51(SQLiteDatabase db)3083     private void upgradeToVersion51(SQLiteDatabase db) {
3084         Log.w(TAG, "Upgrading DeletedEvents table");
3085 
3086         // We don't have enough information to fill in the correct
3087         // value of the calendar_id for old rows in the DeletedEvents
3088         // table, but rows in that table are transient so it is unlikely
3089         // that there are any rows.  Plus, the calendar_id is used only
3090         // when deleting a calendar, which is a rare event.  All new rows
3091         // will have the correct calendar_id.
3092         db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;");
3093 
3094         // Trigger to remove a calendar's events when we delete the calendar
3095         db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup");
3096         db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " +
3097                 "BEGIN " +
3098                 "DELETE FROM Events WHERE calendar_id=" +
3099                     "old._id;" +
3100                 "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" +
3101                 "END");
3102         db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted");
3103     }
3104 
dropTables(SQLiteDatabase db)3105     private void dropTables(SQLiteDatabase db) {
3106         Log.i(TAG, "Clearing database");
3107 
3108         String[] columns = {
3109                 "type", "name"
3110         };
3111         Cursor cursor = db.query("sqlite_master", columns, null, null, null, null, null);
3112         if (cursor == null) {
3113             return;
3114         }
3115         try {
3116             while (cursor.moveToNext()) {
3117                 final String name = cursor.getString(1);
3118                 if (!name.startsWith("sqlite_")) {
3119                     // If it's not a SQL-controlled entity, drop it
3120                     final String sql = "DROP " + cursor.getString(0) + " IF EXISTS " + name;
3121                     try {
3122                         db.execSQL(sql);
3123                     } catch (SQLException e) {
3124                         Log.e(TAG, "Error executing " + sql + " " + e.toString());
3125                     }
3126                 }
3127             }
3128         } finally {
3129             cursor.close();
3130         }
3131     }
3132 
3133     @Override
getWritableDatabase()3134     public synchronized SQLiteDatabase getWritableDatabase() {
3135         SQLiteDatabase db = super.getWritableDatabase();
3136         return db;
3137     }
3138 
getSyncState()3139     public SyncStateContentProviderHelper getSyncState() {
3140         return mSyncState;
3141     }
3142 
3143     /**
3144      * Schedule a calendar sync for the account.
3145      * @param account the account for which to schedule a sync
3146      * @param uploadChangesOnly if set, specify that the sync should only send
3147      *   up local changes.  This is typically used for a local sync, a user override of
3148      *   too many deletions, or a sync after a calendar is unselected.
3149      * @param url the url feed for the calendar to sync (may be null, in which case a poll of
3150      *   all feeds is done.)
3151      */
scheduleSync(Account account, boolean uploadChangesOnly, String url)3152     void scheduleSync(Account account, boolean uploadChangesOnly, String url) {
3153         Bundle extras = new Bundle();
3154         if (uploadChangesOnly) {
3155             extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly);
3156         }
3157         if (url != null) {
3158             extras.putString("feed", url);
3159         }
3160         ContentResolver.requestSync(account, Calendars.CONTENT_URI.getAuthority(),
3161                 extras);
3162     }
3163 
createEventsView(SQLiteDatabase db)3164     private static void createEventsView(SQLiteDatabase db) {
3165         db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";");
3166         String eventsSelect = "SELECT "
3167                 + Tables.EVENTS + "." + CalendarContract.Events._ID
3168                         + " AS " + CalendarContract.Events._ID + ","
3169                 + CalendarContract.Events.TITLE + ","
3170                 + CalendarContract.Events.DESCRIPTION + ","
3171                 + CalendarContract.Events.EVENT_LOCATION + ","
3172                 + CalendarContract.Events.EVENT_COLOR + ","
3173                 + CalendarContract.Events.EVENT_COLOR_KEY + ","
3174                 + CalendarContract.Events.STATUS + ","
3175                 + CalendarContract.Events.SELF_ATTENDEE_STATUS + ","
3176                 + CalendarContract.Events.DTSTART + ","
3177                 + CalendarContract.Events.DTEND + ","
3178                 + CalendarContract.Events.DURATION + ","
3179                 + CalendarContract.Events.EVENT_TIMEZONE + ","
3180                 + CalendarContract.Events.EVENT_END_TIMEZONE + ","
3181                 + CalendarContract.Events.ALL_DAY + ","
3182                 + CalendarContract.Events.ACCESS_LEVEL + ","
3183                 + CalendarContract.Events.AVAILABILITY + ","
3184                 + CalendarContract.Events.HAS_ALARM + ","
3185                 + CalendarContract.Events.HAS_EXTENDED_PROPERTIES + ","
3186                 + CalendarContract.Events.RRULE + ","
3187                 + CalendarContract.Events.RDATE + ","
3188                 + CalendarContract.Events.EXRULE + ","
3189                 + CalendarContract.Events.EXDATE + ","
3190                 + CalendarContract.Events.ORIGINAL_SYNC_ID + ","
3191                 + CalendarContract.Events.ORIGINAL_ID + ","
3192                 + CalendarContract.Events.ORIGINAL_INSTANCE_TIME + ","
3193                 + CalendarContract.Events.ORIGINAL_ALL_DAY + ","
3194                 + CalendarContract.Events.LAST_DATE + ","
3195                 + CalendarContract.Events.HAS_ATTENDEE_DATA + ","
3196                 + CalendarContract.Events.CALENDAR_ID + ","
3197                 + CalendarContract.Events.GUESTS_CAN_INVITE_OTHERS + ","
3198                 + CalendarContract.Events.GUESTS_CAN_MODIFY + ","
3199                 + CalendarContract.Events.GUESTS_CAN_SEE_GUESTS + ","
3200                 + CalendarContract.Events.ORGANIZER + ","
3201                 + "COALESCE("
3202                 + Events.IS_ORGANIZER + ", " + Events.ORGANIZER + " = " + Calendars.OWNER_ACCOUNT
3203                 + ") AS " + Events.IS_ORGANIZER + ","
3204                 + CalendarContract.Events.CUSTOM_APP_PACKAGE + ","
3205                 + CalendarContract.Events.CUSTOM_APP_URI + ","
3206                 + CalendarContract.Events.UID_2445 + ","
3207                 + CalendarContract.Events.SYNC_DATA1 + ","
3208                 + CalendarContract.Events.SYNC_DATA2 + ","
3209                 + CalendarContract.Events.SYNC_DATA3 + ","
3210                 + CalendarContract.Events.SYNC_DATA4 + ","
3211                 + CalendarContract.Events.SYNC_DATA5 + ","
3212                 + CalendarContract.Events.SYNC_DATA6 + ","
3213                 + CalendarContract.Events.SYNC_DATA7 + ","
3214                 + CalendarContract.Events.SYNC_DATA8 + ","
3215                 + CalendarContract.Events.SYNC_DATA9 + ","
3216                 + CalendarContract.Events.SYNC_DATA10 + ","
3217                 + Tables.EVENTS + "." + CalendarContract.Events.DELETED
3218                 + " AS " + CalendarContract.Events.DELETED + ","
3219                 + Tables.EVENTS + "." + CalendarContract.Events._SYNC_ID
3220                 + " AS " + CalendarContract.Events._SYNC_ID + ","
3221                 + Tables.EVENTS + "." + CalendarContract.Events.DIRTY
3222                 + " AS " + CalendarContract.Events.DIRTY + ","
3223                 + Tables.EVENTS + "." + Events.MUTATORS
3224                 + " AS " + Events.MUTATORS + ","
3225                 + CalendarContract.Events.LAST_SYNCED + ","
3226                 + Tables.CALENDARS + "." + Calendars.ACCOUNT_NAME
3227                 + " AS " + CalendarContract.Events.ACCOUNT_NAME + ","
3228                 + Tables.CALENDARS + "." + Calendars.ACCOUNT_TYPE
3229                 + " AS " + CalendarContract.Events.ACCOUNT_TYPE + ","
3230                 + Calendars.CALENDAR_TIME_ZONE + ","
3231                 + Calendars.CALENDAR_DISPLAY_NAME + ","
3232                 + Calendars.CALENDAR_LOCATION + ","
3233                 + Calendars.VISIBLE + ","
3234                 + Calendars.CALENDAR_COLOR + ","
3235                 + Calendars.CALENDAR_COLOR_KEY + ","
3236                 + Calendars.CALENDAR_ACCESS_LEVEL + ","
3237                 + Calendars.MAX_REMINDERS + ","
3238                 + Calendars.ALLOWED_REMINDERS + ","
3239                 + Calendars.ALLOWED_ATTENDEE_TYPES + ","
3240                 + Calendars.ALLOWED_AVAILABILITY + ","
3241                 + Calendars.CAN_ORGANIZER_RESPOND + ","
3242                 + Calendars.CAN_MODIFY_TIME_ZONE + ","
3243                 + Calendars.CAN_PARTIALLY_UPDATE + ","
3244                 + Calendars.IS_PRIMARY + ","
3245                 + Calendars.CAL_SYNC1 + ","
3246                 + Calendars.CAL_SYNC2 + ","
3247                 + Calendars.CAL_SYNC3 + ","
3248                 + Calendars.CAL_SYNC4 + ","
3249                 + Calendars.CAL_SYNC5 + ","
3250                 + Calendars.CAL_SYNC6 + ","
3251                 + Calendars.CAL_SYNC7 + ","
3252                 + Calendars.CAL_SYNC8 + ","
3253                 + Calendars.CAL_SYNC9 + ","
3254                 + Calendars.CAL_SYNC10 + ","
3255                 + Calendars.OWNER_ACCOUNT + ","
3256                 + Calendars.SYNC_EVENTS  + ","
3257                 + "ifnull(" + Events.EVENT_COLOR + "," + Calendars.CALENDAR_COLOR + ") AS "
3258                 + Events.DISPLAY_COLOR
3259                 + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS
3260                 + " ON (" + Tables.EVENTS + "." + Events.CALENDAR_ID
3261                 + "=" + Tables.CALENDARS + "." + Calendars._ID
3262                 + ")";
3263 
3264         db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect);
3265     }
3266 
3267     /**
3268      * Extracts the calendar email from a calendar feed url.
3269      * @param feed the calendar feed url
3270      * @return the calendar email that is in the feed url or null if it can't
3271      * find the email address.
3272      * TODO: this is duplicated in CalendarSyncAdapter; move to a library
3273      */
calendarEmailAddressFromFeedUrl(String feed)3274     public static String calendarEmailAddressFromFeedUrl(String feed) {
3275         // Example feed url:
3276         // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees
3277         String[] pathComponents = feed.split("/");
3278         if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) {
3279             try {
3280                 return URLDecoder.decode(pathComponents[5], "UTF-8");
3281             } catch (UnsupportedEncodingException e) {
3282                 Log.e(TAG, "unable to url decode the email address in calendar " + feed);
3283                 return null;
3284             }
3285         }
3286 
3287         Log.e(TAG, "unable to find the email address in calendar " + feed);
3288         return null;
3289     }
3290 
3291     /**
3292      * Get a "allcalendars" url from a "private/full" or "private/free-busy" url
3293      * @param url
3294      * @return the rewritten Url
3295      *
3296      * For example:
3297      *
3298      *      http://www.google.com/calendar/feeds/joe%40joe.com/private/full
3299      *      http://www.google.com/calendar/feeds/joe%40joe.com/private/free-busy
3300      *
3301      * will be rewriten into:
3302      *
3303      *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
3304      *      http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com
3305      */
getAllCalendarsUrlFromEventsUrl(String url)3306     private static String getAllCalendarsUrlFromEventsUrl(String url) {
3307         if (url == null) {
3308             if (Log.isLoggable(TAG, Log.DEBUG)) {
3309                 Log.d(TAG, "Cannot get AllCalendars url from a NULL url");
3310             }
3311             return null;
3312         }
3313         if (url.contains("/private/full")) {
3314             return url.replace("/private/full", "").
3315                     replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
3316         }
3317         if (url.contains("/private/free-busy")) {
3318             return url.replace("/private/free-busy", "").
3319                     replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full");
3320         }
3321         // Just log as we dont recognize the provided Url
3322         if (Log.isLoggable(TAG, Log.DEBUG)) {
3323             Log.d(TAG, "Cannot get AllCalendars url from the following url: " + url);
3324         }
3325         return null;
3326     }
3327 
3328     /**
3329      * Get "selfUrl" from "events url"
3330      * @param url the Events url (either "private/full" or "private/free-busy"
3331      * @return the corresponding allcalendar url
3332      */
getSelfUrlFromEventsUrl(String url)3333     private static String getSelfUrlFromEventsUrl(String url) {
3334         return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
3335     }
3336 
3337     /**
3338      * Get "editUrl" from "events url"
3339      * @param url the Events url (either "private/full" or "private/free-busy"
3340      * @return the corresponding allcalendar url
3341      */
getEditUrlFromEventsUrl(String url)3342     private static String getEditUrlFromEventsUrl(String url) {
3343         return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url));
3344     }
3345 
3346     /**
3347      * Rewrite the url from "http" to "https" scheme
3348      * @param url the url to rewrite
3349      * @return the rewritten URL
3350      */
rewriteUrlFromHttpToHttps(String url)3351     private static String rewriteUrlFromHttpToHttps(String url) {
3352         if (url == null) {
3353             if (Log.isLoggable(TAG, Log.DEBUG)) {
3354                 Log.d(TAG, "Cannot rewrite a NULL url");
3355             }
3356             return null;
3357         }
3358         if (url.startsWith(SCHEMA_HTTPS)) {
3359             return url;
3360         }
3361         if (!url.startsWith(SCHEMA_HTTP)) {
3362             throw new IllegalArgumentException("invalid url parameter, unknown scheme: " + url);
3363         }
3364         return SCHEMA_HTTPS + url.substring(SCHEMA_HTTP.length());
3365     }
3366 
3367     /**
3368      * Duplicates an event and its associated tables (Attendees, Reminders, ExtendedProperties).
3369      * <p>
3370      * Does not create a duplicate if the Calendar's "canPartiallyUpdate" is 0 or the Event's
3371      * "dirty" is 1 (so we don't create more than one duplicate).
3372      *
3373      * @param id The _id of the event to duplicate.
3374      */
duplicateEvent(final long id)3375     protected void duplicateEvent(final long id) {
3376         final SQLiteDatabase db = getWritableDatabase();
3377         final long canPartiallyUpdate = DatabaseUtils.longForQuery(db, "SELECT "
3378                 + Calendars.CAN_PARTIALLY_UPDATE + " FROM " + Views.EVENTS
3379                 + " WHERE " + Events._ID + " = ?", new String[]{
3380                 String.valueOf(id)
3381         });
3382         if (canPartiallyUpdate == 0) {
3383             return;
3384         }
3385 
3386         db.execSQL("INSERT INTO " + CalendarDatabaseHelper.Tables.EVENTS
3387                 + "  (" + LAST_SYNCED_EVENT_COLUMNS + ","
3388                 +         Events.DIRTY + "," + Events.LAST_SYNCED + ")"
3389                 + " SELECT " + LAST_SYNCED_EVENT_COLUMNS + ", 0, 1"
3390                 + " FROM " + Tables.EVENTS
3391                 + " WHERE "  + Events._ID + " = ? AND " + Events.DIRTY + " = ?",
3392                 new Object[]{
3393                         id,
3394                         0, // Events.DIRTY
3395                 });
3396         final long newId = DatabaseUtils.longForQuery(
3397                 db, "SELECT CASE changes() WHEN 0 THEN -1 ELSE last_insert_rowid() END", null);
3398         if (newId < 0) {
3399             return;
3400         }
3401 
3402         if (Log.isLoggable(TAG, Log.VERBOSE)) {
3403             Log.v(TAG, "Duplicating event " + id + " into new event " + newId);
3404         }
3405 
3406         copyEventRelatedTables(db, newId, id);
3407     }
3408 
3409     /**
3410      * Makes a copy of the Attendees, Reminders, and ExtendedProperties rows associated with
3411      * a specific event.
3412      *
3413      * @param db The database.
3414      * @param newId The ID of the new event.
3415      * @param id The ID of the old event.
3416      */
copyEventRelatedTables(SQLiteDatabase db, long newId, long id)3417     static void copyEventRelatedTables(SQLiteDatabase db, long newId, long id) {
3418         db.execSQL("INSERT INTO " + Tables.REMINDERS
3419                 + " ( "  + CalendarContract.Reminders.EVENT_ID + ", "
3420                         + LAST_SYNCED_REMINDER_COLUMNS + ") "
3421                 + "SELECT ?," + LAST_SYNCED_REMINDER_COLUMNS
3422                 + " FROM " + Tables.REMINDERS
3423                 + " WHERE " + CalendarContract.Reminders.EVENT_ID + " = ?",
3424                 new Object[] {newId, id});
3425         db.execSQL("INSERT INTO "
3426                 + Tables.ATTENDEES
3427                 + " (" + CalendarContract.Attendees.EVENT_ID + ","
3428                         + LAST_SYNCED_ATTENDEE_COLUMNS + ") "
3429                 + "SELECT ?," + LAST_SYNCED_ATTENDEE_COLUMNS + " FROM " + Tables.ATTENDEES
3430                 + " WHERE " + CalendarContract.Attendees.EVENT_ID + " = ?",
3431                 new Object[] {newId, id});
3432         db.execSQL("INSERT INTO " + Tables.EXTENDED_PROPERTIES
3433                 + " (" + CalendarContract.ExtendedProperties.EVENT_ID + ","
3434                 + LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS + ") "
3435                 + "SELECT ?, " + LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS
3436                 + " FROM " + Tables.EXTENDED_PROPERTIES
3437                 + " WHERE " + CalendarContract.ExtendedProperties.EVENT_ID + " = ?",
3438                 new Object[]{newId, id});
3439     }
3440 
removeDuplicateEvent(final long id)3441     protected void removeDuplicateEvent(final long id) {
3442         final SQLiteDatabase db = getWritableDatabase();
3443         final Cursor cursor = db.rawQuery("SELECT " + Events._ID + " FROM " + Tables.EVENTS
3444                 + " WHERE " + Events._SYNC_ID
3445                 + " = (SELECT " + Events._SYNC_ID
3446                 + " FROM " + Tables.EVENTS
3447                 + " WHERE " + Events._ID + " = ?) "
3448                 + "AND " + Events.LAST_SYNCED + " = ?",
3449                 new String[]{
3450                         String.valueOf(id),
3451                         "1", // Events.LAST_SYNCED
3452                 });
3453         try {
3454             // there should only be at most one but this can't hurt
3455             if (cursor.moveToNext()) {
3456                 final long dupId = cursor.getLong(0);
3457 
3458                 if (Log.isLoggable(TAG, Log.VERBOSE)) {
3459                     Log.v(TAG, "Removing duplicate event " + dupId + " of original event " + id);
3460                 }
3461                 // triggers will clean up related tables.
3462                 db.execSQL("DELETE FROM Events WHERE " + Events._ID + " = ?", new Object[]{dupId});
3463             }
3464         } finally {
3465           cursor.close();
3466         }
3467     }
3468 }
3469