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.util.Log; 40 41 import com.android.calendarcommon2.Time; 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 = 601; 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 if (oldVersion < 601) { 1416 // There are no table changes in 601, but recreating the events view is required 1417 createEventsView = true; 1418 oldVersion = 601; 1419 } 1420 1421 if (createEventsView) { 1422 createEventsView(db); 1423 } 1424 if (oldVersion != DATABASE_VERSION) { 1425 Log.e(TAG, "Need to recreate Calendar schema because of " 1426 + "unknown Calendar database version: " + oldVersion); 1427 dropTables(db); 1428 bootstrapDB(db); 1429 oldVersion = DATABASE_VERSION; 1430 } else { 1431 removeOrphans(db); 1432 } 1433 } catch (SQLiteException e) { 1434 if (mInTestMode) { 1435 // We do want to crash if we are in test mode. 1436 throw e; 1437 } 1438 Log.e(TAG, "onUpgrade: SQLiteException, recreating db. ", e); 1439 Log.e(TAG, "(oldVersion was " + oldVersion + ")"); 1440 dropTables(db); 1441 bootstrapDB(db); 1442 return; // this was lossy 1443 } 1444 1445 long endWhen = System.nanoTime(); 1446 Log.d(TAG, "Calendar upgrade took " + ((endWhen - startWhen) / 1000000) + "ms"); 1447 1448 /** 1449 * db versions < 100 correspond to Froyo and earlier. Gingerbread bumped 1450 * the db versioning to 100. Honeycomb bumped it to 200. ICS will begin 1451 * in 300. At each major release we should jump to the next 1452 * centiversion. 1453 */ 1454 } 1455 1456 @Override onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)1457 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1458 Log.i(TAG, "Can't downgrade DB from version " + oldVersion + " to " + newVersion); 1459 dropTables(db); 1460 bootstrapDB(db); 1461 return; 1462 } 1463 1464 /** 1465 * If the user_version of the database if between 59 and 66 (those versions has been deployed 1466 * with no primary key for the CalendarMetaData table) 1467 */ recreateMetaDataAndInstances67(SQLiteDatabase db)1468 private void recreateMetaDataAndInstances67(SQLiteDatabase db) { 1469 // Recreate the CalendarMetaData table with correct primary key 1470 db.execSQL("DROP TABLE CalendarMetaData;"); 1471 createCalendarMetaDataTable59(db); 1472 1473 // Also clean the Instance table as this table may be corrupted 1474 db.execSQL("DELETE FROM Instances;"); 1475 } 1476 fixAllDayTime(Time time, String timezone, Long timeInMillis)1477 private static boolean fixAllDayTime(Time time, String timezone, Long timeInMillis) { 1478 time.set(timeInMillis); 1479 if(time.getHour() != 0 || time.getMinute() != 0 || time.getSecond() != 0) { 1480 time.setHour(0); 1481 time.setMinute(0); 1482 time.setSecond(0); 1483 return true; 1484 } 1485 return false; 1486 } 1487 1488 /**********************************************************/ 1489 /* DO NOT USE CONSTANTS FOR UPGRADES, USE STRING LITERALS */ 1490 /**********************************************************/ 1491 1492 /**********************************************************/ 1493 /* 6xx db version is for K release 1494 /**********************************************************/ 1495 upgradeToVersion600(SQLiteDatabase db)1496 private void upgradeToVersion600(SQLiteDatabase db) { 1497 /* 1498 * Changes from version 5xx to 600: 1499 * - add mutator columns to Events & calendars 1500 */ 1501 db.execSQL("ALTER TABLE Events ADD COLUMN mutators TEXT;"); 1502 db.execSQL("ALTER TABLE Calendars ADD COLUMN mutators TEXT;"); 1503 } 1504 1505 /**********************************************************/ 1506 /* 5xx db version is for JB MR1 release 1507 /**********************************************************/ 1508 upgradeToVersion501(SQLiteDatabase db)1509 private void upgradeToVersion501(SQLiteDatabase db) { 1510 /* 1511 * Changes from version 403 to 501: 1512 * - add isOrganizer column to Events table 1513 * - add isPrimary column to Calendars table 1514 */ 1515 db.execSQL("ALTER TABLE Events ADD COLUMN isOrganizer INTEGER;"); 1516 db.execSQL("ALTER TABLE Calendars ADD COLUMN isPrimary INTEGER;"); 1517 } 1518 upgradeToVersion502(SQLiteDatabase db)1519 private void upgradeToVersion502(SQLiteDatabase db) { 1520 /* 1521 * Changes from version 501 to 502: 1522 * - add UID for events added from the RFC 2445 iCalendar format. 1523 */ 1524 db.execSQL("ALTER TABLE Events ADD COLUMN uid2445 TEXT;"); 1525 } 1526 1527 /**********************************************************/ 1528 /* 4xx db version is for J release 1529 /**********************************************************/ 1530 upgradeToVersion403(SQLiteDatabase db)1531 private void upgradeToVersion403(SQLiteDatabase db) { 1532 /* 1533 * Changes from version 402 to 403: 1534 * - add custom app package name and uri Events table 1535 */ 1536 db.execSQL("ALTER TABLE Events ADD COLUMN customAppPackage TEXT;"); 1537 db.execSQL("ALTER TABLE Events ADD COLUMN customAppUri TEXT;"); 1538 } 1539 upgradeToVersion402(SQLiteDatabase db)1540 private void upgradeToVersion402(SQLiteDatabase db) { 1541 /* 1542 * Changes from version 401 to 402: 1543 * - add identity and namespace to Attendees table 1544 */ 1545 db.execSQL("ALTER TABLE Attendees ADD COLUMN attendeeIdentity TEXT;"); 1546 db.execSQL("ALTER TABLE Attendees ADD COLUMN attendeeIdNamespace TEXT;"); 1547 } 1548 1549 /* 1550 * Changes from version 309 to 401: 1551 * Fix repeating events' exceptions with the wrong original_id 1552 */ upgradeToVersion401(SQLiteDatabase db)1553 private void upgradeToVersion401(SQLiteDatabase db) { 1554 db.execSQL("UPDATE events SET original_id=(SELECT _id FROM events inner_events WHERE " + 1555 "inner_events._sync_id=events.original_sync_id AND " + 1556 "inner_events.calendar_id=events.calendar_id) WHERE NOT original_id IS NULL AND " + 1557 "(SELECT calendar_id FROM events ex_events WHERE " + 1558 "ex_events._id=events.original_id) <> calendar_id "); 1559 } 1560 upgradeToVersion400(SQLiteDatabase db)1561 private void upgradeToVersion400(SQLiteDatabase db) { 1562 db.execSQL("DROP TRIGGER IF EXISTS calendar_color_update"); 1563 // CREATE_CALENDAR_COLOR_UPDATE_TRIGGER was inlined 1564 db.execSQL("CREATE TRIGGER " 1565 + "calendar_color_update" + " UPDATE OF " + Calendars.CALENDAR_COLOR_KEY 1566 + " ON " + Tables.CALENDARS + " WHEN new." + Calendars.CALENDAR_COLOR_KEY 1567 + " NOT NULL BEGIN " + "UPDATE " + Tables.CALENDARS 1568 + " SET calendar_color=(SELECT " + Colors.COLOR + " FROM " + Tables.COLORS 1569 + " WHERE " + Colors.ACCOUNT_NAME + "=" + "new." + Calendars.ACCOUNT_NAME + " AND " 1570 + Colors.ACCOUNT_TYPE + "=" + "new." + Calendars.ACCOUNT_TYPE + " AND " 1571 + Colors.COLOR_KEY + "=" + "new." + Calendars.CALENDAR_COLOR_KEY + " AND " 1572 + Colors.COLOR_TYPE + "=" + Colors.TYPE_CALENDAR + ") " 1573 + " WHERE " + Calendars._ID + "=" + "old." + Calendars._ID 1574 + ";" + " END"); 1575 db.execSQL("DROP TRIGGER IF EXISTS event_color_update"); 1576 // CREATE_EVENT_COLOR_UPDATE_TRIGGER was inlined 1577 db.execSQL("CREATE TRIGGER " 1578 + "event_color_update" + " UPDATE OF " + Events.EVENT_COLOR_KEY + " ON " 1579 + Tables.EVENTS + " WHEN new." + Events.EVENT_COLOR_KEY + " NOT NULL BEGIN " 1580 + "UPDATE " + Tables.EVENTS 1581 + " SET eventColor=(SELECT " + Colors.COLOR + " FROM " + Tables.COLORS + " WHERE " 1582 + Colors.ACCOUNT_NAME + "=" + "(SELECT " + Calendars.ACCOUNT_NAME + " FROM " 1583 + Tables.CALENDARS + " WHERE " + Calendars._ID + "=new." + Events.CALENDAR_ID 1584 + ") AND " + Colors.ACCOUNT_TYPE + "=" + "(SELECT " + Calendars.ACCOUNT_TYPE 1585 + " FROM " + Tables.CALENDARS + " WHERE " + Calendars._ID + "=new." 1586 + Events.CALENDAR_ID + ") AND " + Colors.COLOR_KEY + "=" + "new." 1587 + Events.EVENT_COLOR_KEY + " AND " + Colors.COLOR_TYPE + "=" 1588 + Colors.TYPE_EVENT + ") " 1589 + " WHERE " + Events._ID + "=" + "old." + Events._ID + ";" + " END"); 1590 } 1591 upgradeToVersion308(SQLiteDatabase db)1592 private void upgradeToVersion308(SQLiteDatabase db) { 1593 /* 1594 * Changes from version 307 to 308: 1595 * - add Colors table to db 1596 * - add eventColor_index to Events table 1597 * - add calendar_color_index to Calendars table 1598 * - add allowedAttendeeTypes to Calendars table 1599 * - add allowedAvailability to Calendars table 1600 */ 1601 createColorsTable(db); 1602 1603 db.execSQL("ALTER TABLE Calendars ADD COLUMN allowedAvailability TEXT DEFAULT '0,1';"); 1604 db.execSQL("ALTER TABLE Calendars ADD COLUMN allowedAttendeeTypes TEXT DEFAULT '0,1,2';"); 1605 db.execSQL("ALTER TABLE Calendars ADD COLUMN calendar_color_index TEXT;"); 1606 db.execSQL("ALTER TABLE Events ADD COLUMN eventColor_index TEXT;"); 1607 1608 // Default Exchange calendars to be supporting the 'tentative' 1609 // availability as well 1610 db.execSQL("UPDATE Calendars SET allowedAvailability='0,1,2' WHERE _id IN " 1611 + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');"); 1612 1613 // Triggers to update the color stored in an event or a calendar when 1614 // the color_index is changed. 1615 createColorsTriggers(db); 1616 } 1617 upgradeToVersion307(SQLiteDatabase db)1618 private void upgradeToVersion307(SQLiteDatabase db) { 1619 /* 1620 * Changes from version 306 to 307: 1621 * - Changed _id field to AUTOINCREMENT 1622 */ 1623 db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;"); 1624 db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete"); 1625 db.execSQL("DROP TRIGGER IF EXISTS original_sync_update"); 1626 db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex"); 1627 createEventsTable307(db); 1628 1629 String FIELD_LIST = 1630 "_id, " + 1631 "_sync_id, " + 1632 "dirty, " + 1633 "lastSynced," + 1634 "calendar_id, " + 1635 "title, " + 1636 "eventLocation, " + 1637 "description, " + 1638 "eventColor, " + 1639 "eventStatus, " + 1640 "selfAttendeeStatus, " + 1641 "dtstart, " + 1642 "dtend, " + 1643 "eventTimezone, " + 1644 "duration, " + 1645 "allDay, " + 1646 "accessLevel, " + 1647 "availability, " + 1648 "hasAlarm, " + 1649 "hasExtendedProperties, " + 1650 "rrule, " + 1651 "rdate, " + 1652 "exrule, " + 1653 "exdate, " + 1654 "original_id," + 1655 "original_sync_id, " + 1656 "originalInstanceTime, " + 1657 "originalAllDay, " + 1658 "lastDate, " + 1659 "hasAttendeeData, " + 1660 "guestsCanModify, " + 1661 "guestsCanInviteOthers, " + 1662 "guestsCanSeeGuests, " + 1663 "organizer, " + 1664 "deleted, " + 1665 "eventEndTimezone, " + 1666 "sync_data1," + 1667 "sync_data2," + 1668 "sync_data3," + 1669 "sync_data4," + 1670 "sync_data5," + 1671 "sync_data6," + 1672 "sync_data7," + 1673 "sync_data8," + 1674 "sync_data9," + 1675 "sync_data10 "; 1676 1677 // copy fields from old to new 1678 db.execSQL("INSERT INTO Events (" + FIELD_LIST + ") SELECT " + FIELD_LIST + 1679 "FROM Events_Backup;"); 1680 1681 db.execSQL("DROP TABLE Events_Backup;"); 1682 1683 // Trigger to remove data tied to an event when we delete that event. 1684 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " + 1685 "BEGIN " + EVENTS_CLEANUP_TRIGGER_SQL + "END"); 1686 1687 // Trigger to update exceptions when an original event updates its 1688 // _sync_id 1689 db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER); 1690 } 1691 upgradeToVersion306(SQLiteDatabase db)1692 private void upgradeToVersion306(SQLiteDatabase db) { 1693 /* 1694 * The following changes are for google.com accounts only. 1695 * 1696 * Change event id's from ".../private/full/... to .../events/... 1697 * Set Calendars.canPartiallyUpdate to 1 to support partial updates 1698 * Nuke sync state so we re-sync with a fresh etag and edit url 1699 * 1700 * We need to drop the original_sync_update trigger because it fires whenever the 1701 * sync_id field is touched, and dramatically slows this operation. 1702 */ 1703 db.execSQL("DROP TRIGGER IF EXISTS original_sync_update"); 1704 db.execSQL("UPDATE Events SET " 1705 + "_sync_id = REPLACE(_sync_id, '/private/full/', '/events/'), " 1706 + "original_sync_id = REPLACE(original_sync_id, '/private/full/', '/events/') " 1707 + "WHERE _id IN (SELECT Events._id FROM Events " 1708 + "JOIN Calendars ON Events.calendar_id = Calendars._id " 1709 + "WHERE account_type = 'com.google')" 1710 ); 1711 db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER); 1712 1713 db.execSQL("UPDATE Calendars SET canPartiallyUpdate = 1 WHERE account_type = 'com.google'"); 1714 1715 db.execSQL("DELETE FROM _sync_state WHERE account_type = 'com.google'"); 1716 } 1717 upgradeToVersion305(SQLiteDatabase db)1718 private void upgradeToVersion305(SQLiteDatabase db) { 1719 /* 1720 * Changes from version 304 to 305: 1721 * -Add CAL_SYNC columns up to 10 1722 * -Rename Calendars.access_level to calendar_access_level 1723 * -Rename calendars _sync_version to cal_sync7 1724 * -Rename calendars _sync_time to cal_sync8 1725 * -Rename displayName to calendar_displayName 1726 * -Rename _sync_local_id to sync_data2 1727 * -Rename htmlUri to sync_data3 1728 * -Rename events _sync_version to sync_data4 1729 * -Rename events _sync_time to sync_data5 1730 * -Rename commentsUri to sync_data6 1731 * -Migrate Events _sync_mark to sync_data8 1732 * -Change sync_data2 from INTEGER to TEXT 1733 * -Change sync_data8 from INTEGER to TEXT 1734 * -Add SYNC_DATA columns up to 10 1735 * -Add EVENT_COLOR to Events table 1736 */ 1737 1738 // rename old table, create new table with updated layout 1739 db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;"); 1740 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 1741 createCalendarsTable305(db); 1742 1743 // copy fields from old to new 1744 db.execSQL("INSERT INTO Calendars (" + 1745 "_id, " + 1746 "account_name, " + 1747 "account_type, " + 1748 "_sync_id, " + 1749 "cal_sync7, " + // rename from _sync_version 1750 "cal_sync8, " + // rename from _sync_time 1751 "dirty, " + 1752 "name, " + 1753 "calendar_displayName, " + // rename from displayName 1754 "calendar_color, " + 1755 "calendar_access_level, " + // rename from access_level 1756 "visible, " + 1757 "sync_events, " + 1758 "calendar_location, " + 1759 "calendar_timezone, " + 1760 "ownerAccount, " + 1761 "canOrganizerRespond, " + 1762 "canModifyTimeZone, " + 1763 "maxReminders, " + 1764 "allowedReminders, " + 1765 "deleted, " + 1766 "canPartiallyUpdate," + 1767 "cal_sync1, " + 1768 "cal_sync2, " + 1769 "cal_sync3, " + 1770 "cal_sync4, " + 1771 "cal_sync5, " + 1772 "cal_sync6) " + 1773 "SELECT " + 1774 "_id, " + 1775 "account_name, " + 1776 "account_type, " + 1777 "_sync_id, " + 1778 "_sync_version, " + 1779 "_sync_time, " + 1780 "dirty, " + 1781 "name, " + 1782 "displayName, " + 1783 "calendar_color, " + 1784 "access_level, " + 1785 "visible, " + 1786 "sync_events, " + 1787 "calendar_location, " + 1788 "calendar_timezone, " + 1789 "ownerAccount, " + 1790 "canOrganizerRespond, " + 1791 "canModifyTimeZone, " + 1792 "maxReminders, " + 1793 "allowedReminders, " + 1794 "deleted, " + 1795 "canPartiallyUpdate," + 1796 "cal_sync1, " + 1797 "cal_sync2, " + 1798 "cal_sync3, " + 1799 "cal_sync4, " + 1800 "cal_sync5, " + 1801 "cal_sync6 " + 1802 "FROM Calendars_Backup;"); 1803 1804 // drop the old table 1805 db.execSQL("DROP TABLE Calendars_Backup;"); 1806 1807 db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;"); 1808 db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete"); 1809 db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex"); 1810 // 305 and 307 can share the same createEventsTable implementation, because the 1811 // addition of "autoincrement" to _ID doesn't affect the upgrade path. (Note that 1812 // much older databases may also already have autoincrement set because the change 1813 // was back-ported.) 1814 createEventsTable307(db); 1815 1816 // copy fields from old to new 1817 db.execSQL("INSERT INTO Events (" + 1818 "_id, " + 1819 "_sync_id, " + 1820 "sync_data4, " + // renamed from _sync_version 1821 "sync_data5, " + // renamed from _sync_time 1822 "sync_data2, " + // renamed from _sync_local_id 1823 "dirty, " + 1824 "sync_data8, " + // renamed from _sync_mark 1825 "calendar_id, " + 1826 "sync_data3, " + // renamed from htmlUri 1827 "title, " + 1828 "eventLocation, " + 1829 "description, " + 1830 "eventStatus, " + 1831 "selfAttendeeStatus, " + 1832 "sync_data6, " + // renamed from commentsUri 1833 "dtstart, " + 1834 "dtend, " + 1835 "eventTimezone, " + 1836 "eventEndTimezone, " + 1837 "duration, " + 1838 "allDay, " + 1839 "accessLevel, " + 1840 "availability, " + 1841 "hasAlarm, " + 1842 "hasExtendedProperties, " + 1843 "rrule, " + 1844 "rdate, " + 1845 "exrule, " + 1846 "exdate, " + 1847 "original_id," + 1848 "original_sync_id, " + 1849 "originalInstanceTime, " + 1850 "originalAllDay, " + 1851 "lastDate, " + 1852 "hasAttendeeData, " + 1853 "guestsCanModify, " + 1854 "guestsCanInviteOthers, " + 1855 "guestsCanSeeGuests, " + 1856 "organizer, " + 1857 "deleted, " + 1858 "sync_data7," + 1859 "lastSynced," + 1860 "sync_data1) " + 1861 1862 "SELECT " + 1863 "_id, " + 1864 "_sync_id, " + 1865 "_sync_version, " + 1866 "_sync_time, " + 1867 "_sync_local_id, " + 1868 "dirty, " + 1869 "_sync_mark, " + 1870 "calendar_id, " + 1871 "htmlUri, " + 1872 "title, " + 1873 "eventLocation, " + 1874 "description, " + 1875 "eventStatus, " + 1876 "selfAttendeeStatus, " + 1877 "commentsUri, " + 1878 "dtstart, " + 1879 "dtend, " + 1880 "eventTimezone, " + 1881 "eventEndTimezone, " + 1882 "duration, " + 1883 "allDay, " + 1884 "accessLevel, " + 1885 "availability, " + 1886 "hasAlarm, " + 1887 "hasExtendedProperties, " + 1888 "rrule, " + 1889 "rdate, " + 1890 "exrule, " + 1891 "exdate, " + 1892 "original_id," + 1893 "original_sync_id, " + 1894 "originalInstanceTime, " + 1895 "originalAllDay, " + 1896 "lastDate, " + 1897 "hasAttendeeData, " + 1898 "guestsCanModify, " + 1899 "guestsCanInviteOthers, " + 1900 "guestsCanSeeGuests, " + 1901 "organizer, " + 1902 "deleted, " + 1903 "sync_data7," + 1904 "lastSynced," + 1905 "sync_data1 " + 1906 1907 "FROM Events_Backup;" 1908 ); 1909 1910 db.execSQL("DROP TABLE Events_Backup;"); 1911 1912 // Trigger to remove data tied to an event when we delete that event. 1913 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " + 1914 "BEGIN " + 1915 EVENTS_CLEANUP_TRIGGER_SQL + 1916 "END"); 1917 1918 // Trigger to update exceptions when an original event updates its 1919 // _sync_id 1920 db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER); 1921 } 1922 upgradeToVersion304(SQLiteDatabase db)1923 private void upgradeToVersion304(SQLiteDatabase db) { 1924 /* 1925 * Changes from version 303 to 304: 1926 * - add canPartiallyUpdate to Calendars table 1927 * - add sync_data7 to Calendars to Events table 1928 * - add lastSynced to Calendars to Events table 1929 */ 1930 db.execSQL("ALTER TABLE Calendars ADD COLUMN canPartiallyUpdate INTEGER DEFAULT 0;"); 1931 db.execSQL("ALTER TABLE Events ADD COLUMN sync_data7 TEXT;"); 1932 db.execSQL("ALTER TABLE Events ADD COLUMN lastSynced INTEGER DEFAULT 0;"); 1933 } 1934 upgradeToVersion303(SQLiteDatabase db)1935 private void upgradeToVersion303(SQLiteDatabase db) { 1936 /* 1937 * Changes from version 302 to 303: 1938 * - change SYNCx columns to CAL_SYNCx 1939 */ 1940 1941 // rename old table, create new table with updated layout 1942 db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;"); 1943 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 1944 createCalendarsTable303(db); 1945 1946 // copy fields from old to new 1947 db.execSQL("INSERT INTO Calendars (" + 1948 "_id, " + 1949 "account_name, " + 1950 "account_type, " + 1951 "_sync_id, " + 1952 "_sync_version, " + 1953 "_sync_time, " + 1954 "dirty, " + 1955 "name, " + 1956 "displayName, " + 1957 "calendar_color, " + 1958 "access_level, " + 1959 "visible, " + 1960 "sync_events, " + 1961 "calendar_location, " + 1962 "calendar_timezone, " + 1963 "ownerAccount, " + 1964 "canOrganizerRespond, " + 1965 "canModifyTimeZone, " + 1966 "maxReminders, " + 1967 "allowedReminders, " + 1968 "deleted, " + 1969 "cal_sync1, " + // rename from sync1 1970 "cal_sync2, " + // rename from sync2 1971 "cal_sync3, " + // rename from sync3 1972 "cal_sync4, " + // rename from sync4 1973 "cal_sync5, " + // rename from sync5 1974 "cal_sync6) " + // rename from sync6 1975 "SELECT " + 1976 "_id, " + 1977 "account_name, " + 1978 "account_type, " + 1979 "_sync_id, " + 1980 "_sync_version, " + 1981 "_sync_time, " + 1982 "dirty, " + 1983 "name, " + 1984 "displayName, " + 1985 "calendar_color, " + 1986 "access_level, " + 1987 "visible, " + 1988 "sync_events, " + 1989 "calendar_location, " + 1990 "calendar_timezone, " + 1991 "ownerAccount, " + 1992 "canOrganizerRespond, " + 1993 "canModifyTimeZone, " + 1994 "maxReminders, " + 1995 "allowedReminders," + 1996 "deleted, " + 1997 "sync1, " + 1998 "sync2, " + 1999 "sync3, " + 2000 "sync4," + 2001 "sync5," + 2002 "sync6 " + 2003 "FROM Calendars_Backup;" 2004 ); 2005 2006 // drop the old table 2007 db.execSQL("DROP TABLE Calendars_Backup;"); 2008 } 2009 upgradeToVersion302(SQLiteDatabase db)2010 private void upgradeToVersion302(SQLiteDatabase db) { 2011 /* 2012 * Changes from version 301 to 302 2013 * - Move Exchange eventEndTimezone values to SYNC_DATA1 2014 */ 2015 db.execSQL("UPDATE Events SET sync_data1=eventEndTimezone WHERE calendar_id IN " 2016 + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');"); 2017 2018 db.execSQL("UPDATE Events SET eventEndTimezone=NULL WHERE calendar_id IN " 2019 + "(SELECT _id FROM Calendars WHERE account_type='com.android.exchange');"); 2020 } 2021 upgradeToVersion301(SQLiteDatabase db)2022 private void upgradeToVersion301(SQLiteDatabase db) { 2023 /* 2024 * Changes from version 300 to 301 2025 * - Added original_id column to Events table 2026 * - Added triggers to keep original_id and original_sync_id in sync 2027 */ 2028 2029 db.execSQL("DROP TRIGGER IF EXISTS " + SYNC_ID_UPDATE_TRIGGER_NAME + ";"); 2030 2031 db.execSQL("ALTER TABLE Events ADD COLUMN original_id INTEGER;"); 2032 2033 // Fill in the original_id for all events that have an original_sync_id 2034 db.execSQL("UPDATE Events set original_id=" + 2035 "(SELECT Events2._id FROM Events AS Events2 " + 2036 "WHERE Events2._sync_id=Events.original_sync_id) " + 2037 "WHERE Events.original_sync_id NOT NULL"); 2038 // Trigger to update exceptions when an original event updates its 2039 // _sync_id 2040 db.execSQL(CREATE_SYNC_ID_UPDATE_TRIGGER); 2041 } 2042 upgradeToVersion300(SQLiteDatabase db)2043 private void upgradeToVersion300(SQLiteDatabase db) { 2044 2045 /* 2046 * Changes from version 205 to 300: 2047 * - rename _sync_account to account_name in Calendars table 2048 * - remove _sync_account from Events table 2049 * - rename _sync_account_type to account_type in Calendars table 2050 * - remove _sync_account_type from Events table 2051 * - rename _sync_dirty to dirty in Calendars/Events table 2052 * - rename color to calendar_color in Calendars table 2053 * - rename location to calendar_location in Calendars table 2054 * - rename timezone to calendar_timezone in Calendars table 2055 * - add allowedReminders in Calendars table 2056 * - rename visibility to accessLevel in Events table 2057 * - rename transparency to availability in Events table 2058 * - rename originalEvent to original_sync_id in Events table 2059 * - remove dtstart2 and dtend2 from Events table 2060 * - rename syncAdapterData to sync_data1 in Events table 2061 */ 2062 2063 // rename old table, create new table with updated layout 2064 db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;"); 2065 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup;"); 2066 createCalendarsTable300(db); 2067 2068 // copy fields from old to new 2069 db.execSQL("INSERT INTO Calendars (" + 2070 "_id, " + 2071 "account_name, " + // rename from _sync_account 2072 "account_type, " + // rename from _sync_account_type 2073 "_sync_id, " + 2074 "_sync_version, " + 2075 "_sync_time, " + 2076 "dirty, " + // rename from _sync_dirty 2077 "name, " + 2078 "displayName, " + 2079 "calendar_color, " + // rename from color 2080 "access_level, " + 2081 "visible, " + 2082 "sync_events, " + 2083 "calendar_location, " + // rename from location 2084 "calendar_timezone, " + // rename from timezone 2085 "ownerAccount, " + 2086 "canOrganizerRespond, " + 2087 "canModifyTimeZone, " + 2088 "maxReminders, " + 2089 "allowedReminders," + 2090 "deleted, " + 2091 "sync1, " + 2092 "sync2, " + 2093 "sync3, " + 2094 "sync4," + 2095 "sync5," + 2096 "sync6) " + 2097 2098 "SELECT " + 2099 "_id, " + 2100 "_sync_account, " + 2101 "_sync_account_type, " + 2102 "_sync_id, " + 2103 "_sync_version, " + 2104 "_sync_time, " + 2105 "_sync_dirty, " + 2106 "name, " + 2107 "displayName, " + 2108 "color, " + 2109 "access_level, " + 2110 "visible, " + 2111 "sync_events, " + 2112 "location, " + 2113 "timezone, " + 2114 "ownerAccount, " + 2115 "canOrganizerRespond, " + 2116 "canModifyTimeZone, " + 2117 "maxReminders, " + 2118 "'0,1,2,3'," + 2119 "deleted, " + 2120 "sync1, " + 2121 "sync2, " + 2122 "sync3, " + 2123 "sync4, " + 2124 "sync5, " + 2125 "sync6 " + 2126 "FROM Calendars_Backup;" 2127 ); 2128 2129 /* expand the set of allowed reminders for Google calendars to include email */ 2130 db.execSQL("UPDATE Calendars SET allowedReminders = '0,1,2' " + 2131 "WHERE account_type = 'com.google'"); 2132 2133 // drop the old table 2134 db.execSQL("DROP TABLE Calendars_Backup;"); 2135 2136 db.execSQL("ALTER TABLE Events RENAME TO Events_Backup;"); 2137 db.execSQL("DROP TRIGGER IF EXISTS events_insert"); 2138 db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete"); 2139 db.execSQL("DROP INDEX IF EXISTS eventSyncAccountAndIdIndex"); 2140 db.execSQL("DROP INDEX IF EXISTS eventsCalendarIdIndex"); 2141 createEventsTable300(db); 2142 2143 // copy fields from old to new 2144 db.execSQL("INSERT INTO Events (" + 2145 "_id, " + 2146 "_sync_id, " + 2147 "_sync_version, " + 2148 "_sync_time, " + 2149 "_sync_local_id, " + 2150 "dirty, " + // renamed from _sync_dirty 2151 "_sync_mark, " + 2152 "calendar_id, " + 2153 "htmlUri, " + 2154 "title, " + 2155 "eventLocation, " + 2156 "description, " + 2157 "eventStatus, " + 2158 "selfAttendeeStatus, " + 2159 "commentsUri, " + 2160 "dtstart, " + 2161 "dtend, " + 2162 "eventTimezone, " + 2163 "eventEndTimezone, " + // renamed from eventTimezone2 2164 "duration, " + 2165 "allDay, " + 2166 "accessLevel, " + // renamed from visibility 2167 "availability, " + // renamed from transparency 2168 "hasAlarm, " + 2169 "hasExtendedProperties, " + 2170 "rrule, " + 2171 "rdate, " + 2172 "exrule, " + 2173 "exdate, " + 2174 "original_sync_id, " + // renamed from originalEvent 2175 "originalInstanceTime, " + 2176 "originalAllDay, " + 2177 "lastDate, " + 2178 "hasAttendeeData, " + 2179 "guestsCanModify, " + 2180 "guestsCanInviteOthers, " + 2181 "guestsCanSeeGuests, " + 2182 "organizer, " + 2183 "deleted, " + 2184 "sync_data1) " + // renamed from syncAdapterData 2185 2186 "SELECT " + 2187 "_id, " + 2188 "_sync_id, " + 2189 "_sync_version, " + 2190 "_sync_time, " + 2191 "_sync_local_id, " + 2192 "_sync_dirty, " + 2193 "_sync_mark, " + 2194 "calendar_id, " + 2195 "htmlUri, " + 2196 "title, " + 2197 "eventLocation, " + 2198 "description, " + 2199 "eventStatus, " + 2200 "selfAttendeeStatus, " + 2201 "commentsUri, " + 2202 "dtstart, " + 2203 "dtend, " + 2204 "eventTimezone, " + 2205 "eventTimezone2, " + 2206 "duration, " + 2207 "allDay, " + 2208 "visibility, " + 2209 "transparency, " + 2210 "hasAlarm, " + 2211 "hasExtendedProperties, " + 2212 "rrule, " + 2213 "rdate, " + 2214 "exrule, " + 2215 "exdate, " + 2216 "originalEvent, " + 2217 "originalInstanceTime, " + 2218 "originalAllDay, " + 2219 "lastDate, " + 2220 "hasAttendeeData, " + 2221 "guestsCanModify, " + 2222 "guestsCanInviteOthers, " + 2223 "guestsCanSeeGuests, " + 2224 "organizer, " + 2225 "deleted, " + 2226 "syncAdapterData " + 2227 2228 "FROM Events_Backup;" 2229 ); 2230 2231 db.execSQL("DROP TABLE Events_Backup;"); 2232 2233 // Trigger to remove data tied to an event when we delete that event. 2234 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON " + Tables.EVENTS + " " + 2235 "BEGIN " + 2236 EVENTS_CLEANUP_TRIGGER_SQL + 2237 "END"); 2238 2239 } 2240 upgradeToVersion205(SQLiteDatabase db)2241 private void upgradeToVersion205(SQLiteDatabase db) { 2242 /* 2243 * Changes from version 204 to 205: 2244 * - rename+reorder "_sync_mark" to "sync6" (and change type from INTEGER to TEXT) 2245 * - rename "selected" to "visible" 2246 * - rename "organizerCanRespond" to "canOrganizerRespond" 2247 * - add "canModifyTimeZone" 2248 * - add "maxReminders" 2249 * - remove "_sync_local_id" (a/k/a _SYNC_DATA) 2250 */ 2251 2252 // rename old table, create new table with updated layout 2253 db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;"); 2254 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 2255 createCalendarsTable205(db); 2256 2257 // copy fields from old to new 2258 db.execSQL("INSERT INTO Calendars (" + 2259 "_id, " + 2260 "_sync_account, " + 2261 "_sync_account_type, " + 2262 "_sync_id, " + 2263 "_sync_version, " + 2264 "_sync_time, " + 2265 "_sync_dirty, " + 2266 "name, " + 2267 "displayName, " + 2268 "color, " + 2269 "access_level, " + 2270 "visible, " + // rename from "selected" 2271 "sync_events, " + 2272 "location, " + 2273 "timezone, " + 2274 "ownerAccount, " + 2275 "canOrganizerRespond, " + // rename from "organizerCanRespond" 2276 "canModifyTimeZone, " + 2277 "maxReminders, " + 2278 "deleted, " + 2279 "sync1, " + 2280 "sync2, " + 2281 "sync3, " + 2282 "sync4," + 2283 "sync5," + 2284 "sync6) " + // rename/reorder from _sync_mark 2285 "SELECT " + 2286 "_id, " + 2287 "_sync_account, " + 2288 "_sync_account_type, " + 2289 "_sync_id, " + 2290 "_sync_version, " + 2291 "_sync_time, " + 2292 "_sync_dirty, " + 2293 "name, " + 2294 "displayName, " + 2295 "color, " + 2296 "access_level, " + 2297 "selected, " + 2298 "sync_events, " + 2299 "location, " + 2300 "timezone, " + 2301 "ownerAccount, " + 2302 "organizerCanRespond, " + 2303 "1, " + 2304 "5, " + 2305 "deleted, " + 2306 "sync1, " + 2307 "sync2, " + 2308 "sync3, " + 2309 "sync4, " + 2310 "sync5, " + 2311 "_sync_mark " + 2312 "FROM Calendars_Backup;" 2313 ); 2314 2315 // set these fields appropriately for Exchange events 2316 db.execSQL("UPDATE Calendars SET canModifyTimeZone=0, maxReminders=1 " + 2317 "WHERE _sync_account_type='com.android.exchange'"); 2318 2319 // drop the old table 2320 db.execSQL("DROP TABLE Calendars_Backup;"); 2321 } 2322 upgradeToVersion203(SQLiteDatabase db)2323 private void upgradeToVersion203(SQLiteDatabase db) { 2324 // Same as Gingerbread version 100 2325 Cursor cursor = db.rawQuery("SELECT value FROM CalendarCache WHERE key=?", 2326 new String[] {"timezoneDatabaseVersion"}); 2327 2328 String oldTimezoneDbVersion = null; 2329 if (cursor != null) { 2330 try { 2331 if (cursor.moveToNext()) { 2332 oldTimezoneDbVersion = cursor.getString(0); 2333 cursor.close(); 2334 cursor = null; 2335 // Also clean the CalendarCache table 2336 db.execSQL("DELETE FROM CalendarCache;"); 2337 } 2338 } finally { 2339 if (cursor != null) { 2340 cursor.close(); 2341 } 2342 } 2343 } 2344 initCalendarCacheTable203(db, oldTimezoneDbVersion); 2345 2346 // Same as Gingerbread version 101 2347 updateCalendarCacheTableTo203(db); 2348 } 2349 upgradeToVersion202(SQLiteDatabase db)2350 private void upgradeToVersion202(SQLiteDatabase db) { 2351 // We will drop the "hidden" column from the calendar schema and add the "sync5" column 2352 db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;"); 2353 2354 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 2355 createCalendarsTable202(db); 2356 2357 // Populate the new Calendars table and put into the "sync5" column the value of the 2358 // old "hidden" column 2359 db.execSQL("INSERT INTO Calendars (" + 2360 "_id, " + 2361 "_sync_account, " + 2362 "_sync_account_type, " + 2363 "_sync_id, " + 2364 "_sync_version, " + 2365 "_sync_time, " + 2366 "_sync_local_id, " + 2367 "_sync_dirty, " + 2368 "_sync_mark, " + 2369 "name, " + 2370 "displayName, " + 2371 "color, " + 2372 "access_level, " + 2373 "selected, " + 2374 "sync_events, " + 2375 "location, " + 2376 "timezone, " + 2377 "ownerAccount, " + 2378 "organizerCanRespond, " + 2379 "deleted, " + 2380 "sync1, " + 2381 "sync2, " + 2382 "sync3, " + 2383 "sync4," + 2384 "sync5) " + 2385 "SELECT " + 2386 "_id, " + 2387 "_sync_account, " + 2388 "_sync_account_type, " + 2389 "_sync_id, " + 2390 "_sync_version, " + 2391 "_sync_time, " + 2392 "_sync_local_id, " + 2393 "_sync_dirty, " + 2394 "_sync_mark, " + 2395 "name, " + 2396 "displayName, " + 2397 "color, " + 2398 "access_level, " + 2399 "selected, " + 2400 "sync_events, " + 2401 "location, " + 2402 "timezone, " + 2403 "ownerAccount, " + 2404 "organizerCanRespond, " + 2405 "deleted, " + 2406 "sync1, " + 2407 "sync2, " + 2408 "sync3, " + 2409 "sync4, " + 2410 "hidden " + 2411 "FROM Calendars_Backup;" 2412 ); 2413 2414 // Drop the backup table 2415 db.execSQL("DROP TABLE Calendars_Backup;"); 2416 } 2417 upgradeToVersion201(SQLiteDatabase db)2418 private void upgradeToVersion201(SQLiteDatabase db) { 2419 db.execSQL("ALTER TABLE Calendars ADD COLUMN sync4 TEXT;"); 2420 } 2421 upgradeToVersion200(SQLiteDatabase db)2422 private void upgradeToVersion200(SQLiteDatabase db) { 2423 // we cannot use here a Calendar.Calendars,URL constant for "url" as we are trying to make 2424 // it disappear so we are keeping the hardcoded name "url" in all the SQLs 2425 db.execSQL("ALTER TABLE Calendars RENAME TO Calendars_Backup;"); 2426 2427 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 2428 createCalendarsTable200(db); 2429 2430 // Populate the new Calendars table except the SYNC2 / SYNC3 columns 2431 db.execSQL("INSERT INTO Calendars (" + 2432 "_id, " + 2433 "_sync_account, " + 2434 "_sync_account_type, " + 2435 "_sync_id, " + 2436 "_sync_version, " + 2437 "_sync_time, " + 2438 "_sync_local_id, " + 2439 "_sync_dirty, " + 2440 "_sync_mark, " + 2441 "name, " + 2442 "displayName, " + 2443 "color, " + 2444 "access_level, " + 2445 "selected, " + 2446 "sync_events, " + 2447 "location, " + 2448 "timezone, " + 2449 "ownerAccount, " + 2450 "organizerCanRespond, " + 2451 "deleted, " + 2452 "sync1) " + 2453 "SELECT " + 2454 "_id, " + 2455 "_sync_account, " + 2456 "_sync_account_type, " + 2457 "_sync_id, " + 2458 "_sync_version, " + 2459 "_sync_time, " + 2460 "_sync_local_id, " + 2461 "_sync_dirty, " + 2462 "_sync_mark, " + 2463 "name, " + 2464 "displayName, " + 2465 "color, " + 2466 "access_level, " + 2467 "selected, " + 2468 "sync_events, " + 2469 "location, " + 2470 "timezone, " + 2471 "ownerAccount, " + 2472 "organizerCanRespond, " + 2473 "0, " + 2474 "url " + 2475 "FROM Calendars_Backup;" 2476 ); 2477 2478 // Populate SYNC2 and SYNC3 columns - SYNC1 represent the old "url" column 2479 // We will need to iterate over all the "com.google" type of calendars 2480 String selectSql = "SELECT _id, url" + 2481 " FROM Calendars_Backup" + 2482 " WHERE _sync_account_type='com.google'" + 2483 " AND url IS NOT NULL;"; 2484 2485 String updateSql = "UPDATE Calendars SET " + 2486 "sync2=?, " + // edit Url 2487 "sync3=? " + // self Url 2488 "WHERE _id=?;"; 2489 2490 Cursor cursor = db.rawQuery(selectSql, null /* selection args */); 2491 if (cursor != null) { 2492 try { 2493 if (cursor.getCount() > 0) { 2494 Object[] bindArgs = new Object[3]; 2495 while (cursor.moveToNext()) { 2496 Long id = cursor.getLong(0); 2497 String url = cursor.getString(1); 2498 String selfUrl = getSelfUrlFromEventsUrl(url); 2499 String editUrl = getEditUrlFromEventsUrl(url); 2500 2501 bindArgs[0] = editUrl; 2502 bindArgs[1] = selfUrl; 2503 bindArgs[2] = id; 2504 2505 db.execSQL(updateSql, bindArgs); 2506 } 2507 } 2508 } finally { 2509 cursor.close(); 2510 } 2511 } 2512 2513 // Drop the backup table 2514 db.execSQL("DROP TABLE Calendars_Backup;"); 2515 } 2516 2517 @VisibleForTesting upgradeToVersion69(SQLiteDatabase db)2518 public static void upgradeToVersion69(SQLiteDatabase db) { 2519 // Clean up allDay events which could be in an invalid state from an earlier version 2520 // Some allDay events had hour, min, sec not set to zero, which throws elsewhere. This 2521 // will go through the allDay events and make sure they have proper values and are in the 2522 // correct timezone. Verifies that dtstart and dtend are in UTC and at midnight, that 2523 // eventTimezone is set to UTC, tries to make sure duration is in days, and that dtstart2 2524 // and dtend2 are at midnight in their timezone. 2525 final String sql = "SELECT _id, " + 2526 "dtstart, " + 2527 "dtend, " + 2528 "duration, " + 2529 "dtstart2, " + 2530 "dtend2, " + 2531 "eventTimezone, " + 2532 "eventTimezone2, " + 2533 "rrule " + 2534 "FROM Events " + 2535 "WHERE allDay=?"; 2536 Cursor cursor = db.rawQuery(sql, new String[] {"1"}); 2537 if (cursor != null) { 2538 try { 2539 String timezone; 2540 String timezone2; 2541 String duration; 2542 Long dtstart; 2543 Long dtstart2; 2544 Long dtend; 2545 Long dtend2; 2546 Time time = new Time(); 2547 Long id; 2548 // some things need to be in utc so we call this frequently, cache to make faster 2549 final String utc = Time.TIMEZONE_UTC; 2550 while (cursor.moveToNext()) { 2551 String rrule = cursor.getString(8); 2552 id = cursor.getLong(0); 2553 dtstart = cursor.getLong(1); 2554 dtstart2 = null; 2555 timezone = cursor.getString(6); 2556 timezone2 = cursor.getString(7); 2557 duration = cursor.getString(3); 2558 2559 if (TextUtils.isEmpty(rrule)) { 2560 // For non-recurring events dtstart and dtend should both have values 2561 // and duration should be null. 2562 dtend = cursor.getLong(2); 2563 dtend2 = null; 2564 // Since we made all three of these at the same time if timezone2 exists 2565 // so should dtstart2 and dtend2. 2566 if(!TextUtils.isEmpty(timezone2)) { 2567 dtstart2 = cursor.getLong(4); 2568 dtend2 = cursor.getLong(5); 2569 } 2570 2571 boolean update = false; 2572 if (!TextUtils.equals(timezone, utc)) { 2573 update = true; 2574 timezone = utc; 2575 } 2576 2577 time.clear(timezone); 2578 update |= fixAllDayTime(time, timezone, dtstart); 2579 dtstart = time.normalize(); 2580 2581 time.clear(timezone); 2582 update |= fixAllDayTime(time, timezone, dtend); 2583 dtend = time.normalize(); 2584 2585 if (dtstart2 != null) { 2586 time.clear(timezone2); 2587 update |= fixAllDayTime(time, timezone2, dtstart2); 2588 dtstart2 = time.normalize(); 2589 } 2590 2591 if (dtend2 != null) { 2592 time.clear(timezone2); 2593 update |= fixAllDayTime(time, timezone2, dtend2); 2594 dtend2 = time.normalize(); 2595 } 2596 2597 if (!TextUtils.isEmpty(duration)) { 2598 update = true; 2599 } 2600 2601 if (update) { 2602 // enforce duration being null 2603 db.execSQL("UPDATE Events SET " + 2604 "dtstart=?, " + 2605 "dtend=?, " + 2606 "dtstart2=?, " + 2607 "dtend2=?, " + 2608 "duration=?, " + 2609 "eventTimezone=?, " + 2610 "eventTimezone2=? " + 2611 "WHERE _id=?", 2612 new Object[] { 2613 dtstart, 2614 dtend, 2615 dtstart2, 2616 dtend2, 2617 null, 2618 timezone, 2619 timezone2, 2620 id} 2621 ); 2622 } 2623 2624 } else { 2625 // For recurring events only dtstart and duration should be used. 2626 // We ignore dtend since it will be overwritten if the event changes to a 2627 // non-recurring event and won't be used otherwise. 2628 if(!TextUtils.isEmpty(timezone2)) { 2629 dtstart2 = cursor.getLong(4); 2630 } 2631 2632 boolean update = false; 2633 if (!TextUtils.equals(timezone, utc)) { 2634 update = true; 2635 timezone = utc; 2636 } 2637 2638 time.clear(timezone); 2639 update |= fixAllDayTime(time, timezone, dtstart); 2640 dtstart = time.normalize(); 2641 2642 if (dtstart2 != null) { 2643 time.clear(timezone2); 2644 update |= fixAllDayTime(time, timezone2, dtstart2); 2645 dtstart2 = time.normalize(); 2646 } 2647 2648 if (TextUtils.isEmpty(duration)) { 2649 // If duration was missing assume a 1 day duration 2650 duration = "P1D"; 2651 update = true; 2652 } else { 2653 int len = duration.length(); 2654 // TODO fix durations in other formats as well 2655 if (duration.charAt(0) == 'P' && 2656 duration.charAt(len - 1) == 'S') { 2657 int seconds = Integer.parseInt(duration.substring(1, len - 1)); 2658 int days = (seconds + DAY_IN_SECONDS - 1) / DAY_IN_SECONDS; 2659 duration = "P" + days + "D"; 2660 update = true; 2661 } 2662 } 2663 2664 if (update) { 2665 // If there were other problems also enforce dtend being null 2666 db.execSQL("UPDATE Events SET " + 2667 "dtstart=?, " + 2668 "dtend=?, " + 2669 "dtstart2=?, " + 2670 "dtend2=?, " + 2671 "duration=?," + 2672 "eventTimezone=?, " + 2673 "eventTimezone2=? " + 2674 "WHERE _id=?", 2675 new Object[] { 2676 dtstart, 2677 null, 2678 dtstart2, 2679 null, 2680 duration, 2681 timezone, 2682 timezone2, 2683 id} 2684 ); 2685 } 2686 } 2687 } 2688 } finally { 2689 cursor.close(); 2690 } 2691 } 2692 } 2693 upgradeToVersion66(SQLiteDatabase db)2694 private void upgradeToVersion66(SQLiteDatabase db) { 2695 // Add a column to indicate whether the event organizer can respond to his own events 2696 // The UI should not show attendee status for events in calendars with this column = 0 2697 db.execSQL("ALTER TABLE Calendars" + 2698 " ADD COLUMN organizerCanRespond INTEGER NOT NULL DEFAULT 1;"); 2699 } 2700 upgradeToVersion64(SQLiteDatabase db)2701 private void upgradeToVersion64(SQLiteDatabase db) { 2702 // Add a column that may be used by sync adapters 2703 db.execSQL("ALTER TABLE Events" + 2704 " ADD COLUMN syncAdapterData TEXT;"); 2705 } 2706 upgradeToVersion62(SQLiteDatabase db)2707 private void upgradeToVersion62(SQLiteDatabase db) { 2708 // New columns are to transition to having allDay events in the local timezone 2709 db.execSQL("ALTER TABLE Events" + 2710 " ADD COLUMN dtstart2 INTEGER;"); 2711 db.execSQL("ALTER TABLE Events" + 2712 " ADD COLUMN dtend2 INTEGER;"); 2713 db.execSQL("ALTER TABLE Events" + 2714 " ADD COLUMN eventTimezone2 TEXT;"); 2715 2716 String[] allDayBit = new String[] {"0"}; 2717 // Copy over all the data that isn't an all day event. 2718 db.execSQL("UPDATE Events SET " + 2719 "dtstart2=dtstart," + 2720 "dtend2=dtend," + 2721 "eventTimezone2=eventTimezone " + 2722 "WHERE allDay=?;", 2723 allDayBit /* selection args */); 2724 2725 // "cursor" iterates over all the calendars 2726 allDayBit[0] = "1"; 2727 Cursor cursor = db.rawQuery("SELECT Events._id," + 2728 "dtstart," + 2729 "dtend," + 2730 "eventTimezone," + 2731 "timezone " + 2732 "FROM Events INNER JOIN Calendars " + 2733 "WHERE Events.calendar_id=Calendars._id" + 2734 " AND allDay=?", 2735 allDayBit /* selection args */); 2736 2737 Time oldTime = new Time(); 2738 Time newTime = new Time(); 2739 // Update the allday events in the new columns 2740 if (cursor != null) { 2741 try { 2742 String[] newData = new String[4]; 2743 cursor.moveToPosition(-1); 2744 while (cursor.moveToNext()) { 2745 long id = cursor.getLong(0); // Order from query above 2746 long dtstart = cursor.getLong(1); 2747 long dtend = cursor.getLong(2); 2748 String eTz = cursor.getString(3); // current event timezone 2749 String tz = cursor.getString(4); // Calendar timezone 2750 //If there's no timezone for some reason use UTC by default. 2751 if(eTz == null) { 2752 eTz = Time.TIMEZONE_UTC; 2753 } 2754 2755 // Convert start time for all day events into the timezone of their calendar 2756 oldTime.clear(eTz); 2757 oldTime.set(dtstart); 2758 newTime.clear(tz); 2759 newTime.set(oldTime.getDay(), oldTime.getMonth(), oldTime.getYear()); 2760 newTime.normalize(); 2761 dtstart = newTime.toMillis(); 2762 2763 // Convert end time for all day events into the timezone of their calendar 2764 oldTime.clear(eTz); 2765 oldTime.set(dtend); 2766 newTime.clear(tz); 2767 newTime.set(oldTime.getDay(), oldTime.getMonth(), oldTime.getYear()); 2768 newTime.normalize(); 2769 dtend = newTime.toMillis(); 2770 2771 newData[0] = String.valueOf(dtstart); 2772 newData[1] = String.valueOf(dtend); 2773 newData[2] = tz; 2774 newData[3] = String.valueOf(id); 2775 db.execSQL("UPDATE Events SET " + 2776 "dtstart2=?, " + 2777 "dtend2=?, " + 2778 "eventTimezone2=? " + 2779 "WHERE _id=?", 2780 newData); 2781 } 2782 } finally { 2783 cursor.close(); 2784 } 2785 } 2786 } 2787 upgradeToVersion61(SQLiteDatabase db)2788 private void upgradeToVersion61(SQLiteDatabase db) { 2789 db.execSQL("DROP TABLE IF EXISTS CalendarCache;"); 2790 2791 // IF NOT EXISTS should be normal pattern for table creation 2792 db.execSQL("CREATE TABLE IF NOT EXISTS CalendarCache (" + 2793 "_id INTEGER PRIMARY KEY," + 2794 "key TEXT NOT NULL," + 2795 "value TEXT" + 2796 ");"); 2797 2798 db.execSQL("INSERT INTO CalendarCache (" + 2799 "key, " + 2800 "value) VALUES (" + 2801 "'timezoneDatabaseVersion'," + 2802 "'2009s'" + 2803 ");"); 2804 } 2805 upgradeToVersion60(SQLiteDatabase db)2806 private void upgradeToVersion60(SQLiteDatabase db) { 2807 // Switch to CalendarProvider2 2808 upgradeSyncState(db); 2809 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 2810 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " + 2811 "BEGIN " + 2812 ("DELETE FROM Events" + 2813 " WHERE calendar_id=old._id;") + 2814 "END"); 2815 db.execSQL("ALTER TABLE Events" + 2816 " ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;"); 2817 db.execSQL("DROP TRIGGER IF EXISTS events_insert"); 2818 // Trigger to set event's sync_account 2819 db.execSQL("CREATE TRIGGER events_insert AFTER INSERT ON Events " + 2820 "BEGIN " + 2821 "UPDATE Events" + 2822 " SET _sync_account=" + 2823 " (SELECT _sync_account FROM Calendars" + 2824 " WHERE Calendars._id=new.calendar_id)," + 2825 "_sync_account_type=" + 2826 " (SELECT _sync_account_type FROM Calendars" + 2827 " WHERE Calendars._id=new.calendar_id) " + 2828 "WHERE Events._id=new._id;" + 2829 "END"); 2830 db.execSQL("DROP TABLE IF EXISTS DeletedEvents;"); 2831 db.execSQL("DROP TRIGGER IF EXISTS events_cleanup_delete"); 2832 // Trigger to remove data tied to an event when we delete that event. 2833 db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON Events " + 2834 "BEGIN " + 2835 ("DELETE FROM Instances" + 2836 " WHERE event_id=old._id;" + 2837 "DELETE FROM EventsRawTimes" + 2838 " WHERE event_id=old._id;" + 2839 "DELETE FROM Attendees" + 2840 " WHERE event_id=old._id;" + 2841 "DELETE FROM Reminders" + 2842 " WHERE event_id=old._id;" + 2843 "DELETE FROM CalendarAlerts" + 2844 " WHERE event_id=old._id;" + 2845 "DELETE FROM ExtendedProperties" + 2846 " WHERE event_id=old._id;") + 2847 "END"); 2848 db.execSQL("DROP TRIGGER IF EXISTS attendees_update"); 2849 db.execSQL("DROP TRIGGER IF EXISTS attendees_insert"); 2850 db.execSQL("DROP TRIGGER IF EXISTS attendees_delete"); 2851 db.execSQL("DROP TRIGGER IF EXISTS reminders_update"); 2852 db.execSQL("DROP TRIGGER IF EXISTS reminders_insert"); 2853 db.execSQL("DROP TRIGGER IF EXISTS reminders_delete"); 2854 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_update"); 2855 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_insert"); 2856 db.execSQL("DROP TRIGGER IF EXISTS extended_properties_delete"); 2857 } 2858 upgradeToVersion59(SQLiteDatabase db)2859 private void upgradeToVersion59(SQLiteDatabase db) { 2860 db.execSQL("DROP TABLE IF EXISTS BusyBits;"); 2861 db.execSQL("CREATE TEMPORARY TABLE CalendarMetaData_Backup(" + 2862 "_id," + 2863 "localTimezone," + 2864 "minInstance," + 2865 "maxInstance" + 2866 ");"); 2867 db.execSQL("INSERT INTO CalendarMetaData_Backup " + 2868 "SELECT " + 2869 "_id," + 2870 "localTimezone," + 2871 "minInstance," + 2872 "maxInstance" + 2873 " FROM CalendarMetaData;"); 2874 db.execSQL("DROP TABLE CalendarMetaData;"); 2875 createCalendarMetaDataTable59(db); 2876 db.execSQL("INSERT INTO CalendarMetaData " + 2877 "SELECT " + 2878 "_id," + 2879 "localTimezone," + 2880 "minInstance," + 2881 "maxInstance" + 2882 " FROM CalendarMetaData_Backup;"); 2883 db.execSQL("DROP TABLE CalendarMetaData_Backup;"); 2884 } 2885 upgradeToVersion57(SQLiteDatabase db)2886 private void upgradeToVersion57(SQLiteDatabase db) { 2887 db.execSQL("ALTER TABLE Events" + 2888 " ADD COLUMN guestsCanModify" + 2889 " INTEGER NOT NULL DEFAULT 0;"); 2890 db.execSQL("ALTER TABLE Events" + 2891 " ADD COLUMN guestsCanInviteOthers" + 2892 " INTEGER NOT NULL DEFAULT 1;"); 2893 db.execSQL("ALTER TABLE Events" + 2894 " ADD COLUMN guestsCanSeeGuests" + 2895 " INTEGER NOT NULL DEFAULT 1;"); 2896 db.execSQL("ALTER TABLE Events" + 2897 " ADD COLUMN organizer" + 2898 " STRING;"); 2899 db.execSQL("UPDATE Events SET organizer=" + 2900 "(SELECT attendeeEmail" + 2901 " FROM Attendees" + 2902 " WHERE " + 2903 "Attendees.event_id=" + 2904 "Events._id" + 2905 " AND " + 2906 "Attendees.attendeeRelationship=2);"); 2907 } 2908 upgradeToVersion56(SQLiteDatabase db)2909 private void upgradeToVersion56(SQLiteDatabase db) { 2910 db.execSQL("ALTER TABLE Calendars" + 2911 " ADD COLUMN ownerAccount TEXT;"); 2912 db.execSQL("ALTER TABLE Events" + 2913 " ADD COLUMN hasAttendeeData INTEGER NOT NULL DEFAULT 0;"); 2914 2915 // Clear _sync_dirty to avoid a client-to-server sync that could blow away 2916 // server attendees. 2917 // Clear _sync_version to pull down the server's event (with attendees) 2918 // Change the URLs from full-selfattendance to full 2919 db.execSQL("UPDATE Events" 2920 + " SET _sync_dirty=0, " 2921 + "_sync_version=NULL, " 2922 + "_sync_id=" 2923 + "REPLACE(_sync_id, " + 2924 "'/private/full-selfattendance', '/private/full')," 2925 + "commentsUri=" 2926 + "REPLACE(commentsUri, " + 2927 "'/private/full-selfattendance', '/private/full');"); 2928 2929 db.execSQL("UPDATE Calendars" 2930 + " SET url=" 2931 + "REPLACE(url, '/private/full-selfattendance', '/private/full');"); 2932 2933 // "cursor" iterates over all the calendars 2934 Cursor cursor = db.rawQuery("SELECT _id, " + 2935 "url FROM Calendars", 2936 null /* selection args */); 2937 // Add the owner column. 2938 if (cursor != null) { 2939 try { 2940 final String updateSql = "UPDATE Calendars" + 2941 " SET ownerAccount=?" + 2942 " WHERE _id=?"; 2943 while (cursor.moveToNext()) { 2944 Long id = cursor.getLong(0); 2945 String url = cursor.getString(1); 2946 String owner = calendarEmailAddressFromFeedUrl(url); 2947 db.execSQL(updateSql, new Object[] {owner, id}); 2948 } 2949 } finally { 2950 cursor.close(); 2951 } 2952 } 2953 } 2954 upgradeResync(SQLiteDatabase db)2955 private void upgradeResync(SQLiteDatabase db) { 2956 // Delete sync state, so all records will be re-synced. 2957 db.execSQL("DELETE FROM _sync_state;"); 2958 2959 // "cursor" iterates over all the calendars 2960 Cursor cursor = db.rawQuery("SELECT _sync_account," + 2961 "_sync_account_type,url FROM Calendars", 2962 null /* selection args */); 2963 if (cursor != null) { 2964 try { 2965 while (cursor.moveToNext()) { 2966 String accountName = cursor.getString(0); 2967 String accountType = cursor.getString(1); 2968 final Account account = new Account(accountName, accountType); 2969 String calendarUrl = cursor.getString(2); 2970 scheduleSync(account, false /* two-way sync */, calendarUrl); 2971 } 2972 } finally { 2973 cursor.close(); 2974 } 2975 } 2976 } 2977 upgradeToVersion55(SQLiteDatabase db)2978 private void upgradeToVersion55(SQLiteDatabase db) { 2979 db.execSQL("ALTER TABLE Calendars ADD COLUMN " + 2980 "_sync_account_type TEXT;"); 2981 db.execSQL("ALTER TABLE Events ADD COLUMN " + 2982 "_sync_account_type TEXT;"); 2983 db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN _sync_account_type TEXT;"); 2984 db.execSQL("UPDATE Calendars" 2985 + " SET _sync_account_type='com.google'" 2986 + " WHERE _sync_account IS NOT NULL"); 2987 db.execSQL("UPDATE Events" 2988 + " SET _sync_account_type='com.google'" 2989 + " WHERE _sync_account IS NOT NULL"); 2990 db.execSQL("UPDATE DeletedEvents" 2991 + " SET _sync_account_type='com.google'" 2992 + " WHERE _sync_account IS NOT NULL"); 2993 Log.w(TAG, "re-creating eventSyncAccountAndIdIndex"); 2994 db.execSQL("DROP INDEX eventSyncAccountAndIdIndex"); 2995 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events (" 2996 + "_sync_account_type, " 2997 + "_sync_account, " 2998 + "_sync_id);"); 2999 } 3000 upgradeToVersion54(SQLiteDatabase db)3001 private void upgradeToVersion54(SQLiteDatabase db) { 3002 Log.w(TAG, "adding eventSyncAccountAndIdIndex"); 3003 db.execSQL("CREATE INDEX eventSyncAccountAndIdIndex ON Events (" 3004 + "_sync_account, _sync_id);"); 3005 } 3006 upgradeToVersion53(SQLiteDatabase db)3007 private void upgradeToVersion53(SQLiteDatabase db) { 3008 Log.w(TAG, "Upgrading CalendarAlerts table"); 3009 db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " + 3010 "creationTime INTEGER NOT NULL DEFAULT 0;"); 3011 db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " + 3012 "receivedTime INTEGER NOT NULL DEFAULT 0;"); 3013 db.execSQL("ALTER TABLE CalendarAlerts ADD COLUMN " + 3014 "notifyTime INTEGER NOT NULL DEFAULT 0;"); 3015 } 3016 upgradeToVersion52(SQLiteDatabase db)3017 private void upgradeToVersion52(SQLiteDatabase db) { 3018 // We added "originalAllDay" to the Events table to keep track of 3019 // the allDay status of the original recurring event for entries 3020 // that are exceptions to that recurring event. We need this so 3021 // that we can format the date correctly for the "originalInstanceTime" 3022 // column when we make a change to the recurrence exception and 3023 // send it to the server. 3024 db.execSQL("ALTER TABLE Events ADD COLUMN " + 3025 "originalAllDay INTEGER;"); 3026 3027 // Iterate through the Events table and for each recurrence 3028 // exception, fill in the correct value for "originalAllDay", 3029 // if possible. The only times where this might not be possible 3030 // are (1) the original recurring event no longer exists, or 3031 // (2) the original recurring event does not yet have a _sync_id 3032 // because it was created on the phone and hasn't been synced to the 3033 // server yet. In both cases the originalAllDay field will be set 3034 // to null. In the first case we don't care because the recurrence 3035 // exception will not be displayed and we won't be able to make 3036 // any changes to it (and even if we did, the server should ignore 3037 // them, right?). In the second case, the calendar client already 3038 // disallows making changes to an instance of a recurring event 3039 // until the recurring event has been synced to the server so the 3040 // second case should never occur. 3041 3042 // "cursor" iterates over all the recurrences exceptions. 3043 Cursor cursor = db.rawQuery("SELECT _id," + 3044 "originalEvent" + 3045 " FROM Events" + 3046 " WHERE originalEvent IS NOT NULL", 3047 null /* selection args */); 3048 if (cursor != null) { 3049 try { 3050 while (cursor.moveToNext()) { 3051 long id = cursor.getLong(0); 3052 String originalEvent = cursor.getString(1); 3053 3054 // Find the original recurring event (if it exists) 3055 Cursor recur = db.rawQuery("SELECT allDay" + 3056 " FROM Events" + 3057 " WHERE _sync_id=?", 3058 new String[] {originalEvent}); 3059 if (recur == null) { 3060 continue; 3061 } 3062 3063 try { 3064 // Fill in the "originalAllDay" field of the 3065 // recurrence exception with the "allDay" value 3066 // from the recurring event. 3067 if (recur.moveToNext()) { 3068 int allDay = recur.getInt(0); 3069 db.execSQL("UPDATE Events" + 3070 " SET originalAllDay=" + allDay + 3071 " WHERE _id="+id); 3072 } 3073 } finally { 3074 recur.close(); 3075 } 3076 } 3077 } finally { 3078 cursor.close(); 3079 } 3080 } 3081 } 3082 upgradeToVersion51(SQLiteDatabase db)3083 private void upgradeToVersion51(SQLiteDatabase db) { 3084 Log.w(TAG, "Upgrading DeletedEvents table"); 3085 3086 // We don't have enough information to fill in the correct 3087 // value of the calendar_id for old rows in the DeletedEvents 3088 // table, but rows in that table are transient so it is unlikely 3089 // that there are any rows. Plus, the calendar_id is used only 3090 // when deleting a calendar, which is a rare event. All new rows 3091 // will have the correct calendar_id. 3092 db.execSQL("ALTER TABLE DeletedEvents ADD COLUMN calendar_id INTEGER;"); 3093 3094 // Trigger to remove a calendar's events when we delete the calendar 3095 db.execSQL("DROP TRIGGER IF EXISTS calendar_cleanup"); 3096 db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " + 3097 "BEGIN " + 3098 "DELETE FROM Events WHERE calendar_id=" + 3099 "old._id;" + 3100 "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" + 3101 "END"); 3102 db.execSQL("DROP TRIGGER IF EXISTS event_to_deleted"); 3103 } 3104 dropTables(SQLiteDatabase db)3105 private void dropTables(SQLiteDatabase db) { 3106 Log.i(TAG, "Clearing database"); 3107 3108 String[] columns = { 3109 "type", "name" 3110 }; 3111 Cursor cursor = db.query("sqlite_master", columns, null, null, null, null, null); 3112 if (cursor == null) { 3113 return; 3114 } 3115 try { 3116 while (cursor.moveToNext()) { 3117 final String name = cursor.getString(1); 3118 if (!name.startsWith("sqlite_")) { 3119 // If it's not a SQL-controlled entity, drop it 3120 final String sql = "DROP " + cursor.getString(0) + " IF EXISTS " + name; 3121 try { 3122 db.execSQL(sql); 3123 } catch (SQLException e) { 3124 Log.e(TAG, "Error executing " + sql + " " + e.toString()); 3125 } 3126 } 3127 } 3128 } finally { 3129 cursor.close(); 3130 } 3131 } 3132 3133 @Override getWritableDatabase()3134 public synchronized SQLiteDatabase getWritableDatabase() { 3135 SQLiteDatabase db = super.getWritableDatabase(); 3136 return db; 3137 } 3138 getSyncState()3139 public SyncStateContentProviderHelper getSyncState() { 3140 return mSyncState; 3141 } 3142 3143 /** 3144 * Schedule a calendar sync for the account. 3145 * @param account the account for which to schedule a sync 3146 * @param uploadChangesOnly if set, specify that the sync should only send 3147 * up local changes. This is typically used for a local sync, a user override of 3148 * too many deletions, or a sync after a calendar is unselected. 3149 * @param url the url feed for the calendar to sync (may be null, in which case a poll of 3150 * all feeds is done.) 3151 */ scheduleSync(Account account, boolean uploadChangesOnly, String url)3152 void scheduleSync(Account account, boolean uploadChangesOnly, String url) { 3153 Bundle extras = new Bundle(); 3154 if (uploadChangesOnly) { 3155 extras.putBoolean(ContentResolver.SYNC_EXTRAS_UPLOAD, uploadChangesOnly); 3156 } 3157 if (url != null) { 3158 extras.putString("feed", url); 3159 } 3160 ContentResolver.requestSync(account, Calendars.CONTENT_URI.getAuthority(), 3161 extras); 3162 } 3163 createEventsView(SQLiteDatabase db)3164 private static void createEventsView(SQLiteDatabase db) { 3165 db.execSQL("DROP VIEW IF EXISTS " + Views.EVENTS + ";"); 3166 String eventsSelect = "SELECT " 3167 + Tables.EVENTS + "." + CalendarContract.Events._ID 3168 + " AS " + CalendarContract.Events._ID + "," 3169 + CalendarContract.Events.TITLE + "," 3170 + CalendarContract.Events.DESCRIPTION + "," 3171 + CalendarContract.Events.EVENT_LOCATION + "," 3172 + CalendarContract.Events.EVENT_COLOR + "," 3173 + CalendarContract.Events.EVENT_COLOR_KEY + "," 3174 + CalendarContract.Events.STATUS + "," 3175 + CalendarContract.Events.SELF_ATTENDEE_STATUS + "," 3176 + CalendarContract.Events.DTSTART + "," 3177 + CalendarContract.Events.DTEND + "," 3178 + CalendarContract.Events.DURATION + "," 3179 + CalendarContract.Events.EVENT_TIMEZONE + "," 3180 + CalendarContract.Events.EVENT_END_TIMEZONE + "," 3181 + CalendarContract.Events.ALL_DAY + "," 3182 + CalendarContract.Events.ACCESS_LEVEL + "," 3183 + CalendarContract.Events.AVAILABILITY + "," 3184 + CalendarContract.Events.HAS_ALARM + "," 3185 + CalendarContract.Events.HAS_EXTENDED_PROPERTIES + "," 3186 + CalendarContract.Events.RRULE + "," 3187 + CalendarContract.Events.RDATE + "," 3188 + CalendarContract.Events.EXRULE + "," 3189 + CalendarContract.Events.EXDATE + "," 3190 + CalendarContract.Events.ORIGINAL_SYNC_ID + "," 3191 + CalendarContract.Events.ORIGINAL_ID + "," 3192 + CalendarContract.Events.ORIGINAL_INSTANCE_TIME + "," 3193 + CalendarContract.Events.ORIGINAL_ALL_DAY + "," 3194 + CalendarContract.Events.LAST_DATE + "," 3195 + CalendarContract.Events.HAS_ATTENDEE_DATA + "," 3196 + CalendarContract.Events.CALENDAR_ID + "," 3197 + CalendarContract.Events.GUESTS_CAN_INVITE_OTHERS + "," 3198 + CalendarContract.Events.GUESTS_CAN_MODIFY + "," 3199 + CalendarContract.Events.GUESTS_CAN_SEE_GUESTS + "," 3200 + CalendarContract.Events.ORGANIZER + "," 3201 + "COALESCE(" 3202 + Events.IS_ORGANIZER + ", " + Events.ORGANIZER + " = " + Calendars.OWNER_ACCOUNT 3203 + ") AS " + Events.IS_ORGANIZER + "," 3204 + CalendarContract.Events.CUSTOM_APP_PACKAGE + "," 3205 + CalendarContract.Events.CUSTOM_APP_URI + "," 3206 + CalendarContract.Events.UID_2445 + "," 3207 + CalendarContract.Events.SYNC_DATA1 + "," 3208 + CalendarContract.Events.SYNC_DATA2 + "," 3209 + CalendarContract.Events.SYNC_DATA3 + "," 3210 + CalendarContract.Events.SYNC_DATA4 + "," 3211 + CalendarContract.Events.SYNC_DATA5 + "," 3212 + CalendarContract.Events.SYNC_DATA6 + "," 3213 + CalendarContract.Events.SYNC_DATA7 + "," 3214 + CalendarContract.Events.SYNC_DATA8 + "," 3215 + CalendarContract.Events.SYNC_DATA9 + "," 3216 + CalendarContract.Events.SYNC_DATA10 + "," 3217 + Tables.EVENTS + "." + CalendarContract.Events.DELETED 3218 + " AS " + CalendarContract.Events.DELETED + "," 3219 + Tables.EVENTS + "." + CalendarContract.Events._SYNC_ID 3220 + " AS " + CalendarContract.Events._SYNC_ID + "," 3221 + Tables.EVENTS + "." + CalendarContract.Events.DIRTY 3222 + " AS " + CalendarContract.Events.DIRTY + "," 3223 + Tables.EVENTS + "." + Events.MUTATORS 3224 + " AS " + Events.MUTATORS + "," 3225 + CalendarContract.Events.LAST_SYNCED + "," 3226 + Tables.CALENDARS + "." + Calendars.ACCOUNT_NAME 3227 + " AS " + CalendarContract.Events.ACCOUNT_NAME + "," 3228 + Tables.CALENDARS + "." + Calendars.ACCOUNT_TYPE 3229 + " AS " + CalendarContract.Events.ACCOUNT_TYPE + "," 3230 + Calendars.CALENDAR_TIME_ZONE + "," 3231 + Calendars.CALENDAR_DISPLAY_NAME + "," 3232 + Calendars.CALENDAR_LOCATION + "," 3233 + Calendars.VISIBLE + "," 3234 + Calendars.CALENDAR_COLOR + "," 3235 + Calendars.CALENDAR_COLOR_KEY + "," 3236 + Calendars.CALENDAR_ACCESS_LEVEL + "," 3237 + Calendars.MAX_REMINDERS + "," 3238 + Calendars.ALLOWED_REMINDERS + "," 3239 + Calendars.ALLOWED_ATTENDEE_TYPES + "," 3240 + Calendars.ALLOWED_AVAILABILITY + "," 3241 + Calendars.CAN_ORGANIZER_RESPOND + "," 3242 + Calendars.CAN_MODIFY_TIME_ZONE + "," 3243 + Calendars.CAN_PARTIALLY_UPDATE + "," 3244 + Calendars.IS_PRIMARY + "," 3245 + Calendars.CAL_SYNC1 + "," 3246 + Calendars.CAL_SYNC2 + "," 3247 + Calendars.CAL_SYNC3 + "," 3248 + Calendars.CAL_SYNC4 + "," 3249 + Calendars.CAL_SYNC5 + "," 3250 + Calendars.CAL_SYNC6 + "," 3251 + Calendars.CAL_SYNC7 + "," 3252 + Calendars.CAL_SYNC8 + "," 3253 + Calendars.CAL_SYNC9 + "," 3254 + Calendars.CAL_SYNC10 + "," 3255 + Calendars.OWNER_ACCOUNT + "," 3256 + Calendars.SYNC_EVENTS + "," 3257 + "ifnull(" + Events.EVENT_COLOR + "," + Calendars.CALENDAR_COLOR + ") AS " 3258 + Events.DISPLAY_COLOR 3259 + " FROM " + Tables.EVENTS + " JOIN " + Tables.CALENDARS 3260 + " ON (" + Tables.EVENTS + "." + Events.CALENDAR_ID 3261 + "=" + Tables.CALENDARS + "." + Calendars._ID 3262 + ")"; 3263 3264 db.execSQL("CREATE VIEW " + Views.EVENTS + " AS " + eventsSelect); 3265 } 3266 3267 /** 3268 * Extracts the calendar email from a calendar feed url. 3269 * @param feed the calendar feed url 3270 * @return the calendar email that is in the feed url or null if it can't 3271 * find the email address. 3272 * TODO: this is duplicated in CalendarSyncAdapter; move to a library 3273 */ calendarEmailAddressFromFeedUrl(String feed)3274 public static String calendarEmailAddressFromFeedUrl(String feed) { 3275 // Example feed url: 3276 // https://www.google.com/calendar/feeds/foo%40gmail.com/private/full-noattendees 3277 String[] pathComponents = feed.split("/"); 3278 if (pathComponents.length > 5 && "feeds".equals(pathComponents[4])) { 3279 try { 3280 return URLDecoder.decode(pathComponents[5], "UTF-8"); 3281 } catch (UnsupportedEncodingException e) { 3282 Log.e(TAG, "unable to url decode the email address in calendar " + feed); 3283 return null; 3284 } 3285 } 3286 3287 Log.e(TAG, "unable to find the email address in calendar " + feed); 3288 return null; 3289 } 3290 3291 /** 3292 * Get a "allcalendars" url from a "private/full" or "private/free-busy" url 3293 * @param url 3294 * @return the rewritten Url 3295 * 3296 * For example: 3297 * 3298 * http://www.google.com/calendar/feeds/joe%40joe.com/private/full 3299 * http://www.google.com/calendar/feeds/joe%40joe.com/private/free-busy 3300 * 3301 * will be rewriten into: 3302 * 3303 * http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com 3304 * http://www.google.com/calendar/feeds/default/allcalendars/full/joe%40joe.com 3305 */ getAllCalendarsUrlFromEventsUrl(String url)3306 private static String getAllCalendarsUrlFromEventsUrl(String url) { 3307 if (url == null) { 3308 if (Log.isLoggable(TAG, Log.DEBUG)) { 3309 Log.d(TAG, "Cannot get AllCalendars url from a NULL url"); 3310 } 3311 return null; 3312 } 3313 if (url.contains("/private/full")) { 3314 return url.replace("/private/full", ""). 3315 replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full"); 3316 } 3317 if (url.contains("/private/free-busy")) { 3318 return url.replace("/private/free-busy", ""). 3319 replace("/calendar/feeds", "/calendar/feeds/default/allcalendars/full"); 3320 } 3321 // Just log as we dont recognize the provided Url 3322 if (Log.isLoggable(TAG, Log.DEBUG)) { 3323 Log.d(TAG, "Cannot get AllCalendars url from the following url: " + url); 3324 } 3325 return null; 3326 } 3327 3328 /** 3329 * Get "selfUrl" from "events url" 3330 * @param url the Events url (either "private/full" or "private/free-busy" 3331 * @return the corresponding allcalendar url 3332 */ getSelfUrlFromEventsUrl(String url)3333 private static String getSelfUrlFromEventsUrl(String url) { 3334 return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url)); 3335 } 3336 3337 /** 3338 * Get "editUrl" from "events url" 3339 * @param url the Events url (either "private/full" or "private/free-busy" 3340 * @return the corresponding allcalendar url 3341 */ getEditUrlFromEventsUrl(String url)3342 private static String getEditUrlFromEventsUrl(String url) { 3343 return rewriteUrlFromHttpToHttps(getAllCalendarsUrlFromEventsUrl(url)); 3344 } 3345 3346 /** 3347 * Rewrite the url from "http" to "https" scheme 3348 * @param url the url to rewrite 3349 * @return the rewritten URL 3350 */ rewriteUrlFromHttpToHttps(String url)3351 private static String rewriteUrlFromHttpToHttps(String url) { 3352 if (url == null) { 3353 if (Log.isLoggable(TAG, Log.DEBUG)) { 3354 Log.d(TAG, "Cannot rewrite a NULL url"); 3355 } 3356 return null; 3357 } 3358 if (url.startsWith(SCHEMA_HTTPS)) { 3359 return url; 3360 } 3361 if (!url.startsWith(SCHEMA_HTTP)) { 3362 throw new IllegalArgumentException("invalid url parameter, unknown scheme: " + url); 3363 } 3364 return SCHEMA_HTTPS + url.substring(SCHEMA_HTTP.length()); 3365 } 3366 3367 /** 3368 * Duplicates an event and its associated tables (Attendees, Reminders, ExtendedProperties). 3369 * <p> 3370 * Does not create a duplicate if the Calendar's "canPartiallyUpdate" is 0 or the Event's 3371 * "dirty" is 1 (so we don't create more than one duplicate). 3372 * 3373 * @param id The _id of the event to duplicate. 3374 */ duplicateEvent(final long id)3375 protected void duplicateEvent(final long id) { 3376 final SQLiteDatabase db = getWritableDatabase(); 3377 final long canPartiallyUpdate = DatabaseUtils.longForQuery(db, "SELECT " 3378 + Calendars.CAN_PARTIALLY_UPDATE + " FROM " + Views.EVENTS 3379 + " WHERE " + Events._ID + " = ?", new String[]{ 3380 String.valueOf(id) 3381 }); 3382 if (canPartiallyUpdate == 0) { 3383 return; 3384 } 3385 3386 db.execSQL("INSERT INTO " + CalendarDatabaseHelper.Tables.EVENTS 3387 + " (" + LAST_SYNCED_EVENT_COLUMNS + "," 3388 + Events.DIRTY + "," + Events.LAST_SYNCED + ")" 3389 + " SELECT " + LAST_SYNCED_EVENT_COLUMNS + ", 0, 1" 3390 + " FROM " + Tables.EVENTS 3391 + " WHERE " + Events._ID + " = ? AND " + Events.DIRTY + " = ?", 3392 new Object[]{ 3393 id, 3394 0, // Events.DIRTY 3395 }); 3396 final long newId = DatabaseUtils.longForQuery( 3397 db, "SELECT CASE changes() WHEN 0 THEN -1 ELSE last_insert_rowid() END", null); 3398 if (newId < 0) { 3399 return; 3400 } 3401 3402 if (Log.isLoggable(TAG, Log.VERBOSE)) { 3403 Log.v(TAG, "Duplicating event " + id + " into new event " + newId); 3404 } 3405 3406 copyEventRelatedTables(db, newId, id); 3407 } 3408 3409 /** 3410 * Makes a copy of the Attendees, Reminders, and ExtendedProperties rows associated with 3411 * a specific event. 3412 * 3413 * @param db The database. 3414 * @param newId The ID of the new event. 3415 * @param id The ID of the old event. 3416 */ copyEventRelatedTables(SQLiteDatabase db, long newId, long id)3417 static void copyEventRelatedTables(SQLiteDatabase db, long newId, long id) { 3418 db.execSQL("INSERT INTO " + Tables.REMINDERS 3419 + " ( " + CalendarContract.Reminders.EVENT_ID + ", " 3420 + LAST_SYNCED_REMINDER_COLUMNS + ") " 3421 + "SELECT ?," + LAST_SYNCED_REMINDER_COLUMNS 3422 + " FROM " + Tables.REMINDERS 3423 + " WHERE " + CalendarContract.Reminders.EVENT_ID + " = ?", 3424 new Object[] {newId, id}); 3425 db.execSQL("INSERT INTO " 3426 + Tables.ATTENDEES 3427 + " (" + CalendarContract.Attendees.EVENT_ID + "," 3428 + LAST_SYNCED_ATTENDEE_COLUMNS + ") " 3429 + "SELECT ?," + LAST_SYNCED_ATTENDEE_COLUMNS + " FROM " + Tables.ATTENDEES 3430 + " WHERE " + CalendarContract.Attendees.EVENT_ID + " = ?", 3431 new Object[] {newId, id}); 3432 db.execSQL("INSERT INTO " + Tables.EXTENDED_PROPERTIES 3433 + " (" + CalendarContract.ExtendedProperties.EVENT_ID + "," 3434 + LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS + ") " 3435 + "SELECT ?, " + LAST_SYNCED_EXTENDED_PROPERTY_COLUMNS 3436 + " FROM " + Tables.EXTENDED_PROPERTIES 3437 + " WHERE " + CalendarContract.ExtendedProperties.EVENT_ID + " = ?", 3438 new Object[]{newId, id}); 3439 } 3440 removeDuplicateEvent(final long id)3441 protected void removeDuplicateEvent(final long id) { 3442 final SQLiteDatabase db = getWritableDatabase(); 3443 final Cursor cursor = db.rawQuery("SELECT " + Events._ID + " FROM " + Tables.EVENTS 3444 + " WHERE " + Events._SYNC_ID 3445 + " = (SELECT " + Events._SYNC_ID 3446 + " FROM " + Tables.EVENTS 3447 + " WHERE " + Events._ID + " = ?) " 3448 + "AND " + Events.LAST_SYNCED + " = ?", 3449 new String[]{ 3450 String.valueOf(id), 3451 "1", // Events.LAST_SYNCED 3452 }); 3453 try { 3454 // there should only be at most one but this can't hurt 3455 if (cursor.moveToNext()) { 3456 final long dupId = cursor.getLong(0); 3457 3458 if (Log.isLoggable(TAG, Log.VERBOSE)) { 3459 Log.v(TAG, "Removing duplicate event " + dupId + " of original event " + id); 3460 } 3461 // triggers will clean up related tables. 3462 db.execSQL("DELETE FROM Events WHERE " + Events._ID + " = ?", new Object[]{dupId}); 3463 } 3464 } finally { 3465 cursor.close(); 3466 } 3467 } 3468 } 3469