blob: ff52f310a1d6a8d071fe2f9012bf8e7ab0a7ec68 (
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
#!/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 DeleteColumnFromTable
dbg "the following error happen: $errStat" msg_level_critical DeleteColumnFromTable
}
} else {
htmlErrorMsg "No column $columnname in the table $table" DeleteColumnFromTable
}
}
set beginTransactionCounter 0
proc begin_db_transaction { {do_not_begin {false}} } {
# do_not_begin intended for restoring transaction state asseses previously
global beginTransactionCounter
if { $do_not_begin } { return }
incr beginTransactionCounter
if { $beginTransactionCounter == 1 } {
# starting transaction
set eval_str [concat BEGIN TRANSACTION]
set err [catch {db eval $eval_str } errStat]
if { $err } {
htmlErrorMsg $errStat begin_db_transaction
dbg "the following error happen: $errStat" msg_level_critical begin_db_transaction
}
}
}
proc getTransactionState { } {
global beginTransactionCounter
if { $beginTransactionCounter >= 1 } {
return true
}
return false
}
proc end_db_transaction { {force_end {false}} } {
# force_end == true ends all transactions
global beginTransactionCounter
if { $force_end && ( $beginTransactionCounter >= 1) } {
set beginTransactionCounter 1
}
incr beginTransactionCounter -1
if { ($beginTransactionCounter == 0) } {
# closing transaction
set eval_str [concat END TRANSACTION]
set err [catch {db eval $eval_str } errStat]
if { $err } {
htmlErrorMsg $errStat end_db_transaction
dbg "the following error happen: $errStat" msg_level_critical begin_db_transaction
}
}
}
|
