SQL Editor 1.0.1

MPG · Post 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.

drakinite · Post 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?

MPG · Post 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.

drakinite · Post 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)

MPG · Post 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

!

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

Glad to help!

fizzjob · Post 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.

Barry4679 · Post 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.

fizzjob · Post 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.

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