SQL.sublime-syntax

%YAML 1.2
---
name: SQL
file_extensions:
  - sql
  - sql.fmt
  - ddl
  - dml
scope: source.sql
contexts:
  jinja:
    - match: '{{'
      push: jinja
    - match: '}}'
      pop: true
  main:
    - match: '{{'
      push: jinja
    - include: comments
    - match: '(?i:\s*\b(grant usage on|alter|create(?:\s+or\s+replace)?)\s+(share|aggregate|conversion|database|domain|function|group|(?:unique\s+)?index|language|operator class|operator|procedure|rule|schema|sequence|table|tablespace|trigger|type|user|vie|stage|external table|task|stream)\s+)(?:([\w{}\.]+)|''(\w+)''|"(\w+)"|`(\w+)`)'
      scope: meta.create.sql
      captures:
        1: keyword.other.create.sql
        2: keyword.other.sql
        3: entity.name.function.sql
        4: entity.name.function.sql
        5: entity.name.function.sql
        6: entity.name.function.sql
    - match: (?i:\s*\b(drop)\s+(aggregate|conversion|database|domain|function|group|index|language|operator class|operator|procedure|rule|schema|sequence|table|tablespace|trigger|type|user|view))
      scope: meta.drop.sql
      captures:
        1: keyword.other.create.sql
        2: keyword.other.sql
    - match: (?i:\s*\b(use)\s+(database|warehouse))
      scope: meta.drop.sql
      captures:
        1: keyword.other.create.sql
        2: keyword.other.sql
    - match: (?i:\s*(drop)\s+(table)\s+(\w+)(\s+cascade)?\b)
      scope: meta.drop.sql
      captures:
        1: keyword.other.create.sql
        2: keyword.other.table.sql
        3: entity.name.function.sql
        4: keyword.other.cascade.sql
    - match: (?i:\s*\b(alter)\s+(task|aggregate|conversion|database|domain|function|group|index|language|operator class|operator|procedure|rule|schema|sequence|table|tablespace|trigger|type|user|view)\s+([\w{}]+)\s+(resume|suspend))
      scope: meta.alter.sql
      captures:
        1: keyword.other.create.sql
        2: keyword.other.table.sql
        3: entity.name.function.sql
        4: keyword.other.create.sql
    - match: |-
        (?xi)

                # normal stuff, capture 1
                 \b(string|bigint|bigserial|bit|boolean|box|bytea|cidr|circle|date|datetime|double\sprecision|inet|int|integer|line|lseg|macaddr|money|ntext|oid|path|point|polygon|real|serial|smallint|sysdate|sysname|text)\b

                # numeric suffix, capture 2 + 3i
                |\b(bit\svarying|character\s(?:varying)?|tinyint|var\schar|float|interval)\((\d+)\)

                # optional numeric suffix, capture 4 + 5i
                |\b(char|number|nvarchar|varbinary|varchar\d?)\b(?:\((\d+)\))?

                # special case, capture 6 + 7i + 8i
                |\b(numeric|decimal)\b(?:\((\d+),(\d+)\))?

                # special case, captures 9, 10i, 11
                |\b(times?)\b(?:\((\d+)\))?(\swith(?:out)?\stime\szone\b)?

                # special case, captures 12, 13, 14i, 15
                |\b(timestamp)(?:(s|tz|_ltz|_ntz))?\b(?:\((\d+)\))?(\s(with|without)\stime\szone\b)?


      captures:
        1: storage.type.sql
        2: storage.type.sql
        3: constant.numeric.sql
        4: storage.type.sql
        5: constant.numeric.sql
        6: storage.type.sql
        7: constant.numeric.sql
        8: constant.numeric.sql
        9: storage.type.sql
        10: constant.numeric.sql
        11: storage.type.sql
        12: storage.type.sql
        13: storage.type.sql
        14: constant.numeric.sql
        15: storage.type.sql
    - match: (?i:\b((?:primary|foreign)\s+key|references|on\sdelete(\s+cascade)?|on\supdate(\s+cascade)?|check|constraint|default)\b)
      scope: storage.modifier.sql
    - match: \b\d+\b
      scope: constant.numeric.sql
    - match: (?i:\b(true|false)\b)
      scope: constant.boolean.sql
    - match: (?i:\b(select(\s+(distinct|top))?|insert(\s+(ignore\s+)?into)?|update|delete|truncate|from|set|where|group\sby|or|like|between|and|with|case|when|then|else|end|union(\s+all)?|having|using|order\sby|limit|(inner|cross)\s+join|join|straight_join|(left|right)(\s+outer)?\s+join|natural(\s+(left|right)(\s+outer)?)?\s+join)\b)
      scope: keyword.other.DML.sql
    - match: (?i:\b(on|((is\s+)?not\s+)?null)\b)
      scope: keyword.other.DDL.create.II.sql
    - match: (?i:\bvalues\b)
      scope: keyword.other.DDL.create.II.sql
    - match: (?i:\b(after|partition by|location|auto_refresh|file_format|type|json|insert_only|warehouse|schedule|call|external table)\b)
      comment: Snowflake DDL functions
      scope: keyword.other.DML.II.sql
    - match: (?i:\b(begin(\s+work)?|start\s+transaction|commit(\s+work)?|rollback(\s+work)?)\b)
      scope: keyword.other.LUW.sql
    - match: (?i:\b(grant(\swith\sgrant\soption)?|revoke)\b)
      scope: keyword.other.authorization.sql
    - match: (?i:\bin\b)
      scope: keyword.other.data-integrity.sql
    - match: (?i:\s*\b(comment\s+on\s+(table|column|aggregate|constraint|database|domain|function|index|operator|rule|schema|sequence|trigger|type|view))\s+.*?\s+(is)\s+)
      scope: keyword.other.object-comments.sql
    - match: (?i)\bAS\b
      scope: keyword.other.alias.sql
    - match: (?i)\b(DESC|ASC)\b
      scope: keyword.other.order.sql
    - match: \*
      scope: keyword.operator.star.sql
    - match: "<=>|[!<>]?=|<>|<|>"
      scope: keyword.operator.comparison.sql
    - match: '-|\+|/'
      scope: keyword.operator.math.sql
    - match: \|\|
      scope: keyword.operator.concatenator.sql
    - match: (?i)\b(CURRENT_(DATE|TIME(STAMP)?|USER)|(SESSION|SYSTEM)_USER)\b
      scope: support.function.scalar.sql
    - match: (?i)\b(AVG|COUNT|MIN|MAX|SUM)(?=\s*\()
      scope: support.function.aggregate.sql
    - match: (?i)\b(ENDSWITH|CONCAT|SUBSTR|TO_JSON|PARSE_JSON|MD5|SPLIT_PART|CONCATENATE|CONVERT|LOWER|SUBSTRING|TRANSLATE|TRIM|UPPER)\b
      scope: support.function.string.sql
    - match: (?i)\b(OBJECT_CONSTRUCT|DATEADD|HOUR|TO_JSON)\b
      scope: support.function.string.sql
    - match: (?i)\b(SYSTEM\$[A-Z_]+)(?=\s*\()
      comment: Snowflake SYSTEM functions
      scope: support.function.scalar.sql
    - match: (?i)\b(METADATA\$(FILENAME|action))(?=\s*)
      comment: Snowflake METADATA constants
      scope: constant.string.sql
    - match: \b(\w+?)\.(\w+)\b
      captures:
        1: constant.other.database-name.sql
        2: constant.other.table-name.sql
    - include: strings
    - include: regexps
    - match: (\()(\))
      comment: Allow for special ↩ behavior
      scope: meta.block.sql
      captures:
        1: punctuation.section.scope.begin.sql
        2: punctuation.section.scope.end.sql
  comments:
    - match: "--"
      scope: punctuation.definition.comment.sql
      push:
        - meta_scope: comment.line.double-dash.sql
        - match: \n
          pop: true
    - match: "#"
      scope: punctuation.definition.comment.sql
      push:
        - meta_scope: comment.line.number-sign.sql
        - match: \n
          pop: true
    - match: /\*
      scope: punctuation.definition.comment.sql
      push:
        - meta_scope: comment.block.c
        - match: \*/
          pop: true
  regexps:
    - match: /(?=\S.*/)
      captures:
        0: punctuation.definition.string.begin.sql
      push:
        - meta_scope: string.regexp.sql
        - match: /
          captures:
            0: punctuation.definition.string.end.sql
          pop: true
        - include: string_interpolation
        - match: \\/
          scope: constant.character.escape.slash.sql
    - match: '%r\{'
      comment: We should probably handle nested bracket pairs!?! -- Allan
      captures:
        0: punctuation.definition.string.begin.sql
      push:
        - meta_scope: string.regexp.modr.sql
        - match: '\}'
          captures:
            0: punctuation.definition.string.end.sql
          pop: true
        - include: string_interpolation
  string_escape:
    - match: \\.
      scope: constant.character.escape.sql
  string_interpolation:
    - match: '(#\{)([^\}]*)(\})'
      scope: string.interpolated.sql
      captures:
        1: punctuation.definition.string.begin.sql
        3: punctuation.definition.string.end.sql
  strings:
    - match: "'"
      captures:
        0: punctuation.definition.string.begin.sql
      push:
        - meta_scope: string.quoted.single.sql
        - match: "''"
          scope: constant.character.escape.sql
        - match: "'"
          captures:
            0: punctuation.definition.string.end.sql
          pop: true
        - include: string_escape
    - match: "`"
      captures:
        0: punctuation.definition.string.begin.sql
      push:
        - meta_scope: string.quoted.other.backtick.sql
        - match: "`"
          captures:
            0: punctuation.definition.string.end.sql
          pop: true
        - include: string_escape
    - match: '"'
      captures:
        0: punctuation.definition.string.begin.sql
      push:
        - meta_scope: string.quoted.double.sql
        - match: '""'
          scope: constant.character.escape.sql
        - match: '"'
          captures:
            0: punctuation.definition.string.end.sql
          pop: true
        - include: string_interpolation
    - match: '%\{'
      captures:
        0: punctuation.definition.string.begin.sql
      push:
        - meta_scope: string.other.quoted.brackets.sql
        - match: '\}'
          captures:
            0: punctuation.definition.string.end.sql
          pop: true
        - include: string_interpolation