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