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