blob: 81f85b36af65cbd30c8fd96218347789e86a52c1 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
#!/bin/sh
# (C) 2011 by Eugeniy Mikhailov, <evgmik@gmail.com>
# vim:set ft=tcl: \
exec tclsh "$0" "$@"
# #######################################################
# everywhere below db must be initialized with command
# sqlite3 db $dbfile
# #######################################################
proc getColListFromTable {table} {
set eval_str [concat SELECT * FROM \'$table\']
set err [catch {
db eval $eval_str v {
set all_column_names $v(*)
}
} errStat ]
if { $err } {
dbg "we should never be here if GradesTable exists" msg_level_critical
dbg $errStat msg_level_critical
htmlErrorMsg $errStat
}
return $all_column_names
}
proc colList2sqlColStr { col_list } {
set sqlStr {}
foreach col $col_list {
if {$sqlStr ne ""} {
set sqlStr $sqlStr,\"$col\"
} else {
set sqlStr \"$col\"
}
}
return $sqlStr
}
proc SelectColvalueFromTable { table column_of_interest col row_value } {
# select value of 'column_of_interest' from 'table' where 'col'='row_value'
set value {}
set eval_str "SELECT \"$column_of_interest\" FROM \'$table\' WHERE $col=:row_value"
set err [catch {
db eval $eval_str v {
set value $v($column_of_interest)
}
} errStat ]
if { $err } {
set msg_text "the following error happen in proc SelectColvalueFromTable while selecting from table $table $errStat"
htmlErrorMsg $msg_text
dbg $msg_text msg_level_critical
set $value {}
}
return $value
}
proc existsColumnWithRowvalueInTable { table column row_value } {
set sql_str [concat SELECT 1 FROM \'$table\' WHERE \"$column\"=\"$row_value\"]
if {![db exists $sql_str]} {
dbg "Column \'$column\' does not have row with value \'$row_value\' in table \'$table\'" msg_level_info
return false
} else {
return true
}
}
proc UpdateColumnWithValueInTableWhere { table column val where_column row_value } {
set eval_str [concat UPDATE \'$table\' SET \"$column\"='$val' WHERE \"$where_column\"=\'$row_value\']
set err [catch {db eval $eval_str } errStat]
if { $err } {
set msg_str "Unable to update column $column in the table $table where $where_column=$row_value. The following error happen: $errStat"
htmlErrorMsg $msg_str
dbg $msg_str msg_level_critical
}
}
proc doesColumnExists {col table} {
if { $col in [getColListFromTable $table] } {
return true;
} else {
return false;
}
}
proc DeleteColumnFromTable { table columnname } {
if { $columnname eq "" } {
htmlErrorMsg "empty column names are not permitted"
}
if { [doesColumnExists $columnname $table] } {
# removing the column name to be deleted from total list
set old_column_list [getColListFromTable $table]
set new_column_list [removeElementFromList $columnname $old_column_list]
set sql_new_column_str [colList2sqlColStr $new_column_list]
set eval_str "BEGIN TRANSACTION;
CREATE TEMPORARY TABLE \'${table}_backup\'($sql_new_column_str);
INSERT INTO \'${table}_backup\' SELECT $sql_new_column_str FROM \'${table}\';
DROP TABLE \'${table}\';
CREATE TABLE \'${table}\'($sql_new_column_str);
INSERT INTO \'${table}\' SELECT $sql_new_column_str FROM \'${table}_backup\';
DROP TABLE \'${table}_backup\';
COMMIT;"
set err [catch {db eval $eval_str } errStat]
if { $err } {
htmlErrorMsg $errStat
dbg "the following error happen: $errStat" msg_level_critical
}
} else {
htmlErrorMsg "No column $columnname in the table $table"
}
}
|
