File: test/python/Dialect.py

Recommend this page to a friend!
  Classes of Nikos M.  >  Dialect PHP ORM Library  >  test/python/Dialect.py  >  Download  
File: test/python/Dialect.py
Role: Auxiliary data
Content type: text/plain
Description: Auxiliary data
Class: Dialect PHP ORM Library
Store and retrieve objects in database using ORM
Author: By
Last change:
Date: 8 months ago
Size: 108,021 bytes
 

 

Contents

Class file image Download
##
#   Dialect,
#   a simple and flexible Cross-Platform & Cross-Vendor SQL Query Builder for PHP, Python, JavaScript
#
#   @version: 1.3.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 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_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 [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) else defau[ k ]
    return data

def map_join( arr, prop, sep=',' ):
    joined = ''
    if arr and len(arr):
        joined = getattr(arr[0], prop)
        for i in range(1,len(arr)): joined += sep + 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 isinstance(ids[0],str):
                    col = ids.pop(0)
                    col_q = d.quote_name( col )
                else:
                    ids.pop(0)
                    col = col_q = '*'

            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=[] ):
        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.3.0'

    #TPL_RE = re.compile(r'\$\(([^\)]+)\)')
    StringTemplate = StringTemplate
    GrammarTemplate = GrammarTemplate
    Ref = Ref

    dialects = {
     "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()"]
        }

		,"types"    	: {
		 "BINARY"		: "VARBINARY"
		,"SMALLINT"		: "TINYINT"
		,"MEDIUMINT"	: "MEDIUMINT"
		,"INT"			: "UNSIGNED INT"
		,"SIGNED_INT"	: "INT"
		,"BIGINT"		: "UNSIGNED BIGINT"
		,"SIGNED_BIGINT": "BIGINT"
		,"FLOAT"		: "FLOAT"
		,"DOUBLE"   	: "DOUBLE"
		,"BOOL"			: "TINYINT"
		,"TIMESTAMP"	: "TIMESTAMP"
		,"DATETIME"		: "DATETIME"
		,"DATE"			: "DATE"
		,"TIME"			: "TIME"
		,"VARCHAR"	    : "VARCHAR"
		,"TEXT"			: "TEXT"
		,"BLOB"			: "BLOB"
		}

        ,"clauses"      : "[<?start_transaction_clause|>START TRANSACTION <type|>;][<?commit_transaction_clause|>COMMIT;][<?rollback_transaction_clause|>ROLLBACK;][<?transact_clause|>START TRANSACTION  <type|>;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]][<?create_clause|>[<?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_clause|>ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]][<?drop_clause|>DROP [<?view|>VIEW][<?!view>[<?temporary|>TEMPORARY ]TABLE][ <?ifexists|>IF EXISTS] <drop_tables>[,<*drop_tables>]][<?union_clause|>(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]][<?select_clause|>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_clause|>INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]][<?update_clause|>UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]][<?delete_clause|>DELETE \nFROM <from_tables>[,<*from_tables>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <offset|0>,<?count>]]"
    }


    ,"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()"]
        }

		,"types"    	: {
		 "BINARY"		: "BYTEA"
		,"SMALLINT"		: "SMALLINT"
		,"MEDIUMINT"	: "INTEGER"
		,"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"
		,"VARCHAR"	    : "VARCHAR"
		,"TEXT"			: "TEXT"
		,"BLOB"			: "BLOB"
		}

        ,"clauses"      : "[<?start_transaction_clause|>START TRANSACTION <type|>;][<?commit_transaction_clause|>COMMIT;][<?rollback_transaction_clause|>ROLLBACK;][<?transact_clause|>START TRANSACTION  <type|>;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]][<?create_clause|>[<?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_clause|>ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]][<?drop_clause|>DROP [<?view|>VIEW][<?!view>TABLE][ <?ifexists|>IF EXISTS] <drop_tables>[,<*drop_tables>]][<?union_clause|>(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]][<?select_clause|>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_clause|>INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]][<?update_clause|>UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]][<?delete_clause|>DELETE \nFROM <from_tables>[,<*from_tables>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]]"
    }


    ,"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"]
        }

		,"types"    	: {
		 "BINARY"		: "VARBINARY"
		,"SMALLINT"		: "TINYINT"
		,"MEDIUMINT"	: "SMALLINT"
		,"INT"			: "INT"
		,"SIGNED_INT"	: "INT"
		,"BIGINT"		: "BIGINT"
		,"SIGNED_BIGINT": "BIGINT"
		,"FLOAT"		: "FLOAT"
		,"DOUBLE"   	: "REAL"
		,"BOOL"			: "BIT"
		,"TIMESTAMP"	: "DATETIME"
		,"DATETIME"		: "DATETIME"
		,"DATE"			: "DATE"
		,"TIME"			: "TIME"
		,"VARCHAR"	    : "VARCHAR"
		,"TEXT"			: "TEXT"
		,"BLOB"			: "TEXT"
		}

        ,"clauses"      : "[<?start_transaction_clause|>BEGIN TRANSACTION <type|>;][<?commit_transaction_clause|>COMMIT;][<?rollback_transaction_clause|>ROLLBACK;][<?transact_clause|>BEGIN TRANSACTION  <type|>;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]][<?create_clause|>[<?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_clause|>ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]][<?drop_clause|>DROP [<?view|>VIEW][<?!view>TABLE][ <?ifexists|>IF EXISTS] <drop_tables>[,<*drop_tables>]][<?union_clause|>(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]]][<?select_clause|>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_clause|>INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]][<?update_clause|>UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]]][<?delete_clause|>DELETE \nFROM <from_tables>[,<*from_tables>][\nWHERE <?where_conditions>][\nORDER BY <?order_conditions>[,<*order_conditions>]]]"
    }


    ,"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')"]
        }

		,"types"    	: {
		 "BINARY"		: "BLOB"
		,"SMALLINT"		: "INTEGER"
		,"MEDIUMINT"	: "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"
		,"VARCHAR"	    : "TEXT"
		,"TEXT"			: "TEXT"
		,"BLOB"			: "BLOB"
		}

        ,"clauses"      : "[<?start_transaction_clause|>BEGIN <type|> TRANSACTION;][<?commit_transaction_clause|>COMMIT;][<?rollback_transaction_clause|>ROLLBACK;][<?transact_clause|>BEGIN <type|> TRANSACTION;\n<statements>;[\n<*statements>;]\n[<?rollback|>ROLLBACK;][<?!rollback>COMMIT;]][<?create_clause|>[<?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_clause|>ALTER [<?view|>VIEW][<?!view>TABLE] <alter_table>\n<columns>[ <?options>]][<?drop_clause|>DROP [<?view|>VIEW][<?!view>TABLE][ <?ifexists|>IF EXISTS] <drop_tables>][<?union_clause|>(<union_selects>)[\nUNION[<?union_all|> ALL]\n(<*union_selects>)][\nORDER BY <?order_conditions>[,<*order_conditions>]][\nLIMIT <?count> OFFSET <offset|0>]][<?select_clause|>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_clause|>INSERT INTO <insert_tables> (<insert_columns>[,<*insert_columns>])\n[VALUES <?values_values>[,<*values_values>]]][<?update_clause|>UPDATE <update_tables>\nSET <set_values>[,<*set_values>][\nWHERE <?where_conditions>]][<?delete_clause|>[<?!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 ['','','','']
        if not isinstance(self.clauses, Dialect.GrammarTemplate):
            self.clauses = Dialect.GrammarTemplate(self.clauses)
            Dialect.dialects[ self.type ][ 'clauses' ] = self.clauses


    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 = clause
        #if not isinstance(self.clauses[ self.clau ], Dialect.GrammarTemplate):
        #    self.clauses[ self.clau ] = Dialect.GrammarTemplate( self.clauses[ self.clau ] )
        #if not isinstance(self.clauses, Dialect.GrammarTemplate):
        #    self.clauses = Dialect.GrammarTemplate( self.clauses )
        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( self.clus )
            clus[ self.clau+'_clause' ] = 1
            query = self.clauses.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 + '(([rlfds]:)?[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 'd'==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 '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 + '([rlfds]:)?([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 'd'==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 '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'])
            if ('statements' not in self.clus) or not len(self.clus['statements']):
                self.clus['statements'] = statements
            else:
                self.clus['statements'] = 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
        if ('drop_tables' not in self.clus) or not len(self.clus['drop_tables']):
            self.clus['drop_tables'] = tables
        else:
            self.clus['drop_tables'] = 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 )
        if ('select_columns' not in self.clus) or not len(self.clus['select_columns']):
            self.clus['select_columns'] = columns
        else:
            self.clus['select_columns'] = 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)
        if ('union_selects' not in self.clus) or not len(self.clus['union_selects']):
            self.clus['union_selects'] = array(selects)
        else:
            self.clus['union_selects'] = 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 )
            if ('insert_tables' not in self.clus) or not len(self.clus['insert_tables']):
                self.clus['insert_tables'] = tables
            else:
                self.clus['insert_tables'] = self.clus['insert_tables'] + tables
            if ('insert_columns' not in self.clus) or not len(self.clus['insert_columns']):
                self.clus['insert_columns'] = columns
            else:
                self.clus['insert_columns'] = 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 'string' in val:
                            vals.append( self.quote( val['string'] ) )
                    else:
                        vals.append( 'NULL' if val is None else (str(val) if is_int(val) 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 )
            if ('update_tables' not in self.clus) or not len(self.clus['update_tables']):
                self.clus['update_tables'] = tables
            else:
                self.clus['update_tables'] = 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 + " = " + value['raw'] )
                elif 'integer' in value:
                    set_values.append( field + " = " + self.intval2str(value['integer']) )
                elif 'string' in value:
                    set_values.append( field + " = " + self.quote(value['string']) )
                elif 'increment' in value:
                    set_values.append( field + " = " + field + " + " + self.intval2str(value['increment']) )
                elif 'decrement' in value:
                    set_values.append( field + " = " + field + " - " + self.intval2str(value['increment']) )
                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 is_int(value) 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 )
            if ('from_tables' not in self.clus) or not len(self.clus['from_tables']):
                self.clus['from_tables'] = tables
            else:
                self.clus['from_tables'] = 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 = 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 = 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 = 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 'integer' == type or is_int(v[0]):
                        v = self.intval2str( 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 'integer' == type or is_int(v[0]):
                        v = self.intval2str( 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 'integer' == type or is_int(v[1]):
                            v[1] = self.intval( 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 'integer' == type or is_int(v[0]):
                            v[0] = self.intval( v[0] )
                        else:
                            v[0] = self.quote( v[0] )
                        conds.append( field + " >= " + str(v[0]) )
                    else:
                        if 'raw' == type:
                            # raw, do nothing
                            pass
                        elif 'integer' == type or (is_int(v[0]) and is_int(v[1])):
                            v = self.intval( 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 'integer' == type or is_int(v[1]):
                            v[1] = self.intval( 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 'integer' == type or is_int(v[0]):
                            v[0] = self.intval( v[0] )
                        else:
                            v[0] = self.quote( v[0] )
                        conds.append( field + " < " + str(v[0]) )
                    else:
                        if 'raw' == type:
                            # raw, do nothing
                            pass
                        elif 'integer' == type or (is_int(v[0]) and is_int(v[1])):
                            v = self.intval( 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 'integer' == type or is_int(v):
                        v = self.intval( 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 'integer' == type or is_int(v):
                        v = self.intval( 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 'integer' == type or is_int(v):
                        v = self.intval( 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 'integer' == type or is_int(v):
                        v = self.intval( 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 is_int(value) 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 + table

    def intval( self, v ):
        if is_int( v ): return v
        elif is_array( v ): return [self.intval( x ) for x in v]
        else: return int( v, 10 )

    def intval2str( self, v ):
        if is_int( v ): return str(v)
        elif is_array( v ): return [self.intval2str( x ) for x in v]
        else: return str(self.intval( 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 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 ]
        func = ''
        args = [] if args is None else array(args)
        argslen = len(args)
        is_arg = False
        for fi in f:
            func += (args[fi-1] if 0<fi and argslen>=fi else '') if is_arg else fi
            is_arg = not is_arg
        return func


    def sql_type( self, data_type ):
        data_type = str(data_type).upper()
        if data_type not in Dialect.dialects[ self.type ][ 'types' ]:
            raise ValueError('Dialect: SQL type "'+data_type+'" does not exist for dialect "'+self.type+'"')
        return Dialect.dialects[ self.type ][ 'types' ][ data_type ]

__all__ = ['Dialect']


For more information send a message to info at phpclasses dot org.