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