Mms 数据库的一个bug

之前遇到的一个Mms 数据库的bug，看到4.1版本已经解掉了；

老版本的代码：

    public static void updateThread(SQLiteDatabase db, long thread_id) {
        if (thread_id < 0) {
            updateAllThreads(db, null, null);
            return;
        }

        // Delete the row for this thread in the threads table if
        // there are no more messages attached to it in either
        // the sms or pdu tables.
        int rows = db.delete("threads",
                  "_id = ? AND _id NOT IN" +
                  "          (SELECT thread_id FROM sms " +
                  "           UNION SELECT thread_id FROM pdu)",
                  new String[] { String.valueOf(thread_id) });
        if (rows > 0) {
            // If this deleted a row, let's remove orphaned canonical_addresses and get outta here
            db.delete("canonical_addresses",
                    "_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null);
            return;
        }
        ...
    }

错误出现在：

If this deleted a row, let's remove orphaned canonical_addresses and get outta here.

其逻辑是正确的，如果threads中有数据项被删除掉，那需要把canonical_addresses不会再使用到的数据项给删除掉；从threads中找到所有的recipient_ids集合，删除canonical_addresses中已经不在集合中的项目；但需要注意的是threads表的recipient_ids（TEXT）数据项，如果一个conversation有多个联系人，那么这些联系人的id（canonical_addresses的_id）将以空格隔开出现在recipient_ids中，就像是这样

threads

_id	recipient_ids
1	1
2	2 3

canonical_addresses

_id	address
1	1000
2	999
3	10100

bug的情况是，如果从threads中删除_id=1的项，那对应canonical_addresses将要删除address 1000，而实际情况是999/10100也被删除；

            db.delete("canonical_addresses",
                    "_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null);
 

"2 3"会被当做一个数据列被处理，所以导致认为“2”，“3”数据项不在集合中而被删除，下面是新代码解决了这个问题：

    /**
     * Look through all the recipientIds referenced by the threads and then delete any
     * unreferenced rows from the canonical_addresses table.
     */
    private static void removeUnferencedCanonicalAddresses(SQLiteDatabase db) {
        Cursor c = db.query("threads", new String[] { "recipient_ids" },
                null, null, null, null, null);
        if (c != null) {
            try {
                if (c.getCount() == 0) {
                    // no threads, delete all addresses
                    int rows = db.delete("canonical_addresses", null, null);
                } else {
                    // Find all the referenced recipient_ids from the threads. recipientIds is
                    // a space-separated list of recipient ids: "1 14 21"
                    HashSet<Integer> recipientIds = new HashSet<Integer>();
                    while (c.moveToNext()) {
                        String[] recips = c.getString(0).split(" ");
                        for (String recip : recips) {
                            try {
                                int recipientId = Integer.parseInt(recip);
                                recipientIds.add(recipientId);
                            } catch (Exception e) {
                            }
                        }
                    }
                    // Now build a selection string of all the unique recipient ids
                    StringBuilder sb = new StringBuilder();
                    Iterator<Integer> iter = recipientIds.iterator();
                    while (iter.hasNext()) {
                        sb.append("_id != " + iter.next());
                        if (iter.hasNext()) {
                            sb.append(" AND ");
                        }
                    }
                    if (sb.length() > 0) {
                        int rows = db.delete("canonical_addresses", sb.toString(), null);
                    }
                }
            } finally {
                c.close();
            }
        }
    }