MobileMe Sync and the fiasco that followed
Some of you may know (or not) but you cannot, unless I messed up somewhere, sync your contacts/calendars/etc. with MobileMe if you are on an unlocked iPhone.
I unfortunately had to find this out the hard way, which wasn't just a simple "hey it didn't work" but more of a it didn't work and completely destroyed my calendar and addressbook.
This happened over me trying to sync it a couple of times over and the resulting effect of creating duplicates for each contact and event I had.
Now this may seem like a simple solution for some that sync every day, but this wasn't my case.
I hadn't sync'd in a month and had added/removed contacts and events that now became a mess.
I thought that a simple sync and edit via AddressBook or outlook would put me back on track, but I was hit with the dreaded 'iPhone contact sync merge error'
I then decided to dig into it a little bit and figure out if I could salvage my events via means of sql.
This turned out positive (or at least I think so) and therefore I've decided to share it with the community.
This hopefully will be a 2 part post, as I still need to recover my contacts.
Anyway, enough of this, here's how to recover your calendar events, when dups get created.
1. This assumes that you have a jailbroken iPhone and know how to get in
2. I am NOT responsible if you lose all your contact/events, images, videos, apps, precious pr0n bookmarks or if your house catches on fire and your sister catches an std because your dad refused to wear a condom.
3. This is not for the faint of heart, since you're dealing straight with the data without any clickity-click and undo to save you, and is therefore of last resort if none of the syncing wizardry worked.
4. WARNING THIS CAN DESTROY ALL YOUR DATA, so backup your files before replacing the original with your modified file
Now on to the tutorial:
1. Copy /private/var/mobile/Library/Calendar/Calendar.sqlitedb to your computer
2. Download an SQLite browser - I'm on a Mac and have had great success with MesaSQLite. There's also SQLite Browser as an open source solution
3. Open the Calendar.sqlitedb (make sure you backed it up once on your computer just in case)
4. To show all your duplicates and how many of each there are, run the following in the 'Custom query' field:
This will provide you with the list of duplicates that will later on be used to identify the events that need to be removed. In my current case, I only needed to check for summary, but you can also use the location and notes field to make sure you're finding all the true duplicates.
select rowid, summary, count(summary) as total from event group by summary having( count(summary)>1)
One of the issue of recurring events, is that they are never really removed. Instead, they are set in the past, and therefore can cause problem when doing such cleanup.
5. The first step is to then identify the duplicates that are associated with a recurring event in the past and remove them. This is done with the following query:
select event_id from Recurrence where event_id in (select rowid from event where summary in (select summary from event group by summary having( count(summary)>1)) order by summary) and end_date != 0
In my case I only have recurrent event for birthdays (therefore every year) you may have to look at a specific event that repeats every day/week/2weeks/month to make sure that you aren't removing incorrect data.
delete from Recurrence where event_id in (select rowid from event where summary in (select summary from event group by summary having( count(summary)>1)) order by summary) and end_date != 0
6. The first query above will always return the latest entered duplicate. Which is the one I wanted to remove and thus keep the original event. This is done with the following delete:
You may need to repeat this last command if you have more than 1 duplicate. (basically run it for the max number of duplicates -1)
delete from event where rowid in (select rowid from event group by summary having( count(summary)>1))
7. If you were to load the file back into your iPhone, you'll find that your events aren't all under the same Calendar.
If this is how you wanted it then great. In my case I wanted to reconcile all events under on main calendar ('Calendar').
The original iPhone Calendar app will have 3 calendars:
To have all my events under the original 'Calendar' I just need to move them all to calendar_id=3 as so:
update event set calendar_id=3 where calendar_id != 3
You can now remove the additional calendars from the calendar table. Either by query:
Or by going to the 'table content' tab, selecting 'Calendar' from the dropdown and show all. Just select the rows you'd like to remove.
delete from calendar where calendar_id >3
Save/Exit MesaSQLite and upload the newly saved Calendar.sqlitedb to the iPhone. Verify that you've got all your events and that they're all under the calendars you've expected.
Give yourself a tap on the back, you've just saved your calendar from mayhem!