branch: externals/sql-indent
commit 2a4556e6e7fc6318a33ca6c9b252098842ce20ab
Author: Pierre Téchoueyres <[email protected]>
Commit: Alex Harsányi <[email protected]>
Start workgin on aligning keywords left or right. (#14)
Provide sample customization for the SQL indentation engine
Some extra indentation helper functions are also provided and documented.
---
customize-indentation.md | 28 +++++
sql-indent-left.el | 299 +++++++++++++++++++++++++++++++++++++++++++++++
sql-indent.el | 95 +++++++++++++--
3 files changed, 411 insertions(+), 11 deletions(-)
diff --git a/customize-indentation.md b/customize-indentation.md
index aa7b5b3..7b77b92 100644
--- a/customize-indentation.md
+++ b/customize-indentation.md
@@ -112,6 +112,34 @@ The following functions are available as part of the
package:
paren, discard the current offset and return the column of the corresponding
open paren.
+* `sqlind-lone-semicolon` -- Indent a lone semicolon with the statement start.
+
+* `sqlind-adjust-operator` -- Adjust the indentation for operators in
+ select clauses, it will
+ indent as follows:
+
+```sql
+ select col1, col2
+ || col3 as composed_column,
+ col4
+ || col5 as composed_column2
+ from my_table
+ where cond1 = 1
+ and cond2 = 2;
+```
+
+* `sqlind-adjust-and-or-left` -- Align an AND, OR or NOT operator with
+the start of the WHERE clause.
+If this rule is added to the 'in-select-clause syntax after the
+`sqlind-lineup-to-clause-end' rule, it will adjust lines starting
+with AND, OR or NOT to be aligned so they sit left under the WHERE clause.
+
+* `sqlind-adjust-and-or-right` -- Align an AND, OR or NOT operator
+ with the end of the WHERE clause. If this rule is added to the
+ 'in-select-clause syntax after the `sqlind-lineup-to-clause-end' rule,
+ it will adjust lines starting with AND, OR or NOT to be aligned so
+ they sit under the WHERE clause.
+
* `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
diff --git a/sql-indent-left.el b/sql-indent-left.el
new file mode 100755
index 0000000..dccec7c
--- /dev/null
+++ b/sql-indent-left.el
@@ -0,0 +1,299 @@
+;;; sql-indent-left.el --- configuration options to indent sql -*-
lexical-binding: t -*-
+;;
+;; Filename: sql-indent-left.el
+;; Description:
+;; Author: [email protected]
+;; Maintainer: [email protected]
+;; Copyright (C) 2017, Pierre Téchoueyres all rights reserved.
+;; Created:
+;; Version: [email protected]
+;; Last-Updated:
+;; By:
+;; Update #: 0
+;; URL:
+;; Keywords: language sql indentation
+;; Compatibility:
+;;
+;; Features that might be required by this library:
+;;
+;; None
+;;
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;
+;;; Commentary:
+;;
+;; Set configuration options to indent sql my way.
+;;
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;
+;;; Change log:
+;;
+;;
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;
+;; This program is free software; you can redistribute it and/or
+;; modify it under the terms of the GNU General Public License as
+;; published by the Free Software Foundation; either version 3, or
+;; (at your option) any later version.
+;;
+;; This program is distributed in the hope that it will be useful,
+;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+;; General Public License for more details.
+;;
+;; You should have received a copy of the GNU General Public License
+;; along with this program; see the file COPYING. If not, write to
+;; the Free Software Foundation, Inc., 51 Franklin Street, Fifth
+;; Floor, Boston, MA 02110-1301, USA.
+;;
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;
+;;; Code:
+
+(require 'sql-indent)
+
+
+(defvar sqlind-indentation-right-offsets-alist
+ `((select-column-continuation sqlind-indent-select-column
+ sqlind-adjust-operator
+ sqlind-lone-semicolon)
+ (in-select-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-right
+ sqlind-lone-semicolon)
+ (in-delete-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-right
+ sqlind-lone-semicolon)
+ (in-insert-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-right
+ sqlind-lone-semicolon)
+ (in-update-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-right
+ sqlind-lone-semicolon)
+ ;; mandatory
+ (select-table-continuation sqlind-indent-select-table +
+ sqlind-lone-semicolon)
+ ;; rest picked up from the original indentation offsets
+ ,@sqlind-default-indentation-offsets-alist)
+ "Align sql code like this :
+
+clear columns
+set linesize 2500
+set trimout on trimspool on
+
+select atc.column_name,
+ atc.data_type,
+ data_length,
+ data_precision,
+ nullable,
+ data_scale,
+ nvl(substr(comments, 1, 100), atc.column_name) comments
+ from all_tab_columns atc,
+ all_col_comments acc
+ where atc.owner = acc.owner
+ and atc.table_name = acc.table_name
+ and atc.column_name = acc.column_name
+ and atc.owner = user
+ and atc.table_name = 'MY_TABLE'
+ and atc.column_name = p_column_name
+ and not exists (select 1
+ from all_tab_columns atc1,
+ all_col_comments acc1
+ where atc1.owner = acc1.owner
+ and atc1.table_name = acc1.table_name
+ and atc1.column_name = acc1.column_name
+ and atc1.owner = atc.owner
+ and atc1.table_name = atc.table_name
+ and acc1.column_name = acc.column_name)
+ ;
+
+delete from my_table mt
+ where col_1 = v_col1
+ and ( col_2 = v_col2
+ or col_3 = v_col3)
+ and col_42 = '42'
+ ;
+
+update my_table
+ set col1_has_a_long_name = value1,
+ col2_is_short = value2
+ where cond1 is not null
+ and ( col_2 = v_col2
+ or col_3 = v_col3)
+ and col_42 = '42'
+ ;
+
+insert into xyzxx
+ ( aaa, xxx, bbb, ccc,
+ ddd, eee, fff, ggg,
+ hhh )
+select aaa,
+ xxx,
+ max (m.b1) as bbb,
+ min (m.b1) as ccc,
+ coalesce (max (n.c2), 0) as ddd,
+ coalesce (min (n.c2), 0) as eee,
+ max (m.b1) over ( partition by c2
+ order by aaa desc ) as fff,
+ min (m.b1) over ( partition by c2
+ order by aaa desc ) as ggg,
+ avg (n.c2) as hhh
+ from (select * from (select aaa,
+ jjj + kkk as b1,
+ row_number () over ( partition by qqq
+ order by rrr,
+ sss ) as rn
+ from mno)
+ where rn = 1) m
+ inner join (select aaa,
+ nnn + ooo as c2
+ from pqr) n
+ using (aaa),
+ group by aaa,
+ xxx
+ order by xxx desc,
+ aaa asc
+ ;
+
+")
+
+(defvar sqlind-indentation-left-offsets-alist
+ `((select-clause 0)
+ (insert-clause 0)
+ (delete-clause 0)
+ (update-clause 0)
+ (case-clause-item-cont 0)
+ (package +)
+ (package-body +)
+ (nested-statement-continuation +)
+ (select-column-continuation sqlind-indent-select-column
+ sqlind-adjust-operator
+ sqlind-lone-semicolon)
+ (in-select-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-left
+ sqlind-lone-semicolon)
+ (in-delete-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-left
+ sqlind-lone-semicolon)
+ (in-insert-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-left
+ sqlind-lone-semicolon)
+ (in-update-clause sqlind-lineup-to-clause-end
+ sqlind-adjust-operator
+ sqlind-adjust-and-or-left
+ sqlind-lone-semicolon)
+ (select-table-continuation sqlind-indent-select-table +
+ sqlind-lone-semicolon)
+ ;; rest picked up from the original indentation offsets
+ ,@sqlind-default-indentation-offsets-alist)
+ "Align sql code like this :
+
+clear columns
+set linesize 2500
+set trimout on trimspool on
+
+select atc.column_name,
+ atc.data_type,
+ data_length,
+ data_precision,
+ nullable,
+ data_scale,
+ nvl(substr(comments, 1, 100), atc.column_name) comments
+from all_tab_columns atc,
+ all_col_comments acc
+where atc.owner = acc.owner
+and atc.table_name = acc.table_name
+and atc.column_name = acc.column_name
+and atc.owner = user
+and atc.table_name = 'MY_TABLE'
+and atc.column_name = p_column_name
+and not exists (select 1
+ from all_tab_columns atc1,
+ all_col_comments acc1
+ where atc1.owner = acc1.owner
+ and atc1.table_name = acc1.table_name
+ and atc1.column_name = acc1.column_name
+ and atc1.owner = atc.owner
+ and atc1.table_name = atc.table_name
+ and acc1.column_name = acc.column_name)
+;
+
+delete from my_table mt
+where col_1 = v_col1
+and ( col_2 = v_col2
+ or col_3 = v_col3)
+and col_42 = '42'
+;
+
+update my_table
+set col1_has_a_long_name = value1,
+ col2_is_short = value2
+where cond1 is not null
+and ( col_2 = v_col2
+ or col_3 = v_col3)
+and col_42 = '42'
+;
+
+insert into xyzxx
+ ( aaa, xxx, bbb, ccc,
+ ddd, eee, fff, ggg,
+ hhh )
+select aaa,
+ xxx,
+ max (m.b1) as bbb,
+ min (m.b1) as ccc,
+ coalesce (max (n.c2), 0) as ddd,
+ coalesce (min (n.c2), 0) as eee,
+ max (m.b1) over ( partition by c2
+ order by aaa desc ) as fff,
+ min (m.b1) over ( partition by c2
+ order by aaa desc ) as ggg,
+ avg (n.c2) as hhh
+from (select * from (select aaa,
+ jjj + kkk as b1,
+ row_number () over ( partition by qqq
+ order by rrr,
+ sss ) as rn
+ from mno)
+ where rn = 1) m
+ inner join (select aaa,
+ nnn + ooo as c2
+ from pqr) n
+ using (aaa),
+group by aaa,
+ xxx
+order by xxx desc,
+ aaa asc
+;
+
+")
+
+;;;###autoload
+(defun sqlind-setup-style-left ()
+ "Define an sql-indentation style where keywords are left aligned."
+ (interactive)
+ (setq sqlind-indentation-offsets-alist
sqlind-indentation-left-offsets-alist))
+
+;;;###autoload
+(defun sqlind-setup-style-right ()
+ "Define an sql-indentation style where keywords are right aligned."
+ (interactive)
+ (setq sqlind-indentation-offsets-alist
sqlind-indentation-right-offsets-alist))
+
+
+;;;###autoload
+(defun sqlind-setup-style-default ()
+ "Define an sql-indentation style where keywords are right aligned."
+ (interactive)
+ (setq sqlind-indentation-offsets-alist
sqlind-default-indentation-offsets-alist))
+
+
+(provide 'sql-indent-left)
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;;; sql-indent-left.el ends here
diff --git a/sql-indent.el b/sql-indent.el
index 65bcef4..1cb9b5e 100644
--- a/sql-indent.el
+++ b/sql-indent.el
@@ -221,12 +221,28 @@ they are one-line only directives."
(ms sqlind-ms-directive)
(sqlite sqlind-sqlite-directive)
(oracle sqlind-sqlplus-directive)
- (t nil))))
+ (t nil)))
+ (directive-start)
+ (match-start))
(when rx
(save-excursion
(when (re-search-backward rx nil 'noerror)
- (forward-line 1)
- (point)))))))
+ (setq match-start (match-beginning 0))
+ (forward-line 1)
+ (setq directive-start (point))
+ (when (string-equal "set" (match-string 0))
+ (let* ((update-start (or (re-search-backward "\\_<update\\_>" nil
'noerror)
+ (1+ directive-start)))
+ (update-end (or (re-search-forward ";" nil 'noerror)
+ (1- directive-start))))
+ (when (and (<= update-start directive-start)
+ (>= update-end directive-start))
+ (goto-char match-start)
+ (setq directive-start nil)
+ (when (re-search-backward rx nil 'noerror)
+ (forward-line 1)
+ (setq directive-start (point)))))))))
+ directive-start)))
(defun sqlind-beginning-of-statement-1 (limit)
"Return the position of a block start, or nil.
@@ -811,7 +827,7 @@ See also `sqlind-beginning-of-block'"
(throw 'finished
(if (looking-at "select")
(sqlind-syntax-in-select pos match-pos)
- (cons (list 'in-insert-clause clause) match-pos)))))))))
+ (cons (list 'in-insert-clause clause) match-pos)))))))))
;;;;; Determine the syntax inside a delete statement
@@ -1429,7 +1445,7 @@ clause (select, from, where, etc) in which the current
point is.
(select-join-condition ++)
(select-table sqlind-indent-select-table)
(select-table-continuation sqlind-indent-select-table +)
- (in-select-clause sqlind-lineup-to-clause-end
sqlind-adjust-and-or)
+ (in-select-clause sqlind-lineup-to-clause-end
sqlind-adjust-and-or-right)
(insert-clause sqlind-right-justify-clause)
(in-insert-clause sqlind-lineup-to-clause-end)
(delete-clause sqlind-right-justify-clause)
@@ -1688,20 +1704,77 @@ keyword ends."
;; otherwise, align to the end of the clause, with a few exceptions
(current-column)))))
-(defun sqlind-adjust-and-or (syntax base-indentation)
- "Align an AND or OR operator with the end of the WHERE clause.
+(defun sqlind-adjust-and-or-right (syntax base-indentation)
+ "Align an AND, OR or NOT operator with the end of the WHERE clause.
If this rule is added to the 'in-select-clause syntax after the
`sqlind-lineup-to-clause-end' rule, it will adjust lines starting
-with AND or OR to be aligned so they sit under the WHERE clause."
+with AND, OR or NOT to be aligned so they sit under the WHERE clause."
(save-excursion
(back-to-indentation)
(destructuring-bind ((sym clause) . anchor) (car syntax)
- (if (and (eq sym 'in-select-clause)
- (equal clause "where")
- (looking-at "and\\|or"))
+ (if (and (equal clause "where")
+ (looking-at "and\\|or\\|not"))
(- base-indentation (1+ (- (match-end 0) (match-beginning 0))))
base-indentation))))
+(defun sqlind-adjust-and-or-left (syntax base-indentation)
+ "Align an AND, OR or NOT operator with the start of the WHERE clause.
+If this rule is added to the 'in-select-clause syntax after the
+`sqlind-lineup-to-clause-end' rule, it will adjust lines starting
+with AND, OR or NOT to be aligned so they sit left under the WHERE clause."
+ (save-excursion
+ (back-to-indentation)
+ (destructuring-bind ((sym clause) . anchor) (car syntax)
+ (if (and (equal clause "where")
+ (looking-at "and\\|or\\|not"))
+ (progn (goto-char anchor) (current-column))
+ base-indentation))))
+
+(defun sqlind-adjust-operator (_syntax base-indentation)
+ "Adjust the indentation for operators in select clauses.
+
+Select columns are lined up to the operands, not the operators.
+For example.
+
+ select col1, col2
+ || col3 as composed_column,
+ col4
+ || col5 as composed_column2
+ from my_table
+ where cond1 = 1
+ and cond2 = 2;
+
+This is an indentation adjuster and needs to be added to the
+`sqlind-indentation-offsets-alist`"
+ (save-excursion
+ (back-to-indentation)
+ ;; If there are non-word constituents at the beginning if the line,
+ ;; consider them an operator and line up the line to the first word of the
+ ;; line, not the operator
+ (cond ((looking-at "\\W+")
+ (let ((ofs (length (match-string 0))))
+ (forward-line -1)
+ (end-of-line)
+ (sqlind-backward-syntactic-ws)
+ (forward-sexp -1)
+ (- (current-column) ofs)))
+ ('t base-indentation))))
+
+(defun sqlind-lone-semicolon (syntax base-indentation)
+ "Indent a lone semicolon with the statement start. For example:
+
+ select col
+ from my_table
+ ;
+
+This is an indentation adjuster and needs to be added to the
+`sqlind-indentation-offsets-alist`"
+ (save-excursion
+ (back-to-indentation)
+ (if (looking-at ";")
+ (sqlind-use-anchor-indentation syntax base-indentation)
+ base-indentation)))
+
(defun sqlind-right-justify-clause (syntax base-indentation)
"Return an indentation which right-aligns the first word at
ANCHOR with the first word in the curent line.
