Message-ID: <1445849459.17392.68.camel@jlap3.macro.local>
Subject: SQL formatting
From: jarek <jarek@poczta.srv.pl>
To: pgadmin-hackers@postgresql.org
Date: Mon, 26 Oct 2015 09:50:59 +0100
Content-Type: multipart/mixed; boundary="=-WkrbRCGOrI/2MbeL/KjY"
Mime-Version: 1.0
Precedence: bulk
Sender: pgadmin-hackers-owner@postgresql.org


--=-WkrbRCGOrI/2MbeL/KjY
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Hello!

	First of all I'd like to thank all the pgAdmin developers for a great
job!
	As I like to have nice formatted SQL, I've written simple script in
python to format SQL procedures. It would be nice, to integrate it with
pgAdmin.
	This script doesn't cover all PostgreSQL structures, but is enough for
me. If there will be some interest, I can improve it.
	I think that it should be enough to add call to this script from SQL
query dialog menu. I've no experience with wxWidgets, so it may be
better if someone more skilled will integrate it.

best regards
Jarek Tabor


--=-WkrbRCGOrI/2MbeL/KjY
Content-Disposition: attachment; filename="sqlf.py"
Content-Type: text/x-python; name="sqlf.py"; charset="UTF-8"
Content-Transfer-Encoding: 8bit

#!/usr/bin/env python
# -*- coding: utf-8 -*-
#
# Copyright (c) 2014-2015 Jarosław Tabor <jarek@srv.pl>
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.


import sys
import re

indent_keywords = ['BEGIN', 'IF', 'FOR']
unindent_keywords = ['END']
uindent_once_keywords = ['ELSE']
indent_once = ['DECLARE']
indentsize = 4

function_re = re.compile('^CREATE[\ ]*(OR[\ ]*REPLACE[\ ]*)*FUNCTION')
function_body_re = re.compile('\$.*\$')
function_declare_re = re.compile('DECLARE')
function_begin_re = re.compile('BEGIN')
function_end_re = re.compile('END;')
function_endsc_re = re.compile(';')
if_re = re.compile('^IF')
endif_re = re.compile('^END IF')
else_re = re.compile('^(ELSE|ELSIF)')
for_re = re.compile('^(FOR.*LOOP|LOOP)')
endfor_re = re.compile('^END[\ ]*LOOP')
exception_re = re.compile('^EXCEPTION')
query_re = re.compile('^(SELECT|PERFORM|UPDATE|DELETE)')

def indent_print( _indent, _line ):
    out = ''
    if _line != '':
        if( _indent > 0 ):
            for i in range(_indent * indentsize):
                out = out + ' '
        print out + _line
    else:
        print

def main(_file):
    indent_level = 0
    function_body = 0
    query = 0
    with open(_file) as f:
        n = 0
        for line in f:
            n = n + 1
            out = ''
            line=line.strip()
            if function_re.match( line ): # function start
                function_body = 1
                indent_level = 1
                print line
                continue
            if function_body_re.match( line ): #function body
                if function_body==1:
                    function_body = 2
                elif function_body==5:
                    function_body = 6
                else:
                    sys.stderr.write('Unexpected $$ keyword in %d\n' % n )
                print line
                continue
            if function_declare_re.match( line ):
                if function_body == 2:            
                    function_body = 3
                else:
                    sys.stderr.write('Unexpected DECLARE keyword in %d\n' % n )
                print line
                continue
            if function_begin_re.match( line ):
                if function_body == 2 or function_body == 3:
                    function_body = 4
                    print line
                else:
                    indent_print(indent_level,line)
                    indent_level = indent_level + 1
                continue
            if function_end_re.match( line ):
                if function_body == 4:
                    if indent_level > 1:
                        indent_level = indent_level -1
                        indent_print(indent_level,line)
                        continue
                    else:
                        function_body = 5
                else:
                    sys.stderr.write('Unexpected END; keyword in %d\n' % n )
                print line
                continue
            if function_body == 6 and (';' in line):                
                function_body = 0
                indent_print(indent_level,line)
                indent_level = 0
                continue
            if if_re.match( line ):
                indent_print(indent_level,line)
                indent_level = indent_level + 1
                continue
            if else_re.match( line ):
                indent_print(indent_level-1,line)
                continue
            if exception_re.match( line ):
                indent_print(indent_level-1,line)
                continue
            if endif_re.match(line):
                if indent_level > 0:
                    indent_level = indent_level-1
            if for_re.match(line):
                indent_print(indent_level,line)
                indent_level = indent_level + 1
                continue
            if endfor_re.match(line):
                if indent_level > 0:
                    indent_level = indent_level-1
            if query_re.match(line):
                #print '*********query 1'
                if not ';' in line:
                    query = 1
                    indent_print(indent_level,line)
                    indent_level = indent_level + 1
                    continue
            if (';' in line) and (query>0):
                #print '*********query 0'
                if indent_level > 0:
                    query = query -1
                    indent_print(indent_level,line)
                    indent_level = indent_level-1
                    continue
            indent_print(indent_level,line)


if __name__ == "__main__":
    if len(sys.argv) > 1:
        main(sys.argv[1])
    else:
        sys.stderr.write('No input file provided.\n' )

--=-WkrbRCGOrI/2MbeL/KjY
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

--=-WkrbRCGOrI/2MbeL/KjY--