table prefix bash script

2009-09-02
2013-05-28
  • Martin Weis
    Martin Weis
    2009-09-02

    Hello!

    I wrote a bash script to add table prefixes to the sql dumps, which I try to post here:

    #!/bin/bash
    # Script:   add_tableprefix.sh
    #####################################################################
    # Program : add_tableprefix.sh
    #
    # Version : 0.1
    # Date    : 09/2009
    # Author  : Martin Weis, Martin.Weis.newsadress @ gmx.de
    #
    # Inputs  : sql file with rebase table structure
    #
    # Notes   :
    #
    #
    #####################################################################

    # Global variables
    SCRIPTNAME=$(basename $0)

    EXIT_SUCCESS=0
    EXIT_FAILURE=1
    EXIT_ERROR=2
    EXIT_BUG=10

    # default values
    TABLEPREFIX="refbase__"

    function usage {
        echo "usage: $0 -i <sqlfile.sql> [-p <table_prefix>] [-o <output.sql>]" >&2
        echo "this script adds a table prefix to the sql install file" >&2
        echo "-i: input filename (e.g. install.sql)" >&2
        echo "-p: table prefix (defaults to refbase__)" >&2
        echo "-o: output filename (defaults to <sqlfile.sql>.prefixed.sql)" >&2
        echo "-v: verbose (debug)" >&2
        #echo "example: in root directory of refbase type: $0 install.sql" >&2
        [[ $# -eq 1 ]] && exit $1 || exit $EXIT_FAILURE
    }

    if [ -z $1 ]
    then
        usage
    fi

    while getopts ':o:i:p:v' OPTION ; do
            case $OPTION in
            i)        INFILE="$OPTARG"
                    ;;
            o)        OUTFILE="$OPTARG"
                    ;;
            p)        TABLEPREFIX="$OPTARG"
                    ;;
            h)        usage $EXIT_SUCCESS
                    ;;
            v)        VERBOSE="y"
                    ;;
            \?)        echo "unknown option \&quot;-$OPTARG\&quot;." >&2
                    usage $EXIT_ERROR
                    ;;
            :)        echo " \&quot;-$OPTARG\&quot;: argument missing" >&2
                    usage $EXIT_ERROR
                    ;;
            *)        echo "whoops, we should not reach here (it's a bug in $0)"
    >&2
                    usage $EXIT_BUG
                    ;;
            esac
    done
    shift $(( OPTIND - 1 ))

    # check arguments
    if [ -z $INFILE ]
    then
        echo "no input file given (use -i)" >&2
        usage $EXIT_ERROR
    fi
    if [ -z $OUTFILE ]
    then
        OUTFILE="${INFILE}.prefixed.sql"
        echo "no output file given, using $OUTFILE" >&2
    fi

    if (( $# > 0 )) ; then
            echo "additional arguments (discarded): $@" >&2
    fi

    # get the table names from sql dump
    TABLELIST=`awk '/DROP TABLE IF EXISTS/{print gensub("\`","","g",gensub(";","","g",$5))}' "$INFILE"`

    # create a sed commandline iterating over the tablenames
    for tablename in $TABLELIST
    do
    NEWNAME="$TABLEPREFIX${tablename}"
    if [[ $VERBOSE = y ]] ; then
        echo -e "$tablename \t->\t $NEWNAME" >&2
    fi
    SEDARGS="$SEDARGS -e 's/\`$tablename\`/\`$NEWNAME\`/g'"
    done

    # cp input file to output file and apply sed command
    cp "$INFILE" "$OUTFILE"
    CMD="sed -i $SEDARGS $OUTFILE"
    if [[ $VERBOSE = y ]] ; then
        echo $CMD
    fi

    eval $CMD
    echo "result written to $OUTFILE"

    exit $EXIT_SUCCESS

     
    • Martin Weis
      Martin Weis
      2009-09-02

      I forgot the license (as below), the script can be downloaded here:
      https://turbo.uni-hohenheim.de/other/add_tableprefix.sh

      # License : GPL2 or later version
      #     This program is free software; you can redistribute it and/or
      #     modify it under the terms of the GNU General Public License
      #     as published by the Free Software Foundation; either version 2
      #     of the License, or (at your option) any later version.
      #    
      #     This program is distributed in the hope that it will be useful,
      #     but WITHOUT ANY WARRANTY; without even the implied warranty of
      #     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
      #     GNU General Public License for more details.
      #    
      #     You should have received a copy of the GNU General Public License
      #     along with this program; if not, write to the Free Software
      #     Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
      #

       
    • Martin Weis
      Martin Weis
      2009-09-02

      OK, the script did not work for update.sql, now it does (hopefully):
      https://turbo.uni-hohenheim.de/other/add_tableprefix.sh