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)