def run_query(self, sql, params=None, **kvargs):
""" Helper function if everything you need is just paramertisized execute
Sets rows_found that is coneninet to use when you don't need result just
want to know how many rows were affected
"""
params = params or kvargs
rows = skytools.plpy_exec(self.global_dict, sql, params)
# convert result rows to dbdict
if rows:
rows = [dbdict(r) for r in rows]
self.rows_found = len(rows)
else:
self.rows_found = 0
return rows
def run_query(self, sql, params = None, **kvargs):
""" Helper function if everything you need is just paramertisized execute
Sets rows_found that is coneninet to use when you don't need result just
want to know how many rows were affected
"""
params = params or kvargs
rows = skytools.plpy_exec(self.global_dict, sql, params)
# convert result rows to dbdict
if rows:
rows = [dbdict(r) for r in rows]
self.rows_found = len(rows)
else:
self.rows_found = 0
return rows
def applyrow(tblname, ev_type, new_row,
backup_row = None,
alt_pkey_cols = None,
fkey_cols = None,
fkey_ref_table = None,
fkey_ref_cols = None,
fn_canapply = canapply_dummy,
fn_colfilter = colfilter_full):
"""Core logic. Actual decisions will be done in callback functions.
- [IUD]: If row referenced by fkey does not exist, event is not applied
- If pkey does not exist but alt_pkey does, row is not applied.
@param tblname: table name, schema-qualified
@param ev_type: [IUD]:pkey1,pkey2
@param alt_pkey_cols: list of alternatice columns to consuder
@param fkey_cols: columns in this table that refer to other table
@param fkey_ref_table: other table referenced here
@param fkey_ref_cols: column in other table that must match
@param fn_canapply: callback function, gets new and old row, returns whether the row should be applied
@param fn_colfilter: callback function, gets new and old row, returns dict of final columns to be applied
"""
gd = None
# parse ev_type
tmp = ev_type.split(':', 1)
if len(tmp) != 2 or tmp[0] not in ('I', 'U', 'D'):
raise DataError('Unsupported ev_type: '+repr(ev_type))
if not tmp[1]:
raise DataError('No pkey in event')
cmd = tmp[0]
pkey_cols = tmp[1].split(',')
qtblname = skytools.quote_fqident(tblname)
# parse ev_data
fields = skytools.db_urldecode(new_row)
if ev_type.find('}') >= 0:
raise DataError('Really suspicious activity')
if ",".join(fields.keys()).find('}') >= 0:
raise DataError('Really suspicious activity 2')
# generate pkey expressions
tmp = ["%s = {%s}" % (skytools.quote_ident(k), k) for k in pkey_cols]
pkey_expr = " and ".join(tmp)
alt_pkey_expr = None
if alt_pkey_cols:
tmp = ["%s = {%s}" % (skytools.quote_ident(k), k) for k in alt_pkey_cols]
alt_pkey_expr = " and ".join(tmp)
log = "data ok"
#
# Row data seems fine, now apply it
#
if fkey_ref_table:
tmp = []
for k, rk in zip(fkey_cols, fkey_ref_cols):
tmp.append("%s = {%s}" % (skytools.quote_ident(rk), k))
fkey_expr = " and ".join(tmp)
q = "select 1 from only %s where %s" % (
skytools.quote_fqident(fkey_ref_table),
fkey_expr)
res = skytools.plpy_exec(gd, q, fields)
if not res:
return "IGN: parent row does not exist"
log += ", fkey ok"
# fetch old row
if alt_pkey_expr:
q = "select * from only %s where %s for update" % (qtblname, alt_pkey_expr)
res = skytools.plpy_exec(gd, q, fields)
if res:
oldrow = res[0]
# if altpk matches, but pk not, then delete
need_del = 0
for k in pkey_cols:
# fixme: proper type cmp?
if fields[k] != str(oldrow[k]):
need_del = 1
break
if need_del:
log += ", altpk del"
q = "delete from only %s where %s" % (qtblname, alt_pkey_expr)
skytools.plpy_exec(gd, q, fields)
res = None
else:
log += ", altpk ok"
else:
# no altpk
q = "select * from only %s where %s for update" % (qtblname, pkey_expr)
res = skytools.plpy_exec(None, q, fields)
# got old row, with same pk and altpk
if res:
oldrow = res[0]
log += ", old row"
ok = fn_canapply(fields, oldrow)
if ok:
log += ", new row better"
if not ok:
# ignore the update
return "IGN:" + log + ", current row more up-to-date"
else:
log += ", no old row"
oldrow = None
if res:
if cmd == 'I':
cmd = 'U'
else:
if cmd == 'U':
cmd = 'I'
# allow column changes
if oldrow:
fields2 = fn_colfilter(fields, oldrow)
for k in pkey_cols:
if k not in fields2:
fields2[k] = fields[k]
fields = fields2
# apply change
if cmd == 'I':
q = skytools.mk_insert_sql(fields, tblname, pkey_cols)
elif cmd == 'U':
q = skytools.mk_update_sql(fields, tblname, pkey_cols)
elif cmd == 'D':
q = skytools.mk_delete_sql(fields, tblname, pkey_cols)
else:
plpy.error('Huh')
plpy.execute(q)
return log
def applyrow(tblname,
ev_type,
new_row,
backup_row=None,
alt_pkey_cols=None,
fkey_cols=None,
fkey_ref_table=None,
fkey_ref_cols=None,
fn_canapply=canapply_dummy,
fn_colfilter=colfilter_full):
"""Core logic. Actual decisions will be done in callback functions.
- [IUD]: If row referenced by fkey does not exist, event is not applied
- If pkey does not exist but alt_pkey does, row is not applied.
@param tblname: table name, schema-qualified
@param ev_type: [IUD]:pkey1,pkey2
@param alt_pkey_cols: list of alternatice columns to consuder
@param fkey_cols: columns in this table that refer to other table
@param fkey_ref_table: other table referenced here
@param fkey_ref_cols: column in other table that must match
@param fn_canapply: callback function, gets new and old row, returns whether the row should be applied
@param fn_colfilter: callback function, gets new and old row, returns dict of final columns to be applied
"""
gd = None
# parse ev_type
tmp = ev_type.split(':', 1)
if len(tmp) != 2 or tmp[0] not in ('I', 'U', 'D'):
raise DataError('Unsupported ev_type: ' + repr(ev_type))
if not tmp[1]:
raise DataError('No pkey in event')
cmd = tmp[0]
pkey_cols = tmp[1].split(',')
qtblname = skytools.quote_fqident(tblname)
# parse ev_data
fields = skytools.db_urldecode(new_row)
if ev_type.find('}') >= 0:
raise DataError('Really suspicious activity')
if ",".join(fields.keys()).find('}') >= 0:
raise DataError('Really suspicious activity 2')
# generate pkey expressions
tmp = ["%s = {%s}" % (skytools.quote_ident(k), k) for k in pkey_cols]
pkey_expr = " and ".join(tmp)
alt_pkey_expr = None
if alt_pkey_cols:
tmp = [
"%s = {%s}" % (skytools.quote_ident(k), k) for k in alt_pkey_cols
]
alt_pkey_expr = " and ".join(tmp)
log = "data ok"
#
# Row data seems fine, now apply it
#
if fkey_ref_table:
tmp = []
for k, rk in zip(fkey_cols, fkey_ref_cols):
tmp.append("%s = {%s}" % (skytools.quote_ident(rk), k))
fkey_expr = " and ".join(tmp)
q = "select 1 from only %s where %s" % (
skytools.quote_fqident(fkey_ref_table), fkey_expr)
res = skytools.plpy_exec(gd, q, fields)
if not res:
return "IGN: parent row does not exist"
log += ", fkey ok"
# fetch old row
if alt_pkey_expr:
q = "select * from only %s where %s for update" % (qtblname,
alt_pkey_expr)
res = skytools.plpy_exec(gd, q, fields)
if res:
oldrow = res[0]
# if altpk matches, but pk not, then delete
need_del = 0
for k in pkey_cols:
# fixme: proper type cmp?
if fields[k] != str(oldrow[k]):
need_del = 1
break
if need_del:
log += ", altpk del"
q = "delete from only %s where %s" % (qtblname, alt_pkey_expr)
skytools.plpy_exec(gd, q, fields)
res = None
else:
log += ", altpk ok"
else:
# no altpk
q = "select * from only %s where %s for update" % (qtblname, pkey_expr)
res = skytools.plpy_exec(None, q, fields)
# got old row, with same pk and altpk
if res:
oldrow = res[0]
log += ", old row"
ok = fn_canapply(fields, oldrow)
if ok:
log += ", new row better"
if not ok:
# ignore the update
return "IGN:" + log + ", current row more up-to-date"
else:
log += ", no old row"
oldrow = None
if res:
if cmd == 'I':
cmd = 'U'
else:
if cmd == 'U':
cmd = 'I'
# allow column changes
if oldrow:
fields2 = fn_colfilter(fields, oldrow)
for k in pkey_cols:
if k not in fields2:
fields2[k] = fields[k]
fields = fields2
# apply change
if cmd == 'I':
q = skytools.mk_insert_sql(fields, tblname, pkey_cols)
elif cmd == 'U':
q = skytools.mk_update_sql(fields, tblname, pkey_cols)
elif cmd == 'D':
q = skytools.mk_delete_sql(fields, tblname, pkey_cols)
else:
plpy.error('Huh')
plpy.execute(q)
return log
