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_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 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 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 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 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 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 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 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 get_drop_sql(self, curs):
list = []
for user, acl, who in self.acl_list:
sql = "REVOKE ALL FROM %s ON %s;" % (quote_ident(user),
quote_fqident(self.name))
list.append(sql)
return "\n".join(list)
def get_create_sql(self, curs, new_table_name=None):
"""Generate creation SQL."""
# no ONLY here as table with childs (only case that matters)
# cannot have contraints that childs do not have
fmt = "ALTER TABLE %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_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 CLUSTER ON %s;' % (
quote_fqident(tname), quote_ident(iname))
return sql
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_table_name=None):
"""Generate creation SQL."""
# no ONLY here as table with childs (only case that matters)
# cannot have contraints that childs do not have
fmt = "ALTER TABLE %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)
if self.is_clustered:
sql +=' ALTER TABLE ONLY %s CLUSTER ON %s;' % (qtbl, qname)
return sql
def get_create_sql(self, curs, new_table_name = None):
if not new_table_name:
return self.defn
# fixme: seems broken
name = find_new_name(curs, self.name)
pnew = "INDEX %s ON %s " % (quote_ident(name), quote_fqident(new_table_name))
rx = r"\bINDEX[ ][a-z0-9._]+[ ]ON[ ][a-z0-9._]+[ ]"
sql = rx_replace(rx, self.defn, pnew)
return sql
def magic_insert(curs, tablename, data, fields = None, use_insert = 0):
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]
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 get_create_sql(self, curs, new_name=None):
if not new_name:
new_name = self.name
sql = "create table %s (" % quote_fqident(new_name)
sep = "\n\t"
for c in self.col_list:
sql += sep + c.column_def
sep = ",\n\t"
sql += "\n);"
return sql
def get_create_sql(self, curs, new_table_name=None):
if not new_table_name:
return self.defn
# fixme: seems broken
name = find_new_name(curs, self.name)
pnew = "INDEX %s ON %s " % (quote_ident(name),
quote_fqident(new_table_name))
rx = r"\bINDEX[ ][a-z0-9._]+[ ]ON[ ][a-z0-9._]+[ ]"
sql = rx_replace(rx, self.defn, pnew)
return sql
def get_create_sql(self, curs, new_name = None):
if not new_name:
new_name = self.name
list = []
for user, acl, who in self.acl_list:
astr = self.acl_to_grants(acl)
sql = "GRANT %s ON %s TO %s;" % (astr, quote_fqident(new_name), quote_ident(user))
list.append(sql)
return "\n".join(list)
def get_create_sql(self, curs, new_name = None):
if not new_name:
new_name = self.name
sql = "create table %s (" % quote_fqident(new_name)
sep = "\n\t"
for c in self.col_list:
sql += sep + c.column_def
sep = ",\n\t"
sql += "\n);"
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 get_create_sql(self, curs, new_name=None):
if not new_name:
new_name = self.name
list = []
for user, acl, who in self.acl_list:
astr = self.acl_to_grants(acl)
sql = "GRANT %s ON %s TO %s;" % (astr, quote_fqident(new_name),
quote_ident(user))
list.append(sql)
return "\n".join(list)
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 only %s where %s;" % (quote_fqident(tbl), whe_str)
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 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)"
if self.dist_key_list is not None:
if self.dist_key_list != []:
sql += "\ndistributed by(%s)" % ','.join(c.name for c
in self.dist_key_list)
else:
sql += '\ndistributed randomly'
sql += ";"
return sql
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 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 mk_insert_sql(row, tbl, pkey_list=None, field_map=None):
"""Generate INSERT statement from dict data.
>>> mk_insert_sql({'id': '1', 'data': None}, 'tbl')
"insert into public.tbl (data, id) values (null, '1');"
"""
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 get_drop_sql(self, curs):
return 'ALTER TABLE ONLY %s NO INHERIT %s' % (quote_fqident(self.name), quote_fqident(self.parent_name))
def get_create_sql(self, curs, new_name = None):
"""Generate creation SQL."""
if not new_name:
new_name = self.table_name
return 'ALTER TABLE %s OWNER TO %s;' % (quote_fqident(new_name), quote_ident(self.owner))
def get_drop_sql(self, curs):
return 'DROP TRIGGER %s ON %s' % (quote_ident(self.name), quote_fqident(self.table_name))
def get_create_sql(self, curs, new_table_name = None):
return 'ALTER TABLE ONLY %s 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 'DROP INDEX %s;' % quote_fqident(self.name)
def get_drop_sql(self, curs):
return "DROP TABLE %s;" % quote_fqident(self.name)
def get_drop_sql(self, curs):
return 'ALTER TABLE ONLY %s NO INHERIT %s' % (quote_fqident(self.name), quote_fqident(self.parent_name))
def get_drop_sql(self, curs):
return 'DROP TRIGGER %s ON %s' % (quote_ident(
self.name), quote_fqident(self.table_name))
def get_create_sql(self, curs, new_name=None):
if not new_name:
new_name = self.table_name
return 'ALTER TABLE %s OWNER TO %s;' % (quote_fqident(new_name),
quote_ident(self.owner))
def get_drop_sql(self, curs):
return 'DROP INDEX %s;' % quote_fqident(self.name)
def get_create_sql(self, curs, new_name = None):
"""Generate creation SQL."""
tbl = new_name or self.table_name
sql = "ALTER TABLE ONLY %s ALTER COLUMN %s\n SET DEFAULT %s;" % (
quote_fqident(tbl), quote_ident(self.name), self.expr)
return sql
def get_create_sql(self, curs, new_table_name = None):
return 'ALTER TABLE ONLY %s INHERIT %s' % (quote_fqident(self.name), quote_fqident(self.parent_name))
def get_drop_sql(self, curs):
sql_list = []
for user, acl, who in self.acl_list:
sql = "REVOKE ALL FROM %s ON %s;" % (quote_ident(user), quote_fqident(self.name))
sql_list.append(sql)
return "\n".join(sql_list)
def get_drop_sql(self, curs):
if self.owner:
return ''
return 'DROP SEQUENCE %s;' % quote_fqident(self.name)
def get_drop_sql(self, curs):
return "DROP TABLE %s;" % quote_fqident(self.name)
def get_drop_sql(self, curs):
"""Generate removal sql."""
fmt = "ALTER TABLE ONLY %s DROP CONSTRAINT %s;"
sql = fmt % (quote_fqident(self.table_name), quote_ident(self.name))
return sql
def get_drop_sql(self, curs):
if self.owner:
return ''
return 'DROP SEQUENCE %s;' % quote_fqident(self.name)
def get_drop_sql(self, curs):
fmt = "ALTER TABLE ONLY %s DROP CONSTRAINT %s;"
sql = fmt % (quote_fqident(self.table_name), quote_ident(self.name))
return sql
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))
def __init__(self, curs, name):
"""Initializes class by loading info about table_name from database."""
self.name = name
self.fqname = quote_fqident(name)