Cacher is the code snippet organizer for pro developers

package require sqlite3

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

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 drop_columns {db table drop_columns} {
	set orig_columns [get_column_names $db $table]
	set keep_columns [list]
	foreach column $orig_columns {
		if {$column ni $drop_columns} {
			lappend keep_columns $column
		}
	}
	set keep_columns_csv [join $keep_columns ,]
	
	set sql [$db onecolumn {SELECT sql FROM sqlite_master WHERE name = $table}]
	set sql [format_sql $sql]
	
	set keep_lines [split $sql \n]
	foreach drop_column $drop_columns {
		set keep_lines [lsearch -all -inline -not $keep_lines "*${drop_column}*"]
	}
	set create_sql [join $keep_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 ($keep_columns_csv) SELECT $keep_columns_csv FROM $tmp"
	$db eval "DROP TABLE $tmp"
}

proc insert_columns {db table pre_column args} {
	set old_columns [get_column_names $db $table]
	set old_columns_csv [join $old_columns ,]
	
	set end_column_name [lindex $old_columns end]
	
	lassign $args new_column_name new_column_def
	
	if {$new_column_name eq $end_column_name} {
		foreach {new_column_name new_column_def} $args {
			$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_lines $old_lines
		foreach {new_column_name new_column_def} $args {
			incr pre_line_idx
			set new_line [format {"%s" %s,} $new_column_name $new_column_def]
			set new_lines [linsert $new_lines $pre_line_idx $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 rename_column {db table old_column new_column} {
	set old_columns [get_column_names $db $table]
	set idx [lsearch $old_columns $old_column]
	set new_columns [lreplace $old_columns $idx $idx $new_column]
	
	set old_columns_csv [join $old_columns ,]
	set new_columns_csv [join $new_columns ,]
	
	set sql [$db onecolumn {SELECT sql FROM sqlite_master WHERE name = $table}]
	set sql [format_sql $sql]
	
	set old_lines [split $sql \n]
	set new_lines [list]
	foreach line $old_lines {
		lappend new_lines [string map [list $old_column $new_column] $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 ($new_columns_csv) SELECT $old_columns_csv FROM $tmp"
	$db eval "DROP TABLE $tmp"
}

if {[info exists ::argv0] && $::argv0 eq [info script]} {
	set dbfile "app.db"
	sqlite3 db $dbfile
	db transaction {
		db eval {
			CREATE TABLE users (
				id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
				name TEXT DEFAULT NULL,
				age INTEGER DEFAULT NULL,
				phone TEXT DEFAULT NULL,
				temp INTEGER DEFAULT 0,
				email TEXT DEFAULT NULL
			);
			CREATE INDEX "users_idx" ON "users" ("name","age","temp");
		}
		drop_columns db "users" "temp"
		rename_column db "users" "name" "user_name"
		insert_columns db "users" "user_name" \
			"nickname" "TEXT DEFAULT NULL" \
			"weight" "REAL DEFAULT NULL"
		db eval {
			CREATE INDEX "users_idx" ON "users" ("user_name","age");
		}
	}
	cleanup db
	db close
	exit
}