尝试插入时引发的SQLiteConstraintException
尝试插入时引发的SQLiteConstraintException
提问于 2014-03-05 06:41:51
我找了又找，还没有找到解决方案。希望这里有人能帮上忙。
我正在尝试将自定义铃声插入MediaStore.Audio.Media.EXTERNAL_CONTENT_URI。大多数情况下它工作得很好，但偶尔在调用getContentResolver().insert()时会抛出SQLiteConstraintException异常。引发异常是因为该表中的特定值已经存在具有唯一列(_data)的记录。但是，当我随后尝试使用_data作为where子句来获取该记录时，返回null。
因此，在我看来，这里检查了多个表，并且在使用MediaStore.Audio.Media.EXTERNAL_CONTENT_URI时，具有相同_data列的记录是我实际使用的表的某种关联表。
所以，我的问题是，如果是这样的话，有没有办法清理这些孤立的记录？或者，有没有办法确定这个重复的值在哪个表中，以便我可以手动删除它？也许是某种类型的文件表？
另外，也许我的假设是完全错误的。任何帮助都是非常感谢的。
下面是保存铃声的代码
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列中显示的位置
希望这能解释一切。提前感谢！
回答 2
Stack Overflow用户
发布于 2014-04-08 09:30:55
“也许是某种类型的文件表？”
差一点就到了！MediaStore.Files就是你要找的。你会发现Android已经在sd卡上索引了everything (甚至是.nomedia目录，这是完全不直观的)。尝试重新插入一条记录将导致SQLiteContraintException (我认为只在API19级，因为并不总是需要_data的唯一性)。还要注意的是，MediaStore.Files是API level 11中的新特性。
注意：有一个更简单的选择，那就是MediaScannerConnection，但我还没有用过它，所以我不确定与直接将它添加到MediaStore相比，性能如何。
无论如何，我必须一直支持到API level 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;
}
}Stack Overflow用户
发布于 2014-09-17 12:42:40
我已经找到了适合我的解决方案。它适用于我测试过的所有设备，甚至是Android4.3 (API 19)。为了避免“列_data不唯一(代码19)”问题，最好执行delete row并再次插入它。它分别用于视频和音频文件。下面是我的代码：
if(gd.getContentType() == ContentType.Video){
ContentValues content = new ContentValues(4);
content.put(MediaStore.Video.VideoColumns.TITLE, filename);
content.put(MediaStore.Video.VideoColumns.DATE_ADDED,
System.currentTimeMillis() / 1000);
content.put(MediaStore.Video.Media.MIME_TYPE, "video/"+fileext);
content.put(MediaStore.Video.Media.DATA, gd.url);
Uri uri2 = context.getContentResolver().insert(MediaStore.Video.Media.EXTERNAL_CONTENT_URI, content);
Log.i(TAG, "=refresh scan new file inserted video uri="+uri2);
if(uri2 != null){
uri_video = uri2;
//context.sendBroadcast(new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri_video));
}else{
Uri filesUri = MediaStore.Files.getContentUri("external");
String[] projection = {MediaStore.MediaColumns._ID, MediaStore.MediaColumns.TITLE};
String selection = MediaStore.MediaColumns.DATA + " = ?";
String[] args = {gd.url};
Cursor c = context.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();
uri2 = MediaStore.Files.getContentUri("external", rowId);
Log.i(TAG, "=refresh scan force uri2="+uri2);
if(uri2 != null){
//context.getContentResolver().update(uri2, content, null, null);
context.getContentResolver().delete(uri2, null, null);
uri2 = context.getContentResolver().insert(MediaStore.Video.Media.EXTERNAL_CONTENT_URI, content);
if(uri2 != null){
uri_video = uri2;
}
}
}
}
}else{
ContentValues content = new ContentValues(4);
content.put(MediaStore.Audio.AudioColumns.TITLE, "audio"+filename);
content.put(MediaStore.Audio.AudioColumns.DATE_ADDED,
System.currentTimeMillis() / 1000);
content.put(MediaStore.Audio.Media.MIME_TYPE, "audio/"+fileext);
content.put(MediaStore.Audio.Media.DATA, gd.url);
Uri uri2 = context.getContentResolver().insert(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, content);
Log.i(TAG, "=refresh scan new file inserted audio uri="+uri2);
if(uri2 != null){
uri_audio = uri2;
//context.sendBroadcast(new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri_audio));
}else{
Uri filesUri = MediaStore.Files.getContentUri("external");
String[] projection = {MediaStore.MediaColumns._ID, MediaStore.MediaColumns.TITLE};
String selection = MediaStore.MediaColumns.DATA + " = ?";
String[] args = {gd.url};
Cursor c = context.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();
uri2 = MediaStore.Files.getContentUri("external", rowId);
Log.i(TAG, "=refresh scan force audio uri2="+uri2);
if(uri2 != null){
//context.getContentResolver().update(uri2, content, null, null);
context.getContentResolver().delete(uri2, null, null);
uri2 = context.getContentResolver().insert(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, content);
if(uri2 != null){
uri_audio = uri2;
}
}
}
}
}
if(uri_video != null)
context.sendBroadcast(new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri_video));
if(uri_audio != null)
context.sendBroadcast(new Intent(Intent.ACTION_MEDIA_SCANNER_SCAN_FILE, uri_audio));页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22184729
复制相关文章
相似问题
腾讯云开发者
