ad_library { Took these defs out of the /www/doc/schema-browser/index.tcl file. } namespace eval sb {} ad_proc sb::get_tables_list {} { Get all tables that belong to the current user. } { return [db_list schema_browser_index_get_tables { select pg_class.relname as table_name from pg_class, pg_user where pg_user.usename = session_user and pg_user.usesysid = pg_class.relowner and pg_class.relkind = 'r' order by relname }] } ad_proc sb::get_tables { selected_table_name } { Build an HTML table of all PG tables belonging to the current user. Each PG table name is returned as a hyperlink to a page which displays the table's structure. } { set n_columns 4 set return_string "" set tables [sb::get_tables_list] if {[llength $tables] == 0} { return {No tables found. Make sure the owner of the tables in the database matches the user-id used by the web server to connect to the database.} } set n_rows [expr {([llength $tables] - 1) / $n_columns + 1}] append return_string "
[string tolower $table_name] | " } else { set href [export_vars -base index {table_name}] append return_string [subst {[string tolower $table_name] | }] } } } append return_string "
" # get table comments if { [db_0or1row sb_get_table_comment { select d.description from pg_class c, pg_description d where c.relname = lower(:table_name) and d.objoid = c.oid and objsubid = 0}] } { append html "\n--[join [split $description "\n"] "\n-- "]" } append html "\nCREATE TABLE [string tolower $table_name] (" if { [db_0or1row sb_get_primary_key { select indkey as primary_key_array from pg_index i join (select oid from pg_class where relname = lower(:table_name)) c on (i.indrelid = c.oid) join pg_class index_class on (index_class.oid = i.indexrelid and i.indisprimary) join pg_am a on (index_class.relam = a.oid)}] } { set primary_key_columns [split $primary_key_array " "] } else { set primary_key_columns [list] } set column_list [list] set column_info_set [ns_set create] # DRB: This changes some PG internal types into SQL92 standard types for readability's # sake. db_foreach schema_browser_index_get_user_table_data " select a.attname as column_name, case when t.typlen = -1 and t.typname <> 'numeric' then a.atttypmod - 4 else NULL end as data_length, case when t.typname = 'numeric' then a.atttypmod::int4 & 65535 - 4 else NULL end as scale, case when t.typname = 'numeric' then (a.atttypmod::int4 >> 16) & 65535 else NULL end as precision, case t.typname when 'int4' then 'integer' when 'bpchar' then 'char' else t.typname end as data_type, d.description as column_comments, pg_get_expr(ad.adbin, ad.adrelid) as data_default, substr(lower(:table_name),1,15) || '_' || substr(lower(a.attname),1,15) as column_constraint_key, case a.attnotnull when true then 'NOT NULL' else '' end as nullable, a.attnum as column_number from (select oid from pg_class where relname=lower(:table_name)) c join pg_attribute a on (c.oid = a.attrelid and a.attnum > 0) join pg_type t on (a.atttypid = t.oid) left join pg_attrdef ad on (a.attrelid = ad.adrelid and a.attnum = ad.adnum) left join pg_description d on (c.oid = d.objoid and a.attnum = d.objsubid) order by a.attnum" -column_set column_info_set { lappend column_list [ns_set copy $column_info_set] } ns_set free $column_info_set # current_constraint_info -- a constraint_info_set for the constraint being processed in the loop below set check_constraint_set [ns_set create] db_foreach schema_browser_index_get_subselect { select conname as constraint_name, pg_get_constraintdef(c.oid) as constraint_source from pg_constraint r join (select oid from pg_class where relname = lower(:table_name)) c on (c.oid = r.conrelid) order by constraint_name } { ns_set put $check_constraint_set $constraint_name $constraint_source } # # write out the columns with associated constraints # set n_column 0 foreach column $column_list { if { $n_column > 0 } { append html "," } set column_comments [ns_set get $column "column_comments"] if {$column_comments ne ""} { set comment_list [split $column_comments "\n"] append html "\n\t--[join $comment_list "\n\t-- "]" } append html "\n" append html "\t[string tolower [ns_set get $column column_name]]\t [ns_set get $column data_type]" if { [ns_set get $column data_length] ne "" } { append html "([ns_set get $column data_length])" } if { [ns_set get $column precision] ne "" } { append html "([ns_set get $column precision], [ns_set get $column scale])" } if { [llength $primary_key_columns] == 1 && [lindex $primary_key_columns 0] == [ns_set get $column column_number] } { append html " PRIMARY KEY" } if { [ns_set get $column "data_default"] ne "" } { append html " DEFAULT [ad_text_to_html -- [ns_set get $column data_default]]" } if { [ns_set get $column "nullable"] ne "" } { append html " [ns_set get $column nullable]" } if { [info exists references([ns_set get $column column_name])] } { append html " $references([ns_set get $column column_name])" } if { [ns_set get $check_constraint_set [ns_set get $column column_constraint_key]] ne "" } { append html "\n\t\t\tCHECK [ns_set get $check_constraint_set [ns_set get $column column_constraint_key]]" ns_set delkey $check_constraint_set [ns_set get $column column_constraint_key] } incr n_column } # # write out the table-level constraints in the table_constraint_list # for { set i 0 } { $i < [ns_set size $check_constraint_set] } { incr i } { if { [ns_set value $check_constraint_set $i] ne "" } { append html ",\n " if { [string first "\$" [ns_set key $check_constraint_set $i]] == -1 } { append html "CONSTRAINT [ns_set key $check_constraint_set $i]\n " } append html "CHECK [ns_set value $check_constraint_set $i]" } } if { [llength $primary_key_columns] > 1 } { append html ",\n\tPRIMARY KEY (" append html [join [db_list sb_get_primary_key_select_2 [subst { select a.attname as column_name from (select oid from pg_class where relname = lower(:table_name)) c join pg_attribute a on (c.oid = a.attrelid) where a.attnum in ([join $primary_key_columns ","]) }]] ","] append html ")" } foreach complex_foreign_key $complex_foreign_keys { append html ",\n\t$complex_foreign_key" } append html "\n);" append html [sb::get_indexes $table_name] append html [sb::get_triggers $table_name] append html [sb::get_child_tables $table_name "t"] if {[string match "pg_*" $table_name]} { set table_size [sb::get_table_size -table_name $table_name -namespace "pg_catalog"] } else { set table_size [sb::get_table_size -table_name $table_name] } append html "\n\n-- Table size: [lc_numeric [lindex $table_size 0]] bytes\n" append html "-- Table rows: [lc_numeric [lindex $table_size 1]]\n" append html "" return $html } # # Local variables: # mode: tcl # tcl-indent-level: 4 # indent-tabs-mode: nil # End: