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

Download and get help for different MediaMonkey for Windows 4 Addons.

Moderators: Peke, Gurus

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Script is updated
Ver 2.0.1 (2008-01-24) [MM3 only]

What's new?
- 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)

Rem/Add VBS buttons assumes that you use this "syntax" in VBS:

Code: Select all

           "SELECT blabla, bla, bla "&_
           "FROM SomeTable "&_
           "WHERE Field1>28"
Check it out and let me know what you think!


Enjoy!
/Bex
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Mr Spouse
Posts: 3
Joined: Fri Jan 11, 2008 7:33 am

Post by Mr Spouse »

Thank you, thank you! :lol:

At last I can get rid of those irritating 'dead' podcast links cluttering up my subscriptions folders and save having to download 'missing' files more than once.

I've only written a few SQL queries so far but it should be possible to convert them to vbs given time!

My efforts are here: http://www.mediamonkey.com/forum/viewtopic.php?t=25378
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Thanks!

Yes, it's a useful tool which can be used to many things. You need to know your SQL though and be a bit careful.
Glad you found a way to fix the podcast problems!
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
oldtimer
Posts: 146
Joined: Wed May 02, 2007 5:49 pm

Post by oldtimer »

About making select statements easily.

What you might want to do is export the table structures to another SQL. I work with Oracle there are a vast quantity of query tools many are shareware. The nice thing about SQL is most of the commands are ANSI SQL which is supported by all SQL languages. You would use a query tool to query an empty structure then cut an paste the statement back into your editor. Some of the paid for tools are pretty smart!

However, I think 'describe' is an ANSI command. If you describe a table it should dump all the column names along with other attributes like type and length. You can chop out the names. It helps if your editor can column cut and paste.

Maybe a MM developer/tester could provide equivelents to select * for the tables with lots of columns. They might have tools to do that for them. Then it is a matter of cut an paste or commenting out unwanted columns. I know I have a bunch of big join statements at work that were made with a tool in a big text file. Then it is faster to use the statements and modify them than to use a tool.

An ERD would be GREAT as well and I am sure that have that stuff.
MarineBrat
Posts: 490
Joined: Tue Jun 14, 2005 12:12 am
Location: Loony left coast, USA.

Post by MarineBrat »

Bex wrote:MarineBrat,
Songs is the biggest table in MM so it would take some time to open it. Especially if you have "only display rows" set to a high value.

How big is it?
How long time does it take?
How many rows do you display?
It's working fine now, and I'm not sure why it's different.

My database has about 28k songs. It was taking longer than a couple of minutes, which is as long as I was willing to wait. I had the display set to 500 rows.

When I run it tonight it takes about 1 second to display 500 rows.

Arg, I take it back. It's locking up again after I run the query a 2nd or 3rd time in a row. The first time it comes up immediatly, and when run a 2nd time it locks up. I know it's locked because the song in the background starts looping at the end.
oldtimer
Posts: 146
Joined: Wed May 02, 2007 5:49 pm

Post by oldtimer »

How are you opening the table? I am brand spanking new to SQL lite but I am a developer who uses Oracle and sometimes Sybase back ends for over 10 years. Once I get pointed in the right direction I may become an asset.

Thanks for your patience!
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

@MarineBrat,
Yes it's a bit slow. I'll see what I can do. In the mean time, display only 10 rows or similar.

@oldtimer,
I'm not sure what you are trying to say?
Btw, SELECT * works in the latest version.

Open a table?
Select * from TableName? But you knew that so what do you mean?

Regarding ERD, see the wiki: (Not complete yet.)
http://www.mediamonkey.com/wiki/index.p ... _structure
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
oldtimer
Posts: 146
Joined: Wed May 02, 2007 5:49 pm

Post by oldtimer »

You have to be somewhere to be able to type select. I do not know how to get there. We used to call it the prompt.

The structure info for MM 2 was good. I can quess the MM3 structure is about the same. I was pleased to see the playlist info is in the database. I hope to be able to make some kind of report as to how many time a tune has been put on a play list.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Ahh, I see. You need to install the latest script found in the first post (which I recommend you to read carefully).

The playlist thing wouldn't be a problem for you. Let us know how it goes.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
peter_h
Posts: 118
Joined: Mon Mar 12, 2007 4:38 pm

Post by peter_h »

I've made an enquiry with the "SQL Maestro" developers, as to the possibility of using their product with MM3's IUNICODE-collated database.

Vadim has written back and asked for a database sample and a collation description.

If you're interested, please see this post for more details:
http://www.mediamonkey.com/forum/viewtopic.php?t=25637
:) Pete, from Wellington, New Zealand.

Running MM4 4.1.31.1919, on Win7 SP1, 16Gig memory, on Lenovo W530 (Intel i7) laptop
**If you're wondering why I'm still on MM4: It still has more useful-to-me plugins; and I prefer the GUI's responsiveness, panes spreadable over multi-monitors flexibility, and predictability (all coz it's more Windows-native). I also hate "flat design" for its visual inefficiency. For me, MM4's benefits are still > MM5, and MM5's downsides < MM4.
Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

Post by Teknojnky »

Bex is it possible to escape out of the query and use an sdb object?

like

Select Songs.Artist
From Songs
Where Songs.Artist = '" & sdb.player.currentsong.artistname & "'
Order by lasttimeplayed desc

which does not give any error, but doesn't display any result


---------------------

edit: I also had an idea that might help break out of bad SQL or other long loops..

Add a timer check in the loop which runs the user SQL and if it passes whatever time (say 60 seconds?) exit the loop.

On my timer loops I've been trying to remember to use

Dim TimeOut : TimeOut = 25
Dim StartTimer : StartTimer = Time

If Int(Timer-StartTimer) > TimeOut Then
Exit whatevertype of loop it is
End If

or whatever similar that applies to the loop in question

if you have a statusbar.terminate you can just make it an OR with the timeout check above.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

1. Yes, that would be very handy but I'm afraid it's not possible.
" & sdb.player.currentsong.artistname & " needs to be compiled in the script when the script is loaded into MM. So now it's only treated as text. Set one Artist to " & sdb.player.currentsong.artistname & " and the SQL will find it.

2. No loop is used when the actual SQL is processed in the database. So if you have a very slow SQL you can't do anything until the database has finished the statement. Just as in any SQL tool.
But when the database is done then I use a loop to build the HTML. I could change the progressbar to indicate that and give the possibility to break it and/or have a time out. We'll see what happens.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Teknojnky
Posts: 5537
Joined: Tue Sep 06, 2005 11:01 pm
Contact:

Post by Teknojnky »

Another issue, I duno if its script or a general MM problem... but if you close MM while the sql viewer window is open, mm crashes with access errors etc.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

It's probably a script issue. I'll have a look.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
fridge
Posts: 75
Joined: Sat Jan 19, 2008 1:21 pm
Location: Vienna, Austria
Contact:

Post by fridge »

Thanks for this great script! I was finally making my external drive available by wlan and having troubles changing the SongPath to the UNC-location. When I tried to do that using SQLiteSpy I got the familiar IUnicode error. Then your script resolved all the problems.

Thanks Bex, you absolutely made my day!

This has probably been posted before but here's how I did it:

1. add one song from the UNC-Location to the MM-archive (to get the IDMedia)

2. execute

Code: Select all

update Songs set SongPath = replace(SongPath, ":\", "\\192.168.1.2\hdd_1_1_1\Musik\") WHERE IDMedia = "45"
in SQL-Viewer where ":\" is the string to be replaced with the new UNC-location "\\192.168.1.2\hdd_1_1_1\Musik\" and "45" is the IDMedia of the (old) external drive

3. execute

Code: Select all

update Songs set IDMedia = "42" WHERE IDMedia = "45"
where "42" is the IDMedia of the network storage

that's it ... works like clockwork :D
WebNodes: adds customised WebNodes to your monkey
Classification and Genre Changer: easily edit the currently playing song's Classification and Genre
fridge's MM-Scripts
Post Reply