path: root/sql-indent.org

    sql-indent.el -- syntax based indentation for SQL files for GNU Emacs

*NOTE* This file is formatted as an Emacs Org file.  If you open it in GNU
Emacs, you will be able to open-close sections of the file, which will make
navigation easier.

* Overview

sql-indent.el is a GNU Emacs minor mode which adds support for syntax-based
indentation when editing SQL code: TAB indents the current line based on the
syntax of the SQL code on previous lines.  This works like the indentation for
C and C++ code.

The package also defines align rules so that the ~align~ function works for
SQL statements, see ~sqlind-align-rules~ for which rules are defined.  This
can be used to align multiple lines around equal signs or "as" statements.
Here is an example of alignment rules:

#+BEGIN_SRC sql
  update my_table
     set col1_has_a_long_name = value1,
         col2_is_short        = value2,
         col3_med             = v2,
         c4                   = v5
   where cond1 is not null;

  select long_colum as lcol,
         scol       as short_column,
         mcolu      as mcol,
    from my_table;
#+END_SRC

To use this feature, select the region you want to align and type:

#+BEGIN_SRC text
  M-x align RET
#+END_SRC

~sqlind-minor-mode~ together with the align rules can assist in writing tidy
SQL code or formatting existing SQL code.  The indentation rules are
customizable, so you can adapt it to match your own indentation preferences.

* Installation

To install this package, open the file ~sql-indent.el~ in Emacs and type

#+BEGIN_SRC text
  M-x package-install-from-buffer RET
#+END_SRC

The syntax-based indentation of SQL code can be turned ON/OFF at any time by
enabling or disabling ~sqlind-minor-mode~:

#+BEGIN_SRC text
  M-x sqlind-minor-mode RET
#+END_SRC

To enable syntax-based indentation for every SQL buffer, you can add
~sqlind-minor-mode~ to ~sql-mode-hook~.  First, bring up the customization
buffer using the command:

#+BEGIN_SRC text
  M-x customize-variable RET sql-mode-hook RET
#+END_SRC
    
Than, click on the "INS" button to add a new entry and put "sqlind-minor-mode"
in the text field.

* Customization

The sql-indent.el package allows customizing the indentation rules to suit
your personal preferences or site specific coding styles.  To create a set of
customized rules, you will need to have basic familiarity with how indentation
works.  See also the "A Simple Example" section below and the
~sql-indent-left.el~ file, which demonstrate how to set up custom indentation
rules.  The sections below contain the rest of the details.

The indentation process happens in two separate phases: first syntactic
information is determined about the line, than the line is indented based on
that syntactic information.  The syntactic parse is not expected to change
often, since it deals with the structure of the SQL code, however, indentation
is a personal preference, and can be easily customized.

Two variables control the way code is indented: ~sqlind-basic-offset~ and
~sqlind-indentation-offsets-alist~.  To customize these variables, you need to
create a function that sets custom values and add it to ~sql-mode-hook~.

** A Simple Example

The default indentation rules will align to the right all the keywords in a
SELECT statement, like this:

#+BEGIN_SRC sql
  select c1, c2
    from t1
   where c3 = 2
#+END_SRC

If you prefer to have them aligned to the left, like this:

#+BEGIN_SRC sql
  select c1, c2
  from t1
  where c3 = 2
#+END_SRC

You can add the following code to your init file:

#+BEGIN_SRC emacs-lisp
  (require 'sql-indent)

  ;; Update indentation rules, select, insert, delete and update keywords
  ;; are aligned with the clause start

  (defvar my-sql-indentation-offsets-alist
    `((select-clause 0)
      (insert-clause 0)
      (delete-clause 0)
      (update-clause 0)
      ,@sqlind-default-indentation-offsets-alist))

  (add-hook 'sqlind-minor-mode-hook
      (lambda ()
         (setq sqlind-indentation-offsets-alist
               my-sql-indentation-offsets-alist)))
#+END_SRC

** Customization Basics

To customize indentation, you will need to provide a new value for the
~sqlind-indentation-offsets-alist~ variable.  The variable is made buffer
local each time it is set, so you need to set it inside the ~sql-mode-hook~.
The variable specifies how each syntactic symbol should be indented.  Since
only a few symbols need to be updated, the usual way to update it is to
"extend" the value of ~sqlind-default-indentation-offsets-alist~, like so:

#+BEGIN_SRC emacs-lisp
  (defvar my-sql-indentation-offsets-alist
    `( ;; put new syntactic symbols here, and add the default ones at the end.
       ;; If there is no value specified for a syntactic symbol, the default
       ;; will be picked up.
      ,@sqlind-default-indentation-offsets-alist))

  ;; Arrange for the new indentation offset to be set up for each SQL buffer.
  (add-hook 'sqlind-minor-mode-hook
            (lambda ()
              (setq sqlind-indentation-offsets-alist
                    my-sql-indentation-offsets-alist)))
#+END_SRC

The simplest way to adjust the indentation is to explore the syntactic
information using ~sqlind-show-syntax-of-line~.  To use it, move the cursor to
the line you would like to indent and type:

#+BEGIN_SRC text
M-x sqlind-show-syntax-of-line RET
#+END_SRC

A message like the one below will be shown in the messages buffer:

#+BEGIN_SRC text
((select-clause . 743) (statement-continuation . 743))
#+END_SRC

The first symbol displayed is the syntactic symbol used for indentation, in
this case ~select-clause~.  The syntactic symbols are described in a section
below, however, for now, this is the symbol that will need to be updated in
~sqlind-indentation-offsets-alist~.  The number next to it represents the
anchor, or reference position in the buffer where the current statement
starts.  The anchor and is useful if you need to write your own indentation
functions.

To customize indentation for this type of statement, add an entry in the
~sqlind-indentation-offsets-alist~, for the syntactic symbol shown, with
information about how it should be indented.  This information is a list
containing *indentation control items* (these are described below).

For example, to indent keyword in SELECT clauses at the same level as the
keyword itself, we use a number which is added to the indentation level of the
anchor, in this case, 0:

#+BEGIN_SRC text
(select-clause 0)
#+END_SRC

To indent it at ~sqlind-basic-offset~ plus one more space, use:

#+BEGIN_SRC text
(select-clause + 1)
#+END_SRC

To right-justify the keyword w.r.t the SELECT keyword, use:

#+BEGIN_SRC text
(select-clause sqlind-right-justify-clause)
#+END_SRC

The default value for ~sqlind-indentation-offsets-alist~ contains many
examples for indentation setup rules.

** Indentation control items

~sqlind-calculate-indentation~ is the function that calculates the indentation
offset to use, based on the contents of ~sqlind-indentation-offsets-alist~.
The indentation offset starts with the indentation column of the ANCHOR point
and it is adjusted based on the following items:

 * a ~NUMBER~ -- the NUMBER will be added to the indentation offset.

 * ~+~ -- the current indentation offset is incremented by
   ~sqlind-basic-offset~

 * ~++~ -- the current indentation offset is indentation by ~2 *
   sqlind-basic-offset~

 * ~-~ -- the current indentation offset is decremented by
   ~sqlind-basic-offset~

 * ~--~ -- the current indentation offset is decremented by 2 *
   ~sqlind-basic-offset~

 * a ~FUNCTION~ -- the syntax and current indentation offset is passed to the
   function and its result is used as the new indentation offset.  This can be
   used to further customize indentation.

*** Indentation Helper Functions

The following helper functions are available as part of the package and can be
used as the FUNCTION part in the ~sqlind-indentation-offsets-alist~

**** sqlind-use-anchor-indentation

discard the current offset and returns the indentation column of the ANCHOR
  
**** sqlind-lineup-to-anchor

discard the current offset and returns the column of the anchor point, which
may be different than the indentation column of the anchor point.

**** sqlind-use-previous-line-indentation

discard the current offset and returns the indentation column of the previous
line

**** sqlind-lineup-open-paren-to-anchor

if the line starts with an open parenthesis, discard the current
offset and return the column of the anchor point.

**** sqlind-lone-semicolon

if the line contains a single semicolon ';', use the value of
~sqlind-use-anchor-indentation~

**** sqlind-adjust-operator

if the line starts with an arithmetic operator (like ~+~ , ~-~, or ~||~), line
it up so that the right hand operand lines up with the left hand operand of
the previous line.  For example, it will indent the ~||~ operator like this:

#+BEGIN_SRC sql
select col1, col2
          || col3 as composed_column, -- align col3 with col2
       col4
    || col5 as composed_column2
from   my_table
where  cond1 = 1
and    cond2 = 2;
#+END_SRC

**** sqlind-left-justify-logical-operator

If the line starts with a logic operator (AND, OR NOT), line the operator with
the start of the WHERE clause.  This rule should be added to the
~in-select-clause~ syntax after the ~sqlind-lineup-to-clause-end~ rule.

**** sqlind-right-justify-logical-operator

If the line starts with a logic operator (AND, OR NOT), line the operator with
the end of the WHERE clause. This rule should be added to the
~in-select-clause~ syntax.
  
#+BEGIN_SRC sql
select *
  from table
 where a = b
   and c = d; -- AND clause sits under the where clause
#+END_SRC

**** sqlind-adjust-comma

if the line starts with a comma, adjust the current offset so that the line is
indented to the first word character.  For example, if added to a
~select-column~ syntax indentation rule, it will indent as follows:

#+BEGIN_SRC sql
select col1
   ,   col2 -- align "col2" with "col1"
from my_table;
#+END_SRC

**** sqlind-lineup-into-nested-statement

discard the current offset and return the column of the first word inside a
nested statement.  This rule should be added to
~nested-statement-continuation~ syntax indentation rule, and will indent as
follows:

#+BEGIN_SRC sql
(    a,
     b  -- b is aligned with a
)
#+END_SRC

*** More Indentation Helper Functions
The following function contain indentation code specific to various SQL
statements.  Have a look at their doc strings for what they do:

 * ~sqlind-indent-comment-start~, ~sqlind-indent-comment-continuation~

 * ~sqlind-indent-select-column~

 * ~sqlind-indent-select-table~

 * ~sqlind-lineup-to-clause-end~

 * ~sqlind-right-justify-clause~

 * ~sqlind-lineup-joins-to-anchor~

** Syntactic Symbols

The SQL parsing code returns a syntax definition (either a symbol or a
list) and an anchor point, which is a buffer position.  The syntax symbols can
be used to define how to indent each line.  The following syntax symbols are
defined for SQL code:

 * ~(syntax-error MESSAGE START END)~ -- this is returned when the parse
   failed.  MESSAGE is an informative message, START and END are buffer
   locations denoting the problematic region.  ANCHOR is undefined for this
   syntax info

 * ~in-comment~ -- line is inside a multi line comment, ANCHOR is the start of
   the comment.

 * ~comment-start~ -- line starts with a comment.  ANCHOR is the start of the
   enclosing block.

 * ~in-string~ -- line is inside a string, ANCHOR denotes the start of the
   string.

 * ~toplevel~ -- line is at toplevel (not inside any programming construct).
   ANCHOR is usually (point-min).

 * ~(in-block BLOCK-KIND LABEL)~ -- line is inside a block construct.
   BLOCK-KIND (a symbol) is the actual block type and can be one of "if",
   "case", "exception", "loop" etc.  If the block is labeled, LABEL contains
   the label.  ANCHOR is the start of the block.

 * ~(in-begin-block KIND LABEL)~ -- line is inside a block started by a begin
   statement.  KIND (a symbol) is "toplevel-block" for a begin at toplevel,
   "defun" for a begin that starts the body of a procedure or function,
   \"package\" for a begin that starts the body of a package, nil for a begin
   that is none of the previous.  For a "defun" or "package", LABEL is the
   name of the procedure, function or package, for the other block types LABEL
   contains the block label, or the empty string if the block has no label.
   ANCHOR is the start of the block.

 * ~(block-start KIND)~ -- line begins with a statement that starts a block.
   KIND (a symbol) can be one of "then", "else" or "loop".  ANCHOR is the
   reference point for the block start (the corresponding if, case, etc).

 * ~(block-end KIND LABEL)~ -- the line contains an end statement.  KIND (a
   symbol) is the type of block we are closing, LABEL (a string) is the block
   label (or procedure name for an end defun).

 * ~declare-statement~ -- line is after a declare keyword, but before the
   begin.  ANCHOR is the start of the declare statement.

 * ~(package NAME)~ -- line is inside a package definition.  NAME is the name
   of the package, ANCHOR is the start of the package.

 * ~(package-body NAME)~ -- line is inside a package body.  NAME is the name
   of the package, ANCHOR is the start of the package body.

 * ~(create-statement WHAT NAME)~ -- line is inside a CREATE statement (other
   than create procedure or function).  WHAT is the thing being created, NAME
   is its name.  ANCHOR is the start of the create statement.

 * ~(defun-start NAME)~ -- line is inside a procedure of function definition
   but before the begin block that starts the body.  NAME is the name of the
   procedure/function, ANCHOR is the start of the procedure/function
   definition.

The following SYNTAX-es are for SQL statements.  For all of them ANCHOR points
to the start of a statement itself.

 * ~labeled-statement-start~ -- line is just after a label.

 * ~statement-continuation~ -- line is inside a statement which starts on a
   previous line.

 * ~nested-statement-open~ -- line is just inside an opening bracket, but the
  actual bracket is on a previous line.

 * ~nested-statement-continuation~ -- line is inside an opening bracket, but
   not the first element after the bracket.

 * ~nested-statement-close~ line is inside an opening bracket and the line
   contains the closing bracket as the first character.

The following SYNTAX-es are for statements which are SQL code (DML
statements).  They are specializations on the previous statement syntaxes and
for all of them a previous generic statement syntax is present earlier in the
SYNTAX list.  Unless otherwise specified, ANCHOR points to the start of the
clause (select, from, where, etc) in which the current point is.

 * ~with-clause~ -- line is inside a WITH clause, but before the main SELECT
   clause.

 * ~with-clause-cte~ -- line is inside a with clause before a CTE (common
   table expression) declaration

 * ~with-clause-cte-cont~ -- line is inside a with clause before a CTE
   definition

 * ~case-clause~ -- line is on a CASE expression (WHEN or END clauses).
   ANCHOR is the start of the CASE expression.

 * ~case-clause-item~ -- line is on a CASE expression (THEN and ELSE clauses).
   ANCHOR is the position of the case clause.

 * ~case-clause-item-cont~ -- line is on a CASE expression but not on one of
   the CASE sub-keywords.  ANCHOR points to the case keyword that this line is
   a continuation of.

 * ~select-clause~ -- line is inside a select statement, right before one of
   its clauses (from, where, order by, etc).

 * ~select-column~ -- line is inside the select column section, after a full
   column was defined (and a new column definition is about to start).

 * ~select-column-continuation~ -- line is inside the select column section,
   but in the middle of a column definition.  The defined column starts on a
   previous like.  Note that ANCHOR still points to the start of the select
   statement itself.

 * ~select-join-condition~ -- line is right before or just after the ON clause
   for an INNER, LEFT or RIGHT join.  ANCHOR points to the join statement for
   which the ON is defined.

 * ~select-table~ -- line is inside the from clause, just after a table was
   defined and a new one is about to start.

 * ~select-table-continuation~ -- line is inside the from clause, inside a
   table definition which starts on a previous line. ANCHOR still points to
   the start of the table definition.

 * ~(in-select-clause CLAUSE)~ -- line is inside the select CLAUSE, which can
   be "where", "order by", "group by" or "having".  Note that CLAUSE can never
   be "select" and "from", because we have special syntaxes inside those
   clauses.

 * ~insert-clause~ -- line is inside an insert statement, right before one of
   its clauses (values, select).

 * ~(in-insert-clause CLAUSE)~ -- line is inside the insert CLAUSE, which can
   be "insert into" or "values".

 * ~delete-clause~ -- line is inside a delete statement right before one of
   its clauses.

 * ~(in-delete-clause CLAUSE)~ -- line is inside a delete CLAUSE, which can be
   "delete from" or "where".

 * ~update-clause~ -- line is inside an update statement right before one of
   its clauses.

 * ~(in-update-clause CLAUSE)~ -- line is inside an update CLAUSE, which can
   be "update", "set" or "where"
* Limitations

The sql-indent package does not contain a full SQL parser, instead it relies
on various heuristics to determine the context of each line in a SQL program.
Relying on heuristics means that sometimes valid SQL code is not detected
correctly, and therefore the indentation will be wrong.

This section contains some of the known cases that are incorrectly detected,
and provides some workarounds for them.

** Parsing expressions

There is no support for parsing SQL expressions, so if an expression is broken
over several lines, sql-indent.el will consider all lines to be
~statement-continuation~ lines.  The exception is that bracketed expressions
are identified correctly so they can be used for indentation.

The examples below summarize what is supported and what is not, as well as the
workarounds:

#+BEGIN_SRC sql
  -- SUPPORTED: case expression immediately after assignment
  var := case ind
         when 1 then 'Guy'
         when 2 then 'Abc'
         when 3 then 'Def'
         else 'World'
         end case;

  -- NOT SUPPORTED: any complex expression involving a case expression.  entire
  -- expression is a 'statement-continuation
  var := 'abc'
    || case ind
    when 1 then 'Guy'
    when 2 then 'Abc'
    when 3 then 'Def'
    else 'World'
    end case;

  -- WORKAROUND: use brackets instead
  var := 'abc'
    || (case ind
        when 1 then 'Guy'
        when 2 then 'Abc'
        when 3 then 'Def'
        else 'World'
        end case);

  -- SUPPORTED: case expression as select column
  select col1,
         case ind
         when 1 then 'Guy'
         when 2 then 'Abc'
         when 3 then 'Def'
         else 'World'
         end case,
         col2,
    from some_table;

  -- NOT SUPPORTED: any complex expression involving a case expression in a
  -- select column.  Entire column is a 'select-column-continuation
  select col1,
         'abc' || case ind
           when 1 then 'Guy'
           when 2 then 'Abc'
           when 3 then 'Def'
           else 'World'
           end case,
         col2,
    from some_table;

  -- WORKAROUND: use brackets instead
  select col1,
         'abc' || (case ind
                   when 1 then 'Guy'
                   when 2 then 'Abc'
                   when 3 then 'Def'
                   else 'World'
                   end case),
         col2,
    from some_table;
#+END_SRC

** DECLARE statements in Postgres

The DECLARE statement in Postgres can start a block of declarations or declare
a single item.  Unfortunately, the sql-indent package is not always able to
differentiate between the two.  Curently, DECLARE blocks are only recognized
at the start of another enclosing block, such as $$, BEGIN, THEN or ELSE, but
they can occur on other situations.  The workaround is to enclose the DECLARE
block inside a BEGIN/END block or to use individual DECLARE statements.

For more info see https://github.com/alex-hhh/emacs-sql-indent/issues/92

DECLARE blocks are not recognized when the follow normal statements,
sql-indent considers them single statements instead.  In the example below,
DECLARE is considered a statement, and the ~local_b~ declaration is anchored
off the previous BEGIN statement:

#+BEGIN_SRC sql
  -- NOT SUPPORTED: `local_b` is not recognized as a declaration
  create function less_than(a text, b text) returns boolean as $$
    begin
      raise debug 'less_than(%, %)', a, b;
      declare
        local_a text := a;
      local_b text := b;
      begin
        return local_a < local_b;
      end;
    end;
  end;
  $$ language plpgsql;
#+END_SRC sql

The workaround is to either surround the DECLARE block with a BEGIN/END
statement, or to prefix each variable declaration with DECLARE, as in the two
examples below:

#+BEGIN_SRC sql
  -- WORKAROUND 1: surround the DECLARE/BEGIN/END with another BEGIN/END block
  create function less_than(a text, b text) returns boolean as $$
    begin
      raise debug 'less_than(%, %)', a, b;
      begin
        declare
          local_a text := a;
          local_b text := b;
        begin
          return local_a < local_b;
        end;
      end;
    end;
  end;
  $$ language plpgsql;

  -- WORKAROUND 2: declare each variable individually
  create function less_than(a text, b text) returns boolean as $$
    begin
      raise debug 'less_than(%, %)', a, b;
      declare
        local_a text := a;
      declare
        local_b text := b;
      begin
        return local_a < local_b;
      end;
    end;
  end;
  $$ language plpgsql;
#+END_SRC sql

.