dw_build_sql

dw_build_sql   db   query_id

Returns the SQL code for a query, based on information in the query_columns table. Returns a list of $sql $select_list_items $order_clauses. Returns 0 if there aren't enough columns specified to form a query.

Source code:

    set select_list_items [list]
    set group_by_items [list]

    set selection [ns_db select $db "select column_name, pretty_name
    from query_columns 
    where query_id = $query_id
    and what_to_do = 'select_and_group_by'"]

    while { [ns_db getrow $db $selection] } {
	set_variables_after_query
	if [empty_string_p $pretty_name] {
	    lappend select_list_items $column_name
	} else {
	    lappend select_list_items "$column_name as \"$pretty_name\""
	}
	lappend group_by_items $column_name
    }

    set selection [ns_db select $db "select column_name, pretty_name, value1
    from query_columns 
    where query_id = $query_id
    and what_to_do = 'select_and_aggregate'"]

    while { [ns_db getrow $db $selection] } {
	set_variables_after_query
	if [empty_string_p $pretty_name] {
	    lappend select_list_items "${value1}($column_name)"
	} else {
	    lappend select_list_items "${value1}($column_name) as \"$pretty_name\""
	}
    }

    set selection [ns_db select $db "select column_name, value1, value2
    from query_columns 
    where query_id = $query_id
    and what_to_do = 'restrict_by'"]

    set where_clauses [list]

    while { [ns_db getrow $db $selection] } {
	set_variables_after_query
	lappend where_clauses "$column_name $value2 '[DoubleApos $value1]'"
    }


    set selection [ns_db select $db "select column_name
    from query_columns 
    where query_id = $query_id
    and what_to_do = 'order_by'"]

    set order_clauses [list]

    while { [ns_db getrow $db $selection] } {
	set_variables_after_query
	lappend order_clauses "$column_name"
    }

    if { [llength $select_list_items] == 0 } {
	return 0
    }

    set sql "SELECT [join $select_list_items ", "]
FROM [dw_table_name]\n"

    if { [llength $where_clauses] > 0 } {
	append sql "WHERE [join $where_clauses " AND "]\n"
    }

    if { [llength $group_by_items] > 0 } {
	append sql "GROUP BY [join $group_by_items ", "]\n"
    }

    if { [llength $order_clauses] > 0 } {
	append sql "ORDER BY [join $order_clauses ", "]"
    }

    return [list $sql $select_list_items $order_clauses]