create_sql

#!/bin/bash
################################################################################
# Licensed to the OpenAirInterface (OAI) Software Alliance under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The OpenAirInterface Software Alliance licenses this file to You under
# the Apache License, Version 2.0  (the "License"); you may not use this file
# except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#-------------------------------------------------------------------------------
# For more information about the OpenAirInterface (OAI) Software Alliance:
#      contact@openairinterface.org
################################################################################
# file create_sql
# brief
# author Anta Huang
# company Eurecom
# email: anta.huang@eurecom.fr
#
THIS_SCRIPT_PATH=$(dirname $(readlink -f $0))
source $THIS_SCRIPT_PATH/../build/tools/build_helper
source sql_schema
set -e
PLMN="20893"
APN_T="oai.ipv4"
K="8baf473f2f8fd09487cccbd7097c6862"
HOST=""
USER=""
PASSWORD=""
DB=""
IMSI_RANGE=""
MORE_MMEIDENTITY=""
IMSI_START="1"
IMSI_END="30"
FILE=""
OUTPUT=0
credentialsFile=./mysql-credentials.cnf

function print_help(){
    echo_info "
     SYNOPSIS
        ./create_sql [-h [host]] [-u [mysql_user]] [-p [mysql_password]] [-d [database_name]]
                     [--plmn [PLMN]] [--imsi [imsi_range]] [-m [more_mmeidentity]] [-o [filename]]

     DESCRIPTION
        Script to insert designated OAI HSS info in SQL

     REQUIRED
        -h [HOST], --host [HOST]                         Set MySQL host
        -u [USER], --user [USER]                         Set MySQL username
        -p [PASSWORD], --password [PASSWORD]             Set MySQL password
        -d [DB], --database [DB]                         Set MySQL database name

     OPTIONAL
        -m, --mmeidentity                                Set the extra mmeidentity
        --plmn [PLMN]                                    Set the PLMN. (default=20893)
        --imsi [IMSI_START]-[IMSI_END]                   Set the IMSI range. (default=1-30)
        --apn [APN]                                      Set the APN string. (default=oai.ipv4)
        -k [UE security key], --key [UE security key]    Set the K value. (default=8baf473f2f8fd09487cccbd7097c6862)
        -o [FILE], --output [file]                       Set output filename
        --help                                           Print this help

     EXAMPLES
        ./create_sql -h 127.0.0.1 -u hssadmin -p linux -d oai_db --plmn 20894 --imsi 1-30 -o oai_db.sql -m host1.openair4G.eur,host2.openair4G.eur
        "

}

function mysql_setup(){
    echo "[client]" > $credentialsFile
    echo "user=$2" >> $credentialsFile
    echo "password=$3" >> $credentialsFile
    echo "host=$1" >> $credentialsFile
}

function create_hss_data(){
    local database_name=$1

     Q_PRE="SET @@global.sql_mode= '';"
     echo_info "Setting SQL mode"
     mysql --defaults-extra-file=$credentialsFile -e "${Q_PRE}"

    #Drop the database in any case
    Q0="DROP DATABASE IF EXISTS $database_name;"
    echo_warning "Dropping database $database_name"
    mysql --defaults-extra-file=$credentialsFile -e "${Q0}"

    # Create the database
    Q1="CREATE DATABASE IF NOT EXISTS $database_name;"
    mysql --defaults-extra-file=$credentialsFile -e "${Q1}"
    if [ $? -ne 0 ]; then
        echo_error "SQL: $database_name creation failed"
        return 1
    else
        echo_success "SQL: $database_name creation succeeded"
    fi

    # apn table
    mysql --defaults-extra-file=$credentialsFile $database_name -e "$APN"
    if [ $? -ne 0 ]; then
        echo_error "SQL: APN table creation failed"
        return 1
    else
        echo_success "SQL: APN table creation succeeded"
    fi

    # mmeidentity table
    mysql --defaults-extra-file=$credentialsFile $database_name -e "$MMEIDENTITY"
    if [ $? -ne 0 ]; then
        echo_error "SQL: MMEIDENTITY table creation failed"
        return 1
    else
        echo_success "SQL: MMEIDENTITY table creation succeeded"
    fi

    # pdn table
    mysql --defaults-extra-file=$credentialsFile $database_name -e "$PDN"
    if [ $? -ne 0 ]; then
        echo_error "SQL: PDN table creation failed"
        return 1
    else
        echo_success "SQL: PDN table creation succeeded"
    fi

    # pgw table
    mysql --defaults-extra-file=$credentialsFile $database_name -e "$PGW"
    if [ $? -ne 0 ]; then
        echo_error "SQL: PGW table creation failed"
        return 1
    else
        echo_success "SQL: PGW table creation succeeded"
    fi

    # terminal-info table
    mysql --defaults-extra-file=$credentialsFile $database_name -e "$TERMINAL_INFO"
    if [ $? -ne 0 ]; then
        echo_error "SQL: TERMINAL_INFO table creation failed"
        return 1
    else
        echo_success "SQL: TERMINAL_INFO table creation succeeded"
    fi

    # users table
    mysql --defaults-extra-file=$credentialsFile $database_name -e "$USERS"
    if [ $? -ne 0 ]; then
        echo_error "SQL: USERS table creation failed"
        return 1
    else
        echo_success "SQL: USERS table creation succeeded"
    fi

    # filling data
    # default mme data in OAI HSS
    OLDIFS=$IFS
    mmeidentity_data="INSERT INTO \`mmeidentity\` VALUES (2,'mme2.openair4G.eur','openair4G.eur',0),(1,'nano.openair4G.eur','openair4G.eur',0),(5,'abeille.openair4G.eur','openair4G.eur',0),(4,'yang.openair4G.eur','openair4G.eur',0),(3,'mme3.openair4G.eur','openair4G.eur',0),(6,'calisson.openair4G.eur','openair4G.eur',0)"
    if [ -z $MORE_MMEIDENTITY ]; then
        mmeidentity_data=$mmeidentity_data";"
        mysql --defaults-extra-file=$credentialsFile $database_name -e "$mmeidentity_data"
    else
        set -- "$MORE_MMEIDENTITY"
        IFS=","; more_mme=($*)
        IFS=$OLDIFS
        counter=7
        for i in "${more_mme[@]}"
        do
            mmeidentity_data=$mmeidentity_data",($counter,'$i','openair4G.eur',0)"
            ((counter++))
        done
        mmeidentity_data=$mmeidentity_data";"
        mysql --defaults-extra-file=$credentialsFile $database_name -e "$mmeidentity_data"
    fi
    if [ $? -ne 0 ]; then
        echo_error "SQL: MMEIDENTITY data creation failed"
        return 1
    else
        echo_success "SQL: MMEIDENTITY data creation succeeded"
    fi


    # imsi data

    # parse the range
    OLDIFS=$IFS
    set -- "$IMSI_RANGE"
    IFS="-"; range=($*)
    IFS=$OLDIFS
    if [ -z ${range[0]} ] | [ -z ${range[1]} ]; then
        echo_error "Invalid IMSI range"
        return 1
    fi
    IMSI_START=${range[0]}
    IMSI_END=${range[1]}
    PLMN_DIGITS=${#PLMN}
    IMSI_DIGITS=`expr 15 - $PLMN_DIGITS`
    echo_info "Creating IMSI info from $PLMN`printf "%0${IMSI_DIGITS}g" $IMSI_START` to $PLMN`printf "%0${IMSI_DIGITS}g" $IMSI_END`"

    #prepare and insert into pdn/users table
    pdn_data="INSERT INTO \`pdn\` VALUES "
    users_data="INSERT INTO \`users\` VALUES "
    counter=0
    last_index=0
    for i in $(seq -f "%0${IMSI_DIGITS}g" $IMSI_START $IMSI_END)
    do
        if [ $(( $counter % 500 )) -eq 0 ] && [ $counter -ne 0 ]; then
          pdn_data=$pdn_data";"
          users_data=$users_data";"
          mysql --defaults-extra-file=$credentialsFile $database_name -e "$pdn_data"
          if [ $? -ne 0 ]; then
            echo_error "SQL: PDN data #$last_index to #$counter creation failed"
            return 1
          else
            echo_success "SQL: PDN data #$last_index to #$counter creation succeeded"
          fi
          mysql --defaults-extra-file=$credentialsFile $database_name -e "$users_data"
          if [ $? -ne 0 ]; then
            echo_error "SQL: USERS data #$last_index to #$counter creation failed"
            return 1
          else
            echo_success "SQL: USERS data #$last_index to #$counter creation succeeded"
          fi
          last_index=$(( $counter+1 ))
          pdn_data="INSERT INTO \`pdn\` VALUES "
          users_data="INSERT INTO \`users\` VALUES "
        elif [ ! $counter -eq 0 ]; then
          pdn_data=$pdn_data","
          users_data=$users_data","
        fi
        pdn_data=$pdn_data"(NULL,'$APN_T','IPv4','0.0.0.0','0:0:0:0:0:0:0:0',50000000,100000000,3,'$PLMN$i',9,15,'DISABLED','ENABLED','LIPA-only')"
        users_data=$users_data"('$PLMN$i', NULL, '35609204079301', NULL, 'PURGED', '120', '50000000', '100000000', '60', NULL, '0', X'$K', '1', '0', '1', '', 1)"
        counter=$[counter + 1]
      done
      pdn_data=$pdn_data";"
      users_data=$users_data";"
      mysql --defaults-extra-file=$credentialsFile $database_name -e "$pdn_data"
      if [ $? -ne 0 ]; then
        echo_error "SQL: PDN data #$last_index to #$counter creation failed"
        return 1
      else
        echo_success "SQL: PDN data #$last_index to #$counter creation succeeded"
      fi
      mysql --defaults-extra-file=$credentialsFile $database_name -e "${users_data}"
      if [ $? -ne 0 ]; then
        echo_error "SQL: USERS data #$last_index to #$counter creation failed"
        return 1
      else
        echo_success "SQL: USERS data #$last_index to #$counter creation succeeded"
      fi

      #pgw data
      pgw_data="INSERT INTO \`pgw\` VALUES (1,'127.0.0.1','0:0:0:0:0:0:0:1'),(2,'192.168.56.101',''),(3,'10.0.0.2','0');"
      mysql --defaults-extra-file=$credentialsFile $database_name -e "$pgw_data"
      if [ $? -ne 0 ]; then
        echo_error "SQL: PGW data creation failed"
        return 1
      else
        echo_success "SQL: PGW data creation succeeded"
      fi

    }

    function main() {

    until [ -z "$1" ]
    do
      case "$1" in
        -h | --host)
          HOST=$2
          [[ -z $HOST ]] && echo_error "Invalid MySQL host" && print_help || echo "Host: $HOST"
          shift 2;;
        -u | --user)
          USER=$2
          [[ -z $USER ]] && echo_error "Invalid MySQL user" && print_help || echo "User: $USER"
          shift 2;;
        -p | --password)
          PASSWORD=$2
          [[ -z $PASSWORD ]] && echo_error "Invalid MySQL password" && print_help || echo "Password: $PASSWORD"
          shift 2;;
        -d | --db)
          DB=$2
          [[ -z $DB ]] && echo_error "Invalid MySQL database name" && print_help || echo "Database: $DB"
          shift 2;;
        --plmn)
          PLMN=$2
                echo_info "Setting PLMN to: $PLMN"
                shift 2;;
            --imsi)
                IMSI_RANGE=$2
                shift 2;;
            --apn)
                APN_T=$2
                echo_info "Setting APN to: $APN_T"
                shift 2;;
            -k | --key)
                K=$2
                echo_info "Setting K to: $K"
                shift 2;;
            -m | --mmeidentity)
                MORE_MMEIDENTITY=$2
                echo_info "Will add $MORE_MMEIDENTITY along with default"
                shift 2;;
            -o | --output)
                FILE=$2
                OUTPUT=1
                echo_info "Will output to ${FILE}"
                shift 2;;
            --help)
                print_help
                exit 1;;
            *)
                print_help
                exit 1;;
        esac
    done

    #Output credential to a file to suppress the MySQL warning
    mysql_setup $HOST $USER $PASSWORD

    #Insert Data into DB
    create_hss_data $DB

    #Output to *.sql if needed
    if [ $OUTPUT -eq 1 ]; then
        mysqldump --defaults-extra-file=$credentialsFile $DB > ${FILE}
    fi
}
main $@