Skip to content

Latest commit

 

History

History
542 lines (453 loc) · 12.6 KB

TODO.md

File metadata and controls

542 lines (453 loc) · 12.6 KB
Raw

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