Program to edit MM SQLite DB?

[chrisjj]

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.

[MMuser2011]

Navicat Premium has the same problem:

[Err] 1 - no such collation sequence: IUNICODE

[chrisjj]

Navicat Premium has the same problem:

[Err] 1 - no such collation sequence: IUNICODE

Thanks. Does this prevent operation?

[MMuser2011]

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

[dtsig]

It is odd that they make it so hard for users to USE their (the users) data. Guess control is everything

[chrisjj]

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.

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.

[MMuser2011]

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.

[chrisjj]

Thanks!

copy
paste

[MMuser2011]

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.

[chrisjj]

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.

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.

[MMuser2011]

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

[chrisjj]

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:



Does SQLite really have no more-compact way?

[chrisjj]

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

[Peke]

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

[chrisjj]

Thanks Peke.