android - 尝试插入时抛出 SQLiteConstraintException

Question

我找了又找，还没有找到解决办法。希望这里有人可以提供帮助。

我正在尝试将自定义铃声插入 MediaStore.Audio.Media.EXTERNAL_CONTENT_URI。大多数时候它工作得很好，但偶尔我会在调用 getContentResolver().insert() 时抛出 SQLiteConstraintException。抛出异常是因为该表中的特定值已经存在具有唯一列 (_data) 的记录。但是，当我尝试使用 _data 作为 where 子句获取该记录时，返回 null。

所以在我看来，这里检查了多个表，并且具有现有相同 _data 列的记录是某种关联表，与我在使用 MediaStore.Audio.Media.EXTERNAL_CONTENT_URI 时实际使用的表相关联。

所以，我的问题是，如果是这种情况，有没有办法清除这些孤立的记录？或者有没有办法确定这个重复值在哪个表中，以便我可以手动删除它？也许是某种类型的文件表？

此外，也许我的假设完全错误。非常感谢任何帮助。

这是保存铃声的代码

ContentValues mediaValues = new ContentValues();
mediaValues.put(MediaStore.MediaColumns.DATA, filename.toString());
mediaValues.put(MediaStore.MediaColumns.TITLE, speakTextTxt);
mediaValues.put(MediaStore.MediaColumns.DISPLAY_NAME, speakTextTxt);
mediaValues.put(MediaStore.MediaColumns.MIME_TYPE, "audio/mpeg3");
mediaValues.put(MediaStore.MediaColumns.SIZE, filename.length());
mediaValues.put(MediaStore.Audio.Media.ARTIST, appName);
mediaValues.put(MediaStore.Audio.Media.IS_RINGTONE, true);
mediaValues.put(MediaStore.Audio.Media.IS_NOTIFICATION, true);
mediaValues.put(MediaStore.Audio.Media.IS_ALARM, true);
mediaValues.put(MediaStore.Audio.Media.IS_MUSIC, false);

ringtoneUri = getContentResolver().insert(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, mediaValues);

在上述ringtoneUri 为null 时会出现此问题。

这是抛出的异常

03-04 13:18:16.522  24774-23075/? E/SQLiteDatabase﹕ Error inserting bucket_id=1420360973 media_type=2 storage_id=65537 date_modified=1393450056 is_alarm=true is_ringtone=true parent=22388 format=12297 artist_id=90 is_music=false bucket_display_name=Ringtones album_id=161 title=German gorilla is_notification=true title_key=    3   /   I   ?   '   A      3   C   I   7   =   =   '    mime_type=audio/mpeg3 date_added=1393967896 _display_name=German_gorilladeuDEUcomgoogleandroidtts-75868.mp3 _size=32044 _data=/storage/emulated/0/Android/data/com.twoclaw.typeyourringtonepro/files/Ringtones/German_gorilladeuDEUcomgoogleandroidtts-75868.mp3
android.database.sqlite.SQLiteConstraintException: column _data is not unique (code 19)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
        at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
        at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
        at com.android.providers.media.MediaProvider.insertFile(MediaProvider.java:3199)
        at com.android.providers.media.MediaProvider.insertInternal(MediaProvider.java:3439)
        at com.android.providers.media.MediaProvider.insert(MediaProvider.java:2851)
        at android.content.ContentProvider$Transport.insert(ContentProvider.java:220)
        at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:156)
        at android.os.Binder.execTransact(Binder.java:404)
        at dalvik.system.NativeStart.run(Native Method)

我看不出与失败的有什么不同，除了它们可能在某个时候已经部分插入。

尝试获取该记录然后返回为空

String[] projection = {MediaStore.MediaColumns.DATA, MediaStore.MediaColumns._ID};
String[] selectionArgs = {filename.toString()};
Cursor existingTone = getContentResolver().query(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, projection, MediaStore.MediaColumns.DATA+"=?", selectionArgs, null);

if (existingTone.getCount() > 0)... 之后为 false

文件确实存在于 _data 列中显示的位置

希望这能解释事情。提前致谢!

Answer 1

"Maybe a files table of some type?"

你们太亲密了! MediaStore.Files正是您要寻找的。您会发现 Android 已经为 SD 卡上的所有内容 编制了索引(甚至是 .nomedia 目录，这完全不直观)。尝试重新插入记录将导致 SQLiteContraintException(我认为仅在 API 级别 19 上，因为并不总是需要 _data 的唯一性)。另请注意，MediaStore.Files 是 API 级别 11 中的新内容。

注意:有一个更简单的选项，那就是MediaScannerConnection ，但我没有使用过它，所以我不确定与直接将它添加到 MediaStore 相比性能如何。

无论如何，我必须一直支持到 API 级别 7，所以这就是我解决它的方式:

Uri findAudioFileUri(File filename) {
    // SDK 11+ has the Files store, which already indexed... everything
    // We need the file's URI though, so we'll be forced to query
    if (Build.VERSION.SDK_INT >= 11) {
        Uri uri = null;

        Uri filesUri = MediaStore.Files.getContentUri("external");
        String[] projection = {MediaStore.MediaColumns._ID, MediaStore.MediaColumns.TITLE};
        String selection = MediaStore.MediaColumns.DATA + " = ?";
        String[] args = {filename.getAbsolutePath()};
        Cursor c = ctx.getContentResolver().query(filesUri, projection, selection, args, null);

        // We expect a single unique record to be returned, since _data is unique
        if (c.getCount() == 1) {
            c.moveToFirst();
            long rowId = c.getLong(c.getColumnIndex(MediaStore.MediaColumns._ID));
            String title = c.getString(c.getColumnIndex(MediaStore.MediaColumns.TITLE));
            c.close();
            uri = MediaStore.Files.getContentUri("external", rowId);

            // Since all this stuff was added automatically, it might not have the metadata you want,
            // like Title, or Artist, or IsRingtone
            if (!title.equals(DESIRED_TITLE)) {
                ContentValues values = new ContentValues();
                values.put(MediaStore.MediaColumns.TITLE, DESIRED_TITLE);

                if (ctx.getContentResolver().update(toneUri, values, null, null) != 1) {
                    throw new UnsupportedOperationException(); // update failed
                }

                // Apparently this is best practice, although I have no idea what the Media Scanner
                // does with the new data
                ctx.sendBroadcast(new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, toneUri));
            }
        }
        else if (c.getCount() == 0) {
            // I suppose the MediaScanner hasn't run yet, we'll insert it
            ... ommitted
        }
        else {
            throw new UnsupportedOperationException(); // it's expected to be unique!
        }

        return uri;
    }
    // For the legacy way, I'm assuming that the file we're working with is in a .nomedia
    // folder, so we are the ones who created it in the MediaStore. If this isn't the case,
    // consider querying for it and updating the existing record. You should store the URIs
    // you create in case you need to delete them from the MediaStore, otherwise you're a
    // litter bug :P
    else {
        ContentValues values = new ContentValues();
        values.put(MediaStore.MediaColumns.DATA, path.getAbsolutePath());
        values.put(MediaStore.MediaColumns.SIZE, path.length());
        values.put(MediaStore.MediaColumns.DISPLAY_NAME, path.getName());
        values.put(MediaStore.MediaColumns.TITLE, DESIRED_TITLE);
        values.put(MediaStore.MediaColumns.MIME_TYPE, "audio/mpeg3");
        values.put(MediaStore.Audio.Media.ARTIST, appName);
        values.put(MediaStore.Audio.Media.IS_RINGTONE, true);
        values.put(MediaStore.Audio.Media.IS_NOTIFICATION, true);
        values.put(MediaStore.Audio.Media.IS_ALARM, true);
        values.put(MediaStore.Audio.Media.IS_MUSIC, false);

        Uri newToneUri = ctx.getContentResolver().insert(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, values);

        // Apparently this is best practice, although I have no idea what the Media Scanner
        // does with the new data
        ctx.sendBroadcast(new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, newToneUri));

        return newToneUri;
    }
}