[Android Software/Hacking General [Developers Only]] dominik6:
First of all I would like to say hello to everyone as this is my first post to this forum :)

I have android phone (HTC Desire Z) for about a week now an I just have to share how insanely awesome this device is! To show you what I mean I'll describe how to fix the problem with incorrect transfer of calendar notes from my old Nokia N95 8GB to new HTC.

I rooted my HTC and uploaded newest Sense ROM (2.1) with Android 2.3.3 from the forum (thanks for making that possible!).

Now, to the problem. I wanted to transfer calendar notes from N95 to my new phone (more than 200 of them). Using the transfer application, the notes got copied but all had lost their alarms! This was disaster for me as I'm depending on these notes on daily basis. I googled for this problem but didn't find any solution. But this is the Android - everything can be done, right?

So, the solution. I've read somewhere that Android stores data of all it's applications as sqlite databases. As I professionally work with sql databases this seemed to be to good to be true... But no, it is true :D

To the point then. It is possible to fix my calendar by executing sql queries directly against the database itself!

I've found and pulled the calendar database file from the device by

adb pull /data/data/com.android.providers.calendar/databases/calendar.db

and got inside

sqlite3 calendar.db

Let's see what tables we have

.tables

Attendees EventsRawTimes android_metadata
CalendarAlerts ExtendedProperties easSyncInfo
CalendarCache Instances easTracking
CalendarMetaData Reminders pcscSyncInfo
Calendars _sync_state pcscTracking
Events _sync_state_metadata view_events

Interesting tables are Events and Reminders. Let's extract their ddls

.schema Events
.schema Reminders

CREATE TABLE Events (_id INTEGER PRIMARY KEY AUTOINCREMENT,_sync_account TEXT,_sync_account_type TEXT,_sync_id TEXT,_sync_version TEXT,
_sync_time TEXT,_sync_local_id INTEGER,_sync_dirty INTEGER,_sync_mark INTEGER,
calendar_id INTEGER NOT NULL,htmlUri TEXT,title TEXT,eventLocation TEXT,description TEXT,
eventStatus INTEGER,selfAttendeeStatus INTEGER NOT NULL DEFAULT 0,commentsUri TEXT,
dtstart INTEGER,dtend INTEGER,eventTimezone TEXT,duration TEXT,allDay INTEGER NOT NULL DEFAULT 0,
visibility INTEGER NOT NULL DEFAULT 0,transparency INTEGER NOT NULL DEFAULT 0,
hasAlarm INTEGER NOT NULL DEFAULT 0,hasExtendedProperties INTEGER NOT NULL DEFAULT 0,
rrule TEXT,rdate TEXT,exrule TEXT,exdate TEXT,originalEvent TEXT,originalInstanceTime INTEGER,
originalAllDay INTEGER,lastDate INTEGER,hasAttendeeData INTEGER NOT NULL DEFAULT 0,
guestsCanModify INTEGER NOT NULL DEFAULT 0,guestsCanInviteOthers INTEGER NOT NULL DEFAULT 1,
guestsCanSeeGuests INTEGER NOT NULL DEFAULT 1,organizer STRING,deleted INTEGER NOT NULL DEFAULT 0,
dtstart2 INTEGER,dtend2 INTEGER,eventTimezone2 TEXT,syncAdapterData TEXT, importance INTEGER NOT NULL DEFAULT 1,
iCalGUID TEXT,last_update_time TEXT,parentID INTEGER,facebook_source_id TEXT,facebook_type TEXT,
facebook_avatar_large TEXT,facebook_avatar_small TEXT,facebook_avatar_local TEXT,
MeetingStatus INTEGER NOT NULL DEFAULT 0);

CREATE TABLE Reminders (_id INTEGER PRIMARY KEY,event_id INTEGER,minutes INTEGER,method INTEGER NOT NULL DEFAULT 0);

and let's take a look at the data inside

SELECT * FROM Events WHERE _id=208;
SELECT * FROM Reminders WHERE event_id=208;

for example

208|PC Sync|com.htc.pcsc|||||1||1||Test1||||1||1304445600000|1304449200000|Europe/Brussels|P3600S|0|0|0|1|1|||||||||1|0|1|1|Outlook|0|||||1|8effd54b-5cd3-451c-8602-2d969f1a84c4|2011-05-03 17:20:36|||||||0

and

203|208||0

Interesting columns are Events.hasAlarm, Reminders.minutes and Reminders.method.

So recall that I have over 200 calendar entries that had lost their alarms during transfer. How to fix this? Simply

UPDATE Events SET hasAlarm=1;
UPDATE Reminders SET method=1;
UPDATE Reminders SET minutes=10;

Now put the modified database back to the phone

adb push calendar.db /data/data/com.android.providers.calendar/databases/calendar.db

and reboot.

And that's it. All alarms are now set properly and work! To attain Nokia-like calendar alerts I'm using application 'Calendar Snooze' and it works like a charm!

Thanks for reading and sorry for being overjoyed but sheer possibility to fix the data in the cellphone by directly accessing it's sql database is so amazing :)

Maybe for you guys it's not so amazing after all but for someone who just switched from Nokia and found out something like that for the first time it's incredible.

I hope that this post will possibly help someone with similar problem one day :)

Kind regards,

dominik6

PS.

The possibilities are endless. You can add indices to tables (to maybe speed up some weird searches); add triggers (for example it's easy to do a inside-database backup by using an after insert trigger to duplicate data to another table), one can bulk update/insert/delete entries in calendar, and so on...

PPS.

SMS and contacts are also stored in sqlite databases...