Do several things SQLite3 cannot do for table columns
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
}