tools/kamdbctl.pgsql
4d5ef34f
 # $Id$
 #
860c33c5
 # Script for adding and dropping Kamailio Postgres tables
4d5ef34f
 #
 # History:
bf458268
 # 2006-05-16  added ability to specify MD5 from a configuration file
 #             FreeBSD does not have the md5sum function (norm)
8a8e917b
 # 2006-07-14  Corrected syntax from MySQL to Postgres (norm)
 #             moved INDEX creation out of CREATE table statement into 
 #                  CREATE INDEX (usr_preferences, trusted)
 #             auto_increment isn't valid in Postgres, replaced with 
 #                  local AUTO_INCREMENT
 #             datetime isn't valid in Postgres, replaced with local DATETIME 
 #             split GRANTs for SERWeb tables so that it is only executed 
 #                  if SERWeb tables are created
 #             added GRANTs for re_grp table
ef17359e
 #             added CREATE pdt table (from PDT module)
8a8e917b
 #             corrected comments to indicate Postgres as opposed to MySQL
 #             made last_modified/created stamps consistent to now() using 
 #                  local TIMESTAMP
a1c4a738
 # 2006-10-19  Added address table (bogdan)
2a679bc0
 # 2006-10-27  subscriber table cleanup; some columns are created only if
 #             serweb is installed (bogdan)
ea7ed106
 # 2007-01-26  added seperate installation routine for presence related tables
 #             and fix permissions for the SERIAL sequences.
175e2d5c
 # 2007-05-21  Move SQL database definitions out of this script (henning)
de1a4247
 # 2007-05-31  Move common definitions to kamdbctl.base file (henningw)
96a2629b
 #
de1a4247
 # 2007-06-11  Use a common control tool for database tasks, like the kamctl
4d5ef34f
 
175e2d5c
 # path to the database schemas
860c33c5
 DATA_DIR="/usr/local/share/kamailio"
175e2d5c
 if [ -d "$DATA_DIR/postgres" ] ; then
 	DB_SCHEMA="$DATA_DIR/postgres"
 else
 	DB_SCHEMA="./postgres"
 fi
 
4d5ef34f
 #################################################################
 # config vars
 #################################################################
b2750804
 
8a8e917b
 # full privileges Postgres user
bed32f67
 if [ -z "$DBROOTUSER" ]; then
 	DBROOTUSER="postgres"
4d5ef34f
 	if [ ! -r ~/.pgpass ]; then
96a2629b
 		merr "~./pgpass does not exist, please create this file and support proper credentials for user postgres."
 		merr "Note: you need at least postgresql>= 7.3"
4d5ef34f
 		exit 1
 	fi
 fi
 
5f72f337
 CMD="psql -q -h $DBHOST -U $DBROOTUSER "
96a2629b
 DUMP_CMD="pg_dump -h $DBHOST -U $DBROOTUSER -c"
4d5ef34f
 #################################################################
 
 
175e2d5c
 # execute sql command with optional db name
4d5ef34f
 sql_query()
 {
175e2d5c
 	if [ $# -gt 1 ] ; then
 		if [ -n "$1" ]; then
 			DB="$1"
 		else
 			DB=""
 		fi
 		shift
 		$CMD -d $DB -c "$@"
 	else
 		$CMD "$@"
 	fi
4d5ef34f
 }
 
175e2d5c
 
7a9eeff1
 openser_drop()  # pars: <database name>
4d5ef34f
 {
b2750804
 if [ $# -ne 1 ] ; then
96a2629b
 	merr "openser_drop function takes two params"
b2750804
 	exit 1
 fi
4d5ef34f
 
eff43b43
 sql_query "template1" "drop database \"$1\";"
b2750804
 if [ $? -ne 0 ] ; then
96a2629b
 	merr "Dropping database $1 failed!"
b2750804
 	exit 1
 fi
eff43b43
 
 # postgresql users are not dropped automatically
 sql_query "template1" "drop user \"$DBRWUSER\"; drop user \"$DBROUSER\";"
 
 if [ $? -ne 0 ] ; then
 	mwarn "Could not drop $DBRWUSER or $DBROUSER users, try to continue.."
 else 
 	minfo "Database user deleted"
 fi
 
96a2629b
 minfo "Database $1 dropped"
7a9eeff1
 } #openser_drop
4d5ef34f
 
 
2a679bc0
 openser_create () # pars: <database name>
4d5ef34f
 {
2a679bc0
 if [ $# -ne 1 ] ; then
96a2629b
 	merr "openser_create function takes one param"
4d5ef34f
 	exit 1
 fi
 
96a2629b
 minfo "creating database $1 ..."
4d5ef34f
 
d1335475
 sql_query "template1" "create database \"$1\";"
175e2d5c
 if [ $? -ne 0 ] ; then
96a2629b
 	merr "Creating database failed!"
175e2d5c
 	exit 1
 fi
7a9eeff1
 
175e2d5c
 sql_query "$1" "CREATE FUNCTION "concat" (text,text) RETURNS text AS 'SELECT \$1 || \$2;' LANGUAGE 'sql';
 	        CREATE FUNCTION "rand" () RETURNS double precision AS 'SELECT random();' LANGUAGE 'sql';"
 # emulate mysql proprietary functions used by the lcr module in postgresql
7a9eeff1
 
a6ce4e45
 if [ $? -ne 0 ] ; then
96a2629b
 	merr "Creating mysql emulation functions failed!"
175e2d5c
 	exit 1
 fi
 
 for TABLE in $STANDARD_MODULES; do
96a2629b
     mdbg "Creating core table: $TABLE"
175e2d5c
     sql_query "$1" < $DB_SCHEMA/$TABLE-create.sql
     if [ $? -ne 0 ] ; then
96a2629b
 	merr "Creating core tables failed!"
175e2d5c
 	exit 1
     fi
 done
 
 sql_query "$1" "CREATE USER $DBRWUSER WITH PASSWORD '$DBRWPW';
96a2629b
 		CREATE USER $DBROUSER WITH PASSWORD '$DBROPW';"
 if [ $? -ne 0 ] ; then
 	mwarn "Create user in database failed, perhaps they allready exist? Try to continue.."
 fi
 
237e66d1
 for TABLE in $STANDARD_TABLES; do
5f72f337
 	sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;"
 	sql_query "$1" "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;"
 	if [ $TABLE != "version" ] ; then
 		sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE "$TABLE"_id_seq TO $DBRWUSER;"
     	sql_query "$1" "GRANT SELECT ON TABLE "$TABLE"_id_seq TO $DBROUSER;"
 	fi
 
237e66d1
 	if [ $? -ne 0 ] ; then
 		merr "Grant privileges to standard tables failed!"
 		exit 1
 	fi
 done
 
8783629a
 if [ -e $DB_SCHEMA/extensions-create.sql ]
 then
 	minfo "Creating custom extensions tables"
 	sql_query $1 < $DB_SCHEMA/extensions-create.sql
 	if [ $? -ne 0 ] ; then
 	merr "Creating custom extensions tables failed!"
 	exit 1
 	fi
 fi
 
860c33c5
 minfo "Core Kamailio tables succesfully created."
a6ce4e45
 
27a71bd2
 get_answer $INSTALL_PRESENCE_TABLES "Install presence related tables? (y/n): "
 if [ "$ANSWER" = "y" ]; then
ea7ed106
 	presence_create $1
 fi
 
27a71bd2
 get_answer $INSTALL_EXTRA_TABLES "Install tables for $EXTRA_MODULES? (y/n): "
 if [ "$ANSWER" = "y" ]; then
5da3724f
 	extra_create $1
7a9eeff1
 fi
 } # openser_create
 
 
ea7ed106
 presence_create () # pars: <database name>
 {
 if [ $# -ne 1 ] ; then
96a2629b
 	merr "presence_create function takes one param"
ea7ed106
 	exit 1
 fi
 
96a2629b
 minfo "creating presence tables into $1 ..."
ea7ed106
 
175e2d5c
 sql_query "$1" < $DB_SCHEMA/presence-create.sql
ea7ed106
 
a6ce4e45
 if [ $? -ne 0 ] ; then
96a2629b
 	merr "Failed to create presence tables!"
a6ce4e45
 	exit 1
 fi
 
6e5f8823
 sql_query "$1" < $DB_SCHEMA/rls-create.sql
 
 if [ $? -ne 0 ] ; then
 	merr "Failed to create rls-presence tables!"
 	exit 1
 fi
 
237e66d1
 for TABLE in $PRESENCE_TABLES; do
5f72f337
 	sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;"
 	sql_query "$1" "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;"
 	sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE "$TABLE"_id_seq TO $DBRWUSER;"
     sql_query "$1" "GRANT SELECT ON TABLE "$TABLE"_id_seq TO $DBROUSER;"
237e66d1
 	if [ $? -ne 0 ] ; then
 		merr "Grant privileges to presence tables failed!"
 		exit 1
 	fi
 done
 
96a2629b
 minfo "Presence tables succesfully created."
ea7ed106
 }  # end presence_create
 
 
5da3724f
 extra_create () # pars: <database name>
 {
 if [ $# -ne 1 ] ; then
96a2629b
 	merr "extra_create function takes one param"
5da3724f
 	exit 1
 fi
 
96a2629b
 minfo "creating extra tables into $1 ..."
 
175e2d5c
 for TABLE in $EXTRA_MODULES; do
96a2629b
     mdbg "Creating extra table: $TABLE"
175e2d5c
     sql_query "$1" < $DB_SCHEMA/$TABLE-create.sql
     if [ $? -ne 0 ] ; then
96a2629b
 	merr "Creating extra tables failed!"
175e2d5c
 	exit 1
     fi
 done
 
237e66d1
 for TABLE in $EXTRA_TABLES; do
5f72f337
 	sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE $TABLE TO $DBRWUSER;"
 	sql_query "$1" "GRANT SELECT ON TABLE $TABLE TO $DBROUSER;"
 	if [ $TABLE != "route_tree" ] ; then
 		sql_query "$1" "GRANT ALL PRIVILEGES ON TABLE "$TABLE"_id_seq TO $DBRWUSER;"
 	    sql_query "$1" "GRANT SELECT ON TABLE "$TABLE"_id_seq TO $DBROUSER;"
 	fi
237e66d1
 	if [ $? -ne 0 ] ; then
 		merr "Grant privileges to extra tables failed!"
 		exit 1
 	fi
 done
 
96a2629b
 minfo "Extra tables succesfully created."
5da3724f
 }  # end extra_create