New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update SQL formatter bundle for new API #1988

Open
karthicraghupathi opened this Issue Sep 17, 2014 · 29 comments

Comments

Projects
None yet
@karthicraghupathi

karthicraghupathi commented Sep 17, 2014

I'm using Sequel Pro 1.0.2 Build 4096. For the last couple of days, the format SQL bundle has stopped working with the following error message:

Unauthorized request, please contact support@dpriver.com a free account.

Here is a screenshot:

image

Can you provide some pointers as to what could have gone wrong and what might be a possible fix?

@sqlparser

This comment has been minimized.

sqlparser commented Sep 18, 2014

Format SQL bundle in Sequel Pro use free sql formatter service from:
http://www.dpriver.com/pp/sqlformat.htm

Access to this free sql formatter service via webpage is available as usual, however Sequel Pro access this service via API which was move to a new site:
https://github.com/sqlparser/sql-pretty-printer/wiki/SQL-FaaS#2-api

So something changes should be made in this file:
https://github.com/sequelpro/Bundles/blob/master/core/Format%20SQL.spBundle/command.plist

I'm author of this sql formatter from dpriver.com, feel free to let me know if anything I can do when migrate to use this new sql formatter service, btw, this sql formatter service is free as usual.

James

@dmoagx dmoagx changed the title from Format SQL Bundle Error to Update endpoint URL for SQL formatter Sep 18, 2014

@dmoagx dmoagx changed the title from Update endpoint URL for SQL formatter to Update SQL formatter bundle for new API Sep 18, 2014

@dmoagx

This comment has been minimized.

Member

dmoagx commented Sep 18, 2014

I wouldn't say we use an API, but rather parse the result from the HTML response page, so this might need a substantial rewrite.

James: BTW Thanks for your e-mail reminder, Sequel Pro development just isn't very active at the moment.
I hope this isn't causing any problems for you.

@gerrior

This comment has been minimized.

gerrior commented Oct 9, 2014

The problem has morphed into "Please refresh this page to load the latest version 4.0"

screen shot 2014-10-09 at 11 11 51 am

@minaevd

This comment has been minimized.

minaevd commented Oct 27, 2014

Here is the fix, no development required.
All you have to do is to change the clientid key in the Bundles editor:

*) open Sequel Pro application
*) go to Bundles -> Bundle Editor
*) Use left pane, Select "Input field" (Show), choose Format SQL
*) find the following row in the "Command" textarea:

*) replace the value of the input to "dpriver-9094-8133-2031", so the new line is:

*) Click "Save"

It works for me.

Dmitry

@Mirocow

This comment has been minimized.

Mirocow commented Nov 7, 2014

# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# check if connected to the internet and if server is up
connected=$(/sbin/ping -t 8 -c 1 -on www.dpriver.com 2> /dev/null | grep '69\.5\.11\.169' | wc -l)
if [ $connected -eq "0" ]; then
    echo "<font color=red>You are probably not connected to the internet or http://www.dpriver.com/ server is down.</font>"
    exit $SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP
fi

# show info alert about sending the SQL statements to an online service
if [ ! -e infoShowed ]; then
    touch infoShowed
    osascript -e 'tell app "Sequel Pro" to display dialog "This command will send the SQL statement(s) - unencrypted - to an online service (http://www.dpriver.com).  Please consider this before sending confidential data!\n\nThis message will only be displayed once." with icon caution' 2> 1
    if [ `cat 1 | wc -c` -ne 0 ]; then
        rm -f 1
        exit $SP_BUNDLE_EXIT_NONE
    fi
    rm -f 1
fi

# send SQL to www.dpriver.com and replace the formatted SQL string in first responder; if a parser error occurred
# show error message and try to jump to the error
cat <<HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <base href="http://www.dpriver.com/pp/">
    <title>Connecting www.dpriver.com</title>
    <script>
        function lookForResult() {
            if(document.all.outputsql.value.length > 8) {
                if(document.all.errorhint.style.display == "inline") {
                    var errormes = document.all.outputsql.value;
                    var re = /.*?\(\d+\s*,\s*(\d+)\).*/;
                    re.exec(errormes);
                    var pos = parseInt(RegExp.\$1) - 1;
                    re = /.*?(\d+).*/;
                    re.exec('$SP_SELECTED_TEXT_RANGE');
                    var offset = parseInt(RegExp.\$1);
                    pos = pos + offset;
                    alert(document.all.outputsql.value);
                    window.system.setSelectedTextRange(pos+'');
                } else {
                    var txt = document.all.outputsql.value;
                    var len = txt.length;
                    window.system.setSelectedTextRange('$SP_SELECTED_TEXT_RANGE');
                    window.system.insertText(txt.slice(0,len-2));
                }
                window.system.closeHTMLOutputWindow();  
            } else {
                setTimeout("lookForResult()",50);
            }
        }

        function waitForResult() {
            window.system.suppressExceptionAlert();
            setTimeout("lookForResult()",50);
        }
    </script>
    <script language="JavaScript" type="text/javascript" src="pp.js"></script>
</head>
<body onload='window.resizeTo(300,200);SQLFMT.format(document.all.sp_submit);waitForResult()'>
<div id="retvalues"></div>
<p><b>Copyright &copy; 2001-2010 Gudu Software<br>All Rights Reserved<br><a href="mailto:support@dpriver.com">Contact US</a></b></p>
<br><br><br><br><br>
<p align=right>Please wait…</p>
<div class="page-container-1" style="display:none;">
    <div id="container">
        <div id="wrapper">
            <div id="content">
                <form id="SqlFmtForm" name="frm_sqlformat" method="post" action="/cgi-bin/ppserver" onsubmit="return false;">
                    <div id="secondpanel"> 
                        <div id="sp_database">
                            <label class="desc" style="display:inline;">Database</label>
                            <select name="dbvendor">
                                <option value="mysql" selected>MySQL</option>
                            </select>
                            <label class="desc" style="display:inline;padding:0 0 0px 40px; border:0px solid;">Output:</label>
                            <select name="outputfmt">
                                <option value="SQL" selected>SQL(Text)</option>
                            </select>
                        </div>
                    </div>
                    <div id="sqlpanel">
                        <div id="sp_inputsql">
                            <textarea id ="inputsql" name="inputsql" cols="1" rows="1" wrap="off">
                            $SQL
                            </textarea>
                        </div>
                        <div id="sp_submit">
                            <input type="button" id="btnformat" name="print" value="Format SQL" onclick="SQLFMT.format(this)" value="Format SQL">
                            <label style="display:none;">(Time used: <span id="timestamp">0</span> seconds)</label>
                        </div>
                        <div id="sp_submit" style="text-align:left;">
                        </div>
                        <label id="errorhint" class="desc" style="color:#DF0000 !important;display:none;">
                            Can't format input sql, make sure there is no syntax error and select correct database.
                            <a href="" style="display:none;">OR Report a bug of this sql beautifier</a>
                        </label>
                        <iframe id="ioutputsql" ></iframe>
                        <div id="sp_outputsql" style="display:none;">
                            <textarea id = "outputsql" name="outputsql" cols="80" rows="15" wrap="off" >
                            </textarea>
                        </div>
                        <div id="sp_errormsg" style="display:none;">
                            Error message goes to here!
                        </div>
                    </div>
                    <div id="formatoptions">
                        <select name="keywordcs">
                            <option value="Uppercase" selected >Uppercase</option>
                            <option value="Lowercase" >Lowercase</option>
                            <option value="InitCap" >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                        </select>
                        <select name="identifiercs">
                            <option value="Uppercase" >Uppercase</option>
                            <option value="Lowercase" selected >Lowercase</option>
                            <option value="InitCap" >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                            <option value="initcapeachword" >Init cap each word</option>
                        </select>
                        <select name="functioncs">
                            <option value="Uppercase" >Uppercase</option>
                            <option value="Lowercase" >Lowercase</option>
                            <option value="InitCap" selected >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                        </select>
                        <input type="radio" name="lnbrwithcomma" value="after" checked > After
                        <input type="radio" name="lnbrwithcomma" value="before" > Before
                        <input type="radio" name="lnbrwithcomma" value= "beforewithspace" > Before with space
                        <input type="radio" name="liststyle" value="stack" checked>Stacked
                        <input type="radio" name="liststyle" value="nostack">Not Stacked
                        <input type="radio" name="salign" value="sleft" checked> Align left
                        <input type="radio" name="salign" value="sright" > Align right
                        <input type="checkbox" name="andorunderwhere" value="yes" checked> And/Or under Where
                        <input type="checkbox" name="removelnbr" value="no" checked> Remove Linebreak before beautify
                        <input type="checkbox" name="trimquotechar" value="yes"> Trim Quoted Char of Each Line quoted char of eachline: <input type="text" name="quotechar" value= "&#34" size = 5 > 
                        <input type="checkbox" name="compactmode" value="yes"> Compact the output of sql output
                        <input type="text" name="maxlenincm" value= "80" size = 5 > 
                    </div>
                    <input type="hidden" name="clientid" value="dpriver-9094-8133-2031" /> 
                </form>
            </div>
        </div>
    </div>
</div>

</body>
</html>
HTML

Replace for this code

@temirov

This comment has been minimized.

temirov commented Nov 26, 2014

Tnx, @minaevd ! replace the value of the input to "dpriver-9094-8133-2031", so the new line is:

<input type="hidden" name="clientid" value="dpriver-9094-8133-2031" />

That did it for me

@jwg2s

This comment has been minimized.

jwg2s commented Jan 26, 2015

👍 this would be awesome if it could be pushed out! One of my favorite, no-longer-working sequel pro features.

@mrardon

This comment has been minimized.

mrardon commented Jan 26, 2015

@jwg2s Just follow directions in workaround above by @minaevd. Works for me.

@jwg2s

This comment has been minimized.

jwg2s commented Jan 26, 2015

I copy pasted the code from @minaevd but now it just deletes the entire query.

@jwg2s

This comment has been minimized.

jwg2s commented Jan 26, 2015

Copied from a fresh install, must be a formatting issue with Github or something. All well now!

@mrardon

This comment has been minimized.

mrardon commented Jan 26, 2015

Dont think you are looking at @minaevd's response.
Shouldn't be much formatting. On line 149 of a clean install change that line to be: <input type="hidden" name="clientid" value="dpriver-9094-8133-2031" /> The only thing you are replacing is the part before -9094-8133-2031 and that becomes dpriver instead of the number in there currently.

@gagaboy

This comment has been minimized.

gagaboy commented Apr 14, 2015

now the code below does work!!

# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# send SQL to www.dpriver.com and replace the formatted SQL string in first responder; if a parser error occurred
# show error message and try to jump to the error
curl --data-urlencode "rqst_input_sql=$SQL" 'http://www.gudusoft.com/format.php' 2> /dev/null  | php -r 'echo json_decode(file_get_contents("php://stdin"))->rspn_formatted_sql;'
@zirho

This comment has been minimized.

zirho commented Jun 26, 2015

@Mirocow hey, Thanks for the code. but it doesn't work for me.
now it just switches all query to blank...

can't go back to original code. since there is no backup code for it.

Can anybody put a working code?

@zirho

This comment has been minimized.

zirho commented Jul 31, 2015

# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# check if connected to the internet and if server is up
connected=$(/sbin/ping -t 8 -c 1 -on www.dpriver.com 2> /dev/null | grep '69\.5\.11\.169' | wc -l)
if [ $connected -eq "0" ]; then
    echo "<font color=red>You are probably not connected to the internet or http://www.dpriver.com/ server is down.</font>"
    exit $SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP
fi

# show info alert about sending the SQL statements to an online service
if [ ! -e infoShowed ]; then
    touch infoShowed
    osascript -e 'tell app "Sequel Pro" to display dialog "This command will send the SQL statement(s) - unencrypted - to an online service (http://www.dpriver.com).  Please consider this before sending confidential data!\n\nThis message will only be displayed once." with icon caution' 2> 1
    if [ `cat 1 | wc -c` -ne 0 ]; then
        rm -f 1
        exit $SP_BUNDLE_EXIT_NONE
    fi
    rm -f 1
fi

# send SQL to www.dpriver.com and replace the formatted SQL string in first responder; if a parser error occurred
# show error message and try to jump to the error
cat <<HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <base href="http://www.dpriver.com/pp/">
    <title>Connecting www.dpriver.com</title>
    <script>
        function lookForResult() {
            if(document.all.outputsql.value.length > 8) {
                if(document.all.errorhint.style.display == "inline") {
                    var errormes = document.all.outputsql.value;
                    var re = /.*?\(\d+\s*,\s*(\d+)\).*/;
                    re.exec(errormes);
                    var pos = parseInt(RegExp.\$1) - 1;
                    re = /.*?(\d+).*/;
                    re.exec('$SP_SELECTED_TEXT_RANGE');
                    var offset = parseInt(RegExp.\$1);
                    pos = pos + offset;
                    alert(document.all.outputsql.value);
                    window.system.setSelectedTextRange(pos+'');
                } else {
                    var txt = document.all.outputsql.value;
                    var len = txt.length;
                    window.system.setSelectedTextRange('$SP_SELECTED_TEXT_RANGE');
                    window.system.insertText(txt.slice(0,len-2));
                }
                window.system.closeHTMLOutputWindow();
            } else {
                setTimeout("lookForResult()",50);
            }
        }

        function waitForResult() {
            window.system.suppressExceptionAlert();
            setTimeout("lookForResult()",50);
        }
    </script>
    <script language="JavaScript" type="text/javascript" src="pp.js"></script>
</head>
<body onload='window.resizeTo(300,200);SQLFMT.format(document.all.sp_submit);waitForResult()'>
<div id="retvalues"></div>
<p><b>Copyright &copy; 2001-2010 Gudu Software<br>All Rights Reserved<br><a href="mailto:support@dpriver.com">Contact US</a></b></p>
<br><br><br><br><br>
<p align=right>Please wait…</p>
<div class="page-container-1" style="display:none;">
    <div id="container">
        <div id="wrapper">
            <div id="content">
                <form id="SqlFmtForm" name="frm_sqlformat" method="post" action="/cgi-bin/ppserver" onsubmit="return false;">
                    <div id="secondpanel">
                        <div id="sp_database">
                            <label class="desc" style="display:inline;">Database</label>
                            <select name="dbvendor">
                                <option value="mysql" selected>MySQL</option>
                            </select>
                            <label class="desc" style="display:inline;padding:0 0 0px 40px; border:0px solid;">Output:</label>
                            <select name="outputfmt">
                                <option value="SQL" selected>SQL(Text)</option>
                            </select>
                        </div>
                    </div>
                    <div id="sqlpanel">
                        <div id="sp_inputsql">
                            <textarea id ="inputsql" name="inputsql" cols="1" rows="1" wrap="off">
                            $SQL
                            </textarea>
                        </div>
                        <div id="sp_submit">
                            <input type="button" id="btnformat" name="print" value="Format SQL" onclick="SQLFMT.format(this)" value="Format SQL">
                            <label style="display:none;">(Time used: <span id="timestamp">0</span> seconds)</label>
                        </div>
                        <div id="sp_submit" style="text-align:left;">
                        </div>
                        <label id="errorhint" class="desc" style="color:#DF0000 !important;display:none;">
                            Can't format input sql, make sure there is no syntax error and select correct database.
                            <a href="" style="display:none;">OR Report a bug of this sql beautifier</a>
                        </label>
                        <iframe id="ioutputsql" ></iframe>
                        <div id="sp_outputsql" style="display:none;">
                            <textarea id = "outputsql" name="outputsql" cols="80" rows="15" wrap="off" >
                            </textarea>
                        </div>
                        <div id="sp_errormsg" style="display:none;">
                            Error message goes to here!
                        </div>
                    </div>
                    <div id="formatoptions">
                        <select name="keywordcs">
                            <option value="Uppercase" selected >Uppercase</option>
                            <option value="Lowercase" >Lowercase</option>
                            <option value="InitCap" >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                        </select>
                        <select name="identifiercs">
                            <option value="Uppercase" >Uppercase</option>
                            <option value="Lowercase" selected >Lowercase</option>
                            <option value="InitCap" >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                            <option value="initcapeachword" >Init cap each word</option>
                        </select>
                        <select name="functioncs">
                            <option value="Uppercase" >Uppercase</option>
                            <option value="Lowercase" >Lowercase</option>
                            <option value="InitCap" selected >InitCap</option>
                            <option value="Unchanged" >Unchanged</option>
                        </select>
                        <input type="radio" name="lnbrwithcomma" value="after" checked > After
                        <input type="radio" name="lnbrwithcomma" value="before" > Before
                        <input type="radio" name="lnbrwithcomma" value= "beforewithspace" > Before with space
                        <input type="radio" name="liststyle" value="stack" checked>Stacked
                        <input type="radio" name="liststyle" value="nostack">Not Stacked
                        <input type="radio" name="salign" value="sleft" checked> Align left
                        <input type="radio" name="salign" value="sright" > Align right
                        <input type="checkbox" name="andorunderwhere" value="yes" checked> And/Or under Where
                        <input type="checkbox" name="removelnbr" value="no" checked> Remove Linebreak before beautify
                        <input type="checkbox" name="trimquotechar" value="yes"> Trim Quoted Char of Each Line quoted char of eachline: <input type="text" name="quotechar" value= "&#34" size = 5 >
                        <input type="checkbox" name="compactmode" value="yes"> Compact the output of sql output
                        <input type="text" name="maxlenincm" value= "80" size = 5 >
                    </div>
                    <input type="hidden" name="clientid" value="dpriver-9094-8133-2031" />
                </form>
            </div>
        </div>
    </div>
</div>

</body>
</html>
HTML
@Greenek

This comment has been minimized.

Greenek commented Sep 2, 2015

I made a pull request with fixed command:

# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# check if connected to the internet and if server is up
connected=$(/sbin/ping -t 8 -c 1 -on www.dpriver.com 2> /dev/null | grep '69\.5\.11\.169' | wc -l)
if [ $connected -eq "0" ]; then
    echo "<font color=red>You are probably not connected to the internet or http://www.dpriver.com/ server is down.</font>"
    exit $SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP
fi

# show info alert about sending the SQL statements to an online service
if [ ! -e infoShowed ]; then
    touch infoShowed
    osascript -e 'tell app "Sequel Pro" to display dialog "This command will send the SQL statement(s) - unencrypted - to an online service (http://www.dpriver.com).  Please consider this before sending confidential data!\n\nThis message will only be displayed once." with icon caution' 2> 1
    if [ `cat 1 | wc -c` -ne 0 ]; then
        rm -f 1
        exit $SP_BUNDLE_EXIT_NONE
    fi
    rm -f 1
fi

# send SQL to www.dpriver.com and replace the formatted SQL string in first responder; if a parser error occurred
# show error message and try to jump to the error
cat <<HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<base href="http://www.dpriver.com/pp/">
<title>Connecting www.dpriver.com</title>
<script>
        function lookForResult() {
            if(document.all.outputsql.value.length > 8) {
                if(document.all.errorhint.style.display == "inline") {
                    var errormes = document.all.outputsql.value;
                    var re = /.*?\(\d+\s*,\s*(\d+)\).*/;
                    re.exec(errormes);
                    var pos = parseInt(RegExp.\$1) - 1;
                    re = /.*?(\d+).*/;
                    re.exec('$SP_SELECTED_TEXT_RANGE');
                    var offset = parseInt(RegExp.\$1);
                    pos = pos + offset;
                    alert(document.all.outputsql.value);
                    window.system.setSelectedTextRange(pos+'');
                } else {
                    var txt = document.all.outputsql.value;
                    var len = txt.length;
                    window.system.setSelectedTextRange('$SP_SELECTED_TEXT_RANGE');
                    window.system.insertText(txt.slice(0,len-2));
                }
                window.system.closeHTMLOutputWindow();
            } else {
                setTimeout("lookForResult()",50);
            }
        }
        function waitForResult() {
            window.system.suppressExceptionAlert();
            setTimeout("lookForResult()",50);
        }
    </script>
<script language="JavaScript" type="text/javascript" src="pp.js"></script>
</head>
<body onload='window.resizeTo(300,200);SQLFMT.format(document.all.sp_submit);waitForResult()'>
<div id="retvalues">
</div>
<p>
    <b>Copyright &copy; 2001-2010 Gudu Software<br>
    All Rights Reserved<br>
    <a href="mailto:support@dpriver.com">Contact US</a></b>
</p>
<br>
<br>
<br>
<br>
<br>
<p align=right>
    Please wait…
</p>
<div class="page-container-1" style="display:none;">
    <div id="container">
        <div id="wrapper">
            <div id="content">
                <form id="SqlFmtForm" name="frm_sqlformat" method="post" action="/cgi-bin/ppserver" onsubmit="return false;">
                    <div id="secondpanel">
                        <div id="sp_database">
                            <label class="desc" style="display:inline;">Database</label>
                            <select name="dbvendor">
                                <option value="mysql" selected>MySQL</option>
                            </select>
                            <label class="desc" style="display:inline;padding:0 0 0px 40px; border:0px solid;">Output:</label>
                            <select name="outputfmt">
                                <option value="SQL" selected>SQL(Text)</option>
                            </select>
                        </div>
                    </div>
                    <div id="sqlpanel">
                        <div id="sp_inputsql">
                            <textarea id="inputsql" name="inputsql" cols="80" rows="15" wrap="off">$SQL</textarea>
                        </div>
                        <div id="sp_submit">
                            <input type="button" id="btnformat" name="print" value="Format SQL" onclick="SQLFMT.format(this)">
                            <label style="display:none;">(Time used: <span id="timestamp">0</span> seconds)</label>
                        </div>
                        <div id="sp_submit" style="text-align:left;">

                        </div>
                        <label id="errorhint" class="desc" style="color:#DF0000 !important;display:none;">
                        Can't format input sql, make sure there is no syntax error and select correct database. <a href="" style="display:none;">OR Report a bug of this sql beautifier</a>
                        </label>
                        <iframe id="ioutputsql">
                        </iframe>
                        <div id="sp_outputsql" style="display:none;">
                            <textarea id="outputsql" name="outputsql" cols="80" rows="15" wrap="off"></textarea>
                        </div>
                        <div id="sp_errormsg" style="display:none;">
                             Error message goes to here!
                        </div>
                    </div>
                    <div id="formatoptions">
                        <label class="desc">Keywords case:</label>
                        <select name="keywordcs">
                            <option value="Uppercase" selected="">Uppercase</option>
                            <option value="Lowercase">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Table name case:</label>
                        <select name="tablenamecs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase" selected="">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Column name case:</label>
                        <select name="columnnamecs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase" selected="">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Function case:</label>
                        <select name="functioncs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase">Lowercase</option>
                            <option value="InitCap" selected="">InitCap</option>
                            <option value="Unchanged">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Datatype case:</label>
                        <select name="datatypecs">
                            <option value="Uppercase" selected="">Uppercase</option>
                            <option value="Lowercase">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Variable case:</label>
                        <select name="variablecs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged" selected="">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Alias case:</label>
                        <select name="aliascs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged" selected="">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Quoted identifier case:</label>
                        <select name="quotedidentifiercs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged" selected="">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <label class="desc">Other identifier case:</label>
                        <select name="identifiercs">
                            <option value="Uppercase">Uppercase</option>
                            <option value="Lowercase" selected="">Lowercase</option>
                            <option value="InitCap">InitCap</option>
                            <option value="Unchanged">Unchanged</option>
                            <option value="initcapeachword">Init cap each word</option>
                        </select>
                        <hr>
                        <label class="desc">Linebreaks with comma:</label>
                        <input type="radio" name="lnbrwithcomma" value="after" checked=""> After <input type="radio" name="lnbrwithcomma" value="before"> Before <input type="radio" name="lnbrwithcomma" value="beforewithspace"> Before with space
                        <hr>
                        <label class="desc">List and Parameters Style:</label>
                        <input type="radio" name="liststyle" value="stack" checked=""> Stacked <input type="radio" name="liststyle" value="nostack"> Not Stacked
                        <hr>
                        <label class="desc"> Stacked align:</label>
                        <input type="radio" name="salign" value="sleft" checked=""> Align left <input type="radio" name="salign" value="sright"> Align right
                        <hr>
                        <label class="desc">And/Or under Where Clause:</label>
                        <input type="checkbox" name="andorunderwhere" value="yes"> And/Or under Where
                        <hr>
                        <label class="desc">Remove linebreak before beautify:</label>
                        <input type="checkbox" name="removelnbr" value="yes"> Remove Linebreak before beautify
                        <hr>
                        <label class="desc">Trim Quoted Char of Each Line:</label>
                        <input type="checkbox" name="trimquotechar" value="yes"> Trim Quoted Char of Each Line quoted char of eachline: <input type="text" name="quotechar" value="&quot;" size="5">
                        <hr>
                        <label class="desc"> Compact mode:</label>
                        <input type="checkbox" name="compactmode" value="yes"> Compact the output of sql output <label class="desc">Max length per line in compact mode:</label>
                        <input type="text" name="maxlenincm" value="80" size="5">
                    </div>
                    <input type="hidden" name="clientid" value="dpriver-9094-8133-2031">
                </form>
            </div>
        </div>
    </div>
</div>
</body>
</html>
HTML
@xurizaemon

This comment has been minimized.

xurizaemon commented Sep 30, 2015

What a shame that the PR above didn't get included in 1.1. I rate this over coloured DB names ...

@sequelpro @sqlparser how could the community better help you get fixes like this into the release? (genuine question not PASV - do you need people to review it? to rate it so it's on the radar? would a bunch of 👍 comments help you know what's important to users?)

EDIT: Ah ... last release was 2013 so prob quite a backlog, and I see there were RCs over the last few weeks, so maybe testing RCs and making a bit of noise for its inclusion would have been the ticket?

@dmoagx

This comment has been minimized.

Member

dmoagx commented Sep 30, 2015

We didn't include this as the owner of the service made some requests to us about how we should rather implement it.
I would have to dig through my mails to find that, get familiar with the code and then compare it to the proposed fixes.
On the other hand I always found it rather strange that we use a webservice for something essential as SQL formatting, since we already have the most difficult part, the lexer, implemented for syntax highlighting anyway.

@xurizaemon

This comment has been minimized.

xurizaemon commented Sep 30, 2015

Thanks.

If you do find those emails and share the service owner's request here, maybe that's something the community could contribute towards?

Totally agree re the webservice vs internal formatter, that service would have a treasure trove of sensitive data passing through it. But for informed use, I do really appreciate having the feature there for legibility, even as I struggle with the way it capitalizes function names :)

@dmoagx

This comment has been minimized.

Member

dmoagx commented Sep 30, 2015

Good thing I keep stuff around.
Since the mail doesn't contain much personal info I'll just go ahead:

(...) Recently I received several emails that mentioned Sequel Pro using sql formatter for SQL formatting/beautification. Since our online sql formatter is free, so you
are welcome to use this service.

I email to you because this online service (http://www.dpriver.com/cgi-bin/ppserver) is not built for public usage and will change interfaces recently. In order to make sure sql formatting feature in Sequel Pro working correctly, I suggest you to use our new public SQL FaaS(Formatter as a Service), detailed document is here:

https://github.com/sqlparser/sql-pretty-printer/wiki/SQL-FaaS#2-api

Of course, this service is FREE as usually.

Please reply this email if you are going to use this new service and make sure set rqst_isf_client to "Sequel Pro" when sending request.
(...)

@xurizaemon

This comment has been minimized.

xurizaemon commented Oct 1, 2015

# check for empty STDIN
SQL=$(cat)
if [ -z "$SQL" ]; then
    echo "No SQL statements passed."
    exit $SP_BUNDLE_EXIT_SHOW_AS_TEXT_TOOLTIP
fi

# check if connected to the internet and if server is up
connected=$(/sbin/ping -t 8 -c 1 -on www.dpriver.com 2> /dev/null | grep '69\.5\.11\.169' | wc -l)
if [ $connected -eq "0" ]; then
    echo "<font color=red>You are probably not connected to the internet or http://www.dpriver.com/ server is down.</font>"
    exit $SP_BUNDLE_EXIT_SHOW_AS_HTML_TOOLTIP
fi

# show info alert about sending the SQL statements to an online service
if [ ! -e infoShowed ]; then
    touch infoShowed
    osascript -e 'tell app "Sequel Pro" to display dialog "This command will send the SQL statement(s) - unencrypted - to an online service (http://www.dpriver.com).  Please consider this before sending confidential data!\n\nThis message will only be displayed once." with icon caution' 2> 1
    if [ `cat 1 | wc -c` -ne 0 ]; then
        rm -f 1
        exit $SP_BUNDLE_EXIT_NONE
    fi
    rm -f 1
fi

RESULT=$( curl -s --data-urlencode "rqst_input_sql=$SQL" http://www.gudusoft.com/format.php )
RESULT=$( echo $RESULT | sed -e 's#.*"rspn_formatted_sql":"##g' )
RESULT=$( echo $RESULT | sed -e 's#"\}##g' )
echo $RESULT

A quick proof of concept - I'll come back to this but there's work to do :)

In Bundle Editor, "Output" becomes "Replace Entire Content".

Better would be to debug JSON result properly, but IDK if OSX ships with a JSON parser for the CLI? Above gets messed up by some JSON escaping (eg WHERE foo="x" => WHERE foo=\"x\").

Hmm, rereading email, should probably be

RESULT=$( curl -s --data-urlencode "rqst_isf_client=Sequel+Pro&rqst_input_sql=$SQL" http://www.gudusoft.com/format.php )
@Bibiko

This comment has been minimized.

Collaborator

Bibiko commented Oct 1, 2015

On 01 Oct 2015, at 01:49, Max Lohrmann notifications@github.com wrote:

On the other hand I always found it rather strange that we use a webservice for something essential as SQL formatting, since we already have the most difficult part, the lexer, implemented for syntax highlighting anyway.

Maybe a good starting point could be to use http://sourceforge.net/projects/fsqlf/ . I was able to compile the CLI on a Mac without problems. The licence is LGPLv2 and it seems that this tool is highly configurable.

Example:

SELECT p1.PLAYERID as ';test;', f1.PLAYERNAME, p2.PLAYERID, f2.PLAYERNAME FROM PLAYER f1, PLAYER f2, PLAYS p1 FULL OUTER JOIN PLAYS p2 ON p1.PLAYERID < p2.PLAYERID AND p1.TEAMID = p2.TEAMID GROUP BY p1.PLAYERID, f1.PLAYERID, p2.PLAYERID, f2.PLAYERID HAVING Count(p1.PLAYERID) = Count() AND Count(p2.PLAYERID) = Count() AND p1.PLAYERID = f1.PLAYERID AND p2.PLAYERID = f2.PLAYERID; select count(*) from table0815;

will be outputted as:

SELECT
p1.PLAYERID AS ';test;'
, f1.PLAYERNAME
, p2.PLAYERID
, f2.PLAYERNAME
FROM PLAYER f1
, PLAYER f2
, PLAYS p1
FULL JOIN PLAYS p2
ON p1.PLAYERID<p2.PLAYERID
AND p1.TEAMID=p2.TEAMID
GROUP BY p1.PLAYERID , f1.PLAYERID , p2.PLAYERID , f2.PLAYERID
HAVING Count ( p1.PLAYERID )=Count ()
AND Count ( p2.PLAYERID )=Count (
)
AND p1.PLAYERID=f1.PLAYERID
AND p2.PLAYERID=f2.PLAYERID
;

SELECT
count (*)
FROM table0815
;

without any configuration.

Cheers, Hans

@eric3zhao

This comment has been minimized.

eric3zhao commented Feb 19, 2016

THX @minaevd,your answer is useful

@tristar500

This comment has been minimized.

tristar500 commented Feb 21, 2016

Just reformatting the answer

Change line 149 from

<input type="hidden" name="clientid" value="4149-9094-8133-2031" />

to

<input type="hidden" name="clientid" value="dpriver-9094-8133-2031" />

@Miki387

This comment has been minimized.

Miki387 commented Nov 26, 2016

It is the way I tried

#!/usr/bin/php
<?php
$url = 'http://www.gudusoft.com/format.php';
$sql = '';
while (($line = fgets(STDIN))) {
	$line = str_replace(array("\r\n","\r","\n"), ' ', $line);
	$sql .= $line;
}
$query = ['rqst_input_sql'=>$sql];

$response = file_get_contents($url . '?' . http_build_query($query));
$response = substr($response, 1, -1);

$result = json_decode($response,true);
echo $result['rspn_formatted_sql'];

Sequel Pro 1.1.2

@neokio

This comment has been minimized.

neokio commented Mar 14, 2017

Is there a working offline bundle yet? I can't find anything that works.

For anyone interested in a great online solution, here's the code to get things working with the sqlformat.org API. It uses cURL with SSL and POST, much more secure than the other alternatives.

#!/usr/bin/php
<?php
$sql = @file_get_contents("php://stdin");
$request = ['reindent'=>1,'sql'=>$sql];
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://sqlformat.org/api/v1/format');
curl_setopt($ch, CURLOPT_POST, count($request));
curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($request));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$result = curl_exec($ch);
curl_close($ch);
echo json_decode($result,true)['result'];

For those inexperienced in bundle editing (myself included), here's what your edited bundle should look like (note that I changed the keyboard shortcut to CMD-OPT-Z ... much easier than the Chopin-fingered CMD-OPT-CTRL-T):

screen shot 2017-03-14 at 8 11 05 pm

From the Query editor, just select your SQL, hit CMD-OPT-Z, and a second later it's replaced with formatted code.

@abhibeckert abhibeckert added Bug and removed Priority-High labels Mar 14, 2017

@dtunes

This comment has been minimized.

dtunes commented Aug 16, 2018

Maybe the following could also work "haven't tried in sequelpro yet".

install sqlparse

pip install sqlpase

then new bundle

#!/usr/bin/env python

import sys
import sqlparse

sql = sys.stdin.readlines()

print sqlparse.format(sql, reindent=True, keyword_case='upper')
@peh

This comment has been minimized.

peh commented Aug 17, 2018

@dtunes pretty close but readlines() is returning a list

#!/usr/local/bin/python

import sys
import sqlparse

sql = sys.stdin.readlines()

print sqlparse.format("".join(sql), reindent=True, keyword_case='upper')

is working perfectly fine for selections or entire editor content

@dtunes

This comment has been minimized.

dtunes commented Aug 19, 2018

@dtunes

This comment has been minimized.

dtunes commented Aug 21, 2018

@peh great man, have't used python in a while... could not remember the api... was a shot in the dark!

Cheers for fixing it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment