1#!/bin/sh 2# 3# Script for adding and dropping Kamailio Oracle tables 4# 5# History: 6 7##In you not have 'AS SYSDBA' access to database connect, comment next string 8##and Oracle administrator must create DBROOTUSER 9##(see scripts/oracle/admin/_create_as_sys.tmpl) 10DBSYSUSER="sys" 11 12 13# path to the database schemas 14DATA_DIR="/usr/local/share/kamailio" 15if [ -d "$DATA_DIR/oracle" ] ; then 16 DB_SCHEMA="$DATA_DIR/oracle" 17else 18 DB_SCHEMA="./oracle" 19fi 20 21 22##### ----------------------------------------------- ##### 23### load ORACLE SQL base 24# 25if [ -f "$MYLIBDIR/kamdbfunc.oracle" ]; then 26 . "$MYLIBDIR/kamdbfunc.oracle" 27else 28 echo "Cannot load ORACLE core functions '$MYLIBDIR/kamdbfunc.oracle' - exiting ..." 29 exit -1 30fi 31 32if [ -z "$SQLPLUS" ] ; then 33 SQLPLUS=`which sqlplus 2>/dev/null` 34 if [ -z "$SQLPLUS" ]; then 35 merr "'sqlplus' tool not found: set SQLPLUS variable to correct tool path" 36 exit 1 37 fi 38 export SQLPLUS 39fi 40if [ -z "$KAMAILIO_ORASEL" ] ; then 41 KAMAILIO_ORASEL=`which kamailio_orasel 2>/dev/null` 42 if [ -n "$SQLPLUS" ]; then 43 export KAMAILIO_ORASEL 44 fi 45fi 46 47################################################################# 48ORALOG=/tmp/opensrdbctl.log 49 50if [ -z "$SED" ]; then 51 SED="sed" 52fi 53 54SED_ROOTUSER="$SED -e s/%DBROOTUSER%/$DBROOTUSER/g" 55SED_USERS="$SED_ROOTUSER;s/%DBROUSER%/$DBROUSER/g;s/%DBRWUSER%/$DBRWUSER/g" 56 57################################################################# 58# config vars 59################################################################# 60 61# 'db'-privileges (scheme) Oracle user 62if [ -z "$DBROOTUSER" ]; then 63 merr "scheme owner (pivileged user) 'DBROOTUSER' must be defined." 64 exit 1 65fi 66 67################################################################# 68oracle_root_cmd() 69{ 70 prompt_oracle_pw root 71 SUFF="" 72 if [ -n "$1" ]; then 73 SUFF="@$1" 74 fi 75 ORACLE_ROOT_CMD="$SQLPLUS -S -L -R 3 $DBROOTUSER/${DBROOTPW}$SUFF" 76 export ORACLE_ROOT_CMD 77} 78 79 80kamailio_drop() # pars: <database name> 81{ 82if [ $# -ne 1 ] ; then 83 merr "kamailio_drop function takes one params" 84 exit 1 85fi 86 87oracle_root_cmd $1 88 89echo "DROP USER $DBROUSER CASCADE; 90 DROP USER $DBRWUSER CASCADE;" | $ORACLE_ROOT_CMD >$ORALOG 91if [ $? -ne 0 ] || check_oracle_log ; then 92 mwarn "Could not drop $DBRWUSER or $DBROUSER users, try to continue.." 93else 94 minfo "Database user deleted" 95fi 96 97$SED_ROOTUSER $DB_SCHEMA/inc/_dropsch.tmpl | $ORACLE_ROOT_CMD >$ORALOG 98if [ $? -ne 0 ] || check_oracle_log ; then 99 merr "Dropping scheme for '$DBROOTUSER' in database '$1' failed!" 100 exit 1 101fi 102 103if [ -n "$DBSYSUSER" ]; then 104 get_answer "ask" "Remove user '$DBROOTUSER' (complete remove scheme)? (y/n): " 105 if [ "$ANSWER" = "y" ]; then 106 prompt_oracle_pw sys 107 SUFF="" 108 if [ -n "$1" ]; then 109 SUFF="@$1" 110 fi 111 SYSCMD="$SQLPLUS -S -L -R 3 $DBSYSUSER/${DBSYSPW}$SUFF AS SYSDBA" 112 echo "DROP USER $DBROOTUSER CASCADE;" | $SYSCMD >$ORALOG 113 if [ $? -ne 0 ] || check_oracle_log ; then 114 merr "Dropping scheme in database '$1' failed!" 115 exit 1 116 fi 117 fi 118fi 119 120minfo "Scheme '$DBROOTUSER' in database '$1' dropped" 121} #kamailio_drop 122 123 124kamailio_create() # pars: <database name> 125{ 126if [ $# -ne 1 ] ; then 127 merr "kamailio_create function takes one param" 128 exit 1 129fi 130 131minfo "creating scheme for '$DBROOTUSER' in database '$1' ..." 132 133if [ -n "$DBSYSUSER" ]; then 134 get_answer "ask" "Create user '$DBROOTUSER' (is new scheme)? (y/n): " 135 if [ "$ANSWER" = "y" ]; then 136 prompt_oracle_pw sys 137 prompt_oracle_pw root 138 SUFF="" 139 if [ -n "$1" ]; then 140 SUFF="@$1" 141 fi 142 SYSCMD="$SQLPLUS -S -L -R 3 $DBSYSUSER/${DBSYSPW}$SUFF AS SYSDBA" 143 144 echo "create user $DBROOTUSER identified by $DBROOTPW 145 default tablespace DATA temporary tablespace TEMP 146 profile DEFAULT;" | $SYSCMD >$ORALOG 147 if [ $? -ne 0 ] || check_oracle_log ; then 148 mwarn "Create privileged user in database failed, perhaps they allready exist? Try to continue.." 149 fi 150 151 $SED_ROOTUSER $DB_SCHEMA/inc/_grantroot.tmpl | $SYSCMD >$ORALOG 152 if [ $? -ne 0 ] || check_oracle_log ; then 153 merr "Creating scheme in database '$1' failed!" 154 exit 1 155 fi 156 fi 157fi 158 159 160oracle_root_cmd $1 161 162$SED_ROOTUSER $DB_SCHEMA/inc/_createsch.tmpl | $ORACLE_ROOT_CMD >$ORALOG 163if [ $? -ne 0 ] || check_oracle_log ; then 164 merr "Creating scheme for '$DBROOTUSER' in database '$1' failed!" 165 exit 1 166fi 167 168cat $DB_SCHEMA/inc/_create_compat.sql | $ORACLE_ROOT_CMD >$ORALOG 169if [ $? -ne 0 ] || check_oracle_log ; then 170 merr "Creating compatibility functions for '$DBROOTUSER' in database '$1' failed!" 171 exit 1 172fi 173 174prompt_oracle_pw rw 175prompt_oracle_pw ro 176echo "create user $DBROUSER identified by $DBROPW 177 default tablespace DATA temporary tablespace TEMP profile DEFAULT; 178 grant connect to $DBROUSER; 179 create user $DBRWUSER identified by $DBRWPW 180 default tablespace DATA temporary tablespace TEMP profile DEFAULT; 181 grant connect to $DBRWUSER;" | $ORACLE_ROOT_CMD >$ORALOG 182if [ $? -ne 0 ] || check_oracle_log ; then 183 mwarn "Create user in database scheme failed, perhaps they allready exist? Try to continue.." 184fi 185 186$SED_USERS $DB_SCHEMA/inc/_grantfunc.tmpl | $ORACLE_ROOT_CMD >$ORALOG 187if [ $? -ne 0 ] || check_oracle_log ; then 188 merr "Make compatibility functions for users failed!" 189 exit 1 190fi 191 192 193for TABLE in $STANDARD_MODULES; do 194 mdbg "Creating core table: $TABLE" 195 cat $DB_SCHEMA/$TABLE-create.sql | $ORACLE_ROOT_CMD >$ORALOG 196 if [ $? -ne 0 ] || check_oracle_log ; then 197 merr "Creating core tables failed!" 198 exit 1 199 fi 200done 201 202if [ -e $DB_SCHEMA/extensions-create.sql ]; then 203 minfo "Creating custom extensions tables" 204 cat $DB_SCHEMA/extensions-create.sql | $ORACLE_ROOT_CMD >$ORALOG 205 if [ $? -ne 0 ] || check_oracle_log ; then 206 merr "Creating custom extensions tables failed!" 207 exit 1 208 fi 209fi 210 211minfo "Core Kamailio tables successfully created." 212 213get_answer $INSTALL_PRESENCE_TABLES "Install presence related tables? (y/n): " 214if [ "$ANSWER" = "y" ]; then 215 presence_create $1 216fi 217 218get_answer $INSTALL_EXTRA_TABLES "Install tables for $EXTRA_MODULES? (y/n): " 219if [ "$ANSWER" = "y" ]; then 220 extra_create $1 221fi 222} # kamailio_create 223 224 225presence_create() # pars: <database name> 226{ 227if [ $# -ne 1 ] ; then 228 merr "presence_create function takes one param" 229 exit 1 230fi 231 232minfo "creating presence tables..." 233oracle_root_cmd $1 234 235cat $DB_SCHEMA/presence-create.sql | $ORACLE_ROOT_CMD >$ORALOG 236if [ $? -ne 0 ] || check_oracle_log ; then 237 merr "Failed to create presence tables!" 238 exit 1 239fi 240 241cat $DB_SCHEMA/rls-create.sql | $ORACLE_ROOT_CMD >$ORALOG 242if [ $? -ne 0 ] || check_oracle_log ; then 243 merr "Failed to create rls-presence tables!" 244 exit 1 245fi 246 247minfo "Presence tables successfully created." 248} 249 250extra_create() # pars: <database name> 251{ 252if [ $# -ne 1 ] ; then 253 merr "extra_create function takes one param" 254 exit 1 255fi 256 257minfo "creating extra tables..." 258oracle_root_cmd $1 259 260for TABLE in $EXTRA_MODULES; do 261 mdbg "Creating extra table: $TABLE" 262 cat $DB_SCHEMA/$TABLE-create.sql | $ORACLE_ROOT_CMD >$ORALOG 263 if [ $? -ne 0 ] || check_oracle_log ; then 264 merr "Creating extra tables failed!" 265 exit 1 266 fi 267done 268 269minfo "Extra tables successfully created." 270} 271 272 273oracle_dump() 274{ 275 if [ $# -ne 2 ] ; then 276 merr "oracle_dump function takes two params" 277 exit 1 278 fi 279 prompt_oracle_pw rw 280 if [ -n "$1" ]; then 281 SUFF="@$1" 282 fi 283 if [ -n "$KAMAILIO_ORASEL" ]; then 284 $KAMAILIO_ORASEL ${DBROUSER}/${DBROPW}$SUFF -BLNe \ 285 "select * from table(dump_tables('$DBROOTUSER'));" >$2 286 else 287 echo "set feed 0 lin 8000 pages 0 288 select * from table(dump_tables('$DBROOTUSER'));" | \ 289 $SQLPLUS -S -L -R 3 ${DBROUSER}/${DBROPW}$SUFF >$2 290 fi 291 return $? 292} 293 294oracle_restore() 295{ 296 if [ $# -ne 2 ] ; then 297 merr "oracle_restore function takes two params" 298 exit 1 299 fi 300 prompt_oracle_pw rw 301 SUFF="" 302 if [ -n "$1" ]; then 303 SUFF="@$1" 304 fi 305 cat "$2" | $SQLPLUS -S -L -R 3 ${DBRWUSER}/${DBRWPW}$SUFF >$ORALOG 306 if [ $? -ne 0 ] || check_oracle_log ; then 307 return 1 308 fi 309 return 0 310} 311 312