Browse code

- ser_mysql.sh cleanup

Jan Janak authored on 15/11/2005 11:28:47
Showing 1 changed files
1 1
deleted file mode 100755
... ...
@@ -1,838 +0,0 @@
1
-#!/bin/sh
2
-#
3
-# $Id$
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
-# 2004-07-05  new definition of table silo (dcm)
23
-# 2004-07-23  added uri_scheme and transport cols to table gw (jih)
24
-# 2005-09-05  new tables dom_preferences, customer and new columns in table domain
25
-
26
-
27
-#################################################################
28
-# config vars
29
-#################################################################
30
-DBNAME=ser
31
-DBHOST=localhost
32
-USERNAME=ser
33
-DEFAULT_PW=heslo
34
-ROUSER=serro
35
-RO_PW=47serro11
36
-SQL_USER="root"
37
-CMD="mysql -h $DBHOST -u$SQL_USER "
38
-DUMP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c -t "
39
-BACKUP_CMD="mysqldump -h $DBHOST -u$SQL_USER -c "
40
-TABLE_TYPE="TYPE=MyISAM"
41
-# user name column
42
-USERCOL="username"
43
-
44
-GENHA1='gen_ha1'
45
-
46
-FOREVER="2020-05-28 21:32:15"
47
-
48
-DEFAULT_ALIASES_EXPIRES=$FOREVER
49
-DEFAULT_Q="1.0"
50
-DEFAULT_CALLID="Default-Call-ID"
51
-DEFAULT_CSEQ="42"
52
-DEFAULT_LOCATION_EXPIRES=$FOREVER
53
-
54
-#################################################################
55
-
56
-
57
-usage() {
58
-COMMAND=`basename $0`
59
-cat <<EOF
60
-usage: $COMMAND create
61
-       $COMMAND drop   (!!entirely deletes tables)
62
-       $COMMAND reinit (!!entirely deletes and than re-creates tables
63
-       $COMMAND backup (dumps current database to stdout)
64
-	   $COMMAND restore <file> (restores tables from a file)
65
-       $COMMAND copy <new_db> (creates a new db from an existing one)
66
-       $COMMAND reinstall (updates to a new SER database)
67
-
68
-       if you want to manipulate database as other MySql user than
69
-       root, want to change database name from default value "$DBNAME",
70
-       or want to use other values for users and password, edit the
71
-       "config vars" section of the command $COMMAND
72
-
73
-EOF
74
-} #usage
75
-
76
-
77
-# read password
78
-prompt_pw()
79
-{
80
-	savetty=`stty -g`
81
-	printf "MySql password for $SQL_USER: "
82
-	stty -echo
83
-	read PW
84
-	stty $savetty
85
-	echo
86
-}
87
-
88
-# execute sql command
89
-sql_query()
90
-{
91
-	$CMD "-p$PW" "$@"
92
-}
93
-
94
-# dump all rows
95
-ser_dump()  # pars: <database name>
96
-{
97
-	if [ $# -ne 1 ] ; then
98
-		echo "ser_dump function takes one param"
99
-		exit 1
100
-	fi
101
-	$DUMP_CMD "-p$PW" $1
102
-}
103
-
104
-
105
-# copy a database to database_bak
106
-ser_backup() # par: <database name>
107
-{
108
-	if [ $# -ne 1 ] ; then
109
-		echo  "ser_backup function takes one param"
110
-		exit 1
111
-	fi
112
-	BU=/tmp/mysql_bup.$$
113
-	$BACKUP_CMD "-p$PW" $1 > $BU
114
-	if [ "$?" -ne 0 ] ; then
115
-		echo "ser backup dump failed"
116
-		exit 1
117
-	fi
118
-	sql_query <<EOF
119
-	create database $1_bak;
120
-EOF
121
-
122
-	ser_restore $1_bak $BU
123
-	if [ "$?" -ne 0 ]; then
124
-		echo "ser backup/restore failed"
125
-		rm $BU
126
-		exit 1
127
-	fi
128
-}
129
-
130
-ser_restore() #pars: <database name> <filename>
131
-{
132
-if [ $# -ne 2 ] ; then
133
-	echo "ser_restore function takes two params"
134
-	exit 1
135
-fi
136
-sql_query $1 < $2
137
-}
138
-
139
-ser_drop()  # pars: <database name>
140
-{
141
-if [ $# -ne 1 ] ; then
142
-	echo "ser_drop function takes two params"
143
-	exit 1
144
-fi
145
-
146
-sql_query << EOF
147
-drop database $1;
148
-EOF
149
-} #ser_drop
150
-
151
-# read realm
152
-prompt_realm()
153
-{
154
-	printf "Domain (realm) for the default user 'admin': "
155
-	read SIP_DOMAIN
156
-	echo
157
-}
158
-
159
-# calculate credentials for admin
160
-credentials()
161
-{
162
-	HA1=`$GENHA1 admin $SIP_DOMAIN heslo`
163
-	if [ $? -ne 0 ] ; then
164
-		echo "HA1 calculation failed"
165
-		exit 1
166
-	fi
167
-	HA1B=`$GENHA1 "admin@$SIP_DOMAIN" $SIP_DOMAIN heslo`
168
-	if [ $? -ne 0 ] ; then
169
-		echo "HA1B calculation failed"
170
-		exit 1
171
-	fi
172
-
173
-  #PHPLIB_ID of users should be difficulty to guess for security reasons
174
-  NOW=`date`;
175
-  PHPLIB_ID=`$GENHA1 "$RANDOM" "$NOW" $SIP_DOMAIN`
176
-	if [ $? -ne 0 ] ; then
177
-    echo "PHPLIB_ID calculation failed"
178
-		exit 1
179
-	fi
180
-}
181
-
182
-ser_create () # pars: <database name> [<no_init_user>]
183
-{
184
-if [ $# -eq 1 ] ; then
185
-	if [ -z "$SIP_DOMAIN" ] ; then
186
-		prompt_realm
187
-	fi
188
-	credentials
189
-	# by default we create initial user
190
-	INITIAL_USER="INSERT INTO subscriber
191
-		($USERCOL, password, first_name, last_name, phone,
192
-		email_address, datetime_created, datetime_modified, confirmation,
193
-    flag, sendnotification, greeting, ha1, domain, ha1b, phplib_id )
194
-		VALUES ( 'admin', 'heslo', 'Initial', 'Admin', '123',
195
-		'root@localhost', '2002-09-04 19:37:45', '0000-00-00 00:00:00',
196
-		'57DaSIPuCm52UNe54LF545750cfdL48OMZfroM53', 'o', '', '',
197
-		'$HA1', '$SIP_DOMAIN', '$HA1B',
198
-    '$PHPLIB_ID' );
199
-
200
-    INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
201
-    VALUES ('admin', '$SIP_DOMAIN', 'is_admin', '1');
202
-
203
-    INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value)
204
-    VALUES ('admin', '$SIP_DOMAIN', 'change_privileges', '1');"
205
-elif [ $# -eq 2 ] ; then
206
-	# if 3rd param set, don't create any initial user
207
-	INITIAL_USER=""
208
-else
209
-	echo "ser_create function takes one or two params"
210
-	exit 1
211
-fi
212
-
213
-echo "creating database $1 ..."
214
-
215
-sql_query <<EOF
216
-create database $1;
217
-use $1;
218
-
219
-# Users: ser is the regular user, serro only for reading
220
-GRANT ALL PRIVILEGES ON $1.* TO $USERNAME IDENTIFIED  BY '$DEFAULT_PW';
221
-GRANT ALL PRIVILEGES ON $1.* TO ${USERNAME}@$DBHOST IDENTIFIED BY '$DEFAULT_PW';
222
-GRANT SELECT ON $1.* TO $ROUSER IDENTIFIED BY '$RO_PW';
223
-GRANT SELECT ON $1.* TO ${ROUSER}@$DBHOST IDENTIFIED BY '$RO_PW';
224
-
225
-
226
-#
227
-# Table structure versions
228
-#
229
-
230
-CREATE TABLE version (
231
-   table_name varchar(64) NOT NULL,
232
-   table_version smallint(5) DEFAULT '0' NOT NULL
233
-) $TABLE_TYPE;
234
-
235
-#
236
-# Dumping data for table 'version'
237
-#
238
-
239
-INSERT INTO version VALUES ( 'subscriber', '5');
240
-INSERT INTO version VALUES ( 'phonebook', '1');
241
-INSERT INTO version VALUES ( 'pending', '4');
242
-INSERT INTO version VALUES ( 'missed_calls', '2');
243
-INSERT INTO version VALUES ( 'location', '7');
244
-INSERT INTO version VALUES ( 'grp', '2');
245
-INSERT INTO version VALUES ( 'aliases', '7');
246
-INSERT INTO version VALUES ( 'acc', '2');
247
-INSERT INTO version VALUES ( 'silo', '3');
248
-INSERT INTO version VALUES ( 'domain', '2');
249
-INSERT INTO version VALUES ( 'uri', '1');
250
-INSERT INTO version VALUES ( 'server_monitoring', '1');
251
-INSERT INTO version VALUES ( 'server_monitoring_agg', '1');
252
-INSERT INTO version VALUES ( 'trusted', '1');
253
-INSERT INTO version VALUES ( 'usr_preferences', '2');
254
-INSERT INTO version VALUES ( 'usr_preferences_types', '1');
255
-INSERT INTO version VALUES ( 'admin_privileges', '1');
256
-INSERT INTO version VALUES ( 'speed_dial', '2');
257
-INSERT INTO version VALUES ( 'gw', '3');
258
-INSERT INTO version VALUES ( 'gw_grp', '1');
259
-INSERT INTO version VALUES ( 'lcr', '1');
260
-INSERT INTO version VALUES ( 'customer', '1');
261
-INSERT INTO version VALUES ( 'dom_preferences', '1');
262
-
263
-#
264
-# Table structure for table 'acc' -- accounted calls
265
-#
266
-
267
-
268
-CREATE TABLE acc (
269
-  caller_UUID varchar(64) NOT NULL default '',
270
-  callee_UUID varchar(64) NOT NULL default '',
271
-  sip_from varchar(128) NOT NULL default '',
272
-  sip_to varchar(128) NOT NULL default '',
273
-  sip_status varchar(128) NOT NULL default '',
274
-  sip_method varchar(16) NOT NULL default '',
275
-  i_uri varchar(128) NOT NULL default '',
276
-  o_uri varchar(128) NOT NULL default '',
277
-  from_uri varchar(128) NOT NULL default '',
278
-  to_uri varchar(128) NOT NULL default '',
279
-  sip_callid varchar(128) NOT NULL default '',
280
-  $USERCOL varchar(64) NOT NULL default '',
281
-  domain varchar(128) NOT NULL default '',
282
-  fromtag varchar(128) NOT NULL default '',
283
-  totag varchar(128) NOT NULL default '',
284
-  time datetime NOT NULL default '0000-00-00 00:00:00',
285
-  timestamp timestamp(14) NOT NULL,
286
-  caller_deleted char(1) NOT NULL default '0',
287
-  callee_deleted char(1) NOT NULL default '0',
288
-  INDEX acc_user ($USERCOL, domain),
289
-  KEY sip_callid (sip_callid)
290
-) $TABLE_TYPE;
291
-
292
-#
293
-# Table structure for table 'aliases' -- location-like table
294
-# (aliases_contact index makes lookup of missed calls much faster)
295
-#
296
-
297
-CREATE TABLE aliases (
298
-  $USERCOL varchar(64) NOT NULL default '',
299
-  domain varchar(128) NOT NULL default '',
300
-  contact varchar(255) NOT NULL default '',
301
-  received varchar(255) default NULL,
302
-  expires datetime NOT NULL default '$DEFAULT_ALIASES_EXPIRES',
303
-  q float(10,3) NOT NULL default '$DEFAULT_Q',
304
-  callid varchar(255) NOT NULL default '$DEFAULT_CALLID',
305
-  cseq int(11) NOT NULL default '$DEFAULT_CSEQ',
306
-  last_modified timestamp(14) NOT NULL,
307
-  replicate int(10) unsigned NOT NULL default '0',
308
-  state tinyint(1) unsigned NOT NULL default '0',
309
-  flags int(11) NOT NULL default '0',
310
-  user_agent varchar(50) NOT NULL default '',
311
-  instance varchar(80) default NULL,
312
-  PRIMARY KEY($USERCOL, domain, contact),
313
-  INDEX aliases_contact (contact)
314
-) $TABLE_TYPE;
315
-
316
-
317
-#
318
-# Table structure for table 'grp' -- group membership
319
-# table; used primarily for ACLs
320
-#
321
-
322
-
323
-CREATE TABLE grp (
324
-  $USERCOL varchar(64) NOT NULL default '',
325
-  domain varchar(128) NOT NULL default '',
326
-  grp varchar(50) NOT NULL default '',
327
-  last_modified datetime NOT NULL default '0000-00-00 00:00:00',
328
-  PRIMARY KEY($USERCOL, domain, grp)
329
-) $TABLE_TYPE;
330
-
331
-
332
-
333
-
334
-#
335
-# Table structure for table 'location' -- that is persistent UsrLoc
336
-#
337
-CREATE TABLE location (
338
-  $USERCOL varchar(64) NOT NULL default '',
339
-  domain varchar(128) NOT NULL default '',
340
-  contact varchar(255) NOT NULL default '',
341
-  received varchar(255) default NULL,
342
-  expires datetime NOT NULL default '$DEFAULT_LOCATION_EXPIRES',
343
-  q float(10,3) NOT NULL default '$DEFAULT_Q',
344
-  callid varchar(255) NOT NULL default '$DEFAULT_CALLID',
345
-  cseq int(11) NOT NULL default '$DEFAULT_CSEQ',
346
-  last_modified timestamp(14) NOT NULL,
347
-  replicate int(10) unsigned NOT NULL default '0',
348
-  state tinyint(1) unsigned NOT NULL default '0',
349
-  flags int(11) NOT NULL default '0',
350
-  user_agent varchar(50) NOT NULL default '',
351
-  instance varchar(80) default NULL,
352
-  PRIMARY KEY($USERCOL, domain, contact)
353
-) $TABLE_TYPE;
354
-
355
-
356
-
357
-
358
-#
359
-# Table structure for table 'missed_calls' -- acc-like table
360
-# for keeping track of missed calls
361
-#
362
-
363
-
364
-CREATE TABLE missed_calls (
365
-  sip_from varchar(128) NOT NULL default '',
366
-  sip_to varchar(128) NOT NULL default '',
367
-  sip_status varchar(128) NOT NULL default '',
368
-  sip_method varchar(16) NOT NULL default '',
369
-  i_uri varchar(128) NOT NULL default '',
370
-  o_uri varchar(128) NOT NULL default '',
371
-  from_uri varchar(128) NOT NULL default '',
372
-  to_uri varchar(128) NOT NULL default '',
373
-  sip_callid varchar(128) NOT NULL default '',
374
-  $USERCOL varchar(64) NOT NULL default '',
375
-  domain varchar(128) NOT NULL default '',
376
-  fromtag varchar(128) NOT NULL default '',
377
-  totag varchar(128) NOT NULL default '',
378
-  time datetime NOT NULL default '0000-00-00 00:00:00',
379
-  timestamp timestamp(14) NOT NULL,
380
-  INDEX mc_user ($USERCOL, domain)
381
-) $TABLE_TYPE;
382
-
383
-
384
-
385
-
386
-#
387
-# Table structure for table 'pending' -- unconfirmed subscribtion
388
-# requests
389
-#
390
-
391
-
392
-CREATE TABLE pending (
393
-  phplib_id varchar(32) NOT NULL default '',
394
-  $USERCOL varchar(64) NOT NULL default '',
395
-  domain varchar(128) NOT NULL default '',
396
-  password varchar(25) NOT NULL default '',
397
-  first_name varchar(25) NOT NULL default '',
398
-  last_name varchar(45) NOT NULL default '',
399
-  phone varchar(15) NOT NULL default '',
400
-  email_address varchar(50) NOT NULL default '',
401
-  datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
402
-  datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
403
-  confirmation varchar(64) NOT NULL default '',
404
-  flag char(1) NOT NULL default 'o',
405
-  sendnotification varchar(50) NOT NULL default '',
406
-  greeting varchar(50) NOT NULL default '',
407
-  ha1 varchar(128) NOT NULL default '',
408
-  ha1b varchar(128) NOT NULL default '',
409
-  allow_find char(1) NOT NULL default '0',
410
-  timezone varchar(128) default NULL,
411
-  rpid varchar(128) default NULL,
412
-  domn int(10) default NULL,
413
-  uuid varchar(64) default NULL,
414
-  PRIMARY KEY ($USERCOL, domain),
415
-  KEY user_2 ($USERCOL),
416
-  UNIQUE KEY phplib_id (phplib_id)
417
-) $TABLE_TYPE;
418
-
419
-
420
-
421
-
422
-#
423
-# Table structure for table 'phonebook' -- user's phonebook
424
-#
425
-
426
-
427
-CREATE TABLE phonebook (
428
-  id int(10) unsigned NOT NULL auto_increment,
429
-  $USERCOL varchar(64) NOT NULL default '',
430
-  domain varchar(128) NOT NULL default '',
431
-  fname varchar(32) NOT NULL default '',
432
-  lname varchar(32) NOT NULL default '',
433
-  sip_uri varchar(128) NOT NULL default '',
434
-  PRIMARY KEY  (id)
435
-) $TABLE_TYPE;
436
-
437
-
438
-#
439
-# Table structure for table 'subscriber' -- user database
440
-#
441
-
442
-
443
-CREATE TABLE subscriber (
444
-  $USERCOL varchar(64) NOT NULL default '',
445
-  domain varchar(128) NOT NULL default '',
446
-  password varchar(25) NOT NULL default '',
447
-  first_name varchar(25) NOT NULL default '',
448
-  last_name varchar(45) NOT NULL default '',
449
-  phone varchar(15) NOT NULL default '',
450
-  email_address varchar(50) NOT NULL default '',
451
-  datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
452
-  datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
453
-  confirmation varchar(64) NOT NULL default '',
454
-  flag char(1) NOT NULL default 'o',
455
-  ha1 varchar(128) NOT NULL default '',
456
-  ha1b varchar(128) NOT NULL default '',
457
-  allow_find char(1) NOT NULL default '0',
458
-  timezone varchar(128) default NULL,
459
-  rpid varchar(128) default NULL,
460
-  domn int(10) default NULL,
461
-  uuid varchar(64) default NULL,
462
-  UNIQUE KEY phplib_id (phplib_id),
463
-  PRIMARY KEY ($USERCOL, domain),
464
-  KEY user_2 ($USERCOL)
465
-) $TABLE_TYPE;
466
-
467
-
468
-# "instant" message silo
469
-
470
-CREATE TABLE silo(
471
-    mid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
472
-    src_addr VARCHAR(255) NOT NULL DEFAULT "",
473
-    dst_addr VARCHAR(255) NOT NULL DEFAULT "",
474
-    r_uri VARCHAR(255) NOT NULL DEFAULT "",
475
-    username VARCHAR(64) NOT NULL DEFAULT "",
476
-    domain VARCHAR(128) NOT NULL DEFAULT "",
477
-    inc_time INTEGER NOT NULL DEFAULT 0,
478
-    exp_time INTEGER NOT NULL DEFAULT 0,
479
-    ctype VARCHAR(32) NOT NULL DEFAULT "text/plain",
480
-    body BLOB NOT NULL DEFAULT ""
481
-) $TABLE_TYPE;
482
-
483
-#
484
-# Table structure for table 'domain' -- domains proxy is responsible for
485
-#
486
-
487
-CREATE TABLE domain (
488
-  d_id int NOT NULL,
489
-  domain varchar(128) NOT NULL default '',
490
-  last_modified datetime NOT NULL default '0000-00-00 00:00:00',
491
-  UNIQUE KEY domain (domain),
492
-  PRIMARY KEY  (d_id, domain)
493
-) $TABLE_TYPE;
494
-
495
-#
496
-# Table structure for table 'customer'
497
-#
498
-
499
-CREATE TABLE customer (
500
-  c_id int NOT NULL AUTO_INCREMENT,
501
-  name varchar(128) NOT NULL,
502
-  address varchar(255),
503
-  phone varchar(64),
504
-  email varchar(255),
505
-  last_modified datetime NOT NULL default '0000-00-00 00:00:00',
506
-  PRIMARY KEY  (c_id)
507
-) $TABLE_TYPE;
508
-
509
-
510
-#
511
-# Table structure for table 'uri' -- uri user parts users are allowed to use
512
-#
513
-CREATE TABLE uri (
514
-  $USERCOL varchar(64) NOT NULL default '',
515
-  domain varchar(128) NOT NULL default '',
516
-  uri_user varchar(50) NOT NULL default '',
517
-  last_modified datetime NOT NULL default '0000-00-00 00:00:00',
518
-  PRIMARY KEY ($USERCOL, domain, uri_user)
519
-) $TABLE_TYPE;
520
-
521
-#
522
-# Table structure for table 'server_monitoring'
523
-#
524
-
525
-
526
-DROP TABLE IF EXISTS server_monitoring;
527
-CREATE TABLE server_monitoring (
528
-  time datetime NOT NULL default '0000-00-00 00:00:00',
529
-  id int(10) unsigned NOT NULL default '0',
530
-  param varchar(32) NOT NULL default '',
531
-  value int(10) NOT NULL default '0',
532
-  increment int(10) NOT NULL default '0',
533
-  PRIMARY KEY  (id,param)
534
-) $TABLE_TYPE;
535
-
536
-#
537
-# Table structure for table 'usr_preferences'
538
-#
539
-
540
-DROP TABLE IF EXISTS usr_preferences;
541
-CREATE TABLE usr_preferences (
542
-  uuid varchar(64) NOT NULL default '',
543
-  $USERCOL varchar(100) NOT NULL default '0',
544
-  domain varchar(128) NOT NULL default '',
545
-  attribute varchar(32) NOT NULL default '',
546
-  value varchar(128) NOT NULL default '',
547
-  type integer NOT NULL default '0',
548
-  modified timestamp(14) NOT NULL,
549
-  PRIMARY KEY  (attribute,$USERCOL,domain)
550
-) $TABLE_TYPE;
551
-
552
-
553
-
554
-#
555
-# Table structure for table 'usr_preferences_types' -- types of atributes in preferences
556
-#
557
-
558
-CREATE TABLE usr_preferences_types (
559
-  att_name varchar(32) NOT NULL default '',
560
-  att_rich_type varchar(32) NOT NULL default 'string',
561
-  att_raw_type int(11) unsigned NOT NULL default '2',
562
-  att_type_spec text,
563
-  default_value varchar(100) NOT NULL default '',
564
-  PRIMARY KEY  (att_name)
565
-) $TABLE_TYPE;
566
-
567
-
568
-#
569
-# Table structure for table 'dom_preferences'
570
-#
571
-
572
-DROP TABLE IF EXISTS dom_preferences;
573
-CREATE TABLE dom_preferences (
574
-  d_id int NOT NULL,
575
-  att_name varchar(32) NOT NULL,
576
-  att_value varchar(255),
577
-  PRIMARY KEY  (d_id, att_name, att_value)
578
-) $TABLE_TYPE;
579
-
580
-#
581
-# Table structure for table trusted
582
-CREATE TABLE trusted (
583
-  src_ip varchar(39) NOT NULL,
584
-  proto varchar(4) NOT NULL,
585
-  from_pattern varchar(64) NOT NULL,
586
-  PRIMARY KEY (src_ip, proto, from_pattern)
587
-) $TABLE_TYPE;
588
-
589
-
590
-#
591
-# Table structure for table 'server_monitoring_agg'
592
-#
593
-DROP TABLE IF EXISTS server_monitoring_agg;
594
-CREATE TABLE server_monitoring_agg (
595
-  param varchar(32) NOT NULL default '',
596
-  s_value int(10) NOT NULL default '0',
597
-  s_increment int(10) NOT NULL default '0',
598
-  last_aggregated_increment int(10) NOT NULL default '0',
599
-  av float NOT NULL default '0',
600
-  mv int(10) NOT NULL default '0',
601
-  ad float NOT NULL default '0',
602
-  lv int(10) NOT NULL default '0',
603
-  min_val int(10) NOT NULL default '0',
604
-  max_val int(10) NOT NULL default '0',
605
-  min_inc int(10) NOT NULL default '0',
606
-  max_inc int(10) NOT NULL default '0',
607
-  lastupdate datetime NOT NULL default '0000-00-00 00:00:00',
608
-  PRIMARY KEY  (param)
609
-) $TABLE_TYPE;
610
-
611
-#
612
-# Table structure for table 'admin_privileges' -- multidomain serweb ACL control
613
-#
614
-
615
-CREATE TABLE admin_privileges (
616
-  $USERCOL varchar(64) NOT NULL default '',
617
-  domain varchar(128) NOT NULL default '',
618
-  priv_name varchar(64) NOT NULL default '',
619
-  priv_value varchar(64) NOT NULL default '',
620
-  PRIMARY KEY  ($USERCOL,priv_name,priv_value,domain)
621
-) $TABLE_TYPE;
622
-
623
-#
624
-# Table structure for table 'speed_dial'
625
-#
626
-
627
-CREATE TABLE speed_dial (
628
-  uuid varchar(64) NOT NULL default '',
629
-  $USERCOL varchar(64) NOT NULL default '',
630
-  domain varchar(128) NOT NULL default '',
631
-  sd_username varchar(64) NOT NULL default '',
632
-  sd_domain varchar(128) NOT NULL default '',
633
-  new_uri varchar(192) NOT NULL default '',
634
-  fname varchar(128) NOT NULL default '',
635
-  lname varchar(128) NOT NULL default '',
636
-  description varchar(64) NOT NULL default '',
637
-  PRIMARY KEY  ($USERCOL,domain,sd_username,sd_domain)
638
-) $TABLE_TYPE;
639
-
640
-#
641
-# Table structure for table 'gw'
642
-#
643
-
644
-CREATE TABLE gw (
645
-  gw_name VARCHAR(128) NOT NULL,
646
-  grp_id INT UNSIGNED NOT NULL,
647
-  ip_addr INT UNSIGNED NOT NULL,
648
-  port SMALLINT UNSIGNED,
649
-  uri_scheme TINYINT UNSIGNED,
650
-  transport TINYINT UNSIGNED,
651
-  prefix VARCHAR(16) default NULL,
652
-  PRIMARY KEY (gw_name),
653
-  KEY (grp_id)
654
-) $TABLE_TYPE;
655
-
656
-#
657
-# Table structure for table 'gw_grp'
658
-#
659
-
660
-CREATE TABLE gw_grp (
661
-  grp_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
662
-  grp_name VARCHAR(64) NOT NULL
663
-) $TABLE_TYPE;
664
-
665
-#
666
-# Table structure for table 'lcr'
667
-#
668
-
669
-CREATE TABLE lcr (
670
-  prefix varchar(16) NOT NULL,
671
-  from_uri varchar(128) NOT NULL DEFAULT '%',
672
-  grp_id INT UNSIGNED NOT NULL,
673
-  priority TINYINT UNSIGNED NOT NULL,
674
-  KEY (prefix),
675
-  KEY (from_uri),
676
-  KEY (grp_id)
677
-) $TABLE_TYPE;
678
-
679
-
680
-# add an admin user "admin" with password==heslo,
681
-# so that one can try it out on quick start
682
-
683
-$INITIAL_USER
684
-
685
-
686
-
687
-EOF
688
-
689
-} # ser_create
690
-
691
-
692
-export PW
693
-if [ "$#" -ne 0 ]; then
694
-  prompt_pw
695
-fi
696
-
697
-case $1 in
698
-	reinstall)
699
-
700
-		#1 create a backup database (named *_bak)
701
-		echo "creating backup database"
702
-		ser_backup $DBNAME
703
-		if [ "$?" -ne 0 ] ; then
704
-			echo "reinstall: ser_backup failed"
705
-			exit 1
706
-		fi
707
-		#2 dump original database and change names in it
708
-		echo "dumping table content ($DBNAME)"
709
-		tmp_file=/tmp/ser_mysql.$$
710
-		ser_dump $DBNAME  > $tmp_file
711
-		if [ "$?" -ne 0 ] ; then
712
-			echo "reinstall: dumping original db failed"
713
-			exit 1
714
-		fi
715
-		sed "s/[uU][sS][eE][rR]_[iI][dD]/user/g" $tmp_file |
716
-			sed "s/[uU][sS][eE][rR]\($\|[^a-zA-Z]\)/$USERCOL\1/g" |
717
-			sed "s/[rR][eE][aA][lL][mM]/domain/g"> ${tmp_file}.2
718
-		#3 drop original database
719
-		echo "dropping table ($DBNAME)"
720
-		ser_drop $DBNAME
721
-		if [ "$?" -ne 0 ] ; then
722
-			echo "reinstall: dropping table failed"
723
-			rm $tmp_file*
724
-			exit 1
725
-		fi
726
-		#4 change names in table definition and restore
727
-		echo "creating new structures"
728
-		ser_create $DBNAME no_init_user
729
-		if [ "$?" -ne 0 ] ; then
730
-			echo "reinstall: creating new table failed"
731
-			rm $tmp_file*
732
-			exit 1
733
-		fi
734
-		#5 restoring table content
735
-		echo "restoring table content"
736
-
737
-		# Recreate perms column here so that subsequent
738
-		# restore succeeds
739
-
740
-    sql_query $DBNAME << EOF
741
-    ALTER TABLE subscriber ADD perms VARCHAR(32)  AFTER ha1b;
742
-    ALTER TABLE pending ADD perms VARCHAR(32)  AFTER ha1b;
743
-EOF
744
-
745
-
746
-		ser_restore $DBNAME ${tmp_file}.2
747
-		if [ "$?" -ne 0 ] ; then
748
-			echo "reinstall: restoring table failed"
749
-			rm $tmp_file*
750
-			exit 1
751
-		fi
752
-
753
-
754
-    sql_query $DBNAME << EOF
755
-
756
-    # Move perms from subscriber to admin_privileges
757
-    INSERT INTO admin_privileges ($USERCOL, domain, priv_name, priv_value) SELECT $USERCOL, domain, 'is_admin', '1' FROM subscriber WHERE perms='admin';
758
-
759
-		# Drop perms column here
760
-    ALTER TABLE subscriber DROP perms;
761
-    ALTER TABLE pending DROP perms;
762
-
763
-EOF
764
-
765
-#XX
766
-#		rm $tmp_file*
767
-		exit 0
768
-		;;
769
-	copy)
770
-		# copy database to some other name
771
-		shift
772
-		if [ $# -ne 1 ]; then
773
-			usage
774
-			exit 1
775
-		fi
776
-		tmp_file=/tmp/ser_mysql.$$
777
-		ser_dump $DBNAME  > $tmp_file
778
-		ret=$?
779
-		if [ "$ret" -ne 0 ]; then
780
-			rm $tmp_file
781
-			exit $ret
782
-		fi
783
-		ser_create $1 no_init_user
784
-		ret=$?
785
-		if [ "$ret" -ne 0 ]; then
786
-			rm $tmp_file
787
-			exit $ret
788
-		fi
789
-		ser_restore $1 $tmp_file
790
-		ret=$?
791
-		rm $tmp_file
792
-		exit $ret
793
-		;;
794
-	backup)
795
-		# backup current database
796
-		ser_dump $DBNAME
797
-		exit $?
798
-		;;
799
-	restore)
800
-		# restore database from a backup
801
-		shift
802
-		if [ $# -ne 1 ]; then
803
-			usage
804
-			exit 1
805
-		fi
806
-		ser_restore $DBNAME $1
807
-		exit $?
808
-		;;
809
-	create)
810
-		# create new database structures
811
-		shift
812
-		if [ $# -eq 1 ] ; then
813
-			DBNAME="$1"
814
-		fi
815
-		ser_create $DBNAME
816
-		exit $?
817
-		;;
818
-	drop)
819
-		# delete ser database
820
-		ser_drop $DBNAME
821
-		exit $?
822
-		;;
823
-	reinit)
824
-		# delete database and create a new one
825
-		ser_drop $DBNAME
826
-		ret=$?
827
-		if [ "$ret" -ne 0 ]; then
828
-			exit $ret
829
-		fi
830
-		ser_create $DBNAME
831
-		exit $?
832
-		;;
833
-	*)
834
-		usage
835
-		exit 1;
836
-		;;
837
-esac
838
-
Browse code

- Columns sendnotification, greeting, and phplib_id removed from subscriber table, they are not being used anymore

Jan Janak authored on 28/10/2005 00:27:45
Showing 1 changed files
... ...
@@ -441,7 +441,6 @@ CREATE TABLE phonebook (
441 441
 
442 442
 
443 443
 CREATE TABLE subscriber (
444
-  phplib_id varchar(32) NOT NULL default '',
445 444
   $USERCOL varchar(64) NOT NULL default '',
446 445
   domain varchar(128) NOT NULL default '',
447 446
   password varchar(25) NOT NULL default '',
... ...
@@ -453,8 +452,6 @@ CREATE TABLE subscriber (
453 452
   datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
454 453
   confirmation varchar(64) NOT NULL default '',
455 454
   flag char(1) NOT NULL default 'o',
456
-  sendnotification varchar(50) NOT NULL default '',
457
-  greeting varchar(50) NOT NULL default '',
458 455
   ha1 varchar(128) NOT NULL default '',
459 456
   ha1b varchar(128) NOT NULL default '',
460 457
   allow_find char(1) NOT NULL default '0',
Browse code

- realm removed from version table

Jan Janak authored on 28/10/2005 00:15:29
Showing 1 changed files
... ...
@@ -245,7 +245,6 @@ INSERT INTO version VALUES ( 'grp', '2');
245 245
 INSERT INTO version VALUES ( 'aliases', '7');
246 246
 INSERT INTO version VALUES ( 'acc', '2');
247 247
 INSERT INTO version VALUES ( 'silo', '3');
248
-INSERT INTO version VALUES ( 'realm', '1');
249 248
 INSERT INTO version VALUES ( 'domain', '2');
250 249
 INSERT INTO version VALUES ( 'uri', '1');
251 250
 INSERT INTO version VALUES ( 'server_monitoring', '1');
Browse code

- config table removed (not used)

Jan Janak authored on 28/10/2005 00:09:49
Showing 1 changed files
... ...
@@ -244,7 +244,6 @@ INSERT INTO version VALUES ( 'location', '7');
244 244
 INSERT INTO version VALUES ( 'grp', '2');
245 245
 INSERT INTO version VALUES ( 'aliases', '7');
246 246
 INSERT INTO version VALUES ( 'acc', '2');
247
-INSERT INTO version VALUES ( 'config', '1');
248 247
 INSERT INTO version VALUES ( 'silo', '3');
249 248
 INSERT INTO version VALUES ( 'realm', '1');
250 249
 INSERT INTO version VALUES ( 'domain', '2');
... ...
@@ -469,16 +468,6 @@ CREATE TABLE subscriber (
469 468
   KEY user_2 ($USERCOL)
470 469
 ) $TABLE_TYPE;
471 470
 
472
-# hook-table for all posssible future config values
473
-# (currently unused)
474
-
475
-CREATE TABLE config (
476
-   attribute varchar(32) NOT NULL,
477
-   value varchar(128) NOT NULL,
478
-   $USERCOL varchar(64) NOT NULL default '',
479
-   domain varchar(128) NOT NULL default '',
480
-   modified timestamp(14)
481
-) $TABLE_TYPE;
482 471
 
483 472
 # "instant" message silo
484 473
 
Browse code

- Tables calls_forwarding, active_sessions, event, and reserved are obsolete

Jan Janak authored on 28/10/2005 00:00:20
Showing 1 changed files
... ...
@@ -237,15 +237,12 @@ CREATE TABLE version (
237 237
 #
238 238
 
239 239
 INSERT INTO version VALUES ( 'subscriber', '5');
240
-INSERT INTO version VALUES ( 'reserved', '1');
241 240
 INSERT INTO version VALUES ( 'phonebook', '1');
242 241
 INSERT INTO version VALUES ( 'pending', '4');
243 242
 INSERT INTO version VALUES ( 'missed_calls', '2');
244 243
 INSERT INTO version VALUES ( 'location', '7');
245 244
 INSERT INTO version VALUES ( 'grp', '2');
246