Add paging, CSV, and JSON export support to Blazer UI

blazerPageBookmarklet.js

// Adds a "Page Results" button to a Blazer query and buttons to export (copy or download) results as JSON or CSV.
//
// To use this, open a Blazer query on the "Edit" page, open Chrome Dev Tools, paste all this code and hit Enter.
//
// When paging, the Blazer query must contain an expression like this that can be used to cursor through results:
//
// WHERE product.order_id >= 0 ORDER BY product.order_id LIMIT 100
// or:
// WHERE agreements.id >= '00000000-0000-0000-0000-000000000000' ORDER BY agreements.id LIMIT 100
//
// You have to order results by the cursor field and set the cursor column and cursor field regex in the UI so
// it matches what's in your actual query.
//
//
// Troubleshooting:
// If you get an error in the Chrome console:
//   Error handling response: Error: runtime/sendMessage: The message port closed before a response was received.
// It's probably caused by a Chrome Extension like Dashlane and it's safe to ignore.
//

function setError(msg) {
    document.getElementById('divError').innerText = msg;
    if (msg.length > 0) {
        document.getElementById('divError').style.display = "block";
    }
    else {
        document.getElementById('divError').style.display = "none";
    }
}
function setStatus(msg) {
    document.getElementById('divStatus').innerText = msg;
    console.log(msg);
}
function setQuery(query) {
    ace.edit('editor').setValue(query);
}
function getQuery() {
    return ace.edit('editor').getValue();
}
function getCursorColumn() {
    return document.getElementById('txtCursorColumn').value;
}
function getMaxPages() {
    return document.getElementById('txtMaxPages').value;
}
function getCursorRegex() {
    return new RegExp(document.getElementById('txtCursorRegex').value);
}
function getCursorRegexReplacement(newValue) {
    return document.getElementById('txtCursorRegex').value.replace('(.+?)', newValue).replace('(.*)', newValue);
}
function setRegexPreview(str) {
    document.getElementById('spanRegexCursorPreview').innerText = str;
}
function updateRegexPreview() {
    const cursorRegex = getCursorRegex();
    if (!cursorRegex.test(getQuery())) {
        setRegexPreview("ERROR: Specified cursor regex does not match the query provided.");
    }
    else {
        setRegexPreview(`Matches: ${getQuery().match(getCursorRegex())[0]}`);
    }
}
function getResultsAsJsonString() {
    return JSON.stringify(parseResults(''), null, 2);
}
function saveFile(filename, data, mimeType='text/json') {
    const blob = new Blob([data], {type: mimeType});
    if(window.navigator.msSaveOrOpenBlob) {
        window.navigator.msSaveBlob(blob, filename);
    }
    else{
        const elem = window.document.createElement('a');
        elem.href = window.URL.createObjectURL(blob);
        elem.download = filename;        
        document.body.appendChild(elem);
        elem.click();        
        document.body.removeChild(elem);
    }
}
function copyJson() {
    navigator.clipboard.writeText(getResultsAsJsonString());
}
function downloadJson() {
    saveFile('results.json', getResultsAsJsonString(), mimeType='text/json');
}
function getResultsAsCSV() {
    const results = parseResults('');
    if (results.length == 0) {
        return [];
    }
    const array = [Object.keys(results[0])].concat(results)
    return array.map(it => {
        return Object.values(it).map(val => {
            const terms = [',',"\n",'"'];
            if (terms.some(term => val.includes(term))) {
                return `"${val.replace('"', '""')}"`;
            }
            else {
                return val;
            }
        }).join(',');
    }).join('\n');
}
function copyCSV() {
    navigator.clipboard.writeText(getResultsAsCSV());
}
function downloadCSV() {
    saveFile('results.csv', getResultsAsCSV(), mimeType='text/csv');
}

function recreateDivPagedResults() {
    // Container to store all our paged results underneath where Blazer outputs its real results
    const existingDivPagedResults = document.getElementById('pagedResults');
    if (existingDivPagedResults) {
        existingDivPagedResults.remove();
    }
    var divPagedResults = document.createElement('div');
    divPagedResults.setAttribute("id", "pagedResults");
    document.getElementById('results').parentElement.appendChild(divPagedResults);
}

function createUI() {
    if (document.getElementById("divToolbar")) return;
    if (!document.title.startsWith('Edit - ')) {
        alert('This code only works on the Blazer query "Edit" screen');
    }

    recreateDivPagedResults();

    var divPagingControls = document.createElement('div');
    divPagingControls.setAttribute("id", "divPagingControls");
    document.body.insertBefore(divPagingControls, document.body.firstChild);

    const defaultCursorRegex = `agreements.id >= '(.+?)'`;
    var divCursorRegex = document.createElement('div');
    divCursorRegex.setAttribute("id", "divCursorRegex");
    divCursorRegex.innerHTML = `Cursor regex: <input id="txtCursorRegex" value="${defaultCursorRegex}" onKeyUp="updateRegexPreview()"/> <span id="spanRegexCursorPreview"/> <br/>`;
    divPagingControls.appendChild(divCursorRegex);

    var divCursorColumn = document.createElement('div');
    divCursorColumn.setAttribute("id", "divCursorColumn");
    divCursorColumn.innerHTML = "Cursor column: <input id=\"txtCursorColumn\" value=\"id\" /><br/>";
    divPagingControls.appendChild(divCursorColumn);

    var divMaxPages = document.createElement('div');
    divMaxPages.setAttribute("id", "divMaxPages");
    divMaxPages.innerHTML = "Max pages: <input id=\"txtMaxPages\" value=\"4\" /><br/>";
    divPagingControls.appendChild(divMaxPages);

    var divStatus = document.createElement('div');
    divStatus.setAttribute("id", "divStatus");
    divPagingControls.appendChild(divStatus, document.body.firstChild);
    setStatus("")

    var divError = document.createElement('div');
    divError.setAttribute("id", "divError");
    divError.setAttribute("class", "alert alert-danger");
    divPagingControls.appendChild(divError, document.body.firstChild);
    setError("")
    
    divPagingControls.appendChild(document.createElement('hr'));
    
    var divToolbar = document.createElement('div');
    divToolbar.setAttribute("id", "divToolbar");
    divToolbar.innerHTML = `
        <button id="btnCopyJson" onClick="copyJson();">Copy JSON</button>
        <button id="btnDownloadJson" onClick="downloadJson();">Download JSON</button>
        <button id="btnCopyCSV" onClick="copyCSV();">Copy CSV</button>
        <button id="btnDownloadCSV" onClick="downloadCSV();">Download CSV</button>
        <p>
        <hr>
        <button id="btnPageResults" onClick="pageResults();">Page Results</button>
    `;
    document.body.insertBefore(divToolbar, document.body.firstChild);

    updateRegexPreview();
}

function parseResults(divResultsSelector = 'div#results') {
    const headers = $(`${divResultsSelector} table.results-table th`).map((i, th) => th.innerText);
    const rowCells = $(`${divResultsSelector} table.results-table tbody tr`).map((i, tr) => $(tr).find("td").map((i, td) => td.innerText));
    return rowCells.map((i, row) => row.toArray().reduce((map, cell, i) => {
        // We want 'company.name' to be accessed via results.company.name, not agreement['company.name']
        const setProp = (outerObj, key, val) => {
            // Credit: https://stackoverflow.com/questions/56919791/javascript-dot-notation-strings-to-nested-object-references
            if (!key.includes('.')) {
                outerObj[key] = val;
                return outerObj;
            }
            const keys = key.split('.');
            const lastKey = keys.pop();
            const lastObj = keys.reduce((a, key) => {
                // Create an object at this key if it doesn't exist yet:
                if (!a[key]) {
                    a[key] = {};
                }
                return a[key];
            }, outerObj);
            // We now have a reference to the last object created (or the one that already existed
            // so, just assign the value:
            lastObj[lastKey] = val;
        }
        setProp(map, headers[i], cell);
        return map;
    }, {})).toArray();
}

async function pageResults() {
    setError('');
    setStatus('Paging results...');
    recreateDivPagedResults();

    const btnPageResults = document.getElementById('btnPageResults');
    if (btnPageResults.innerText === 'Page Results') {
        btnPageResults.innerText = 'Cancel';
        window.stopPagingResults = false;
    }
    else {
        window.stopPagingResults = true;
        return;
    }

    try {
        // Validate query structure
        const originQuery = getQuery();
        let newQuery = originQuery;
        if (!/ORDER BY /i.test(originQuery)) {
            setError("ERROR: Query must contain an ORDER BY clause");
            return;
        }
        if (!/LIMIT /i.test(originQuery)) {
            setError("ERROR: Query must contain a LIMIT clause");
            return;
        }
        if (!/WHERE /i.test(originQuery)) {
            setError("ERROR: Query must contain a WHERE clause");
            return;
        }
        const cursorRegex = getCursorRegex();
        if (!cursorRegex.test(originQuery)) {
            setError("ERROR: Specified cursor regex does not match the query provided.");
            return;
        }

        // Fetch the initial value for the cursor from the query itself
        let cursorValue = originQuery.match(cursorRegex)[1]; // '00000000-0000-0000-0000-000000000000';
        let lastResultFromLastPage = null;

        const maxPagesToFetch = getMaxPages();
        let pagesFetched = 0;
        let totalResults = 0;
        while(!window.stopPagingResults) {
            if (pagesFetched == maxPagesToFetch) {
                setError(`ERROR: Fetched maximum number of pages (${pagesFetched})`);
                return;
            }
            pagesFetched++;
            
            newQuery = newQuery.replace(getCursorRegex(), getCursorRegexReplacement(cursorValue));
            setQuery(newQuery);
            console.log(`Running query with cursorValue ${cursorValue}`);
            await app.run();
            async function awaitResults() {
                console.log(`app.running=${app.running}`);
                while(!window.stopPagingResults) {
                    if (!app.running) {
                        console.log('Query finished');
                        return;
                    }
                    console.log('Waiting for query to finish...');
                    await new Promise(resolve => setTimeout(resolve, 1000));
                }
                if (window.stopPagingResults) {
                    console.log('User canceled');
                }
            }
            await awaitResults();
            console.log('Query complete');
            
            let results = parseResults();
            if (results.length == 0) {
                setError("ERROR: No results returned");
                return;
            }
            
            // We always fetch at least 1 duplicate row from the previous results, to ensure we don't miss any values.
            // Find where the new results begin
            if (lastResultFromLastPage) {
                let lastDuplicateValueIndex = results.findIndex((element) => JSON.stringify(lastResultFromLastPage) === JSON.stringify(element));
                if (lastDuplicateValueIndex == -1) {
                    // Somehow we didn't find any rows in common with the previous results. This shouldn't happen.
                    setError("ERROR: Something unexpectedly broken with the cursor");
                    return;
                }
                // Remove all duplicate results from the new results
                results.splice(0, lastDuplicateValueIndex+1);
                // Remove all duplicate results from the Blazer results UI itself
                $("div#results table.results-table tbody tr").filter(`:lt(${lastDuplicateValueIndex+1})`).remove();
            }
            lastResultFromLastPage = results[results.length-1];

            // Archive results at the bottom of the page
            let resultsCopy = document.getElementsByClassName('results-container')[0].cloneNode(true);
            resultsCopy.getElementsByTagName("thead")[0].remove(); // remove redundant header row
            let divPageSeparator = document.createElement('div');
            divPageSeparator.setAttribute('class', 'pageSeparator');
            divPageSeparator.innerText = `Page: ${pagesFetched}, Results: ${results.length}`;
            document.getElementById('pagedResults').appendChild(divPageSeparator);
            document.getElementById('pagedResults').appendChild(resultsCopy);
            // Remove the real, original result rows from Blazer
            $("div#results table.results-table tbody tr").remove();

            // Update overall results stats
            totalResults += results.length;
            $('#results-html > p')[0].innerHTML = `${totalResults} results across ${pagesFetched} pages. <a href="#" onClick="$('div.pageSeparator').hide();" >Hide Page Headers</a>`;
            setStatus(`Fetched ${totalResults} results across ${pagesFetched} pages.`);
            
            // Fetch next results
            if (results.length === 0) {
                setError("ERROR: No new unique results for this page compared to previous. In your query, set a higher LIMIT of results to return.");
                return;
            }
            let nextCursorValue = results[results.length - 1][getCursorColumn()];
            console.log('Next cursor value: ' + nextCursorValue);
            if (nextCursorValue === cursorValue) {
                setError("ERROR: The cursor value repeats over this range. In your query, set a higher LIMIT of results to return.");
                return;
            }
            cursorValue = nextCursorValue;
        }
    }
    finally {
        btnPageResults.innerText = 'Page Results';
    }
}
createUI();