def run_tbl_operations(conn_params, form_type, get_data={}, post_data={}):
# generate / derive queries to be run
queries, msg = [], ''
if form_type == 'tbl_edit_form':
queries, msg = sql.alter_table(conn_params['dialect'], get_data, post_data)
elif form_type == 'tbl_vacuum_form':
queries = sql.pg_vacuum_stmt(get_data, post_data)
else:
# implemented queries: analyze table and reindex table
# only the pg dialect
queries = sql.generate_query(form_type, conn_params['dialect'], get_data)
# execute queries and return formatted status messages
if form_type == 'tbl_edit_form':
ret = sa.short_query(conn_params, queries)
else: ret = sa.execute_outside_transaction(conn_params, queries)
if ret['status'] == 'success':
if form_type == 'tbl_edit_form': ret['msg'] = msg + ' change succesfull'
else:
msg = '{command} operation ran succesfully'
if form_type == 'tbl_vacuum_form': com = 'VACUUM'
elif form_type == 'analyze_table': com = 'ANALYZE'
elif form_type == 'reindex_table': com = 'REINDEX'
ret['msg'] = msg.format(command=com)
ret['msg'] = '<p class="query-msg {status}">{msg}</p>'.format(
status = 'error' if ret['status'] == 'error' else 'success',
msg = ret['msg']
)
return ret
def insert_row(conn_params, get_data={}, form_data={}):
# set execution context
conn_params['db'] = get_data['db']
# format form_data ( from a form) according to the following rules
# * add single qoutes to the variables
# * make lists a concatenation of lists
cols, values = [], []
for k in form_data:
if k in ('csrfmiddlewaretoken', 'save_changes_to'): continue
cols.append(k)
if type(form_data[k]) == list:
value = u",".join( form_data[k] )
values.append( fns.str_quote(value) )
else:
values.append( fns.str_quote(form_data[k]) )
# generate sql insert statement
q = u"INSERT INTO {0}{tbl} ({1}) VALUES ({2})".format(
u'{schm}.'.format(**get_data) if conn_params['dialect'] == 'postgresql' else u'',
u",".join(cols), u",".join(values), **get_data
)
# run query and return results
ret = sa.short_query(conn_params, (q, ))
if ret['status'] == 'success': ret['msg'] = 'Insertion succeeded'
# format status messages used in flow control (javascript side)
# replaces with space and new lines with the HTML equivalents
ret['msg'] = '<div class="alert-message block-message {0} span8 data-entry"><code>\
{1}</code></div>'.format(
'success' if ret['status'] == 'success' else 'error',
ret['msg'].replace(' ', ' ').replace('\n', '<br />')
)
return ret
def create_column(conn_params, get_data={}, form_data={}):
# fetch queries
queries = sql.get_column_sql(conn_params['dialect'], get_data, form_data)
ret = sa.short_query(conn_params, queries)
if ret['status'] == 'success': ret['msg'] = 'column creation succesfull'
# format status messages used in flow control (javascript side)
# replaces with space and new lines with the HTML equivalents
ret['msg'] = '<div class="alert-message block-message {0} data-entry"><code>\
{1}</code></div>'.format(
'success' if ret['status'] == 'success' else 'error',
ret['msg'].replace(' ', ' ').replace('\n', '<br />')
)
return ret
def update_row(conn_params, indexed_cols={}, get_data={}, form_data={}):
# set execution context
conn_params['db'] = get_data['db']
# format form_data ( from a form) according to the following rules
# * add single qoutes to the variables
# * make lists a concatenation of lists
cols, values = [], []
for k in form_data:
if k in (u'csrfmiddlewaretoken', u'save_changes_to'): continue
cols.append(k)
if type(form_data[k]) == list:
value = u",".join( form_data[k] )
values.append( fns.str_quote(value) )
else:
values.append( fns.str_quote(form_data[k]) )
# generate SET sub statment
_l_set = []
for i in range(len(cols)):
short_stmt = u"=".join([cols[i], values[i]])
_l_set.append(short_stmt)
# generate WHERE sub statement
_l_where = []
for key in indexed_cols:
short_stmt = u"=".join([ key, fns.str_quote(form_data[key]) ])
_l_where.append(short_stmt)
# generate full query
q = u"UPDATE {0}{tbl} SET {set_stmts} WHERE {where_stmts}".format(
u'{schm}.'.format(**get_data) if conn_params['dialect'] == 'postgresql' else u'',
set_stmts = u", ".join(_l_set), where_stmts = u" AND ".join(_l_where), **get_data
)
# run query and return results
ret = sa.short_query(conn_params, (q, ))
if ret['status'] == 'success': ret['msg'] = 'Row update succeeded'
# format status messages used in flow control (javascript side)
# replaces with space and new lines with the HTML equivalents
ret['msg'] = '<div class="alert-message block-message {0} span12 data-entry"><code>\
{1}</code></div>'.format(
'success' if ret['status'] == 'success' else 'error',
ret['msg'].replace(' ', ' ').replace('\n', '<br />')
)
return ret
def rpr_query(conn_params, query_type, get_data={}, post_data={}):
'''
Run queries that have to be generated on the fly. Most queries depends on get_data,
while some few depends on post_data
get_data and post_data are gotten from request.GET and request.POST or form.cleaned_data
some functions in this file should have been (and previously have been) in this function. but
the contents of a function are not indexed by IDEs and that makes debugging difficult
'''
# common queries that returns success state as a dict only
no_return_queries = ('create_user', 'drop_user', 'create_db','create_table',
'drop_table', 'empty_table', 'delete_row', 'create_column', 'drop_column',
'drop_db', 'drop_sequence', 'reset_sequence', 'drop_constraint', )
psycopg2_queries = ('drop_db', )
if query_type in no_return_queries:
conn_params['db'] = get_data['db'] if get_data.has_key('db') else conn_params['db']
query_data = {}
query_data.update(get_data, **post_data)
queries = sql.generate_query( query_type, conn_params['dialect'],query_data)
if conn_params['dialect'] == 'postgresql' and query_type in psycopg2_queries:
# this queries needs to be run outside a transaction block
# SA execute functions runs all its queries inside a transaction block
result = sa.execute_outside_transaction(conn_params, queries)
else: result = sa.short_query(conn_params, queries)
return HttpResponse( json.dumps(result) )
# specific queries with implementations similar to both dialects
elif query_type in ('indexes', 'primary_keys', 'foreign_key_relation'):
if conn_params['dialect'] == 'postgresql' and query_type == 'indexes':
return get_constraints(conn_params, query_type, get_data)
r = sa.full_query(conn_params,
sql.generate_query(query_type, conn_params['dialect'], get_data)[0])
return r
elif query_type in ('get_single_row',):
sub_q_data = {'tbl': get_data['tbl'],'db':get_data['db']}
if get_data.has_key('schm'):
sub_q_data['schm'] = get_data['schm']
# generate where statement
sub_q_data['where'] = ""
for ind in range(len(post_data)):
sub_q_data['where'] += post_data.keys()[ind].strip() + "="
val = post_data.values()[ind].strip()
sub_q_data['where'] += fns.quote(val)
if ind != len(post_data) - 1: sub_q_data['where'] += ' AND '
# retrieve and run queries
conn_params['db'] = get_data['db']
# assert False
q = sql.generate_query(query_type, conn_params['dialect'], sub_q_data)
r = sa.full_query(conn_params, q[0])
return r
elif query_type in ('table_rpr', 'table_structure', 'raw_table_structure', 'seqs_rpr'):
sub_q_data = {'db': get_data['db'],}
if get_data.has_key('tbl'):
sub_q_data['tbl'] = get_data['tbl']
if get_data.has_key('schm'):
sub_q_data['schm'] = get_data['schm']
# make query
if conn_params['dialect'] == 'postgresql' and query_type == 'raw_table_structure':
q = 'table_structure'
else: q = query_type
r = sa.full_query(conn_params,
sql.generate_query(q, conn_params['dialect'], sub_q_data)[0] )
# further needed processing
if conn_params['dialect'] == 'postgresql' and query_type.count('table_structure'):
rwz = []
for tuple_row in r['rows']:
row = list(tuple_row)
data_type_str = row[1]
_l = [ data_type_str ]
datetime_precision = row[7]
if data_type_str in ('bit', 'bit varying', 'character varying', 'character') and type(row[4]) is int:
_l.append( '({0})'.format(row[4]) )
# elif data_type_str in ('numeric', 'decimal') and type(row[5]) is int or type(row[6]) is int:
elif data_type_str in ('numeric', 'decimal'):
numeric_precision, numeric_scale = row[5], row[6]
_l.append( '(%d,%d)' % (numeric_precision, numeric_scale) )
# interval types should need some revising
elif data_type_str in ('interval', ):
_l.append( '(%d)' % datetime_precision )
# time and timestamps have a somewhat different declarative syntax
elif datetime_precision is int and (data_type_str.startswith('time') or data_type_str.startswith('timestamp')):
_in = 9 if data_type_str.startswith('timestamp') else 4 # the length of time and timestamp respectively
_l = [ data_type_str[:_in], '(%d)' % datetime_precision , data_type_str[_in:]]
# append the current row to rwz
if query_type == 'table_structure': rwz.append([row[0], "".join(_l), row[2], row[3] ])
elif query_type == 'raw_table_structure':
row.append("".join(_l))
rwz.append(row)
# change r['rows']
r['rows'] = rwz
# change r['columns']
if query_type == 'table_structure':
r['columns'] = [ r['columns'][0], r['columns'][1], r['columns'][2], r['columns'][3] ]
elif query_type == 'raw_table_structure': r['columns'].append('column_type')
return r
# queries with dissimilar implementations
elif conn_params['dialect'] == 'mysql':
if query_type == 'describe_databases':
conn_params['db'] = 'INFORMATION_SCHEMA';
query = sql.stored_query(query_type, conn_params['dialect'])
return sa.full_query(conn_params, query)
else:
return fns.http_500('query not yet implemented!')
else:
return fns.http_500('dialect not supported!')
