예제 #1
0
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)
예제 #2
0
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)