SQL Editor 1.0.1

by **fizzjob** » Thu Nov 17, 2022 9:11 am

Barry4679 wrote: ↑Wed Nov 16, 2022 8:46 am Install is by just overwriting the two files of the same name?

Correct - you don't even have to exit MM, it'll just pick it up automatically the next time you open the SQL Editor.

by **Barry4679** » Wed Nov 16, 2022 8:46 am

fizzjob wrote: ↑Mon Nov 14, 2022 10:53 am Admittedly this is probably only of interest to a small number of users

I would be one of those, thanks.

Install is by just overwriting the two files of the same name?

FWIIW my workaround for exporting, up until now, has been to attach my own database to mm5.db by running an sql "attach" command inside the Sql Editor window, and then inserting SQL Editor results into my own database.

by **fizzjob** » Mon Nov 14, 2022 10:53 am

For my own purposes I have modified this script to allow for exporting of the query results to a tab-delimited file. I opted for tab-delimited because IMO tabs are less likely to be inside a field than other common delimiters like commas and semicolons. It also (theoretically) minimizes conflicts from dealing with quotation marks, apostrophes and so forth. The "export" button is only functional if there is data to export.

Admittedly this is probably only of interest to a small number of users, but why not?

dlgSQLEditor.html

Code: Select all

<html class="dialog">

<head>
    <title>SQL Editor</title>
    <script src="file:///mminit.js"></script>
    <script src="dlgSQLEditor.js"></script>
</head>

<body data-posSaveName="dlgSQLEditor" data-defaultSize="1000,800">
    <div class="padding fill flex column">
        <div data-id="toppanel" class="fill flex column hSeparatorTiny">
            <label data-add-colon class="paddingRow">Query</label>
            <div data-id="panel" class="stretchWidth flex column hSeparatorTiny" style="height: 150px">
                <div data-id="sqlpanel" class="fill" data-control-class="SQLEditor">
                </div>
            </div>
            <label data-add-colon class="paddingRow">Result</label>
            <div data-id="resultLV" class="fill scrollable">
            </div>
        </div>        
        <div data-control-class="Buttons">
            <div data-id="btnExecute" data-position="opposite">Execute</div>
	    <div data-id="btnExport">Export</div>
            <div data-id="btnCancel">Close</div>
        </div>
        <label data-id="progress" class="statusbar"></label>
    </div>
</body>

</html>

dlgSQLEditor.js

Code: Select all

var UI;

function OnSQLExec(sql, resultLV) {

    resultLV.innerHTML = '';

    var tm = Date.now();

    UI.btnExecute.controlClass.disabled = true;
    UI.progress.innerHTML = 'Running query ...';

    app.db.getQueryResultAsync(sql).then(function (res) {
        var tmload = Date.now();
        var table = '<table class="sqltable"><tr>';
        var names = res.names;
        var cols = names.count;
        var rows = 0;
        UI.progress.innerHTML = 'Rendering data ...';
        names.locked(function () {
            for (var i = 0; i < cols; i++) {
                table += '<th>' + names.getValue(i) + '</th>';
            }
        });
        table += '</tr>';
        var loop = 0;
        var token = {
            canceled: false
        };
        asyncLoop(function () {
            loop = 0;
            while (loop < 10 && !res.eof) {
                table += '<tr>';
                for (var i = 0; i < cols; i++) {
                    table += '<td>' + res.fields.getValue(i) + '</td>';
                }
                table += '</tr>';
                res.next();
                rows++;
                loop++;
                if (rows > 1000) break;
            }
            return (rows > 1000) || res.eof;
        }, 0, token, function () {
            resultLV.innerHTML = table;
            UI.btnExecute.controlClass.disabled = false;
			UI.btnExport.controlClass.disabled = false;
            UI.progress.innerHTML = 'Query for ' + rows + ' rows took ' + (tmload - tm) + 'ms (rendering took ' + (Date.now() - tmload) + 'ms)';
        });
    }, function (err) {
        UI.progress.innerHTML = 'Query error "' + err + '"';
        UI.btnExecute.controlClass.disabled = false;
		UI.btnExport.controlClass.disabled = true;
    });
}

function exportToTSV(resultLV) {
    var tsv_data = [];
 
    var rows = document.getElementsByTagName('tr');
    for (var i = 0; i < rows.length; i++) {
        var cols = rows[i].querySelectorAll('td,th');
        var tsvrow = [];
        for (var j = 0; j < cols.length; j++) {
            tsvrow.push(cols[j].innerHTML);
        }
        tsv_data.push(tsvrow.join("\t"));
    }
    tsv_data = tsv_data.join('\n');
	app.utils.dialogSaveFile('%USERPROFILE%\desktop', 'tsv', 'TSV (*.tsv)|*.tsv|All files (*.*)|*.*', _('Exporting') + '...', 'mm5_output.tsv').then(function (resfilename) {
		if (resfilename != '') {
			app.filesystem.saveTextToFileAsync(resfilename, tsv_data);
		}
	})
}

function init(params) {
    var wnd = this;
    wnd.title = _('SQL editor');

    UI = getAllUIElements();
	
	UI.btnExport.controlClass.disabled = true;

    localListen(UI.btnExecute, 'click', () => {
        OnSQLExec(UI.sqlarea.value, UI.resultLV);
    });
	
	localListen(UI.btnExport, 'click', () => {
		exportToTSV(UI.resultLV);
	});
}

The only odd behavior I've come across is cancelling the save dialog sends the SQL Editor behind the main MM5 window.

by **drakinite** » Mon Feb 28, 2022 4:51 pm

Glad to help!

by **MPG** » Mon Feb 28, 2022 4:47 pm

Drakinite my friend are a godsend!

Your solution worked. You have saved me month's of work! TYVM....if we every have the opportunity to meet, there's a beer in your hand

!

by **drakinite** » Mon Feb 28, 2022 4:35 pm

I see.
I don't see any problems with your code at first sight, but putting it into an async function will make operations much easier.

Code: Select all

async function UpdateAuthor(psql, pauthor){
	var trcklist;
	trcklist = app.db.getTracklist(psql, -1);
    await trcklist.whenLoaded();
    console.log(`Setting author = ${pauthor} to ${trcklist.count} tracks`);
    listForEach(trcklist, (track) => {
        track.author = pauthor;
    });
    try {
        await trcklist.commitAsync();
        console.log('done');
    }
    catch (err) {
        console.error(err);
    }
}

Now, instead of updateAuthor(); updateAuthor(); do:

Code: Select all

async function execute() {
	await UpdateAuthor(<path>, <name>);
	await UpdateAuthor(<path 2>, <name 2>);
	await UpdateAuthor(<path 3>, <name 3>);
	etc.
}
execute();

This will prevent MediaMonkey from attempting to do hundreds of operations at once. Also, logging to the console will help you identify if something is wrong (e.g. if you got the filepath wrong)

by **MPG** » Mon Feb 28, 2022 2:18 pm

Each song potentially has a different composer.

I have a spreadsheet that I extracted from my MM4 database. From that spreadsheet, I can create the Update statement which I then copy into an MM5 addon which will in turn update the MM5 database.

by **drakinite** » Mon Feb 28, 2022 2:15 pm

If you have 7000 tracks to update, why are you selecting specific/individual filenames in your SQL statements?

by **MPG** » Mon Feb 28, 2022 2:10 pm

I have 7000 records to update. If I can automate this...it'll take seconds....otherwise, it's a months worth of effort.

by **drakinite** » Mon Feb 28, 2022 2:03 pm

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.

by **MPG** » Mon Feb 28, 2022 11:24 am

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.

by **Ludek** » Mon Feb 28, 2022 8:02 am

MPG wrote: ↑Fri Feb 25, 2022 2:05 pm I really really miss VBA...it worked.

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);
				}		
});

by **MPG** » Fri Feb 25, 2022 2:05 pm

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.

by **MPG** » Fri Feb 25, 2022 1:05 pm

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.

by **MPG** » Thu Feb 24, 2022 2:18 pm

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(); });

SQL Editor 1.0.1

Post a reply

Expand view Topic review: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1

Re: SQL Editor 1.0.1