从数据库导出SQLite3表，其中包含"Error: no此类排序规则序列: IUNICODE“

Question

使用用于SQLite的DB浏览器，您现在可以成功地从带有IUNICODE排序规则序列错误的数据库导出一个表，但是这是一个使用DB浏览器的GUI的手动过程。我使用DB浏览器，因为如果您尝试使用sqlite3从bash或sqlite控制台导出表，您将得到：

Error: no such collation sequence: IUNICODE

由于DB浏览器似乎没有命令行选项(这是正确的吗？)，我正在寻找另一种方法，以便能够自动导出表，最好使用bash脚本。

根据CL的响应below...did：

sqlite3 /home/lpc123/data/MM.DB "SELECT sql FROM sqlite_master WHERE type='table' AND name='Songs';" 

...which生成了一长串表定义：

CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT COLLATE IUNICODE,IDAlbum INTEGER,Album TEXT COLLATE IUNICODE,AlbumArtist TEXT COLLATE IUNICODE,DiscNumber TEXT COLLATE IUNICODE,TrackNumber TEXT COLLATE IUNICODE,SongTitle TEXT COLLATE IUNICODE,SongPath TEXT COLLATE IUNICODE,Extension TEXT(5),Year INTEGER,Genre TEXT COLLATE IUNICODE,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT COLLATE IUNICODE,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT COLLATE IUNICODE,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT COLLATE IUNICODE,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT COLLATE IUNICODE,Custom2 TEXT COLLATE IUNICODE,Custom3 TEXT COLLATE IUNICODE,Custom4 TEXT COLLATE IUNICODE,Custom5 TEXT COLLATE IUNICODE,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT COLLATE IUNICODE,Publisher TEXT COLLATE IUNICODE,Encoder TEXT COLLATE IUNICODE,Lyricist TEXT COLLATE IUNICODE,Conductor TEXT COLLATE IUNICODE,Remixer TEXT COLLATE IUNICODE,InvolvedPeople TEXT COLLATE IUNICODE,OrigTitle TEXT COLLATE IUNICODE,OrigArtist TEXT COLLATE IUNICODE,OrigLyricist TEXT COLLATE IUNICODE,GroupDesc TEXT COLLATE IUNICODE,SubTitle TEXT COLLATE IUNICODE,ISRC TEXT COLLATE IUNICODE,InitialKey TEXT COLLATE IUNICODE,Language TEXT COLLATE IUNICODE,WebCommercial TEXT COLLATE IUNICODE,WebCopyright TEXT COLLATE IUNICODE,WebFilepage TEXT COLLATE IUNICODE,WebArtist TEXT COLLATE IUNICODE,WebSource TEXT COLLATE IUNICODE,WebRadio TEXT COLLATE IUNICODE,WebPayment TEXT COLLATE IUNICODE,WebPublisher TEXT COLLATE IUNICODE,WebUser TEXT COLLATE IUNICODE,OrigYear INTEGER,Tempo TEXT COLLATE IUNICODE,Mood TEXT COLLATE IUNICODE,Occasion TEXT COLLATE IUNICODE,Quality TEXT COLLATE IUNICODE,Lyrics TEXT COLLATE IUNICODE,Comment TEXT COLLATE IUNICODE, StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT COLLATE IUNICODE, SeasonNumber TEXT COLLATE IUNICODE, Actors TEXT COLLATE IUNICODE, Producer TEXT COLLATE IUNICODE, ParentalRating TEXT COLLATE IUNICODE, ArtworkModified REAL)

从该字符串中删除"COLLATE“的所有实例：

CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT ,IDAlbum INTEGER,Album TEXT ,AlbumArtist TEXT ,DiscNumber TEXT ,TrackNumber TEXT ,SongTitle TEXT ,SongPath TEXT ,Extension TEXT(5),Year INTEGER,Genre TEXT ,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT ,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT ,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT ,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT ,Custom2 TEXT ,Custom3 TEXT ,Custom4 TEXT ,Custom5 TEXT ,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT ,Publisher TEXT ,Encoder TEXT ,Lyricist TEXT ,Conductor TEXT ,Remixer TEXT ,InvolvedPeople TEXT ,OrigTitle TEXT ,OrigArtist TEXT ,OrigLyricist TEXT ,GroupDesc TEXT ,SubTitle TEXT ,ISRC TEXT ,InitialKey TEXT ,Language TEXT ,WebCommercial TEXT ,WebCopyright TEXT ,WebFilepage TEXT ,WebArtist TEXT ,WebSource TEXT ,WebRadio TEXT ,WebPayment TEXT ,WebPublisher TEXT ,WebUser TEXT ,OrigYear INTEGER,Tempo TEXT ,Mood TEXT ,Occasion TEXT ,Quality TEXT ,Lyrics TEXT ,Comment TEXT , StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT , SeasonNumber TEXT , Actors TEXT , Producer TEXT , ParentalRating TEXT , ArtworkModified REAL)

使用PRAGMA writable_schema=1启用了对writable_schema=1的写访问：

sqlite3 /home/lpc123/data/MM.DB "PRAGMA writable_schema=1;"

以下是事情发生故障或先前的命令没有执行的地方)：

将新表定义写入表中。

更新sqlite_master集sql=‘.’其中类型=‘table’和name='MonkeyTable'；我认为这意味着：

sqlite3 /home/lpc123/data/MM.DB "UPDATE sqlite_master SET sql='CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT ,IDAlbum INTEGER,Album TEXT ,AlbumArtist TEXT ,DiscNumber TEXT ,TrackNumber TEXT ,SongTitle TEXT ,SongPath TEXT ,Extension TEXT(5),Year INTEGER,Genre TEXT ,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT ,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT ,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT ,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT ,Custom2 TEXT ,Custom3 TEXT ,Custom4 TEXT ,Custom5 TEXT ,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT ,Publisher TEXT ,Encoder TEXT ,Lyricist TEXT ,Conductor TEXT ,Remixer TEXT ,InvolvedPeople TEXT ,OrigTitle TEXT ,OrigArtist TEXT ,OrigLyricist TEXT ,GroupDesc TEXT ,SubTitle TEXT ,ISRC TEXT ,InitialKey TEXT ,Language TEXT ,WebCommercial TEXT ,WebCopyright TEXT ,WebFilepage TEXT ,WebArtist TEXT ,WebSource TEXT ,WebRadio TEXT ,WebPayment TEXT ,WebPublisher TEXT ,WebUser TEXT ,OrigYear INTEGER,Tempo TEXT ,Mood TEXT ,Occasion TEXT ,Quality TEXT ,Lyrics TEXT ,Comment TEXT , StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT , SeasonNumber TEXT , Actors TEXT , Producer TEXT , ParentalRating TEXT , ArtworkModified REAL)' WHERE type='table' AND name='Songs';"

导致此错误的原因：

Error: table sqlite_master may not be modified

我认为创建表的部分可能不属于该部分，所以我再次尝试了这一次，而没有创建表部分：

sqlite3 /home/lpc123/data/MM.DB "UPDATE sqlite_master SET sql='TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT ,IDAlbum INTEGER,Album TEXT ,AlbumArtist TEXT ,DiscNumber TEXT ,TrackNumber TEXT ,SongTitle TEXT ,SongPath TEXT ,Extension TEXT(5),Year INTEGER,Genre TEXT ,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT ,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT ,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT ,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT ,Custom2 TEXT ,Custom3 TEXT ,Custom4 TEXT ,Custom5 TEXT ,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT ,Publisher TEXT ,Encoder TEXT ,Lyricist TEXT ,Conductor TEXT ,Remixer TEXT ,InvolvedPeople TEXT ,OrigTitle TEXT ,OrigArtist TEXT ,OrigLyricist TEXT ,GroupDesc TEXT ,SubTitle TEXT ,ISRC TEXT ,InitialKey TEXT ,Language TEXT ,WebCommercial TEXT ,WebCopyright TEXT ,WebFilepage TEXT ,WebArtist TEXT ,WebSource TEXT ,WebRadio TEXT ,WebPayment TEXT ,WebPublisher TEXT ,WebUser TEXT ,OrigYear INTEGER,Tempo TEXT ,Mood TEXT ,Occasion TEXT ,Quality TEXT ,Lyrics TEXT ,Comment TEXT , StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT , SeasonNumber TEXT , Actors TEXT , Producer TEXT , ParentalRating TEXT , ArtworkModified REAL)' WHERE type='table' AND name='Songs';"

这也没用。

Answer 1

对此的回答是一个张贴在这里的脚本，它将数据库中包含的所有表提取为单独的CSV文件，并且可以在bash中运行。在这种情况下，所需的输出是一个表，并命名为songs.csv。

然后，您可以创建一个SQL文件: import.sql，您必须裁剪该文件以包含提取表中显示的所有列(本例显示四列)：

CREATE TABLE Songs (ID varchar(255) not null, Artist varchar(255) not null, IDAlbum varchar(255) not null, Album varchar(255) not null);
.separator ,
.import songs.csv Songs

现在您可以在bash中运行以下操作：

sqlite3 currdb.sqlite < import.sql

...which创建一个可访问的数据库，其中包含导入的表。

Answer 2

可以修改数据库以删除对该排序规则的所有引用。

阅读表格定义：

SELECT sql FROM sqlite_master WHERE type='table' AND name='MonkeyTable';

从字符串中删除COLLATE IUNICODE，然后使用schema=1；启用对sqlite_master的写访问，并将新的表定义写入其中：

UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='MonkeyTable';

如果您确实希望以交互方式执行此操作，则必须在同一会话中执行两个语句：

sqlite3 mm.db "PRAGMA writable_schema=1; UPDATE sqlite_master ..."

然后，重新打开数据库，并运行再索引 (因为更改排序规则使所有索引内容无效)。

Answer 3

MediaMonkey用户的工作解决方案：

1.)从这里下载windows的“命令行SQLite工具包”：https://sqlite.org/download.html (这个归档文件实际上叫做sqlite-tools-win32-x86-3330000.zip，不存在64位，32位很好)。

2.)解压缩ZIP文件，至少需要文件sqlite3.exe

3.)请始终创建MM.DB的副本，不要将原始副本用于以下步骤！

4.)使用CopyOfMM.DB在CMD窗口中打开sqlite3.exe CopyOfMM.DB

5.)在sqlite提示符上，执行以下命令：

SELECT sql FROM sqlite_master WHERE type='table' AND name='Songs';

6.)将完整的CREATE输出文本复制到像Notepad++这样的编辑器中

7.)将所有出现的COLLATE IUNICODE文本替换为空(删除它们)

8.)使用.quit退出sqlite提示符

9.)在CMD窗口中以一行方式执行以下命令(在步骤7中用修改过的文本完全替换CREATE TABLE Songs部件。此文本在MediaMonkey版本之间有所不同！)添加后缀' WHERE type='table' AND name='Songs';")

sqlite3.exe CopyOfMM.DB "PRAGMA writable_schema=1; UPDATE sqlite_master SET sql='CREATE TABLE Songs (......)' WHERE type='table' AND name='Songs';"

这只需要一秒钟。

9a)如果要确定此命令是否更改了表定义，请重复步骤#4和#5，并再次检查是否不再存在IUNICODE排序规则。退出sqlite3.exe提示符。

10.)使用以下命令重新编制表歌的索引：

sqlite3.exe CopyOfMM.DB "REINDEX Songs;"

这需要一分钟左右，这取决于这个表中的条目数量。

现在，您可以为您的歌表执行任何sqlite命令，而不需要“Error: no此类排序序列:IUNICODE”。

重复一遍：不使用原始的MediaMonkey数据库！