Просмотреть все чаты (в неудобном виде пока, но не суть важно):

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT * FROM Events"

 

Посчитать все входящие и исходящие смски пользователю с номером XXXXXXXX:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT count(*) FROM Events WHERE event_type_id=7 AND remote_uid LIKE '%XXXXXXXX%'"

 

То же, но только исходящие смс-ки:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT count(*) FROM Events WHERE event_type_id=7 AND outgoing=1 AND remote_uid LIKE '%XXXXXXXX%'"

 

Счётчик всех отправленных смс без конкретного номера:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT count(*) FROM Events WHERE event_type_id=7 AND outgoing=1"

 

Посчитать количество символов, которые вы настрочили за все время пользователю XXXXXXXXX: 

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT free_text FROM Events WHERE event_type_id=7 AND outgoing=1 AND remote_uid LIKE '%XXXXXXXXXX%'" | wc -c

 

Показать все смски, отправленные пользователю XXXXXXXX за январь 2011:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT strftime('%Y-%m-%d %H-%M-%S', start_time, 'unixepoch'),' - ', free_text FROM Events WHERE event_type_id=7 AND outgoing=1 AND remote_uid LIKE '%XXXXXXXX%' AND strftime('%Y%m', start_time, 'unixepoch')='201101'"

 

Показать все ВХОДЯЩИЕ телефонные звонки пользователю XXXXXXXXXX за январь 2011:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT strftime('%Y-%m-%d %H-%M-%S', start_time, 'unixepoch') FROM Events WHERE event_type_id=1 AND outgoing=0 AND remote_uid LIKE '%XXXXXXXXXXX%' AND strftime('%Y%m', start_time, 'unixepoch')='201101'"

 

Показать все ИСХОДЯЩИЕ телефонные звонки пользователю XXXXXXXXXX за январь 2011:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT strftime('%Y-%m-%d %H-%M-%S', start_time, 'unixepoch') FROM Events WHERE event_type_id=1 AND outgoing=1 AND remote_uid LIKE '%XXXXXXXXXX%' AND strftime('%Y%m', start_time, 'unixepoch')='201101'"

 

Сохранение в TXT:

sqlite3 /home/user/.rtcom-eventlogger/el-v1.db "SELECT * FROM Events" >/home/user/MyDocs/file.txt

 

---

 

Nokia N900 is a great smart phone but not without its hiccups. When you are having long conversations, sometimes your messages start disappearing. sometimes the messaging doesn’t work and shows the in progress but the message has already been sent. But once you restart the phone, you get all the pending messages which were lost previously but your own sent messages are still not part of the conversation. Sometimes It shows some of your messages and some are not there and some are shown as still sending with the arrow icon for hours. If you are experiencing one or all of the weird things on your Nokia N900 conversations, you have come to the right blog post.

 

This problem occurs because the Headers table in the event database /home/user/.rtcom-eventlogger/el-v1.db becomes corrupt. let’s try to repair it on the device and there are several ways.

 

First Method – Optimize the event database

1. Close all the application and go offline

$ sudo gainroot

2.

# sqlite3 /home/user/.rtcom-eventlogger/el-v1.db VACUUM;

3. Reboot

4. Go online

What does VACUUM command do?

The VACUUM command rebuilds the entire database. There are several reasons an application might do this:

Unless SQLite is running in “auto_vacuum=FULL” mode, when a large amount of data is deleted from the database file it leaves behind empty space, or “free” database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the database reclaims this space and reduces the size of the database file.

Frequent inserts, updates, and deletes can cause the database file to become fragmented – where data for a single table or index is scattered around the database file.

Running VACUUM ensures that each table and index is largely stored contiguously within the database file.

In some cases, VACUUM may also reduce the number of partially filled pages in the database, reducing the size of the database file further.

 

Second Option – Try to repair event database

If the VACUUM command has not done the trick for you, let;s try an advanced method by re-importing the conversations data.

#gain root access:

sudo gainroot

#navigate to target directory:

cd .rtcom-eventlogger

#Dump existing database to a SQL file:

sqlite3 el-v1.db .dump > el-v1.db.sql

#Restore the SQL to a new database:

sqlite3 el-v1.db.new < el-v1.db.sql

#You may ignore SQL errors at this stage

#they occur due to corruption in the source.

#Now overwrite the broken DB (you may want to backup the original db)

cp el-v1.db.new el-v1.db

#Finally, ensure that the file is owned by 'user'

#and is in the 'users' group so it may be read by Conversations

chown user el-v1.db

chgrp users el-v1.db

Now close and re-open your Conversations app.

Last Option – Delete the corrupted event database

 

Sometimes the el-v1 database becomes corrupted and one or more specific conversation threads can no longer be accessed. The easiest way is simply to delete it and start over. So if all else fails, you can remove the event database to clear all the corrupted data and start afresh by typing following:

 

Do not do this if you want to keep your messages, it will remove your all conversations.

 

sudo gainroot

rm -rf /home/user/.rtcom-eventlogger/el-v1.db

killall rtcom-messaging-ui

 

Hope it helps.

 

Cheers!