What kind of conversion does the pre-compiler of a DBMS with the SQL language?

Question

The compiler of a DBMS handles commands written in SQL.

Assuming I’m using Mysql.

What would be done with the command:

select * from client;

What type of conversion does the pre-compiler of a DBMS with the SQL language?

Answer 1

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

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

The process

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

1. Parsing: creates an evaluation and parsing tree by following rules on the syntactic structure of the past SQL. Parser uses Bison and flex (as a comparison PHP also uses Bison for lexical analysis and Parsing).
2. Transformation: makes semantic interpretation and interpolation with data from step 1 tree.

Step 1

1. Checks if the query has a valid syntax (validation of the "pure" text that was sent)
2. If the syntax is correct an evaluation tree is mounted (parse Tree)
3. This tree is iterated with a lexical parser to recognize identifiers (SQL keywords, etc). For each keyword or identifier that is found a token is generated that is passed to the parser.

Found actions are transformed into C code.

Step 2

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

Access to the SQL query tree

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

An example in C can be seen in this Gist or in that. In Ruby you can use 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 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}}],