Program to edit MM SQLite DB?
Moderator: Gurus
Program to edit MM SQLite DB?
What programs have people found that will allow edit of the MM SQLite DB, without too much trouble from the IUNICODE collate issue?
So far I have found only SQLiteSpy and SQLite Expert Personal, both of which are overly lacking in edit capabilities for me.
Thanks.
So far I have found only SQLiteSpy and SQLite Expert Personal, both of which are overly lacking in edit capabilities for me.
Thanks.
Chris
-
- Posts: 1308
- Joined: Mon Oct 17, 2011 8:28 am
- Location: Central Europe
Re: Program to edit MM SQLite DB?
Navicat Premium has the same problem:
[Err] 1 - no such collation sequence: IUNICODE
● Magic Nodes v4.3.3 (2018-10-03) last free version ● SQL Viewer v2.4 (2009-10-25)
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
Re: Program to edit MM SQLite DB?
Thanks. Does this prevent operation?MMuser2011 wrote:Navicat Premium has the same problem:[Err] 1 - no such collation sequence: IUNICODE
Chris
-
- Posts: 1308
- Joined: Mon Oct 17, 2011 8:28 am
- Location: Central Europe
Re: Program to edit MM SQLite DB?
Yes. It results in the same limitations (as ORDER BY doesn't work).
You can try something like COLLATE BINARY as mentioned here: http://www.mediamonkey.com/forum/viewto ... =2&t=23908
but this is not exactly the same as COLLATE IUNICODE.
Unfortunately, there is still no IUNICODE extension (*.dll) available from Ventis Media. Such an extension could be loaded in 3rd party applications: http://www.ventismedia.com/mantis/view.php?id=11083
You can try something like COLLATE BINARY as mentioned here: http://www.mediamonkey.com/forum/viewto ... =2&t=23908
but this is not exactly the same as COLLATE IUNICODE.
Unfortunately, there is still no IUNICODE extension (*.dll) available from Ventis Media. Such an extension could be loaded in 3rd party applications: http://www.ventismedia.com/mantis/view.php?id=11083
● Magic Nodes v4.3.3 (2018-10-03) last free version ● SQL Viewer v2.4 (2009-10-25)
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
Re: Program to edit MM SQLite DB?
It is odd that they make it so hard for users to USE their (the users) data. Guess control is everything
Where's the db and ini stored
Reporting Bugs
Where tags are stored
Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig
Reporting Bugs
Where tags are stored
Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig
Re: Program to edit MM SQLite DB?
Limitation I can live with. One some programs it prevents operation. I.e. everything.MMuser2011 wrote:Yes. It results in the same limitations (as ORDER BY doesn't work).
Thanks. But I can live with error messages and disturbed sort.MMuser2011 wrote:You can try something like COLLATE BINARY
What I need is more programs to try, until I find one with multiple field copy and paste.
Chris
-
- Posts: 1308
- Joined: Mon Oct 17, 2011 8:28 am
- Location: Central Europe
Re: Program to edit MM SQLite DB?
If you tell us what kind of SQL command do you try to execute, I can do a test with my Navicat Premium and let you know the result.
● Magic Nodes v4.3.3 (2018-10-03) last free version ● SQL Viewer v2.4 (2009-10-25)
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
-
- Posts: 1308
- Joined: Mon Oct 17, 2011 8:28 am
- Location: Central Europe
Re: Program to edit MM SQLite DB?
SQLiteSpy seems to do that without errors with a SQL command like:
"id = 5" is the existing source row
"id = 6" is the existing target row
You have to enter every single Column (...) that you want to update (up to 52 in the table Devices).
I just made a test it with some(!) of the columns not with an entire row.
BTW: Navicat Premium allows to manually copy&paste multiple rows.
Personally, I would prefer a 'reusable' solution like a SQL command.
I don't know if such an update breaks any index or other things in the MM.DB.
Please be careful with your data and try it with a copy of your database.
Code: Select all
UPDATE Devices
SET
MusicMask = (SELECT MusicMask FROM Devices WHERE id = 5),
ClassMusicMask = (SELECT ClassMusicMask FROM Devices WHERE id = 5),
AudiobookMask = (SELECT AudiobookMask FROM Devices WHERE id = 5)
...
WHERE id = 6;
"id = 6" is the existing target row
You have to enter every single Column (...) that you want to update (up to 52 in the table Devices).
I just made a test it with some(!) of the columns not with an entire row.
BTW: Navicat Premium allows to manually copy&paste multiple rows.
Personally, I would prefer a 'reusable' solution like a SQL command.
I don't know if such an update breaks any index or other things in the MM.DB.
Please be careful with your data and try it with a copy of your database.
● Magic Nodes v4.3.3 (2018-10-03) last free version ● SQL Viewer v2.4 (2009-10-25)
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
Re: Program to edit MM SQLite DB?
Thanks.MMuser2011 wrote:SQLiteSpy seems to do that without errors with a SQL command like:"id = 5" is the existing source rowCode: Select all
UPDATE Devices SET MusicMask = (SELECT MusicMask FROM Devices WHERE id = 5), ClassMusicMask = (SELECT ClassMusicMask FROM Devices WHERE id = 5), AudiobookMask = (SELECT AudiobookMask FROM Devices WHERE id = 5) ... WHERE id = 6;
"id = 6" is the existing target row
You have to enter every single Column (...) that you want to update (up to 52 in the table Devices).
I was looking for copy and paste, but perhaps I should consider a a reusable SQL script instead.MMuser2011 wrote:BTW: Navicat Premium allows to manually copy&paste multiple rows.
Personally, I would prefer a 'reusable' solution like a SQL command.
What I am trying to do is keep the device settings for my many ZEN devices identical, by copying the user-set fields from one device to the others. So the issue with a script specifying target row explicitly as "id = 6" is that I need to paste to different rows. Addressing by name e.g. WHERE DeviceCaption = "ZEN B" would work... except in SQLite I get "SQLite3 Error 1 - no such collation sequence: IUNICODE."
Perhaps I should just write this in an MM script. Or in Bex's SQL Viewer.
Chris
-
- Posts: 1308
- Joined: Mon Oct 17, 2011 8:28 am
- Location: Central Europe
Re: Program to edit MM SQLite DB?
Sure. You can always write a MM script or use Bex's SQL Viewer.
Or you just change the one single target row number from 6 to 7, let it run, change it to 8, let it run, change it to 9, let it run...
Or you just change the one single target row number from 6 to 7, let it run, change it to 8, let it run, change it to 9, let it run...
● Magic Nodes v4.3.3 (2018-10-03) last free version ● SQL Viewer v2.4 (2009-10-25)
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
● ExternalTools v1.4 (2011-05-09) ● iTunesMonkey 1.0 + Acoustid
● Calculate Cover Size v1.7 (2012-10-23) ● RegExp Find & Replace v4.4.9 (2018-10-03) last free version
Re: Program to edit MM SQLite DB?
Seems soMMuser2011 wrote:You have to enter every single Column (...) that you want to update (up to 52 in the table Devices).
I tried
Code: Select all
REPLACE INTO Devices (ID, MusicMask,ClassMusicMask,AudiobookMask)
SELECT 5, MusicMask,ClassMusicMask,AudiobookMask FROM Devices
WHERE id = 6
Does SQLite really have no more-compact way?
Chris
Re: Program to edit MM SQLite DB?
Meanwhile *with thanks to MMuser2011), in case it is useful to anyone else, here's my script to copy all (what I presume are) user-settable device parameters from device "ZEN A" to all other devices having name starting "ZEN ".
Executed in SQL-Viewer: http://i.imgur.com/uIk2KHC.png and results viewed in SQLite Expert Professional http://i.imgur.com/cfWL63F.png .
(I could not find a Refresh key on SQLiteSpy.)
Code: Select all
UPDATE Devices
SET
SynchAll = (SELECT SynchAll FROM Devices WHERE DeviceCaption ="ZEN A"),
AdvancedSynch = (SELECT AdvancedSynch FROM Devices WHERE DeviceCaption ="ZEN A"),
MusicMask = (SELECT MusicMask FROM Devices WHERE DeviceCaption ="ZEN A"),
ClassMusicMask = (SELECT ClassMusicMask FROM Devices WHERE DeviceCaption ="ZEN A"),
AudiobookMask = (SELECT AudiobookMask FROM Devices WHERE DeviceCaption ="ZEN A"),
PodcastMask = (SELECT PodcastMask FROM Devices WHERE DeviceCaption ="ZEN A"),
VideoPodcastMask = (SELECT VideoPodcastMask FROM Devices WHERE DeviceCaption ="ZEN A"),
VideoMask = (SELECT VideoMask FROM Devices WHERE DeviceCaption ="ZEN A"),
MusicVideoMask = (SELECT MusicVideoMask FROM Devices WHERE DeviceCaption ="ZEN A"),
TVMask = (SELECT TVMask FROM Devices WHERE DeviceCaption ="ZEN A"),
SaveAAToFolder = (SELECT SaveAAToFolder FROM Devices WHERE DeviceCaption ="ZEN A"),
SaveAAToTag = (SELECT SaveAAToTag FROM Devices WHERE DeviceCaption ="ZEN A"),
RemoveAAFromTag = (SELECT RemoveAAFromTag FROM Devices WHERE DeviceCaption ="ZEN A"),
RemoveAAByteRate = (SELECT RemoveAAByteRate FROM Devices WHERE DeviceCaption ="ZEN A"),
SyncAAMask = (SELECT SyncAAMask FROM Devices WHERE DeviceCaption ="ZEN A"),
CopyM3U = (SELECT CopyM3U FROM Devices WHERE DeviceCaption ="ZEN A"),
PlaylistFormat = (SELECT PlaylistFormat FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UFolder = (SELECT M3UFolder FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UFlags = (SELECT M3UFlags FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UCopyLocations = (SELECT M3UCopyLocations FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UCopyArtists = (SELECT M3UCopyArtists FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UCopyAlbums = (SELECT M3UCopyAlbums FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UCopyPlaylists = (SELECT M3UCopyPlaylists FROM Devices WHERE DeviceCaption ="ZEN A"),
M3UOrganize = (SELECT M3UOrganize FROM Devices WHERE DeviceCaption ="ZEN A"),
DeleteUnsynch = (SELECT DeleteUnsynch FROM Devices WHERE DeviceCaption ="ZEN A"),
DeleteConfirm = (SELECT DeleteConfirm FROM Devices WHERE DeviceCaption ="ZEN A"),
DeleteUnknown = (SELECT DeleteUnknown FROM Devices WHERE DeviceCaption ="ZEN A"),
DeleteConfirmUnknown = (SELECT DeleteConfirmUnknown FROM Devices WHERE DeviceCaption ="ZEN A"),
ResyncOnMaskChange = (SELECT ResyncOnMaskChange FROM Devices WHERE DeviceCaption ="ZEN A"),
BiDirSync = (SELECT BiDirSync FROM Devices WHERE DeviceCaption ="ZEN A"),
BiDirConfirm = (SELECT BiDirConfirm FROM Devices WHERE DeviceCaption ="ZEN A"),
BiDirSyncMetadata = (SELECT BiDirSyncMetadata FROM Devices WHERE DeviceCaption ="ZEN A"),
SynchOnConnect = (SELECT SynchOnConnect FROM Devices WHERE DeviceCaption ="ZEN A"),
ScanOnConnect = (SELECT ScanOnConnect FROM Devices WHERE DeviceCaption ="ZEN A"),
AllowWifiSync = (SELECT AllowWifiSync FROM Devices WHERE DeviceCaption ="ZEN A"),
AutoUnmount = (SELECT AutoUnmount FROM Devices WHERE DeviceCaption ="ZEN A"),
DontDelete = (SELECT DontDelete FROM Devices WHERE DeviceCaption ="ZEN A"),
SyncBackFolders = (SELECT SyncBackFolders FROM Devices WHERE DeviceCaption ="ZEN A"),
ScanFolders = (SELECT ScanFolders FROM Devices WHERE DeviceCaption ="ZEN A"),
AutoConversions = (SELECT AutoConversions FROM Devices WHERE DeviceCaption ="ZEN A"),
ImagePath = (SELECT ImagePath FROM Devices WHERE DeviceCaption ="ZEN A"),
CopyToPCFolder = (SELECT CopyToPCFolder FROM Devices WHERE DeviceCaption ="ZEN A"),
Invisible = (SELECT Invisible FROM Devices WHERE DeviceCaption ="ZEN A"),
FirstGenre = (SELECT FirstGenre FROM Devices WHERE DeviceCaption ="ZEN A"),
FirstArtist = (SELECT FirstArtist FROM Devices WHERE DeviceCaption ="ZEN A")
WHERE DeviceCaption != "ZEN A" AND DeviceCaption LIKE "ZEN %"
(I could not find a Refresh key on SQLiteSpy.)
Chris
Re: Program to edit MM SQLite DB?
I Escalated http://www.ventismedia.com/mantis/view.php?id=11083 as MMuser2011 solution will work on Numerical but not on Strings (eg. song title).
Best regards,
Peke
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
How to attach PICTURE/SCREENSHOTS to forum posts
Peke
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
How to attach PICTURE/SCREENSHOTS to forum posts