#!/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 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
		} 
	}
}