blob: 268845e022073441977f6819664fc2a641ba0df7 (
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
#!/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"
}
}
set isDbTransactionActive false
proc begin_db_transaction { } {
global isDbTransactionActive
if { !$isDbTransactionActive } {
set isDbTransactionActive true
set eval_str [concat BEGIN TRANSACTION]
set err [catch {db eval $eval_str } errStat]
if { $err } {
htmlErrorMsg $errStat
dbg "the following error happen: $errStat" msg_level_critical
}
}
}
proc end_db_transaction { } {
global isDbTransactionActive
if { $isDbTransactionActive } {
set isDbTransactionActive false
set eval_str [concat END TRANSACTION]
set err [catch {db eval $eval_str } errStat]
if { $err } {
htmlErrorMsg $errStat
dbg "the following error happen: $errStat" msg_level_critical
}
}
}
|
