dw_build_sql db query_idWhat it does:
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.Defined in: /web/philip/tcl/dw-defs.tcl
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]