NAME

pgFormatter::Beautify - Library for pretty-printing SQL queries

VERSION

Version 5.2

SYNOPSIS

This module can be used to reformat given SQL query, optionally anonymizing parameters.

Output can be either plain text, or it can be HTML with appropriate styles so that it can be displayed on a web page.

Example usage:

    my $beautifier = pgFormatter::Beautify->new();
    $beautifier->query( 'select a,b,c from d where e = f' );

    $beautifier->beautify();
    my $nice_txt = $beautifier->content();

    $beautifier->format('html');
    $beautifier->beautify();
    my $nice_html = $beautifier->content();

    $beautifier->format('html');
    $beautifier->anonymize();
    $beautifier->beautify();
    my $nice_anonymized_html = $beautifier->content();

    $beautifier->format();
    $beautifier->beautify();
    $beautifier->wrap_lines()
    my $wrapped_txt = $beautifier->content();

FUNCTIONS

new

Generic constructor - creates object, sets defaults, and reads config from given hash with options.

Takes options as hash. Following options are recognized:

break - String that is used for linebreaks. Default is "\n".
colorize - if set to false CSS style will not be applied to html output. Used internally to display errors in CGI mode withour style.
comma - set comma at beginning or end of a line in a parameter list

end - put comma at end of the list (default)
start - put comma at beginning of the list

comma_break - add new-line after each comma in INSERT statements
format - set beautify format to apply to the content (default: text)

text - output content as plain/text (command line mode default)
html - output text/html with CSS style applied to content (CGI mode default)

functions - list (arrayref) of strings that are function names
keywords - list (arrayref) of strings that are keywords
multiline - use multi-line search for placeholder regex, see placeholder.
no_comments - if set to true comments will be removed from query
no_grouping - if set to true statements will not be grouped in a transaction, an extra newline character will be added between statements like outside a transaction.
placeholder - use the specified regex to find code that must not be changed in the query.
query - query to beautify
rules - hash of rules - uses rule semantics from SQL::Beautify
space - character(s) to be used as space for indentation
spaces - how many spaces to use for indentation
uc_functions - what to do with function names:

0 - do not change
1 - change to lower case
2 - change to upper case
3 - change to Capitalized

separator - string used as dynamic code separator, default is single quote
uc_keywords - what to do with keywords - meaning of value like with uc_functions
uc_types - what to do with data types - meaning of value like with uc_functions
wrap - wraps given keywords in pre- and post- markup. Specific docs in SQL::Beautify
format_type - try an other formatting
wrap_limit - wrap queries at a certain length
wrap_after - number of column after which lists must be wrapped
wrap_comment - apply wrapping to comments starting with --
numbering - statement numbering as a comment before each query
redshift - add Redshift keywords
no_extra_line - do not add an extra empty line at end of the output
keep_newline - preserve empty line in plpgsql code

For defaults, please check function set_defaults.

query

Accessor to query string. Both reads:

    $object->query()

, and writes

    $object->query( $something )

content

Accessor to content of results. Must be called after $object->beautify().

This can be either plain text or html following the format asked by the client with the $object->format() method.

highlight_code

Makes result html with styles set for highlighting.

tokenize_sql

Splits input SQL into tokens

Code lifted from SQL::Beautify

beautify

Beautify SQL.

After calling this function, $object->content() will contain nicely indented result.

Code lifted from SQL::Beautify

_add_token

Add a token to the beautified string.

Code lifted from SQL::Beautify

_over

Increase the indentation level.

Code lifted from SQL::Beautify

_back

Decrease the indentation level.

Code lifted from SQL::Beautify

_indent

Return a string of spaces according to the current indentation level and the spaces setting for indenting.

Code lifted from SQL::Beautify

_new_line

Add a line break, but make sure there are no empty lines.

Code lifted from SQL::Beautify

_next_token

Have a look at the token that's coming up next.

Code lifted from SQL::Beautify

_token

Get the next token, removing it from the list of remaining tokens.

Code lifted from SQL::Beautify

_is_keyword

Check if a token is a known SQL keyword.

Code lifted from SQL::Beautify

_is_type

Check if a token is a known SQL type

_is_comment

Check if a token is a SQL or C style comment

_is_function

Check if a token is a known SQL function.

Code lifted from SQL::Beautify and rewritten to check one long regexp instead of a lot of small ones.

add_keywords

Add new keywords to highlight.

Code lifted from SQL::Beautify

_re_from_list

Create compiled regexp from prefix, suffix and and a list of values to match.

_refresh_functions_re

Refresh compiled regexp for functions.

add_functions

Add new functions to highlight.

Code lifted from SQL::Beautify

add_rule

Add new rules.

Code lifted from SQL::Beautify

_get_rule

Find custom rule for a token.

Code lifted from SQL::Beautify

_process_rule

Applies defined rule.

Code lifted from SQL::Beautify

_is_constant

Check if a token is a constant.

Code lifted from SQL::Beautify

_is_punctuation

Check if a token is punctuation.

Code lifted from SQL::Beautify

_generate_anonymized_string

Simply generate a random string, thanks to Perlmonks.

Returns original in certain cases which don't require anonymization, like timestamps, or intervals.

anonymize

Anonymize litteral in SQL queries by replacing parameters with fake values

set_defaults

Sets defaults for newly created objects.

Currently defined defaults:

spaces => 4
space => ' '
break => "\n"
uc_keywords => 2
uc_functions => 0
uc_types => 1
no_comments => 0
no_grouping => 0
placeholder => ''
multiline => 0
separator => ''
comma => 'end'
format => 'text'
colorize => 1
format_type => 0
wrap_limit => 0
wrap_after => 0
wrap_comment => 0
no_extra_line => 0
keep_newline => 0

format

Set output format - possible values: 'text' and 'html'

Default is text output. Returns 0 in case or wrong format and use default.

set_dicts

Sets various dictionaries (lists of keywords, functions, symbols, and the like)

This was moved to separate function, so it can be put at the very end of module so it will be easier to read the rest of the code.

_remove_dynamic_code

Internal function used to hide dynamic code in plpgsql to the parser. The original values are restored with function _restore_dynamic_code().

_restore_dynamic_code

Internal function used to restore plpgsql dynamic code in plpgsql that was removed by the _remove_dynamic_code() method.

_quote_operator

Internal function used to quote operator with multiple character to be tokenized as a single word. The original values are restored with function _restore_operator().

_restore_operator

Internal function used to restore operator that was removed by the _quote_operator() method.

_quote_comment_stmt

Internal function used to replace constant in a COMMENT statement to be tokenized as a single word. The original values are restored with function _restore_comment_stmt().

_restore_comment_stmt

Internal function used to restore comment string that was removed by the _quote_comment_stmt() method.

_remove_comments

Internal function used to remove comments in SQL code to simplify the work of the wrap_lines. Comments must be restored with the _restore_comments() method.

_restore_comments

Internal function used to restore comments in SQL code that was removed by the _remove_comments() method.

wrap_lines

Internal function used to wrap line at a certain length.

AUTHOR

pgFormatter is an original work from Gilles Darold

BUGS

Please report any bugs or feature requests to: https://github.com/darold/pgFormatter/issues

COPYRIGHT

LICENSE

pgFormatter is free software distributed under the PostgreSQL Licence.