Backup Playlists 2.1 [MM2+3]

[trixmoto]

This script was suggested/discussed here: viewtopic.php?t=6930

It allows you to export your autoplaylist criteria (not the songs themselves, nor manual playlists) into a separate database. You can then use this script to import those criteria into your MM database. This can be used for backing up or transferring criteria from one library to another.

As always, installers for this script can be downloaded from my website!

Code: Select all

'
' MediaMonkey Script
'
' NAME: BackupPlaylists 2.1
'
' AUTHOR: trixmoto (http://trixmoto.net)
' DATE : 17/08/2008
'
' Thank you to Steegy for his help.
'
' INSTALL: Copy to Scripts directory and add the following to Scripts.ini
'          Don't forget to remove comments (') and set the order appropriately
'
' [BackupPlaylists]
' FileName=BackupPlaylists.vbs
' ProcName=BackupPlaylists
' Order=15
' DisplayName=Backup Playlists
' Description=Backup playlists
' Language=VBScript
' ScriptType=0
'
' FIXES: Fixed problems caused by apostrophes
'

Option Explicit

Sub BackupPlaylists
  Dim Form : Set Form = SDB.UI.NewForm
  Form.Common.SetRect 100, 100, 235, 110
  Form.BorderStyle  = 3
  Form.FormPosition = 4
  Form.Caption = "Backup Playlists"

  Dim Btn1 : Set Btn1 = SDB.UI.NewButton(Form)
  Btn1.Caption = "Export to backup"
  Btn1.Common.Top = 10
  Btn1.Common.Left = 10
  Btn1.Common.Width = 200
  Btn1.UseScript = Script.ScriptPath
  Btn1.ModalResult = 1

  Dim Btn2 : Set Btn2 = SDB.UI.NewButton(Form)
  Btn2.Caption = "Import from backup"
  Btn2.Common.Top = 40
  Btn2.Common.Left = 10
  Btn2.Common.Width = 200
  Btn2.UseScript = Script.ScriptPath
  Btn2.ModalResult = 3
 
  Dim result : result = Form.showModal
  If result = 1 Then
    If SDB.VersionHi > 2 Then
      Call Export3()
    Else
      Call Export()
    End If
  ElseIf result = 3 Then
    If SDB.VersionHi > 2 Then
      Call Import3()
    Else
      Call Import()
    End If
  End If
End Sub

Sub Export
  Dim output : output = "Starting export..."&Chr(13)
  Dim mmpath : mmpath = getmmpath()
  output = output&"MMPath="&mmpath&Chr(13)
  If Not (mmpath = "") Then
    Dim dbpath : dbpath = getdbpath()
    output = output&"DBPath="&dbpath&Chr(13)
    If Not (dbpath = "") Then
      Dim mmconn : Set mmconn = getconnection(mmpath)
      Dim mmrset : Set mmrset = mmconn.Execute("SELECT * FROM Playlists WHERE (IsAutoPlaylist = 1 OR IDPlaylist IN (SELECT ParentPlaylist FROM Playlists));")
      Dim Catalog : Set Catalog = CreateObject("ADOX.Catalog")
      Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Engine Type=5; Data Source="&dbpath
      output = output&"Backup database created"&Chr(13)
      Dim dbconn : Set dbconn = getconnection(dbpath)
      Dim dbrset : Set dbrset = dbconn.Execute("CREATE TABLE Playlists ([IDPlaylist] integer, [PlaylistName] text, [ParentPlaylist] integer, [Comment] memo, [IsAutoPlaylist] integer, [QueryData] memo, [Persistent] integer, [Synchronize] integer, CONSTRAINT [Index1] PRIMARY KEY ([IDPlaylist]));")
      output = output&"Playlists table created"&Chr(13)
      Dim mmrsetIDPlaylist,mmrsetPlaylistName,mmrsetParentPlaylist,mmrsetComment
      Dim mmrsetIsAutoPlaylist,mmrsetQueryData,mmrsetPersistent,mmrsetSynchronize
      Do While Not mmrset.EOF
        If Not (mmrset("IDPlaylist") = "") Then mmrsetIDPlaylist = mmrset("IDPlaylist") Else mmrsetIDPlaylist = 0
        mmrsetPlaylistName = Replace(mmrset("PlaylistName"),"'","''")
        If Not (mmrset("ParentPlaylist") = "") Then mmrsetParentPlaylist = mmrset("ParentPlaylist") Else mmrsetParentPlaylist = 0
        mmrsetComment = Replace(mmrset("Comment"),"'","''")
        If Not (mmrset("IsAutoPlaylist") = "") Then mmrsetIsAutoPlaylist = mmrset("IsAutoPlaylist") Else mmrsetIsAutoPlaylist = 0
        mmrsetQueryData = Replace(mmrset("QueryData"),"'","''")
        If Not (mmrset("Persistent") = "") Then mmrsetPersistent = mmrset("Persistent") Else mmrsetPersistent = 0
        If Not (mmrset("Synchronize") = "") Then mmrsetSynchronize = mmrset("Synchronize") Else mmrsetSynchronize = 0
        Set dbrset = dbconn.Execute("INSERT INTO Playlists (IDPlaylist, PlaylistName, ParentPlaylist, Comment, IsAutoPlaylist, QueryData, Persistent, Synchronize) Values ("& mmrsetIDPlaylist & ", '" & mmrsetPlaylistName & "', " & mmrsetParentPlaylist & ", '" & mmrsetComment & "', " & mmrsetIsAutoPlaylist & ", '" & mmrsetQueryData & "', " & mmrsetPersistent & ", " & mmrsetSynchronize & ");")
        output = output&"Playlist exported: "&dbrsetPlaylistName&Chr(13)
        mmrset.MoveNext
      Loop
      mmconn.Close
      dbconn.Close
      Call SDB.MessageBox(output&"Export complete.", mtInformation, Array(mbOk))
    Else
      Call SDB.MessageBox(output&"Error: 'BackupPlaylists.mdb' was not selected.",mtError,Array(mbOk)) 
    End If
  Else
    Call SDB.MessageBox(output&"Error: 'MediaMonkey.mdb' could not be found.",mtError,Array(mbOk))
  End If
End Sub

Sub Import
  Dim output : output = "Starting import..."&Chr(13)
  Dim dbpath : dbpath = getdbpath()   
  output = output&"DBPath="&dbpath&Chr(13)
  If Not (dbpath = "") Then
    Dim mmpath : mmpath = getmmpath()
    output = output&"MMPath="&mmpath&Chr(13)
    If Not (mmpath = "") Then
      Dim dbconn : Set dbconn = getconnection(dbpath)
      Set dbrset = dbconn.Execute("SELECT * FROM Playlists WHERE (IsAutoPlaylist = 1 OR IDPlaylist IN (SELECT ParentPlaylist FROM Playlists));")
      Dim mmconn : Set mmconn = getconnection(mmpath)
      Dim mmrset : Set mmrset = mmconn.Execute("SELECT IDPlaylist FROM Playlists")     
      Dim dbrsetIDPlaylist,currid
      dbrsetIDPlaylist = 0
      Do While Not mmrset.EOF
        If Not (mmrset("IDPlaylist") = "") Then currid = mmrset("IDPlaylist") Else currid = 0       
        If currid > dbrsetIDPlaylist Then dbrsetIDPlaylist = currid
        mmrset.MoveNext
      Loop
      Dim dbrsetPlaylistName,dbrsetParentPlaylist,dbrsetComment
      Dim dbrsetIsAutoPlaylist,dbrsetQueryData,dbrsetPersistent,dbrsetSynchronize
      Do While Not dbrset.EOF
        dbrsetIDPlaylist = dbrsetIDPlaylist + 1
        dbrsetPlaylistName = Replace(dbrset("PlaylistName"),"'","''")
        If Not (dbrset("ParentPlaylist") = "") Then dbrsetParentPlaylist = dbrset("ParentPlaylist") Else dbrsetParentPlaylist = 0
        If Not (dbrset("Comment") = "") Then dbrsetComment = Replace(dbrset("Comment"),"'","''") Else dbrsetComment = "Thanks Trixmoto"
        If Not (dbrset("IsAutoPlaylist") = "") Then dbrsetIsAutoPlaylist = dbrset("IsAutoPlaylist") Else dbrsetIsAutoPlaylist = 0
        dbrsetQueryData = Replace(dbrset("QueryData"),"'","''")
        If Not (dbrset("Persistent") = "") Then dbrsetPersistent = dbrset("Persistent") Else dbrsetPersistent = 0
        If Not (dbrset("Synchronize") = "") Then dbrsetSynchronize = dbrset("Synchronize") Else dbrsetSynchronize = 0
   
        Set mmrset = mmconn.Execute("SELECT * FROM Playlists WHERE PlaylistName='"&dbrsetPlaylistName&"'")
        If mmrset.EOF Then
          Set mmrset = mmconn.Execute("INSERT INTO Playlists (IDPlaylist, PlaylistName, ParentPlaylist, Comment, IsAutoPlaylist, QueryData, Persistent, Synchronize) Values (" & dbrsetIDPlaylist & ", '" & dbrsetPlaylistName & "', " & dbrsetParentPlaylist & ", '" & dbrsetComment & "', " & dbrsetIsAutoPlaylist & ", '" & dbrsetQueryData & "', " & dbrsetPersistent & ", " & dbrsetSynchronize & ");")
          output = output&"Playlist imported: "&dbrsetPlaylistName&Chr(13)
        Else
          output = output&"Playlist already exists: "&dbrsetPlaylistName&Chr(13)
        End If
        dbrset.MoveNext
      Loop       
      mmconn.Close
      dbconn.Close
      Call SDB.MessageBox(output&"Import complete.", mtInformation, Array(mbOk))
    Else
      Call SDB.MessageBox(output&"Error: 'BackupPlaylists.mdb' was not selected.",mtError,Array(mbOk)) 
    End If
  Else
    Call SDB.MessageBox(output&"Error: 'MediaMonkey.mdb' could not be found.",mtError,Array(mbOk)) 
  End If
End Sub

Function getdbpath()
  getdbpath = SDB.IniFile.StringValue("Scripts","BackupPlaylistsDir")
  getdbpath = SDB.SelectFolder(getdbpath, "Select your BackupPlaylists database location:")
  If getdbpath = "" Then
    Exit Function
  End If
  If Not (Right(getdbpath,1) = "\") Then
    SDB.IniFile.StringValue("Scripts","BackupPlaylistsDir") = getdbpath&"\"
    getdbpath = getdbpath&"\BackupPlaylists.mdb"
  Else
    SDB.IniFile.StringValue("Scripts","BackupPlaylistsDir") = getdbpath
    getdbpath = getdbpath&"BackupPlaylists.mdb"
  End If
End Function

Function getmmpath()
  getmmpath = SDB.IniFile.StringValue("System","DBName")
  If getmmpath = "" Then
    getmmpath = SDB.MyMusicPath&"MediaMonkey\"
  End If
  getmmpath = SDB.SelectFolder(getmmpath, "Select your MediaMonkey database location:")
  If getmmpath = "" Then
    Exit Function
  End If
  If Not (Right(getmmpath,1) = "\") Then
    getmmpath = getmmpath&"\MediaMonkey.mdb"
  Else
    getmmpath = getmmpath&"MediaMonkey.mdb"
  End If
End Function

Function getconnection(path)
  Set getconnection = CreateObject("ADODB.Connection")
  getconnection.Provider = "Microsoft.Jet.OLEDB.4.0"
  getconnection.Open "Data Source="&path
End Function

Sub Export3()
  If InstallLiteX() Then
    Dim output : output = "Starting export..."&Chr(13)
    Dim mmpath : mmpath = SDB.Database.Path
    output = output&"MMPath="&mmpath&Chr(13)
    If Not (mmpath = "") Then
      Dim dbpath : dbpath = Replace(getdbpath(),".mdb",".DB")
      output = output&"DBPath="&dbpath&Chr(13)
      If Not (dbpath = "") Then
        Dim db : Set db = CreateObject("LiteX.LiteConnection")
        If Not (db Is Nothing) Then
          db.Open(mmpath)
          Dim bp : Set bp = CreateObject("LiteX.LiteConnection")
          If Not (bp Is Nothing) Then
            bp.Open(dbpath)
            output = output&"Backup database created"&Chr(13)
            bp.Execute("DROP TABLE IF EXISTS Playlists")
            bp.Execute("CREATE TABLE Playlists (IDPlaylist integer, PlaylistName text, ParentPlaylist integer, Comment text, IsAutoPlaylist integer, QueryData text, srcMedia integer, srcPath text, Persistent integer, Synchronize integer, CONSTRAINT Index1 PRIMARY KEY (IDPlaylist))")
            bp.Execute("BEGIN TRANSACTION")
            output = output&"Playlists table created"&Chr(13)           
            Dim qy : Set qy = db.Prepare("SELECT * FROM Playlists WHERE (IsAutoPlaylist = 1 OR IDPlaylist IN (SELECT ParentPlaylist FROM Playlists))")
            If Not (qy Is Nothing) Then
              Dim ns : ns = "IDPlaylist, PlaylistName, ParentPlaylist, Comment, IsAutoPlaylist, QueryData, srcMedia, srcPath, Persistent, Synchronize"
              While (Not qy.Step())
                Dim ri : Set ri = qy.Rows.Item
                Dim vs : vs = FixInt(ri(0))&",'"&FixStr(ri(1))&"',"&FixInt(ri(2))&",'"&FixStr(ri(3))&"',"&FixInt(ri(4))&",'"&FixStr(ri(5))&"',"&FixInt(ri(6))&",'"&FixStr(ri(7))&"',"&FixInt(ri(8))&","&FixInt(ri(9))
                bp.Execute("INSERT INTO Playlists ("&ns&") Values ("&vs&")")
                output = output&"Playlist exported: "&ri(1)&Chr(13)
              WEnd
              qy.Close()
            End If
            bp.Execute("COMMIT TRANSACTION")
            bp.Close()
            Call SDB.MessageBox(output&"Export complete.", mtInformation, Array(mbOk))
          Else
            Call SDB.MessageBox(output&"Error: 'LiteX' could not be created.",mtError,Array(mbOk)) 
          End If
          db.Close()
        Else
          Call SDB.MessageBox(output&"Error: 'LiteX' could not be created.",mtError,Array(mbOk)) 
        End If
      Else
        Call SDB.MessageBox(output&"Error: 'BackupPlaylists.DB' was not selected.",mtError,Array(mbOk)) 
      End If
    Else
      Call SDB.MessageBox(output&"Error: 'MM.DB' could not be found.",mtError,Array(mbOk)) 
    End If
  Else
    Call SDB.MessageBox(output&"Error: 'LiteX' could not be installed.",mtError,Array(mbOk)) 
  End If
End Sub

Sub Import3()
  If InstallLiteX() Then
    Dim output : output = "Starting import..."&Chr(13)
    Dim dbpath : dbpath = Replace(getdbpath(),".mdb",".DB")
    output = output&"DBPath="&dbpath&Chr(13)
    If Not (dbpath = "") Then
      Dim mmpath : mmpath = SDB.Database.Path
      output = output&"MMPath="&mmpath&Chr(13)
      If Not (mmpath = "") Then
        Dim bp : Set bp = CreateObject("LiteX.LiteConnection")
        If Not (bp Is Nothing) Then
          bp.Open(dbpath)
          Dim db : Set db = SDB.Database
          If Not (db Is Nothing) Then
            Dim id : id = FixInt(db.OpenSQL("SELECT Max(IDPlaylist) FROM Playlists").ValueByIndex(0))
            Dim ns : ns = "IDPlaylist, PlaylistName, ParentPlaylist, Comment, IsAutoPlaylist, QueryData, srcMedia, srcPath, Persistent, Synchronize"
            Dim qy : Set qy = bp.Prepare("SELECT * FROM Playlists WHERE (IsAutoPlaylist = 1 OR IDPlaylist IN (SELECT ParentPlaylist FROM Playlists))")
            If Not (qy Is Nothing) Then     
              While (Not qy.Step())
                Dim ri : Set ri = qy.Rows.Item
                Dim ps : ps = FixInt(db.OpenSQL("SELECT Count(*) FROM Playlists WHERE PlaylistName='"&ri(1)&"'").ValueByIndex(0))
                If ps = 0 Then
                  id = id+1
                  Dim vs : vs = id&",'"&FixStr(ri(1))&"',"&FixInt(ri(2))&",'"&FixStr(ri(3))&"',"&FixInt(ri(4))&",'"&FixStr(ri(5))&"',"&FixInt(ri(6))&",'"&FixStr(ri(7))&"',"&FixInt(ri(8))&","&FixInt(ri(9))
                  db.ExecSQL("INSERT INTO Playlists ("&ns&") Values ("&vs&")")
                  output = output&"Playlist imported: "&ri(1)&Chr(13)
                Else
                  output = output&"Playlist already exists: "&ri(1)&Chr(13)
                End If
              WEnd
              qy.Close()
            End If 
            Call SDB.MessageBox(output&"Import complete.", mtInformation, Array(mbOk))
          Else
            Call SDB.MessageBox(output&"Error: 'LiteX' could not be created.",mtError,Array(mbOk))           
          End If
        Else
          Call SDB.MessageBox(output&"Error: 'LiteX' could not be created.",mtError,Array(mbOk)) 
        End If
      Else
        Call SDB.MessageBox(output&"Error: 'MM.DB' could not be found.",mtError,Array(mbOk)) 
      End If
    Else
      Call SDB.MessageBox(output&"Error: 'BackupPlaylists.DB' was not selected.",mtError,Array(mbOk)) 
    End If
  Else
    Call SDB.MessageBox(output&"Error: 'LiteX' could not be installed.",mtError,Array(mbOk)) 
  End If
End Sub

Function InstallLiteX()
  InstallLiteX = False
  On Error Resume Next
  Dim litex : Set litex = CreateObject("LiteX.LiteConnection")
  If Not (Err.Number = 0) Then
    Err.Clear
    Dim dll : dll = SDB.ApplicationPath&"Scripts\sqlite3u.dll"
    Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(dll) Then
      Exit Function
    End If
    Dim wsh : Set wsh = CreateObject("WScript.Shell")
    Dim res : res = wsh.Run("regsvr32 "&Chr(34)&dll&Chr(34)&" /s",1,1)
  End If 
  On Error GoTo 0   
  InstallLiteX = True
End Function

Function FixInt(num)
  If IsNull(num) Then
    FixInt = "0"
  Else
    If num = "" Then
      FixInt = "0"
    Else
      FixInt = CStr(num)
    End If
  End If
End Function

Function FixStr(str)
  If IsNull(str) Then
    FixStr = ""
  Else
    FixStr = Replace(str,"'","''")
  End If
End Function

Sub Install()
  Dim inip : inip = SDB.ApplicationPath&"Scripts\Scripts.ini"
  Dim inif : Set inif = SDB.Tools.IniFileByPath(inip)
  If Not (inif Is Nothing) Then
    inif.StringValue("BackupPlaylists","Filename") = "BackupPlaylists.vbs"
    inif.StringValue("BackupPlaylists","Procname") = "BackupPlaylists"
    inif.StringValue("BackupPlaylists","Order") = "15"
    inif.StringValue("BackupPlaylists","DisplayName") = "Backup Playlists"
    inif.StringValue("BackupPlaylists","Description") = "Backup Playlists"
    inif.StringValue("BackupPlaylists","Language") = "VBScript"
    inif.StringValue("BackupPlaylists","ScriptType") = "0"
    SDB.RefreshScriptItems
  End If
End Sub 

[gab]

Trix:

I'm getting an error running this script: "Error # 2147217900 - Microsoft JET database engine...". Any ideas what I'm doing wrong. Also, any way to integrate the playlist backup with your other Backup script to make it a one step process?

Thanks,
George

[trixmoto]

You probably do not have the Jet engine installed correctly. There are Windows version specific instructions here: http://www.zuggsoft.com/data/

[martialartsguy]

I was receiving the same error when attempting to output the Playlists.

After attempting to download the Jet engine as suggested from your link, I receive the error:

Setup has detected that the Service Pack version of this system is
newer than the update you are applying.

Any suggestions or should I just wait for MM 3.0?

Love this script btw. Makes keeping track of my playlists much easier.

[trixmoto]

If you have a different version installed you might need to update this line...

Code: Select all

getconnection.Provider = "Microsoft.Jet.OLEDB.4.0"

This script will not work in MM3 as it uses an ADODB connection which I don't think will be compatible with the new SQLite database.

[martialartsguy]

Thank - I'll give it a try when I get home.

Bummer it won't work w/MM3 but I see you have a rewrite in red on the scripts page. I look forward to it.

[trixmoto]

I don't know if it will be possible as I haven't learnt SQList yet, but hopefully I will rewrite this script for MM3.

[martialartsguy]

Trix-

That's actually the line in my current version. I tried to do a search to see if I could find out the version I had of Jet, but no luck. Any other ideas?

[trixmoto]

New version (2.0) is now available to download from my website. I have made it compatible with MM3, but nothing has changed in MM2 so no need to upgrade this script if you haven't upgraded your monkey!

In MM3 a component called "LiteX" needs to be installed. The script will attempt to do this automatically but if you have UAC enabled on Vista then this will probably fail. In this case you can manually install the component by right-clicking on a "cmd.exe" shortcut and select "Run as administrator". You should get a black window in which you can enter...

regsvr32 "C:\Program Files\MediaMonkey\Scripts\sqlite3u.dll"

...although you might need to update the path! After doing this the script should function fully. I have tested with some unicode characters but I don't have many in my library so please let me know if you have any problems.

[nynaevelan]

In MM3 a component called "LiteX" needs to be installed.

Is this LiteX included with the sript, if not where would one download it from??

Nyn

EDIT: Ok, I found the program from a link in one of your other posts but since there is not exe file, how do I install it?

[trixmoto]

Yes, the file is included in the installation package. It should be here: "C:\Program Files\MediaMonkey\Scripts\sqlite3u.dll"

[nynaevelan]

That's what I thought but when I follow your instructions I get this error, I tried it with and without the quotation marks:



Nyn

Ok, I got it to work, you had a typo in there "regsvr32"

[nynaevelan]

Ok, now that I got it to work I get this error when running an export:



Nyn

It appears the error is in two of my playlists since they had a name with the apostrophe in it, once I removed it the export worked. Now I have another problem, the backup is not exporting all of my playlists. I have my playlists nested into groups with a total of 153 playlists. In the Playlists table of the main db I have 168 rows, in the Playlists table of the Backup Playlists db I have 27 rows. After comparing the results to my actual playlists, the script is exporting all the autoplaylists but it is only exporting the Parent playlists. It is not exporting the nested static playlists. Any suggestions?? BTW, sorry for making a PITA of myself, AGAIN.

Nyn

[MusicBringer]

Ok, now that I got it to work I get this error when running an export:

Sorry to say that I too, am getting the sort of error message.
It happens on both my PC and my laptop.

[nynaevelan]

MusicBringer:

Check the name of your playlists, I had a couple of playlists that started with a ', once I changed it the script ran successfully. I only ran into this with the playlists which started with the apostrophe, I don't know if it will create a problem for those with the apostrophe in the middle because none of my nested playlists are being exported.

Nyn