<commit_before># Milestones for sql-query-parser

## **[0.1.0]** In progress
- [x] `SELECT`
  - [ ] Sub-queries `SELECT * FROM (SELECT * FROM b)`
  - [x] Functions, aggregation, etc... `SELECT COUNT(*) FROM apples`
  - [ ] Compound queries `SELECT * FROM a UNION SELECT * FROM b`
  - [ ] Aliases `SELECT * FROM apples AS a`
    - [x] `apples AS unquoted_name`
    - [x] `apples no_as`
    - [x] `apples containsWhereKeyword` and `apples AS floatDatatype`
      - [ ] **BUG**: Currently, paradoxically working for all keywords everything except `INT`, `INTEGER`, `INT2` but still working for `BIGINT`, `MEDIUMINT`...
      - [x] Do not allow **unquoted** alias as exact match for a keyword or datatype name `apples AS VARCHAR`, `apples AS Join`
    - [x] `apples AS [inBrackets]`
    - [x] ```apples AS `backticks````
    - [x] `apples AS "Double Quoted with Spaces"`
  - [ ] `JOIN` types `INNER`, `OUTER`, `LEFT`
  - [x] Query modifiers `WHERE`, `GROUP BY`, `HAVING`
    - [x] `WHERE`
    - [x] `FROM`
    - [x] `ORDER BY`
    - [x] `GROUP BY`
    - [x] `HAVING`
- [x] `INSERT`
  - [x] `INSERT INTO ... VALUES (...), (...)`
  - [x] `INSERT INTO ... DEFAULT VALUES`
  - [x] `INSERT INTO ... SELECT * FROM apples`
- [ ] `UPDATE`
- [ ] `DELETE`
- [ ] `DROP`
- [ ] `CREATE`
- [ ] Expressions `1 != 2`, `CAST banana AS INT`
  - [x] `CAST banana AS INT`
  - [x] `CASE WHEN apple > 1 THEN 'YES' ELSE 'NO' END`
  - [x] `bees NOT IN (SELECT * FROM apples)`
  - [ ] additional tests for each expression type needed
  - [x] **BUG**: Need to fix the grouping of expressions to allow for expressions to be logically organized.
    - Example: `WHERE 1 < 2 AND 3 < 4`

      > ```
      >
      >           AND                            <
      >       /         \         versus     /       \
      >      <           <                  1        AND
      >   /     \     /     \                      /     \
      >  1       2   3       4                    2       <
      >                                                /     \
      >                                               3       4
      > ```

    - **FIXED: now grouping correctly when using binary AND / OR**

- [ ] Datatypes
  - [x] SQLite

    | Expression                  | Resulting Affinity |
    |:--------------------------- | ------------------:|
    | INT                         | INTEGER            |
    | INTEGER                     | INTEGER            |
    | TINYINT                     | INTEGER            |
    | SMALLINT                    | INTEGER            |
    | MEDIUMINT                   | INTEGER            |
    | BIGINT                      | INTEGER            |
    | UNSIGNED BIG                | INTEGER            |
    | INT2                        | INTEGER            |
    | INT8	                      | INTEGER            |
    | CHARACTER(20)               | TEXT               |
    | VARCHAR(255)                | TEXT               |
    | VARYING CHARACTER(255)      | TEXT               |
    | NCHAR(55)                   | TEXT               |
    | NATIVE CHARACTER(70)        | TEXT               |
    | NVARCHAR(100)               | TEXT               |
    | TEXT                        | TEXT               |
    | CLOB	                      | TEXT               |
    | BLOB                        | NONE               |
    | no datatype specified	      | NONE               |
    | REAL                        | REAL               |
    | DOUBLE                      | REAL               |
    | DOUBLE PRECISION            | REAL               |
    | FLOAT	                      | REAL               |
    | NUMERIC                     | NUMERIC            |
    | DECIMAL(10,5)               | NUMERIC            |
    | BOOLEAN                     | NUMERIC            |
    | DATE                        | NUMERIC            |
    | DATETIME	                  | NUMERIC            |

  - [ ] PostgreSQL

    | Name                        | Aliases            |
    |:--------------------------- | ------------------:|
    | bigint                      | int8	             |
    | bigserial	                  | serial8	           |
    | bit [ (n) ]	 	              |                    |
    | bit varying [ (n) ]	        | varbit	           |
    | boolean	                    | bool	             |
    | box	 	                      |                    |
    | bytea	 	                    |                    |
    | character [ (n) ]	          | char [ (n) ]       |
    | character varying [ (n) ]   |	varchar [ (n) ]    |
    | cidr                        |                    |
    | circle                      |                    |
    | date                        |                    |
    | double precision	          | float8             |
    | inet	 	                    |                    |
    | integer	                    | int, int4	         |
    | interval [ fields ] [ (p) ] |                    |
    | json                        |                    |
    | line	 	                    |                    |
    | lseg	                      |                    |
    | macaddr	                    |                    |
    | money	                      |                    |
    | numeric [ (p, s) ]          | decimal [ (p, s) ] |
    | path                        |                    |
    | point                       |                    |
    | polygon	                    |                    |
    | real	                      | float4             |
    | smallint                    | int2               |
    | smallserial	                | serial2            |
    | serial                      | serial4            |
    | text                        |                    |
    | time [ (p) ] 	              | timetz             |
    | timestamp [ (p) ]           | timestamptz	      |
    | tsquery                     |                    |
    | tsvector                    |                    |
    | txid_snapshot	              |                    |
    | uuid                        |                    |
    | xml                         |                    |
<commit_msg>updated TODO.md with examples of expression types and query modifiers. refs #1<commit_after># Milestones for sql-query-parser

## **[0.1.0]** In progress
- [x] `SELECT` **TODO: Need tests**
  - [ ] Sub-queries

    ``` sql
    SELECT *
    FROM (
      SELECT *
      FROM b
    ) AS z
    ```

    - [ ] *Has spec*
  - [x] Functions `SUM()`, aggregation `*`, etc...

    ``` sql
    SELECT COUNT(*)
    FROM apples
    ```

    - [ ] *Has spec*
  - [ ] Compound queries

    ``` sql
    SELECT *
    FROM a
    UNION
    SELECT *
    FROM b
    ```

      - [ ] *Has spec*

  - [x] Alternate syntax

    ``` sql
    VALUES (1, 2, 'hat')
    ORDER BY id DESC
    ```

  - [x] `JOIN` types `INNER`, `OUTER`, `LEFT` **TODO: Need tests**
    - [x] Joins on tables and/or sub-queries
      - [ ] *Has spec*
    - [x] `USING`

      ``` sql
      SELECT *
      FROM bees
        JOIN inventory AS i USING i.name, i.type
      ```

      - [ ] *Has spec*

  - [x] Query modifiers `WHERE`, `GROUP BY`, `HAVING` **TODO: Need tests**
    - [x] `WHERE`
      - [ ] *Has spec*
    - [x] `FROM`
      - [ ] *Has spec*
    - [x] `ORDER BY`
      - [ ] *Has spec*
    - [x] `GROUP BY`
      - [ ] *Has spec*
    - [x] `HAVING`  
      - [ ] *Has spec*
    - [ ] `LIMIT`
      - [ ] *Has spec*
- [x] `INSERT` **TODO: Need tests**
  - [x] Basic

    ``` sql
    INSERT INTO bees (a, b, c)
    VALUES (1, 2, 'hey'), (2, 3, 'yo')
    ```

    - [ ] *Has spec*
  - [x] Default values

    ``` sql
    INSERT INTO apples (a, b, c)
    DEFAULT VALUES
    ```

    - [ ] *Has spec*
  - [x] Insert into select

    ``` sql
    INSERT INTO apples (a, b, c)
    SELECT * FROM apples
    ```

    - [ ] *Has spec*
- [ ] `UPDATE`
- [ ] `DELETE`
- [ ] `DROP`
- [ ] `CREATE`
- [ ] Indexed sources in queries

  ``` sql
  SELECT *
  FROM bees AS b INDEXED BY bees_index
  ```

- [ ] Aliases `SELECT * FROM apples AS a` **TODO: Need tests**
  - [x] `apples AS unquoted_name`
    - [x] *Has spec*
  - [x] `apples no_as`
    - [x] *Has spec*
  - [x] `apples containsWhereKeyword` and `apples AS floatDatatype`
    - [x] *Has spec*
    - [ ] **BUG**: Currently, paradoxically working for all keywords everything except `INT`, `INTEGER`, `INT2` but still working for `BIGINT`, `MEDIUMINT`...
    - [x] Do not allow **unquoted** alias as exact match for a keyword or datatype name `apples AS VARCHAR`, `apples AS Join`
  - [x] `apples AS [inBrackets]`
    - [x] *Has spec*
  - [x] ```apples AS `backticks````
    - [x] *Has spec*
  - [x] `apples AS "Double Quoted with Spaces"`
    - [x] *Has spec*
- [ ] Expressions  **TODO: Need tests**
  - [x] `CAST banana AS INT`
    - [x] *Has spec*
  - [x] `CASE`

    ``` sql
    SELECT CASE WHEN apple > 1 THEN 'YES' ELSE 'NO' END
    FROM apples
    ```

    - [x] *Has spec*
  - [x] Binary `IN`

    ``` sql  
    SELECT *
    FROM hats
    WHERE bees NOT IN (SELECT * FROM apples)
    ```

    - [x] *Has spec*
  - [ ] Unary

    ``` sql
    SELECT NOT bees AS [b]
    FROM hats
    ```

    - [ ] *Has spec*
  - [ ] `RAISE`

    ``` sql
    RAISE (ROLLBACK, 'hey there!')
    ```

    - [ ] *Has spec*
  - [ ] `COLLATE`

    ``` sql
    bees COLLATE bees_collation
    ```

    - [ ] *Has spec*
  - [ ] `LIKE`

    ``` sql
    SELECT *
    FROM hats
    WHERE bees LIKE '%somebees%'
    ```

    - [ ] *Has spec*
  - [ ] `ESCAPE`

    ``` sql
    SELECT ESCAPE expr
    FROM hats
    ```

    - [ ] *Has spec*
  - [ ] Binary `IS`, `IS NOT`

    ``` sql
    SELECT *
    FROM hats
    WHERE ham IS NOT NULL
    ```

    - [ ] *Has spec*
  - [ ] `BETWEEN`

    ``` sql
    SELECT *
    FROM hats
    WHERE x BETWEEN 2 AND 3
    ```

    - [ ] *Has spec*
  - [x] Expression lists

    ``` sql
    SELECT expr1, expr2, expr3
    FROM hats
    ```

    - [x] *Has spec*
  - [ ] Binary operation

    ``` sql
    SELECT *
    FROM hats
    WHERE 2 != 3
    ```

    - [ ] *Has spec*
  - [ ] Functions

    ``` sql
    SELECT MYFUNC(col, 1.2, 'str')
    ```
    - [ ] *Has spec*
  - [ ] Table expressions

    ``` sql
    WITH ham AS (
      SELECT type
      FROM hams
    )
    SELECT *
    FROM inventory
      INNER JOIN ham
        ON inventory.variety = ham.type
    ```

      - [ ] *Has spec*

  - [x] Logical grouping `1 == 2 AND 2 == 3`
    - [x] *Has spec*
    - [x] **BUG**: Need to fix the grouping of expressions to allow for expressions to be logically organized.
      - Example:

        ``` sql
        SELECT *
        FROM bees
        WHERE 1 < 2 AND 3 < 4
        ```

        > ```
        >
        >           AND                            <
        >       /         \         versus     /       \
        >      <           <                  1        AND
        >   /     \     /     \                      /     \
        >  1       2   3       4                    2       <
        >                                                /     \
        >                                               3       4
        > ```

      - **FIXED: now grouping correctly when using binary AND / OR**
- [ ] Literals
  - [ ] `'string'`
  - [ ] Decimal, Hex, Exponent `12`, `1.2`, `1E-9`, `0xe1e3`
  - [ ] Signed number `-2.001`
- [ ] Bind parameters
  - [ ] Numbered `?`, `?12`
  - [ ] Named `@bees`
  - [ ] TCL `$hey "Hey There"`
- [ ] BLOB `X'stuff'`
- [ ] Datatypes
  - [x] SQLite

    | Expression                  | Resulting Affinity |
    |:--------------------------- | ------------------:|
    | INT                         | INTEGER            |
    | INTEGER                     | INTEGER            |
    | TINYINT                     | INTEGER            |
    | SMALLINT                    | INTEGER            |
    | MEDIUMINT                   | INTEGER            |
    | BIGINT                      | INTEGER            |
    | UNSIGNED BIG                | INTEGER            |
    | INT2                        | INTEGER            |
    | INT8	                      | INTEGER            |
    | CHARACTER(20)               | TEXT               |
    | VARCHAR(255)                | TEXT               |
    | VARYING CHARACTER(255)      | TEXT               |
    | NCHAR(55)                   | TEXT               |
    | NATIVE CHARACTER(70)        | TEXT               |
    | NVARCHAR(100)               | TEXT               |
    | TEXT                        | TEXT               |
    | CLOB	                      | TEXT               |
    | BLOB                        | NONE               |
    | no datatype specified	      | NONE               |
    | REAL                        | REAL               |
    | DOUBLE                      | REAL               |
    | DOUBLE PRECISION            | REAL               |
    | FLOAT	                      | REAL               |
    | NUMERIC                     | NUMERIC            |
    | DECIMAL(10,5)               | NUMERIC            |
    | BOOLEAN                     | NUMERIC            |
    | DATE                        | NUMERIC            |
    | DATETIME	                  | NUMERIC            |

  - [ ] PostgreSQL

    | Name                        | Aliases            |
    |:--------------------------- | ------------------:|
    | bigint                      | int8	             |
    | bigserial	                  | serial8	           |
    | bit [ (n) ]	 	              |                    |
    | bit varying [ (n) ]	        | varbit	           |
    | boolean	                    | bool	             |
    | box	 	                      |                    |
    | bytea	 	                    |                    |
    | character [ (n) ]	          | char [ (n) ]       |
    | character varying [ (n) ]   |	varchar [ (n) ]    |
    | cidr                        |                    |
    | circle                      |                    |
    | date                        |                    |
    | double precision	          | float8             |
    | inet	 	                    |                    |
    | integer	                    | int, int4	         |
    | interval [ fields ] [ (p) ] |                    |
    | json                        |                    |
    | line	 	                    |                    |
    | lseg	                      |                    |
    | macaddr	                    |                    |
    | money	                      |                    |
    | numeric [ (p, s) ]          | decimal [ (p, s) ] |
    | path                        |                    |
    | point                       |                    |
    | polygon	                    |                    |
    | real	                      | float4             |
    | smallint                    | int2               |
    | smallserial	                | serial2            |
    | serial                      | serial4            |
    | text                        |                    |
    | time [ (p) ] 	              | timetz             |
    | timestamp [ (p) ]           | timestamptz	      |
    | tsquery                     |                    |
    | tsvector                    |                    |
    | txid_snapshot	              |                    |
    | uuid                        |                    |
    | xml                         |                    |

{'fraction_non_alphanumeric': 0.12642036231413545, 'fraction_numerical': 0.00824621777806636, 'mean_word_length': 0.7464565143440299, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 95, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5050369', 'n_tokens_mistral': 4702, 'n_tokens_neox': 4242, 'n_words': 1072}