Database backup

# This ruby script exctracts all information from a given database
# and builds insert statements.  Think if it as a flat file backup of
# the database.

require 'dbi'

tables = "table_to_backup1", "table_to_backup2"
data = Array.new

dbh = DBI.connect("DBI:Mysql:my_db", "user", "psswd" )

tables.each{ |table|
    # grab table creation information
    str = "SHOW CREATE table #{table};"
    sth = dbh.prepare( str )
    sth.execute
    data.push sth.fetch[1].gsub( /`/, "" ) + ";"

    # grab table content information
    str = "SELECT * FROM #{table};"
    sth = dbh.prepare( str )
    sth.execute
    # Step through each result, build insert statement
    while row=sth.fetch
        insert = "INSERT INTO #{table} VALUES("
        first = true
        row.each{ |val|
            if val.nil?
                insert << ", null"
            else
                val = "#{val}"
                val.gsub!( /"/, "\\\"" )
                first == true ? insert << "\"#{val}\"" : insert << ",\"#{val}\""
            end
            first = nil
        }
        insert << ");"
        data.push insert
    end
}
dbh.disconnect

# Print out table creation and insert statements
data.each_index{ |i|
    puts data[i]
}