def generate_query(query_type, dialect='postgresql', query_data=None):
'''
Generates queries of ``query_type`` with the given ``query_data``. Queries here need
some form(s) of translation.
Queries common to all dialects are written out here while others are left to their
own files.
The generated queries are returned as a tuple of strings.
'''
# init
if query_data.has_key('schm'):
prfx = "{schm}.".format(**query_data) if dialect =='postgresql' else ""
else: prfx = ""
#queries
if query_type == 'get_single_row':
q0 = "SELECT * FROM {0}{tbl} WHERE {where} LIMIT 1".format(prfx, **query_data)
return (q0,)
elif query_type == 'browse_table':
q0 = "SELECT * FROM {0}{tbl}"
if query_data.has_key('sort_key') and query_data.has_key('sort_dir'):
q0 += " ORDER BY {sort_key} {sort_dir}"
q0 += " LIMIT {limit} OFFSET {offset}"
return (q0.format(prfx, **query_data),)
elif query_type == 'count_rows':
q0 = "SELECT count(*) FROM {0}{tbl}".format(prfx, **query_data)
return (q0,)
elif query_type == 'drop_table':
queries = []
for where in query_data['conditions']:
queries.append( "DROP TABLE {0}{table}".format(prfx, **where))
return tuple(queries)
elif query_type == 'empty_table':
queries = []
for where in query_data['conditions']:
queries.append( "TRUNCATE {0}{table}".format(prfx, **where) )
return tuple(queries)
elif query_type == 'delete_row':
queries = []
where_stmt = fns.where_frm_conditns( query_data['conditions'] )
for where_cond in where_stmt:
q0 = "DELETE FROM {0}{tbl}".format(prfx, **query_data) + " WHERE "+where_cond
queries.append(q0)
return tuple(queries)
elif query_type == 'drop_db':
queries = []
for where in query_data['conditions']:
queries.append( "DROP DATABASE {name}".format(**where) )
return tuple(queries)
elif query_type == 'drop_column':
queries = []
for where in query_data['conditions']:
sql_stmt = "ALTER TABLE {schm_prefx}{tbl} DROP COLUMN {column}".format(
schm_prefx=prfx, tbl=query_data['tbl'], **where)
queries.append(sql_stmt)
return tuple(queries)
elif query_type == 'drop_constraint':
queries = []
for where in query_data['conditions']:
unsub_stmt = u"ALTER TABLE {schm}.{tbl} DROP {constraint_type}"
# convieniency hack : looks like hard coding
# spaces are enced as \xa0
if where['type'] == 'unique': where['type'] = 'INDEX'
where['type'] = where['type'].replace(u'\xa0', ' ')
where['type'] = where['type'].upper()
# accomodate syntax differences
if dialect == 'mysql' and where['type'] == 'PRIMARY KEY': pass
else: unsub_stmt += " {name}"
# substitute variable placeholders with their respective values
queries.append( unsub_stmt.format(
schm = query_data.get('schm') if dialect == 'postgresql' else query_data.get('db'),
constraint_type = 'CONSTRAINT' if dialect == 'postgresql' else where['type'],
tbl = query_data.get('tbl'),
**where))
return tuple(queries)
if dialect == 'postgresql':
return pgsql.generate_query(query_type, query_data=query_data)
elif dialect == 'mysql':
return mysql.generate_query(query_type, query_data=query_data)
def generate_query(query_type, dialect='postgresql', query_data=None):
'''
Generates queries of ``query_type`` with the given ``query_data``. Queries here need
some form(s) of translation.
Queries common to all dialects are written out here while others are left to their
own files.
The generated queries are returned as a tuple of strings.
'''
# init
if query_data.has_key('schm'):
prfx = "{schm}.".format(**query_data) if dialect =='postgresql' else ""
else: prfx = ""
#queries
if query_type == 'get_single_row':
q0 = "SELECT * FROM {0}{tbl} WHERE {where} LIMIT 1".format(prfx, **query_data)
return (q0,)
elif query_type == 'browse_table':
q0 = "SELECT * FROM {0}{tbl}"
if query_data.has_key('sort_key') and query_data.has_key('sort_dir'):
q0 += " ORDER BY {sort_key} {sort_dir}"
q0 += " LIMIT {limit} OFFSET {offset}"
return (q0.format(prfx, **query_data),)
elif query_type == 'count_rows':
q0 = "SELECT count(*) FROM {0}{tbl}".format(prfx, **query_data)
return (q0,)
elif query_type == 'drop_table':
queries = []
for where in query_data['conditions']:
queries.append( "DROP TABLE {0}{table}".format(prfx, **where))
return tuple(queries)
elif query_type == 'empty_table':
queries = []
for where in query_data['conditions']:
queries.append( "TRUNCATE {0}{table}".format(prfx, **where) )
return tuple(queries)
elif query_type == 'delete_row':
queries = []
where_stmt = fns.where_frm_conditns( query_data['conditions'] )
for where_cond in where_stmt:
q0 = "DELETE FROM {0}{tbl}".format(prfx, **query_data) + " WHERE "+where_cond
queries.append(q0)
return tuple(queries)
elif query_type == 'drop_db':
queries = []
for where in query_data['conditions']:
queries.append( "DROP DATABASE {name}".format(**where) )
return tuple(queries)
elif query_type == 'drop_column':
queries = []
for where in query_data['conditions']:
sql_stmt = "ALTER TABLE {schm_prefx}{tbl} DROP COLUMN {column}".format(
schm_prefx=prfx, tbl=query_data['tbl'], **where)
queries.append(sql_stmt)
return tuple(queries)
elif query_type == 'drop_foreign_key':
queries = []
for where in query_data['conditions']:
where['name'] = where['name'].replace("'", "")
queries.append( "ALTER TABLE {schm}.{tbl} DROP {symbol_name} {name}".format(
schm = query_data.get('schm') if dialect == 'postgresql' else query_data.get('db'),
symbol_name = 'CONSTRAINT' if dialect == 'postgresql' else 'FOREIGN KEY',
tbl = query_data.get('tbl'),
**where))
return tuple(queries)
if dialect == 'postgresql':
return pgsql.generate_query(query_type, query_data=query_data)
elif dialect == 'mysql':
return mysql.generate_query(query_type, query_data=query_data)