Program to edit MM SQLite DB?

Get answers about using the current release of MediaMonkey for Windows.

Moderator: Gurus

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Program to edit MM SQLite DB?

Post by chrisjj » Sun Aug 17, 2014 10:10 am

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.
Chris

MMuser2011
Posts: 1306
Joined: Mon Oct 17, 2011 8:28 am
Location: Central Europe

Re: Program to edit MM SQLite DB?

Post by MMuser2011 » Sun Aug 17, 2014 10:30 am

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

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Sun Aug 17, 2014 12:30 pm

MMuser2011 wrote:Navicat Premium has the same problem:
[Err] 1 - no such collation sequence: IUNICODE
Thanks. Does this prevent operation?
Chris

MMuser2011
Posts: 1306
Joined: Mon Oct 17, 2011 8:28 am
Location: Central Europe

Re: Program to edit MM SQLite DB?

Post by MMuser2011 » Sun Aug 17, 2014 2:01 pm

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

dtsig
Posts: 3198
Joined: Mon Jan 24, 2011 6:34 pm

Re: Program to edit MM SQLite DB?

Post by dtsig » Sun Aug 17, 2014 2:11 pm

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

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Sun Aug 17, 2014 3:38 pm

MMuser2011 wrote:Yes. It results in the same limitations (as ORDER BY doesn't work).
Limitation I can live with. One some programs it prevents operation. I.e. everything.
MMuser2011 wrote:You can try something like COLLATE BINARY
Thanks. But I can live with error messages and disturbed sort.

What I need is more programs to try, until I find one with multiple field copy and paste.
Chris

MMuser2011
Posts: 1306
Joined: Mon Oct 17, 2011 8:28 am
Location: Central Europe

Re: Program to edit MM SQLite DB?

Post by MMuser2011 » Sun Aug 17, 2014 5:08 pm

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

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Sun Aug 17, 2014 5:14 pm

Thanks!

copy Image
paste Image
Chris

MMuser2011
Posts: 1306
Joined: Mon Oct 17, 2011 8:28 am
Location: Central Europe

Re: Program to edit MM SQLite DB?

Post by MMuser2011 » Mon Aug 18, 2014 2:43 am

SQLiteSpy seems to do that without errors with a SQL command like:

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 = 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.
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

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Mon Aug 18, 2014 8:12 am

MMuser2011 wrote:SQLiteSpy seems to do that without errors with a SQL command like:

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 = 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).
Thanks.
MMuser2011 wrote:BTW: Navicat Premium allows to manually copy&paste multiple rows.
Personally, I would prefer a 'reusable' solution like a SQL command.
I was looking for copy and paste, but perhaps I should consider a a reusable SQL script instead.

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

MMuser2011
Posts: 1306
Joined: Mon Oct 17, 2011 8:28 am
Location: Central Europe

Re: Program to edit MM SQLite DB?

Post by MMuser2011 » Mon Aug 18, 2014 8:20 am

Sure. You can always write a MM script or use Bex's SQL Viewer. :wink:

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

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Mon Aug 18, 2014 12:42 pm

MMuser2011 wrote:You have to enter every single Column (...) that you want to update (up to 52 in the table Devices).
Seems so :(

I tried

Code: Select all

REPLACE INTO Devices (ID, MusicMask,ClassMusicMask,AudiobookMask)
SELECT 5, MusicMask,ClassMusicMask,AudiobookMask FROM Devices
WHERE id = 6
but this wiped every other destination field:

Image

Does SQLite really have no more-compact way?
Chris

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Mon Aug 18, 2014 7:40 pm

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 ".

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 %"
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.)
Chris

Peke
Posts: 13082
Joined: Tue Jun 10, 2003 7:21 pm
Location: Serbia
Contact:

Re: Program to edit MM SQLite DB?

Post by Peke » Mon Aug 18, 2014 8:49 pm

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,
Pavle
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
How to add SCREENSHOTS to forum

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: Program to edit MM SQLite DB?

Post by chrisjj » Mon Aug 18, 2014 8:53 pm

Thanks Peke.
Chris

Post Reply