[MAILBOX-229] Orphan JAMES_MAIL_USERFLAG rows should be deleted

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 0.5
• Component/s: jpa
• Environment: James3 which acquired from SVN trunk on Feburuary 2015; PostgreSQL 9.2.6; Thunderbird 31.6.0
• Assignee: Unassigned
• Reporter: Kohei Nozaki
• Created: 27/Apr/15 13:45
• Updated: 22/Nov/15 07:29
• Resolved: 22/Nov/15 07:28

Description

Consider issuing following IMAP command while the mail which has uid 1, already has a flag named NonJunk:

7 store 1 +flags NonJunk
* 1 FETCH (FLAGS (\Seen NonJunk))
7 OK STORE completed.

Then following SQL will be executed:

2015-04-27 22:21:13 JST LOG:  execute <unnamed>: BEGIN
2015-04-27 22:21:13 JST LOG:  execute <unnamed>: UPDATE public.JAMES_MAIL_USERFLAG SET mailbox_id = $1, mail_uid = $2 WHERE mailbox_id = $3 AND mail_uid = $4
2015-04-27 22:21:13 JST DETAIL:  parameters: $1 = NULL, $2 = NULL, $3 = '1', $4 = '1'
2015-04-27 22:21:13 JST LOG:  execute <unnamed>: INSERT INTO public.JAMES_MAIL_USERFLAG (userflag_id, userflag_name, mailbox_id, mail_uid) VALUES ($1, $2, $3, $4)
2015-04-27 22:21:13 JST DETAIL:  parameters: $1 = '301', $2 = 'NonJunk', $3 = '1', $4 = '1'
2015-04-27 22:21:13 JST LOG:  execute S_1: COMMIT

Currently AbstractJPAMessage#setFlags() is managing relation between JAMES_MAIL and JAMES_MAIL_USERFLAG, it simply invokes userFlags.clear();, then adds all of flags newly. this brings many orphan rows in JAMES_MAIL_USERFLAG as follows:

james=# select * from james_mail_userflag where mailbox_id is null and mail_uid is null;
 userflag_id | userflag_name | mailbox_id | mail_uid 
-------------+---------------+------------+----------
         151 | NonJunk       |            |         
         152 | NonJunk       |            |         
         154 | NonJunk       |            |         
         155 | NonJunk       |            |         
         157 | NonJunk       |            |         
...

I think these orphan rows should be deleted because they are looks useless.