Aggressive SQL indentation in emacs

Question

I want an emacs SQL indentation function with the following properties:

1. Upcase { select, from, as, where, group by, order by, asc, desc, and, or }. -

EDIT: there are sqlup-mode.el and sql-upcase.el which both address this part of the problem.

2. Maintain an invariant that { select, from, where, group by, order by } are followed by a newline.
3. All select, where, group by and order by clauses are on their own line.
4. 2 space indentation for non-top-level stuff
5. AND and OR elements of WHERE clause is indented.

Example:

before:

select foo, bar, count(1) as foo_bar_count
from mytable
where foo = 1 and bar = 2
group by foo, bar
order by foo desc

after:

SELECT 
  foo, 
  bar, 
  count(1) AS foo_bar_count
FROM 
  mytable
WHERE 
  foo = 1 
  AND bar = 2
GROUP BY 
  foo, 
  bar
ORDER BY 
  foo DESC

I haven't found a reference to anything like this.

Questions:

1. For point 1, the following seems to be sufficient:

   (defun kb/sql-upcase-keywords()
     (interactive)
     (goto-char (point-min))
     (while (re-search-forward "^[ \t]*\\(select\\|from\\)" nil t)
       (replace-match (upcase (match-string 1)))))
   

2. For point 3 - is there an example of elisp which inserts newlines efficiently? I have hacked the following

   (defun kb/sql-add-newlines()
     (interactive)
     ;; (save-excursion)
     (goto-char (point-min))
     (while (re-search-forward
            "^[    ]*\\\\(select\\\\|from\\\\|where\\\\|group by\\\\|order by\\\\)[    ]*\\\\([^   ]*\\\\)"
             nil t)
       (message "Match found at %s" (point))
       (replace-match "\\\\1\n\\\\2")
       (previous-line)))
   

   Is there a better way of doing this? (The previous-line call seems to be needed in order to match select foo\nselect foo)

3. Is there a simpler way forward that I am missing?

Answer 1

It doesn't satisfy all your requirements (by far), but there's an automated SQL indentation package for Emacs at https://github.com/alex-hhh/emacs-sql-indent

You can install it now from GNU ELPA.