SQL-Viewer 2.4 [Script] 2009-10-25

Bex · Post by **Bex** » Sun Jan 13, 2008 6:24 pm

SQL-Viewer

This script lets you run SQL-statements directly in MM, using MM's "own" SQLite engine. It has a huge advantage compared to external SQLite programs.
No more annoying: SQLite Error 1 - no such collation sequence: IUNICODE

You'll also get correct results when querying TEXT-fields in respect of Unicode characters, which isn't the case in external programs.

It also means that you now can use MM specific SQLite functions, such as LIKE, INSTR(), UPPERW() and more.
See: http://www.mediamonkey.com/wiki/index.p ... _structure

The script it not as fancy looking as some external SQLite viewing programs but it has some smart features which they lack.

This is how it looks like:

Bug-reports, suggestions and comments are of course welcome!
Let me know what you think!

Enjoy!
/Bex

For information only. You can download the installer package and read the installation instructions at the bottom of this post.
---------------------------------------------------------------------------------

Code: Select all

' MediaMonkey Script
'
' NAME: SQL Viewer
' VERSION: 2.4
' LAST UPDATED: 2009-10-25
' AUTHOR: Bex
' DATE : 2007-01-13
'/////////////////////////////////////////////////////////////////////////////////////////////////'
Option Explicit 
Const akLeft = 1
Const akTop = 2
Const akRight = 4
Const akBottom = 8
Const alTop = 1
Const alBottom = 2
Const alClient = 5
Const mmListDropdown = 2
Const mmFormScreenCenter = 4
Public styleOn

Dim Tables(),Fields(),History(),Saves(),Templates(),Functions(),SongData()

Dim INI : Set INI = SDB.IniFile
Dim UI  : Set UI  = SDB.UI
Dim DB  : Set DB  = SDB.DataBase

Sub SqlViewer()  
  Dim Form,Foot,Top1,Top2,Top3,Mle,WB,HTML,Spi
  Dim DlgWidth,LastRun,LastRows,LastTime,LastHtml,LastSql

  DB.ExecSQL("CREATE TABLE If NOT EXISTS TmpSqlHist (tmpIDnr INTEGER PRIMARY KEY, tmpRows INTEGER, tmpTime TEXT, tmpHtml TEXT, tmpSql TEXT)")
  DB.ExecSQL("CREATE TABLE If NOT EXISTS TmpSqlSaves (tmpIDnr INTEGER PRIMARY KEY, tmpName TEXT, tmpRows INTEGER, tmpTime TEXT, tmpHtml TEXT, tmpSql TEXT)")
  DlgWidth = 700

  ' Create the window to be shown
  Set Form = UI.NewForm
  Form.Common.SetRect 50, 50, DlgWidth, 400
  Form.Common.MinWidth = 720
  Form.Common.MinHeight = 410
  Form.FormPosition = mmFormScreenCenter
  Form.Caption = "SQL Query Viewer & DDL/DML Executor (SQLite: " & DB.OpenSQL("SELECT SQLITE_VERSION(*)").ValueByIndex(0) &")"
  Form.BorderStyle = 2
  Form.StayOnTop = True

  'Create the third panel from the Top of the window for hosting window action buttons
  Set Top3 = UI.NewTranspPanel(Form)
  Top3.Common.Align = alTop
  Top3.Common.Height = 28

  'Create the second panel from the Top of the window to host the sql-input window
  Set Top2 = UI.NewTranspPanel(Form)
  Top2.Common.Align = alTop
  Top2.Common.Height = 155

  'Create the first panel from the Top hosting various buttons
  Set Top1 = UI.NewTranspPanel(Form)
  Top1.Common.Align = alTop
  Top1.Common.Height = 102
  
  'Create a Footer
  Set Foot = UI.NewTranspPanel(Form)
  Foot.Common.Align = alBottom
  Foot.Common.Height = 28
  Foot.Common.ControlName = "Foot"
  
  ' Create a web browser component
  Set WB = UI.NewActiveX(Form, "Shell.Explorer")
  WB.Common.Align = alClient      ' Fill all client rectangle
  WB.Common.ControlName = "WB"

  Set SDB.Objects("holdHTML") = CreateObject("Scripting.Dictionary")
  SDB.Objects("holdHTML").RemoveAll
  SDB.Objects("holdHTML").add 0,""

  Call CreateDrd(Top1,  2,  2,700,20,"History",1+2+4,0,"History",History,"AddFromHist","Select a History record to add it to the SQL Window")
  Call CreateDrd(Top1,  2, 27,560,20,"Saves","",0,"Saves",Saves,"AddFromSave","Select a Saved record to add it to the SQL Window")
  Dim SavesItemIdx : SavesItemIdx=Form.Common.TopParent.Common.ChildControl("Saves").ItemIndex
  Call CreateBtn(Top1,564, 25, 40,24,"Save","SaveBtn","SavCurSql","","Save Current Query/Statement To Selected Name","","",Eval(SavesItemIdx>0))
  Call CreateBtn(Top1,605, 25, 48,24," Save as","","Saveas","","Save Current Query/Statement to a New Name","","",True)
  Call CreateBtn(Top1,655, 25, 48,24,"Delete","DelBtn","DelSave","","Delete selected Save","","",Eval(SavesItemIdx>0))
  
  Call CreateBtn(Top1,  1, 50, 50,23,"SELECT"  ,"","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1, 51, 50, 20,23," *"      ,"","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1, 72, 50, 60,23,"COUNT(*)","","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,133, 50, 50,23,"FROM"    ,"","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,184, 50, 50,23,"WHERE"   ,"","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,235, 50, 60,23,"GROUP BY","","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,296, 50, 55,23,"HAVING"  ,"","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,352, 50, 60,23,"ORDER BY","","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,413, 50, 50,23,"Comma"   ,"","AddSQLtxt", "","","","",True)
  Call CreateBtn(Top1,464, 50, 20,23," ="      ,"","AddSQLtxt", "","","","",True)
  
  Call CreateBtn(Top1,504, 50, 55,23,"RemVBS","","RemVBS","","Removes Surrounding Spaces, Double quotes, Ampersand and Underscore","","",True)
  Call CreateBtn(Top1,560, 50, 55,23,"AddVBS","","AddVBS","","The Opposite of RemVBS with specified Indent Spaces","","",True)
  Call CreateLbl(Top1,620, 55, 20,25,"Indent:","")
  Set Spi = UI.NewSpinEdit(Top1)
  Spi.Common.SetRect       662, 51, 40,20
  Spi.Common.ControlName = "IndSpaces"
  Spi.MinValue = 0
  Spi.MaxValue = 40
  Spi.Common.Hint = "Indent Spaces in AddVBS"
  Spi.Value = INI.IntValue("SqlViewer","IndSpaces")
  Script.RegisterEvent Spi, "OnChange", "ChangeIndSpaces"
  
  Call CreateDrd(Top1,  2, 76,150,20,"Tables","",0,"Tables",Tables,"PopuFields","Select a Table and Press Add to add it to the SQL Window")
  Call CreateBtn(Top1,156, 74, 40,24,"&Add","","AddTable","","Adds Selected Table to the SQL Window","","",True)
  Call CreateDrd(Top1,200, 76,200,20,"Fields","",0,"Fields",Fields,"AddField","Select a Field to add it to the SQL Window")
  Call CreateCbx(Top1,404, 78,200,20,"Add as Table.Field","IfTable","If Checked:"& VbNewline &"Fields are Added with its TableName"&_
                                          VbNewline &"If not Checked:"& VbNewline &"Only FieldName is added",INI.BoolValue("SqlViewer","AddTable"),"OnClickAddTable")
  Call CreateDrd(Top1,520, 76,182,20,"SongData","",0,"SongData",SongData,"AddSongData","Select a Field to add its data from the Selected song to the SQL Window")
  'Call CreateDrd(Top1,520, 51,100,20,"Functions",0,"Functions",Functions,"AddFunction","Select a Function to add it to the SQL Window")

  ' Create a button that runs the SQL
  Call CreateLbl(Top3,135,  7, 70, 25,"Display Only","")
  Call CreateLbl(Top3,265,  7, 70, 25,"Rows","")
  Set Spi = UI.NewSpinEdit(Top3)
  Spi.Common.SetRect       200,  4, 60, 20
  Spi.Common.ControlName = "MaxRows"
  Spi.MinValue = 1
  Spi.MaxValue = 9999
  Spi.Value = INI.IntValue("SqlViewer","MaxRows")
  Script.RegisterEvent Spi, "OnChange", "ChangeMaxRows"
  Call CreateBtn(Top3,  2,  2, 120, 24,"&Run Select Query","","RunQuery","","","","",True)
  Call CreateBtn(Top3,300,  2, 180, 24,"&Execute DDL/DML Statement","","ExecSql","","","","",True)

  Call CreateLbl(Foot,  5,  2,70,20,"Rows:","")
  Call CreateLbl(Foot, 40,  2,70,20,0,"Rows") 
  Call CreateLbl(Foot,  9, 17,70,20,"Time:","")
  Call CreateLbl(Foot, 40, 17,70,20,Trim(" "),"eTime") 'If I write "" then the controlname eTime is Displayed in the Foot???? 
  Call CreateBtn(Foot,592,  5,85,24,"&Close","","OnClose", akRight,"","","Cancel",True)
  Call CreateBtn(Foot,500,  5,85,24,"Clear All","","ClearAll", akRight,"","","",True)

  'Create sql-input window
  Set Mle = UI.NewMultiLineEdit(Top2)
  Mle.Common.Align = alClient
  Mle.Common.ControlName = "sql"
  Mle.Text = Trim(" ") 'If I write "" then the controlname is written as text???? 

  Form.SavePositionName = "SQLWindow"
  Form.Common.Visible = True
  SDB.Objects("SQLThingy") = Form  ' Save reference to the form somewhere, otherwise it would simply disappear
End Sub

Sub ClearAll(Btn)
  Dim Top : Set Top=Btn.Common.TopParent.Common
  Top.ChildControl("History").ItemIndex=0
  Top.ChildControl("Saves").ItemIndex=0
  Top.ChildControl("Tables").ItemIndex=0
  Top.ChildControl("Fields").ItemIndex=0
  Top.ChildControl("SongData").ItemIndex=0
  Top.ChildControl("sql").Text=""
  Top.ChildControl("WB").SetHTMLDocument("")
  SDB.Objects("holdHTML").RemoveAll
  SDB.Objects("holdHTML").add 0,""
  Top.ChildControl("Rows").Caption=""
  Top.ChildControl("eTime").Caption=""
End sub

Sub SavCurSql(Btn)
  Dim Top,Name,rows,etime,html,sql
  Set Top=Btn.Common.TopParent.Common
  If Trim(Top.ChildControl("sql").Text) = "" Then Exit Sub
  Name  = FixSQL(Top.ChildControl("Saves").Text)
  rows  = Top.ChildControl("Rows").Caption
  If Trim(rows) = "" Then rows = "Null"
  etime = FixSQL(Top.ChildControl("etime").Caption)
  html  = FixSQL(SDB.Objects("holdHTML").item(0))
  sql   = FixSQL(Top.ChildControl("sql").Text)
  DB.ExecSQL("UPDATE TmpSqlSaves SET tmpRows="& rows &", tmpTime="& etime &", tmpHtml="& html &", tmpSql="& sql &" WHERE tmpIDnr||' '||tmpName="& Name)
End sub

Sub Saveas(inBtn)
  Dim Form,result,Btn,i,Edt,SavesDD,Name,rows,etime,html,sql
  If Trim(inBtn.Common.TopParent.Common.ChildControl("sql").Text) = "" Then Exit Sub

  Set Form = SDB.UI.NewForm
  Form.Common.SetRect 0, 0, 250,130
  Form.FormPosition = 4   ' Screen Center
  Form.BorderStyle = 3    ' Dialog
  Form.Caption = "Name Your Save"
  
  Call CreateLbl(Form, 20, 20,70,20,"Give It a Name:","")
  Set Edt = UI.NewEdit(Form)
  Edt.Common.SetRect  20, 40,200,20
  Edt.Common.ControlName = "SaveName"

  Call CreateBtn(Form, 30,80,85,25,SDB.Localize("&Save"),"","","","",1,"Default",True)
  Call CreateBtn(Form,130,80,85,25,SDB.Localize("&Cancel"),"","","","",2,"Cancel",True)
  
  result = Form.ShowModal
  If result = 1 Then
     Set SavesDD = inBtn.Common.TopParent.Common.ChildControl("Saves")
     Name  = FixSQL(Form.Common.ChildControl("SaveName").Text)
     rows  = inBtn.Common.TopParent.Common.ChildControl("Rows").Caption
     if Trim(rows) = "" Then rows = "Null"
     etime = FixSQL(inBtn.Common.TopParent.Common.ChildControl("etime").Caption)
     html  = FixSQL(SDB.Objects("holdHTML").item(0))
     sql   = FixSQL(inBtn.Common.TopParent.Common.ChildControl("sql").Text)
     DB.ExecSQL("INSERT INTO TmpSqlSaves (tmpName, tmpRows, tmpTime, tmpHtml, tmpSql) "&_
                "SELECT "& Name &", "& rows &", "& etime &", "& html &", "& sql)
     Call ClearDropDown(SavesDD)
     Call CreateDropDownItems("Saves")
     Call FillDropDownFromArray(SavesDD, Saves)
     SavesDD.ItemIndex=1
  Else
     Exit Sub
  End If
End sub

Sub DelSave(Btn)
   Dim SavesDD
   Set SavesDD = Btn.Common.TopParent.Common.ChildControl("Saves")
   If SavesDD.ItemIndex = 0 Then Exit Sub
   DB.ExecSQL("DELETE FROM TmpSqlSaves WHERE tmpIDnr||' '||tmpName="& FixSQL(SavesDD.Text))
   SavesDD.DeleteItem(SavesDD.ItemIndex)
   SavesDD.ItemIndex = 0
   Btn.Common.TopParent.Common.ChildControl("sql").Text      = ""
   Btn.Common.TopParent.Common.ChildControl("WB").SetHTMLDocument("")
   SDB.Objects("holdHTML").RemoveAll
   SDB.Objects("holdHTML").add 0,""
   Btn.Common.TopParent.Common.ChildControl("Rows").Caption  = ""
   Btn.Common.TopParent.Common.ChildControl("eTime").Caption = ""
End Sub

Sub ChangeIndSpaces(Spi)
  INI.IntValue("SqlViewer","IndSpaces")=Spi.Value
End Sub

Sub ChangeMaxRows(Spi)
  INI.IntValue("SqlViewer","MaxRows")=Spi.Value
End Sub

Sub OnClickAddTable(Cbx)
  INI.BoolValue("SqlViewer","AddTable")=Cbx.Checked
End Sub

Sub ExecSql(Pnl)
   Dim t(1),etime,sql,HistDD
   Pnl.Common.TopParent.Common.ChildControl("Rows").Caption  = ""
   Pnl.Common.TopParent.Common.ChildControl("eTime").Caption = ""
   Pnl.Common.TopParent.Common.ChildControl("WB").SetHTMLDocument("")
   SDB.Objects("holdHTML").RemoveAll
   SDB.Objects("holdHTML").add 0,""
   sql = Pnl.Common.TopParent.Common.ChildControl("sql").Text
   If sql="" Then Exit Sub
   Set HistDD = Pnl.Common.TopParent.Common.ChildControl("History")
   t(0) = Timer()
   DB.ExecSQL(sql)
   t(1) = Timer()
   etime = FormatNumber(Round(t(1)-t(0),3),3) & " sec"
   DB.ExecSQL("INSERT INTO TmpSqlHist (tmpTime, tmpSql) VALUES("& FixSQL(etime) &", "& FixSQL(sql)&")")
   Pnl.Common.TopParent.Common.ChildControl("eTime").Caption = etime
   Call ClearDropDown(HistDD)
   Call CreateDropDownItems("History")
   Call FillDropDownFromArray(HistDD, History)
   HistDD.ItemIndex=0
   SDB.MessageBox "Statement Successfully Executed in " & etime, mtInformation, Array(mbOK)
End Sub

Sub AddSQLtxt(Btn)
   Dim sqlw,txt
   Set sqlw = Btn.Common.TopParent.Common.ChildControl("sql")
   txt = Trim(Btn.Caption)
   Select Case txt
     Case "="
       sqlw.Text=Ltrim(sqlw.Text) &"="
     Case "Comma"
       sqlw.Text=Ltrim(sqlw.Text) &", "
     Case Else
       sqlw.Text=sqlw.Text & txt &" "
   End Select
End Sub

Sub RemVBS(Btn)
   Dim a,i,sqlw
   Set sqlw = Btn.Common.TopParent.Common.ChildControl("sql")
   a=Split(sqlw.Text,vbcrlf)
   For i=0 to Ubound(a)
      a(i)= DB.OpenSQL("SELECT TRIM("& FixSQL(a(i)) &",'""&_')").StringByIndex(0)
   Next
   sqlw.Text=Join(a,vbcrlf)
End Sub

Sub AddVBS(Btn)
   Dim a,i,sqlw,Spi
   Set Spi  = Btn.Common.TopParent.Common.ChildControl("IndSpaces")
   Set sqlw = Btn.Common.TopParent.Common.ChildControl("sql")
   If Instr(sqlw.Text,"""")>0 Then Exit Sub
   a=Split(sqlw.Text,vbcrlf)
   For i=0 to Ubound(a)
      a(i)= Space(Spi.Value) & """" & a(i)
      If i<Ubound(a) Then
         a(i)= a(i) & " ""&_"
      Else
         a(i)= a(i) & """"
      End If
   Next
   sqlw.Text=Join(a,vbcrlf)
End Sub

Sub AddTable(Btn)
   Dim TablesDD,sqlw,txt
   Set TablesDD = Btn.Common.TopParent.Common.ChildControl("Tables")
   Set sqlw     = Btn.Common.TopParent.Common.ChildControl("sql")
   If TablesDD.ItemIndex = 0 Then Exit Sub
   txt = TablesDD.Text & " "
   sqlw.Text = sqlw.Text & txt
End Sub

Sub AddField(DD)
   Dim TablesDD,Cbx,sqlw,txt
   If DD.ItemIndex = 0 Then Exit Sub
   Set TablesDD = DD.Common.TopParent.Common.ChildControl("Tables")
   Set sqlw     = DD.Common.TopParent.Common.ChildControl("sql")
   Set Cbx      = DD.Common.TopParent.Common.ChildControl("IfTable")
   If Cbx.Checked Then
      txt = TablesDD.Text & "." & DD.Text & " "
   Else
      txt = DD.Text & " "
   End If
   sqlw.Text = sqlw.Text & txt
End Sub

Sub AddFromHist(DD)
   Dim Top,HistRec
   Set Top=DD.Common.TopParent.Common
   Top.ChildControl("Saves").ItemIndex = 0
   Set HistRec = DB.OpenSQL("SELECT * FROM TmpSqlHist WHERE tmpIDnr||' '||tmpSql="& FixSQL(DD.Text))
   Top.ChildControl("Rows").Caption  = HistRec.ValueByName("tmpRows")
   Top.ChildControl("eTime").Caption = HistRec.StringByName("tmpTime")
   Top.ChildControl("sql").Text      = HistRec.StringByName("tmpSql")
   Top.ChildControl("WB").SetHTMLDocument(HistRec.StringByName("tmpHtml"))
   SDB.Objects("holdHTML").RemoveAll
   SDB.Objects("holdHTML").add 0,HistRec.StringByName("tmpHtml")
End Sub

Sub AddFromSave(DD)
   Dim Top,Enabled,SaveRec
   Set Top=DD.Common.TopParent.Common
   Top.ChildControl("History").ItemIndex = 0
   If DD.ItemIndex=0 Then Enabled=False Else Enabled=True
   Top.ChildControl("SaveBtn").Common.Enabled=Enabled
   Top.ChildControl("DelBtn").Common.Enabled=Enabled
   Set SaveRec = DB.OpenSQL("SELECT * FROM TmpSqlSaves WHERE tmpIDnr||' '||tmpName="& FixSQL(DD.Text))
   Top.ChildControl("Rows").Caption  = SaveRec.ValueByName("tmpRows")
   Top.ChildControl("eTime").Caption = SaveRec.StringByName("tmpTime")
   Top.ChildControl("sql").Text      = SaveRec.StringByName("tmpSql")
   Top.ChildControl("WB").SetHTMLDocument(SaveRec.StringByName("tmpHtml"))
   SDB.Objects("holdHTML").RemoveAll
   SDB.Objects("holdHTML").add 0,SaveRec.StringByName("tmpHtml")
End Sub

Sub CreateDropDownItems(What)
  Dim iter,i
  Select Case What
    Case "History"
       Redim History(DB.OpenSQL("SELECT COUNT(*) FROM TmpSqlHist").ValueByIndex(0))
       History(0) = "<History>"
       i=1
       Set iter = DB.OpenSQL("SELECT tmpIDnr||' '||TmpSql FROM TmpSqlHist ORDER BY tmpIDnr DESC LIMIT 200")
       Do While Not iter.EOF
          History(i) = iter.ValueByIndex(0)
          i=i+1
       iter.Next
       Loop
    Case "Saves"
       Redim Saves(DB.OpenSQL("SELECT COUNT(*) FROM TmpSqlSaves").ValueByIndex(0))
       Saves(0) = "<Saves>"
       i=1
       Set iter = DB.OpenSQL("SELECT tmpIDnr||' '||tmpName FROM TmpSqlSaves ORDER BY tmpIDnr DESC")
       Do While Not iter.EOF
          Saves(i) = iter.ValueByIndex(0)
          i=i+1
       iter.Next
       Loop
    Case "Tables"
       Redim Tables(DB.OpenSQL("SELECT Count(*) FROM "&_
                               "(SELECT * FROM sqlite_master UNION ALL "&_
                               " SELECT * FROM sqlite_temp_master) "&_
                               "WHERE type = 'table'").ValueByIndex(0))
       Tables(0) = "<Tables>"
       i=1
       Set iter = DB.OpenSQL("SELECT tbl_name FROM "&_
                             "(SELECT * FROM sqlite_master UNION ALL "&_
                             " SELECT * FROM sqlite_temp_master) "&_
                             "WHERE type = 'table' ORDER BY 1")
       Do While Not iter.EOF
          Tables(i) = iter.ValueByIndex(0)
          i=i+1
       iter.Next
       Loop
    Case "Fields"
       Redim Fields(0)
       Fields(0) = "<Select a Table first>"
    Case "Functions"
       Redim Functions(0)
       Functions(0) = "<Functions>"
    Case "SongData"
       Redim SongData(30)
       SongData(0)  = "<Add data from Selected song>"
       SongData(1)  = "Songs.ID"
       SongData(2)  = "SongTitle"
       SongData(3)  = "ArtistID(s)"
       SongData(4)  = "Artist(s)"
       SongData(5)  = "AlbumID"
       SongData(6)  = "AlbumName"
       SongData(7)  = "AlbumArtistID(s)"
       SongData(8)  = "AlbumArtist(s)"
       SongData(9)  = "GenreID(s)"
       SongData(10) = "Genre(s)"
       SongData(11) = "ComposerID(s)"
       SongData(12) = "Composer(s)"
       SongData(13) = "ConductorID(s)"
       SongData(14) = "Conductor(s)"
       SongData(15) = "LyricistID(s)"
       SongData(16) = "Lyricist(s)"
       SongData(17) = "Rating"
       SongData(18) = "Date"
       SongData(19) = "OriginalYear"
       SongData(20) = "SampleRate"
       SongData(21) = "Bitrate"
       SongData(22) = "Length"
       SongData(23) = "Size"
       SongData(24) = "IDMedia"
       SongData(25) = "ParentFolderID"
       SongData(26) = "IDFolder"
       SongData(27) = "SubFolderID(s)"
       SongData(28) = "DateAdded"
       SongData(29) = "FileModified"
       SongData(30) = "LastPlayed"
  End Select
End Sub

Sub AddSongData(DD)
  Dim list,DB,info,sqlw,txt,SongDataSQL(30)
  Set list = SDB.SelectedSongList
  Set DB   = SDB.Database
  If list.Count=0 Then SDB.MessageBox "Select a song in MM first!", mtInformation, Array(mbOK) : Exit Sub End If
  If DD.ItemIndex = 0 Then Exit Sub
  Set sqlw     = DD.Common.TopParent.Common.ChildControl("sql")
  SongDataSQL(0)  = ""
  SongDataSQL(1)  = list.item(0).ID
  SongDataSQL(2)  = FixSQL(list.item(0).Title)
  SongDataSQL(3)  = "IN (" & DB.OpenSql("SELECT group_concat(IDArtist,', ') FROM ArtistsSongs WHERE PersonType=1 AND IDSong="&list.item(0).ID&" GROUP BY IDSong").StringByIndex(0) & ")"
  SongDataSQL(4)  = FixSQL(list.item(0).ArtistName)
  SongDataSQL(5)  = list.item(0).Album.ID
  SongDataSQL(6)  = FixSQL(list.item(0).AlbumName)
  SongDataSQL(7)  = "IN (" & DB.OpenSql("SELECT group_concat(IDArtist,', ') FROM ArtistsAlbums WHERE IDAlbum="&list.item(0).Album.ID&" GROUP BY IDAlbum").StringByIndex(0) & ")"
  SongDataSQL(8)  = FixSQL(list.item(0).AlbumArtistName)
  SongDataSQL(9)  = "IN (" & DB.OpenSql("SELECT group_concat(IDGenre,', ') FROM GenresSongs WHERE IDSong="&list.item(0).ID&" GROUP BY IDSong").StringByIndex(0) & ")"
  SongDataSQL(10) = FixSQL(list.item(0).Genre)
  SongDataSQL(11) = "IN (" & DB.OpenSql("SELECT group_concat(IDArtist,', ') FROM ArtistsSongs WHERE PersonType=3 AND IDSong="&list.item(0).ID&" GROUP BY IDSong").StringByIndex(0) & ")"
  SongDataSQL(12) = FixSQL(list.item(0).Author)
  SongDataSQL(13) = "IN (" & DB.OpenSql("SELECT group_concat(IDArtist,', ') FROM ArtistsSongs WHERE PersonType=4 AND IDSong="&list.item(0).ID&" GROUP BY IDSong").StringByIndex(0) & ")"
  SongDataSQL(14) = FixSQL(list.item(0).Conductor)
  SongDataSQL(15) = "IN (" & DB.OpenSql("SELECT group_concat(IDArtist,', ') FROM ArtistsSongs WHERE PersonType=5 AND IDSong="&list.item(0).ID&" GROUP BY IDSong").StringByIndex(0) & ")"
  SongDataSQL(16) = FixSQL(list.item(0).Lyricist)
  SongDataSQL(17) = list.item(0).Rating
  SongDataSQL(18) = DB.OpenSql("SELECT Year FROM Songs WHERE ID="&list.item(0).ID).StringByIndex(0)
  SongDataSQL(19) = DB.OpenSql("SELECT OrigYear FROM Songs WHERE ID="&list.item(0).ID).StringByIndex(0)
  SongDataSQL(20) = list.item(0).SampleRate
  SongDataSQL(21) = list.item(0).Bitrate
  SongDataSQL(22) = list.item(0).SongLength
  SongDataSQL(23) = list.item(0).FileLength
  SongDataSQL(24) = list.item(0).Media.ID
  SongDataSQL(25) = DB.OpenSql("SELECT IDParentFolder FROM Folders WHERE ID IN (SELECT IDFolder FROM Songs WHERE ID="&list.item(0).ID &")").StringByIndex(0)
  SongDataSQL(26) = DB.OpenSql("SELECT IDFolder FROM Songs WHERE ID="&list.item(0).ID).StringByIndex(0)
  SongDataSQL(27) = "IN (" & DB.OpenSql("SELECT group_concat(ID,', ') FROM Folders WHERE IDParentFolder IN (SELECT IDFolder FROM Songs WHERE ID="&list.item(0).ID &")").StringByIndex(0) & ")"
  SongDataSQL(28) = DB.OpenSql("SELECT DateAdded FROM Songs WHERE ID="&list.item(0).ID).StringByIndex(0)
  SongDataSQL(29) = DB.OpenSql("SELECT FileModified FROM Songs WHERE ID="&list.item(0).ID).StringByIndex(0)
  SongDataSQL(30) = DB.OpenSql("SELECT LastTimePlayed FROM Songs WHERE ID="&list.item(0).ID).StringByIndex(0)
   
  txt = SongDataSQL(DD.ItemIndex) & " "
  sqlw.Text = sqlw.Text & txt
End Sub

Function FixSQL(SQLString)
  FixSQL= "'" & Replace(SQLString,"'","''") & "'"
End Function

Sub PopuFields(Ctrl)
   Dim table,i,fieldSql,a,DropDown
   On Error Resume Next
   Set DropDown = Ctrl.Common.TopParent.Common.ChildControl("Fields")
   Call ClearDropDown(DropDown)
   If Ctrl.ItemIndex=0 Then
      DropDown.AddItem "<Select a Table first>"
      DropDown.ItemIndex=0
      Exit Sub
   End If
   
   table = Ctrl.Text
   
   fieldSql = DB.OpenSQL("SELECT sql FROM "&_
                         "(SELECT * FROM sqlite_master UNION ALL "&_
                         " SELECT * FROM sqlite_temp_master) "&_
                         "WHERE type = 'table' AND tbl_name='"& table &"'").StringByIndex(0)
   fieldSql = Mid(fieldSql,Instr(fieldSql,"(")+1)
   a = Split(fieldSql,",")
   Redim Fields(Ubound(a)+1)
   Fields(0)="<Fields In " & Table & ">"
   For i=0 To Ubound(a)
      Fields(i+1) = Mid(Trim(a(i)),1,Instr(Trim(a(i))," ")-1)
   Next
   
   Call FillDropDownFromArray(DropDown, Fields)
   DropDown.ItemIndex = 0
End Sub

Sub RunQuery(Pnl)
   Dim t(1),sql,Header,i,j,n,bar,HTML2,MaxRows,WB,HistDD,iter,HTML,Rows,eTime
   Dim RemStart,RemEnd,remov,com,repl1,repl2
   
   sql = Pnl.Common.TopParent.Common.ChildControl("sql").Text
   If sql="" Then Exit Sub
   
   Set HistDD = Pnl.Common.TopParent.Common.ChildControl("History")
   Set WB     = Pnl.Common.TopParent.Common.ChildControl("WB")
   Set Rows   = Pnl.Common.TopParent.Common.ChildControl("Rows")
   Set eTime  = Pnl.Common.TopParent.Common.ChildControl("eTime")
   MaxRows = Pnl.Common.TopParent.Common.ChildControl("MaxRows").Value
   INI.IntValue("SqlViewer","MaxRows") = MaxRows
   Call ClearDropDown(HistDD)

   i=0
   Set Bar = SDB.Progress
   Bar.Text = "Running SQL-statement..."
   t(0) = Timer()
   Set iter = DB.OpenSQL(sql)
   Set Header = iter.FieldNames
   Do While Not iter.EOF
      i=i+1
      If i <= MaxRows Then 
         HTML2 = HTML2 & "      <tr" & Style() & ">" & vbcrlf
         If i < 10 then n= "0" & i Else n=i
         HTML2 = HTML2 & "<td>" & MapField(n) & "</td>" & vbcrlf
         For j = 0 To Header.Count-1
            HTML2 = HTML2 & "<td>" & MapField(iter.StringByIndex(j)) & "</td>" & vbcrlf
         Next
         HTML2 = HTML2 & "      </tr>" & vbcrlf
      End If
      iter.Next
   Loop
   t(1) = Timer()
   Set iter = Nothing
   Rows.Caption  = i
   eTime.Caption = FormatNumber(Round(t(1)-t(0),3),3) & " sec"

  HTML = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">" & vbcrlf
  HTML = HTML & "<html>" & vbcrlf
  HTML = HTML & "  <head>" & vbcrlf
  HTML = HTML & "    <title>" & "SQL Viewer" & "</title>" & vbcrlf
  HTML = HTML & "  </head>" & vbcrlf

  HTML = HTML & "<STYLE TYPE=text/css>" & vbcrlf
  HTML = HTML & "body{font-family:'Verdana',sans-serif; background-color:#FFFFFF; font-size:9pt; color:#000000;}" & vbcrlf
  HTML = HTML & "H1{font-family:'Verdana',sans-serif; font-size:13pt; font-weight:bold; color:#AAAAAA; text-align:left}" & vbcrlf
  HTML = HTML & "P{font-family:'Verdana',sans-serif; font-size:8pt; color:#000000;}" & vbcrlf
  HTML = HTML & "TH{font-family:'Verdana',sans-serif; font-size:9pt; font-weight:bold; color:#000000; border-color:#000000; border-style: solid; border-left-width:0px; border-right-width:0px; border-top-width:0px; border-bottom-width:3px;}" & vbcrlf
  HTML = HTML & "TD{font-family:'Verdana',sans-serif; font-size:8pt; color:#000000; border-color:#000000; border-style: solid; border-left-width:0px; border-right-width:0px; border-top-width:0px; border-bottom-width:1px;}" & vbcrlf
  HTML = HTML & "TD.highlight{font-family:'Verdana',sans-serif; font-size:8pt; background-color:#FFFF77; color:#000000; border-color:#000000; border-style: solid; border-left-width:0px; border-right-width:0px; border-top-width:0px; border-bottom-width:1px;}" & vbcrlf
  HTML = HTML & "TR.dark{background-color:#EEEEEE}" & VbCrLf
  HTML = HTML & "TR.aleft TH{text-align:left}" & vbcrlf
  HTML = HTML & "</STYLE>" & vbcrlf

  HTML = HTML & "  <body>" & vbcrlf
  'HTML = HTML & "    <H1>" & sql & "</H1>" & vbcrlf
  HTML = HTML & "    <table border=""0"" cellspacing=""0"" cellpadding=""4"" width=""100%"">" & vbcrlf
  HTML = HTML & "      <tr class=""aleft"">" & vbcrlf

  HTML = HTML & "        <th>" & "#" & "</th>" & vbcrlf
   For j = 0 To Header.Count-1
      HTML = HTML & "        <th>" & Header.item(j) & "</th>" & vbcrlf 
   Next
  HTML = HTML & "      </tr>" & vbcrlf
  HTML = HTML & HTML2   
  HTML = HTML & "    </table>" & vbcrlf
  HTML = HTML & "  </body>" & vbcrlf
  HTML = HTML & "</html>" & vbcrlf
  WB.SetHTMLDocument( HTML)
  SDB.Objects("holdHTML").RemoveAll
  SDB.Objects("holdHTML").add 0,HTML

   DB.ExecSQL("INSERT INTO TmpSqlHist (tmpRows, tmpTime, tmpHtml, tmpSql) "&_
              "VALUES("& Rows.Caption &", '"& eTime.Caption &"', "& FixSQL(HTML) &", "& FixSQL(sql) &")")
   Call CreateDropDownItems("History")
   Call FillDropDownFromArray(HistDD, History)
   HistDD.ItemIndex=0
   If Not bar Is Nothing Then Set bar = Nothing
End Sub

Sub OnClose(Btn)
   Set SDB.Objects("SQLThingy") = Nothing
   Set SDB.Objects("holdHTML") = Nothing
   DB.ExecSQL("DELETE FROM TmpSqlHist WHERE tmpIDnr NOT IN (SELECT tmpIDnr FROM TmpSqlHist ORDER BY tmpIDnr DESC LIMIT 200)")
End Sub

Function Style()
  styleOn = Not styleOn
  If styleOn Then
    Style = ""
  Else
    Style = " class=""Dark"""
  End If
End Function

Function MapXML(original)
  Dim hold
  hold = Replace(original, "&", "&")
  hold = Replace(hold, "  ", "&nbsp; ")
  hold = Replace(hold, "<", "<")
  hold = Replace(hold, ">", ">")
  hold = Replace(hold, """", """)
  Dim i
  i=1
  While i<=Len(hold)
    If (AscW(Mid(hold, i, 1))>127) Then
      hold = Mid(hold, 1, i-1)+"&#"+CStr(AscW(Mid(hold, i, 1)))+";"+Mid(hold, i+1)
    End If
    i=i+1
  WEnd
  MapXML = hold
End Function

Function MapField(fld)
  If fld="" Then
    MapField = "&nbsp;"
  Else
    MapField = MapXML(fld)
  End If
End Function

Function CreateLbl(Owner,X,Y,W,H,Caption,ControlName)
    Set CreateLbl = UI.NewLabel(Owner)
    CreateLbl.Common.ControlName = ControlName
    CreateLbl.Common.SetRect X,Y,W,H 
    CreateLbl.Caption = Caption
End Function

Function CreateBtn(Owner,X,Y,W,H,Caption,ControlName,OnClickFunc,Anchors,Hint,ModalResult,DefaultCancel,Enabled)
    Set CreateBtn = UI.NewButton(Owner)
    CreateBtn.Common.SetRect X,Y,W,H
    CreateBtn.Common.ControlName = ControlName
    CreateBtn.Caption = Caption
    If not Anchors="" Then CreateBtn.Common.Anchors = Anchors
    CreateBtn.UseScript = SDB.ApplicationPath & "Scripts\SqlViewer.vbs"
    CreateBtn.OnClickFunc = OnClickFunc
    CreateBtn.Common.Hint = Hint
    If ModalResult<>"" Then CreateBtn.ModalResult = ModalResult
    If DefaultCancel<>"" Then
      If DefaultCancel="Default" Then CreateBtn.Default = True
      If DefaultCancel="Cancel"  Then CreateBtn.Cancel  = True
    End If
    CreateBtn.Common.Enabled = Enabled
End Function

Function CreateCbx(Owner,X,Y,W,H,Caption,ControlName,Hint,Checked,OnClickName)
    Set CreateCbx = UI.NewCheckBox(Owner)
    CreateCbx.Common.ControlName = ControlName
    CreateCbx.Caption = Caption
    CreateCbx.Common.SetRect X,Y,W,H
    CreateCbx.Common.Hint = Hint
    CreateCbx.Checked = Checked
    If Not OnClickName="" Then Script.RegisterEvent CreateCbx.Common, "OnClick", OnClickName
End Function

Function CreateDrd(Owner,X,Y,W,H,ControlName,Anchors,ItemIndex,What,SourceArray,OnSelectFunc,Hint)
  Set CreateDrd = UI.NewDropDown(Owner)
  CreateDrd.Common.SetRect X,Y,W,H
  CreateDrd.Common.ControlName = ControlName
  CreateDropDownItems What
  FillDropDownFromArray CreateDrd.Common.ChildControl(ControlName), SourceArray
  CreateDrd.ItemIndex = ItemIndex
  If Not Anchors="" Then CreateDrd.Common.Anchors = Anchors
  CreateDrd.Style = 2
  CreateDrd.UseScript = SDB.ApplicationPath & "Scripts\SqlViewer.vbs"
  CreateDrd.OnSelectFunc = OnSelectFunc
  CreateDrd.Common.Hint = Hint
End Function

Sub FillDropDownFromArray(DropDown, SourceArray)
  Dim i
  For i=0 To UBound(SourceArray)
    DropDown.AddItem SourceArray(i)
  Next
End Sub

Sub ClearDropDown(DropDown)
   Dim i
   For i=DropDown.ItemCount-1 To 0 Step -1 
      DropDown.DeleteItem i
   Next
End Sub

Sub Install()
   'Add scripts.ini entries
   Dim inip : inip = SDB.ApplicationPath&"Scripts\Scripts.ini"
   Dim inif : Set inif = SDB.Tools.IniFileByPath(inip)
   If Not (inif Is Nothing) Then   
   	  inif.StringValue("SqlViewer","Filename") = "SqlViewer.vbs"
      inif.StringValue("SqlViewer","Procname") = "SqlViewer"
      inif.StringValue("SqlViewer","Order") = "100"
      inif.StringValue("SqlViewer","DisplayName") = "SQL-Viewer"
      inif.StringValue("SqlViewer","Description") = "Run SQL Statement Within MM Text"
      inif.StringValue("SqlViewer","Language") = "VBScript"
      inif.StringValue("SqlViewer","ScriptType") = "0"
      inif.StringValue("SqlViewer","Shortcut") = "CTRL+F9" 
      SDB.RefreshScriptItems
   End If
   'Add entries to Mediamonkey.ini
   If INI.StringValue("SqlViewer","MaxRows")   = "" Then INI.IntValue("SqlViewer","MaxRows") = 50
   If INI.StringValue("SqlViewer","IndSpaces") = "" Then INI.IntValue("SqlViewer","MaxRows") = 15
   If INI.StringValue("SqlViewer","AddTable")  = "" Then INI.BoolValue("SqlViewer","AddTable") = False
End Sub

___________________________________________________________
Updates:
Ver 2.4 (2009-10-25) [MM3 only]
- Added a Clear All Button
- Added a Save As Button and fixed the Save button so it works properly
- Fixed a random "This key is already associated bla, bla" bug

Ver 2.3 (200-05-15) [MM3 only]
These are added to the DropDown which adds data to the SQL-Window from the selected song in MM:
- Added ParentFolderID
- Added FolderID
- Added SubFolderID(s)

Ver 2.2 (2008-05-08 )
- Added DropDown which adds data to the SQL-Window from the selected song in MM

Ver 2.1.1 (2008-05-01)
- Fixed some layout issues (thanks to tekno)
- Improved RemVBS function

Ver 2.1 (2008-04-30)
- Change the window to a floating window
- Enlarged the <History> and <Saves> dropdowns dramatically (They were annoyingly short.)
- Changed numbers of saved history queries to 200
- Fixed so "history saves" older than last 200 are automatically deleted
- Added more buttons, [*], [COUNT(*)], [ORDER BY]

Ver 2.0.1 (2008-01-24) [MM3 only]
- Added RemVBS buttons which fixes the statement if it's copied from a VBS script
- Added AddVBS which does the opposite
- Remove The SQL-Statement from the result window
- Fixed a bug in DropDown Fileds (not all fields where displayed)

Major update

Ver 2.0 (2008-01-22)
- Added a window in the window where SQL statement is entered (No more file workaround)
- Added possibility to run DDL/DML statements (SDB.ExecSql) Be Careful!!!
- Added new Fix so * can be used after SELECT
- Added ran statements are saved (History)
- Added possibility to save Runs/Templates (Saves)
- Added DropDown With MM's all Tables
- Added DropDown With all Fields from selected Table
- Added various Buttons which adds SQL-keywords to the window
- Tuned the VBScript code so the SQL's are processed much faster

---------------------------------------------------------------------------------
Download:
Latest version:
MM3 (Installer)
Note! You must have MM 3.0.2.1131 are later to use this script!
SQL-Viewer 2.4

Previous version (in case a recent script update by me is malfunctioning):
SQL-Viewer 2.3

Installation Instructions:
- New Install or upgrade:
MM3 (Installer)
Avoid "Product installation error"
- Vista Users:
- - To be able to install scripts you must Run MM as an administrator.
- - It means that you must right click the MM icon and select "Run as administrator" even if you are logged in as an administrator.
- All Users:
- - Check in your task manager that you only have one instance of MediaMonkey.exe running.
1. Download the zipped mmip file and double click on it.
2. Restart MediaMonkey (If needed)

- Upgrade:
Just install the newer version over the old one. Restart MM.

RedX · Post by **RedX** » Sun Jan 13, 2008 8:28 pm

GODLIKE!!

thanks for this one bex!

No more annoyances when trying out SQL syntaxes

trixmoto · Post by **trixmoto** » Mon Jan 14, 2008 4:12 am

Wow, this should be a MASSIVE help, thanks!

Bex · Post by **Bex** » Mon Jan 14, 2008 5:00 am

Thanks guys!

New version
Ver 1.1 (2008-01-14)
- Fixed bug when using functions containing comma in SELECT
- Fixed bug with last field header wasn't displayed sometimes

Enjoy!
/Bex

trixmoto · Post by **trixmoto** » Mon Jan 14, 2008 5:11 am

Bex wrote:You cant use * in the SELECT clause so you must write each and every field you want to display with their name (and optionally alias if you want).

Is this something that you are working on, or will this never work?

Bex · Post by **Bex** » Mon Jan 14, 2008 5:18 am

No, sorry but I have no clue how I can get the field names from various tables, inlines aso. Well I have a clue but it's very hard to accomplish.

trixmoto · Post by **trixmoto** » Mon Jan 14, 2008 5:36 am

It wouldn't be that hard in SQLite, just use the masters table...

Code: Select all

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'Artists'

This returns the create command used to create the table, from which you can extract the column names.

And I used your script to test that as well - great script!

Bex · Post by **Bex** » Mon Jan 14, 2008 6:04 am

Yeah it's cool isn't it?

I actually knew that but what todo if you have a statement like this:

Code: Select all

select * from
table3, (Select table1.field1, table1.field2
 from table1,table2
 where field1>0 and table1.id=table2.id
 group by table1.field1, table1.field2
 having count(*)>1) inline1
where table3.field1=inline1.field1

Perhaps there is a way to get all columns returned of a statement from SQLite directly?
Something like:

Code: Select all

select all_columns from ("sql-statement")

But i don't know how todo that. But I wonder how various SQL-tools do it?

trixmoto · Post by **trixmoto** » Mon Jan 14, 2008 7:25 am

I guess you'd have to query the master table to replace the "*" and then add "field1" and "field2". I agree this could get quite complex!

Bex · Post by **Bex** » Mon Jan 14, 2008 1:13 pm

I found a way to always get the count of columns returned from a sql-statement. (Use Explain before the statement and look after Callback. But do it in sqlite spy.)
It seems to always work. It's a big step forward since then there won't be anymore critical error when the script sometimes misses a column.

But how to get the actual column names returned from an sql statement doesn't seems to be possible. At least I don't know how to do it...

Teknojnky · Post by **Teknojnky** » Mon Jan 14, 2008 1:54 pm

This would be even cooler if there was a dialog box or form with something similar to the 'auto-organize' mask dialogs where you just click on common fields and statements to add them to the query. A Query Builder type thing so to speak. (tho I am sure that might add quite a bit of complexity you may not wish to code)

A suggestion on the name, 'Power Query'.

Bex · Post by **Bex** » Mon Jan 14, 2008 2:02 pm

Yes, I agree that it would be cool but it's simply to complex for me to code. And there are already such tools available on the market. The main advantage with this script is that you can query MM's "internal sql-engine", in respect of collation and some functions, which you can't with external tools.

Teknojnky · Post by **Teknojnky** » Mon Jan 14, 2008 2:07 pm

What about just a simple dialog/form with a input box with history (something like the search toolbar), this would avoid having to edit the text file (unless there is some reason this is more desirable?).

Theoretically you could have the edit box saved to the text file, or even use the text file to store the history of previously used queries.

Bex · Post by **Bex** » Mon Jan 14, 2008 2:22 pm

Well, there are already programmes which can do all that, perhaps not in a MM specific way, but using those it's just a matter of copy/paste the code into the Statement1.sql file and run the query through this script.

Bex · Post by **Bex** » Mon Jan 14, 2008 2:35 pm

Now I see what you mean.

The easiest way, for now, to alter between different SQL-statements is to write all statements in the file and comment out the ones you don't won't to run. Like this:

Code: Select all

/*
This
is 
ignored 
due 
to 
it's 
between /* and  */
*/
-- This is also ignored but only this line

SQL-Viewer 2.4 [Script] 2009-10-25

SQL-Viewer 2.4 [Script] 2009-10-25

Re: SQL-Viewer 1.1 [Script] 2008-01-14 [MM3]