What kind of conversion does the precompiler of a DBMS with the SQL language?

Navigation
1

The precompiler of a DBMS treats commands written in SQL.

Assuming I'm using MySQL.

What would be done with the command:

  

select * from client;

What kind of conversion does the precompiler of a DBMS with the SQL language?

    
asked by anonymous 02.11.2015 / 02:02

1 answer

3

PostgreSQL describes the internal process in the documentation, in the chapter 47 - Internal Process Overview . Practical information can be found in the Official PostgreSQL Wiki .

The source code of the parser is available on GitHub. If you understand C, I recommend analyzing the code (warning: the code represents 20 years of work, so expect huge complexity, but mastery).

The process

The "parsing stage" as it is called consists of two steps:

  • Parsing: creates an evaluation and analysis tree following rules about the last SQL syntax structure. The parser uses bison and flex (as a comparison PHP also uses bison for parsing and parsing lexical).
  • Transformation: performs semantic interpretation and interpolation with data from the tree in step 1.

    • Step 1

  • Checks whether the query has a valid syntax (validation of the "pure" text that was sent)
  • If the syntax is correct, a parse tree is mounted
  • This tree is iterated with a lex analyzer to recognize identifiers (SQL key words, etc). For each key word or identifier that is found, a token is passed to the parser.

    • The actions found are transformed into C code.

    Step 2

    In short, it makes SQL data bindings, using the structures you created in step 1.

    Accessing the tree of a SQL query

    With a C or Ruby program, you can access the trees generated in the parsing process. For this you must use the raw_parser function and can display it formatted using the nodeToString function.

    An example in C can be seen in this Gist or nesse . In Ruby you can use the Gem pg_query that generates trees in JSON.

    Example of a tree created from a query: 

    SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;
(
  {SELECT 
  :distinctClause <> 
  :intoClause <> 
  :targetList (
     {RESTARGET 
     :name <> 
     :indirection <> 
     :val 
        {COLUMNREF 
        :fields (
           {A_STAR
           }
        )
        :location 7
        }
     :location 7
     }
  )
  :fromClause (
     {RANGEVAR 
     :schemaname <> 
     :relname foo 
     :inhOpt 2 
     :relpersistence p 
     :alias <> 
     :location 14
     }
  )
  :whereClause 
     {AEXPR  
     :name ("=")
     :lexpr 
        {COLUMNREF 
        :fields ("bar")
        :location 24
        }
     :rexpr 
        {A_CONST 
        :val 42 
        :location 30
        }
     :location 28
     }
  :groupClause <> 
  :havingClause <> 
  :windowClause <> 
  :valuesLists <> 
  :sortClause (
     {SORTBY 
     :node 
        {COLUMNREF 
        :fields ("id")
        :location 42
        }
     :sortby_dir 2 
     :sortby_nulls 0 
     :useOp <> 
     :location -1
     }
  )
  :limitOffset <> 
  :limitCount 
     {A_CONST 
     :val 23 
     :location 56
     }
  :lockingClause <> 
  :withClause <> 
  :op 0 
  :all false 
  :larg <> 
  :rarg <>
  }
)

    And an example of a tree generated using Gem pg_query: 

    #<PgQuery:0x000000009673a0
@query="SELECT * FROM foo where bar = 42 ORDER BY id DESC LIMIT 23;",
@parsetree=
 [{"SELECT"=>
    {"distinctClause"=>nil,
     "intoClause"=>nil,
     "targetList"=>
      [{"RESTARGET"=>
         {"name"=>nil,
          "indirection"=>nil,
          "val"=>{"COLUMNREF"=>{"fields"=>[{"A_STAR"=>{}}], "location"=>7}},
          "location"=>7}}],
     "fromClause"=>
      [{"RANGEVAR"=>
         {"schemaname"=>nil,
          "relname"=>"foo",
          "inhOpt"=>2,
          "relpersistence"=>"p",
          "alias"=>nil,
          "location"=>14}}],
     "whereClause"=>
      {"AEXPR"=>
        {"name"=>["="],
         "lexpr"=>{"COLUMNREF"=>{"fields"=>["bar"], "location"=>24}},
         "rexpr"=>{"A_CONST"=>{"val"=>42, "location"=>30}},
         "location"=>28}},
     "groupClause"=>nil,
     "havingClause"=>nil,
     "windowClause"=>nil,
     "valuesLists"=>nil,
     "sortClause"=>
      [{"SORTBY"=>
         {"node"=>{"COLUMNREF"=>{"fields"=>["id"], "location"=>42}},
          "sortby_dir"=>2,
          "sortby_nulls"=>0,
          "useOp"=>nil,
          "location"=>-1}}],
     "limitOffset"=>nil,
     "limitCount"=>{"A_CONST"=>{"val"=>23, "location"=>56}},
     "lockingClause"=>nil,
     "withClause"=>nil,
     "op"=>0,
     "all"=>false,
     "larg"=>nil,
     "rarg"=>nil}}],
        
    02.11.2015 / 14:47
    How to display the contents of a queue? Internal Error Error UID of script is smaller than min_uid