17b40dde3168f4af2c757cb43955aa3bfe1668666Erik/* 27b40dde3168f4af2c757cb43955aa3bfe1668666Erik * Copyright (C) 2010 The Android Open Source Project 37b40dde3168f4af2c757cb43955aa3bfe1668666Erik * 47b40dde3168f4af2c757cb43955aa3bfe1668666Erik * Licensed under the Apache License, Version 2.0 (the "License"); 57b40dde3168f4af2c757cb43955aa3bfe1668666Erik * you may not use this file except in compliance with the License. 67b40dde3168f4af2c757cb43955aa3bfe1668666Erik * You may obtain a copy of the License at 77b40dde3168f4af2c757cb43955aa3bfe1668666Erik * 87b40dde3168f4af2c757cb43955aa3bfe1668666Erik * http://www.apache.org/licenses/LICENSE-2.0 97b40dde3168f4af2c757cb43955aa3bfe1668666Erik * 107b40dde3168f4af2c757cb43955aa3bfe1668666Erik * Unless required by applicable law or agreed to in writing, software 117b40dde3168f4af2c757cb43955aa3bfe1668666Erik * distributed under the License is distributed on an "AS IS" BASIS, 127b40dde3168f4af2c757cb43955aa3bfe1668666Erik * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 137b40dde3168f4af2c757cb43955aa3bfe1668666Erik * See the License for the specific language governing permissions and 147b40dde3168f4af2c757cb43955aa3bfe1668666Erik * limitations under the License 157b40dde3168f4af2c757cb43955aa3bfe1668666Erik */ 167b40dde3168f4af2c757cb43955aa3bfe1668666Erikpackage com.android.providers.calendar; 177b40dde3168f4af2c757cb43955aa3bfe1668666Erik 182cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 192cff10f1a005bd7302245d4c680cf851193c3a97RoboErikimport com.android.common.content.SyncStateContentProviderHelper; 202cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 217b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport android.database.Cursor; 227b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport android.database.DatabaseUtils; 237b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport android.database.sqlite.SQLiteDatabase; 242cff10f1a005bd7302245d4c680cf851193c3a97RoboErikimport android.test.mock.MockContext; 257b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport android.test.suitebuilder.annotation.MediumTest; 267b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport android.text.TextUtils; 277b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport android.util.Log; 287b40dde3168f4af2c757cb43955aa3bfe1668666Erik 29d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErikimport java.util.Arrays; 30d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 317b40dde3168f4af2c757cb43955aa3bfe1668666Erikimport junit.framework.TestCase; 327b40dde3168f4af2c757cb43955aa3bfe1668666Erik 337b40dde3168f4af2c757cb43955aa3bfe1668666Erikpublic class CalendarDatabaseHelperTest extends TestCase { 342cff10f1a005bd7302245d4c680cf851193c3a97RoboErik private static final String TAG = "CDbHelperTest"; 357b40dde3168f4af2c757cb43955aa3bfe1668666Erik 367b40dde3168f4af2c757cb43955aa3bfe1668666Erik private SQLiteDatabase mBadDb; 377b40dde3168f4af2c757cb43955aa3bfe1668666Erik private SQLiteDatabase mGoodDb; 387b40dde3168f4af2c757cb43955aa3bfe1668666Erik private DatabaseUtils.InsertHelper mBadEventsInserter; 397b40dde3168f4af2c757cb43955aa3bfe1668666Erik private DatabaseUtils.InsertHelper mGoodEventsInserter; 407b40dde3168f4af2c757cb43955aa3bfe1668666Erik 417b40dde3168f4af2c757cb43955aa3bfe1668666Erik @Override 427b40dde3168f4af2c757cb43955aa3bfe1668666Erik public void setUp() { 437b40dde3168f4af2c757cb43955aa3bfe1668666Erik mBadDb = SQLiteDatabase.create(null); 447b40dde3168f4af2c757cb43955aa3bfe1668666Erik assertNotNull(mBadDb); 457b40dde3168f4af2c757cb43955aa3bfe1668666Erik mGoodDb = SQLiteDatabase.create(null); 467b40dde3168f4af2c757cb43955aa3bfe1668666Erik assertNotNull(mGoodDb); 477b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 487b40dde3168f4af2c757cb43955aa3bfe1668666Erik 492cff10f1a005bd7302245d4c680cf851193c3a97RoboErik protected void bootstrapDbVersion50(SQLiteDatabase db) { 502cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 512cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // TODO remove the dependency on this system class 522cff10f1a005bd7302245d4c680cf851193c3a97RoboErik SyncStateContentProviderHelper syncStateHelper = new SyncStateContentProviderHelper(); 532cff10f1a005bd7302245d4c680cf851193c3a97RoboErik syncStateHelper.createDatabase(db); 542cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 552cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE Calendars (" + 562cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 572cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_account TEXT," + 582cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_id TEXT," + 592cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_version TEXT," + 602cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_time TEXT," + // UTC 612cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_local_id INTEGER," + 622cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_dirty INTEGER," + 632cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_mark INTEGER," + // Used to filter out new rows 642cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "url TEXT," + 652cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "name TEXT," + 662cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "displayName TEXT," + 672cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "hidden INTEGER NOT NULL DEFAULT 0," + 682cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "color INTEGER," + 692cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "access_level INTEGER," + 702cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "selected INTEGER NOT NULL DEFAULT 1," + 712cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "sync_events INTEGER NOT NULL DEFAULT 0," + 722cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "location TEXT," + 732cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "timezone TEXT" + 742cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 752cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 762cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Trigger to remove a calendar's events when we delete the calendar 772cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER calendar_cleanup DELETE ON Calendars " + 782cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 792cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM Events WHERE calendar_id = old._id;" + 802cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM DeletedEvents WHERE calendar_id = old._id;" + 812cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 822cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 832cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // TODO: do we need both dtend and duration? 842cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE Events (" + 852cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 862cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_account TEXT," + 872cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_id TEXT," + 882cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_version TEXT," + 892cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_time TEXT," + // UTC 902cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_local_id INTEGER," + 912cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_dirty INTEGER," + 922cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_mark INTEGER," + // To filter out new rows 93d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik // TODO remove NOT NULL when upgrade rebuilds events to have 94d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik // true v50 schema 95d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik "calendar_id INTEGER NOT NULL," + 962cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "htmlUri TEXT," + 972cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "title TEXT," + 982cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventLocation TEXT," + 992cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "description TEXT," + 1002cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventStatus INTEGER," + 1012cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "selfAttendeeStatus INTEGER NOT NULL DEFAULT 0," + 1022cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "commentsUri TEXT," + 1032cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "dtstart INTEGER," + // millis since epoch 1042cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "dtend INTEGER," + // millis since epoch 1052cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone TEXT," + // timezone for event 1062cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "duration TEXT," + 1072cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "allDay INTEGER NOT NULL DEFAULT 0," + 1082cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "visibility INTEGER NOT NULL DEFAULT 0," + 1092cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "transparency INTEGER NOT NULL DEFAULT 0," + 1102cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "hasAlarm INTEGER NOT NULL DEFAULT 0," + 1112cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "hasExtendedProperties INTEGER NOT NULL DEFAULT 0," + 1122cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "rrule TEXT," + 1132cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "rdate TEXT," + 1142cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "exrule TEXT," + 1152cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "exdate TEXT," + 1162cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "originalEvent TEXT," + 1172cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "originalInstanceTime INTEGER," + // millis since epoch 1182cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "lastDate INTEGER" + // millis since epoch 1192cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1202cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1212cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE INDEX eventsCalendarIdIndex ON Events (calendar_id);"); 1222cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1232cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE EventsRawTimes (" + 1242cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 1252cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "event_id INTEGER NOT NULL," + 1262cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "dtstart2445 TEXT," + 1272cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "dtend2445 TEXT," + 1282cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "originalInstanceTime2445 TEXT," + 1292cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "lastDate2445 TEXT," + 1302cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UNIQUE (event_id)" + 1312cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1322cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1332cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // NOTE: we do not create a trigger to delete an event's instances upon update, 1342cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // as all rows currently get updated during a merge. 1352cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1362cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE DeletedEvents (" + 1372cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_id TEXT," + 1382cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_version TEXT," + 1392cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_account TEXT," + 1402cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_sync_mark INTEGER" + // To filter out new rows 1412cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1422cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1432cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE Instances (" + 1442cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 1452cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "event_id INTEGER," + 1462cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "begin INTEGER," + // UTC millis 1472cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "end INTEGER," + // UTC millis 1482cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "startDay INTEGER," + // Julian start day 1492cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "endDay INTEGER," + // Julian end day 1502cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "startMinute INTEGER," + // minutes from midnight 1512cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "endMinute INTEGER," + // minutes from midnight 1522cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UNIQUE (event_id, begin, end)" + 1532cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1542cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1552cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE INDEX instancesStartDayIndex ON Instances (startDay);"); 1562cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1572cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE CalendarMetaData (" + 1582cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 1592cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "localTimezone TEXT," + 1602cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "minInstance INTEGER," + // UTC millis 1612cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "maxInstance INTEGER," + // UTC millis 1622cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "minBusyBits INTEGER," + // UTC millis 1632cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "maxBusyBits INTEGER" + // UTC millis 1642cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1652cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1662cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE BusyBits(" + 1672cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "day INTEGER PRIMARY KEY," + // the Julian day 1682cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "busyBits INTEGER," + // 24 bits for 60-minute intervals 1692cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "allDayCount INTEGER" + // number of all-day events 1702cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1712cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1722cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE Attendees (" + 1732cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 1742cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "event_id INTEGER," + 1752cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "attendeeName TEXT," + 1762cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "attendeeEmail TEXT," + 1772cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "attendeeStatus INTEGER," + 1782cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "attendeeRelationship INTEGER," + 1792cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "attendeeType INTEGER" + 1802cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 1812cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1822cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE INDEX attendeesEventIdIndex ON Attendees (event_id);"); 1832cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1842cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE Reminders (" + 1852cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 1862cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "event_id INTEGER," + 1872cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "minutes INTEGER," + 1882cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "method INTEGER NOT NULL" + 1892cff10f1a005bd7302245d4c680cf851193c3a97RoboErik " DEFAULT 0);"); 1902cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1912cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE INDEX remindersEventIdIndex ON Reminders (event_id);"); 1922cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 1932cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // This table stores the Calendar notifications that have gone off. 1942cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE CalendarAlerts (" + 1952cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 1962cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "event_id INTEGER," + 1972cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "begin INTEGER NOT NULL," + // UTC millis 1982cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "end INTEGER NOT NULL," + // UTC millis 1992cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "alarmTime INTEGER NOT NULL," + // UTC millis 2002cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "state INTEGER NOT NULL," + 2012cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "minutes INTEGER," + 2022cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UNIQUE (alarmTime, begin, event_id)" + 2032cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 2042cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2052cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE INDEX calendarAlertsEventIdIndex ON CalendarAlerts (event_id);"); 2062cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2072cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TABLE ExtendedProperties (" + 2082cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id INTEGER PRIMARY KEY," + 2092cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "event_id INTEGER," + 2102cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "name TEXT," + 2112cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "value TEXT" + 2122cff10f1a005bd7302245d4c680cf851193c3a97RoboErik ");"); 2132cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2142cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE INDEX extendedPropertiesEventIdIndex ON ExtendedProperties (event_id);"); 2152cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2162cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Trigger to remove data tied to an event when we delete that event. 2172cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER events_cleanup_delete DELETE ON Events " + 2182cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2192cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM Instances WHERE event_id = old._id;" + 2202cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM EventsRawTimes WHERE event_id = old._id;" + 2212cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM Attendees WHERE event_id = old._id;" + 2222cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM Reminders WHERE event_id = old._id;" + 2232cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM CalendarAlerts WHERE event_id = old._id;" + 2242cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "DELETE FROM ExtendedProperties WHERE event_id = old._id;" + 2252cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2262cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2272cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Triggers to set the _sync_dirty flag when an attendee is changed, 2282cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // inserted or deleted 2292cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER attendees_update UPDATE ON Attendees " + 2302cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2312cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=old.event_id;" + 2322cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2332cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER attendees_insert INSERT ON Attendees " + 2342cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2352cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=new.event_id;" + 2362cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2372cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER attendees_delete DELETE ON Attendees " + 2382cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2392cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=old.event_id;" + 2402cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2412cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2422cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Triggers to set the _sync_dirty flag when a reminder is changed, 2432cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // inserted or deleted 2442cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER reminders_update UPDATE ON Reminders " + 2452cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2462cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=old.event_id;" + 2472cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2482cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER reminders_insert INSERT ON Reminders " + 2492cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2502cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=new.event_id;" + 2512cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2522cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER reminders_delete DELETE ON Reminders " + 2532cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2542cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=old.event_id;" + 2552cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2562cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Triggers to set the _sync_dirty flag when an extended property is changed, 2572cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // inserted or deleted 2582cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER extended_properties_update UPDATE ON ExtendedProperties " + 2592cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2602cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=old.event_id;" + 2612cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2622cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER extended_properties_insert UPDATE ON ExtendedProperties " + 2632cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2642cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=new.event_id;" + 2652cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2662cff10f1a005bd7302245d4c680cf851193c3a97RoboErik db.execSQL("CREATE TRIGGER extended_properties_delete UPDATE ON ExtendedProperties " + 2672cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "BEGIN " + 2682cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "UPDATE Events SET _sync_dirty=1 WHERE Events._id=old.event_id;" + 2692cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "END"); 2702cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } 2712cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 2727b40dde3168f4af2c757cb43955aa3bfe1668666Erik private void createVersion67EventsTable(SQLiteDatabase db) { 2737b40dde3168f4af2c757cb43955aa3bfe1668666Erik db.execSQL("CREATE TABLE Events (" + 2747b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_id INTEGER PRIMARY KEY," + 2757b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_account TEXT," + 2767b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_account_type TEXT," + 2777b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_id TEXT," + 2787b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_version TEXT," + 2797b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_time TEXT," + // UTC 2807b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_local_id INTEGER," + 2817b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_dirty INTEGER," + 2827b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_sync_mark INTEGER," + // To filter out new rows 2837b40dde3168f4af2c757cb43955aa3bfe1668666Erik "calendar_id INTEGER NOT NULL," + 2847b40dde3168f4af2c757cb43955aa3bfe1668666Erik "htmlUri TEXT," + 2857b40dde3168f4af2c757cb43955aa3bfe1668666Erik "title TEXT," + 2867b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventLocation TEXT," + 2877b40dde3168f4af2c757cb43955aa3bfe1668666Erik "description TEXT," + 2887b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventStatus INTEGER," + 2897b40dde3168f4af2c757cb43955aa3bfe1668666Erik "selfAttendeeStatus INTEGER NOT NULL DEFAULT 0," + 2907b40dde3168f4af2c757cb43955aa3bfe1668666Erik "commentsUri TEXT," + 2917b40dde3168f4af2c757cb43955aa3bfe1668666Erik "dtstart INTEGER," + // millis since epoch 2927b40dde3168f4af2c757cb43955aa3bfe1668666Erik "dtend INTEGER," + // millis since epoch 2937b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone TEXT," + // timezone for event 2947b40dde3168f4af2c757cb43955aa3bfe1668666Erik "duration TEXT," + 2957b40dde3168f4af2c757cb43955aa3bfe1668666Erik "allDay INTEGER NOT NULL DEFAULT 0," + 2967b40dde3168f4af2c757cb43955aa3bfe1668666Erik "visibility INTEGER NOT NULL DEFAULT 0," + 2977b40dde3168f4af2c757cb43955aa3bfe1668666Erik "transparency INTEGER NOT NULL DEFAULT 0," + 2987b40dde3168f4af2c757cb43955aa3bfe1668666Erik "hasAlarm INTEGER NOT NULL DEFAULT 0," + 2997b40dde3168f4af2c757cb43955aa3bfe1668666Erik "hasExtendedProperties INTEGER NOT NULL DEFAULT 0," + 3007b40dde3168f4af2c757cb43955aa3bfe1668666Erik "rrule TEXT," + 3017b40dde3168f4af2c757cb43955aa3bfe1668666Erik "rdate TEXT," + 3027b40dde3168f4af2c757cb43955aa3bfe1668666Erik "exrule TEXT," + 3037b40dde3168f4af2c757cb43955aa3bfe1668666Erik "exdate TEXT," + 3047b40dde3168f4af2c757cb43955aa3bfe1668666Erik "originalEvent TEXT," + // _sync_id of recurring event 3057b40dde3168f4af2c757cb43955aa3bfe1668666Erik "originalInstanceTime INTEGER," + // millis since epoch 3067b40dde3168f4af2c757cb43955aa3bfe1668666Erik "originalAllDay INTEGER," + 3077b40dde3168f4af2c757cb43955aa3bfe1668666Erik "lastDate INTEGER," + // millis since epoch 3087b40dde3168f4af2c757cb43955aa3bfe1668666Erik "hasAttendeeData INTEGER NOT NULL DEFAULT 0," + 3097b40dde3168f4af2c757cb43955aa3bfe1668666Erik "guestsCanModify INTEGER NOT NULL DEFAULT 0," + 3107b40dde3168f4af2c757cb43955aa3bfe1668666Erik "guestsCanInviteOthers INTEGER NOT NULL DEFAULT 1," + 3117b40dde3168f4af2c757cb43955aa3bfe1668666Erik "guestsCanSeeGuests INTEGER NOT NULL DEFAULT 1," + 3127b40dde3168f4af2c757cb43955aa3bfe1668666Erik "organizer STRING," + 3137b40dde3168f4af2c757cb43955aa3bfe1668666Erik "deleted INTEGER NOT NULL DEFAULT 0," + 3147b40dde3168f4af2c757cb43955aa3bfe1668666Erik "dtstart2 INTEGER," + //millis since epoch, allDay events in local timezone 3157b40dde3168f4af2c757cb43955aa3bfe1668666Erik "dtend2 INTEGER," + //millis since epoch, allDay events in local timezone 3167b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone2 TEXT," + //timezone for event with allDay events in local timezone 3177b40dde3168f4af2c757cb43955aa3bfe1668666Erik "syncAdapterData TEXT" + //available for use by sync adapters 3187b40dde3168f4af2c757cb43955aa3bfe1668666Erik ");"); 3197b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 3207b40dde3168f4af2c757cb43955aa3bfe1668666Erik 3212cff10f1a005bd7302245d4c680cf851193c3a97RoboErik private void addVersion50Events() { 3222cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // April 5th 1:01:01 AM to April 6th 1:01:01 3232cff10f1a005bd7302245d4c680cf851193c3a97RoboErik mBadDb.execSQL("INSERT INTO Events (_id,dtstart,dtend,duration," + 3242cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone,allDay,calendar_id) " + 3252cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "VALUES (1,1270454471000,1270540872000,'P10S'," + 3262cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "'America/Los_Angeles',1,1);"); 3272cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 3282cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // April 5th midnight to April 6th midnight, duration cleared 3292cff10f1a005bd7302245d4c680cf851193c3a97RoboErik mGoodDb.execSQL("INSERT INTO Events (_id,dtstart,dtend,duration," + 3302cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone,allDay,calendar_id) " + 3312cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "VALUES (1,1270425600000,1270512000000,null," + 3322cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "'UTC',1,1);"); 3332cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 3342cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // April 5th 1:01:01 AM to April 6th 1:01:01, recurring weekly (We only check for the 3352cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // existence of an rrule so it doesn't matter if the day is correct) 3362cff10f1a005bd7302245d4c680cf851193c3a97RoboErik mBadDb.execSQL("INSERT INTO Events (_id,dtstart,dtend,duration," + 3372cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone,allDay,rrule,calendar_id) " + 3382cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "VALUES (2,1270454462000,1270540863000," + 3392cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "'P10S','America/Los_Angeles',1," + 3402cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "'WEEKLY:MON',1);"); 3412cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 3422cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // April 5th midnight with 1 day duration, if only dtend was wrong we wouldn't fix it, but 3432cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // if anything else is wrong we clear dtend to be sure. 3442cff10f1a005bd7302245d4c680cf851193c3a97RoboErik mGoodDb.execSQL("INSERT INTO Events (" + 3452cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "_id,dtstart,dtend,duration," + 3462cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone,allDay,rrule,calendar_id)" + 3472cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "VALUES (2,1270425600000,null,'P1D'," + 3482cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "'UTC',1," + 3492cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "'WEEKLY:MON',1);"); 3502cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 3512cff10f1a005bd7302245d4c680cf851193c3a97RoboErik assertEquals(mBadDb.rawQuery("SELECT _id FROM Events;", null).getCount(), 2); 3522cff10f1a005bd7302245d4c680cf851193c3a97RoboErik assertEquals(mGoodDb.rawQuery("SELECT _id FROM Events;", null).getCount(), 2); 3532cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } 3542cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 3557b40dde3168f4af2c757cb43955aa3bfe1668666Erik private void addVersion67Events() { 3567b40dde3168f4af2c757cb43955aa3bfe1668666Erik // April 5th 1:01:01 AM to April 6th 1:01:01 3577b40dde3168f4af2c757cb43955aa3bfe1668666Erik mBadDb.execSQL("INSERT INTO Events (_id,dtstart,dtend,duration,dtstart2,dtend2," + 3587b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone,eventTimezone2,allDay,calendar_id) " + 3597b40dde3168f4af2c757cb43955aa3bfe1668666Erik "VALUES (1,1270454471000,1270540872000,'P10S'," + 3607b40dde3168f4af2c757cb43955aa3bfe1668666Erik "1270454460000,1270540861000,'America/Los_Angeles','America/Los_Angeles',1,1);"); 3617b40dde3168f4af2c757cb43955aa3bfe1668666Erik 3627b40dde3168f4af2c757cb43955aa3bfe1668666Erik // April 5th midnight to April 6th midnight, duration cleared 3637b40dde3168f4af2c757cb43955aa3bfe1668666Erik mGoodDb.execSQL("INSERT INTO Events (_id,dtstart,dtend,duration,dtstart2,dtend2," + 3647b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone,eventTimezone2,allDay,calendar_id) " + 365e3730b9dce00439666e7ef324a28263a0ee92032Erik "VALUES (1,1270425600000,1270512000000,null," + 366e3730b9dce00439666e7ef324a28263a0ee92032Erik "1270450800000,1270537200000,'UTC','America/Los_Angeles',1,1);"); 3677b40dde3168f4af2c757cb43955aa3bfe1668666Erik 3687b40dde3168f4af2c757cb43955aa3bfe1668666Erik // April 5th 1:01:01 AM to April 6th 1:01:01, recurring weekly (We only check for the 3697b40dde3168f4af2c757cb43955aa3bfe1668666Erik // existence of an rrule so it doesn't matter if the day is correct) 3707b40dde3168f4af2c757cb43955aa3bfe1668666Erik mBadDb.execSQL("INSERT INTO Events (_id,dtstart,dtend,duration,dtstart2,dtend2," + 3717b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone,eventTimezone2,allDay,rrule,calendar_id) " + 3727b40dde3168f4af2c757cb43955aa3bfe1668666Erik "VALUES (2,1270454462000,1270540863000," + 3737b40dde3168f4af2c757cb43955aa3bfe1668666Erik "'P10S',1270454461000,1270540861000,'America/Los_Angeles','America/Los_Angeles',1," + 3747b40dde3168f4af2c757cb43955aa3bfe1668666Erik "'WEEKLY:MON',1);"); 3757b40dde3168f4af2c757cb43955aa3bfe1668666Erik 3767b40dde3168f4af2c757cb43955aa3bfe1668666Erik // April 5th midnight with 1 day duration, if only dtend was wrong we wouldn't fix it, but 3777b40dde3168f4af2c757cb43955aa3bfe1668666Erik // if anything else is wrong we clear dtend to be sure. 3787b40dde3168f4af2c757cb43955aa3bfe1668666Erik mGoodDb.execSQL("INSERT INTO Events (" + 3797b40dde3168f4af2c757cb43955aa3bfe1668666Erik "_id,dtstart,dtend,duration,dtstart2,dtend2," + 3807b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone,eventTimezone2,allDay,rrule,calendar_id)" + 381e3730b9dce00439666e7ef324a28263a0ee92032Erik "VALUES (2,1270425600000,null,'P1D',1270450800000,null," + 382e3730b9dce00439666e7ef324a28263a0ee92032Erik "'UTC','America/Los_Angeles',1," + 3837b40dde3168f4af2c757cb43955aa3bfe1668666Erik "'WEEKLY:MON',1);"); 3847b40dde3168f4af2c757cb43955aa3bfe1668666Erik 3857b40dde3168f4af2c757cb43955aa3bfe1668666Erik assertEquals(mBadDb.rawQuery("SELECT _id FROM Events;", null).getCount(), 2); 3867b40dde3168f4af2c757cb43955aa3bfe1668666Erik assertEquals(mGoodDb.rawQuery("SELECT _id FROM Events;", null).getCount(), 2); 3877b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 3887b40dde3168f4af2c757cb43955aa3bfe1668666Erik 3897b40dde3168f4af2c757cb43955aa3bfe1668666Erik @MediumTest 390e3730b9dce00439666e7ef324a28263a0ee92032Erik public void testUpgradeToVersion69() { 3917b40dde3168f4af2c757cb43955aa3bfe1668666Erik // Create event tables 3927b40dde3168f4af2c757cb43955aa3bfe1668666Erik createVersion67EventsTable(mBadDb); 3937b40dde3168f4af2c757cb43955aa3bfe1668666Erik createVersion67EventsTable(mGoodDb); 3947b40dde3168f4af2c757cb43955aa3bfe1668666Erik // Fill in good and bad events 3957b40dde3168f4af2c757cb43955aa3bfe1668666Erik addVersion67Events(); 3967b40dde3168f4af2c757cb43955aa3bfe1668666Erik // Run the upgrade on the bad events 397e3730b9dce00439666e7ef324a28263a0ee92032Erik CalendarDatabaseHelper.upgradeToVersion69(mBadDb); 3987b40dde3168f4af2c757cb43955aa3bfe1668666Erik Cursor badCursor = null; 3997b40dde3168f4af2c757cb43955aa3bfe1668666Erik Cursor goodCursor = null; 4007b40dde3168f4af2c757cb43955aa3bfe1668666Erik try { 4017b40dde3168f4af2c757cb43955aa3bfe1668666Erik badCursor = mBadDb.rawQuery("SELECT _id,dtstart,dtend,duration,dtstart2,dtend2," + 4027b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone,eventTimezone2,rrule FROM Events WHERE allDay=?", 4037b40dde3168f4af2c757cb43955aa3bfe1668666Erik new String[] {"1"}); 4047b40dde3168f4af2c757cb43955aa3bfe1668666Erik goodCursor = mGoodDb.rawQuery("SELECT _id,dtstart,dtend,duration,dtstart2,dtend2," + 4057b40dde3168f4af2c757cb43955aa3bfe1668666Erik "eventTimezone,eventTimezone2,rrule FROM Events WHERE allDay=?", 4067b40dde3168f4af2c757cb43955aa3bfe1668666Erik new String[] {"1"}); 4077b40dde3168f4af2c757cb43955aa3bfe1668666Erik // Check that we get the correct results back 4087b40dde3168f4af2c757cb43955aa3bfe1668666Erik assertTrue(compareCursors(badCursor, goodCursor)); 4097b40dde3168f4af2c757cb43955aa3bfe1668666Erik } finally { 4107b40dde3168f4af2c757cb43955aa3bfe1668666Erik if (badCursor != null) { 4117b40dde3168f4af2c757cb43955aa3bfe1668666Erik badCursor.close(); 4127b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 4137b40dde3168f4af2c757cb43955aa3bfe1668666Erik if (goodCursor != null) { 4147b40dde3168f4af2c757cb43955aa3bfe1668666Erik goodCursor.close(); 4157b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 4167b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 4177b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 4187b40dde3168f4af2c757cb43955aa3bfe1668666Erik 4192cff10f1a005bd7302245d4c680cf851193c3a97RoboErik @MediumTest 4202cff10f1a005bd7302245d4c680cf851193c3a97RoboErik public void testUpgradeToCurrentVersion() { 4212cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Create event tables 4222cff10f1a005bd7302245d4c680cf851193c3a97RoboErik bootstrapDbVersion50(mBadDb); 4232cff10f1a005bd7302245d4c680cf851193c3a97RoboErik bootstrapDbVersion50(mGoodDb); 4242cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Fill in good and bad events 4252cff10f1a005bd7302245d4c680cf851193c3a97RoboErik addVersion50Events(); 4262cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Run the upgrade on the bad events 4272cff10f1a005bd7302245d4c680cf851193c3a97RoboErik CalendarDatabaseHelper cDbHelper = new CalendarDatabaseHelper(new MockContext()); 4282cff10f1a005bd7302245d4c680cf851193c3a97RoboErik cDbHelper.onUpgrade(mBadDb, 50, CalendarDatabaseHelper.DATABASE_VERSION); 4292cff10f1a005bd7302245d4c680cf851193c3a97RoboErik Cursor badCursor = null; 4302cff10f1a005bd7302245d4c680cf851193c3a97RoboErik Cursor goodCursor = null; 4312cff10f1a005bd7302245d4c680cf851193c3a97RoboErik try { 4322cff10f1a005bd7302245d4c680cf851193c3a97RoboErik badCursor = mBadDb.rawQuery("SELECT _id,dtstart,dtend,duration," + 4332cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone,rrule FROM Events WHERE allDay=?", 4342cff10f1a005bd7302245d4c680cf851193c3a97RoboErik new String[] {"1"}); 4352cff10f1a005bd7302245d4c680cf851193c3a97RoboErik goodCursor = mGoodDb.rawQuery("SELECT _id,dtstart,dtend,duration," + 4362cff10f1a005bd7302245d4c680cf851193c3a97RoboErik "eventTimezone,rrule FROM Events WHERE allDay=?", 4372cff10f1a005bd7302245d4c680cf851193c3a97RoboErik new String[] {"1"}); 4382cff10f1a005bd7302245d4c680cf851193c3a97RoboErik // Check that we get the correct results back 4392cff10f1a005bd7302245d4c680cf851193c3a97RoboErik assertTrue(compareCursors(badCursor, goodCursor)); 4402cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } finally { 4412cff10f1a005bd7302245d4c680cf851193c3a97RoboErik if (badCursor != null) { 4422cff10f1a005bd7302245d4c680cf851193c3a97RoboErik badCursor.close(); 4432cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } 4442cff10f1a005bd7302245d4c680cf851193c3a97RoboErik if (goodCursor != null) { 4452cff10f1a005bd7302245d4c680cf851193c3a97RoboErik goodCursor.close(); 4462cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } 4472cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } 4482cff10f1a005bd7302245d4c680cf851193c3a97RoboErik } 4492cff10f1a005bd7302245d4c680cf851193c3a97RoboErik 450d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik private static final String SQLITE_MASTER = "sqlite_master"; 451d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 452d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik private static final String[] PROJECTION = {"tbl_name", "sql"}; 453d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 454d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik public void testSchemasEqualForAllTables() { 455d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 456d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik CalendarDatabaseHelper cDbHelper = new CalendarDatabaseHelper(new MockContext()); 457d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik bootstrapDbVersion50(mBadDb); 458d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik cDbHelper.onCreate(mGoodDb); 459d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik cDbHelper.onUpgrade(mBadDb, 50, CalendarDatabaseHelper.DATABASE_VERSION); 460d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik // Check that for all tables, schema definitions are the same between updated db and new db. 461d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik Cursor goodCursor = mGoodDb.query(SQLITE_MASTER, PROJECTION, null, null, null, null, 462d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik "tbl_name,sql" /* orderBy */); 463d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik Cursor badCursor = mBadDb.query(SQLITE_MASTER, PROJECTION, null, null, null, null, 464d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik "tbl_name,sql" /* orderBy */); 465d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 466d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik while (goodCursor.moveToNext()) { 467d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik assertTrue("Should have same number of tables", badCursor.moveToNext()); 468d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik assertEquals("Table names different between upgraded schema and freshly-created scheme", 469d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik goodCursor.getString(0), badCursor.getString(0)); 470d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 471d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik String badString = badCursor.getString(1); 472d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik String goodString = goodCursor.getString(1); 473d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik if (badString == null && goodString == null) { 474d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik continue; 475d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik } 476d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik // Have to strip out some special characters and collapse spaces to 477d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik // get reasonable output 478d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik badString = badString.replaceAll("[()]", ""); 479d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik goodString = goodString.replaceAll("[()]", ""); 480d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik badString = badString.replaceAll(" +", " "); 481d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik goodString = goodString.replaceAll(" +", " "); 482d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik // And then split on commas and trim whitespace 483d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik String[] badSql = badString.split(","); 484d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik String[] goodSql = goodString.split(","); 485d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik for (int i = 0; i < badSql.length; i++) { 486d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik badSql[i] = badSql[i].trim(); 487d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik } 488d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik for (int i = 0; i < goodSql.length; i++) { 489d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik goodSql[i] = goodSql[i].trim(); 490d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik } 491d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik Arrays.sort(badSql); 492d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik Arrays.sort(goodSql); 493d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik assertTrue("Table schema different for table " + goodCursor.getString(0) + ": <" 494d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik + Arrays.toString(goodSql) + "> -- <" + Arrays.toString(badSql) + ">", 495d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik Arrays.equals(goodSql, badSql)); 496d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik } 497d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik assertFalse("Should have same number of tables", badCursor.moveToNext()); 498d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik } 499d5f4742d7ba16d791edd9fd33a1a2a42eeac709bRoboErik 5007b40dde3168f4af2c757cb43955aa3bfe1668666Erik /** 5017b40dde3168f4af2c757cb43955aa3bfe1668666Erik * Compares two cursors to see if they contain the same data. 5027b40dde3168f4af2c757cb43955aa3bfe1668666Erik * 5037b40dde3168f4af2c757cb43955aa3bfe1668666Erik * @return Returns true of the cursors contain the same data and are not null, false 5047b40dde3168f4af2c757cb43955aa3bfe1668666Erik * otherwise 5057b40dde3168f4af2c757cb43955aa3bfe1668666Erik */ 5067b40dde3168f4af2c757cb43955aa3bfe1668666Erik private static boolean compareCursors(Cursor c1, Cursor c2) { 5077b40dde3168f4af2c757cb43955aa3bfe1668666Erik if(c1 == null || c2 == null) { 5087b40dde3168f4af2c757cb43955aa3bfe1668666Erik Log.d("CDBT","c1 is " + c1 + " and c2 is " + c2); 5097b40dde3168f4af2c757cb43955aa3bfe1668666Erik return false; 5107b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5117b40dde3168f4af2c757cb43955aa3bfe1668666Erik 5127b40dde3168f4af2c757cb43955aa3bfe1668666Erik int numColumns = c1.getColumnCount(); 5137b40dde3168f4af2c757cb43955aa3bfe1668666Erik if (numColumns != c2.getColumnCount()) { 5147b40dde3168f4af2c757cb43955aa3bfe1668666Erik Log.d("CDBT","c1 has " + numColumns + " columns and c2 has " + c2.getColumnCount()); 5157b40dde3168f4af2c757cb43955aa3bfe1668666Erik return false; 5167b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5177b40dde3168f4af2c757cb43955aa3bfe1668666Erik 5187b40dde3168f4af2c757cb43955aa3bfe1668666Erik if (c1.getCount() != c2.getCount()) { 5197b40dde3168f4af2c757cb43955aa3bfe1668666Erik Log.d("CDBT","c1 has " + c1.getCount() + " rows and c2 has " + c2.getCount()); 5207b40dde3168f4af2c757cb43955aa3bfe1668666Erik return false; 5217b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5227b40dde3168f4af2c757cb43955aa3bfe1668666Erik 5237b40dde3168f4af2c757cb43955aa3bfe1668666Erik c1.moveToPosition(-1); 5247b40dde3168f4af2c757cb43955aa3bfe1668666Erik c2.moveToPosition(-1); 5257b40dde3168f4af2c757cb43955aa3bfe1668666Erik while(c1.moveToNext() && c2.moveToNext()) { 5267b40dde3168f4af2c757cb43955aa3bfe1668666Erik for(int i = 0; i < numColumns; i++) { 5277b40dde3168f4af2c757cb43955aa3bfe1668666Erik if(!TextUtils.equals(c1.getString(i),c2.getString(i))) { 5287b40dde3168f4af2c757cb43955aa3bfe1668666Erik Log.d("CDBT", c1.getString(i) + "\n" + c2.getString(i)); 5297b40dde3168f4af2c757cb43955aa3bfe1668666Erik return false; 5307b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5317b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5327b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5337b40dde3168f4af2c757cb43955aa3bfe1668666Erik 5347b40dde3168f4af2c757cb43955aa3bfe1668666Erik return true; 5357b40dde3168f4af2c757cb43955aa3bfe1668666Erik } 5367b40dde3168f4af2c757cb43955aa3bfe1668666Erik} 537