How to inspect space usage of a profile database

Show and Tell
1

Basics

You can already find the following summary stats under Settings → Advanced → View Log:

  • Size of SQLite database and blob directory
  • Largest tables in MB and rows
  • Webxdc with biggest status update in MiB and update count, including its message ID

Disk usage of each message text

select
octet_length(coalesce(rfc724_mid, '')) +
octet_length(coalesce(server_folder, '')) +
octet_length(coalesce(txt, '')) +
octet_length(coalesce(txt_raw, '')) +
octet_length(coalesce(param, '')) +
octet_length(coalesce(mime_headers, '')) +
octet_length(coalesce(mime_in_reply_to, '')) +
octet_length(coalesce(mime_references, '')) +
octet_length(coalesce(error, '')) +
octet_length(coalesce(subject, '')) +
octet_length(coalesce(hop_info, '')) +
octet_length(coalesce(txt_normalized, '')) +
bytes as len,
octet_length(coalesce(mime_headers, '')) as HTML,
octet_length(coalesce(param, '')) as cite_xdc,
*
from msgs
order by len
desc;

Disk usage of message text grouped by chat

select
sum(
octet_length(coalesce(rfc724_mid, '')) +
octet_length(coalesce(server_folder, '')) +
octet_length(coalesce(txt, '')) +
octet_length(coalesce(txt_raw, '')) +
octet_length(coalesce(m.param, '')) +
octet_length(coalesce(mime_headers, '')) +
octet_length(coalesce(mime_in_reply_to, '')) +
octet_length(coalesce(mime_references, '')) +
octet_length(coalesce(error, '')) +
octet_length(coalesce(subject, '')) +
octet_length(coalesce(hop_info, '')) +
octet_length(coalesce(txt_normalized, ''))) as len,
sum(octet_length(coalesce(mime_headers, ''))) as HTML,
sum(octet_length(coalesce(m.param, ''))) as cite_xdc,
c.name as chat
from msgs as m, chats as c
where c.id = m.chat_id
group by c.id
order by len
desc;

Disk usage of each webxdc state update

select
octet_length(coalesce(update_item, '')) +
octet_length(coalesce(uid, '')) as len,
txt,
chats.name as chat,
datetime(msgs.timestamp, 'unixepoch', 'localtime') as date,
*
from msgs_status_updates as u, msgs, chats
where msgs.id = u.msg_id
and chats.id = msgs.chat_id
order by len
desc;

Disk usage of webxdc state grouped by app and chat

select
sum(
octet_length(coalesce(update_item, '')) +
octet_length(coalesce(uid, ''))) as data,
m.bytes as code,
chats.name as chat,
datetime(m.timestamp, 'unixepoch', 'localtime') as time,
substr(m.param,instr(m.param,concat(char(10),"v="))+3) as app,
m.id as msg_id,
m.param
from msgs_status_updates as u, msgs as m, chats
where m.id = u.msg_id
and chats.id = m.chat_id
group by chats.id, m.id
order by data
desc;

Filename of big attachments

cat << EOF |
select
substr(substr(m.param,instr(m.param,concat(char(10),"f="))+3),1,instr(substr(m.param,instr(m.param,concat(char(10),"f="))+3),char(10))-1) as file
from msgs as m
where bytes > 123456
;
EOF
sqlite3 -csv -batch ../dc.db |
cut -d '/' -f 2- |
xargs echo rm
1 Like