I get asked on a regular basis as to how to rename a table. Unfortunately, today, we don’t have the alter table command. It is fortcoming later this year but for the meantime, I’ve written this simple script to do this and this appears to do the very same thing as alter table <table> rename to <new table> but outside the database.
The assumptions are -
1. you are running Infobright on Linux
2. you installed in the default locations, if not change the DATADIR variable to the correct location
3. you have table names without special characters that would throw the script off
#!/bin/bash
###########################################################
### Infobright 2009 ###
### Developed by: Client Services ###
### Authors: Carl Gelbart ###
### Version 0.1 ###
### ###
### FOR TESTING AND EVALUATION PURPOSES ONLY. NO ###
### WARRANTY IS OFFERED AND NO LIABILITY IS ACCEPTED ###
### FOR INAPPROPRIATE USE OF THIS SOFTWARE. ###
###########################################################
# Usage: rename_table <database name> <table_name> <new_table_name>
#
# rename_table will do the same as the alter SQL statement
# alter table <table name> rename to <new table name>
#
if [ $# != 3 ]; then
echo; echo "syntax: rename_table "
echo " <database name> "
echo " <table name> "
echo " <new table name> "
echo
exit 2
fi
DATADIR=/usr/local/infobright/data
DBNAME=$1
TABLE=$2
NEW=$3
if [ -f $DATADIR/$DBNAME/$TABLE.frm ]
then
mv $DATADIR/$DBNAME/$TABLE.bht $DATADIR/$DBNAME/$NEW.bht
mv $DATADIR/$DBNAME/$TABLE.frm $DATADIR/$DBNAME/$NEW.frm
sync
echo table renamed
else
echo table not found, insert quarter and try again
exit 28
fi
exit
