SQL Editor 1.0.1

Get help for different MediaMonkey 5 Addons.

Moderators: jiri, drakinite, Addon Administrators

Barry4679
Posts: 2408
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL Editor 1.0.1

Post by Barry4679 »

drakinite wrote: Sun Nov 14, 2021 10:05 am Hmm, what would be the benefit of that?
Now that you ask, it is not as great an idea as it was yesterday. :D

I was thinking of anything like this.
Obviously not a sensible query but it demonstrates the suggestion; IMO ]the first line is more elegant in the bottom query

No biggie.

One thing I noticed today is that your colourisation is defeated by enclosing brackets or adjacent commas ... see the blank spaces that I have had to introduce into the 1st line of the top query.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

ah, good point!
And ok - The reasoning behind the "after as" suggestion is fair, though it may be a bit of a chore to add. But I can easily fix that parentheses issue. Are there any other characters, other than commas and parens, that can surround SQL keywords?
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
Barry4679
Posts: 2408
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL Editor 1.0.1

Post by Barry4679 »

drakinite wrote: Mon Nov 15, 2021 6:39 pmAre there any other characters, other than commas and parens, that can surround SQL keywords?
Operators like + - | * / % & < > = !

Also rowid is a valid fieldname in any table
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

Thanks! (I'm sure you can tell from the questions that I'm not the most experienced at SQL :P)
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
MPG
Posts: 418
Joined: Tue May 13, 2008 11:22 pm

SQL Editor Update Statements

Post by MPG »

Hi,
I just realized that I did a major foopa with my Clear Fields add-on. I ran the script against my classical music and it removed the composer (author) values. Oops! Now, I have an old MM4 backup that I can use to repopulate the fields. I have extracted the values using SQL Lite and have created the query to update the author field:

Update songs Set author = "Alex Lifeson; Geddy Lee; Neil Peart" Where songpath = ":\Music\Music Collection\Classical\S\String Quartet Tribute To Rush\Exit...Stage Right\01 - The Spirit Of Radio.mp3";

When I run a select statement against the update, the database has been updated, however, if I open the song properties through the UI, the update is not showing. I'm missing something...just not sure what...can anyone provide some insight?
TIA
MPG
Triumph - Hold On: Music holds the secret, to know it can make you whole.
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

I believe that's because executing SQL doesn't update the UI. I just ran a test and the changes do become visible after an app restart. Will ask the others if it's possible to work around that.
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
Ludek
Posts: 4958
Joined: Fri Mar 09, 2007 9:00 am

Re: SQL Editor 1.0.1

Post by Ludek »

To be sure that the song's metadata matches the DB changes made externally you need to restart MM5.
But your way the values won't be written to file tag anyway.

It is better to use app.db.getTracklist('SELECT * FROM Songs ...') and modify the metadata within the tracklist directly like this:

Code: Select all

var trcklist = app.db.getTracklist('SELECT * FROM Songs  WHERE SongPath = ":\Music\Music Collection\Classical\S\String Quartet Tribute To Rush\Exit...Stage Right\01 - The Spirit Of Radio.mp3"', -1); 
trcklist.whenLoaded().then(function () { 
          // all tracks are loaded here         
          listForEach(tracklist, (track) => {
          	track.author =  "Alex Lifeson; Geddy Lee; Neil Peart";          	
          });
          tracklist.commitAsync(); // to update database and tag
});                                       
This way the changes are immediatelly updated eveywhere in MM5 UI + tag changes are also written.
MPG
Posts: 418
Joined: Tue May 13, 2008 11:22 pm

Re: SQL Editor 1.0.1

Post by MPG »

Hi Ludek,
Thanks for the suggestion.

I've been working with the code this morning and have determined that the app.db.getTracklist never returns a result set, yet, if I run the SQL in the editor it does return a row.

this is my code:

Code: Select all

	var trcklist;
	trcklist = app.db.getTracklist('SELECT * FROM Songs  WHERE SongPath = ":\Music\Music Collection\Classical\S\String Quartet Tribute To Rush\Exit...Stage Right\01 - The Spirit Of Radio.mp3"', -1);
		trcklist.whenLoaded().then(function () { 
			messageDlg(_(trcklist.count), 'Information', ['btnOK'], {
				defaultButton: 'btnOK'
			}, undefined);
	
			listForEach(trcklist, (track) => {
				track.author = "Alex Lifeson; Geddy Lee; Neil Peart";
			});
		trcklist.commitAsync(); 
	});

I can't use console.log to trap the count as there is no UI.

Another problem that I'm encountering is that I have some songs with a single quote in the name. Such as:
:\Music\Music Collection\Classical\S\String Quartet Tribute To Rush\Exit...Stage Right\08 - Broon's Bane.mp3

I've tried using two single quotes with no success.

Any suggestions?
TIA
MPG
Triumph - Hold On: Music holds the secret, to know it can make you whole.
Ludek
Posts: 4958
Joined: Fri Mar 09, 2007 9:00 am

Re: SQL Editor 1.0.1

Post by Ludek »

Escape the backslashes and apostrophes like this:

Code: Select all

trcklist = app.db.getTracklist("SELECT * FROM Songs  WHERE SongPath = ':\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\08 - Broon''s Bane.mp3'", -1);
MPG
Posts: 418
Joined: Tue May 13, 2008 11:22 pm

Re: SQL Editor 1.0.1

Post by MPG »

I have both good news and not so good news.
The good news: escaping the backslashes works.

The not so good news:
1) Escaping the apostrophe doesn't work
2) I have 7,000 records to update...running the script one after the other such as this only updates the last row.

Code: Select all

	var trcklist;
	trcklist = app.db.getTracklist('SELECT * FROM Songs  WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\01 - The Spirit Of Radio.mp3"', -1);trcklist.whenLoaded().then(function () { listForEach(trcklist, (track) => {track.author = "Alex Lifeson; Geddy Lee; Neil Peart";});trcklist.commitAsync(); });
	trcklist = app.db.getTracklist('SELECT * FROM Songs  WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\09 - The Trees.mp3"', -1);trcklist.whenLoaded().then(function () { listForEach(trcklist, (track) => {track.author = "Alex Lifeson; Geddy Lee; Neil Peart";});trcklist.commitAsync(); });
TIA
MPG
Triumph - Hold On: Music holds the secret, to know it can make you whole.
MPG
Posts: 418
Joined: Tue May 13, 2008 11:22 pm

Re: SQL Editor 1.0.1

Post by MPG »

Good news, to escape the apostrophe use a backslash in front of it.

Ok, so now just need to figure out how to wait for the commitasync to complete before processing the next command.

Progress....at a snails pace is slow and tiresome...but progress. :o
TIA
MPG
Triumph - Hold On: Music holds the secret, to know it can make you whole.
MPG
Posts: 418
Joined: Tue May 13, 2008 11:22 pm

Re: SQL Editor 1.0.1

Post by MPG »

Ok, so this is my latest effort....still no results. I created a separate function and I'm passing it the SQL and the author. Sometimes they update with the correct author...sometimes they only update the author of the last value, sometimes only the last row is updated. I know the problem is with the commitasync...I just don't know what the fix is. As a side note...false is always returned from the commitasync promise.

This is the function:

Code: Select all

function UpdateAuthor(psql, pauthor){
	var trcklist;
	trcklist = app.db.getTracklist(psql, -1);
	trcklist.whenLoaded().then(function () { 
		listForEach(trcklist, (track) => {
			track.author = pauthor;
		});
		trcklist.commitAsync().then(function(result){
			if (result) {
				messageDlg(_('Worked'), 'Information', ['btnOK'], {
					defaultButton: 'btnOK'
				}, undefined);
			} else {
				messageDlg(_('FAILED!'), 'Warning', ['btnOK'], {
					defaultButton: 'btnOK'
				}, undefined);
			}
		}); 
	});
}
The function calls are:
UpdateAuthor('SELECT * FROM Songs WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\01 - The Spirit Of Radio.mp3"',"John");
UpdateAuthor('SELECT * FROM Songs WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\07 - Jacob\'s Ladder.mp3"',"Leo");
UpdateAuthor('SELECT * FROM Songs WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\09 - The Trees.mp3"',"Empey");


I really really miss VBA...it worked. :roll:
TIA
MPG
Triumph - Hold On: Music holds the secret, to know it can make you whole.
Ludek
Posts: 4958
Joined: Fri Mar 09, 2007 9:00 am

Re: SQL Editor 1.0.1

Post by Ludek »

MPG wrote: Fri Feb 25, 2022 2:05 pm I really really miss VBA...it worked. :roll:
So it works now or not for you?

I think your code above works, but the Promise usage is not correct resulting in false negative result.
See: https://developer.mozilla.org/en-US/doc ... ts/Promise

Use this:

Code: Select all

trcklist.commitAsync().then(
				function(){
					messageDlg('Worked', 'Information', ['btnOK'], {
						defaultButton: 'btnOK'
					}, undefined);
				}, 
				function(err){
					messageDlg('FAILED!' + err, 'Warning', ['btnOK'], {
						defaultButton: 'btnOK'
					}, undefined);
				}		
}); 
MPG
Posts: 418
Joined: Tue May 13, 2008 11:22 pm

Re: SQL Editor 1.0.1

Post by MPG »

Hi Ludek,
Thanks for the suggestions. The code doesn't work:
For example, I have the following function calls:

Code: Select all

UpdateAuthor('SELECT * FROM Songs  WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\01 - The Spirit Of Radio.mp3"',"John");

UpdateAuthor('SELECT * FROM Songs  WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\07 - Jacob\'s Ladder.mp3"',"Leo");

UpdateAuthor('SELECT * FROM Songs  WHERE SongPath = ":\\Music\\Music Collection\\Classical\\S\\String Quartet Tribute To Rush\\Exit...Stage Right\\09 - The Trees.mp3"',"Empey");
to this function:

Code: Select all

function UpdateAuthor(psql, pauthor){
	var trcklist;
	trcklist = app.db.getTracklist(psql, -1);
	trcklist.whenLoaded().then(function () { 
		listForEach(trcklist, (track) => {
			track.author = pauthor;
		});
		trcklist.commitAsync().then(
			function(){
				messageDlg('Worked', 'Information', ['btnOK'], {
					defaultButton: 'btnOK'
				}, undefined);
			},
			function(err){
				messageDlg('FAILED!' + err, 'Warning', ['btnOK'], {
					defaultButton: 'btnOK'
				}, undefined);
			}
		);
	}); 
}
The first call updates the composer to "Empey" and it should be "John"
The second call updates the composer to "Leo". So that works
The third call updates the composer to "Empey". And this worked to.

I've also tried it with more calls and similar results happen...most of the songs are only updated with the value of the last function call, not the supplied value.

Please help...there has to be a way to get this to work.
TIA
MPG
Triumph - Hold On: Music holds the secret, to know it can make you whole.
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

Say, why are you going through all this effort instead of updating the tracks manually in the UI? You can search for the file path in the MM5 search, select multiple tracks & edit their properties, and edit the author of the selected tracks as needed.
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
Post Reply