Move messages to folder with sqlite = OK to do? any side-effects?

freeloadermatt

Quick question on accessing user mail stores using sqlite:
Would there be any side-effects to doing things like deleting messages or moving them between folders by using sqlite statements in scripts?

Example, this sqlite statement moves a message from one folder to another:
UPDATE messages SET parent_fid = XX WHERE message_id = YY

Running this against a test copy of a server does seem to have the effect you would expect: using Web GUI you can see the message in its original folder before the command is run, then hit refresh and it has moved to the desired folder.

Are there any gotchas we need to look out for like information being cached in other places apart from sqlite etc etc?

WalterH

There is the ICS (change system). You need to update the ICS when moving items.

freeloadermatt

WalterH
Is this "ICS" as in calendar entries?

If yes then cool, as I was intending only to move only emails and not meetings.
If something else, please point me at the right place?

freeloadermatt

Thanks @WalterH I'll have a look and figure out what to do.

mwilliams

No, @WalterH means this ICS: https://learn.microsoft.com/en-us/openspecs/exchange_server_protocols/ms-oxcfxics/b9752f3d-d50d-44b8-9e6b-608a117c8532

mwilliams

Overall you should refrain from any modifications in the sqlite directly. Reading: OK. Writing: If you don't know MAPI you should leave it alone or really dive into MAPI before you think even remotely of touching data.

crpb

you could export the message and import it in another folder and then delete the original.
i think you basically know how to as you have looked at the script i linked in another thread and i'm currently to lazy to look it all up 😛.

mwilliams

crpb

grmox-mbop delmsg

crpb

mwilliams
yes, but first you should use either of gromox-exm2{ical,vcf,eml,mt} to export a object and then use gromox-mt2exm to reimport again. and of course read gromox-mbop(8) for the Folder specification.
Well not you but the general public *g*

crpb

And because i have my sqlitebrowser still open i just drop this (with too much comments in it) folder name lookup query...

/* FOLDERS WHERE CONTAINERCLASS ( 907214879 ) IS A MAILDIR( IPF.Note ) GREATER EQUAL TOPOFINFORMATIONSTORE ( 9) */
-- SELECT folder_id FROM folder_properties WHERE proptag = '907214879' AND propval = 'IPF.Note' AND folder_id >=9

/* FOLDERNAMES */
-- SELECT folder_id, propval FROM folder_properties WHERE proptag = '805371935' ORDER BY folder_id

/* FOLDERNAMES AND IDS FOR MAILDIRS */
-- SELECT DISTINCT folder_id AS FOLDERID , propval AS FOLDERNAME FROM folder_properties WHERE proptag = '805371935' AND folder_id IN (SELECT DISTINCT folder_id FROM folder_properties WHERE proptag = '907214879' AND propval = 'IPF.Note' AND folder_id >=9)

/* FIND FOLDERID FOR FOLDER NAMED LIKE */
--  SELECT folder_id FROM folder_properties WHERE propval LIKE '%non-junk%'
-- SELECT folder_id, propval FROM folder_properties WHERE proptag = '805371935' ORDER BY folder_id

-- SELECT folder_id AS FOLDERID , propval AS FOLDERNAME FROM folder_properties WHERE FOLDERNAME LIKE '%non-junk%' AND proptag = '805371935' AND folder_id IN (SELECT DISTINCT folder_id FROM folder_properties WHERE proptag = '907214879' AND propval = 'IPF.Note' AND folder_id >=9)
/* sqlformat -k upper -i lower --reindent_aligned */
SELECT folder_id AS folderid,
       propval AS foldername
  FROM folder_properties
 WHERE foldername LIKE '%non-junk%'
   AND proptag = '805371935'
   AND folder_id IN (
        SELECT DISTINCT folder_id
          FROM folder_properties
         WHERE proptag = '907214879'
           AND propval = 'IPF.Note'
           AND folder_id >=9
       )

crpb

And now i thought about how to query/generate the folder spec for folders deeper than IPM_SUBTREE/INBOX/FOLDERNAME but my sql skills are not that good.. i could solve it with a loop in a shell script but if that is solvable with a single query it would be much better. bascially just loop over it until parent_fid=9 and in the end concat the foldernames together or something like it 🤔

freeloadermatt

Thanks @mwilliams @crpb !

Export/import and then delmsg will work for my use case: need to touch multiple users, running in python as superuser, but don’t have individual users unencrypted passwords so can’t use IMAP or EWS as they would both require authentication to each mailbox in turn.

crpb

You could use this with localhost as a listener 😛
https://community.grommunio.com/d/836-seperate-authmgrcfg/6