# This is part of an ArsDigita Community System 3.x module
# called the Schema and Data Browser, available here:
# http://eveander.com/arsdigita/acs-repository/sdb

ad_page_contract {
    Shows the definition for all tables, views, sequences, triggers,
    and PL/SQL objects in a database.

    @author eveander@eveander.com
    @creation-date 18 October 2002
    @cvs-id $Id: objects-all.tcl,v 1.2 2002/10/25 18:17:31 eve Exp $
} {
}

set title "All database objects and their definitions"

ReturnHeaders

ns_write "[ad_admin_header $title]
<h2>$title</h2>

[ad_admin_context_bar [list "/admin/sdb/" "Schema and Data Browser"] "All Database Objects"]

<hr>

<h3>Tables</h3>

<ul>

"

# put table_names into a list and then loop through them w/tcl to avoid running
# out of database handles

set table_name_list [list]
db_foreach get_all_table_names "select table_name from user_tables order by table_name" {
    lappend table_name_list $table_name
}

foreach table_name $table_name_list {
    ns_write "<li><font color=009900>$table_name</font>"

    # see if there are any table-level comments
    set table_comments ""
    db_foreach get_table_level_comments "select comments from user_tab_comments where table_name = :table_name and comments is not null" {
	append table_comments "$comments<p>"
    }

    ns_write "$table_comments
    <table border=1>
    <tr><th>column_name</th><th>data_type</th><th>data_length</th><th>data_scale</th><th>data_precision</th><th>nullable</th><th>data_default</th><th>constraints</th><th>comments</th></tr>"

    db_foreach get_all_columns "select column_name, data_type, data_length, data_scale, data_precision, nullable from user_tab_columns where table_name = :table_name" {

        # get data_default, which is a long (so our Oracle driver can't select it directly)
	set data_default [db_exec_plsql get_data_default "begin :1 := sdb_get_col_data_default(:table_name,:column_name); end;"]

	# see if there are any row-level comments
	set row_comments ""
	db_foreach get_row_level_comments "select comments from user_col_comments where table_name = :table_name and column_name = :column_name and comments is not null" {
	    append row_comments "$comments<p>"
	}

        # get constraints
        set constraint_list [list]
	db_foreach get_constraints "select ucc.constraint_name, uc.constraint_type, uc.r_constraint_name
        from user_cons_columns ucc, user_constraints uc
        where ucc.constraint_name = uc.constraint_name
        and ucc.table_name = :table_name
        and ucc.column_name = :column_name" {

            # constraint_type can be:
            # C: check
            # R: references
            # P: primary key
            # U: unique

            if { $constraint_type == "C" } {
                set search_condition [db_exec_plsql get_search_condition "begin :1 := sdb_get_con_search_condition(:constraint_name); end;"]
                lappend constraint_list "$constraint_name: check $search_condition"
	    } elseif { $constraint_type == "R" } {
    set r_table_name [db_string get_table_referenced "select table_name from user_constraints where constraint_name = :r_constraint_name"]
                lappend constraint_list "$constraint_name: references $r_table_name"
	    } elseif { $constraint_type == "P" } {
                lappend constraint_list "$constraint_name: primary key"
	    } elseif { $constraint_type == "U" } {
                lappend constraint_list "$constraint_name: unique"
	    } else {
                lappend constraint_list "$constraint_name: unknown constraint of type $constraint_type"
	    }


	}

        set constraints [join $constraint_list "<br>"]

	ns_write "<tr><td>$column_name</td><td>$data_type</td><td>$data_length</td><td>[ec_nbsp_if_null $data_scale]</td><td>[ec_nbsp_if_null $data_precision]</td><td>$nullable</td><td>[ec_nbsp_if_null $data_default]</td><td>[ec_nbsp_if_null $constraints]</td><td>[ec_nbsp_if_null $row_comments]</td></tr>"

    }

    ns_write "</table>
    <p>
    Indices:
    <ul>
    "

    set old_index_name ""
    set column_list [list]
    db_foreach get_all_indices "select index_name, column_name from user_ind_columns where table_name=:table_name order by index_name" {
	if { $old_index_name != $index_name && ![empty_string_p $old_index_name] } {
            ns_write "<li>$old_index_name on [join $column_list ", "]"
            set column_list [list]
	}
        lappend column_list $column_name
        set old_index_name $index_name
    }
    # write out last index
    if { ![empty_string_p $old_index_name] } {
        ns_write "<li>$old_index_name on [join $column_list ", "]"
    } else {
        ns_write "\[none\]"
    }


    ns_write "</ul>
    <p>
    "

}

ns_write "</ul>

<h3>Views</h3>

<ul>
"

db_foreach get_all_views "select view_name from user_views order by view_name" {
    ns_write "<li><font color=009900>$view_name</font>
    <p>
    <pre>
    "

    # get text, which is a long
    set text [db_exec_plsql get_text "begin :1 := sdb_get_view_text(:view_name); end;"]


    ns_write "$text
    </pre>
    <p>
    "
}

ns_write "</ul>

<h3>Sequences</h3>

<ul>
"

db_foreach get_all_sequences "select sequence_name, min_value, max_value, increment_by, cycle_flag, order_flag, cache_size
from user_sequences
order by sequence_name" {

    set sequence_def "create sequence $sequence_name"

    if { $increment_by > 0 } {
        if { $increment_by != 1 } {
            append sequence_def "<br>increment by $increment_by"
        }
        if { [string compare $min_value "1"] != 0 } {
            append sequence_def "<br>minvalue $min_value"
        }
        if { [string compare $max_value "999999999999999999999999999"] != 0 } {
            append sequence_def "<br>maxvalue $max_value"
        }

    } else {
        append sequence_def "<br>increment by $increment_by"

        if { [string compare $min_value "-99999999999999999999999999"] != 0 } {
            append sequence_def "<br>minvalue $min_value"
        }
        if { [string compare $max_value "-1"] != 0 } {
            append sequence_def "<br>maxvalue $max_value"
        }
    }

    if { $cycle_flag == "Y" } {
        append sequence_def "<br>cycle"
    }
    if { $order_flag == "Y" } {
        append sequence_def "<br>order"
    }
    if { $cache_size != 20 } {
        append sequence_def "<br>cache $cache_size"
    }

    ns_write "<li>$sequence_def;<p>\n"
}

ns_write "</ul>

<h3>Triggers</h3>

<ul>
"

db_foreach get_all_triggers "select trigger_name, trigger_type, triggering_event, table_name, when_clause, description
from user_triggers
order by trigger_name" {

    # get trigger_body, which is a long
    set trigger_body [db_exec_plsql get_trigger_body "begin :1 := sdb_get_trigger_body(:trigger_name); end;"]

    ns_write "<li><font color=009900>$trigger_name</font>
    <p>
    Trigger Type: $trigger_type<br>
    Triggering Event: $triggering_event<br>
    Table Name: $table_name<br>
    When Clause:<blockquote><pre>$when_clause</pre></blockquote>
    Description:<blockquote><pre>$description</pre></blockquote>
    Trigger Body:<blockquote><pre>$trigger_body</pre></blockquote>

    <p>
    "
}

ns_write "</ul>
<h3>PL/SQL</h3>
<ul>
"

db_foreach get_all_plsql_objects "select name, type from user_source group by name, type order by name" {
    ns_write "<li><font color=009900>$name</font> ($type)
    <blockquote><pre>
    "

    db_foreach get_lines "select text from user_source where name=:name and type=:type order by line" {
        # text appears to always end with a newline, so we don't have to add one
        ns_write "$text"
    }

    ns_write "</pre></blockquote>"
}

ns_write "</ul>
[ad_admin_footer]
"