SQLiteConstraintException thrown when trying to insert

Question

I searched and searched and haven't found a solution for this. Hopefully someone here can help.

I'm trying to insert a custom ringtone into MediaStore.Audio.Media.EXTERNAL_CONTENT_URI. Most of the time it works great, but once in a while I get SQLiteConstraintException thrown when calling getContentResolver().insert(). The exception is thrown because a record with a unique column (_data) already exists for a particular value in that table. However, when I then try to get that record using _data as the where clause, null is returned.

So it seems to me that there are multiple tables being checked here and the record with the existing identical _data column is some sort of associated table to the one I'm actually working with when using MediaStore.Audio.Media.EXTERNAL_CONTENT_URI.

So, my question is, if this is the case, is there a way to clear out these orphaned records? Or is there a way to determine which table this duplicate value is in so that I can manually delete it? Maybe a files table of some type?

Also, maybe I'm completely wrong in my assumptions. Any help is really appreciated.

Here's the code that saves the ringtone

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);

In the above ringtoneUri is null when this issue occurs.

Here's the Exception being thrown

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)

I can't see anything that's different with the ones that fail, except that maybe they were already partially inserted at some point.

Trying to get that record then comes back empty

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)... is false after that

The file does exist in the location shown in the _data column

Hopefully that explains things. Thanks in advance!

Answer 1

"Maybe a files table of some type?"

You were so close! MediaStore.Files is exactly what you're looking for. You'll find that Android has indexed everything on the SD card (even .nomedia directories, which is totally unintuitive). Attempting to reinsert a record will result in the SQLiteContraintException (I think only on API level 19, since the uniqueness of _data wasn't always required). Also note that MediaStore.Files is new in API level 11.

NOTE: There's a simpler option, and that is MediaScannerConnection, but I haven't used it so I'm unsure of how the performance compares to directly adding it to the MediaStore.

Anyway, I have to support all the way down to API level 7, so this is how I'm solving it:

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;
    }
}