SMS database
Ok here is my story. My n900 was slow as hell. Because of that i backed her up and wiped her clean ;) I restored Messages with nokia pc suit but now all the messages are sorted wrong. First are the messages that i recieved and on the end there are my sent messages. OK.... after a month i had enough. I opened the database file and started analyzing what is wrong.
you can find the database here
Code:
/home/user/.rtcom-eventlogger/el-v1
I found out that the messages are sorted from the column storage_time and not start_time. It looks like the date stored in the column storage_time for the restored messages is the date the data was restored.
I just wanted to share this with you. What do you thing should the messages be sorted by storage_time or start_time. I think it should be
start_time.
Now i need to copy the start_time column into storage_time. I hope it works ;)
ok i tried to do this:
1. create empy table
2. copy all data from events to the new table odedred by start_time asc
3. empty the events table
4. copy the data back - couldn't do this... some id problems
used this code if someone knows why it didn't work ;)
Code:
CREATE TABLE Events2 (id INTEGER PRIMARY KEY AUTOINCREMENT,service_id INTEGER NOT NULL,event_type_id INTEGER NOT NULL,storage_time INTEGER NOT NULL,start_time INTEGER NOT NULL,end_time INTEGER,is_read INTEGER DEFAULT 0,outgoing BOOL DEFAULT 0,flags INTEGER DEFAULT 0,bytes_sent INTEGER DEFAULT 0,bytes_received INTEGER DEFAULT 0,local_uid TEXT,local_name TEXT,remote_uid TEXT,channel TEXT,free_text TEXT,group_uid TEXT);
INSERT INTO Events2
(service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid)
select service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid from events order by start_time asc
delete from events
INSERT INTO Events
(id, service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid)
select id, service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid from events2 order by start_time asc
ok... after playing a while with the sqlite i found another solution that worked for me. The only bad thing is that the id starts from the last row forward as data is pasted into the original table.
Code:
CREATE TABLE Events2 (id INTEGER PRIMARY KEY AUTOINCREMENT,service_id INTEGER NOT NULL,event_type_id INTEGER NOT NULL,storage_time INTEGER NOT NULL,start_time INTEGER NOT NULL,end_time INTEGER,is_read INTEGER DEFAULT 0,outgoing BOOL DEFAULT 0,flags INTEGER DEFAULT 0,bytes_sent INTEGER DEFAULT 0,bytes_received INTEGER DEFAULT 0,local_uid TEXT,local_name TEXT,remote_uid TEXT,channel TEXT,free_text TEXT,group_uid TEXT);
INSERT INTO Events2
(service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid)
select service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid from events order by start_time asc
INSERT INTO Events
(id, service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid)
select null, service_id, event_type_id, storage_time, start_time, end_time, is_read, outgoing, flags, bytes_sent, bytes_received, local_uid, local_name, remote_uid, channel, free_text, group_uid from events2 order by start_time asc
after this i got the max id from events2 table ;) xxxx number
Code:
drop table events2;
delete from events where id <= xxxxx
after this i also did this but it is kinda optional but should speed up the database a bit imo
Code:
reindex events;
vacuum;
OK hope this helps someone. If you do this you can use sqlite3 on your n900 and paste the sentences in. I copied the database file from my n900 to my pc and edited it there and copied back ;) |
