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