sqlitetablelist.mht

TCL的数据库处理支撑库及一些示例

 iwidgets::combobox .sqlframe.entry -width 75 -editable true -unique =
true -labeltext "SQL Query"  -labelpos w -textvariable sqlquery =
-selectioncommand {
    #puts "selected: [.sqlframe.entry getcurselection]"
    set sqlquery [.sqlframe.entry getcurselection]
    #.sqlframe.entry insert list end $sqlquery
    set f [open ${database_name}_queries.tcl w]
    for {set x 0} {$x < [.sqlframe.entry index end]} {incr x} {
        puts $f [.sqlframe.entry get $x]
    }
    close $f
 }

 lappend query_list ""
 if {[file isfile ${database_name}_queries.tcl]} {
    set f [open ${database_name}_queries.tcl r]
    while {![eof $f]} {
        gets $f line
        if {$line !=3D {}} {
            lappend query_list "$line"
        }
    }
    close $f
 }
 #.sqlframe.entry insert list end {SELECT * FROM SpokanePhysicians;}
 foreach query $query_list {
    .sqlframe.entry insert list end $query
 }

 #.sqlframe.entry selection set {}

 pack .sqlframe.entry -side left -expand true -fill both

 button .sqlframe.go_button -text "Go" -command {
    catch {
        destroy .result_text
        destroy .result_scroll
        destroy .result_scroll2
        destroy .query_results
        destroy .hsb
        destroy .vsb
        destroy .pw
    }
    iwidgets::panedwindow .pw -width 6i -height 2.5i -orient vertical
    pack .pw -expand true -fill both

    .pw add "left" -margin 2

    .pw add "right" -margin 2

    set left [.pw childsite "left"]
    set right [.pw childsite "right"]

    set no_columns 1
    set column_names ""

    set result [db eval "$sqlquery"]

    regexp {SELECT.+FROM} $sqlquery no_columns

    if {![regexp {\*} $no_columns]} {
        regsub -all {SELECT } $no_columns {} no_columns
        regsub -all { FROM} $no_columns {} no_columns
        #set no_columns [split $no_columns ", "]
        regsub -all {, } $no_columns { } no_columns
        set column_names [split $no_columns " "]
        set no_columns [llength [split $no_columns " "]]
        #puts $result
        if {$report_type =3D=3D "text" || $report_type =3D=3D "both"} {
            if {$report_type =3D=3D "text"} {
                .pw fraction 100 0
            } else {
                .pw fraction 50 50
            }
            scrollbar .result_scroll2 -orient horizontal -command =
{.result_text xview}
            pack .result_scroll2 -in $left -expand true -fill x
            scrollbar .result_scroll -command {.result_text yview}
            pack .result_scroll -in $left -side left -anchor w -padx 0 =
-expand true -fill y

            text .result_text -width 125 -yscrollcommand {.result_scroll =
set} -wrap none
            pack .result_text -in $left -side left -anchor w -padx 0 =
-expand true -fill both

        }
        set initial_result_length [llength $result]

        for {set x $no_columns} {$x <=3D [llength $result]} {incr x =
[expr $no_columns + 1]} {
            set result [linsert $result $x  \n]
        }

        #for {set x 1} {$x <=3D [llength $result]} {incr x 1} {
        #    if {[expr $x % ($no_columns + 1)] eq 0} {
        #    set result [linsert $result $x  \n]
        #    } else {
        #    set result [linsert $result $x "\t"]
        #    }
        #}

        regsub -all {\{\n\}} $result "\n" result
        if {$report_type =3D=3D "text" || $report_type =3D=3D "both"} {
            .result_text insert end "$column_names \n"
            .result_text insert end $result
        }
        set new_column_names [list "0 [join $column_names "\n0 "]"]
        regsub -all {\{} $new_column_names {} new_column_names
        regsub -all {\}} $new_column_names {} new_column_names
        if {$report_type =3D=3D "tablelist" || $report_type =3D=3D =
"both"} {
            if {$report_type =3D=3D "tablelist"} {
                .pw fraction 0 100
            } else {
                .pw fraction 50 50
            }

            tablelist::tablelist .query_results -columns =
$new_column_names \
                    -labelcommand tablelist::sortByColumn -sortcommand =
demo::compareAsSet \
                    -editendcommand applyValue -height 15 -width 120 =
-stretch all \
                    -xscrollcommand [list .hsb set] -yscrollcommand =
[list .vsb set] \
                    -stripebackground           #e0e8f0
            for {set x 0} {$x < [llength $column_names]} {incr x} {
                .query_results columnconfigure $x -maxwidth 30 -editable =
no
            }
            scrollbar .vsb -orient vertical   -command [list =
.query_results yview]
            scrollbar .hsb -orient horizontal -command [list =
.query_results xview]
            #grid .query_results -row 0 -column 0 -sticky news
            #grid .vsb -row 0 -column 1 -sticky ns
            #grid .hsb -row 1 -column 0 -sticky ew

            #grid rowconfigure    $tf 0 -weight 1
            #grid columnconfigure $tf 0 -weight 1
            pack .hsb -in $right -expand true -fill x
            pack .vsb -in $right -side left -fill y
            pack .query_results -in $right -side left
            foreach line [split $result "\n"] {
                regsub -all {'} $line {\\u0027} line
                regsub -all {"} $line {\\u0022} line
                #.query_results insert end [string map {' \'} $line]
                .query_results insert end $line
            }
        }

    } else {
        #toplevel .message
        #label .message.label -text "Sorry, cannot process the wildcard =
yet"
        #pack .message.label
        #label .message.label2 -text "column names"
        #pack .message.label2
        if {$report_type =3D=3D "text" || $report_type =3D=3D "both"} {
            if {$report_type =3D=3D "text"} {
                .pw fraction 100 0
            } else {
                .pw fraction 50 50
            }

            scrollbar .result_scroll2 -orient horizontal -command =
{.result_text xview}
            pack .result_scroll2 -in $left -expand true -fill x

            scrollbar .result_scroll -command {.result_text yview}
            pack .result_scroll -in $left -side left -anchor w -padx 0 =
-expand true -fill y

            text .result_text -width 125 -xscrollcommand =
{.result_scroll2 set} -yscrollcommand {.result_scroll set} -wrap none
            pack .result_text -in $left -side left -anchor w -padx 0 =
-expand true -fill both

        }

        #need to get the table_name in order to find the column names =
when using a wildcard
        if {[regexp "WHERE" $sqlquery]} {
            regexp {FROM .+ WHERE} $sqlquery table_name
            regsub {FROM } $table_name {} table_name
            regsub { WHERE} $table_name {} table_name
        } else {
            regexp {FROM [^ ;]+[ ;]} $sqlquery table_name
            regsub {FROM } $table_name {} table_name
            set table_name [string trimright $table_name]
            set table_name [string trimright $table_name ";"]
        }
        #.message.label configure -text "$table_name"
        # need to get the names of all the columns in the selected table =
using SQL command on the  sqlite_master table
        set initial_column_names [db eval [subst {SELECT sql FROM =
(SELECT * FROM sqlite_master UNION  ALL SELECT * FROM =
sqlite_temp_master) WHERE tbl_name LIKE '$table_name' AND type!=3D'meta' =
ORDER BY  type DESC, name;}]]
        puts "initial_column_names =3D=3D\n$initial_column_names"

        if {[regexp "CREATE TABLE" $initial_column_names]} {
            # get rid of some junk in the reply that we don't want
            regsub "CREATE TABLE $table_name" $initial_column_names {} =
initial_column_names
            #regsub   =
{((version\)|\(name),|(KE|PRIMAR)Y)|(version\)|\(name,|(KE|PRIMAR)Y)|(\(s=
ignature\)|UNIQUE)|(\(signature\),|UNIQUE)}  $initial_column_names {} =
initial_column_names
            regsub {PRIMARY KEY \((.+, .+)+\),} $initial_column_names {} =
initial_column_names
            regsub {UNIQUE \(.+\)} $initial_column_names {} =
initial_column_names
            regsub {PRIMARY KEY \((.+, .+)+\)} $initial_column_names {} =
initial_column_names
            puts "initial_column_names =3D=3D\n$initial_column_names"

            regsub -all {\(} $initial_column_names {} =
initial_column_names
            regsub -all {\)} $initial_column_names {} =
initial_column_names
            regsub -all {\{} $initial_column_names {} =
initial_column_names
            regsub -all {\}} $initial_column_names {} =
initial_column_names
            puts "initial_column_names =3D=3D\n$initial_column_names"

            # the reply still contains the column name followed by a =
comma and the type description
            # so we need to make a new list with only the first element =
- the name without the type  description
            set key_index_counter 0
            foreach name [split $initial_column_names ","] {
                if {[regexp "PRIMARY KEY" $name]} {
                    set primary_key $key_index_counter
                    set primary_key_name [lindex $name 0]
                }
                if {[lindex $name 0] !=3D "" && [lindex $name 0] =
!=3D "CREATE"} {
                    lappend column_names [lindex $name 0]
                }
                incr key_index_counter
            }
        }

        if {[regexp "CREATE VIEW" $initial_column_names] && =
![regexp "\\*" $initial_column_names]} {
            # we need to get the names of the columns you want from =
between the SELECT and the FROM statements
            regexp "SELECT .+ FROM" $initial_column_names match
            puts "match =3D=3D $match"
            regsub "SELECT " $match {} match
            regsub " FROM" $match {} match
            regsub -all {, } $match { } match
            puts "match =3D=3D $match"

            # in this case, the initial_column_names is actually the =
table names - I know that is  confusing - just too lazy to change the =
code
            regsub "CREATE VIEW $table_name AS SELECT .+ FROM " =
$initial_column_names {} initial_column_names
            regsub { WHERE.+$} $initial_column_names {} =
initial_column_names
            set initial_column_names [split $initial_column_names ", "]
            regsub -all {\(} $initial_column_names {} =
initial_column_names
            regsub -all {\)} $initial_column_names {} =
initial_column_names
            regsub -all {\{} $initial_column_names {} =
initial_column_names
            regsub -all {\}} $initial_column_names {} =
initial_column_names
            regsub -all {\\} $initial_column_names {} =
initial_column_names

            set column_names $match

        }

        # this is the case where you use a wildcard for selecting the =
columnames when creating a view. So you will get all the column names in =
the tablelist widget.
        if {[regexp "CREATE VIEW" $initial_column_names] && =
[regexp "\\*" $initial_column_names]} {
            regsub "CREATE VIEW $table_name AS SELECT \\* FROM " =
$initial_column_names {} initial_column_names
            regsub { WHERE.+$} $initial_column_names {} =
initial_column_names
            set initial_column_names [split $initial_column_names ", "]
            regsub -all {\(} $initial_column_names {} =
initial_column_names
            regsub -all {\)} $initial_column_names {} =
initial_column_names
            regsub -all {\{} $initial_column_names {} =
initial_column_names
            regsub -all {\}} $initial_column_names {} =
initial_column_names
            regsub -all {\\} $initial_column_names {} =
initial_column_names
            puts $initial_column_names

            foreach view_table $initial_column_names {
                set initial_column_names2 [db eval [subst {SELECT sql =
FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM =
sqlite_temp_master) WHERE tbl_name LIKE '$view_table' AND type!=3D'meta' =
ORDER BY type DESC, name;}]]
                regsub "CREATE TABLE $view_table" $initial_column_names2 =
{} initial_column_names2
                regsub -all {\(} $initial_column_names2 {} =
initial_column_names2
                regsub -all {\)} $initial_column_names2 {} =
initial_column_names2
                regsub -all {\{} $initial_column_names2 {} =
initial_column_names2
                regsub -all {\}} $initial_column_names2 {} =
initial_column_names2

                # the reply still contains the column name followed by a =
comma and the type description
                # so we need to make a new list with only the first =
element - the name without the type description
                set key_index_counter 0
                foreach name [split $initial_column_names2 ","] {
                    if {[regexp "PRIMARY KEY" $name]} {
                        set primary_key $key_index_counter
                        set primary_key_name [lindex $name 0]
                    }

                    lappend column_names [lindex $name 0]

                    incr key_index_counter
                }