# SQL code generation

import lists

    to collisions.'''

        str_, old_suffix = match.groups()

        suffix = old_suffix+suffix

    * contains only *lowercase* word (\w) characters

    * doesn't start with a digit

    * contains "_", so that it's not a keyword

    return re.match(r'^(?=.*_)(?!\d)[^\WA-Z]+$', name)

    return quote + name.replace(quote, quote+quote) + quote

        # doubling an embedded quote escapes it in both PostgreSQL and MySQL

    removed_ref = [False]

    name = strings.remove_prefix(quote, name, removed_ref)

    if removed_ref[0]:

        name = strings.remove_suffix(quote, name, removed_ref)

        assert removed_ref[0]

        name = name.replace(quote+quote, quote)

    return name

    '''Also removes comments.'''

    if str_.startswith('/*'): comment, sep, str_ = str_.partition('*/')

    return str_.lstrip()

    def col_info(self, col):

        return TypedCol(col.name, '<type>', CustomCode('<default>'), True)

    def __str__(self): return clean_name(strings.repr_no_u(self))

        self.lang = lang

        Code.__init__(self)

        self.str_ = str_

    def to_str(self, db): return self.str_

    '''

    @param db If set, runs db.std_code() on the value.

    '''

        if db != None: value = db.std_code(value)

        return CustomCode(value)

        Code.__init__(self)

        self.expr = expr

    def to_str(self, db): return '('+self.expr.to_str(db)+')'

class Name(Code):

        self.name = name

    def to_str(self, db): return db.esc_name(self.name)

def as_Name(value):

    if isinstance(value, Code): return value

    else: return Name(value)

        Code.__init__(self)

        self.value = value

    def to_str(self, db): return db.esc_value(self.value)

    if isinstance(value, Code): return value

    else: return Literal(value)

src_self = object() # tells Col that it is its own source column

class Derived(Code):

    def __init__(self, srcs):

        '''

        @param srcs (self_type...)|src_self The element(s) this is derived from

        '''

        srcs = tuple(srcs) # make Col hashable

        self.srcs = srcs

    def _compare_on(self):

        compare_on = self.__dict__.copy()

        del compare_on['srcs'] # ignore

        return compare_on

def cols_srcs(cols): return lists.uniqify(iters.flatten((v.srcs for v in cols)))

        @param schema str|None (for no schema)

        self.schema = schema

        str_ = ''

        str_ += as_Name(self.name).to_str(db)

    def to_Table(self): return self

    def _compare_on(self):

        compare_on = Derived._compare_on(self)

        del compare_on['index_cols'] # ignore

        return compare_on

    return isinstance(table, Table) and table.to_Table() is table

def underlying_table(table):

    table = remove_table_rename(table)

    if not is_underlying_table(table): raise NoUnderlyingTableException

    return table

    table.name = concat(table.name, suffix)

    return table

    def __init__(self, name, code, cols=None):

        Table.__init__(self, name)

        self.code = code

        self.cols = cols

    def to_str(self, db):

        if str_.find('\n') >= 0: whitespace = '\n'

        else: whitespace = ' '

        str_ += whitespace+'AS '+Table.to_str(self, db)

            str_ += ' ('+(', '.join((c.to_str(db) for c in self.cols)))+')'

    def to_Table(self): return Table(self.name)

    if isinstance(table, NamedTable): table = table.code

    return table

        @param table Table|None (for no table)

        self.name = name

        self.table = table

            if for_str: str_ = concat(str(table), '.'+str_)

            else: str_ = table.to_str(db)+'.'+str_

    if not is_table_col(col): return None

    table = col.table

    try: name = table.index_cols[col.name]

    except KeyError: return None

    else: return Col(name, table, col.srcs)

    '''

    @param name If not None, any non-Col input will be renamed using NamedCol.

    '''

    if name != None:

        col = as_Value(col)

        if not isinstance(col, Col): col = NamedCol(name, col)

    if isinstance(col, Code): return col

    elif isinstance(col, FunctionCall):

        col = copy.deepcopy(col) # don't modify input!

        col.args[0].table = table

    else:

        col.table = table

    table = as_Table(table)

    for i, col in enumerate(cols):

        col = cols[i] = as_Col(col)

        col.table = table

    col = as_Col(col)

    if check_table != None:

        table = col.table

        assert table == None or table == check_table

    return Col(col.name)

    return Col(concat(col.name, suffix), col.table, col.srcs)

        self.code = code

    def to_str(self, db):

    def to_Col(self): return Col(self.name)

    if isinstance(col, NamedCol): col = col.code

    return col

    col = remove_col_rename(col)

    '''Wraps a value, propagating any column renaming to the returned value.'''

    if isinstance(value, NamedCol):

        return NamedCol(value.name, wrap_func(value.code))

    else: return wrap_func(value)

        return dicts.DictProxy.__getitem__(self, self._key(key))

    def __init__(self, name, value):

        NamedCol.__init__(self, name, value)

    def to_str(self, db):

        return Col.to_str(self, db)+' := '+self.code.to_str(db)

        args = map(filter_, args)

        args += [NamedArg(k, filter_(v)) for k, v in kw_args.iteritems()]

        self.function = function

        self.args = args

    def to_str(self, db):

        args_str = ', '.join((v.to_str(db) for v in self.args))

        return self.function.to_str(db)+'('+args_str+')'

    '''Wraps a value inside a function call.

    Propagates any column renaming to the returned value.

    '''

    '''Unwraps any function call to its first argument.

    Also removes any column renaming.

    '''

    func_call = remove_col_rename(func_call)

    if not isinstance(func_call, FunctionCall): return func_call

    if check_name != None:

        name = func_call.function.name

        assert name == None or name == check_name

    return func_call.args[0]

class FunctionDef(Code):

        self.return_type = return_type

        self.body = body

    def to_str(self, db):

        str_ = '''\

CREATE FUNCTION '''+self.function.to_str(db)+'''()

RETURNS SETOF '''+self.return_type+'''

'''

        return str_

    def __init__(self, row_type, select_query, with_row, cols=None,

        exc='unique_violation'):

        Code.__init__(self, lang='plpgsql')

        select_query = as_Code(select_query)

        with_row = as_Code(with_row)

        self.row_type = row_type

        self.select_query = select_query

        self.with_row = with_row

        self.cols = cols

        self.exc = exc

    def to_str(self, db):

        if self.cols == None: row_vars = [Table('row')]

        else: row_vars = [Col(c.name, 'row') for c in self.cols]

        str_ = '''\

DECLARE

    row '''+self.row_type+''';

BEGIN

    /* Need an EXCEPTION block for each individual row because "When an error is

    caught by an EXCEPTION clause, [...] all changes to persistent database

    state within the block are rolled back."

    This is unfortunate because "A block containing an EXCEPTION clause is

    significantly more expensive to enter and exit than a block without one."

    (http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html\

#PLPGSQL-ERROR-TRAPPING)

    */

    FOR '''+(', '.join((v.to_str(db) for v in row_vars)))+''' IN

'''+self.select_query.to_str(db)+'''

    LOOP

        BEGIN

            RETURN QUERY

'''+self.with_row.to_str(db)+'''

;

        EXCEPTION

            WHEN '''+self.exc+''' THEN NULL; -- continue to next row

        END;

    END LOOP;

END;\

'''

        return str_

class Cast(FunctionCall):

    def __init__(self, type_, value):

        value = as_Value(value)

        self.type_ = type_

        self.value = value

    def to_str(self, db):

        return 'CAST('+self.value.to_str(db)+' AS '+self.type_+')'

    if util.is_str(value.value): value = Cast('text', value)

    return value

    def __init__(self, cond):

    def to_str(self, db): return 'NOT '+self.cond.to_str(db)

    def __init__(self, col, value):

        self.col = col

        self.value = value

    def to_str(self, db): return self.value.to_str(db, self.col)

    '''

    @param keyword The keyword to add before the conditions, if any

    '''

    str_ = ''

    if keyword != None:

        if conds == []: whitespace = ''

        elif len(conds) == 1: whitespace = ' '

        else: whitespace = '\n'

        str_ += keyword+whitespace

    str_ += '\nAND '.join(conds)

    return str_

        self.value = value

        raise NotImplemented()

class CompareCond(ValueCond):

    def __init__(self, value, operator='='):

        @param operator By default, compares NULL values literally. Use '~=' or

            '~!=' to pass NULLs through.

        '''

        self.operator = operator

        # Parse operator

        operator = strings.remove_prefix('~', operator, passthru_null_ref)

        neg_ref = [False]

        operator = strings.remove_prefix('!', operator, neg_ref)

        check_null = False

                check_null = equals and isinstance(right_value, Col)

                    right_value = ensure_not_null(db, right_value,

                        left_value.type) # apply same function to both sides

        right = right_value.to_str(db)

        str_ = left+' '+operator+' '+right

        if neg_ref[0]: str_ = 'NOT '+str_