android短彩信数据库设计（三）

转载请注明出处：http://blog.csdn.net/droyon/article/details/8738873

在Android短彩信中，有这么三个ContentProvider组建，MmsSmsPrivider.java，SmsPrivider.java，MmsPrivider.java，我们发送的数据库请求会到这里。

ContentPrivider组建是android四大组件之一，我们来看看短彩信如何利用这个组件。

MmsSmsPrivider.java

1、

private static final UriMatcher URI_MATCHER =            new UriMatcher(UriMatcher.NO_MATCH);

初始化一个UriMatcher，默认不匹配任何东西。

2、

// These constants are used to construct union queries across the    // MMS and SMS base tables.    // These are the columns that appear in both the MMS ("pdu") and    // SMS ("sms") message tables.    private static final String[] MMS_SMS_COLUMNS =            { BaseColumns._ID, Mms.DATE, Mms.DATE_SENT, Mms.READ, Mms.THREAD_ID, Mms.LOCKED };

我翻译一下英文注释：

这些常量用于在彩信和短信以来的数据表内组建union查询语句。这些列是彩信数据表pdu以及短信数据表sms公共的列。

// These are the columns that appear only in the MMS message    // table.    private static final String[] MMS_ONLY_COLUMNS = {        Mms.CONTENT_CLASS, Mms.CONTENT_LOCATION, Mms.CONTENT_TYPE,        Mms.DELIVERY_REPORT, Mms.EXPIRY, Mms.MESSAGE_CLASS, Mms.MESSAGE_ID,        Mms.MESSAGE_SIZE, Mms.MESSAGE_TYPE, Mms.MESSAGE_BOX, Mms.PRIORITY,        Mms.READ_STATUS, Mms.RESPONSE_STATUS, Mms.RESPONSE_TEXT,        Mms.RETRIEVE_STATUS, Mms.RETRIEVE_TEXT_CHARSET, Mms.REPORT_ALLOWED,        Mms.READ_REPORT, Mms.STATUS, Mms.SUBJECT, Mms.SUBJECT_CHARSET,        Mms.TRANSACTION_ID, Mms.MMS_VERSION };

仅仅在彩信数据表内会定义的数据项。

// These are the columns that appear only in the SMS message    // table.    private static final String[] SMS_ONLY_COLUMNS =            { "address", "body", "person", "reply_path_present",              "service_center", "status", "subject", "type", "error_code" };

仅仅在短信数据表内定义的数据项。

实例化一个能够包含短信和彩信所有项的数组

// These are all the columns that appear in the MMS and SMS    // message tables.    private static final String[] UNION_COLUMNS =            new String[MMS_SMS_COLUMNS.length                       + MMS_ONLY_COLUMNS.length                       + SMS_ONLY_COLUMNS.length];

定义了上述三个常量数组，我们在后面用他们构建union查询等等。

3、

private static final String SMS_CONVERSATION_CONSTRAINT = "(" +            Sms.TYPE + " != " + Sms.MESSAGE_TYPE_DRAFT + ")";private static final String MMS_CONVERSATION_CONSTRAINT = "(" +            Mms.MESSAGE_BOX + " != " + Mms.MESSAGE_BOX_DRAFTS + " AND (" +            Mms.MESSAGE_TYPE + " = " + PduHeaders.MESSAGE_TYPE_SEND_REQ + " OR " +            Mms.MESSAGE_TYPE + " = " + PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF + " OR " +            Mms.MESSAGE_TYPE + " = " + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + "))";

两个常量，前者代表短信不为草稿，后者代表彩信不为草稿，也就是说彩信数据仅仅是这三种类型。
4、

// Search on the words table but return the rows from the corresponding sms table    private static final String SMS_QUERY =            "SELECT sms._id AS _id,thread_id,address,body,date,date_sent,index_text,words._id " +            "FROM sms,words WHERE (index_text MATCH ? " +            "AND sms._id=words.source_id AND words.table_to_use=1)";    // Search on the words table but return the rows from the corresponding parts table    private static final String MMS_QUERY =            "SELECT pdu._id,thread_id,addr.address,part.text " +            "AS body,pdu.date,pdu.date_sent,index_text,words._id " +            "FROM pdu,part,addr,words WHERE ((part.mid=pdu._id) AND " +            "(addr.msg_id=pdu._id) AND " +            "(addr.type=" + PduHeaders.TO + ") AND " +            "(part.ct='text/plain') AND " +            "(index_text MATCH ?) AND " +            "(part._id = words.source_id) AND " +            "(words.table_to_use=2))";    // This code queries the sms and mms tables and returns a unified result set    // of text matches.  We query the sms table which is pretty simple.  We also    // query the pdu, part and addr table to get the mms result.  Notet we're    // using a UNION so we have to have the same number of result columns from    // both queries.    private static final String SMS_MMS_QUERY =            SMS_QUERY + " UNION " + MMS_QUERY +            " GROUP BY thread_id ORDER BY thread_id ASC, date DESC";

在信息中搜索匹配的字符串。分别对短信和彩信构建查询语句，然后使用union将他们联合起来。使用union的两边要保证对应位置的数据类型一样。

5、

private static final String AUTHORITY = "mms-sms";    static {        URI_MATCHER.addURI(AUTHORITY, "conversations", URI_CONVERSATIONS);        URI_MATCHER.addURI(AUTHORITY, "complete-conversations", URI_COMPLETE_CONVERSATIONS);        // In these patterns, "#" is the thread ID.        URI_MATCHER.addURI(                AUTHORITY, "conversations/#", URI_CONVERSATIONS_MESSAGES);...........URI_MATCHER.addURI(AUTHORITY, "locked", URI_FIRST_LOCKED_MESSAGE_ALL);        URI_MATCHER.addURI(AUTHORITY, "locked/#", URI_FIRST_LOCKED_MESSAGE_BY_THREAD_ID);        URI_MATCHER.addURI(AUTHORITY, "messageIdToThread", URI_MESSAGE_ID_TO_THREAD);        initializeColumnSets();    }

构建UriMatcher，最后调用initializeColumnSets()方法，我们想来介绍一下这个方法，这个方法做的事情也挺简单的。就是给我们前面提到的UNION_COLUMNS数组，以及下面两项

// These are all the columns that appear in the MMS table.    private static final Set<String> MMS_COLUMNS = new HashSet<String>();    // These are all the columns that appear in the SMS table.    private static final Set<String> SMS_COLUMNS = new HashSet<String>();

填充数据

/**     * Construct Sets of Strings containing exactly the columns     * present in each table.  We will use this when constructing     * UNION queries across the MMS and SMS tables.     */    private static void initializeColumnSets() {        int commonColumnCount = MMS_SMS_COLUMNS.length;        int mmsOnlyColumnCount = MMS_ONLY_COLUMNS.length;        int smsOnlyColumnCount = SMS_ONLY_COLUMNS.length;        Set<String> unionColumns = new HashSet<String>();        for (int i = 0; i < commonColumnCount; i++) {            MMS_COLUMNS.add(MMS_SMS_COLUMNS[i]);            SMS_COLUMNS.add(MMS_SMS_COLUMNS[i]);            unionColumns.add(MMS_SMS_COLUMNS[i]);        }        for (int i = 0; i < mmsOnlyColumnCount; i++) {            MMS_COLUMNS.add(MMS_ONLY_COLUMNS[i]);            unionColumns.add(MMS_ONLY_COLUMNS[i]);        }        for (int i = 0; i < smsOnlyColumnCount; i++) {            SMS_COLUMNS.add(SMS_ONLY_COLUMNS[i]);            unionColumns.add(SMS_ONLY_COLUMNS[i]);        }        int i = 0;        for (String columnName : unionColumns) {            UNION_COLUMNS[i++] = columnName;        }    }

6、

public boolean onCreate() {        mOpenHelper = MmsSmsDatabaseHelper.getInstance(getContext());        mUseStrictPhoneNumberComparation =            getContext().getResources().getBoolean(                    com.android.internal.R.bool.config_use_strict_phone_number_comparation);        return true;    }

再往下就是利用getInstance方法得到SQLiteDatabaseHelper.java的对象。

7、在然后是query方法，这个方法首先通过6中得到mOpernHelper得到可读数据库，然后根据uri的匹配结果，进入到不同的case中进行数据的查询。我们一步一步的看看过程。

SQLiteDatabase db = mOpenHelper.getReadableDatabase();        Cursor cursor = null;

switch(URI_MATCHER.match(uri)) {            case URI_COMPLETE_CONVERSATIONS:                cursor = getCompleteConversations(projection, selection, sortOrder);                break;

首先调用getCompleteConversations方法去数据库获取数据，将数据包装成cursor，在后面返回。

我们看看这个方法的内部逻辑。

7.1、

/**     * Return every message in each conversation in both MMS     * and SMS.     */    private Cursor getCompleteConversations(String[] projection,            String selection, String sortOrder) {        String unionQuery = buildConversationQuery(projection, selection, sortOrder);//方法介绍见7.2        return mOpenHelper.getReadableDatabase().rawQuery(unionQuery, EMPTY_STRING_ARRAY);//利用上面得到的sql，进行数据检索，将数据包装到cursor中并返回。    }

这个方法调用buildConversationQuery方法，我们进入到这个方法中，

7.2、

private static String buildConversationQuery(String[] projection,            String selection, String sortOrder) {        String[] mmsProjection = createMmsProjection(projection);        SQLiteQueryBuilder mmsQueryBuilder = new SQLiteQueryBuilder();//首先初始化一个SQLiteQueryBuilder对象，作为彩信sql的构建对象        SQLiteQueryBuilder smsQueryBuilder = new SQLiteQueryBuilder();//和上面雷同        mmsQueryBuilder.setDistinct(true);//设置去掉重复的数据        smsQueryBuilder.setDistinct(true);//同上        mmsQueryBuilder.setTables(joinPduAndPendingMsgTables());//见7.3        smsQueryBuilder.setTables(SmsProvider.TABLE_SMS);//为sql设置目标数据表。例如：select * from 【table】。这里就是用来设置table的。        String[] smsColumns = handleNullMessageProjection(projection);//处理空projection，见7.4        String[] mmsColumns = handleNullMessageProjection(mmsProjection);//同上        String[] innerMmsProjection = makeProjectionWithNormalizedDate(mmsColumns, 1000);//见7.5,转换date的秒单位为毫秒        String[] innerSmsProjection = makeProjectionWithNormalizedDate(smsColumns, 1);//同上        Set<String> columnsPresentInTable = new HashSet<String>(MMS_COLUMNS);//构建一个set，其中含有短信以及彩信的数据库表的所有字段。        columnsPresentInTable.add("pdu._id");        columnsPresentInTable.add(PendingMessages.ERROR_TYPE);//增加两项数据表项        String mmsSelection = concatSelections(selection,                                Mms.MESSAGE_BOX + " != " + Mms.MESSAGE_BOX_DRAFTS);//见7.6,拼接两个selection        String mmsSubQuery = mmsQueryBuilder.buildUnionSubQuery(//调用buildUnionSubQuery进行构建sql子句。想了解参数的意义，见7.7                MmsSms.TYPE_DISCRIMINATOR_COLUMN, innerMmsProjection,                columnsPresentInTable, 0, "mms",                concatSelections(mmsSelection, MMS_CONVERSATION_CONSTRAINT),                null, null);        String smsSubQuery = smsQueryBuilder.buildUnionSubQuery(                MmsSms.TYPE_DISCRIMINATOR_COLUMN, innerSmsProjection, SMS_COLUMNS,                0, "sms", concatSelections(selection, SMS_CONVERSATION_CONSTRAINT),                null, null);        SQLiteQueryBuilder unionQueryBuilder = new SQLiteQueryBuilder();//构建一个新的sql语句包装对象，用于将sms以及mms的包装对象加进来。        unionQueryBuilder.setDistinct(true);//设置清楚重复数据项。设置了这一项，使用的是union不设置则使用union all        String unionQuery = unionQueryBuilder.buildUnionQuery(//将短信和彩信的sql加进来。                new String[] { smsSubQuery, mmsSubQuery },                handleNullSortOrder(sortOrder), null);        SQLiteQueryBuilder outerQueryBuilder = new SQLiteQueryBuilder();        outerQueryBuilder.setTables("(" + unionQuery + ")");//将上述sql搜索的字符串作为一个数据表        return outerQueryBuilder.buildQuery(//构建最终的sql。                smsColumns, null, null, null, sortOrder, null);//执行完毕会得到类似这样的数据：}

//select a,b,c from (select aa,bb,null as cc,null as dd from sms where aa = 1union selectnull as aa,null as bb,cc,dd from pdu where aaa = 1) where (_id = 1) groupBy xxx date DESC.

7.3、

private static String joinPduAndPendingMsgTables() {        return MmsProvider.TABLE_PDU + " LEFT JOIN " + TABLE_PENDING_MSG                + " ON pdu._id = pending_msgs.msg_id";    }

pdu表和pending_msgs表进行左连接
7.4、

/**     * If a null projection is given, return the union of all columns     * in both the MMS and SMS messages tables.  Otherwise, return the     * given projection.     */    private static String[] handleNullMessageProjection(            String[] projection) {        return projection == null ? UNION_COLUMNS : projection;    }

检查projection是否为null，如果不为null,返回自身，如果为null，则返回所有UNION_COLUMNS，这个UNION_COLUMNS的初始化在5中介绍了。

7.5、

 /**     * Add normalized date to the list of columns for an inner     * projection.     */    private static String[] makeProjectionWithNormalizedDate(            String[] projection, int dateMultiple) {        int projectionSize = projection.length;        String[] result = new String[projectionSize + 1];        result[0] = "date * " + dateMultiple + " AS normalized_date";        System.arraycopy(projection, 0, result, 1, projectionSize);        return result;    }

为projection添加normalized_date，由于sms表内的date存储的是当前的时间，是毫秒。而在彩信数据库中，存储的是秒，因而转换时间时，彩信需要乘以1000,转换为毫秒。

7.6、

private static String concatSelections(String selection1, String selection2) {        if (TextUtils.isEmpty(selection1)) {            return selection2;        } else if (TextUtils.isEmpty(selection2)) {            return selection1;        } else {            return selection1 + " AND " + selection2;        }    }

拼接selection1以及selection2.

7.7、

public String buildUnionSubQuery(            String typeDiscriminatorColumn,            String[] unionColumns,            Set<String> columnsPresentInTable,            int computedColumnsOffset,            String typeDiscriminatorValue,            String selection,            String groupBy,            String having) {        int unionColumnsCount = unionColumns.length;        String[] projectionIn = new String[unionColumnsCount];        for (int i = 0; i < unionColumnsCount; i++) {            String unionColumn = unionColumns[i];            if (unionColumn.equals(typeDiscriminatorColumn)) {                projectionIn[i] = "'" + typeDiscriminatorValue + "' AS "                        + typeDiscriminatorColumn;            } else if (i <= computedColumnsOffset                       || columnsPresentInTable.contains(unionColumn)) {                projectionIn[i] = unionColumn;            } else {                projectionIn[i] = "NULL AS " + unionColumn;            }        }        return buildQuery(                projectionIn, selection, groupBy, having,                null /* sortOrder */,                null /* limit */);    }

贴出源码比较容易理解，这个函数的设计思想。
一切的重点在于columnsPresentInTable中。把属于此set集合的项放进sql中，如果不再此set集合中，则把null as xx放进sql字符串。

这个函数就是把不属于set集合的列在搜索结果中现实为null.

剩下的case项基本雷同。

public Cursor query(Uri uri, String[] projection,            String selection, String[] selectionArgs, String sortOrder) {................       cursor.setNotificationUri(getContext().getContentResolver(), MmsSms.CONTENT_URI);        return cursor;    }

最后的一项很重要，它为自身注册了一个观察者，监听了URI，当数据库发生变动，

if (affectedRows > 0) {            context.getContentResolver().notifyChange(MmsSms.CONTENT_URI, null);        }

如果数据库发生变动，并执行 notifyChanged方法，那么注册相应uri的Cursor会受到通知，并做出反应。

ContentProvider类大部分为相应请求，构建sql进行数据库的查询。

更多相关文章

随机推荐