[elpa] externals/sql-indent 7a103c2 1/3: Recognize MySQL optional clauses, #60 (#61)

[Stefan Monnier]

branch: externals/sql-indent
commit 7a103c2d5a9c0353483c4e582be8d3d955d4802e
Author: Alex Harsányi <address@hidden>
Commit: GitHub <address@hidden>

    Recognize MySQL optional clauses, #60 (#61)
    
    (sqlind-maybe-skip-mysql-create-options): skip over optional clauses between
    CREATE and the thing being created -- this allows correct identification of
    the create statement
    
    (sqlind-maybe-create-statement): skip over any "if not exist" keywords 
between
    the thing being created and its name -- this allows correct identification 
of
    the view or table name.
    
    (sqlind-refine-syntax): refine SELECT statements inside table declarations, 
as
    in "CREATE TABLE AS SELECT..."
    
    also recognize tabs as whitespace syntax in various regexps.
    
    add tests, also update the `if-exists.sql` test as the view name is 
correctly
    determined now.
---
 sql-indent-test.el          |  3 +++
 sql-indent.el               | 61 +++++++++++++++++++++++++++++++++++++--------
 test-data/if-exists-syn.eld | 11 +++-----
 test-data/if-exists.sql     |  2 --
 test-data/pr60-syn.eld      | 44 ++++++++++++++++++++++++++++++++
 test-data/pr60.sql          | 20 +++++++++++++++
 6 files changed, 121 insertions(+), 20 deletions(-)

diff --git a/sql-indent-test.el b/sql-indent-test.el
index ebe8dba..4152c0a 100644
--- a/sql-indent-test.el
+++ b/sql-indent-test.el
@@ -366,4 +366,7 @@ information read from DATA-FILE (as generated by
 (ert-deftest sqlind-ert-pr54 ()
   (sqlind-ert-check-file-syntax "test-data/pr54.sql" "test-data/pr54-syn.eld"))
 
+(ert-deftest sqlind-ert-pr60 ()
+  (sqlind-ert-check-file-syntax "test-data/pr60.sql" "test-data/pr60-syn.eld"))
+
 ;;; sql-indent-test.el ends here
diff --git a/sql-indent.el b/sql-indent.el
index e4c383c..e49998a 100644
--- a/sql-indent.el
+++ b/sql-indent.el
@@ -371,7 +371,7 @@ But don't go before LIMIT."
 ;;;;; Find the syntax and beginning of the current block
 
 (defconst sqlind-end-statement-regexp
-  "\\_<end\\_>\\(?:[ \n\r\t]*\\)\\(if\\_>\\|loop\\_>\\|case\\_>\\)?\\(?:[ 
\n\r\f]*\\)\\([a-z0-9_]+\\)?"
+  "\\_<end\\_>\\(?:[ \t\n\r\t]*\\)\\(if\\_>\\|loop\\_>\\|case\\_>\\)?\\(?:[ 
\t\n\r\f]*\\)\\([a-z0-9_]+\\)?"
   "Match an end of statement.
 Matches a string like \"end if|loop|case MAYBE-LABEL\".")
 
@@ -421,7 +421,7 @@ See also `sqlind-beginning-of-block'"
        ;; a then keyword only starts a block when it is part of an
        ;; if, case/when or exception statement
        (cond
-         ((looking-at "\\(<<[a-z0-9_]+>>\\)?\\(?:[ 
\n\r\f]*\\)\\(\\(?:els\\)?if\\)\\_>")
+         ((looking-at "\\(<<[a-z0-9_]+>>\\)?\\(?:[ 
\t\n\r\f]*\\)\\(\\(?:els\\)?if\\)\\_>")
           (let ((if-label (sqlind-match-string 1))
                 (if-kind (intern (sqlind-match-string 2)))) ; can be if or 
elsif
             (setq if-label (if if-label (substring if-label 2 -2) ""))
@@ -437,7 +437,7 @@ See also `sqlind-beginning-of-block'"
                       (throw 'finished
                         (list 'syntax-error
                               "bad closing for if block" (point) pos))))))))
-         ((looking-at "\\(<<[a-z0-9_]+>>\\)?\\(?:[ \n\r\f]*\\)case\\_>")
+         ((looking-at "\\(<<[a-z0-9_]+>>\\)?\\(?:[ \t\n\r\f]*\\)case\\_>")
           ;; find the nearest when block, but only if there are no
           ;; end statements in the stack
           (let ((case-label (sqlind-match-string 1)))
@@ -624,23 +624,63 @@ See also `sqlind-beginning-of-block'"
     (throw 'finished
       (if (null sqlind-end-stmt-stack)
          'declare-statement
-         (list 'syntax-error "nested declare block" (point) (point))))))
+        (list 'syntax-error "nested declare block" (point) (point))))))
+
+(defun sqlind-maybe-skip-mysql-create-options ()
+  "Move point past any MySQL option declarations.
+
+Statements like \"CREATE VIEW\" or \"CREATE TABLE\" can have
+various options betwen the CREATE keyword and the thing being
+created.  If such options exist at (point) the cursor is moved
+past them.
+
+Currently we move over the following options:
+
+  TEMPORARY
+  ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}
+  DEFINER = { user | CURENT_USER }
+  SQL SECURITY { DEFINER | INVOKER }
+
+We don't consider if the options are valid or not for the thing
+being created.  We just skip any and all of them that are
+present."
+  (when (eq sql-product 'mysql)
+    (catch 'finished
+      (while t
+        (cond
+          ((looking-at "temporary\\_>")
+           (goto-char (match-end 0))
+           (sqlind-forward-syntactic-ws))
+          ((looking-at 
"\\(definer\\|algorithm\\)\\(\\s-\\|[\n]\\)*=\\(\\s-\\|[\n]\\)*\\S-+")
+           (goto-char (match-end 0))
+           (sqlind-forward-syntactic-ws))
+          ((looking-at "sql\\(\\s-\\|[\n]\\)+security\\(\\s-\\|[\n]\\)+\\S-+")
+           (goto-char (match-end 0))
+           (sqlind-forward-syntactic-ws))
+          (t (throw 'finished nil)))))))
 
 (defun sqlind-maybe-create-statement ()
   "If (point) is on a CREATE statement, report its syntax.
 See also `sqlind-beginning-of-block'"
-  (when (or (looking-at "create\\_>\\(?:[ \n\r\f]+\\)\\(or\\(?:[ 
\n\r\f]+\\)replace\\_>\\)?")
+  (when (or (looking-at "create\\_>\\(?:[ \t\n\r\f]+\\)\\(or\\(?:[ 
\t\n\r\f]+\\)replace\\_>\\)?")
             (looking-at "alter\\_>"))
     (prog1 t                            ; make sure we return t
       (save-excursion
        ;; let's see what are we creating
        (goto-char (match-end 0))
         (sqlind-forward-syntactic-ws)
+        (sqlind-maybe-skip-mysql-create-options)
        (let ((what (intern (downcase (buffer-substring-no-properties
                                       (point)
                                       (progn (forward-word) (point))))))
              (name (downcase (buffer-substring-no-properties
-                              (progn (sqlind-forward-syntactic-ws) (point))
+                              (progn (sqlind-forward-syntactic-ws)
+                                      ;; Skip over a possible "if (not)
+                                      ;; exists", to get the actual name
+                                      (when (looking-at 
"if\\(\\s-\\|[\n]\\)+\\(not\\)?\\(\\s-\\|[\n]\\)exists")
+                                        (goto-char (match-end 0))
+                                        (sqlind-forward-syntactic-ws))
+                                      (point))
                               (progn (skip-syntax-forward "w_()") (point))))))
          (when (and (eq what 'package) (equal name "body"))
            (setq what 'package-body)
@@ -678,7 +718,7 @@ See also `sqlind-beginning-of-block'"
   "If (point) is on a procedure definition statement, report its syntax.
 See also `sqlind-beginning-of-block'"
   (catch 'exit
-    (when (looking-at "\\(procedure\\|function\\)\\(?:[ 
\n\r\f]+\\)\\([a-z0-9_]+\\)")
+    (when (looking-at "\\(procedure\\|function\\)\\(?:[ 
\t\n\r\f]+\\)\\([a-z0-9_]+\\)")
       (prog1 t                          ; make sure we return t
        (let ((proc-name (sqlind-match-string 2)))
          ;; need to find out if this is a procedure/function
@@ -690,7 +730,7 @@ See also `sqlind-beginning-of-block'"
            (when (looking-at "(")
              (ignore-errors (forward-sexp 1))
              (sqlind-forward-syntactic-ws))
-           (when (looking-at "return\\(?:[ 
\n\r\f]+\\)\\([a-z0-9_.]+\\(?:%\\(?:row\\)?type\\)?\\)")
+           (when (looking-at "return\\(?:[ 
\t\n\r\f]+\\)\\([a-z0-9_.]+\\(?:%\\(?:row\\)?type\\)?\\)")
              (goto-char (match-end 0))
              (sqlind-forward-syntactic-ws))
            (when (looking-at ";")
@@ -1390,9 +1430,10 @@ not a statement-continuation POS is the same as the
        (when (sqlind-search-backward pos "when\\_>" anchor)
          (push (cons '(in-block when) (point)) context))))
 
-    ;; indenting the select clause inside a view
+    ;; indenting the select clause inside a view or a "create table as"
+    ;; statement.
     ((and (eq syntax-symbol 'create-statement)
-          (eq (nth 1 syntax) 'view))
+          (memq (nth 1 syntax) '(view table)))
      (goto-char anchor)
      (catch 'done
        (while (re-search-forward "\\bselect\\b" pos 'noerror)
diff --git a/test-data/if-exists-syn.eld b/test-data/if-exists-syn.eld
index b133374..80ceb59 100644
--- a/test-data/if-exists-syn.eld
+++ b/test-data/if-exists-syn.eld
@@ -4,16 +4,11 @@
  (((create-statement index "ix1_some_table")
    . 39))
  ((toplevel . 1))
- ((comment-start . 1)
-  (toplevel . 1))
- ((comment-start . 1)
-  (toplevel . 1))
  ((toplevel . 1))
- (((create-statement index "if")
-   . 190))
+ (((create-statement index "ix2_some_table")
+   . 98))
  ((toplevel . 1))
  ((toplevel . 1))
  (((create-statement index "ix3_some_table")
-   . 269))
+   . 177))
  ((toplevel . 1)))
- 
\ No newline at end of file
diff --git a/test-data/if-exists.sql b/test-data/if-exists.sql
index 3e7e631..cdced66 100644
--- a/test-data/if-exists.sql
+++ b/test-data/if-exists.sql
@@ -3,8 +3,6 @@ drop index if exists IX1_SOME_TABLE;
 create index IX1_SOME_TABLE
   on SOME_TABLE(some_column);
 
--- NOTE: syntax incorrectly detects the name of the index as being "if".  will
--- fix later
 create index if not exists IX2_SOME_TABLE
   on SOME_TABLE(some_other_column);
 
diff --git a/test-data/pr60-syn.eld b/test-data/pr60-syn.eld
new file mode 100644
index 0000000..48b7290
--- /dev/null
+++ b/test-data/pr60-syn.eld
@@ -0,0 +1,44 @@
+(((toplevel . 1))
+ (((create-statement view "myview")
+   . 1))
+ (((create-statement view "myview")
+   . 1))
+ (((create-statement view "myview")
+   . 1))
+ (((create-statement view "myview")
+   . 1))
+ (((create-statement view "myview")
+   . 1))
+ ((select-column . 112)
+  ((create-statement view "myview")
+   . 1))
+ ((select-clause . 112)
+  ((create-statement view "myview")
+   . 1))
+ ((select-table-continuation . 208)
+  ((create-statement view "myview")
+   . 1))
+ ((toplevel . 1))
+ ((toplevel . 1))
+ (((create-statement table "foo")
+   . 282))
+ ((select-column . 328)
+  ((create-statement table "foo")
+   . 282))
+ ((select-clause . 328)
+  ((create-statement table "foo")
+   . 282))
+ ((select-table-continuation . 424)
+  ((create-statement table "foo")
+   . 282))
+ ((toplevel . 1))
+ ((comment-start . 1)
+  (toplevel . 1))
+ ((comment-start . 1)
+  (toplevel . 1))
+ ((comment-start . 1)
+  (toplevel . 1))
+ ((comment-start . 1)
+  (toplevel . 1))
+ ((toplevel . 1)))
+ 
\ No newline at end of file
diff --git a/test-data/pr60.sql b/test-data/pr60.sql
new file mode 100644
index 0000000..0830a84
--- /dev/null
+++ b/test-data/pr60.sql
@@ -0,0 +1,20 @@
+create or replace
+  algorithm = undefined
+  definer = address@hidden
+  sql security definer
+  view myview as
+  select distinct table1.fielda as firstfield,
+                  table2.fieldb as secondfield
+    from table1
+           join table2 on table1.table1id = table2.fktable1;
+
+create temporary table if not exists foo as
+  select distinct table1.fielda as firstfield,
+                  table2.fieldb as secondfield
+    from table1
+           join table2 on table1.table1id = table2.fktable1;
+
+-- local variables:
+-- mode: sql
+-- sql-product: mysql
+-- end: