· 10 years ago · Jan 08, 2016, 10:35 PM
1#!/bin/sh
2#
3# $Id: ser_mysql.sh,v 1.44 2003/09/19 08:27:46 janakj Exp $
4#
5# Script for adding and dropping ser MySql tables
6#
7# TO-DO: update_structures command for migriting to new
8# table definitons
9# USAGE: call the command without any parameters for info
10#
11# 2003-01-21 changed SILO table definition, by dcm
12#
13# History:
14# 2003-03-12 added replication mark and state columns to location (nils)
15# 2003-03-05: Changed user to username, user is reserved word (janakj)
16# 2003-01-26 statistics table introduced (jiri)
17# 2003-01-25: Optimized keys of some core tables (janakj)
18# 2003-01-25: USER_ID changed to user everywhere (janakj)
19# 2003-01-24: Changed realm column of subscriber and pending
20# tables to domain (janakj)
21# 2003-04-14 reinstall introduced (jiri)
22#
23
24#################################################################
25# config vars
26#################################################################
27DBNAME=sermysql
28DBHOST=localhost
29USERNAME=seruser
30DEFAULT_PW=serpass
31ROUSER=serro
32RO_PW=47serro11
33SQL_USER="root"
34CMD="mysql -h $DBHOST -u$SQL_USER "
35DUMP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c -t "
36BACKUP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c "
37TABLE_TYPE="TYPE=MyISAM"
38# user name column
39USERCOL="username"
40
41GENHA1='gen_ha1'
42
43#################################################################
44
45
46usage() {
47COMMAND=`basename $0`
48cat <<EOF
49usage: $COMMAND create
50 $COMMAND drop (!!entirely deletes tables)
51 $COMMAND reinit (!!entirely deletes and than re-creates tables
52 $COMMAND backup (dumps current database to stdout)
53 $COMMAND restore <file> (restores tables from a file)
54 $COMMAND copy <new_db> (creates a new db from an existing one)
55 $COMMAND reinstall (updates to a new SER database)
56
57 if you want to manipulate database as other MySql user than
58 root, want to change database name from default value "$DBNAME",
59 or want to use other values for users and password, edit the
60 "config vars" section of the command $COMMAND
61
62EOF
63} #usage
64
65
66# read password
67prompt_pw()
68{
69 savetty=`stty -g`
70 printf "MySql password for $SQL_USER: "
71 stty -echo
72 read PW
73 stty $savetty
74 echo
75}
76
77# execute sql command
78sql_query()
79{
80 $CMD "-p$PW" "$@"
81}
82
83# dump all rows
84ser_dump() # pars: <database name>
85{
86 if [ $# -ne 1 ] ; then
87 echo "ser_dump function takes one param"
88 exit 1
89 fi
90 $DUMP_CMD "-p$PW" $1
91}
92
93
94# copy a database to database_bak
95ser_backup() # par: <database name>
96{
97 if [ $# -ne 1 ] ; then
98 echo "ser_backup function takes one param"
99 exit 1
100 fi
101 BU=/tmp/mysql_bup.$$
102 $BACKUP_CMD "-p$PW" $1 > $BU
103 if [ "$?" -ne 0 ] ; then
104 echo "ser backup dump failed"
105 exit 1
106 fi
107 sql_query <<EOF
108 create database $1_bak;
109EOF
110
111 ser_restore $1_bak $BU
112 if [ "$?" -ne 0 ]; then
113 echo "ser backup/restore failed"
114 rm $BU
115 exit 1
116 fi
117}
118
119ser_restore() #pars: <database name> <filename>
120{
121if [ $# -ne 2 ] ; then
122 echo "ser_restore function takes two params"
123 exit 1
124fi
125sql_query $1 < $2
126}
127
128ser_drop() # pars: <database name>
129{
130if [ $# -ne 1 ] ; then
131 echo "ser_drop function takes two params"
132 exit 1
133fi
134
135sql_query << EOF
136drop database $1;
137EOF
138} #ser_drop
139
140# read realm
141prompt_realm()
142{
143 printf "Domain (realm) for the default user 'admin': "
144 read SIP_DOMAIN
145 echo
146}
147
148# calculate credentials for admin
149credentials()
150{
151 HA1=`$GENHA1 admin $SIP_DOMAIN heslo`
152 if [ $? -ne 0 ] ; then
153 echo "HA1 calculation failed"
154 exit 1
155 fi
156 HA1B=`$GENHA1 "admin@$SIP_DOMAIN" $SIP_DOMAIN heslo`
157 if [ $? -ne 0 ] ; then
158 echo "HA1B calculation failed"
159 exit 1
160 fi
161}
162
163ser_create () # pars: <database name> [<no_init_user>]
164{
165if [ $# -eq 1 ] ; then
166 if [ -z "$SIP_DOMAIN" ] ; then
167 prompt_realm
168 fi
169 credentials
170 # by default we create initial user
171 INITIAL_USER="INSERT INTO subscriber
172 ($USERCOL, password, first_name, last_name, phone,
173 email_address, datetime_created, datetime_modified, confirmation,
174 flag, sendnotification, greeting, ha1, domain, ha1b, phplib_id, perms )
175 VALUES ( 'admin', 'serpass', 'Initial', 'Admin', '123',
176 'root@localhost', '2002-09-04 19:37:45', '0000-00-00 00:00:00',
177 '57DaSIPuCm52UNe54LF545750cfdL48OMZfroM53', 'o', '', '',
178 '$HA1', '$SIP_DOMAIN', '$HA1B',
179 '65e397cda0aa8e3202ea22cbd350e4e9', 'admin' );"
180elif [ $# -eq 2 ] ; then
181 # if 3rd param set, don't create any initial user
182 INITIAL_USER=""
183else
184 echo "ser_create function takes one or two params"
185 exit 1
186fi
187
188echo "creating database $1 ..."
189
190sql_query <<EOF
191create database $1;
192use $1;
193
194# Users: ser is the regular user, serro only for reading
195GRANT ALL PRIVILEGES ON $1.* TO $USERNAME IDENTIFIED BY '$DEFAULT_PW';
196GRANT ALL PRIVILEGES ON $1.* TO ${USERNAME}@$DBHOST IDENTIFIED BY '$DEFAULT_PW';
197GRANT SELECT ON $1.* TO $ROUSER IDENTIFIED BY '$RO_PW';
198GRANT SELECT ON $1.* TO ${ROUSER}@$DBHOST IDENTIFIED BY '$RO_PW';
199
200
201#
202# Table structure versions
203#
204
205CREATE TABLE version (
206 table_name varchar(64) NOT NULL,
207 version smallint(5) DEFAULT '0' NOT NULL
208) $TABLE_TYPE;
209
210#
211# Dumping data for table 'version'
212#
213
214INSERT INTO version VALUES ( 'subscriber', '2');
215INSERT INTO version VALUES ( 'reserved', '1');
216INSERT INTO version VALUES ( 'phonebook', '1');
217INSERT INTO version VALUES ( 'pending', '2');
218INSERT INTO version VALUES ( 'missed_calls', '2');
219INSERT INTO version VALUES ( 'location', '3');
220INSERT INTO version VALUES ( 'grp', '2');
221INSERT INTO version VALUES ( 'event', '1');
222INSERT INTO version VALUES ( 'aliases', '3');
223INSERT INTO version VALUES ( 'active_sessions', '1');
224INSERT INTO version VALUES ( 'acc', '2');
225INSERT INTO version VALUES ( 'config', '1');
226INSERT INTO version VALUES ( 'silo', '2');
227INSERT INTO version VALUES ( 'realm', '1');
228INSERT INTO version VALUES ( 'domain', '1');
229INSERT INTO version VALUES ( 'uri', '1');
230INSERT INTO version VALUES ( 'server_monitoring', '1');
231INSERT INTO version VALUES ( 'server_monitoring_agg', '1');
232
233
234#
235# Table structure for table 'acc' -- accounted calls
236#
237
238
239CREATE TABLE acc (
240 sip_from varchar(128) NOT NULL default '',
241 sip_to varchar(128) NOT NULL default '',
242 sip_status varchar(128) NOT NULL default '',
243 sip_method varchar(16) NOT NULL default '',
244 i_uri varchar(128) NOT NULL default '',
245 o_uri varchar(128) NOT NULL default '',
246 from_uri varchar(128) NOT NULL default '',
247 to_uri varchar(128) NOT NULL default '',
248 sip_callid varchar(128) NOT NULL default '',
249 $USERCOL varchar(64) NOT NULL default '',
250 domain varchar(128) NOT NULL default '',
251 fromtag varchar(128) NOT NULL default '',
252 totag varchar(128) NOT NULL default '',
253 time datetime NOT NULL default '0000-00-00 00:00:00',
254 timestamp timestamp(14) NOT NULL,
255 INDEX acc_user ($USERCOL, domain)
256) $TABLE_TYPE;
257
258
259
260
261#
262# Table structure for table 'active_sessions' -- web stuff
263#
264
265
266CREATE TABLE active_sessions (
267 sid varchar(32) NOT NULL default '',
268 name varchar(32) NOT NULL default '',
269 val text,
270 changed varchar(14) NOT NULL default '',
271 PRIMARY KEY (name,sid),
272 KEY changed (changed)
273) $TABLE_TYPE;
274
275
276
277#
278# Table structure for table 'aliases' -- location-like table
279#
280
281CREATE TABLE aliases (
282 $USERCOL varchar(64) NOT NULL default '',
283 domain varchar(128) NOT NULL default '',
284 contact varchar(255) NOT NULL default '',
285 expires datetime default NULL,
286 q float(10,2) default NULL,
287 callid varchar(255) default NULL,
288 cseq int(11) default NULL,
289 last_modified timestamp(14) NOT NULL,
290 replicate int(10) unsigned default NULL,
291 state tinyint(1) unsigned default NULL,
292 flags int(11) NOT NULL default '0',
293 PRIMARY KEY($USERCOL, domain, contact)
294) $TABLE_TYPE;
295
296
297#
298# Table structure for table 'event' -- track of predefined
299# events to which a user subscribed
300#
301
302
303CREATE TABLE event (
304 id int(10) unsigned NOT NULL auto_increment,
305 $USERCOL varchar(64) NOT NULL default '',
306 domain varchar(128) NOT NULL default '',
307 uri varchar(255) NOT NULL default '',
308 description varchar(255) NOT NULL default '',
309 PRIMARY KEY (id)
310) $TABLE_TYPE;
311
312
313
314
315#
316# Table structure for table 'grp' -- group membership
317# table; used primarily for ACLs
318#
319
320
321CREATE TABLE grp (
322 $USERCOL varchar(64) NOT NULL default '',
323 domain varchar(128) NOT NULL default '',
324 grp varchar(50) NOT NULL default '',
325 last_modified datetime NOT NULL default '0000-00-00 00:00:00',
326 PRIMARY KEY($USERCOL, domain, grp)
327) $TABLE_TYPE;
328
329
330
331
332#
333# Table structure for table 'location' -- that is persistent UsrLoc
334#
335CREATE TABLE location (
336 $USERCOL varchar(64) NOT NULL default '',
337 domain varchar(128) NOT NULL default '',
338 contact varchar(255) NOT NULL default '',
339 expires datetime default NULL,
340 q float(10,2) default NULL,
341 callid varchar(255) default NULL,
342 cseq int(11) default NULL,
343 last_modified timestamp(14) NOT NULL,
344 replicate int(10) unsigned default NULL,
345 state tinyint(1) unsigned default NULL,
346 flags int(11) NOT NULL default '0',
347 PRIMARY KEY($USERCOL, domain, contact)
348) $TABLE_TYPE;
349
350
351
352
353#
354# Table structure for table 'missed_calls' -- acc-like table
355# for keeping track of missed calls
356#
357
358
359CREATE TABLE missed_calls (
360 sip_from varchar(128) NOT NULL default '',
361 sip_to varchar(128) NOT NULL default '',
362 sip_status varchar(128) NOT NULL default '',
363 sip_method varchar(16) NOT NULL default '',
364 i_uri varchar(128) NOT NULL default '',
365 o_uri varchar(128) NOT NULL default '',
366 from_uri varchar(128) NOT NULL default '',
367 to_uri varchar(128) NOT NULL default '',
368 sip_callid varchar(128) NOT NULL default '',
369 $USERCOL varchar(64) NOT NULL default '',
370 domain varchar(128) NOT NULL default '',
371 fromtag varchar(128) NOT NULL default '',
372 totag varchar(128) NOT NULL default '',
373 time datetime NOT NULL default '0000-00-00 00:00:00',
374 timestamp timestamp(14) NOT NULL,
375 INDEX mc_user ($USERCOL, domain)
376) $TABLE_TYPE;
377
378
379
380
381#
382# Table structure for table 'pending' -- unconfirmed subscribtion
383# requests
384#
385
386
387CREATE TABLE pending (
388 phplib_id varchar(32) NOT NULL default '',
389 $USERCOL varchar(64) NOT NULL default '',
390 domain varchar(128) NOT NULL default '',
391 password varchar(25) NOT NULL default '',
392 first_name varchar(25) NOT NULL default '',
393 last_name varchar(45) NOT NULL default '',
394 phone varchar(15) NOT NULL default '',
395 email_address varchar(50) NOT NULL default '',
396 datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
397 datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
398 confirmation varchar(64) NOT NULL default '',
399 flag char(1) NOT NULL default 'o',
400 sendnotification varchar(50) NOT NULL default '',
401 greeting varchar(50) NOT NULL default '',
402 ha1 varchar(128) NOT NULL default '',
403 ha1b varchar(128) NOT NULL default '',
404 perms varchar(32) default NULL,
405 allow_find char(1) NOT NULL default '0',
406 timezone varchar(128) default NULL,
407 PRIMARY KEY ($USERCOL, domain),
408 KEY user_2 ($USERCOL),
409 UNIQUE KEY phplib_id (phplib_id)
410) $TABLE_TYPE;
411
412
413
414
415#
416# Table structure for table 'phonebook' -- user's phonebook
417#
418
419
420CREATE TABLE phonebook (
421 id int(10) unsigned NOT NULL auto_increment,
422 $USERCOL varchar(64) NOT NULL default '',
423 domain varchar(128) NOT NULL default '',
424 fname varchar(32) NOT NULL default '',
425 lname varchar(32) NOT NULL default '',
426 sip_uri varchar(128) NOT NULL default '',
427 PRIMARY KEY (id)
428) $TABLE_TYPE;
429
430
431
432
433#
434# Table structure for table 'reserved' -- reserved username
435# which should be never allowed for subscription
436#
437
438
439CREATE TABLE reserved (
440 $USERCOL char(64) NOT NULL default '',
441 UNIQUE KEY user2(username)
442) $TABLE_TYPE;
443
444
445
446
447#
448# Table structure for table 'subscriber' -- user database
449#
450
451
452CREATE TABLE subscriber (
453 phplib_id varchar(32) NOT NULL default '',
454 $USERCOL varchar(64) NOT NULL default '',
455 domain varchar(128) NOT NULL default '',
456 password varchar(25) NOT NULL default '',
457 first_name varchar(25) NOT NULL default '',
458 last_name varchar(45) NOT NULL default '',
459 phone varchar(15) NOT NULL default '',
460 email_address varchar(50) NOT NULL default '',
461 datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
462 datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
463 confirmation varchar(64) NOT NULL default '',
464 flag char(1) NOT NULL default 'o',
465 sendnotification varchar(50) NOT NULL default '',
466 greeting varchar(50) NOT NULL default '',
467 ha1 varchar(128) NOT NULL default '',
468 ha1b varchar(128) NOT NULL default '',
469 perms varchar(32) default NULL,
470 allow_find char(1) NOT NULL default '0',
471 timezone varchar(128) default NULL,
472 UNIQUE KEY phplib_id (phplib_id),
473 PRIMARY KEY ($USERCOL, domain),
474 KEY user_2 ($USERCOL)
475) $TABLE_TYPE;
476
477# hook-table for all posssible future config values
478# (currently unused)
479
480CREATE TABLE config (
481 attribute varchar(32) NOT NULL,
482 value varchar(128) NOT NULL,
483 $USERCOL varchar(64) NOT NULL default '',
484 domain varchar(128) NOT NULL default '',
485 modified timestamp(14)
486) $TABLE_TYPE;
487
488# "instant" message silo
489
490CREATE TABLE silo(
491 mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
492 src_addr VARCHAR(255) NOT NULL DEFAULT "",
493 dst_addr VARCHAR(255) NOT NULL DEFAULT "",
494 r_uri VARCHAR(255) NOT NULL DEFAULT "",
495 inc_time INTEGER NOT NULL DEFAULT 0,
496 exp_time INTEGER NOT NULL DEFAULT 0,
497 ctype VARCHAR(32) NOT NULL DEFAULT "text/plain",
498 body BLOB NOT NULL DEFAULT ""
499) $TABLE_TYPE ;
500
501
502#
503# Table structure for table 'domain' -- domains proxy is responsible for
504#
505
506CREATE TABLE domain (
507 domain varchar(128) NOT NULL default '',
508 last_modified datetime NOT NULL default '0000-00-00 00:00:00',
509 PRIMARY KEY (domain)
510) $TABLE_TYPE;
511
512
513#
514# Table structure for table 'uri' -- uri user parts users are allowed to use
515#
516CREATE TABLE uri (
517 $USERCOL varchar(64) NOT NULL default '',
518 domain varchar(128) NOT NULL default '',
519 uri_user varchar(50) NOT NULL default '',
520 last_modified datetime NOT NULL default '0000-00-00 00:00:00',
521 PRIMARY KEY ($USERCOL, domain, uri_user)
522) $TABLE_TYPE;
523
524#
525# Table structure for table 'server_monitoring'
526#
527
528
529DROP TABLE IF EXISTS server_monitoring;
530CREATE TABLE server_monitoring (
531 time datetime NOT NULL default '0000-00-00 00:00:00',
532 id int(10) unsigned NOT NULL default '0',
533 param varchar(32) NOT NULL default '',
534 value int(10) NOT NULL default '0',
535 increment int(10) NOT NULL default '0',
536 PRIMARY KEY (id,param)
537) $TABLE_TYPE;
538
539
540CREATE TABLE preferences (
541 $USERCOL varchar(64) NOT NULL default '',
542 domain varchar(128) NOT NULL default '',
543 attribute varchar(50) NOT NULL default '',
544 value varchar(100) NOT NULL default '',
545 PRIMARY KEY ($USERCOL, domain, attribute)
546) $TABLE_TYPE;
547
548
549#
550# Table structure for table 'server_monitoring_agg'
551#
552
553
554DROP TABLE IF EXISTS server_monitoring_agg;
555CREATE TABLE server_monitoring_agg (
556 param varchar(32) NOT NULL default '',
557 s_value int(10) NOT NULL default '0',
558 s_increment int(10) NOT NULL default '0',
559 last_aggregated_increment int(10) NOT NULL default '0',
560 av float NOT NULL default '0',
561 mv int(10) NOT NULL default '0',
562 ad float NOT NULL default '0',
563 lv int(10) NOT NULL default '0',
564 min_val int(10) NOT NULL default '0',
565 max_val int(10) NOT NULL default '0',
566 min_inc int(10) NOT NULL default '0',
567 max_inc int(10) NOT NULL default '0',
568 lastupdate datetime NOT NULL default '0000-00-00 00:00:00',
569 PRIMARY KEY (param)
570) $TABLE_TYPE;
571
572# add an admin user "admin" with password==heslo,
573# so that one can try it out on quick start
574
575$INITIAL_USER
576
577
578
579EOF
580
581} # ser_create
582
583
584export PW
585if [ "$#" -ne 0 ]; then
586 prompt_pw
587fi
588
589case $1 in
590 reinstall)
591
592 #1 create a backup database (named *_bak)
593 echo "creating backup database"
594 ser_backup $DBNAME
595 if [ "$?" -ne 0 ] ; then
596 echo "reinstall: ser_backup failed"
597 exit 1
598 fi
599 #2 dump original database and change names in it
600 echo "dumping table content ($DBNAME)"
601 tmp_file=/tmp/ser_mysql.$$
602 ser_dump $DBNAME > $tmp_file
603 if [ "$?" -ne 0 ] ; then
604 echo "reinstall: dumping original db failed"
605 exit 1
606 fi
607 sed "s/[uU][sS][eE][rR]_[iI][dD]/user/g" $tmp_file |
608 sed "s/[uU][sS][eE][rR]/$USERCOL/g" |
609 sed "s/[rR][eE][aA][lL][mM]/domain/g"> ${tmp_file}.2
610 #3 drop original database
611 echo "dropping table ($DBNAME)"
612 ser_drop $DBNAME
613 if [ "$?" -ne 0 ] ; then
614 echo "reinstall: dropping table failed"
615 rm $tmp_file*
616 exit 1
617 fi
618 #4 change names in table definition and restore
619 echo "creating new structures"
620 ser_create $DBNAME no_init_user
621 if [ "$?" -ne 0 ] ; then
622 echo "reinstall: creating new table failed"
623 rm $tmp_file*
624 exit 1
625 fi
626 #5 restoring table content
627 echo "restoring table content"
628 ser_restore $DBNAME ${tmp_file}.2
629 if [ "$?" -ne 0 ] ; then
630 echo "reinstall: restoring table failed"
631 rm $tmp_file*
632 exit 1
633 fi
634#XX
635# rm $tmp_file*
636 exit 0
637 ;;
638 copy)
639 # copy database to some other name
640 shift
641 if [ $# -ne 1 ]; then
642 usage
643 exit 1
644 fi
645 tmp_file=/tmp/ser_mysql.$$
646 ser_dump $DBNAME > $tmp_file
647 ret=$?
648 if [ "$ret" -ne 0 ]; then
649 rm $tmp_file
650 exit $ret
651 fi
652 ser_create $1 no_init_user
653 ret=$?
654 if [ "$ret" -ne 0 ]; then
655 rm $tmp_file
656 exit $ret
657 fi
658 ser_restore $1 $tmp_file
659 ret=$?
660 rm $tmp_file
661 exit $ret
662 ;;
663 backup)
664 # backup current database
665 ser_dump $DBNAME
666 exit $?
667 ;;
668 restore)
669 # restore database from a backup
670 shift
671 if [ $# -ne 1 ]; then
672 usage
673 exit 1
674 fi
675 ser_restore $DBNAME $1
676 exit $?
677 ;;
678 create)
679 # create new database structures
680 shift
681 if [ $# -eq 1 ] ; then
682 DBNAME="$1"
683 fi
684 ser_create $DBNAME
685 exit $?
686 ;;
687 drop)
688 # delete ser database
689 ser_drop $DBNAME
690 exit $?
691 ;;
692 reinit)
693 # delete database and create a new one
694 ser_drop $DBNAME
695 ret=$?
696 if [ "$ret" -ne 0 ]; then
697 exit $ret
698 fi
699 ser_create $DBNAME
700 exit $?
701 ;;
702 *)
703 usage
704 exit 1;
705 ;;
706esac
707
708