Repository: sql-formatter-org/sql-formatter
Pull request #327 (2022-07-16 21:16:02)

Utility for auto-generation of syntax variants
Listing all these syntax variations (like currently done for BigQuery) has grown a bit overboard:

```js
  'CREATE FUNCTION',
  'CREATE OR REPLACE FUNCTION',
  'CREATE FUNCTION IF NOT EXISTS',
  'CREATE TEMP FUNCTION',
  'CREATE OR REPLACE TEMP FUNCTION',
  'CREATE TEMP FUNCTION IF NOT EXISTS',
  'CREATE TEMPORARY FUNCTION',
  'CREATE OR REPLACE TEMPORARY FUNCTION',
  'CREATE TEMPORARY FUNCTION IF NOT EXISTS',
```

Wrote a helper function to generate all the above variants automatically from syntax like this:

```js
  'CREATE [OR REPLACE] [TEMP|TEMPORARY] FUNCTION [IF NOT EXISTS]',
```

It's a very simplistic utility, but at least it cuts the amount of code down drastically. The resulting list is also more readable with all this repetitive noise eliminated.

Note: the above expression also produces some invalid variants like `CREATE OR REPLACE FUNCTION IF NOT EXISTS`, but IMHO that's a very small overhead not worth worrying about. The gains in readability should win here by a lot.


Patch:
diff --git a/src/expandPhrases.ts b/src/expandPhrases.ts
new file mode 100644
index 00000000..d69438ab
--- /dev/null
+++ b/src/expandPhrases.ts
@@ -0,0 +1,73 @@
+/**
+ * Performs expandSinglePhrase() on array
+ */
+export const expandPhrases = (phrases: string[]): string[] => phrases.flatMap(expandSinglePhrase);
+
+/**
+ * Expands a syntax description like
+ *
+ *     "CREATE [OR REPLACE] [TEMP|TEMPORARY] TABLE"
+ *
+ * into an array of all possible combinations like:
+ *
+ *     [ "CREATE TABLE",
+ *       "CREATE TEMP TABLE",
+ *       "CREATE TEMPORARY TABLE",
+ *       "CREATE OR REPLACE TABLE",
+ *       "CREATE OR REPLACE TEMP TABLE",
+ *       "CREATE OR REPLACE TEMPORARY TABLE" ]
+ */
+export const expandSinglePhrase = (phrase: string): string[] =>
+  buildCombinations(parsePhrase(phrase)).map(text => text.trim());
+
+// This data type holds variants of a single part in whole phrase.
+// Corresponding to syntax as follows:
+//
+//   "TABLE"            --> ["TABLE"]
+//   "[TABLE]"          --> ["", "TABLE"]
+//   "[TEMP|TEMPORARY]" --> ["", "TEMP", "TEMPORARY"]
+//
+type PhrasePart = string[];
+
+const REQUIRED_PART = /[^[\]]+/y;
+const OPTIONAL_PART = /\[.*?\]/y;
+
+const parsePhrase = (text: string): PhrasePart[] => {
+  let index = 0;
+  const result: PhrasePart[] = [];
+  while (index < text.length) {
+    // Match everything else outside of "[...]" blocks
+    REQUIRED_PART.lastIndex = index;
+    const requiredMatch = REQUIRED_PART.exec(text);
+    if (requiredMatch) {
+      result.push([requiredMatch[0].trim()]);
+      index += requiredMatch[0].length;
+    }
+
+    // Match "[...]" block
+    OPTIONAL_PART.lastIndex = index;
+    const optionalMatch = OPTIONAL_PART.exec(text);
+    if (optionalMatch) {
+      const choices = optionalMatch[0]
+        .slice(1, -1)
+        .split('|')
+        .map(s => s.trim());
+      result.push(['', ...choices]);
+      index += optionalMatch[0].length;
+    }
+
+    if (!requiredMatch && !optionalMatch) {
+      throw new Error(`Unbalanced parenthesis in: ${text}`);
+    }
+  }
+  return result;
+};
+
+const buildCombinations = ([first, ...rest]: PhrasePart[]): string[] => {
+  if (first === undefined) {
+    return [''];
+  }
+  return buildCombinations(rest).flatMap(tail =>
+    first.map(head => head.trim() + ' ' + tail.trim())
+  );
+};
diff --git a/src/languages/bigquery/bigquery.formatter.ts b/src/languages/bigquery/bigquery.formatter.ts
index dc30d269..d7e85d34 100644
--- a/src/languages/bigquery/bigquery.formatter.ts
+++ b/src/languages/bigquery/bigquery.formatter.ts
@@ -1,10 +1,11 @@
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { EOF_TOKEN, isToken, TokenType, type Token } from 'src/lexer/token';
+import { expandPhrases } from 'src/expandPhrases';
 import { keywords } from './bigquery.keywords';
 import { functions } from './bigquery.functions';
 
-const reservedCommands = [
+const reservedCommands = expandPhrases([
   // DQL, https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
   'SELECT',
   'FROM',
@@ -20,122 +21,58 @@ const reservedCommands = [
   'WITH',
   'OMIT RECORD IF', // legacy
   // DML, https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax
-  'INSERT',
-  'INSERT INTO',
+  'INSERT [INTO]',
   'VALUES',
-  'DELETE',
-  'DELETE FROM',
+  'DELETE [FROM]',
   'TRUNCATE TABLE',
   'UPDATE',
-  'MERGE',
-  'MERGE INTO',
+  'MERGE [INTO]',
   // 'USING',
   // DDL, https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language
-  'CREATE SCHEMA',
-  'CREATE SCHEMA IF NOT EXISTS',
+  'CREATE SCHEMA [IF NOT EXISTS]',
   'DEFAULT COLLATE',
-  'CREATE TABLE',
-  'CREATE TABLE IF NOT EXISTS',
-  'CREATE TEMP TABLE',
-  'CREATE TEMP TABLE IF NOT EXISTS',
-  'CREATE TEMPORARY TABLE',
-  'CREATE TEMPORARY TABLE IF NOT EXISTS',
+  'CREATE [OR REPLACE] [TEMP|TEMPORARY|SNAPSHOT|EXTERNAL] TABLE [IF NOT EXISTS]',
   'CLUSTER BY',
-  'CREATE OR REPLACE TABLE',
-  'CREATE OR REPLACE TEMP TABLE',
-  'CREATE OR REPLACE TEMPORARY TABLE',
   'FOR SYSTEM_TIME AS OF', // CREATE SNAPSHOT TABLE
-  'CREATE SNAPSHOT TABLE',
-  'CREATE SNAPSHOT TABLE IF NOT EXISTS',
-  'CREATE VIEW',
-  'CREATE VIEW IF NOT EXISTS',
-  'CREATE OR REPLACE VIEW',
-  'CREATE MATERIALIZED VIEW',
-  'CREATE OR REPLACE MATERIALIZED VIEW',
-  'CREATE MATERIALIZED VIEW IF NOT EXISTS',
-  'CREATE EXTERNAL TABLE',
-  'CREATE OR REPLACE EXTERNAL TABLE',
-  'CREATE EXTERNAL TABLE IF NOT EXISTS',
+  'CREATE [OR REPLACE] [MATERIALIZED] VIEW [IF NOT EXISTS]',
   'WITH CONNECTION',
   'WITH PARTITION COLUMNS',
-  'CREATE FUNCTION',
-  'CREATE OR REPLACE FUNCTION',
-  'CREATE FUNCTION IF NOT EXISTS',
-  'CREATE TEMP FUNCTION',
-  'CREATE OR REPLACE TEMP FUNCTION',
-  'CREATE TEMP FUNCTION IF NOT EXISTS',
-  'CREATE TEMPORARY FUNCTION',
-  'CREATE OR REPLACE TEMPORARY FUNCTION',
-  'CREATE TEMPORARY FUNCTION IF NOT EXISTS',
+  'CREATE [OR REPLACE] [TEMP|TEMPORARY|TABLE] FUNCTION [IF NOT EXISTS]',
   'REMOTE WITH CONNECTION',
-  'CREATE TABLE FUNCTION',
-  'CREATE OR REPLACE TABLE FUNCTION',
-  'CREATE TABLE FUNCTION IF NOT EXISTS',
   'RETURNS TABLE',
-  'CREATE PROCEDURE',
-  'CREATE OR REPLACE PROCEDURE',
-  'CREATE PROCEDURE IF NOT EXISTS',
-  'CREATE ROW ACCESS POLICY',
-  'CREATE OR REPLACE ROW ACCESS POLICY',
-  'CREATE ROW ACCESS POLICY IF NOT EXISTS',
+  'CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS]',
+  'CREATE [OR REPLACE] ROW ACCESS POLICY [IF NOT EXISTS]',
   'GRANT TO',
   'FILTER USING',
   'CREATE CAPACITY',
   'AS JSON',
   'CREATE RESERVATION',
   'CREATE ASSIGNMENT',
-  'CREATE SEARCH INDEX',
-  'CREATE SEARCH INDEX IF NOT EXISTS',
-  'ALTER SCHEMA',
-  'ALTER SCHEMA IF EXISTS',
+  'CREATE SEARCH INDEX [IF NOT EXISTS]',
+  'ALTER SCHEMA [IF EXISTS]',
   'SET DEFAULT COLLATE',
   'SET OPTIONS',
-  'ALTER TABLE',
-  'ALTER TABLE IF EXISTS',
-  'ADD COLUMN',
-  'ADD COLUMN IF NOT EXISTS',
+  'ALTER TABLE [IF EXISTS]',
+  'ADD COLUMN [IF NOT EXISTS]',
   'RENAME TO',
-  'DROP COLUMN',
-  'DROP COLUMN IF EXISTS',
-  'ALTER COLUMN',
-  'ALTER COLUMN IF EXISTS',
+  'DROP COLUMN [IF EXISTS]',
+  'ALTER COLUMN [IF EXISTS]',
   'DROP NOT NULL',
   'SET DATA TYPE',
-  'ALTER VIEW',
-  'ALTER VIEW IF EXISTS',
-  'ALTER MATERIALIZED VIEW',
-  'ALTER MATERIALIZED VIEW IF EXISTS',
+  'ALTER [MATERIALIZED] VIEW [IF EXISTS]',
   'ALTER BI_CAPACITY',
-  'DROP SCHEMA',
-  'DROP SCHEMA IF EXISTS',
-  'DROP TABLE',
-  'DROP TABLE IF EXISTS',
-  'DROP SNAPSHOT TABLE',
-  'DROP SNAPSHOT TABLE IF EXISTS',
-  'DROP EXTERNAL TABLE',
-  'DROP EXTERNAL TABLE IF EXISTS',
-  'DROP VIEW',
-  'DROP VIEW IF EXISTS',
-  'DROP MATERIALIZED VIEW',
-  'DROP MATERIALIZED VIEW IF EXISTS',
-  'DROP FUNCTION',
-  'DROP FUNCTION IF EXISTS',
-  'DROP TABLE FUNCTION',
-  'DROP TABLE FUNCTION IF EXISTS',
-  'DROP PROCEDURE',
-  'DROP PROCEDURE IF EXISTS',
+  'DROP SCHEMA [IF EXISTS]',
+  'DROP [SNAPSHOT|EXTERNAL] TABLE [IF EXISTS]',
+  'DROP [MATERIALIZED] VIEW [IF EXISTS]',
+  'DROP [TABLE] FUNCTION [IF EXISTS]',
+  'DROP PROCEDURE [IF EXISTS]',
   'DROP ROW ACCESS POLICY',
   'DROP ALL ROW ACCESS POLICIES',
-  'DROP CAPACITY',
-  'DROP CAPACITY IF EXISTS',
-  'DROP RESERVATION',
-  'DROP RESERVATION IF EXISTS',
-  'DROP ASSIGNMENT',
-  'DROP ASSIGNMENT IF EXISTS',
-  'DROP SEARCH INDEX',
-  'DROP SEARCH INDEX IF EXISTS',
-  'DROP',
-  'DROP IF EXISTS',
+  'DROP CAPACITY [IF EXISTS]',
+  'DROP RESERVATION [IF EXISTS]',
+  'DROP ASSIGNMENT [IF EXISTS]',
+  'DROP SEARCH INDEX [IF EXISTS]',
+  'DROP [IF EXISTS]',
   // DCL, https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language
   'GRANT',
   'REVOKE',
@@ -166,31 +103,19 @@ const reservedCommands = [
   'ASSERT',
   // Other, https://cloud.google.com/bigquery/docs/reference/standard-sql/other-statements
   'EXPORT DATA',
-];
+]);
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases([
+  'INTERSECT [ALL | DISTINCT]',
+  'UNION [ALL | DISTINCT]',
+  'EXCEPT [ALL | DISTINCT]',
+]);
 
-const reservedJoins = [
-  'JOIN',
+const reservedJoins = expandPhrases([
+  '[LEFT | RIGHT | FULL] [OUTER] JOIN',
   'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
   'CROSS JOIN',
-];
+]);
 
 // https://cloud.google.com/bigquery/docs/reference/#standard-sql-reference
 export default class BigQueryFormatter extends Formatter {


Test patch:
diff --git a/test/unit/expandPhrases.test.ts b/test/unit/expandPhrases.test.ts
new file mode 100644
index 00000000..f559fdfd
--- /dev/null
+++ b/test/unit/expandPhrases.test.ts
@@ -0,0 +1,52 @@
+import { expandSinglePhrase } from 'src/expandPhrases';
+
+describe('expandSinglePhrase()', () => {
+  it('returns single item when no [optional blocks] found', () => {
+    expect(expandSinglePhrase('INSERT INTO')).toEqual(['INSERT INTO']);
+  });
+
+  it('expands expression with one [optional block] at the end', () => {
+    expect(expandSinglePhrase('DROP TABLE [IF EXISTS]')).toEqual([
+      'DROP TABLE',
+      'DROP TABLE IF EXISTS',
+    ]);
+  });
+
+  it('expands expression with one [optional block] at the middle', () => {
+    expect(expandSinglePhrase('CREATE [TEMPORARY] TABLE')).toEqual([
+      'CREATE TABLE',
+      'CREATE TEMPORARY TABLE',
+    ]);
+  });
+
+  it('expands expression with one [optional block] at the start', () => {
+    expect(expandSinglePhrase('[EXPLAIN] SELECT')).toEqual(['SELECT', 'EXPLAIN SELECT']);
+  });
+
+  it('expands multiple [optional] [blocks]', () => {
+    expect(expandSinglePhrase('CREATE [OR REPLACE] [MATERIALIZED] VIEW')).toEqual([
+      'CREATE VIEW',
+      'CREATE OR REPLACE VIEW',
+      'CREATE MATERIALIZED VIEW',
+      'CREATE OR REPLACE MATERIALIZED VIEW',
+    ]);
+  });
+
+  it('expands expression with [multi|choice|block]', () => {
+    expect(expandSinglePhrase('CREATE [TEMP|TEMPORARY|VIRTUAL] TABLE')).toEqual([
+      'CREATE TABLE',
+      'CREATE TEMP TABLE',
+      'CREATE TEMPORARY TABLE',
+      'CREATE VIRTUAL TABLE',
+    ]);
+  });
+
+  it('throws error when encountering unbalanced ][-braces', () => {
+    expect(() => expandSinglePhrase('CREATE [TABLE')).toThrowErrorMatchingInlineSnapshot(
+      `"Unbalanced parenthesis in: CREATE [TABLE"`
+    );
+    expect(() => expandSinglePhrase('CREATE TABLE]')).toThrowErrorMatchingInlineSnapshot(
+      `"Unbalanced parenthesis in: CREATE TABLE]"`
+    );
+  });
+});