NAME
    Mysql::PrettyPrinter - A lean, efficient SQL pretty-printer for MySQL

VERSION
    Version 0.10

SYNOPSIS
    The pretty-printer uses capitalisation, line-breaks, and indentation to
    reformat the given chunk of SQL into a standardised form.

        $formatted = Mysql::PrettyPrinter->format(sql => $sql);

    or

        my $pp = Mysql::PrettyPrinter->new;
        $pp->sql($sql);
        $pp->add_sql($sql2);
        $pp->make_tokens;
        $pp->add_tokens($tok0, $tok1, $tok2);
        # ...do some processing on @{$pp->{tokens}}
        my $output = $pp->format(indent => '  ');

    or

        mysqlbinlog /data/log/binlog.001 \
            | head -n 40 \
            | perl -MMysql::PrettyPrinter \
                -e'Mysql::PrettyPrinter->passthrough' \
            | a2ps

DESCRIPTION
    Very simple-minded pretty-printer for MySQL SQL. It gets 'almost there'
    results with terse efficient code. It sets line-breaks and
    probably-appropriate indentation, and optionally wraps token classes in
    markup.

    If your requirements are more sophisticated, this class will never do
    the whole job for you, however you might get a lot of mileage from using
    this class as a pre-processor, extracting the tokens from $pp->tokens
    (and never calling $pp->format).

METHODS
    new(wordsep => ' ', linesep => "\n", indent => ' ', wrap => undef, sql
    => $sql)
        Constructor. If you're only invoking format once then you can
        probably skip the constructor. On the other hand, if you want to
        construct the object once and use it many times, you can set your
        default options here.

        wordsep
            Character or string to use as a word separator, default: ' '.

        linesep
            Character or string to use as a line separator, default: "\n".
            You may want to specify "\r\n" for Windows, or "\n\r" for old
            Mac.

        indent
            String equating to one level of indentation, default: wordsep x
            4. You may want to specify "\t" if that suits your coding
            standard.

        wrap
            Markup for wrapping token classes, default: undef. The example
            from SQL::Beautify is

                wrap => { keyword => [ "\x1B[0;31m", "\x1B[0m" ] }

            which will make keywords red on some terminals.

            Another example is

                wrap => { keyword => [ '<div class="keyword">', '</div>' ] }

            which will wrap keywords in HTML+CSS markup.

            If you specify a hash for wrap, you must specify a pair of
            strings for one or more of the token classes: keyword, function,
            literal. (If you want to omit one half of the wrapping pair, use
            an empty string in its place.)

        sql The SQL string to be formatted. Useful if you want to manipulate
            the SQL or token list before calling format, or you are going to
            invoke format several times on the same SQL string. Otherwise
            just specify it when calling format.

    sql($sql)
        Another place for specifying the string to be formatted. Useful if
        you want to re-use the same object with different SQL.

        Returns the current SQL string if invoked without argument.

    add_sql($sql)
        Lets you append a string before lexical analysis. Useful if you want
        to build up SQL in more than one pass.

    make_tokens(sql => $sql)
        Invokes lexical analysis. An additional place where you can specify
        the SQL. Useful if you want to examine or manipulate tokens before
        formatting.

    tokens(@tokens)
        Lets you specify your own list of tokens, perhaps a result of your
        own lexical analysis or a result of manipulation you performed on
        the previous list.

        Returns the current token list if invoked without argument.
        Relevance depends on you invoking make_tokens or tokens beforehand.

    add_tokens(@tokens)
        Lets you append a list of tokens to the current token list.
        Usefulness depends on you invoking make_tokens or tokens beforehand.

    format(wordsep => ' ', linesep => "\n", indent => ' ', wrap => undef,
    sql => $sql)
        Returns the formatted output string. Takes same arguments as new.
        Can be invoked as a class method, which is very convenient in the
        simple (most common) use case.

            my $pp = Mysql::PrettyPrinter->new;
            print $pp->format(sql => 'SELECT 1 + 1');

        or

            print Mysql::PrettyPrinter->format(sql => 'SELECT 1 + 1');

    lexicals($sql, $omit_whitespace_tokens)
        Utility method for lexical analysis, returning the list of lexical
        tokens from a given string of SQL. This is not specific to MySQL,
        and is only a minor performance enhancement over SQL::Tokenizer. A
        true value for $omit_whitespace_tokens causes it to strip out tokens
        of pure whitespace such as blank lines.

    passthrough(%options)
        A convenience method for invoking format from the commandline. It's
        arguable whether the example at the top of these notes is any more
        convenient than using

            | perl -MMysql::PrettyPrinter -e'BEGIN{$/=q{}}
                print Mysql::PrettyPrinter->format(%options, sql => <>)'

        One advantage is it avoids issues when using double quotes.

INVOCATION STYLE
    Most methods support method chaining. So you can invoke

        print Mysql::PrettyPrinter->new
        ->sql('SELECT')->add_sql('1 + 1')
        ->make_tokens->tokens('SELECT')->add_tokens('2', '+', '2')
        ->format

    The exceptions are when used as getters:

        my $sql = $pp->sql;
        my @tokens = $pp->tokens;

LIMITATIONS
    The strengths of this module are simplicity and speed. The main weakness
    is that it does very little semantic processing. So the "IF" in "DROP
    TABLE IF EXISTS" is treated the same as in "SELECT IF (...".

    The second weakness is it provides little flexibility on style. You can
    specify strings for line-break and word-break, what string equates to a
    'tab', and whether keywords, functions, and constants should be wrapped
    in any markup, but that's about it. You cannot say "I want to align on
    neighbouring 'ON'/'AS'/comparitor". The simplicity of its approach means
    it can be adapted easily to other purposes, eg modify the keywords to
    let it handle oracle instead, eg modify its I/O so it can process on a
    pipeline with minimal lookahead and buffering.

    Ideas for future enhancements include:

    It should be extended to handle comments, both in indentation and
    markup. It should provide more output options, eg PHP, Perl, HTML+CSS.
    It could provide smart processing, eg remove redundant parentheses. If
    the list of options grows, it would be nice to support having a
    configuration file.

AUTHOR
    Nic Sandfield, "<niczero at cpan.org>"

BUGS
    It has no unit tests.

    Please report bugs, issues, or feature requests to
    "bug-mysql-prettyprinter at rt.cpan.org", or through the web interface
    at <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Mysql-PrettyPrinter>.
    I will be notified and then you'll automatically be notified of progress
    on your bug as I make changes.

INSTALLATION
    To install this module from its CPAN source package, run the following
    commands:

            perl Makefile.PL
            make
            make test
            make install

SUPPORT
    You can find documentation for this module with the perldoc command.

        perldoc Mysql::PrettyPrinter

    You can also look for information at:

    *   RT: CPAN's request tracker

        <http://rt.cpan.org/NoAuth/Bugs.html?Dist=Mysql-PrettyPrinter>

    *   AnnoCPAN: Annotated CPAN documentation

        <http://annocpan.org/dist/Mysql-PrettyPrinter>

    *   CPAN Ratings

        <http://cpanratings.perl.org/d/Mysql-PrettyPrinter>

    *   Search CPAN

        <http://search.cpan.org/dist/Mysql-PrettyPrinter/>

ACKNOWLEDGEMENTS
    SQL::Beautify
        My module owes its existence to the fine beginnings in this module
        (C) 2009 Jonas Kramer. I merely fixed some bugs, extended the
        usability, made it more efficient, and specialised it to the syntax
        of MySQL (v5.1).

    SQL::Tokenizer
        The regular expression within lexicals was lifted from this module
        (C) 2010 Igor Sutton. I merely made it fractionally more efficient.

LICENCE AND COPYRIGHT
    Copyright 2010 Nic Sandfield. All rights reserved.

    This program is free software; you may use it, redistribute it, or
    modify it under the terms of the GNU General Public Licence (GPL) v3 (or
    any later version) as published by the Free Software Foundation.

    See <http://dev.perl.org/licenses/> or
    <http://www.gnu.org/licenses/license-list.html> for more information.