So I Deleted a Calendar on my iPhone

I didn’t expect it would be difficult to delete a calendar from my iPhone, but it was.

I recently upgraded to iPhone OS 3.0 (took me a while, I know), and began using the built-in calendar functionality to sync with Google Calendar. I had previously been using an application called “NemusSync”, so now I had too copies of my google calendar information displayed in the calendar application. I wanted to get rid of the old one, but there doesn’t appear to be a UI for it, and doing a Google search on how to delete an iphone calendar wasn’t immediately helpful.

This method works for jailbroken phones, and is probably more involved than you want it to be, but it is what I did. It requires SSH, Python, and the sqlite3 module (all installed via Cydia).

Also, I’m only hiding the extra calendar – the data is still on the phone, but I don’t have enough data there that I’m worried about it – you could use the same method to actually delete the data from the database if you want.

Here we go, with comment lines beginning with ‘#’:

# connect to your phone, enter password when prompted
$ ssh root@PHONE_IP_ADDRESS
Password:

# start the Python interpreter
/var/root# python
Python 2.5.1 (r251:54863, xx/xx/xx, xx:xx:xx)
[GCC 4.2.1 (Based on Apple Inc. build 5555)] on darwin
Type "help", "copyright", "credits" or "license" for more information.

# import the SQLITE db module, and connect to the database
>>> import sqlite3
>>> name='/private/var/mobile/Library/Calendar/Calendar.sqlitedb'
>>> c = sqlite3.connect( name )
>>> def printall( x ):
... i = 0
... for r in x.fetchall():
... i+= 1
... print i, r
...
>>> printall( c.execute( 'select * from Calendar' ))
1 (1, 1, u'Default', 0, 1, 0, -1, -1, -1, None, None, None, None, None, None, None)
2 (2, 1, u'local', 0, 0, 0, 127, 127, 127, 1, None, 0, None, None, None, None)
3 (3, 1, u'MYADDRESS@gmail.com', 0, 0, 0, 163, 41, 41, 1, None, None, None, None, None, None)
4 (4, 2, u'MYADDRESS@gmail.com', 0, 0, 0, 163, 41, 41, 0, None, 0, u'MYADDRESS%40gmail.com@www.google.com/calendar/dav/MYADDRESS%40gmail.com/events/', u'MY_ID', None, None)

>>> printall( c.execute( 'update calendar set hidden=1 where rowid=3'))
>>> c.commit()
>>>

And that should hide the old calendar from the Calendar application.

Note that I did have to explore the database and figure out how to inspect the schema of the sqlite database to find the table and column names. Preserving that information here for the collective memory…


>>> c.execute( '''show tables''' )
Traceback (most recent call last):
File "", line 1, in
sqlite3.OperationalError: near "show": syntax error

# shoot, no "show tables". ...googling... OK:
>>> printall( c.execute( 'select name from sqlite_master where type="table";'))
1 (u'_SqliteDatabaseProperties',)
2 (u'Store',)
3 (u'sqlite_sequence',)
4 (u'Alarm',)
5 (u'AlarmChanges',)
6 (u'Recurrence',)
7 (u'RecurrenceChanges',)
8 (u'OccurrenceCache',)
9 (u'OccurrenceCacheDays',)
10 (u'GCalAccounts',)
11 (u'GCalCalendars',)
12 (u'GCalState',)
13 (u'GCalEvents',)
14 (u'Calendar',)
15 (u'CalendarChanges',)
16 (u'Event',)
17 (u'EventChanges',)
18 (u'EventExceptionDate',)
19 (u'Task',)
20 (u'TaskChanges',)
21 (u'Attendee',)
22 (u'AttendeeChanges',)
23 (u'Participant',)

# Calendar looks good...
>>> printall( c.execute( "describe calendar" ))
Traceback (most recent call last):
File "", line 1, in
sqlite3.OperationalError: near "describe": syntax error

# dang, no "describe TABLE". ...googling... OK
>>> printall( c.execute( "PRAGMA table_info(Calendar)" ))
1 (0, u'ROWID', u'INTEGER', 0, None, 1)
2 (1, u'store_id', u'INTEGER', 0, None, 0)
3 (2, u'title', u'TEXT', 0, None, 0)
4 (3, u'read_only', u'INTEGER', 0, None, 0)
5 (4, u'hidden', u'INTEGER', 0, None, 0)
6 (5, u'immutable', u'INTEGER', 0, None, 0)
7 (6, u'color_r', u'INTEGER', 0, None, 0)
8 (7, u'color_g', u'INTEGER', 0, None, 0)
9 (8, u'color_b', u'INTEGER', 0, None, 0)
10 (9, u'color_is_display', u'INTEGER', 0, None, 0)
11 (10, u'type', u'TEXT', 0, None, 0)
12 (11, u'supported_entity_types', u'INTEGER', 0, None, 0)
13 (12, u'external_id', u'TEXT', 0, None, 0)
14 (13, u'external_mod_tag', u'TEXT', 0, None, 0)
15 (14, u'external_id_tag', u'TEXT', 0, None, 0)
16 (15, u'external_rep', u'BLOB', 0, None, 0)

# that "hidden" column looks useful...

2 Comments »

  1. Brendan Said,

    December 22, 2009 @ 7:20 am

    Wow – this is waaaayy over the top: why not simply use sqlite3 to access the databases directly? Did you know you can get an sqlite addon for Firefox, allowing you to do all of this visually?

  2. kb Said,

    December 22, 2009 @ 10:12 am

    Brendan, thanks for the feedback. Hadn’t realized the sqlite3 executable was available on the jailbroken iphone, and I wasn’t aware of the SQLite Manager addon for Firefox.

    For either of these, you’ll still need to have the phone jailbroken. If you want to use the Firefox addon, you can either mount the iPhone filesystem with something like sshfs (Linux, Win) or copy the datafile to the desktop, modify it, then copy it back.

    Alternatively, using the sqlite3 command-line utility on the iphone:

    # sqlite3 /private/var/mobile/Library/Calendar/Calendar.sqlitedb
    SQLite version 3.6.12
    Enter ".help" for instructions
    sqlite> .help
    ...
    sqlite> .tables
    Alarm                      GCalEvents
    AlarmChanges               GCalState
    Attendee                   OccurrenceCache
    AttendeeChanges            OccurrenceCacheDays
    Calendar                   Participant
    CalendarChanges            Recurrence
    Event                      RecurrenceChanges
    EventChanges               Store
    EventExceptionDate         Task
    GCalAccounts               TaskChanges
    GCalCalendars              _SqliteDatabaseProperties
    sqlite> .schema Calendar
    CREATE TABLE Calendar (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, store_id INTEGER, title TEXT, read_only INTEGER, hidden INTEGER, immutable INTEGER, color_r INTEGER, color_g INTEGER, color_b INTEGER, color_is_display INTEGER, type TEXT, supported_entity_types INTEGER, external_id TEXT, external_mod_tag TEXT, external_id_tag TEXT, external_rep BLOB);
    CREATE INDEX CalendarExternalId on Calendar(external_id);
    CREATE INDEX CalendarStoreId on Calendar(store_id);
    CREATE TRIGGER delete_calendar_members AFTER DELETE ON Calendar
    BEGIN
    DELETE FROM OccurrenceCacheDays where calendar_id = OLD.ROWID;DELETE FROM OccurrenceCache where calendar_id = OLD.ROWID;DELETE FROM Task WHERE calendar_id = OLD.ROWID;DELETE FROM Event WHERE calendar_id = OLD.ROWID;DELETE FROM OccurrenceCacheDays WHERE count = 0;
    END;
    sqlite> select * from Calendar;
    ...
    and so forth...
    

    Nice!

    kb

Leave a Comment