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:

Code:

select  rowid, summary, count(summary) as total from event group by summary having( count(summary)>1)

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.

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:
Find them:

Code:

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

Delete them:

Code:

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

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.

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:

Code:

delete from event where rowid in (select  rowid from event group by summary having( count(summary)>1))

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)

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:
1=>'Default' (hidden)
2=>'Home'
3=>'Calendar'.

To have all my events under the original 'Calendar' I just need to move them all to calendar_id=3 as so:

Code:

update event set calendar_id=3 where calendar_id != 3

You can now remove the additional calendars from the calendar table. Either by query:

Code:

delete from calendar where calendar_id >3

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.

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!

Let's now move on to fixing all of your contacts. Once again this assumes all the above and will also assume that you'd like all contacts to be kept within one main group

1. Copy /private/var/mobile/Library/Calendar/Calendar.sqlitedb to your computer
2. Open MesaSQLite (it's been downloaded and used above!)

I found that the easiest way to find duplicates is via phone numbers. You can then do the same thing for emails.

3. Find all duplicate phone numbers and the count:

Code:

select *, count(value) as total from abmultivalue where property=3 group by value having count(value)>1

In the ABMultiValue table, the phone numbers have property=3, email have property=4


4. Delete all contacts that have duplicate phone numbers:

Code:

delete from abperson where rowid in (select record_id from abmultivalue where property=3  group by value having count(value)>1)

5. Finally delete the duplicate phone numbers:

Code:

delete from abmultivalue where rowid in (select rowid from abmultivalue  where property=3 group by value having count(value)>1)

Repeat step 4 and 5 for the total amount of duplicates - 1

6. Before you upload the new file, make sure to clean up the possible duplicate address. You can find if you have any with the following:

Code:

select * from ABMultiValueEntry where parent_id not in (select uid from abmultivalue)

7. Same as above, if you with to delete these entries:

Code:

delete from ABMuliValueEntry where parent_id in (select parent_id from ABMultiValueEntry where parent_id not in (select uid from abmultivalue))

8. Save/Exit MesaSQLite and upload the Addressbook.sqlitedb to your phone.

You should now be able to view all of your contacts without their duplicates! Hooray!

ps: I've found that for the addressbook to be re-loaded it is sometimes needed to reboot the phone.

As a side note make sure to CHOWN your sqlitedb files to mobile:mobile.

Otherwise you wont be able to write to them and therefore wont be able to save new numbers/events

edit - Also you may find yourself with contacts that had previously been flagged as 'sync contacts'. In order for them to no-longer be flagged as such, run the following command:

Code:

update abperson set storeid=0 where storeid=1

You can then delete all extra sync protocols in the ABStore table. Just remember to keep the first one with id of 0