##
# Dialect,
# a simple and flexible Cross-Platform & Cross-Vendor SQL Query Builder for PHP, Python, JavaScript
#
# @version: 1.4.0
# https://github.com/foo123/Dialect
#
# Abstract the construction of SQL queries
# Support multiple DB vendors
# Intuitive and Flexible API
##
# https://github.com/foo123/StringTemplate
import re, math, time
#import random
NEWLINE = re.compile(r'\n\r|\r\n|\n|\r')
SQUOTE = re.compile(r"'")
T_REGEXP = type(SQUOTE)
def pad(s, n, z = '0', pad_right = False):
ps = str(s)
if pad_right:
while len(ps) < n: ps += z
else:
while len(ps) < n: ps = z + ps
return ps
GUID = 0
def guid():
global GUID
GUID += 1
return pad(hex(int(time.time()))[2:],12)+'__'+pad(hex(GUID)[2:],4)#+'__'+pad(hex(random.randint(0, 1000))[2:],4)
def createFunction(args, sourceCode, additional_symbols = dict()):
# http://code.activestate.com/recipes/550804-create-a-restricted-python-function-from-a-string/
funcName = 'dialect_dyna_func_' + guid()
# The list of symbols that are included by default in the generated
# function's environment
SAFE_SYMBOLS = [
"list", "dict", "enumerate", "tuple", "set", "long", "float", "object",
"bool", "callable", "True", "False", "dir",
"frozenset", "getattr", "hasattr", "abs", "cmp", "complex",
"divmod", "id", "pow", "round", "slice", "vars",
"hash", "hex", "int", "isinstance", "issubclass", "len",
"map", "filter", "max", "min", "oct", "chr", "ord", "range",
"reduce", "repr", "str", "type", "zip", "xrange", "None",
"Exception", "KeyboardInterrupt"
]
# Also add the standard exceptions
__bi = __builtins__
if type(__bi) is not dict:
__bi = __bi.__dict__
for k in __bi:
if k.endswith("Error") or k.endswith("Warning"):
SAFE_SYMBOLS.append(k)
del __bi
# Include the sourcecode as the code of a function funcName:
s = "def " + funcName + "(%s):\n" % args
s += sourceCode # this should be already properly padded
# Byte-compilation (optional)
byteCode = compile(s, "<string>", 'exec')
# Setup the local and global dictionaries of the execution
# environment for __TheFunction__
bis = dict() # builtins
globs = dict()
locs = dict()
# Setup a standard-compatible python environment
bis["locals"] = lambda: locs
bis["globals"] = lambda: globs
globs["__builtins__"] = bis
globs["__name__"] = "SUBENV"
globs["__doc__"] = sourceCode
# Determine how the __builtins__ dictionary should be accessed
if type(__builtins__) is dict:
bi_dict = __builtins__
else:
bi_dict = __builtins__.__dict__
# Include the safe symbols
for k in SAFE_SYMBOLS:
# try from current locals
try:
locs[k] = locals()[k]
continue
except KeyError:
pass
# Try from globals
try:
globs[k] = globals()[k]
continue
except KeyError:
pass
# Try from builtins
try:
bis[k] = bi_dict[k]
except KeyError:
# Symbol not available anywhere: silently ignored
pass
# Include the symbols added by the caller, in the globals dictionary
globs.update(additional_symbols)
# Finally execute the Function statement:
eval(byteCode, globs, locs)
# As a result, the function is defined as the item funcName
# in the locals dictionary
fct = locs[funcName]
# Attach the function to the globals so that it can be recursive
del locs[funcName]
globs[funcName] = fct
# Attach the actual source code to the docstring
fct.__doc__ = sourceCode
# return the compiled function object
return fct
class StringTemplate:
"""
StringTemplate for Python,
https://github.com/foo123/StringTemplate
"""
VERSION = '1.0.0'
guid = guid
createFunction = createFunction
def multisplit(tpl, reps, as_array = False):
a = [ [1, tpl] ]
reps = enumerate(reps) if as_array else reps.items()
for r,s in reps:
c = []
sr = s if as_array else r
s = [0, s]
for ai in a:
if 1 == ai[0]:
b = ai[1].split(sr)
bl = len(b)
c.append([1, b[0]])
if bl > 1:
for bj in b[1:]:
c.append(s)
c.append([1, bj])
else:
c.append(ai)
a = c
return a
def multisplit_re(tpl, rex):
a = []
i = 0
m = rex.search(tpl, i)
while m:
a.append([1, tpl[i:m.start()]])
try:
mg = m.group(1)
except:
mg = m.group(0)
is_numeric = False
try:
mn = int(mg,10)
is_numeric = False if math.isnan(mn) else True
except ValueError:
is_numeric = False
a.append([0, mn if is_numeric else mg])
i = m.end()
m = rex.search(tpl, i)
a.append([1, tpl[i:]])
return a
def arg(key = None, argslen = None):
out = 'args'
if None != key:
if isinstance(key,str):
key = key.split('.') if len(key) else []
else:
key = [key]
#givenArgsLen = bool(None !=argslen and isinstance(argslen,str))
for k in key:
is_numeric = False
try:
kn = int(k,10) if isinstance(k,str) else k
is_numeric = False if math.isnan(kn) else True
except ValueError:
is_numeric = False
if is_numeric:
out += '[' + str(kn) + ']';
else:
out += '["' + str(k) + '"]';
return out
def compile(tpl, raw = False):
global NEWLINE
global SQUOTE
if True == raw:
out = 'return ('
for tpli in tpl:
notIsSub = tpli[0]
s = tpli[1]
out += s if notIsSub else StringTemplate.arg(s)
out += ')'
else:
out = 'return ('
for tpli in tpl:
notIsSub = tpli[0]
s = tpli[1]
if notIsSub: out += "'" + re.sub(NEWLINE, "' + \"\\n\" + '", re.sub(SQUOTE, "\\'", s)) + "'"
else: out += " + str(" + StringTemplate.arg(s,"argslen") + ") + "
out += ')'
return createFunction('args', " " + out)
defaultArgs = re.compile(r'\$(-?[0-9]+)')
def __init__(self, tpl = '', replacements = None, compiled = False):
global T_REGEXP
self.id = None
self.tpl = None
self._renderer = None
self._args = [tpl,StringTemplate.defaultArgs if not replacements else replacements,compiled]
self._parsed = False
def __del__(self):
self.dispose()
def dispose(self):
self.id = None
self.tpl = None
self._renderer = None
self._args = None
self._parsed = None
return self
def parse(self):
if self._parsed is False:
# lazy init
tpl = self._args[0]
replacements = self._args[1]
compiled = self._args[2]
self._args = None
self.tpl = StringTemplate.multisplit_re(tpl, replacements) if isinstance(replacements, T_REGEXP) else StringTemplate.multisplit(tpl, replacements)
self._parsed = True
if compiled is True: self._renderer = StringTemplate.compile(self.tpl)
return self
def render(self, args = None):
if None == args: args = []
if self._parsed is False:
# lazy init
self.parse()
if callable(self._renderer): return self._renderer(args)
out = ''
for t in self.tpl:
if 1 == t[0]: out += t[1]
else:
s = t[1]
out += '' if s not in args else str(args[s])
return out
# https://github.com/foo123/GrammarTemplate
def compute_alignment(s, i, l):
alignment = ''
while i < l:
c = s[i]
if (" " == c) or ("\r" == c) or ("\t" == c) or ("\v" == c) or ("\0" == c):
alignment += c
i += 1
else:
break
return alignment
def align(s, alignment):
l = len(s)
if l and len(alignment):
aligned = '';
for c in s:
aligned += c
if "\n" == c: aligned += alignment
else:
aligned = s
return aligned
def walk(obj, keys, keys_alt = None, obj_alt = None):
found = 0
if keys:
o = obj
l = len(keys)
i = 0
found = 1
while i < l:
k = keys[i]
i += 1
if o is not None:
if isinstance(o,(list,tuple)) and int(k)<len(o):
o = o[int(k)]
elif isinstance(o,dict) and (k in o):
o = o[k]
else:
try:
o = getattr(o, k)
except AttributeError:
found = 0
break
else:
found = 0
break
if (not found) and keys_alt:
o = obj
l = len(keys_alt)
i = 0
found = 1
while i < l:
k = keys_alt[i]
i += 1
if o is not None:
if isinstance(o,(list,tuple)) and int(k)<len(o):
o = o[int(k)]
elif isinstance(o,dict) and (k in o):
o = o[k]
else:
try:
o = getattr(o, k)
except AttributeError:
found = 0
break
else:
found = 0
break
if (not found) and (obj_alt is not None) and (obj_alt is not obj):
if keys:
o = obj_alt
l = len(keys)
i = 0
found = 1
while i < l:
k = keys[i]
i += 1
if o is not None:
if isinstance(o,(list,tuple)) and int(k)<len(o):
o = o[int(k)]
elif isinstance(o,dict) and (k in o):
o = o[k]
else:
try:
o = getattr(o, k)
except AttributeError:
found = 0
break
else:
found = 0
break
if (not found) and keys_alt:
o = obj_alt
l = len(keys_alt)
i = 0
found = 1
while i < l:
k = keys_alt[i]
i += 1
if o is not None:
if isinstance(o,(list,tuple)) and int(k)<len(o):
o = o[int(k)]
elif isinstance(o,dict) and (k in o):
o = o[k]
else:
try:
o = getattr(o, k)
except AttributeError:
found = 0
break
else:
found = 0
break
return o if found else None
class StackEntry:
def __init__(self, stack = None, value = None):
self.prev = stack
self.value = value
class TplEntry:
def __init__(self, node = None, tpl = None ):
if tpl: tpl.next = self
self.node = node
self.prev = tpl
self.next = None
def multisplit(tpl, delims, postop = False):
IDL = delims[0]
IDR = delims[1]
OBL = delims[2]
OBR = delims[3]
lenIDL = len(IDL)
lenIDR = len(IDR)
lenOBL = len(OBL)
lenOBR = len(OBR)
ESC = '\\'
OPT = '?'
OPTR = '*'
NEG = '!'
DEF = '|'
COMMENT = '#'
TPL = ':='
REPL = '{'
REPR = '}'
DOT = '.'
REF = ':'
ALGN = '@'
#NOTALGN = '&'
COMMENT_CLOSE = COMMENT+OBR
default_value = None
negative = 0
optional = 0
aligned = 0
localised = 0
l = len(tpl)
delim1 = [IDL, lenIDL, IDR, lenIDR]
delim2 = [OBL, lenOBL, OBR, lenOBR]
delim_order = [None,0,None,0,None,0,None,0]
postop = postop is True
a = TplEntry({'type': 0, 'val': '', 'algn': ''})
cur_arg = {
'type' : 1,
'name' : None,
'key' : None,
'stpl' : None,
'dval' : None,
'opt' : 0,
'neg' : 0,
'algn' : 0,
'loc' : 0,
'start' : 0,
'end' : 0
}
roottpl = a
block = None
opt_args = None
subtpl = {}
cur_tpl = None
arg_tpl = {}
start_tpl = None
# hard-coded merge-sort for arbitrary delims parsing based on str len
if delim1[1] < delim1[3]:
s = delim1[0]
delim1[2] = delim1[0]
delim1[0] = s
i = delim1[1]
delim1[3] = delim1[1]
delim1[1] = i
if delim2[1] < delim2[3]:
s = delim2[0]
delim2[2] = delim2[0]
delim2[0] = s
i = delim2[1]
delim2[3] = delim2[1]
delim2[1] = i
start_i = 0
end_i = 0
i = 0
while (4 > start_i) and (4 > end_i):
if delim1[start_i+1] < delim2[end_i+1]:
delim_order[i] = delim2[end_i]
delim_order[i+1] = delim2[end_i+1]
end_i += 2
else:
delim_order[i] = delim1[start_i]
delim_order[i+1] = delim1[start_i+1]
start_i += 2
i += 2
while 4 > start_i:
delim_order[i] = delim1[start_i]
delim_order[i+1] = delim1[start_i+1]
start_i += 2
i += 2
while 4 > end_i:
delim_order[i] = delim2[end_i]
delim_order[i+1] = delim2[end_i+1]
end_i += 2
i += 2
stack = None
s = ''
i = 0
while i < l:
c = tpl[i]
if ESC == c:
s += tpl[i+1] if i+1 < l else ''
i += 2
continue
delim = None
if delim_order[0] == tpl[i:i+delim_order[1]]:
delim = delim_order[0]
elif delim_order[2] == tpl[i:i+delim_order[3]]:
delim = delim_order[2]
elif delim_order[4] == tpl[i:i+delim_order[5]]:
delim = delim_order[4]
elif delim_order[6] == tpl[i:i+delim_order[7]]:
delim = delim_order[6]
if IDL == delim:
i += lenIDL
if len(s):
if 0 == a.node['type']: a.node['val'] += s
else: a = TplEntry({'type': 0, 'val': s, 'algn': ''}, a)
s = ''
elif IDR == delim:
i += lenIDR
# argument
argument = s
s = ''
p = argument.find(DEF)
if -1 < p:
default_value = argument[p+1:]
argument = argument[0:p]
else:
default_value = None
if postop:
c = tpl[i] if i < l else ''
else:
c = argument[0]
if OPT == c or OPTR == c:
optional = 1
if OPTR == c:
start_i = 1
end_i = -1
else:
start_i = 0
end_i = 0
if postop:
i += 1
if (i < l) and (NEG == tpl[i]):
negative = 1
i += 1
else:
negative = 0
else:
if NEG == argument[1]:
negative = 1
argument = argument[2:]
else:
negative = 0
argument = argument[1:]
elif REPL == c:
if postop:
s = ''
j = i+1
jl = l
while (j < jl) and (REPR != tpl[j]):
s += tpl[j]
j += 1
i = j+1
else:
s = ''
j = 1
jl = len(argument)
while (j < jl) and (REPR != argument[j]):
s += argument[j]
j += 1
argument = argument[j+1:]
s = s.split(',')
if len(s) > 1:
start_i = s[0].strip()
start_i = int(start_i,10) if len(start_i) else 0
end_i = s[1].strip()
end_i = int(end_i,10) if len(end_i) else -1
optional = 1
else:
start_i = s[0].strip()
start_i = int(start_i,10) if len(start_i) else 0
end_i = start_i
optional = 0
s = ''
negative = 0
else:
optional = 0
negative = 0
start_i = 0
end_i = 0
if negative and default_value is None: default_value = ''
c = argument[0]
if ALGN == c:
aligned = 1
argument = argument[1:]
else:
aligned = 0
c = argument[0]
if DOT == c:
localised = 1
argument = argument[1:]
else:
localised = 0
p = argument.find(REF)
template = argument.split(REF) if -1 < p else [argument,None]
argument = template[0]
template = template[1]
p = argument.find(DOT)
nested = argument.split(DOT) if -1 < p else None
if cur_tpl and (cur_tpl not in arg_tpl): arg_tpl[cur_tpl] = {}
if TPL+OBL == tpl[i:i+2+lenOBL]:
# template definition
i += 2
template = template if template and len(template) else 'grtpl--'+guid()
start_tpl = template
if cur_tpl and len(argument):
arg_tpl[cur_tpl][argument] = template
if not len(argument): continue # template definition only
if (template is None) and cur_tpl and (cur_tpl in arg_tpl) and (argument in arg_tpl[cur_tpl]):
template = arg_tpl[cur_tpl][argument]
if optional and not cur_arg['opt']:
cur_arg['name'] = argument
cur_arg['key'] = nested
cur_arg['stpl'] = template
cur_arg['dval'] = default_value
cur_arg['opt'] = optional
cur_arg['neg'] = negative
cur_arg['algn'] = aligned
cur_arg['loc'] = localised
cur_arg['start'] = start_i
cur_arg['end'] = end_i
# handle multiple optional arguments for same optional block
opt_args = StackEntry(None, [argument,nested,negative,start_i,end_i,optional,localised])
elif optional:
# handle multiple optional arguments for same optional block
if (start_i != end_i) and (cur_arg['start'] == cur_arg['end']):
# set as main arg a loop arg, if exists
cur_arg['name'] = argument
cur_arg['key'] = nested
cur_arg['stpl'] = template
cur_arg['dval'] = default_value
cur_arg['opt'] = optional
cur_arg['neg'] = negative
cur_arg['algn'] = aligned
cur_arg['loc'] = localised
cur_arg['start'] = start_i
cur_arg['end'] = end_i
opt_args = StackEntry(opt_args, [argument,nested,negative,start_i,end_i,optional,localised])
elif (not optional) and (cur_arg['name'] is None):
cur_arg['name'] = argument
cur_arg['key'] = nested
cur_arg['stpl'] = template
cur_arg['dval'] = default_value
cur_arg['opt'] = 0
cur_arg['neg'] = negative
cur_arg['algn'] = aligned
cur_arg['loc'] = localised
cur_arg['start'] = start_i
cur_arg['end'] = end_i
# handle multiple optional arguments for same optional block
opt_args = StackEntry(None, [argument,nested,negative,start_i,end_i,0,localised])
if 0 == a.node['type']: a.node['algn'] = compute_alignment(a.node['val'], 0, len(a.node['val']))
a = TplEntry({
'type' : 1,
'name' : argument,
'key' : nested,
'stpl' : template,
'dval' : default_value,
'opt' : optional,
'algn' : aligned,
'loc' : localised,
'start' : start_i,
'end' : end_i
}, a)
elif OBL == delim:
i += lenOBL
if len(s):
if 0 == a.node['type']: a.node['val'] += s
else: a = TplEntry({'type': 0, 'val': s, 'algn': ''}, a)
s = ''
# comment
if COMMENT == tpl[i]:
j = i+1
jl = l
while (j < jl) and (COMMENT_CLOSE != tpl[j:j+lenOBR+1]):
s += tpl[j]
j += 1
i = j+lenOBR+1
if 0 == a.node['type']: a.node['algn'] = compute_alignment(a.node['val'], 0, len(a.node['val']))
a = TplEntry({'type': -100, 'val': s}, a)
s = ''
continue
# optional block
stack = StackEntry(stack, [a, block, cur_arg, opt_args, cur_tpl, start_tpl])
if start_tpl: cur_tpl = start_tpl
start_tpl = None
cur_arg = {
'type' : 1,
'name' : None,
'key' : None,
'stpl' : None,
'dval' : None,
'opt' : 0,
'neg' : 0,
'algn' : 0,
'loc' : 0,
'start' : 0,
'end' : 0
}
opt_args = None
a = TplEntry({'type': 0, 'val': '', 'algn': ''})
block = a
elif OBR == delim:
i += lenOBR
b = a
cur_block = block
prev_arg = cur_arg
prev_opt_args = opt_args
if stack:
a = stack.value[0]
block = stack.value[1]
cur_arg = stack.value[2]
opt_args = stack.value[3]
cur_tpl = stack.value[4]
start_tpl = stack.value[5]
stack = stack.prev
else:
a = None
if len(s):
if 0 == b.node['type']: b.node['val'] += s
else: b = TplEntry({'type': 0, 'val': s, 'algn': ''}, b)
s = ''
if start_tpl:
subtpl[start_tpl] = TplEntry({
'type' : 2,
'name' : prev_arg['name'],
'key' : prev_arg['key'],
'loc' : prev_arg['loc'],
'algn' : prev_arg['algn'],
'start' : prev_arg['start'],
'end' : prev_arg['end'],
'opt_args': None,#opt_args
'tpl' : cur_block
})
start_tpl = None
else:
if 0 == a.node['type']: a.node['algn'] = compute_alignment(a.node['val'], 0, len(a.node['val']))
a = TplEntry({
'type' : -1,
'name' : prev_arg['name'],
'key' : prev_arg['key'],
'loc' : prev_arg['loc'],
'algn' : prev_arg['algn'],
'start' : prev_arg['start'],
'end' : prev_arg['end'],
'opt_args': prev_opt_args,
'tpl' : cur_block
}, a)
else:
c = tpl[i]
i += 1
if "\n" == c:
# note line changes to handle alignments
if len(s):
if 0 == a.node['type']: a.node['val'] += s
else: a = TplEntry({'type': 0, 'val': s, 'algn': ''}, a)
s = ''
if 0 == a.node['type']: a.node['algn'] = compute_alignment(a.node['val'], 0, len(a.node['val']))
a = TplEntry({'type': 100, 'val': "\n"}, a)
else:
s += c
if len(s):
if 0 == a.node['type']: a.node['val'] += s
else: a = TplEntry({'type': 0, 'val': s, 'algn': ''}, a)
if 0 == a.node['type']: a.node['algn'] = compute_alignment(a.node['val'], 0, len(a.node['val']))
return [roottpl, subtpl]
def optional_block(args, block, SUB = None, FN = None, index = None, alignment = '', orig_args = None):
out = ''
block_arg = None
if -1 == block['type']:
# optional block, check if optional variables can be rendered
opt_vars = block['opt_args']
# if no optional arguments, render block by default
if opt_vars and opt_vars.value[5]:
while opt_vars:
opt_v = opt_vars.value
opt_arg = walk(args, opt_v[1], [str(opt_v[0])], None if opt_v[6] else orig_args)
if (block_arg is None) and (block['name'] == opt_v[0]): block_arg = opt_arg
if ((0 == opt_v[2]) and (opt_arg is None)) or ((1 == opt_v[2]) and (opt_arg is not None)): return ''
opt_vars = opt_vars.prev
else:
block_arg = walk(args, block['key'], [str(block['name'])], None if block['loc'] else orig_args)
arr = is_array(block_arg)
lenn = len(block_arg) if arr else -1
#if not block['algn']: alignment = ''
if arr and (lenn > block['start']):
rs = block['start']
re = lenn-1 if -1==block['end'] else min(block['end'],lenn-1)
ri = rs
while ri <= re:
out += main(args, block['tpl'], SUB, FN, ri, alignment, orig_args)
ri += 1
elif (not arr) and (block['start'] == block['end']):
out = main(args, block['tpl'], SUB, FN, None, alignment, orig_args)
return out
def non_terminal(args, symbol, SUB = None, FN = None, index = None, alignment = '', orig_args = None):
out = ''
if symbol['stpl'] and ((SUB and (symbol['stpl'] in SUB)) or (symbol['stpl'] in GrammarTemplate.subGlobal) or (FN and ((symbol['stpl'] in FN) or ('*' in FN))) or ((symbol['stpl'] in GrammarTemplate.fnGlobal) or ('*' in GrammarTemplate.fnGlobal))):
# using custom function or sub-template
opt_arg = walk(args, symbol['key'], [str(symbol['name'])], None if symbol['loc'] else orig_args)
if (SUB and (symbol['stpl'] in SUB)) or (symbol['stpl'] in GrammarTemplate.subGlobal):
# sub-template
if (index is not None) and ((0 != index) or (symbol['start'] != symbol['end']) or (not symbol['opt'])) and is_array(opt_arg):
opt_arg = opt_arg[index] if index < len(opt_arg) else None
if (opt_arg is None) and (symbol['dval'] is not None):
# default value if missing
out = symbol['dval']
else:
# try to associate sub-template parameters to actual input arguments
tpl = SUB[symbol['stpl']].node if SUB and (symbol['stpl'] in SUB) else GrammarTemplate.subGlobal[symbol['stpl']].node
tpl_args = {}
if opt_arg is not None:
if is_array(opt_arg): tpl_args[tpl['name']] = opt_arg
else: tpl_args = opt_arg
out = optional_block(tpl_args, tpl, SUB, FN, None, alignment if symbol['algn'] else '', args if orig_args is None else orig_args)
#if symbol['algn']: out = align(out, alignment)
else:#elif fn:
# custom function
fn = None
if FN and (symbol['stpl'] in FN): fn = FN[symbol['stpl']]
elif FN and ('*' in FN): fn = FN['*']
elif symbol['stpl'] in GrammarTemplate.fnGlobal: fn = GrammarTemplate.fnGlobal[symbol['stpl']]
elif '*' in GrammarTemplate.fnGlobal: fn = GrammarTemplate.fnGlobal['*']
if is_array(opt_arg):
index = index if index is not None else symbol['start']
opt_arg = opt_arg[index] if index < len(opt_arg) else None
if callable(fn):
fn_arg = {
#'value' : opt_arg,
'symbol' : symbol,
'index' : index,
'currentArguments' : args,
'originalArguments' : orig_args,
'alignment' : alignment
}
opt_arg = fn(opt_arg, fn_arg)
else:
opt_arg = str(fn)
out = symbol['dval'] if (opt_arg is None) and (symbol['dval'] is not None) else str(opt_arg)
if symbol['algn']: out = align(out, alignment)
elif symbol['opt'] and (symbol['dval'] is not None):
# boolean optional argument
out = symbol['dval']
else:
# plain symbol argument
opt_arg = walk(args, symbol['key'], [str(symbol['name'])], None if symbol['loc'] else orig_args)
# default value if missing
if is_array(opt_arg):
index = index if index is not None else symbol['start']
opt_arg = opt_arg[ index ] if index < len(opt_arg) else None
out = symbol['dval'] if (opt_arg is None) and (symbol['dval'] is not None) else str(opt_arg)
if symbol['algn']: out = align(out, alignment)
return out
def main(args, tpl, SUB = None, FN = None, index = None, alignment = '', orig_args = None):
out = ''
current_alignment = alignment
while tpl:
tt = tpl.node['type']
if -1 == tt: # optional code-block
out += optional_block(args, tpl.node, SUB, FN, index, current_alignment if tpl.node['algn'] else alignment, orig_args)
elif 1 == tt: # non-terminal
out += non_terminal(args, tpl.node, SUB, FN, index, current_alignment if tpl.node['algn'] else alignment, orig_args)
elif 0 == tt: # terminal
current_alignment += tpl.node['algn']
out += tpl.node['val']
elif 100 == tt: # new line
current_alignment = alignment
out += "\n" + alignment
#elif -100 == tt: # comment
# # pass
tpl = tpl.next
return out
class GrammarTemplate:
"""
GrammarTemplate for Python,
https://github.com/foo123/GrammarTemplate
"""
VERSION = '3.0.0'
defaultDelimiters = ['<','>','[',']']
fnGlobal = {}
subGlobal = {}
guid = guid
multisplit = multisplit
align = align
main = main
def __init__(self, tpl = '', delims = None, postop = False):
self.id = None
self.tpl = None
self.fn = {}
# lazy init
self._args = [tpl, delims if delims else GrammarTemplate.defaultDelimiters, postop]
def __del__(self):
self.dispose()
def dispose(self):
self.id = None
self.tpl = None
self.fn = None
self._args = None
return self
def parse(self):
if (self.tpl is None) and (self._args is not None):
# lazy init
self.tpl = GrammarTemplate.multisplit(self._args[0], self._args[1], self._args[2])
self._args = None
return self
def render(self, args = None):
# lazy init
if self.tpl is None: self.parse()
return GrammarTemplate.main({} if None == args else args, self.tpl[0], self.tpl[1], self.fn)
import copy
NULL_CHAR = chr(0)
def is_int(v):
return isinstance(v, int)
def is_float(v):
return isinstance(v, float)
def is_string(v):
return isinstance(v, str)
def is_obj(v):
return isinstance(v, dict)
def is_array(v):
return isinstance(v, (list,tuple))
def array(v):
return v if isinstance(v, list) else (list(v) if isinstance(v, tuple) else [v])
def empty(v):
return (isinstance(v, (tuple,list,str,dict)) and 0 == len(v)) or not v
def addslashes(s, chars = None, esc = '\\'):
global NULL_CHAR
s2 = ''
if chars is None: chars = '\\"\'' + NULL_CHAR
for c in s:
s2 += c if c not in chars else ('\\0' if 0 == ord(c) else (esc+c))
return s2
def defaults(data, defau, overwrite = False, array_copy = False):
overwrite = overwrite is True
array_copy = array_copy is True
for k in defau:
if overwrite or not(k in data):
data[k] = defau[k][:] if array_copy and isinstance(defau[k], (list,tuple)) else defau[k]
return data
def map_join(arr, prop, sep = ','):
joined = ''
if arr and len(arr):
joined = str(getattr(arr[0], prop))
for i in range(1,len(arr)): joined += sep + str(getattr(arr[i], prop))
return joined
#def filter( data, filt, positive=True ):
# if positive is not False:
# filtered = { }
# for field in filt:
# if field in data:
# filtered[field] = data[field]
# return filtered
# else:
# filtered = { }
# for field in data:
# if field not in filt:
# filtered[field] = data[field]
# return filtered
Ref_spc_re = re.compile(r'\s')
Ref_num_re = re.compile(r'[0-9]')
Ref_alf_re = re.compile(r'[a-z_]', re.I)
class Ref:
def parse(r, d):
# catch passing instance as well
if isinstance(r, Ref): return r
global Ref_spc_re
global Ref_num_re
global Ref_alf_re
# should handle field formats like:
# [ F1(..Fn( ] [[dtb.]tbl.]col [ )..) ] [ AS alias ]
# and/or
# ( ..subquery.. ) [ AS alias]
# and extract alias, dtb, tbl, col identifiers (if present)
# and also extract F1,..,Fn function identifiers (if present)
r = r.strip()
l = len(r)
i = 0
stacks = [[]]
stack = stacks[0]
ids = []
funcs = []
keywords2 = ['AS']
# 0 = SEP, 1 = ID, 2 = FUNC, 5 = Keyword, 10 = *, 100 = Subtree
s = ''
err = None
paren = 0
quote = None
paren2 = 0
quote2 = None
quote2pos = None
subquery = None
while i < l:
ch = r[i]
i += 1
if '('==ch and 1==i:
# ( ..subquery.. ) [ AS alias]
paren2+=1
continue
if 0 < paren2:
# ( ..subquery.. ) [ AS alias]
if '"' == ch or '`' == ch or '\'' == ch or '[' == ch or ']' == ch:
if not quote2:
quote2 = ']' if '[' == ch else ch
quote2pos = i-1
elif quote2 == ch:
dbl_quote = (('"'==ch or '`'==ch) and (d.qn[3]==ch+ch)) or ('\''==ch and d.q[3]==ch+ch)
esc_quote = (('"'==ch or '`'==ch) and (d.qn[3]=='\\'+ch)) or ('\''==ch and d.q[3]=='\\'+ch)
if dbl_quote and (i<l) and (ch==r[i]):
# double-escaped quote in identifier or string
i+=1
elif esc_quote:
# maybe-escaped quote in string
escaped = False
# handle special case of " ESCAPE '\' "
if (-1!=d.e[1].find("'\\'")) and ("'\\'"==r[quote2pos:i]):
pass
else:
# else find out if quote is escaped or not
j = i-2
while 0<=j and '\\'==r[j]:
escaped = not escaped
j-=1
if not escaped:
quote2 = None
quote2pos = None
else:
quote2 = None
quote2pos = None
continue
elif quote2:
continue
elif '(' == ch:
paren2+=1
continue
elif ')' == ch:
paren2-=1
if 0 > paren2:
err = ['paren',i]
break
elif 0 == paren2:
if quote2:
err = ['quote',i]
break
subquery = r[0:i]
s = subquery
continue
else:
continue
else:
continue
else:
# [ F1(..Fn( ] [[dtb.]tbl.]col [ )..) ] [ AS alias ]
if '"' == ch or '`' == ch or '\'' == ch or '[' == ch or ']' == ch:
# sql quote
if not quote:
if len(s) or (']' == ch):
err = ['invalid',i]
break
quote = ']' if '[' == ch else ch
continue
elif quote == ch:
if (i<l) and (ch==r[i]):
# double-escaped quote in identifier
s += ch
i += 1
continue
else:
if len(s):
stack.insert(0,[1, s])
ids.insert(0,s)
s = ''
else:
err = ['invalid',i]
break
quote = None
continue
elif quote:
s += ch
continue
if quote:
# part of sql-quoted value
s += ch
continue
if '*' == ch:
# placeholder
if len(s):
err = ['invalid',i]
break
stack.insert(0,[10, '*'])
ids.insert(0,10)
elif '.' == ch:
# separator
if len(s):
stack.insert(0,[1, s])
ids.insert(0,s)
s = ''
if not len(stack) or 1 != stack[0][0]:
# error, mismatched separator
err = ['invalid',i]
break
stack.insert(0,[0, '.'])
ids.insert(0,0)
elif '(' == ch:
# left paren
paren += 1
if len(s):
# identifier is function
stack.insert(0,[2, s])
funcs.insert(0,s)
s = ''
if not len(stack) or (2 != stack[0][0] and 1 != stack[0][0]):
err = ['invalid',i]
break
if 1 == stack[0][0]:
stack[0][0] = 2
funcs.insert(0,ids.pop(0))
stacks.insert(0,[])
stack = stacks[0]
elif ')' == ch:
# right paren
paren -= 1
if len(s):
keyword = s.upper() in keywords2
stack.insert(0,[5 if keyword else 1, s])
ids.insert(0, 5 if keyword else s)
s = ''
if len(stacks) < 2:
err = ['invalid',i]
break
# reduce
stacks[1].insert(0,[100, stacks.pop(0)])
stack = stacks[0]
elif Ref_spc_re.match(ch):
# space separator
if len(s):
keyword = s.upper() in keywords2
stack.insert(0,[5 if keyword else 1, s])
ids.insert(0, 5 if keyword else s)
s = ''
continue
elif Ref_num_re.match(ch):
if not len(s):
err = ['invalid',i]
break
# identifier
s += ch
elif Ref_alf_re.match(ch):
# identifier
s += ch
else:
err = ['invalid',i]
break
if len(s):
stack.insert(0,[1, s])
ids.insert(0,s)
s = ''
if not err and (paren or paren2): err = ['paren', l]
if not err and (quote or quote2): err = ['quote', l]
if not err and 1 != len(stacks): err = ['invalid', l]
if err:
err_pos = err[1]-1
err_type = err[0]
if 'paren' == err_type:
# error, mismatched parentheses
raise ValueError('Dialect: Mismatched parentheses "'+r+'" at position '+str(err_pos)+'.')
elif 'quote' == err_type:
# error, mismatched quotes
raise ValueError('Dialect: Mismatched quotes "'+r+'" at position '+str(err_pos)+'.')
else:# if 'invalid' == err_type:
# error, invalid character
raise ValueError('Dialect: Invalid character "'+r+'" at position '+str(err_pos)+'.')
alias = None
alias_q = ''
if subquery is not None:
if (len(ids) >= 3) and (5 == ids[1]) and isinstance(ids[0],str):
alias = ids.pop(0)
alias_q = d.quote_name(alias)
ids.pop(0)
col = subquery
col_q = subquery
tbl = None
tbl_q = ''
dtb = None
dtb_q = ''
tbl_col = col
tbl_col_q = col_q
else:
if (len(ids) >= 3) and (5 == ids[1]) and isinstance(ids[0],str):
alias = ids.pop(0)
alias_q = d.quote_name(alias)
ids.pop(0)
col = None
col_q = ''
if len(ids) and (isinstance(ids[0],str) or 10 == ids[0]):
if 10 == ids[0]:
ids.pop(0)
col = col_q = '*'
else:
col = ids.pop(0)
col_q = d.quote_name(col)
tbl = None
tbl_q = ''
if (len(ids) >= 2) and (0 == ids[0]) and isinstance(ids[1],str):
ids.pop(0)
tbl = ids.pop(0)
tbl_q = d.quote_name(tbl)
dtb = None
dtb_q = ''
if (len(ids) >= 2) and (0 == ids[0]) and isinstance(ids[1],str):
ids.pop(0)
dtb = ids.pop(0)
dtb_q = d.quote_name(dtb)
tbl_col = (dtb+'.' if dtb else '') + (tbl+'.' if tbl else '') + (col if col else '')
tbl_col_q = (dtb_q+'.' if dtb else '') + (tbl_q+'.' if tbl else '') + (col_q if col else '')
return Ref(col, col_q, tbl, tbl_q, dtb, dtb_q, alias, alias_q, tbl_col, tbl_col_q, funcs)
def __init__(self, _col, col, _tbl, tbl, _dtb, dtb, _alias, alias, _qual, qual, _func = list()):
self._col = _col
self.col = col
self._tbl = _tbl
self.tbl = tbl
self._dtb = _dtb
self.dtb = dtb
self._alias = _alias
self._qualified =_qual
self.qualified = qual
self.full = self.qualified
self._func = [] if not _func else _func
if len(self._func):
for f in self._func: self.full = f+'('+self.full+')'
if self._alias is not None:
self.alias = alias
self.aliased = self.full + ' AS ' + self.alias
else:
self.alias = self.full
self.aliased = self.full
def cloned(self, alias = None, alias_q = None, func = None):
if alias is None and alias_q is None:
alias = self._alias
alias_q = self.alias
elif alias is not None:
alias_q = alias if alias_q is None else alias_q
if func is None:
func = self._func
return Ref(self._col, self.col, self._tbl, self.tbl, self._dtb, self.dtb, alias, alias_q,
self._qualified, self.qualified, func)
def __del__(self):
self.dispose()
def dispose(self):
self._func = None
self._col = None
self.col = None
self._tbl = None
self.tbl = None
self._dtb = None
self.dtb = None
self._alias = None
self.alias = None
self._qualified = None
self.qualified = None
self.full = None
self.aliased = None
return self
class Dialect:
"""
Dialect for Python,
https://github.com/foo123/Dialect
"""
VERSION = '1.4.0'
#TPL_RE = re.compile(r'\$\(([^\)]+)\)')
StringTemplate = StringTemplate
GrammarTemplate = GrammarTemplate
Ref = Ref
dialects = {
# https://dev.mysql.com/doc/refman/8.0/en/
"mysql" : {
"quotes" : [ ["'","'","\\'","\\'"], ["`","`","``","``"], ["","","",""] ]
,"functions" : {
"strpos" : ["POSITION(",2," IN ",1,")"]
,"strlen" : ["LENGTH(",1,")"]
,"strlower" : ["LCASE(",1,")"]
,"strupper" : ["UCASE(",1,")"]
,"trim" : ["TRIM(",1,")"]
,"quote" : ["QUOTE(",1,")"]
,"random" : "RAND()"
,"now" : "NOW()"
}
# https://dev.mysql.com/doc/refman/8.0/en/data-types.html
,"types" : {
"SMALLINT" : ["TINYINT(",[1,'255'],") UNSIGNED"]
,"SIGNED_SMALLINT": ["TINYINT(",[1,'255'],")"]
,"INT" : ["INT(",[1,'255'],") UNSIGNED"]
,"SIGNED_INT" : ["INT(",[1,'255'],")"]
,"BIGINT" : ["BIGINT(",[1,'255'],") UNSIGNED"]
,"SIGNED_BIGINT" : ["BIGINT(",[1,'255'],")"]
,"FLOAT" : ["FLOAT(",[1,'24'],")"]
,"DOUBLE" : ["FLOAT(",[1,'53'],")"]
,"BOOL" : "TINYINT(1)"
,"TIMESTAMP" : "TIMESTAMP"
,"DATETIME" : "DATETIME"
,"DATE" : "DATE"
,"TIME" : "TIME"
,"VARBINARY" : ["VARBINARY(",[1,'255'],")"]
,"VARCHAR" : ["VARCHAR(",[1,'255'],")"]
,"TEXT" : "TEXT"
,"BLOB" : "BLOB"
,"JSON" : "JSON"
}
,"clauses" : {
"start_transaction" : "START TRANSACTION <type|>;",
"commit_transaction" : "COMMIT;",
"rollback_transaction" : "ROLLBACK;",
"transact" : "START TRANSACTION <type|>;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]",
"create" : "[<?view|>CREATE VIEW <create_table> [(\n<?columns>[,\n<*columns>]\n)] AS <query>][<?!view>CREATE[ <?temporary|>TEMPORARY] TABLE[ <?ifnotexists|>IF NOT EXISTS] <create_table> [(\n<?columns>:=[<col:COL>:=[[[CONSTRAINT <?constraint> ]UNIQUE KEY <name|> <type|> (<?uniquekey>[,<*uniquekey>])][[CONSTRAINT <?constraint> ]PRIMARY KEY <type|> (<?primarykey>)][[<?!index>KEY][<?index|>INDEX] <name|> <type|> (<?key>[,<*key>])][CHECK (<?check>)][<?column> <type>[ <?!isnull><?isnotnull|>NOT NULL][ <?!isnotnull><?isnull|>NULL][ DEFAULT <?default_value>][ <?auto_increment|>AUTO_INCREMENT][ <?!primary><?unique|>UNIQUE KEY][ <?!unique><?primary|>PRIMARY KEY][ COMMENT '<?comment>'][ COLUMN_FORMAT <?format>][ STORAGE <?storage>]]][,\n<*col:COL>]]\n)][ <?options>:=[<opt:OPT>:=[[ENGINE=<?engine>][AUTO_INCREMENT=<?auto_increment>][CHARACTER SET=<?charset>][COLLATE=<?collation>]][, <*opt:OPT>]]][\nAS <?query>]]",
"alter" : "ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]",
"drop" : "DROP [<?view|>VIEW][<?!view>[<?temporary|>TEMPORARY ]TABLE][ <?ifexists|>IF EXISTS] <drop_tables>[,<*drop_tables>]",
"union" : "(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]",
"select" : "SELECT <select_columns>[,<*select_columns>]\nFROM <from_tables>[,<*from_tables>][\n<?join_clauses>:=[<join:JOIN>:=[[<?type> ]JOIN <table>[ ON <?cond>]][\n<*join:JOIN>]]][\nWHERE <?where_conditions>][\nGROUP BY <?group_conditions>[,<*group_conditions>]][\nHAVING <?having_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]",
"insert" : "INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]",
"update" : "UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]",
"delete" : "DELETE \nFROM <from_tables>[,<*from_tables>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]"
}
}
# https://www.postgresql.org/docs/current/index.html
,"postgresql" : {
"quotes" : [ ["'","'","''","''"], ["\"","\"","\"\"","\"\""], ["E","","E",""] ]
,"functions" : {
"strpos" : ["position(",2," in ",1,")"]
,"strlen" : ["length(",1,")"]
,"strlower" : ["lower(",1,")"]
,"strupper" : ["upper(",1,")"]
,"trim" : ["trim(",1,")"]
,"quote" : ["quote(",1,")"]
,"random" : "random()"
,"now" : "now()"
}
# https://www.postgresql.org/docs/current/datatype.html
,"types" : {
"SMALLINT" : "SMALLSERIAL"
,"SIGNED_SMALLINT": "SMALLINT"
,"INT" : "SERIAL"
,"SIGNED_INT" : "INTEGER"
,"BIGINT" : "BIGSERIAL"
,"SIGNED_BIGINT" : "BIGINT"
,"FLOAT" : "REAL"
,"DOUBLE" : "DOUBLE PRECISION"
,"BOOL" : "BOOLEAN"
,"TIMESTAMP" : "TIMESTAMP WITHOUT TIME ZONE"
,"DATETIME" : "TIMESTAMP WITHOUT TIME ZONE"
,"DATE" : "DATE"
,"TIME" : "TIME WITHOUT TIME ZONE"
,"VARBINARY" : "BYTEA"
,"VARCHAR" : ["VARCHAR(",[1,'255'],")"]
,"TEXT" : "TEXT"
,"BLOB" : "BYTEA"
,"JSON" : "JSON"
}
,"clauses" : {
"start_transaction" : "START TRANSACTION <type|>;",
"commit_transaction" : "COMMIT;",
"rollback_transaction" : "ROLLBACK;",
"transact" : "START TRANSACTION <type|>;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]",
"create" : "[<?view|>CREATE[ <?temporary|>TEMPORARY] VIEW <create_table> [(\n<?columns>[,\n<*columns>]\n)] AS <query>][<?!view>CREATE[ <?temporary|>TEMPORARY] TABLE[ <?ifnotexists|>IF NOT EXISTS] <create_table> [(\n<?columns>:=[<col:COL>:=[[<?column> <type>[ COLLATE <?collation>][ CONSTRAINT <?constraint>][ <?!isnull><?isnotnull|>NOT NULL][ <?!isnotnull><?isnull|>NULL][ DEFAULT <?default_value>][ CHECK (<?check>)][ <?unique|>UNIQUE][ <?primary|>PRIMARY KEY]]][,\n<*col:COL>]]\n)]]",
"alter" : "ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]",
"drop" : "DROP [<?view|>VIEW][<?!view>TABLE][ <?ifexists|>IF EXISTS] <drop_tables>[,<*drop_tables>]",
"union" : "(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]",
"select" : "SELECT <select_columns>[,<*select_columns>]\nFROM <from_tables>[,<*from_tables>][\n<?join_clauses>:=[<join:JOIN>:=[[<?type> ]JOIN <table>[ ON <?cond>]][\n<*join:JOIN>]]][\nWHERE <?where_conditions>][\nGROUP BY <?group_conditions>[,<*group_conditions>]][\nHAVING <?having_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]",
"insert" : "INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]",
"update" : "UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]",
"delete" : "DELETE \nFROM <from_tables>[,<*from_tables>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]"
}
}
# https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver16
,"transactsql" : {
"quotes" : [ ["'","'","''","''"], ["[","]","[","]"], [""," ESCAPE '\\'","",""] ]
,"functions" : {
"strpos" : ["CHARINDEX(",2,",",1,")"]
,"strlen" : ["LEN(",1,")"]
,"strlower" : ["LOWER(",1,")"]
,"strupper" : ["UPPER(",1,")"]
,"trim" : ["LTRIM(RTRIM(",1,"))"]
,"quote" : ["QUOTENAME(",1,",\"'\")"]
,"random" : "RAND()"
,"now" : "CURRENT_TIMESTAMP"
}
# https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16
,"types" : {
"SMALLINT" : "SMALLINT"
,"SIGNED_SMALLINT": "SMALLINT"
,"INT" : "INT"
,"SIGNED_INT" : "INT"
,"BIGINT" : "BIGINT"
,"SIGNED_BIGINT" : "BIGINT"
,"FLOAT" : ["FLOAT(",[1,'24'],")"]
,"DOUBLE" : ["FLOAT(",[1,'53'],")"]
,"BOOL" : "BIT"
,"TIMESTAMP" : "DATETIME"
,"DATETIME" : "DATETIME"
,"DATE" : "DATE"
,"TIME" : "TIME"
,"VARBINARY" : ["VARBINARY(",[1,'255'],")"]
,"VARCHAR" : ["VARCHAR(",[1,'255'],")"]
,"TEXT" : "TEXT"
,"BLOB" : "IMAGE"
,"JSON" : "TEXT"
}
,"clauses" : {
"start_transaction" : "BEGIN TRANSACTION <type|>;",
"commit_transaction" : "COMMIT;",
"rollback_transaction" : "ROLLBACK;",
"transact" : "BEGIN TRANSACTION <type|>;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]",
"create" : "[<?view|>CREATE[ <?temporary|>TEMPORARY] VIEW[ <?ifnotexists|>IF NOT EXISTS] <create_table> [(\n<?columns>[,\n<*columns>]\n)] AS <query>][<?!view>[<?ifnotexists|>IF NOT EXISTS (SELECT * FROM sysobjects WHERE name=<create_table> AND xtype='U')\n]CREATE TABLE <create_table> [<?!query>(\n<columns>:=[<col:COL>:=[[[CONSTRAINT <?constraint> ]<?column> <type|>[ <?isnotnull|>NOT NULL][ [CONSTRAINT <?constraint> ]DEFAULT <?default_value>][ CHECK (<?check>)][ <?!primary><?unique|>UNIQUE][ <?!unique><?primary|>PRIMARY KEY[ COLLATE <?collation>]]]][,\n<*col:COL>]]\n)][<?ifnotexists|>\nGO]]",
"alter" : "ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]",
"drop" : "DROP [<?view|>VIEW][<?!view>TABLE][ <?ifexists|>IF EXISTS] <drop_tables>[,<*drop_tables>]",
"union" : "(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]]",
"select" : "SELECT <select_columns>[,<*select_columns>]\nFROM <from_tables>[,<*from_tables>][\n<?join_clauses>:=[<join:JOIN>:=[[<?type> ]JOIN <table>[ ON <?cond>]][\n<*join:JOIN>]]][\nWHERE <?where_conditions>][\nGROUP BY <?group_conditions>[,<*group_conditions>]][\nHAVING <?having_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>][\nOFFSET <offset|0> ROWS FETCH NEXT <?count> ROWS ONLY]][<?!order_conditions>[\nORDER BY 1\nOFFSET <offset|0> ROWS FETCH NEXT <?count> ROWS ONLY]]",
"insert" : "INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]",
"update" : "UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]]",
"delete" : "DELETE \nFROM <from_tables>[,<*from_tables>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]]"
}
}
# https://www.sqlite.org/doclist.html
,"sqlite" : {
"quotes" : [ ["'","'","''","''"], ["\"","\"","\"\"","\"\""], [""," ESCAPE '\\'","",""] ]
,"functions" : {
"strpos" : ["instr(",2,",",1,")"]
,"strlen" : ["length(",1,")"]
,"strlower" : ["lower(",1,")"]
,"strupper" : ["upper(",1,")"]
,"trim" : ["trim(",1,")"]
,"quote" : ["quote(",1,")"]
,"random" : "random()"
,"now" : "datetime('now')"
}
# https://www.sqlite.org/datatype3.html
,"types" : {
"SMALLINT" : "INTEGER"
,"SIGNED_SMALLINT": "INTEGER"
,"INT" : "INTEGER"
,"SIGNED_INT" : "INTEGER"
,"BIGINT" : "INTEGER"
,"SIGNED_BIGINT" : "INTEGER"
,"FLOAT" : "REAL"
,"DOUBLE" : "REAL"
,"BOOL" : "INTEGER"
,"TIMESTAMP" : "TEXT"
,"DATETIME" : "TEXT"
,"DATE" : "TEXT"
,"TIME" : "TEXT"
,"VARBINARY" : "BLOB"
,"VARCHAR" : "TEXT"
,"TEXT" : "TEXT"
,"BLOB" : "BLOB"
,"JSON" : "TEXT"
}
,"clauses" : {
"start_transaction" : "BEGIN <type|> TRANSACTION;",
"commit_transaction" : "COMMIT;",
"rollback_transaction" : "ROLLBACK;",
"transact" : "BEGIN <type|> TRANSACTION;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]",
"create" : "[<?view|>CREATE[ <?temporary|>TEMPORARY] VIEW[ <?ifnotexists|>IF NOT EXISTS] <create_table> [(\n<?columns>[,\n<*columns>]\n)] AS <query>][<?!view>CREATE[ <?temporary|>TEMPORARY] TABLE[ <?ifnotexists|>IF NOT EXISTS] <create_table> [<?!query>(\n<columns>:=[<col:COL>:=[[[CONSTRAINT <?constraint> ]<?column> <type|>[ <?isnotnull|>NOT NULL][ DEFAULT <?default_value>][ CHECK (<?check>)][ <?!primary><?unique|>UNIQUE][ <?!unique><?primary|>PRIMARY KEY[ <?auto_increment|>AUTOINCREMENT][ COLLATE <?collation>]]]][,\n<*col:COL>]]\n)[ <?without_rowid|>WITHOUT ROWID]][AS <?query>]]",
"alter" : "ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]",
"drop" : "DROP [<?view|>VIEW][<?!view>TABLE][ <?ifexists|>IF EXISTS] <drop_tables>",
"union" : "(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]",
"select" : "SELECT <select_columns>[,<*select_columns>]\nFROM <from_tables>[,<*from_tables>][\n<?join_clauses>:=[<join:JOIN>:=[[<?type> ]JOIN <table>[ ON <?cond>]][\n<*join:JOIN>]]][\nWHERE <?where_conditions>][\nGROUP BY <?group_conditions>[,<*group_conditions>]][\nHAVING <?having_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]",
"insert" : "INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]",
"update" : "UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>]",
"delete" : "[<?!order_conditions><?!count>DELETE FROM <from_tables> [, <*from_tables>][\nWHERE <?where_conditions>]][DELETE FROM <from_tables> [, <*from_tables>] WHERE rowid IN (\nSELECT rowid FROM <from_tables> [, <*from_tables>][\nWHERE <?where_conditions>]\nORDER BY <?order_conditions> [, <*order_conditions>][\nLIMIT <?count> OFFSET <offset|0>]\n)][<?!order_conditions>DELETE FROM <from_tables> [, <*from_tables>] WHERE rowid IN (\nSELECT rowid FROM <from_tables> [, <*from_tables>][\nWHERE <?where_conditions>]\nLIMIT <?count> OFFSET <offset|0>\n)]"
}
}
}
aliases = {
"mysqli" : "mysql"
,"mariadb" : "mysql"
,"sqlserver" : "transactsql"
,"postgres" : "postgresql"
,"postgre" : "postgresql"
}
def __init__(self, type = 'mysql'):
if type and (type in Dialect.aliases): type = Dialect.aliases[type]
if (not type) or (type not in Dialect.dialects) or ('clauses' not in Dialect.dialects[ type ]):
raise ValueError('Dialect: SQL dialect does not exist for "'+type+'"')
self.clau = None
self.clus = None
self.tbls = None
self.cols = None
self.vews = {}
self.tpls = {}
self.db = None
self.escdb = None
self.escdbn = None
self.p = '';
self.type = type
self.clauses = Dialect.dialects[self.type]['clauses']
self.q = Dialect.dialects[self.type]['quotes'][0]
self.qn = Dialect.dialects[self.type]['quotes'][1]
self.e = Dialect.dialects[self.type]['quotes'][2] if 1 < len(Dialect.dialects[self.type]['quotes']) else ['','','','']
def __del__(self):
self.dispose()
def dispose(self):
self.clau = None
self.clus = None
self.tbls = None
self.cols = None
self.vews = None
self.tpls = None
self.db = None
self.escdb = None
self.escdbn = None
self.p = None
self.type = None
self.clauses = None
self.q = None
self.qn = None
self.e = None
return self
def __str__(self):
sql = self.sql()
return sql if sql else ''
def driver(self, *args):
if len(args):
db = args[0]
self.db = db if db else None
return self
return self.db
def escape(self, *args):
if len(args):
escdb = args[0]
does_quote = bool(args[1]) if len(args)>1 else False
self.escdb = [escdb, does_quote] if escdb and callable(escdb) else None
return self
return self.escdb
def escapeId(self, *args):
if len(args):
escdbn = args[0]
does_quote = bool(args[1]) if len(args)>1 else False
self.escdbn = [escdbn, does_quote] if escdbn and callable(escdbn) else None
return self
return self.escdb
def prefix(self, *args):
if len(args):
prefix = args[0]
self.p = prefix if prefix else ''
return self
return self.p
def reset(self, clause):
if not clause or (clause not in self.clauses):
raise ValueError('Dialect: SQL clause "'+str(clause)+'" does not exist for dialect "'+self.type+'"')
self.clus = {}
self.tbls = {}
self.cols = {}
self.clau = str(clause)
if not isinstance(self.clauses[self.clau], Dialect.GrammarTemplate):
self.clauses[self.clau] = Dialect.GrammarTemplate(self.clauses[self.clau])
return self
def clear(self):
self.clau = None
self.clus = None
self.tbls = None
self.cols = None
return self
def subquery(self):
sub = Dialect(self.type)
sub.driver(self.driver()).prefix(self.prefix())
escdb = self.escape()
escdbn = self.escapeId()
if escdb: sub.escape(escdb[0], escdb[1])
if escdbn: sub.escapeId(escdbn[0], escdbn[1])
sub.vews = self.vews
return sub
def sql(self):
query = ''
if self.clau and (self.clau in self.clauses):
clus = self.clus.copy()
if 'select_columns' in self.clus:
clus['select_columns'] = map_join(self.clus['select_columns'], 'aliased')
if 'from_tables' in self.clus:
clus['from_tables'] = map_join(self.clus['from_tables'], 'aliased')
if 'insert_tables' in self.clus:
clus['insert_tables'] = map_join(self.clus['insert_tables'], 'aliased')
if 'insert_columns' in self.clus:
clus['insert_columns'] = map_join(self.clus['insert_columns'], 'full')
if 'update_tables' in self.clus:
clus['update_tables'] = map_join(self.clus['update_tables'], 'aliased')
if 'create_table' in self.clus:
clus['create_table'] = map_join(self.clus['create_table'], 'full')
if 'alter_table' in self.clus:
clus['alter_table'] = map_join(self.clus['alter_table'], 'full')
if 'drop_tables' in self.clus:
clus['drop_tables'] = map_join(self.clus['drop_tables'], 'full')
if 'where_conditions_required' in self.clus:
clus['where_conditions'] = ('('+str(self.clus['where_conditions_required'])+') AND ('+str(self.clus['where_conditions'])+')') if 'where_conditions' in self.clus else str(self.clus['where_conditions_required'])
#del self.clus['where_conditions_required']
if 'having_conditions_required' in self.clus:
clus['having_conditions'] = ('('+str(self.clus['having_conditions_required'])+') AND ('+str(self.clus['having_conditions'])+')') if 'having_conditions' in self.clus else str(self.clus['having_conditions_required'])
#del self.clus['having_conditions_required']
query = self.clauses[self.clau].render(clus)
#self.clear()
return query
def createView(self, view):
if view and self.clau:
self.vews[view] = {
'clau' : self.clau,
'clus' : self.clus,
'tbls' : self.tbls,
'cols' : self.cols
}
# make existing where / having conditions required
if 'where_conditions' in self.vews[view]['clus']:
if len(self.vews[view]['clus']['where_conditions']):
self.vews[view]['clus']['where_conditions_required'] = self.vews[view]['clus']['where_conditions']
del self.vews[view]['clus']['where_conditions']
if 'having_conditions' in self.vews[view]['clus']:
if len(self.vews[view]['clus']['having_conditions']):
self.vews[view]['clus']['having_conditions_required'] = self.vews[view]['clus']['having_conditions']
del self.vews[view]['clus']['having_conditions']
self.clear()
return self
def useView(self, view):
# using custom 'soft' view
selected_columns = self.clus['select_columns']
view = self.vews[view]
self.clus = defaults(self.clus, view['clus'], True, True)
self.tbls = defaults({}, view['tbls'], True)
self.cols = defaults({}, view['cols'], True)
# handle name resolution and recursive re-aliasing in views
if selected_columns:
selected_columns = self.refs(selected_columns, self.cols, True)
select_columns = []
for selected_column in selected_columns:
if '*' == selected_column.full:
select_columns = select_columns + self.clus['select_columns']
else:
select_columns.append(selected_column)
self.clus['select_columns'] = select_columns
return self
def dropView(self, view):
if view and (view in self.vews):
del self.vews[view]
return self
def prepareTpl(self, tpl, *args):
#, query, left, right
if tpl:
argslen = len(args)
if 0 == argslen:
query = None
left = None
right = None
use_internal_query = True
elif 1 == argslen:
query = args[0]
left = None
right = None
use_internal_query = False
elif 2 == argslen:
query = None
left = args[0]
right = args[1]
use_internal_query = True
else: # if 2 < argslen:
query = args[0]
left = args[1]
right = args[2]
use_internal_query = False
# custom delimiters
left = re.escape(left) if left else '%'
right = re.escape(right) if right else '%'
# custom prepared parameter format
pattern = re.compile(left + '(([rlfids]:)?[0-9a-zA-Z_]+)' + right)
if use_internal_query:
sql = Dialect.StringTemplate(self.sql(), pattern)
#self.clear()
else:
sql = Dialect.StringTemplate(query, pattern)
self.tpls[tpl] = {
'sql' : sql,
'types' : None
}
return self
def prepared(self, tpl, args):
if tpl and (tpl in self.tpls):
sql = self.tpls[tpl]['sql']
types = self.tpls[tpl]['types']
if types is None:
# lazy init
sql.parse()
types = {}
# extract parameter types
for i in range(len(sql.tpl)):
tpli = sql.tpl[i]
if not tpli[0]:
k = tpli[1].split(':')
if len(k) > 1:
types[k[1]] = k[0]
sql.tpl[i][1] = k[1]
else:
types[k[0]] = "s"
sql.tpl[i][1] = k[0]
self.tpls[tpl]['types'] = types
params = {}
for k in args:
v = args[k]
type = types[k] if k in types else "s"
if 'r'==type:
# raw param
if is_array(v):
params[k] = ','.join(v)
else:
params[k] = v
elif 'l'==type:
# like param
params[k] = self.like(v)
elif 'f'==type:
if is_array(v):
# array of references, e.g fields
tmp = array(v)
params[k] = Ref.parse(tmp[0], self).aliased
for i in range(1,len(tmp)): params[k] += ','+Ref.parse(tmp[i], self).aliased
else:
# reference, e.g field
params[k] = Ref.parse(v, self).aliased
elif 'i'==type:
if is_array(v):
# array of integers param
params[k] = ','.join(self.intval2str(array(v)))
else:
# integer param
params[k] = self.intval2str(v)
elif 'd'==type:
if is_array(v):
# array of floats param
params[k] = ','.join(self.floatval2str(array(v)))
else:
# float param
params[k] = self.floatval2str(v)
#elif 's'==type:
else:
if is_array(v):
# array of strings param
params[k] = ','.join(self.quote(array(v)))
else:
# string param
params[k] = self.quote(v)
return sql.render(params)
return ''
def prepare(self, query, args, left = None, right = None):
if query and args:
# custom delimiters
left = re.escape(left) if left else '%'
right = re.escape(right) if right else '%'
# custom prepared parameter format
pattern = re.compile(left + '([rlfids]:)?([0-9a-zA-Z_]+)' + right)
prepared = ''
m = pattern.search(query)
while m:
pos = m.start(0)
le = len(m.group(0))
param = m.group(2)
if param in args:
type = m.group(1)[0:-1] if m.group(1) else "s"
if 'r'==type:
# raw param
if is_array(args[param]):
param = ','.join(args[param])
else:
param = args[param]
elif 'l'==type:
# like param
param = self.like(args[param])
elif 'f'==type:
if is_array(args[param]):
# array of references, e.g fields
tmp = array(args[param])
param = Ref.parse(tmp[0], self).aliased
for i in range(1,len(tmp)): param += ','+Ref.parse(tmp[i], self).aliased
else:
# reference, e.g field
param = Ref.parse(args[param], self).aliased
elif 'i'==type:
if is_array(args[param]):
# array of integers param
param = ','.join(self.intval2str(array(args[param])))
else:
# integer param
param = self.intval2str(args[param])
elif 'd'==type:
if is_array(args[param]):
# array of floats param
param = ','.join(self.floatval2str(array(args[param])))
else:
# float param
param = self.floatval2str(args[param])
#elif 's'==type:
else:
if is_array(args[param]):
# array of strings param
param = ','.join(self.quote(array(args[param])))
else:
# string param
param = self.quote(args[param])
prepared += query[0:pos] + param
else:
prepared += query[0:pos] + self.quote('')
query = query[pos+le:]
m = pattern.search(query)
if len(query): prepared += query
return prepared
return query
def dropTpl(self, tpl):
if tpl and (tpl in self.tpls):
self.tpls[tpl]['sql'].dispose()
del self.tpls[tpl]
return self
def StartTransaction(self, type = None, start_transaction_clause = 'start_transaction'):
if self.clau != start_transaction_clause: self.reset(start_transaction_clause)
self.clus['type'] = type if not empty(type) else None
return self
def CommitTransaction(self, commit_transaction_clause = 'commit_transaction'):
if self.clau != commit_transaction_clause: self.reset(commit_transaction_clause)
return self
def RollbackTransaction(self, rollback_transaction_clause = 'rollback_transaction'):
if self.clau != rollback_transaction_clause: self.reset(rollback_transaction_clause)
return self
def Transaction(self, options, transact_clause = 'transact'):
if self.clau != transact_clause: self.reset(transact_clause)
options = {} if empty(options) else options
self.clus['type'] = options['type'] if options and ('type' in options) and not empty(options['type']) else None
self.clus['rollback'] = 1 if options and ('rollback' in options) and options['rollback'] else None
if ('statements' in options) and not empty(options['statements']):
statements = array(options['statements'])
self.clus['statements'] = statements if ('statements' not in self.clus) or not len(self.clus['statements']) else self.clus['statements'] + statements
return self
def Create(self, table, options = None, create_clause = 'create'):
if self.clau != create_clause: self.reset(create_clause)
options = {'ifnotexists' : 1} if empty(options) else options
table = self.refs(table, self.tbls)
self.clus['create_table'] = table
self.clus['view'] = 1 if options and ('view' in options) and options['view'] else None
self.clus['ifnotexists'] = 1 if options and ('ifnotexists' in options) and options['ifnotexists'] else None
self.clus['temporary'] = 1 if options and ('temporary' in options) and options['temporary'] else None
self.clus['query'] = str(options['query']) if options and ('query' in options) and len(str(options['query'])) else None
if ('columns' in options) and not empty(options['columns']):
cols = array(options['columns'])
self.clus['columns'] = cols if 'columns' not in self.clus else self.clus['columns'] + cols
if ('table' in options) and not empty(options['table']):
opts = array(options['table'])
self.clus['options'] = opts if 'options' not in self.clus else self.clus['options'] + opts
return self
def Alter(self, table, options = None, alter_clause = 'alter'):
if self.clau != alter_clause: self.reset(alter_clause)
table = self.refs(table, self.tbls)
self.clus['alter_table'] = table
options = {} if empty(options) else options
self.clus['view'] = 1 if options and ('view' in options) and options['view'] else None
if ('columns' in options) and not empty(options['columns']):
cols = array(options['columns'])
self.clus['columns'] = cols if 'columns' not in self.clus else self.clus['columns'] + cols
if ('table' in options) and not empty(options['table']):
opts = array(options['table'])
self.clus['options'] = opts if 'options' not in self.clus else self.clus['options'] + opts
return self
def Drop(self, tables = '*', options = None, drop_clause = 'drop'):
if self.clau != drop_clause: self.reset(drop_clause)
view = tables[0] if is_array(tables) else tables
if (view in self.vews):
# drop custom 'soft' view
self.dropView(view)
return self
if is_string(tables): tables = tables.split(',')
tables = self.refs('*' if not tables else tables, self.tbls)
options = {'ifexists' : 1} if empty(options) else options
self.clus['view'] = 1 if options and ('view' in options) and options['view'] else None
self.clus['ifexists'] = 1 if options and ('ifexists' in options) and options['ifexists'] else None
self.clus['temporary'] = 1 if options and ('temporary' in options) and options['temporary'] else None
self.clus['drop_tables'] = tables if ('drop_tables' not in self.clus) or not len(self.clus['drop_tables']) else self.clus['drop_tables'] + tables
return self
def Select(self, columns = '*', select_clause = 'select'):
if self.clau != select_clause: self.reset(select_clause)
if is_string(columns): columns = columns.split(',')
columns = self.refs('*' if not columns else columns, self.cols)
self.clus['select_columns'] = columns if ('select_columns' not in self.clus) or not len(self.clus['select_columns']) else self.clus['select_columns'] + columns
return self
def Union(self, selects, all = False, union_clause = 'union'):
if self.clau != union_clause: self.reset(union_clause)
self.clus['union_selects'] = array(selects) if ('union_selects' not in self.clus) or not len(self.clus['union_selects']) else self.clus['union_selects'] + array(selects)
self.clus['union_all'] = '' if bool(all) else None
return self
def Insert(self, tables, columns, insert_clause = 'insert'):
if self.clau != insert_clause: self.reset(insert_clause);
view = tables[0] if is_array(tables) else tables
if (view in self.vews) and self.clau == self.vews[view]['clau']:
# using custom 'soft' view
self.useView(view)
else:
if is_string(tables): tables = tables.split(',')
if is_string(columns): columns = columns.split(',')
tables = self.refs(tables, self.tbls)
columns = self.refs(columns, self.cols)
self.clus['insert_tables'] = tables if ('insert_tables' not in self.clus) or not len(self.clus['insert_tables']) else self.clus['insert_tables'] + tables
self.clus['insert_columns'] = columns if ('insert_columns' not in self.clus) or not len(self.clus['insert_columns']) else self.clus['insert_columns'] + columns
return self
def Values(self, values):
if empty(values): return self
# array of arrays
if not is_array(values) or not is_array(values[0]): values = [values]
insert_values = []
for vs in values:
vs = array(vs)
if len(vs):
vals = []
for val in vs:
if is_obj(val):
if 'raw' in val:
vals.append(val['raw'])
elif 'integer' in val:
vals.append(self.intval2str(val['integer']))
elif 'int' in val:
vals.append(self.intval2str(val['int']))
elif 'float' in val:
vals.append(self.floatval2str(val['float']))
elif 'string' in val:
vals.append(self.quote(val['string']))
else:
vals.append('NULL' if val is None else (str(val) if isinstance(val, (int,float)) else self.quote(val)))
insert_values.append('(' + ','.join(vals) + ')')
insert_values = ','.join(insert_values)
if 'values_values' in self.clus and len(self.clus['values_values']) > 0:
insert_values = self.clus['values_values'] + ',' + insert_values
self.clus['values_values'] = insert_values
return self
def Update(self, tables, update_clause = 'update'):
if self.clau != update_clause: self.reset(update_clause)
view = tables[0] if is_array(tables) else tables
if (view in self.vews) and self.clau == self.vews[view]['clau']:
# using custom 'soft' view
self.useView(view)
else:
if is_string(tables): tables = tables.split(',')
tables = self.refs(tables, self.tbls)
self.clus['update_tables'] = tables if ('update_tables' not in self.clus) or not len(self.clus['update_tables']) else self.clus['update_tables'] + tables
return self
def Set(self, fields_values):
if empty(fields_values): return self
set_values = []
COLS = self.cols
for f in fields_values:
field = self.refs(f, COLS)[0].full
value = fields_values[f]
if is_obj(value):
if 'raw' in value:
set_values.append(field + " = " + str(value['raw']))
elif 'integer' in value:
set_values.append(field + " = " + self.intval2str(value['integer']))
elif 'int' in value:
set_values.append(field + " = " + self.intval2str(value['int']))
elif 'float' in value:
set_values.append(field + " = " + self.floatval2str(value['float']))
elif 'string' in value:
set_values.append(field + " = " + self.quote(value['string']))
elif 'increment' in value:
set_values.append(field + " = " + field + " + " + str(self.numval(value['increment'])))
elif 'decrement' in value:
set_values.append(field + " = " + field + " - " + str(self.numval(value['decrement'])))
elif 'case' in value:
set_case_value = field + " = CASE"
if 'when' in value['case']:
for case_value in value['case']['when']:
set_case_value += "\nWHEN " + self.conditions(value['case']['when'][case_value],False) + " THEN " + self.quote(case_value)
if 'else' in value['case']:
set_case_value += "\nELSE " + self.quote(value['case']['else'])
else:
for case_value in value['case']:
set_case_value += "\nWHEN " + self.conditions(value['case'][case_value],False) + " THEN " + self.quote(case_value)
set_case_value += "\nEND"
set_values.append(set_case_value)
else:
set_values.append(field + " = " + ('NULL' if value is None else (str(value) if isinstance(value, (int,float)) else self.quote(value))))
set_values = ','.join(set_values)
if 'set_values' in self.clus and len(self.clus['set_values']) > 0:
set_values = self.clus['set_values'] + ',' + set_values
self.clus['set_values'] = set_values
return self
def Delete(self, delete_clause = 'delete'):
if self.clau != delete_clause: self.reset(delete_clause)
return self
def From(self, tables):
if empty(tables): return self
view = tables[0] if is_array(tables) else tables
if (view in self.vews) and (self.clau == self.vews[view]['clau']):
# using custom 'soft' view
self.useView(view)
else:
if is_string(tables): tables = tables.split(',')
tables = self.refs(tables, self.tbls)
self.clus['from_tables'] = tables if ('from_tables' not in self.clus) or not len(self.clus['from_tables']) else self.clus['from_tables'] + tables
return self
def Join(self, table, on_cond = None, join_type = ''):
table = self.refs(table, self.tbls )[0].aliased
join_type = None if empty(join_type) else str(join_type).upper()
if empty(on_cond):
join_clause = {
'table' : table,
'type' : join_type
}
else:
if is_string(on_cond):
on_cond = self.refs(on_cond.split('='), self.cols)
on_cond = '(' + on_cond[0].full + '=' + on_cond[1].full + ')'
else:
for field in on_cond:
cond = on_cond[field]
if not is_obj(cond): on_cond[field] = {'eq':cond, 'type':'identifier'}
on_cond = '(' + self.conditions(on_cond, False) + ')'
join_clause = {
'table' : table,
'type' : join_type,
'cond' : on_cond
}
if 'join_clauses' not in self.clus: self.clus['join_clauses'] = [join_clause]
else: self.clus['join_clauses'].append(join_clause)
return self
def Where(self, conditions, boolean_connective = "and"):
if empty(conditions): return self
boolean_connective = str(boolean_connective).upper() if boolean_connective else "AND"
if "OR" != boolean_connective: boolean_connective = "AND"
conditions = self.conditions(conditions, False)
if 'where_conditions' in self.clus and len(self.clus['where_conditions']) > 0:
conditions = self.clus['where_conditions'] + " "+boolean_connective+" " + conditions
self.clus['where_conditions'] = conditions
return self
def Group(self, col):
group_condition = self.refs(col, self.cols)[0].alias
if 'group_conditions' in self.clus and len(self.clus['group_conditions']) > 0:
group_condition = self.clus['group_conditions'] + ',' + group_condition
self.clus['group_conditions'] = group_condition
return self
def Having(self, conditions, boolean_connective = "and"):
if empty(conditions): return self
boolean_connective = str(boolean_connective).upper() if boolean_connective else "AND"
if "OR" != boolean_connective: boolean_connective = "AND"
conditions = self.conditions(conditions, True)
if 'having_conditions' in self.clus and len(self.clus['having_conditions']) > 0:
conditions = self.clus['having_conditions'] + " "+boolean_connective+" " + conditions
self.clus['having_conditions'] = conditions
return self
def Order(self, col, dir = "asc"):
dir = str(dir).upper() if dir else "ASC"
if "DESC" != dir: dir = "ASC"
order_condition = self.refs(col, self.cols)[0].alias + " " + dir
if 'order_conditions' in self.clus and len(self.clus['order_conditions']) > 0:
order_condition = self.clus['order_conditions'] + ',' + order_condition
self.clus['order_conditions'] = order_condition
return self
def Limit(self, count, offset = 0):
self.clus['count'] = int(count, 10) if is_string(count) else count
self.clus['offset'] = int(offset, 10) if is_string(offset) else offset
return self
def Page(self, page, perpage):
page = int(page, 10) if is_string(page) else page
perpage = int(perpage, 10) if is_string(perpage) else perpage
return self.Limit(perpage, page*perpage)
def conditions(self, conditions, can_use_alias = False):
if empty(conditions): return ''
if is_string(conditions): return conditions
condquery = ''
conds = []
COLS = self.cols
fmt = 'alias' if can_use_alias is True else 'full'
for f in conditions:
value = conditions[f]
if is_obj(value):
if 'raw' in value:
conds.append(str(value['raw']))
continue
if 'or' in value:
cases = []
for or_cl in value['or']:
cases.append(self.conditions(or_cl, can_use_alias))
conds.append(' OR '.join(cases))
continue
if 'and' in value:
cases = []
for and_cl in value['and']:
cases.append(self.conditions(and_cl, can_use_alias))
conds.append(' AND '.join(cases))
continue
if 'either' in value:
cases = []
for either in value['either']:
case_i = {}
case_i[f] = either
cases.append(self.conditions(case_i, can_use_alias))
conds.append(' OR '.join(cases))
continue
if 'together' in value:
cases = []
for together in value['together']:
case_i = {}
case_i[f] = together
cases.append(self.conditions(case_i, can_use_alias))
conds.append(' AND '.join(cases))
continue
field = getattr(self.refs(f, COLS)[0], fmt)
type = value['type'] if 'type' in value else 'string'
if 'case' in value:
cases = field + " = CASE"
if 'when' in value['case']:
for case_value in value['case']['when']:
cases += " WHEN " + self.conditions(value['case']['when'][case_value], can_use_alias) + " THEN " + self.quote(case_value)
if 'else' in value['case']:
cases += " ELSE " + self.quote(value['case']['else'])
else:
for case_value in value['case']:
cases += " WHEN " + self.conditions(value['case'][case_value], can_use_alias) + " THEN " + self.quote(case_value)
cases += " END"
conds.append(cases)
elif 'multi_like' in value:
conds.append(self.multi_like(field, value['multi_like']))
elif 'like' in value:
conds.append(field + " LIKE " + (str(value['like']) if 'raw' == type else self.like(value['like'])))
elif 'not_like' in value:
conds.append(field + " NOT LIKE " + (str(value['not_like']) if 'raw' == type else self.like(value['not_like'])))
elif 'contains' in value:
v = str(value['contains'])
if 'raw' == type:
# raw, do nothing
pass
else:
v = self.quote(v)
conds.append(self.sql_function('strpos', [field, v]) + ' > 0')
elif 'not_contains' in value:
v = str(value['not_contains'])
if 'raw' == type:
# raw, do nothing
pass
else:
v = self.quote(v)
conds.append(self.sql_function('strpos', [field, v]) + ' = 0')
elif 'in' in value:
v = array(value['in'])
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v[0]):
v = self.intval2str(v);
elif 'float' == type or is_float(v[0]):
v = self.floatval2str(v);
else:
v = self.quote(v)
conds.append(field + " IN (" + ','.join(v) + ")")
elif 'not_in' in value:
v = array(value['not_in'])
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v[0]):
v = self.intval2str(v);
elif 'float' == type or is_float(v[0]):
v = self.floatval2str(v);
else:
v = self.quote(v)
conds.append(field + " NOT IN (" + ','.join(v) + ")")
elif 'between' in value:
v = array(value['between'])
# partial between clause
if v[0] is None:
# switch to lte clause
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v[1]):
v[1] = self.intval(v[1])
elif 'float' == type or is_float(v[1]):
v[1] = self.floatval(v[1])
else:
v[1] = self.quote(v[1])
conds.append(field + " <= " + str(v[1]))
elif v[1] is None:
# switch to gte clause
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v[0]):
v[0] = self.intval(v[0])
elif 'float' == type or is_float(v[0]):
v[0] = self.floatval(v[0])
else:
v[0] = self.quote(v[0])
conds.append(field + " >= " + str(v[0]))
else:
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or (is_int(v[0]) and is_int(v[1])):
v = self.intval(v)
elif 'float' == type or (is_float(v[0]) and is_float(v[1])):
v = self.floatval(v)
else:
v = self.quote(v)
conds.append(field + " BETWEEN " + str(v[0]) + " AND " + str(v[1]))
elif 'not_between' in value:
v = array(value['not_between'])
# partial between clause
if v[0] is None:
# switch to gt clause
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v[1]):
v[1] = self.intval(v[1])
elif 'float' == type or is_float(v[1]):
v[1] = self.floatval(v[1])
else:
v[1] = self.quote(v[1])
conds.append(field + " > " + str(v[1]))
elif v[1] is None:
# switch to lt clause
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v[0]):
v[0] = self.intval(v[0])
elif 'float' == type or is_float(v[0]):
v[0] = self.floatval(v[0])
else:
v[0] = self.quote(v[0])
conds.append(field + " < " + str(v[0]))
else:
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or (is_int(v[0]) and is_int(v[1])):
v = self.intval(v)
elif 'float' == type or (is_float(v[0]) and is_float(v[1])):
v = self.floatval(v)
else:
v = self.quote(v)
conds.append(field + " < " + str(v[0]) + " OR " + field + " > " + str(v[1]))
elif ('gt' in value) or ('gte' in value):
op = 'gt' if 'gt' in value else "gte"
v = value[op]
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v):
v = self.intval(v)
elif 'float' == type or is_float(v):
v = self.floatval(v)
elif 'identifier' == type or 'field' == type:
v = getattr(self.refs(v, COLS)[0], fmt)
else:
v = self.quote(v)
conds.append(field + (" > " if 'gt'==op else " >= ") + str(v))
elif ('lt' in value) or ('lte' in value):
op = 'lt' if 'lt' in value else "lte"
v = value[op]
if 'raw' == type:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v):
v = self.intval(v)
elif 'float' == type or is_float(v):
v = self.floatval(v)
elif 'identifier' == type or 'field' == type:
v = getattr(self.refs(v, COLS)[0], fmt)
else:
v = self.quote(v)
conds.append(field + (" < " if 'lt'==op else " <= ") + str(v))
elif ('not_equal' in value) or ('not_eq' in value):
op = 'not_equal' if 'not_equal' in value else "not_eq"
v = value[op]
if 'raw' == type or v is None:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v):
v = self.intval(v)
elif 'float' == type or is_float(v):
v = self.floatval(v)
elif 'identifier' == type or 'field' == type:
v = getattr(self.refs(v, COLS)[0], fmt)
else:
v = self.quote(v)
conds.append((field + " IS NOT NULL") if v is None else (field + " <> " + str(v)))
elif ('equal' in value) or ('eq' in value):
op = 'equal' if 'equal' in value else "eq"
v = value[op]
if 'raw' == type or v is None:
# raw, do nothing
pass
elif ('int' == type or 'integer' == type) or is_int(v):
v = self.intval(v)
elif 'float' == type or is_float(v):
v = self.floatval(v)
elif 'identifier' == type or 'field' == type:
v = getattr(self.refs(v, COLS)[0], fmt)
else:
v = self.quote(v)
conds.append((field + " IS NULL") if v is None else (field + " = " + str(v)))
else:
field = getattr(self.refs(f, COLS)[0], fmt)
conds.append((field + " IS NULL") if value is None else (field + " = " + (str(value) if isinstance(value, (int,float)) else self.quote(value))))
if len(conds): condquery = '(' + ') AND ('.join(conds) + ')'
return condquery
def joinConditions(self, join, conditions):
j = 0
conditions_copied = copy.copy(conditions)
for f in conditions_copied:
ref = Ref.parse(f, self)
field = ref._col
if field not in join: continue
cond = conditions[f]
main_table = join[field]['table']
main_id = join[field]['id']
join_table = join[field]['join']
join_id = join[field]['join_id']
j += 1
join_alias = join_table+str(j)
where = {}
if ('key' in join[field]) and field != join[field]['key']:
join_key = join[field]['key']
where[join_alias+'.'+join_key] = field
else:
join_key = field
if 'value' in join[field]:
join_value = join[field]['value']
where[join_alias+'.'+join_value] = cond
else:
join_value = join_key
where[join_alias+'.'+join_value] = cond
self.Join(
join_table+" AS "+join_alias,
main_table+'.'+main_id+'='+join_alias+'.'+join_id,
"inner"
).Where(where)
del conditions[f]
return self
def refs(self, refs, lookup, re_alias = False):
if re_alias is True:
for i in range(len(refs)):
ref = refs[i]
alias = ref.alias
qualified = ref.qualified
qualified_full = ref.full
if '*' == qualified_full: continue
if alias not in lookup:
if qualified_full in lookup:
ref2 = lookup[qualified_full]
alias2 = ref2.alias
qualified_full2 = ref2.full
if (qualified_full2 != qualified_full) and (alias2 != alias) and (alias2 == qualified_full):
# handle recursive aliasing
#if (qualified_full2 != alias2) and (alias2 in lookup):
# del lookup[alias2]
ref2 = ref2.cloned(ref.alias)
refs[i] = lookup[alias] = ref2
elif qualified in lookup:
ref2 = lookup[qualified]
if ref2.qualified != qualified: ref2 = lookup[ref2.qualified]
if ref.full != ref.alias:
ref2 = ref2.cloned(ref.alias, None, ref._func)
else:
ref2 = ref2.cloned(None, ref2.alias, ref._func)
refs[i] = lookup[ref2.alias] = ref2
if (ref2.alias != ref2.full) and (ref2.full not in lookup):
lookup[ref2.full] = ref2
else:
lookup[alias] = ref
if (alias != qualified_full) and (qualified_full not in lookup):
lookup[qualified_full] = ref
else:
refs[i] = lookup[alias]
else:
rs = array(refs)
refs = []
for i in range(len(rs)):
#r = rs[i].split(',')
#for j in range(len(r)):
ref = Ref.parse(rs[i], self)
alias = ref.alias
qualified = ref.full
if alias not in lookup:
lookup[alias] = ref
if (qualified != alias) and (qualified not in lookup):
lookup[qualified] = ref
else:
ref = lookup[alias]
refs.append(ref)
return refs
def tbl(self, table):
if is_array(table): return [self.tbl(x) for x in table]
return self.p + str(table)
def intval(self, v):
if is_array(v): return [self.intval(x) for x in v]
else: return v if is_int(v) else int(str(v), 10)
def intval2str( self, v ):
if is_array(v): return [self.intval2str(x) for x in v]
else: return str(self.intval(v))
def floatval(self, v):
if is_array(v): return [self.floatval(x) for x in v]
else: return v if is_float(v) else float(str(v))
def floatval2str(self, v):
if is_array(v): return [self.floatval2str(x) for x in v]
else: return str(self.floatval(v))
def numval(self, v):
if is_array(v): return [self.numval(x) for x in v]
else: return v if isinstance(v, (int,float)) else float(str(v))
def quote_name(self, v, optional = False):
optional = optional is True
qn = self.qn
if is_array(v):
return [self.quote_name(x, optional) for x in v]
v = str(v)
if optional and qn[0] == v[0:len(qn[0])] and qn[1] == v[-len(qn[1]):]:
return v
if self.escdbn:
return self.escdbn[0](v) if self.escdbn[1] else (qn[0] + self.escdbn[0](v) + qn[1])
else:
ve = ''
for c in v:
# properly try to escape quotes, by doubling for example, inside name
if qn[0] == c:
ve += qn[2]
elif qn[1] == c:
ve += qn[3]
else:
ve += c
return qn[0] + ve + qn[1]
def quote(self, v):
if is_array(v): return [self.quote(x) for x in v]
q = self.q
e = self.e
v = str(v)
hasBackSlash = (-1 != v.find('\\')) #('\\' in v)
if self.escdb:
return self.escdb[0](v) if self.escdb[1] else ((e[2] if hasBackSlash else '') + q[0] + self.escdb[0](v) + q[1] + (e[3] if hasBackSlash else ''))
return (e[2] if hasBackSlash else '') + q[0] + self.esc(v) + q[1] + (e[3] if hasBackSlash else '')
def esc(self, v):
global NULL_CHAR
if is_array(v): return [self.esc(x) for x in v]
escdb = self.escdb
v = str(v)
if escdb and not escdb[1]: return escdb[0](v)
else:
# simple ecsaping using addslashes
# '"\ and NUL (the NULL byte).
chars = '\\' + NULL_CHAR
esc = '\\'
q = self.q
ve = ''
for c in v:
if q[0] == c: ve += q[2]
elif q[1] == c: ve += q[3]
else: ve += addslashes(c, chars, esc)
return ve
def esc_like(self, v):
if is_array(v): return [self.esc_like(x) for x in v]
return addslashes(str(v), '_%', '\\')
def like(self, v):
if is_array(v): return [self.like(x) for x in v]
q = self.q
e = ['','','',''] if self.escdb else self.e
return e[0] + q[0] + '%' + self.esc_like(self.esc(v)) + '%' + q[1] + e[1]
def multi_like(self, f, v, trimmed = True):
trimmed = trimmed is not False
like = f + " LIKE "
ORs = v.split(',')
if trimmed: ORs = filter(len, list(map(lambda x: x.strip(), ORs)))
for i in range(len(ORs)):
ANDs = ORs[i].split('+')
if trimmed: ANDs = filter(len, list(map(lambda x: x.strip(), ANDs)))
for j in range(len(ANDs)): ANDs[j] = like + self.like(ANDs[j])
ORs[i] = '(' + ' AND '.join(ANDs) + ')'
return ' OR '.join(ORs)
def sql_function(self, f, args = None):
if ('functions' not in Dialect.dialects[self.type]) or (f not in Dialect.dialects[self.type]['functions']):
raise ValueError('Dialect: SQL function "'+f+'" does not exist for dialect "'+self.type+'"')
f = Dialect.dialects[self.type]['functions'][f]
if isinstance(f, (list,tuple)):
func = ''
args = [] if args is None else array(args)
argslen = len(args)
is_arg = False
for fi in f:
func += ((str(args[fi[0]-1]) if 0<fi[0] and argslen>=fi[0] else (str(fi[1]) if (1<len(fi)) and not (fi[1] is None) else '')) if isinstance(fi, (list,tuple)) else (str(args[fi-1]) if 0<fi and argslen>=fi else '')) if is_arg else str(fi)
is_arg = not is_arg
return func
return str(f)
def sql_type(self, data_type, args = None):
data_type = str(data_type).upper()
if ('types' not in Dialect.dialects[self.type]) or (data_type not in Dialect.dialects[self.type]['types']):
raise ValueError('Dialect: SQL type "'+data_type+'" does not exist for dialect "'+self.type+'"')
d = Dialect.dialects[self.type]['types'][data_type]
if isinstance(d, (list,tuple)):
dd = ''
args = [] if args is None else array(args)
argslen = len(args)
is_arg = False
for di in d:
dd += ((str(args[di[0]-1]) if 0<di[0] and argslen>=di[0] else (str(di[1]) if (1<len(di)) and not (di[1] is None) else '')) if isinstance(di, (list,tuple)) else (str(args[di-1]) if 0<di and argslen>=di else '')) if is_arg else str(di)
is_arg = not is_arg
return dd
return str(d)
__all__ = ['Dialect']
|
Download
