TODO.md

Milestones for sqlite-parser

[1.0.0] In progress

• [In Progress] Finish standardizing AST format across all types of statements
  • ORDER BY
  • LIMIT
  • name property across node types
• [In Progress] Organize tests and SQL test queries by type and split out into different files/directories.

[0.10.0] Finished

• Set proper rules for identifier names, e.g.: [a-z0-9\_\-]+

• Interactive demo editor showing SQL and corresponding AST

• Missing specs

  • Basic Drop Table
  • Basic Drop Trigger
  • Basic Function
  • Basic Subquery
  • Basic Union
  • Create Check 1
  • Create Check 2
  • Create Foreign Key 1
  • Create Foreign Key 2
  • Create Primary Key 1
  • Create Table Alt Syntax
  • Expression Like
  • Expression Table 1
  • Expression Unary 1
  • Function Mixed Args
  • Insert Into Default
  • Join Types 1
  • Join Types 2
  • Select Parts 1
  • Select Qualified Table 1
  • Transaction Rollback

• Expression grouping issues

  • Grouping with unary and binary expressions

    `anger` != null AND NOT `happiness`

    `happiness` NOT NULL AND `anger` > 0

    `happiness` IS NOT NULL AND `anger` > 0

    `happiness` ISNULL AND `anger` >

    `anger` > 0 AND `happiness` IS NOT NULL

    NOT `happiness` AND `anger` > 0

    NOT `happiness` OR ~`ANGER` AND `anger` IS NOT 0

  • Grouping with parenthesis

    SELECT *
    FROM hats
    WHERE
      (1 != 2 OR 3 != 4) AND ( 3 == 3 )

    SELECT *
    FROM hats
    WHERE
      hat OR (shirt AND (shoes OR wig) AND pants)

• Remove modifier key from all parts of AST and standardize as conditions

• Create INDEX

  • Has spec

• Create TRIGGER

  • Has spec

• Create VIEW

  • Has spec

• Create VIRTUAL table

  • Has spec
  • This currently only works with expression arguments and does not support passing column definitions and/or table constraint definitions as is allowed in the SQLite spec for virtual table module arguments.
    • FIXED: fixed by checking for a column name followed by a type definition or column constraint before assuming the type is an expression list, if these things are found, then treat the arguments as a set of source definitions as in a creation statement for a table
    • See: Virtual Tables

• Need to display correct error location when there are multiple statements in the input SQL

• comment rules should not use sym_* rules since you should not be able to put a space between the two symbols at the start and/or end of a comment.

  SELECT * - - not valid but is being accepted
  

[0.1.0] Finished

• SELECT

  • Sub-queries

    SELECT *
    FROM (
      SELECT *
      FROM b
    ) AS z

    • Has spec

  • Functions SUM(), aggregation *, etc...

    SELECT COUNT(*)
    FROM apples

    • Has spec

  • Compound queries

    SELECT *
    FROM a
    UNION
    SELECT *
    FROM b

    • Has spec

  • Alternate syntax

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

    • Has spec

  • JOIN types INNER, OUTER, LEFT

    • Joins on tables and/or sub-queries

      • Has spec

    • USING

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

      • Has spec

  • Query modifiers WHERE, GROUP BY, HAVING

    • WHERE
      • Has spec
    • FROM
      • Has spec
    • ORDER BY
      • Has spec
    • GROUP BY
      • Has spec
    • HAVING
      • Has spec
    • LIMIT
      • Has spec

• INSERT

  • Basic

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

    • Has spec

  • Default values

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

    • Has spec

  • Insert into select

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

    • Has spec

• UPDATE

  • Basic format
    • Has spec
  • Limit update format
    • Has spec

• DELETE

  • Basic format
    • Has spec
  • Limit update format
    • Has spec

• DROP

  • Has spec

• CREATE

  • Table format
    • Basic format
      • Has spec
      • Table constraints
        • PRIMARY KEY
          • Has spec
        • CHECK
          • Has spec
        • FOREIGN KEY
          • Has spec
      • Column constraints
        • PRIMARY KEY
          • Has spec
        • NOT NULL, UNIQUE
          • Has spec
        • CHECK
          • Has spec
        • DEFAULT
          • Has spec
        • COLLATE
          • Has spec
        • FOREIGN KEY
          • Has spec
  • Create table AS SELECT
    • Has spec

• ALTER TABLE

  • Has spec

• Transaction statement types

  BEGIN IMMEDIATE TRANSACTION
  CREATE TABLE foods (
    id int PRIMARY KEY,
    item varchar(50),
    size varchar(15),
    price int
  );
  
  INSERT INTO foods (item, size, id, price)
    SELECT 'banana', size, null, price
    FROM bananas
    WHERE color != 'red'
  
  COMMIT

  • BEGIN
    • Has spec
  • COMMIT, END
    • Has spec
  • ROLLBACK
    • Has spec

• Query plan EXPLAIN QUERY PLAN stmt

  • Has spec

• Multiple queries in batch

  CREATE TABLE Actors (
    name varchar(50),
    country varchar(50),
    salary integer
  );
  
  INSERT INTO Actors (name, country, salary) VALUES
    ('Vivien Leigh', 'IN', 150000),
    ('Clark Gable', 'USA', 120000),
    ('Olivia de Havilland', 'Japan', 30000),
    ('Hattie McDaniel', 'USA', 45000);

  • Full-featured (multiple, related statements) tests (have: 2)

• Indexed sources in queries

  SELECT *
  FROM bees AS b INDEXED BY bees_index

  • Has spec

• Comments

  • Line comments

    SELECT *
    FROM hats --happy table
    WHERE color = 'black'

    • Has spec

  • Block comments

    /*
     * This is a /* nested */
     * C-style block comment as allowed
     * in SQL spec
     */
    SELECT *
    FROM hats
    WHERE color = 'black'

    • Has spec

• Aliases SELECT * FROM apples AS a

  • apples AS unquoted_name

    • Has spec

  • apples no_as

    • Has spec

  • apples containsWhereKeyword and apples AS floatDatatype

    • Has spec
    • BUG: Currently, paradoxically working for all keywords everything except INT, INTEGER, INT2 but still working for BIGINT, MEDIUMINT...
      • FIXED: fixed by changing order of reserved_nodes rule symbols
    • Do not allow unquoted alias as exact match for a keyword or datatype name apples AS VARCHAR, apples AS Join

  • apples AS [inBrackets]

    • Has spec

  • ```apples AS `backticks````

    • Has spec

  • apples AS "Double Quoted with Spaces"

    • Has spec

  • Single-quoted aliases are invalid in most SQL dialects

    SELECT hat AS 'The Hat'
    FROM dinosaurs

• Expressions

  • CAST banana AS INT

    • Has spec

  • CASE

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

    • Has spec

  • Binary IN

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

    • Has spec

  • Unary

    SELECT NOT bees AS [b]
    FROM hats

    • Has spec

  • RAISE

    RAISE (ROLLBACK, 'hey there!')

    • Has spec

  • COLLATE

    bees COLLATE bees_collation

    • Has spec

  • LIKE

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

    • Has spec

  • ESCAPE

    SELECT bees NOT LIKE '%hive' ESCAPE hat > 1
    FROM hats

    • Has spec

  • Binary IS, IS NOT

    SELECT *
    FROM hats
    WHERE ham IS NOT NULL

    • Has spec

  • BETWEEN

    SELECT *
    FROM hats
    WHERE x BETWEEN 2 AND 3

    • Has spec

  • Expression lists

    SELECT expr1, expr2, expr3
    FROM hats

    • Has spec

  • Binary operation

    SELECT *
    FROM hats
    WHERE 2 != 3

    • Has spec

  • Functions

    SELECT MYFUNC(col, 1.2, 'str')

    • Has spec

  • Table expressions

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

    • Has spec

  • Logical grouping 1 == 2 AND 2 == 3

    • Has spec
    • BUG: Need to fix the grouping of expressions to allow for expressions to be logically organized.

      • Example:

        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'

• AST

  • Initial AST Format

    {
      "statement": [
        {
          "type": "statement",
          "variant": "select"
        },
        {
          "type": "statement",
          "variant": "create"
        }
      ]
    }

  • BUG: AST should output normalized (lowercased) values for case-insentive data (e.g.: datatypes, keywords, etc...)

  • ISSUE: Need to normalize format across all statement types (e.g.: CREATE TABLE, SELECT)

    • Normalize CREATE, SELECT, INSERT, UPDATE, DROP, DELETE
    • Constraint versus Clause versus Condition (Table Constraint, Column Constraint, etc...)

• Datatypes

  • 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