ladas
root@gromi:/var/lib/gromox/user/clownflare.de/cb/exmdb# sqlite3 -readonly exchange.sqlite3 << EOF
> -- THIS ONE IS JUST TO GET FANCY FOLDERNAMES!
> CREATE VIEW IF NOT EXISTS temp.folderlist
> AS SELECT distinct(p.folder_id),
> p.parent_id,
> f1.propval AS foldername
> FROM folders p
> JOIN folder_properties f1
> ON f1.folder_id = p.folder_id
> AND f1.proptag = 805371935
> ORDER BY p.folder_id
> ;
> CREATE VIEW IF NOT EXISTS temp.messagecount AS
> SELECT fl.folder_id AS id,
> fl.foldername AS folder,
> SUM(m.message_size) as foldersize,
> count(m.message_id) AS count
> FROM folderlist fl
> LEFT JOIN messages m
> ON fl.folder_id = m.parent_fid
> AND m.is_deleted = 0
> WHERE fl.parent_id > 1
> GROUP BY fl.folder_id,
> fl.foldername
> ;
> SELECT * from temp.messagecount;
> EOF
10|Sent Items|114301113|1913
11|Deleted Items|633611|38
12|Outbox||0
13|Inbox|543232387|5034
14|Drafts||0
.......
I recommend using sqlitebrowser with a local copy of the database for trying out things.
You can run the temp.views in there and than just do e.g. SELECT * FROM temp.messagecount
Also note that i only run those with the -readonly switch with sqlite3 on live databases to not leave any clutter even tho all VIEWS are created as temporary ones.
