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