def get_create_sql(self, curs, new_table_name=None):
     fmt = "ALTER TABLE ONLY %s ADD CONSTRAINT %s %s;"
     if new_table_name:
         name = self.name
         if self.contype in ('p', 'u'):
             name = find_new_name(curs, self.name)
         qtbl = quote_fqident(new_table_name)
         qname = quote_ident(name)
     else:
         qtbl = quote_fqident(self.table_name)
         qname = quote_ident(self.name)
     sql = fmt % (qtbl, qname, self.defn)
     return sql

    def get_create_sql(self, curs, new_seq_name = None):
        """Generate creation SQL."""

        # we are in table def, forget full def
        if self.owner:
            sql = "ALTER SEQUENCE %s OWNED BY %s" % (
                    quote_fqident(self.name), self.owner )
            return sql

        name = self.name
        if new_seq_name:
            name = new_seq_name
        sql = 'CREATE SEQUENCE %s %s;' % (quote_fqident(name), self.defn)
        return sql

def mk_update_sql(row, tbl, pkey_list, field_map = None):
    r"""Generate UPDATE statement from dict data.

    >>> mk_update_sql({'id': 0, 'id2': '2', 'data': 'str\\'}, 'Table', ['id', 'id2'])
    'update only public."Table" set data = E\'str\\\\\' where id = \'0\' and id2 = \'2\';'
    """

    if len(pkey_list) < 1:
        raise Exception("update needs pkeys")
    set_list = []
    whe_list = []
    pkmap = {}
    for k in pkey_list:
        pkmap[k] = 1
        new_k = field_map and field_map[k] or k
        col = quote_ident(new_k)
        val = quote_literal(row[k])
        whe_list.append("%s = %s" % (col, val))

    if field_map:
        for src, dst in field_map.iteritems():
            if src not in pkmap:
                col = quote_ident(dst)
                val = quote_literal(row[src])
                set_list.append("%s = %s" % (col, val))
    else:
        for col, val in row.iteritems():
            if col not in pkmap:
                col = quote_ident(col)
                val = quote_literal(val)
                set_list.append("%s = %s" % (col, val))
    return "update only %s set %s where %s;" % (quote_fqident(tbl),
            ", ".join(set_list), " and ".join(whe_list))

def full_copy(tablename, src_curs, dst_curs, column_list = [], condition = None):
    """COPY table from one db to another."""

    qtable = quote_fqident(tablename)
    if column_list:
        qfields = ",".join([quote_ident(f) for f in column_list])
        src = dst = "%s (%s)" % (qtable, qfields)
    else:
        qfields = '*'
        src = dst = qtable

    if condition:
        src = "(SELECT %s FROM %s WHERE %s)" % (qfields, qtable, condition)

    if hasattr(src_curs, 'copy_expert'):
        sql_to = "COPY %s TO stdout" % src
        sql_from = "COPY %s FROM stdin" % dst
        buf = CopyPipe(dst_curs, sql_from = sql_from)
        src_curs.copy_expert(sql_to, buf)
    else:
        if condition:
            # regular psycopg copy_to generates invalid sql for subselect copy
            raise Exception('copy_expert() is needed for conditional copy')
        buf = CopyPipe(dst_curs, dst)
        src_curs.copy_to(buf, src)
    buf.flush()

    return (buf.total_bytes, buf.total_rows)

def magic_insert(curs, tablename, data, fields = None, use_insert = 0, quoted_table = False):
    r"""Copy/insert a list of dict/list data to database.

    If curs == None, then the copy or insert statements are returned
    as string.  For list of dict the field list is optional, as its
    possible to guess them from dict keys.

    Example:
    >>> magic_insert(None, 'tbl', [[1, '1'], [2, '2']], ['col1', 'col2'])
    'COPY public.tbl (col1,col2) FROM STDIN;\n1\t1\n2\t2\n\\.\n'
    """
    if len(data) == 0:
        return

    # decide how to process
    if hasattr(data[0], 'keys'):
        if fields == None:
            fields = data[0].keys()
        if use_insert:
            row_func = _gen_dict_insert
        else:
            row_func = _gen_dict_copy
    else:
        if fields == None:
            raise Exception("Non-dict data needs field list")
        if use_insert:
            row_func = _gen_list_insert
        else:
            row_func = _gen_list_copy

    qfields = [quote_ident(f) for f in fields]
    if quoted_table:
        qtablename = tablename
    else:
        qtablename = quote_fqident(tablename)

    # init processing
    buf = StringIO()
    if curs == None and use_insert == 0:
        fmt = "COPY %s (%s) FROM STDIN;\n"
        buf.write(fmt % (qtablename, ",".join(qfields)))
 
    # process data
    for row in data:
        buf.write(row_func(qtablename, row, fields, qfields))
        buf.write("\n")

    # if user needs only string, return it
    if curs == None:
        if use_insert == 0:
            buf.write("\\.\n")
        return buf.getvalue()

    # do the actual copy/inserts
    if use_insert:
        curs.execute(buf.getvalue())
    else:
        buf.seek(0)
        hdr = "%s (%s)" % (qtablename, ",".join(qfields))
        curs.copy_from(buf, hdr)

def mk_update_sql(row, tbl, pkey_list, field_map = None):
    """Generate UPDATE statement from dict data."""

    if len(pkey_list) < 1:
        raise Exception("update needs pkeys")
    set_list = []
    whe_list = []
    pkmap = {}
    for k in pkey_list:
        pkmap[k] = 1
        new_k = field_map and field_map[k] or k
        col = quote_ident(new_k)
        val = quote_literal(row[k])
        whe_list.append("%s = %s" % (col, val))

    if field_map:
        for src, dst in field_map.iteritems():
            if src not in pkmap:
                col = quote_ident(dst)
                val = quote_literal(row[src])
                set_list.append("%s = %s" % (col, val))
    else:
        for col, val in row.iteritems():
            if col not in pkmap:
                col = quote_ident(col)
                val = quote_literal(val)
                set_list.append("%s = %s" % (col, val))
    return "update %s set %s where %s;" % (quote_fqident(tbl),
            ", ".join(set_list), " and ".join(whe_list))

 def get_create_sql(self, curs, new_table_name = None):
     """Generate creation SQL."""
     if new_table_name:
         # fixme: seems broken
         iname = find_new_name(curs, self.name)
         tname = new_table_name
         pnew = "INDEX %s ON %s " % (quote_ident(iname), quote_fqident(tname))
         rx = r"\bINDEX[ ][a-z0-9._]+[ ]ON[ ][a-z0-9._]+[ ]"
         sql = rx_replace(rx, self.defn, pnew)
     else:
         sql = self.defn
         iname = self.local_name
         tname = self.table_name
     if self.is_clustered:
         sql += ' ALTER TABLE ONLY %s\n  CLUSTER ON %s;' % (
             quote_fqident(tname), quote_ident(iname))
     return sql

 def get_create_sql(self, curs, new_name = None):
     """Generate creation SQL."""
     if not new_name:
         new_name = self.name
     sql = "CREATE TABLE %s (" % quote_fqident(new_name)
     sep = "\n    "
     for c in self.col_list:
         sql += sep + c.column_def
         sep = ",\n    "
     sql += "\n);"
     return sql

def mk_delete_sql(row, tbl, pkey_list, field_map = None):
    """Generate DELETE statement from dict data."""

    if len(pkey_list) < 1:
        raise Exception("delete needs pkeys")
    whe_list = []
    for k in pkey_list:
        new_k = field_map and field_map[k] or k
        col = quote_ident(new_k)
        val = quote_literal(row[k])
        whe_list.append("%s = %s" % (col, val))
    whe_str = " and ".join(whe_list) 
    return "delete from %s where %s;" % (quote_fqident(tbl), whe_str)

def mk_insert_sql(row, tbl, pkey_list = None, field_map = None):
    """Generate INSERT statement from dict data."""

    col_list = []
    val_list = []
    if field_map:
        for src, dst in field_map.iteritems():
            col_list.append(quote_ident(dst))
            val_list.append(quote_literal(row[src]))
    else:
        for c, v in row.iteritems():
            col_list.append(quote_ident(c))
            val_list.append(quote_literal(v))
    col_str = ", ".join(col_list)
    val_str = ", ".join(val_list)
    return "insert into %s (%s) values (%s);" % (
                    quote_fqident(tbl), col_str, val_str)

def full_copy(tablename, src_curs, dst_curs, column_list=[]):
    """COPY table from one db to another."""

    qtable = quote_fqident(tablename)
    if column_list:
        qfields = [quote_ident(f) for f in column_list]
        hdr = "%s (%s)" % (qtable, ",".join(qfields))
    else:
        hdr = qtable
    if hasattr(src_curs, "copy_expert"):
        sql_to = "COPY %s TO stdout" % hdr
        sql_from = "COPY %s FROM stdout" % hdr
        buf = CopyPipe(dst_curs, sql_from=sql_from)
        src_curs.copy_expert(sql_to, buf)
    else:
        buf = CopyPipe(dst_curs, hdr)
        src_curs.copy_to(buf, hdr)
    buf.flush()

    return (buf.total_bytes, buf.total_rows)

 def get_drop_sql(self, curs):
     return 'ALTER TABLE ONLY %s NO INHERIT %s' % (quote_fqident(self.name), quote_fqident(self.parent_name))

    def __init__(self, curs, name):
        """Initializes class by loading info about table_name from database."""

        self.name = name
        self.fqname = quote_fqident(name)

 def get_drop_sql(self, curs):
     return "ALTER TABLE %s ALTER COLUMN %s\n  DROP DEFAULT;" % (
             quote_fqident(self.table_name), quote_ident(self.name))