Insert a new column after the specified column in SQLite3

sqlite3_insert_column.tcl

package require sqlite3

proc format_sql {sql} {
	set sql [regsub -all -line -- {--.+$} $sql ""]
	set sql [string map [list "\n" ""] $sql]
	set sql [string map [list "," ",\n"] $sql]
	set sql [string map [list "(" "(\n"] $sql]
	set sql [string map [list ")" "\n)"] $sql]
	return $sql
}

proc get_column_names {db table} {
	set pragma [format {PRAGMA table_info('%s')} $table]
	set names [list]
	foreach {cid name type notnull dflt_value pk} [$db eval $pragma] {
		lappend names $name
	}
	return $names
}

proc insert_column {db table pre_column new_column_name new_column_def} {
	set old_columns [get_column_names $db $table]
	set old_columns_csv [join $old_columns ,]
	
	set end_column_name [lindex $old_columns end]
	
	if {$new_column_name eq $end_column_name} {
		$db eval "ALTER TABLE ADD \"$new_column_name\" $new_column_def"
	} else {
		set sql [$db onecolumn {SELECT sql FROM sqlite_master WHERE name = $table}]
		set sql [format_sql $sql]
		
		set old_lines [split $sql \n]
		set pre_line_idx [lsearch $old_lines "*${pre_column}*"]
		set new_line [format {"%s" %s,} $new_column_name $new_column_def]
		set new_lines [linsert $old_lines $pre_line_idx+1 $new_line]
		set create_sql [join $new_lines \n]
		
		set unq [clock seconds]
		
		set old "${table}"
		set tmp "${table}_temp_$unq"
		
		$db eval "ALTER TABLE $old RENAME TO $tmp"
		$db eval $create_sql
		
		$db eval "INSERT INTO $old ($old_columns_csv) SELECT $old_columns_csv FROM $tmp"
		$db eval "DROP TABLE $tmp"
	}
}

proc cleanup {db} {
	$db eval {VACUUM}
	$db eval {REINDEX}
}

if {[info exists ::argv0] && $::argv0 eq [info script]} {
	set dbfile "app.db"
	sqlite3 db $dbfile
	db transaction {
		insert_column db "users" "name" "nickname" "TEXT DEFAULT NULL"
	}
	cleanup db
	db close
	exit
}