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