1#!/bin/sh 2 3# 4# Netmagis database upgrade 5# 6# Supported upgrade path from 2.0 to all current Netmagis versions. 7# 8# Syntax : 9# netmagis-dbupgrade [-f netmagis-conf] [target-version] 10# 11 12usage () 13{ 14 ( 15 echo "usage: $0 [-f netmagis.conf] [target-version]" 16 echo "Without argument: display actual versions" 17 echo "With target argument: upgrade database to this version" 18 ) >&2 19 exit 1 20} 21 22# Directory where upgrade scripts are located 23UPGRADEDIR=%NMLIBDIR%/upgrade 24 25############################################################################## 26# Configuration file read 27############################################################################## 28 29init_env () 30{ 31 32 # Main netmagis database parameters (dnsdb*) 33 varlist="dnsdbhost dnsdbport dnsdbname dnsdbuser dnsdbpassword" 34 eval $(%CONFPROG% -f $_conffile -c $varlist) 35 36 if [ $? != 0 ] 37 then 38 usage 39 exit 1 40 fi 41 42 PGHOST="$dnsdbhost" 43 PGPORT="$dnsdbport" 44 PGDATABASE="$dnsdbname" 45 PGUSER="$dnsdbuser" 46 PGPASSWORD="$dnsdbpassword" 47 export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD 48 49 # Mac database parameters (macdb*) 50 varlist="macdbhost macdbport macdbname macdbuser macdbpassword" 51 eval $(%CONFPROG% -f $_conffile -c $varlist) 52 53 if [ $? != 0 ] 54 then 55 usage 56 exit 1 57 fi 58 59 # Other configuration parameters 60 varlist="_version" 61 eval $(%CONFPROG% -f $_conffile -c $varlist) 62 63 if [ $? != 0 ] 64 then 65 usage 66 exit 1 67 fi 68} 69 70############################################################################## 71# Test if column exists 72############################################################################## 73 74# $1 : schema 75# $2 : table 76# $3 : column 77column_exists () 78{ 79 count=$(psql --quiet --no-psqlrc --tuples-only --no-align \ 80 -c "SELECT 1-COUNT(*) FROM information_schema.columns 81 WHERE table_schema = '$1' 82 AND table_name = '$2' 83 AND column_name = '$3' 84 ") 85 return $count 86} 87 88############################################################################## 89# Database version heuristic 90############################################################################## 91 92detect_schema_version () 93{ 94 v=-1 # unrecognized 95 if column_exists global config key 96 then 97 v=$(psql --quiet --no-psqlrc --tuples-only --no-align \ 98 -c "SELECT value FROM global.config 99 WHERE key = 'schemaversion'") 100 elif column_exists topo confcmd idccmd 101 then v=21 102 elif column_exists topo sensor id 103 then v=20 104 else v=0 # pre-2.0 105 fi 106 echo $v 107} 108 109############################################################################## 110# Check target path 111############################################################################## 112 113# $1 = current version 114# $2 = target version 115check_target_path () 116{ 117 begin=$1 118 end=$2 119 120 # get all upgrade directories 121 list=$(cd $UPGRADEDIR ; ls -d *-* | sort -n) 122 123 # skip directories not in path 124 lastto="" 125 path="" 126 for d in $list 127 do 128 from=$(echo $d | sed 's/-.*//') 129 to=$(echo $d | sed 's/.*-//') 130 if [ $from = $begin -a $to -le $end ] 131 then 132 path="$path $d" 133 begin=$to 134 lastto=$to 135 fi 136 done 137 # is the target version equal to the last item in the path? 138 if [ "$end" != "$lastto" ] 139 then 140 path="" 141 fi 142 echo $path 143} 144 145 146############################################################################## 147# SQL file execution 148############################################################################## 149 150# $1 = file (relative to %NMLIBDIR%) 151sql_file_execute () 152{ 153 PGCLIENTENCODING=utf8 154 export PGCLIENTENCODING 155 156 psql --no-psqlrc --quiet --file %NMLIBDIR%/$1 2>&1 \ 157 | grep -v 'NOTICE: .* will create implicit' 158} 159 160############################################################################## 161# Language creation 162############################################################################## 163 164# $1 = language 165# exits if creation failed 166create_language () 167{ 168 if psql --quiet --no-psqlrc -c "CREATE EXTENSION IF NOT EXISTS $1" 169 then : 170 else 171 echo "Unable to create language "$1" in database '$PGDATABASE'" >&2 172 exit 1 173 fi 174} 175 176############################################################################## 177# Create netmagis database 178############################################################################## 179 180create_netmagis () 181{ 182 echo "Creating main Netmagis database as '$dnsdbname'" 183 184 PGHOST="$dnsdbhost" 185 PGPORT="$dnsdbport" 186 PGDATABASE="$dnsdbname" 187 PGUSER="$dnsdbuser" 188 PGPASSWORD="$dnsdbpassword" 189 export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD 190 191 ############################### 192 # Create main netmagis database 193 ############################### 194 195 if db_create_if_not_exists 196 then : 197 else 198 echo "Erreur while creating database '$PGDATABASE'" 2>&1 199 exit 1 200 fi 201 202 # exit if schema already exist 203 if db_test_schemas "global" "dns" "topo" "pgauth" 204 then 205 exit 1 206 fi 207 208 ############################### 209 # Initialize main netmagis database schemas and contents 210 ############################### 211 212 create_language plpgsql 213 create_language pltcl 214 215 create_netmagis_schemas 216 217 netmagis_insert_config 218 219 ############################### 220 # Create netmagis application users 221 ############################### 222 223 for u in $rootusers 224 do 225 netmagis_create_user $u 226 done 227 228 netmagis_create_user $defuser 229 230 if [ "$pwgen" = "" -o "$crypt" = "" ] 231 then 232 echo "Netmagis configuration parameter 'pwgen' and/or 'crypt' uninitialized" >&2 233 echo "Users $rootusers have been created" >&2 234 else 235 echo "You can change passwords within netmagis application" >&2 236 fi 237} 238 239 240############################################################################## 241# Main program 242############################################################################## 243 244# 245# Get default configuration file location 246# 247eval $(%CONFPROG% -c _conffile) 248 249 250# 251# Syntax checking 252# 253 254args=$(getopt hf: $*) 255 256if [ $? != 0 ] 257then usage 258fi 259 260set -- $args 261 262while true 263do 264 case "$1" in 265 -h) usage 266 exit 0 267 ;; 268 -f) _conffile=$2 269 shift 2 270 ;; 271 --) shift 272 break 273 ;; 274 esac 275done 276 277case $# in 278 0) target="" ;; 279 1) target="$1" ;; 280 *) usage ;; 281esac 282 283# 284# Initialize environnement 285# 286 287init_env 288 289# 290# Detect versions 291# 292 293schemaversion=$(detect_schema_version) 294codeversion=$(echo $_version | sed 's/^\([0-9][0-9]*\)\.\([0-9][0-9]*\).*/\1\2/') 295 296if [ $schemaversion -gt $codeversion ] 297then 298 echo "Database schema ($schemaversion) is not yet recognized by Netmagis ($codeversion)" >&2 299 exit 1 300fi 301 302# 303# Just stop here if no target is specified 304# 305 306if [ -z "$target" ] 307then 308 echo "Detected schema version: $schemaversion" 309 echo "Latest schema supported: $codeversion" 310 exit 0 311fi 312 313# 314# Check upgrade path 315# 316 317upgrade_path=$(check_target_path $schemaversion $target) 318if [ -z "$upgrade_path" ] 319then 320 echo "Cannot find an upgrade path from $schemaversion to $target." >&2 321 exit 0 322fi 323 324# 325# Perform upgrade 326# 327 328echo "Preparing upgrade from $schemaversion to $target." 329 330TMP=/tmp/netmagis-dbupdate.$$ 331 332for d in $upgrade_path 333do 334 u=$UPGRADEDIR/$d/upgrade.sql 335 if [ -f $u ] 336 then 337 echo "\\echo Upgrading: $d" 338 echo "\\i $u" 339 fi 340done > $TMP 341 342PGOPTIONS='--client-min-messages=warning' 343export PGOPTIONS 344 345if psql --quiet --no-psqlrc --single-transaction -f $TMP 346then 347 echo "Upgrade successful" >&2 348 r=0 349else 350 echo "Upgrade error. Aborted" >&2 351 r=1 352fi 353 354rm -f $TMP 355 356exit $r 357