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

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

I haven't got time to look into this yet. But try to reload all scripts instead of just SQL-Viewer, it works for me.
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
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

Bex wrote:I haven't got time to look into this yet. But try to reload all scripts instead of just SQL-Viewer, it works for me.
Will try.
No big deal. Just posted the info from my testing so you would have it when and if. :D
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

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

Post by Bex »

Script is updated:
Ver 2.3 (2009-10-25)
- 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


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
nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

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

Post by nynaevelan »

I get a "file not found" error when clicking on the link. :(

Never mind it was a browser issue. :oops:
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States

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

Post by Mizery_Made »

Something I've wondered at times about this script, is if a "Save/Save As" could be offered for the Select Statement outputs. Unless I'm missing something.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

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

Post by Bex »

I'll put it on my todo-list! But it will take some time I think...
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
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

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

Post by Owyn »

Bex wrote:Script is updated:
Ver 2.3 (2009-10-25)
- 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


Let me know what you think!

Enjoy!
/Bex
Looks good.
One minor thing (was in prior release as well). Save As dialog box needs to be a bit bigger vertically.
Image
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

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

Post by Bex »

Thanks, I'll get it sorted in the next version!
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
JJP

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

Post by JJP »

Hello Bex (hope I got that right)

Great solution you created here, I too got deeply ****ed off at not being able to use SQLLite to edit any database entry affected by this really dumb MM database collation sequence. I tried your solution and it worked as advertised first time.

My reason for using your utility was to update some of the sort orders that get applied in the "Lists" table behind MM. Except for very occasional reasons, why anybody would find an unsorted list useful is beyond most users (by the way, I worked in the software industry for over 10 years WITH end-users). MM is a great program, but the developers could help by making a few things a little less funky.

So my tip is that if the user wants to execute multiple statements in one foul-swoop, they should just terminate each statement with a semi-colon (;)

update Lists set SortOrder = 0 where IDListType = 2 and ID = 28;
update Lists set SortOrder = 1 where IDListType = 2 and ID = 11;
update Lists set SortOrder = 2 where IDListType = 2 and ID = 25;
etc.

I also have a separate suggestion. I'm not a SQLLite expert, but it's possible that the extra lines added below and shown in blue could make executing DML a bit safer - i.e. using transactions.

Sub ExecSql(Pnl)
.....
DB.BeginTransaction
DB.ExecSQL(sql)
DB.Commit
.....
End Sub

I guess it depends how MM is wrapping all this up. If MM weren't in front of the SQLLite database, as it has to be because of the custom collation sequence, then I would definitely use the "BeginTransaction"...."Commit" wrapped around my SQL, just in case a user does something really stupid. Even an experienced user can make a type or not pay attention to what their doing. :-)

Anyway, thanks once again for your effort and sharing. Live long and prosper.

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

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

Post by Bex »

Thanks!

I have a new fancy version of this script laying around which actually can do multiple statements in one go. We'll see when I get the time to sort out some outstanding issues left to fix before I can release it.

To sort the Classifications lists in the gui, you can simply do so by dragging the nodes on top of each other.

BeginTransaction and Commit isn't really necessary as far as safety is concerned since I'm using MM's API to perform the queries which take care of that, but it has another advantage and that is increased speed, when performing multiple statements in one go. I guess I could add that but I'm not sure how important it is?
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
bourbe
Posts: 7
Joined: Wed Oct 20, 2010 3:08 pm

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

Post by bourbe »

Hello Bex,

I already thank you for this script which is rather useful. I am a user of MM and I recently wanted to make modifications on the DataBase. While wanting to make simple query of update on the DataBase

UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “The Al”;

I have the following error: No such collation sequence: IUNICODE

I thus make use of SQL - Viewer 2.4 to carry out my queries. You said that with this version it will be possible to carry out multiples statement

UPDATE Songs SET Kind = “Pop” WHERE Artist = “Shakira Feat. Wyclef Jean”;
UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “Snow Patrol”;
UPDATE Songs SET Kind = “Pop” WHERE Artist = “Taylor Hicks”;
UPDATE Songs SET Kind = “Pop” WHERE Artist = “Teddy Geiger”;
UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “The Al”;
UPDATE Songs SET Kind = “Rock'n'roll” WHERE Artist = “The Fray”;

I tried to do it but it doesn't work. How to make it work ?

In the case that it doesn't work can you PLEASE propose me a solution to me to do it because I have 2100 queries to execute

An ultimate solution could be that I send you my database and my queries, you execute them and return me the DataBase.

Regards,

Bourbe

PS: can you look at this:

http://www.mediamonkey.com/forum/viewto ... 19&t=53222
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States

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

Post by Mizery_Made »

There's no "Kind" column in the Songs table. Based on the information you're trying to insert, it looks as though you're looking for the "Genre" column. However, couldn't you just tag the songs within MM with the relevant genre? I forsee some potential breakage if you just update the Songs table with SQLViewer, as there is also a Genre and GenreSongs table.
Guest

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

Post by Guest »

Hi,

I made a mistake, the queries are:

UPDATE Songs SET Genre = “Pop” WHERE Artist = “Shakira Feat. Wyclef Jean”;
UPDATE Songs SET Genre = “Rock'n'roll” WHERE Artist = “Snow Patrol”;
UPDATE Songs SET Genre = “Pop” WHERE Artist = “Taylor Hicks”;
UPDATE Songs SET Genre = “Pop” WHERE Artist = “Teddy Geiger”;
UPDATE Songs SET Genre = “Rock'n'roll” WHERE Artist = “The Al”;
UPDATE Songs SET Genre = “Rock'n'roll” WHERE Artist = “The Fray”;

couldn't you just tag the songs within MM with the relevant genre?

like i said I have 2100 queries to do and it is very tiresome

I forsee some potential breakage if you just update the Songs table with SQLViewer, as there is also a Genre and GenreSongs table

These table are automatically update when the table Songs is updated

Bourbe.
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States

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

Post by Mizery_Made »

Guest wrote:I forsee some potential breakage if you just update the Songs table with SQLViewer, as there is also a Genre and GenreSongs table

These table are automatically update when the table Songs is updated
No, they are not. Running a test update of "UPDATE Songs SET Genre = "TestGenre" WHERE ID=1" results in the Genre being changed for the first song in the database in the Songs table, however the Genre and GenreSongs tables are not updated. As suspected, this results in breakage of features as the "updated" Genre will appear in the Genre column within MM as well as in the Genre field on the songs properties, but the track will not show up in the Genre node under the Genre you have set (if it's a new Genre, that genre won't even be displayed, as it's not in the Genres table).
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

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

Post by Bex »

Sorry but multiple execution is not implemented yet. But it's better to do these kinds of changes in the gui since it updates the correct tables and writes the data to the tag of the songs.
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
Post Reply