Page 4 of 17
Posted: Thu Jan 24, 2008 4:50 pm
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
Posted: Thu Jan 24, 2008 6:41 pm
by Mr Spouse
Thank you, thank you!
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
Posted: Thu Jan 24, 2008 6:48 pm
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!
Posted: Sat Jan 26, 2008 12:04 am
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.
Posted: Sat Jan 26, 2008 1:19 am
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.
Posted: Sat Jan 26, 2008 10:42 am
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!
Posted: Sat Jan 26, 2008 3:55 pm
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
Posted: Mon Jan 28, 2008 9:04 pm
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.
Posted: Tue Jan 29, 2008 11:34 am
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.
Posted: Wed Jan 30, 2008 7:56 am
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
Posted: Fri Feb 01, 2008 7:01 pm
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.
Posted: Sat Feb 02, 2008 4:44 am
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.
Posted: Tue Feb 05, 2008 3:52 am
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.
Posted: Tue Feb 05, 2008 2:37 pm
by Bex
It's probably a script issue. I'll have a look.
Posted: Tue Feb 05, 2008 4:04 pm
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
