CalendarDatabaseHelper.java revision 162c7c9bbd53b623fbe913b376e7f7f42915bb59
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.android.internal.content.SyncStateContentProviderHelper; 20 21import android.accounts.Account; 22import android.content.ContentResolver; 23import android.content.ContentValues; 24import android.content.Context; 25import android.content.res.Resources; 26import android.database.Cursor; 27import android.database.DatabaseUtils; 28import android.database.sqlite.SQLiteDatabase; 29import android.database.sqlite.SQLiteOpenHelper; 30import android.os.Bundle; 31import android.provider.Calendar; 32import android.provider.ContactsContract; 33import android.text.format.Time; 34import android.util.Log; 35 36import java.io.UnsupportedEncodingException; 37import java.net.URLDecoder; 38 39/** 40 * Database helper for calendar. Designed as a singleton to make sure that all 41 * {@link android.content.ContentProvider} users get the same reference. 42 */ 43/* package */ class CalendarDatabaseHelper extends SQLiteOpenHelper { 44 private static final String TAG = "CalendarDatabaseHelper"; 45 46 private static final String DATABASE_NAME = "calendar.db"; 47 48 // TODO: change the Calendar contract so these are defined there. 49 static final String ACCOUNT_NAME = "_sync_account"; 50 static final String ACCOUNT_TYPE = "_sync_account_type"; 51 52 // Note: if you update the version number, you must also update the code 53 // in upgradeDatabase() to modify the database (gracefully, if possible). 54 private static final int DATABASE_VERSION = 64; 55 56 private final Context mContext; 57 private final SyncStateContentProviderHelper mSyncState; 58 59 private static CalendarDatabaseHelper sSingleton = null; 60 61 private DatabaseUtils.InsertHelper mCalendarsInserter; 62 private DatabaseUtils.InsertHelper mEventsInserter; 63 private DatabaseUtils.InsertHelper mEventsRawTimesInserter; 64 private DatabaseUtils.InsertHelper mInstancesInserter; 65 private DatabaseUtils.InsertHelper mAttendeesInserter; 66 private DatabaseUtils.InsertHelper mRemindersInserter; 67 private DatabaseUtils.InsertHelper mCalendarAlertsInserter; 68 private DatabaseUtils.InsertHelper mExtendedPropertiesInserter; 69 70 public long calendarsInsert(ContentValues values) { 71 return mCalendarsInserter.insert(values); 72 } 73 74 public long eventsInsert(ContentValues values) { 75 return mEventsInserter.insert(values); 76 } 77 78 public long eventsRawTimesInsert(ContentValues values) { 79 return mEventsRawTimesInserter.insert(values); 80 } 81 82 public long eventsRawTimesReplace(ContentValues values) { 83 return mEventsRawTimesInserter.replace(values); 84 } 85 86 public long instancesInsert(ContentValues values) { 87 return mInstancesInserter.insert(values); 88 } 89 90 public long instancesReplace(ContentValues values) { 91 return mInstancesInserter.replace(values); 92 } 93 94 public long attendeesInsert(ContentValues values) { 95 return mAttendeesInserter.insert(values); 96 } 97 98 public long remindersInsert(ContentValues values) { 99 return mRemindersInserter.insert(values); 100 } 101 102 public long calendarAlertsInsert(ContentValues values) { 103 return mCalendarAlertsInserter.insert(values); 104 } 105 106 public long extendedPropertiesInsert(ContentValues values) { 107 return mExtendedPropertiesInserter.insert(values); 108 } 109 110 public static synchronized CalendarDatabaseHelper getInstance(Context context) { 111 if (sSingleton == null) { 112 sSingleton = new CalendarDatabaseHelper(context); 113 } 114 return sSingleton; 115 } 116 117 /** 118 * Private constructor, callers except unit tests should obtain an instance through 119 * {@link #getInstance(android.content.Context)} instead. 120 */ 121 /* package */ CalendarDatabaseHelper(Context context) { 122 super(context, DATABASE_NAME, null, DATABASE_VERSION); 123 if (false) Log.i(TAG, "Creating OpenHelper"); 124 Resources resources = context.getResources(); 125 126 mContext = context; 127 mSyncState = new SyncStateContentProviderHelper(); 128 } 129 130 @Override 131 public void onOpen(SQLiteDatabase db) { 132 mSyncState.onDatabaseOpened(db); 133 134 mCalendarsInserter = new DatabaseUtils.InsertHelper(db, "Calendars"); 135 mEventsInserter = new DatabaseUtils.InsertHelper(db, "Events"); 136 mEventsRawTimesInserter = new DatabaseUtils.InsertHelper(db, "EventsRawTimes"); 137 mInstancesInserter = new DatabaseUtils.InsertHelper(db, "Instances"); 138 mAttendeesInserter = new DatabaseUtils.InsertHelper(db, "Attendees"); 139 mRemindersInserter = new DatabaseUtils.InsertHelper(db, "Reminders"); 140 mCalendarAlertsInserter = new DatabaseUtils.InsertHelper(db, "CalendarAlerts"); 141 mExtendedPropertiesInserter = 142 new DatabaseUtils.InsertHelper(db, "ExtendedProperties"); 143 } 144 145 @Override 146 public void onCreate(SQLiteDatabase db) { 147 Log.i(TAG, "Bootstrapping database"); 148 149 mSyncState.createDatabase(db); 150 151 db.execSQL("CREATE TABLE Calendars (" + 152 "_id INTEGER PRIMARY KEY," + 153 ACCOUNT_NAME + " TEXT," + 154 ACCOUNT_TYPE + " TEXT," + 155 "_sync_id TEXT," + 156 "_sync_version TEXT," + 157 "_sync_time TEXT," + // UTC 158 "_sync_local_id INTEGER," + 159 "_sync_dirty INTEGER," + 160 "_sync_mark INTEGER," + // Used to filter out new rows 161 "url TEXT," + 162 "name TEXT," + 163 "displayName TEXT," + 164 "hidden INTEGER NOT NULL DEFAULT 0," + 165 "color INTEGER," + 166 "access_level INTEGER," + 167 "selected INTEGER NOT NULL DEFAULT 1," + 168 "sync_events INTEGER NOT NULL DEFAULT 0," + 169 "location TEXT," + 170 "timezone TEXT," + 171 "ownerAccount TEXT" + 172 ");"); 173 174 // Trigger to remove a calendar's events when we delete the calendar 175 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " + 176 "BEGIN " + 177 "DELETE FROM Events WHERE calendar_id = old._id;" + 178 "END"); 179 180 // TODO: do we need both dtend and duration? 181 db.execSQL("CREATE TABLE Events (" + 182 "_id INTEGER PRIMARY KEY," + 183 ACCOUNT_NAME + " TEXT," + 184 ACCOUNT_TYPE + " TEXT," + 185 "_sync_id TEXT," + 186 "_sync_version TEXT," + 187 "_sync_time TEXT," + // UTC 188 "_sync_local_id INTEGER," + 189 "_sync_dirty INTEGER," + 190 "_sync_mark INTEGER," + // To filter out new rows 191 "calendar_id INTEGER NOT NULL," + 192 "htmlUri TEXT," + 193 "title TEXT," + 194 "eventLocation TEXT," + 195 "description TEXT," + 196 "eventStatus INTEGER," + 197 "selfAttendeeStatus INTEGER NOT NULL DEFAULT 0," + 198 "commentsUri TEXT," + 199 "dtstart INTEGER," + // millis since epoch 200 "dtend INTEGER," + // millis since epoch 201 "eventTimezone TEXT," + // timezone for event 202 "duration TEXT," + 203 "allDay INTEGER NOT NULL DEFAULT 0," + 204 "visibility INTEGER NOT NULL DEFAULT 0," + 205 "transparency INTEGER NOT NULL DEFAULT 0," + 206 "hasAlarm INTEGER NOT NULL DEFAULT 0," + 207 "hasExtendedProperties INTEGER NOT NULL DEFAULT 0," + 208 "rrule TEXT," + 209 "rdate TEXT," + 210 "exrule TEXT," + 211 "exdate TEXT," + 212 "originalEvent TEXT," + // _sync_id of recurring event 213 "originalInstanceTime INTEGER," + // millis since epoch 214 "originalAllDay INTEGER," + 215 "lastDate INTEGER," + // millis since epoch 216 "hasAttendeeData INTEGER NOT NULL DEFAULT 0," + 217 "guestsCanModify INTEGER NOT NULL DEFAULT 0," + 218 "guestsCanInviteOthers INTEGER NOT NULL DEFAULT 1," + 219 "guestsCanSeeGuests INTEGER NOT NULL DEFAULT 1," + 220 "organizer STRING," + 221 "deleted INTEGER NOT NULL DEFAULT 0," + 222 "dtstart2 INTEGER," + //millis since epoch, allDay events in local timezone 223 "dtend2 INTEGER," + //millis since epoch, allDay events in local timezone 224 "eventTimezone2 TEXT," + //timezone for event with allDay events in local timezone 225 "syncAdapterData TEXT" + //available for use by sync adapters 226 ");"); 227 228 // Trigger to set event's sync_account 229 db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON Events " + 230 "BEGIN " + 231 "UPDATE Events SET _sync_account=" + 232 "(SELECT _sync_account FROM Calendars WHERE Calendars._id=new.calendar_id)," + 233 "_sync_account_type=" + 234 "(SELECT _sync_account_type FROM Calendars WHERE Calendars._id=new.calendar_id) " + 235 "WHERE Events._id=new._id;" + 236 "END"); 237 238 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events (" 239 + Calendar.Events._SYNC_ACCOUNT_TYPE + ", " + Calendar.Events._SYNC_ACCOUNT + ", " 240 + Calendar.Events._SYNC_ID + ");"); 241 242 db.execSQL("CREATE INDEX eventsCalendarIdIndex ON Events (" + 243 Calendar.Events.CALENDAR_ID + 244 ");"); 245 246 db.execSQL("CREATE TABLE EventsRawTimes (" + 247 "_id INTEGER PRIMARY KEY," + 248 "event_id INTEGER NOT NULL," + 249 "dtstart2445 TEXT," + 250 "dtend2445 TEXT," + 251 "originalInstanceTime2445 TEXT," + 252 "lastDate2445 TEXT," + 253 "UNIQUE (event_id)" + 254 ");"); 255 256 db.execSQL("CREATE TABLE Instances (" + 257 "_id INTEGER PRIMARY KEY," + 258 "event_id INTEGER," + 259 "begin INTEGER," + // UTC millis 260 "end INTEGER," + // UTC millis 261 "startDay INTEGER," + // Julian start day 262 "endDay INTEGER," + // Julian end day 263 "startMinute INTEGER," + // minutes from midnight 264 "endMinute INTEGER," + // minutes from midnight 265 "UNIQUE (event_id, begin, end)" + 266 ");"); 267 268 db.execSQL("CREATE INDEX instancesStartDayIndex ON Instances (" + 269 Calendar.Instances.START_DAY + 270 ");"); 271 272 db.execSQL("CREATE TABLE CalendarMetaData (" + 273 "_id INTEGER PRIMARY KEY," + 274 "localTimezone TEXT," + 275 "minInstance INTEGER," + // UTC millis 276 "maxInstance INTEGER" + // UTC millis 277 ");"); 278 279 createCalendarCacheTable(db); 280 281 db.execSQL("CREATE TABLE Attendees (" + 282 "_id INTEGER PRIMARY KEY," + 283 "event_id INTEGER," + 284 "attendeeName TEXT," + 285 "attendeeEmail TEXT," + 286 "attendeeStatus INTEGER," + 287 "attendeeRelationship INTEGER," + 288 "attendeeType INTEGER" + 289 ");"); 290 291 db.execSQL("CREATE INDEX attendeesEventIdIndex ON Attendees (" + 292 Calendar.Attendees.EVENT_ID + 293 ");"); 294 295 db.execSQL("CREATE TABLE Reminders (" + 296 "_id INTEGER PRIMARY KEY," + 297 "event_id INTEGER," + 298 "minutes INTEGER," + 299 "method INTEGER NOT NULL" + 300 " DEFAULT " + Calendar.Reminders.METHOD_DEFAULT + 301 ");"); 302 303 db.execSQL("CREATE INDEX remindersEventIdIndex ON Reminders (" + 304 Calendar.Reminders.EVENT_ID + 305 ");"); 306 307 // This table stores the Calendar notifications that have gone off. 308 db.execSQL("CREATE TABLE CalendarAlerts (" + 309 "_id INTEGER PRIMARY KEY," + 310 "event_id INTEGER," + 311 "begin INTEGER NOT NULL," + // UTC millis 312 "end INTEGER NOT NULL," + // UTC millis 313 "alarmTime INTEGER NOT NULL," + // UTC millis 314 "creationTime INTEGER NOT NULL," + // UTC millis 315 "receivedTime INTEGER NOT NULL," + // UTC millis 316 "notifyTime INTEGER NOT NULL," + // UTC millis 317 "state INTEGER NOT NULL," + 318 "minutes INTEGER," + 319 "UNIQUE (alarmTime, begin, event_id)" + 320 ");"); 321 322 db.execSQL("CREATE INDEX calendarAlertsEventIdIndex ON CalendarAlerts (" + 323 Calendar.CalendarAlerts.EVENT_ID + 324 ");"); 325 326 db.execSQL("CREATE TABLE ExtendedProperties (" + 327 "_id INTEGER PRIMARY KEY," + 328 "event_id INTEGER," + 329 "name TEXT," + 330 "value TEXT" + 331 ");"); 332 333 db.execSQL("CREATE INDEX extendedPropertiesEventIdIndex ON ExtendedProperties (" + 334 Calendar.ExtendedProperties.EVENT_ID + 335 ");"); 336 337 // Trigger to remove data tied to an event when we delete that event. 338 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON Events " + 339 "BEGIN " + 340 "DELETE FROM Instances WHERE event_id = old._id;" + 341 "DELETE FROM EventsRawTimes WHERE event_id = old._id;" + 342 "DELETE FROM Attendees WHERE event_id = old._id;" + 343 "DELETE FROM Reminders WHERE event_id = old._id;" + 344 "DELETE FROM CalendarAlerts WHERE event_id = old._id;" + 345 "DELETE FROM ExtendedProperties WHERE event_id = old._id;" + 346 "END"); 347 348 createEventsView(db); 349 350 ContentResolver.requestSync(null /* all accounts */, 351 ContactsContract.AUTHORITY, new Bundle()); 352 } 353 354 private void createCalendarCacheTable(SQLiteDatabase db) { 355 // This is a hack because versioning skipped version number 61 of schema 356 // TODO after version 70 this can be removed 357 db.execSQL("DROP TABLE IF EXISTS CalendarCache;"); 358 359 // IF NOT EXISTS should be normal pattern for table creation 360 db.execSQL("CREATE TABLE IF NOT EXISTS CalendarCache (" + 361 "_id INTEGER PRIMARY KEY," + 362 "key TEXT NOT NULL," + 363 "value TEXT" + 364 ");"); 365 366 db.execSQL("INSERT INTO CalendarCache (key, value) VALUES (" + 367 "'" + CalendarCache.KEY_TIMEZONE_DATABASE_VERSION + "'," + 368 "'" + CalendarCache.DEFAULT_TIMEZONE_DATABASE_VERSION + "'" + 369 ");"); 370 } 371 372 @Override 373 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 374 Log.i(TAG, "Upgrading DB from version " + oldVersion 375 + " to " + newVersion); 376 if (oldVersion < 49) { 377 dropTables(db); 378 mSyncState.createDatabase(db); 379 return; // this was lossy 380 } 381 382 if (oldVersion < 51) { 383 upgradeToVersion51(db); // From 50 or 51 384 oldVersion = 51; 385 } 386 if (oldVersion == 51) { 387 upgradeToVersion52(db); 388 oldVersion += 1; 389 } 390 if (oldVersion == 52) { 391 upgradeToVersion53(db); 392 oldVersion += 1; 393 } 394 if (oldVersion == 53) { 395 upgradeToVersion54(db); 396 oldVersion += 1; 397 } 398 if (oldVersion == 54) { 399 upgradeToVersion55(db); 400 oldVersion += 1; 401 } 402 if (oldVersion == 55 || oldVersion == 56) { 403 // Both require resync, so just schedule it once 404 upgradeResync(db); 405 } 406 if (oldVersion == 55) { 407 upgradeToVersion56(db); 408 oldVersion += 1; 409 } 410 if (oldVersion == 56) { 411 upgradeToVersion57(db); 412 oldVersion += 1; 413 } 414 if (oldVersion == 57) { 415 // Changes are undone upgrading to 60, so don't do anything. 416 oldVersion += 1; 417 } 418 if (oldVersion == 58) { 419 upgradeToVersion59(db); 420 oldVersion += 1; 421 } 422 if (oldVersion == 59) { 423 upgradeToVersion60(db); 424 oldVersion += 1; 425 } 426 if (oldVersion == 60) { 427 upgradeToVersion61(db); 428 oldVersion += 1; 429 } 430 if (oldVersion == 61) { 431 upgradeToVersion62(db); 432 oldVersion += 1; 433 } 434 if (oldVersion == 62) { 435 upgradeToVersion63(db); 436 oldVersion += 1; 437 } 438 if (oldVersion == 63) { 439 upgradeToVersion64(db); 440 oldVersion += 1; 441 } 442 } 443 444 private void upgradeToVersion56(SQLiteDatabase db) { 445 db.execSQL("ALTER TABLE Calendars ADD COLUMN ownerAccount TEXT;"); 446 db.execSQL("ALTER TABLE Events ADD COLUMN hasAttendeeData INTEGER;"); 447 // Clear _sync_dirty to avoid a client-to-server sync that could blow away 448 // server attendees. 449 // Clear _sync_version to pull down the server's event (with attendees) 450 // Change the URLs from full-selfattendance to full 451 db.execSQL("UPDATE Events" 452 + " SET _sync_dirty=0," 453 + " _sync_version=NULL," 454 + " _sync_id=" 455 + "REPLACE(_sync_id, '/private/full-selfattendance', '/private/full')," 456 + " commentsUri =" 457 + "REPLACE(commentsUri, '/private/full-selfattendance', '/private/full');"); 458 db.execSQL("UPDATE Calendars" 459 + " SET url=" 460 + "REPLACE(url, '/private/full-selfattendance', '/private/full');"); 461 462 // "cursor" iterates over all the calendars 463 Cursor cursor = db.rawQuery("SELECT _id, url FROM Calendars", 464 null /* selection args */); 465 // Add the owner column. 466 if (cursor != null) { 467 try { 468 while (cursor.moveToNext()) { 469 Long id = cursor.getLong(0); 470 String url = cursor.getString(1); 471 String owner = calendarEmailAddressFromFeedUrl(url); 472 db.execSQL("UPDATE Calendars SET ownerAccount=? WHERE _id=?", 473 new Object[] {owner, id}); 474 } 475 } finally { 476 cursor.close(); 477 } 478 } 479 } 480 481 private void upgradeResync(SQLiteDatabase db) { 482 // Delete sync state, so all records will be re-synced. 483 db.execSQL("DELETE FROM _sync_state;"); 484 485 // "cursor" iterates over all the calendars 486 Cursor cursor = db.rawQuery("SELECT _sync_account,_sync_account_type,url " 487 + "FROM Calendars", 488 null /* selection args */); 489 if (cursor != null) { 490 try { 491 while (cursor.moveToNext()) { 492 String accountName = cursor.getString(0); 493 String accountType = cursor.getString(1); 494 final Account account = new Account(accountName, accountType); 495 String calendarUrl = cursor.getString(2); 496 scheduleSync(account, false /* two-way sync */, calendarUrl); 497 } 498 } finally { 499 cursor.close(); 500 } 501 } 502 } 503 504 private void upgradeToVersion64(SQLiteDatabase db) { 505 // Add a column that may be used by sync adapters 506 db.execSQL("ALTER TABLE Events ADD COLUMN syncAdapterData TEXT;"); 507 } 508 509 private void upgradeToVersion63(SQLiteDatabase db) { 510 // we need to recreate the Events view 511 createEventsView(db); 512 } 513 514 private void upgradeToVersion62(SQLiteDatabase db) { 515 // New columns are to transition to having allDay events in the local timezone 516 db.execSQL("ALTER TABLE Events ADD COLUMN dtstart2 INTEGER;"); 517 db.execSQL("ALTER TABLE Events ADD COLUMN dtend2 INTEGER;"); 518 db.execSQL("ALTER TABLE Events ADD COLUMN eventTimezone2 TEXT;"); 519 520 String[] allDayBit = new String[] {"0"}; 521 // Copy over all the data that isn't an all day event. 522 db.execSQL("UPDATE Events " + 523 "SET dtstart2=dtstart,dtend2=dtend,eventTimezone2=eventTimezone " + 524 "WHERE allDay=?;", 525 allDayBit /* selection args */); 526 527 // "cursor" iterates over all the calendars 528 allDayBit[0] = "1"; 529 Cursor cursor = db.rawQuery("SELECT Events._id,dtstart,dtend,eventTimezone,timezone " + 530 "FROM Events INNER JOIN Calendars " + 531 "WHERE Events.calendar_id=Calendars._id AND allDay=?", 532 allDayBit /* selection args */); 533 534 Time oldTime = new Time(); 535 Time newTime = new Time(); 536 // Update the allday events in the new columns 537 if (cursor != null) { 538 try { 539 String[] newData = new String[4]; 540 cursor.moveToPosition(-1); 541 while (cursor.moveToNext()) { 542 long id = cursor.getLong(0); // Order from query above 543 long dtstart = cursor.getLong(1); 544 long dtend = cursor.getLong(2); 545 String eTz = cursor.getString(3); // current event timezone 546 String tz = cursor.getString(4); // Calendar timezone 547 //If there's no timezone for some reason use UTC by default. 548 if(eTz == null) { 549 eTz = Time.TIMEZONE_UTC; 550 } 551 552 // Convert start time for all day events into the timezone of their calendar 553 oldTime.clear(eTz); 554 oldTime.set(dtstart); 555 newTime.clear(tz); 556 newTime.set(oldTime.monthDay, oldTime.month, oldTime.year); 557 newTime.normalize(false); 558 dtstart = newTime.toMillis(false /*ignoreDst*/); 559 560 // Convert end time for all day events into the timezone of their calendar 561 oldTime.clear(eTz); 562 oldTime.set(dtend); 563 newTime.clear(tz); 564 newTime.set(oldTime.monthDay, oldTime.month, oldTime.year); 565 newTime.normalize(false); 566 dtend = newTime.toMillis(false /*ignoreDst*/); 567 568 newData[0] = String.valueOf(dtstart); 569 newData[1] = String.valueOf(dtend); 570 newData[2] = tz; 571 newData[3] = String.valueOf(id); 572 db.execSQL("UPDATE Events " + 573 "SET dtstart2=?,dtend2=?,eventTimezone2=? " + 574 "WHERE _id=?", 575 newData); 576 } 577 } finally { 578 cursor.close(); 579 } 580 } 581 } 582 583 private void upgradeToVersion61(SQLiteDatabase db) { 584 createCalendarCacheTable(db); 585 } 586 587 private void upgradeToVersion60(SQLiteDatabase db) { 588 // Switch to CalendarProvider2 589 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 590 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " + 591 "BEGIN " + 592 "DELETE FROM Events WHERE calendar_id = old._id;" + 593 "END"); 594 db.execSQL("ALTER TABLE Events ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;"); 595 db.execSQL("DROP TRIGGER IF EXISTS events_insert"); 596 db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON Events " + 597 "BEGIN " + 598 "UPDATE Events SET _sync_account=" + 599 "(SELECT _sync_account FROM Calendars WHERE Calendars._id=new.calendar_id)," + 600 "_sync_account_type=" + 601 "(SELECT _sync_account_type FROM Calendars WHERE Calendars._id=new.calendar_id) " + 602 "WHERE Events._id=new._id;" + 603 "END"); 604 db.execSQL("DROP TABLE IF EXISTS DeletedEvents;"); 605 db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete"); 606 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON Events " + 607 "BEGIN " + 608 "DELETE FROM Instances WHERE event_id = old._id;" + 609 "DELETE FROM EventsRawTimes WHERE event_id = old._id;" + 610 "DELETE FROM Attendees WHERE event_id = old._id;" + 611 "DELETE FROM Reminders WHERE event_id = old._id;" + 612 "DELETE FROM CalendarAlerts WHERE event_id = old._id;" + 613 "DELETE FROM ExtendedProperties WHERE event_id = old._id;" + 614 "END"); 615 db.execSQL("DROP TRIGGER IF EXISTS attendees_update"); 616 db.execSQL("DROP TRIGGER IF EXISTS attendees_insert"); 617 db.execSQL("DROP TRIGGER IF EXISTS attendees_delete"); 618 db.execSQL("DROP TRIGGER IF EXISTS reminders_update"); 619 db.execSQL("DROP TRIGGER IF EXISTS reminders_insert"); 620 db.execSQL("DROP TRIGGER IF EXISTS reminders_delete"); 621 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update"); 622 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert"); 623 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete"); 624 625 createEventsView(db); 626 } 627 628 private void upgradeToVersion59(SQLiteDatabase db) { 629 db.execSQL("DROP TABLE IF EXISTS BusyBits;"); 630 db.execSQL("CREATE TEMPORARY TABLE CalendarMetaData_Backup" + 631 "(_id,localTimezone,minInstance,maxInstance);"); 632 db.execSQL("INSERT INTO CalendarMetaData_Backup " + 633 "SELECT _id,localTimezone,minInstance,maxInstance FROM CalendarMetaData;"); 634 db.execSQL("DROP TABLE CalendarMetaData;"); 635 db.execSQL("CREATE TABLE CalendarMetaData(_id,localTimezone,minInstance,maxInstance);"); 636 db.execSQL("INSERT INTO CalendarMetaData " + 637 "SELECT _id,localTimezone,minInstance,maxInstance FROM CalendarMetaData_Backup;"); 638 db.execSQL("DROP TABLE CalendarMetaData_Backup;"); 639 } 640 641 private void upgradeToVersion57(SQLiteDatabase db) { 642 db.execSQL("ALTER TABLE Events ADD COLUMN guestsCanModify" 643 + " INTEGER NOT NULL DEFAULT 0;"); 644 db.execSQL("ALTER TABLE Events ADD COLUMN guestsCanInviteOthers" 645 + " INTEGER NOT NULL DEFAULT 1;"); 646 db.execSQL("ALTER TABLE Events ADD COLUMN guestsCanSeeGuests" 647 + " INTEGER NOT NULL DEFAULT 1;"); 648 db.execSQL("ALTER TABLE Events ADD COLUMN organizer STRING;"); 649 db.execSQL("UPDATE Events SET organizer=" 650 + "(SELECT attendeeEmail FROM Attendees WHERE " 651 + "Attendees.event_id = Events._id" 652 + " AND Attendees.attendeeRelationship=2);"); 653 } 654 655 private void upgradeToVersion55(SQLiteDatabase db) { 656 db.execSQL("ALTER TABLE Calendars ADD COLUMN _sync_account_type TEXT;"); 657 db.execSQL("ALTER TABLE Events ADD COLUMN _sync_account_type TEXT;"); 658 db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;"); 659 db.execSQL("UPDATE Calendars" 660 + " SET _sync_account_type='com.google'" 661 + " WHERE _sync_account IS NOT NULL"); 662 db.execSQL("UPDATE Events" 663 + " SET _sync_account_type='com.google'" 664 + " WHERE _sync_account IS NOT NULL"); 665 db.execSQL("UPDATE DeletedEvents" 666 + " SET _sync_account_type='com.google'" 667 + " WHERE _sync_account IS NOT NULL"); 668 Log.w(TAG, "re-creating eventSyncAccountAndIdIndex"); 669 db.execSQL("DROP INDEX eventSyncAccountAndIdIndex"); 670 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events (" 671 + Calendar.Events._SYNC_ACCOUNT_TYPE + ", " 672 + Calendar.Events._SYNC_ACCOUNT + ", " 673 + Calendar.Events._SYNC_ID + ");"); 674 } 675 676 private void upgradeToVersion54(SQLiteDatabase db) { 677 Log.w(TAG, "adding eventSyncAccountAndIdIndex"); 678 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events (" 679 + Calendar.Events._SYNC_ACCOUNT + ", " + Calendar.Events._SYNC_ID + ");"); 680 } 681 682 private void upgradeToVersion53(SQLiteDatabase db) { 683 Log.w(TAG, "Upgrading CalendarAlerts table"); 684 db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN creationTime INTEGER DEFAULT 0;"); 685 db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN receivedTime INTEGER DEFAULT 0;"); 686 db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN notifyTime INTEGER DEFAULT 0;"); 687 } 688 689 private void upgradeToVersion52(SQLiteDatabase db) { 690 // We added "originalAllDay" to the Events table to keep track of 691 // the allDay status of the original recurring event for entries 692 // that are exceptions to that recurring event. We need this so 693 // that we can format the date correctly for the "originalInstanceTime" 694 // column when we make a change to the recurrence exception and 695 // send it to the server. 696 db.execSQL("ALTER TABLE Events ADD COLUMN originalAllDay INTEGER;"); 697 698 // Iterate through the Events table and for each recurrence 699 // exception, fill in the correct value for "originalAllDay", 700 // if possible. The only times where this might not be possible 701 // are (1) the original recurring event no longer exists, or 702 // (2) the original recurring event does not yet have a _sync_id 703 // because it was created on the phone and hasn't been synced to the 704 // server yet. In both cases the originalAllDay field will be set 705 // to null. In the first case we don't care because the recurrence 706 // exception will not be displayed and we won't be able to make 707 // any changes to it (and even if we did, the server should ignore 708 // them, right?). In the second case, the calendar client already 709 // disallows making changes to an instance of a recurring event 710 // until the recurring event has been synced to the server so the 711 // second case should never occur. 712 713 // "cursor" iterates over all the recurrences exceptions. 714 Cursor cursor = db.rawQuery("SELECT _id,originalEvent FROM Events" 715 + " WHERE originalEvent IS NOT NULL", null /* selection args */); 716 if (cursor != null) { 717 try { 718 while (cursor.moveToNext()) { 719 long id = cursor.getLong(0); 720 String originalEvent = cursor.getString(1); 721 722 // Find the original recurring event (if it exists) 723 Cursor recur = db.rawQuery("SELECT allDay FROM Events" 724 + " WHERE _sync_id=?", new String[] {originalEvent}); 725 if (recur == null) { 726 continue; 727 } 728 729 try { 730 // Fill in the "originalAllDay" field of the 731 // recurrence exception with the "allDay" value 732 // from the recurring event. 733 if (recur.moveToNext()) { 734 int allDay = recur.getInt(0); 735 db.execSQL("UPDATE Events SET originalAllDay=" + allDay 736 + " WHERE _id="+id); 737 } 738 } finally { 739 recur.close(); 740 } 741 } 742 } finally { 743 cursor.close(); 744 } 745 } 746 } 747 748 private void upgradeToVersion51(SQLiteDatabase db) { 749 Log.w(TAG, "Upgrading DeletedEvents table"); 750 751 // We don't have enough information to fill in the correct 752 // value of the calendar_id for old rows in the DeletedEvents 753 // table, but rows in that table are transient so it is unlikely 754 // that there are any rows. Plus, the calendar_id is used only 755 // when deleting a calendar, which is a rare event. All new rows 756 // will have the correct calendar_id. 757 db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;"); 758 759 // Trigger to remove a calendar's events when we delete the calendar 760 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 761 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " + 762 "BEGIN " + 763 "DELETE FROM Events WHERE calendar_id = old._id;" + 764 "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" + 765 "END"); 766 db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted"); 767 } 768 769 private void dropTables(SQLiteDatabase db) { 770 db.execSQL("DROP TABLE IF EXISTS Calendars;"); 771 db.execSQL("DROP TABLE IF EXISTS Events;"); 772 db.execSQL("DROP TABLE IF EXISTS EventsRawTimes;"); 773 db.execSQL("DROP TABLE IF EXISTS Instances;"); 774 db.execSQL("DROP TABLE IF EXISTS CalendarMetaData;"); 775 db.execSQL("DROP TABLE IF EXISTS CalendarCache;"); 776 db.execSQL("DROP TABLE IF EXISTS Attendees;"); 777 db.execSQL("DROP TABLE IF EXISTS Reminders;"); 778 db.execSQL("DROP TABLE IF EXISTS CalendarAlerts;"); 779 db.execSQL("DROP TABLE IF EXISTS ExtendedProperties;"); 780 } 781 782 @Override 783 public synchronized SQLiteDatabase getWritableDatabase() { 784 SQLiteDatabase db = super.getWritableDatabase(); 785 return db; 786 } 787 788 public SyncStateContentProviderHelper getSyncState() { 789 return mSyncState; 790 } 791 792 /** 793 * Schedule a calendar sync for the account. 794 * @param account the account for which to schedule a sync 795 * @param uploadChangesOnly if set, specify that the sync should only send 796 * up local changes. This is typically used for a local sync, a user override of 797 * too many deletions, or a sync after a calendar is unselected. 798 * @param url the url feed for the calendar to sync (may be null, in which case a poll of 799 * all feeds is done.) 800 */ 801 void scheduleSync(Account account, boolean uploadChangesOnly, String url) { 802 Bundle extras = new Bundle(); 803 if (uploadChangesOnly) { 804 extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly); 805 } 806 if (url != null) { 807 extras.putString("feed", url); 808 extras.putBoolean(ContentResolver.SYNC_EXTRAS_MANUAL, true); 809 } 810 ContentResolver.requestSync(account, Calendar.Calendars.CONTENT_URI.getAuthority(), extras); 811 } 812 813 public void wipeData() { 814 SQLiteDatabase db = getWritableDatabase(); 815 816 db.execSQL("DELETE FROM Calendars;"); 817 db.execSQL("DELETE FROM Events;"); 818 db.execSQL("DELETE FROM EventsRawTimes;"); 819 db.execSQL("DELETE FROM Instances;"); 820 db.execSQL("DELETE FROM CalendarMetaData;"); 821 db.execSQL("DELETE FROM CalendarCache;"); 822 db.execSQL("DELETE FROM Attendees;"); 823 db.execSQL("DELETE FROM Reminders;"); 824 db.execSQL("DELETE FROM CalendarAlerts;"); 825 db.execSQL("DELETE FROM ExtendedProperties;"); 826 } 827 828 public interface Views { 829 public static final String EVENTS = "view_events"; 830 } 831 832 public interface Tables { 833 public static final String EVENTS = "Events"; 834 public static final String CALENDARS = "Calendars"; 835 } 836 837 private static void createEventsView(SQLiteDatabase db) { 838 db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";"); 839 String eventsSelect = "SELECT " 840 + Tables.EVENTS + "." + Calendar.Events._ID + " AS " + Calendar.Events._ID + "," 841 + Calendar.Events.HTML_URI + "," 842 + Calendar.Events.TITLE + "," 843 + Calendar.Events.DESCRIPTION + "," 844 + Calendar.Events.EVENT_LOCATION + "," 845 + Calendar.Events.STATUS + "," 846 + Calendar.Events.SELF_ATTENDEE_STATUS + "," 847 + Calendar.Events.COMMENTS_URI + "," 848 + Calendar.Events.DTSTART + "," 849 + Calendar.Events.DTEND + "," 850 + Calendar.Events.DURATION + "," 851 + Calendar.Events.EVENT_TIMEZONE + "," 852 + Calendar.Events.ALL_DAY + "," 853 + Calendar.Events.VISIBILITY + "," 854 + Calendar.Events.TIMEZONE + "," 855 + Calendar.Events.SELECTED + "," 856 + Calendar.Events.ACCESS_LEVEL + "," 857 + Calendar.Events.TRANSPARENCY + "," 858 + Calendar.Events.COLOR + "," 859 + Calendar.Events.HAS_ALARM + "," 860 + Calendar.Events.HAS_EXTENDED_PROPERTIES + "," 861 + Calendar.Events.RRULE + "," 862 + Calendar.Events.RDATE + "," 863 + Calendar.Events.EXRULE + "," 864 + Calendar.Events.EXDATE + "," 865 + Calendar.Events.ORIGINAL_EVENT + "," 866 + Calendar.Events.ORIGINAL_INSTANCE_TIME + "," 867 + Calendar.Events.ORIGINAL_ALL_DAY + "," 868 + Calendar.Events.LAST_DATE + "," 869 + Calendar.Events.HAS_ATTENDEE_DATA + "," 870 + Calendar.Events.CALENDAR_ID + "," 871 + Calendar.Events.GUESTS_CAN_INVITE_OTHERS + "," 872 + Calendar.Events.GUESTS_CAN_MODIFY + "," 873 + Calendar.Events.GUESTS_CAN_SEE_GUESTS + "," 874 + Calendar.Events.ORGANIZER + "," 875 + Calendar.Events.DELETED + "," 876 + Tables.EVENTS + "." + Calendar.Events._SYNC_ID 877 + " AS " + Calendar.Events._SYNC_ID + "," 878 + Tables.EVENTS + "." + Calendar.Events._SYNC_VERSION 879 + " AS " + Calendar.Events._SYNC_VERSION + "," 880 + Tables.EVENTS + "." + Calendar.Events._SYNC_DIRTY 881 + " AS " + Calendar.Events._SYNC_DIRTY + "," 882 + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT 883 + " AS " + Calendar.Events._SYNC_ACCOUNT + "," 884 + Tables.EVENTS + "." + Calendar.Events._SYNC_ACCOUNT_TYPE 885 + " AS " + Calendar.Events._SYNC_ACCOUNT_TYPE + "," 886 + Tables.EVENTS + "." + Calendar.Events._SYNC_TIME 887 + " AS " + Calendar.Events._SYNC_TIME + "," 888 + Tables.EVENTS + "." + Calendar.Events._SYNC_LOCAL_ID 889 + " AS " + Calendar.Events._SYNC_LOCAL_ID + "," 890 + Calendar.Calendars.URL + "," 891 + Calendar.Calendars.OWNER_ACCOUNT + "," 892 + Calendar.Calendars.SYNC_EVENTS 893 + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS 894 + " ON (" + Tables.EVENTS + "." + Calendar.Events.CALENDAR_ID 895 + "=" + Tables.CALENDARS + "." + Calendar.Calendars._ID 896 + ")"; 897 898 db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect); 899 } 900 901 /** 902 * Extracts the calendar email from a calendar feed url. 903 * @param feed the calendar feed url 904 * @return the calendar email that is in the feed url or null if it can't 905 * find the email address. 906 * TODO: this is duplicated in CalendarSyncAdapter; move to a library 907 */ 908 public static String calendarEmailAddressFromFeedUrl(String feed) { 909 // Example feed url: 910 // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees 911 String[] pathComponents = feed.split("/"); 912 if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) { 913 try { 914 return URLDecoder.decode(pathComponents[5], "UTF-8"); 915 } catch (UnsupportedEncodingException e) { 916 Log.e(TAG, "unable to url decode the email address in calendar " + feed); 917 return null; 918 } 919 } 920 921 Log.e(TAG, "unable to find the email address in calendar " + feed); 922 return null; 923 } 924} 925