CalendarDatabaseHelper.java revision bedfbe0065e48c652814bd3b864dabf7589f344b
1/* 2 * Copyright (C) 2009 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License 15 */ 16 17package com.android.providers.calendar; 18 19import com.google.common.annotations.VisibleForTesting; 20 21import com.android.internal.content.SyncStateContentProviderHelper; 22 23import android.accounts.Account; 24import android.content.ContentResolver; 25import android.content.ContentValues; 26import android.content.Context; 27import android.content.res.Resources; 28import android.database.Cursor; 29import android.database.DatabaseUtils; 30import android.database.sqlite.SQLiteDatabase; 31import android.database.sqlite.SQLiteException; 32import android.database.sqlite.SQLiteOpenHelper; 33import android.os.Bundle; 34import android.provider.Calendar; 35import android.provider.ContactsContract; 36import android.provider.SyncStateContract; 37import android.text.TextUtils; 38import android.text.format.Time; 39import android.util.Log; 40 41import java.io.UnsupportedEncodingException; 42import java.net.URLDecoder; 43 44/** 45 * Database helper for calendar. Designed as a singleton to make sure that all 46 * {@link android.content.ContentProvider} users get the same reference. 47 */ 48/* package */ class CalendarDatabaseHelper extends SQLiteOpenHelper { 49 private static final String TAG = "CalendarDatabaseHelper"; 50 51 private static final String DATABASE_NAME = "calendar.db"; 52 53 private static final int DAY_IN_SECONDS = 24 * 60 * 60; 54 55 // Note: if you update the version number, you must also update the code 56 // in upgradeDatabase() to modify the database (gracefully, if possible). 57 static final int DATABASE_VERSION = 71; 58 59 private static final int PRE_FROYO_SYNC_STATE_VERSION = 3; 60 61 public interface Tables { 62 public static final String CALENDARS = "Calendars"; 63 public static final String EVENTS = "Events"; 64 public static final String EVENTS_RAW_TIMES = "EventsRawTimes"; 65 public static final String INSTANCES = "Instances"; 66 public static final String ATTENDEES = "Attendees"; 67 public static final String REMINDERS = "Reminders"; 68 public static final String CALENDAR_ALERTS = "CalendarAlerts"; 69 public static final String EXTENDED_PROPERTIES = "ExtendedProperties"; 70 public static final String CALENDAR_META_DATA = "CalendarMetaData"; 71 public static final String CALENDAR_CACHE = "CalendarCache"; 72 public static final String SYNC_STATE = "_sync_state"; 73 public static final String SYNC_STATE_META = "_sync_state_metadata"; 74 } 75 76 public interface Views { 77 public static final String EVENTS = "view_events"; 78 } 79 80 // Copied from SyncStateContentProviderHelper. Don't really want to make them public there. 81 private static final String SYNC_STATE_META_VERSION_COLUMN = "version"; 82 83 private static final String AFTER_EVENT_INSERT_SQL = 84 "UPDATE " + Tables.EVENTS + 85 " SET " + Calendar.Events._SYNC_ACCOUNT + "=" + 86 " (SELECT " + Calendar.Calendars._SYNC_ACCOUNT + " FROM " + Tables.CALENDARS + 87 " WHERE " + Tables.CALENDARS + "." + Calendar.Calendars._ID + 88 "=new." + Calendar.Events.CALENDAR_ID + ")," + 89 Calendar.SyncColumns._SYNC_ACCOUNT_TYPE + "=" + 90 " (SELECT " + Calendar.Calendars._SYNC_ACCOUNT_TYPE + " FROM " + Tables.CALENDARS + 91 " WHERE " + Tables.CALENDARS + "." + Calendar.Calendars._ID + 92 "=new." + Calendar.Events.CALENDAR_ID + ") " + 93 "WHERE " + Tables.EVENTS + "." + Calendar.Events._ID + 94 "=new." + Calendar.Events._ID + ";"; 95 96 // This needs to be done when all the tables are already created 97 private static final String EVENTS_CLEANUP_TRIGGER_SQL = 98 "DELETE FROM " + Tables.INSTANCES + 99 " WHERE "+ Calendar.Instances.EVENT_ID + "=" + 100 "old." + Calendar.Events._ID + ";" + 101 "DELETE FROM " + Tables.EVENTS_RAW_TIMES + 102 " WHERE " + Calendar.EventsRawTimes.EVENT_ID + "=" + 103 "old." + Calendar.Events._ID + ";" + 104 "DELETE FROM " + Tables.ATTENDEES + 105 " WHERE " + Calendar.Attendees.EVENT_ID + "=" + 106 "old." + Calendar.Events._ID + ";" + 107 "DELETE FROM " + Tables.REMINDERS + 108 " WHERE " + Calendar.Reminders.EVENT_ID + "=" + 109 "old." + Calendar.Events._ID + ";" + 110 "DELETE FROM " + Tables.CALENDAR_ALERTS + 111 " WHERE " + Calendar.CalendarAlerts.EVENT_ID + "=" + 112 "old." + Calendar.Events._ID + ";" + 113 "DELETE FROM " + Tables.EXTENDED_PROPERTIES + 114 " WHERE " + Calendar.ExtendedProperties.EVENT_ID + "=" + 115 "old." + Calendar.Events._ID + ";"; 116 117 private static final String CALENDAR_CLEANUP_TRIGGER_SQL = "DELETE FROM " + Tables.EVENTS + 118 " WHERE " + Calendar.Events.CALENDAR_ID + "=" + 119 "old." + Calendar.Events._ID + ";"; 120 121 private final Context mContext; 122 private final SyncStateContentProviderHelper mSyncState; 123 124 private static CalendarDatabaseHelper sSingleton = null; 125 126 private DatabaseUtils.InsertHelper mCalendarsInserter; 127 private DatabaseUtils.InsertHelper mEventsInserter; 128 private DatabaseUtils.InsertHelper mEventsRawTimesInserter; 129 private DatabaseUtils.InsertHelper mInstancesInserter; 130 private DatabaseUtils.InsertHelper mAttendeesInserter; 131 private DatabaseUtils.InsertHelper mRemindersInserter; 132 private DatabaseUtils.InsertHelper mCalendarAlertsInserter; 133 private DatabaseUtils.InsertHelper mExtendedPropertiesInserter; 134 135 public long calendarsInsert(ContentValues values) { 136 return mCalendarsInserter.insert(values); 137 } 138 139 public long eventsInsert(ContentValues values) { 140 return mEventsInserter.insert(values); 141 } 142 143 public long eventsRawTimesInsert(ContentValues values) { 144 return mEventsRawTimesInserter.insert(values); 145 } 146 147 public long eventsRawTimesReplace(ContentValues values) { 148 return mEventsRawTimesInserter.replace(values); 149 } 150 151 public long instancesInsert(ContentValues values) { 152 return mInstancesInserter.insert(values); 153 } 154 155 public long instancesReplace(ContentValues values) { 156 return mInstancesInserter.replace(values); 157 } 158 159 public long attendeesInsert(ContentValues values) { 160 return mAttendeesInserter.insert(values); 161 } 162 163 public long remindersInsert(ContentValues values) { 164 return mRemindersInserter.insert(values); 165 } 166 167 public long calendarAlertsInsert(ContentValues values) { 168 return mCalendarAlertsInserter.insert(values); 169 } 170 171 public long extendedPropertiesInsert(ContentValues values) { 172 return mExtendedPropertiesInserter.insert(values); 173 } 174 175 public static synchronized CalendarDatabaseHelper getInstance(Context context) { 176 if (sSingleton == null) { 177 sSingleton = new CalendarDatabaseHelper(context); 178 } 179 return sSingleton; 180 } 181 182 /** 183 * Private constructor, callers except unit tests should obtain an instance through 184 * {@link #getInstance(android.content.Context)} instead. 185 */ 186 /* package */ CalendarDatabaseHelper(Context context) { 187 super(context, DATABASE_NAME, null, DATABASE_VERSION); 188 if (false) Log.i(TAG, "Creating OpenHelper"); 189 Resources resources = context.getResources(); 190 191 mContext = context; 192 mSyncState = new SyncStateContentProviderHelper(); 193 } 194 195 @Override 196 public void onOpen(SQLiteDatabase db) { 197 mSyncState.onDatabaseOpened(db); 198 199 mCalendarsInserter = new DatabaseUtils.InsertHelper(db, Tables.CALENDARS); 200 mEventsInserter = new DatabaseUtils.InsertHelper(db, Tables.EVENTS); 201 mEventsRawTimesInserter = new DatabaseUtils.InsertHelper(db, Tables.EVENTS_RAW_TIMES); 202 mInstancesInserter = new DatabaseUtils.InsertHelper(db, Tables.INSTANCES); 203 mAttendeesInserter = new DatabaseUtils.InsertHelper(db, Tables.ATTENDEES); 204 mRemindersInserter = new DatabaseUtils.InsertHelper(db, Tables.REMINDERS); 205 mCalendarAlertsInserter = new DatabaseUtils.InsertHelper(db, Tables.CALENDAR_ALERTS); 206 mExtendedPropertiesInserter = 207 new DatabaseUtils.InsertHelper(db, Tables.EXTENDED_PROPERTIES); 208 } 209 210 /* 211 * Upgrade sync state table if necessary. Note that the data bundle 212 * in the table is not upgraded. 213 * 214 * The sync state used to be stored with version 3, but now uses the 215 * same sync state code as contacts, which is version 1. This code 216 * upgrades from 3 to 1 if necessary. (Yes, the numbers are unfortunately 217 * backwards.) 218 * 219 * This code is only called when upgrading from an old calendar version, 220 * so there is no problem if sync state version 3 gets used again in the 221 * future. 222 */ 223 private void upgradeSyncState(SQLiteDatabase db) { 224 long version = DatabaseUtils.longForQuery(db, 225 "SELECT " + SYNC_STATE_META_VERSION_COLUMN 226 + " FROM " + Tables.SYNC_STATE_META, 227 null); 228 if (version == PRE_FROYO_SYNC_STATE_VERSION) { 229 Log.i(TAG, "Upgrading calendar sync state table"); 230 db.execSQL("CREATE TEMPORARY TABLE state_backup(_sync_account TEXT, " 231 + "_sync_account_type TEXT, data TEXT);"); 232 db.execSQL("INSERT INTO state_backup SELECT _sync_account, _sync_account_type, data" 233 + " FROM " 234 + Tables.SYNC_STATE 235 + " WHERE _sync_account is not NULL and _sync_account_type is not NULL;"); 236 db.execSQL("DROP TABLE " + Tables.SYNC_STATE + ";"); 237 mSyncState.onDatabaseOpened(db); 238 db.execSQL("INSERT INTO " + Tables.SYNC_STATE + "(" 239 + SyncStateContract.Columns.ACCOUNT_NAME + "," 240 + SyncStateContract.Columns.ACCOUNT_TYPE + "," 241 + SyncStateContract.Columns.DATA 242 + ") SELECT _sync_account, _sync_account_type, data from state_backup;"); 243 db.execSQL("DROP TABLE state_backup;"); 244 } else { 245 // Wrong version to upgrade. 246 // Don't need to do anything more here because mSyncState.onDatabaseOpened() will blow 247 // away and recreate the database (which will result in a resync). 248 Log.w(TAG, "upgradeSyncState: current version is " + version + ", skipping upgrade."); 249 } 250 } 251 252 @Override 253 public void onCreate(SQLiteDatabase db) { 254 bootstrapDB(db); 255 } 256 257 private void bootstrapDB(SQLiteDatabase db) { 258 Log.i(TAG, "Bootstrapping database"); 259 260 mSyncState.createDatabase(db); 261 262 db.execSQL("CREATE TABLE " + Tables.CALENDARS + " (" + 263 Calendar.Calendars._ID + " INTEGER PRIMARY KEY," + 264 Calendar.Calendars._SYNC_ACCOUNT + " TEXT," + 265 Calendar.Calendars._SYNC_ACCOUNT_TYPE + " TEXT," + 266 Calendar.Calendars._SYNC_ID + " TEXT," + 267 Calendar.Calendars._SYNC_VERSION + " TEXT," + 268 Calendar.Calendars._SYNC_TIME + " TEXT," + // UTC 269 Calendar.Calendars._SYNC_DATA + " INTEGER," + 270 Calendar.Calendars._SYNC_DIRTY + " INTEGER," + 271 Calendar.Calendars._SYNC_MARK + " INTEGER," + // Used to filter out new rows 272 Calendar.Calendars.URL + " TEXT," + 273 Calendar.Calendars.NAME + " TEXT," + 274 Calendar.Calendars.DISPLAY_NAME + " TEXT," + 275 Calendar.Calendars.HIDDEN + " INTEGER NOT NULL DEFAULT 0," + 276 Calendar.Calendars.COLOR + " INTEGER," + 277 Calendar.Calendars.ACCESS_LEVEL + " INTEGER," + 278 Calendar.Calendars.SELECTED + " INTEGER NOT NULL DEFAULT 1," + 279 Calendar.Calendars.SYNC_EVENTS + " INTEGER NOT NULL DEFAULT 0," + 280 Calendar.Calendars.LOCATION + " TEXT," + 281 Calendar.Calendars.TIMEZONE + " TEXT," + 282 Calendar.Calendars.OWNER_ACCOUNT + " TEXT, " + 283 Calendar.Calendars.ORGANIZER_CAN_RESPOND + " INTEGER NOT NULL DEFAULT 1," + 284 Calendar.Calendars.DELETED + " INTEGER NOT NULL DEFAULT 0," + 285 Calendar.Calendars.SELF_URL + " TEXT," + 286 Calendar.Calendars.EDIT_URL + " TEXT," + 287 Calendar.Calendars.EVENTS_URL + " TEXT" + 288 ");"); 289 290 db.execSQL("CREATE INDEX calendarsUrlIndex ON " + Tables.CALENDARS + "(" 291 + Calendar.Calendars.URL + 292 ");"); 293 294 // Trigger to remove a calendar's events when we delete the calendar 295 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " + 296 "BEGIN " + 297 CALENDAR_CLEANUP_TRIGGER_SQL + 298 "END"); 299 300 // TODO: do we need both dtend and duration? 301 db.execSQL("CREATE TABLE " + Tables.EVENTS + " (" + 302 Calendar.Events._ID + " INTEGER PRIMARY KEY," + 303 Calendar.Events._SYNC_ACCOUNT + " TEXT," + 304 Calendar.Events._SYNC_ACCOUNT_TYPE + " TEXT," + 305 Calendar.Events._SYNC_ID + " TEXT," + 306 Calendar.Events._SYNC_VERSION + " TEXT," + 307 // sync time in UTC 308 Calendar.Events._SYNC_TIME + " TEXT," + 309 Calendar.Events._SYNC_DATA + " INTEGER," + 310 Calendar.Events._SYNC_DIRTY + " INTEGER," + 311 // sync mark to filter out new rows 312 Calendar.Events._SYNC_MARK + " INTEGER," + 313 Calendar.Events.CALENDAR_ID + " INTEGER NOT NULL," + 314 Calendar.Events.HTML_URI + " TEXT," + 315 Calendar.Events.TITLE + " TEXT," + 316 Calendar.Events.EVENT_LOCATION + " TEXT," + 317 Calendar.Events.DESCRIPTION + " TEXT," + 318 Calendar.Events.STATUS + " INTEGER," + 319 Calendar.Events.SELF_ATTENDEE_STATUS + " INTEGER NOT NULL DEFAULT 0," + 320 Calendar.Events.COMMENTS_URI + " TEXT," + 321 // dtstart in millis since epoch 322 Calendar.Events.DTSTART + " INTEGER," + 323 // dtend in millis since epoch 324 Calendar.Events.DTEND + " INTEGER," + 325 // timezone for event 326 Calendar.Events.EVENT_TIMEZONE + " TEXT," + 327 Calendar.Events.DURATION + " TEXT," + 328 Calendar.Events.ALL_DAY + " INTEGER NOT NULL DEFAULT 0," + 329 Calendar.Events.VISIBILITY + " INTEGER NOT NULL DEFAULT 0," + 330 Calendar.Events.TRANSPARENCY + " INTEGER NOT NULL DEFAULT 0," + 331 Calendar.Events.HAS_ALARM + " INTEGER NOT NULL DEFAULT 0," + 332 Calendar.Events.HAS_EXTENDED_PROPERTIES + " INTEGER NOT NULL DEFAULT 0," + 333 Calendar.Events.RRULE + " TEXT," + 334 Calendar.Events.RDATE + " TEXT," + 335 Calendar.Events.EXRULE + " TEXT," + 336 Calendar.Events.EXDATE + " TEXT," + 337 // originalEvent is the _sync_id of recurring event 338 Calendar.Events.ORIGINAL_EVENT + " TEXT," + 339 // originalInstanceTime is in millis since epoch 340 Calendar.Events.ORIGINAL_INSTANCE_TIME + " INTEGER," + 341 Calendar.Events.ORIGINAL_ALL_DAY + " INTEGER," + 342 // lastDate is in millis since epoch 343 Calendar.Events.LAST_DATE + " INTEGER," + 344 Calendar.Events.HAS_ATTENDEE_DATA + " INTEGER NOT NULL DEFAULT 0," + 345 Calendar.Events.GUESTS_CAN_MODIFY + " INTEGER NOT NULL DEFAULT 0," + 346 Calendar.Events.GUESTS_CAN_INVITE_OTHERS + " INTEGER NOT NULL DEFAULT 1," + 347 Calendar.Events.GUESTS_CAN_SEE_GUESTS + " INTEGER NOT NULL DEFAULT 1," + 348 Calendar.Events.ORGANIZER + " STRING," + 349 Calendar.Events.DELETED + " INTEGER NOT NULL DEFAULT 0," + 350 // dstart2 is in millis since epoch, allDay events are in local timezone 351 Calendar.Events.DTSTART2 + " INTEGER," + 352 // dtend2 is in millis since epoch, allDay events are in local timezone 353 Calendar.Events.DTEND2 + " INTEGER," + 354 // timezone for event with allDay events are in local timezone 355 Calendar.Events.EVENT_TIMEZONE2 + " TEXT," + 356 // syncAdapterData is available for use by sync adapters 357 Calendar.Events.SYNC_ADAPTER_DATA + " TEXT" + 358 ");"); 359 360 // Trigger to set event's sync_account 361 db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON " + Tables.EVENTS + " " + 362 "BEGIN " + 363 AFTER_EVENT_INSERT_SQL + 364 "END"); 365 366 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON " + Tables.EVENTS + " (" + 367 Calendar.Events._SYNC_ACCOUNT_TYPE + ", " + 368 Calendar.Events._SYNC_ACCOUNT + ", " + 369 Calendar.Events._SYNC_ID + 370 ");"); 371 372 db.execSQL("CREATE INDEX eventsCalendarIdIndex ON " + Tables.EVENTS + " (" + 373 Calendar.Events.CALENDAR_ID + 374 ");"); 375 376 db.execSQL("CREATE TABLE " + Tables.EVENTS_RAW_TIMES + " (" + 377 Calendar.EventsRawTimes._ID + " INTEGER PRIMARY KEY," + 378 Calendar.EventsRawTimes.EVENT_ID + " INTEGER NOT NULL," + 379 Calendar.EventsRawTimes.DTSTART_2445 + " TEXT," + 380 Calendar.EventsRawTimes.DTEND_2445 + " TEXT," + 381 Calendar.EventsRawTimes.ORIGINAL_INSTANCE_TIME_2445 + " TEXT," + 382 Calendar.EventsRawTimes.LAST_DATE_2445 + " TEXT," + 383 "UNIQUE (" + Calendar.EventsRawTimes.EVENT_ID + ")" + 384 ");"); 385 386 db.execSQL("CREATE TABLE " + Tables.INSTANCES + " (" + 387 Calendar.Instances._ID + " INTEGER PRIMARY KEY," + 388 Calendar.Instances.EVENT_ID + " INTEGER," + 389 Calendar.Instances.BEGIN + " INTEGER," + // UTC millis 390 Calendar.Instances.END + " INTEGER," + // UTC millis 391 Calendar.Instances.START_DAY + " INTEGER," + // Julian start day 392 Calendar.Instances.END_DAY + " INTEGER," + // Julian end day 393 Calendar.Instances.START_MINUTE + " INTEGER," + // minutes from midnight 394 Calendar.Instances.END_MINUTE + " INTEGER," + // minutes from midnight 395 "UNIQUE (" + 396 Calendar.Instances.EVENT_ID + ", " + 397 Calendar.Instances.BEGIN + ", " + 398 Calendar.Instances.END + ")" + 399 ");"); 400 401 db.execSQL("CREATE INDEX instancesStartDayIndex ON " + Tables.INSTANCES + " (" + 402 Calendar.Instances.START_DAY + 403 ");"); 404 405 createCalendarMetaDataTable(db); 406 407 createCalendarCacheTable(db); 408 409 db.execSQL("CREATE TABLE " + Tables.ATTENDEES + " (" + 410 Calendar.Attendees._ID + " INTEGER PRIMARY KEY," + 411 Calendar.Attendees.EVENT_ID + " INTEGER," + 412 Calendar.Attendees.ATTENDEE_NAME + " TEXT," + 413 Calendar.Attendees.ATTENDEE_EMAIL + " TEXT," + 414 Calendar.Attendees.ATTENDEE_STATUS + " INTEGER," + 415 Calendar.Attendees.ATTENDEE_RELATIONSHIP + " INTEGER," + 416 Calendar.Attendees.ATTENDEE_TYPE + " INTEGER" + 417 ");"); 418 419 db.execSQL("CREATE INDEX attendeesEventIdIndex ON " + Tables.ATTENDEES + " (" + 420 Calendar.Attendees.EVENT_ID + 421 ");"); 422 423 db.execSQL("CREATE TABLE " + Tables.REMINDERS + " (" + 424 Calendar.Reminders._ID + " INTEGER PRIMARY KEY," + 425 Calendar.Reminders.EVENT_ID + " INTEGER," + 426 Calendar.Reminders.MINUTES + " INTEGER," + 427 Calendar.Reminders.METHOD + " INTEGER NOT NULL" + 428 " DEFAULT " + Calendar.Reminders.METHOD_DEFAULT + 429 ");"); 430 431 db.execSQL("CREATE INDEX remindersEventIdIndex ON " + Tables.REMINDERS + " (" + 432 Calendar.Reminders.EVENT_ID + 433 ");"); 434 435 // This table stores the Calendar notifications that have gone off. 436 db.execSQL("CREATE TABLE " + Tables.CALENDAR_ALERTS + " (" + 437 Calendar.CalendarAlerts._ID + " INTEGER PRIMARY KEY," + 438 Calendar.CalendarAlerts.EVENT_ID + " INTEGER," + 439 Calendar.CalendarAlerts.BEGIN + " INTEGER NOT NULL," + // UTC millis 440 Calendar.CalendarAlerts.END + " INTEGER NOT NULL," + // UTC millis 441 Calendar.CalendarAlerts.ALARM_TIME + " INTEGER NOT NULL," + // UTC millis 442 Calendar.CalendarAlerts.CREATION_TIME + " INTEGER NOT NULL," + // UTC millis 443 Calendar.CalendarAlerts.RECEIVED_TIME + " INTEGER NOT NULL," + // UTC millis 444 Calendar.CalendarAlerts.NOTIFY_TIME + " INTEGER NOT NULL," + // UTC millis 445 Calendar.CalendarAlerts.STATE + " INTEGER NOT NULL," + 446 Calendar.CalendarAlerts.MINUTES + " INTEGER," + 447 "UNIQUE (" + 448 Calendar.CalendarAlerts.ALARM_TIME + ", " + 449 Calendar.CalendarAlerts.BEGIN + ", " + 450 Calendar.CalendarAlerts.EVENT_ID + ")" + 451 ");"); 452 453 db.execSQL("CREATE INDEX calendarAlertsEventIdIndex ON " + Tables.CALENDAR_ALERTS + " (" + 454 Calendar.CalendarAlerts.EVENT_ID + 455 ");"); 456 457 db.execSQL("CREATE TABLE " + Tables.EXTENDED_PROPERTIES + " (" + 458 Calendar.ExtendedProperties._ID + " INTEGER PRIMARY KEY," + 459 Calendar.ExtendedProperties.EVENT_ID + " INTEGER," + 460 Calendar.ExtendedProperties.NAME + " TEXT," + 461 Calendar.ExtendedProperties.VALUE + " TEXT" + 462 ");"); 463 464 db.execSQL("CREATE INDEX extendedPropertiesEventIdIndex ON " + Tables.EXTENDED_PROPERTIES 465 + " (" + 466 Calendar.ExtendedProperties.EVENT_ID + 467 ");"); 468 469 createEventsView(db); 470 471 // Trigger to remove data tied to an event when we delete that event. 472 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " + 473 "BEGIN " + 474 EVENTS_CLEANUP_TRIGGER_SQL + 475 "END"); 476 477 ContentResolver.requestSync(null /* all accounts */, 478 ContactsContract.AUTHORITY, new Bundle()); 479 } 480 481 private void createCalendarMetaDataTable(SQLiteDatabase db) { 482 db.execSQL("CREATE TABLE " + Tables.CALENDAR_META_DATA + " (" + 483 Calendar.CalendarMetaData._ID + " INTEGER PRIMARY KEY," + 484 Calendar.CalendarMetaData.LOCAL_TIMEZONE + " TEXT," + 485 Calendar.CalendarMetaData.MIN_INSTANCE + " INTEGER," + // UTC millis 486 Calendar.CalendarMetaData.MAX_INSTANCE + " INTEGER" + // UTC millis 487 ");"); 488 } 489 490 private void createCalendarCacheTable(SQLiteDatabase db) { 491 // This is a hack because versioning skipped version number 61 of schema 492 // TODO after version 70 this can be removed 493 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";"); 494 495 // IF NOT EXISTS should be normal pattern for table creation 496 db.execSQL("CREATE TABLE IF NOT EXISTS " + Tables.CALENDAR_CACHE + " (" + 497 CalendarCache.COLUMN_NAME_ID + " INTEGER PRIMARY KEY," + 498 CalendarCache.COLUMN_NAME_KEY + " TEXT NOT NULL," + 499 CalendarCache.COLUMN_NAME_VALUE + " TEXT" + 500 ");"); 501 502 db.execSQL("INSERT INTO CalendarCache (key, value) VALUES (" + 503 "'" + CalendarCache.KEY_TIMEZONE_DATABASE_VERSION + "'," + 504 "'" + CalendarCache.DEFAULT_TIMEZONE_DATABASE_VERSION + "'" + 505 ");"); 506 } 507 508 @Override 509 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 510 Log.i(TAG, "Upgrading DB from version " + oldVersion 511 + " to " + newVersion); 512 if (oldVersion < 49) { 513 dropTables(db); 514 mSyncState.createDatabase(db); 515 return; // this was lossy 516 } 517 518 // From schema versions 59 to version 66, the CalendarMetaData table definition had lost 519 // the primary key leading to having the CalendarMetaData with multiple rows instead of 520 // only one. The Instance table was then corrupted (during Instance expansion we are using 521 // the localTimezone, minInstance and maxInstance from CalendarMetaData table. 522 // This boolean helps us tracking the need to recreate the CalendarMetaData table and 523 // clear the Instance table (and thus force an Instance expansion). 524 boolean recreateMetaDataAndInstances = (oldVersion >= 59 && oldVersion <= 66); 525 526 try { 527 if (oldVersion < 51) { 528 upgradeToVersion51(db); // From 50 or 51 529 oldVersion = 51; 530 } 531 if (oldVersion == 51) { 532 upgradeToVersion52(db); 533 oldVersion += 1; 534 } 535 if (oldVersion == 52) { 536 upgradeToVersion53(db); 537 oldVersion += 1; 538 } 539 if (oldVersion == 53) { 540 upgradeToVersion54(db); 541 oldVersion += 1; 542 } 543 if (oldVersion == 54) { 544 upgradeToVersion55(db); 545 oldVersion += 1; 546 } 547 if (oldVersion == 55 || oldVersion == 56) { 548 // Both require resync, so just schedule it once 549 upgradeResync(db); 550 } 551 if (oldVersion == 55) { 552 upgradeToVersion56(db); 553 oldVersion += 1; 554 } 555 if (oldVersion == 56) { 556 upgradeToVersion57(db); 557 oldVersion += 1; 558 } 559 if (oldVersion == 57) { 560 // Changes are undone upgrading to 60, so don't do anything. 561 oldVersion += 1; 562 } 563 if (oldVersion == 58) { 564 upgradeToVersion59(db); 565 oldVersion += 1; 566 } 567 if (oldVersion == 59) { 568 upgradeToVersion60(db); 569 oldVersion += 1; 570 } 571 if (oldVersion == 60) { 572 upgradeToVersion61(db); 573 oldVersion += 1; 574 } 575 if (oldVersion == 61) { 576 upgradeToVersion62(db); 577 oldVersion += 1; 578 } 579 if (oldVersion == 62) { 580 upgradeToVersion63(db); 581 oldVersion += 1; 582 } 583 if (oldVersion == 63) { 584 upgradeToVersion64(db); 585 oldVersion += 1; 586 } 587 if (oldVersion == 64) { 588 upgradeToVersion65(db); 589 oldVersion += 1; 590 } 591 if (oldVersion == 65) { 592 upgradeToVersion66(db); 593 oldVersion += 1; 594 } 595 if (oldVersion == 66) { 596 // Changes are done thru recreateMetaDataAndInstances() method 597 oldVersion += 1; 598 } 599 if (recreateMetaDataAndInstances) { 600 recreateMetaDataAndInstances(db); 601 } 602 if (oldVersion == 67 || oldVersion == 68) { 603 upgradeToVersion69(db); 604 oldVersion = 69; 605 } 606 if (oldVersion == 69) { 607 upgradeToVersion70(db); 608 oldVersion += 1; 609 } 610 if (oldVersion == 70) { 611 upgradeToVersion71(db); 612 oldVersion += 1; 613 } 614 } catch (SQLiteException e) { 615 Log.e(TAG, "onUpgrade: SQLiteException, recreating db. " + e); 616 dropTables(db); 617 bootstrapDB(db); 618 return; // this was lossy 619 } 620 } 621 622 /** 623 * If the user_version of the database if between 59 and 66 (those versions has been deployed 624 * with no primary key for the CalendarMetaData table) 625 */ 626 private void recreateMetaDataAndInstances(SQLiteDatabase db) { 627 // Recreate the CalendarMetaData table with correct primary key 628 db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + ";"); 629 createCalendarMetaDataTable(db); 630 631 // Also clean the Instance table as this table may be corrupted 632 db.execSQL("DELETE FROM " + Tables.INSTANCES + ";"); 633 } 634 635 private static boolean fixAllDayTime(Time time, String timezone, Long timeInMillis) { 636 time.set(timeInMillis); 637 if(time.hour != 0 || time.minute != 0 || time.second != 0) { 638 time.hour = 0; 639 time.minute = 0; 640 time.second = 0; 641 return true; 642 } 643 return false; 644 } 645 646 @VisibleForTesting 647 void upgradeToVersion71(SQLiteDatabase db) { 648 // Recreate the Events Views as we are deprecating Calendars "url" column 649 createEventsView(db); 650 } 651 652 @VisibleForTesting 653 void upgradeToVersion70(SQLiteDatabase db) { 654 // Add deleted column 655 db.execSQL("ALTER TABLE " + Tables.CALENDARS +" ADD COLUMN " + 656 Calendar.Calendars.DELETED + " INTEGER NOT NULL DEFAULT 0;"); 657 658 // Add selfUrl, editUrl and eventsUrl 659 // Population of the new columns will be done by the SyncAdapter itself 660 db.execSQL("ALTER TABLE " + Tables.CALENDARS + " ADD COLUMN " + 661 Calendar.Calendars.SELF_URL + " TEXT"); 662 db.execSQL("ALTER TABLE " + Tables.CALENDARS + " ADD COLUMN " + 663 Calendar.Calendars.EDIT_URL + " TEXT"); 664 db.execSQL("ALTER TABLE " + Tables.CALENDARS + " ADD COLUMN " + 665 Calendar.Calendars.EVENTS_URL + " TEXT"); 666 667 // Create index on Url column 668 db.execSQL("CREATE INDEX calendarsUrlIndex ON " + Tables.CALENDARS + " (" + 669 Calendar.Calendars.URL + 670 ");"); 671 672 // Recreate the Events Views as column "deleted" is now ambiguous 673 // ("deleted" is now defined in both Calendars and Events tables) 674 createEventsView(db); 675 } 676 677 @VisibleForTesting 678 static void upgradeToVersion69(SQLiteDatabase db) { 679 // Clean up allDay events which could be in an invalid state from an earlier version 680 // Some allDay events had hour, min, sec not set to zero, which throws elsewhere. This 681 // will go through the allDay events and make sure they have proper values and are in the 682 // correct timezone. Verifies that dtstart and dtend are in UTC and at midnight, that 683 // eventTimezone is set to UTC, tries to make sure duration is in days, and that dtstart2 684 // and dtend2 are at midnight in their timezone. 685 final String sql = "SELECT " + Calendar.Events._ID + ", " + 686 Calendar.Events.DTSTART + ", " + 687 Calendar.Events.DTEND + ", " + 688 Calendar.Events.DURATION + ", " + 689 Calendar.Events.DTSTART2 + ", " + 690 Calendar.Events.DTEND2 + ", " + 691 Calendar.Events.EVENT_TIMEZONE + ", " + 692 Calendar.Events.EVENT_TIMEZONE2 + ", " + 693 Calendar.Events.RRULE + " " + 694 "FROM " + Tables.EVENTS + " " + 695 "WHERE " + Calendar.Events.ALL_DAY + "=?"; 696 Cursor cursor = db.rawQuery(sql, new String[] {"1"}); 697 if (cursor != null) { 698 try { 699 String timezone; 700 String timezone2; 701 String duration; 702 Long dtstart; 703 Long dtstart2; 704 Long dtend; 705 Long dtend2; 706 Time time = new Time(); 707 Long id; 708 // some things need to be in utc so we call this frequently, cache to make faster 709 final String utc = Time.TIMEZONE_UTC; 710 while (cursor.moveToNext()) { 711 String rrule = cursor.getString(8); 712 id = cursor.getLong(0); 713 dtstart = cursor.getLong(1); 714 dtstart2 = null; 715 timezone = cursor.getString(6); 716 timezone2 = cursor.getString(7); 717 duration = cursor.getString(3); 718 719 if (TextUtils.isEmpty(rrule)) { 720 // For non-recurring events dtstart and dtend should both have values 721 // and duration should be null. 722 dtend = cursor.getLong(2); 723 dtend2 = null; 724 // Since we made all three of these at the same time if timezone2 exists 725 // so should dtstart2 and dtend2. 726 if(!TextUtils.isEmpty(timezone2)) { 727 dtstart2 = cursor.getLong(4); 728 dtend2 = cursor.getLong(5); 729 } 730 731 boolean update = false; 732 if (!TextUtils.equals(timezone, utc)) { 733 update = true; 734 timezone = utc; 735 } 736 737 time.clear(timezone); 738 update |= fixAllDayTime(time, timezone, dtstart); 739 dtstart = time.normalize(false); 740 741 time.clear(timezone); 742 update |= fixAllDayTime(time, timezone, dtend); 743 dtend = time.normalize(false); 744 745 if (dtstart2 != null) { 746 time.clear(timezone2); 747 update |= fixAllDayTime(time, timezone2, dtstart2); 748 dtstart2 = time.normalize(false); 749 } 750 751 if (dtend2 != null) { 752 time.clear(timezone2); 753 update |= fixAllDayTime(time, timezone2, dtend2); 754 dtend2 = time.normalize(false); 755 } 756 757 if (!TextUtils.isEmpty(duration)) { 758 update = true; 759 } 760 761 if (update) { 762 // enforce duration being null 763 db.execSQL("UPDATE " + Tables.EVENTS + " SET " + 764 Calendar.Events.DTSTART + "=?, " + 765 Calendar.Events.DTEND + "=?, " + 766 Calendar.Events.DTSTART2 + "=?, " + 767 Calendar.Events.DTEND2 + "=?, " + 768 Calendar.Events.DURATION + "=?, " + 769 Calendar.Events.EVENT_TIMEZONE + "=?, " + 770 Calendar.Events.EVENT_TIMEZONE2 + "=? " + 771 "WHERE " + Calendar.Events._ID + "=?", 772 new Object[] { 773 dtstart, 774 dtend, 775 dtstart2, 776 dtend2, 777 null, 778 timezone, 779 timezone2, 780 id} 781 ); 782 } 783 784 } else { 785 // For recurring events only dtstart and duration should be used. 786 // We ignore dtend since it will be overwritten if the event changes to a 787 // non-recurring event and won't be used otherwise. 788 if(!TextUtils.isEmpty(timezone2)) { 789 dtstart2 = cursor.getLong(4); 790 } 791 792 boolean update = false; 793 if (!TextUtils.equals(timezone, utc)) { 794 update = true; 795 timezone = utc; 796 } 797 798 time.clear(timezone); 799 update |= fixAllDayTime(time, timezone, dtstart); 800 dtstart = time.normalize(false); 801 802 if (dtstart2 != null) { 803 time.clear(timezone2); 804 update |= fixAllDayTime(time, timezone2, dtstart2); 805 dtstart2 = time.normalize(false); 806 } 807 808 if (TextUtils.isEmpty(duration)) { 809 // If duration was missing assume a 1 day duration 810 duration = "P1D"; 811 update = true; 812 } else { 813 int len = duration.length(); 814 // TODO fix durations in other formats as well 815 if (duration.charAt(0) == 'P' && 816 duration.charAt(len - 1) == 'S') { 817 int seconds = Integer.parseInt(duration.substring(1, len - 1)); 818 int days = (seconds + DAY_IN_SECONDS - 1) / DAY_IN_SECONDS; 819 duration = "P" + days + "D"; 820 update = true; 821 } 822 } 823 824 if (update) { 825 // If there were other problems also enforce dtend being null 826 db.execSQL("UPDATE " + Tables.EVENTS + " SET " + 827 Calendar.Events.DTSTART + "=?, " + 828 Calendar.Events.DTEND + "=?, " + 829 Calendar.Events.DTSTART2 + "=?, " + 830 Calendar.Events.DTEND2 + "=?, " + 831 Calendar.Events.DURATION + "=?," + 832 Calendar.Events.EVENT_TIMEZONE + "=?, " + 833 Calendar.Events.EVENT_TIMEZONE2 + "=? " + 834 "WHERE " + Calendar.Events._ID + "=?", 835 new Object[] { 836 dtstart, 837 null, 838 dtstart2, 839 null, 840 duration, 841 timezone, 842 timezone2, 843 id} 844 ); 845 } 846 } 847 } 848 } finally { 849 cursor.close(); 850 } 851 } 852 } 853 854 private void upgradeToVersion66(SQLiteDatabase db) { 855 // Add a column to indicate whether the event organizer can respond to his own events 856 // The UI should not show attendee status for events in calendars with this column = 0 857 db.execSQL("ALTER TABLE " + Tables.CALENDARS + 858 " ADD COLUMN " + Calendar.Calendars.ORGANIZER_CAN_RESPOND + 859 " INTEGER NOT NULL DEFAULT 1;"); 860 } 861 862 private void upgradeToVersion65(SQLiteDatabase db) { 863 // we need to recreate the Events view 864 createEventsView(db); 865 } 866 867 private void upgradeToVersion64(SQLiteDatabase db) { 868 // Add a column that may be used by sync adapters 869 db.execSQL("ALTER TABLE " + Tables.EVENTS + 870 " ADD COLUMN " + Calendar.Events.SYNC_ADAPTER_DATA + " TEXT;"); 871 } 872 873 private void upgradeToVersion63(SQLiteDatabase db) { 874 // we need to recreate the Events view 875 createEventsView(db); 876 } 877 878 private void upgradeToVersion62(SQLiteDatabase db) { 879 // New columns are to transition to having allDay events in the local timezone 880 db.execSQL("ALTER TABLE " + Tables.EVENTS + 881 " ADD COLUMN " + Calendar.Events.DTSTART2 + " INTEGER;"); 882 db.execSQL("ALTER TABLE " + Tables.EVENTS + 883 " ADD COLUMN " + Calendar.Events.DTEND2 + " INTEGER;"); 884 db.execSQL("ALTER TABLE " + Tables.EVENTS + 885 " ADD COLUMN " + Calendar.Events.EVENT_TIMEZONE2 + " TEXT;"); 886 887 String[] allDayBit = new String[] {"0"}; 888 // Copy over all the data that isn't an all day event. 889 db.execSQL("UPDATE " + Tables.EVENTS + " SET " + 890 Calendar.Events.DTSTART2 + "=" + Calendar.Events.DTSTART + "," + 891 Calendar.Events.DTEND2 + "=" + Calendar.Events.DTEND + "," + 892 Calendar.Events.EVENT_TIMEZONE2 + "=" + Calendar.Events.EVENT_TIMEZONE + " " + 893 "WHERE " + Calendar.Events.ALL_DAY + "=?;", 894 allDayBit /* selection args */); 895 896 // "cursor" iterates over all the calendars 897 allDayBit[0] = "1"; 898 Cursor cursor = db.rawQuery("SELECT " + Tables.EVENTS + "." + Calendar.Events._ID + "," + 899 Calendar.Events.DTSTART + "," + 900 Calendar.Events.DTEND + "," + 901 Calendar.Events.EVENT_TIMEZONE + "," + 902 Calendar.Calendars.TIMEZONE + " " + 903 "FROM " + Tables.EVENTS + " INNER JOIN " + Tables.CALENDARS + " " + 904 "WHERE " + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID + "=" + 905 Tables.CALENDARS + "." + Calendar.Calendars._ID + 906 " AND " 907 + Calendar.Events.ALL_DAY + "=?", 908 allDayBit /* selection args */); 909 910 Time oldTime = new Time(); 911 Time newTime = new Time(); 912 // Update the allday events in the new columns 913 if (cursor != null) { 914 try { 915 String[] newData = new String[4]; 916 cursor.moveToPosition(-1); 917 while (cursor.moveToNext()) { 918 long id = cursor.getLong(0); // Order from query above 919 long dtstart = cursor.getLong(1); 920 long dtend = cursor.getLong(2); 921 String eTz = cursor.getString(3); // current event timezone 922 String tz = cursor.getString(4); // Calendar timezone 923 //If there's no timezone for some reason use UTC by default. 924 if(eTz == null) { 925 eTz = Time.TIMEZONE_UTC; 926 } 927 928 // Convert start time for all day events into the timezone of their calendar 929 oldTime.clear(eTz); 930 oldTime.set(dtstart); 931 newTime.clear(tz); 932 newTime.set(oldTime.monthDay, oldTime.month, oldTime.year); 933 newTime.normalize(false); 934 dtstart = newTime.toMillis(false /*ignoreDst*/); 935 936 // Convert end time for all day events into the timezone of their calendar 937 oldTime.clear(eTz); 938 oldTime.set(dtend); 939 newTime.clear(tz); 940 newTime.set(oldTime.monthDay, oldTime.month, oldTime.year); 941 newTime.normalize(false); 942 dtend = newTime.toMillis(false /*ignoreDst*/); 943 944 newData[0] = String.valueOf(dtstart); 945 newData[1] = String.valueOf(dtend); 946 newData[2] = tz; 947 newData[3] = String.valueOf(id); 948 db.execSQL("UPDATE " + Tables.EVENTS + " SET " + 949 Calendar.Events.DTSTART2 + "=?, " + 950 Calendar.Events.DTEND2 + "=?, " + 951 Calendar.Events.EVENT_TIMEZONE2 + "=? " + 952 "WHERE " + Calendar.Events._ID + "=?", 953 newData); 954 } 955 } finally { 956 cursor.close(); 957 } 958 } 959 } 960 961 private void upgradeToVersion61(SQLiteDatabase db) { 962 createCalendarCacheTable(db); 963 } 964 965 private void upgradeToVersion60(SQLiteDatabase db) { 966 // Switch to CalendarProvider2 967 upgradeSyncState(db); 968 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 969 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " + 970 "BEGIN " + 971 CALENDAR_CLEANUP_TRIGGER_SQL + 972 "END"); 973 db.execSQL("ALTER TABLE " + Tables.EVENTS + 974 " ADD COLUMN " + Calendar.Events.DELETED + " INTEGER NOT NULL DEFAULT 0;"); 975 db.execSQL("DROP TRIGGER IF EXISTS events_insert"); 976 // Trigger to set event's sync_account 977 db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON " + Tables.EVENTS + " " + 978 "BEGIN " + 979 AFTER_EVENT_INSERT_SQL + 980 "END"); 981 db.execSQL("DROP TABLE IF EXISTS DeletedEvents;"); 982 db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete"); 983 // Trigger to remove data tied to an event when we delete that event. 984 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " + 985 "BEGIN " + 986 EVENTS_CLEANUP_TRIGGER_SQL + 987 "END"); 988 db.execSQL("DROP TRIGGER IF EXISTS attendees_update"); 989 db.execSQL("DROP TRIGGER IF EXISTS attendees_insert"); 990 db.execSQL("DROP TRIGGER IF EXISTS attendees_delete"); 991 db.execSQL("DROP TRIGGER IF EXISTS reminders_update"); 992 db.execSQL("DROP TRIGGER IF EXISTS reminders_insert"); 993 db.execSQL("DROP TRIGGER IF EXISTS reminders_delete"); 994 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update"); 995 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert"); 996 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete"); 997 998 createEventsView(db); 999 } 1000 1001 private void upgradeToVersion59(SQLiteDatabase db) { 1002 db.execSQL("DROP TABLE IF EXISTS BusyBits;"); 1003 db.execSQL("CREATE TEMPORARY TABLE " + Tables.CALENDAR_META_DATA + "_Backup" + "(" + 1004 Calendar.CalendarMetaData._ID + "," + 1005 Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," + 1006 Calendar.CalendarMetaData.MIN_INSTANCE + "," + 1007 Calendar.CalendarMetaData.MAX_INSTANCE + 1008 ");"); 1009 db.execSQL("INSERT INTO " + Tables.CALENDAR_META_DATA + "_Backup " + 1010 "SELECT " + 1011 Calendar.CalendarMetaData._ID + "," + 1012 Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," + 1013 Calendar.CalendarMetaData.MIN_INSTANCE + "," + 1014 Calendar.CalendarMetaData.MAX_INSTANCE + 1015 " FROM " + Tables.CALENDAR_META_DATA + ";"); 1016 db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + ";"); 1017 createCalendarMetaDataTable(db); 1018 db.execSQL("INSERT INTO " + Tables.CALENDAR_META_DATA + " " + 1019 "SELECT " + 1020 Calendar.CalendarMetaData._ID + "," + 1021 Calendar.CalendarMetaData.LOCAL_TIMEZONE + "," + 1022 Calendar.CalendarMetaData.MIN_INSTANCE + "," + 1023 Calendar.CalendarMetaData.MAX_INSTANCE + 1024 " FROM " + Tables.CALENDAR_META_DATA + "_Backup;"); 1025 db.execSQL("DROP TABLE " + Tables.CALENDAR_META_DATA + "_Backup;"); 1026 } 1027 1028 private void upgradeToVersion57(SQLiteDatabase db) { 1029 db.execSQL("ALTER TABLE " + Tables.EVENTS + 1030 " ADD COLUMN " + Calendar.Events.GUESTS_CAN_MODIFY + 1031 " INTEGER NOT NULL DEFAULT 0;"); 1032 db.execSQL("ALTER TABLE " + Tables.EVENTS + 1033 " ADD COLUMN " + Calendar.Events.GUESTS_CAN_INVITE_OTHERS + 1034 " INTEGER NOT NULL DEFAULT 1;"); 1035 db.execSQL("ALTER TABLE " + Tables.EVENTS + 1036 " ADD COLUMN " + Calendar.Events.GUESTS_CAN_SEE_GUESTS + 1037 " INTEGER NOT NULL DEFAULT 1;"); 1038 db.execSQL("ALTER TABLE " + Tables.EVENTS + 1039 " ADD COLUMN " + Calendar.Events.ORGANIZER + 1040 " STRING;"); 1041 db.execSQL("UPDATE " + Tables.EVENTS + " SET " + Calendar.Events.ORGANIZER + "=" + 1042 "(SELECT " + Calendar.Attendees.ATTENDEE_EMAIL + 1043 " FROM " + Tables.ATTENDEES + "" + 1044 " WHERE " + 1045 Tables.ATTENDEES + "." + Calendar.Attendees.EVENT_ID + "=" + 1046 Tables.EVENTS + "." + Calendar.Events._ID + 1047 " AND " + 1048 Tables.ATTENDEES + "." + Calendar.Attendees.ATTENDEE_RELATIONSHIP + "=2);"); 1049 } 1050 1051 private void upgradeToVersion56(SQLiteDatabase db) { 1052 db.execSQL("ALTER TABLE " + Tables.CALENDARS + 1053 " ADD COLUMN " + Calendar.Calendars.OWNER_ACCOUNT + " TEXT;"); 1054 db.execSQL("ALTER TABLE " + Tables.EVENTS + 1055 " ADD COLUMN " + Calendar.Events.HAS_ATTENDEE_DATA + " INTEGER;"); 1056 1057 // Clear _sync_dirty to avoid a client-to-server sync that could blow away 1058 // server attendees. 1059 // Clear _sync_version to pull down the server's event (with attendees) 1060 // Change the URLs from full-selfattendance to full 1061 db.execSQL("UPDATE " + Tables.EVENTS 1062 + " SET " + Calendar.Events._SYNC_DIRTY + "=0, " 1063 + Calendar.Events._SYNC_VERSION + "=NULL, " 1064 + Calendar.Events._SYNC_ID + "=" 1065 + "REPLACE(" + Calendar.Events._SYNC_ID + ", " + 1066 "'/private/full-selfattendance', '/private/full')," 1067 + Calendar.Events.COMMENTS_URI + "=" 1068 + "REPLACE(" + Calendar.Events.COMMENTS_URI + ", " + 1069 "'/private/full-selfattendance', '/private/full');"); 1070 1071 db.execSQL("UPDATE " + Tables.CALENDARS 1072 + " SET " + Calendar.Calendars.URL + "=" 1073 + "REPLACE(" + Calendar.Calendars.URL + ", " + 1074 "'/private/full-selfattendance', '/private/full');"); 1075 1076 // "cursor" iterates over all the calendars 1077 Cursor cursor = db.rawQuery("SELECT " + Calendar.Calendars._ID + ", " + 1078 Calendar.Calendars.URL + " FROM " + Tables.CALENDARS, 1079 null /* selection args */); 1080 // Add the owner column. 1081 if (cursor != null) { 1082 try { 1083 final String updateSql = "UPDATE " + Tables.CALENDARS + 1084 " SET " + Calendar.Calendars.OWNER_ACCOUNT + "=?" + 1085 " WHERE " + Calendar.Calendars._ID + "=?"; 1086 while (cursor.moveToNext()) { 1087 Long id = cursor.getLong(0); 1088 String url = cursor.getString(1); 1089 String owner = calendarEmailAddressFromFeedUrl(url); 1090 db.execSQL(updateSql, new Object[] {owner, id}); 1091 } 1092 } finally { 1093 cursor.close(); 1094 } 1095 } 1096 } 1097 1098 private void upgradeResync(SQLiteDatabase db) { 1099 // Delete sync state, so all records will be re-synced. 1100 db.execSQL("DELETE FROM " + Tables.SYNC_STATE + ";"); 1101 1102 // "cursor" iterates over all the calendars 1103 Cursor cursor = db.rawQuery("SELECT " + Calendar.Calendars._SYNC_ACCOUNT + "," + 1104 Calendar.Calendars._SYNC_ACCOUNT_TYPE + "," + 1105 Calendar.Calendars.URL 1106 + " FROM " + Tables.CALENDARS, 1107 null /* selection args */); 1108 if (cursor != null) { 1109 try { 1110 while (cursor.moveToNext()) { 1111 String accountName = cursor.getString(0); 1112 String accountType = cursor.getString(1); 1113 final Account account = new Account(accountName, accountType); 1114 String calendarUrl = cursor.getString(2); 1115 scheduleSync(account, false /* two-way sync */, calendarUrl); 1116 } 1117 } finally { 1118 cursor.close(); 1119 } 1120 } 1121 } 1122 1123 private void upgradeToVersion55(SQLiteDatabase db) { 1124 db.execSQL("ALTER TABLE " + Tables.CALENDARS + " ADD COLUMN " + 1125 Calendar.Calendars._SYNC_ACCOUNT_TYPE + " TEXT;"); 1126 db.execSQL("ALTER TABLE " + Tables.EVENTS + " ADD COLUMN " + 1127 Calendar.Events._SYNC_ACCOUNT_TYPE + " TEXT;"); 1128 db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;"); 1129 db.execSQL("UPDATE " + Tables.CALENDARS 1130 + " SET " + Calendar.Calendars._SYNC_ACCOUNT_TYPE + "='com.google'" 1131 + " WHERE " + Calendar.Calendars._SYNC_ACCOUNT + " IS NOT NULL"); 1132 db.execSQL("UPDATE " + Tables.EVENTS 1133 + " SET " + Calendar.Events._SYNC_ACCOUNT_TYPE + "='com.google'" 1134 + " WHERE " + Calendar.Events._SYNC_ACCOUNT + " IS NOT NULL"); 1135 db.execSQL("UPDATE DeletedEvents" 1136 + " SET _sync_account_type='com.google'" 1137 + " WHERE _sync_account IS NOT NULL"); 1138 Log.w(TAG, "re-creating eventSyncAccountAndIdIndex"); 1139 db.execSQL("DROP INDEX eventSyncAccountAndIdIndex"); 1140 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON " + Tables.EVENTS + " (" 1141 + Calendar.Events._SYNC_ACCOUNT_TYPE + ", " 1142 + Calendar.Events._SYNC_ACCOUNT + ", " 1143 + Calendar.Events._SYNC_ID + ");"); 1144 } 1145 1146 private void upgradeToVersion54(SQLiteDatabase db) { 1147 Log.w(TAG, "adding eventSyncAccountAndIdIndex"); 1148 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events (" 1149 + Calendar.Events._SYNC_ACCOUNT + ", " + Calendar.Events._SYNC_ID + ");"); 1150 } 1151 1152 private void upgradeToVersion53(SQLiteDatabase db) { 1153 Log.w(TAG, "Upgrading CalendarAlerts table"); 1154 db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " + 1155 Calendar.CalendarAlerts.CREATION_TIME + " INTEGER DEFAULT 0;"); 1156 db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " + 1157 Calendar.CalendarAlerts.RECEIVED_TIME + " INTEGER DEFAULT 0;"); 1158 db.execSQL("ALTER TABLE " + Tables.CALENDAR_ALERTS + " ADD COLUMN " + 1159 Calendar.CalendarAlerts.NOTIFY_TIME + " INTEGER DEFAULT 0;"); 1160 } 1161 1162 private void upgradeToVersion52(SQLiteDatabase db) { 1163 // We added "originalAllDay" to the Events table to keep track of 1164 // the allDay status of the original recurring event for entries 1165 // that are exceptions to that recurring event. We need this so 1166 // that we can format the date correctly for the "originalInstanceTime" 1167 // column when we make a change to the recurrence exception and 1168 // send it to the server. 1169 db.execSQL("ALTER TABLE " + Tables.EVENTS + " ADD COLUMN " + 1170 Calendar.Events.ORIGINAL_ALL_DAY + " INTEGER;"); 1171 1172 // Iterate through the Events table and for each recurrence 1173 // exception, fill in the correct value for "originalAllDay", 1174 // if possible. The only times where this might not be possible 1175 // are (1) the original recurring event no longer exists, or 1176 // (2) the original recurring event does not yet have a _sync_id 1177 // because it was created on the phone and hasn't been synced to the 1178 // server yet. In both cases the originalAllDay field will be set 1179 // to null. In the first case we don't care because the recurrence 1180 // exception will not be displayed and we won't be able to make 1181 // any changes to it (and even if we did, the server should ignore 1182 // them, right?). In the second case, the calendar client already 1183 // disallows making changes to an instance of a recurring event 1184 // until the recurring event has been synced to the server so the 1185 // second case should never occur. 1186 1187 // "cursor" iterates over all the recurrences exceptions. 1188 Cursor cursor = db.rawQuery("SELECT " + Calendar.Events._ID + "," + 1189 Calendar.Events.ORIGINAL_EVENT + 1190 " FROM " + Tables.EVENTS + 1191 " WHERE " + Calendar.Events.ORIGINAL_EVENT + " IS NOT NULL", 1192 null /* selection args */); 1193 if (cursor != null) { 1194 try { 1195 while (cursor.moveToNext()) { 1196 long id = cursor.getLong(0); 1197 String originalEvent = cursor.getString(1); 1198 1199 // Find the original recurring event (if it exists) 1200 Cursor recur = db.rawQuery("SELECT " + Calendar.Events.ALL_DAY + 1201 " FROM " + Tables.EVENTS + 1202 " WHERE " + Calendar.Events._SYNC_ID + "=?", 1203 new String[] {originalEvent}); 1204 if (recur == null) { 1205 continue; 1206 } 1207 1208 try { 1209 // Fill in the "originalAllDay" field of the 1210 // recurrence exception with the "allDay" value 1211 // from the recurring event. 1212 if (recur.moveToNext()) { 1213 int allDay = recur.getInt(0); 1214 db.execSQL("UPDATE " + Tables.EVENTS + 1215 " SET " + Calendar.Events.ORIGINAL_ALL_DAY + "=" + allDay + 1216 " WHERE " + Calendar.Events._ID + "="+id); 1217 } 1218 } finally { 1219 recur.close(); 1220 } 1221 } 1222 } finally { 1223 cursor.close(); 1224 } 1225 } 1226 } 1227 1228 private void upgradeToVersion51(SQLiteDatabase db) { 1229 Log.w(TAG, "Upgrading DeletedEvents table"); 1230 1231 // We don't have enough information to fill in the correct 1232 // value of the calendar_id for old rows in the DeletedEvents 1233 // table, but rows in that table are transient so it is unlikely 1234 // that there are any rows. Plus, the calendar_id is used only 1235 // when deleting a calendar, which is a rare event. All new rows 1236 // will have the correct calendar_id. 1237 db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;"); 1238 1239 // Trigger to remove a calendar's events when we delete the calendar 1240 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 1241 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON " + Tables.CALENDARS + " " + 1242 "BEGIN " + 1243 "DELETE FROM " + Tables.EVENTS + " WHERE " + Calendar.Events.CALENDAR_ID + "=" + 1244 "old." + Calendar.Events._ID + ";" + 1245 "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" + 1246 "END"); 1247 db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted"); 1248 } 1249 1250 private void dropTables(SQLiteDatabase db) { 1251 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDARS + ";"); 1252 db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS + ";"); 1253 db.execSQL("DROP TABLE IF EXISTS " + Tables.EVENTS_RAW_TIMES + ";"); 1254 db.execSQL("DROP TABLE IF EXISTS " + Tables.INSTANCES + ";"); 1255 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_META_DATA + ";"); 1256 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_CACHE + ";"); 1257 db.execSQL("DROP TABLE IF EXISTS " + Tables.ATTENDEES + ";"); 1258 db.execSQL("DROP TABLE IF EXISTS " + Tables.REMINDERS + ";"); 1259 db.execSQL("DROP TABLE IF EXISTS " + Tables.CALENDAR_ALERTS + ";"); 1260 db.execSQL("DROP TABLE IF EXISTS " + Tables.EXTENDED_PROPERTIES + ";"); 1261 } 1262 1263 @Override 1264 public synchronized SQLiteDatabase getWritableDatabase() { 1265 SQLiteDatabase db = super.getWritableDatabase(); 1266 return db; 1267 } 1268 1269 public SyncStateContentProviderHelper getSyncState() { 1270 return mSyncState; 1271 } 1272 1273 /** 1274 * Schedule a calendar sync for the account. 1275 * @param account the account for which to schedule a sync 1276 * @param uploadChangesOnly if set, specify that the sync should only send 1277 * up local changes. This is typically used for a local sync, a user override of 1278 * too many deletions, or a sync after a calendar is unselected. 1279 * @param url the url feed for the calendar to sync (may be null, in which case a poll of 1280 * all feeds is done.) 1281 */ 1282 void scheduleSync(Account account, boolean uploadChangesOnly, String url) { 1283 Bundle extras = new Bundle(); 1284 if (uploadChangesOnly) { 1285 extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly); 1286 } 1287 if (url != null) { 1288 extras.putString("feed", url); 1289 extras.putBoolean(ContentResolver.SYNC_EXTRAS_MANUAL, true); 1290 } 1291 ContentResolver.requestSync(account, Calendar.Calendars.CONTENT_URI.getAuthority(), extras); 1292 } 1293 1294 private static void createEventsView(SQLiteDatabase db) { 1295 db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";"); 1296 String eventsSelect = "SELECT " 1297 + Tables.EVENTS + "." + Calendar.Events._ID + " AS " + Calendar.Events._ID + "," 1298 + Calendar.Events.HTML_URI + "," 1299 + Calendar.Events.TITLE + "," 1300 + Calendar.Events.DESCRIPTION + "," 1301 + Calendar.Events.EVENT_LOCATION + "," 1302 + Calendar.Events.STATUS + "," 1303 + Calendar.Events.SELF_ATTENDEE_STATUS + "," 1304 + Calendar.Events.COMMENTS_URI + "," 1305 + Calendar.Events.DTSTART + "," 1306 + Calendar.Events.DTEND + "," 1307 + Calendar.Events.DURATION + "," 1308 + Calendar.Events.EVENT_TIMEZONE + "," 1309 + Calendar.Events.ALL_DAY + "," 1310 + Calendar.Events.VISIBILITY + "," 1311 + Calendar.Calendars.TIMEZONE + "," 1312 + Calendar.Calendars.SELECTED + "," 1313 + Calendar.Calendars.ACCESS_LEVEL + "," 1314 + Calendar.Events.TRANSPARENCY + "," 1315 + Calendar.Calendars.COLOR + "," 1316 + Calendar.Events.HAS_ALARM + "," 1317 + Calendar.Events.HAS_EXTENDED_PROPERTIES + "," 1318 + Calendar.Events.RRULE + "," 1319 + Calendar.Events.RDATE + "," 1320 + Calendar.Events.EXRULE + "," 1321 + Calendar.Events.EXDATE + "," 1322 + Calendar.Events.ORIGINAL_EVENT + "," 1323 + Calendar.Events.ORIGINAL_INSTANCE_TIME + "," 1324 + Calendar.Events.ORIGINAL_ALL_DAY + "," 1325 + Calendar.Events.LAST_DATE + "," 1326 + Calendar.Events.HAS_ATTENDEE_DATA + "," 1327 + Calendar.Events.CALENDAR_ID + "," 1328 + Calendar.Events.GUESTS_CAN_INVITE_OTHERS + "," 1329 + Calendar.Events.GUESTS_CAN_MODIFY + "," 1330 + Calendar.Events.GUESTS_CAN_SEE_GUESTS + "," 1331 + Calendar.Events.ORGANIZER + "," 1332 + Tables.EVENTS + "." + Calendar.Events.DELETED 1333 + " AS " + Calendar.Events.DELETED + "," 1334 + Tables.EVENTS + "." + Calendar.Events._SYNC_ID 1335 + " AS " + Calendar.Events._SYNC_ID + "," 1336 + Tables.EVENTS + "." + Calendar.Events._SYNC_VERSION 1337 + " AS " + Calendar.Events._SYNC_VERSION + "," 1338 + Tables.EVENTS + "." + Calendar.Events._SYNC_DIRTY 1339 + " AS " + Calendar.Events._SYNC_DIRTY + "," 1340 + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT 1341 + " AS " + Calendar.Events._SYNC_ACCOUNT + "," 1342 + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT_TYPE 1343 + " AS " + Calendar.Events._SYNC_ACCOUNT_TYPE + "," 1344 + Tables.EVENTS + "." + Calendar.Events._SYNC_TIME 1345 + " AS " + Calendar.Events._SYNC_TIME + "," 1346 + Tables.EVENTS + "." + Calendar.Events._SYNC_DATA 1347 + " AS " + Calendar.Events._SYNC_DATA + "," 1348 + Tables.EVENTS + "." + Calendar.Events._SYNC_MARK 1349 + " AS " + Calendar.Events._SYNC_MARK + "," 1350 + Calendar.Calendars.EVENTS_URL + "," 1351 + Calendar.Calendars.OWNER_ACCOUNT + "," 1352 + Calendar.Calendars.SYNC_EVENTS 1353 + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS 1354 + " ON (" + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID 1355 + "=" + Tables.CALENDARS + "." + Calendar.Calendars._ID 1356 + ")"; 1357 1358 db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect); 1359 } 1360 1361 /** 1362 * Extracts the calendar email from a calendar feed url. 1363 * @param feed the calendar feed url 1364 * @return the calendar email that is in the feed url or null if it can't 1365 * find the email address. 1366 * TODO: this is duplicated in CalendarSyncAdapter; move to a library 1367 */ 1368 public static String calendarEmailAddressFromFeedUrl(String feed) { 1369 // Example feed url: 1370 // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees 1371 String[] pathComponents = feed.split("/"); 1372 if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) { 1373 try { 1374 return URLDecoder.decode(pathComponents[5], "UTF-8"); 1375 } catch (UnsupportedEncodingException e) { 1376 Log.e(TAG, "unable to url decode the email address in calendar " + feed); 1377 return null; 1378 } 1379 } 1380 1381 Log.e(TAG, "unable to find the email address in calendar " + feed); 1382 return null; 1383 } 1384} 1385