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

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Postby Bex » Thu Jan 24, 2008 4:50 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby Mr Spouse » Thu Jan 24, 2008 6:41 pm

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
Mr Spouse
 
Posts: 3
Joined: Fri Jan 11, 2008 7:33 am

Postby Bex » Thu Jan 24, 2008 6:48 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby oldtimer » Sat Jan 26, 2008 12:04 am

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.
oldtimer
 
Posts: 140
Joined: Wed May 02, 2007 5:49 pm

Postby MarineBrat » Sat Jan 26, 2008 1:19 am

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.
MarineBrat
 
Posts: 485
Joined: Tue Jun 14, 2005 12:12 am
Location: Loony left coast, USA.

Postby oldtimer » Sat Jan 26, 2008 10:42 am

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!
oldtimer
 
Posts: 140
Joined: Wed May 02, 2007 5:49 pm

Postby Bex » Sat Jan 26, 2008 3:55 pm

@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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby oldtimer » Mon Jan 28, 2008 9:04 pm

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.
oldtimer
 
Posts: 140
Joined: Wed May 02, 2007 5:49 pm

Postby Bex » Tue Jan 29, 2008 11:34 am

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby peter_h » Wed Jan 30, 2008 7:56 am

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 WinXP sp3, 2Gig memory, nVidia 8600GT video.
peter_h
 
Posts: 112
Joined: Mon Mar 12, 2007 4:38 pm

Postby Teknojnky » Fri Feb 01, 2008 7:01 pm

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.
Teknojnky
 
Posts: 5509
Joined: Tue Sep 06, 2005 11:01 pm

Postby Bex » Sat Feb 02, 2008 4:44 am

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby Teknojnky » Tue Feb 05, 2008 3:52 am

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.
Teknojnky
 
Posts: 5509
Joined: Tue Sep 06, 2005 11:01 pm

Postby Bex » Tue Feb 05, 2008 2:37 pm

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
Bex
 
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Postby fridge » Tue Feb 05, 2008 4:04 pm

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
fridge
 
Posts: 75
Joined: Sat Jan 19, 2008 1:21 pm
Location: Vienna, Austria

PreviousNext

Return to Need Help with Addons?

Who is online

Users browsing this forum: Alexa [Bot] and 6 guests