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