apb_extra_utils.sql_parser.x_sql_parser

  1#  coding=utf-8
  2#
  3#  Author: Ernesto Arredondo Martinez (ernestone@gmail.com)
  4#  Created: 7/6/19 18:23
  5#  Last modified: 25/4/18 15:27
  6#  Copyright (c) 2019
  7
  8import math
  9import re
 10
 11from sqlparse import engine, tokens as toks
 12from sqlparse.sql import TokenList, Token
 13
 14from . import x_grouping
 15
 16KEY = 'key'
 17VAL = 'val'
 18SEP = 'sep'
 19OPE = 'ope'
 20TXT = 'txt'
 21GEN = 'gen'
 22SQL = 'sql'
 23ERR = 'err'
 24GRP = 'grup'
 25STM = 'stmnt'
 26tipos_elem = {KEY: [toks.Keyword],
 27              VAL: [toks.Name,
 28                    toks.Literal,
 29                    toks.String,
 30                    toks.Number,
 31                    toks.Token.Name,
 32                    toks.Token.Literal,
 33                    toks.Token.String,
 34                    toks.Token.Literal.String.Single,
 35                    toks.Token.Literal.String.Symbol,
 36                    toks.Token.Number,
 37                    toks.Token.Literal.String.Symbol,
 38                    toks.Token.Literal.Number.Float,
 39                    toks.Token.Literal.Number.Integer],
 40              SEP: [toks.Punctuation],
 41              OPE: [toks.Operator,
 42                    toks.Comparison,
 43                    toks.Wildcard,
 44                    toks.Assignment],
 45              TXT: [toks.Comment,
 46                    toks.Text,
 47                    toks.Whitespace,
 48                    toks.Newline],
 49              GEN: [toks.Other,
 50                    toks.Generic],
 51              SQL: [toks.DML,
 52                    toks.DDL,
 53                    toks.CTE,
 54                    toks.Command],
 55              ERR: [toks.Error]}
 56
 57default_sep_txt_regex = ("[" + re.escape('"') + re.escape("'") + "_-]")
 58limit_noms_sql = 30
 59
 60
 61def format_text_to_long(txt, long_max, regex_sep_parts=default_sep_txt_regex):
 62    """
 63
 64    Args:
 65        txt:
 66        long_max:
 67        regex_sep_parts:
 68
 69    Returns:
 70        txt_res (str)
 71    """
 72    # Quitar caracteres especiales duplicados
 73    txt_res = re.sub(r"(\_|\-|\'|\")\1*", r"\1", txt)
 74    # Quitar caracteres especiales en inicio o final de palabra
 75    p_ini = re.compile("^" + default_sep_txt_regex)
 76    txt_res = p_ini.sub("", txt_res)
 77    p_fin = re.compile(default_sep_txt_regex + "$")
 78    txt_res = p_fin.sub("", txt_res)
 79
 80    num_extra_chars = len(txt_res) - long_max
 81    min_chars_part = 3
 82    txt_parts = re.split(regex_sep_parts, txt_res)
 83    l_seps = re.findall(regex_sep_parts, txt_res)
 84
 85    txt_parts.reverse()  # Se invierte orden para tratar antes las ultimas partes
 86    while num_extra_chars > 0:
 87        txt_parts_aux = [p for p in txt_parts if len(p) > min_chars_part]
 88        len_total_parts = 0
 89        for p in txt_parts_aux:
 90            len_total_parts += len(p)
 91        factor_extra_total = num_extra_chars / len_total_parts
 92
 93        for id, a_part in enumerate(txt_parts):
 94            len_part = len(a_part)
 95            if len_part > min_chars_part:
 96                rest_char = factor_extra_total * len_part
 97                if rest_char < 1:
 98                    rest_char = 1
 99                else:
100                    rest_char = math.floor(rest_char)
101
102                rest_aux = min((len_part - min_chars_part), rest_char)
103                txt_parts[id] = a_part[:-rest_aux]
104                num_extra_chars -= rest_aux
105
106            if num_extra_chars == 0:
107                break
108
109        if min_chars_part > 1:
110            min_chars_part -= 1
111        else:
112            break
113
114    txt_parts.reverse()
115
116    # Si aun habiendo reducido todas las partes a 1char supera se quitan separadores
117    num_parts = len(txt_parts) + len(l_seps)
118    if num_parts > long_max:
119        rest_parts = num_parts - long_max
120        if len(l_seps) < rest_parts:
121            l_seps = []
122        else:
123            l_seps = l_seps[:-rest_parts]
124
125    txt_res = ""
126    for i, p in enumerate(txt_parts[:-1]):
127        txt_res += p
128        if i < len(l_seps):
129            txt_res += l_seps[i]
130
131    txt_res += txt_parts[-1]
132
133    if len(txt_res) > long_max:
134        txt_res = txt_res[:long_max]
135
136    return txt_res
137
138
139def get_nom_obj_sql(nom_base, prefix="", sufix=""):
140    """
141
142    Args:
143        nom_base:
144        prefix:
145        sufix:
146
147    Returns:
148        nom_res (str)
149    """
150    len_nom = len(prefix) + len(nom_base) + len(sufix)
151    nom_res = prefix + nom_base + sufix
152    if len_nom > limit_noms_sql:
153        extra_chars = limit_noms_sql - len(prefix) - len(sufix)
154        if extra_chars > 5:
155            nom_base = format_text_to_long(nom_base, extra_chars)
156            nom_res = prefix + nom_base + sufix
157        else:
158            nom_res = format_text_to_long(nom_res, limit_noms_sql)
159
160    return nom_res.upper()
161
162
163def get_parser_sql(a_sql_file):
164    """
165
166    Args:
167        a_sql_file:
168
169    Returns:
170        xSqlParser
171    """
172    with open(a_sql_file) as a_file:
173        a_sql_text = a_file.read()
174
175    return xSqlParser(a_sql_text)
176
177
178class xElemPos(object):
179    """
180    """
181    __slots__ = ('elem', 'index')
182
183    def __init__(self, x_elem_stmnt, index_elems_stmnt):
184        self.elem = x_elem_stmnt
185        self.index = index_elems_stmnt
186
187
188class xElemStmntSql(Token):
189    """
190    Representa un componente de sentencia SQL
191    """
192    __slots__ = ('parent_stmnt', 'tipo', 'valor')
193
194    def __init__(self, parent_stmnt, token_tipo, str_val=""):
195        super(xElemStmntSql, self).__init__(token_tipo, str_val)
196
197        self.parent_stmnt = parent_stmnt
198        self.set_tipo(token_tipo)
199        self.set_val(str_val)
200
201    def set_tipo(self, token_tipo):
202        self.ttype = token_tipo
203
204        a_tipo = None
205        for k, tips in tipos_elem.items():
206            if token_tipo in tips:
207                a_tipo = k
208                break
209
210        if a_tipo is None:
211            a_tipo = GEN
212
213        self.tipo = a_tipo
214
215    def set_val(self, val):
216        self.value = str(val)
217        self.normalized = val.upper() if self.is_keyword else val
218
219        str_val = self.value.upper()
220        if not self.is_whitespace:
221            str_val = str_val.strip()
222
223        self.valor = str_val
224
225    def substitute_val(self, old_val, new_val, long_max=None):
226        if re.search(old_val, self.format_val, re.IGNORECASE):
227            set_val = re.sub(old_val, new_val, self.format_val, re.IGNORECASE)
228
229            if long_max is not None:
230                set_val = format_text_to_long(set_val, long_max)
231
232            self.set_val(set_val)
233
234    @property
235    def format_val(self):
236        str_res = re.sub(' +', ' ', self.valor)
237
238        return str_res
239
240
241class xStatementSql(TokenList):
242    """
243    Representa una sentencia SQL estructurada de tal modo que se pueda acceder
244    a sus datos caracterizadores
245    """
246    __slots__ = ('parent_stmnt', 'elems_stmnt')
247
248    def __init__(self, a_sqlparser_stmnt, parent_stmnt=None):
249        super(xStatementSql, self).__init__(a_sqlparser_stmnt.tokens)
250
251        self.parent_stmnt = parent_stmnt
252
253        self.inicializar()
254
255    def inicializar(self):
256        self.elems_stmnt = []
257
258        idx_tok = -1
259
260        while idx_tok is not None:
261            (idx_tok, tok) = self.token_next(idx_tok, False, True)
262
263            if tok is None:
264                break
265
266            self.add_token_as_x_elem(tok)
267
268    def elems_stmnt_sin_esp(self, index_ini=0, index_fi=None):
269        return list(filter(lambda el: not el.is_whitespace, self.elems_stmnt[index_ini:index_fi]))
270
271    @property
272    def format_val(self):
273        str_res = "".join([elem.format_val for elem in self.elems_stmnt])
274
275        return str_res.strip()
276
277    @property
278    def tipo(self):
279        if self.parent_stmnt is None:
280            return STM
281        else:
282            return GRP
283
284    # Retorna el primer keyword del statement que lo debería definir
285    @property
286    def keyword_principal(self):
287        for el in self.elems_stmnt_sin_esp():
288            if el.tipo == KEY:
289                return el.format_val
290
291    # Retorna el valor (ID se ha llamado) después del keyword principal
292    @property
293    def id_principal(self):
294        elem_pos = self.get_next_elem_for_key(self.keyword_principal)
295
296        if elem_pos is not None:
297            return elem_pos.elem.format_val.replace("'", "").replace('"', "")
298
299    def add_token_as_x_elem(self, tok, index=None):
300        tok_tipo = tok.ttype
301        tok_val = tok.value
302
303        if index is None:
304            index = len(self.elems_stmnt)
305
306        if issubclass(tok.__class__, TokenList):
307            new_x_elem = xStatementSql(tok, self)
308
309        else:
310            if tok.is_keyword:
311                # Se verifica si hay varios KEYWORDS seguidos con los que se construye un KEYWORD unico
312                ElemPos_ant = self.get_elem_ant()
313
314                elem_ant = None
315                idx_ant = None
316
317                if ElemPos_ant is not None:
318                    elem_ant = ElemPos_ant.elem
319                    idx_ant = ElemPos_ant.index
320
321                if elem_ant is not None and elem_ant.is_keyword:
322                    elem_ant.set_val(" ".join([elem_ant.value, tok_val]))
323                    elem_ant.set_tipo(tok_tipo)
324
325                    # Se borra espacios intermedios si los hay
326                    del self.elems_stmnt[idx_ant + 1:index]
327
328                    return
329
330            new_x_elem = xElemStmntSql(self, tok_tipo, tok_val)
331
332        self.elems_stmnt.insert(index, new_x_elem)
333
334    def get_elem_ant(self, index=None, espacios=False):
335        if index is None:
336            index = len(self.elems_stmnt)
337
338        ret = None
339        index_ant = index - 1
340        while index_ant >= 0:
341            elem_ant = self.elems_stmnt[index_ant]
342
343            if espacios or not elem_ant.is_whitespace:
344                ret = xElemPos(elem_ant, index_ant)
345                break
346
347            index_ant -= 1
348
349        return ret
350
351    def get_elem_post(self, index=None, espacios=False):
352        if index is None:
353            index = -1
354
355        idx_fi = len(self.elems_stmnt)
356
357        ret = None
358
359        index_post = index + 1
360        while index_post < idx_fi:
361            elem_post = self.elems_stmnt[index_post]
362
363            if espacios or not elem_post.is_whitespace:
364                ret = xElemPos(elem_post, index_post)
365                break
366
367            index_post += 1
368
369        return ret
370
371    def get_next_elem_for_key(self, a_keyword_sql, from_index=0):
372        for idx, elem in enumerate(self.elems_stmnt[from_index:]):
373            if elem.tipo == KEY and elem.format_val == a_keyword_sql.upper():
374                return self.get_elem_post(idx)
375
376    def get_elem_match_val(self, match_val, from_index=0):
377        for idx, elem in enumerate(self.elems_stmnt[from_index:]):
378            if re.search(match_val, elem.format_val, re.IGNORECASE):
379                if elem.tipo == GRP or elem.tipo == STM:
380                    return elem.get_elem_match_val(match_val)
381                else:
382                    return xElemPos(elem, idx)
383
384    def substitute_val(self, old_val, new_val, long_max=None):
385        for elem in self.elems_stmnt:
386            if elem.tipo == GRP or elem.tipo == VAL:
387                elem.substitute_val(old_val, new_val, long_max)
388
389
390class xSqlParser:
391    __slots__ = ('sql_statements',
392                 'sql_text',
393                 'x_statements',
394                 'func_group_orig',
395                 'engine_parser_sql')
396
397    def __init__(self, a_sql_text=""):
398        self.sql_statements = []
399        self.x_statements = []
400        self.sql_text = a_sql_text
401        self.func_group_orig = x_grouping.grouping.group
402        x_grouping.grouping.group = x_grouping.group
403        self.engine_parser_sql = engine.FilterStack()
404        self.engine_parser_sql.enable_grouping()
405
406        self.sql_statements = tuple(self.engine_parser_sql.run(self.sql_text))
407
408        # Asigna statements_clas sin Tokens inutiles (newline, space) y clasificados por tipo
409        for stmnt in self.sql_statements:
410            a_new_x_stmnt = xStatementSql(stmnt)
411
412            if a_new_x_stmnt is not None:
413                self.x_statements.append(a_new_x_stmnt)
414
415    def get_stmnt(self, key_principal, id_principal=None):
416        for a_x_stmnt in self.x_statements:
417            if a_x_stmnt.keyword_principal == key_principal.upper() and \
418                    (id_principal is None or a_x_stmnt.id_principal == id_principal.upper()):
419                return a_x_stmnt
420
421    @property
422    def as_script_sql(self):
423        a_script = ""
424        for a_stmnt in self.x_statements:
425            a_script += a_stmnt.format_val
426            a_script += "\n"
427            a_script += "/"
428            a_script += "\n"
429
430        return a_script