lpeg处理sql语法，嵌套次数太多时，内存爆炸，正在查原因，估计是pattern相加的时候TTRee每个都copy了一遍

[opendoctor]

[2021-04-12 18:10:24.484] SYSTEM 00000002: LAUNCH snlua bootstrap
[2021-04-12 18:10:24.485] SYSTEM 00000003: LAUNCH snlua launcher
[2021-04-12 18:10:24.485] SYSTEM 00000004: LAUNCH snlua cdummy
[2021-04-12 18:10:24.485] SYSTEM 00000005: LAUNCH harbor 0 4
[2021-04-12 18:10:24.485] SYSTEM 00000006: LAUNCH snlua datacenterd
[2021-04-12 18:10:24.485] SYSTEM 00000007: LAUNCH snlua service_mgr
[2021-04-12 18:10:24.485] SYSTEM 00000008: LAUNCH snlua service_provider
[2021-04-12 18:10:24.486] SYSTEM 00000009: LAUNCH snlua service_cell ltls_holder
[2021-04-12 18:10:24.490] SYSTEM 0000000a: LAUNCH snlua main 1
[2021-04-12 18:10:24.510] SYSTEM 0000000a: Memory warning 33.01 M
[2021-04-12 18:10:24.533] SYSTEM 0000000a: Memory warning 68.54 M
[2021-04-12 18:10:24.565] SYSTEM 0000000a: Memory warning 134.53 M
[2021-04-12 18:10:24.637] SYSTEM 0000000a: Memory warning 281.22 M
[2021-04-12 18:10:24.772] SYSTEM 0000000a: Memory warning 533.03 M
[2021-04-12 18:10:26.539] SYSTEM 0000000a: Memory warning 1055.58 M
[2021-04-12 18:10:29.105] DUMP (./geek/script/test.lua:365)

• "" = "10 + 1"
  [2021-04-12 18:10:29.116] SYSTEM 0000000b: LAUNCH snlua debug_console 8008
  [2021-04-12 18:10:29.116] SYSTEM 0000000b: Start debug console at 127.0.0.1:8008
  [2021-04-12 18:10:29.117] SYSTEM 00000002: KILL self

[opendoctor]

local log = require "log"

local lpeg = require "lpeg"
local C,Cb,Cc,Carg = lpeg.C,lpeg.Cb,lpeg.Cc,lpeg.Carg
local Cs,Cf,Cg,Cp = lpeg.Cs,lpeg.Cf,lpeg.Cg,lpeg.Cp
local P,V,S,R,B = lpeg.P,lpeg.V,lpeg.S,lpeg.R,lpeg.B
local locale = lpeg.locale()
local space = locale.space

local strchars = Cs((C(S("\0\b\n\r\t\26\'"")) / "\%1" + 1) ^ 0)

local upper = string.upper
local lower = string.lower
local sub = string.sub

local function ignore_case_p(k)
local pk
for i = 1,#k do
local l = lower(sub(k,i,i))
local u = upper(sub(k,i,i))
local p = S(l == u and l or l .. u)
pk = not pk and p or pk * p
end
return pk
end

local keywords = {
"select","from","into","insert","values","where","on","duplicate","update","default","key","set","delete","add",
"alter","and","or","column","create","table","drop","view","exec","join","top","distinct","union","truncate",
"rownum","left","right","outer","order","inner","by","not","is","null","having","index","procedure","unique",
"database","check","case","asc","desc","all","any","backup"
}

local word = setmetatable({},{
__index = function(t,k)
local p = ignore_case_p(k)
t[k] = p
return p
end
})

local keywords_p
for _,v in pairs(keywords) do
local p = ignore_case_p(v)
keywords_p = not keywords_p and p or keywords_p + p
end

local op = setmetatable({},{
__index = function(t,op)
local p = P(op)
t[op] = p
return p
end
})

local k_not = word["not"]
local k_in = word["in"]
local k_and = word["and"]
local k_or = word["or"]
local k_for = word["for"]
local k_is = word.is
local k_div = word.div
local k_update = word.update
local k_index = word.index
local k_key = word.key
local k_use = word.use
local k_join = word.join
local k_order = word.order
local k_group = word.group
local k_by = word.by
local k_ignore = word.ignore
local k_force = word.force
local k_inner = word.inner
local k_on = word.on
local k_duplicate = word.duplicate
local k_delete = word.delete
local k_from = word.from
local k_insert = word.insert
local k_into = word.into
local k_natural = word.natural
local k_cross = word.cross
local k_straight_join = word.straight_join
local k_low_priority = word.low_priority
local k_high_priority = word.high_priority
local k_with = word.with
local k_sql_calc_found_rows = word.sql_calc_found_rows
local k_sql_no_cache = word.sql_no_cache
local k_sql_cache = word.sql_cache
local k_lock = word.lock
local k_mode = word.mode
local k_share = word.share
local k_delayed = word.delayed
local k_distinct = word.distinct
local k_distinctrow = word.distinctrow
local k_all = word.all
local k_partition = word.partition
local k_using = word.using
local k_null = word.null
local k_values = word.values
local k_value = word.value
local k_default = word.default
local k_limit = word.limit
local k_between = word.between
local k_union = word.union
local k_having = word.having
local k_exists = word.exists
local k_where = word.where
local k_left = word.left
local k_right = word.right
local k_select = word.select
local k_set = word.set
local k_outer = word.outer
local k_replace = word.replace
local k_sql_small_result = word.sql_small_result
local k_sql_big_result = word.sql_big_result
local k_sql_buffer_result = word.sql_buffer_result
local k_oj = word.oj
local k_sounds = word.sounds
local k_like = word.like
local k_regexp = word.regexp
local k_true = word["true"]
local k_false = word["false"]
local k_unkown = word.unkown
local k_mod = word.mod
local k_collate = word.collate
local k_binary = word.binary
local k_row = word.row
local k_match = word.match
local k_case = word.case
local k_against = word.against
local k_language = word.language
local k_expansion = word.expansion
local k_query = word.query
local k_boolean = word.boolean
local k_when = word.when
local k_then = word["then"]
local k_else = word["else"]
local k_end = word["end"]
local k_interval = word.interval
local k_escape = word.escape
local k_any = word.any
local k_xor = word.xor

local function sql_pattern()
local space1 = space ^ 1
local space0 = space ^ 0
local bracket_l = P"("
local bracket_r = P")"
local nonbracket_l = (1 - (bracket_l + space))
local nonbracket_l1 = nonbracket_l^1
local quote = S"'""
local comma = P","
local star = P"*"
local number_chars = R"09"
local lower_chars = R"az"
local upper_chars = R"AZ"
local concat_chars = S"-_"
local plus_minus_chars = S"+-"
local id_quote = P"" local id_head = lower_chars + upper_chars + concat_chars local id_chars = id_head + number_chars local semicolon = P";" local exclamation = P"!" local op_equal = op["="] local bit_op = op["|"] + op["&"] + op["<<"] + op[">>"] + op["+"] + op["-"] + op["*"] + op["/"] + k_div + k_mod + op["%"] + op["^"] local compare_op = op[">="] + op["<="] + op["<"] + op[">"] + op["="] + op["!="] + op["<>"] + op["<=>"] local logic_op = k_and + k_or + k_xor + op["||"] + op["&&"] local integer = plus_minus_chars^-1 * number_chars ^ 1 local num = integer local str = quote * ((1 - quote) ^ 1) * quote local literal = str + num local word_entity = (1 - id_chars)^0 * keywords_p * (-1 + (1 - id_chars^1)) local rawidentifier = (id_quote * id_head * id_chars^0 * id_quote) + C(id_head * id_chars^0) - word_entity local identifier = (id_quote * id_head * id_chars^0 * id_quote) + (C(id_head * id_chars^0)/"%1`" - word_entity)
local alias = (word.as * space1)^-1 * identifier
local nonalias_identifier = (identifier * space0 * "." * space0)^-1 * identifier
local alias_identifier = nonalias_identifier * (space1 * alias)^-1

local tbl_name =  alias_identifier
local col_name = nonalias_identifier
local col_name_list = col_name * ((space0 * comma * space0 * col_name)^0)
local subquery = bracket_l * space0 * V"select" * space0 * bracket_r
local subquery_table = subquery * (space1 * alias) ^ -1

-- local expr_list = V"expr" * (space0 * V"expr")^0
-- local bracket_expr_list = bracket_l * space0 * expr_list * space0 * bracket_r
-- local row_simple_expr = k_row * space0 * bracket_expr_list
-- local func_call = nonbracket_l1 * space0 * bracket_l * space0 * V"expr" * space0 * bracket_r
-- local k_with_query_expansion = k_with * space1 * k_query * space1 * k_expansion
-- local k_in_natural_language_mode = k_in * space1 * k_natural * k_language * space1 * k_mode * (space1 * k_with_query_expansion)^-1
-- local k_in_boolean_mode = k_in * space1 * k_boolean * space1 * k_mode
-- local search_modifier = k_in_natural_language_mode + k_in_boolean_mode + k_with_query_expansion
-- local against = k_against * space0 * bracket_l * space0 * V"expr" * (space1 + search_modifier)^-1 * space1 * bracket_r
-- local match_expr = k_match * space0 * bracket_l * space0 * col_name_list * space0 * bracket_r * space1 * against
-- local case_when = k_when * space1 * literal * k_then * space1 * literal
-- local case_else = k_else * space1 * literal
-- local case_expr = k_case * space1 * (literal + V"expr") * space1 * case_when * (space1 * case_when)^0 * (space1 * case_else)^-1 * space1 * k_end
-- local interval_expr = k_interval * space1 * rawidentifier * space1 * rawidentifier
-- local exists_expr = k_exists * space1 * subquery
-- local prefix_simple_expr = (op["+"] + op["-"] + op["~"] + op["!"] + k_binary) * space1 * V"simple_expr"

local constant_simple_expr = literal
                -- + case_expr
                -- + exists_expr + match_expr
                -- + row_simple_expr + interval_expr + prefix_simple_expr
                -- + func_call + nonalias_identifier
local or_simple_expr = constant_simple_expr * space1 * op["||"] * space1 * V"simple_expr"
local collate = constant_simple_expr * space1 * k_collate * space1 * nonalias_identifier

local simple_expr = constant_simple_expr + collate + or_simple_expr

local bit_expr = (simple_expr * space0 * bit_op * space0 * V"bit_expr") + simple_expr

local values = V"expr" * (space0 * comma * V"expr") ^ 0
local is_in = bit_expr * space1 * (k_not * space1)^-1 * k_in * space0 * (subquery + (bracket_l * space0 * values * space0 * bracket_r))
local between = bit_expr * space1 * (k_not * space1)^-1 * k_between * space1 * bit_expr * space1 * k_and * space1 * bit_expr
local like = bit_expr * space1 * (k_not * space1)^-1 * k_like * space1 * simple_expr * (space1 * k_escape * simple_expr)^-1
local regexp = bit_expr * space1 * (k_not * space1)^-1 * k_regexp * space1 * bit_expr
local sounds_like = bit_expr * space1 * k_sounds * space1 * k_like * space1 * bit_expr
local predicate = sounds_like + regexp + like + between + is_in + bit_expr

local is_null = predicate * space1 * k_is * (space1 * k_not)^-1 * space1 * k_null
local lg_equal = predicate * space1 * op["<=>"] * space1 * predicate
local compare = predicate * space1 * compare_op * space1 * (predicate + ((k_all + k_any)^-1 * space1 * subquery))
local boolean_primary = is_null + compare + lg_equal + predicate

local is_true_false = boolean_primary * space1 * k_is * space1 * k_not^-1 * (k_true + k_false + k_unkown)
local expr = ((k_not + op["!"]) * space0 * V"expr") + is_true_false + (boolean_primary * space1 * logic_op * V"expr") + boolean_primary
local bracket_expr = (bracket_l * space0)^-1 * expr * (space0 * bracket_r)^-1

local alias_expr = expr * (space1 * alias)^-1

local k_order_by = k_order * space1 * k_by
local k_group_by = k_group * space1 * k_by
local k_with_group = k_with * space1 * k_group
local k_on_duplicate = k_on * space1 * k_duplicate * space1 * k_key * space1 * k_update

local select_expr = alias_expr
local select_expr_list = star + (select_expr * (space0 * comma * space0 * select_expr)^0)
local parition_list = V"expr" * (space0 * comma * space0 * V"expr")^0

local sort = word.desc + word.asc
local orderfield = col_name * (space1 * sort)^-1
local order_list = orderfield * (space0 * comma * orderfield)^0
local assignment = col_name * space0 * op_equal * space0 * (V"expr" + k_default)
local assignment_list = assignment * (space0 * comma * space0 * assignment)^0
local join_col_list = col_name_list
local index_name = identifier
local index_name_list = index_name * (space0 * comma * space0 * index_name)^0
local index_hint_for = k_for * space1 * (k_join + k_order_by + k_group_by)
local index_hint = (k_use + k_ignore + k_force) * space1 * (k_key + k_index) * (space1 * index_hint_for)^-1 * space1 * bracket_l * space0 * index_name_list * space0 * bracket_r
local index_hintlist = index_hint * (space0 * comma * space0 * index_hint)^0

local distinct = k_distinct + k_distinctrow + k_all
local cache = k_sql_cache + k_sql_no_cache
local priority = k_low_priority + k_delayed + k_high_priority
local lock_in_share_mode = k_lock * space1 * k_in * space1 * k_share * space1 * k_mode
local for_update = k_for * space1 * k_update
local partition = k_partition * space0 * bracket_l * space0 * parition_list * space0 * bracket_r
local group = k_group_by * space1 * order_list * (space1 * k_with_group)^-1
local order = k_order_by * space1 * order_list
local limit = k_limit * space1 * integer * (((space0 * comma * space0) + (space1 * word.offset * space1)) * integer) ^ -1

local cond_list = (bracket_l * space0)^-1 * V"expr" * (space0 * bracket_r)^-1
local where = k_where * space1 * cond_list
local having = k_having * space1 * cond_list
local union = k_union * (space1 * (k_all + k_distinct))^-1 * space1 * V"select"
local search_cond = cond_list

local join_spec = (k_on * space1 * search_cond) + (k_using * space1 * join_col_list)
local table_factor = (tbl_name * (space1 * partition)^-1 * (space1 * alias)^-1 * index_hintlist^-1) + subquery_table
local inner_cross_join = (k_inner + k_cross) * space1 * k_join * space1 * table_factor * (space1 * join_spec)
local left_right_outer_join = (k_left + k_right) * (space1 * k_outer)^-1 * space1 * k_join * space1 * V"table_ref" * space1 * join_spec 
local natural_join = k_natural * (space1 * (k_left + k_right) * (space1 * k_outer)^-1)^-1 * space1 * V"table_ref" * space1 * join_spec 
local traight_join = k_straight_join * space1 * table_factor * (space1 * k_on * search_cond)^-1
local any_join = inner_cross_join + traight_join + left_right_outer_join + natural_join
local table_ref  = (table_factor * space1 * any_join) + table_factor
local escaped_table_ref = (k_oj * space1)^-1 * table_ref
local table_ref_list = escaped_table_ref * (space0 * comma * space0 * escaped_table_ref)^0
local from = k_from * space1 * table_ref_list * (space1 * partition)^-1
local into_outfile = word.outfile * space1 * str * (word.character * space1 * k_set * space1 * identifier)^-1
local into_dumpfile = word.dumpfile * space1 * str
local into_var = identifier * (space0 * comma * space0 * identifier)^0
local into_option = k_into * space1 * (into_outfile + into_dumpfile + into_var)
local select_tail = for_update + lock_in_share_mode

local select = k_select
        * (space1 * distinct)^-1
        * (space1 * k_high_priority)^-1
        * (space1 * k_straight_join)^-1
        * (space1 * k_sql_small_result)^-1
        * (space1 * k_sql_big_result)^-1
        * (space1 * k_sql_buffer_result)^-1
        * (space1 * cache)^-1
        * (space1 * k_sql_calc_found_rows)^-1
        * space1 * select_expr_list
        * (space1 * into_option)^-1
        * space1 * from
        * (space1 * where)^-1
        * (space1 * group)^-1
        * (space1 * having)^-1
        * (space1 * order)^-1
        * (space1 * limit)^-1
        * (space1 * into_option)^-1
        * (space1 * select_tail)^-1


local delete = k_delete 
        * space1 * k_from 
        * space1 * tbl_name 
        * space1 * where


local on_duplicate = k_on_duplicate * space1 * assignment_list
local insert_fieldlist = bracket_l * space0 * col_name_list * space0 * bracket_r
local insret_valuelist = bracket_l * space0 * values * space0 * bracket_r
local insert_into = k_into^-1 * space0 * nonalias_identifier * (space1 * partition)^-1 * (space0 * insert_fieldlist)
local insert_values = (k_values + k_value) * space0 * insret_valuelist * (space0 * comma * space0 * insret_valuelist)^0
local insert = k_insert 
        * (space1 * priority)^-1
        * (space1 * k_ignore)^-1
        * space1 * insert_into
        * space1  * (insert_values + select)
        * (space1 * on_duplicate)^-1

local replace = k_replace 
        * (space1 * (k_low_priority + k_delayed))^-1
        * (space1 * insert_into)
        * space1 * (insert_values + select)

local set = k_set * space1 * assignment_list
local update_limit = k_limit * space1 * num
local update = k_update 
        * (space1 * k_low_priority)^-1
        * (space1 * k_ignore)^-1
        * space1 * nonalias_identifier 
        * space1 * set 
        * (space1 * where)^-1
        * (space1 * order)^-1
        * (space1 * update_limit)^-1

local t = {
    "bit_expr",
    s = (space0 * (V"insert" + V"select" + V"delete" + V"update" + V"replace") * (space0 * semicolon)^-1)^1,
    select = select,
    delete = delete,
    insert = insert,
    update = update,
    replace = replace,
    table_ref = table_ref,
    constant_simple_expr = constant_simple_expr,
    bit_expr = bit_expr,
    simple_expr = simple_expr,
    boolean_primary = boolean_primary,
    expr = bracket_expr,
}

local p = Cs(t)

-- local str = [[
--     select x.a,y.b,y.`from`,C(`ii`) from tt as x left join (select * from bb) y on x.a = y.a where y.a = 1 and b != x.a * 2 group by ii,jj order by ll desc,x.ll asc limit 1,100
--     ]]
local str = [[10 + 1]]

log.dump(p:match(str))

return t

end

sql_pattern()

[powerpeng]

跟 skynet 有关系吗，免费帮忙修 bug ？

[opendoctor]

最近太忙，修了一半还没搞完呢，不管是不是skynet的问题，skynet也在用，放出来大家知道也好

[aceyin]

用 lpeg 处理 SQL 语法， 是想做点啥呢？
能分享一下不， 前段时间也想搞个ORM，但是是反过来的：通过对象生成SQL， 并没有用 lpeg 来处理SQL

[sniper00]

用 lpeg 处理 SQL 语法， 是想做点啥呢？

能分享一下不， 前段时间也想搞个ORM，但是是反过来的：通过对象生成SQL， 并没有用 lpeg 来处理SQL

建议存json 现在主流数据库都支持json查询 如postgresql