輸入一條SQL語句,PostgreSQL如何解析輸入的SQL,識別SQL型別以及基表/欄位等資訊?接下來的幾節將逐一進行解析.
本節介紹了PostgreSQL的詞法定義檔案(Flex輸入檔案),在檔案src/backend/parser/scan.l中.
如前所述,Flex輸入檔案由四部分組成:
```
%{
Declarations
%}
Definitions
%%
Rules
%%
User subroutines
```
本節介紹第二部分Definitions.

### 一、Definitions
在Declarations和Rules之間的部分是Definitions,這一部分可以定義進行正規表示式的"宏定義",這些定義可在 規則（Rules）段被使用，如:
```
newline			[\n\r]
```
這樣在Rules中可以直接使用newline指代[\n\r]。
```

//各種選項設定
%option reentrant
%option bison-bridge
%option bison-locations
%option 8bit
%option never-interactive
%option nodefault
%option noinput
%option nounput
%option noyywrap
%option noyyalloc
%option noyyrealloc
%option noyyfree
%option warn
%option prefix="core_yy"

/*
 * OK, here is a short description of lex/flex rules behavior.
 * The longest pattern which matches an input string is always chosen.
 * For equal-length patterns, the first occurring in the rules list is chosen.
 * INITIAL is the starting state, to which all non-conditional rules apply.
 * Exclusive states change parsing rules while the state is active.  When in
 * an exclusive state, only those rules defined for that state apply.
 * 下面是一些lex/flex規則動作的簡單描述.
 * 通常會選中可以最大匹配輸入的字串模式.
 * 對於長度一致的模式,規則連結串列中的第一個規則會選中.
 * INITIAL是開始狀態,適用於所有非條件規則.
 *
 * We use exclusive states for quoted strings, extended comments,
 * and to eliminate parsing troubles for numeric strings.
 * Exclusive states:
 *  <xb> bit string literal
 *  <xc> extended C-style comments
 *  <xd> delimited identifiers (double-quoted identifiers)
 *  <xh> hexadecimal numeric string
 *  <xq> standard quoted strings
 *  <xe> extended quoted strings (support backslash escape sequences)
 *  <xdolq> $foo$ quoted strings
 *  <xui> quoted identifier with Unicode escapes
 *  <xuiend> end of a quoted identifier with Unicode escapes, UESCAPE can follow
 *  <xus> quoted string with Unicode escapes
 *  <xusend> end of a quoted string with Unicode escapes, UESCAPE can follow
 *  <xeu> Unicode surrogate pair in extended quoted string
 * 對於引用字串/擴充套件註釋使用獨佔的狀態,並消除數值字串在解析中存在的麻煩.
 * 獨佔的狀態包括:
 *  <xb> 位字串
 *	<xc> 擴充套件的C-style註釋
 *	<xd> 分隔識別符號(雙引號識別符號)
 *	<xh> 十六進位制數字字串
 *	<xq> 標準的帶引號的字串
 *	<xe> 擴充套件的帶引號的字串(支援反斜槓轉義序列)
 *  <xdolq> $foo$帶引號的字串
 *  <xui> 帶Unicode轉義的帶引號的識別符號
 *  <xuiend> 帶Unicode轉義的帶引號的識別符號的結尾,後跟UESCAPE
 *	<xus> 帶Unicode轉義的帶引號的字串
 *	<xueend> 帶Unicode轉義的帶引號的字串的結尾,後跟UESCAPE
 *	<xeu> 擴充套件帶引號的字串中的Unicode代理對
 *
 * Remember to add an <<eof>> case whenever you add a new exclusive state!
 * The default one is probably not the right thing.
 * 增加一個獨佔狀態時,務請記住新增<<eof>>.
 * 預設情況下可能不是正確.
 */
//INITIAL是開始狀態,其他狀態必須由%s或%x指定

%x xb
%x xc
%x xd
%x xh
%x xe
%x xq
%x xdolq
%x xui
%x xuiend
%x xus
%x xusend
%x xeu

/*
 * In order to make the world safe for Windows and Mac clients as well as
 * Unix ones, we accept either \n or \r as a newline.  A DOS-style \r\n
 * sequence will be seen as two successive newlines, but that doesn't cause
 * any problems.  Comments that start with -- and extend to the next
 * newline are treated as equivalent to a single whitespace character.
 * 對了適配Windows和Mac客戶端,\n或者\r也視為新行.
 * DOS-style的\r\n序列被視為兩個連續的新行,但這不會引起任何問題.
 * 由--開始的註釋,如果擴充套件到新行,視為單個空白字元
 *
 * NOTE a fine point: if there is no newline following --, we will absorb
 * everything to the end of the input as a comment.  This is correct.  Older
 * versions of Postgres failed to recognize -- as a comment if the input
 * did not end with a newline.
 * 注意:如果--後沒有新行,將把輸入末尾的所有內容作為註釋.
 * PG的舊版本對這種情況無法識別,--如果沒有以換行符結束,則作為註釋
 *
 * XXX perhaps \f (formfeed) should be treated as a newline as well?
 * XXX 那麼,\f也應該作為新行來處理
 *
 * XXX if you change the set of whitespace characters, fix scanner_isspace()
 * to agree, and see also the plpgsql lexer.
 * XXX 如果改變了空白字符集合,注意同步修改scanner_isspace()以適應修改後的情況,同時關注plpgsql的詞法
 */

//\t -->Tab鍵,\n -->換行,\t -->回車,\f -->換頁
space			[ \t\n\r\f]		//tab鍵/換行/回車/換頁
horiz_space		[ \t\f]			//空格/tab鍵/換頁
newline			[\n\r]			//換行/回車
non_newline		[^\n\r]			//除了換行/回車外的其他字元
//單行註釋
comment			("--"{non_newline}*)
//空白字元(1個或以上空格或者註釋均視為whitespace)
whitespace		({space}+|{comment})

/*
 * SQL requires at least one newline in the whitespace separating
 * string literals that are to be concatenated.  Silly, but who are we
 * to argue?  Note that {whitespace_with_newline} should not have * after
 * it, whereas {whitespace} should generally have a * after it...
 * SQL語句要求在分隔字串字面值的空格中至少有一行換行符,
 *   這些字串字面值將被連線起來.
 * 很傻:( 但這又有什麼好爭論的呢?
 * 注意{whitespace_with_newline}不應該在定義的後面存在*號,
 *   這裡{whitespace}通常至少在其後面跟一個*
 */
//特殊空白,1個+以上空格或註釋後跟新行
special_whitespace		({space}+|{comment}{newline})
//水平空白(一堆的空格或者註釋)
horiz_whitespace		({horiz_space}|{comment})
//0個或多個horiz_whitespace+新行+0個或多個特殊空白
whitespace_with_newline	({horiz_whitespace}*{newline}{special_whitespace}*)

/*
 * To ensure that {quotecontinue} can be scanned without having to back up
 * if the full pattern isn't matched, we include trailing whitespace in
 * {quotestop}.  This matches all cases where {quotecontinue} fails to match,
 * except for {quote} followed by whitespace and just one "-" (not two,
 * which would start a {comment}).  To cover that we have {quotefail}.
 * The actions for {quotestop} and {quotefail} must throw back characters
 * beyond the quote proper.
 * 如果全模式沒有匹配,為了確保{quotecontinue}不需要備份就可以掃描,
 *   我們在{quotestop}中包含了尾部空格.
 * 這可以匹配{quotecontinue}無法匹配的所有情況,除了{quote}後跟空格而且只有一個'-'字元的情況
 * (注意,不是兩個'-'字元,這被視為{comment}的開始)
 * 為了覆蓋含有{quotefail}的情況,{quotestop}和{quotefail}的動作必須返回超出引號的字元
 */
quote			'
quotestop		{quote}{whitespace}*
quotecontinue	{quote}{whitespace_with_newline}{quote}
quotefail		{quote}{whitespace}*"-"

//<xb>
/* Bit string 
 * It is tempting to scan the string for only those characters
 * which are allowed. However, this leads to silently swallowed
 * characters if illegal characters are included in the string.
 * For example, if xbinside is [01] then B'ABCD' is interpreted
 * as a zero-length string, and the ABCD' is lost!
 * Better to pass the string forward and let the input routines
 * validate the contents.
 * 位字串
 * 傾向於只掃描字串中允許的字元.
 * 但是這會導致如果非法字元包含在字串中時默默的接受這些非法字元.
 * 比如,如果xbinside是[01],則B'ABCD'被視為0長度的字串,並且丟失了ABCD' 
 */
xbstart			[bB]{quote}	//開始:b或B字元開頭,後跟單引號'字元
xbinside		[^']*		//字串內容:除單引號外的其他字元,0個或多個

/* Hexadecimal number */
//<xh> 十六進位制數字
xhstart			[xX]{quote}	//開始:以x或X打頭,後跟單引號
xhinside		[^']*		//內容:除單引號外的其他字元,0個或多個

/* National character */
//<xn> 國家字元(Unicode)
xnstart			[nN]{quote}	//開始:以n或N打頭

/* Quoted string that allows backslash escapes */
//<xe> 允許反斜槓跳脫字元的帶引號的字串
xestart			[eE]{quote}	//開始:e或E打頭,後跟單引號
xeinside		[^\\']+		//內容:除反斜槓和單引號外的其他字元
xeescape		[\\][^0-7]	//跳脫字元:以反斜槓打頭後跟除0-7之外的其他字元
xeoctesc		[\\][0-7]{1,3}	//八進位制跳脫字元:以反斜槓打頭後跟0-7,出現1次-3次
xehexesc		[\\]x[0-9A-Fa-f]{1,2}	//十六進位制跳脫字元:以反斜槓打頭後跟0-F/f,出現1次-2次
//Unicode字元:以反斜槓打頭,後跟u和0-F/f(連續出現4次)或者是後跟U,0-F/f連續出現8次
xeunicode		[\\](u[0-9A-Fa-f]{4}|U[0-9A-Fa-f]{8})	
//不符合xeunicode的其他情況
xeunicodefail	[\\](u[0-9A-Fa-f]{0,3}|U[0-9A-Fa-f]{0,7})

/* Extended quote
 * 擴充套件引號
 * xqdouble implements embedded quote, ''''
 * xqdouble實現了內嵌引號,''''
 */
xqstart			{quote}
xqdouble		{quote}{quote}
xqinside		[^']+

/* $foo$ style quotes ("dollar quoting")
 * The quoted string starts with $foo$ where "foo" is an optional string
 * in the form of an identifier, except that it may not contain "$",
 * and extends to the first occurrence of an identical string.
 * There is *no* processing of the quoted text.
 * $foo$型別的引號("美元引號")
 * 帶引號的字串以$foo$開始,這裡foo是一個可選的字串,
 *   但它不包含字元$,並且擴充套件到相同字串的第一次出現.
 *   擴充套件到識別符號第一次出現的地方.
 * 對於引用文字,不需要進行處理
 *
 * {dolqfailed} is an error rule to avoid scanner backup when {dolqdelim}
 * fails to match its trailing "$".
 * {dolqfailed}是一種錯誤規則,用以避免掃描器在{dolqdelim}不能匹配末尾的$時進行備份
 */
//<xdolq>
dolq_start		[A-Za-z\200-\377_]		//開始:大小寫英文字母/80-FF字元(8進位制是200-377)/下劃線			
dolq_cont		[A-Za-z\200-\377_0-9]	//dolq_start + 數字
dolqdelim		\$({dolq_start}{dolq_cont}*)?\$	//分隔符$xx$,xx可選
dolqfailed		\${dolq_start}{dolq_cont}*	//失敗:以$開始,但沒有$結束
dolqinside		[^$]+					//內容:除$外的其他字元

/* Double quote
 * Allows embedded spaces and other special characters into identifiers.
 * 雙引號
 * 允許嵌入空格和其他特殊字元
 */
//<xd>
dquote			\"				//雙引號
xdstart			{dquote}		//開始:以雙引號打頭
xdstop			{dquote}		//結束:以雙引號結束
xddouble		{dquote}{dquote}//兩個雙引號
xdinside		[^"]+			//內容:除雙引號外的其他字元,1個或多個

/* Unicode escapes */
//<xue>
//跳脫字元:
uescape			[uU][eE][sS][cC][aA][pP][eE]{whitespace}*{quote}[^']{quote}
/* error rule to avoid backup */
//錯誤規則:避免備份
uescapefail		[uU][eE][sS][cC][aA][pP][eE]{whitespace}*"-"|[uU][eE][sS][cC][aA][pP][eE]{whitespace}*{quote}[^']|[uU][eE][sS][cC][aA][pP][eE]{whitespace}*{quote}|[uU][eE][sS][cC][aA][pP][eE]{whitespace}*|[uU][eE][sS][cC][aA][pP]|[uU][eE][sS][cC][aA]|[uU][eE][sS][cC]|[uU][eE][sS]|[uU][eE]|[uU]

/* Quoted identifier with Unicode escapes */
//使用Unicode跳脫字元的引用識別符號(雙引號)
//<xui>
xuistart		[uU]&{dquote}	//開頭:以u/U打頭,後跟雙引號

/* Quoted string with Unicode escapes */
//使用Unicode跳脫字元的字串(單引號)
//<xus>
xusstart		[uU]&{quote}

/* Optional UESCAPE after a quoted string or identifier with Unicode escapes. */
//引用字串或者識別符號後可選的UESCAPE
//<>
xustop1		{uescapefail}?
xustop2		{uescape}

/* error rule to avoid backup */
//錯誤規則:避免備份
xufailed		[uU]&

/* C-style comments
 * C風格註釋
 *
 * The "extended comment" syntax closely resembles allowable operator syntax.
 * The tricky part here is to get lex to recognize a string starting with
 * slash-star as a comment, when interpreting it as an operator would produce
 * a longer match --- remember lex will prefer a longer match!  Also, if we
 * have something like plus-slash-star, lex will think this is a 3-character
 * operator whereas we want to see it as a + operator and a comment start.
 * The solution is two-fold:
 * 1. append {op_chars}* to xcstart so that it matches as much text as
 *    {operator} would. Then the tie-breaker (first matching rule of same
 *    length) ensures xcstart wins.  We put back the extra stuff with yyless()
 *    in case it contains a star-slash that should terminate the comment.
 * 2. In the operator rule, check for slash-star within the operator, and
 *    if found throw it back with yyless().  This handles the plus-slash-star
 *    problem.
 * Dash-dash comments have similar interactions with the operator rule.
 * "擴充套件註釋"語法與允許的運算子語法非常相似.
 * 這裡比較棘手的部分是讓詞法分析器可以識別以斜槓加星號開頭的字串作為註釋,
 *   因為在認為星號為運算子時可能會產生更長的匹配 -- 記住:詞法分析器傾向於更長的匹配.
 * 同時,如果存在形如+/*這樣的字串,詞法分析器會認為這是3元運算子,
 *   但其實我們希望把它視作一個加號運算子和註釋的開始.
 * 解決方案如下:
 * 1.追加{op_chars}*到xcstart中,以便它可以匹配儘可能多的文字(與{operator}一樣).
 *   然後,tie-breaker(相同長度首次匹配原則)確保xcstart會首先匹配.
 *   我們用yyless()放進去了一些額外的東西,以防它包含一個星號和斜槓(即:*/),這會終止註釋
 * 2.在運算子規則中,檢查運算子中的反斜槓+星號,如發現則返回給yyless().這可以處理+/*這個問題
 * "--"註釋與運算子規則有型別的互動方式.
 */
xcstart			\/\*{op_chars}*			//開始:/*+運算子(0個或多個)
xcstop			\*+\/					//結束:1個或多個*號+字元/
xcinside		[^*/]+					//內容:除了*和/外的其他字元,1個或多個

digit			[0-9]					//數字:0-9
ident_start		[A-Za-z\200-\377_]		//識別符號開始:英文字母/80-FF字元/下劃線
ident_cont		[A-Za-z\200-\377_0-9\$]	//識別符號:ident_start外加數字

identifier		{ident_start}{ident_cont}*	//識別符號

/* Assorted special-case operators and operator-like tokens */
//組合的特殊情況運算子和類似運算子的tokens
typecast		"::"	//強制型別轉換運算子
dot_dot			\.\.	//點點運算子
colon_equals	":="	//賦值運算子

/*
 * These operator-like tokens (unlike the above ones) also match the {operator}
 * rule, which means that they might be overridden by a longer match if they
 * are followed by a comment start or a + or - character. Accordingly, if you
 * add to this list, you must also add corresponding code to the {operator}
 * block to return the correct token in such cases. (This is not needed in
 * psqlscan.l since the token value is ignored there.)
 * 這些類運算子tokens(不同於上面所列)同時會匹配{operator}規則,
 *   這意味著如果後跟註釋起始符或者+-字元的話,它們可能會被長匹配覆蓋.
 * 因此,如果加入到連結串列中,必須同時相應的程式碼到{operator}塊中以便返回正確的token.
 * (在psqlscan.l中不需要這樣做,因為token值會被忽略)
 */
equals_greater	"=>"	//等於大於
less_equals		"<="	//小於等於
greater_equals	">="	//大於等於
less_greater	"<>"	//小於/大於
not_equals		"!="	//不等於

/*
 * "self" is the set of chars that should be returned as single-character
 * tokens.  "op_chars" is the set of chars that can make up "Op" tokens,
 * which can be one or more characters long (but if a single-char token
 * appears in the "self" set, it is not to be returned as an Op).  Note
 * that the sets overlap, but each has some chars that are not in the other.
 *
 * If you change either set, adjust the character lists appearing in the
 * rule for "operator"!
 * "self"是那些作為單字元tokens返回的字符集合.
 * "op_chars"是組成"Op" tokens(一個或多個字元)的字符集合
 *   (如果單個字元token出現在"self"中,則不會作為Op返回).
 * 注意這些集合是重複的,但是每個集合都有一些不在另外一個集合中的字元.
 * 如果改變了其中一個集合,調整出現在"operator"所設定的規則中字元列表.
 */
self			[,()\[\].;\:\+\-\*\/\%\^\\=]
op_chars		[\~\!\@\#\^\&\|\`\?\+\-\*\/\%\\=]
operator		{op_chars}+

/* we no longer allow unary minus in numbers.
 * instead we pass it separately to parser. there it gets
 * coerced via doNegate() -- Leon aug 20 1999
 * 我們不再允許一進位制負數,這些值會單獨傳遞給解析器.
 * 在那裡,會透過doNegate()方法處理.
 *
 * {decimalfail} is used because we would like "1..10" to lex as 1, dot_dot, 10.
 * {decimalfail}在處理形如1..10的情況
 *
 * {realfail1} and {realfail2} are added to prevent the need for scanner
 * backup when the {real} rule fails to match completely.
 * 新增{realfail1} 和 {realfail2}的目的是防止在{real}規則匹配失敗時的掃描器備份
 */

integer			{digit}+	//整數
decimal			(({digit}*\.{digit}+)|({digit}+\.{digit}*))	//小數
decimalfail		{digit}+\.\.	//匹配失敗的小數
real			({integer}|{decimal})[Ee][-+]?{digit}+	//實數
realfail1		({integer}|{decimal})[Ee]		//匹配失敗1
realfail2		({integer}|{decimal})[Ee][-+]	//匹配失敗2

param			\${integer}	//引數

other			.			//其他

/*
 * Dollar quoted strings are totally opaque, and no escaping is done on them.
 * Other quoted strings must allow some special characters such as single-quote
 *  and newline.
 * Embedded single-quotes are implemented both in the SQL standard
 *  style of two adjacent single quotes "''" and in the Postgres/Java style
 *  of escaped-quote "\'".
 * Other embedded escaped characters are matched explicitly and the leading
 *  backslash is dropped from the string.
 * Note that xcstart must appear before operator, as explained above!
 *  Also whitespace (comment) must appear before operator.
 * 使用$符號括起來的字串是完全密封的,在其上無任何的轉義可做.
 * 其他引用字串必須執行一些特殊字元比如單引號或者新行.
 * 嵌入式的單引號在標準SQL風格中透過兩個相鄰的單引號"''"實現,
 *   在Postgres/Java風格中使用跳脫字元"\'"實現.
 * 其他嵌入式的跳脫字元顯式匹配,打頭的反斜槓會從字串中去掉.
 * 如前所解釋過的,務必注意xcstart必須在運算子前出現.
 * 同時空白字元(註釋)必須在運算子前出現.
 */
```

### 二、參考資料
[Flex&Bison](http://web.iitd.ac.in/~sumeet/flex__bison.pdf)</xus></xui></xue></xd></xdolq></xe></xn></xh></xb></eof></eof></xeu></xueend></xus></xuiend></xui></xdolq></xe></xq></xh></xd></xc></xb></xeu></xusend></xus></xuiend></xui></xdolq></xe></xq></xh></xd></xc></xb>

PostgreSQL 原始碼解讀（169）- 查詢#89(PG中的詞法定義：scanner.l)#2

相關文章