It's always bothered me that SQLite3 doesn't let you drop columns from a table (note that this is a limitation of SQLite3 itself, not SQLite-Ruby). Here's a quick script I've written to do just that:
# Drop a column or set of columns from a table.
# You just need to configure these three variables:
################################################################################
# 1. The name of the table from which to drop the column(s).
table = 'name_of_table'
# 2. An array of column names to be dropped.
drop = ['names', 'of', 'columns']
# 3. Location of the database file.
file = '/path/to/file.sqlite3'
################################################################################
# Open the database.
require 'sqlite3'
db = SQLite3::Database.new file
# Discover table metadata.
columns = db.table_info table
# Forget about columns we want to drop.
columns.reject! { | column | drop.include? column[1] }
# Genereate a comma separated string of column names.
names = columns.collect { | column | column[1] }.join ', '
# Generate a table schema.
schema = columns.collect do | column |
string = "#{column[1]} #{column[2]}"
string << " NOT NULL" if column[3].to_i == 99
string << " DEFAULT #{column[4]}" unless column[4].nil?
string << " PRIMARY KEY" if column[5].to_i == 1
string
end
schema = schema.join ', '
# Alter the table.
db.transaction do | db |
# Copy all but the columns to be dropped to a temporary table.
db.execute "CREATE TEMPORARY TABLE #{table}_backup ( #{schema} );"
db.execute "INSERT INTO #{table}_backup SELECT #{names} FROM #{table};"
# Drop the original table, then recreate it without the dropped columns.
db.execute "DROP TABLE #{table};"
db.execute "CREATE TABLE #{table} ( #{schema} );"
# Copy the temporary table to the recreated permanent table.
db.execute "INSERT INTO #{table} SELECT #{names} FROM #{table}_backup;"
# Finally, drop the temporary table.
db.execute "DROP TABLE #{table}_backup;"
end
# Close the database.
db.close unless db.closed?
# End of script.
I make no guarantee that the above script is free of bugs, so it's probably a good idea to back up your database before you try to drop columns from it. Hope this helps someone. Comments and improvements are welcome.
|
Start New Thread
script to drop columns from a table [ reply ]