Scroll to navigation

SQL::Abstract::Tree(3) User Contributed Perl Documentation SQL::Abstract::Tree(3)

NAME

SQL::Abstract::Tree - Represent SQL as an AST

SYNOPSIS

 my $sqla_tree = SQL::Abstract::Tree->new({ profile => 'console' });
 print $sqla_tree->format('SELECT * FROM foo WHERE foo.a > 2');
 # SELECT *
 #   FROM foo
 #   WHERE foo.a > 2

METHODS

new

 my $sqla_tree = SQL::Abstract::Tree->new({ profile => 'console' });
 $args = {
   profile => 'console',      # predefined profile to use (default: 'none')
   fill_in_placeholders => 1, # true for placeholder population
   placeholder_surround =>    # The strings that will be wrapped around
              [GREEN, RESET], # populated placeholders if the above is set
   indent_string => ' ',      # the string used when indenting
   indent_amount => 2,        # how many of above string to use for a single
                              # indent level
   newline       => "\n",     # string for newline
   html_escape   => 0,        # HTML-escape the SQL text and the filled in
                              # placeholders (but not the strings above)
   colormap      => {
     select => [RED, RESET], # a pair of strings defining what to surround
                             # the keyword with for colorization
     # ...
   },
   indentmap     => {
     select        => 0,     # A zero means that the keyword will start on
                             # a new line
     from          => 1,     # Any other positive integer means that after
     on            => 2,     # said newline it will get that many indents
     # ...
     and           => 1,     # optional, see "Lines of their own" below
     or            => 1,
     ','           => 1,
   },
 }

Returns a new SQL::Abstract::Tree object. All arguments are optional.

Lines of their own

Besides clause keywords, the "indentmap" understands three more entries, none of which is set by the predefined profiles:

"and", "or"
Every further condition joined with "AND" (or "OR") starts a new line, indented by that many levels below its clause (or below the parenthesis it is in).
","
The items of a comma list right below a clause (the select list, "ORDER BY", "GROUP BY", "SET" ...) each get a line of their own, indented by that many levels below the clause. Lists further down, like function arguments or an "IN" list, stay on one line.

Merge them into a profile like any other override:

 my $sqlat = SQL::Abstract::Tree->new({
   profile   => 'console',
   indentmap => { and => 1, or => 1, ',' => 1 },
 });
 print $sqlat->format(
   'SELECT a, b FROM t WHERE x = ? AND y = ? ORDER BY a, b', [1, 2]
 );
 # SELECT
 #   a,
 #   b
 #   FROM t
 # WHERE x = 1
 #   AND y = 2
 # ORDER BY
 #   a,
 #   b

They only have an effect when a "newline" is set.

profiles

There are four predefined profiles, "none", "console", "console_monochrome", and "html". Typically a user will probably just use "console" or "console_monochrome", but if something about a profile bothers you, merely use the profile and override the parts that you don't like.

The "html" profile sets "html_escape", so its output can go straight into an HTML page: bind values and SQL text are escaped, the profile's own "<span>" and "<br />" markup is not.

format

 $sqlat->format('SELECT * FROM bar WHERE x = ?', [1])

Takes $sql and "\@bindargs".

Returns a formatting string based on the string passed in

parse

 $sqlat->parse('SELECT * FROM bar WHERE x = ?')

Returns a "tree" representing passed in SQL. Please do not depend on the structure of the returned tree. It may be stable at some point, but not yet.

unparse

 $sqlat->unparse($tree_structure, \@bindargs)

Transform "tree" into SQL, applying various transforms on the way.

format_keyword

 $sqlat->format_keyword('SELECT')

Currently this just takes a keyword and puts the "colormap" stuff around it. Later on it may do more and allow for coderef based transforms.

pad_keyword

 my ($before, $after) = @{$sqlat->pad_keyword('SELECT')};

Returns whitespace to be inserted around a keyword.

fill_in_placeholder

 my $value = $sqlat->fill_in_placeholder(\@bindargs)

Removes last arg from passed arrayref and returns it, surrounded with the values in placeholder_surround, and then surrounded with single quotes.

indent

Returns as many indent strings as indent amounts times the first argument.

ACCESSORS

colormap

See "new"

fill_in_placeholders

See "new"

html_escape

See "new" and "profiles"

indent_amount

See "new"

indent_string

See "new"

indentmap

See "new"

newline

See "new"

placeholder_surround

See "new"

2026-11-06 perl v5.44.0